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