В прошлый раз мы строили сводную диграмму. Сегодня мы приступим к построению сводной таблицы. Она даёт бóльшую свободу действий, и из неё тоже можно быстро построить сводную диаграмму.
Сводная таблица может служить для нескольких целей:
- Анализ данных (смотрим глазами, ищем инсайты);
- Визуализация данных (например, вставка в слайд);
- Построение сводных диаграмм.
Сегодня построим простую сводную таблицу для анализа данных. В следующих частях рассмотрим, как можно её доработать и визуализировать.
Не забывайте сохранять файл! Я больше не напоминаю об этом, надеясь, что вы делаете это уже автоматически. Видите скриншот — после совершения действий, указанных на нём, немедленно сохраняйтесь командой Ctrl S.
Начало работы
Прежде всего, нам нужен исходный файл. Для примера я взял открытые данные по заболеваемости COVID-2019 в Кыргызстане.
Посмотрим на информацию в файле. Каждая строчка отвечает за одно сообщение о случае, связанном с болезнью: заражении, выздоровлении или смерти. Тут же содержится число таких случаев в указанном районе указанной области на указанную дату, а также ссылка на источник информации. Ещё есть источник заражения. Первое, что приходит в голову, — это посчитать кол-во заражений и выздоровлений на период времени, чтобы увидеть динамику.
При первом взгляде на данные можно решить, какую именно информацию мы хотим из них извлечь. Это особенно актуально, если задача состоит не в построении определённого отчёта, а в выявлении закономерностей, когда мы заранее не знаем, к чему придём. Поиск любой доступной информации — рутинная работа аналитика.
Создаём сводную таблицу
Без лишних раздумий создаём сводную таблицу: на вкладке "Вставка" нажимаем на иконку "Сводная таблица" и подтверждаем: нажимаем "ОК".
У нас появился новый лист с заготовкой сводной таблицы. Теперь нужно настроить её поля в окне справа: "Дату" помещаем в строчки, "Тип случая" в столбцы, а сумму по "Числу случаев" — в значения.
С этой таблицей уже можно работать, но сделаем ещё некоторые движения.
Добавляем месяц
Первое, что затрудняет работу с таблицей, — большое количество дат. Для начала лучше посчитать случаи заболевания/выздоровления не на день, а на месяц. Для этого нужно добавить месяц. Переходим на лист с исходными данными (Лист1), и там в ближайшем пустом столбце (у нас это H) вводим название колонки: "Месяц". Спускаемся на клетку ниже и вводим простую формулу: "=МЕСЯЦ(A2)" и нажимаем Enter. Эта формула вычислит номер месяца для даты, лежащей в ячейке A2.
Сейчас нам нужно, чтобы этот столбец заполнился до конца таблицы. Для этого выделяем ячейку с нашей формулой, наводим курсор мышки на квадратную точку в правом нижнем углу выделенной ячейки и дважды кликаем.
Теперь в столбце H лежит номер месяца для каждой строки. Но сводная таблица об этом не знает: она не смотрит на этот столбец. Нужно добавить столбец H в источник данных. Для этого переходим на лист со сводной таблицей (у меня это Лист2), щёлкаем на сводной таблице один раз, переходим на вкладку "Анализ сводной таблицы" и выбираем "Источник данных".
Теперь автоматически отображается лист с исходными данными и открывается окошко для изменения диапазона данных сводной таблицы. Присмотримся подробнее.
Диапазон данных определяется так: название_листа!левая_верхняя_ячейка:правая_нижняя_ячейка. Мы видим, что правая ячейка $G$1730, а "Месяц" лежит в столбце H. Вручную исправляем G на H и нажимаем "ОК".
Посмотрим, что у нас получилось в итоге. "Месяц" теперь есть в списке полей справа. Значит, можно заменить "Дату" на "Месяц": снимаем галочку с "Даты", а потом тащим "Месяц" мышкой в область "Строки".
У нас получилась агрегированная по-новому таблица. Давайте посмотрим на неё.
Не забыли сохранить файл? 😉
Взгляд на таблицу. Доработки
В каждой строке содержится номер месяца: одна строка — один месяц. В каждом столбце находится тип случая: один столбец — один тип случая. В значениях — суммы по количеству заданных типов случая на заданный месяц. Это очень удобно.
Смертей мало, и данные есть только за один месяц. Это вряд ли может быть интересным. Ещё менее интересны пустые случаи. Давайте их уберём, чтобы не мешали. Для этого достаточно щёлкнуть по стрелочке у "Названий столбцов" и снять лишние галочки, а потом нажать "ОК".
Кроме того, не очень понятны "Общие итоги" для строк: там суммируются значения заболевших и выздоровевших. Абсолютно бесполезное число. Поэтому идём на вкладку "Конструктор", нажимаем "Общие итоги" и выбираем "Включить только для столбцов".
Посмотрим, что у нас получилось.
Результат работы
Итак, мы получили сводную таблицу. Такие таблицы лучше не публиковать, так как из них не видно, какие выводы мы хотим продемонстрировать. Но для себя мы видим рост заболеваемости и выздоровления от месяца к месяцу. Кроме того, можно заинтересоваться соотношением выздоровевших к заболевшим: за март из более чем 100 случаев всего 3 выздоровления. Это объясняется тем, что заболевших до середины марта не было, и из всех, кому не посчастливилось поймать вирус, в течение марта успели переболеть и выздороветь менее 3%. Остальные выздоравливали уже в апреле.
При анализе данных необходимо учитывать соотношение событий в пространстве и времени. Например, в нашем случае, если бы мы построили отношение выздоровевших к заболевшим, можно было бы ложно интерпретировать динамику как увеличение доли выздоравливающих от марта к апрелю. Поэтому мы и не считаем такое отношение и рассматриваем эти цифры раздельно. Ложная интерпретация данных — прямой путь к неправильным решениям. Об этом поговорим подробнее как-нибудь потом.
В процессе работы мы научились:
- Создавать сводную таблицу;
- Писать простые формулы;
- Добавлять данные в сводную таблицу через "источник данных";
- Фильтровать столбцы сводной таблицы;
- Управлять отображением общих итогов по столбцам или строкам.
Кроме этого, мы немного погрузились в анализ и интерпретацию данных. В последующих статьях мы будем касаться этого всё чаще. А до следующего занятия пройдите наш путь несколько раз, и попробуйте поделать то, чего мы не делали, наблюдая за изменениями, поиграйте с файлом. Это повысит вашу уверенность во владении Excel, даже если пока что вы пользуетесь малым количеством функций.
Буду благодарен за обратную связь в комментариях.
#Сводная таблица #обучение Excel #аналитика данных #сводник в Excel #excel для новичков
