Verschillen vinden in Excel - 5 eenvoudige technieken uitgelegd

Wanneer u met grote datasets in Microsoft Excel werkt, is het onvermijdelijk dat u discrepanties in de gegevens tegenkomt. Het identificeren en oplossen van deze inconsistenties is van cruciaal belang voor nauwkeurige rapportage en analyse.

Het goede nieuws? Excel wordt geleverd met een verscheidenheid aan hulpmiddelen en technieken om u te helpen deze problemen op te sporen en op te lossen. In dit artikel leer je over enkele van de meest effectieve methoden. Dit omvat filteren, voorwaardelijke opmaak en het gebruik van geavanceerde formules.

Door deze technieken in uw dagelijkse workflow op te nemen, wordt u efficiënter in het beheren en analyseren van uw gegevens. Dit leidt uiteindelijk tot betere besluitvorming en betere resultaten voor uw projecten.

Inhoudsopgave

• Verschillen in Excel begrijpen
• 5 manieren om verschillen in Excel te vinden
• #1 - Verschillen vinden via handmatige beoordeling in Excel
• #2 - Filter gebruiken in Excel om inconsistente gegevens te detecteren en op te lossen
• 3# – Verschillen markeren met voorwaardelijke opmaak
• #4 - Verschillen vinden met geavanceerde Excel-functies
  • IF- en IS-functies
  • VERT.ZOEKEN, HORIZ.ZOEKEN & XLZOEKEN
  • OVEREENKOMST
• #5 - Excel-invoegtoepassingen gebruiken voor detectie van discrepanties
• Hoe kunt u regels voor gegevensvalidatie opstellen om discrepanties te voorkomen?
• Ons laatste woord

Verschillen in Excel begrijpen

Verschillen in rijen en kolommen kunnen het gevolg zijn van menselijke invoerfouten, dubbele invoer of onregelmatigheden in de gegevensopmaak.

Kijk eens naar de volgende gegevens. U zult merken dat het geslacht van Walter White en Wayne Cena verkeerd is gespeld - Mael in plaats van mannelijk. Het is inderdaad eenvoudig om de fouten in dit scenario op te merken en op te lossen. Maar dat is niet het geval met spreadsheets die duizenden rijen en kolommen bevatten.

Om deze verschillen te vinden en te beheren, kunt u de volgende Excel- functies gebruiken:

• Filters: pas filters toe op specifieke kolommen om unieke waarden te bekijken en snel inconsistente gegevens op te sporen.
• Voorwaardelijke opmaak: gebruik opmaakregels om cellen te markeren die voldoen aan specifieke criteria of niet-overeenkomende gegevens tussen kolommen.
• Geavanceerde Excel-functies: gebruik functies zoals VERGELIJKEN en VERT.ZOEKEN om datasets te vergelijken en onderlinge verschillen te identificeren.

Als u begrijpt hoe u deze functies in Excel kunt gebruiken, kunt u efficiënt verschillen in uw datasets opsporen en corrigeren.

5 manieren om verschillen in Excel te vinden

Er zijn 5 belangrijke manieren waarop we verschillen in Excel graag aanpakken:

1. Handmatige beoordeling
2. Filteren
3. Conditionele opmaak
4. Geavanceerde Excel-functies
5. Excel-invoegtoepassingen

Laten we ze allemaal eens doornemen, zodat u de beste manier kunt vinden die bij u past.

#1 - Verschillen vinden via handmatige beoordeling in Excel

Wanneer u met Excel werkt , kan een handmatige beoordeling een nuttige methode zijn om discrepanties in uw gegevens te identificeren. Dit proces vereist een zorgvuldig onderzoek van de datasets om eventuele inconsistenties of fouten op te sporen.

Hier volgen enkele stappen die u kunt volgen om een ​​handmatige beoordeling uit te voeren:

• Begin met een duidelijk begrip van de datasets waarmee u werkt. Maak uzelf vertrouwd met de rijen, kolommen en opmaak.
• Blader door de gegevens en let goed op de informatie die in elke cel wordt gepresenteerd. U kunt tools zoals celmarkering met vulkleur, tekstopmaak of voorwaardelijke opmaak gebruiken om gegevens leesbaarder te maken.
• Vergelijk de ene rij met de andere of de ene kolom met de andere. Zoek naar niet-overeenkomende informatie, dubbele invoer, enz. die onnauwkeurigheden in uw Excel-sheets kunnen veroorzaken.
• Terwijl u discrepanties identificeert, maakt u een lijst of gebruikt u opmerkingen om ze te documenteren. Later kunnen deze dienen als belangrijke referenties voor het corrigeren van de problemen.

Bij het handmatig beoordelen van gegevens op verschillen in een Excel-spreadsheet, is geduld de sleutel. Dit proces is tijdrovend. Maar het is een goed uitgangspunt om mogelijke fouten te identificeren.

#2 - Filter gebruiken in Excel om inconsistente gegevens te detecteren en op te lossen

Een van de gemakkelijkste manieren om inconsistente gegevens te detecteren, is door Excel's Filter te gebruiken .

Met Filter isoleren we de onjuiste gegevens in de kolom Geslacht in de volgende dataset:

1. Selecteer Sorteren en filteren op het tabblad Home .

2. Kies Filter - zorg ervoor dat uw cursor zich in het bereik van de gegevens bevindt.

3. Klik op de vervolgkeuzelijstpijl op de kolom met inconsistente gegevens.

4. Deselecteer nu alle juiste overeenkomende gegevens. In ons geval is dat Man . Klik vervolgens op OK om de wijzigingen op te slaan.

5. Uw Excel-werkblad toont nu alleen rijen met onjuiste waarden. Om ze op te lossen, klikt u op Zoeken en selecteren op het tabblad Start .

6. Klik op Vervangen wanneer de vervolgkeuzelijst verschijnt.

7. Typ de onjuiste waarde in Zoeken naar en de juiste in Vervangen door . Klik vervolgens op Alles vervangen .

8. Er verschijnt een dialoogvenster waarin de wijzigingen worden bevestigd. Klik op OK om door te gaan.

3# – Verschillen markeren met voorwaardelijke opmaak

Een andere manier om kolom- en rijverschillen te identificeren, is door voorwaardelijke opmaak te gebruiken.

U hebt toegang tot regels zoals Cellen markeren, Boven/Onder, Gegevensbalken, Kleurenschalen en ijzersets. Bovendien kunt u een nieuwe regel maken en eerdere regels beheren of wissen.

We gebruiken een voorbeeldtabel om u de kracht van voorwaardelijke opmaak te laten zien:

1. Selecteer uw tabel of gewenste rijen en kolommen. We kiezen Kolom A (Bedrijf) en Kolom B (Land). Selecteer vervolgens Voorwaardelijke opmaak op het tabblad Start .

2. Selecteer een regeltype en vervolgens de regel. We kiezen celregels voor markeren en dubbele waarden ...

3. Nu moeten we selecteren of we dubbele of unieke waarden en de vulkleur en het lettertype willen markeren om cellen op te maken. Als u klaar bent, klikt u op OK .

4. Alle dubbele waarden zijn nu gemarkeerd met Lichtrode vulling met donkerrode tekst.

#4 - Verschillen vinden met geavanceerde Excel-functies

IF- en IS-functies

Om cellen te vergelijken en verschillen te identificeren, kunt u IF- en IS-functies gebruiken.

We hebben bijvoorbeeld de formule gebruikt =IF(A2=B2,"MATCH","NOT MATCHED")om cellen A2 en B2 te vergelijken. En we hebben de formule gesleept om deze op andere cellen toe te passen. Identieke cellen geven dus MATCH terug , anders NOT MATCHED .

U kunt IF combineren met IS-functies zoals ISNUMBER of ISTEXT om te controleren op specifieke gegevenstypen. =IF(ISNUMBER(B2), "Number", "NaN")De formule retourneert bijvoorbeeld Getal in kolom c als B2 numeriek is en anders NaN .

VERT.ZOEKEN, HORIZ.ZOEKEN & XLZOEKEN

De functies VERT.ZOEKEN (verticaal) en HORIZ.ZOEKEN (horizontaal) zijn handig bij het vergelijken van waarden in verschillende kolommen of rijen van twee tabellen. Gebruik hiervoor de volgende formule: =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]) of =HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup]).

Vervang lookup_value door de waarde die u in de andere tabel wilt vinden, table_array door het celbereik in de tweede tabel, col_index_num of row_index_num door het indexnummer van de kolom of rij waaruit u een waarde wilt retourneren, en [range_lookup] door ONWAAR voor exacte overeenkomsten of WAAR voor een benadering bij benadering.

In de volgende zelfstudie gebruiken we de functie VERT.ZOEKEN in kolom E om de namen van werknemers in kolom A en kolom D te vergelijken om hun salarissen uit kolom B te halen als er een overeenkomst wordt gevonden.

XLOOKUP is een vernieuwde versie van VLOOKUP en HLOOKUP beschikbaar op Excel 2021 en Excel 365. De XLOOKUP-formule is: =XLOOKUP(lookup_value, lookup_array, return_array,[if_not_found]).

Vervang lookup_value door de waarde die u in de andere tabel wilt vinden, lookup_array door het celbereik om in de tweede tabel te zoeken, return_array door de array waaruit u een waarde wilt retourneren en [if_nout_found] door een tekstwaarde als er geen overeenkomst is waarden worden gevonden.

Hier is een snel voorbeeld van XLOOKUP met dezelfde tabellen:

OVEREENKOMST

De MATCH-functie kan ook worden gebruikt om twee lijsten te vergelijken op verschillen.

De functie VERGELIJKEN, =MATCH(lookup_value, lookup_array, [match_type])zoekt naar een opgegeven item in een bereik en retourneert de relatieve positie van het item binnen dat bereik.

Hier vinden we de positie van Watermeloenen in de array A2:A8 met MATCH. We gebruiken match_type als 0 om de eerste waarde te vinden die exact gelijk is aan de lookup_value .

#5 - Excel-invoegtoepassingen gebruiken voor detectie van discrepanties

Excel biedt verschillende invoegtoepassingen en ingebouwde hulpmiddelen waarmee u afwijkingen efficiënter kunt opsporen en analyseren. In deze sectie zullen we enkele van die handige invoegtoepassingen bespreken.

Overweeg eerst om de invoegtoepassing Spreadsheet Inquire te gebruiken. Dit kan u helpen twee werkmappen te vergelijken en verschillen per cel te markeren. De invoegtoepassing Spreadsheet Inquire inschakelen:

1. Klik op het tabblad Bestand .

2. Selecteer Opties en klik vervolgens op de categorie Invoegtoepassingen .

3. Selecteer COM-invoegtoepassingen in het vak Beheren en klik op Start .

4. Schakel het selectievakje Spreadsheetinformatie in en klik op OK .

5. Eenmaal ingeschakeld, navigeert u naar het tabblad Informeren om de opdracht Bestanden vergelijken te gebruiken .

Een ander handig hulpmiddel voor het opsporen van afwijkingen is het Analysis ToolPak . Deze invoegtoepassing biedt geavanceerde statistische functies en hulpprogramma's voor gegevensanalyse, die handig kunnen zijn bij het analyseren van discrepanties.

Om het Analysis ToolPak in te schakelen, volgt u dezelfde stappen als het inschakelen van de invoegtoepassing Spreadsheet Inquire. Maar selecteer Excel-invoegtoepassingen in het vak Beheren en vink het vakje Analysis ToolPak aan .

Samenvattend biedt Excel verschillende invoegtoepassingen om u te helpen bij het opsporen en afhandelen van verschillen in uw gegevens. U kunt de nauwkeurigheid van uw gegevens verbeteren door vertrouwd te raken met deze tools en formules.

Hoe kunt u regels voor gegevensvalidatie opstellen om discrepanties te voorkomen?

Voer de volgende stappen uit om regels voor gegevensvalidatie in Excel te maken die verschillen kunnen helpen voorkomen:

1. Selecteer de cellen die u wilt beperken. Dit kan een enkele cel zijn, een reeks cellen of een hele kolom.

2. Ga naar het tabblad Gegevens in de werkbalk en klik op de knop Gegevensvalidatie (gemarkeerd door twee horizontale vakjes, een groen vinkje en een rood gekruiste cirkel).

3. Zorg ervoor dat u zich in het venster Gegevensvalidatie op het tabblad Instellingen bevindt . Hier krijgt u de mogelijkheid om uw validatiecriteria te definiëren op basis van uw behoeften.

Er zijn verschillende soorten validatiecriteria beschikbaar in Excel, waaronder enkele:

• Geheel getal – staat alleen gehele getallen binnen een bepaald bereik toe.
• Decimaal – staat alleen decimale getallen binnen een bepaald bereik toe.
• Lijst – beperkt de invoer tot een vooraf gedefinieerde lijst met acceptabele waarden.
• Datum – vereist datums binnen een specifiek datumbereik.
• Tijd - accepteert alleen tijden binnen een bepaald tijdsbereik.
• Tekstlengte – legt beperkingen op aan de lengte van de ingevoerde tekst.
• Aangepast – hiermee kunt u aangepaste validatieregels maken met behulp van Excel-formules.

Nadat u de juiste validatiecriteria hebt geselecteerd, geeft u de gewenste parameters op. Als u bijvoorbeeld een geheel getal kiest, moet u het minimum en maximum instellen met een gegevensbereikvalidator zoals tussen, gelijk aan, kleiner dan, enz.

Naast de validatiecriteria kunt u ook aangepaste foutmeldingen en invoerhints instellen om gebruikers te helpen uw validatieregels te begrijpen en zich eraan te houden. Schakel hiervoor naar de tabbladen Input Message en Error Alert om de gewenste berichten in te voeren.

Door regels voor gegevensvalidatie te implementeren, vermindert u de kans op gegevensinconsistenties aanzienlijk en verbetert u de algehele nauwkeurigheid van uw Excel-werkbladen.

Ons laatste woord

In dit artikel hebt u verschillende methoden geleerd om verschillen in uw Excel-gegevens op te sporen en aan te pakken. Laten we die belangrijke strategieën samenvatten:

• Filtervervolgkeuzemenu 's in kolommen gebruiken om unieke waarden en inconsistente gegevens te identificeren.
• Voorwaardelijke opmaak gebruiken in geselecteerde cellen om verschillen te markeren met behulp van een aantal regelsjablonen.
• Het toepassen van geavanceerde Excel-functies zoals MATCH , VLOOKUP , XLOOKUP , etc. functies om gegevens in twee lijsten of tabellen te controleren.
• Excel-invoegtoepassingen gebruiken om verschillen op te sporen.
• Opstellen van regels voor gegevensvalidatie om fouten in gegevens te voorkomen

Met deze technieken in uw Excel-vaardigheden, zullen uw gegevens nauwkeuriger en betrouwbaarder zijn.

Vergeet niet om deze methoden regelmatig te oefenen om efficiënter te worden in het opsporen van verschillen in uw spreadsheets.

Blijf uw vaardigheden verfijnen door u aan te melden voor onze en blijf nieuwe functies en functionaliteiten ontdekken om nog beter te worden in Excel.