Aantal ontbrekende waarden tellen
Stel dat je twee lijsten hebt, om het totale aantal waarden in de ene lijst te tellen die niet bestaan in een andere lijst, kun je een SOMPRODUCT -formule gebruiken met behulp van de VERGELIJKEN - en ISNA-functies, of de AANTALLEN.ALS -functie.
Ontbrekende waarden tellen met SOMPRODUCT, VERGELIJKEN en ISNA
Ontbrekende waarden tellen met SOMPRODUCT en AANTALLEN.ALS
Ontbrekende waarden tellen met SOMPRODUCT, VERGELIJKEN en ISNA
Om het totale aantal waarden in lijst B te tellen die ontbreken in lijst A zoals hierboven weergegeven, kun je eerst de functie VERGELIJKEN gebruiken om een array te retourneren van de relatieve positie van de waarden uit lijst B in lijst A. Als een waarde niet bestaat in lijst A, wordt een #N/B fout geretourneerd. De ISNA-functie zal vervolgens de #N/B fouten identificeren en SOMPRODUCT telt het totale aantal fouten.
Algemene syntaxis
=SOMPRODUCT(--ISNA(VERGELIJKEN(bereik_te_tellen,zoekbereik,0)))
- bereik_te_tellen: Het bereik waaruit ontbrekende waarden moeten worden geteld. Hier verwijst dit naar lijst B.
- zoekbereik: Het bereik om te vergelijken met het bereik_te_tellen. Hier verwijst dit naar lijst A.
- 0: Het match_type 0 dwingt VERGELIJKEN om een exacte overeenkomst uit te voeren.
Om het totale aantal waarden in lijst B te tellen die ontbreken in lijst A, kopieer of voer de onderstaande formule in cel H6 in en druk op Enter om het resultaat te krijgen:
=SOMPRODUCT(--ISNA(VERGELIJKEN(F6:F8,B6:B10,0)))
Uitleg van de formule
=SOMPRODUCT(--ISNA(VERGELIJKEN(F6:F8,B6:B10,0)))
- VERGELIJKEN(F6:F8,B6:B10,0): Het match_type 0 dwingt de functie VERGELIJKEN om numerieke waarden te retourneren die de relatieve posities aangeven van de waarden door de cellen F6 tot F8 in het bereik B6:B10. Als een waarde niet bestaat in lijst A, wordt een #N/B fout geretourneerd. Dus, de resultaten zullen zijn in een array zoals deze: {2;3;#N/B}.
- ISNA(ISNA(VERGELIJKEN(F6:F8,B6:B10,0))) = ISNA(ISNA({2;3;#N/B})): ISNA werkt om te achterhalen of een waarde een “#N/B” fout is of niet. Zo ja, dan retourneert de functie een WAAR; zo niet, dan retourneert deze een ONWAAR. Dus, de ISNA-formule zal {ONWAAR;ONWAAR;WAAR} retourneren.
- SOMPRODUCT(--ISNA(VERGELIJKEN(F6:F8,B6:B10,0))) = SOMPRODUCT(--{ONWAAR;ONWAAR;WAAR}): Het dubbele negatieve teken converteert WAARs naar 1-en en ONWAARs naar 0-en: {0;1;0}. Vervolgens retourneert de SOMPRODUCT-functie de som: 1.
Ontbrekende waarden tellen met SOMPRODUCT en AANTALLEN.ALS
Om het totale aantal waarden in lijst B te tellen die ontbreken in lijst A, kun je ook de AANTALLEN.ALS-functie gebruiken om te bepalen of een waarde bestaat in lijst A of niet met de voorwaarde “=0”, omdat er een 0 wordt gegenereerd als een waarde ontbreekt. De SOMPRODUCT zal vervolgens het totale aantal ontbrekende waarden tellen.
Algemene syntaxis
=SOMPRODUCT(--(AANTALLEN.ALS(zoekbereik,bereik_te_tellen)=0))
- zoekbereik: Het bereik om te vergelijken met het bereik_te_tellen. Hier verwijst dit naar lijst A.
- bereik_te_tellen: Het bereik waaruit ontbrekende waarden moeten worden geteld. Hier verwijst dit naar lijst B.
- 0: Het match_type 0 dwingt VERGELIJKEN om een exacte overeenkomst uit te voeren.
Om het totale aantal waarden in lijst B te tellen die ontbreken in lijst A, kopieer of voer de onderstaande formule in cel H6 in en druk op Enter om het resultaat te krijgen:
=SOMPRODUCT(--(AANTALLEN.ALS(B6:B10,F6:F8)=0))
Uitleg van de formule
=SOMPRODUCT(--(AANTALLEN.ALS(B6:B10,F6:F8)=0))
- AANTALLEN.ALS(B6:B10,F6:F8): De functie AANTALLEN.ALS telt het aantal keren dat de cellen door F6 tot F8 voorkomen in het bereik B6:B10. Het resultaat zal zijn in een array zoals deze: {1;1;0}.
- --(--(AANTALLEN.ALS(B6:B10,F6:F8)=0)=0) = --(--({1;1;0}=0)=0): Het fragment {1;1;0}=0 levert een WAAR en ONWAAR array {ONWAAR;ONWAAR;WAAR}. Het dubbele negatieve teken verandert dan WAARs in 1-en en ONWAARs in 0-en. De uiteindelijke array is als volgt: {0;0;1}.
- SOMPRODUCT(--(AANTALLEN.ALS(B6:B10,F6:F8)=0)) = SOMPRODUCT({0;0;1}): De SOMPRODUCT retourneert dan de som: 1.
Gerelateerde functies
In Excel kan de SOMPRODUCT-functie worden gebruikt om twee of meer kolommen of arrays met elkaar te vermenigvuldigen, en vervolgens de som van de producten te krijgen. In feite is de SOMPRODUCT een nuttige functie die kan helpen bij het tellen of optellen van celwaarden met meerdere criteria, net zoals de AANTALLEN.MEERVOUDIG of SOMMEN.ALS-functie. Dit artikel introduceert de functie syntaxis en enkele voorbeelden voor deze SOMPRODUCT-functie.
De Excel VERGELIJKEN-functie zoekt naar een specifieke waarde in een bereik van cellen en retourneert de relatieve positie van de waarde.
De AANTALLEN.ALS-functie is een statistische functie in Excel die wordt gebruikt om het aantal cellen te tellen dat voldoet aan een criterium. Het ondersteunt logische operatoren (<>, =, >, en <), en jokertekens (? en *) voor gedeeltelijke overeenkomsten.
Gerelateerde formules
Er zijn gevallen waarin je twee lijsten moet vergelijken om te controleren of een waarde van lijst A bestaat in lijst B in Excel. Bijvoorbeeld, je hebt een lijst met producten en je wilt controleren of de producten in je lijst voorkomen in de productlijst die door je leverancier is verstrekt. Om deze taak uit te voeren, hebben we hieronder drie manieren opgesomd, voel je vrij om degene te kiezen die je leuk vindt.
Cellen tellen die gelijk zijn aan
Dit artikel richt zich op de Excel-formules voor het tellen van cellen die precies gelijk zijn aan een tekststring die je specificeert of gedeeltelijk gelijk aan de gegeven tekststring zoals hieronder in de schermafbeeldingen te zien is. Ten eerste legt het de formulesyntaxis en argumenten uit, daarna biedt het voorbeelden om beter begrip te krijgen.
Aantal cellen tellen dat niet tussen twee gegeven getallen ligt
Het aantal cellen tussen twee getallen tellen is een veelvoorkomende taak voor ons in Excel, maar in bepaalde gevallen wil je misschien de cellen tellen die niet tussen twee gegeven getallen liggen. Bijvoorbeeld, ik heb een productenlijst met de verkoopcijfers van maandag tot zondag, nu moet ik het aantal cellen krijgen dat niet tussen de specifieke laagste en hoogste cijfers ligt zoals hieronder in de schermafbeelding te zien is. In dit artikel introduceer ik enkele formules om deze taak in Excel aan te pakken.
De Beste Office-productiviteitstools
Kutools voor Excel - Helpt U Om Uit Te Blinken In De Menigte
Kutools voor Excel Beschikt Over Meer Dan 300 Functies, Waardoor Wat U Nodig Hebt Maar Een Klik Verwijderd Is...
Office Tab - Schakel Tabbladgestuurd Lezen en Bewerken in Microsoft Office (inclusief Excel)
- Eén seconde om te schakelen tussen tientallen open documenten!
- Verminder honderden muisklikken voor u elke dag, zeg vaarwel tegen muisarm.
- Verhoogt uw productiviteit met 50% bij het bekijken en bewerken van meerdere documenten.
- Brengt Efficiënte Tabbladen naar Office (inclusief Excel), Net Als Chrome, Edge en Firefox.