Автоматическое создание графиков в excel
Одним из революционных нововведений в крайней версии Microsoft Excel 2010 стали инфокривые sparklines - миниграфики, помещающиеся внутри ячеек и наглядно отображающие динамику числовых данных:
Чтобы создать подобные миниграфики, нужно выделить ячейки, куда мы хотим их поместить и воспользоваться кнопками группы Спарклайны (Sparklines) с вкладки Вставка (Insert) :
В открывшемся диалоговом окне нужно задать диапазон исходных данных и диапазон вывода:
Созданные миниграфики можно всячески форматировать и настраивать с помощью динамической вкладки Конструктор (Design) :
В частности, можно легко поменять цвет линий и столбцов спарклайна и выделить особыми цветами минимальные и максимальные значения:
Поскольку спарклайн не является отдельным графическим объектом, а выступает, по сути, в роли фона ячейки, то он нисколько не помешает ввести в ячейку текст, числа или другую информацию. Это открывает простор для фантазии - если немного пошаманить с выравниванием введенного текста, шириной и высотой ячейки, то можно достичь интересных результатов:
Что делать, если у вас пока старая версия Excel? Или нужен тип графика, которого нет в наборе спарклайнов? Переходим к следующим способам!
Способ 2. Дополнительные надстройки для микрографиков
На самом деле, идея подобных графиков витала в воздухе уже достаточно давно. Еще для 2003 версии Excel существовало несколько надстроек с подобным функционалом, самым известными из которых были замечательная бесплатная Sparklines Эдварда Тафта и и платные BonaVista microcharts (169$) и Bissantz SparkMaker (200$). Да и я в свое время тоже пытался сделать что-то подобное в надстройке PLEX.
Если брать в качестве примера бесплатную надстройку Sparklines, то после ее подключения мы получим новую вкладку (или панель инструментов) примерно такого вида:
Как легко заметить, эта надстройка умеет строить заметно больше трех типов диаграмм :)
Единственный минус - надстройку нужно будет установить на всех компьютерах, где вы планируете работать с файлом, содержащим такие диаграммы.
Способ 3. Повтор символов N-раз
"Бюджетным вариантом" одномерных микрографиков являются повторяющиеся однотипные символы, имитирующие линейчатую диаграмму. Для этого можно воспользоваться текстовой функцией ПОВТОР (REPT) , которая умеет выводить в ячейку любой заданный символ нужное количество раз. Для вывода нестандартных символов (зная их код) можно использовать функцию СИМВОЛ (CHAR) . В общем и целом это выглядит так:
Символ с кодом 103 - черный прямоугольник шрифта Webdings, поэтому не забудьте установить этот шрифт для ячеек C2:C12. Также можно поиграться с символами других шрифтов, например в столбце Е использован символ с кодом 110 из шрифта Wingdings.
Способ 4. Макросы
Способ представляет собой улучшенный предыдущий вариант, где набор повторяющихся символов (используется знак "|") создает не формула, а простая пользовательская функция на VBA. Причем для каждой ячейки создается отдельный столбец, т.к. функция использует символ переноса строки после каждого числа - выглядит это примерно так:
Чтобы использовать этот трюк у себя в файле, открываем редактор VBA (Alt+F11), добавляем новый модуль в книгу (меню Insert - Module) и копируем туда код функции NanoChart:
Затем вставляем функцию NanoChart в нужные ячейки, указывая в качестве аргументов числовые данные, как на рисунке выше. Для получившихся ячеек с микрографиками необходимо включить перенос по словам и поворот на 90 градусов через меню Формат - Ячейки - Выравнивание (Format - Cells - Alignment) . Константа MaxSymbols задает длину максимально высокого столбца в минигистограмме.
Теперь в мастере функций в категории Определенные пользователем появилась новая функция LineChart c двумя аргументами - диапазоном и кодом цвета графика. Если ее вставить в пустую ячейку, например, справа от числовой строки и скопировать затем, как обычно, на весь столбец, то получим весьма симпатичное отображение числовых данных в виде миниграфиков:
В новых версиях Excel разработчики максимально автоматизировали процесс создания диаграмм и графиков. В следующих примерах данного урока мы рассмотрим базовые возможности Excel в области создания диаграмм и графиков.
Как быстро создать диаграмму или график
Создайте таблицу, по которой нужно будет создать графическое отображение ее содержимого:
Теперь мы выполним несколько простых действий:
- создадим гистограмму;
- изменим на ней размер шрифта;
- проверим динамическое изменение на графике в зависимости от изменения данных в таблице.
Для начала работы выделите диапазон A1:C4 и выполняйте следующие действия:
- Нажмите клавишу F11, после чего у вас автоматически создастся диаграмма на отдельном листе. Так же автоматически активизируется панель инструментов: «Работа с диаграммами»-«Конструктор».
- Измените тип диаграммы на: «Гистограмма»-«Гистограмма с группировкой».
- Щелкните правой кнопкой мышки по легенде и выберите опцию «Шрифт» в контекстном меню. В появившемся окне укажите больший размер шрифта (например, 12) и нажмите ОК.
- Выберите инструмент «Конструктор»-«Расположение»-«Переместить диаграмму». В появившемся диалоговом окне: «Перемещение диаграммы» выберите опцию «на имеющемся листе», а из выпадающего списка укажите лист где размещена таблица с исходными данными «Лист1» и нажмите ОК.
График переместился на лист, где находятся его исходные данные. Теперь проверим их связь между собой. Для этого сделайте любые изменения в значениях таблицы. Например, измените размер выручки Магазина 2 в понедельник. После нажатия клавиши Enter график динамически изменяет отображение значений в соответствии с новыми значениями в таблице.
Для следующего примера удалите текущий график. Для этого щелкните по графику, чтобы активировать его и нажмите клавишу «DELETE» на клавиатуре.
Примечание. Обратите внимание, что при переносе графика с отдельного листа, сам лист «Диаграмма1» удаляется автоматически.
Как изменить оси в графике Excel
Снова выделите диапазон A1:C4 и нажмите комбинацию горячих клавиш для создания графика ALT+F1. Снова в конструкторе измените тип диаграммы на гистограмму с группировкой. В результате у нас по оси X отображаются магазины, а нужно там отобразить дни недели.
Теперь проследим одну важную особенность автоматического создания диаграмм и графиков:
- Удалите текущую гистограмму. Щелкните по ней левой кнопкой мышки и нажмите клавишу «DELETE» на клавиатуре.
- Измените таблицу, добавив все рабочие дни недели.
- Выделите диапазон A1:F4 и снова нажмите комбинацию клавиш ALT+F1.
Теперь при создании график поменял значения на осях X и Y. Возникает вопрос, почему так ведет себя Excel? Ответ очень прост: Excel всегда распределяет данные на графике так, чтобы большее количество данных приходилось на ось X, а меньшее – на ось Y соответственно.
Внимание! При необходимости всегда можно вручную задать какие данные на каких осях графика должны находиться. Для этого в конструкторе выберите инструмент «Строка/столбец» или воспользуйтесь инструментом «Выбрать данные».
В данном примере только разъясняется логическое поведение Excel при автоматическом создании графиков с использованием шаблонов по умолчанию.
Возможности диаграмм и графиков в Excel
Визуальный анализ данных записанных в таблицах Excel это утомительный и рутинный рабочий процесс. Как не парадоксально, но в большинстве случаев нас интересует не детали отчетов, а общее представление текущей ситуации и тенденции ее изменения. Из отчетов в таблицах Excel мы хотим быстро и легко получать разного рода важную информацию о бизнесе:
- какие ценности растут, какие снижаются;
- в каком месяце доходы были самые высокие, а в каком низкие;
- на какой продукт магазин тратит наибольшую часть вложенных средств и т.п.
Естественно значительно удобнее читать, понять и воспринять такого рода информацию из графиков, чем из таблиц.
С каждой новой версией Excel появляются все новые и усовершенствованные инструменты для создания и редактирования диаграмм и графиков. В один клик мышкой можно изменять типы диаграмм и стили их оформления. Теперь нет необходимости шаг за шагом выстраивать строить графики. Достаточно только выделить диапазон ячеек с исходными данными и выбрать готовый базовый шаблон графика, который потом легко преобразовать и настроить под свои потребности. Данный метод создания графиков и диаграмм удовлетворяет до 90% потребностей пользователей. Для особенных задач присутствует возможность детального разбора графического представления информации. Если часто приходится сталкиваться с уникальными задачами, то можно создать свои пользовательские шаблоны графиков или диаграмм. Для типичных наиболее часто решаемых задач стоит использовать базовые шаблоны, предлагаемые программой по умолчанию.
Часто-используемые возможности диаграмм и графиков рассмотрим на следующих уроках с полезными, практическими примерами.
Информация воспринимается легче, если представлена наглядно. Один из способов презентации отчетов, планов, показателей и другого вида делового материала – графики и диаграммы. В аналитике это незаменимые инструменты.
Построить график в Excel по данным таблицы можно несколькими способами. Каждый из них обладает своими преимуществами и недостатками для конкретной ситуации. Рассмотрим все по порядку.
Простейший график изменений
График нужен тогда, когда необходимо показать изменения данных. Начнем с простейшей диаграммы для демонстрации событий в разные промежутки времени.
Допустим, у нас есть данные по чистой прибыли предприятия за 5 лет:
Год | Чистая прибыль* |
2010 | 13742 |
2011 | 11786 |
2012 | 6045 |
2013 | 7234 |
2014 | 15605 |
Заходим во вкладку «Вставка». Предлагается несколько типов диаграмм:
Выбираем «График». Во всплывающем окне – его вид. Когда наводишь курсор на тот или иной тип диаграммы, показывается подсказка: где лучше использовать этот график, для каких данных.
Выбрали – скопировали таблицу с данными – вставили в область диаграммы. Получается вот такой вариант:
Прямая горизонтальная (синяя) не нужна. Просто выделяем ее и удаляем. Так как у нас одна кривая – легенду (справа от графика) тоже убираем. Чтобы уточнить информацию, подписываем маркеры. На вкладке «Подписи данных» определяем местоположение цифр. В примере – справа.
Улучшим изображение – подпишем оси. «Макет» – «Название осей» – «Название основной горизонтальной (вертикальной) оси»:
Заголовок можно убрать, переместить в область графика, над ним. Изменить стиль, сделать заливку и т.д. Все манипуляции – на вкладке «Название диаграммы».
Вместо порядкового номера отчетного года нам нужен именно год. Выделяем значения горизонтальной оси. Правой кнопкой мыши – «Выбрать данные» - «Изменить подписи горизонтальной оси». В открывшейся вкладке выбрать диапазон. В таблице с данными – первый столбец. Как показано ниже на рисунке:
Можем оставить график в таком виде. А можем сделать заливку, поменять шрифт, переместить диаграмму на другой лист («Конструктор» - «Переместить диаграмму»).
График с двумя и более кривыми
Допустим, нам нужно показать не только чистую прибыль, но и стоимость активов. Данных стало больше:
Но принцип построения остался прежним. Только теперь есть смысл оставить легенду. Так как у нас 2 кривые.
Добавление второй оси
Как добавить вторую (дополнительную) ось? Когда единицы измерения одинаковы, пользуемся предложенной выше инструкцией. Если же нужно показать данные разных типов, понадобится вспомогательная ось.
Сначала строим график так, будто у нас одинаковые единицы измерения.
Выделяем ось, для которой хотим добавить вспомогательную. Правая кнопка мыши – «Формат ряда данных» – «Параметры ряда» - «По вспомогательной оси».
Нажимаем «Закрыть» - на графике появилась вторая ось, которая «подстроилась» под данные кривой.
Это один из способов. Есть и другой – изменение типа диаграммы.
Щелкаем правой кнопкой мыши по линии, для которой нужна дополнительная ось. Выбираем «Изменить тип диаграммы для ряда».
Определяемся с видом для второго ряда данных. В примере – линейчатая диаграмма.
Всего несколько нажатий – дополнительная ось для другого типа измерений готова.
Строим график функций в Excel
Вся работа состоит из двух этапов:
- Создание таблицы с данными.
- Построение графика.
Пример: y=x(√x – 2). Шаг – 0,3.
Составляем таблицу. Первый столбец – значения Х. Используем формулы. Значение первой ячейки – 1. Второй: = (имя первой ячейки) + 0,3. Выделяем правый нижний угол ячейки с формулой – тянем вниз столько, сколько нужно.
В столбце У прописываем формулу для расчета функции. В нашем примере: =A2*(КОРЕНЬ(A2)-2). Нажимаем «Ввод». Excel посчитал значение. «Размножаем» формулу по всему столбцу (потянув за правый нижний угол ячейки). Таблица с данными готова.
Переходим на новый лист (можно остаться и на этом – поставить курсор в свободную ячейку). «Вставка» - «Диаграмма» - «Точечная». Выбираем понравившийся тип. Щелкаем по области диаграммы правой кнопкой мыши – «Выбрать данные».
Выделяем значения Х (первый столбец). И нажимаем «Добавить». Открывается окно «Изменение ряда». Задаем имя ряда – функция. Значения Х – первый столбец таблицы с данными. Значения У – второй.
Жмем ОК и любуемся результатом.
Наложение и комбинирование графиков
Построить два графика в Excel не представляет никакой сложности. Совместим на одном поле два графика функций в Excel. Добавим к предыдущей Z=X(√x – 3). Таблица с данными:
Выделяем данные и вставляем в поле диаграммы. Если что-то не так (не те названия рядов, неправильно отразились цифры на оси), редактируем через вкладку «Выбрать данные».
А вот наши 2 графика функций в одном поле.
Графики зависимости
Данные одного столбца (строки) зависят от данных другого столбца (строки).
Построить график зависимости одного столбца от другого в Excel можно так:
Условия: А = f (E); В = f (E); С = f (E); D = f (E).
Выбираем тип диаграммы. Точечная. С гладкими кривыми и маркерами.
Выбор данных – «Добавить». Имя ряда – А. Значения Х – значения А. Значения У – значения Е. Снова «Добавить». Имя ряда – В. Значения Х – данные в столбце В. Значения У – данные в столбце Е. И по такому принципу всю таблицу.
Готовые примеры графиков и диаграмм в Excel скачать:
Как сделать еженедельный график в Excel вместе с ежедневным.
Пример создания динамического синхронного еженедельного графика вместе с ежедневным. Синхронное отображение двух таймфреймов на одном графике.
Точно так же можно строить кольцевые и линейчатые диаграммы, гистограммы, пузырьковые, биржевые и т.д. Возможности Excel разнообразны. Вполне достаточно, чтобы наглядно изобразить разные типы данных.
Любую информацию легче воспринимать, если она представлена наглядно. Это особенно актуально, когда мы имеем дело с числовыми данными. Их необходимо сопоставить, сравнить. Оптимальный вариант представления – диаграммы. Будем работать в программе Excel.
Так же мы научимся создавать динамические диаграммы и графики, которые автоматически обновляют свои показатели в зависимости от изменения данных. По ссылке в конце статьи можно скачать шаблон-образец в качестве примера.
Как построить диаграмму по таблице в Excel?
- Создаем таблицу с данными.
- Выделяем область значений A1:B5, которые необходимо презентовать в виде диаграммы. На вкладке «Вставка» выбираем тип диаграммы.
- Нажимаем «Гистограмма» (для примера, может быть и другой тип). Выбираем из предложенных вариантов гистограмм.
- После выбора определенного вида гистограммы автоматически получаем результат.
- Такой вариант нас не совсем устраивает – внесем изменения. Дважды щелкаем по названию гистограммы – вводим «Итоговые суммы».
- Сделаем подпись для вертикальной оси. Вкладка «Макет» - «Подписи» - «Названия осей». Выбираем вертикальную ось и вид названия для нее.
- Вводим «Сумма».
- Конкретизируем суммы, подписав столбики показателей. На вкладке «Макет» выбираем «Подписи данных» и место их размещения.
- Уберем легенду (запись справа). Для нашего примера она не нужна, т.к. мало данных. Выделяем ее и жмем клавишу DELETE.
- Изменим цвет и стиль.
Выберем другой стиль диаграммы (вкладка «Конструктор» - «Стили диаграмм»).
Как добавить данные в диаграмму в Excel?
- Добавляем в таблицу новые значения - План.
- Выделяем диапазон новых данных вместе с названием. Копируем его в буфер обмена (одновременное нажатие Ctrl+C). Выделяем существующую диаграмму и вставляем скопированный фрагмент (одновременное нажатие Ctrl+V).
- Так как не совсем понятно происхождение цифр в нашей гистограмме, оформим легенду. Вкладка «Макет» - «Легенда» - «Добавить легенду справа» (внизу, слева и т.д.). Получаем:
Есть более сложный путь добавления новых данных в существующую диаграмму – с помощью меню «Выбор источника данных» (открывается правой кнопкой мыши – «Выбрать данные»).
Когда нажмете «Добавить» (элементы легенды), откроется строка для выбора диапазона данных.
Как поменять местами оси в диаграмме Excel?
- Щелкаем по диаграмме правой кнопкой мыши – «Выбрать данные».
- В открывшемся меню нажимаем кнопку «Строка/столбец».
- Значения для рядов и категорий поменяются местами автоматически.
Как закрепить элементы управления на диаграмме Excel?
Если очень часто приходится добавлять в гистограмму новые данные, каждый раз менять диапазон неудобно. Оптимальный вариант – сделать динамическую диаграмму, которая будет обновляться автоматически. А чтобы закрепить элементы управления, область данных преобразуем в «умную таблицу».
- Выделяем диапазон значений A1:C5 и на «Главной» нажимаем «Форматировать как таблицу».
- В открывшемся меню выбираем любой стиль. Программа предлагает выбрать диапазон для таблицы – соглашаемся с его вариантом. Получаем следующий вид значений для диаграммы:
- Как только мы начнем вводить новую информацию в таблицу, будет меняться и диаграмма. Она стала динамической:
Мы рассмотрели, как создать «умную таблицу» на основе имеющихся данных. Если перед нами чистый лист, то значения сразу заносим в таблицу: «Вставка» - «Таблица».
Как сделать диаграмму в процентах в Excel?
Представлять информацию в процентах лучше всего с помощью круговых диаграмм.
Исходные данные для примера:
- Выделяем данные A1:B8. «Вставка» - «Круговая» - «Объемная круговая».
- Вкладка «Конструктор» - «Макеты диаграммы». Среди предлагаемых вариантов есть стили с процентами.
- Выбираем подходящий.
- Очень плохо просматриваются сектора с маленькими процентами. Чтобы их выделить, создадим вторичную диаграмму. Выделяем диаграмму. На вкладке «Конструктор» - «Изменить тип диаграммы». Выбираем круговую с вторичной.
- Автоматически созданный вариант не решает нашу задачу. Щелкаем правой кнопкой мыши по любому сектору. Должны появиться точки-границы. Меню «Формат ряда данных».
- Задаем следующие параметры ряда:
- Получаем нужный вариант:
Диаграмма Ганта в Excel
Диаграмма Ганта – это способ представления информации в виде столбиков для иллюстрации многоэтапного мероприятия. Красивый и несложный прием.
- У нас есть таблица (учебная) со сроками сдачи отчетов.
- Для диаграммы вставляем столбец, где будет указано количество дней. Заполняем его с помощью формул Excel.
- Выделяем диапазон, где будет находиться диаграмма Ганта. То есть ячейки будут залиты определенным цветом между датами начала и конца установленных сроков.
- Открываем меню «Условное форматирование» (на «Главной»). Выбираем задачу «Создать правило» - «Использовать формулу для определения форматируемых ячеек».
- Вводим формулу вида: =И(E$2>=$B3;E$2 Готовые примеры графиков и диаграмм в Excel скачать:
Как сделать еженедельный график в Excel вместе с ежедневным.
Пример создания динамического синхронного еженедельного графика вместе с ежедневным. Синхронное отображение двух таймфреймов на одном графике.
В программе Excel специально разрабатывались диаграммы и графики для реализации визуализации данных.
Предположим, что нам с вами требуется визуализировать данные из вот такой таблицы со значениями продаж автомобилей по разным странам в 2021 году (реальные данные, взятые отсюда, кстати):
Поскольку количество рядов данных (стран) велико, то попытка запихнуть их все сразу в один график приведёт либо к ужасной "спагетти-диаграмме", либо к построению отдельных диаграмм на каждый ряд, что весьма громоздко.
Изящным решением этой проблемы может стать построение диаграммы только по данным из текущей строки, т. е. строки, где стоит активная ячейка:
Реализовать такое очень легко - потребуется лишь две формулы и один крохотный макрос в 3 строки.
Шаг 1. Номер текущей строки
Первое, что нам потребуется - это именованный диапазон, вычисляющий номер строки на листе, где сейчас стоит наша активная ячейка. Открываем на вкладке Формулы - Диспетчер имен (Formulas - Name manager) , жмём на кнопку Создать (Create) и вводим туда следующую конструкцию:
- Имя - любое подходящее имя для нашей переменной (в нашем случае это ТекСтрока)
- Область - здесь и далее нужно выбрать текущий лист, чтобы создаваемые имена были локальными
- Диапазон - тут используем функцию ЯЧЕЙКА (CELL) , которая умеет выдавать кучу разных параметров для заданной ячейки, в том числе и нужный нам номер строки - за это отвечает аргумент "строка".
Шаг 2. Ссылка на заголовок
Для отображения выбранной страны в заголовке и легенде диаграммы, нам нужно получить ссылку на ячейку с её (страны) названием из первого столбца. Для этого создаём еще один локальный (т.е. Область = текущий лист, а не Книга!) именованный диапазон со следующей формулой:
Здесь функция ИНДЕКС выбирает из заданного диапазона (столбца А, где лежат наши страны-подписи) ячейку с номером строки, который мы до этого определили.
Шаг 3. Ссылка на данные
Теперь аналогичным образом давайте получим ссылку на диапазон со всеми данными по продажам из текущей строки, где стоит сейчас активная ячейка. Создаём ещё один именованный диапазон со следующей формулой:
Здесь третий аргумент равный нулю заставляет ИНДЕКС вернуть в качестве результата не отдельное значение, а всю строку.
Шаг 4. Подставляем ссылки в диаграмму
Теперь выделим шапку таблицы и первую строку с данными (диапазон ) и построим по ним диаграмму через Вставка - Диаграммы (Insert - Charts) . Если выделить на диаграмме ряд с данными, то в строке формул отобразится функция РЯД (SERIES) - специальная функция, которую Excel автоматически использует при создании любой диаграммы, чтобы сослаться на исходные данные и подписи:
Аккуратно подменим в этой функции первый (подпись) и третий (данные) аргументы названиями наших диапазонов с шагов 2 и 3:
Диаграмма начнет отображать данные по продажам из текущей строки.
Шаг 5. Макрос пересчета
Остался последний штрих. Microsoft Excel пересчитывает формулы только при изменении данных на листе или при нажатии на клавишу F9 , а мы хотим, чтобы пересчёт происходил при изменении выделения, т. е. при любом перемещении активной ячейки по листу. Для этого потребуется добавить в нашу книгу простой макрос.
Щёлкните правой кнопкой мыши по ярлычку листа с данными и выберите команду Исходный код (Source code) . В открывшееся окно введём код макроса-обработчика события изменения выделения:
Как легко сообразить, всё, что он делает - это запускает пересчет листа при любом изменении положения активной ячейки.
Шаг 6. Подсветка текущей строки
Для наглядности, можно добавить ещё и правило условного форматирования для выделения цветом страны, которая сейчас отображается на графике. Для этого выделим таблицу и выберем Главная - Условное форматирование - Создать правило - Использовать формулу для определения форматируемых ячеек (Home - Conditional formatting - New rule - Use a formula to determine which cells to format) :
Здесь формула проверяет для каждой ячейки в таблице совпадение её номера строки с тем номером, что хранится в переменной ТекСтрока, и если совпадение имеет место, то срабатывает заливка выбранным цветом.
Вот и всё - просто и красиво, правда?
Примечания
- На больших таблицах вся эта красота может тормозить - условное форматирование штука ресурсоёмкая, да и пересчёт на каждое выделение тоже может быть тяжеловат.
- Чтобы на графике не пропадали данные при случайном выделении ячейки выше или ниже таблицы, можно добавить в имя ТекСтрока дополнительную проверку вложенными функциями ЕСЛИ вида:
Читайте также: