Как использовать динамический диапазон в 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, созданная в учебнике:

 = СЧЕТЕСЛИ (ДВССЫЛ (Е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 подсчитывает количество ячеек, в которых есть текстовое значение two .

В этом случае результат равен 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 заключаются в том, что текстовые значения могут быть вставлены в виде ссылок на ячейки и что она динамически обновляет диапазоны при изменении вашей электронной таблицы.

Это значительно упрощает общее обслуживание электронных таблиц, особенно для очень больших электронных таблиц.

Оцените статью
Solutics.ru
Добавить комментарий