Как составить сводную диаграмму

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

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

• Количество значений (применяется не только к числам);
• Среднее значение;
• Сумма чисел;
• Минимальное или максимальное число;
• и другие статистические функции, которые мы сейчас рассматривать не будем.

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

Начало работы

Возьмём файл с курсом доллара США за определённые даты (а вы можете повторить те же действия на своих данных — функционал один и тот же). Нашей задачей будет построить диаграмму, отражающую средний курс на каждую из двух недель. Мы будем называть эту таблицу исходной таблицей, а таблицу рядом с диаграммой — сводной таблицей.

Первое, что нам нужно, — это создать сводную диаграмму. Поместим курсор в верхнюю левую клетку, если он находится не там. На вкладке “Вставка” нажмём на иконку с подписью “Сводная диаграмма”. Excel понимает сам, какой диапазон данных нужно включить в сводную диаграмму, мы её строим на отдельном листе, поэтому просто жмём “ОК”.

Не забываем сохраняться: Ctrl S. У нас открывается новый лист, на нём появляются некоторые области. Рассмотрим их.

Область сводной таблицы и сводной диаграммы нас пока не интересует: там появятся собственно таблица и диаграмма. Нас же привлекают области 1 и 2.

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

Принцип агрегации данных просто понять и сложно объяснить: из выбранного в качестве оси столбца Excel выберет все уникальные значения. Каждому из них будет соответствовать одно или несколько чисел, лежащих в поле “значения”. Эти числа будут обрабатываться агрегирующей функцией, и на выходе будет всегда одно число —количество, или сумма, или минимум/максимум, или иная метрика.

Для построения диаграммы нам нужно перетащить мышкой названия столбцов из области 1 в область 2. Так как нам нужны недели, то мы несём столбец “Дата” в “ось”, а столбец “Курс доллара США в рублях” в “значения”.

Мы получили диаграмму. На ней мы видим не недели, а даты, и не средний курс, а сумму. Теперь нам нужно сгруппировать даты по 7 дней (недели), а агрегирующую функцию сменить на среднее. Но сначала сохраним файл: Ctrl S.

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

Группировка в сводной диаграмме

Откроем вкладку “Данные”, там найдём кнопку “Группировать” и выберем в выпавшем меню “Группировать”.

В открывшемся окне выбираем “Дни” (нам нужно сгруппировать дни в недели), остальное выделение снимаем, если есть. Внизу указываем, что группировать нужно по 7 дней, и жмём “ОК”.

Количество строк в таблице уменьшилось до двух. Это правильно: у нас как раз посчитан курс на 14 дней, то есть 2 недели. Сохраняем файл и продолжаем. Теперь наша задача — сменить агрегацию.

В “значениях”, где написано “сумма по полю…” нажимаем на стрелочку. В выпавшем меню выбираем “Параметры полей значений”.

В открывшемся окне необходимо выбрать “Среднее”, чтобы посчитать средний курс на неделю. Там есть и другие функции: сумма, количество и так далее, они нам пока не нужны. Нажимаем “ОК”.

Более подробно с функционалом этих меню мы познакомимся в следующем занятии — там мы будем работать со сводными таблицами.

Сохраним файл.

Наводим красоту: линии сетки, подписи, заголовки

Уберём линии сетки, они выглядят неинформативно и засоряют поле. Для этого выделим их мышкой и нажмём клавишу Delete или Backspace.

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

Сохраним файл. Пока что мы видим огромное количество цифр после десятичной точки. Это совершенно лишнее для такого рода графика, поэтому уменьшим разрядность отображаемого числа. Мы это уже делали, когда строили простой график. Нам нужна вкладка “Главная”, потом выделим те ячейки, у которых нужно поменять разрядность (в таблице), и нажмём нужное количество раз на иконку “Уменьшить разрядность”. У меня получилось 6 раз, потому что две цифры после запятой уже являются копейками и, вероятно, довольно важны.

Можно сохранить файл и сделать более красивый заголовок и столбцу, и диаграмме. Для этого в ячейку с именем столбца поставим курсор и напечатаем что-то более понятное:

И в диаграмме вместо сухого “Итог” впишем понятный заголовок:

А вот подписи данных можно сделать и покрупнее. Вкладка “Главная”, выделяем подписи данных левым кликом, и на иконке выбора размера шрифта выбираем подходящий. Мне таким показался 16-й.

Не забудьте сохранить файл 🙂

Наводим красоту: типы заливки столбцов диаграммы; подписи

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

Узорная заливка — это заполнение столбца не сплошным цветом, а каким-либо узором. Эта функция лежит в формате ряда данных. Нажмём правой кнопкой мышки на диаграмму, выберем “формат ряда данных” во всплывающем меню.

Далее нам нужна вкладка-иконка в правом окне с символом заливки. Выбираем “узорную заливку” и отмечаем нужный нам узор. Мне понравилась диагональная штриховка.

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

Освобождая место на графике, удалим ось и легенду — они нам не нужны, потому что у графика есть подписи данных. Просто выделяем их по отдельности и нажимаем Delete или Backspace.

Сохраняем файл.

Результат работы

Посидев минут 15 с диаграммой, мы получили хорошую картинку, пригодную для вставки в презентацию. По ней видно, насколько вырос курс доллара США, и какие значения он принимал. При этом мы удалили всё лишнее.

Сегодня мы познакомились с основой агрегации данных. Мы научились:

• Создавать сводную диаграмму;
• Выбирать агрегирующую функцию;
• Группировать данные;
• Менять заливку столбцов диаграммы.

Кроме того, мы повторили некоторые операции с диаграммами, знакомые нам из урока “Как построить график в Excel“, и ничего не пришлось переделывать, потому что мы регулярно сохраняли файл.

В следующий раз мы поговорим о сводных таблицах, погрузимся глубже в агрегацию данных, и как всегда, не обойдётся без повторения. А пока попробуйте поиграть с пройденными функциями, заглянуть в разделы и меню, в которые мы не зашли: нет лучшей обучающей практики, чем эксперименты. Любое действие в Excel можно отменить, как и в других редакторах: Ctrl Z. Это даёт широкое пространство для опытов.

Буду благодарен за обратную связь в комментариях.

#сводная диаграмма #обучение excel #аналитика данных #диаграмма в excel #excel для новичков