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

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

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