Как научить Excel ссылаться на ячейку в сводной таблице, как на обычную

Рубрика: 7. Полезняшки Excel

Если вы активно используете сводные таблицы, вам, наверное, приходилось создавать формулы, ссылающиеся на ячейки сводной таблицы. Рассмотрим пример. Допустим вы работаете в крупной торговой компании (продукты питания), и хотите проанализировать динамику расходов на заказ партий товаров (рис. 1).

Рис. 1. Исходные данные

Скачать заметку в формате Word или pdf, примеры в формате Excel2013

На основе исходных данных вы создаете сводную таблицу и группируете заказы и расходы по месяцам (рис. 2).

Рис. 2. Сводная таблица

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

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

Я уже несколько лет обходил эту проблему различными ухищрениями, а недавно знакомый показал изящный и предельно простой способ избежать этой напасти… 🙂 Оказывается, автоматическое создание формулы ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ можно отключить! Пройдите по меню Файл (у меня Excel 2013) → Параметры Excel, перейдите на вкладку Формулы и в разделе Работа с формулами отключите опцию Использовать функции GetPivotData для ссылок в сводной таблице

Рис. 4. Отключите опцию Использовать функции GetPivotData для ссылок в сводной таблице

Теперь при ссылке на ячейку сводной таблицы будет отображаться ее адрес, как и при выборе обычной ячейки (рис. 5).

Рис. 5. При ссылке на ячейку сводной таблицы отображается адрес ячейки, как и при выборе обычной ячейки

Комментарии: 15 комментариев

Это великолетие:)

Спасибо Вам огромное!!!
Наконец-то узнала, как это сделать!

Спасибо большое!
Долго искала как это сделать!

Супер! спасибки:-)

Супер, спасибо! У меня чуть оргазм не случился))

Спасибище!!!

Спасибо огромное! Все оказалось элементарно!

Гениально!!! Спасибо огромное!

Нереально круто!!:)

Спасибо, искал как раз как сделать с точностью до наоборот — чтобы ставилась не ссылка, а автоматом строилась функция GetPivotData. Потому что, если пользоваться группировками в сводной таблице (схлопывать/раскрывать данные), то адреса ячеек меняются (в отличии от обычных группировок) и ломаются все прямые ссылки на сводную таблицу. К сожалению, в Excel’e под Mac ставятся как раз ссылки на ячейки, а не формула.

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

Второй вариант: на вкладке Анализ в первой группе рядом с кнопкой Параметры Сводной таблицы есть выпадающий список. Мы туда заходили, чтобы разбить Сводную на несколько листов на основе значения фильтра отчета. В этом выпадающем списке – последний пункт, Создавать GetPivotData. Ставя/убирая галочку включаем и отключает генерацию функции. Этот способ удобен тем, что щёлкнув по данному пункту правой кнопкой мыши, его можно добавить на панель быстрого доступа.

Супер! 15 ЛЕТ работаю в Эксель и не знала.

Да, GetPivotData (в дискуссии с Yus) на самом деле удобнее, если схлопывать данные, но как в этом случае обеспечить автоматическое заполнение нижележащих ячеек? Я — в каждую ввожу формулу щёлкая ячейки нужной строки сводной таблицы. Если же потяну мышкой, то во всех ячейках будет формула/итог первой.

А за совет, Baguzin, огромное спасибо!

Большое спасибо! не догадалась бы туда залезть 🙂


Прокомментировать