Форматирование сводных таблиц в Google Sheets (шаг за шагом)
Сводные таблицы предлагают простой способ суммировать значения набора данных.
В этом руководстве представлен пошаговый пример того, как создать и отформатировать сводную таблицу для необработанного набора данных в Google Таблицах.
Шаг 1: введите данные
Во-первых, давайте введем некоторые данные о продажах для воображаемой компании:
Шаг 2: Создайте сводную таблицу
Затем выделите все данные. На верхней ленте щелкните Данные , а затем щелкните Сводная таблица .
Выберите ввод сводной таблицы на новом листе или на существующем листе, затем нажмите « Создать ».
В появившемся справа редакторе сводной таблицы добавьте Product в Rows , Region в Columns и Sales в Values .
Теперь наша сводная таблица будет выглядеть так:
Шаг 3. Выберите пользовательскую тему
Затем щелкните вкладку « Формат » на верхней ленте и нажмите « Тема» :
В появившемся справа окне выберите любую тему для сводной таблицы. Или вы можете нажать «Настроить », чтобы выбрать собственные цвета темы.
Мы выберем тему Simple Light :
Шаг 4: Добавьте границу и отцентрируйте текст
Далее мы выделим все данные и добавим границу вокруг каждой ячейки:
Наконец, мы центрируем значения данных внутри сводной таблицы:
Сводная таблица теперь отформатирована, чтобы выглядеть аккуратно и чисто.
Вы можете найти больше руководств по Google Таблицам на этой странице .
Google Таблицы: как создать сводную таблицу из нескольких листов
В следующем пошаговом примере показано, как создать сводную таблицу из нескольких листов в Google Sheets.
Шаг 1: введите данные
Предположим, у нас есть электронная таблица с двумя листами, названными неделя1 и неделя2 :
1 неделя:
Неделя 2:
Предположим, мы хотим создать сводную таблицу, используя данные с обоих листов.
Шаг 2. Объедините данные в один лист
Прежде чем мы сможем создать сводную таблицу, используя оба листа, мы должны объединить все данные в один лист.
Для этого мы можем использовать следующую формулу QUERY :
=QUERY(< week1!A1:C11 ; week2!A2:C11 >) Вот как использовать эту формулу на практике:
Обратите внимание, что данные с листов неделя1 и неделя2 теперь объединены в один лист.
Шаг 3: Создайте сводную таблицу
Чтобы создать сводную таблицу, мы выделим значения в диапазоне A1:C21 , затем перейдем на вкладку « Вставка » и затем нажмем « Сводная таблица ».
Затем мы можем создать следующую сводную таблицу:
Окончательная сводная таблица включает данные как из листов неделя 1, так и за неделю 2.
Дополнительные ресурсы
В следующих руководствах объясняется, как выполнять другие распространенные операции в Google Таблицах:
Сводные таблицы в Google Spreadsheets
Сводные таблицы в Google Таблицах имеют более скромный функционал по сравнению с Excel, но все же остаются полезным инструментом.
Добавить новую сводную таблицу можно следующим образом:
- Выделить исходные данные
- Нажать на кнопку “Сводная таблица” в меню “Данные”:
После нажатия на выделенную кнопку сразу будет создан новый лист, в котором и будет сводная — и редактор отчетов справа (то, что называется списком полей в Excel):
Рассмотрим пример: из таблицы с книгами получим сводную картину по тематикам. Исходная таблица выглядит так:
Для этого в редакторе отчетов в значения перенесем 2015 и 2016 год:
В строки добавим Тематику и отсортируем по убыванию по сумме 2016-го года:
И получим следующий результат:
Рассчитываемое поле
Рассчитываемое поле в сводной таблице позволяет производить дополнительные вычисления. Это аналог вычисляемого поля в Excel.
Добавить его можно, щелкнув на соответствующую кнопку меню после нажатия на “Добавить поле” в редакторе отчетов сводной таблицы:
После этого задайте новому полю имя и введите формулу для расчета.
В примере мы рассчитаем прирост в 2016 году.
Обратите внимание, что названия полей в формуле указываются в апострофах. Они соответствуют названиям столбцов исходной таблицы, на основе которой вы построили сводную.
Как видите, в сводной, как и в обычных диапазонах, можно менять формат ячеек. У ячеек с вычисляемым полем мы установили процентный формат.
В Google Таблицах, в отличие от Excel, можно напрямую ссылаться на ячейки сводной в формулах, без помощи функции ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ (GETPIVOTDATA).
То есть мы могли получить такой же результат и без рассчитываемого поля:
Но это менее предпочтительный вариант. Если вы поменяете структуру сводной, рассчитываемое поле будет работать для всех строк и любой структуры. А формулы придется протягивать вручную.
P.S. Функция ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ в Google Таблицах есть. Какой бы у вас ни был язык формул, ее название нужно вводить на английском: GETPIVOTDATA.
Открытые диапазоны в сводных таблицах
Существенный минус Сводных таблиц — они не работают с открытыми диапазонами.
Даже если вы укажете диапазон A2:E, он автоматически изменится на A2:E1000, где тысяча — номер последней строки в документе. Соответственно, если после этого в 1001 строке и ниже появятся данные, они не попадут в сводную.
Поэтому в случаях, если нужно работать с открытым диапазоном и данные будут постоянно пополняться, лучше использовать функцию QUERY, а не сводную.
Но! Диапазон вида A:E для сводных работать будет. Так что если исходные данные начинаются с первой же строки листа и будут пополняться, используйте диапазон без номеров строк.
Создание сводной таблицы в Гугл Таблицах
По сути, сводная таблица является инструментом для анализа представленных данных.
Создание сводной таблицы на основе рекомендаций
Допустим, имеется таблица расходов семьи, и требуется посчитать сумму расходов по каждому члену семьи в отдельности.
- Вместе с названиями столбцов выделить в Гугл Таблице область, которую собираемся заключить в сводную таблицу. (Не используйте в выделенной области скрытые столбцы и объединение ячеек, это приведет к ошибке) .
- В верхнем меню Гугл Таблицы нажимаем Вставка — Создать сводную таблицу . Указываем, где создавать сводную таблицу, на этом или на новом листе.
- После создания сводной таблицы, в правой части экрана открывается Редактор сводной таблицы , в котором приводятся рекомендации по построению сводной таблицы.
- Изучаем рекомендации, подходит ли для наших целей какая-либо из приведенных рекомендаций.
- Здесь же находится иконка предварительного просмотра сводной таблицы, нажав на которую можно увидеть, как будет выглядеть рекомендуемая сводная таблица.
- И если в конечном итоге рекомендация подходит для требуемой задачи, кликаем на нее.
- Создалась сводная таблица, где можно оценить полученный результат на предмет соответствия требуемой задаче. В нашем случае, в сводной таблице посчиталась сумма трат по каждому из членов семьи, что и требовалось изначально.
Как создать сводную таблицу вручную, без рекомендаций
Часто бывает, что при создании сводной таблицы автоматические рекомендации не предлагают ничего, что соответствовало бы требуемой задаче. На этот случай, сводную таблицу можно создать вручную, самостоятельно выбрав требуемые параметры для отображения.
После того, как создалась сводная таблица и открылся Редактор сводной таблицы, под полем рекомендаций находятся кнопки добавления данных в сводную таблицу.
Нажимаем кнопку Строки , и выбираем значение Владелец . Это означает, что в сводной таблице значения Владелец будет располагаться построчно в одном столбце.
Теперь выбираем подсчитываемые данные, нажимаем кнопку Значения , и выбираем значение Сумма
Таким образом мы самостоятельно создали такую же сводную таблицу, как и с применением рекомендаций Редактора сводной таблицы .
Резюме: с помощью сводной таблицы мы упорядочили данные из общего списка и рассчитали сумму затрат по каждому из членов семьи.
С помощью сводных таблиц из исходных данных удобно получать итоговые и средние значения. Например, из таблицы сделок, где уже рассчитана продолжительность сделки в днях, можно рассчитать среднюю продолжительность в днях по каждому из статусов сделки .
Из тех же исходных данных с помощью сводной таблицы можно увидеть статистику сделок по статусу по каждому из менеджеров . То есть, сколько у каждого менеджера было сделок с частичной оплатой и сколько с полной.
Используйте функционал сводных таблиц в Гугл Таблицах, чтобы быстро анализировать исходные данные и делать нужные вам выводы.