Lejupielādējiet darbgrāmatas piemēru
Šī apmācība parādīs, kā aprēķināt “starpsumma, ja”, skaitot tikai redzamās rindas ar kritērijiem.
FUNKCIJA
Funkcija SUBTOTAL var veikt dažādus aprēķinus par datu diapazonu (skaits, summa, vidējais utt.). Vissvarīgākais ir tas, ka to var izmantot, lai aprēķinātu tikai redzamās (filtrētās) rindās. Šajā piemērā mēs izmantosim funkciju, lai saskaitītu (COUNTA) redzamās rindas, iestatot argumentu SUBTOTAL function_num uz 3 (pilns iespējamo funkciju saraksts atrodams šeit.)
= KOPSUMMA (3 USD, 2 USD: 14 USD)
Ievērojiet, kā mainās rezultāti, manuāli filtrējot rindas.
Starpsumma IF
Lai izveidotu “Starpsumma”, mēs masīva formulā izmantosim SUMPRODUCT, SUBTOTAL, OFFSET, ROW un MIN kombināciju. Izmantojot šo kombināciju, mēs būtībā varam izveidot vispārēju funkciju “SUBTOTAL IF”. Apskatīsim piemēru.
Mums ir dalībnieku saraksts un viņu apmeklējuma statuss katram pasākumam:
Pieņemot, ka mums tiek lūgts saskaitīt dalībnieku skaitu, kuri ir dinamiski apmeklējuši pasākumu, manuāli filtrējot sarakstu šādi:
Lai to paveiktu, mēs varam izmantot šo formulu:
= SUMPRODUCT ((=)*(VISSUMMĀ (3, OFFSET (, ROW ()-MIN (ROW ()), 0)))))
= SUMPRODUCT ((D2: D14 = "Apmeklēts")*(SUBTOTAL (3, OFFSET (D2, ROW (D2: D14) -MIN (ROW (D2: D14)), 0))))
Izmantojot programmu Excel 2022 un vecāku versiju, masīva formula jāievada, nospiežot CTRL + SHIFT + ENTER lai pateiktu programmai Excel, ka ievadāt masīva formulu. Jūs zināt, ka formula tika pareizi ievadīta kā masīva formula, kad ap formulu parādās cirtaini iekavas (skatiet attēlu iepriekš).
Kā darbojas formula?
Formula darbojas, reizinot divus masīvus SUMPRODUCT iekšpusē, kur pirmais masīvs attiecas uz mūsu kritērijiem, bet otrais masīvs filtrē tikai redzamās rindas:
= SUMPRODUCT (*)
Kritēriju masīvs
Kritēriju masīvs novērtē katru rindu mūsu vērtību diapazonā (statuss “Apmeklēts” šajā piemērā) un ģenerē šādu masīvu:
=(=)
= (D2: D14 = "Apmeklēts")
Izeja:
{TRUE; FALSE; FALSE; PATIESA; FALSE; TURE; TURE; TURE; FALSE; FALSE; PATIESA; FALSE; TRUE}
Ņemiet vērā, ka mūsu formulas pirmā masīva izvade ignorē to, vai rinda ir redzama vai nē, un šeit palīdz mūsu otrais masīvs.
Redzamības masīvs
Izmantojot SUBTOTAL, lai izslēgtu mūsu diapazonā neredzamas rindas, mēs varam izveidot savu redzamības masīvu. Tomēr tikai SUBTOTAL atgriezīs vienu vērtību, bet SUMPRODUCT gaida vērtību masīvu. Lai to novērstu, mēs izmantojam OFFSET, lai izietu vienu rindu vienlaikus. Šī metode prasa barot OFFSET masīvu, kurā vienlaikus ir viens skaitlis. Otrais masīvs izskatās šādi:
= KOPSUMMA (3, OFFSET (, ROW ()-MIN (ROW ()), 0))
= KOPSUMMA (3, NOBĪDE (D2, RIJA (D2: D14) -MIN (RIJA (D2: D14)), 0))
Izeja:
{1;1;0;0;1;1}
Abu savienošana kopā:
= SUMPRODUCT ({TRUE; TRUE; FALSE; FALSE; TRUE; TRUE} * {1; 1; 0; 0; 1; 1})
= 4
Starpsumma IF ar vairākiem kritērijiem
Lai pievienotu vairākus kritērijus, vienkārši pievienojiet vairākus kritērijus kopā SUMPRODUCT, piemēram:
= SUMPRODUCT ((=)*(=)*(SUBTOTAL (3, OFFSET (, ROW ()-MIN (ROW ()), 0)))))
= SUMPRODUCT ((E2: E14 = "Apmeklēts")*(B2: B14 = 2019)*(KOKSUMTS (3, OFFSET (E2, ROW (E2: E14)) -MIN (ROW (E2: E14)), 0)) ))
Starpsumma IF Google izklājlapās
Funkcija SUBTOTAL IF darbojas Google izklājlapās tāpat kā programmā Excel:
