Если вы активно используете сводные таблицы, вам, наверное, приходилось создавать формулы, ссылающиеся на ячейки сводной таблицы. Рассмотрим пример. Допустим вы работаете в крупной торговой компании (продукты питания), и хотите проанализировать динамику расходов на заказ партий товаров (рис. 1).
Рис. 1. Исходные данные
Скачать заметку в формате Word или pdf, примеры в формате Excel2013
На основе исходных данных вы создаете сводную таблицу и группируете заказы и расходы по месяцам (рис. 2).
Рис. 2. Сводная таблица
Если теперь вы захотите проанализировать, как меняются относительные затраты от месяца к месяцу, то, возможно, в ячейке D4 захотите ввести формулу =С4/В4. Однако, при попытке ввода формулы не с клавиатуры, а выбором ячейки мышкой, Excel автоматически создаст формулу ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ (рис. 3).
Рис. 3. Ссылка на ячейку приводит к созданию формулы ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ
Я уже несколько лет обходил эту проблему различными ухищрениями, а недавно знакомый показал изящный и предельно простой способ избежать этой напасти… 🙂 Оказывается, автоматическое создание формулы ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ можно отключить! Пройдите по меню Файл (у меня Excel 2013) → Параметры Excel, перейдите на вкладку Формулы и в разделе Работа с формулами отключите опцию Использовать функции GetPivotData для ссылок в сводной таблице
Рис. 4. Отключите опцию Использовать функции GetPivotData для ссылок в сводной таблице
Теперь при ссылке на ячейку сводной таблицы будет отображаться ее адрес, как и при выборе обычной ячейки (рис. 5).
Рис. 5. При ссылке на ячейку сводной таблицы отображается адрес ячейки, как и при выборе обычной ячейки
Это великолетие:)
Спасибо Вам огромное!!!
Наконец-то узнала, как это сделать!
Спасибо большое!
Долго искала как это сделать!
Супер! спасибки:-)
Супер, спасибо! У меня чуть оргазм не случился))
Спасибище!!!
Спасибо огромное! Все оказалось элементарно!
Гениально!!! Спасибо огромное!
Нереально круто!!:)
Спасибо, искал как раз как сделать с точностью до наоборот — чтобы ставилась не ссылка, а автоматом строилась функция GetPivotData. Потому что, если пользоваться группировками в сводной таблице (схлопывать/раскрывать данные), то адреса ячеек меняются (в отличии от обычных группировок) и ломаются все прямые ссылки на сводную таблицу. К сожалению, в Excel’e под Mac ставятся как раз ссылки на ячейки, а не формула.
Yus, на указанную вами тему у меня есть довольно подробная заметка
Функция ПОЛУЧИТЬ.ДАННЫЕ. СВОДНОЙ.ТАБЛИЦЫВторой вариант: на вкладке Анализ в первой группе рядом с кнопкой Параметры Сводной таблицы есть выпадающий список. Мы туда заходили, чтобы разбить Сводную на несколько листов на основе значения фильтра отчета. В этом выпадающем списке – последний пункт, Создавать GetPivotData. Ставя/убирая галочку включаем и отключает генерацию функции. Этот способ удобен тем, что щёлкнув по данному пункту правой кнопкой мыши, его можно добавить на панель быстрого доступа.
Супер! 15 ЛЕТ работаю в Эксель и не знала.
Да, GetPivotData (в дискуссии с Yus) на самом деле удобнее, если схлопывать данные, но как в этом случае обеспечить автоматическое заполнение нижележащих ячеек? Я — в каждую ввожу формулу щёлкая ячейки нужной строки сводной таблицы. Если же потяну мышкой, то во всех ячейках будет формула/итог первой.
А за совет, Baguzin, огромное спасибо!
Большое спасибо! не догадалась бы туда залезть 🙂
спасибо!!
СПАСИБО!
Супер! Спасибо большое
Пытаюсь навесить условное форматирование на данные сводной таблице, а при обновлении форматирование пропадает. Как сослаться на ячейки внутри сводной таблицы?
Дмитрий, я попробовал, у меня не пропадает:
Почитайте еще Функция ПОЛУЧИТЬ.ДАННЫЕ. СВОДНОЙ.ТАБЛИЦЫ. Если ничего не получится, пришлите файл на s_bag@mail.ru
Спасибо! Вы классные.
долго искала — и вот оно, счастье.
Дело в том, что я хотел сразу на несколько столбцов, и это не работает, а если по одной колонке, то работает.
Спасибо огромное! Вы мой спаситель)
ОГРОМНОЕ СПАСИБО!
спасибо
у меня не получилось (((
Необходимо в Таблица -> Свойства внешних данных включить галочку «сохранить сведения о сортировке\фильтре\форматировании»
Тогда форматирование не слетает при обновлении сводной.
Ужасно возмущался когда узнал, что это не включено априори…
«Анализ сводной таблицы»- «вычисляемое поле»- задать имя поля; ввести формулу — «ОК»
Спасибо! быстро и по теме
Мое почтение