Удаление источника данных сводной
Если вы построили сводную и не планируете дальнейшие манипуляции с источником данных, то его можно просто удалить.
Это не помешает вам в будущем настраивать сводную, менять ее макет (переносить поля в те или иные области).
Ведь данные при создании сводной загружаются в кэш, так что все равно остаются в файле. И удаление источника сделает его легче.
Это не помешает вам в будущем настраивать сводную, менять ее макет (переносить поля в те или иные области).
Ведь данные при создании сводной загружаются в кэш, так что все равно остаются в файле. И удаление источника сделает его легче.
А если вдруг захочется посмотреть на исходные данные, можно просто щелкнуть дважды на общий итог. Ведь двойной щелчок = создание листа со всеми строками, которые «стоят» за тем числом, на которое вы щелкнули в сводной. А общий итог складывается из всех строк исходных данных.
Так что если вы рассматривали вариант удаления источника, чтобы скрыть его от других пользователей и оставить им только сводную, увы, это не сработает 😺
Так что если вы рассматривали вариант удаления источника, чтобы скрыть его от других пользователей и оставить им только сводную, увы, это не сработает 😺
Быстрая фильтрация в сводной таблице
Если вам нужно быстро исключить некоторые значения из сводной: выделите то, что нужно убрать (в строках или столбцах отчета сводной таблицы) и нажмите Ctrl + - (минус).
Данные будут отфильтрованы, те значения, что вы выделяли, будут исключены в фильтре.
Данные будут отфильтрованы, те значения, что вы выделяли, будут исключены в фильтре.
Добавляем гистограммы в сводной таблице отдельным столбцом
Если вы хотите, чтобы визуализация была не "поверх ячеек" с данными, а отдельным столбиком, и чтобы это было частью сводной (то есть отражала актуальные данные в случае обновления исходника и соответственно сводной) — это способ для вас.
В двух словах: мы добавляем еще один столбец с теми же суммами, применяем к нему условное форматирование (это могут быть не только гистограммы, но и значки / цветовая шкала) и потом в настройках правила условного форматирования включаем опцию «Показывать только столбец» (Show Bar Only).
Группировка нескольких текстовых элементов в сводной
Допустим, в ваших исходных данных есть категория товара. Мониторы, ноутбуки и прочее, прочее.
Вы хотите объединить несколько категорий в одну группу в сводной таблице, чтобы смотреть на общие данные (продажи, остатки…) по всем сразу.
Для этого:
1 Выделяем несколько элементов (зажав клавишу Ctrl);
2 Щелкаем правой кнопкой и в контекстном меню выбираем Группировать / Group
или
2 Нажимаем на ленте на вкладке «Анализ сводной таблицы» (PivotTable Analyze) — «Группировка по выделенному» (Group Selection)
3 Щелкаем на название группы (по умолчанию будет «Группа1») и переименовываем.
Вы хотите объединить несколько категорий в одну группу в сводной таблице, чтобы смотреть на общие данные (продажи, остатки…) по всем сразу.
Для этого:
1 Выделяем несколько элементов (зажав клавишу Ctrl);
2 Щелкаем правой кнопкой и в контекстном меню выбираем Группировать / Group
или
2 Нажимаем на ленте на вкладке «Анализ сводной таблицы» (PivotTable Analyze) — «Группировка по выделенному» (Group Selection)
3 Щелкаем на название группы (по умолчанию будет «Группа1») и переименовываем.
Группировка дат в сводной: анализируем сезонность
Задача: посмотреть на сезонность, какой месяц «лучше», какой «хуже». На сезонность — то есть на январь за все годы, на февраль за все годы, и так далее.
Для этой задачи извлечем из чемоданчика всемогущий мультитул — сводную таблицу. По умолчанию в сводной даты группируются по годам-кварталам-месяцам, то есть мы смотрим на данные в рамках каждого года. А нам нужно убрать этот верхний уровень, смотреть только на уровень месяцев (или кварталов, если вам нужно сезонность на этом уровне). Для этого группируем данные сами — только по месяцам.
Это можно сделать на ленте: Анализ сводной таблицы — Группировка по полю
или в контекстном меню — щелкаем по полю с датами в сводной правой кнопкой мыши и нажимаем «Группировать» (или нажимаем Г на клавиатуре)
После группировки можно посмотреть на сумму показателя по месяцам, а можно на среднее значение. Еще раз уточняем: теперь это данные за все январи в периоде, то есть мы не смотрим на динамику во времени, а смотрим на сезонность! Если нам нужна динамика от месяца к месяцу, то нужна группировка и по годам, и по месяцам, как было изначально при построении сводной (в большинстве версий Excel поле с датами само группируется в таком формате при его переносе в область строк)
Задача: посмотреть на сезонность, какой месяц «лучше», какой «хуже». На сезонность — то есть на январь за все годы, на февраль за все годы, и так далее.
Для этой задачи извлечем из чемоданчика всемогущий мультитул — сводную таблицу. По умолчанию в сводной даты группируются по годам-кварталам-месяцам, то есть мы смотрим на данные в рамках каждого года. А нам нужно убрать этот верхний уровень, смотреть только на уровень месяцев (или кварталов, если вам нужно сезонность на этом уровне). Для этого группируем данные сами — только по месяцам.
Это можно сделать на ленте: Анализ сводной таблицы — Группировка по полю
или в контекстном меню — щелкаем по полю с датами в сводной правой кнопкой мыши и нажимаем «Группировать» (или нажимаем Г на клавиатуре)
После группировки можно посмотреть на сумму показателя по месяцам, а можно на среднее значение. Еще раз уточняем: теперь это данные за все январи в периоде, то есть мы не смотрим на динамику во времени, а смотрим на сезонность! Если нам нужна динамика от месяца к месяцу, то нужна группировка и по годам, и по месяцам, как было изначально при построении сводной (в большинстве версий Excel поле с датами само группируется в таком формате при его переносе в область строк)
Число уникальных элементов
Допустим, у нас есть таблица со сделками: в разных городах с разными клиентами. Мы хотим понять, сколько в каждом городе у нас клиентов.
Если в сводной считать "Количество" по городам, то это будет количество строк, то есть сделок, а не уникальных значений.
Если в сводной считать "Количество" по городам, то это будет количество строк, то есть сделок, а не уникальных значений.
Увы, в стандартном наборе вычислений (из 11 операций) в сводных подсчета уникальных значений нет. Но если добавить наши данные в модель данных (Power Pivot) при создании сводной, то такая возможность появится! В сам Power Pivot можно даже не заходить, и не обязательно собственно строить модель данных, добавляя туда еще какие-то таблицы. Если вам нужна только эта возможность — просто включите флажок «Добавить эти данные в модель данных» (Add this data to the Data Model) при вставке сводной. И далее в параметрах поля значений выбирайте операцию «Число разных элементов» (Distinct Count).
P. S. А в Google Таблицах функция для подсчета уникальных в сводных есть — COUNTUNIQUE (как и обычная функция рабочего листа, не в сводных, с таким именем).
P. S. А в Google Таблицах функция для подсчета уникальных в сводных есть — COUNTUNIQUE (как и обычная функция рабочего листа, не в сводных, с таким именем).
Автоматическое обновление сводной при открытии книги
Хотите, чтобы сводная таблица обновлялась автоматически при открытии файла? Например, если вы отправляете книгу Excel с отчетом коллегам и не уверены, что все обновят сводную сами.
Хотите, чтобы сводная таблица обновлялась автоматически при открытии файла? Например, если вы отправляете книгу Excel с отчетом коллегам и не уверены, что все обновят сводную сами.
1 Правая кнопка по сводной — «Параметры сводной таблицы» (PivotTable Options)
2 Переходим на вкладку «Данные» (Data)
3 Включаем флажок «Обновить при открытии файла» (Refresh Data When Opening The File)
2 Переходим на вкладку «Данные» (Data)
3 Включаем флажок «Обновить при открытии файла» (Refresh Data When Opening The File)
Заменяем в сводной таблице пустые значения нулями
По умолчанию в области значений в сводной нулевые значения будут пустыми (допустим, если по конкретному параметру или сочетанию параметров нет остатков / не было продаж и т. п.)
Если хотите заменить пустые ячейки нулями:
1 Вкладка «Анализ сводной таблицы» (PivotTable Analyze) —> Параметры или правая кнопка мыши по сводной и в контекстном меню «Параметры сводной таблицы» (PivotTable Options).
2 На первой вкладке «Макет и формат» (Layout & Format) вводим значение 0 в поле «Для пустых ячеек отображать» (For empty cells show).
По умолчанию в области значений в сводной нулевые значения будут пустыми (допустим, если по конкретному параметру или сочетанию параметров нет остатков / не было продаж и т. п.)
Если хотите заменить пустые ячейки нулями:
1 Вкладка «Анализ сводной таблицы» (PivotTable Analyze) —> Параметры или правая кнопка мыши по сводной и в контекстном меню «Параметры сводной таблицы» (PivotTable Options).
2 На первой вкладке «Макет и формат» (Layout & Format) вводим значение 0 в поле «Для пустых ячеек отображать» (For empty cells show).
Превращаем сводную в формулы
Если при создании сводной таблицы вы включите флажок «Добавить эти данные в модель данных» (Add this data to Data Model) — для этого нужен Power Pivot в вашей версии Excel, то впоследствии можно будет превратить сводную таблицу в формулы.
Что это за формулы такие? Формулы куба. Они могут напомнить вам функцию ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ (GETPIVOTDATA), но она получает данные из существующей сводной. А функции куба заменяют собой сводную! И проще всего получить их в готовом виде, чтобы изучить, преобразовав существующую сводную таблицу.
Строим сводную таблицу — включаем флажок «Добавить эти данные в модель данных» — переходим на вкладку «Анализ сводной таблицы» и там выбираем «Средства OLAP» — Преобразовать в формулы" (OLAP tools — Convert to Formulas).
Что изменится? Каждая ячейка сводной станет независимой формулой. Больше не будет единого объекта — сводной таблицы — будут отдельные формулы. Вы сможете переупорядочить вашу сводную как вам захочется:
Если при создании сводной таблицы вы включите флажок «Добавить эти данные в модель данных» (Add this data to Data Model) — для этого нужен Power Pivot в вашей версии Excel, то впоследствии можно будет превратить сводную таблицу в формулы.
Что это за формулы такие? Формулы куба. Они могут напомнить вам функцию ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ (GETPIVOTDATA), но она получает данные из существующей сводной. А функции куба заменяют собой сводную! И проще всего получить их в готовом виде, чтобы изучить, преобразовав существующую сводную таблицу.
Строим сводную таблицу — включаем флажок «Добавить эти данные в модель данных» — переходим на вкладку «Анализ сводной таблицы» и там выбираем «Средства OLAP» — Преобразовать в формулы" (OLAP tools — Convert to Formulas).
Что изменится? Каждая ячейка сводной станет независимой формулой. Больше не будет единого объекта — сводной таблицы — будут отдельные формулы. Вы сможете переупорядочить вашу сводную как вам захочется:
Какие это функции?
КУБЗНАЧЕНИЕ / CUBEVALUE — это как область значений сводной, в ней задается подключение (к модели данных, первый аргумент = «ThisWorkbookDataModel») и ссылки на заголовки столбца, названия элементов (в нашем примере категория ресторана из области строк), может быть ссылка и на срез.
Заголовки в сводной (и заголовок области значений в духе «Сумма по полю …», и названия элементов в строках/столбцах) будут заданы другой функцией — КУБЭЛЕМЕНТ / CUBEMEMBER.
Есть и другие функции куборв, например, КУБМНОЖ / CUBESET (возвращает список всех значений из столбца) и КУБПОРЭЛЕМЕНТ / CUBERANKEDMEMBER, которая из ячейки с функцией КУБМНОЖ может извлечь значение по его порядковому номеру.
КУБЗНАЧЕНИЕ / CUBEVALUE — это как область значений сводной, в ней задается подключение (к модели данных, первый аргумент = «ThisWorkbookDataModel») и ссылки на заголовки столбца, названия элементов (в нашем примере категория ресторана из области строк), может быть ссылка и на срез.
Заголовки в сводной (и заголовок области значений в духе «Сумма по полю …», и названия элементов в строках/столбцах) будут заданы другой функцией — КУБЭЛЕМЕНТ / CUBEMEMBER.
Есть и другие функции куборв, например, КУБМНОЖ / CUBESET (возвращает список всех значений из столбца) и КУБПОРЭЛЕМЕНТ / CUBERANKEDMEMBER, которая из ячейки с функцией КУБМНОЖ может извлечь значение по его порядковому номеру.
Чередование строк в сводной: пользовательский стиль
В сводных, как и в «обычных» (умных) таблицах можно включать чередование строк.
Но не всегда стандартный вариант вам подойдет.
Если нужен свой вариант (1 строка белая + 3 голубых, например) — создаем свой стиль сводной таблицы и там настраиваем размер и формат строк.
Вкладка «Конструктор» на ленте (контекстная — появится только при активации сводной) —> Стили сводной таблицы —> Создать стиль сводной таблицы
Design —> PivotTable Style Options —> New PivotTable Style
И далее «Первая полоса строк», «Вторая полоса строк» (First Row Stripe, Second Row Stripe).
Но не всегда стандартный вариант вам подойдет.
Если нужен свой вариант (1 строка белая + 3 голубых, например) — создаем свой стиль сводной таблицы и там настраиваем размер и формат строк.
Вкладка «Конструктор» на ленте (контекстная — появится только при активации сводной) —> Стили сводной таблицы —> Создать стиль сводной таблицы
Design —> PivotTable Style Options —> New PivotTable Style
И далее «Первая полоса строк», «Вторая полоса строк» (First Row Stripe, Second Row Stripe).
