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

Обновлено 21.06.2022

Добрый день! Уважаемые читатели и гости одного из крупнейших IT блогов в России Pyatilistnik.org. В прошлый раз я вам рассказал, как разделить столбец в CSV на ряд столбцов, судя по обратной связи статья оказалась полезной. Я продолжаю изучать Exel и Google таблицы и в сегодняшней публикации я хочу рассмотреть одну из своих задач, которую я выполняю каждый год по планированию ресурсов ну будущее. Речь пойдет, о том, как создать сводную таблицу, что это вообще такое и как вы можете это применять в своей практике.

Что такое сводные таблицы?

Сводные таблицы (Pivot Table) - Как любит писать Microoft, это мощный инструмент для расчета, обобщения и анализа данных, который позволяет вам видеть сравнения, закономерности и тенденции в ваших данных. Если сказать более развернуто и не сухим языком, а человеческим, то сводная таблица используется для суммирования, сортировки, реорганизации, группировки, подсчета, итоговых или средних данных, хранящихся в таблице. Это позволяет нам преобразовывать столбцы в строки и строки в столбцы. Он позволяет группировать по любому полю (столбцу) и использовать для них расширенные вычисления.

Сводные таблицы - идеальное решение, когда вам нужно суммировать и анализировать большие объемы данных. Всего за несколько кликов вы получите доступ к совершенно новому набору информации. Как уже было сказано, Exel или Google таблицы - это одно из решений для создания сводных таблиц, но лучшие инструменты не требуют написания сложных формул или необходимости начинать все заново каждый раз, когда вы хотите организовать данные по-другому. Вариант перетаскивания для перемещения ваших полей - самый простой способ.

Каковы практические примеры сводной таблицы?

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

• Группировать элементы/записи/строки по категориям
• Подсчитать количество предметов в каждой категории,
• Суммировать стоимость предметов или вычислить среднее значение, найти минимальное или максимальное значение и т.д.
• Создать % итогов
• Сегментируйте данные по дате, пользователю или другой переменной и вычисляйте итоги
• Организуйте данные в столбцы и строки с помощью автоматических вычислений, которые в противном случае были бы невозможны в Excel

Пример применения сводных таблиц на колоде карт

Для того чтобы вы могли лучше понять всю ту писанину, что я оставил выше, я бы хотел рассмотреть пример на обычной колоде покерных карт из 52 штук. У каждой карты есть символ (трефы ♣, бубны ♦, червы ♥, пики ♠), значение (A, от 1 до 10, J, QK) и цвет (черный или красный)

С помощью сводных таблиц (pivot table) вы легко сможете ее сгруппировать по цветам. Мы разделили карты на две категории или, если хотите, в две новые колоды.

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

Теперь мы знаем, что в стандартной колоде из 52 карт одинаковое количество черных и красных карт. В первом столбце мы видим метки черный, и красный. Они называются метками строк. Разве это не сбивает с толку. Ярлыки строк в столбце? Да, потому что каждой строке в начале нужен свой ярлык. В результате метки располагаются одна под другой и образуют столбец. Не запутайтесь. Давайте мы повернем все на 90 градусов по часовой стрелке. В результате получим. Теперь pivot table представляет нам ту же информацию, но слегка иначе. Какая форма нам больше нравится, зависит только от нас. Одно отличие состоит в том, что у нас больше нет меток строк. Вместо этого у нас есть метки столбцов. Ярлыки столбцов по-прежнему относятся к красному и черному цветам. Дело в том, что теперь они маркируют каждый из столбцов.

Давайте еще усложним задачу для функции сводных таблиц и создадим группировку по мастям. Напоминаю наши символы (трефы ♣, бубны ♦ , червы ♥ , пики ♠). Таким образом, мы можем сортировать по группам по символу.

Опять же, мы можем попросить компьютер подсчитать карты за нас.

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

При необходимости вы можете сделать вот такие варианты:

Давайте еще раз их посчитаем.

Вот такой вариант

Многоуровневые метки строк

Многоуровневые подписи столбцов. Второй и третий случаи на первый взгляд могут показаться немного сложными. Только представьте, что мы сначала делим карточки на категории по цвету. Далее мы делим карты на 4 и 4 категории в соответствии с символом.

Так же мы можем посчитать процентное соотношение. Мы видим, что всего 26 красных карточек, всего 26 черных карточек и 13 карточек с каждым из символов. Важно отметить, что в итоговых значениях по столбцам учитываются все карточки (52), а также итоги по строкам (52).  Это потому, что столбцы и строки представляют разные типы категорий. Вы заметили, насколько прекрасна сводная таблица? Все карты разделены по ячейкам сводной таблицы. Каждая карта представлена ​​ровно один раз. Это похоже на реальный мир - вы не можете положить одну карту в две колоды одновременно. Это означает, насколько большую часть общего числа представляет каждый столбец или строка.

А вот применение процентов. Как видим, половина карточек красные, половина - черные. Отдельные символы всегда представлены четвертью всех карт.

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

Первым делом я себе сделаю памятку и заодно покажу вам, как пользоваться сводными таблицами в Гугл таблицах, мы ими уже пользовались при удалении пустых ячеек. На входе у меня есть выгруженные данные из vCenter Server по производительности кластера ESXI за последний год. На основании этих данных производится прогнозирование закупок в виде серверов, чтобы обеспечить новые проекты 2021 года. Данная информация послужит одним из факторов по формированию бюджета. Вот такие столбцы у меня есть:

• Time - Время
• Usage - использование CPU
• Total - Общее количество CPU в кластере
• Host - Имя кластера ESXI
• Date - Дата

В сводной таблице я хочу сформировать данные по использованию процессорных мощностей, и в дальнейшем применять к этим данным формулы. Чтобы сформировать сводную таблицу в Google таблицах вам необходимо выделить все столбцы, которые будут исходными данными для нее. В моем случае, это диапазон столбцов от А до К. Выделяем их и нажимаем в верхнем меню, пункт "Данные - Создать сводную таблицу"

У вас появится новое окно "Новая сводная таблица", тут вам нужно определиться, будет ли она на новой странице или же в рамках этой. И там и там нужно будет указать диапазон столбцов, куда она будет формироваться. Я выберу существующий лист и задам столбцы от M до P.

У вас откроется редактор сводной таблицы, тут вы и будите ее формировать. На выбор у вас будет 4 значения:

• Строки
• Столбцы
• Значения
• Фильтры

Нажимаем добавить строку.

Добавляем строку, в моем примере это будет дата.

Как видим дата растянулась вниз по строкам.

Теперь нам нужно добавить значения в сводную таблицу, для этого нажимаем кнопку добавить и по очереди выбираем нужные пункты. У меня это пункт "Usage", "Total".

Как видите у меня заполнились строки по двум столбцам из выбранных пунктов.

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

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

Еще пример, предположим, что вам необходимо посчитать ресурсы потребляемые каждым проектом в вашей виртуальной инфраструктуре. Вы делаете выгрузку в vCenter по "custom attributes" и из CSV создаете читаемый файл с колонками вот такого вида.