Note: The other languages of the website are Google-translated. Back to English

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čítat chybějící hodnoty 1

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)))

spočítat chybějící hodnoty 2

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))

spočítat chybějící hodnoty 3

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

Funkce Excel SUMPRODUCT

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

Funkce Excel MATCH vyhledá konkrétní hodnotu v rozsahu buněk a vrátí relativní polohu hodnoty.

Funkce Excel COUNTIF

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

Najděte chybějící hodnoty

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

Počet buněk roven

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

Chcete dokončit svou každodenní práci rychle a dokonale? Kutools pro Excel přináší 300 výkonných pokročilých funkcí (kombinovat sešity, součet podle barvy, rozdělit obsah buněk, převést datum atd.) A ušetřit vám 80% času.

  • Navrženo pro 1500 80 pracovních scénářů, pomůže vám vyřešit XNUMX% problémů s Excelem.
  • Omezte každý den tisíce kliknutí na klávesnici a myš a ulehčete svým unaveným očím a rukám.
  • Staňte se odborníkem na Excel za 3 minuty. Už si nemusíte pamatovat žádné bolestivé vzorce a kódy VBA.
  • 30denní neomezená bezplatná zkušební verze. 60denní záruka vrácení peněz. Bezplatný upgrade a podpora po dobu 2 let.
Pás karet aplikace Excel (s nainstalovaným programem Kutools pro Excel)

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, Firefox a Nový Internet Explorer.
Snímek obrazovky aplikace Excel (s nainstalovanou kartou Office)
Komentáře (0)
Zatím žádné hodnocení. Buďte první, kdo ohodnotí!
Zatím zde nejsou žádné komentáře
Zanechat své připomínky
Odesílání jako host
×
Ohodnoťte tento příspěvek:
0   Postavy
Doporučená umístění