Розбір функції ДВССИЛ (INDIRECT) на прикладах
На перший погляд (особливо під час читання довідки) функціяДВССИЛ (INDIRECT)виглядає простою і навіть непотрібною. Її суть у тому, щоб перетворювати текст схожий на посилання – на повноцінне посилання. Тобто. якщо нам потрібно послатися на комірку А1, то ми можемо або звично зробити пряме посилання (ввести знак одно в D1, клацнути мишею по А1 і натиснути Enter), а можемо використовуватиДВССИЛдля тієї ж мети:
"Ну ОК", – скажете ви. "І що тут корисного?"
Але не судіть за першим враженням - воно оманливе. Ця функція може допомогти вам у великій кількості ситуацій.
Приклад 1. Транспонування
пазон горизонтальний (транспонувати). Можна використовувати спеціальну вставку або функціюТРАНСП (TRANSPOSE)у формулі масиву, але можна обійтися і нашійДВССИЛ:
Зворотну процедуру краще зробити трохи по-іншому. Оскільки цього разу нам потрібно формувати посилання на осередки B2, C2, D2 і т.д., то зручніше використовувати режим посилань R1C1 замість класичного "морського бою". У цьому режимі наші комірки відрізнятимуться лише номером стовпця: B2=R1C 2 , C2=R1C 3 , D2=R1C 4 і т.д.
Приклад 2. Підсумовування за інтервалом
Ми вже розбирали один спосіб підсумовування вікна (діапазону) заданого розміру на аркуші за допомогою функціїЗМІЩ (OFFSET). Подібне завдання можна вирішити і за допомогоюДВССИЛ. Якщо нам потрібно підсумовувати дані тільки з певного діапазону-періоду, то можна склеїти його зі шматочків і перетворити потім на повноцінне посилання, яке і вставити всередину функціїСУМ (SUM):
Приклад 3. Список, що випадає, за розумною таблицею
Іноді Microsoft Excel не сприймає імена та стовпці розумних таблиць як повноцінні посилання.Так, наприклад, при спробі створити список, що випадає (вкладкаДані - Перевірка даних) на основі стовпцяСпівробітникиз розумної таблиціЛюдими отримаємо помилку:
Якщо ж "обвернути" посилання нашою функцієюДВССИЛ, то Excel спокійнісінько її прийме і наш список, що випадає, буде динамічно оновлюватися при дописуванні нових співробітників в кінець розумної таблиці:
Приклад 4. Посилання, що не збиваються
Якщо ставити звичайні посилання (в першу зелену комірку ввести =B2 і скопіювати вниз), то потім при видаленні, наприклад, Даші ми отримаємо у відповідній їй зеленій комірці помилку #ПОСИЛКА! (#REF!). У разі застосування для створення посилань функціїДВССИЛтакої проблеми не буде.
Приклад 5. Збір даних із кількох аркушів
Припустимо, що ми маємо 5 аркушів з однотипними звітами від різних співробітників (Михайло, Олена, Іван, Сергій, Дмитро):
Припустимо, що форма, розміри, становище і послідовність товарів та місяців у всіх таблицях однакові - розрізняються лише числа.
Зібрати дані з усіх листів (не підсумувати, а покласти один під одного "стопочкою") можна лише однією формулою:
Як бачите, ідея та ж: ми склеюємо посилання на потрібний осередок заданого аркуша, аДВССИЛперетворює його на "живу". Для зручності над таблицею я додав літери стовпців (B, C, D), а праворуч - номери рядків, які потрібно взяти з кожного аркуша.
Підводні камені
При використанніДВССИЛ (INDIRECT)потрібно пам'ятати про її слабкі місця: