Форматирование сводных таблиц в 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 для сводных работать будет. Так что если исходные данные начинаются с первой же строки листа и будут пополняться, используйте диапазон без номеров строк.
Как работать со сводными таблицами в Гугл Таблицах?
Сводная таблица в Гугл Таблицах – это довольно сложный инструмент для продвинутых пользователей. Возможно, вы видели подобную опцию в Microsoft Excel? Если вы уже готовы работать с большими объемами данных и можете освоить подобные функции – давайте начинать! Сделали подробную вводную инструкцию для новичков. Поверьте, очень скоро вы оцените универсальность этого способа!
Создание
Начнем с небольшого ликбеза! Что вообще такое сводные таблицы в Гугл Таблицах? Этот инструмент пригодится тем, кто работает с большими объемами данных: удобно обобщать сведения по разным категориям, группировать данные, анализировать и измерять показатели.
Можно ввести огромное количество информации, при этом вам не придется пользоваться формулами и функциями (которые сложны для большинства начинающих пользователей). Вы сможете легко и быстро анализировать введенные цифры без боязни запутаться в массиве данных. Инструмент универсален в работе и показывает невероятно эффективные результаты.
Хотите узнать, как посмотреть историю изменений в Гугл Таблицах? Воспользуйтесь нашей инструкцией из прошлой статьи.
Сводные таблицы в Гугл доступны всем пользователям, они входят в базовый бесплатный функционал сервиса. Готовы научиться? Давайте начнем с создания!
- Откройте нужный документ;
- Выделите ряд ячеек, которые вам нужны (помните, что достаточно просто захватить и потянуть курсором за синий маркер);
- Найдите на панели сверху раздел «Вставка» ;
- Нажмите и перейдите к вкладке «Создать сводную таблицу в Гугл Таблицах» через выпадающее меню.
Уделим этому вопросу немного внимания. Разумеется, вы не можете копировать пустые ячейки, предварительно вам нужно заполнить столбцы и строчки нужной информацией — текстом, цифрами и др. Очень важно грамотно структурировать вводимые данные! Если в вашем документе хаос, воспользоваться инструментом не получится.
Что это значит? Перед тем, как сделать сводную таблицу в Гугл Таблицах, необходимо убедиться, что у каждого используемого столбца есть заголовок. Каждый столбик должен соответствовать конкретной теме, лучше не использовать объединенные ячейки.
А теперь продолжим. На экране появится окно с подтверждением, вам нужно проверить несколько параметров:
- Убедитесь, что выбран верный диапазон ячеек. При необходимости измените его вручную;
- Ниже выберите, куда будет копироваться информация (можно вставить в новый лист или сделать прямо на текущем листе).
Вот и все, автоматически откроется новая вкладка (если вы не работаете на существующем листе). Теперь можно переходить к редактированию и непосредственно к работе!
Настройка
Редактор сводная таблицы в Google Таблицах откроется автоматически после создания нового диапазона ячеек. Вы увидите его с правой стороны экрана.
Важно: мы рекомендуем работать на новом листе. Столь серьезный инструмент неразумно размещать там, где уже есть какая-то информация. Тем более, вы не ограничены в создании вкладок!
Хотите построить диаграмму Ганта в Гугл Таблицах самостоятельно? Узнай, как это сделать в прошлом обзоре.
Итак, вернемся к Google редактору. Все доступные инструменты перед вами, всего их четыре. Рассмотрим каждый из них.
Строки
Чтобы изменить этот параметр, жмем на кнопку «Добавить» .
- Выберите порядок (по возрастанию или по убыванию);
- Определите, как будут сортироваться данные (через выпадающий список).
При необходимости можно включить опции «Повторить заголовки строк» и «Показывать итог» . Для этого просто поставьте галочки в соответствующих полях.
Столбцы
Чтобы работать со столбцами в сводной таблице в Гугл Таблицах, нужно также нажать на значок добавления, а затем выбрать нужный диапазон из списка.
- Выбирайте порядок (по возрастанию/убыванию);
- Выбирайте способ сортировки.
В целом, никаких отличий от параметра «Строки» .
Значения
Когда вы нажмете на кнопку «Добавить» , можно будет создать рассчитываемое поле в сводной таблице Google.Оно нужно для произведения дополнительных вычислений!
- В открывшееся поле вводим нужную формулу;
- Ниже выбираем по какому параметру суммировать данные;
- Через выпадающее меню определяем, как показывать числа (по умолчанию, % от суммы значений в строке/столбце или от итоговой суммы).
Кроме того, можно выбрать любые столбцы и строки. Затем укажите тип суммирования и вывод данных.
Фильтры
Для любой сводной таблицы в Google Sheets можно настроить фильтрацию по определенным типам введенной информации.
- Нажимайте на кнопку «Добавить» ;
- Появится меню, где можно выбрать столбец/строку.
Далее откроется окно статуса объектов, нажимаем на выпадающий список и выбираем тип фильтрации.
Фильтровать по условию
- Не содержит/содержит данные;
- Текст содержит/не содержит;
- Текст начинается/заканчивается с…;
- Текст в точности;
- Дата (точно);
- Дата до/после определенного числа;
- Больше/больше или равно;
- Меньше/меньше или равно;
- Равно/не равно;
- Между/не между;
- Ваша формула.
Далее нужно ввести значение (в зависимости от того, какой фильтр для сводная таблицы в Google Таблицах вы выбираете). Появится поле для ввода текста/даты/формулы и других данных.
Фильтровать по значению
Если вы выбираете вариант «По значению» , можете выбрать значение из списка (одно, несколько или все сразу).
Последнее: чтобы удалить любое из добавленных правил, достаточно просто нажать на крестик наверху справа.
Обновление информации будет происходить автоматически (вы просто вносите изменения в штатном режиме). Но не забывайте следить за тем, чтобы отчет ссылался на весь диапазон исходных данных в Google документе — если произойдет сбой, возникнут трудности с правильностью подсчетов.
Для начала вам хватит информации о том, как сделать сводную таблицу в Гугл Таблицах – это базовая инструкция для начинающих. Поверьте, стоит вам самостоятельно изучить доступный функционал, как вы поймете, что необходимо делать. Уже через несколько часов вы станете настоящим профессионалом!