Построение динамично меняющейся диаграммы на основе сводной таблицы.
В работе с программой «Excel» часто сталкиваюсь с тем, что для качественной аналитики больших объемов данных недостаточно обычного статичного графика или диаграммы, требуется использовать динамичный изменяющийся график с фильтрами и отборами информации.
Такой график или диаграмму можно реализовать в программе «Excel» версии 2010 и выше при помощи сводных таблиц.
Как сделать динамический график(диаграмму) на основании сводной таблицы.
| Город | Страна | Артикул | Товар | Категория | Наличие на складе |
| Самара | Россия | 2333 | Икра лососевая | Пища | Есть |
| Харьков | Украина | 443 | Мышь оптоволоконная | Комплектующие | Нет |
| Ереван | Армения | 255 | Кран вентильный | Комплектующие | В пути |
| Ижевск | Россия | 455 | Ведро эмалерованное | Тара | Есть |
| Кострома | Россия | 23345 | Сливки (флакон) | Пища | Нет |
| Коломна | Россия | 444 | ПО Free Excel (1 шт) | Комплектующие | В пути |
| Алма -Аты | Казахстан | 3345 | Коробка картонная | Тара | Есть |
| Таганрог | Россия | 556 | Пакет 3*4 (100шт.) | Тара | Нет |
| Самара | Россия | 235456 | Мышь оптоволоконная | Комплектующие | В пути |
| Харьков | Украина | 3455 | Кран вентильный | Комплектующие | Есть |
| Ереван | Армения | 344 | Сетка 20 метров | Комплектующие | Нет |
| Ижевск | Россия | 3444 | Сливки (флакон) | Пища | В пути |
| Кострома | Россия | 789 | ПО Free Excel (1 шт) | Комплектующие | Есть |
| Коломна | Россия | 456 | Яблоки, кг. | Пища | Нет |
| Алма -Аты | Казахстан | 56456 | Яблоки красные (1 ящик) | Пища | В пути |
| Таганрог | Россия | 678 | Сельдь | Пища | Есть |
| Самара | Россия | 3456 | Слива (1 уп.) | Пища | Нет |
| Харьков | Украина | 36 | Алыча(1 шт.) | Пища | В пути |
| Ереван | Армения | 345 | Кран вентильный | Комплектующие | Есть |
| Ижевск | Россия | 568 | Таз | Тара | Нет |
| Кострома | Россия | 788 | Батончик шоколад | Пища | В пути |
| Коломна | Россия | 345 | ПО Free Excel (1 шт) | Комплектующие | Есть |
| Алма -Аты | Казахстан | 345 | Диван раскладной | Мебель | Нет |
| Таганрог | Россия | 777 | Кресло | Мебель | В пути |
Шаг 1. Создание сводной таблицы.
Шаг 2. Создание диаграммы (графика)
В результате получаем динамично меняющуюся диаграмму в зависимости от выбранных параметров в фильтрах.
Выбор товара на диаграмме
[expert_bq id=»1570″]Вы также можете вносить косметические изменения в вид интервального графика, включая замену заголовков и меток осей для этого дважды кликните по области, которую вы хотите отредактировать. Если же вы хотите что-то уточнить, обращайтесь ко мне![/expert_bq] Вы можете собрать все данные, которые больше определённого значения, в одном интервале, независимо от его длины. Для этого поставьте флажок «Выход за верхнюю границу интервала» и укажите значение, выше которого все результаты будут помещены в один интервал:
Как создать интервальный график в Microsoft Excel.
Ряды динамики с нарастающими итогами строятся при определении общего объема товарооборота в розничной торговле. Так, обобщением товарно — денежных отчетов за последние операционные периоды (пятидневки, недели, декады и т. д.).
| Город | Страна | Артикул | Товар | Категория | Наличие на складе |
| Самара | Россия | 2333 | Икра лососевая | Пища | Есть |
| Харьков | Украина | 443 | Мышь оптоволоконная | Комплектующие | Нет |
| Ереван | Армения | 255 | Кран вентильный | Комплектующие | В пути |
| Ижевск | Россия | 455 | Ведро эмалерованное | Тара | Есть |
| Кострома | Россия | 23345 | Сливки (флакон) | Пища | Нет |
| Коломна | Россия | 444 | ПО Free Excel (1 шт) | Комплектующие | В пути |
| Алма -Аты | Казахстан | 3345 | Коробка картонная | Тара | Есть |
| Таганрог | Россия | 556 | Пакет 3*4 (100шт.) | Тара | Нет |
| Самара | Россия | 235456 | Мышь оптоволоконная | Комплектующие | В пути |
| Харьков | Украина | 3455 | Кран вентильный | Комплектующие | Есть |
| Ереван | Армения | 344 | Сетка 20 метров | Комплектующие | Нет |
| Ижевск | Россия | 3444 | Сливки (флакон) | Пища | В пути |
| Кострома | Россия | 789 | ПО Free Excel (1 шт) | Комплектующие | Есть |
| Коломна | Россия | 456 | Яблоки, кг. | Пища | Нет |
| Алма -Аты | Казахстан | 56456 | Яблоки красные (1 ящик) | Пища | В пути |
| Таганрог | Россия | 678 | Сельдь | Пища | Есть |
| Самара | Россия | 3456 | Слива (1 уп.) | Пища | Нет |
| Харьков | Украина | 36 | Алыча(1 шт.) | Пища | В пути |
| Ереван | Армения | 345 | Кран вентильный | Комплектующие | Есть |
| Ижевск | Россия | 568 | Таз | Тара | Нет |
| Кострома | Россия | 788 | Батончик шоколад | Пища | В пути |
| Коломна | Россия | 345 | ПО Free Excel (1 шт) | Комплектующие | Есть |
| Алма -Аты | Казахстан | 345 | Диван раскладной | Мебель | Нет |
| Таганрог | Россия | 777 | Кресло | Мебель | В пути |
5 приёмов, как улучшить диаграммы в Excel
Автор книги «Мастерство визуализации данных» и создатель блога data.vis Ольга Базалева поделилась приёмами создания профессиональных диаграмм в Excel.
В статье — продвинутые способы создания качественных диаграмм. С основными правилами визуализации данных можно ознакомиться в предыдущей публикации.
Приём 1: задать перекрытие столбцов на диаграмме
Когда и зачем применять. Когда нужно сравнить показатели текущего года и прошлого. Если на гистограмме или линейчатой диаграмме вывести текущий год на передний план, а прошлый оставить на заднем, информация будет легче считываться.
Клик правой кнопкой мыши — ПКМ — по любому столбцу диаграммы.
Чтобы одни столбцы полностью перекрывали другие, выбирайте перекрытие 100%. Можно настроить другое соотношение перекрытия, например, 70%.
Приём 2: уменьшить расстояние между столбцами
Когда и зачем применять. Прием подходит для любых диаграмм динамики, например, результатов работы или выручки по месяцам. Сокращать расстояние между столбцами нужно, чтобы внимание не рассеивалось на них, динамика воспринималась целостной.
Чтобы сохранить визуальную целостность и сделать акцент на данных, выбирайте зазор до 25-50%.
Курс
EXCEL
Приём 3: отобразить среднее скользящее
Когда и зачем применять. Приём нужен при анализе динамики какого-либо показателя за длительный период с разбивкой по дням, когда ежедневные показатели значительно отличаются. Из-за сильных колебаний будет сложно рассмотреть тенденцию. Наглядность диаграммы улучшит скользящее среднее.
Инструмент сглаживает колебания и позволяет чётче увидеть тренд. Можно построить скользящее среднее за 21 день, 11, 5 или выбрать любое другое окно. Все зависит от уровня сглаживания, который вы хотите увидеть. Чем шире окно, тем более сглаженная линия.
Приём 4: оставить одну подпись на несколько диаграмм
Когда и зачем применять. Если вы построили две диаграммы с одинаковыми категориями данных на одном листе, оставьте одну подпись. Это поможет сэкономить место, избежать повторов одной и той же информации и исключить распространенную ошибку, когда на двух диаграммах категории расположены в разной последовательности.
Приём 5: добавить вспомогательную ось
Когда и зачем применять. Когда на одном графике отображается динамика двух показателей, которые сложно сравнить друг с другом. Например, показателей в рублях и процентах или с разным порядком чисел — выручкой в миллионном исчислении и средним чеком в тысячном. Ось помогает проще сопоставлять и считывать данные.
Подборка курсов, бесплатных лекций и материалов по Excel
Чтобы упростить восприятие информации, сделайте на диаграмме подсказки цветом. На примере все элементы ряда «Выручка», в том числе сами столбцы, название и подписи оси, окрашены в зеленый. Ряд «Средний чек» и его элементы — в красный.
На вспомогательной оси проще оценить динамику среднего чека, элементы двух рядов выделены разными цветами
Мнение автора и редакции может не совпадать. Хотите написать колонку для «Нетологии»? Читайте наши условия публикации. Чтобы быть в курсе всех новостей и читать новые статьи, присоединяйтесь к Телеграм-каналу Нетологии.
Подборка книг и блогов по визуализации данных
[expert_bq id=»1570″]MS Excel содержит много встроенных функций, использование которых значительно облегчает обработку статистической информации, касающуюся рядов динамики. Если же вы хотите что-то уточнить, обращайтесь ко мне![/expert_bq] Для начала – график простой функции, например, квадратной y=x2. Поскольку построение графика по таблице не вызывает уже проблем, то и надо этим воспользоваться, создать таблицу. В excel это очень просто:Как построить график в Эксель — Microsoft Excel
Выбрав данные, перейдите во вкладку «Вставить» на панели ленты. Различные доступные вам варианты графиков будут перечислены в разделе «Диаграммы» в середине. Нажмите кнопку «Вставить статистическую диаграмму», чтобы просмотреть список доступных диаграмм. В разделе «Гистограмма» раскрывающегося меню выберите первый верхний график слева.
