В некоторых случаях может понадобиться вставка ссылок на отдельные ячейки сводной таблицы. На рисунке показан пример сводной таблицы, отображающей доходы и затраты за три года. В данной таблице поле месяца скрыто; по этой причине отображаются только годовые итоги.
В столбце F содержатся формулы, и он не является частью сводной таблицы. Эти формулы вычисляют соотношение доходов и расходов за каждый год. Вы, наверное, ожидаете, что в ячейке F5 находится следующая формула:
=С5/В5
Однако это не так. Необходима такая формула:
=ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ(“Сумма по полю Доходы”;$А$3;
“Годы”;2008)/ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ(
“Сумма по полю Расходы”;$А$3;“Годы”;2008)
Для ссылки на ячейки сводной таблицы при создании формул нужно использовать специальную встроенную функцию ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ. Если вводить ссылки вручную, Excel не будет заменять их этой функцией.
В чем же смысл использования этой функции? Она гарантирует, что при изменении компоновки сводной таблицы формула будет продолжать ссылаться на нужные ячейки. На рисунке показана та же сводная таблица после отображения данных по месяцам. Таким образом, формулы в столбце F продолжают показывать корректный результат, даже несмотря на то, что итоговые данные за годы теперь изменили свое местоположение. Если бы использовались обычные ссылки на ячейки, формулы показывали бы теперь неверные результаты.
Предупреждение
Использование функции ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ имеет один подвох – извлекаемые ею данные должны отображаться в сводной таблице. Если изменить структуру сводной таблицы таким образом, чтобы эти данные были скрыты, формула вернет ошибку.
Если по некоторой причине вы хотите избежать использования программой функции ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ в формулах, примените команду Работа со сводными таблицами→Параметры→Сводная таблица→Параметры→Создать GetPivotData. Эта команда работает как переключатель.