Как использовать динамический диапазон в Excel с COUNTIF и INDIRECT

2024 Автор: Abigail Brown | [email protected]. Последнее изменение: 2023-12-17 06:59

Что нужно знать

• Функция ДВССЫЛ изменяет диапазон ссылок на ячейки в формуле, не редактируя формулу.
• Используйте ДВССЫЛ в качестве аргумента для СЧЁТЕСЛИ, чтобы создать динамический диапазон ячеек, отвечающих заданным критериям.
• Критерии устанавливаются функцией ДВССЫЛ, и учитываются только ячейки, соответствующие критериям.

В этой статье объясняется, как использовать функцию ДВССЫЛ в формулах Excel для изменения диапазона ссылок на ячейки, используемых в формуле, без необходимости редактирования самой формулы. Это гарантирует использование одних и тех же ячеек даже при изменении электронной таблицы. Информация относится к Excel 2019, Excel 2016, Excel 2013, Excel 2010, Excel для Mac и Excel Online.

Использование динамического диапазона с формулой СЧЁТЕСЛИ - ДВССЫЛ

Функция ДВССЫЛ может использоваться с рядом функций, которые принимают ссылку на ячейку в качестве аргумента, таких как функции СУММ и СЧЁТЕСЛИ.

Использование ДВССЫЛ в качестве аргумента для СЧЕТЕСЛИ создает динамический диапазон ссылок на ячейки, которые могут быть подсчитаны функцией, если значения ячеек соответствуют критериям. Он делает это, превращая текстовые данные, иногда называемые текстовой строкой, в ссылку на ячейку.

Этот пример основан на данных, показанных на изображении выше. Формула СЧЁТЕСЛИ - ДВССЫЛ, созданная в этом руководстве:

=СЧЁТЕСЛИ(ДВССЫЛ(E1&":"&E2), ">10")

В этой формуле аргумент функции ДВССЫЛ содержит:

• Ячейка ссылается на E1 и E2, которые содержат текстовые данные D1 и D6.
• Оператор диапазона, двоеточие (:), заключенное в двойные кавычки (" "), превращающее двоеточие в текст строка.
• Два амперсанда (&), которые используются для конкатенации или объединения двоеточия со ссылками на ячейки E1 и E2.

В результате ДВССЫЛ преобразует текстовую строку D1:D6 в ссылку на ячейку и передает ее функции СЧЁТЕСЛИ для подсчета, если указанные ячейки больше 10.

Функция ДВССЫЛ принимает любой ввод текста. Это могут быть ячейки рабочего листа, содержащие текст или ссылки на текстовые ячейки, введенные непосредственно в функцию.

Динамическое изменение диапазона формулы

Помните, цель состоит в том, чтобы создать формулу с динамическим диапазоном. Динамический диапазон можно изменить без редактирования самой формулы.

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

Функция СЧЁТЕСЛИ в этом примере подсчитывает ячейки, содержащие числа, только если они больше 10. Хотя четыре из пяти ячеек в диапазоне D1:D6 содержат данные, только три ячейки содержат числа. Ячейки, которые пусты или содержат текстовые данные, игнорируются функцией.

Подсчет текста с помощью СЧЁТЕСЛИ

Функция СЧЕТЕСЛИ не ограничивается подсчетом числовых данных. Он также подсчитывает ячейки, содержащие текст, проверяя, соответствуют ли они определенному тексту.

Для этого в ячейку G2 вводится следующая формула:

=СЧЁТЕСЛИ(ДВССЫЛ(E1&":"&E2), "два")

В этой формуле функция ДВССЫЛ ссылается на ячейки с B1 по B6. Функция СЧЁТЕСЛИ суммирует количество ячеек, содержащих текстовое значение два.

В этом случае результат равен 1.

COUNTA, COUNTBLANK и INDIRECT

Две другие функции подсчета Excel: СЧЕТЧИК, которая подсчитывает ячейки, содержащие данные любого типа, игнорируя только пустые или пустые ячейки, и СЧИТАТЬПУСТОТЫ, которая подсчитывает только пустые или пустые ячейки в диапазоне.

Поскольку синтаксис обеих этих функций аналогичен функции СЧЁТЕСЛИ, их можно заменить в приведенном выше примере на ДВССЫЛ, чтобы создать следующие формулы:

=СЧЁТ(ДВССЫЛ(E1&":"&E2))
=СЧЁТПУСТО(ДВССЫЛ(E1&":"&E2)
Для диапазона D1:D6 функция COUNTA возвращает ответ 4, так как четыре из пяти ячеек содержат данные. СЧИТАТЬПУСТОТЫ возвращает ответ 1, так как в диапазоне есть только одна пустая ячейка.

Зачем использовать НЕПРЯМУЮ функцию?
Преимущество использования функции ДВССЫЛ во всех этих формулах заключается в том, что новые ячейки можно вставлять в любом месте диапазона.
Диапазон динамически изменяется внутри различных функций, и результаты обновляются соответствующим образом.





Без функции ДВССЫЛ каждую функцию нужно было бы отредактировать, чтобы включить все 7 ячеек, включая новую.
Преимущество функции ДВССЫЛ заключается в том, что текстовые значения могут быть вставлены в качестве ссылок на ячейки и что она динамически обновляет диапазоны всякий раз, когда изменяется таблица.
Это значительно упрощает общее обслуживание электронных таблиц, особенно для очень больших электронных таблиц.