Přejít k hlavnímu obsahu

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

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


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
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations