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 .
