Как сделать красивый график в excel

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

Создание оригинальных, интерактивных и динамических графиков с анимацией в 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» 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 Formula Examples
• Создать таблицу
• Форматирование
• Функции Excel
• Формулы и диапазоны
• Фильтр и сортировка
• Диаграммы и графики
• Сводные таблицы
• Печать документов
• Базы данных и XML
• Возможности Excel
• Настройки параметры
• Уроки Excel
• Макросы VBA
• Скачать примеры

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

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

Сделать диаграммы привлекательными гораздо проще, чем вы думаете. Получить рекомендации на все случаи жизни не выйдет, но освоить несколько приемов в Excel и узнать азы теории, вы сможете за 10 минут.

Из тридцатилетнего опыта и десятков прочитанных книг я выбрал семь полезных приемов. Их мы и разберем в этой статье в блоге ЛАНИТ.

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

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

Я также подготовил файл, где можно повторить все описанные выше приемы и поэкспериментировать.

Прием 1. Уменьшите боковой зазор

Выделите диапазон А1:В13 и пройдите по меню Вставка –> Рекомендуемые диаграммы –> Гистограмма с группировкой. Нажмите Ok.

Правой кнопкой мыши кликните на столбце диаграммы, выберите Формат ряда данных. Уменьшите Боковой зазор. Мне нравятся диаграммы с боковым зазором в 30%. Поэкспериментируйте.

Боковой зазор в процентах измеряет отношение ширины зазора и столбца. При 0% столбцы сольются, при 100% ширина зазора и столбца равны.

• Близость.Группируйте связанные элементы. Если несколько разрозненных объектов расположить близко друг к другу, они создадут визуальное целое.
• Выравнивание.Ни один элемент не может располагаться произвольно. Элементы должны иметь визуальную связь.
• Повторение.Повторяйте элементы верстки во всем материале: цвет, форму, пространственные связи, толщину линий. Вы наведете порядок и добьетесь единообразия.
• Контраст.Избегайте элементов с незначительным отличием. Если шрифт, цвет, кегль, толщина линии не одинаковы, сделайте их очень разными.

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

Прием 2. Удалите лишние элементы, используйте подписи вместо шкалы

Excel по умолчанию снабжает диаграммы атрибутами. Но не все элементы одинаково полезны, и работают на донесение идей до аудитории. Вернитесь к диаграмме на рис. 3, последовательно выделите и безжалостно удалите вертикальную ось и линии сетки. Уменьшите кегль заголовка, сделайте шрифт полужирным и выключите влево – по левой границе первого столбца диаграммы (см. ниже рис. 6). Используйте принцип выравнивания Робин Уильямс.

Правой кнопкой мыши кликните на столбце и выберите Добавить подписи данных. Кликните на подпись и на панели Формат подписей данных установите Положение подписи – У вершины, внутри. Также установите Числовую категорию для формата подписи и Число десятичных знаков 0.

Еще раз правой кнопкой мыши кликните на подпись и выберите Шрифт. Увеличьте кегль и задайте белый цвет.

Сравните с исходной диаграммой.

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

Тафти не разрешает переводить свои книги, поэтому в Интернете можно найти лишь неофициальные переводы.

Прием 3. Сделайте заголовки говорящими

Часто диаграмму оставляют без заголовка или используют описательный заголовок. А в выступлении или тексте документа дают пояснения. Но диаграмма не должна требовать сопроводительного текста. Диаграмма должна заменять текст. Сравните:

… и не набирайте текст прописными (заглавными) буквами.

Теория. Джин Желязны (1934–2023) много лет проработал директором по визуальным коммуникациям консалтинговой фирмы McKinsey & Company. Представляете, как ценилась визуализация, если была такая должность? Джин издал несколько книг, и самой известной стала Говори на языке диаграмм. В ней масса интересных идей, и одна из них – говорящие заголовки.

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

Прием 4. Цвета для подобного берите из одного ряда палитры

Добавим в пример данные за предыдущий год:

Поскольку столбцов стало больше, оставьте шкалу ординат, чтобы не перегружать диаграмму обилием подписей. Удалите линии сетки. На шкалу ординат добавьте линию, подпись и деления, уберите десятичный знак. Разместите подпись шкалы справа горизонтально. Уменьшите боковой зазор до 30%. Увеличьте перекрытие рядов до 50%. Выберите цвета из одного ряда палитры: для прошлого года – менее интенсивный. Уменьшите кегль заголовка, выключите влево, замените на говорящий. Перенесите легенду вверх. Выровняйте по левому краю.

Прием 5. Идите от идеи к виду сравнения и далее к типу диаграммы

1. Сформулируйте идею (от данных к идее), которую хотите донести при помощи визуализации.
2. Определите вид сравнения (от идеи к сравнению): покомпонентное, позиционное, временное, частотное или корреляционное.
3. Выберите тип диаграммы (от сравнения к диаграмме).

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

Покомпонентное сравнение показывает вклад части в целое. Если элементов немного, то постройте круговую диаграмму. Если элементов более 5–7, возьмите линейчатую диаграмму. Круговая диаграмма хуже отражает отношение частей, зато показывает, что части образуют целое. Линейчатая диаграмма лучше выявляет отношение частей, но теряет ощущение целого. Различные идеи требуют разных диаграмм:

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

Вернитесь к доле мониторов на рис. 1, но оставьте только данные за Q1-2023.

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

Так будет лучше:

Частотное сравнение показывает, сколько значений параметра попадает в последовательные диапазоны. Исследуя численность городов России с помощью Power Query импортируйте данные из Википедии в Excel и постройте статистическую диаграмму:

В РФ по состоянию на 2023 г. 170 городов с населением более 100 000 человек. В том числе 16 городов-миллионников.

В статистической диаграмме боковой зазор равен 0. Это подчеркивает, что данные непрерывны, а разбиение на диапазоны условно.

Обратите внимание, в Excel два типа диаграмм названы гистограммами:

На рассмотренных диаграммах использовались ось категорий (Х) и ось значений (Y).

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

Я увлекаюсь игрой Fantasy Premier League (FPL). Изучая качество предсказательной модели, нанес на точечную диаграмму значения предсказанных и полученных очков в сезоне 2022/23.

Также добавил линию регрессии и коэффициент детерминации. R 2 равный 0,7 означает, что 70% изменчивости зависимой переменной – набранных очков – можно объяснить предложенной линейной моделью.

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

Вид сравнения

На какой вопрос отвечает

Тип диаграммы

Какой вклад частей в целое

Каковы различия между категориями

Временное

Как изменяются данные во времени

Как значения параметра распределены по диапазонам

Есть ли зависимость между двумя переменными

Рис. 19. Обзор видов сравнения и типов диаграмм

Прием 6. Возьмите подписи из ячеек листа

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

Продолжите исследование городов России. Постройте точечную диаграмму в двойных логарифмических координатах и добавьте на нее линию регрессии:

Некоторые точки отклоняются от прямой, что говорит о нарушении закона Ципфа. Прежде чем презентовать результаты, добавьте на диаграмму подписи. Введите в ячейки листа текст с названием осей. Выделите диаграмму. Пройдите Конструктор диаграмм → Добавить элемент диаграммы → Название осей → Основная вертикальная. Выделите на диаграмме блок Название оси. Кликните в строке формул, введите знак =. Выберите ячейку Е16. Нажмите Enter, в названии оси отразится текст из ячейки. Затем добавьте название горизонтальной оси.

Названия городов есть в столбце А, но для добавления на диаграмму их слишком много. В столбце С оставьте названия, которые хотите поместить на диаграмму. Кликните правой кнопкой мыши на любой точке диаграммы и выберите Добавить подписи данных. В меню Формат подписей данных, установите галочку Включить в подписи значения из ячеек. Выберите диапазон ячеек в столбце С. Нажмите Ok. Руками подвигайте каждую отдельную подпись, чтобы улучшить читаемость.

Закон Ципфа гласит, что произведение численности населения города на его ранг есть величина постоянная. Этот закон в двойных логарифмических координатах описывается прямой линией. То, что ряд городов-миллионников на графике лежат ниже прямой, говорит о недостаточной их развитости… Или о том, что часть жителей «перетянули» на себя две столицы.

Прием 7. Для сравнения нескольких категорий во времени используйте график

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

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

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

Скопируйте формат понравившейся диаграммы

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

Анализируя выбор элиты и поляны в Fantasy Premier League, вы построили диаграмму для защитников. Переходя к полузащитникам, создайте диаграмму по умолчанию.

Выделите диаграмму-образец, нажмите Ctrl+C. Выделите диаграмму по умолчанию, пройдите Главная –> Вставить –> Специальная вставка –> Форматы.

Нажмите Ok. Вуаля!

Резюме

Итак, вот все перечисленные выше приемы, которые мы рассмотрели.

1. Уменьшите боковой зазор между столбиками гистограммы.
2. Удалите лишние элементы, используйте подписи данных вместо шкалы.
3. Используйте говорящие заголовки.
4. Цвета для подобного берите из одного ряда палитры.
5. Идея –> Вид сравнения –> Тип диаграммы.
6. Возьмите подписи из ячеек листа.
7. Для сравнения нескольких категорий во времени используйте график.

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

• «‎Дизайн. Книга для недизайнеров», Робин Уильямс;
• «Говори на языке диаграмм», Джин Желязны;
• «Искусство визуализации в бизнесе», Нейтан Яу;
• «Графики, которые убеждают всех», Александр Богачев.

*Статья написана в рамках Хабрачелленджа 0.1, который прошел в ЛАНИТ осенью 2023 года

12 нестандартных диаграмм Excel

В этой статье собрали некоторые необычные диаграммы Excel со ссылками на краткие инструкции по их построению.

Диаграмма по мотивам Wall Street Journal

Какое-то время назад для того, чтобы показать неограниченность рисования в Excel, делал ряд статей, а файлы Excel к ним не прикладывал. Решил, что пора «рассекретить» хитрые диаграммы. Встретил диаграмму, созданную дизайнерами Wall Street Journal. И тут же ее воспроизвел в Excel.

Диаграмма темпов роста в Excel

Как в Excel создать диаграмму с динамикой темпов роста, где изменения показателей показаны стрелками? Все просто — рисуем столбцы и добавляем к ним полосы повышения и понижения. Плюс рисуем графики с накоплением — первый для роста, второй график — уровень подписи.

Диаграмма сравнения роста показателей

Как в Excel показать сравнить рост двух различных показателей? Можно «просто» сравнить данные. Правда, если они не сопоставимы, то особо ничего не увидишь. Но есть еще один способ, пожалуй, самый наглядный: методом базисной подстановки, который любит The Economist.

Диаграмма ABC-анализа

Про ABС-анализ слышали, наверное, все. Методику анализа приводить не буду. Привожу решение с визуализацией по мотивам журнала The Economist.

График структуры и перехода

Какое-то время назад в журнале The Economist увидел диаграмму с переходами по структуре от периода к периоду и сделал её в Excel. А недавно и для проекта в Power BI вместе с ABC-анализом. Файл Excel прикладываю для изучения.

Диаграмма с горизонтальной «зеброй»

Как построить в Excel диаграмму журнального качества с зеброй вместо горизонтальных линий? Есть несколько способов, с одним из которых можно ознакомиться благодаря приложенному файлу. Диаграмма построена по мотивам Wall Street Journal.

Как цветом показать на графике рост или падение последнего значения

Итак, задача: показать тенденцию последнего месяца (квартала, года, периода). В стандартных графиках можно настроить такое представление, чтобы на конце линии была красная стрелка, если у нас спад, и зеленая — если подъем.

Диаграмма по мотивам The Economist

Обнаружил в журнале The Economist за октябрь 2014 года интересную гистограмму с меткой на столбцах. Тут же ее воспроизвел в Excel — вроде все получилось, кроме шрифтов.

Линейчатая диаграмма с эффектами

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

Диаграмма в виде колб

Как-то давно я делал отчет с инфографикой. Первая колба — год, наполнение — сколько дней в году прошло. Следующие колбы — это контрольные показатели. 100% — план за год, наполнение — как выполнен факт. Смотрим, сколько прошло времени и как мы сработали.

Спарклайны и микрографики

Как мы делаем отчеты? В виде здоровых (и нездоровых, кстати, тоже) таблиц. Что из них понятно людям, имеющим малый опыт работы с большими объемами данных? Ничего. Далее рассмотрен пример, как показать структуру продаж за месяц и за год, и тут же показать динамику по месяцам и годам.

Полоса прокрутки в Excel

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

Диаграмма темпов роста в Excel

Как в Excel создать диаграмму с динамикой темпов роста, где изменения данных показаны стрелками?

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

Подготовили для вас подробные пошаговые инструкции. Решили, что вам будет удобнее сразу на листе Excel повторять все шаги и строить диаграмму.
Скачивайте и применяйте!

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

Если тема про диаграммы вам интересна, смотрите статьи:

• Диаграммы Excel для презентаций
• Необычные диаграммы с точками
• Необычные диаграммы с областями
• Необычные пузырьковые и точечные диаграммы
• Диаграммы с группировкой
• Динамика плана и факта
• Нестандартные диаграммы Excel

Шаг 1. Добавить вычисления.

Добавим в таблицу строку «подписи» — с суммой немного больше исходного значения.

И строку «рост», где будет рассчитан прирост выручки к предыдущему периоду.
В первой колонке проставляем #Н/Д для того, чтобы не значения этого столбца не выводились в диаграмме.

Шаг 2. Создать диаграмму.

Чтобы создать диаграмму, выделите таблицу и выберите в меню Вставка → Гистограмма → Гистограмма с накоплением.

Шаг 3. Рост и подписи превратить в график

Выделите диаграмму мышкой, затем перейдите в меню Конструктор → Изменить тип диаграммы. Выберите тип диаграммы «Комбинированная». Для данных «подписи» и «рост» задаем тип диаграммы «График с накоплением». Благодаря этому график с малыми значениями наложится на график с большими значениями.

Шаг 4. Добавить подписи для линии роста

Добавляем подписи для линии роста: выделите на диаграмме линию роста.

Выберите в меню Конструктор → Добавить элемент диаграммы → Метки данных → выбираем Слева.

Делаем линии «рост» и «подписи» на диаграмме невидимыми: выделите линию правой кнопкой мышки, нажимаем Формат ряда данных. Назначаем тип линии = Нет линий.

Шаг 5. Добавить линии ряда данных

Чтобы добавить линии между столбцами, выделите столбцы гистограммы.

Перейдите в меню Конструктор → Добавить элемент диаграммы → Линии → Линии ряда данных.

* Если такая линия не появилась, проверьте, правильный ли у вас тип диаграммы — должна быть Гистограмма с накоплением.