Вступ до динамічних діапазонів
Функція VLOOKUP часто використовується для пошуку інформації, яка зберігається в таблицях в Excel. Так, наприклад, якщо у нас є список імен та віку людей:
І тоді ми можемо в сусідній клітці скористатися функцією VLOOKUP, щоб визначити вік Павла:
Поки що це досить стандарт. Але що станеться, якщо нам потрібно буде додати ще деякі імена до списку? Очевидною ідеєю було б змінити діапазон у VLOOKUP. Однак у дійсно складній моделі може бути кілька посилань на VLOOKUP. Це означає, що нам доведеться змінювати кожне посилання - припускаючи, що ми знаємо, де вони знаходяться.
Однак Excel пропонує альтернативний спосіб - так званий діапазон DYNAMIC. Це діапазон, який автоматично розширює оновлення. Це ідеально, якщо ваші списки постійно розширюються (наприклад, дані про продажі за місяць).
Щоб налаштувати динамічний діапазон, нам потрібно мати назву діапазону - тому ми назвемо наш AGE_DATA. Підхід до налаштування динамічних діапазонів відрізняється між Excel 2007 та попередніми версіями Excel:
У Excel 2007 натисніть «Визначити ім'я» під формулами:
У попередніх версіях Excel натисніть «Вставити», а потім - «Імена».
У спливаючому вікні введіть назву нашого динамічного діапазону - це «ВІКОВІ ДАНІ»:
У полі з позначкою «Посилається на» нам потрібно ввести діапазон наших даних. Це буде досягнуто за допомогою функції OFFSET. Це має 5 аргументів:
= ЗМІЩЕННЯ (Посилання, Рядки, Cols, Висота, Ширина)
- Посилання - це адреса верхнього лівого кута нашого діапазону - в даному випадку комірка B5
- Ряди - це кількість рядків з ВЕРХНОГО ВЛІВОГО, які ми хочемо мати в цьому діапазоні - це буде 0 у цьому випадку
- Cols - це кількість рядків з ВЕРХНОГО ВЛІВОГО, які ми хочемо, щоб був цей діапазон - це буде 0 у цьому випадку
- Висота діапазону - про це дивіться нижче
- Ширина діапазону - це 2, у нас є ДВІ стовпці в нашому діапазоні (ім'я осіб та їх вік)
Тепер висота діапазону буде змінюватися залежно від кількості записів у нашій таблиці (яка наразі становить 7).
Звичайно, нам потрібен спосіб підрахунку рядків у нашій таблиці, які оновлюються автоматично - тому одним із способів цього є використання функції COUNTA. Це просто підраховує кількість не порожніх клітинок у діапазоні. Оскільки наші імена у стовпці В, кількість записів у наших даних COUNTA (B: B).
Зауважте, що якщо б ви помістили це в клітинку, ви отримаєте значення 8 - оскільки воно включає заголовок Імена. Однак це несуттєво.
Тож у полі «Посилається на» ми ставимо:
= OFFSET ($ B $ 5,0,0, кількість (B: B), 2)
І натисніть кнопку OK. Тепер створено наш динамічний діапазон.
Тепер повернімось до формул VLOOKUP і замініть діапазон $ B: 4: $ C11 назвою нашого нового динамічного діапазону AGE_DATA, щоб ми мали:
Поки що нічого не змінилося. Однак, якщо ми додамо ще кілька імен до нашої таблиці:
А в камері, де був Павло, замініть його новим ім’ям, таким як Педро (цього не було в оригінальному списку):
І ми бачимо, що Excel автоматично повернув вік Педро - навіть якщо ми не змінили формули VLOOKUP. Натомість область динамічного діапазону збільшилася, включивши додаткові назви.
Динамічні діапазони дуже корисні, коли у нас збільшується обсяг даних - особливо, коли потрібні таблиці VLOOKUP і PIVOT.
