Töltse le a példa munkafüzetet
Ez az oktatóanyag bemutatja, hogyan kell kiszámítani a „részösszeg, ha” értéket, és csak a látható sorokat számolja meg kritériumokkal.
SUBTOTAL funkció
A SUBTOTAL függvény különféle számításokat végezhet az adatok tartományán (szám, összeg, átlag stb.). A legfontosabb, hogy csak látható (szűrt) sorokon lehet számolni. Ebben a példában a függvényt használjuk a (COUNTA) látható sorok megszámlálásához, ha a SUBTOTAL function_num argumentumot 3 -ra állítjuk (A lehetséges funkciók teljes listája itt található.)
= ÖSSZESEN (3, $ D $ 2: $ D $ 14)
Figyelje meg, hogyan változnak az eredmények, amikor manuálisan szűrjük a sorokat.
RÉSZSZÁM HA
„Részösszeg If” létrehozásához a SUMPRODUCT, SUBTOTAL, OFFSET, ROW és MIN kombinációját használjuk egy tömbképletben. Ezzel a kombinációval lényegében létrehozhatunk egy általános „SUBTOTAL IF” függvényt. Nézzünk egy példát.
Rendelkezünk a tagok listájával és jelenléti státuszával minden eseményhez:
Feltételezve, hogy megkérjük, számoljuk meg dinamikusan az eseményen részt vevő tagok számát, miközben manuálisan szűrjük a listát:
Ennek eléréséhez ezt a képletet használhatjuk:
= SUMPRODUCT ((=)*(RÉSZSZÁM (3, OFFSET (, SOR ()-MIN (ROW ()), 0)))))
= SUMPRODUCT ((D2: D14 = "Attended")*(RÉSZSZÁM (3, OFFSET (D2, SOR (D2: D14) -MIN (SOR (D2: D14)), 0))))
Az Excel 2022 és korábbi verziók használatakor meg kell adnia a tömb képletét a megnyomásával CTRL + SHIFT + ENTER hogy elmondja az Excelnek, hogy tömbképletet ír be. Tudni fogja, hogy a képletet helyesen adta meg tömbképletként, amikor a képlet körül göndör zárójelek jelennek meg (lásd a fenti képet).
Hogyan működik a képlet?
A képlet úgy működik, hogy megszoroz két tömböt a SUMPRODUCT -ban, ahol az első tömb a feltételeinkkel foglalkozik, a második tömbszűrő pedig csak a látható sorokra:
= SUMPRODUCT (*)
A kritériumok tömbje
A feltételtömb értékeli az értéktartományunk minden egyes sorát (ebben a példában „Látogatott” állapot), és egy ilyen tömböt hoz létre:
=(=)
= (D2: D14 = "részt vett")
Kimenet:
{IGAZ; HAMIS; HAMIS; IGAZ; HAMIS; TURE; TURE; TURE; HAMIS; HAMIS; IGAZ; HAMIS; IGAZ}
Ne feledje, hogy a képletünk első tömbjének kimenete figyelmen kívül hagyja, hogy a sor látható -e vagy sem, és ebben segít a második tömbünk.
A láthatósági tömb
A SUBTOTAL használatával kizárhatjuk a nem látható sorokat a tartományunkból, és létrehozhatjuk láthatósági tömbünket. Azonban a SUBTOTAL önmagában egyetlen értéket ad vissza, míg a SUMPRODUCT egy tömb értéket vár. Ennek kiküszöbölésére az OFFSET segítségével egyenként egy sort haladunk át. Ez a technika megköveteli az OFFSET tömb etetését, amely egy számot tartalmaz egyszerre. A második tömb így néz ki:
= ÖSSZES (3, OFFSET (, SOR ()-MIN (SOR ()), 0))
= ÖSSZES (3, OFFSET (D2, SOR (D2: D14) -MIN (SOR (D2: D14)), 0))
Kimenet:
{1;1;0;0;1;1}
A kettő összefűzése:
= SUMPRODUCT ({IGAZ; IGAZ; HAMIS; HAMIS; IGAZ; IGAZ} * {1; 1; 0; 0; 1; 1})
= 4
RÉSZSZÁM HA több kritériummal
Több feltétel hozzáadásához egyszerűen adjon meg több feltételt a SUMPRODUCT -on belül, így:
= SUMPRODUCT ((=)*(=)*(RÉSZSZÁM (3, OFFSET (, SOR ()-MIN (SOR ()), 0)))))
= SUMPRODUCT ((E2: E14 = "Attended")*(B2: B14 = 2019)*(RÉSZSZÁM (3, OFFSET (E2, SOR (E2: E14) -MIN (SOR (E2: E14)), 0)) ))
RÉSZSZÁM HA a Google Táblázatokban
A SUBTOTAL IF funkció pontosan ugyanúgy működik a Google Táblázatokban, mint az Excelben:
