GetPivotData в Excel - Советы по Excel

Вы только что создали сводную таблицу в Excel. Вы щелкаете за пределами сводной таблицы. Вы строите формулу Excel. Вы копируете эту формулу во все строки сводной таблицы. Расчет сообщает о неправильном ответе для всех, кроме первой строки сводной таблицы. Вы только что были поражены "функцией" Generate GetPivotData. Давайте посмотрим, что это такое и как этого избежать.

Просто предотвратите проблему GetPivotData - быстрый метод

Самый быстрый способ предотвратить создание GetPivotData Excel - это ввести формулу без использования мыши или клавиш со стрелками. Если вы просто наберете текст =E5/D5, Excel создаст «нормальную» относительную формулу, которую можно скопировать во все строки, смежные со сводной таблицей.

Предотвращение проблемы GetPivotData - постоянный метод

Существует скрытый параметр, запрещающий Excel создавать GetPivotData. В Excel 2003 он более скрыт, чем в Excel 2007.

В Excel 2007 выполните следующие действия:

• Создайте сводную таблицу в Excel 2007
• Убедитесь, что активная ячейка находится внутри сводной таблицы
• Посмотрите на левую часть вкладки Параметры работы со сводными таблицами на ленте. Есть кнопка "Параметры". Не нажимайте кнопку «Параметры»! Справа от кнопки «Параметры» находится стрелка раскрывающегося списка. Щелкните стрелку раскрывающегося списка. Снимите флажок «Создать GetPivotData».
• Теперь вы можете вводить формулы с помощью мыши, клавиш со стрелками или набора текста.

В Excel 2003 и более ранних версиях выполните следующие действия:

• Выберите инструменты, настройте
• В диалоговом окне "Настройка" есть три вкладки. Выберите вкладку Команды в центре.
• В левом списке выберите седьмой элемент, Данные
• В правом списке прокрутите почти до самого низа. Восьмой значок с конца списка - «Создать GetPivotData». Перетащите этот значок из списка в середину любой существующей панели инструментов.
• Нажмите кнопку «Закрыть», чтобы закрыть диалоговое окно.
• Щелкните значок, который вы только что добавили на панель инструментов Excel. Это отключит эту функцию. Теперь вы можете вводить формулы с помощью мыши без создания функций GetPivotData.

Вот бонусный совет: раньше я презирал эту функцию и просто хотел, чтобы она все время отключалась. Я добавил значок на свою панель инструментов, выключил значок, а затем удалил его с панели инструментов. Теперь … Я немного расслабился. Я вижу, что этот значок иногда находит хорошее применение. Итак, я построил сводную таблицу в Excel 2003 и убедился, что указатель ячейки находится в сводной таблице. Затем я использовал диалоговое окно настройки, чтобы перетащить значок «Создать GetPivotData» на панель инструментов сводной таблицы. Теперь, когда я нахожусь в сводной таблице, я могу выбрать, включить или выключить эту функцию.

Почему Microsoft сделала это? Есть ли хорошее применение GetPivotData?

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

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

При изменении сводной таблицы GetPivotData будет продолжать возвращать те же логические данные из сводной таблицы при условии, что данные все еще видны в сводной таблице. Это может быть важно, если вы меняете от месяца к месяцу в поле страницы сводной таблицы и хотите всегда возвращать продажи для определенного клиента. Поскольку список клиентов меняется каждый месяц, позиция клиента будет меняться. Используя =GETPIVOTDATA, вы можете быть уверены, что вернете правильное значение из сводной таблицы.

=GETPIVOTDATAвсегда начинается с двух конкретных аргументов. Затем он может иметь дополнительные пары аргументов.

Вот два обязательных аргумента:

1. Имя поля данных. Это может быть «Сумма дохода» или просто «Доход».
2. Любая ячейка, расположенная «внутри» сводной таблицы. Знаете ли вы, что у вашей сводной таблицы есть имя? Вы, вероятно, не знали этого, потому что не можете найти имя в интерфейсе Excel. Вам нужно будет перейти в VBA, чтобы узнать имя сводной таблицы. Итак, Microsoft было проще позволить вам идентифицировать сводную таблицу, указав на любую ячейку в сводной таблице. Хотя вы можете выбрать любую ячейку, безопаснее всего выбрать ячейку в верхнем левом углу. Существует вероятность того, что ваша сводная таблица может сжиматься для определенной комбинации полей страницы. В этом случае использование ячейки в верхнем левом углу гарантирует, что вы продолжаете указывать внутрь сводной таблицы.

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

GetPivotData может возвращать только значения, видимые в сводной таблице

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

• В ячейке A2 GETPIVOTDATA возвращает продажи ABC в Центральном регионе за январь 2004 г. Это захват 80003 из ячейки G19.
• Однако формула в A6 не работает. Он просит продажи ABC во всех регионах. Сводная таблица показывает общий ABC для Центрального региона, общий ABC для Востока, общий ABC для Запада, но никогда не показывает общий ABC для всех регионов, поэтому результат - #REF! ошибка.
• Если вы переместите поле региона вверх до области страницы, формула в A6 начнет работать, но формула в A2 и A4 начнет возвращать #REF !. Если вы выбрали «Центральное» в раскрывающемся списке «Регион», все четыре формулы будут работать.
• Отключение общих итогов в диалоговом окне параметров сводной таблицы ПРИВЕДЕТ к тому, что многие функции GetPivotData начнут возвращать #REF! ошибки.