Сводные формулы в Excel

Аналогом функции ВПР для сводных таблиц является функция ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ, которая возвращает данные, хранящиеся в отчете сводной таблицы.

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

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

Чтобы отключить автоматическую генерацию функции ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ, выберите любую ячейку в сводной таблице, перейдите по вкладке Работа со сводными таблицами -> Параметры в группу Сводная таблица. Щелкните по стрелке вниз, находящейся рядом с вкладкой Параметры. В выпавшем меню, уберите галку с пункта Создать GetPivotData.

Теперь, при ссылке на ячейку, находящуюся в сводной таблице, Excel будет генерировать адрес ячейки.

Использование ссылок на ячейки в функции ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ

Вместо указания названия пунктов или полей в функции ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ, вы можете ссылаться на ячейки, находящиеся на листе. В примере ниже ячейка E3 содержит название продукта, а формула в ячейке E4 ссылается на нее. В результате будет возвращен суммарный объем по тортам.

Использование ссылок на поле сводной таблицы

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

В примере ячейка E3 содержит название поля данных «Количество», и было бы неплохо ссылаться на эту ячейку в функции, вместо того, чтобы иметь название поля в формуле ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ.

Однако, если мы поменяем первый аргумент поле_данных на ссылку на ячейку E3, Excel вернет нам ошибку #ССЫЛКА!

=ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ(E3;$A$3)

Проблему решит простое добавление пустой строки (“”) в начало или конец ссылки на ячейку.

=ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ(E3&«”;$A$3)

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

Использование дат в функции ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ

Если вы используете даты в функции ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ, у вас могут возникнуть проблемы, даже если дата отображается в сводной таблице. К примеру, аргументом формулы ниже является дата «21/04/2013», и сводная таблица содержит поле с датами продаж. Однако формула в ячейке E4 возвращает ошибку.

=ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ(“Объем”;$A$3;”Дата”;”21/04/2013«)

Для предотвращения ошибок, связанных с датами, вы можете воспользоваться одним из следующих способов:

• Сравнять форматы дат в формуле и сводной таблице
• Использовать функцию ДАТАЗНАЧ
• Использовать функцию ДАТА
• Сослаться на ячейку с корректной датой

Сравнять форматы дат в формуле и сводной таблице.

Для получения корректного результата, во время использования функции ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ, убедитесь, что форматы дат аргумента формулы и сводной таблицы одинаковые.

В ячейке E4, в формуле использована дата формата «ДД.ММ.ГГГГ», и в результате возвращена правильтая информация.

Использование функции ДАТАЗНАЧ

Вместо ручного ввода даты в формуле, можно добавить функцию ДАТАЗНАЧ для возврата даты.

В ячейке E4, дата введена с помощью функции ДАТАЗНАЧ, и Excel возвращает необходимую информацию.

=ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ(“Объем”;$A$3;”Дата”;ДАТАЗНАЧ(“21.04.2013″))

Использование функции ДАТА

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

=ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ(“Объем”;$A$3;”Дата”;ДАТА(2013;4;21))

Ссылка на ячейку с датой

Вместо ручного ввода даты в формуле, можно сослаться на ячейку, содержащую дату (в любом формате, в котором Excel воспринимает данные, как даты). В примере в ячейке E4, формула ссылается на ячейку E3 и Excel возвращает корректные данные.

=ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ(“Объем”;$A$3;”Дата”;E3)

Вам также могут быть интересны следующие статьи