Большинство людей впервые сталкивается с GETPIVOTDATA, когда они пытаются построить формулу вне сводной таблицы, которая использует числа в сводной таблице. Например, этот процент отклонения не будет скопирован в другие месяцы из-за того, что Excel вставляет функции GETPIVOTDATA.
Excel вставляет GETPIVOTDATA каждый раз, когда вы используете мышь или клавиши со стрелками, чтобы указать на ячейку внутри сводной таблицы при построении формулы вне сводной таблицы.
Кстати, если вы не хотите, чтобы функция GETPIVOTDATA появлялась, просто введите формулу, например, =D5/C5-1без использования мыши или клавиш со стрелками для указания на ячейки. Эта формула копируется без проблем.
Вот набор данных, который содержит один номер плана в месяц для каждого магазина. Также есть фактические продажи в месяц для каждого магазина за полные месяцы. Ваша цель - построить отчет, который показывает фактические данные за завершенные месяцы и план на будущие месяцы.
Создайте сводную таблицу с помощью Store in Rows. Поместите месяц и тип в столбцы. Вы получите отчет, показанный ниже, с фактическим январским планом, январским планом и совершенно бессмысленным январским фактическим + планом.
Если вы выберете ячейку месяца и перейдете в Настройки поля, вы можете изменить Промежуточные итоги на Нет.
Это удаляет бесполезный план Actual +. Но вам все равно нужно избавиться от столбцов плана с января по апрель. Нет хорошего способа сделать это внутри сводной таблицы.
Итак, ваш ежемесячный рабочий процесс становится:
- Добавьте к набору данных фактические данные за новый месяц.
- Создайте новую сводную таблицу с нуля.
- Скопируйте сводную таблицу и вставьте ее как значения, чтобы она больше не была сводной таблицей.
- Удалите столбцы, которые вам не нужны.
Есть способ лучше. На следующем очень сжатом рисунке показан новый рабочий лист Excel, добавленный в книгу. Это все просто Excel, без сводных таблиц. Единственное волшебство - это функция ЕСЛИ в строке 4, которая переключается с Фактического на План в зависимости от даты в ячейке P1.
Самая первая ячейка, которую необходимо заполнить, - это данные за январь для Бейбрука. Щелкните эту ячейку и введите знак равенства.
С помощью мыши вернитесь к сводной таблице. Найдите ячейку январских фактов для Бейбрука. Щелкните эту ячейку и нажмите Enter. Как обычно, Excel создает одну из тех надоедливых функций GETPIVOTDATA, которые нельзя скопировать.
Но сегодня давайте изучим синтаксис GETPIVOTDATA.
Первый аргумент ниже - это числовое поле «Продажи». Второй аргумент - это ячейка, в которой находится сводная таблица. Остальные пары аргументов - это имя поля и значение. Вы видите, что сделала автоматически созданная формула? В качестве названия магазина было жестко запрограммировано "Бейбрук". Вот почему вы не можете скопировать эти автоматически сгенерированные формулы GETPIVOTDATA. Они фактически жестко кодируют имена в формулы. Даже если вы не можете скопировать эти формулы, вы можете их редактировать. В этом случае было бы лучше, если бы вы отредактировали формулу, чтобы она указывала на ячейку $ D6.
На рисунке ниже показана формула после ее редактирования. Ушли в прошлое "Бэйбрук", "Ян" и "Актуальный". Вместо этого вы указываете на $ D6, E $ 3 и E $ 4.
Скопируйте эту формулу и выберите «Специальная вставка», «Формулы» во всех остальных числовых ячейках.
Вот ваш ежемесячный рабочий процесс:
- Создайте уродливую сводную таблицу, которую никто никогда не увидит.
- Настройте лист отчета.
Каждый месяц вам необходимо:
- Вставьте новые актуальные данные под данными.
- Обновите уродливую сводную таблицу.
-
Измените ячейку P1 на листе отчета, чтобы отразить новый месяц. Все номера обновляются.
Вы должны признать, что использование отчета, извлекающего числа из сводной таблицы, дает вам лучшее из обоих миров. Вы можете форматировать отчет так, как не можете форматировать сводную таблицу. Пустые строки вполне подойдут. В первой и последней строках могут быть символы валюты, но не между ними. Вы также получаете двойное подчеркивание под общими итогами.
Спасибо @iTrainerMX за предложение этой функции.
