Обща формула
=SUMPRODUCT((range=criteria)*(SUBTOTAL(103,OFFSET(range,rows,0,1))))
Обобщение
За да преброите видими редове само с критерии, можете да използвате доста сложна формула, базирана на SUMPRODUCT, SUBTOTAL и OFFSET. В показания пример формулата в C12 е:
=SUMPRODUCT((C5:C8=C10)*(SUBTOTAL(103,OFFSET(C5,ROW(C5:C8)-MIN(ROW(C5:C8)),0))))
Предговор
Функцията SUBTOTAL може лесно да генерира суми и отчитания за скрити и нескрити редове. Той обаче не може да се справи с критерии като COUNTIF или SUMIF без някаква помощ. Едно от решенията е да използвате SUMPRODUCT, за да приложите както функцията SUBTOTAL (чрез OFFSET), така и критериите. Подробностите за този подход са описани по-долу.
Обяснение
В основата си тази формула работи чрез настройване на два масива в SUMPRODUCT. Първият масив прилага критерии, а вторият масив обработва видимостта:
=SUMPRODUCT(criteria*visibility)
Критериите се прилагат с част от формулата:
=(C5:C8=C10)
Което генерира масив като този:
(FALSE;TRUE;FALSE;TRUE)
Когато TRUE означава „отговаря на критериите“. Забележете, тъй като използваме умножение (*) за този масив, стойностите TRUE FALSE автоматично ще бъдат преобразувани в 1 и 0 от математическата операция, така че в крайна сметка получаваме:
(0;1;0;1)
Филтърът за видимост се прилага с помощта на SUBTOTAL, с номер на функция 103.
SUBTOTAL е в състояние да изключи скрити редове при изпълнение на изчисления, така че можем да го използваме в този случай, за да генерираме "филтър", за да изключим скритите редове вътре в SUMPRODUCT. Проблемът обаче е, че SUBTOTAL връща едно число, докато ние се нуждаем от масив от резултати, за да го използваме успешно в SUMPRODUCT. Номерът е да използвате OFFSET, за да подадете SUBTOTAL по една препратка на ред, така че OFFSET да върне по един резултат на ред.
Разбира се, това изисква друг трик, който е да се даде на OFFSET масив, който съдържа по едно число на ред, започвайки с нула. Правим това с израз, изграден върху функцията ROW:
=ROW(C5:C8)-MIN(ROW(C5:C8)
който ще генерира масив като този:
(0;1;2;3)
В обобщение, вторият масив (който обработва видимостта с помощта на SUBTOTAL) се генерира по следния начин:
=SUBTOTAL(103,OFFSET(C5,ROW(C5:C8)-MIN(ROW(C5:C8)),0)) =SUBTOTAL(103,OFFSET(C5,(0;1;2;3),0)) =SUBTOTAL(103,("East";"West";"Midwest";"West")) =(1;0;1;1)
И накрая имаме:
=SUMPRODUCT((0,1,0,1)*(1;0;1;1))
Което връща 1.
Множество критерии
Можете да разширите формулата, за да обработва множество критерии като този:
=SUMPRODUCT((rng1=criteria1)*(rng2=criteria2)*(SUBTOTAL(103,OFFSET(rng,rows,0,1))))
Сумиране на резултатите
За да върнете сума от стойности вместо броене, можете да адаптирате формулата, така че да включва диапазон от суми:
=SUMPRODUCT(criteria*visibility*sumrange)
Критериите и масивите за видимост работят по същия начин, както е обяснено по-горе, с изключение на клетки, които не се виждат. Ако се нуждаете от частично съвпадение, можете да конструирате израз, като използвате ISNUMBER + SEARCH, както е обяснено тук.
