Функция ВПР для сводных таблиц — это функция GET.PIVOTTABLE DATA, которая возвращает данные, хранящиеся в отчете сводной таблицы.
Для быстрого доступа к функции вам необходимо ввести знак равенства в ячейку (=) и выбрать нужную ячейку в сводной таблице. Excel автоматически сгенерирует функцию ПОЛУЧИТЬ ДАННЫЕ Сводной таблицы.
Чтобы отключить автоматическое создание функции ПОЛУЧИТЬ ДАННЫЕ Сводной таблицы, выберите любую ячейку в сводной таблице, перейдите на вкладку «Инструменты сводной таблицы» -> «Параметры» в группе «Сводная таблица». Щелкните стрелку вниз рядом с вкладкой Параметры. В раскрывающемся меню снимите флажок Создать GetPivotData.
Теперь, когда вы ссылаетесь на ячейку в сводной таблице, Excel сгенерирует адрес ячейки.
Вместо того, чтобы указывать имена элементов или полей в функции GET.DATA.PUMP.TABLE, вы можете ссылаться на ячейки рабочего листа. В следующем примере ячейка E3 содержит название продукта, а формула в ячейке E4 ссылается на него. В результате будет возвращен общий объем для коржей.
Нет вопросов о том, как работают ссылки на элементы сводной таблицы, проблемы возникают, если мы хотим обратиться к полю данных.
В примере ячейка E3 содержит имя поля данных «Количество», и было бы полезно сослаться на эту ячейку в функции вместо имени поля в формуле GET.DATA.PUMP.TABLE.
Однако, если мы изменим первый аргумент data_field на ссылку на ячейку E3, Excel вернет ошибку #REFERENCE!
= GET.DATA.PUMP.TABLE (E3; $ A $ 3)
Проблема решается простым добавлением пустой строки («») в начало или конец ссылки на ячейку.
= GET.PUMP.TABLE DATA (E3 & «»; $ A $ 3)
Простая корректировка формулы вернет правильное значение.
Если вы используете даты в функции GET PIVOTTABLE DATA, у вас могут возникнуть проблемы, даже если дата отображается в сводной таблице. Например, следующая формула — это дата «21.04.2013», а сводная таблица содержит поле с датами продаж. Однако формула в ячейке E4 возвращает ошибку.
= ПОЛУЧИТЬ ПЕРСОНАЛЬНЫЕ ДАННЫЕ.ТАБЛИЦА («Объем»; $ A $ 3; «Дата»; «21.04.2013″)
Чтобы избежать ошибок даты, вы можете использовать один из следующих методов:
Чтобы получить правильный результат, при использовании функции GET PIVOTTABLE DATA убедитесь, что форматы даты аргумента формулы и сводной таблицы совпадают.
В ячейке E4 формула использует дату в формате «ДД.ММ.ГГГГ», и в результате была возвращена правильная информация.
Вместо того, чтобы вручную вставлять дату в формулу, вы можете добавить функцию DATEVALUE для возврата даты.
В ячейке E4 дата вводится с помощью функции ДАТАЗНАЧ, и Excel возвращает запрошенную информацию.
= GET.PERSONAL.TABLE DATA («Объем», $ A $ 3, «Дата»; DATEVALUE («21.04.2013»))
Вместо того, чтобы вручную вставлять дату в формулу, вы можете использовать функцию ДАТА, которая позволит вам правильно вернуть необходимую информацию.
= GET.PERSONAL.TABLE DATA («Объем», $ A $ 3, «Дата», ДАТА (2013, 4, 21))
Вместо того, чтобы вручную вводить дату в формулу, вы можете ссылаться на ячейку, содержащую дату (в любом формате, в котором Excel интерпретирует данные как даты). В примере в ячейке E4 формула ссылается на ячейку E3, и Excel возвращает правильные данные.
= ПОЛУЧИТЬ ПЕРСОНАЛЬНЫЕ ДАННЫЕ.ТАБЛИЦА («Объем»; $ A $ 3; «Дата»; E3)
