V tomto článku se naučíme počítat více rozsahů s jedním kritériem v aplikaci Microsoft Excel.
Scénář:
Jednoduše řečeno, při práci s datovými tabulkami někdy musíme počítat buňky, kde kritéria splňují více než dva rozsahy. V aplikaci Excel můžete provádět úkoly, jako jsou operace ve více rozsazích, pomocí níže uvedeného vzorce. Kritéria lze použít na text, číslo nebo jakoukoli částečnou shodu v aplikaci Excel. Kritéria uvnitř vzorce se provádějí pomocí operátorů. Operátoři mají rádi ( = ), menší než rovno ( <= ), větší než ( > ) nebo se nerovná ().
Jak problém vyřešit?
Pro tento problém budeme muset použít funkci SUMPRODUCT. Nyní z funkce vytvoříme vzorec. Zde jsou uvedeny dva rozsahy dat a musíme spočítat řádky, které splňují 3 kritéria. Funkce SUMPRODUCT vrací SUM odpovídajících hodnot TRUE (jako 1) a ignoruje hodnoty odpovídající FALSE hodnotám (jako 0) ve vráceném poli
Obecný 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, který hledat
krit: použitelná kritéria
op: operátor kritérií, podmínka daná jako operátor mezi rozsahem a kritérii
+0: převádí logické hodnoty na binární (0 a 1).
Příklad:
Pochopit to všechno může být matoucí. Pojďme tedy tento vzorec otestovat spuštěním na níže uvedeném příkladu. Zde musíme najít počet řádků uvedených v rozsahu se 3 podmínkami. Zde máme seznam diplomatických setkání pořádaných mezi Indií a USA od roku 2014. Tabulka ukazuje prezidenta / PM s označením země a rokem. Tabulka je také rozdělena na části představující domovskou zemi a seznam hostující země.
Níže uvedené podmínky:
Americký prezident „Barack Obama navštívil Indii s méně než 2 problémy.
Použijte vzorec:
| = SUMPRODUCT ((C4: C10 = "Barack Obama") + 0, (F4: F10 = "Indie") + 0, (G4: G10 <2) + 0)) |
C4: C10 = „Barack Obama“: Prezident odpovídající „Baracku Obamovi“ v seznamu návštěv.
F4: F10 = "Indie": hostitelská země odpovídající "Indii".
G4: G10 <2: problémy méně než dvě.
+0: převádí logické hodnoty na binární (0 a 1).
Zde je rozsah uveden jako referenční buňka. Stisknutím klávesy Enter získáte počet.
Jak vidíte, americký prezident Barack Obama navštívil Indii pouze jednou, což se stalo v roce 2015. To ukazuje, že vzorec extrahuje počet shodných časů v odpovídajícím poli. Protože tam je také jeden a tentýž čas, kdy americký prezident „Barack Obama“ navštívil Indii, kde se také problémy rovnají 1, což je méně než 2.
S rovná se kritériím:
Výše uvedený příklad byl snadný. Aby to bylo zajímavé, budeme podle údajů počítat, kolikrát USA od roku 2014 hostovaly Indii od roku 2014.
Níže uvedené podmínky:
USA hostovaná Indie, která má problémy, se rovná 2.
Použijte vzorec:
| = SUMPRODUCT ((F4: F10 = "USA") + 0, (D4: D10 = "Indie") + 0, (G4: G10 = 2) + 0) |
F4: F10 = "USA": hostitelská země odpovídající "USA".
D4: D10 = "Indie": hostující země odpovídající "Indii".
G4: G10 = 2: emise se rovná dvěma.
+0: převádí logické hodnoty na binární (0 a 1).
Zde je rozsah uveden jako referenční buňka. Stisknutím klávesy Enter získáte počet.
Jak vidíte, USA dvakrát hostily Indii a problémy se rovnají dvěma. To ukazuje, že vzorec extrahuje počet časů odpovídajících odpovídajícímu poli. Vzhledem k tomu, že USA hostily Indii 5krát, ale problémy byly buď 1 nebo 3, ale zde potřebujeme problémy, které je třeba spojit s 2.
S více než kritérii:
Aby to bylo zajímavé, budeme podle údajů počítat, kolikrát americký prezident „Donald Trump“ hostil indického premiéra počínaje rokem 2014.
Níže uvedené podmínky:
Americký prezident „Donald Trump“ hostil Indii, která má problémy větší než 1.
Použijte vzorec:
| = SUMPRODUCT ((C4: C10 = "Donald Trump") + 0, (F4: F10 = "Indie") + 0, (G4: G10> 1) + 0) |
F4: F10 = "USA": hostitelský prezident odpovídající "Donaldu Trumpovi".
D4: D10 = "Indie": hostující země odpovídající "Indii".
G4: G10 = 2: emise se rovná dvěma.
+0: převádí logické hodnoty na binární (0 a 1).
Zde je rozsah uveden jako referenční buňka. Stisknutím klávesy Enter získáte počet.
Jak vidíte, jednou tam, kde americký prezident „Donald Trump“ hostil Indii a problémy větší než dvě. To ukazuje, že vzorec extrahuje počet časů odpovídajících odpovídajícímu poli. Protože tam byl dvakrát americký prezident „Donald Trump“ hostil Indii, ale problémy byly buď 1 nebo 3, ale zde potřebujeme, aby problémy byly větší než 1, což jsou 3 lži v roce 2019.
S problémy, které nejsou zohledněny v kritériích:
Abychom to snadno a pohodlně pochopili, budeme podle údajů počítat, kolikrát celkem americký prezident navštívil Indii od roku 2014.
Níže uvedené podmínky:
Americký prezident navštívil Indii celkem od roku 2014.
Použijte vzorec:
| = SUMPRODUCT ((F4: F10 = "Indie")+0, (D4: D10 = "USA")+0) |
F4: F10 = "USA": hostitelská země odpovídající "USA".
D4: D10 = "Indie": hostující země odpovídající "Indii".
G4: G10 = 2: emise se rovná dvěma.
+0: převádí logické hodnoty na binární (0 a 1).
Zde je rozsah uveden jako referenční buňka. Stisknutím klávesy Enter získáte počet.
Jak vidíte, 2krát tam, kde USA navštívily Indii, a problémy větší než dva. To ukazuje, že vzorec extrahuje počet časů odpovídajících odpovídajícímu poli. Bylo to jednou, když americký prezident „Barack Obama“ navštívil Indii v roce 2015, a jednou, když americký prezident „Donald Trump“ navštívil Indii v roce 2020.
Jako kritéria můžete také provádět rozsahy. Spočítejte buňky, kde 2 rozsahy splňují kritéria. Další informace o Countifu se SUMPRODUCT v Excelu najdete zde.
Zde je několik pozorovacích poznámek uvedených níže.
Poznámky:
- Vzorec funguje pouze s čísly.
- Pole ve vzorci musí mít stejnou délku, protože vzorec vrací chybu, když ne.
- Funkce SUMPRODUCT považuje nečíselné hodnoty za 0 s.
- Funkce SUMPRODUCT považuje logickou hodnotu TRUE za 1 a False za 0.
- Pole argumentů musí mít stejnou velikost, jinak funkce vrátí chybu.
- Funkce SUMPRODUCT vrací součet po převzetí jednotlivých produktů v odpovídajícím poli.
Doufám, že tento článek o počítání více rozsahů s jedním kritériem v aplikaci Microsoft Excel je vysvětlující. Další články o výpočtu hodnot a souvisejících vzorcích Excelu najdete zde. Pokud se vám naše blogy líbily, sdílejte je se svými přáteli na Facebooku. A také nás můžete sledovat na Twitteru a Facebooku. Rádi bychom od vás slyšeli, dejte nám vědět, jak můžeme zlepšit, doplnit nebo inovovat naši práci a zlepšit ji pro vás. Napište nám na e -mail.
Jak používat funkci COUNTIF v aplikaci Excel : Pomocí této úžasné funkce spočítejte hodnoty s podmínkami. K počítání konkrétních hodnot nemusíte data filtrovat. Funkce Countif je nezbytná pro přípravu řídicího panelu.
Jak používat funkci SUMPRODUCT v Excelu: Vrací SUMU po vynásobení hodnot ve více polích v Excelu.
COUNTIFS s rozsahem dynamických kritérií : Počet buněk výběrem kritérií ze seznamu možností v buňce kritérií v aplikaci Excel pomocí nástroje pro ověření dat.
COUNTIFS odpovídá dvěma kritériím : více kritérií odpovídá v různých seznamech v tabulce pomocí funkce COUNTIFS v aplikaci Excel
COUNTIFS S NEBO Pro více kritérií : spárujte dva nebo více jmen ve stejném seznamu pomocí kritérií NEBO použitých v seznamu v aplikaci Excel.
Jak používat Countif ve VBA v Microsoft Excelu : Počet buněk s kritérii pomocí kódu jazyka Visual Basic for Applications v makrech aplikace Excel.
Jak používat zástupné znaky v aplikaci Excel : Počet zástupných frází v textových seznamech pomocí zástupných znaků ( * , ? , ~ ) v excelu
Oblíbené články:
Jak používat funkci IF v aplikaci Excel : Příkaz IF v aplikaci Excel zkontroluje podmínku a vrátí konkrétní hodnotu, pokud je podmínka PRAVDA, nebo vrátí jinou konkrétní hodnotu, pokud NEPRAVDA.
Jak používat funkci VLOOKUP v Excelu : Toto je jedna z nejpoužívanějších a nejoblíbenějších funkcí Excelu, která se používá k vyhledávání hodnot z různých rozsahů a listů.
Jak používat funkci SUMIF v aplikaci Excel : Toto je další základní funkce řídicího panelu. To vám pomůže shrnout hodnoty za konkrétních podmínek.
Jak používat funkci COUNTIF v aplikaci Excel : Pomocí této úžasné funkce spočítejte hodnoty s podmínkami. K počítání konkrétních hodnot nemusíte data filtrovat. Funkce Countif je nezbytná pro přípravu řídicího panelu.