Počítadla s logikou NEBO pro více kritérií v aplikaci Excel
Normálně můžete použít funkci COUNTIFS k počítání buněk na základě jedné nebo více podmínek s logikou AND v aplikaci Excel. Setkali jste se někdy se situací, kdy potřebujete počítat více než jednu hodnotu z jednoho sloupce nebo řady buněk? To znamená počítat s více podmínkami a logikou NEBO. V takovém případě můžete použít funkce SUMA a COUNTIFS společně nebo použít funkci SUMPRODUCT.
- Počítejte buňky s podmínkami NEBO v Excelu
- Počítejte buňky s více sadami podmínek NEBO v aplikaci Excel
Počítejte buňky s podmínkami NEBO v Excelu
Například mám zobrazený rozsah dat, jak je uvedeno níže, nyní chci spočítat číslo produktu, který je „Pencil“ nebo „Ruler“, zde budu diskutovat o dvou vzorcích pro řešení tohoto úkolu v aplikaci Excel.
Počítejte buňky s podmínkami NEBO pomocí funkcí SUMA a COUNTIFS
Chcete -li v Excelu počítat s více podmínkami NEBO, můžete použít funkce SUMA a COUNTIFS s konstantou pole, obecná syntaxe je:
- range: Rozsah dat obsahuje kritéria, ze kterých počítáte buňky;
- criterion1, criterion2, criterion3…: Podmínky, na základě kterých chcete buňky počítat.
Chcete -li spočítat počet produktů, které jsou „tužka“ nebo „pravítko“, zkopírujte nebo zadejte níže uvedený vzorec do prázdné buňky a poté stiskněte vstoupit klíč k získání výsledku:
Vysvětlení vzorce:
= SUM (COUNTIFS (B2: B13, {"Pencil", "Ruler"})))
- {"Tužka", "Pravítko"}: Nejprve byste měli zabalit všechny podmínky do matice jako: {"Pencil", "Ruler"}, položky oddělte čárkami.
- COUNTIFS (B2: B13, {"Pencil", "Ruler"}): Tato funkce COUNTIFS získá individuální počty pro „tužku“ a „pravítko“ a výsledek získáte takto: {2,3}.
- SUM (COUNTIFS (B2: B13, {"Pencil", "Ruler"})) = SUM ({2,3}): Nakonec tato funkce SUMA sečte všechny položky v poli a vrátí výsledek: 5.
Tipy: Pro kritéria můžete také použít odkazy na buňky, použijte prosím níže uvedený vzorec pole a poté stiskněte Ctrl + Shift + Enter klíče dohromady, abyste získali správný výsledek:
Počítejte buňky s podmínkami NEBO pomocí funkce SUMPRODUCT
Zde je další vzorec, který je vytvořen funkcí SUMPRODUCT a který může pomoci počítat buňky také s logikou NEBO. Obecná syntaxe je:
- range: Rozsah dat obsahuje kritéria, ze kterých počítáte buňky;
- criterion1, criterion2, criterion3…: Podmínky, na základě kterých chcete buňky počítat.
Zkopírujte nebo zadejte následující vzorec do prázdné buňky a stiskněte vstoupit klíč k vrácení výsledku:
Vysvětlení vzorce:
= SUMPRODUCT (1*(B2: B13 = {"Pencil", "Ruler"})))
- B2: B13 = {"Tužka", "Pravítko"}: Tento výraz porovnává každé kritérium „Pencil“, „Ruler“ s buňkou rozsahu B2: B13. Pokud je kritérium splněno, vrátí hodnotu PRAVDA, v opačném případě se zobrazí NEPRAVDA a výsledek získáte takto: {PRAVDA, NEPRAVDA; NEPRAVDA, NEPRAVDA; NEPRAVDA, NEPRAVDA; NEPRAVDA, PRAVDA; NEPRAVDA, NEPRAVDA; PRAVDA, NEPRAVDA ; FALSE, FALSE; FALSE, TRUE; FALSE, FALSE; FALSE, FALSE; FALSE, TRUE; FALSE, FALSE}.
- 1*(B2: B13 = {"Tužka", "Pravítko"}): Násobení převádí logické hodnoty - PRAVDA a NEPRAVDA na 1 a 0, takže výsledek získá takto: {1,0; 0,0; 0,0; 0,1; 0,0; 1,0; 0,0 , 0,1; 0,0; 0,0; 0,1; 0,0; XNUMX}.
- SUMPRODUCT(1*(B2:B13={"Pencil","Ruler"}))= SUMPRODUCT({1,0;0,0;0,0;0,1;0,0;1,0;0,0;0,1;0,0;0,0;0,1;0,0}): Nakonec funkce SUMPRODUCT sečte všechna čísla v poli a získáte výsledek: 5.
Počítejte buňky s více sadami podmínek NEBO v aplikaci Excel
Někdy může být nutné počítat buňky se dvěma nebo více sadami podmínek NEBO, v tomto případě můžete použít buď SUM a COUNTIFS s konstantou pole, nebo SUMPRODUCT s funkcemi ISNUMBER MATCH.
Počítejte buňky se dvěma sadami podmínek NEBO pomocí funkcí SUMA a COUNTIFS
Abyste se vypořádali pouze se dvěma sadami kritérií NEBO, stačí do vzorce COUNTIFS přidat další konstantu pole.
Například mám zobrazený rozsah dat, jak je uvedeno níže, nyní chci spočítat lidi, kteří si objednali „Tužku“ nebo „Pravítko“ a částka je <100 nebo> 200.
Zadejte nebo zkopírujte následující vzorec do buňky a poté stiskněte vstoupit klíč k získání výsledku, viz screenshot:
Poznámka: Ve vzorci byste měli použít středník pro druhou konstantu pole, která vytvoří svislé pole.
Počítejte buňky s více sadami podmínek NEBO pomocí funkce SUMPRODUCT
Výše uvedený vzorec funguje pouze pro dvě sady kritérií NEBO, pokud potřebujete počítat s více kritérii, může vám pomoci komplexní vzorec SUMPRODUCT spolu s funkcemi ISNUMBER MATCH.
Vezměte si například níže uvedená data. Chcete -li spočítat lidi, kteří si objednali „Tužku“ nebo „Pravítko“ a stav je „Doručeno“ nebo „Na cestě“ a podepsáno „Bobem“ nebo „Eko“, měli byste použít složitý vzorec .
Zkopírujte nebo zadejte níže uvedený vzorec do prázdné buňky a stiskněte vstoupit klíč pro výpočet, viz screenshot:
Vysvětlení vzorce:
=SUMPRODUCT(ISNUMBER(MATCH(B2:B13,{"Pencil","Ruler"},0))*ISNUMBER(MATCH(C2:C13,{"Delivered","In transit"},0))*ISNUMBER(MATCH(D2:D13,{"Bob","Eko"},0)))
ISNUMBER (MATCH (B2: B13, {"Pencil", "Ruler"}, 0)):
- MATCH (B2: B13, {"Pencil", "Ruler"}, 0): Tato funkce MATCH slouží k porovnání každé buňky v rozsahu B2: B13 s odpovídající konstantou pole. Pokud je nalezena shoda, vrátí relativní polohu hodnoty v poli, jinak se zobrazí chybová hodnota. Seznam polí tedy získáte takto: {1;#N/A;#N/A; 2;#N/A; 1;#N/A; 2; 1;#N/A; 2;# N/A}.
- ISNUMBER(MATCH(B2:B13,{"Pencil","Ruler"},0))= ISNUMBER({1;#N/A;#N/A;2;#N/A;1;#N/A;2;1;#N/A;2;#N/A}): Funkce ISNUMBER převádí čísla na TRUE a chybové hodnoty na FALSE takto: {TRUE; FALSE; FALSE; TRUE; FALSE; TRUE; FALSE; TRUE; TRUE; FALSE; TRUE; FALSE}.
Výše uvedenou logiku lze také použít na druhý a třetí výraz ISNUMBER.
SUMPRODUCT(ISNUMBER(MATCH(B2:B13,{"Pencil","Ruler"},0))*ISNUMBER(MATCH(C2:C13,{"Delivered","In transit"},0))*ISNUMBER(MATCH(D2:D13,{"Bob","Eko"},0))):
- Poté se tato tři pole společně vynásobí v SUMPRODUCT, který automaticky převede hodnoty TRUE a FALSE na 1 s a 0 s v rámci matematické operace takto: SUMPRODUCT ({1; 0; 0; 1; 0; 1; 0; 1; 1; 0; 1; 0} *{1; 1; 0; 0; 1; 1; 0; 1; 1; 0; 1; 1} *{1; 0; 0; 0; 0; 0; 0; 1; 0; 0; 1; 0}) = SUMPRODUKT ({1; 0; 0; 0; 0; 0; 0; 1; 0; 0; 1; 0}).
- Nakonec funkce SUMPRODUCT sečte všechna čísla v poli, aby se získal výsledek: 3.
Použitá relativní funkce:
- SOUČET:
- Funkce Excel SUMA vrací součet dodaných hodnot.
- COUNTIF:
- Funkce COUNTIF je statistická funkce v aplikaci Excel, která se používá k počítání počtu buněk, které splňují kritérium.
- SUMPRODUKT:
- Funkci SUMPRODUCT lze použít k vynásobení dvou nebo více sloupců nebo polí dohromady a následnému získání součtu produktů.
- ČÍSLO:
- Funkce Excel ISNUMBER vrací TRUE, pokud buňka obsahuje číslo, a FALSE, pokud ne.
- ZÁPAS:
- Funkce Microsoft Excel MATCH hledá konkrétní hodnotu v rozsahu buněk a vrátí relativní pozici této hodnoty.
Další články:
- Počítejte jedinečné číselné hodnoty na základě kritérií
- V listu aplikace Excel můžete trpět problémem, který spočítá počet jedinečných číselných hodnot na základě konkrétní podmínky. Jak například mohu z přehledu spočítat jedinečné hodnoty Množství produktu „Tričko“, jak je znázorněno na obrázku níže? V tomto článku ukážu některé vzorce k dosažení tohoto úkolu v aplikaci Excel.
- Počítejte buňky, které splňují dvě nebo více kritérií
- Chcete -li v aplikaci Excel spočítat počet buněk na základě dvou nebo více kritérií, funkce COUNTIFS vám může pomoci vyřešit tento úkol rychle a snadno.
- Spočítat počet řádků s více NEBO kritérii
- Chcete -li spočítat počet řádků s více kritérii v různých sloupcích, s logikou NEBO, může vám pomoci funkce SUMPRODUCT. Mám například zobrazenou zprávu o produktu, jak je uvedeno níže, nyní chci spočítat řádky, kde je produkt „tričko“ nebo barva „černá“. Jak se s tímto úkolem vypořádat v Excelu?
Nejlepší kancelářské nástroje produktivity
Kutools pro Excel - pomůže vám vyniknout před davem
Kutools pro Excel se může pochlubit více než 300 funkcemi, Zajištění toho, že to, co potřebujete, je jen jedno kliknutí...
Záložka Office - Povolte čtení a úpravy na záložkách v Microsoft Office (včetně Excelu)
- Jednu sekundu přepnete mezi desítkami otevřených dokumentů!
- Snižte stovky kliknutí myší každý den, sbohem s myší rukou.
- Zvyšuje vaši produktivitu o 50% při prohlížení a úpravách více dokumentů.
- Přináší efektivní karty do Office (včetně Excelu), stejně jako Chrome, Edge a Firefox.
