Есть ли общий способ обработки только видимых ячеек в Excel?

Мне интересно, есть ли общий способ выразить, что следует учитывать только видимые строки формулы.

Если бы у меня, например, была формула sumif($E5:$E100; "ABC"; $F5:F100), было бы очень полезно, если бы был способ выразить, что заданные диапазоны должны учитывать только видимые ячейки. Я мог представить, что для конструкции диапазона, такой как % или like, может быть указан своего рода префикс. Например, тогда формула будет выглядеть как sumif(%$E5:%$E100; "ABC"; %F5:%F100), чтобы было ясно, что в заданных диапазонах должны учитываться только видимые строки.

То же самое будет, например, для sum(%A1:%A100), что будет означать, что в диапазоне между A1 и A100 должны быть взяты только видимые ячейки для суммирования ячеек.

Дело в том, что этот конструкт может быть взят внутри любой формулы, какой бы она ни была.

Заранее спасибо Георг


excel formula calculation cell visible
person Horowitzathome    schedule 17.02.2019    source источник
comment
Как правило, для суммирования диапазона на основе совпадения в диапазоне критериев... но только для видимых строк вы можете использовать эту формулу: =SUMPRODUCT((criteriarange=criteria)+0,SUBTOTAL(109,OFFSET(sumrange,ROW(sumrange)-MIN(ROW(sumrange)),0,1,1))) Первая часть (диапазон критериев=критерий)+0 просто проверяет критерии для каждой строки и возвращает 1 для совпадения или 0 OFFSET возвращает массив диапазонов, где каждый диапазон в этом случае представляет собой одну ячейку из диапазона суммы. ПРОМЕЖУТОЧНЫЙ ИТОГ может обрабатывать это и с помощью функции суммы (109) дает сумму (т.е. значение) каждой ячейки, только когда она видна.   -  person skkakkar    schedule 17.02.2019
comment
использовать промежуточный итог вместо суммы   -  person Lambik    schedule 17.02.2019
comment
можно попробовать что-то вроде (не проверено) =SUMPRODUCT(($E$5:$E$100="ABC")+0,SUBTOTAL(109,OFFSET($F$2:$F$100,ROW($F$2:$F$100)-MIN(ROW($F$2:$F$100)),0,1,1)))   -  person skkakkar    schedule 17.02.2019

Ответы (1)


arrow_upward
1
arrow_downward

Как правило, для суммирования суммирования на основе совпадения в диапазоне критериев..... но только для видимых строк вы можете использовать эту формулу: =SUMPRODUCT((criteriarange=criteria)+0,SUBTOTAL(109,OFFSET(sumrange,ROW(sumrange)-MIN(ROW(sumrange)),0,1,1))) Первая часть (criteriarange=criteria)+0 просто проверяет критерии для каждой строки и возвращает 1 для совпадения или 0 OFFSET возвращает " массив диапазонов», где каждый диапазон в этом случае представляет собой одну ячейку из диапазона суммы. SUBTOTAL может обработать это и с суммой function (109) дает «сумму» (т.е. значение) каждой ячейки, только когда она видна. - SUMPRODUCT затем умножает два диапазона и суммирует результат, эффективно давая вам сумму видимых строк, где критерии соответствуют Попробуйте это

=SUMPRODUCT(($E$5:$E$100="ABC")+0,SUBTOTAL(109,OFFSET($F$5:$F$100,ROW($F$5:$F$100)-MIN(ROW($F$5:$F$100)),0,1,1)))

введите здесь описание изображения

person skkakkar    schedule 17.02.2019