V tomto článku se naučíme počítat řádky, pokud to v aplikaci Excel splňuje více kritérií.
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. To lze provést pomocí níže popsaného vzorce.
Jak problém vyřešit?
Pro tento problém budeme muset použít funkci SUMPRODUCT. Zde jsou uvedeny dva rozsahy a potřebujeme počet řádků, které splňují 3 kritéria. Funkce SUMPRODUCT vrací SUM odpovídající odpovídajících hodnot PRAVDA (jako 1) a ignoruje hodnoty odpovídající NEPRAVDIVÝM hodnotám (jako 0) ve vrácení jednoho pole, kde podmínky byly PRAVDA.
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 / premiéra 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 jednou navštívil Indii, 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 1 č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 opakování shodných v odpovídajícím 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 opakování shodných v odpovídajícím 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.
- Operátoři mají rádi ( = ), menší než rovno ( <= ), větší než ( > ) nebo se nerovná () lze provést v rámci použitého vzorce, pouze s čísly.
Doufám, že tento článek o tom, jak počítat řádky, které splňují více kritérií v aplikaci Excel, je vysvětlující. Další články o počítání vzorců 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
Najděte poslední řádek dat s čísly v Excelu : V řadě textových hodnot najděte poslední data v Excelu.
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čítejte buňky, které se liší od ostatních hodnot buněk v aplikaci Excel.
COUNTIFS odpovídá dvěma kritériím : Počet buněk odpovídajících dvěma různým kritériím v seznamu v aplikaci Excel.
COUNTIFS S NEBO Pro více kritérií : Pomocí funkce NEBO spočítejte buňky s více kritérii.
Funkce COUNTIFS v aplikaci Excel : Počet buněk závislých na jiných hodnotách buněk.
Jak používat Countif ve VBA v Microsoft Excelu : Počítejte buňky pomocí kódu Visual Basic for Applications.
Jak používat zástupné znaky v aplikaci Excel : Počítejte buňky odpovídající frázím pomocí zástupných znaků v aplikaci Excel
Populární články
50 Zkratka aplikace Excel pro zvýšení produktivity : Získejte rychleji svůj úkol. Těchto 50 klávesových zkratek vám umožní pracovat v Excelu ještě rychleji.
Jak používat tFunkce 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 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 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.
