Як використовувати динамічний діапазон в Excel З COUNTIF та INDIRECT

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

Інструкції в цій статті стосуються Excel 2019, Excel 2016, Excel 2013, Excel 2010, Excel для Mac та Excel Online.

Використовуйте динамічний діапазон за допомогою формули COUNTIF - INDIRECT

Функція INDIRECT може використовуватися з низкою функцій, які приймають посилання на клітинку як аргумент, наприклад, функції SUM та COUNTIF.

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

Знімок екрана

Цей приклад ґрунтується на даних, показаних на зображенні вище. Формула COUNTIF - INDIRECT, створена в підручнику:

= COUNTIF (непрямий (E1 & ":" & E2), "> 10")

У цій формулі аргумент функції INDIRECT містить:

  • Посилання на комірки E1 і E2, які містять текстові дані D1 і D6.
  • Оператор діапазону - двокрапка (:), оточена подвійними лапками ( "" ), що перетворює двокрапку в текстовий рядок.
  • Два амперсанда (&), які використовуються для об'єднання або з’єднання товстої кишки з посиланнями комірок E1 і E2.

Результат полягає в тому, що INDIRECT перетворює текстовий рядок D1: D6 в посилання на комірку і передає його в функцію COUNTIF, що підраховується, якщо посилаються комірки більше 10.

Функція INDIRECT приймає будь-які введення тексту. Це можуть бути комірки на робочому аркуші, що містять текст або посилання на текстові комірки, які вводяться безпосередньо у функцію.

Динамічно змінюйте діапазон формули

Пам'ятайте, мета - створити формулу з динамічним діапазоном. Динамічний діапазон можна змінити без редагування самої формули.

Змінюючи текстові дані, розміщені в комірках E1 і E2, з D1 і D6 на D3 і D7, діапазон, що нараховується функцією, може бути легко змінений з D1: D6 на D3: D7. Це виключає необхідність безпосередньо редагувати формулу в комірці G1.

Функція COUNTIF у цьому прикладі підраховує клітинки, що містять числа, якщо вони більше 10. Хоча чотири з п'яти комірок у діапазоні D1: D6 містять дані, лише три комірки містять числа. Порожні клітини або містять текстові дані функція ігнорується.

Підрахунок тексту за допомогою COUNTIF

Функція COUNTIF не обмежується підрахунком числових даних. Він також підраховує комірки, що містять текст, перевіряючи, чи вони відповідають певному тексту.

Для цього в комірку G2 вводиться наступна формула:

= COUNTIF (непрямий (E1 & ":" & E2), "два")

У цій формулі функція INDIRECT посилається на клітини B1 до B6. Функція COUNTIF підсумовує кількість комірок, у яких текстове значення два.

У цьому випадку результат дорівнює 1.

COUNTA, COUNTBLANK та INDIRECT

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

Оскільки обидві ці функції мають аналогічний синтаксис функції COUNTIF, вони можуть бути замінені у наведеному вище прикладі INDIRECT для створення наступних формул:

= COUNTA (непряма (E1 & ":" & E2))
= COUNTBLANK (непрямий (E1 & ":" & E2)

Для діапазону D1: D6 COUNTA повертає відповідь 4, оскільки чотири з п'яти комірок містять дані. COUNTBLANK повертає відповідь 1, оскільки в діапазоні є лише одна порожня клітинка.

Навіщо використовувати функцію НЕПРЯМО?

Перевага використання функції INDIRECT у всіх цих формулах полягає в тому, що нові комірки можна вставити будь-де в діапазоні.

Діапазон динамічно зміщується всередині різних функцій, і результати оновлюються відповідно.

Знімок екрана

Без функції INDIRECT кожну функцію потрібно редагувати, щоб вона включала всі 7 комірок, включаючи нову.

Переваги функції INDIRECT полягають у тому, що текстові значення можна вставляти у вигляді посилань на комірки та що вона динамічно оновлює діапазони щоразу, коли ваша електронна таблиця змінюється.

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