Як створити динамічний визначений діапазон в Excel

Ваші дані Excel часто змінюються, тому корисно створити динамічно визначений діапазон, який автоматично розширюється та звужується до розміру діапазону даних. Давайте подивимося як.

Використовуючи динамічно визначений діапазон, вам не потрібно вручну редагувати діапазони формул, діаграм і зведених таблиць, коли дані змінюються. Це відбудеться автоматично.

Для створення динамічних діапазонів використовуються дві формули: OFFSET та INDEX. У цій статті буде зосереджено на використанні функції INDEX, оскільки це більш ефективний підхід. OFFSET є мінливою функцією, яка може сповільнити роботу великих електронних таблиць.

Створіть динамічний визначений діапазон в Excel

Для нашого першого прикладу ми маємо список даних з одним стовпцем, який показано нижче.

Нам потрібно, щоб це було динамічним, щоб у разі додавання або видалення інших країн діапазон автоматично оновлювався.

  Як отримати код підтвердження Apple на пристрої

У цьому прикладі ми хочемо уникнути осередку заголовка. Таким чином, ми хочемо діапазон $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) для пошуку та повернення.

  Як залишити робочу область Slack

Одна з функцій COUNTA використовується для підрахунку непорожніх рядків, а інша — для непустих стовпців, що робить її динамічною в обох напрямках. Хоча ця формула починалася з A1, ви могли вказати будь-яку початкову клітинку.

Тепер ви можете використовувати це визначене ім’я (продажі) у формулі або як ряд даних діаграми, щоб зробити їх динамічними.