Spočítejte chybějící hodnoty
Předpokládejme, že máte dva seznamy a chcete-li spočítat celkový počet hodnot v jednom seznamu, které neexistují v jiném seznamu, můžete použít SUMPRODUCT formule s pomocí MATCH a ISNA funkce, nebo COUNTIF funkce.
Spočítejte chybějící hodnoty pomocí SUMPRODUCT, MATCH a ISNA
Spočítejte chybějící hodnoty pomocí SUMPRODUCT a COUNTIF
Spočítejte chybějící hodnoty pomocí SUMPRODUCT, MATCH a ISNA
Chcete-li počítat celkový počet hodnot v seznamu B, které v seznamu A chybí jak je uvedeno výše, můžete nejprve pomocí funkce MATCH vrátit pole relativní pozice hodnot ze seznamu B v seznamu A. Pokud hodnota v seznamu A neexistuje, bude vrácena chyba #N/A. Funkce ISNA pak identifikuje chyby #N/A a SUMPRODUCT spočítá celkový počet chyb.
Obecná syntaxe
=SUMPRODUCT(--ISNA(MATCH(range_to_count,lookup_range,0)))
- range_to_count: Rozsah, ze kterého se mají počítat chybějící hodnoty. Zde odkazujeme na seznam B.
- rozsah vyhledávání: Rozsah pro srovnání s range_to_count. Zde odkazujeme na seznam A.
- 0: Projekt typ_ shody 0 přinutí MATCH provést přesnou shodu.
Chcete-li počítat celkový počet hodnot v seznamu B, které v seznamu A chybí, zkopírujte nebo zadejte vzorec níže do buňky H6 a stiskněte vstoupit získat výsledek:
=SOUČETOVÝ VÝROBEK(--ISNA(Shoda(F6: F8,B6: B10,0)))
Vysvětlení vzorce
=SUMPRODUCT(--ISNA(MATCH(F6:F8,B6:B10,0)))
- MATCH(F6:F8;B6:B10,0;XNUMX): Projekt typ_ shody 0 vynutí funkci MATCH, aby vrátila číselné hodnoty, které označují relativní pozice hodnot v buňkách F6 na F8 v dosahu B6: B10. Pokud hodnota v seznamu A neexistuje, bude vrácena chyba #N/A. Takže výsledky budou v takovém poli: {2;3;#N/A}.
- ISNA(MATCH(F6:F8;B6:B10,0;XNUMX)) = ISNA({2;3;#N/A}): ISNA pracuje na zjištění, zda je hodnota chybou „#N/A“ nebo ne. Pokud ano, funkce vrátí TURE; Pokud ne, vrátí FALSE. Vzorec ISNA se tedy vrátí {FALSE;FALSE;TRUE}.
- SUMPRODUCT(--ISNA(MATCH(F6:F8;B6:B10,0;XNUMX))) = SOUČETNÍ PRODUKT(--{FALSE;FALSE;TRUE}): Dvojité záporné znaménko převádí TRUEs na 1s a FALSE na 0s: {0; 1; 0}. Potom funkce SUMPRODUCT vrátí součet: 1.
Spočítejte chybějící hodnoty pomocí SUMPRODUCT a COUNTIF
Chcete-li počítat celkový počet hodnot v seznamu B, které v seznamu A chybí, můžete také použít funkci COUNTIF, abyste zjistili, zda hodnota existuje v seznamu A nebo ne s podmínkou „=0“, protože pokud nějaká hodnota chybí, vygeneruje se 0. SUMPRODUCT pak spočítá celkový počet chybějících hodnot.
Obecná syntaxe
=SUMPRODUCT(--(COUNTIF(lookup_range,range_to_count)=0))
- rozsah vyhledávání: Rozsah pro srovnání s range_to_count. Zde odkazujeme na seznam A.
- range_to_count: Rozsah, ze kterého se mají počítat chybějící hodnoty. Zde odkazujeme na seznam B.
- 0: Projekt typ_ shody 0 přinutí MATCH provést přesnou shodu.
Chcete-li počítat celkový počet hodnot v seznamu B, které v seznamu A chybí, zkopírujte nebo zadejte vzorec níže do buňky H6 a stiskněte vstoupit získat výsledek:
=SOUČETOVÝ PRODUKT(--(COUNTIF(B6: B10,F6: F8)=0))
Vysvětlení vzorce
=SUMPRODUCT(--(COUNTIF(B6:B10,F6:F8)=0))
- COUNTIF(B6:B10;F6:F8): Funkce COUNTIF počítá počet výskytů buněk F6 na F8 v dosahu B6: B10. Výsledek bude v poli takto: {1; 1; 0}.
- - (COUNTIF(B6:B10;F6:F8)= 0) = - ({1; 1; 0}= 0): Fragment {1;1;0}=0 poskytuje pole TRUE a FALSE {FALSE;FALSE;TRUE}. Dvojité záporné znaménko pak změní TRUEs na 1s a FALSE na 0s. Konečné pole je takto: {0; 0; 1}.
- SUMPRODUKT(- (COUNTIF(B6:B10;F6:F8)= 0)) = SOUČETNÍ PRODUKT({0; 0; 1}): SUMPRODUCT pak vrátí součet: 1.
Související funkce
V aplikaci Excel lze funkci SUMPRODUCT použít k vynásobení dvou nebo více sloupců nebo polí dohromady a následnému získání součtu produktů. Ve skutečnosti je SUMPRODUCT užitečná funkce, která může pomoci spočítat nebo sčítat hodnoty buněk s více kritérii, jako je funkce COUNTIFS nebo SUMIFS. Tento článek představí syntaxi funkce a několik příkladů pro tuto funkci SUMPRODUCT.
Funkce Excel MATCH vyhledá konkrétní hodnotu v rozsahu buněk a vrátí relativní polohu hodnoty.
Funkce COUNTIF je statistická funkce v Excelu, která se používá k počítání počtu buněk, které splňují kritérium. Podporuje logické operátory (<>, =, > a <) a zástupné znaky (? a *) pro částečnou shodu.
Související vzorce
Existují případy, kdy potřebujete porovnat dva seznamy, abyste zjistili, zda hodnota seznamu A existuje v seznamu B v aplikaci Excel. Máte například seznam produktů a chcete zkontrolovat, zda produkty ve vašem seznamu existují v seznamu produktů poskytnutém vaším dodavatelem. Chcete-li tento úkol splnit, uvádíme níže tři způsoby, můžete si vybrat ten, který se vám líbí.
Tento článek se zaměří na vzorce aplikace Excel pro počítání buněk, které jsou přesně stejné jako textový řetězec, který zadáte, nebo částečné stejné jako daný textový řetězec, jak je uvedeno níže. Nejprve vysvětlí syntaxi a argument vzorce, poskytne příklady pro lepší pochopení.
Spočítejte počet buněk, které nejsou mezi dvěma danými čísly
Počítat počet buněk mezi dvěma čísly je pro nás v Excelu běžný úkol, ale v určitých případech možná budete chtít počítat buňky ne mezi dvěma danými čísly. Například mám seznam produktů s prodejem od pondělí do neděle, nyní potřebuji zjistit počet buněk, které nejsou mezi konkrétními nízkými a vysokými čísly, jak je uvedeno níže. Tento článek představím některé vzorce pro řešení tohoto úkolu v aplikaci Excel.
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.
