Привет сообщество суперпользователей,
В настоящее время я использую формулу 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 в старых версиях или нет.