Как в сводной таблице итоги сделать сверху

Как в сводной таблице итоги сделать сверху

Argument ‘Topic id’ is null or empty

Сейчас на форуме

© Николай Павлов, Planetaexcel, 2006-2023
info@planetaexcel.ru

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

ООО «Планета Эксел» ИНН 7735603520 ОГРН 1147746834949

ИП Павлов Николай Владимирович ИНН 633015842586 ОГРНИП 310633031600071

Трюк №48. Как перемещать общие итоги в сводной таблице?

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

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

Эту функцию можно использовать так: =GETPIVOTDATA(«Sum of Amount»;$B$5), в русской версии Excel =ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ(«Сумма по полю»;$B$5) или так: =GETPIVOTOATA(«Amount»;$B$5), в русской версии Excel ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ («Сумма»;$В$5).

Обе функции позволят выделить нужные данные и будут отслеживать общий итог, если его переместить вверх, вниз, влево или вправо. Мы использовали адрес ячейки $В$5, но если вы укажете любую ячейку в пределах сводной таблицы, то всегда получите итог.

В первой функции используется поле Sum of Amount, а во второй — поле Amount. Если в области Данные (Data) сводной таблицы есть поле Amount, необходимо присвоить этому полю имя Amount. Если, однако, поле Amount используется в области Данные (Data) несколько раз, необходимо указать имя, которое вы присвоили ему, или же имя, принятое по умолчанию (рис. 4.5).

Рис. 4.5. Поле Amount используется два раза; в первом случае ему присвоено имя Sum of Amount, а во втором — Number Sold

Чтобы изменить эти поля, нужно дважды щелкнуть их. Это может сбить с толку, если вы еще не до конца поняли сводные таблицы. К счастью, в Excel 2002 и более поздних версиях процесс стал намного проще, так как можно поместить в ячейку аргументы и применить правильный синтаксис функции с помощью мыши. В любой ячейке введите = (знак равенства) и щелкните ячейку, в которой находится общий итог. Excel автоматически вставит нужные аргументы.
[stextbox сожалению, если вы используете мастер функций (Function Wizard) или сначала введете =ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ() (=GETPIVOTDATA()) и уже после этого щелкнете ячейку, в которой находится общий итог, Excel создаст неразбериху, попытавшись поместить в эту ячейку еще одну функцию ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ (GETPIVOTDATA).[/stextbox]
Вероятно, самый простой и менее запутанный способ получить общий итог — воспользоваться функцией =МАХ(PivGTCol), в русской версии Excel =MAKC(PivGTCol), где столбец, в котором содержится общий итог, имеет имя PivGTCol.

Кроме того, для получения из сводной таблицы совокупности чисел в зависимости от того, насколько они большие, можно воспользоваться функциями НАИБОЛЬШИЙ (LARGE) и НАИМЕНЬШИЙ (SMALL). Например, следующая формула выделяет второе по величине число из сводной таблицы: =LARGE(PivGTCol;2), в русской версии Excel =НАИБОЛЬШИЙ(PivGTCol;2).

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

Примеры работы со сводными таблицами в Excel

Работать со сводными таблицами Excel приходится в разных сферах. Можно быстро обрабатывать большие объемы информации, сравнивать, группировать данные. Это значительно облегчает труд менеджеров, продавцов, руководителей, маркетологов, социологов и т.д.

Сводные таблицы позволяют быстро сформировать различные отчеты по одним и тем же данным. Кроме того, эти отчеты можно гибко настраивать, изменять, обновлять и детализировать.

Создание отчета с помощью мастера сводных таблиц

У нас есть тренировочная таблица с данными:

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

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

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

Создам отчет с помощью мастера сводных таблиц. В новых версиях Excel он почему-то спрятано глубоко в настройках:

1. Выберите «Файл»-«Параметры»-«Панель быстрого доступа».
2. В выпадающем списке левой колонки: «Выбрать команду из» укажите «Все команды».
3. В левой колонке найдите по алфавитному порядку и выделите: «Мастер сводных таблиц и диаграмм». Нажмите на кнопку между колонками: «Добавить» чтобы инструмент переместился в правую колонку и нажмите ОК.

Теперь инструмент находится в панели быстрого доступа, а значит всегда под рукой.

1. Ставим курсор в любом месте таблицы с данными. Вызываем мастер сводных таблиц, нажимая на соответствующий инструмент, который теперь уже расположенный напанели быстрого доступа.
2. На первом шаге выбираем источник данных для формирования сводной таблицы. Нажимаем «Далее». Чтобы собрать информацию в сводный отчет из нескольких листов, выбираем: «в списке или базе данных Microsoft Excel».
3. На втором шаге определяем диапазон данных, на основании которых будет строиться отчет. Так как у нас стоит курсор в таблице, диапазон обозначится автоматически.
4. На третьем шаге Excel предлагает выбрать, куда поместить сводную таблицу. Жмем «Готово» и открывается макет.
5. Нужно обозначить поля для отображения в отчете. Допустим, мы хотим узнать суммы продаж по каждому продавцу. Ставим галочки – получаем:

Готовый отчет можно форматировать, изменять.

Как обновить данные в сводной таблице Excel?

Это можно сделать вручную и автоматически.

1. Ставим курсор в любом месте сводной таблице. В результате становится видна вкладка «Работа со сводными таблицами».
2. В меню «Данные» жмем на кнопку «Обновить» (или комбинацию клавиш ALT+F5).
3. Если нужно обновить все отчеты в книге Excel, выбираем кнопку «Обновить все» (или комбинацию клавиш CTRL+ALT+F5).

Настройка автоматического обновления при изменении данных:

1. На вкладке «Работа со сводными таблицами» (необходимо щелкнуть по отчету) выбираем меню «Параметры».
2. Открываем «Дополнительные параметры сводной таблицы». Открывается мастер.
3. В разделе «Данные» устанавливаем галочку напротив пункта «Обновить при открытии файла».

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

Некоторые секреты форматирования

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

Группировка по дате в сводной таблице Excel:

1. Источник информации – отчет с данными.
2. Так как нам нужна группировка по дате, выделяем любую ячейку с соответствующим значением. Щелкаем правой кнопкой мыши.
3. Из выпавшего меню выбираем «Группировку». Откроется инструмент вида:
4. В полях «Начиная с» и «По» Excel автоматически проставил начальную и конечную даты диапазона данных. Определяемся с шагом группировки. Для нашего примера – либо месяцы, либо кварталы. Остановимся на месяцах.

Получаем отчет, в котором четко видны суммы продаж по месяцам. Поэкспериментируем и установим шаг – «Кварталы». Результат – сводная таблица вида:

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

Чтобы убрать результаты группировки, необходимо щелкнуть по ячейке с данными правой кнопкой мыши и нажать разгруппировать. Либо выбрать данный параметр в меню «Структура».

Работа с итогами

У нас есть сводный отчет такого вида:

Видны итоги по месяцам (сделано «Группировкой») и по наименованиям товаров. Сделаем отчет более удобным для изучения.

Как в сводной таблице сделать итоги сверху:

1. «Работа со сводными таблицами» — «Конструктор».
2. На вкладке «Макет» нажимаем «Промежуточные итоги». Выбираем «Показывать все промежуточные итоги в заголовке группы».
3. Получается следующий вид отчета:

Уже нет той перегруженности, которая затрудняла восприятие информации.

Как удалить промежуточные итоги? Просто на вкладке макет выбираем «Не показывать промежуточные суммы»:

Получим отчет без дополнительных сумм:

Детализация информации

Огромные сводные таблицы, которые составляются на основании «чужих» таблиц, периодически нуждаются в детализации. Мы не знаем, откуда взялась сумма в конкретной ячейке Excel. Но можно это выяснить, если разбить сводную таблицу на несколько листов.

1. В марте продано двуспальных кроватей на сумму 23 780 у.е. Откуда взялась эта цифра. Выделяем ячейку с данной суммой и щелкаем правой кнопкой мыши и выбираем опцию:
2. На новом листе откроется таблица с данными о продажах товара.

Мы можем переместить всю сводную таблицу на новый лист, выбрав на вкладке «Действия» кнопку «Переместить».

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

В нашем примере – ВСЕ товары, ВСЕ даты, ВСЕ суммы и магазины. Возможно, пользователю не нужны некоторые элементы. Они просто загромождают отчет и мешают сосредоточиться на главном. Уберем ненужные элементы.

1. Нажимаем на стрелочку у названия столбца, где будем корректировать количество информации.
2. Выбираем из выпадающего меню название поля. В нашем примере – это название товара или дата. Мы остановимся на названии.
3. Устанавливаем фильтр по значению. Исключим из отчета информацию по односпальным кроватям – уберем флажок напротив названия товара.

Жмем ОК – сводная таблица меняется.

• Excel Formula Examples
• Создать таблицу
• Форматирование
• Функции Excel
• Формулы и диапазоны
• Фильтр и сортировка
• Диаграммы и графики
• Сводные таблицы
• Печать документов
• Базы данных и XML
• Возможности Excel
• Настройки параметры
• Уроки Excel
• Макросы VBA
• Скачать примеры

Управление итогами с помощью DAX-формул

Хотите, научим вас плохому – как «поломать» итоги в сводной таблице? Судя по вопросам, которые задают нам подписчики – да. Про итоговую строку бывают разные вопросы: как в ней вместо суммы показать среднее, как показать два итога, как показать итог в самом начале. И все это про сводные таблицы Excel или матрицы Power BI.
Это хорошие вопросы. Ответим на них по порядку:

1) как управлять цифрами в итоговой строке;
2) как разместить итоги или промежуточные итоги в начале таблицы или раздела.

И еще разберём, какие вычисления больше подойдут для Power BI, а какие – для Excel.

Ломаем итоговую строку

Итак, нестандартная ситуация: в строке итогов нужно показать какое-то необычное число, отличающееся от основных данных в таблице. Например, в таблице – просто значения, а в итогах или промежуточных итогах должно быть среднее или нарастающая сумма. Или вообще какие-то коэффициенты. В общем, разбираем, как «обмануть» итоговую строку таблицы и показать там что-то неожиданное.

Для управления итогами нам потребуются DAX-формулы , применение которых немного отличается в Power BI и Excel. Примеры таких формул – ISINSCOPE , ISFILTERED и SELECTEDVALUE . И реже HASONEVALUE , ISCROSSFILTERED , HASONEFILTER , FILTERS .

В Power BI оптимальная формула для управления итогами для разных уровней иерархии – ISINSCOPE . С помощью этой формулы можно определить, сгруппированы ли уровни иерархии.

Разберем, как работает эта формула на примере справочника товаров. В справочнике на рисунке слева есть товары, подгруппы и группы.

С помощью ISINSCOPE и конструкции SWITCH + TRUE добавим в таблицу «уровни»: для товаров – символ с кружком, а для групп и подгрупп – текст.

уровень = SWITCH ( TRUE (), ISINSCOPE ( ‘ спТовары ‘ [товар] ), UNICHAR ( 68178 ), ISINSCOPE ( ‘ спТовары ‘ [подгруппа] ), » подгруппа » , ISINSCOPE ( ‘ спТовары ‘ [группа] ), » группа » , » итог » )

По-научному: функция ISINSCOPE возвращает значение ИСТИНА, если столбец включен в контекст фильтра и является столбцом группировки для текущей строки.

Формула для значений в итоговой строке почти такая же – NOT + ISINSCOPE . В логическом отрицании NOT потребуется перечислить все уровни строк в таблице.