Atsisiųskite darbaknygės pavyzdį
Ši pamoka parodys, kaip apskaičiuoti „tarpinę sumą, jei“, skaičiuojant tik matomas eilutes su kriterijais.
SUBTOTAL funkcija
Funkcija SUBTOTAL gali atlikti įvairius duomenų diapazono skaičiavimus (skaičių, sumą, vidurkį ir tt). Svarbiausia, kad jį galima naudoti tik matomoms (filtruotoms) eilėms apskaičiuoti. Šiame pavyzdyje mes naudosime funkciją, kad suskaičiuotume (COUNTA) matomas eilutes, nustatydami argumentą SUBTOTAL function_num į 3 (visą galimų funkcijų sąrašą rasite čia.)
= POSUMA (3, 2 USD: 2 USD: 14 USD)
Atkreipkite dėmesį, kaip keičiasi rezultatai, kai rankiniu būdu filtruojame eilutes.
POSUMA, JEI
Norėdami sukurti „tarpinę sumą“, masyvo formulėje naudosime SUMPRODUCT, SUBTOTAL, OFFSET, ROW ir MIN derinį. Naudodami šį derinį, mes iš esmės galime sukurti bendrą „SUBTOTAL IF“ funkciją. Eikime per pavyzdį.
Turime kiekvieno renginio narių sąrašą ir jų dalyvavimo statusą:
Tarkime, kad mūsų prašoma suskaičiuoti dinamiškai įvykyje dalyvavusių narių skaičių, nes rankiniu būdu filtruojame sąrašą taip:
Norėdami tai padaryti, galime naudoti šią formulę:
= SUMPRODUCT ((=)*(SUBTOTAL (3, OFFSET (, ROW ()-MIN (ROW ()), 0)))))
= SUMPRODUCT ((D2: D14 = "Dalyvavo")*(SUBTOTAL (3, OFFSET (D2, ROW (D2: D14) -MIN (ROW (D2: D14)), 0))))
Kai naudojate „Excel 2022“ ir ankstesnes versijas, turite įvesti masyvo formulę paspausdami CTRL + SHIFT + ENTER pasakyti „Excel“, kad įvedate masyvo formulę. Jūs žinosite, kad formulė buvo tinkamai įvesta kaip masyvo formulė, kai aplink formulę atsiranda garbanotieji skliausteliai (žr. Paveikslėlį aukščiau).
Kaip veikia formulė?
Formulė veikia dauginant du masyvus SUMPRODUCT viduje, kur pirmasis masyvas susijęs su mūsų kriterijais, o antrasis masyvas filtruoja tik matomas eilutes:
= SUMPRODUCT (*)
Kriterijų masyvas
Kriterijų masyvas įvertina kiekvieną mūsų reikšmių diapazono eilutę (šiame pavyzdyje - „Dalyvavo“ būsena) ir sukuria tokį masyvą:
=(=)
= (D2: D14 = "Dalyvavo")
Išėjimas:
{TIESA; NETIESA; NETIESA; TIESA; NETIESA; TURE; TURE; TURE; NETIESA; NETIESA; TIESA; NETIESA; TIESA}
Atminkite, kad mūsų formulės pirmojo masyvo išvestis ignoruoja, ar eilutė matoma, ar ne, o čia padeda mūsų antrasis masyvas.
Matomumo masyvas
Naudodami SUBTOTAL, kad neįtrauktume nematomų eilučių į savo diapazoną, galime sukurti matomumo masyvą. Tačiau vien SUBTOTAL grąžins vieną vertę, o SUMPRODUCT tikisi daugybės reikšmių. Norėdami tai išspręsti, mes naudojame OFFSET, kad praeitume vieną eilutę vienu metu. Ši technika reikalauja maitinti OFFSET masyvą, kuriame vienu metu yra vienas skaičius. Antrasis masyvas atrodo taip:
= SUBTOTAL (3, OFFSET (, ROW ()-MIN (ROW ()), 0))
= SUBTOTAL (3, OFFSET (D2, ROW (D2: D14) -MIN (ROW (D2: D14)), 0))
Išėjimas:
{1;1;0;0;1;1}
Dviejų sujungimas:
= SUMPRODUCT ({TRUE; TRUE; FALSE; FALSE; TRUE; TRUE} * {1; 1; 0; 0; 1; 1})
= 4
POSUMA, jei yra keli kriterijai
Norėdami pridėti kelis kriterijus, tiesiog pridėkite dar kelis kriterijus kartu SUMPRODUCT:
= SUMPRODUCT ((=)*(=)*(SUBTOTAL (3, OFFSET (, ROW ()-MIN (ROW ()), 0)))))
= SUMPRODUCT ((E2: E14 = "Dalyvavo")*(B2: B14 = 2019)*(SUBTOTAL (3, OFFSET (E2, ROW (E2: E14)) -MIN (ROW (E2: E14)), 0)) ))
POSUMA, JEI „Google“ skaičiuoklėse
Funkcija SUBTOTAL IF „Google“ skaičiuoklėse veikia taip pat, kaip ir „Excel“:
