V tomto článku sa naučíme počítať riadky, ak v programe Excel spĺňa viacero kritérií.
Scenár:
Jednoducho povedané, pri práci s tabuľkami údajov niekedy potrebujeme spočítať bunky, kde kritériá spĺňajú viac ako dva rozsahy. To je možné vykonať pomocou nižšie uvedeného vzorca.
Ako problém vyriešiť?
Na tento problém budeme musieť použiť funkciu SUMPRODUCT. Tu sú uvedené dva rozsahy a potrebujeme počet riadkov, ktoré spĺňajú 3 kritériá. Funkcia SUMPRODUCT vracia SUM zodpovedajúcich TRUE hodnôt (ako 1) a ignoruje hodnoty zodpovedajúce FALSE hodnotám (ako 0) v jednom poli, kde boli podmienky TRUE.
Všeobecný vzorec:
| = SUMPRODUCT ((rng_1 op_1 kritika_1) + 0, (rng_2 op_2 kritika_1) + 0, rng_2 op_2 kritika_1) + 0) |
rng: rozsah, ktorý treba hľadať
krit: kritériá, ktoré sa majú použiť
op: operátor kritérií, podmienka daná ako operátor medzi rozsahom a kritériami
+0: prevádza booleovské hodnoty na binárne (0 a 1).
Príklad:
Toto všetko môže byť mätúce na pochopenie. Otestujme teda tento vzorec spustením na príklade uvedenom nižšie.
Tu musíme nájsť počet riadkov uvedených v rozsahu s 3 podmienkami. Tu nájdete zoznam diplomatických stretnutí, ktoré sa konali medzi Indiou a USA od roku 2014. Tabuľka zobrazuje prezidenta / premiéra s označením krajiny a rokom. Tabuľka je tiež rozdelená na časti predstavujúce domovskú krajinu a zoznam hosťujúcich krajín.
Podmienky uvedené nižšie:
Americký prezident „Barack Obama navštívil Indiu s menej ako 2 problémami.
Použite vzorec:
| = SUMPRODUCT ((C4: C10 = "Barack Obama") + 0, (F4: F10 = "India") + 0, (G4: G10 <2) + 0)) |
C4: C10 = „Barack Obama“: Prezident zodpovedajúci výrazu „Barack Obama“ v zozname návštev.
F4: F10 = "India": hostiteľská krajina zodpovedajúca "Indii".
G4: G10 <2: problémy menej ako dve.
+0: prevádza booleovské hodnoty na binárne (0 a 1).
Tu je rozsah uvedený ako referencia bunky. Počítadlo získate stlačením klávesu Enter.
Ako vidíte, americký prezident Barack Obama raz navštívil Indiu, čo sa stalo v roku 2015. To ukazuje, že vzorec extrahuje počet časov zodpovedajúcich zodpovedajúcemu súboru. Pretože je tu 1krát, keď americký prezident „Barack Obama“ navštívil Indiu, kde sa problémy rovnajú aj 1, čo je menej ako 2
S rovná sa kritériám:
Vyššie uvedený príklad bol ľahký. Aby to bolo zaujímavé, podľa údajov spočítame, koľkokrát boli USA hostiteľom Indie od roku 2014.
Podmienky uvedené nižšie:
USA hostená India s problémami sa rovná 2.
Použite vzorec:
| = SUMPRODUCT ((F4: F10 = "USA") + 0, (D4: D10 = "India") + 0, (G4: G10 = 2) + 0) |
F4: F10 = "USA": hostiteľská krajina zodpovedajúca "USA".
D4: D10 = „India“: hosťujúca krajina zodpovedajúca „Indii“.
G4: G10 = 2: emisie sa rovnajú dvom.
+0: prevádza booleovské hodnoty na binárne (0 a 1).
Tu je rozsah uvedený ako referencia bunky. Počítadlo získate stlačením klávesu Enter.
Ako vidíte, v USA sa konala hostiteľská krajina Indie a problémy sa rovnajú dvom. Toto ukazuje, že vzorec extrahuje počet zodpovedajúcich polí v zodpovedajúcom poli. Pretože v USA hostilo Indiu päťkrát, ale problémy boli buď 1 alebo 3, ale tu potrebujeme problémy, ktoré je potrebné zosúladiť s 2.
S kritériami vyššími ako:
Aby to bolo zaujímavé, podľa údajov spočítame, koľkokrát americký prezident „Donald Trump“ hostil indického premiéra od roku 2014.
Podmienky uvedené nižšie:
Americký prezident „Donald Trump“ hostil Indiu, ktorá má problémy viac ako 1.
Použite vzorec:
| = SUMPRODUCT ((C4: C10 = "Donald Trump") + 0, (F4: F10 = "India") + 0, (G4: G10> 1) + 0) |
F4: F10 = "USA": prezident hostiteľa zodpovedajúci "Donaldovi Trumpovi".
D4: D10 = „India“: hosťujúca krajina zodpovedajúca „Indii“.
G4: G10 = 2: emisie sa rovnajú dvom.
+0: prevádza booleovské hodnoty na binárne (0 a 1).
Tu je rozsah uvedený ako referencia bunky. Počítadlo získate stlačením klávesu Enter.
Ako vidíte, jedenkrát, keď americký prezident „Donald Trump“ hostil Indiu, a problémy viac ako dve. To ukazuje, že vzorec extrahuje počet zodpovedajúcich polí v zodpovedajúcom poli. Pretože tam bol dvakrát americký prezident „Donald Trump“ hostiteľom Indie, ale problémy boli buď 1 alebo 3, ale tu potrebujeme, aby boli problémy väčšie ako 1, čo sú 3 lži v roku 2019.
S problémami, ktoré nie sú zahrnuté v kritériách:
Aby to bolo jednoduché a pohodlné na pochopenie, spočítame podľa údajov, koľkokrát celkovo americký prezident navštívil Indiu od roku 2014.
Podmienky uvedené nižšie:
Americký prezident navštívil Indiu od roku 2014.
Použite vzorec:
| = SUMPRODUCT ((F4: F10 = "India")+0, (D4: D10 = "USA")+0) |
F4: F10 = "USA": hostiteľská krajina zodpovedajúca "USA".
D4: D10 = "India": hosťujúca krajina zodpovedajúca "Indii".
G4: G10 = 2: emisie sa rovnajú dvom.
+0: prevádza booleovské hodnoty na binárne (0 a 1).
Tu je rozsah uvedený ako referencia bunky. Počítadlo získate stlačením klávesu Enter.
Ako vidíte, dvakrát USA navštívili Indiu a problémy boli väčšie ako dva. Toto ukazuje, že vzorec extrahuje počet zodpovedajúcich polí v zodpovedajúcom poli. Bol to raz, keď americký prezident „Barack Obama“ navštívil Indiu v roku 2015, a raz, keď americký prezident „Donald Trump“ navštívil Indiu v roku 2020.
Ako kritériá môžete vykonať aj rozsahy. Spočítajte bunky, kde 2 rozsahy spĺňajú kritériá. Tu sa dozviete viac o Countif so SUMPRODUCT v Exceli.
Tu je niekoľko pozorovacích poznámok uvedených nižšie.
Poznámky:
- Vzorec funguje iba s číslami.
- Polia vo vzorci musia mať rovnakú dĺžku, pretože vzorec vráti chybu, ak nie je.
- Funkcia SUMPRODUCT považuje nečíselné hodnoty za 0 s.
- Funkcia SUMPRODUCT považuje logickú hodnotu TRUE za 1 a False za 0.
- Pole argumentov musí mať rovnakú veľkosť, inak funkcia vráti chybu.
- Funkcia SUMPRODUCT vracia súčet po prevzatí jednotlivých produktov v zodpovedajúcom poli.
- Operátori sa radi rovnajú ( = ), menej ako sa rovná ( <= ), väčší než ( > ) alebo sa nerovná () je možné vykonať v rámci použitého vzorca, iba s číslami.
Dúfam, že tento článok o tom, ako počítať riadky, ktoré v programe Excel spĺňajú viacero kritérií, je vysvetľujúci. Tu nájdete ďalšie články o vzorcoch počítania. Ak sa vám páčili naše blogy, zdieľajte ich so svojimi prvými na Facebooku. A tiež nás môžete sledovať na Twitteri a Facebooku. Radi by sme vás počuli, dajte nám vedieť, ako môžeme zlepšiť, doplniť alebo inovovať našu prácu a zlepšiť ju pre vás. Napíšte nám na emailovú stránku
Nájdite posledný riadok údajov s číslami v programe Excel : V rade textových hodnôt nájdite posledné údaje v programe Excel.
Ako používať funkciu SUMPRODUCT v programe Excel: Vráti SUMU po vynásobení hodnôt vo viacerých poliach v programe Excel.
COUNTIFS s rozsahom dynamických kritérií : Počet buniek, ktoré sa odlišujú od ostatných hodnôt buniek v programe Excel.
COUNTIFS zápas dvoch kritérií : Počet buniek zodpovedajúcich dvom rôznym kritériám v zozname v programe Excel.
COUNTIFS s ALEBO pre viac kritérií : Počet buniek s viacerými kritériami sa zhoduje pomocou funkcie OR.
Funkcia COUNTIFS v programe Excel : Počet buniek závislých od iných hodnôt buniek.
Ako používať Countif vo VBA v programe Microsoft Excel : Počítanie buniek pomocou kódu Visual Basic for Applications.
Ako používať zástupné znaky v programe Excel : Spočítajte bunky zodpovedajúce frázam pomocou zástupných znakov v programe Excel
Populárne články
50 Skratka pre Excel, ktorá zvýši vašu produktivitu : Zrýchlite sa vo svojej úlohe. Týchto 50 skratiek vám umožní pracovať ešte rýchlejšie v Exceli.
Ako používať tFunkcia VLOOKUP v programe Excel : Toto je jedna z najpoužívanejších a najobľúbenejších funkcií programu Excel, ktorá sa používa na vyhľadávanie hodnôt z rôznych rozsahov a hárkov.
Ako používať funkciu COUNTIF v programe Excel : Počítajte hodnoty s podmienkami pomocou tejto úžasnej funkcie. Na počítanie konkrétnych hodnôt nie je potrebné filtrovať údaje. Funkcia Countif je potrebná na prípravu palubnej dosky.
Ako používať funkciu SUMIF v programe Excel : Toto je ďalšia zásadná funkcia palubnej dosky. To vám pomôže zhrnúť hodnoty za konkrétnych podmienok.
