Как сделать динамическую диаграмму

Как строить динамические графики в Excel по именованным диапазонам

Научитесь использовать весь функционал Excel для работы с данными, их визуализации и автоматизации рабочих задач
на курсе «Excel Academy»

В прошлой статье мы разобрали динамические графики, построенные с помощью флажков и применения функции ЕСЛИ. Тогда графики появлялись и растворялись по включению и выключению флажков.

Давайте сегодня рассмотрим альтернативную ситуацию, когда нужно, чтобы при выборе команды из выпадающего списка отображался соответствующий график; новая команда – новый график и так далее.

Табличка у нас та же самая. Разница лишь в том, что рядом нам нужно вывести выпадающий список.

Определяемся с ячейкой, переходим на вкладку «Данные», потом «Проверка» данных, в поле «Тип данных» выбираем «Список», а в качестве источника выделяем диапазон (столбец) от Команды 1 до Команды 5.

Иными словами, говорим Excel, какие обозначения будут переключаться в нашем списке.

Проверяем список, на всякий случай.

Теперь самое интересное – прописывание формулы в именованный диапазон. Вот только давайте сначала мы расскажем логику этого построения.

Во главе всего у нас будет стоять функция СМЕЩ. В ней целых 5 аргументов, и многие пользователи с опаской используют данную функцию. Однако, суть её проста, да и аргументы нам понадобятся далеко не все.

Пока не заморачивайтесь с тем, куда её нужно писать, лишь поймайте ход мысли. Вот формула:

Весь фокус – в правильном создании имени.

Нажимаем Ctrl+F3 (Диспетчер имён – Создать) или идём на вкладку «Формулы» и кликаем по «Задать имя».

Имя – Commands (можно выбрать любое, которое в состоянии запомнить), сразу скопируйте его.

А в диапазоне как раз и вбиваем ту самую зловещую формулу:

Результат работы этой формулы вы можете проверить без «Диспетчера имён»: предварительно вводите эту формулу в любую свободную ячейку и смотрите, чтобы появились числа.

Приступаем к самому волнительному моменту – вставке диаграммы. Выделяем всю нашу таблицу через Ctrl+A или обводкой, затем идём по адресу: «Вставка» – «Диаграмма» – выбираем рекомендованный тип, почему бы и нет.

Кликаем правой кнопкой на поле диаграммы, а затем – «Выбрать данные» (либо с Ленты, в «Конструкторе диаграмм»).

В левом поле под названием «Элементы легенды (ряды)» очищаем все подписи (кнопка «Удалить») и нажимаем «Создать»:

«Имя ряда» – это ячейка с выпадающим списком, просто выбираем её.

В «Значения» мы должны прописать созданное имя, не забыв скрепить его с листом, то есть должно быть так:

Commands (здесь может быть заданное вами имя) – это тот самый именованный диапазон с главной функцией СМЕЩ.

По сути, готово! Если вы будете переключать команды из выпадающего списка, то график отобразит результаты по конкретному номеру.

Выбирайте решение для того типа задачи, которое вам необходимо, будь то флажки или именованный диапазон с выпадающим списком, и приступайте!

Научитесь использовать весь функционал Excel для работы с данными, их визуализации и автоматизации рабочих задач
на курсе «Excel Academy»

Источник

Как построить динамический график с анимацией в Excel

Создание оригинальных, интерактивных и динамических графиков с анимацией в Excel можно реализовать с помощью макросов. Анимация очень уместна в скучных отчетах, особенно если речь идет о визуализации данных.

Как сделать интерактивный график с анимацией в Excel

В данном примере возьмем за исходный показатель изменяемое число в процентном значении. Создайте две таблички как показано ниже на рисунке:

Продолжаем заполнять вторую таблицу. В первой ячейке второй таблички указываем формулу вычитания от 100% значение, взятое из исходного показателя:

Теперь переводим оба значения в отрицательное число процентов:

Исходные данные подготовлены и обработанные. Переходим непосредственно к построению динамического графика.

Выделите диапазон ячеек D2:G1 второй таблицы и выберите график: «ВСТАВКА»-«Диаграммы»-«Гистограмма с накоплением»:

Теперь перейдите в дополнительное меню гистограммы и выберите переключатель: «РАБОТА С ДИАГРАММАМИ»-«КОНСТРУКТОР»-«Данные»-«Строка/Столбец»:

За одно снимите все галочки с опций выпадающего меню «ЭЛЕМЕНТЫ ДИАГРАММЫ» при нажатии на кнопку плюс «+».

Далее нижний (Ряд4) и через один вверх (Ряд2) присваиваем одинаковый цвет. А для остальных двух рядов (верхний Ряд1 и через один вниз Ряд3) делаем невидимыми убрав цвет заливки:

Динамический график для анимации готов, но мы добавим к нему сложную фигуру, сделанную также в офисной программе PowerPoint.

Как сделать сложную фигуру для красивых графиков в PowerPoint

Создаем 2 таких фигуры переворачиваем их вертикально создавая форму песочных часов, как показано ниже на рисунке:

Выделяем две фигуры и объединяем в одну выбрав инструмент и з дополнительного меню: «Средства рисования»-«ФОРМАТ»-«Вставка фигур»-«Объединить фигуры»-«Объединение»:

Далее необходимо создать еще одну большею по размерам фигуру «Прямоугольник» без контура. После чего необходимо наложить сверху на большой прямоугольник фигуру песочных часов предварительно выделив и выбрав: «Средства рисования»-«ФОРМАТ»-«Упорядочение»-«Переместить вперед»-«На передний план». Затем выделить их обе и выбрать инструмент: «Средства рисования»-«ФОРМАТ»-«Вставка фигур»-«Объединить фигуры»-«Группирование»:

В результате у нас получилась маска. Меняем для нее цвет заливки на «белый» используя палитру: «Средства рисования»-«ФОРМАТ»-«Стили фигур»-«Заливка фигуры»-«Цвет-белый». А чтобы удалить только лишь внешний контур сначала копируем CTRL+C, но вставляем через контекстное меню вызванное правой кнопкой мышки кликнув на пустом месте листа Excel. Из появившегося контекстного меню выбираем опцию «Рисунок», чтобы вставить фигуру как рисунок:

После чего накладываем рисунок (маску) на гистограмму с накоплением. Далее подгоняем его размер.

Добавление сложной фигуры из PowerPoint на график в Excel

Пока выделен рисунок доступно дополнительное меню с инструментом обрезки его внешней границы: «РАБОТА С РИСУНКАМИ»-«ФОРМАТ»-«Размер»-«Обрезка»

Устанавливаем новые границы с помощью маркеров и снова нажимаем на кнопку «Обрезка», чтобы получить желаемый результат.

Недостает еще визуальной имитации струи. Для этого добавим еще одну фигуру прямоугольника без контура, но с таим же цветом заливки как окрашенные рады гистограммы. Этот прямоугольник можно уже создать прямо из Excel, выбрав фигуру для струи: «ВСТАВКА»-«Иллюстрации»-«Фигуры»-«Прямоугольник». А цвета настраиваем из его дополнительного меню: «СРЕДСТВА РИСОВАНИЯ»-«ФОРМАТ»-«Стили фигур»-«Заливка»-«Цвет»-«Зеленый» и здесь же «Контур»-«Нет контура»:

Размер данного прямоугольника должен быть по высоте равен нижнему сосуду, а ширина равна горловине нижнего сосуда. Все готово для оживления с помощью анимации динамического графика VBA-макросами Excel.

Макрос для анимации динамического графика в Excel

Для добавления анимации откройте редактор макросов: «РАЗРАБОТЧИК»-«Код»-«Visual Basic» (Alt+F11). Затем пропишите ниже приведенный код макроса прямо в Лист1:

Код макроса для копирования:

Option Explicit
Private Sub Worksheet_Change( ByVal Target As Range)

Dim i As Integer
Dim temp As Integer
temp = 1000 / ActiveSheet.Range( «B4» )

If Target.Address = «$B$2» Then

For i = 0 To Int(Target.Value * temp)
DoEvents
ActiveSheet.Range( «B3» ).Value = i / temp
Next i
ActiveSheet.Range( «B3» ).Value = Target.Value
End If
End Sub

Теперь после ввода значения в ячейку B2 будет каждый раз автоматически выполнятся макрос анимации значений в ячейках, соответственно на графике.

Нам осталось лишь добавить подписи данных на графике, передав в них значение из ячейки B3. Но в этом случае в качестве подписей данных мы не будем использовать средства диаграмм, а создадим свою с помощью надписи. Для этого выберите опцию из: «ВСТАВКА»-«Текст»-«Надпись»:

Пока выделен элемент «Надпись» выведите в строку формул ссылку на ячейку B3 и нажмите клавишу Enter на клавиатуре для подтверждения. Таким образом мы в надпись передаем значение из ячейки B3 в качестве отображаемого текста. Протестируем график на интерактивность и динамическую изменяемость с помощью анимации:

Стоит отметить что в ячейке B4 мы можем задать скорость анимации. Таким образом не сложно из интерактивного графика сделать таймер в Excel.

Анимация на графиках позволяет развеселить любую скучную презентацию с визуализацией данных на графиках и диаграммах в Excel. Теперь Ваши отчеты и труды бут привлекать к себе больше внимания.

Источник

Марк Мур. Динамические диаграммы

В последнее время меня интересует тема дашбордов. Динамические диаграммы, использующие элементы управления – это одна из типичных фишек наглядного представления данных. Ранее в заметке Диаграммы в Excel. Отображение части данных с использованием элементов управления я описал работу одного из элементов – полосы прокрутки. Представляю вам еще четыре элемента управления, которые способны сделать ваши диаграммы профессиональными и легко читаемыми: счетчик (Spin Button), флажок (CheckBox), переключатель (Option Button), камера. Изложение довольно подробное, так что материал доступен и пользователям начального уровня. Обзор основан на переводе книги

Mark Moore. Mastering Excel. Interactive Charts

Скачать заметку в формате Word или pdf, пошаговые инструкции и итоговые результаты в Excel

Вкладка Разработчик

При стандартной установке Excel вкладка Разработчик не активируется. Пройдите по меню Файл –> Параметры. В открывшемся окне Параметры Excel выберите закладку Настроить ленту и поставьте галочку Разработчик (рис. 1).

Рис. 1. Активация вкладки Разработчик; чтобы увеличить изображение кликните на нем правой кнопкой мыши и выберите Открыть картинку в новой вкладке

На ленте появится вкладка Разработчик. Если в области Элементы управления кликнуть на кнопке Вставить, увидите интересующие нас инструменты (рис. 2). Не используйте Элементы ActiveX. Хотя они и выглядят похоже, но используют код VBA.

Рис. 2. Элементы управления формы на вкладке Разработчик

Уровни Excel

Исходные данные представляют собой отчет о продажах (рис. 3). Данные преобразованы в таблицу. Такое представление нагляднее и удобнее для дальнейшей обработки. Главное – вы получаете возможность обращаться к массиву данных по имени таблицы.

Рис. 3. Исходные данные, преобразованные в таблицу

Я хочу предложить вам концепцию трех уровней Excel. Это поможет вам организовывать ваши данные и их представление. Уровень данных – это необработанные данные, которые были импортированы, или иным образом помещены на лист. Уровень бизнес-логики включает различные формулы, сводные таблицы и т.п. Уровень представления – это визуализация данных для пользователей: диаграммы, дашборды, отчеты и др.

Изменяя один уровень, вы влияете на другие. Например, если вы добавляете 100 строк на уровень данных, уровень бизнес-логики (формулы) обработает эти новые строки, а уровень представления включит новые данные в диаграммы. Это произойдет автоматически, если вы организовали данные в Таблицу (как на рис. 3).

Но вы можете изменить один слой, не затрагивая другие. Например, нового менеджера не устраивает дизайн дашборда. Если вы построили модель на основе уровней, вам нужно будет изменить только уровень представления. Ваша работа, выполненная на уровне данных и бизнес-логики, не пропадет.

Как правило, диаграмма не может получить данные непосредственно с уровня данных. Последний недостаточно организован для этого. Поэтому мы создаем промежуточный лист (или область) с уровнем бизнес-логики, извлекающий данные из уровня данных. Для лучшего визуального восприятия саму диаграмму также лучше расположить на отдельном листе.

Excel хранит даты в виде чисел, даже если значение в ячейке или строке формул выглядит как дата (например, 18.02.2016). Если отформатировать ячейку, содержащую дату, как число, мы увидим истинное значение. Целая часть такого числа – количество дней, прошедших с 1 января 1900 года. Десятичная часть числа (если таковая имеется) – доля от 24 часов в сутках. Например, 42 372,5 соответствует 01.03.2016 12:00.

Формат даты инвойса (см. рис. 3) – ДД.ММ.ГГ. Когда же мы начнем строить диаграмму, мы захотим увидеть данные по месяцам, а не по дням. Чтобы добавить гибкости, я ввел столбец Краткая дата. В нем используется формула =ТЕКСТ(B2; » МММ-ГГ » ), которая возвращает дату в текстовом формате.

Небольшой трюк. Промежуточные листы будут использовать формулу =СУММЕСЛИ(). Для суммирования дат, представленных в текстовом формате, дата должна быть введена в формулу тоже, как текст (с предварительным апострофом) ‘фев-16. Этот текст будет соответствовать тому, что находится в столбце Краткая дата.

Кнопка Счетчик (Spin Button)

Мы создадим комбинированную диаграмму: фактические данные будут показаны столбиками, а целевой уровень продаж – линией. Диаграмма будет отражать продажи по выбранному продавцу. Целевой уровень будет настраиваться с помощью кнопки Счетчик. Вот, что у нас должно получиться:

Рис. 4. Фактические продажи и целевой уровень

Обратите внимание, что к заметке прикреплено два Excel-файла. Первый позволит вам выполнять пошаговые инструкции. Второй содержит итоговые результаты. Откройте первый файл. Перейдите на лист Счетчик. В ячейке C2 настроена проверка данных. Чтобы ее реализовать, выделите ячейку С2 и пройдите по меню Данные –> Работа с данными –> Проверка данных. В открывшемся окне Проверка вводимых данных выберите Тип данных – Список, и укажите Источник =Поиск!$A$2:$A$11 (рис. 5).

Рис. 5. Список продавцов

Перейдите на лист Счет-подготов. Он – промежуточный. На нем собраны данные, на основании которых будет построена диаграмма (рис. 6). Формула =СУММЕСЛИМН() находит сумму инвойсов за каждый месяц по продавцу, указанному в ячейке B2. Сама же ячейка В2 связана с ячейкой С2 на листе Счетчик. Т.е., на листе Счет-подготов в ячейке В2 будет имя продавца, выбранного в списке на листе Счетчик.

Рис. 6. Уровень бизнес-логики – промежуточный лист, как база для построения диаграммы

Вставьте график. Для чего выделите диапазон В1:N3 и пройдите по меню Вставить –> Диаграммы. Выберите пиктограмму Вставить график или диаграмму с областями (рис. 7).

Рис. 7. Вставить диаграмму

Получится следующая диаграмма:

Рис. 8. Начальный вид диаграммы

Чтобы упростить работу, вы сначала все манипуляции выполните на листе Счет-подготов, а потом переместите диаграмму и кнопку на лист Счетчик.

На вкладке Разработчик кликните на значок кнопки Счетчик (рис. 9).

Рис. 9. Значок Счетчик

Указатель мыши превратиться в тонкий крестик. Нарисуйте небольшой вертикальный прямоугольник, и отпустите клавишу мыши. Впоследствии вы сможете изменить размер кнопки Счетчик.

Зададим свойства кнопки Счетчик. Щелкните на кнопке правой кнопкой мыши и выберите опцию Формат объекта… (рис. 10).

Рис. 10. Формат кнопки Счетчик

В окне Формат элемента управления задайте параметры, как указано на рисунке (в последствии вы можете изменить любое из указанных в окне значений; например, вы сочтете, что удобнее сделать шаг изменений 5000):

Рис. 11. Формат элемента управления

Нажмите ОК. Кликните на любой ячейке, чтобы отменить выделение кнопки Счетчик. Несколько раз нажмите на верхнюю часть кнопки Счетчик, чтобы увеличить целевой объем продаж. Одновременно будет увеличиваться значение в ячейке А3 и значения в ячейках С3:N3. Линия Целевые продажи на графике поползет вверх. Щелкните правой кнопкой мыши на диаграмме и выберите Вырезать. Вставьте диаграмму на лист Счетчик. Вернитесь на лист Счет-подготов. Щелкните правой кнопкой мыши на кнопке Счетчик и выберите Вырезать. Вставьте кнопку Счетчик на лист Счетчик. Убедитесь, что кнопка по-прежнему связана с ячейкой ‘Счет-подготов’!$A$3. Преобразуйте линейную диаграмму в комбинированную. Отформатируйте столбики и линии. Воспользуйтесь рекомендациями Эдварда Тафти и минимизируйте количество элементов диаграммы.

Давайте еще раз рассмотрим, как работает эта диаграмма. Данные поступают из Таблицы на листе Данные. Основная часть бизнес-логики (работа формул) выполняется на листе Счет-подготов. Лист Счетчик содержит список продавцов, кнопку и диаграмму. Как работают эти два листа:

Если вы поэкспериментируете с тем, какие данные должны отражаться на диаграмме, вам станет более понятной польза от концепции уровней и от того, как мы организовали структуру решения задачи. Если вам нужно изменить бизнес-логику, вы меняете лист Счет-подготов. Вам не нужно менять, ни диаграмму, ни лист данных. Например, напишите формулы, которые будут устанавливать целевой уровень продаж в зависимости от продавца.

Инструмент Флажок (CheckBox)

В этом раздела мы построим диаграмму, отражающую объем продаж по одному или нескольким регионам (рис. 12). Для интерактивного скрытия / отображения региона используются флажки.

Рис. 12. Продажи по регионам

Откройте Excel-файл. Перейдите на лист Флажок, который пока содержит лишь уникальный список регионов. Перейдите на вкладку Разработчик. Выберите Вставить –> Флажок (рис. 13). Щелкните и перетащите курсор мыши на лист. Кликните на тексте по умолчанию Флажок 1 и удалить его. Перетащите и измените размер флажка, чтобы он поместился в ячейку А3 (рис. 14).

Рис. 13. Вставка флажка

Рис. 14. Первый флажок создан

Трюк. Нажмите на ячейку A3, чтобы выбрать ячейку, а не флажок. Перетащите маркер заполнения (в правом нижнем углу ячейки) до А8. Флажок будет скопирован. К сожалению, свойства каждого флажка нужно установить по-отдельности.

Щелкните правой кнопкой мыши на первый флажок. Выберите Формат Объекта. Перейдите на вкладку Элемент управление (рис. 15). Нажмите кнопку установлен и свяжите с ячейкой ‘Флаг-подготов’!$A$13. Нажимать OK

Рис. 15. Свойства первого флажка

Проверьте, как это работает. При установленном флажке в ячейке А13 листа Флаг-подготов отражается значение ИСТИНА, при снятом флажке – ЛОЖЬ.

Снимите несколько флажков на листе Флажок, перейдите на лист Флаг-подготов. Вы увидите, что соответствующие строки в диапазоне С13:N18 заполнились нулями. Обратите внимание, что вам не обязательно нажимать флажки, вы можете просто ввести ИСТИНА (или 1) / ЛОЖЬ (или 0) в ячейках столбца A.

Выделите диапазон B12:N18. Вставьте линейный график (см. рис. 7). Щелкните правой кнопкой мыши на графике и выберите Вырезать. Вставьте график на лист Флажок. Проверьте, как меняется график при установке/снятии флажков. Отформатируйте диаграмму, придав ей более профессиональный вид.

Инструмент Переключатель (Option Button)

Переключатель позволяет выбрать одну из опций. Мы построим следующую диаграмму:

Рис. 16. Продажи по кварталам

Бизнес-логика для этого случая немного сложнее предыдущих. Вы будете использовать функцию ВПР, чтобы определить, какой вариант выбрал пользователь. Перейдите на лист Переключатель. Вставьте кнопку Переключатель в ячейку В3 (рис. 17). Скопируйте кнопку на ячейки В4:В6.

Рис. 17. Вставка переключателя

Выберите каждую кнопку и измените название по умолчанию на номер квартала (порядок имеет значение: первая, созданная вами кнопка, должна соответствовать 1-му кварталу и т.д.).

Вставьте групповой блок (рис. 18). При его создании убедитесь, что он полностью окружает все кнопки. Назовите группу Кварталы. Щелкните правой кнопкой мыши на одну из кнопок. Выберите Формат объекта. Перейдите на вкладку Элемент управления. Установите ссылку на ячейку A12 листа Перекл-подготов. Поскольку все кнопки являются частью группы, установка одной ссылки на ячейку устанавливает их все. Понажимайте на кнопки, чтобы увидеть изменения чисел в ячейке A12.

Рис. 18. Вставка группы

Рис. 19. Свойства Переключателя

На промежуточном листе Перекл-подготов в диапазоне С3:Н6 формулы =СУММЕСЛИМН() на основе исходных данных суммируют продажи по кварталам и регионам (рис. 20).

Рис. 20. Промежуточный лист бизнес-логики

Для построения диаграммы будет использована область В11:Н12, данные в которую будут отбираться из области В3:Н6 на основании значения в ячейке А12. Проблема с кнопками Переключателя заключается в том, что они возвращают только свой индекс в группе. Другими словами, первая кнопка вернет 1, вторая – 2 и т.д.

С задачей отбора по индексу справляется функция ВПР. Наберите в ячейке В12 формулу: =ВПР($A$12;$A$3:$H$6;СТОЛБЕЦ();ЛОЖЬ). Протащите ее на диапазон В12:Н12. Создайте объемную круговую диаграмму на основании диапазона В11:Н12. Перенесите ее на лист Переключатель. Протестируйте диаграмму, нажимая кнопки разных кварталов. Отформатируйте диаграмму. У вас должно получиться что-то близкое к рис. 16.

Инструмент Камера

Этот раздел чуть сложнее. Он включает несколько оригинальных трюков. Вот что у нас получится в результате:

Рис. 21. Общие продажи по регионам и менеджерам

Это небольшой дашборд. Однако, если нажать кнопку Переключатель, диаграмма изменит вид. И это всё без макросов.

Инструмент Камера является очень полезной функцией, которая давно представлена в Excel, но всегда была скрыта. Добавьте кнопку Камера на панель быстрого доступа. Нажмите на стрелку на панели (рис. 22). Выберите Другие команды…

Рис. 22. Настройка панели быстрого доступа

В открывшемся окне Параметры Excel (рис. 23) в верхнем поле выберите Команды не на ленте (1). На левой панели найдите и выберите кнопку Камера (2). Нажмите Добавить (3). В правом окне появится кнопка Камера (4). Нажмите OK.

Рис. 23. Параметры Excel

Кнопка Камера появилась на панели быстрого доступа (рис. 24).

Рис. 24. Кнопка Камера на панели быстрого доступа

Инструмент Камера делает снимок диапазона ячеек, который затем можно перемещать по листу и даже на другие листы. При этом изображение связывается с исходными ячейками. Это означает, что при изменении исходных ячеек изображение будет обновляться автоматически.

Покажем, как работает инструмент. На листе Камера-подготов выделите диапазон B3:C8:

Рис. 25. Выделение диапазона

Нажмите кнопку Камера. Вокруг выделенной области появилась мигающая дорожка. Щелкните в любом месте листа, чтобы создать изображение. Поменяйте в исходной области цвет шрифта / фон ячейки / значения, вставьте картинку поверх исходных ячеек. Изображение отразит все эти изменения. Верните данные к исходному состоянию (например, несколько раз нажав Ctrl+Z). К сожалению, нельзя создать изображение в Excel и вставить его в другое приложение Microsoft, например, PowerPoint. Это не сработает.

На листе Камера-подготов мы создадим четыре разные диаграммы. Выделите диапазон В2:В8. Вставьте круговую диаграмму (1 на рис. 26). Поместите ее в левой части листа. Снова выберите диапазон B2:C8. Вставьте объемную круговую диаграмму (2 на рис. 26). Поместите ее под первой диаграммой (рис. 27).

Рис. 26. Вставьте две круговые диаграммы

Рис. 27. Две круговые диаграммы

Можете добавить симпатичные выноски на объемную диаграмму. Для этого выберите диаграмму, нажмите на кнопку плюс; нажмите на Метки данных и выберите данные Выноски (рис. 28).

Рис. 28. Выноски на объемной диаграмме

Теперь добавьте две другие диаграммы. Выделите диапазон I2: J12. Вставьте гистограмму с группировкой. Переместите диаграмму под диапазон исходных данных. Снова выделите диапазон I2: J12. Вставьте линейчатую диаграмму с группировкой (рис. 29).

Рис. 29. Гистограмма и линейчатая диаграмма с группировкой

Отформатируйте диаграммы. Выберите первую диаграмму и перейдите на вкладку Конструктор (рис. 30). Выберите Стиль 1. Выберите стиль для второй диаграммы.

Рис. 30. Выбор стиля диаграммы

На листе Камера создайте две кнопки Переключателей, объедините их группой. Создайте еще две кнопки Переключателей, объедините их во вторую группу. Переименуйте группы (рис 31). Свяжите кнопки Переключатель для первой группы с ячейкой F2 листа Камера-подготов, для второй группы с ячейкой М2 Камера-подготов.

Рис. 31. Свойства левой группы

Теперь нужно создать четыре именованных диапазона, по одному для каждого графика. Именованный диапазон – это способ присвоить имя диапазону ячеек. Выберите область, которая полностью охватывает первую диаграмму (рис. 32). Для этого выберите ячейку В9. Нажмите и удерживайте клавишу Shift. Используйте клавишу со стрелкой вправо, чтобы расширить выделение до ячейки G9. Используйте клавишу со стрелкой вниз, чтобы расширить выделение на область В9:G23.

Рис. 32. Выделение области В9:G23

При выделенном диапазоне В9:G23 щелкните внутри поля имя (рис. 33). В нем отражается адрес левой верхней ячейки выделенного диапазона. Введите новое имя – SalesbyRegion2D (без пробелов). Нажать Enter. (Это важно. Если вы кликнули мышкой где-либо за пределами диапазона, повторите всё сначала.) Если вы сделали всё правильно, новое имя появится в поле имя (рис. 34).

Выделите диапазон В24:G38, содержащий объемную круговую диаграмму. Поименуйте этот диапазон SalesbyRegion3D. Если что-то пошло не так, или в последующем диапазон «слетит», перейдите на ленте на вкладку Формулы, кликните на кнопку Диспетчер имен. В открывшемся окне выберите имя и поправьте диапазон.

Повторите эти действия и присвойте имена диапазонам под двумя другими диаграммами.

Теперь у вас есть четыре разных диаграммы, и каждая диаграмма находится внутри своего собственного диапазона с индивидуальным именем. Поскольку кнопки Переключатель возвращают числа, нужно построить формулу, которая преобразует 1 или 2 в соответствующее имя.

На листе Камера-подготов в ячейку F3 введите формулу:

=ЕСЛИ(F2=1; » SalesbyRegion2D » ;» » SalesbyRegion3D » )

В ячейку M3 листа Камера-подготов введите формулу:

=ЕСЛИ(M2=1; » SalesbyPersonBar » ; » SalesbyPersonColumn » )

Воспользуемся тем, что изображение камеры может использовать в качестве источника именованный диапазон. Перейдите на лист Камера. Выберите любой диапазон (например, B10:B20). Нажмите кнопку Камера на панели быстрого доступа. Щелкните в любом месте, чтобы создать изображение. Посмотрите на строку формул. Там указан выбранный диапазон: =$B$10:$B$20. Его нужно изменить на значение, возвращаемое оператором ЕСЛИ().

Перейдите на вкладку Формулы. Щелкните Диспетчер имен. В открывшемся окне Диспетчер имен кликните Создать. В окне Создание имени введите имя диапазона SwitchPie (рис. 35) и в поле Диапазон формулу:

Рис. 35. Создание нового именованного диапазона

Что делает функция ДВССЫЛ? Она извлекает значение из ячейки F3 и вместо использования текстового значения в ячейке вычисляет это значение… которое просто оказывается одним из именованных диапазонов. Это одна из самых интуитивно непонятных функций Excel. Если вы хотите разобраться, рекомендую Примеры использования функции ДВССЫЛ.

Нажмите кнопку Создать еще раз. Создайте именованный диапазон SwitchBarColumn с формулой: =ДВССЫЛ(‘Камера-подготов’!$M$3). Перейти на лист Переключатель. Выберите ранее созданное изображение. При выбранном изображении измените в строке формул =$B$10:$B$20 на =SwitchPie. Изображение камеры изменилось, и отобразило диаграмму! Создайте новое изображение камеры. При выбранном изображении измените формулу изображения на =SwitchBarColumn. Всё готово. Используйте кнопки Переключатель для изменения вида графиков.

Еще раз кратко о том, что мы сделали в последнем разделе. Изображение камеры показывает на листе именованный диапазон. Именованный диапазон содержит диаграмму. Поэтому именованный диапазон показывает диаграмму. Это здорово, но статично, вы хотите изменять картинку, отражаемую инструментом Камера. Вы создали два оператора ЕСЛИ(), которые возвращают имена диапазонов, в которых находятся диаграммы. Вы создали третий именованный диапазон, который взял текст имени диапазона и преобразовал его в фактический объект имени диапазона с помощью функции ДВССЫЛ(). Этот третий диапазон стал источником изображения для Камеры.

Если вы сумели повторить за мной все шаги, поздравляю! Если вы запутались, не расстраивайтесь: всё же вы освоили несколько более простых инструментов, описанных в начале заметки.

Источник