Vergelijk twee kolommen voor overeenkomsten en verschillen in Excel
In deze tutorial wordt uitgelegd hoe je twee kolommen in Excel kunt vergelijken, wat een normale Excel-taak is in ons dagelijkse werk. Het vergelijken van twee kolommen kan in Excel op verschillende manieren worden gedaan, zoals het vergelijken van twee kolommen rij voor rij of cellen per cel, het vergelijken van twee kolommen om overeenkomsten of verschillen te markeren, enz. Hier behandelt dit artikel de meeste mogelijke scenario's van het vergelijken van twee kolommen ontmoeten, en ik hoop dat het je kan helpen.
Opmerking:
In deze zelfstudie worden enkele voorbeeldgegevens gegeven om de methoden voor het vergelijken van twee kolommen in verschillende gevallen beter uit te leggen. Op basis van uw dataset kan het zijn dat u bepaalde inhoud (verwijzingen) moet wijzigen of aanpassen, maar de basisprincipes blijven hetzelfde. Of download direct de voorbeelden van elke casus als u alleen wilt controleren of de methoden werken of niet.
Hieronder staat een dataset waarbij ik in dezelfde rij moet controleren of de namen in kolom A hetzelfde zijn als die in kolom B of niet.
Klik om het voorbeeldbestand te downloaden
1.1 Vergelijk cellen in dezelfde rij voor een exacte overeenkomst
Over het algemeen kunt u de onderstaande formule gebruiken als u twee kolommen rij voor rij wilt vergelijken voor een exacte overeenkomst:
Media Enter toets en sleep de vulgreep omlaag naar cel D8. Als de formule WAAR retourneert, zijn de waarden van twee kolommen volledig hetzelfde, als de formule FALSE retourneert, zijn ze verschillend.
Als u twee kolommen rij voor rij wilt vergelijken voor niet hoofdlettergevoelig, of als u meer beschrijving wilt krijgen, zoals Match, Mismatch, dan kunt u de ALS-functie gebruiken.
Cellen in dezelfde rij vergelijken om exact overeen te komen
Als u de teksten "Match" en "Mismatch" wilt gebruiken om de vergelijkende resultaten te beschrijven, gebruik dan onderstaande formule:
Media Enter toets om het eerste resultaat te krijgen, en sleep vervolgens de automatische vulgreep naar cel D8.
Cellen in dezelfde rij vergelijken voor niet-hoofdlettergevoelige overeenkomsten
Als u cellen wilt vergelijken voor niet hoofdlettergevoelig, kunt u de onderstaande formule gebruiken:
Media Enter toets om het eerste resultaat te krijgen, en sleep vervolgens de automatische vulgreep naar cel E8.
Opmerking
In bovenstaande formules kunt u de teksten "Match" en "Mismatch" wijzigen naar uw eigen beschrijving.
Als u de overeenkomende of verschillende waarden wilt markeren, kunt u de Conditionele opmaak functie kan u helpen.
1. Selecteer de twee kolommen die worden gebruikt om te vergelijken met (B2: C8, exclusief kolomkoppen) en klik op Home > Conditionele opmaak > Nieuwe regel.
2. In het knallen Nieuwe opmaakregel dialoogvenster, klik om te kiezen Gebruik een formule om te bepalen welke cellen moeten worden opgemaakt functie in het Selecteer een regeltype sectie en typ vervolgens = $ B2 = $ C2 in het tekstvak van Formatteer waarden waar deze formule waar is.
3. Klik nu Formaat om de Cellen opmaken dialoog, dan onder Vulling Kies een kleur die je nodig hebt om de overeenkomsten te markeren.
Of u kunt de lettergrootte, lettergrootte, celranden of getalnotatie wijzigen om de overeenkomsten te overtreffen zoals u nodig hebt op andere tabbladen.
4. klikken OK > OK om de dialoogvensters te sluiten, worden de cellen in dezelfde rij gemarkeerd als ze hetzelfde zijn.
Als u de niet-overeenkomende waarden wilt markeren, kunt u dit gebruiken in het = $ B2 <> $ C2 in de Formatteer waarden waar deze formule waar is textbox in het Bewerk de opmaakregel dialoogvenster.
Vervolgens worden de verschillen van twee kolommen in dezelfde rij gemarkeerd met een opgegeven kleur.
1.4 Vergelijk twee kolommen rij voor rij en markeer niet-overeenkomende waarden (met behulp van VBA)
Als u twee kolommen rij voor rij wilt vergelijken met VBA-code, voldoet deze zelfstudie aan u.
1. Schakel het werkblad in dat de twee kolommen bevat die worden gebruikt om te vergelijken, druk op Alt + F11 toetsen om het Microsoft Visual Basic voor toepassingen venster.
2. Klik in het popping-dialoogvenster op Invoegen > Module.
3. Kopieer en plak vervolgens de onderstaande macro in het nieuwe modulescript.
VBA: vergelijk twee kolommen rij voor rij en markeer de verschillen
Sub ExtendOffice_HighlightColumnDifferences()
'UpdatebyKutools20201016
Dim xRg As Range
Dim xWs As Worksheet
Dim xFI As Integer
On Error Resume Next
SRg:
Set xRg = Application.InputBox("Select two columns:", "Kutools for Excel", , , , , , 8)
If xRg Is Nothing Then Exit Sub
If xRg.Columns.Count <> 2 Then
MsgBox "Please select two columns"
GoTo SRg
End If
Set xWs = xRg.Worksheet
For xFI = 1 To xRg.Rows.Count
If Not StrComp(xRg.Cells(xFI, 1), xRg.Cells(xFI, 2), vbBinaryCompare) = 0 Then
xWs.Range(xRg.Cells(xFI, 1), xRg.Cells(xFI, 2)).Interior.ColorIndex = 7 'you can change the color index as you need.
End If
Next xFI
End Sub
4. druk op F5 toets om de code uit te voeren, dan verschijnt er een dialoogvenster voor het selecteren van de twee kolommen.
5. klikken OK. Vervolgens zijn de verschillen tussen twee kolommen gemarkeerd met een achtergrondkleur.
Opmerking
U kunt de markeringskleur naar eigen behoefte wijzigen door de kleurindex in de code te wijzigen, de kleurindexreferentie:
In dit deel wordt de dataset weergegeven zoals hieronder, en u wilt alle waarden vinden die zowel in kolom B als in kolom C tegelijk staan, of zoek de waarden alleen in kolom B.
Klik om het voorbeeldbestand te downloaden
Hier kunt u de formule gebruiken die wordt gecombineerd met de ALS en AANTAL.ALS-functie om twee kolommen te vergelijken en de waarden te vinden die in kolom B staan maar niet in kolom C.
Media Enter -toets en sleep de hendel voor automatisch aanvullen naar cel D8.
Opmerking
1. Deze formule vergelijkt twee kolommen zonder hoofdlettergevoelig.
2. U kunt de omschrijving "Nee in C" en "Ja in C" wijzigen in andere.
3. Als u twee volledige kolommen wilt vergelijken, wijzigt u het vaste bereik $ C $ 2: $ C $ 8 in $ C: $ C.
2.2 Vergelijk en markeer dubbele of unieke gegevens (met voorwaardelijke opmaak)
De Conditionele opmaak functie in Excel is krachtig, hier kunt u het gebruiken om twee kolommencellen per cel te vergelijken en vervolgens de verschillen of overeenkomsten te markeren als u dat nodig hebt.
Markeer alle dubbele of unieke waarden in twee kolommen
1. Selecteer twee kolommen waarmee u wilt vergelijken en klik op Home > Conditionele opmaak > Markeer Cells-regels > Duplicate Values.
2. In het knallen Duplicate Values dialoogvenster, kies een markeringsformaat dat u nodig hebt uit de vervolgkeuzelijst met.
3. klikken OK. Vervolgens zijn de duplicaten in twee kolommen gemarkeerd.
Opmerking
Als u de unieke waarden (de verschillen) in twee kolommen wilt markeren, klikt u ook op Home > Conditionele opmaak > Markeer Cells-regels > Duplicate Values om de Duplicate Values dialoogvenster, wijzigt u het duplicaat naar Unique in de linker vervolgkeuzelijst, kies vervolgens een ander formaat uit de waarden met vervolgkeuzelijst, klik op OK.
De unieke waarden worden gemarkeerd.
Zoek en markeer de waarden in kolom B als deze ook in kolom C staat
Als u de waarden in kolom B wilt markeren die ook in kolom C staan, is de Cvoorwaardelijke opmaak functie kan je ook een plezier doen.
1. Selecteer de kolom B2: B8, klik op Home > Conditionele opmaak > Nieuwe regel.
2. In de Nieuwe opmaakregel dialoogvenster, kies Gebruik een formule om te bepalen welke cellen moeten worden opgemaakt van het Selecteer een regeltype sectie en typ vervolgens = AANTAL.ALS ($ C $ 2: $ C $ 8, $ B2)> 0 in het tekstvak van Formatteer waarden waar deze formule waar is.
3. klikken Formaat naar de Cellen opmaken dialoog, onder Vulling kies een kleur om de overeenkomsten te markeren.
U kunt andere formaten gebruiken om de waarden op de tabbladen Lettertype, Getal, Rand te markeren.
4. klikken OK > OK. Vervolgens zijn de waarden in kolom B die ook in kolom C voorkomen, gemarkeerd met een opgegeven kleur.
Als u de waarden wilt markeren die alleen in kolom B maar niet in kolom C staan, herhaalt u bovenstaande stappen, maar wijzigt u de formule in stap 2 in = AANTAL.ALS ($ C $ 2: $ C $ 8, $ B2) = 0 functie in het Nieuwe opmaakregel dialoogvenster.
Kies vervolgens een andere kleur om de waarden op te maken.
Opmerking
Hier vergelijkt de voorwaardelijke opmaak twee kolommen zonder hoofdlettergevoelig.
2.3 Vergelijk en selecteer of markeer dubbele of unieke gegevens (met behulp van een handige tool)
Soms kunt u, na het vergelijken van twee kolommen, andere acties ondernemen op de overeenkomsten of verschillen, zoals selecteren, verwijderen, kopiëren enzovoort. In dit geval een handig hulpmiddel - Selecteer dezelfde en verschillende cellen of Kutools for Excel kan direct de overeenkomsten of het verschil selecteren om de volgende operatie beter uit te voeren, kan ook de waarden direct markeren.
Na gratis installeren Kutools for Excel, doe dan als volgt:
1. klikken Kutools > kies > Selecteer dezelfde en verschillende cellen.
2. Vervolgens in de Selecteer dezelfde en verschillende cellen dialoogvenster, doe dan als volgt:
1) In de Vind waarden en in Think secties, kies twee kolommen waarmee u wilt vergelijken;
2) Kies Elke rij keuze;
3) Kies Dezelfde waarden or Verschillende waarden zoals je nodig hebt;
4) Als u de waarden wilt markeren, kiest u de gewenste kleur.
3. klikken Ok, verschijnt er een dialoogvenster om u aan het aantal gevonden waarden te herinneren, klik op OK om het dialoogvenster te sluiten. En tegelijkertijd zijn de waarden geselecteerd, nu kunt u verwijderen of kopiëren of andere bewerkingen uitvoeren.
Indien u de Vul de rugkleur en Vul de kleur van het lettertype selectievakjes, wordt het resultaat als volgt weergegeven:
Opmerking
Als u wilt vergelijken met hoofdlettergevoelig, vinkt u de Hoofdlettergevoelig optie.
Deze tool ondersteunt het vergelijken van twee kolommen in verschillende werkbladen.
Als u de overeenkomende waarden in een andere kolom wilt weergeven nadat u twee kolommen cel voor cel hebt vergeleken, kan de onderstaande macrocode u hier helpen.
1. Schakel het blad in waarvan u twee kolommen wilt vergelijken, en druk vervolgens op Alt + F11 toetsen om de Microsoft Visual Basic voor toepassingen venster.
2. klikken Invoegen > Module functie in het Microsoft Visual Basic voor toepassingen venster.
3. Kopieer en plak vervolgens onderstaande code in het nieuwe lege modulescript.
VBA: lijst duplicaten in naast kolom na vergelijking van twee kolommen
Sub ExtendOffice_FindMatches()
'UpdatebyKutools20201019
Dim xRg, xRgC1, xRgC2, xRgF1, xRgF2 As Range
Dim xIntSR, xIntER, xIntSC, xIntEC As Integer
On Error Resume Next
SRg:
Set xRgC1 = Application.InputBox("Select first column:", "Kutools for Excel", , , , , , 8)
If xRgC1 Is Nothing Then Exit Sub
If xRgC1.Columns.Count <> 1 Then
MsgBox "Please select single column"
GoTo SRg
End If
SsRg:
Set xRgC2 = Application.InputBox("Select the second column:", "Kutools for Excel", , , , , , 8)
If xRgC2 Is Nothing Then Exit Sub
If xRgC2.Columns.Count <> 1 Then
MsgBox "Please select single column"
GoTo SsRg
End If
Set xWs = xRg.Worksheet
For Each xRgF1 In xRgC1
For Each xRgF2 In xRgC2
If xRgF1.Value = xRgF2.Value Then xRgF2.Offset(0, 1) = xRgF1.Value
Next xRgF2
Next xRgF1
End Sub
4. druk op F5 sleutel om de code uit te voeren, verschijnen er twee dialoogvensters een voor een zodat u twee kolommen afzonderlijk kunt selecteren.
Opmerking:: Selecteer eerst de linkerkolom en selecteer vervolgens de rechterkolom in het tweede dialoogvenster, anders zullen de duplicaten de originele gegevens in de tweede kolom vervangen.
5. klikken OK > OK, dan worden de overeenkomsten automatisch in de rechterkolom van de twee kolommen weergegeven.
Opmerking
De VBA-code vergelijkt twee kolommen met hoofdlettergevoelig.
2.5 Vergelijk twee kolommen en markeer duplicaten (met VBA-code)
Als u twee kolommen cel voor cel wilt vergelijken en vervolgens de overeenkomsten wilt markeren, kunt u onderstaande code proberen.
1. Schakel het blad in waarvan u twee kolommen wilt vergelijken, en druk vervolgens op Alt + F11 toetsen om de Microsoft Visual Basic voor toepassingen venster.
2. klikken Invoegen > Module functie in het Microsoft Visual Basic voor toepassingen venster.
3. Kopieer onderstaande macrocode en plak ze in de nieuwe blanco Module scripts.
VBA: vergelijk twee kolommen en markeer duplicaten
Sub ExtendOffice_CompareTwoRanges()
'UpdatebyKutools20201019
Dim xRg, xRgC1, xRgC2, xRgF1, xRgF2 As Range
SRg:
Set xRgC1 = Application.InputBox("Select the column you want compare according to", "Kutools for Excel", , , , , , 8)
If xRgC1 Is Nothing Then Exit Sub
If xRgC1.Columns.Count <> 1 Then
MsgBox "Please select a single column"
GoTo SRg
End If
SsRg:
Set xRgC2 = Application.InputBox("Select the column you want to highlight duplicates in:", "Kutools for Excel", , , , , , 8)
If xRgC2 Is Nothing Then Exit Sub
If xRgC2.Columns.Count <> 1 Then
MsgBox "Please select a single column"
GoTo SsRg
End If
For Each xRgF1 In xRgC1
For Each xRgF2 In xRgC2
If xRgF1.Value = xRgF2.Value Then
xRgF2.Interior.ColorIndex = 38 '(you can change the color index as you need)
End If
Next
Next
End Sub
4. druk op F5 sleutel om de code uit te voeren. Selecteer in het eerste pop-outvenster de kolom waarvan u dubbele waarden wilt vergelijken.
5. klikken OK. Selecteer in het tweede dialoogvenster de kolom waarin u dubbele waarden wilt markeren.
6. klikken OK. Nu zijn de duplicaten in de tweede kolom gemarkeerd met een achtergrondkleur na vergelijking met de eerste kolom.
Opmerking
1. De code vergelijkt kolommen met hoofdlettergevoelig.
2. U kunt de markeringskleur naar eigen behoefte wijzigen door de kleurindex in de code te wijzigen, de kleurindexreferentie:
Soms wilt u misschien meer dan twee kolommen in dezelfde rij vergelijken, zoals de dataset zoals hieronder afgebeeld. Hier in deze sectie worden verschillende methoden vermeld om meerdere kolommen te vergelijken.
Klik om het voorbeeldbestand te downloaden
3.1 Vind volledige overeenkomsten in alle cellen in dezelfde rij (met de IF-formule)
Om volledige overeenkomsten tussen kolommen in dezelfde rij te vinden, kan de onderstaande IF-formule u helpen.
Als de cellen in dezelfde rij met elkaar overeenkomen, wordt "Volledige overeenkomst" weergegeven of "Niet".
Media Enter toets om het eerste vergelijkingsresultaat te krijgen en sleep vervolgens de automatische vulgreep naar cel E7.
Opmerking
1. De formule vergelijkt kolommen zonder hoofdlettergevoelig.
2. Als u meer of gelijk aan drie kolommen wilt vergelijken, kunt u onderstaande formule gebruiken:
In de formule is 3 het aantal kolommen dat u kunt wijzigen om aan uw behoefte te voldoen.
3.2 Zoek overeenkomend in twee willekeurige cellen in dezelfde rij (met IF-formule)
Soms wilt u weten of twee kolommen in dezelfde rij overeenkomen, u kunt de onderstaande IF-formule gebruiken.
In deze formule moet u elk paar cellen in dezelfde rij vergelijken. "Overeenkomen" geeft aan dat er twee overeenkomende cellen zijn. Als er geen overeenkomende cellen zijn, geeft de formule "Niet" weer, u kunt de teksten naar behoefte wijzigen.
Media Enter -toets en sleep de vulgreep naar cel E7.
Opmerking
1. Deze formule ondersteunt niet hoofdlettergevoelig.
2. Als er veel kolommen nodig zijn om te vergelijken, kan het te lang zijn om een paar cellen in de formule te vergelijken. In dit geval kunt u overwegen om onderstaande formule te gebruiken die de ALS- en AANTAL.ALS-functies combineert.
Als u de rijen wilt markeren waarmee alle cellen met elkaar overeenkomen, kunt u de Conditionele opmaak functie in Excel.
1. Selecteer het bereik dat u gebruikt en klik op Home > Conditionele opmaak > Nieuwe regel.
2. In de Nieuwe opmaakregel dialoogvenster, kies Gebruik een formule om te bepalen welke cellen moeten worden opgemaakt van het Selecteer een regeltype sectie, dan kunt u een van onderstaande formules gebruiken in de Formatteer waarden waar deze formule waar is tekstvak.
Or
Opmerking:: Als het aantal kolommen meer is dan drie, stel dat het 5 is, dan moeten de formules worden gewijzigd in:
Or
3. klikken Formaat om naar het dialoogvenster Cellen opmaken te gaan en kies vervolgens een vulkleur of een andere celopmaak om de rijen te overtreffen.
4. klikken OK > OK, nu worden alleen rijen waarin alle cellen overeenkomen, gemarkeerd.
Opmerking
Bovenstaande formules ondersteunen geen hoofdlettergevoelig.
3.4 Vergelijk meerdere kolommen en markeer rijverschillen
Als u de rijverschillen wilt markeren, wat betekent dat het kolomcellen één voor één vergelijkt en de verschillende cellen zoekt volgens de eerste kolom, kunt u de ingebouwde Excel-functie gebruiken-Ga naar speciaal.
1. Selecteer het bereik waarvoor u rijverschillen wilt markeren en klik op Home > Zoek en selecteer > Ga naar speciaal.
2. In het knallen Ga naar speciaal dialoogvenster, vink aan Rij verschillen optie.
3. klikken OK. Nu zijn de rijverschillen geselecteerd.
4. Houd nu de geselecteerde cellen, klik Home > opvulkleur om een kleur uit het vervolgkeuzemenu te selecteren.
Opmerking
Deze methode vergelijkt cellen zonder hoofdlettergevoelig.
Stel dat er twee kolommen zijn, kolom B is langer en kolom C is korter, zoals hieronder wordt getoond. Hoe kom je, in vergelijking met kolom B, achter de ontbrekende gegevens in kolom C?
Klik om het voorbeeldbestand te downloaden
Als u alleen wilt bepalen welke gegevens ontbreken na het vergelijken van twee kolommen, kunt u een van de onderstaande formules gebruiken:
Or
Media Enter -toets en sleep vervolgens de automatische vulgreep over cel D10. Als de gegevens nu zowel in kolom B als in kolom C staan, retourneert de formule ONWAAR, als de gegevens alleen in kolom B staan maar niet in kolom C staan, retourneert de formule WAAR.
Opmerking
Bovenstaande twee formules vergelijken gegevens zonder hoofdlettergevoelig.
Als u de ontbrekende gegevens onder de kortere kolom wilt weergeven nadat u twee kolommen hebt vergeleken, kan de INDEX-matrixformule u helpen.
In de onderstaande cel van de kortere kolom, veronderstel dat cel C7, typ onderstaande formule:
Media Shift+Ctrl+Enter -toets om de eerste ontbrekende gegevens op te halen en sleep vervolgens de automatische vulhendel omlaag totdat deze de foutwaarde # N / A retourneert.
Vervolgens kunt u de foutwaarde verwijderen en worden alle ontbrekende gegevens onder de kortere kolom weergegeven.
Opmerking
Deze formule vergelijkt cellen zonder hoofdlettergevoelig.
Als u een vervolgoperatie wilt uitvoeren op de ontbrekende gegevens na het vergelijken van twee kolommen, zoals het vermelden van de ontbrekende gegevens in een andere kolom of het aanvullen van de ontbrekende gegevens onder de kortere kolom, kunt u een handig hulpmiddel proberen:Selecteer dezelfde en verschillende cellen of Kutools for Excel.
1. klikken Kutools > kies > Selecteer dezelfde en verschillende cellen.
2. In de Selecteer dezelfde en verschillende cellen dialoogvenster, doe zoals hieronder:
1 in Vind waarden in kies de langere kolom die de volledige lijst bevat.
In Think kies de kortere kolom waarin enkele gegevens ontbreken.
2) Kies Elke rij optie.
3) Kies Verschillende waarden optie.
3. klikken Ok, verschijnt er een dialoogvenster om u aan het aantal ontbrekende gegevens te herinneren, klik op OK om het te sluiten. Vervolgens zijn de ontbrekende gegevens geselecteerd.
Nu kunt u op drukken CTRL + C toetsen om de geselecteerde ontbrekende gegevens te kopiëren en ze te plakken door op te drukken Ctrl + V toetsen onder de kortere kolom of een andere nieuwe kolom als je nodig hebt.
Opmerking
Tik op het Hoofdletterongevoelig optie in het dialoogvenster Zelfde en verschillende cellen selecteren zal twee kolommen vergelijken met hoofdlettergevoelig.
Hier is een macrocode die de ontbrekende gegevens onder de twee kolommen kan vullen.
1. Open het vel dat u gebruikt en druk op Alt + F11 toetsen om de te openen Microsoft Visual Basic voor toepassingen venster.
2. klikken Invoegen > Module om een nieuwe module te maken om onder VBA-code in te plaatsen.
VBA: vergelijk twee kolommen en vul ontbrekende gegevens in
Sub ExtendOffice_PullUniques()
'UpdatebyKutools20201021
Dim xRg, xRgC1, xRgC2, xFRg1, xFRg2 As Range
Dim xIntR, xIntSR, xIntER, xIntSC, xIntEC As Integer
Dim xWs As Worksheet
On Error Resume Next
SRg:
Set xRg = Application.InputBox("Select two columns:", "Kutools for Excel", , , , , , 8)
If xRg Is Nothing Then Exit Sub
If xRg.Columns.Count <> 2 Then
MsgBox "Please select two columns as a range"
GoTo SRg
End If
Set xWs = xRg.Worksheet
xIntSC = xRg.Column
xIntEC = xRg.Columns.Count + xIntSC - 1
xIntSR = xRg.Row
xIntER = xRg.Rows.Count + xIntSR - 1
Set xRg = xRg.Columns
Set xRgC1 = xWs.Range(xWs.Cells(xIntSR, xIntSC), xWs.Cells(xIntER, xIntSC))
Set xRgC2 = xWs.Range(xWs.Cells(xIntSR, xIntEC), xWs.Cells(xIntER, xIntEC))
xIntR = 1
For Each xFRg In xRgC1
If WorksheetFunction.CountIf(xRgC2, xFRg.Value) = 0 Then
xWs.Cells(xIntER, xIntEC).Offset(xIntR) = xFRg
xIntR = xIntR + 1
End If
Next
xIntR = 1
For Each xFRg In xRgC2
If WorksheetFunction.CountIf(xRgC1, xFRg) = 0 Then
xWs.Cells(xIntER, xIntSC).Offset(xIntR) = xFRg
xIntR = xIntR + 1
End If
Next
End Sub
3. Druk vervolgens op F5 toets om de code uit te voeren, verschijnt er een dialoogvenster voor het selecteren van de twee vergelijkende kolommen.
4. klikken OK. Nu zijn de ontbrekende gegevens onder de twee kolommen weergegeven.
Opmerking
De code vergelijkt cellen zonder hoofdlettergevoelig.
Als er twee kolommen met datums zijn, zoals onderstaand screenshot, wilt u misschien vergelijken welke datum later in dezelfde rij is.
Klik om het voorbeeldbestand te downloaden
5.1 Vergelijk twee kolommen indien groter dan of kleiner dan (met formule)
U kunt de eenvoudige formule gebruiken om snel te achterhalen of de datum 1 later is dan datum 2 in elke rij.
Media Enter toets om het eerste vergeleken resultaat te krijgen en sleep vervolgens de automatische vulgreep naar cel C6 om alle resultaten te krijgen.
Opmerking
1. In Excel worden datums opgeslagen als cijferreeksen, het zijn in feite cijfers. Daarom past u de formule toe om datums rechtstreeks te vergelijken.
2. Als u wilt vergelijken of datum 1 eerder is dan datum 2 in elke rij, wijzigt u het symbool > naar < in de formule.
Als u de cellen in kolom Datum 1 wilt markeren als deze groter zijn dan Datum 2, kunt u de Conditionele opmaak functie in Excel.
1. Selecteer de datums in kolom B (Datum1) en klik op Home > Conditionele opmaak > Nieuwe regel.
2. In de Nieuwe opmaakregel dialoogvenster, selecteer Gebruik een formule om te bepalen welke cellen moeten worden opgemaakt functie in het Selecteer een regeltype sectie en typ vervolgens de formule = $ B2> $ C2 in het tekstvak van Formatteer waarden waar deze formule waar is.
Gebruik de formule als u de cellen in kolom B wilt markeren die kleiner zijn dan die in kolom C = $ B2 <$ C2.
3. klikken Formaat om het te openen Cellen opmaken dialoogvenster en kies vervolgens het formaattype dat u nodig hebt.
4. klikken OK > OK. Vervolgens zijn de cellen in kolom Datum1 die groter zijn dan die in kolom Datum2 gemarkeerd.
Er zijn bijvoorbeeld twee tabellen, nu wilt u kolom B en kolom E vergelijken, en vervolgens de relatieve prijzen uit kolom C zoeken en deze in kolom F retourneren.
Klik om het voorbeeldbestand te downloaden
6.1 Haal de exacte matchingsgegevens op (met behulp van een formule of een handig hulpmiddel)
Hier introduceert het enkele handige formules en een hulpmiddel om deze taak op te lossen.
Formule methode
Gebruik in de cel F2 (de cel waarin u de geretourneerde waarde wilt plaatsen) een van de onderstaande formules:
Or
Media Enter key, en de eerste waarde is gevonden. Sleep vervolgens de automatische vulgreep naar cel F6, alle waarden zijn geëxtraheerd.
Opmerking
1. De formules ondersteunen geen hoofdlettergevoelig.
2. Het cijfer 2 in de formule geeft aan dat u de overeenkomende waarden in de tweede kolom van de tabelmatrix vindt.
3. Als de formules de relatieve waarde niet kunnen vinden, wordt foutwaarde # N / B geretourneerd.
Een handige toolmethode
Als je in de war bent met formules, kun je de handige tool proberen - Formule Helper of Kutools for Excel, dat meerdere formules bevat voor het oplossen van de meeste problemen in Excel. Hiermee hoeft u alleen het bereik te selecteren, maar u hoeft niet te onthouden hoe de formules worden gebruikt.
Na gratis installeren Kutools for Excel, doe dan als volgt:
1. Selecteer de cel F2 (de cel waarin u de retourwaarde wilt plaatsen) en klik op Kutools > Formule Helper > Opzoeken en verwijzen > Zoek een waarde in de lijst.
2. In de Formules Helper dialoogvenster, rechts Argumenten ingevoerd sectie, selecteer het tabelmatrixbereik, zoekwaarde en specificeer in welke kolom je de geretourneerde waarde wilt zoeken.
Tip: Vergeet niet om de absolute verwijzing in het Opzoekwaarde vak naar relatief, of u kunt alleen de eerste waarde vinden.
3. klikken Ok, is de eerste waarde gevonden en sleep vervolgens de automatische vulgreep naar cel F6.
6.2 Haal de gedeeltelijke matching-gegevens op (met behulp van de formule)
Als er een klein verschil is tussen de twee vergeleken kolommen, zoals onderstaand screenshot, kunnen de bovenstaande methoden niet werken.
Kies een van de onderstaande formules om deze taak op te lossen:
Or
Media Enter -toets en sleep vervolgens de automatische vulgreep naar de cel F5, alle waarden zijn gevonden.
Opmerking
1. De formules ondersteunen geen hoofdlettergevoelig.
2. Het getal 2 in de formule geeft aan dat u de retourwaarden in de tweede kolom van de tabelmatrix vindt.
3. Als de formules de relatieve waarde niet kunnen vinden, wordt foutwaarde # N / B geretourneerd.
4. * in de formule is een jokerteken dat wordt gebruikt om elk teken of tekenreeksen aan te duiden.
Onderstaande dataset is een voorbeeld voor het vergelijken en tellen van overeenkomsten of verschillen.
Klik om het voorbeeldbestand te downloaden
7.1 Vergelijk twee kolommen en tel overeenkomsten (met behulp van de SUMPRODUCT-formule)
De SUMPRODUCT-formule kan de overeenkomsten snel in twee kolommen tellen.
Media Enter sleutel om het resultaat te krijgen.
Opmerking
De formule telt cellen zonder hoofdlettergevoelig.
7.2 Vergelijk twee kolommen en tel overeenkomsten of verschillen (met behulp van een handige tool)
Als je overeenkomsten of verschillen tussen twee kolommen wilt tellen, is de handige tool - Selecteer dezelfde en verschillende cellen.
Na gratis installeren Kutools for Excel, doe dan als volgt:
1. klikken Kutools > kies > Selecteer dezelfde en verschillende cellen.
2. In de Selecteer Zelfde en verschilcellen dialoogvenster, kiest u de twee kolombereiken in Vind waarden in en Think secties afzonderlijk en kies vervolgens Elke rijen kies Dezelfde waarden or Verschillende waarden optie zoals je nodig hebt.
3. klikken Ok. Er verschijnt een dialoogvenster om u te vertellen hoeveel overeenkomende of verschillende cellen zijn geselecteerd.
Bijpassende cellen
Verschillende cellen
Stel dat hier een lijst met gegevens is in kolom B, en u wilt de cellen tellen die "Apple" of "Candy" bevatten in kolom D, zoals onderstaand screenshot:
Klik om het voorbeeldbestand te downloaden
Om te tellen of een cel een of meer waarden bevat, kunt u een formule met jokertekens gebruiken om dit probleem op te lossen.
Media Shift+Ctrl+Enter toets om de eerste controle te krijgen en sleep vervolgens de hendel voor automatisch aanvullen naar cel F8.
Als de gerelateerde cel nu een of meer waarden in kolom D bevat, geeft het resultaat het getal groter dan 0 weer, als het geen waarde in kolom D bevat, wordt 0 geretourneerd.
Als u het totale aantal cellen wilt tellen dat de waarden in kolom D bevat, gebruikt u de onderstaande formule van cel F8:
Opmerking
1. U kunt de formule ook gebruiken om te tellen als de cel waarden in een andere kolom bevat
Deze formule hoeft alleen maar in te drukken Enter -toets en sleep vervolgens de automatische vulgreep.
2. In de formules is * het jokerteken dat een teken of tekenreeks aangeeft.
Als u de verschillen of overeenkomsten wilt verwijderen nadat u twee kolommen hebt vergeleken, zoals onderstaand screenshot:
Klik om het voorbeeldbestand te downloaden
U kunt de formule gebruiken om eerst het verschil of de overeenkomsten te vinden:
Media Enter -toets en sleep de automatische vulgreep naar de cel D8.
Pas dan de Filteren feature in Excel om de verschillen of overeenkomsten uit te filteren.
Selecteer de formulekolom en klik op Data > Filteren.
Dan verschijnt de filterknop in de kolom C, klik op de filterknop om het drop-down menu uit te vouwen, kies het Verschil or Match zoals je nodig hebt. Dan klikken OK af te maken.
Nu zijn alleen de verschillen of overeenkomsten eruit gefilterd. U kunt ze selecteren en op drukken Verwijder sleutel om ze te verwijderen.
Klik nu op Data > Filteren nogmaals om het filter te wissen.
Verwijder de formulekolom als u deze niet meer nodig heeft.
Er zijn twee kolommen: de ene bevat de oorspronkelijke prijzen en de andere bevat de verkoopprijzen. Dit deel introduceert nu een formule om deze twee kolommen te vergelijken en vervolgens de procentuele verandering tussen de twee kolommen te berekenen.
Klik om het voorbeeldbestand te downloaden
U kunt de onderstaande formule gebruiken om de procentuele verandering tussen de twee prijzen in dezelfde rij te berekenen.
Media Enter -toets om een nummer te krijgen, en sleep vervolgens de automatische vulgreep naar cel D7.
Formatteer vervolgens het resultaat van de formule als percentage. Selecteer de resultaten, klik op Home tabblad en ga naar Telefoon Nummer groep om te klikken Percentage stijl.
De resultaten van de formule zijn opgemaakt als percentages.
Nu weet u hoe u twee kolommen moet vergelijken na het lezen van bovenstaande methoden. In sommige gevallen wilt u misschien twee bereiken vergelijken (twee reeksen met meerdere kolommen). U kunt bovenstaande methoden (de formules of voorwaardelijke opmaak) gebruiken om ze kolom voor kolom te vergelijken, maar hier introduceert u een handig hulpmiddel - Kutools for Excel kan deze klus in verschillende gevallen snel oplossen met formulevrij.
Klik om het voorbeeldbestand te downloaden
11.1 Vergelijk twee bereiken per cel
Hier zijn twee bereiken die nodig zijn om door cellen te worden vergeleken, u kunt de Selecteer dezelfde en verschillende cellen nut van Kutools for Excel om ermee om te gaan.
Na gratis installeren Kutools for Excel, doe dan als volgt:
1. klikken Kutools > kies > Selecteer dezelfde en verschillende cellen.
2. In het knallen Selecteer dezelfde en verschillende cellen dialoogvenster, doe zoals hieronder:
1) In de Vind waarden in Kies het bereik waarvan u de overeenkomsten of verschillen wilt achterhalen na het vergelijken van twee bereiken.
2) In de Think kies het andere bereik dat wordt gebruikt om het bereik te vergelijken.
3 in Gebaseerd op sectie, kies Eencellig.
4) Vervolgens in de Vind kies het type cellen dat u wilt selecteren of markeren.
5) In de Verwerking van resultaten sectie, kunt u de cellen markeren door de achtergrondkleur of de lettertypekleur te vullen. Als u niet hoeft te markeren, vinkt u de selectievakjes niet aan.
3. klikken Ok. Een dialoogvenster verschijnt en herinnert eraan hoeveel cellen / rijen zijn geselecteerd, klik op OK om het te sluiten. Nu zijn de cellen die anders zijn dan die in het andere bereik geselecteerd en gemarkeerd.
Markering van dezelfde waarden
Opmerking
Als u twee bereiken per rij wilt vergelijken, kunt u ook de Selecteer dezelfde en verschillende cellen functie, maar kies in dit geval de Elke rij optie.
11.2 Vergelijk twee bereiken als de gegevens in dezelfde volgorde staan
Als u twee bereiken per rij wilt vergelijken, kan de Vergelijk de functie Cellen of Kutools for Excel kan jou helpen.
Na gratis installeren Kutools for Excel, doe dan als volgt:
Stel dat het bereik F2: H7 een model is, nu wilt u weten of de gegevens in het bereik B2: D7 in de juiste volgorde staan volgens het bereik F2: H7.
1. klikken Kutools > Vergelijk cellen.
2. In de Vergelijk cellen dialoogvenster, ingesteld zoals hieronder:
1) Kies de twee bereiken in de Vind waarden in en Think dozen afzonderlijk.
2) Kies het celtype dat u wilt markeren in het Vind pagina.
3) Kies het markeringstype in het Verwerking van resultaten pagina.
3. klikken Ok. Er verschijnt een dialoogvenster dat eraan herinnert hoeveel cellen zijn geselecteerd, klik op OK om het te sluiten. Nu zijn de cellen die anders zijn dan die in het andere bereik geselecteerd en gemarkeerd.
Mogelijk bent u ook geïnteresseerd in |
|
Vergelijk datums als deze groter zijn dan een andere datum in Excel Vergelijk twee bladen naast elkaar Vlookup om twee lijsten in afzonderlijke werkbladen te vergelijken Zoek, markeer, filter, tel, verwijder duplicaten in Excel |
De beste tools voor kantoorproductiviteit
Kutools for Excel Lost de meeste van uw problemen op en verhoogt uw productiviteit met 80%
- Super Formula-balk (bewerk eenvoudig meerdere regels tekst en formule); Lay-out lezen (gemakkelijk grote aantallen cellen lezen en bewerken); Plakken in gefilterd bereik...
- Voeg cellen / rijen / kolommen samen en het bewaren van gegevens; Gespleten cellen inhoud; Combineer dubbele rijen en som / gemiddelde... Voorkom dubbele cellen; Vergelijk Ranges...
- Selecteer Dupliceren of Uniek Rijen; Selecteer lege rijen (alle cellen zijn leeg); Super zoeken en fuzzy zoeken in veel werkboeken; Willekeurige selectie ...
- Exacte kopie Meerdere cellen zonder de formuleverwijzing te wijzigen; Maak automatisch verwijzingen naar meerdere bladen; Plaats kogels, Selectievakjes en meer ...
- Favoriete formules en snel invoegen, Bereiken, grafieken en afbeeldingen; Versleutel cellen met wachtwoord; Maak een mailinglijst en stuur e-mails ...
- Extraheer tekst, Tekst toevoegen, Verwijderen op positie, Ruimte verwijderen; Paging-subtotalen maken en afdrukken; Converteren tussen celinhoud en opmerkingen...
- Super filter (bewaar en pas filterschema's toe op andere bladen); Geavanceerd sorteren per maand / week / dag, frequentie en meer; Speciaal filter door vet, cursief ...
- Combineer werkmappen en werkbladen; Tabellen samenvoegen op basis van sleutelkolommen; Gegevens splitsen in meerdere bladen; Batch Converteer xls, xlsx en PDF...
- Draaitabel groeperen op weeknummer, dag van de week en meer ... Toon ontgrendelde, vergrendelde cellen door verschillende kleuren; Markeer cellen met formule / naam...
- Schakel bewerken en lezen met tabbladen in Word, Excel, PowerPoint in, Publisher, Access, Visio en Project.
- Open en maak meerdere documenten in nieuwe tabbladen van hetzelfde venster in plaats van in nieuwe vensters.
- Verhoogt uw productiviteit met 50% en vermindert honderden muisklikken voor u elke dag!
