Как сделать ссылку на сводную таблицу в excel
Argument ‘Topic id’ is null or empty
Сейчас на форуме
© Николай Павлов, Planetaexcel, 2006-2023
info@planetaexcel.ru
Использование любых материалов сайта допускается строго с указанием прямой ссылки на источник, упоминанием названия сайта, имени автора и неизменности исходного текста и иллюстраций.
| ООО «Планета Эксел» ИНН 7735603520 ОГРН 1147746834949 |
ИП Павлов Николай Владимирович ИНН 633015842586 ОГРНИП 310633031600071 |
Как управлять ссылками на ячейки в сводных таблицах Excel
Если вы работаете со сводными таблицами, то, вероятно, заметили, что в формуле, которая ссылается на ячейку в сводной таблице» ссылка автоматически преобразуется в функцию ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ с рядом аргументов.
Рассмотрим пример (рис. 170.1). Формула в ячейке 14 (которую я создал) является обычной ссылкой на ячейку G4 в сводной таблице. Но вместо того, чтобы отобразить обычную формулу Excel преобразует ее так:
=ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ(«Оценка»;$E$3;»Пол»;»Женский»;»Район»;»Район1″) .
Рис. 170.1. Формула, которая ссылается на ячейку в сводной таблице, может стать составной
Этот тип ссылки гарантирует, что формулы продолжат возвращать правильный результат, если структура сводной таблицы изменится. Если вы предпочтете избежать этого автоматического преобразования, не используйте указывание при создании формулы, которая ссылается на ячейки сводной таблицы. Просто введите ссылку на ячейку, набрав ее адрес.
Вы также можете снять флажок Создать GetPivotData в группе Работа со сводными таблицами ► Параметры ► Сводная таблица ► Параметры. В результате Excel перестанет генерировать формулы ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ. Установите флажок, и Excel начнет генерировать эти формулы снова. Эта команда не действует на существующие формулы.
Функция ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ
Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel для Интернета Excel 2021 Excel 2021 для Mac Excel 2019 Excel 2019 для Mac Excel 2016 Excel 2016 для Mac Excel 2013 Excel 2010 Excel 2007 Excel для Mac 2011 Excel Starter 2010 Еще. Меньше
Функция ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ возвращает видимые данные из сводной таблицы.
В этом примере =ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ.(«Продажи»; A3) возвращает общий объем продаж из сводной таблицы:
Синтаксис
ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ(поле_данных; сводная_таблица; [поле1; элемент1; поле2; элемент2]; …)
Аргументы функции ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ описаны ниже.
поле_данных
Имя поля сводной таблицы, содержащее данные, которые необходимо извлечь. Должно быть заключено в кавычки.
сводная_таблица
Ссылка на ячейку, диапазон ячеек или именованный диапазон ячеек в сводной таблице. Эти сведения используются для определения сводной таблицы, содержащей данные, которые необходимо извлечь.
поле1, элемент1, поле2, элемент2.
От 1 до 126 пар имен полей и элементов, описывающих данные, которые необходимо извлечь. Они могут следовать друг за другом в произвольном порядке. Имена полей и элементов (кроме дат и чисел) должны быть заключены в кавычки.
В сводных таблицах OLAP элементы могут содержать исходное имя измерения, а также исходное имя элемента. Пара «поле-элемент» для сводной таблицы OLAP может выглядеть следующим образом:
- Можно быстро ввести простую формулу ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ, введя = (знак равенства) в ячейке, в которой должно быть возвращено значение, и затем щелкнув ячейку в сводной таблице, содержащей необходимые данные.
- Вы можете отключить эту возможность. Для этого нужно выбрать любую ячейку в существующей сводной таблице, а затем перейти к вкладке Анализ сводной таблицы >Сводная таблица >Параметры > и снять флажок у параметра Генерировать функцию ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ.
- Вычисляемые поля или элементы и дополнительные вычисления могут включаться в расчеты для функции ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ.
- Аргумент «сводная_таблица» задан как диапазон, включающий несколько сводных таблиц. Данные будут извлекаться из той сводной таблицы, которая была создана последней.
- Если аргументы «поле» и «элемент» описывают одну ячейку, возвращается значение, содержащееся в этой ячейке, независимо от его типа (строка, число, ошибка или пустая ячейка).
- Если аргумент «элемент» содержит дату, необходимо представить это значение как порядковый номер или воспользоваться функцией ДАТА, чтобы это значение не изменилось при открытии листа в системе с другими языковыми настройками. Например, элемент, ссылающийся на дату 5 марта 1999 г., можно ввести двумя способами: 36 224 или ДАТА(1999;3;5). Время можно задать в виде десятичных значений или с помощью функции ВРЕМЯ.
- Если аргумент «сводная_таблица» не является диапазоном, содержащим сводную таблицу, функция ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ возвращает значение ошибки #ССЫЛКА!.
- Если аргументы не описывают видимое поле или содержат фильтр отчета, в котором не отображаются отфильтрованные данные, функция ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ возвращает #ССЫЛКА! (значение ошибки).
Примеры
Формулы в примере ниже представляют различные методы извлечения данных из сводной таблицы.
Дополнительные сведения
Вы всегда можете задать вопрос эксперту в Excel Tech Community или получить поддержку в сообществах.
Ссылки на ячейки сводной таблицы
В некоторых случаях может понадобиться вставка ссылок на отдельные ячейки сводной таблицы. На рисунке показан пример сводной таблицы, отображающей доходы и затраты за три года. В данной таблице поле месяца скрыто; по этой причине отображаются только годовые итоги.
В столбце F содержатся формулы, и он не является частью сводной таблицы. Эти формулы вычисляют соотношение доходов и расходов за каждый год. Вы, наверное, ожидаете, что в ячейке F5 находится следующая формула:
Однако это не так. Необходима такая формула:
=ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ(“Сумма по полю Доходы”;$А$3;
“Годы”;2008)/ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ(
“Сумма по полю Расходы”;$А$3;“Годы”;2008)
Для ссылки на ячейки сводной таблицы при создании формул нужно использовать специальную встроенную функцию ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ. Если вводить ссылки вручную, Excel не будет заменять их этой функцией.
В чем же смысл использования этой функции? Она гарантирует, что при изменении компоновки сводной таблицы формула будет продолжать ссылаться на нужные ячейки. На рисунке показана та же сводная таблица после отображения данных по месяцам. Таким образом, формулы в столбце F продолжают показывать корректный результат, даже несмотря на то, что итоговые данные за годы теперь изменили свое местоположение. Если бы использовались обычные ссылки на ячейки, формулы показывали бы теперь неверные результаты.
Предупреждение
Использование функции ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ имеет один подвох – извлекаемые ею данные должны отображаться в сводной таблице. Если изменить структуру сводной таблицы таким образом, чтобы эти данные были скрыты, формула вернет ошибку.
Если по некоторой причине вы хотите избежать использования программой функции ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ в формулах, примените команду Работа со сводными таблицами→Параметры→Сводная таблица→Параметры→Создать GetPivotData. Эта команда работает как переключатель.