Jak počítat řádky s kritérii pomocí SUMPRODUCT

Anonim

V tomto článku se naučíme, jak počítat řádky s kritérii SUMPRODUCT v Excelu.

Scénář:
Jednoduše řečeno, při práci s daty někdy potřebujeme spočítat buňky, kde 2 rozsahy splňují kritéria. Podmíněné počítání lze provést pomocí funkce COUNTIF, ale není to jediná funkce, která to může udělat. Funkce SUMPRODUCT je univerzální funkce, kterou lze použít k počítání s kritérii, někdy dokonce lepší než funkce countif.

Jak problém vyřešit?
Pro tento článek budeme muset použít funkci SUMPRODUCT. Nyní z těchto funkcí vytvoříme vzorec. Zde dostáváme data ze dvou rozsahů a musíme spočítat hodnoty, kde vzorec splňuje kritéria.

Obecný vzorec:

= SUMPRODUCT ( - (rozsah1 operátor rozsah2))

rozsah 1 : 1. rozsah
rozsah 2 : 2. rozsah
operátor: kritéria, podmínka daná jako operátor mezi 2 rozsahy

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 máme rekord za posledních 7 velkých zápasů fanklubů Premier League.

Máme rekord za posledních 7 zápasů s domácím týmem, proti týmu, domácími a soupeři vstřelili góly.

Nejprve musíme zjistit počet zápasů, které každý tým vyhrál doma.

Nyní použijeme následující vzorec k získání počtu her, které vyhráli doma.
Použijte vzorec:

= SUMPRODUCT ( - (E5: E11> F5: F11))

E5: E11 : 1. rozmezí, domácí vstřelili góly
F5: F11 : 2. dosah, soupeř dával góly
>: kritéria, podmínka dána větší než operátor

Vysvětlení:

  • Podmínkou, která zde musí být splněna, je, že domácí góly musí být větší než góly soupeře.
  • E5: E11 (rozsah1) musí být větší než F5: F11 (rozsah2).
  • -- operátor použitý k převodu TRUE hodnoty na 1 & False hodnoty na 0.
  • Funkce SUMPRODUCT získá součet 1 s ve vráceném poli, což bude počet požadovaných výher.


Zde je rozsah uveden jako referenční buňka. Stisknutím klávesy Enter získáte počet.


Jak vidíte, celkový počet zápasů, kde domácí tým vyhrál a vyhrál, jsou 4 zápasy.

Výše uvedený příklad byl snadný. Aby to bylo zajímavé, spočítáme, kolik zápasů tým vyhrál v celých datech.


Nejprve musíme zjistit počet zápasů, které každý tým vyhrál doma.

Nyní použijeme následující vzorec pro zjištění počtu her, člověče. United vyhráli.
Použijte vzorec:

= SUMPRODUCT ( - (C5: C11 = G5) * (E5: E11> F5: F11) + (D5: D11 = G5) * (E5: E11 <F5: F11))

Vysvětlení:

  • (C5: C11 = G5) * (E5: E11> F5: F11) kontroluje domácí tým jako muž. United a vstřelili více gólů než tým soupeře.
  • (D5: D11 = G5) * (E5: E11 <F5: F11) kontroluje proti týmu jako muž. United a vstřelili více gólů než domácí.
  • Funkce SUMPRODUCT získá součet 1 s ve vráceném poli, což bude počet požadovaných výher Man. Sjednocený.


Zde je rozsah uveden jako referenční buňka. Stisknutím klávesy Enter získáte počet.


Jak vidíte celkový počet zápasů, kde muž. Tým United vyhrál 2 zápasy.

Zde je několik pozorovacích poznámek uvedených níže.

Poznámky:

  1. Vzorec funguje pouze s čísly.
  2. Vzorec funguje pouze v případě, že ve vyhledávací tabulce nejsou žádné duplikáty
  3. Funkce SUMPRODUCT považuje nečíselné hodnoty za 0 s.
  4. Funkce SUMPRODUCT považuje logickou hodnotu TRUE za 1 a False za 0.
  5. Pole argumentů musí mít stejnou délku jako funkce

Doufám, že tento článek o tom, jak vrátit počet, pokud s SUMPRODUCT v aplikaci Excel je vysvětlující. Další články o funkcích SUMPRODUCT najdete zde. Sdílejte prosím svůj dotaz níže v poli pro komentáře. Pomůžeme vám.

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 závislých na jiných hodnotách buněk v Excelu.

COUNTIFS dvě shody kritérií: Počítejte buňky odpovídající 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, které mají více kritérií.

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 zkratek aplikace Excel pro zvýšení produktivity

Upravte rozevírací seznam

Absolutní reference v Excelu

Pokud s podmíněným formátováním

Pokud se zástupnými znaky

Vlookup podle data

Převod palců na stopy a palce v aplikaci Excel 2016

Připojte jména a příjmení v Excelu

Počítejte buňky, které odpovídají A nebo B