Excel: Комбинация SUMPRODUCT с SUBTOTAL при подсчете диапазона ячеек больше, чем другой диапазон ячеек

Excel: Комбинация SUMPRODUCT с SUBTOTAL при подсчете диапазона ячеек больше, чем другой диапазон ячеек
Excel: Комбинация SUMPRODUCT с SUBTOTAL при подсчете диапазона ячеек больше, чем другой диапазон ячеек - karsten_wuerth @ Unsplash

Привет сообщество суперпользователей,

В настоящее время я использую формулу SUMPRODUCT SUMPRODUCT(--(H101:H106<L101:L106)).

Эта формула возвращает число, которое показывает, сколько ячеек в диапазоне H101:H106 больше, чем эквивалентная ячейка напротив в L101:L106.

Я пытаюсь объединить это с SUBTOTAL, чтобы формула сравнивала только те строки, которые я фильтрую. Я немного поискал и увидел несколько сложных решений, которые объединяют формулу SUBTOTAL с OFFSET, но я не могу понять, как заставить это работать с тем, чего я пытаюсь достичь.

Надеюсь, это имеет смысл, и заранее спасибо за любую помощь!

Суть заключается в том, что вам нужно создать массив, состоящий из того же количества строк, что и другие булевы, а затем перемножить их вместе.

Чтобы создать массив True/False о том, виден он или нет, нужно:

SUBTOTAL(3,OFFSET(H101,ROW(H101:H106)-MIN(ROW(H101:H106)),0))>0

ROW(H101:H106)-MIN(ROW(H101:H106)) возвращает массив из, в данном случае из {0;1;2;3;4;5}.

Затем этот массив передается в OFFSET, а массив диапазонов передается в SUBTOTAL.

Мы используем 3 или COUNTA, чтобы узнать, виден ряд или нет. Это вернет вертикальный массив TRUE/FALSE, TRUE будет возвращен, если ряд виден, и FALSE, если нет.

Таким образом, комбинируя вашу формулу и SUBTOTAL:

=SUMPRODUCT((SUBTOTAL(3,OFFSET(H101,ROW(H101:H106)-MIN(ROW(H101:H106)),0))>0)*(H101:H106<L101:L106))

Получится количество, в котором и строка видна, и значение в H меньше, чем значение в L.

Одно замечание: я забыл, требуется ли для этого Ctrl-Shift-Enter в старых версиях или нет.


NevaDev, 7 февраля 2023 г., 04:33