Сводные таблицы часть 1 (GS20)⁠⁠

Привет, дорогие чатлане!

Сегодня поговорим о сводных таблицах. Можно сказать что это вторая веха в освоении таблиц после ВПРа и про них чаще всего спрашивают на собеседованиях.

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

Пост же я разобью на две части. Первая - собственно своды, вторая - обращение к сводам извне и аналог сводов в функции query().

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

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

Второе (и это уже обязательно) - в таблице не должно быть объединенных ячеек.

Покажу песочницу, которую буду использовать для демонстрации:

Н.Б. Для эксельщиков: ГТ плохо работает с датами в сводах, поэтому месяц пришлось вынести в отдельный столбец, для экселя - это не обязательно.

Итак, как создать свод?

Выделяем нужный диапазон, выбираем пункт меню Данные // Сводная таблица (в экселе аналогично в меню Вставить // Insert).

Конструкторы сводов отличаются для ГТ и экселя и дальше буду рассказывать только для ГТ.

Для наглядности буду собирать свод на этом же листе:

Этап следующий:

Красная зона - сама сводная таблица, синяя - конструктор свода.

Сделаю пример и расскажу о том что это и зачем:

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

В моем примере - я взял значения из столбца "Месяц" и сделал их строками новой таблицы. Аналогично - менеджеров я сделал столбцами, а на пересечении - сумма значений столбца Сумма.

Можно создать еще большую детализацию:

Немного детальнее про конструктор:

Здесь можно выбрать порядок сортировки (возрастание или убывание), саму сортировку - алфавитная, значения конкретного столбца свода, показать или скрыть итоги и показать или скрыть названия строк для каждой строки ниже уровнем (скрин ниже):

Если поставить последнюю галочку - в этой ячейке будет повторно записано значение "6".

И про значения на пересечениях:

Здесь представлены все варианты того, что можно сделать со значениями на пересечениях (просуммировать, посчитать количество, посчитать среднее, максимальное, отклонение, произведение). Отдельно оставлю ссылку на документацию для вариантов VAR и VARP: https://support.microsoft.com/en-us/office/var-varp-functions-e08f5f59-22f3-43d1-863f-a195df09904f#:~:text=Remarks,a variance cannot be calculated.

Если сделать двойной клик по любой ячейке свода - будет создан лист, который покажет все строки оригинальной таблицы, которые были записаны в эту ячейку:

Последнее - фильтры. Они позволяют отсечь те или иные значения из сводов и работает как обычный фильтр в таблицах: