Descărcați exemplul de registru de lucru
Acest tutorial va demonstra cum se calculează „subtotalul dacă”, numărând doar rândurile vizibile cu criterii.
Funcția SUBTOTAL
Funcția SUBTOTAL poate efectua diverse calcule pe o gamă de date (număr, sumă, medie etc.). Cel mai important, poate fi folosit pentru a calcula numai rândurile vizibile (filtrate). În acest exemplu, vom folosi funcția de numărare (COUNTA) rânduri vizibile prin setarea argumentului SUBTOTAL function_num la 3 (O listă completă a funcțiilor posibile poate fi găsită aici.)
= SUBTOTAL (3, $ D $ 2: $ D $ 14)
Observați cum se modifică rezultatele pe măsură ce filtrăm manual rândurile.
SUBTOTAL DACĂ
Pentru a crea un „Subtotal Dacă”, vom folosi o combinație de SUMPRODUCT, SUBTOTAL, OFFSET, ROW și MIN într-o formulă matrice. Folosind această combinație, putem crea în esență o funcție generică „SUBTOTAL IF”. Să parcurgem un exemplu.
Avem o listă a membrilor și statutul de prezență al acestora pentru fiecare eveniment:
Se presupune că ni se cere să numărăm numărul membrilor care au participat la un eveniment dinamic, deoarece filtrăm manual lista astfel:
Pentru a realiza acest lucru, putem folosi această formulă:
= SUMPRODUCT ((=) * (SUBTOTAL (3, OFFSET (, ROW () - MIN (ROW ()), 0))))
= SUMPRODUCT ((D2: D14 = "Participat") * (SUBTOTAL (3, OFFSET (D2, ROW (D2: D14) -MIN (ROW (D2: D14)), 0))))
Când utilizați Excel 2022 și versiunile anterioare, trebuie să introduceți formula matrice apăsând CTRL + SHIFT + ENTER pentru a spune Excel că introduceți o formulă matrice. Veți ști că formula a fost introdusă corect ca o formulă matrice atunci când parantezele crețate apar în jurul formulei (a se vedea imaginea de mai sus).
Cum funcționează formula?
Formula funcționează prin înmulțirea a două matrice în interiorul SUMPRODUCT, unde prima matrice tratează criteriile noastre și a doua matrice filtrează numai rândurile vizibile:
= SUMPRODUCT (*)
Matricea de criterii
Matricea de criterii evaluează fiecare rând din intervalul nostru de valori (Starea „Participat” în acest exemplu) și generează un tablou de genul acesta:
=(=)
= (D2: D14 = "Participat")
Ieșire:
{ADEVĂRAT; FALS; FALS; ADEVĂRAT; FALS; TURE; TURE; TURE; FALS; FALS; ADEVĂRAT; FALS; ADEVĂRAT}
Rețineți că ieșirea din prima matrice din formula noastră ignoră dacă rândul este vizibil sau nu, acesta este locul în care a doua matrice vine în ajutor.
Matricea de vizibilitate
Folosind SUBTOTAL pentru a exclude rândurile nevizibile din gama noastră, putem genera matricea noastră de vizibilitate. Cu toate acestea, SUBTOTAL singur va returna o singură valoare, în timp ce SUMPRODUCT așteaptă o serie de valori. Pentru a rezolva acest lucru, folosim OFFSET pentru a trece un rând la rând. Această tehnică necesită alimentarea cu OFFSET a unui tablou care conține un număr la un moment dat. A doua matrice arată astfel:
= SUBTOTAL (3, OFFSET (, ROW () - MIN (ROW ()), 0))
= SUBTOTAL (3, OFFSET (D2, ROW (D2: D14) -MIN (ROW (D2: D14)), 0))
Ieșire:
{1;1;0;0;1;1}
Îmbinându-i pe cei doi:
= SUMPRODUCT ({TRUE; TRUE; FALSE; FALSE; TRUE; TRUE} * {1; 1; 0; 0; 1; 1})
= 4
SUBTOTAL IF cu criterii multiple
Pentru a adăuga mai multe criterii, pur și simplu să multiplicați mai multe criterii împreună în SUMPRODUCT, astfel:
= SUMPRODUCT ((=) * (=) * (SUBTOTAL (3, OFFSET (, ROW () - MIN (ROW ()), 0))))
= SUMPRODUCT ((E2: E14 = "Participat") * (B2: B14 = 2019) * (SUBTOTAL (3, OFFSET (E2, ROW (E2: E14) -MIN (ROW (E2: E14)), 0)) )))
SUBTOTAL DACĂ în Foi de calcul Google
Funcția SUBTOTAL IF funcționează exact la fel în Foi de calcul Google ca în Excel:
