Функция ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ

Функция ВПР для сводных таблиц — это функция GET.PIVOTTABLE DATA, которая возвращает данные, хранящиеся в отчете сводной таблицы.

Для быстрого доступа к функции вам необходимо ввести знак равенства в ячейку (=) и выбрать нужную ячейку в сводной таблице. Excel автоматически сгенерирует функцию ПОЛУЧИТЬ ДАННЫЕ Сводной таблицы.

Отключение создания GetPivotData

Чтобы отключить автоматическое создание функции ПОЛУЧИТЬ ДАННЫЕ Сводной таблицы, выберите любую ячейку в сводной таблице, перейдите на вкладку «Инструменты сводной таблицы» -> «Параметры» в группе «Сводная таблица». Щелкните стрелку вниз рядом с вкладкой Параметры. В раскрывающемся меню снимите флажок Создать 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)