Как сделать диаграмму с накоплением
Построение гистограммы в Excel
Гистограмма – очень полезный инструмент, с помощью которого можно сделать демонстрируемую информацию более наглядной и простой для восприятия. Это особенно актуально, когда время на изучение данных в таблице ограничено, и нужно в сжатые сроки сформировать целостное представление о них. Давайте посмотрим, каким образом можно построить гистограмму в Эксель.
Строим гистограмму
Для выполнения поставленной задачи в Excel можно воспользоваться разными методами:
Ниже мы подробнее остановимся на каждом из этих пунктов.
Метод 1: используем инструменты на ленте программы
Это, пожалуй, самый простой способ. И вот, как он реализуется:
Гистограмма с накоплением
До того, как приступить к созданию гистограммы с накоплением, проверяем, чтобы самая верхняя левая ячейка таблицы была пустой.
Затем делаем следующее:
Примечание: в гистограммах с накоплением один столбец содержит сразу несколько значений. В нашем случае – это данные по всем четырем торговым точкам за конкретную дату.
Нормированная гистограмма с накоплением
В данном случае отображается (в процентном выражении) вклад каждого значения в общем количестве.
Метод 2: пользуемся надстройкой “Пакета “анализа”
Для начала нужно включить данный пакет.
Метод 3: выполняем условное форматирование с гистограммой
Получить гистограмму можно и с помощью условного форматирования ячеек. План действий следующий:
Редактирование гистограммы
Вставленную на лист гистограмму можно изменить:
Помимо вышеперечисленного, на ленте программы во вкладке “Конструктор”, в которой мы окажемся автоматическим после создания гистограммы, мы можем сменить стиль, выбрать один из экспресс-макетов и т.д.
Также, мы можем добавить новый элемент диаграммы, щелкнув на соответствующий значок в левом верхнем углу ленты и выбрав из списка нужный пункт.
Примечание: возможных настроек и параметров, которые можно задать для гистограммы достаточно много, и каждый пользователь сможет подобрать для себя оптимальный вариант. Мы описали лишь часть основных действий и инструментов, которые могут помочь в этом.
Заключение
Программа Excel позволяет построить различные гистограммы, которые помогают лучше и быстрее освоить информацию, особенно, когда ее слишком много, а времени, наоборот, мало. Выбор конкретного вида и метода построения диаграммы зависит от того, какие цели преследует пользователь, и каким он видит конечный результат.
Как сделать диаграмму с процентами в Excel
Часто в отчетах нужно отобразить в Excel из скольких частей состоит стопроцентная целостность определенного показателя и сколько приходится процентов на каждую его часть. Например, чтобы узнать рентабельность товара нам нужно его цену реализации разбить на части: закупочная цена, покрытие расходов, наценка. Для отображения долей хорошо использовать круговые диаграммы с разноцветными секторами. Рассмотрим все более детально на конкретном примере.
Круговые диаграммы с процентами в Excel
Допустим, у нас есть условный товар, о котором мы все знаем в цифрах. Но нам нужно определить какими партиями его продавать. Если его наценка составляет 15%-20% значит, данный товар будет реализовываться только оптовыми партиями, а если более 20% – розничными. Розничная цена для данного товара не должна превышать 1700, а оптовая – 1400. Низкорентабельные товары будем считать с наценкой менее 15%. Теперь заполните таблицу, так как показано на рисунке:
Сделаем круговую диаграмму с процентами:
Выделите диапазон B2:B4 выберите инструмент: «Вставка»-«Диаграммы»-«Круговая».
Если кликнуть по диаграмме у нас активируется дополнительная панель. На ней выберите тип отображения с процентным соотношением долей: «Работа с диаграммами»-«Конструктор»-«Макеты диаграмм»-«Макет 6».
Теперь нам наглядно видно, что наценку лучше увеличить на 50 и продавать данный товар в розницу. Так как оптовыми партиями реализовывать его будет нерентабельно.
Экспонируем наценку, чтобы повысить презентабельность диаграммы. Для этого первый раз кликните по кругу диаграммы. А второй раз непосредственно по сектору наценки. После чего удерживая левую клавишу мышки, немного сместите сектор наценки.
Кольцевая диаграмма с процентами
Презентуем с помощью диаграммы таблицу с данными о деятельности фирмы за 2 года. И сравним их в процентном соотношении. Постройте следующую таблицу:
Для решения данной задачи можно использовать 2 круговые диаграммы. Но в данном примере мы будем использовать более эффективный инструмент:
Главная цель этих двух примеров – показать разницу между разными типами диаграмм и их отличие перед гистограммами. Их рассмотрим в следующем примере.
Гистограмма в процентах
Теперь рассмотрим, как сделать в Excel гистограмму на проценты. Для примера возьмем эту же таблицу презентуем с помощью сразу 3-х гистограмм. Снова выделите диапазон ячеек A2:C4 и выберите: «Вставка»-«Диаграмма»-«Гистограмма»:
Теперь на всех созданных гистограммах используйте переключатель: «Работа с диаграммами»-«Конструктор»-«Строка/столбец».
Изначально при создании гистограмм Excel разместил по умолчанию годы в рядах, а наименования показателей в категориях. Так как наименований больше они попали в категории. А нам нужно было сравнить показатели по годам и для этого мы поменяли строки со столбцами местами, используя переключатель «Строка/столбец».
Вкратце опишем, что отображает каждый тип выбранной гистограммы в данном примере:
Каждый тип диаграммы отличается своими преимуществами и недостатками. Важно уметь правильно подбирать способ графического отображения для разного рода данных. Этому учит наука «Инфографика».
Как сделать диаграмму с накоплением в excel?
Гистограмма с накоплением. Отображение итогов
Выбор того или иного вида диаграммы обусловлен в первую очередь идеей, которую вы хотите донести до аудитории. Если ваша задача сравнить некоторые сущности в определенные моменты времени, то лучший выбор – гистограмма. Например:
Скачать заметку в формате Word, примеры в формате Excel
Если же вы сравниваете не одну, а несколько сущностей, которые к тому же дают суммарный вклад в общие итоги, то наилучшим образом подойдет гистограмма с накоплением:
К сожалению, стандартные методы Excel не позволяют вывести подпись, отражающую итоговое значение по столбцу; можно отобразить значения отдельных или всех составляющих:
Небольшой трюк позволяет отобразить на гистограмме с накоплением итоговые значения.
1. Добавьте в таблицу данных сумму по строкам и постройте гистограмму с накоплением на основе всех данных:
2. Выделите на диаграмме только итоговый ряд и правой кнопкой мыши выберите в меню «Изменить тип диаграммы для ряда»:
3. Выберите обычный график (без маркеров):
4. Выделите итоговый ряд (линию) и добавьте подписи данных
5. Выделите подписи данных и отформатируйте их
6. Измените расположение подписи на «Сверху»
7. Выделите нашу итоговую линию и сделайте ее невидимой:
8. Удалите строку «Итого» в легенде; для этого дважды щелкните на легенде, выделите строку «Итого», удалите ее.
Идею трюка я позаимствовал в книге Холи «Excel 2007. Трюки»
Гистограмма в Excel – это способ построения наглядной диаграммы, отражающей изменение нескольких видов данных за какой-то период времени.
С помощью гистограммы удобно иллюстрировать различные параметры и сравнивать их. Рассмотрим самые популярные виды гистограмм и научимся их строить.
Как построить обновляемую гистограмму?
Имеем данные по продажам разных видов молочной продукции по каждому месяцу за 2015 год.
Построим обновляемую гистограмму, которая будет реагировать на вносимые в таблицу изменения. Выделим весь массив вместе с шапкой и кликнем на вкладку ВСТАВКА. Найдем так ДИАГРАММЫ – ГИСТОГРАММА и выберем самый первый тип. Он называется ГИСТОГРАММА С ГРУППИРОВКОЙ.
Получили гистограмму, размер поля которой можно менять. На такой диаграмме наглядно видно, например, что самые большие продажи были по молоку в ноябре, а самые маленькие – по сливкам в июне.
Если мы будем вносить в таблицу изменения, внешний вид гистограммы тоже будет меняться. Для примера вместо 1400 в январе по кефиру поставим 4000. Видим, как зеленый столбец полетел вверх.
Гистограмма с накоплением
Теперь рассмотрим, как построить гистограмму с накоплением в Excel. Еще один тип гистограмм, который позволяет отразить данные в процентном соотношении. Строится она точно так же, но выбирается другой тип.
Получаем диаграмму, на которой можно видеть, что, например, в январе больше продано молока, чем кефира или сливок. А в августе, по сравнению с другими молочными продуктами, молока было продано мало. И т.п.
Гистограммы в Excel можно изменять. Так, если мы кликнем правой кнопкой мыши в пустом месте диаграммы и выберем ИЗМЕНИТЬ ТИП, то сможем несколько ее видоизменить. Поменяем тип нашей гистограммы с накоплением на нормированную. Результатом будет та же самая диаграмма, но по оси Y будут отражены соотношения в процентном эквиваленте.
Аналогично можно производить и другие изменения гистограммы, что мы и сделали:
Как объединить гистограмму и график в Excel?
Некоторые массивы данных подразумевают построение более сложных диаграмм, которые совмещают несколько их видов. К примеру, гистограмма и график.
Рассмотрим пример. Для начала добавим к таблице с данными еще одну строку, где прописана выручка за каждый месяц. Она указана в рублях.
Теперь изменим существующую диаграмму. Кликнем в пустом месте правой кнопкой и выберем ВЫБРАТЬ ДАННЫЕ. Появится такое поле, на котором будет предложено выбрать другой интервал. Выделяем всю таблицу снова, но уже охватывая и строку с выручкой.
Excel автоматически расширил область значений по оси Y, поэтому данные по количеству продаж остались в самом низу в виде незаметных столбиков.
Но такая гистограмма неверна, потому что на одной диаграмме у нас значатся числа в рублевом и количественном эквиваленте (рублей и литров). Поэтому нужно произвести изменения. Перенесем данные по выручке на правую сторону. Кликнем по фиолетовым столбикам правой кнопкой, выберем ФОРМАТ РЯДА ДАННЫХ и отметим ПО ВСПОМОГАТЕЛЬНОЙ ОСИ.
Видим, что график сразу изменился. Теперь фиолетовый столбик с выручкой имеет свою область значения (справа).
Но это все равно не очень удобно, потому что столбики почти сливаются. Поэтому произведем еще одно дополнительное действие: кликнем правой кнопкой по фиолетовым столбцам и выберем ИЗМЕНИТЬ ТИП ДИАГРАММЫ ДЛЯ РЯДА. Появится окно, в котором выбираем график, самый первый тип.
Получаем вполне наглядную диаграмму, представляющую собой объединение гистограммы и графика. Видим, что максимальная выручка была в январе и ноябре, а минимальная – в августе.
Аналогично можно совмещать любые виды диаграмм.
Диаграммы – прекрасный инструмент, при помощи которого можно визуализировать данные различных источников. Не многие знают, как построить гистограмму в Excel по данным таблицы. На самом деле ничего сложного тут нет. Давайте рассмотрим различные варианты.
Раздел «Диаграммы»
Итак, приступаем к делу.
Вам будет предложено построение различными самыми популярными способами:
Можете навести на каждый из них и посмотреть примеры. Для просмотра других вариантов, нужно нажать на соответствующий пункт.
Обратите внимание на то, что в каждой категории есть несколько видов построения.
Гистограммы с накоплением
На этот раз придется добавить еще один столбец. Так как две колонки будут выглядеть точно так же, как в случае с группировкой.
Третья графа должна быть в виде цифр, а не текста, чтобы программа могла нормально сложить данные.
Как видите, на этой гистограмме шкала по оси «y» отображается в процентах. Данный способ построения хорош тем, что можно увидеть сравнительную информацию.
Пакет «анализ данных»
Данная возможность в быстром доступе по умолчанию отсутствует. Для того чтобы вставить её на панель, необходимо сделать следующие действия.
Теперь рассмотрим процесс создания диаграммы по этой таблице. Для этого необходимо выполнить следующие действия.
В этом случае оси x и y подбираются автоматически.
Условное форматирование
Красивый анализ введенных данных можно сделать прямо внутри в таблице.
В относительных единицах (заливке) намного проще анализировать информацию и тем самым определить, в какой ячейке максимальное или минимальное значение.
Дополнительные ряды распределения
Существуют и другие виды обработки статистических данных. К ним можно отнести:
Большинство из них можно создавать готовыми шаблонами. Например, для создания «Диаграммы Парето» необходимо сделать следующее.
Как оформить гистограмму
Как правило, большинству пользователей не нравится стандартный внешний вид создаваемых объектов. Изменить его очень просто.
Благодаря ей вы сможете сделать что угодно. Кроме этого, редактирование возможно через контекстное меню.
Сделав правый клик мыши по пустой области диаграммы, вы сможете:
Рассмотрим некоторые варианты.
Копирование
Нажав на соответствующий пункт меню, всё содержимое гистограммы окажется в буфере обмена. После этого вы сможете вставить её в Ворде. Стоит отметить, что сделать то же самое можно при помощи сочетания клавиш Ctrl+C. Для вставки используем комбинацию Ctrl+V.
Смотрится очень красиво.
Перемещение
Для начала кликаем в контекстном меню на «Переместить диаграмму».
После этого появится окно, в котором можно указать назначение выбранного объекта.
Если выберете первый вариант, то он будет перемещен на новый лист.
Вращение
Для этих манипуляций нужно выбрать следующий пункт.
Вследствие этого в правой части экрана появится дополнительная панель, в которой можно «поиграться» с двумя осями.
Таким способом можно придать еще больше объемного эффекта.
Подписываем объект
Если вы кликните на название, то справа отобразится панель для работы с текстом. Более того, появится возможность редактирования.
При желании вы можете добавить такие эффекты, как:
Вы можете совместить все эти отдельные атрибуты. Но не перестарайтесь, иначе получится кошмар.
Как объединить гистограмму и график в Экселе
Чтобы совместить разные виды объектов, необходимо использовать нестандартные наборы диаграмм.
Обратите внимание на то, что справа по оси Y появилась дополнительная шкала, предназначенная только для линии, то есть для ряда «Всего». Левая шкала – для всего остального.
Диаграмма онлайн
Для тех, кто не может правильно построить гистограмму, на помощь приходят онлайн-сервисы. Например, сайт OnlineCharts.
Нажав на кнопку «Создайте Вашу диаграмму», вы увидите огромное количество различных настроек, благодаря которым сможете нарисовать что хотите.
Полученный результат легко скачать себе на компьютер.
Заключение
В данной статье мы пошагово рассмотрели, как создавать различные виды диаграмм при помощи всевозможных инструментов. Не бойтесь экспериментировать. Удалить свой результат вы всегда успеете.
Следует отметить, что существуют разные версии программы Эксель. Например, Excel 2010, 2013 и 2016 в этом плане очень похожи. Продукты 2003 и 2007 годов в наше время не так актуальны и отличие между ними колоссальное.
Видеоинструкция
Тем, у кого остались какие-то вопросы, рекомендуется посмотреть видео, в котором всё подробно рассказывается с дополнительными комментариями.
Линейчатые диаграммы с накоплением и гистограммы с накоплением широко используются в мире информации и бизнеса. Уверен, что их популярность вызвана способностью показывать большое количество информации на одной диаграмме, при этом создаются они достаточно просто. Однако, я не фанат этих диаграмм и далее объясню, чем, по моему мнению, плохи эти диаграммы, и предложу несколько альтернативных вариантов.
Плохие диаграммы
Неровная базисная линия
Рисунок выше показывает, насколько трудно быстро увидеть вектор продаж в Азии (ряд Asia), поскольку базисная линия не ровная. Я подчёркиваю – быстро, т.к. наша основная цель, чтобы диаграмма легко и понятно передавала информацию и показывала тренд. Гораздо проще взглянуть на имеющиеся числа, чем пытаться по этой диаграмме понять увеличиваются или уменьшаются нужные столбцы в разные периоды времени. А если одновременно рассматривать и все прочие регионы, то столбцы ряда Asia вовсе теряются среди столбцов, расположившихся выше и ниже. Полный бардак!
А вот график, который показывает актуальный тренд для ряда данных Asia. Теперь намного проще увидеть, что последние три квартала продажи снижались. Возможно, это привлечёт внимание к эффективности региона и подтолкнёт к соответствующим действиям для исправления ситуации.
Когда использовать диаграммы с накоплением
Диаграммы с накоплением отлично работают, когда с течением времени происходят значительные изменения в данных, и когда нужно показать тренд по одному или двум рядам. Лучше всего выделить этот ряд ярким цветом и добавить текст для описания тренда. Этот ряд данных нужно переместить в самую нижнюю позицию на графике, чтобы он был построен от базисной линии – так пользователь сможет увидеть вектор развития ряда.
Вариант №2, предложенный ниже, показывает использование диаграммы с накоплением, базисная линия которой сделана динамической, что позволяет быстро анализировать тренды.
Решения
Теперь, когда у нас есть понимание, в каких случаях НЕ следует использовать диаграммы с накоплением, можно приступить к поиску альтернативных способов быстро и ясно донести информацию. Решение будет зависеть от того, какие тренды обнаруживаются в данных и в каком виде мы хотим донести сообщение пользователю.
Вариант №1: Панель диаграмм
Панель диаграмм – это группа небольших диаграмм, собранных вместе в виде панели. Такой способ дает возможность показать ситуацию в каждом регионе на отдельной диаграмме.
Здесь мы видим тренды по каждому региону. Тренды, которые мы не видели на диаграмме с накоплением из-за неровных базисных линий и масштаба графика. Продажи в Северной Америке (North America) и в Европе (Europe) значительно выше, чем в Азии (Asia) и в Африке (Africa), поэтому эти два тренда теряются на фоне первых двух.
Некоторые важные моменты теряются, если не присмотреться к ним поближе. На панели диаграмм мы можем выделить их цветом, как я сделал на рисунке выше. Теперь четко видно, что продажи в Азии снижаются, в то время как в Африке за последний год продажи почти удвоились. Суммы по некоторым регионам малы по сравнению с общей суммой продаж, и если рассматривать только диаграмму с накоплением, то можно упустить такие малозаметные, но очень важные моменты.
Вариант №2: Динамическая базисная линия в гистограмме или линейчатой диаграмме с накоплением
Хорошее решение для тех случаев, когда нужно сохранить диаграмму с накоплением, или, когда не желательно отпугивать аудиторию чем-то, коренным образом, отличающимся от привычных диаграмм. Я называю это диаграммой с накоплением с динамической базисной линией, т.к. пользователь может при помощи выпадающего списка быстро выбрать, какой ряд данных поместить внизу графика. Создать такую диаграмму очень просто. Не потребуется ни работа с макросами, ни знание VBA. Всего лишь две формулы и элемент управления «Поле со списком».
Когда ряд данных West находится наверху столбца, трудно увидеть какую-либо зависимость. Но как только ряд West перемещается в самый низ столбца к базисной линии, сразу становится видно, что последние полгода происходит устойчивый спад объёма продаж.
При желании можно также увидеть взаимосвязь между рядами данных South и West во второй половине года, если переместить ряд East вверх столбца. Одним словом, данные на диаграмме нужно расположить таким образом, чтобы донести максимум информации до читателя.
Буду очень рад узнать от Вас, какие ещё альтернативные варианты возможны. Пусть они будут простыми или сложными – как Вам угодно, здесь нет правильных или неправильных решений. Помните, что основная цель – ясно и быстро донести информацию пользователю.
Урок подготовлен для Вас командой сайта office-guru.ru
Источник: /> Перевел: Антон Андронов
Правила перепечаткиЕще больше уроков по Microsoft Excel
Оцените качество статьи. Нам важно ваше мнение: