Дані у вашому Excel-файлі часто підлягають змінам, тому створення динамічно визначеного діапазону, здатного автоматично адаптуватися до розміру ваших даних, є вкрай корисним рішенням. Розглянемо, як це можна реалізувати.
Застосування динамічно визначеного діапазону дозволяє уникнути необхідності ручного коригування діапазонів у формулах, діаграмах і зведених таблицях при зміні даних. Все відбувається в автоматичному режимі.
Для формування таких діапазонів використовуються дві основні формули: OFFSET та INDEX. У цьому матеріалі ми сконцентруємося на використанні функції INDEX, оскільки вона є більш ефективним рішенням. OFFSET, у свою чергу, є нестабільною функцією, що може сповільнювати роботу великих таблиць.
Створення динамічного діапазону в Excel
Для початку розглянемо приклад зі списком даних, розміщеним в одному стовпці.
Нашою задачею є зробити цей діапазон динамічним, щоб при додаванні або видаленні країн він автоматично оновлювався.
У цьому випадку ми хочемо виключити клітинку заголовка. Таким чином, нам потрібен діапазон $A$2:$A$6, але динамічний. Для цього перейдіть до розділу “Формули” і оберіть “Визначити назву”.
У полі “Назва” введіть “країни”, а у полі “Посилається на” введіть наведену нижче формулу.
=$A$2:INDEX($A:$A,COUNTA($A:$A))
Введення цієї формули в клітинку таблиці, а потім її копіювання до поля “Нове ім’я” іноді є більш швидким і зручним способом.
Як це працює?
Перша частина формули ідентифікує початкову клітинку діапазону (A2), після чого слідує оператор діапазону (:).
=$A$2:
Оператор діапазону змушує функцію INDEX повертати діапазон, а не окреме значення. Після цього функція INDEX використовується разом із функцією COUNTA. COUNTA визначає кількість непорожніх клітинок у стовпці A (в нашому випадку – шість).
INDEX($A:$A,COUNTA($A:$A))
Ця формула просить функцію INDEX повернути діапазон, що відповідає останній непорожній клітинці у стовпці A ($A$6).
В результаті ми отримуємо діапазон $A$2:$A$6, який завдяки функції COUNTA є динамічним, оскільки він автоматично визначає останній рядок. Тепер ви можете використовувати ім’я “країни” у правилах перевірки даних, формулах, діаграмах та інших місцях, де потрібно посилатися на список країн.
Створення двовимірного динамічного діапазону
У першому прикладі діапазон був динамічним лише за висотою. Однак, трохи модифікувавши формулу та використовуючи ще одну функцію COUNTA, можна створити діапазон, який буде динамічним як за висотою, так і за шириною.
Для ілюстрації візьмемо наступний набір даних.
У цьому випадку ми створимо динамічний діапазон, який включатиме і заголовки. Перейдіть до “Формули” та оберіть “Визначити назву”.
У полі “Назва” введіть “продажі”, а у полі “Посилається на” вставте формулу, наведену нижче.
=$A$1:INDEX($1:$1048576,COUNTA($A:$A),COUNTA($1:$1))
Ця формула використовує $A$1 як початкову клітинку. Функція INDEX застосовує діапазон усього аркуша ($1:$1048576) для пошуку та повернення потрібних даних.
Одна функція COUNTA використовується для підрахунку непорожніх рядків, а інша – для підрахунку непорожніх стовпців, що робить діапазон динамічним у двох вимірах. Хоча формула починається з A1, ви можете вказати будь-яку іншу початкову клітинку.
Тепер ви можете використовувати ім’я “продажі” у формулах або як набір даних для діаграм, щоб забезпечити їх динамічну зміну.