Використовуйте функцію INDIRECT у формулах Excel, щоб змінити діапазон посилань на комірки, які використовуються у формулі, без необхідності зміни самої формули. Це гарантує, що використовуються одні й ті самі комірки, навіть якщо ваша електронна таблиця змінюється.
Інструкції в цій статті належать до Excel 2019, Excel 2016, Excel 2013, Excel 2010, Excel для Mac і Excel Online.
Використовуйте динамічний діапазон з формулою COUNTIF - INDIRECT
Функція INDIRECT може використовуватися з низкою функцій, які приймають посилання на комірку як аргумент, такі як функції SUM і COUNTIF.
Використання INDIRECT як аргументу для COUNTIF створює динамічний діапазон посилань на комірки, які можуть бути підраховані функцією, якщо значення комірок відповідають критерію. Це робиться шляхом перетворення текстових даних, іноді званих текстовим рядком, на посилання на комірку.
Цей приклад заснований на даних, показаних на малюнку вище. COUNTIF - INDIRECT формула створена в підручнику:
= СЧЕТЕСЛИ (ДВССИЛ (Е1 & «»:"& Е2)", > 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 вводиться наступна формула:
= СЧЕТЕСЛИ (ДВССИЛ (Е1 & «»:«» & Е2), «» два «»)
У цій формулі функція INDIRECT посилається на комірки з B1 по B6. Функція COUNTIF підсумовує кількість комірок, в яких є текстове значення два.
У цьому випадку результат дорівнює 1.
COUNTA, COUNTBLANK і БЕЗПОСЕРЕДНІЙ
Дві інші функції підрахунку Excel - це COUNTA, яка підраховує комірки, що містять дані будь-якого типу, ігноруючи тільки порожні або порожні комірки, і COUNTBLANK, яка підраховує тільки порожні або порожні комірки в діапазоні.
Оскільки обидві ці функції мають синтаксис, аналогічний функції COUNTIF, їх можна замінити на наведений вище приклад за допомогою INDIRECT для створення таких формул:
= COUNTA (INDIRECT (E1 & "":"" & E2))
= COUNTBLANK (INDIRECT (E1 & "":"" & E2)
Діапазон D1: D6 COUNTA повертає відповідь 4, оскільки чотири з п'яти комірок містять дані. COUNTBLANK повертає відповідь 1, оскільки в діапазоні є лише одна порожня комірка.
Навіщо використовувати непряму функцію?
Перевага використання функції INDIRECT у всіх цих формулах полягає в тому, що нові комірки можна вставляти в будь-яке місце діапазону.
Діапазон динамічно переміщується всередині різних функцій, і результати оновлюються відповідним чином.
Без функції INDIRECT кожна функція повинна була бути відредагована, щоб включити всі 7 комірок, включаючи нову.
Переваги функції INDIRECT полягають у тому, що текстові значення можна вставляти як посилання на комірки і що вона динамічно оновлює діапазони при кожній зміні вашої електронної таблиці.
Це значно спрощує загальне обслуговування електронних таблиць, особливо для дуже великих електронних таблиць.