Přejít k hlavnímu obsahu

Počítadla s logikou NEBO pro více kritérií v aplikaci Excel

Autor: Xiaoyang Naposledy změněno: 2021-10-25

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

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:

=SUM(COUNTIF(range, {criterion1, criterion2, criterion3, …}))
  • 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:

=SUM(COUNTIFS(B2:B13,{"Pencil","Ruler"}))


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:

=SUM(COUNTIF(B2:B13,D2:D3))


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:

=SUMPRODUCT(1*(range ={criterion1, criterion2, criterion3, …}))
  • 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:

=SUMPRODUCT(1*(B2:B13={"Pencil","Ruler"}))


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:

=SUM(COUNTIFS(B2:B13,{"Pencil","Ruler"},C2:C13,{"<100";">200"}))

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:

=SUMPRODUCT(ISNUMBER(MATCH(B2:B13,{"Pencil","Ruler"},0))*ISNUMBER(MATCH(C2:C13,{"Delivered","In transit"},0))*ISNUMBER(MATCH(D2:D13,{"Bob","Eko"},0)))


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.
  • 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 AI asistent: Revoluční analýza dat založená na: Inteligentní provedení   |  Generovat kód  |  Vytvořte vlastní vzorce  |  Analyzujte data a generujte grafy  |  Vyvolejte funkce Kutools...
Populární funkce: Najít, zvýraznit nebo identifikovat duplikáty  |  Odstranit prázdné řádky  |  Kombinujte sloupce nebo buňky bez ztráty dat  |  Kolo bez vzorce ...
Super VLookup: Více kritérií  |  Vícenásobná hodnota  |  Přes Multi-Sheets  |  Fuzzy vyhledávání...
Adv. Rozbalovací seznam: Snadno rozevírací seznam  |  Závislý rozbalovací seznam  |  Vícenásobný výběr rozevíracího seznamu...
Správce sloupců: Přidejte konkrétní počet sloupců  |  Přesunout sloupce  |  Přepnout stav viditelnosti skrytých sloupců  Porovnejte sloupce s Vyberte stejné a různé buňky ...
Doporučené funkce: Zaměření mřížky  |  Návrhové zobrazení  |  Velký Formula Bar  |  Správce sešitů a listů | Knihovna zdrojů (Automatický text)  |  Výběr data  |  Zkombinujte pracovní listy  |  Šifrovat/dešifrovat buňky  |  Odesílat e-maily podle seznamu  |  Super filtr  |  Speciální filtr (filtr tučné/kurzíva/přeškrtnuté...) ...
Top 15 sad nástrojů12 Text Tools (doplnit text, Odebrat znaky ...)  |  50+ Graf Typ nemovitosti (Ganttův diagram ...)  |  40+ Praktické Vzorce (Vypočítejte věk na základě narozenin ...)  |  19 Vložení Tools (Vložte QR kód, Vložit obrázek z cesty ...)  |  12 Konverze Tools (Čísla na slova, Přepočet měny ...)  |  7 Sloučit a rozdělit Tools (Pokročilé kombinování řádků, Rozdělit buňky Excelu ...)  |  ... a více

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í...

Popis


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.
Comments (0)
No ratings yet. Be the first to rate!
There are no comments posted here yet
Leave your comments
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations