Vind ontbrekende waarden
Er zijn gevallen waarin u twee lijsten moet vergelijken om te controleren of een waarde van lijst A bestaat in lijst B in Excel. U heeft bijvoorbeeld een lijst met producten en u wilt controleren of de producten in uw lijst voorkomen in de productlijst die door uw leverancier is aangeleverd. Om deze taak te volbrengen, hebben we hieronder drie manieren opgesomd, voel je vrij om degene te kiezen die je leuk vindt.
Vind ontbrekende waarden met MATCH, ISNA en IF
Vind ontbrekende waarden met VERT.ZOEKEN, ISNA en IF
Vind ontbrekende waarden met AANTAL.ALS en ALS
Vind ontbrekende waarden met MATCH, ISNA en IF
Er achter komen als alle producten in uw lijst in de lijst van uw leverancier staan zoals in de bovenstaande schermafbeelding te zien is, kunt u eerst de MATCH-functie gebruiken om de positie van een product van uw lijst (waarde van lijst A) in de leverancierslijst (lijst B) op te halen. MATCH retourneert de fout #N/A wanneer een product niet wordt gevonden. Vervolgens kunt u het resultaat naar ISNA sturen om de #N/A-fouten om te zetten in TRUE's, wat betekent dat die producten ontbreken. De ALS-functie retourneert dan het verwachte resultaat.
Algemene syntaxis
=IF(ISNA(MATCH("lookup_value",lookup_range,0)),"Missing","Found")
√ Opmerking: u kunt de "Ontbrekende", "Gevonden" naar elke gewenste waarde wijzigen.
- opzoekwaarde: De waarde MATCH die wordt gebruikt om zijn positie op te halen als deze bestaat in opzoekbereik of #N.v.t. fout als dat niet het geval is. Hier verwijst naar de producten in uw lijst.
- opzoeken_bereik: Het bereik van cellen om te vergelijken met de opzoekwaarde. Hier wordt verwezen naar de productlijst van de leverancier.
Er achter komen als alle producten in uw lijst in de lijst van uw leverancier staan, kopieer of voer de onderstaande formule in de cel H6 in en druk op Enter om het resultaat te krijgen:
=ALS(ISNA(OVEREENKOMST(30002,$ B $ 6: $ B $ 10,0)),"Ontbrekend","Gevonden")
Of gebruik een celverwijzing om de formule dynamisch te maken:
=ALS(ISNA(OVEREENKOMST(G6,$ B $ 6: $ B $ 10,0)),"Ontbrekend","Gevonden")
√ Opmerking: de dollartekens ($) hierboven geven absolute referenties aan, wat betekent dat de opzoekbereik in de formule verandert niet wanneer u de formule naar andere cellen verplaatst of kopieert. Er zijn echter geen dollartekens toegevoegd aan de opzoekwaarde omdat je wilt dat het dynamisch is. Sleep na het invoeren van de formule de vulgreep naar beneden om de formule op de onderstaande cellen toe te passen.
Verklaring van de formule
Hier gebruiken we de onderstaande formule als voorbeeld:
=IF(ISNA(MATCH(G8,$B$6:$B$10,0)),"Missing","Found")
- WEDSTRIJD(G8,$B$6:$B$10,0): Het match_type 0 dwingt de functie VERGELIJKEN om een numerieke waarde te retourneren die de positie van de eerste overeenkomst van . aangeeft 3004, de waarde in de cel G8, in de array $ B $ 6: $ B $ 10. In dit geval kon MATCH de waarde echter niet vinden in de opzoekarray, dus het retourneert de # N / A fout.
- ISNA(WEDSTRIJD(G8,$B$6:$B$10,0)) = ISNA(# N / A): ISNA werkt om erachter te komen of een waarde een "#N/A"-fout is of niet. Zo ja, dan zal de functie TURE teruggeven; Als de waarde allesbehalve een "#N/A" -fout is, wordt FALSE geretourneerd. Dus deze ISNA-formule zal terugkeren TURE.
- ALS(ISNA(WEDSTRIJD(G8,$B$6:$B$10,0)),"Ontbrekend","Gevonden") = IF(TRUE,"Ontbrekend","Gevonden"): De ALS-functie retourneert Missing als de vergelijking gemaakt door ISNA en MATCH TRUE is, anders wordt Found geretourneerd. Dus de formule zal terugkeren Vermist.
Vind ontbrekende waarden met VERT.ZOEKEN, ISNA en IF
Om erachter te komen of alle producten in uw lijst in de lijst van uw leveranciers voorkomen, kunt u de VERGELIJKEN-functie hierboven vervangen door VERT.ZOEKEN, aangezien het hetzelfde werkt als VERGELIJKEN, zodat de fout #N/A wordt geretourneerd als de waarde niet bestaat in een andere lijst, of we zeggen dat hij ontbreekt.
Algemene syntaxis
=IF(ISNA(VLOOKUP("lookup_value",lookup_range,1,FALSE)),"Missing","Found")
√ Opmerking: u kunt de "Ontbrekende", "Gevonden" naar elke gewenste waarde wijzigen.
- opzoekwaarde: De waarde VERT.ZOEKEN die wordt gebruikt om de positie op te halen als deze bestaat in opzoekbereik of #N.v.t. fout als dat niet het geval is. Hier verwijst naar de producten in uw lijst.
- opzoeken_bereik: Het bereik van cellen om te vergelijken met de opzoekwaarde. Hier wordt verwezen naar de productlijst van de leverancier.
Als u wilt weten of alle producten in uw lijst in de lijst van uw leveranciers voorkomen, kopieert u de onderstaande formule of voert u deze in de cel H6 in en drukt u op Enter om het resultaat te krijgen:
=ALS(ISNA(VERT.ZOEKEN(30002,$ B $ 6: $ B $ 10,1,FALSE)),"Ontbrekend","Gevonden")
Of gebruik een celverwijzing om de formule dynamisch te maken:
=ALS(ISNA(VERT.ZOEKEN(G6,$ B $ 6: $ B $ 10,1,FALSE)),"Ontbrekend","Gevonden")
√ Opmerking: de dollartekens ($) hierboven geven absolute referenties aan, wat betekent dat de opzoekbereik in de formule verandert niet wanneer u de formule naar andere cellen verplaatst of kopieert. Er zijn echter geen dollartekens toegevoegd aan de opzoekwaarde omdat je wilt dat het dynamisch is. Sleep na het invoeren van de formule de vulgreep naar beneden om de formule op de onderstaande cellen toe te passen.
Verklaring van de formule
Hier gebruiken we de onderstaande formule als voorbeeld:
=IF(ISNA(VLOOKUP(G8,$B$6:$B$10,1,FALSE)),"Missing","Found")
- VERT.ZOEKEN(G8,$B$6:$B$10,1,FALSE): De range_lookup Juist dwingt de functie VERT.ZOEKEN om de waarde op te zoeken en terug te geven die exact overeenkomt 3004, de waarde in de cel G8. Als de lookup_value 3004 bestaat in de 1st kolom van de array $ B $ 6: $ B $ 10, de VERT.ZOEKEN zal die waarde retourneren; Anders wordt de foutwaarde #N/B geretourneerd. Hier bestaat 3004 niet in de array, dus het resultaat zou zijn: # N / A.
- ISNA(VERT.ZOEKEN(G8,$B$6:$B$10,1,FALSE)) = ISNA(# N / A): ISNA werkt om erachter te komen of een waarde een "#N/A"-fout is of niet. Zo ja, dan zal de functie TURE teruggeven; Als de waarde allesbehalve een "#N/A" -fout is, wordt FALSE geretourneerd. Dus deze ISNA-formule zal terugkeren TURE.
- ALS(ISNA(VERT.ZOEKEN(G8,$B$6:$B$10,1,FALSE)),"Ontbrekend","Gevonden") = IF(TRUE,"Ontbrekend","Gevonden"): De ALS-functie retourneert Ontbrekend als de vergelijking gemaakt door ISNA en VERT.ZOEKEN WAAR is, anders wordt Gevonden geretourneerd. Dus de formule zal terugkeren Vermist.
Vind ontbrekende waarden met AANTAL.ALS en ALS
Om erachter te komen of alle producten in uw lijst in de lijst van uw leveranciers voorkomen, kunt u een eenvoudigere formule gebruiken met de functies AANTAL.ALS en ALS. De formule maakt gebruik van het feit dat Excel elk getal behalve nul (0) als WAAR evalueert. Dus als een waarde in een andere lijst voorkomt, retourneert de AANTAL.ALS-functie het aantal keren dat het voorkomt in die lijst, en ALS neemt het getal als een TURE; Als de waarde niet in de lijst voorkomt, retourneert de AANTAL.ALS-functie 0 en IF neemt het als een ONWAAR.
Algemene syntaxis
=IF(COUNTIF("lookup_range",lookup_value),"Found","Missing")
√ Opmerking: u kunt de "Gevonden", "Ontbrekende" naar elke gewenste waarde wijzigen.
- opzoeken_bereik: Het bereik van cellen om te vergelijken met de opzoekwaarde. Hier wordt verwezen naar de productlijst van de leverancier.
- opzoekwaarde: De waarde AANTAL.ALS die wordt gebruikt om het aantal keren dat het voorkomt in . te retourneren opzoekbereik. Hier verwijst naar de producten in uw lijst.
Als u wilt weten of alle producten in uw lijst in de lijst van uw leveranciers voorkomen, kopieert u de onderstaande formule of voert u deze in de cel H6 in en drukt u op Enter om het resultaat te krijgen:
=ALS(AANTAL.ALS($ B $ 6: $ B $ 10,30002),"Gevonden","Vermist")
Of gebruik een celverwijzing om de formule dynamisch te maken:
=ALS(AANTAL.ALS($ B $ 6: $ B $ 10,G6),"Gevonden","Vermist")
√ Opmerking: de dollartekens ($) hierboven geven absolute referenties aan, wat betekent dat de opzoekbereik in de formule verandert niet wanneer u de formule naar andere cellen verplaatst of kopieert. Er zijn echter geen dollartekens toegevoegd aan de opzoekwaarde omdat je wilt dat het dynamisch is. Sleep na het invoeren van de formule de vulgreep naar beneden om de formule op de onderstaande cellen toe te passen.
Verklaring van de formule
Hier gebruiken we de onderstaande formule als voorbeeld:
=IF(COUNTIF($B$6:$B$10,G8),"Found","Missing")
- AANTAL.ALS($B$6:$B$10,G8): De AANTAL.ALS-functie telt hoe vaak doet 3004, de waarde in de cel G8, verschijnt in de array $ B $ 6: $ B $ 10. Blijkbaar bestaat 3004 niet in de array, dus het resultaat zou zijn: 0.
- ALS(AANTAL.ALS($B$6:$B$10,G8),"Gevonden","Ontbrekend") = IF(0,"Gevonden","Vermist"): De ALS-functie evalueert 0 als ONWAAR. Dus de formule zal terugkeren Vermist, de waarde die moet worden geretourneerd wanneer de eerste augment evalueert naar FALSE.
Gerelateerde functies
De ALS-functie is een van de eenvoudigste en handigste functies in de Excel-werkmap. Het voert een eenvoudige logische test uit die afhankelijk is van het vergelijkingsresultaat, en het retourneert één waarde als een resultaat WAAR is, of een andere waarde als het resultaat FALSE is.
De Excel MATCH-functie zoekt naar een specifieke waarde in een celbereik en retourneert de relatieve positie van de waarde.
De Excel VERT.ZOEKEN-functie zoekt naar een waarde door deze te matchen in de eerste kolom van een tabel en retourneert de corresponderende waarde uit een bepaalde kolom in dezelfde rij.
De AANTAL.ALS-functie is een statistische functie in Excel die wordt gebruikt om het aantal cellen te tellen dat aan een criterium voldoet. Het ondersteunt logische operators (<>, =, > en <) en de jokertekens (? en *) voor gedeeltelijke overeenkomsten.
Verwante formules
Zoek een waarde op die specifieke tekst bevat met jokertekens
Om de eerste overeenkomst te vinden die een bepaalde tekenreeks in een bereik in Excel bevat, kunt u een INDEX- en MATCH-formule gebruiken met jokertekens - de asterisk (*) en het vraagteken (?).
Gedeeltelijke overeenkomst met VERT.ZOEKEN
Er zijn momenten waarop u Excel nodig hebt om gegevens op te halen op basis van gedeeltelijke informatie. Om het probleem op te lossen, kunt u een VERT.ZOEKEN-formule gebruiken samen met jokertekens - de asterisk (*) en het vraagteken (?).
Geschatte overeenkomst met INDEX en MATCH
Er zijn momenten waarop we geschatte overeenkomsten in Excel moeten vinden om de prestaties van werknemers te evalueren, de scores van studenten te beoordelen, de verzendkosten te berekenen op basis van gewicht, enz. In deze tutorial zullen we het hebben over het gebruik van de INDEX- en MATCH-functies om de resultaten die we nodig hebben.
Zoek de meest overeenkomende waarde op met meerdere criteria
In sommige gevallen moet u mogelijk de dichtstbijzijnde of geschatte overeenkomstwaarde opzoeken op basis van meer dan één criterium. Met de combinatie van INDEX-, MATCH- en IF-functies kunt u het snel voor elkaar krijgen in Excel.
De beste tools voor kantoorproductiviteit
Kutools for Excel - Helpt u zich te onderscheiden van de menigte
Kutools voor Excel beschikt over meer dan 300 functies, Ervoor zorgen dat wat u nodig heeft slechts één klik verwijderd is...
Office-tabblad - Schakel lezen en bewerken met tabbladen in Microsoft Office in (inclusief Excel)
- Een seconde om te schakelen tussen tientallen geopende documenten!
- Verminder elke dag honderden muisklikken voor u, zeg maar dag tegen muishand.
- 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.
