Гистограмма с накоплением. Отображение итогов

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

Скачать заметку в формате Word, примеры в формате Excel

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

К сожалению, стандартные методы Excel не позволяют вывести подпись, отражающую итоговое значение по столбцу; можно отобразить значения отдельных или всех составляющих:

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

1. Добавьте в таблицу данных сумму по строкам и постройте гистограмму с накоплением на основе всех данных:

2. Выделите на диаграмме только итоговый ряд и правой кнопкой мыши выберите в меню «Изменить тип диаграммы для ряда»:

3. Выберите обычный график (без маркеров):

4. Выделите итоговый ряд (линию) и добавьте подписи данных

5. Выделите подписи данных и отформатируйте их

6. Измените расположение подписи на «Сверху»

7. Выделите нашу итоговую линию и сделайте ее невидимой:

8. Удалите строку «Итого» в легенде; для этого дважды щелкните на легенде, выделите строку «Итого», удалите ее.

Получилось так:

или так:

Идею трюка я позаимствовал в книге Холи «Excel 2007. Трюки»

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

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

Как построить обновляемую гистограмму?

Имеем данные по продажам разных видов молочной продукции по каждому месяцу за 2015 год.

Построим обновляемую гистограмму, которая будет реагировать на вносимые в таблицу изменения. Выделим весь массив вместе с шапкой и кликнем на вкладку ВСТАВКА. Найдем так ДИАГРАММЫ – ГИСТОГРАММА и выберем самый первый тип. Он называется ГИСТОГРАММА С ГРУППИРОВКОЙ.

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

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

Гистограмма с накоплением

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

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

Гистограммы в Excel можно изменять. Так, если мы кликнем правой кнопкой мыши в пустом месте диаграммы и выберем ИЗМЕНИТЬ ТИП, то сможем несколько ее видоизменить. Поменяем тип нашей гистограммы с накоплением на нормированную. Результатом будет та же самая диаграмма, но по оси Y будут отражены соотношения в процентном эквиваленте.

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

• поменяли шрифта на Arial и изменили его цвет на фиолетовый;
• сделали подчеркивание пунктирной линией;
• переместили легенду немного выше;
• добавили подписи к столбцам.

Как объединить гистограмму и график в Excel?

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

Рассмотрим пример. Для начала добавим к таблице с данными еще одну строку, где прописана выручка за каждый месяц. Она указана в рублях.

Теперь изменим существующую диаграмму. Кликнем в пустом месте правой кнопкой и выберем ВЫБРАТЬ ДАННЫЕ. Появится такое поле, на котором будет предложено выбрать другой интервал. Выделяем всю таблицу снова, но уже охватывая и строку с выручкой.

Excel автоматически расширил область значений по оси Y, поэтому данные по количеству продаж остались в самом низу в виде незаметных столбиков.

Но такая гистограмма неверна, потому что на одной диаграмме у нас значатся числа в рублевом и количественном эквиваленте (рублей и литров). Поэтому нужно произвести изменения. Перенесем данные по выручке на правую сторону. Кликнем по фиолетовым столбикам правой кнопкой, выберем ФОРМАТ РЯДА ДАННЫХ и отметим ПО ВСПОМОГАТЕЛЬНОЙ ОСИ.

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

Но это все равно не очень удобно, потому что столбики почти сливаются. Поэтому произведем еще одно дополнительное действие: кликнем правой кнопкой по фиолетовым столбцам и выберем ИЗМЕНИТЬ ТИП ДИАГРАММЫ ДЛЯ РЯДА. Появится окно, в котором выбираем график, самый первый тип.

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

Аналогично можно совмещать любые виды диаграмм.

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

Раздел «Диаграммы»

Итак, приступаем к делу.

1. Для начала нужно создать таблицу. Значения могут быть произвольными.

2. Далее данные необходимо выделить.

3. Откройте в меню-вкладку «Вставка» и кликните на иконку работы с гистограммой.

Вам будет предложено построение различными самыми популярными способами:

• обычная гистограмма;
• объемная;
• линейчатая;
• объемная линейчатая.

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

Обратите внимание на то, что в каждой категории есть несколько видов построения.

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

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

Гистограммы с накоплением

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

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

1. Выделяем таблицу, нажимаем на кнопку «Гистограммы» и выбираем что-нибудь с накоплением.

2. В результате этого вы увидите следующее.

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

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

Пакет «анализ данных»

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

1. Нажмите на пункт меню «Файл».

2. Кликаем на «Параметры».

3. Далее переходим в «Надстройки».

4. Убедитесь, что в «Управлении» выбран пункт «Надстройки Excel». После этого нажмите на кнопку «Перейти…».

5. Поставьте галочку около «Пакет анализа» и нажмите на кнопку «OK».

6. Переходим на главной панели на вкладку «Данные». В правой части ленты появится новая кнопка «Анализ данных».

Теперь рассмотрим процесс создания диаграммы по этой таблице. Для этого необходимо выполнить следующие действия.

1. Нажмите на только что добавленную кнопку. Выберите пункт «Гистограмма» и кликните на «OK».

2. После этого вы увидите следующее окно.

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

4. Теперь поставьте галочку около пункта «Вывод графика» и нажмите на кнопку «OK».

5. В результате этого вы получите вот такую «Гистограмму» с анализом значений.

В этом случае оси x и y подбираются автоматически.

Условное форматирование

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

1. Для этого необходимо выделить ее, перейти на вкладку «Главная» и кликнуть на «Условное форматирование».

2. В появившемся меню выберите пункт «Гистограммы». После этого появится большой список различных вариантов. Вы можете попробовать наложить любые цвета на свой вкус. Для этого достаточно навести указатель на один из предложенных шаблонов.

3. В итоге получите красивую таблицу, в которой данные представлены градиентной заливкой.

В относительных единицах (заливке) намного проще анализировать информацию и тем самым определить, в какой ячейке максимальное или минимальное значение.

Дополнительные ряды распределения

Существуют и другие виды обработки статистических данных. К ним можно отнести:

• Парето;
• Полигон частот;
• Кумуляты и т.д.

Большинство из них можно создавать готовыми шаблонами. Например, для создания «Диаграммы Парето» необходимо сделать следующее.

1. Выделить таблицу.
2. Перейти на вкладку «Вставить».
3. Кликнуть на иконку «Вставка статистической диаграммы».
4. Выбрать нужную заготовку.

Как оформить гистограмму

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

1. При выделении диаграммы в меню появляется новая вкладка «Конструктор».

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

Сделав правый клик мыши по пустой области диаграммы, вы сможете:

• скопировать или вырезать;
• изменить тип;
• выбрать другие данные;
• переместить её;
• повернуть объемную фигуру.

Рассмотрим некоторые варианты.

Копирование

Нажав на соответствующий пункт меню, всё содержимое гистограммы окажется в буфере обмена. После этого вы сможете вставить её в Ворде. Стоит отметить, что сделать то же самое можно при помощи сочетания клавиш Ctrl+C. Для вставки используем комбинацию Ctrl+V.

Смотрится очень красиво.

Перемещение

Для начала кликаем в контекстном меню на «Переместить диаграмму».

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

Если выберете первый вариант, то он будет перемещен на новый лист.

Вращение

Для этих манипуляций нужно выбрать следующий пункт.

Вследствие этого в правой части экрана появится дополнительная панель, в которой можно «поиграться» с двумя осями.

Таким способом можно придать еще больше объемного эффекта.

Подписываем объект

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

При желании вы можете добавить такие эффекты, как:

• тень;

• свечение;

• сглаживание;

• объемность.

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

Как объединить гистограмму и график в Экселе

Чтобы совместить разные виды объектов, необходимо использовать нестандартные наборы диаграмм.

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

2. Теперь выделите все строки, перейдите на вкладку «Вставка», нажмите на иконку «Гистограммы» и выберите последний вариант.

3. В появившемся окне перейдите на «Комбинированная».

4. После этого вы сможете указать тип диаграммы для каждого ряда.

5. Необходимо везде указать «Гистограмма с группировкой», а для ряда «Всего» – тип «Линия». При этом нужно поставить галочку в графе «Вспомогательная ось».

6. После нажатия на кнопку «OK», мы получим новую диаграмму, которая сочетает в себе гистограмму и график.

Обратите внимание на то, что справа по оси Y появилась дополнительная шкала, предназначенная только для линии, то есть для ряда «Всего». Левая шкала – для всего остального.

Диаграмма онлайн

Для тех, кто не может правильно построить гистограмму, на помощь приходят онлайн-сервисы. Например, сайт OnlineCharts.

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

Полученный результат легко скачать себе на компьютер.

Заключение

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

Следует отметить, что существуют разные версии программы Эксель. Например, Excel 2010, 2013 и 2016 в этом плане очень похожи. Продукты 2003 и 2007 годов в наше время не так актуальны и отличие между ними колоссальное.

Видеоинструкция

Тем, у кого остались какие-то вопросы, рекомендуется посмотреть видео, в котором всё подробно рассказывается с дополнительными комментариями.

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

Плохие диаграммы

На рисунке выше показана гистограмма с накоплением. Я считаю её плохой диаграммой, поскольку по ней совершенно невозможно проследить тренд. На диаграмме показаны пять рядов данных и их общая сумма. Но на деле мы можем видеть тренды только по двум показателям из шести! Мы видим тренд по ряду данных North America и по общей сумме (высота всего столбца), т.к. их базисная линия ровная. Фактически, их базисной линией является ось Х со значением $0. Этот момент очень важен при создании графика, который должен показывать тенденцию развития (тренд). Наш глаз улавливает базисную линию, как начало каждого столбца, и затем сравнивает верхнюю точку каждого столбца, передвигаясь слева направо. Такой подход отлично работает для ряда данных North America и для общей суммы, но определить тренды для остальных четырёх регионов очень трудно.

Неровная базисная линия

Рисунок выше показывает, насколько трудно быстро увидеть вектор продаж в Азии (ряд Asia), поскольку базисная линия не ровная. Я подчёркиваю – быстро, т.к. наша основная цель, чтобы диаграмма легко и понятно передавала информацию и показывала тренд. Гораздо проще взглянуть на имеющиеся числа, чем пытаться по этой диаграмме понять увеличиваются или уменьшаются нужные столбцы в разные периоды времени. А если одновременно рассматривать и все прочие регионы, то столбцы ряда Asia вовсе теряются среди столбцов, расположившихся выше и ниже. Полный бардак!

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

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

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

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

Решения

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

Вариант №1: Панель диаграмм

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

Здесь мы видим тренды по каждому региону. Тренды, которые мы не видели на диаграмме с накоплением из-за неровных базисных линий и масштаба графика. Продажи в Северной Америке (North America) и в Европе (Europe) значительно выше, чем в Азии (Asia) и в Африке (Africa), поэтому эти два тренда теряются на фоне первых двух.

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

Вариант №2: Динамическая базисная линия в гистограмме или линейчатой диаграмме с накоплением

Хорошее решение для тех случаев, когда нужно сохранить диаграмму с накоплением, или, когда не желательно отпугивать аудиторию чем-то, коренным образом, отличающимся от привычных диаграмм. Я называю это диаграммой с накоплением с динамической базисной линией, т.к. пользователь может при помощи выпадающего списка быстро выбрать, какой ряд данных поместить внизу графика. Создать такую диаграмму очень просто. Не потребуется ни работа с макросами, ни знание VBA. Всего лишь две формулы и элемент управления «Поле со списком».

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

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

Вариант №3: ???

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

Спасибо!

Урок подготовлен для Вас командой сайта office-guru.ru
Источник: /> Перевел: Антон Андронов

Правила перепечаткиЕще больше уроков по Microsoft Excel

Оцените качество статьи. Нам важно ваше мнение: