Це простий, але цікавий трюк, який дозволяє замінювати дані з маленьких таблиць, взагалі не використовуючи клітинки. Його суть полягає в тому, що прямо у формулу можна «вшити» масив значень підстановки. Давайте розглянемо кілька способів, як це зробити.
Функція SELECT
Якщо потрібно підставити дані з одновимірного масиву числом, то можна скористатися функцією ІНДЕКС або її більш простим і підходящим, в даному випадку, аналогом – функцією ВИБІР (ВИБРАТИ). Виводить елемент масиву за його порядковим номером. Так, наприклад, якщо нам потрібно відобразити назву дня тижня за його номером, то можна скористатися цією конструкцією
Це простий приклад для початку, щоб отримати уявлення про те, що таблицю пошуку можна вставити прямо у формулу. Тепер давайте розглянемо більш складний, але красивіший приклад.
Масив констант у формулі
Припустимо, у нас є список міст, де використовується функція VPR (ВЛООКУП) підставляються значення коефіцієнтів посадового окладу з другої колонки жовтої таблиці справа:
Хитрість полягає в тому, що ви можете замінити посилання на діапазон таблицею $E$3:$F$5 масив констант безпосередньо у формулу, і права таблиця більше не знадобиться. Щоб не вводити дані вручну, можна піти на невелику хитрість.
Виберіть будь-яку порожню клітинку. Введіть з клавіатури знак рівності і виберіть діапазон з таблицею – його адреса повинна відображатися в рядку формул:
Виділіть мишкою посилання E3:F5 у рядку формул і натисніть клавішу F9 – посилання перетвориться на масив констант:
Залишилося скопіювати отриманий масив і вставити його в нашу формулу за допомогою VLOOKUP, а саму таблицю видалити за непотрібністю:
Іменований масив констант
Розвиваючи ідею попереднього способу, можна спробувати ще один варіант – зробити іменований масив констант в оперативній пам’яті, яка потім використовується у формулі. Для цього натисніть вкладку формула (Формули) button Менеджер імен (Менеджер імен)… Потім натисніть кнопку Створювати, придумайте і введіть назву (нехай буде, наприклад, Міста) і в полі Діапазон (Довідка) вставити масив констант, скопійованих у попередньому методі:
прес OK і закрити Менеджер імен. Тепер додане ім’я можна безпечно використовувати на будь-якому аркуші книги в будь-якій формулі – наприклад, у нашій функції VLOOKUP:
Компактно, красиво і в якомусь сенсі навіть захищає від грайливих рук непрофесіоналів 🙂
- Як використовувати функцію VLOOKUP для пошуку даних з однієї таблиці в іншу
- Як використовувати приблизний пошук за допомогою функції VLOOKUP
- Розрахунки без формул