Vind ontbrekende waarden in Excel

Anonim

In dit artikel zullen we leren hoe u ontbrekende waarden uit de tabel kunt opzoeken met behulp van de formule in Excel.

We hebben bijvoorbeeld een lijst met waarden en we hebben bevestiging nodig met betrekking tot de locatie van de waarde in de lijst. Hiervoor zullen we de situatie overwegen en enkele formules voorstellen om hetzelfde te doen. We zullen verschillende functiebewerkingen gebruiken, afhankelijk van het gemak van de table_array.

De volgende functie zal in gebruik zijn. Leer ze dus een beetje kennen voordat u ze gebruikt.

  1. ALS-functie
  2. AANTAL.ALS-functie
  3. ISNA-functie
  4. VERT.ZOEKEN functie
  5. MATCH-functie

We zullen er een formule van maken. Eerst wordt de opzoekwaarde doorzocht in de specifieke kolom van de tabelarray. Dan zullen de overeenkomende waarden ons de bevestiging geven met behulp van de ALS-functie. De ALS-functie retourneert de bevestiging met de waarden "Is there" & "Missing".

Eerste methode: Gebruik van AANTAL.ALS en ALS functie

Gebruik de generieke formule

= ALS (AANTAL.ALS (lijst, celwaarde), "Is er", "Ontbrekend")

Uitleg:

  • AANTAL.ALS-functie houdt het aantal celwaarde in de lijst en retourneert het getal naar de ALS-functie.
  • ALS-functie beschouwt 0 als ONWAAR en elk ander geheel getal anders dan 0 als WAAR.
  • ALS-functie retourneert "Is er" als Waarde als waar en "Ontbrekend" als waarde als Onwaar.

Voorbeeld:

Laten we dit oplossen door de formule in het voorbeeld te gebruiken.

Hier hebben we een tabel en hier moeten we informatie uit halen.

Hier hebben we de bevestiging van de ID-lijst nodig. Dus gebruiken we het genoemde bereik voor de ID-lijst. ID kaart genoemd bereikgebruik voor de C2:C14.

Dus we zullen de formule gebruiken om het totale bedrag te krijgen

= ALS (AANTAL.ALS (ID, G4), "Is er", "Ontbrekend")

Uitleg voor de formule:

  1. De functie AANTAL.ALS houdt de telling van ID 900 in de lijst en retourneert de telling naar de functie ALS.
  2. ALS-functie beschouwt 0 als ONWAAR en elk ander geheel getal anders dan 0 als WAAR
  3. ALS-functie retourneert "Is er" als Waarde als waar en "Ontbrekend" als waarde als Onwaar.

Hier worden de argumentarrays voor de functie gegeven als celverwijzing.

Zoals u kunt zien, retourneert de formule de waarden voor het ID-nummer 807 & 953. Maar retourneert Ontbrekend voor het ID-nummer 900.

Tweede methode: Gebruik van ISNA & VERT.ZOEKEN functie.

Syntaxis van formule:

= IF ( ISNA ( VLOOKUP ( cell_value , list , 1 , 0 )), "Ontbrekend", "Is There")

Uitleg voor de formule:

  1. De functie VERT.ZOEKEN zoekt de celwaarde op in de eerste kolom van de lijst table_array. De functie retourneert de waarde als deze wordt gevonden, of retourneert de fout #N/B.
  2. De ISNA-functie vangt de #N/B-fout op en retourneert TRUE als #N/B-fout bestaat, of retourneert FALSE.
  3. ALS-functie retourneert "Is er" als Waarde als ONWAAR en "Ontbrekend" als waarde als WAAR.

Zoals je kunt zien op de bovenstaande momentopname. De formule retourneert de "Is There" voor de overeenkomende ID 807 & 953. Maar retourneert "Ontbrekend" voor de niet-overeenkomende ID 900.

derde methode: Gebruik van ISNA & MATCH-functie.

Syntaxis van formule:

= IF ( ISNA ( MATCH ( cell_value , list , 0 )), "Ontbrekend", "Is There")

Uitleg voor de formule:

  1. MATCH-functie zoekt de celwaarde op in de lijst table_array. De functie retourneert de waarde als deze wordt gevonden, of retourneert de fout #N/B.
  2. De ISNA-functie vangt de #N/B-fout op en retourneert TRUE als #N/B-fout bestaat, of retourneert FALSE.
  3. ALS-functie retourneert "Is er" als Waarde als ONWAAR en "Ontbrekend" als waarde als WAAR.

Zoals je kunt zien op de bovenstaande momentopname. De formule retourneert de "Is There" voor de overeenkomende ID 807 & 953. Maar retourneert "Ontbrekend" voor de niet-overeenkomende ID 900.

Het bovenstaande verklaarde 3 voorbeelden om de ontbrekende waarden in de lijst in Excel te vinden. Alle drie de formules werken prima, maar er zijn enkele punten om naar te zoeken.

Opmerkingen:

  1. De functie VERT.ZOEKEN kijkt niet naar links in de table_array.
  2. De AANTAL.ALS-functie ondersteunt jokertekens ( * , ? ) wat helpt bij het extraheren van waarden met zinnen.
  3. Niet-numerieke waarden moeten tussen dubbele aanhalingstekens staan ​​("waarde") of gebruik cell_reference…
  4. Bekijk de hele lijst terwijl de functie waarden retourneert waar deze overeenkomen.
  5. Het array-argument voor de functie kan worden gegeven als celverwijzing of benoemde bereiken.
  6. U kunt deze formules naar wens aanpassen met behulp van een andere Excel-functie.
  7. De functie retourneert de som van de waarden die aan alle voorwaarden voldoen.

Ik hoop dat je hebt begrepen hoe je ontbrekende waarden in Excel kunt vinden. Bekijk hier meer artikelen over Excel-functieformules. Aarzel niet om uw vraag of feedback op het bovenstaande artikel te vermelden. Wij zullen u helpen.

INDEX-MATCH in Excel

VERT.ZOEKEN Meerdere waarden

VERT.ZOEKEN met Dynamic Col Index

Gedeeltelijke overeenkomst met de functie VERT.ZOEKEN

Vlookup op datum in Excel

17 dingen over Excel VERT.ZOEKEN

populaire artikels

50 Excel-snelkoppeling om uw productiviteit te verhogen

Een vervolgkeuzelijst bewerken

Absolute referentie in Excel

Indien met voorwaardelijke opmaak

Indien met jokertekens

Opzoeken op datum