Можно ли использовать функцию Excel для оценки критериев с использованием строк, подобно тому, как это делают функции COUNTIFS() и SUMIFS()?

Question

Моя проблема в том, что у меня есть критерий, записанный как строка в ячейке, например, ">= 50" находится в ячейке A1. Я могу легко использовать это с SUMIFS и COUNTIFS, поскольку эти функции принимают логические критерии как строку. Поэтому COUNTIFS(range, A1) скажет мне, сколько элементов в диапазоне больше или равны 50.

Я хочу иметь возможность сделать это с помощью функции, похожей на IF (просто что-то, что оценивает логический тест), но, похоже, единственный способ оценить булево значение в функции IF — это использовать реальные булевы операции, а не строку. Поэтому я хочу эквивалент IF(range >= 50, 1, 0) при применении к массиву (чтобы результатом был диапазон единиц и нулей в зависимости от того, выполняется ли условие), но вместо этого синтаксис будет примерно таким: IF_new(range, ">= 50", 1, 0).

Моя конечная цель — использовать функцию стиля IF_new(...) внутри SUMPRODUCT() (как индикатор того, какие строки должны быть включены). Поэтому я специально ищу способ сделать это, используя ">= 50" (чтобы логическое значение содержалось внутри строки и оценивалось таким же образом, как в COUNTIFS и SUMIFS) внутри функции, а не просто >=50.

Возможно ли это без использования VBA? Заранее спасибо!

Answer 1

Не существует стандартного метода для изменения =">=50"ячейки в булев оператор IF. Ссылка на ячейку, содержащую=">=50" можетможно использовать в функциях СЧЕТЕСЛИ или СУММЕСЛИ, но они обычно не работают хорошо в формулах СУММПРОИЗВ.

Используйте OFFSET, чтобы изолировать каждую ячейку в диапазоне как отдельную.диапазонпо критериям, таким как текст в СЧЕТЕСЛИ, для предоставления Вкл/Выкл в операции СУММПРОИЗВ.

=SUMPRODUCT(COUNTIF(OFFSET(A1, ROW(1:6), 0, 1, 1), A1), B2:B7, C2:C7)

Вероятно, этого можно добиться с помощью EVALUATE и именованных диапазонов.