зміст
На перший погляд (особливо при читанні довідки) функція НЕПРАВИЛЬНО (НЕПРЯМИЙ) виглядає просто і навіть непотрібно. Його суть полягає в тому, щоб текст, схожий на посилання, перетворити на повноцінне посилання. Ті. якщо нам потрібно послатися на комірку A1, ми можемо або створити пряме посилання (введіть знак рівності в D1, клацніть A1 і натисніть Enter), або ми можемо використати НЕПРАВИЛЬНО з тією ж метою:
Будь ласка, зверніть увагу, що аргумент функції – посилання на A1 – взято в лапки, тому це фактично текст.
«Ну добре», - скажете ви. «А яка користь?»
Але не судіть за першим враженням – воно оманливе. Ця функція може допомогти вам у багатьох ситуаціях.
Приклад 1. Транспонувати
Класика жанру: потрібно повернути вертикальний діам
канавки до горизонталі (транспонування). Звичайно, можна використовувати спеціальну вставку або функцію TRANSP (ТРАНСПОНУВАТИ) у формулі масиву, але ви можете обійтися нашою НЕПРАВИЛЬНО:
Логіка проста: щоб отримати адресу наступної комірки, ми склеюємо літеру «А» зі спеціальним символом «&» і номер стовпця поточної комірки, який нам дає функція КОЛОНКА (КОЛОНА).
Зворотну процедуру краще зробити трохи інакше. Так як на цей раз нам необхідно сформувати посилання на комірки B2, C2, D2 і т. д., то зручніше використовувати режим посилання R1C1 замість класичного «морського бою». У цьому режимі наші комірки відрізнятимуться лише номером стовпця: B2=R1C2, C2=R1C3, D2=R1C4 і т.п.
Тут з’являється другий необов’язковий аргумент функції. НЕПРАВИЛЬНО. Якщо воно рівне БРЕХНЯ (ПОМИЛКОВИЙ), тоді ви можете встановити адресу посилання в режимі R1C1. Тож ми можемо легко перенести горизонтальний діапазон назад у вертикальний:
Приклад 2. Сума за інтервалом
Ми вже розбирали один спосіб підсумовування по вікну (діапазону) заданого розміру на аркуші за допомогою функції ВІДХОДЖЕННЯ (ЗМІЩЕННЯ). Подібну проблему також можна вирішити за допомогою НЕПРАВИЛЬНО. Якщо нам потрібно підсумувати дані тільки з певного діапазону-періоду, то ми можемо склеїти їх зі шматочків, а потім перетворити в повноцінне посилання, яке можна вставити всередину функції SUM (СУМА):
Приклад 3. Випадаючий список Smart table
Іноді Microsoft Excel не розглядає розумні імена та стовпці таблиць як повні посилання. Так, наприклад, при спробі створити розкривний список (таб Дані – Перевірка даних) на основі стовпця Співробітники з розумної таблиці Люди ми отримаємо помилку:
Якщо ми «обернемо» посилання нашою функцією НЕПРАВИЛЬНО, тоді Excel легко прийме це, і наш спадний список динамічно оновлюватиметься під час додавання нових співробітників у кінець розумної таблиці:
Приклад 4. Нерозривні ланки
Як відомо, Excel автоматично виправляє адреси посилань у формулах при вставці або видаленні рядків-стовпців на аркуші. У більшості випадків це правильно і зручно, але не завжди. Припустимо, нам потрібно перенести імена з довідника співробітників у звіт:
Якщо поставити звичайні посилання (у першу зелену комірку ввести =B2 і скопіювати вниз), то при видаленні, наприклад, Даші, ми отримаємо #ПОСИЛАННЯ! помилка у відповідній їй зеленій клітинці. (#РЕФ!). У разі використання функції створення посилань НЕПРАВИЛЬНО такої проблеми не буде.
Приклад 5: Збір даних із кількох аркушів
Припустимо, у нас є 5 аркушів з однотипними звітами від різних співробітників (Михайло, Олена, Іван, Сергій, Дмитро):
Припустимо, що форма, розмір, позиція та послідовність товарів і місяців у всіх таблицях однакові – відрізняються лише цифри.
Ви можете зібрати дані з усіх аркушів (не підсумовувати, а складати один під одним у «купу») за допомогою однієї формули:
Як бачите, ідея та ж: клеїмо посилання на потрібну комірку даного аркуша, і НЕПРАВИЛЬНО перетворює його на «живий». Для зручності над таблицею я додав літери колонок (B,C,D), а праворуч – номери рядків, які потрібно взяти з кожного аркуша.
Пастки
Якщо ви використовуєте НЕПРАВИЛЬНО (НЕПРЯМИЙ) потрібно пам'ятати про його слабкі сторони:
- Якщо ви посилаєтеся на інший файл (вставляючи ім’я файлу в квадратні дужки, ім’я аркуша та адресу комірки), це працює лише тоді, коли вихідний файл відкритий. Якщо ми закриємо його, ми отримаємо помилку #LINK!
- INDIRECT не може посилатися на динамічний іменований діапазон. На статиці – без проблем.
- INDIRECT — це мінлива або «мінлива» функція, тобто вона перераховується для будь-якої зміни в будь-якій клітинці аркуша, а не лише для впливу на клітинки, як у звичайних функціях. Це погано впливає на продуктивність і краще не захоплюватися великими таблицями INDIRECT.
- Як створити динамічний діапазон за допомогою автоматичного зміни розміру
- Підсумовування по вікну діапазону на аркуші з функцією OFFSET