Formula generică
=SUMPRODUCT((range=criteria)*(SUBTOTAL(103,OFFSET(range,rows,0,1))))
rezumat
Pentru a număra rândurile vizibile numai cu criterii, puteți utiliza o formulă destul de complexă bazată pe SUMPRODUCT, SUBTOTAL și OFFSET. În exemplul prezentat, formula din C12 este:
=SUMPRODUCT((C5:C8=C10)*(SUBTOTAL(103,OFFSET(C5,ROW(C5:C8)-MIN(ROW(C5:C8)),0))))
Prefaţă
Funcția SUBTOTAL poate genera cu ușurință sume și numărări pentru rândurile ascunse și non-ascunse. Cu toate acestea, nu poate gestiona criterii precum COUNTIF sau SUMIF fără ajutor. O soluție este utilizarea SUMPRODUCT pentru a aplica atât funcția SUBTOTAL (prin OFFSET), cât și criteriile. Detaliile acestei abordări sunt descrise mai jos.
Explicaţie
La bază, această formulă funcționează prin configurarea a două matrice în interiorul SUMPRODUCT. Prima matrice aplică criterii, iar a doua matrice gestionează vizibilitatea:
=SUMPRODUCT(criteria*visibility)
Criteriul se aplică cu o parte din formulă:
=(C5:C8=C10)
Ceea ce generează o matrice ca aceasta:
(FALSE;TRUE;FALSE;TRUE)
Unde ADEVĂRAT înseamnă „îndeplinește criteriile”. Rețineți, deoarece folosim multiplicarea (*) pe această matrice, valorile TRUE FALSE vor fi convertite automat în 1 și 0 prin operația matematică, așa că vom termina cu:
(0;1;0;1)
Filtrul de vizibilitate este aplicat folosind SUBTOTAL, cu funcția numărul 103.
SUBTOTAL este capabil să excludă rândurile ascunse atunci când rulăm calcule, deci îl putem folosi în acest caz pentru a genera un „filtru” pentru a exclude rândurile ascunse din interiorul SUMPRODUCT. Totuși, problema este că SUBTOTAL returnează un singur număr, în timp ce avem nevoie de o serie de rezultate pentru a-l utiliza cu succes în SUMPRODUCT. Trucul este să folosiți OFFSET pentru a alimenta SUBTOTAL o referință pe rând, astfel încât OFFSET să returneze un rezultat pe rând.
Desigur, acest lucru necesită un alt truc, care este de a oferi OFFSET o matrice care conține un număr pe rând, începând cu zero. Facem asta cu o expresie construită pe funcția ROW:
=ROW(C5:C8)-MIN(ROW(C5:C8)
care va genera o matrice ca aceasta:
(0;1;2;3)
În rezumat, a doua matrice (care gestionează vizibilitatea utilizând SUBTOTAL), este generată astfel:
=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)
Și, în cele din urmă, avem:
=SUMPRODUCT((0,1,0,1)*(1;0;1;1))
Care returnează 1.
Criterii multiple
Puteți extinde formula pentru a gestiona mai multe criterii ca aceasta:
=SUMPRODUCT((rng1=criteria1)*(rng2=criteria2)*(SUBTOTAL(103,OFFSET(rng,rows,0,1))))
Rezumatul rezultatelor
Pentru a returna o sumă de valori în loc de un număr, puteți adapta formula pentru a include un interval de sumă:
=SUMPRODUCT(criteria*visibility*sumrange)
Criteriile și matricile de vizibilitate funcționează la fel cum s-a explicat mai sus, excluzând celulele care nu sunt vizibile. Dacă aveți nevoie de potrivire parțială, puteți construi o expresie folosind ISNUMBER + SEARCH, așa cum este explicat aici.
