Сводные таблицы Excel: 10 полезных приемов

Сводные таблицы в Excel — мощный инструмент анализа данных, позволяющий быстро выявлять закономерности, фильтровать информацию и представлять ее в удобном формате. Однако даже опытные пользователи Excel не всегда знают о всех возможностях этого инструмента. В статье рассмотрим 10 приемов, которые помогут работать со сводными таблицами эффективнее.

Удаление источника данных сводной

Если вы создали сводную таблицу и не планируете вносить изменения в исходные данные, их можно удалить. Это не повлияет на дальнейшую работу со сводной: вы сможете менять настройки и вносить изменения в макет (переносить поля в те или иные области), поскольку все данные уже загружены в кэш. А удаление источника данных сделает файл легче.

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

Важно: этот метод не защитит данные от просмотра другими пользователями, так как их можно легко восстановить (если щелкнуть дважды на общий итог в таблице, то, как и в случае с кликом по любой другой ячейке сводной таблицы, будет создан отдельный с лист с теми строками, которые это число сформировали — в случае с итогом это будут все данные).

Быстрая фильтрация в сводной таблице

Чтобы быстро исключить ненужные значения из сводной таблицы, выделите их в строках или столбцах и нажмите Ctrl + – (минус). Данные отфильтруются, выделенные значения будут исключены в фильтре.

Добавление гистограмм в отдельный столбец

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



Добавьте дополнительный столбец с теми же значениями, затем примените к нему форматирование (гистограммы, значки или цветовую шкалу). В настройках условного форматирования включите опцию «Показывать только столбец» (Show Bar Only), чтобы оставить только визуальные индикаторы.

Группировка нескольких текстовых элементов

Если в исходных данных есть категории товаров (например, мониторы, ноутбуки и т. д.), и вам нужно объединить их в одну группу, чтобы смотреть общие данные по всем категориям (продажи, остатки), сделайте следующее:

1. Выделите несколько элементов, удерживая Ctrl.
2. Щелкните правой кнопкой мыши и выберите «Группировать» (Group), либо используйте вкладку «Анализ сводной таблицы» → «Группировка по выделенному» (Group Selection).
3. Переименуйте созданную группу (по умолчанию будет «Группа1») для удобства анализа.

Группировка дат для анализа сезонности

Если необходимо проанализировать сезонность (например, сравнить продажи за один и тот же месяц в разные годы), создайте сводную таблицу и вручную сгруппируйте даты только по месяцам. Это можно сделать на ленте: «Анализ сводной таблицы» → «Группировка по полю» или через контекстное меню, шелкнув по полю с датами правой кнопкой и выбрав «Группировать» (или Г на клавиатуре).

Так можно посмотреть суммарные показатели по месяцам или на их среднее значение.

Важно: это будут данные за все январи в периоде, то есть вы будете смотреть сезонность, а не динамику во времени.

Для просмотра динамики от месяца к месяцу, сделайте группировку и по годам, и по месяцам (в большинстве версий Excel поле с датами автоматически группируется в таком формате при переносе в область строк).

Число уникальных элементов

Допустим, у нас есть таблица со сделками: разные города, разные клиенты. Нам нужно узнать, сколько уникальных клиентов есть в каждом городе.

Если в сводной таблице выбрать подсчет «Количество» по городам, мы получим общее число строк, то есть количество сделок, а не число уникальных клиентов.

В стандартном наборе вычислений сводных таблиц (из 11 операций) такого подсчета нет. Однако, если при создании сводной таблицы добавить данные в модель Power Pivot, такая возможность появится. Заходить в сам Power Pivot не нужно, как и строить полноценную модель данных. Достаточно просто поставить галочку «Добавить эти данные в модель данных» (Add this data to the Data Model) при вставке сводной. После этого в параметрах поля значений можно выбрать операцию «Число разных элементов» (Distinct Count).

P. S. В Google Таблицах для этого есть встроенная функция COUNTUNIQUE, которая работает и в обычных расчетах, и в сводных таблицах.

Автоматическое обновление сводной таблицы при открытии книги Excel

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

1. Щелкните правой кнопкой по таблице и выберите «Параметры сводной таблицы» (PivotTable Options).
2. Перейдите на вкладку «Данные» (Data).
3. Включите флажок «Обновить при открытии файла» (Refresh Data When Opening The File).

Замена пустых значений нулями

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

Если хотите заменить пустые ячейки нулями:

1. Откройте вкладку «Анализ сводной таблицы» (PivotTable Analyze) → Параметры или в контекстном меню — «Параметры сводной таблицы» (PivotTable Options)
2. На вкладке «Макет и формат» (Layout & Format) введите 0 в поле «Для пустых ячеек отображать» (For empty cells show).

Превращение сводной таблицы в формулы

Если при создании сводной таблицы установить флажок «Добавить эти данные в модель данных» (Add this data to Data Model) — для этого нужен Power Pivot в вашей версии Excel, — то в дальнейшем сводную можно будет превратить в формулы.

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

Как это сделать:

1. Постройте сводную таблицу, не забыв включить флажок «Добавить эти данные в модель данных».
2. Перейдите на вкладку «Анализ сводной таблицы».
3. Выберите «Средства OLAP» → «Преобразовать в формулы» (OLAP Tools → Convert to Formulas).

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

Какие функции используются?

• КУБЗНАЧЕНИЕ (CUBEVALUE) — аналог области значений сводной. В ней задается подключение (к модели данных, первый аргумент = «ThisWorkbookDataModel») и ссылки на заголовки столбца, названия элементов (в нашем примере категория ресторана из области строк), может быть ссылка и на срез.
• КУБЭЛЕМЕНТ (CUBEMEMBER) — отвечает за заголовки: как названия элементов в строках и столбцах, так и название поля значений (например, «Сумма по полю …»).

Другие функции куба:

• КУБМНОЖ (CUBESET) — возвращает список всех значений из столбца.
• КУБПОРЭЛЕМЕНТ (CUBERANKEDMEMBER) — извлекает конкретное значение из КУБМНОЖ по его порядковому номеру.

Чередование строк в сводной: пользовательский стиль

Если стандартное чередование строк в сводной таблице вам не подходит, создайте свой стиль и настройте размер и формат строк:

1. Откройте в ленте вкладку «Конструктор» (Design) → Стили сводной таблицы (PivotTable Style Options) → Создать стиль сводной таблицы (New PivotTable Style).
2. Далее — «Первая полоса строк» (First Row Stripe), «Вторая полоса строк» (Second Row Stripe). Настройте цвета и форматирование для разных полос строк, например, одну белую и три голубых.

Обложка статьи отсюда.

The post Сводные таблицы Excel: 10 полезных приемов appeared first on Бизнес.