Как использовать динамический диапазон в 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, созданная в учебнике:
В этой формуле аргумент для функции 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 не ограничивается подсчетом числовых данных. Он также считает ячейки, содержащие текст, проверяя, соответствуют ли они определенному тексту.
В этой формуле функция INDIRECT ссылается на ячейки с B1 по B6. Функция COUNTIF подсчитывает количество ячеек, в которых есть текстовое значение two .
COUNTA, COUNTBLANK и косвенный
Две другие функции подсчета Excel – это COUNTA, которая подсчитывает ячейки, содержащие данные любого типа, игнорируя только пустые или пустые ячейки, и COUNTBLANK, которая подсчитывает только пустые или пустые ячейки в диапазоне.
Поскольку обе эти функции имеют синтаксис, аналогичный функции COUNTIF, они могут быть заменены в приведенном выше примере с помощью INDIRECT для создания следующих формул:
Для диапазона D1: D6 COUNTA возвращает ответ 4, поскольку четыре из пяти ячеек содержат данные. COUNTBLANK возвращает ответ 1, поскольку в диапазоне есть только одна пустая ячейка.
Зачем использовать косвенную функцию?
Преимущество использования функции INDIRECT во всех этих формулах заключается в том, что новые ячейки можно вставлять в любое место диапазона.
Диапазон динамически изменяется внутри различных функций, и результаты обновляются соответствующим образом.
Без функции INDIRECT каждая функция должна была бы быть отредактирована, чтобы включить все 7 ячеек, включая новую.
Преимущества функции INDIRECT заключаются в том, что текстовые значения могут быть вставлены в виде ссылок на ячейки и что она динамически обновляет диапазоны при изменении вашей электронной таблицы.
Это значительно упрощает общее обслуживание электронных таблиц, особенно для очень больших электронных таблиц.
[expert_bq id=»1570″]Это суждение справедливо и для случаев, когда в результате выполнения какой-либо функции в ячейку была выведена пустая строка. Если же вы хотите что-то уточнить, обращайтесь ко мне![/expert_bq] это означает, что если вы протяните формулу, то ссылка на ячейку $F$4 останется на месте, т.е. зафиксирована строка ‘4’ и столбец ‘F’. Если вы еще раз нажмёте клавишу F4, то ссылка станет F$4 — это означает, что зафиксирована строка 4, а столбец F будет перемещаться.Excel выбрать непустые ячейки из диапазона
- Ссылки на ячейки E1 и E2, которые содержат текстовые данные D1 и D6.
- Оператор диапазона – двоеточие (: ), заключенное в двойные кавычки ( “” ), которое превращает двоеточие в текстовую строку.
- Два амперсанда (&), которые используются для объединения или объединения двоеточия с ссылками на ячейки E1 и E2.
Программа должна для каждого контракта, где цена превышает предельную, заменять ее (в рабочем листе Контракты) на соответствующую предельную цену. Должно также подсчитываться и выводиться на экран количество контрактов, для которых потребовалось снижение цены.
[expert_bq id=»1570″]Если требуется, чтобы функция возвращала значение ИСТИНА, если ячейка не является пустой, ее можно использовать совместно с функцией НЕ. Если же вы хотите что-то уточнить, обращайтесь ко мне![/expert_bq]
Единственный аргумент является обязательным для заполнения и может принимать ссылку на ячейку или на диапазон ячеек, в которых необходимо определить наличие каких-либо данных. Если функция принимает диапазон ячеек, функция должна быть использована в качестве формулы массива.
Как зафиксировать ссылку в Excel? | Что важно знать о | Статьи
- Если бы листы назывались у нас Магазин 1, Магазин 2 и так далее, то дополнительный столбец со словами Лист1, Лист2 и так далее нам бы не потребовался.
- Функцию ДВССЫЛ используют часто тогда, когда требуется изменить ссылку на ячейку в формуле, не изменяя саму формулу.
Функция СУММ используется для определения суммы величин, возвращаемых функцией –ЕПУСТО для каждой ячейки из диапазона B3:B17 (числовых значений, поскольку используется двойное отрицание). Если запись СУММ(–ЕПУСТО(B3:B17) возвращает любое значение >0, функция ЕСЛИ вернет значение ИСТИНА.
Как включить или исключить ячейку из среднего диапазона, в зависимости от значения ячеек в другой строке?
Примечание: Вы не можете удалить или изменить имена, определенные для ячеек или диапазонов в поле имя . Имена можно удалять и изменять только в диспетчере имен (вкладка » формулы «, Группа » определенные имена «). Дополнительные сведения см. в статье Определение и использование имен в формулах.
