Используйте функцию 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 и INDIRECT
Две другие функции подсчета Excel - это COUNTA, которая подсчитывает ячейки, содержащие данные любого типа, игнорируя только пустые или пустые ячейки, и COUNTBLANK, который считает только пустые или пустые ячейки в диапазоне.
Поскольку обе эти функции имеют аналогичный синтаксис функции COUNTIF, они могут быть заменены в приведенном выше примере с помощью INDIRECT для создания следующих формул:
= СЧЕТЗ (ДВССЫЛ (Е1 & ":" & Е2))= СЧИТАТЬПУСТОТЫ (ДВССЫЛ (Е1 & ":" & Е2)
Для диапазона D1: D6 COUNTA возвращает ответ 4, поскольку четыре из пяти ячеек содержат данные. COUNTBLANK возвращает ответ 1, поскольку в диапазоне имеется только одна пустая ячейка. Преимущество использования функции INDIRECT во всех этих формулах состоит в том, что новые ячейки можно вставлять в любом месте диапазона. Диапазон динамически смещается внутри различных функций и соответственно обновляется результат. Без функции INDIRECT каждая функция должна быть отредактирована, чтобы включить все 7 ячеек, включая новую. Преимущества функции INDIRECT заключаются в том, что текстовые значения можно вставлять в качестве ссылок на ячейки и динамически обновлять диапазоны всякий раз, когда изменяется таблица. Это упрощает общее обслуживание электронных таблиц, особенно для очень больших электронных таблиц. Зачем использовать НЕПРАВИЛЬНУЮ функцию?
