V tomto článku sa naučíme počítať viac rozsahov s jedným kritériom v programe Microsoft Excel.
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. V programe Excel môžete vykonávať úlohy, ako sú operácie vo viacerých rozsahoch, podľa nižšie uvedeného vzorca. Kritériá je možné použiť na text, číslo alebo akúkoľvek čiastočnú zhodu v programe Excel. Kritériá vo vzorci sa vykonávajú pomocou operátorov. Operátori sa radi rovnajú ( = ), menej ako sa rovná ( <= ), väčší než ( > ) alebo sa nerovná ().
Ako problém vyriešiť?
Na tento problém budeme musieť použiť funkciu SUMPRODUCT. Teraz z funkcie vytvoríme vzorec. Tu dostávame dva rozsahy údajov a musíme spočítať riadky, 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) vo vrátenom poli.
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 schôdzí, 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 navštívil Indiu iba raz, čo sa stalo v roku 2015. To ukazuje, že vzorec extrahuje počet časov zodpovedajúcich zodpovedajúcemu súboru. Existuje totiž jeden a ten istý čas, 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 kritériá:
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. Toto 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.
Dúfam, že tento článok o počítaní viacerých rozsahov s jedným kritériom v programe Microsoft Excel je vysvetľujúci. Tu nájdete ďalšie články o výpočte hodnôt a súvisiacich vzorcoch programu Excel. Ak sa vám páčili naše blogy, zdieľajte ich so svojimi priateľ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.
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 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í : Spočítajte bunky výberom kritérií zo zoznamu možností v bunke kritérií v programe Excel pomocou nástroja na overenie údajov.
COUNTIFS zápas dvoch kritérií : viac kritérií sa zhoduje v rôznych zoznamoch v tabuľke pomocou funkcie COUNTIFS v programe Excel
COUNTIFS s ALEBO pre viac kritérií : priradenie dvoch alebo viacerých mien v tom istom zozname pomocou kritérií ALEBO použitých v zozname v programe Excel.
Ako používať Countif vo VBA v programe Microsoft Excel : Počet buniek s kritériami pomocou kódu Visual Basic for Applications v makrách programu Excel.
Ako používať zástupné znaky v programe Excel : Spočítajte bunky zodpovedajúce frázam v textových zoznamoch pomocou zástupných znakov ( * , ? , ~ ) v exceli
Populárne články:
Ako používať funkciu IF v programe Excel : Príkaz IF v programe Excel skontroluje podmienku a vráti konkrétnu hodnotu, ak je podmienka PRAVDA, alebo vráti inú konkrétnu hodnotu, ak je NEPRAVDA.
Ako používať funkciu 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 SUMIF v programe Excel : Toto je ďalšia zásadná funkcia palubnej dosky. To vám pomôže zhrnúť hodnoty za konkrétnych podmienok.
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.