Сводные таблицы в Google Spreadsheets

Сводные таблицы в Google Таблицах имеют более скромный функционал по сравнению с Excel, но все же остаются полезным инструментом.

Добавить новую сводную таблицу можно следующим образом:

1. Выделить исходные данные
2. Нажать на кнопку “Сводная таблица” в меню “Данные”:

После нажатия на выделенную кнопку сразу будет создан новый лист, в котором и будет сводная - и редактор отчетов справа (то, что называется списком полей в Excel):

Рассмотрим пример: из таблицы с книгами получим сводную картину по тематикам. Исходная таблица выглядит так:

Для этого в редакторе отчетов в значения перенесем 2015 и 2016 год:

В строки добавим Тематику и отсортируем по убыванию по сумме 2016-го года:

И получим следующий результат:

Рассчитываемое поле

Рассчитываемое поле в сводной таблице позволяет производить дополнительные вычисления. Это аналог вычисляемого поля в Excel.

Добавить его можно, щелкнув на соответствующую кнопку меню после нажатия на “Добавить поле” в редакторе отчетов сводной таблицы:

После этого задайте новому полю имя и введите формулу для расчета.

В примере мы рассчитаем прирост в 2016 году.

Обратите внимание, что названия полей в формуле указываются в апострофах. Они соответствуют названиям столбцов исходной таблицы, на основе которой вы построили сводную.

Результат:

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

В Google Таблицах, в отличие от Excel, можно напрямую ссылаться на ячейки сводной в формулах, без помощи функции ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ (GETPIVOTDATA).

То есть мы могли получить такой же результат и без рассчитываемого поля:

Но это менее предпочтительный вариант. Если вы поменяете структуру сводной, рассчитываемое поле будет работать для всех строк и любой структуры. А формулы придется протягивать вручную.

P.S. Функция ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ в Google Таблицах есть. Какой бы у вас ни был язык формул, ее название нужно вводить на английском: GETPIVOTDATA.

Открытые диапазоны в сводных таблицах

Существенный минус Сводных таблиц - они не работают с открытыми диапазонами.

Даже если вы укажете диапазон A2:E, он автоматически изменится на A2:E1000, где тысяча - номер последней строки в документе. Соответственно, если после этого в 1001 строке и ниже появятся данные, они не попадут в сводную.

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

Но! Диапазон вида A:E для сводных работать будет. Так что если исходные данные начинаются с первой же строки листа и будут пополняться, используйте диапазон без номеров строк.