Excel интерактивные диаграммы в excel

Интерактивная диаграмма

Качественная визуализация большого объема информации – это почти всегда нетривиальная задача, т.к. отображение всех данных часто приводит к перегруженности диаграммы, ее запутанности и, в итоге, к неправильному восприятию и выводам.

Вот, например, данные по курсам валют за несколько месяцев:

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

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

Выглядеть это может примерно так:

Нравится? Тогда поехали…

Шаг 1. Создаем дополнительную таблицу для диаграммы

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

В Excel 2007/2010 к созданным диапазонам можно применить команду Форматировать как таблицу (Format as Table) с вкладки Главная (Home):

Это даст нам следующие преимущества:

• Любые формулы в таких таблицах автоматически транслируются на весь столбец – не надо «тянуть» их вручную до конца таблицы
• При дописывании к таблице новых строк в будущем (новых дат и курсов) – размеры таблицы увеличиваются автоматически, включая корректировку диапазонов в диаграммах, ссылках на эту таблицу в других формулах и т.д.
• Таблица быстро получает красивое форматирование (чересстрочную заливку и т.д.)
• Каждая таблица получает собственное имя (в нашем случае – Таблица1 и Таблица2), которое можно затем использовать в формулах.

Подробнее про преимущества использования подобных Таблиц можно почитать тут.

Шаг 2. Добавляем флажки (checkboxes) для валют

В  Excel 2007/2010 для этого необходимо отобразить вкладку Разработчик (Developer), а в Excel 2003 и более старших версиях – панель инструментов Формы (Forms). Для этого:

• В Excel 2003: выберите в меню Вид – Панели инструментов – Формы (View – Toolbars – Forms)
• В Excel 2007: нажать кнопку Офис – Параметры Excel – Отобразить вкладку Разработчик на ленте (Office Button – Excel options – Show Developer Tab in the Ribbon)
• В Excel 2010: Файл – Параметры – Настройка ленты – включить флаг Разрабочик (File – Options – Customize Ribbon – Developer)

На появившейся панели инструментов или вкладке Разработчик (Developer) в раскрывающемся списке Вставить (Insert) выбираем инструмент Флажок (Checkbox) и рисуем два флажка-галочки для включения-выключения каждой из валют:

Текст флажков можно поменять, щелкнув по ним правой кнопкой мыши и выбрав команду Изменить текст (Edit text).

Теперь привяжем наши флажки к любым ячейкам для определения того, включен флажок или нет (в нашем примере это две желтых ячейки в верхней части дополнительной таблицы). Для этого щелкните правой кнопкой мыши по очереди по каждому добавленному флажку и выберите команду Формат объекта (Format Control), а затем в открывшемся окне задайте Связь с ячейкой (Cell link).

Наша цель в том, чтобы каждый флажок был привязан  к соответствующей желтой ячейке над столбцом с валютой. При включении флажка в связанную ячейку будет выводиться ИСТИНА (TRUE), при выключении – ЛОЖЬ (FALSE). Это позволит, в дальнейшем, проверять с помощью формул связанные ячейки и выводить в дополнительную таблицу либо значение курса из исходной таблицы для построения графика, либо #Н/Д (#N/A), чтобы график не строился.

Шаг 3. Транслируем данные в дополнительную таблицу

Теперь заполним дополнительную таблицу формулой, которая будет транслировать исходные данные из основной таблицы, если соответствующий флажок валюты включен и связанная ячейка содержит слово ИСТИНА (TRUE):

Заметьте, что при использовании команды Форматировать как таблицу (Format as Table) на первом шаге, формула имеет использует имя таблицы и название колонки. В случае обычного диапазона, формула будет более привычного вида:

=ЕСЛИ(F$1;B4;#Н/Д)

Обратите внимание  на частичное закрепление ссылки на желтую ячейку (F$1), т.к. она должна смещаться вправо, но не должна – вниз, при копировании формулы на весь диапазон.

Теперь при включении-выключении флажков наша дополнительная таблица заполняется либо данными из исходной таблицы, либо искусственно созданной ошибкой #Н/Д, которая не дает линии на графике.

Шаг 4. Создаем полосы прокрутки для оси времени и масштабирования

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

Полосу прокрутки (Scroll bar) берем там же, где и флажки – на панели инструментов Формы (Forms) или на вкладке Разработчик (Developer):

Рисуем на листе в любом подходящем месте одну за другой две полосы – для сдвига по времени и масштаба:

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

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

Шаг 5. Создаем динамический именованный диапазон

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

• Отступом от начала таблицы вниз на заданное количество строк, т.е. отступом по временной шкале прошлое-будущее (синяя ячейка)
• Количеством ячеек по высоте, т.е. масштабом (зеленая ячейка)

Этот именованный диапазон мы позже будем использовать как исходные данные для построения диаграммы.

Для создания такого диапазона будем использовать функцию СМЕЩ (OFFSET) из категории Ссылки и массивы (Lookup and Reference) — эта функция умеет создавать ссылку на диапазон заданного размера в заданном месте листа и имеет следующие аргументы:

В качестве точки отсчета берется некая стартовая ячейка, затем задается смещение относительно нее на заданное количество строк вниз и столбцов вправо. Последние два аргумента этой функции – высота и ширина нужного нам диапазона. Так, например, если бы мы хотели иметь ссылку на диапазон данных с курсами за 5 дней, начиная с 4 января, то можно было бы использовать нашу функцию СМЕЩ со следующими аргументами:

=СМЕЩ(A3;4;1;5;2)

Хитрость в том, что константы в этой формуле можно заменить на ссылки на ячейки с переменным содержимым – в нашем случае, на синюю и зеленую ячейки. Сделать это можно, создав динамический именованный диапазон с функцией СМЕЩ (OFFSET). Для этого:

• В Excel 2007/2010 нажмите кнопку Диспетчер имен (Name Manager) на вкладке Формулы (Formulas)
• В Excel 2003 и старше – выберите в меню Вставка – Имя – Присвоить (Insert – Name – Define)

Для создания нового именованного диапазона нужно нажать кнопку Создать (Create) и ввести имя диапазона и ссылку на ячейки в открывшемся окне.

Сначала создадим два простых статических именованных диапазона с именами, например, Shift и Zoom, которые будут ссылаться на синюю и зеленую ячейки соответственно:    

Теперь чуть сложнее – создадим диапазон с именем Euros, который будет ссылаться с помощью функции СМЕЩ (OFFSET) на данные по курсам евро за выбранный отрезок времени, используя только что созданные до этого диапазоны Shift и Zoom и ячейку E3 в качестве точки отсчета:

Обратите внимание, что перед именем диапазона используется имя текущего листа – это сужает круг действия именованного диапазона, т.е. делает его доступным в пределах текущего листа, а не всей книги. Это необходимо нам для построения диаграммы в будущем. В новых версиях Excel для создания локального имени листа можно использовать выпадающий список Область.

Аналогичным образом создается именованный диапазон Dollars для данных по курсу доллара:

И завершает картину диапазон Labels, указывающий на подписи к оси Х, т.е. даты для выбранного отрезка:

Общая получившаяся картина должна быть примерно следующей:

Шаг 6. Строим диаграмму

Выделим несколько строк в верхней части вспомогательной таблицы, например диапазон E3:G10 и построим по нему диаграмму типа График (Line). Для этого в Excel 2007/2010 нужно перейти на вкладку Вставка (Insert) и в группе Диаграмма (Chart) выбрать тип График (Line), а в более старших версиях выбрать в меню Вставка – Диаграмма (Insert – Chart). Если выделить одну из линий на созданной диаграмме, то в строке формул будет видна функция РЯД (SERIES), обслуживающая выделенный ряд данных:

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

=РЯД(Лист1!$F$3;Лист1!$E$4:$E$10;Лист1!$F$4:$F$10;1)

на

=РЯД(Лист1!$F$3;Лист1!Labels;Лист1!Euros;1)

Выполнив эту процедуру последовательно для рядов данных доллара и евро, мы получим то, к чему стремились – диаграмма будет строиться по динамическим диапазонам Dollars и Euros, а подписи к оси Х будут браться из динамического же диапазона Labels. При изменении положения ползунков будут меняться диапазоны и, как следствие, диаграмма. При включении-выключении флажков – отображаться только те валюты, которые нам нужны.

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

Ссылки по теме

• Умные таблицы Excel 2007/2010

Хитрости »

22 Май 2017              32095 просмотров

Интерактивный график

Скачать пример из видеоурока:

  Интерактивная диаграмма.xls (47,5 KiB, 3 665 скачиваний)

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

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

Но что если необходимо показать динамику по каждой точке отдельно? Приведенный выше график не очень подходит для этой цели — слишком много лишних данных, в результате чего он смотрится довольно нагроможденно. Можно создать несколько одинаковых графиков, каждый из которых будет показывать данные по одной торговой точке. Это будет наглядно и удобно, если торговых точек 3-5. Но если их 10 и более, то такое нагромождение графиков мало того, что не наглядно — это еще весьма затратно по времени.
Поэтому, если есть необходимость показать динамику по отдельным торговым точкам, но при этом не обязательно делать много графиков, можно воспользоваться таким решением:

Скачать пример:

  Интерактивная диаграмма.xls (47,5 KiB, 3 665 скачиваний)

А теперь разберем, как это можно сделать.

• Для начала необходимо создать диаграмму нужного типа: выделяем диапазон A4:K5 -вкладка Вставка(Insert) -группа Диаграммы(Charts) —Вставить график или диаграмму с областями(Line) —График(Line)
• в удобном месте на основании названий торговых точек создаем обычный выпадающий список
  В файле примере список создан в ячейке B11: выделяем ячейку B11 -вкладка Данные(Data) —Проверка данных(Data Validation). В поле Тип данных(Allow) выбираем Список(List), в поле Источник(Source) указываем ссылку на диапазон с именами торговых точек: =$A$5:$A$9
• Теперь необходимо создать именованный диапазон, который в зависимости от выбранной в списке торговой точки сформирует диапазон данных для диаграммы. Переходим на вкладку Формулы(Formulas) —Диспетчер имен(Name Manager) —Создать(New). В поле Имя(Name) пишем: _forchart, а в поле Диапазон(Refers to) следующую формулу:
  =СМЕЩ($B$4:$K$4;ПОИСКПОЗ($B$11;$A$5:$A$9;0);)
  =OFFSET($B$4:$K$4,MATCH($B$11,$A$5:$A$9,0),)
  функция СМЕЩ(ссылка; смещ_по_строкам; смещ_по_столбцам; [высота]; [ширина]) — OFFSET
  берет ссылку на указанные ячейки и смещает эту ссылку на указанное количество строк и столбцов. В качестве ссылки указываем заголовок с датами из таблицы выручки: $B$4:$K$4
  ПОИСКПОЗ(MATCH) — эта функция берет ячейку $B$11 и ищет её в диапазоне $A$5:$A$9. Когда находит, то возвращает номер строки, в которой она находится в этом диапазоне. Т.е. для «К-р Октябрьский» это будет значение 1, для «ул.Ленина» — 2 и т.д.
  Это значит, что как только мы изменим значение в ячейке B11(а там у нас список торговых точек), то функция СМЕЩ сразу переопределит диапазон:
  =СМЕЩ($B$4:$K$4;ПОИСКПОЗ($B$11;$A$5:$A$9;0);) =>
  =СМЕЩ($B$4:$K$4;ПОИСКПОЗ(«ул.Фурманова»;$A$5:$A$9;0);) =>
  =СМЕЩ($B$4:$K$4;5;) =>
  =$B$9:$K$9

Остается только назначить этот диапазон созданной диаграмме: выделяем диаграмму -правый щелчок мыши —Исходные данные(Source data) или Выбрать данные(Select data)

выделяем единственный ряд в поле Элементы легенды(ряды)(Legeng Entries (Series)) и нажимаем Изменить(Edit)

в поле Значения(Series values) вписываем: =Interactive!_forchart

совет: изначально в этом поле записана прямая ссылка: =Interactive!$B$5:$K$5. Чтобы ничего не упустить надо просто заменить адреса ячеек на именованный диапазон, который создали ранее — _forchart: =Interactive!$B$5:$K$5 => =Interactive!_forchart
восклицательный знак и возможные другие знаки надо оставить.

Два раза нажимаем Ок. Все, наша диаграмма готова. Теперь можно выбирать название из выпадающего списка и диаграмма поменяет свои значения на продажи выбранной торговой точки.
Скачать пример:

  Интерактивная диаграмма.xls (47,5 KiB, 3 665 скачиваний)

Так же см.:
Два в одном — как сделать?
Динамическая диаграмма
Ступенчатый график в Excel

Статья помогла? Поделись ссылкой с друзьями!

    Видеоуроки

Поиск по меткам



Access
apple watch
Multex
Power Query и Power BI
VBA управление кодами
Бесплатные надстройки
Дата и время
Записки
ИП
Надстройки
Печать
Политика Конфиденциальности
Почта
Программы
Работа с приложениями
Разработка приложений
Росстат
Тренинги и вебинары
Финансовые
Форматирование
Функции Excel
акции MulTEx
ссылки
статистика

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

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

Создайте динамическую интерактивную диаграмму с помощью раскрывающегося списка

Создайте динамическую интерактивную диаграмму с помощью переключателей

Создайте динамическую интерактивную диаграмму с помощью флажков

Создайте динамическую интерактивную диаграмму с помощью раскрывающегося списка

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

1. Во-первых, вы должны вставить форму раскрывающегося списка, пожалуйста, нажмите Застройщик > Вставить > Поле со списком (элемент управления формой), а затем нарисуйте поле со списком, как показано ниже:

2. Затем щелкните правой кнопкой мыши поле со списком и выберите Управление форматом из контекстного меню см. снимок экрана:

3. В Форматировать объект диалоговое окно под Control
Вкладка:

• В разделе Диапазон ввода текстовое поле, выберите диапазон данных в таблице данных, исключите заголовок столбца (A2: I6);
• В разделе Сотовая ссылка текстовое поле, выберите пустую ячейку, предназначенную для хранения вывода выпадающего меню (B8);
• В разделе Выпадающие строки введите количество точек данных в вашем наборе данных (в данном случае 5).

4, Затем нажмите OK кнопка, теперь названия продуктов были добавлены в раскрывающийся список, а значение связанной ячейки (B8) характеризует текущий выбранный элемент из меню как вариант 1, 2, 3, 4 или 5, см. снимок экрана:

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

6. Введите следующую формулу в A12 и скопируйте ее в I12, и она вернет значение в строке, номер которой соответствует текущему выбранному элементу в раскрывающемся списке, см. Снимок экрана:

=INDEX(A2:A6, $B$8)

Внимание: В формуле A2: A6 это данные первого столбца ваших исходных данных, и B8 это ячейка, связанная с полем со списком.

7. На этом шаге вы можете создать диаграмму на основе новых вспомогательных данных (A11: I12), выберите эти данные, а затем вставьте диаграмму, как вам нужно, см. Снимок экрана:

8. Наконец, если вы хотите поместить раскрывающийся список в верхней части диаграммы, щелкните правой кнопкой мыши поле со списком и выберите Оформить заказ > На передний план, см. снимок экрана:

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

Создайте динамическую интерактивную диаграмму с помощью переключателей

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

1. Сначала вставьте переключатели, нажмите Застройщик > Вставить > Кнопка выбора (элемент управления формой), см. снимок экрана:

2. А затем нарисуйте несколько переключателей на основе названий ваших продуктов, здесь я нарисую 5 переключателей и переименую их в названия продуктов, см. Снимок экрана:

3. Затем щелкните правой кнопкой мыши любой из переключателей и выберите Управление форматом, см. снимок экрана:

4. В выскочившем Форматировать объект диалоговое окно под Control
На вкладке щелкните пустую ячейку, чтобы назначить сохранение вывода переключателя (B8). Смотрите скриншот:

5. Теперь значение связанной ячейки (B8) характеризует текущий выбранный переключатель как вариант 1, 2, 3, 4 или 5, см. Снимок экрана:

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

7. Затем введите следующие формулы, и вы получите следующие данные, см. Снимок экрана:

In cell B12: =IF($B$8=1,B2,NA()), and copy this formula into the entire row;
In cell B13: =IF($B$8=2,B3,NA()), and copy this formula into the entire row;
In cell B14: =IF($B$8=3,B4,NA()), and copy this formula into the entire row;
In cell B15: =IF($B$8=4,B5,NA()), and copy this formula into the entire row;
In cell B16: =IF($B$8=5,B6,NA()), and copy this formula into the entire row.

Внимание: В приведенных выше формулах B8 это ячейка, связанная с переключателем, 1,2,3,4,5 в каждой формуле означает число, связанное с переключателями, B2, B3, B4, B5, B6 в каждой формуле указывает первые данные в каждой строке, которые вы хотите отобразить из исходной таблицы.

8. После подготовки данных выберите новый диапазон данных (A11: I16) и вставьте диаграмму, как вам нужно, и теперь интерактивная диаграмма полностью упакована, при выборе одного переключателя будет отображаться соответствующая серия данных. в диаграмме, как показано ниже:

Создайте динамическую интерактивную диаграмму с помощью флажков

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

Примечание:Чтобы применить это График с флажком, во-первых, вы должны скачать Kutools for Excel, а затем быстро и легко примените эту функцию.

После установки Kutools for Excel, пожалуйста, сделайте так:

1. Нажмите Кутулс > Графики > Сравнение категорий > График с флажком, см. снимок экрана:

2. В График с флажком диалоговом окне выполните следующие операции:

• В разделе Диапазон данных в поле выберите ряд данных, которые будут отображаться на диаграмме;
• В разделе Ярлыки осей поле выберите данные меток оси;
• В разделе Легендарные записи (серия) выберите данные, которые будут отображаться в качестве легенды диаграммы (где отображаются флажки).

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

Больше относительных статей:

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

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

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

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

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

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

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

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

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

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

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



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

Теперь нам необходимо сделать сложную фигуру. Для этого нам понадобиться программный инструмент – PowerPoint , который также входит в пакет MS Office. В нем для создания фигур предусмотрена очень полезная опция «Слияние фигур», которой нет в Excel или Word. Откройте программу PowerPoint из пакета офисных программ MS Office и выберите: «ВСТАВКА»-«Иллюстрации»-«Фигуры»-«Пятиугольник»:

Создаем 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» ThenFor 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. Теперь Ваши отчеты и труды бут привлекать к себе больше внимания.

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

Описание проблемы

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

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

Создание динамической диаграммы

В первую очередь необходимо создать выпадающий список, откуда мы будем выбирать, интересующий нас, показатель. Переходим по вкладке Разработчик в группу Элементы управления, выбираем Вставить –> Элементы управления формы –> Поле со списком.

После того, как вы щелкните по иконке Поле со списком, ваш курсор превратится в перекрестье, это означает, что Excel ожидает указание места, куда необходимо разместить элемент управления. Щелкните в то место на рабочем листе, где вы хотите разместить выпадающий список. Вы увидите большое квадратное поле с треугольником внутри – это и есть первоначальный вид поля со списком. Задайте вашему элементу требуемые размеры, используя маркеры, находящиеся по краям элемента.

Щелкните правой кнопкой мыши по выпадающему списку, выберите Формат объекта. В появившемся диалоговом окне Формат элемента управления, задайте диапазон ячеек, откуда будет формироваться список (в нашем случае, это список всех показателей, по которым мы будем строить график), и ячейку, куда будет помещаться результат выбора из списка.

Далее необходимо создать два именованных диапазона. Первый будет отвечать за значения ряда данных, второй за название. В зависимости от выбора элемента из списка, оба диапазона будут менять свои адреса.

Переходим по вкладке Формулы в группу Определенные имена, выбираем Диспетчер имен и создаем два диапазона с именами значения и название с соответствующими формулами.

=СМЕЩ(ДинамДиагр!$A$4;ДинамДиагр!$A$16;1;;7)

=ИНДЕКС(ДинамДиагр!$A$5:$A$14;ДинамДиагр!$A$16)

На рабочем листе с таблицей с данными выбираем диапазон A1:H2, переходим по вкладке Вставка в группу Диаграммы, выбираем Диаграмму с областями. Excel построил нам диаграмму с одним рядом данных, как мы его и просили.

Щелкаем по ряду данных в области диаграммы. В строке формул вы увидите функцию формирующую ряд данных диаграммы. Где первый параметр отвечает за название ряда, второй – за ось категорий, третий — это значения нашего ряда данных.

Меняем значения первого и третьего параметра на уже подготовленные именованные диапазоны

=РЯД(ДинамДиагр!$A$2;ДинамДиагр!$B$1:$H$1;ДинамДиагр!$B$2:$H$2;1)

Должно получиться так:

=РЯД(ДинамДиагр!название;ДинамДиагр!$B$1:$H$1;ДинамДиагр!значения;1)

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

Осталось задать привлекательный формат нашей диаграмме. Убираем все лишние элементы: линии сетки и название диаграммы. Меняем цвет ряда данных, добавляем к нему линии проекции. Задаем цвет области построения и области диаграммы.

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

Динамическая диаграмма готова.

Скачать файл с примером динамической диаграммы в Excel.

Перейти к содержимому