Зрозуміло, що спосіб створити звичайні посилання на осередки зведеної таблиці є. Причому від версії Excel він не залежить. Але спочатку кілька слів про саму проблему.
Я поясню її на прикладі звіту, фрагмент якого показаний на рис. 1. Це зведена таблиця, яка сформована за деякою базі даних. У таблиці показані обсяги продажів по шести контрагентам. Припустимо, що ці дані ми вирішили вставити в іншу таблицю у вигляді посилань на осередки зведеного звіту, і вже там зробити остаточний розрахунок. Подивимося, що з цього вийде. Щоб не ускладнювати завдання, я створю посилання на тому ж робочому аркуші, де розташована зведена таблиця. Далі робимо так:
1. Стаємо на вільну комірку, нехай це буде «D3».
2. Набираємо символ «=» (початок формули).
3. Клацаємо лівою кнопкою миші на осередку «B3» (я хочу зробити посилання на суму реалізації по контрагенту «ТОВ" Топаз "»). В осередку «D3» замість посилання ми побачимо такий результат: «= ПОЛУЧІТЬ.ДАННИЕ.СВОДНОЙ.ТАБЛІЦИ (" Сума "; $ A $ 1;" Покупець ";" ТОВ "" Топаз "" ")». При цьому значення в осередку «D3» дорівнюватиме «119,80», що відповідає обсягам продажів по «ТОВ" Топаз "».
4. Копіюємо цю формулу вниз до осередку «D8» (на всю висоту зведеної таблиці). Результат у всіх осередках буде однаковим - «119,80». Тобто функція отримання даних з зведеного звіту послалася на одну і ту ж комірку зведеної таблиці.
До речі, виправити таку ситуацію нескладно: потрібно замість фіксованого елемента «" ТОВ "" Топаз "" "" поставити посилання на осередок «A3». Тобто формула в комірці «D3» повинна виглядати так: «= ПОЛУЧІТЬ.ДАННИЕ.СВОДНОЙ.ТАБЛІЦИ (" Сума "; $ A $ 1;" Покупець "; A3)» (зміни виділені жирним шрифтом). У цьому варіанті після копіювання формули вниз до осередку «D8» ми отримаємо правильні обсяги реалізації по кожному контрагенту.
Недолік роботи з функцією «= ПОЛУЧІТЬ.ДАННИЕ.СВОДНОЙ.ТАБЛІЦИ ()» полягає в тому, що потрібно коригувати значення ключового поля або замінювати його посиланням. Тому в деяких випадках зручніше замість вбудованої функції використовувати посилання на осередки зведеної таблиці. Щоб вставити такі посилання автоматично (відмовитися від використання функції «= ПОЛУЧІТЬ.ДАННИЕ.СВОДНОЙ.ТАБЛІЦИ ()»), потрібно знати одну тонкість.
Знаючи це правило, ми легко отримаємо «нормальні» посилання на осередки зведеної таблиці. Для цього робимо так:
1. Відкриваємо документ, як на рис. 1.
2. Стаємо на осередок «D3».
3. Вводимо символ «=» (починаємо запис формули).
4. Клацаємо лівою кнопкою миші на осередку «A3». Excel додасть в поточну комірку посилання «= A3», де записано назву фірми. В даному конкретному випадку - це «ТОВ" Топаз "».
5. Натискаємо «Enter» (завершуємо введення формули).
6. Копіюємо формулу в осередок «E3».
1. Відкриваємо документ, переходимо на осередок «D3».
2. Вводимо в неї формулу «= A3».
3. Копіюємо формулу в комірки «D3: E8». В результаті ми отримаємо копію даних зі зведеної таблиці у вигляді формул.
4. Будуємо графік за даними «D3: E8».
5. Щоб приховати «робочу область», форматіруем значення в блоці «D3: D8» білим кольором або ставимо графік поверх осередків «D3: D8», щоб закрити їм допоміжну інформацію (рис. 2).