Excel. Часть 3. Сводные таблицы

В предыдущих выпусках мастерской об Excel «Важные истории» рассказали об основах работы с этим инструментом и о том, как с помощью математических и статистических функций получить выводы из данных. В этот раз – о сводных таблицах.

Зачем нужны сводные таблицы?

В Excel и Google Spreadsheets есть мощный инструмент анализа данных – сводные таблицы. Они помогают из большого количества сырых данных сделать наглядный отчет, который поможет быстро найти выводы для журналистских историй. Сводные таблицы агрегируют данные и выполняют за вас их анализ. 

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

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

• Для примера мы будем использовать данные Минпросвещения России о количестве усыновленных за рубеж российских детей в 2019 году. Эту статистику мы использовали в своем исследовании о том, как введенные российскими властями ограничения для иностранцев повлияли на российских сирот. Этот набор данных для тренировки мы собрали из отчетов каждого региона. В них указан год, регион, из которого дети были усыновлены за рубеж, страна, в которую они отправились, количество усыновленных детей, количество усыновленных детей с инвалидностью и количество усыновленных по разным возрастным группам.

• Это руководство подходит для работы в Google Spreadsheets.

Как создать сводную таблицу?

Перед созданием сводной таблицы важно убедиться, что у каждого столбца есть заголовок – это понадобится при построении сводной таблицы. Чтобы создать сводную таблицу, надо выделить все данные, нажать «Данные» – «Создать сводную таблицу» – «Создать».

Появится новый лист, слева будет макет таблицы, а справа редактор сводных таблиц. В этом редакторе есть «Строки», «Столбцы», «Значения» и «Фильтры». В поле «Строки» указываем заголовок столбца из исходной таблицы, значения из которого хотим отобразить в строках. В поле «Столбцы» – заголовок столбца из исходной таблицы, значения из которого будут отображены в столбцах. В поле «Значение» – заголовок столбца из исходной таблицы с тем параметром, с которым собираемся производить расчеты.

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

Появится новая таблица со списком стран и количеством усыновленных в них детей. Внизу таблицы автоматически подсчитывается сумма. Так мы узнаем, что всего за 2019 год иностранцами было усыновлено 240 детей.

Сортировка в сводной таблице

В сводных таблицах работает сортировка. Чтобы наглядно увидеть, в какие страны было усыновлено больше всего детей, мы выбираем в поле «Страна» – «Сортировать» – «По убыванию», а в «Сортировать по» – «SUM параметра Всего усыновлено». В этом случае таблица перестроится, и мы увидим, что больше всего усыновленных за рубеж детей пришлось на Италию.

Фильтры в сводной таблице

По исходным данным мы видим, что в некоторые страны детей не усыновляли, но они все равно попали в государственные отчеты со значением «0» в столбце «Всего усыновлено». Чтобы избавиться от таких случаев, пригодятся фильтры. В поле «Фильтры» выбираем «Фильтровать по значению», параметр «Всего усыновлено» и убираем галочку с «0». В таблице остаются только страны без нулевых значений.

Процентное отображение значений

Данные в сводной таблице могут быть отображены в разных форматах. Сейчас количество усыновленных отображено в абсолютных числах, но мы можем показать их и в процентах. Для этого надо в поле «Значения» в параметре «Всего усыновлено» выбрать «Показывать как» – «% от итоговой суммы». Тогда мы увидим, например, что на Италию пришлось 68% детей из усыновленных за рубеж в 2019 году.

Добавление нескольких значений

В сводной таблице можно добавлять сразу несколько значений. Например, рядом с общим количеством усыновленных в страны показать, сколько из них – детей с инвалидностью. Для этого добавляем еще одного «Значение» – «Усыновлено детей с инвалидностью», а в окне «Суммировать по» – параметр SUM. Можно добавить еще одно значение с возрастом. Например, узнать, сколько в эти страны было усыновлено детей в возрасте до 1 года. Добавляем еще одно «Значение» – «до 1 года», а в окне «Суммировать по» – параметр SUM. Тогда мы увидим, что иностранные усыновители не брали в свои семьи младенцев – по крайней мере в 2019 году.

Перемена строк

Давайте теперь поработаем с регионами: например, узнаем, из каких регионов иностранцы чаще всего усыновляли детей в 2019 году. Для этого надо удалить из конструктора сводных таблиц «Строку» – «Страна» и добавить «Строку» – «Регион», а в «Значениях» оставить только параметр «Всего усыновлено». Выполним сортировку: в поле «Регион» выбираем «Сортировать» – «По убыванию», «Сортировать по» – «SUM параметра Всего усыновлено». В этом случае мы увидим, что больше всего детей, усыновленных иностранцами в 2019 году, жили в Кемеровской области.

Если нас интересует, сколько детей уехало из какого-то конкретного региона, мы снова можем воспользоваться «Фильтрами». В поле «Фильтр» нажимаем «Добавить» – параметр «Регион». Если нажать на выпадающее меню под надписью «Статус», мы увидим, что сейчас там выделены все регионы. Нажмем «Очистить» и введем в поле только один регион, например, «Москва». В появившейся сводной таблице мы увидим, что в 2019 году иностранцы усыновили всего двух детей из Москвы.

Суммирование значений разными способами

До этого момента все значения мы суммировали с помощью функции SUM, но в выпадающем меню есть и другие варианты, позволяющие производить расчеты с данными: среднее, минимум, максимум и другие. Давайте воспользуемся функцией COUNTUNIQUE, которая подсчитывает количество уникальных значений или диапазонов в наборе данных. Например, мы хотим узнать, сколько всего регионов участвуют в международном усыновлении. Для этого очистим таблицу и создадим новую. В «Строки» добавим «Страна». В «Значения» – «Регион» с параметром COUNTUNIQUE. В строке «Итого» сводной таблицы мы увидим, что всего 30 регионов участвовали в усыновлении.

Эта же таблица показывает нам, из какого количества регионов каждая страна усыновляла детей (функция COUNTUNIQUE посчитала количество уникальных пар «Страна» – «Регион»). Если мы добавим рядом еще одно «Значение» – «Всего усыновлено», мы увидим, что, например, все усыновленные Аргентиной дети – а их было 23 – родом из одного региона.

Чтобы посмотреть, из какого, мы можем добавить еще одну «Строку» – «Регион». Такой вид таблицы позволяет нам увидеть, какие страны усыновляли детей из каких регионов. В ней мы мы увидим, что Аргентина усыновила всех 23 детей из Пермского края. Возможно, за этим фактом, найденным в данных, скрывается какая-то интересная журналистская история.

Использование столбцов

Такую же таблицу можно было бы отобразить в другом виде, более привычном для взгляда, где строками являются регионы, а столбцами – страны. Для этого надо удалить. лишние значения: из «Строк» удалить «Страна», из «Значений» – «Регионы». И добавить в поле «Столбцы» – «Страна».

Сохранение результатов

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