Excel: как делать ссылки на сводную таблицу
«Уважаемые сотрудники «Б & К»! Я часто пользуюсь сводными таблицами, и в связи с этим у меня вопрос: как правильно делать ссылки на ячейки сводного отчета? Дело в том, что при обычном способе создания ссылок Excel вместо адреса вставляет специальную функцию, а это иногда очень неудобно. Подскажите, есть ли простой способ решения этой проблемы? Я работаю с программой MS Excel 2010. Среди параметров программы подходящих настроек я не нашел. Надеюсь на вашу помощь. Спасибо.
Владимир Ярославцев, главный бухгалтер, г. Днепропетровск».
Отвечает Николай КАРПЕНКО, канд. техн. наук, доцент каф. Прикладной математики и информационных технологий Харьковской национальной академии городского хозяйства
Разумеется, что способ создать обычные ссылки на ячейки сводной таблицы есть. Причем от версии Excel он не зависит. Но вначале пару слов о самой проблеме.
Я поясню ее на примере отчета, фрагмент которого показан на рис. 1. Это сводная таблица, которая сформирована по некоторой базе данных. В таблице показаны объемы продаж по шести контрагентам. Предположим, что эти данные мы решили вставить в другую таблицу в виде ссылок на ячейки сводного отчета, и уже там сделать окончательный расчет. Посмотрим, что из этого получится. Чтобы не усложнять задачу, я создам ссылки на том же рабочем листе, где расположена сводная таблица. Дальше делаем так:
1. Становимся на свободную ячейку, пусть это будет «D3».
2. Набираем символ «=» (начало формулы).
3. Щелкаем левой кнопкой мыши на ячейке «B3» (я хочу сделать ссылку на сумму реализации по контрагенту «ТОВ "Топаз"»). В ячейке «D3» вместо ссылки мы увидим такой результат: «=ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ("Сумма"; $A$1;"Покупатель";"ТОВ ""Топаз""")». При этом значение в ячейке «D3» будет равно «119,80», что соответствует объемам продаж по «ТОВ "Топаз"».
4. Копируем эту формулу вниз до ячейки «D8» (на всю высоту сводной таблицы). Результат во всех ячейках будет одинаковым — «119,80». То есть функция получения данных из сводного отчета сослалась на одну и ту же ячейку сводной таблицы.
Причина такого поведения лежит в параметрах функции «=ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ()». Таких параметров у нее четыре. Первым идет название поля, по которому нужно выбрать итог. В нашем случае это поле «Сумма». Так это поле называлось в исходной базе, с этим именем оно и попало в сводный отчет. Вторым параметром стоит ссылка на ячейку с заголовком поля. В формуле эта ссылка выглядит как «$A$1». Кстати, абсолютная адресация в данном случае обязательна! Третий параметр — название поля, по которому Excel будет выбирать данные из сводного отчета. В формуле указано, что поиск конкретного числа в сводной таблице нужно делать по полю «Покупатель». Последний параметр — это строка для поиска конкретного значения среди покупателей. В нашей функции указано значение «ТОВ "Топаз"». Поэтому Excel выберет итог именно по этому контрагенту. Сразу бросается в глаза, что большинство параметров в функции «=ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ()» указаны в виде текстовых строк. Именно поэтому не сработала корректировка адресов при копировании формулы в ячейки «D3:D8», и все функции вернули один и тот же результат.
Кстати, исправить такую ситуацию несложно: нужно вместо фиксированного элемента «"ТОВ ""Топаз"""» поставить ссылку на ячейку «A3». То есть формула в ячейке «D3» должна выглядеть так: «=ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ ("Сумма";$A$1;"Покупатель" ;A3)» (изменения выделены полужирным начертанием). В этом варианте после копирования формулы вниз до ячейки «D8» мы получим правильные объемы реализации по каждому контрагенту.
Однако речь сейчас о другом. Использование функции «=ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ()» имеет свои преимущества и недостатки. Среди преимуществ я бы указал, что независимо от порядка сортировки записей в сводной таблице ссылка через функцию обеспечит правильный результат. И это понятно — извлечение данных из сводного отчета функция делает по ключевому полю, а не по адресу рабочего листа! Если посмотреть на формулу в ячейке «D3», то ключевым полем для обращения к сводной таблице является название фирмы «ТОВ "Топаз"». И при этом не имеет никакого значения, где конкретно находится запись по этой фирме — на первой позиции отчета или в самом конце. Данные Excel подставит правильно.
Недостаток работы с функцией «=ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ()» состоит в том, что нужно корректировать значение ключевого поля или заменять его ссылкой. Поэтому в некоторых случаях удобнее вместо встроенной функции использовать ссылки на ячейки сводной таблицы. Чтобы вставить такие ссылки автоматически (отказаться от использования функции «=ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ()»), нужно знать одну тонкость.
Секрет Встроенную функцию «=ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ()» Excel использует только при ссылках на поля в области данных сводного отчета. При организации ссылок на заголовки строк или колонок он вставляет обычные ссылки на ячейки рабочего листа.
Зная это правило, мы легко получим «нормальные» ссылки на ячейки сводной таблицы. Для этого делаем так:
1. Открываем документ, как на рис. 1.
2. Становимся на ячейку «D3».
3. Вводим символ «=» (начинаем запись формулы).
4. Щелкаем левой кнопкой мыши на ячейке «A3». Excel добавит в текущую ячейку ссылку «=A3», где записано название фирмы. В данном конкретном случае — это «ТОВ "Топаз"».
5. Нажимаем «Enter» (завершаем ввод формулы).
6. Копируем формулу в ячейку «E3».
Смотрим на содержимое ячеек «D3» и «E3». Как и следовало ожидать, там находятся обычные ссылки: «=A3» и «=B3». Одна указывает на ячейку с названием фирмы, вторая — на объем реализации. Теперь с этими ссылками можно делать все что угодно — переносить на другой лист, использовать в расчетах и т. д.
И последнее. Работа с обычными ссылками незаменима, когда нужно построить график по данным сводного отчета (!) в программе Excel 2003. При создании такого графика Excel 2003 формирует его на отдельном листе, а это не всегда удобно. Чтобы отказаться от такой возможности и построить диаграмму на текущем листе, нужно создать рабочую область со ссылками на данные сводной таблицы. А затем по этим ссылкам сформировать диаграмму. Для таблицы на рис. 1 процедура выглядит так:
1. Открываем документ, переходим на ячейку «D3».
2. Вводим в нее формулу «=A3».
3. Копируем формулу в ячейки «D3:E8». В результате мы получим копию данных из сводной таблицы в виде формул.
4. Строим график по данным «D3:E8».
5. Чтобы скрыть «рабочую область», форматируем значения в блоке «D3:D8» белым цветом или ставим график поверх ячеек «D3:D8», чтобы закрыть им вспомогательную информацию (рис. 2).
На сегодня все. Удачной работы! Жду ваши вопросы, замечания и предложения на bk@id.factor.ua , nictomkar@rambler.ru или на форуме редакции www.bk.factor.ua/forum .
