Porównaj dwie kolumny pod kątem dopasowań i różnic w programie Excel

W tym samouczku omówiono, jak porównać dwie kolumny w programie Excel, co jest normalną pracą programu Excel w naszej codziennej pracy. Porównywanie dwóch kolumn można przeprowadzić na różne sposoby w programie Excel, na przykład porównując dwie kolumny wiersz po wierszu lub komórki według komórek, porównując dwie kolumny w celu wyróżnienia dopasowań lub różnic itp. W tym artykule omówiono większość możliwych scenariuszy porównywania dwóch kolumn, które można spotkać się i mieć nadzieję, że może ci pomóc.

Uwagi

W tym samouczku przedstawiono przykładowe dane, które pozwalają lepiej wyjaśnić metody porównywania dwóch kolumn w różnych przypadkach. Na podstawie zbioru danych może zajść potrzeba zmiany lub dostosowania niektórych treści (odniesień), jednak podstawowe zasady pozostaną takie same. Lub bezpośrednio pobierz próbki każdego przypadku, jeśli chcesz tylko sprawdzić, czy metody działają, czy nie.

Poniżej znajduje się zestaw danych, w którym muszę sprawdzić w tym samym wierszu, czy nazwy w kolumnie A są takie same jak w kolumnie B, czy nie.

Kliknij, aby pobrać przykładowy plik

1.1 Porównaj komórki w tym samym wierszu, aby uzyskać dokładne dopasowanie

Generalnie, jeśli chcesz porównać dwie kolumny wiersz po wierszu, aby uzyskać dokładne dopasowanie, możesz użyć poniższej formuły:

Wiadomości wchodzić i przeciągnij uchwyt wypełniania w dół do komórki D8. Jeśli formuła zwraca PRAWDA, wartości w dwóch kolumnach są całkowicie takie same, jeśli zwraca FAŁSZ, są różne.

1.2 Porównaj komórki w tym samym wierszu, aby uzyskać dopasowanie dokładne lub bez uwzględniania wielkości liter (używając formuły JEŻELI)

Jeśli chcesz porównać dwie kolumny wiersz po wierszu, aby nie uwzględniać wielkości liter, lub uzyskać więcej opisów, takich jak Dopasowanie, Niezgodność, możesz użyć funkcji JEŻELI.

Porównywanie komórek w tym samym wierszu w celu uzyskania dokładnego dopasowania

Jeśli chcesz użyć tekstów „Dopasowanie” i „Niezgodność” do opisania wyników porównania, użyj poniższego wzoru:

Wiadomości wchodzić aby uzyskać pierwszy wynik, a następnie przeciągnij uchwyt automatycznego wypełniania do komórki D8.

Porównywanie komórek w tym samym wierszu pod kątem dopasowania bez rozróżniania wielkości liter

Jeśli chcesz porównać komórki pod kątem wielkości liter, możesz użyć poniższej formuły:

Wiadomości wchodzić aby uzyskać pierwszy wynik, a następnie przeciągnij uchwyt automatycznego wypełniania do komórki E8.

Uwaga

W powyższych wzorach możesz zmienić tekst „Dopasuj” i „Niezgodność” na własny opis.

1.3 Porównaj komórki w tym samym wierszu i zaznacz pasujące lub niezgodne dane (używając formatowania warunkowego)

Jeśli chcesz wyróżnić pasujące lub różne wartości, rozszerzenie Formatowanie warunkowe funkcja może ci pomóc.

1. Wybierz dwie kolumny, które mają być porównywane z (B2: C8, z wyłączeniem nagłówków kolumn), a następnie kliknij Strona główna > Formatowanie warunkowe > Nowa zasada.

2. W poppingu Nowa reguła formatowania kliknij, aby wybrać Użyj formuły, aby określić, które komórki należy sformatować w Wybierz typ reguły sekcję, a następnie wpisz = $ B2 = $ C2 w polu tekstowym Formatuj wartości tam, gdzie ta formuła jest prawdziwa.

3. Teraz kliknij utworzony wyświetlić Formatowanie komórek okno dialogowe, a następnie pod Wypełniać wybierz jeden kolor, który chcesz podświetlić dopasowania.

Możesz też zmienić rozmiar czcionki, rozmiar czcionki, obramowanie komórek lub format liczb, aby wyróżnić się dopasowaniami, zgodnie z potrzebami na innych kartach.

4. kliknij OK > OK aby zamknąć okna dialogowe, komórki w tym samym wierszu zostaną podświetlone, jeśli są takie same.

Jeśli chcesz podświetlić niezgodne wartości, możesz użyć tego w = $ B2 <> $ C2 do Formatuj wartości tam, gdzie ta formuła jest prawdziwa pole tekstowe w Edytuj regułę formatowania dialog.

Następnie różnice między dwiema kolumnami w tym samym wierszu zostaną podświetlone określonym kolorem.

1.4 Porównaj dwie kolumny wiersz po wierszu i zaznacz wartości niezgodności (używając VBA)

Jeśli chcesz porównać dwie kolumny wiersz po wierszu z kodem VBA, ten samouczek Cię zadowoli.

1. Włącz arkusz zawierający dwie kolumny używane do porównania, naciśnij Alt + F11 klawisze, aby włączyć Microsoft Visual Basic for Applications okno.

2. W wyświetlonym oknie dialogowym kliknij wstawka > Moduł.

3. Następnie skopiuj i wklej poniższe makro w nowym skrypcie modułu.

VBA: porównaj dwie kolumny wiersz po wierszu i zaznacz różnice

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. naciśnij F5 aby uruchomić kod, pojawi się okno dialogowe umożliwiające wybranie dwóch kolumn.

5. kliknij OK. Następnie różnice dwóch kolumn zostały wyróżnione kolorem tła.

Uwaga

Możesz zmienić kolor podświetlenia w zależności od własnych potrzeb, zmieniając indeks kolorów w kodzie, odniesienie do indeksu kolorów:

W tej części zestaw danych jest pokazany poniżej i chcesz znaleźć wszystkie wartości, które znajdują się jednocześnie w kolumnie B i C, lub znaleźć wartości tylko w kolumnie B.

Kliknij, aby pobrać przykładowy plik

2.1 Porównaj dwie kolumny komórek według komórek i wyświetl porównanie wyników w innej kolumnie (używając formuły)

Tutaj możesz użyć formuły połączonej z funkcją JEŻELI i LICZ.JEŻELI, aby porównać dwie kolumny i znaleźć wartości, które znajdują się w kolumnie B, ale nie w kolumnie C.

Wiadomości wchodzić i przeciągnij uchwyt autouzupełniania do komórki D8.

Uwaga

1. Ta formuła porównuje dwie kolumny bez rozróżniania wielkości liter.

2. Możesz zmienić opis „Nie w C” i „Tak w C” na inne.

3. Jeśli chcesz porównać dwie całe kolumny, zmień ustalony zakres 2 $ C $: 8 $ C na $ C: $ C.

2.2 Porównaj i wyróżnij zduplikowane lub unikalne dane (używając formatowania warunkowego)

Pandemia Formatowanie warunkowe Funkcja w programie Excel jest potężna, tutaj możesz jej użyć do porównania dwóch kolumn komórek według komórek, a następnie zaznacz różnice lub dopasowania, zgodnie z potrzebami.

Podświetl wszystkie duplikaty lub unikalne wartości w dwóch kolumnach

1. Wybierz dwie kolumny, z którymi chcesz porównać, a następnie kliknij Strona główna > Formatowanie warunkowe > Podświetl Zasady komórek > Zduplikowane wartości.

2. W poppingu Zduplikowane wartości wybierz żądany format podświetlania z rozwijanej listy wartości za pomocą.

3. kliknij OK. Następnie zaznaczono duplikaty w dwóch kolumnach.

Uwaga

Jeśli chcesz podświetlić unikalne wartości (różnice) w dwóch kolumnach, również kliknij Strona główna > Formatowanie warunkowe > Podświetl Zasady komórek > Zduplikowane wartości wyświetlić Zduplikowane wartości zmień plik Duplikat do Wyjątkowy na rozwijanej liście po lewej stronie, a następnie wybierz inny format z listy wartości, a następnie kliknij OK.

Unikalne wartości zostaną podświetlone.

Znajdź i zaznacz wartości w kolumnie B, jeśli znajdują się one również w kolumnie C.

Jeśli chcesz podświetlić wartości w kolumnie B, które również znajdują się w kolumnie C, Cdodatkowe formatowanie Funkcja może również wyświadczyć ci przysługę.

1. Wybierz kolumnę B2: B8, kliknij Strona główna > Formatowanie warunkowe > Nowa zasada.

2. w Nowa reguła formatowania dialog, wybierz Użyj formuły, aby określić, które komórki należy sformatować z Wybierz typ reguły sekcję, a następnie wpisz = LICZ.JEŻELI (2 C $ 8: 2 C $, B0 $)> XNUMX w polu tekstowym Formatuj wartości tam, gdzie ta formuła jest prawdziwa.

3. kliknij utworzony , aby przejść do Formatowanie komórek dialog, pod Wypełniać wybierz jeden kolor, aby wyróżnić dopasowania.

Możesz użyć innych formatów, aby wyróżnić wartości w zakładkach Czcionka, Numer, Obramowanie.

4. kliknij OK > OK. Następnie wartości w kolumnie B, które istnieją również w kolumnie C, zostały podświetlone określonym kolorem.

Jeśli chcesz podświetlić wartości, które tylko w kolumnie B, ale nie w kolumnie C, powtórz powyższe kroki, ale zmień formułę w kroku 2 na = LICZ.JEŻELI (2 C $ 8: 2 C $, B0 $) = XNUMX w Nowa reguła formatowania dialog.

Następnie wybierz inny kolor, aby sformatować wartości.

Uwaga

Tutaj formatowanie warunkowe porównuje dwie kolumny bez rozróżniania wielkości liter.

2.3 Porównaj i wybierz lub zaznacz zduplikowane lub unikalne dane (za pomocą poręcznego narzędzia)

Czasami po porównaniu dwóch kolumn możesz podjąć inne działania na dopasowaniach lub różnicach, takie jak zaznaczanie, usuwanie, kopiowanie i tak dalej. W tym przypadku przydatne narzędzie - Wybierz Te same i różne komórki of Kutools dla programu Excel może bezpośrednio wybrać dopasowania lub różnice w celu lepszego wykonania następnej operacji, a także może bezpośrednio podświetlić wartości.

Po bezpłatna instalacja Kutools dla programu Excel, wykonaj poniższe czynności:

1. kliknij Kutools > Wybierz > Wybierz Te same i różne komórki.

2. Następnie w Wybierz Te same i różne komórki okno dialogowe, wykonaj poniższe czynności:

1) W Znajdź wartości w I Zgodnie z sekcje, wybierz dwie kolumny, z którymi chcesz porównać;

2) Wybierz Każdy rząd opcja;

3) Wybierz Te same wartości or Różne wartości tak jak potrzebujesz;

4) Jeśli chcesz wyróżnić wartości, wybierz odpowiedni kolor.

3. kliknij Ok, pojawi się okno dialogowe przypominające o liczbie znalezionych wartości, kliknij OK aby zamknąć okno dialogowe. W tym samym czasie wartości zostały wybrane, teraz możesz usuwać lub kopiować lub wykonywać inne operacje.

Jeśli zaznaczysz Wypełnij kolor tła Wypełnij kolor czcionki pola wyboru, wynik jest wyświetlany w następujący sposób:

Uwaga

Jeśli chcesz porównać z rozróżnianiem wielkości liter, sprawdź Rozróżnianie wielkości liter opcja.

To narzędzie obsługuje porównywanie dwóch kolumn w różnych arkuszach.

2.4 Porównaj dwie kolumny i wymień dokładne duplikaty w innej kolumnie (używając kodu VBA)

Jeśli chcesz wyświetlić pasujące wartości w innej kolumnie po porównaniu dwóch kolumn komórka po komórce, tutaj może Ci pomóc poniższy kod makra.

1. Włącz arkusz, który chcesz porównać dwie kolumny, a następnie naciśnij Alt + F11 klawisze, aby wyświetlić Microsoft Visual Basic for Applications okno.

2. kliknij wstawka > Moduł w Microsoft Visual Basic for Applications okno.

3. Następnie skopiuj i wklej poniższy kod do nowego pustego skryptu modułu.

VBA: Lista duplikatów w kolumnie obok po porównaniu dwóch kolumn

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. naciśnij F5 Aby uruchomić kod, po kolei pojawiają się dwa okna dialogowe umożliwiające osobne wybranie dwóch kolumn.

Uwagi: Najpierw wybierz lewą kolumnę, a następnie prawą kolumnę w drugim oknie dialogowym, w przeciwnym razie duplikaty zastąpią oryginalne dane w drugiej kolumnie.

5. kliknij OK > OK, wówczas dopasowania zostały automatycznie wymienione w prawej kolumnie dwóch kolumn.

Uwaga

Kod VBA porównuje dwie kolumny z uwzględnieniem wielkości liter.

2.5 Porównaj dwie kolumny i zaznacz duplikaty (używając kodu VBA)

Jeśli chcesz porównać dwie kolumny komórka po komórce, a następnie zaznaczyć dopasowania, możesz wypróbować poniższy kod.

1. Włącz arkusz, który chcesz porównać dwie kolumny, a następnie naciśnij Alt + F11 klawisze, aby wyświetlić Microsoft Visual Basic for Applications okno.

2. kliknij wstawka > Moduł w Microsoft Visual Basic for Applications okno.

3. Skopiuj poniższy kod makra i wklej je w nowym pustym miejscu Moduł skrypt.

VBA: porównaj dwie kolumny i zaznacz duplikaty

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. naciśnij F5 klucz do uruchomienia kodu. W pierwszym wyskakującym oknie dialogowym wybierz kolumnę, według której chcesz porównać zduplikowane wartości.

5. kliknij OK. W drugim oknie dialogowym wybierz kolumnę, w której chcesz podświetlić zduplikowane wartości.

6. kliknij OK. Teraz duplikaty w drugiej kolumnie zostały wyróżnione kolorem tła po porównaniu z pierwszą kolumną.

Uwaga

1. Kod porównuje kolumny z uwzględnieniem wielkości liter.

2. Możesz zmienić kolor podświetlenia w zależności od własnych potrzeb, zmieniając indeks kolorów w kodzie, odniesienie do indeksu kolorów:

Czasami możesz chcieć porównać więcej niż dwie kolumny w tym samym wierszu, na przykład zestaw danych, jak pokazano na zrzucie ekranu. W tej sekcji wymieniono różne metody porównywania wielu kolumn.

Kliknij, aby pobrać przykładowy plik

3.1 Znajdź pełne dopasowania we wszystkich komórkach w tym samym wierszu (używając formuły JEŻELI)

Aby znaleźć pełne dopasowania między kolumnami w tym samym wierszu, może Ci pomóc poniższa formuła JEŻELI.

Jeśli komórki w tym samym wierszu pasują do siebie, wyświetlany jest komunikat „Pełne dopasowanie” lub „Nie”.

Wiadomości wchodzić , aby uzyskać pierwszy wynik porównania, a następnie przeciągnij uchwyt automatycznego wypełniania do komórki E7.

Uwaga

1. Formuła porównuje kolumny bez rozróżniania wielkości liter.

2. Jeśli chcesz porównać więcej lub równych trzech kolumn, możesz użyć poniższej formuły:

We wzorze 3 to liczba kolumn, możesz ją zmienić w zależności od potrzeb.

3.2 Znajdź dopasowane w dowolnych dwóch komórkach w tym samym wierszu (używając formuły JEŻELI)

Czasami chcesz się dowiedzieć, czy dowolne dwie kolumny w tym samym wierszu są dopasowane, możesz użyć poniższej formuły JEŻELI.

W tej formule musisz porównać dowolną parę komórek w tym samym wierszu. „Dopasuj” oznacza, że ​​są dopasowane dwie dowolne komórki. Jeśli nie ma pasujących komórek, formuła wyświetla „Nie”, możesz zmienić tekst według potrzeb.

Wiadomości wchodzić i przeciągnij uchwyt wypełniania do komórki E7.

Uwaga

1. Ta formuła nie obsługuje rozróżniania wielkości liter.

2. Jeśli jest wiele kolumn do porównania, porównanie dowolnej pary komórek w formule może być zbyt długie. W takim przypadku możesz rozważyć użycie poniższej formuły, która łączy funkcje JEŻELI i LICZ.JEŻELI.

3.3 Znajdź pełne dopasowania we wszystkich komórkach w tym samym wierszu i zaznacz (używając formatowania warunkowego)

Jeśli chcesz podświetlić wiersze, które pasują do wszystkich komórek, możesz użyć rozszerzenia Formatowanie warunkowe funkcja w programie Excel.

1. Wybierz zakres, którego używasz, a następnie kliknij Strona główna > Formatowanie warunkowe > Nowa zasada.

2. w Nowa reguła formatowania dialog, wybierz Użyj formuły, aby określić, które komórki należy sformatować z Wybierz typ reguły , możesz użyć jednej z poniższych formuł w Formatuj wartości tam, gdzie ta formuła jest prawdziwa pole tekstowe.

Or

Uwagi: Jeśli liczba kolumn jest większa niż trzy, zakładając, że jest to 5, formuły należy zmienić na:

Or

3. kliknij utworzony , aby przejść do okna dialogowego Formatowanie komórek, a następnie wybierz jeden kolor wypełnienia lub inne formatowanie komórek, aby wyróżnić się z wierszy.

4. kliknij OK > OK, teraz tylko te wiersze, w których wszystkie komórki są zgodne, będą podświetlone.

Uwaga

Powyższe formuły nie obsługują rozróżniania wielkości liter.

3.4 Porównaj wiele kolumn i zaznacz różnice między wierszami

Jeśli chcesz podświetlić różnice między wierszami, co oznacza, że ​​porównuje komórki kolumn jeden po drugim i znajduje różne komórki według pierwszej kolumny, możesz użyć wbudowanej funkcji programu Excel -Przejdź do specjalnego.

1. Wybierz zakres, w którym chcesz zaznaczyć różnice w wierszach, i kliknij Strona główna > Znajdź i wybierz > Przejdź do specjalnego.

2. W poppingu Przejdź do specjalnego okno dialogowe, sprawdź Różnice między wierszami opcja.

3. kliknij OK. Teraz różnice w wierszach zostały wybrane.

4. Teraz zaznacz komórki, kliknij Strona główna > Kolor wypełnienia aby wybrać jeden kolor z menu rozwijanego.

Uwaga

Ta metoda porównuje komórki bez rozróżniania wielkości liter.

Przypuśćmy, że są dwie kolumny, kolumna B jest dłuższa, a kolumna C jest krótsza, jak pokazano poniżej. W porównaniu z kolumną B, jak znaleźć brakujące dane w kolumnie C?

Kliknij, aby pobrać przykładowy plik

4.1 Porównaj i znajdź brakujące punkty danych (za pomocą funkcji WYSZUKAJ.PIONOWO lub PODAJ.

Jeśli chcesz tylko określić, których danych brakuje po porównaniu dwóch kolumn, możesz użyć jednej z poniższych formuł:

Or

Wiadomości wchodzić , a następnie przeciągnij uchwyt automatycznego wypełniania na komórkę D10. Teraz, jeśli dane znajdują się zarówno w kolumnie B, jak i kolumnie C, formuła zwraca FALSE, jeśli dane znajdują się tylko w kolumnie B, ale brakuje w kolumnie C, formuła zwraca wartość TRUE.

Uwaga

Powyższe dwie formuły porównują dane bez rozróżniania wielkości liter.

4.2 Porównaj dwie kolumny i wypisz brakujące dane poniżej (używając wzoru INDEKSU)

Jeśli chcesz wyświetlić brakujące dane poniżej krótszej kolumny po porównaniu dwóch kolumn, może Ci pomóc formuła tablicowa INDEKS.

W poniższej komórce krótszej kolumny, zakładając, że komórka C7, wpisz poniższą formułę:

Wiadomości Shift + Ctrl + Enter aby uzyskać pierwsze brakujące dane, a następnie przeciągnij uchwyt automatycznego wypełniania w dół, aż zwróci wartość błędu # N / A.

Następnie możesz usunąć wartość błędu, a wszystkie brakujące dane zostały wymienione poniżej krótszej kolumny.

Uwaga

Ta formuła porównuje komórki bez rozróżniania wielkości liter.

4.3 Porównaj i znajdź brakujące punkty danych i wypisz je w innej kolumnie (używając poręcznego narzędzia)

Jeśli chcesz wykonać dodatkowe operacje na brakujących danych po porównaniu dwóch kolumn, na przykład wypisanie brakujących danych w innej kolumnie lub uzupełnienie brakujących danych poniżej krótszej kolumny, możesz wypróbować przydatne narzędzie -Wybierz Te same i różne komórki of Kutools dla programu Excel.

1. kliknij Kutools > Wybierz > Wybierz Te same i różne komórki.

2. w Wybierz Te same i różne komórki okno dialogowe, wykonaj poniższe czynności:

1 w Znajdź wartości w wybierz dłuższą kolumnę, która zawiera pełną listę.
In Zgodnie z wybierz krótszą kolumnę, w której brakuje niektórych danych.

2) Wybierz Każdy rząd opcja.

3) Wybierz Różne wartości opcja.

3. kliknij Ok, pojawi się okno dialogowe przypominające o liczbie brakujących danych, kliknij OK zamknąć to. Następnie wybrano brakujące dane.

Teraz możesz nacisnąć Ctrl + C klawisze, aby skopiować wybrane brakujące dane i wkleić je, naciskając Ctrl + V klawisze poniżej krótszej kolumny lub innej nowej kolumny, jeśli potrzebujesz.

Uwaga

Zaznaczanie Niewrażliwa na wielkość liter Opcja w oknie dialogowym Wybierz te same i różne komórki porówna dwie kolumny z uwzględnieniem wielkości liter.

4.4 Porównaj dwie kolumny i wymień brakujące dane poniżej (używając VBA)

Oto kod makra, który może wypełnić brakujące dane poniżej dwóch kolumn.

1. Otwórz arkusz, którego używasz, naciśnij Alt + F11 klawisze, aby otworzyć Microsoft Visual Basic for Applications okno.

2. kliknij wstawka > Moduł aby utworzyć nowy moduł do umieszczenia poniżej kodu VBA w.

VBA: porównaj dwie kolumny i uzupełnij brakujące dane

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. Następnie naciśnij F5 aby uruchomić kod, pojawi się okno dialogowe umożliwiające wybranie dwóch porównywanych kolumn.

4. kliknij OK. Teraz brakujące dane zostały wymienione poniżej dwóch kolumn.

Uwaga

Kod porównuje komórki bez rozróżniania wielkości liter.

Jeśli są dwie kolumny dat, jak pokazano na zrzucie ekranu, możesz porównać, która data jest później w tym samym wierszu.

Kliknij, aby pobrać przykładowy plik

5.1 Porównaj dwie kolumny, jeśli większe lub mniejsze niż (używając formuły)

Możesz użyć prostej formuły, aby szybko sprawdzić, czy data 1 jest późniejsza niż data 2 w każdym wierszu.

Wiadomości wchodzić aby uzyskać pierwszy porównywany wynik, a następnie przeciągnij uchwyt automatycznego wypełniania do komórki C6, aby uzyskać wszystkie wyniki.

Uwaga

1. W programie Excel daty są przechowywane jako serie liczb, w rzeczywistości są to liczby. Dlatego możesz zastosować formułę do bezpośredniego porównywania dat.

2. Jeśli chcesz porównać, czy data 1 jest wcześniejsza niż data 2 w każdym wierszu, zmień symbol > do < we wzorze.

5.2 Porównaj dwie kolumny, jeśli są większe lub mniejsze niż formatowanie (przy użyciu formatowania warunkowego)

Jeśli chcesz podświetlić komórki w kolumnie Data 1, jeśli są większe niż Data 2, możesz użyć Formatowanie warunkowe funkcja w programie Excel.

1. Wybierz daty w kolumnie B (Data1), a następnie kliknij Strona główna > Formatowanie warunkowe > Nowa zasada.

2. w Nowa reguła formatowania dialog, wybierz Użyj formuły, aby określić, które komórki należy sformatować w Wybierz typ reguły , a następnie wpisz formułę = $ B2> $ C2 w polu tekstowym Formatuj wartości tam, gdzie ta formuła jest prawdziwa.

Jeśli chcesz podświetlić komórki w kolumnie B, które są mniejsze niż komórki w kolumnie C, użyj formuły = B2 $ <$ C2.

3. kliknij utworzony , aby otworzyć Formatowanie komórek a następnie wybierz odpowiedni typ formatu.

4. kliknij OK > OK. Następnie zaznaczono komórki w kolumnie Data1, które są większe niż komórki w kolumnie Data2.

Na przykład istnieją dwie tabele, teraz chcesz porównać kolumnę B i kolumnę E, a następnie znaleźć ceny względne z kolumny C i zwrócić je w kolumnie F.

Kliknij, aby pobrać przykładowy plik

6.1 Wyciągnij dokładne pasujące dane (używając wzoru lub poręcznego narzędzia)

Tutaj wprowadza kilka pomocnych formuł i narzędzie do rozwiązania tego zadania.

Metoda formuł

W komórce F2 (komórce, w której chcesz umieścić zwracaną wartość) użyj jednej z poniższych formuł:

Or

Wiadomości wchodzić key, a pierwsza wartość została znaleziona. Następnie przeciągnij uchwyt automatycznego wypełniania w dół do komórki F6, wszystkie wartości zostały wyodrębnione.

Uwaga

1. W formułach nie jest uwzględniana wielkość liter.

2. Liczba 2 we wzorze wskazuje, że pasujące wartości znajdują się w drugiej kolumnie tablicy tabeli.

3. Jeśli formuły nie mogą znaleźć wartości względnej, zwraca wartość błędu # N / A.

Poręczna metoda narzędziowa

Jeśli jesteś zdezorientowany z formułami, możesz wypróbować przydatne narzędzie - Pomocnik formuły of Kutools dla programu Excel, który zawiera wiele formuł do rozwiązywania większości problemów w programie Excel. Dzięki niemu wystarczy wybrać zakres, ale nie musisz pamiętać, jak używają formuł.

Po bezpłatna instalacja Kutools dla programu Excel, wykonaj poniższe czynności:

1. Wybierz komórkę F2 (komórkę, w której chcesz umieścić zwracaną wartość) i kliknij Kutools > Pomocnik formuły > Wyszukiwanie i odniesienie > Poszukaj wartości na liście.

2. w Pomocnik formuł dialog po prawej stronie Wprowadzanie argumentów wybierz zakres tablicy tabeli, wyszukaj wartość i określ kolumnę, w której chcesz znaleźć wartość zwracaną.

Wskazówka: Pamiętaj, aby zmienić bezwzględne odniesienie w Lookup_value do względnej lub możesz znaleźć tylko pierwszą wartość.

3. kliknij Ok, pierwsza wartość została znaleziona, a następnie przeciągnij uchwyt automatycznego wypełniania w dół do komórki F6.

6.2 Wyciągnij dane częściowego dopasowania (używając wzoru)

Jeśli między dwiema porównywanymi kolumnami występują drobne różnice, jak pokazano na zrzucie ekranu, powyższe metody nie będą działać.

Wybierz jedną z poniższych formuł, aby rozwiązać to zadanie:

Or

Wiadomości wchodzić , a następnie przeciągnij uchwyt automatycznego wypełniania w dół do komórki F5, wszystkie wartości zostały znalezione.

Uwaga

1. W formułach nie jest uwzględniana wielkość liter.

2. Liczba 2 we wzorze wskazuje, że wartości zwracane znajdują się w drugiej kolumnie tablicy tabeli.

3. Jeśli formuły nie mogą znaleźć wartości względnej, zwraca wartość błędu # N / A.

4. * we wzorze jest symbolem wieloznacznym, który służy do wskazania dowolnego znaku lub ciągów znaków.

Poniższy zestaw danych jest przykładem porównywania i liczenia dopasowań lub różnic.

Kliknij, aby pobrać przykładowy plik

7.1 Porównaj dwie kolumny i policz dopasowania (używając formuły SUMPRODUCT)

Formuła SUMPRODUCT może szybko policzyć dopasowania w dwóch kolumnach.

Wiadomości wchodzić aby uzyskać wynik.

Uwaga

Formuła zlicza komórki bez rozróżniania wielkości liter.

7.2 Porównaj dwie kolumny i policz dopasowania lub różnice (za pomocą poręcznego narzędzia)

Jeśli chcesz policzyć dopasowania lub różnice między dwiema kolumnami, przydatne narzędzie - Wybierz Te same i różne komórki.

Po bezpłatna instalacja Kutools dla programu Excel, wykonaj poniższe czynności:

1. kliknij Kutools > Wybierz > Wybierz Te same i różne komórki.

2. w Wybierz opcję Same & Difference Cells wybierz dwa zakresy kolumn w Znajdź wartości w Zgodnie z sekcje oddzielnie, a następnie wybierz Każdy rządi wybierz Te same wartości or Różne wartości opcja, jak potrzebujesz.

3. kliknij Ok. Pojawi się okno dialogowe z informacją, ile pasujących lub różnych komórek jest zaznaczonych.

Pasujące komórki

Różne komórki

Przypuśćmy, że tutaj jest lista danych w kolumnie B i chcesz policzyć komórki zawierające „Apple” lub „Candy” w kolumnie D, jak pokazano na poniższym zrzucie ekranu:

Kliknij, aby pobrać przykładowy plik

Aby policzyć, czy komórka zawiera co najmniej jedną wartość, możesz użyć formuły z symbolami wieloznacznymi, aby rozwiązać ten problem.

Wiadomości Shift + Ctrl + Enter aby uzyskać pierwsze sprawdzenie, a następnie przeciągnij uchwyt autouzupełniania w dół do komórki F8.

Teraz, jeśli powiązana komórka zawiera jedną lub więcej wartości w kolumnie D, wynik wyświetla liczbę większą niż 0, jeśli nie zawiera żadnej wartości w kolumnie D, zwraca 0.

Jeśli chcesz policzyć całkowitą liczbę komórek zawierających wartości z kolumny D, użyj formuły w komórce F8 poniżej:

Uwaga

1. Możesz również użyć formuły do ​​obliczenia, czy komórka zawiera wartości z innej kolumny

Ta formuła musi tylko nacisnąć wchodzić a następnie przeciągnij uchwyt automatycznego wypełniania.

2. W formułach * jest symbolem wieloznacznym oznaczającym dowolny znak lub ciąg.

Jeśli chcesz usunąć różnice lub dopasowania po porównaniu dwóch kolumn, jak pokazano na poniższym zrzucie ekranu:

Kliknij, aby pobrać przykładowy plik

Możesz użyć wzoru, aby najpierw znaleźć różnicę lub dopasowania:

Wiadomości wchodzić i przeciągnij uchwyt automatycznego wypełniania w dół do komórki D8.

Następnie zastosuj Filtr funkcja w przewyższać aby odfiltrować różnice lub dopasować wartość.

Wybierz kolumnę z formułą, a następnie kliknij Statystyki i dane > Filtr.

Następnie przycisk filtru pojawi się w kolumnie C, kliknij przycisk filtru, aby rozwinąć menu rozwijane, wybierz Różnica or Mecz tak jak potrzebujesz. Następnie kliknij OK skończyć.

Teraz tylko różnice lub dopasowania zostały odfiltrowane. Możesz je wybrać i nacisnąć Kasować klucz, aby je usunąć.

Teraz kliknij Statystyki i dane > Filtr ponownie, aby wyczyścić filtr.

Usuń kolumnę z formułą, jeśli już jej nie potrzebujesz.

Istnieją dwie kolumny, jedna zawiera ceny oryginalne, a druga ceny sprzedaży. Teraz ta część wprowadza formułę do porównania tych dwóch kolumn, a następnie obliczenia procentowej zmiany między tymi dwiema kolumnami.

Kliknij, aby pobrać przykładowy plik

Możesz użyć poniższego wzoru, aby obliczyć procentową zmianę między dwiema cenami w tym samym wierszu.

Wiadomości wchodzić aby uzyskać liczbę, a następnie przeciągnij uchwyt automatycznego wypełniania w dół do komórki D7.

Następnie sformatuj wynik formuły jako procent. Wybierz wyniki, kliknij Strona główna kartę i przejdź do Numer grupa do kliknięcia Styl procentowy.

Wyniki formuły zostały sformatowane jako procenty.

Teraz wiesz, jak porównać dwie kolumny po przeczytaniu powyższych metod. Jednak w niektórych przypadkach możesz chcieć porównać dwa zakresy (dwie serie z wieloma kolumnami). Możesz użyć powyższych metod (formuł lub formatowania warunkowego), aby porównać je kolumna po kolumnie, ale tutaj wprowadza przydatne narzędzie - Kutools dla programu Excel może szybko rozwiązać tę pracę w różnych przypadkach bez formuły.

Kliknij, aby pobrać przykładowy plik

11.1 Porównaj dwa zakresy według komórki

Oto dwa zakresy, które należy porównać według komórek, możesz użyć Wybierz Te same i różne komórki narzędzie Kutools for Excel do obsługi tego.

Po bezpłatna instalacja Kutools dla programu Excel, wykonaj poniższe czynności:

1. kliknij Kutools > Wybierz > Wybierz Te same i różne komórki.

2. W poppingu Wybierz Te same i różne komórki okno dialogowe, wykonaj poniższe czynności:

1) W Znajdź wartości w wybierz zakres, dla którego chcesz znaleźć dopasowania lub różnice po porównaniu dwóch zakresów.

2) W Zgodnie z wybierz inny zakres używany do porównywania zakresu.

3 w Na podstawie Sekcja, wybierz Pojedyncza komórka.

4) Następnie w Znajdź wybierz typ komórek, które chcesz zaznaczyć lub wyróżnić.

5) W Przetwarzanie wyników sekcji, możesz podświetlić komórki przez wypełnienie kolorem tła lub kolorem czcionki, jeśli nie musisz wyróżniać, nie zaznaczaj pól wyboru.

3. kliknij Ok. Pojawi się okno dialogowe z przypomnieniem, ile komórek / wierszy zostało wybranych, kliknij OK zamknąć to. Teraz komórki, które różnią się od komórek z innego zakresu, zostały wybrane i wyróżnione.

Podświetlanie tych samych wartości

Uwaga

Jeśli chcesz porównać dwa zakresy według wiersza, możesz również zastosować Wybierz Te same i różne komórki funkcja, ale w tym przypadku wybierz Każdy rząd opcja.

11.2 Porównaj dwa zakresy, jeśli dane są w tej samej kolejności

Jeśli chcesz porównać dwa zakresy według wiersza, rozszerzenie Funkcja porównania komórek of Kutools for Excel może ci pomóc.

Po bezpłatna instalacja Kutools dla programu Excel, wykonaj poniższe czynności:

Przypuśćmy, że zakres F2: H7 jest modelem, teraz chcesz sprawdzić, czy dane w zakresie B2: D7 są we właściwej kolejności zgodnie z zakresem F2: H7.

1. kliknij Kutools > Porównaj komórki.

2. w Porównaj komórki okno dialogowe, ustawione jak poniżej:

1) Wybierz dwa zakresy w Znajdź wartości w Zgodnie z pudełka oddzielnie.

2) Wybierz typ komórki, którą chcesz podświetlić w Znajdź Sekcja.

3) Wybierz typ podświetlenia w Przetwarzanie wyników Sekcja.

3. kliknij Ok. Pojawi się okno dialogowe z przypomnieniem, ile komórek zostało zaznaczonych, kliknij OK zamknąć to. Teraz komórki, które różnią się od komórek z innego zakresu, zostały wybrane i wyróżnione.

Najlepsze narzędzia biurowe

Kutools dla programu Excel rozwiązuje większość problemów i zwiększa produktywność o 80%

• Pasek Super Formula (łatwo edytować wiele wierszy tekstu i formuły); Układ do czytania (łatwe odczytywanie i edytowanie dużej liczby komórek); Wklej do filtrowanego zakresu...
• Scal komórki / wiersze / kolumny i przechowywanie danych; Podziel zawartość komórek; Połącz zduplikowane wiersze i sumę / średnią... Zapobiegaj zduplikowanym komórkom; Porównaj zakresy...
• Wybierz Duplikat lub Unikalny Wydziwianie; Wybierz puste wiersze (wszystkie komórki są puste); Super Find i Fuzzy Find w wielu zeszytach ćwiczeń; Losowy wybór ...
• Dokładna kopia Wiele komórek bez zmiany odwołania do formuły; Automatyczne tworzenie odniesień do wielu arkuszy; Wstaw punktory, Pola wyboru i nie tylko ...
• Ulubione i szybkie wstawianie formuł, Zakresy, wykresy i obrazy; Szyfruj komórki z hasłem; Utwórz listę mailingową i wysyłaj e-maile ...
• Wyodrębnij tekst, Dodaj tekst, Usuń według pozycji, Usuń przestrzeń; Tworzenie i drukowanie podsumowań stronicowania; Konwertuj zawartość komórek i komentarze...
• Super filtr (zapisz i zastosuj schematy filtrów do innych arkuszy); Zaawansowane sortowanie według miesiąca / tygodnia / dnia, częstotliwości i innych; Specjalny filtr pogrubieniem, kursywą ...
• Połącz skoroszyty i arkusze robocze; Scal tabele na podstawie kluczowych kolumn; Podziel dane na wiele arkuszy; Konwersja wsadowa xls, xlsx i PDF...
• Grupowanie tabel przestawnych według numer tygodnia, dzień tygodnia i więcej ... Pokaż odblokowane, zablokowane komórki w różnych kolorach; Podświetl komórki, które mają formułę / nazwę...

Czytaj więcej... Pełne funkcje 30-dniowy bezpłatny okres próbny ... Zakup... 

---

Karta Office - przenosi interfejs z zakładkami do pakietu Office i znacznie ułatwia pracę

• Włącz edycję i czytanie na kartach w programach Word, Excel, PowerPoint, Publisher, Access, Visio i Project.
• Otwieraj i twórz wiele dokumentów w nowych kartach tego samego okna, a nie w nowych oknach.
• Zwiększa produktywność o 50% i redukuje setki kliknięć myszą każdego dnia!

Czytaj więcej... Pełne funkcje 30-dniowy bezpłatny okres próbny ... Zakup...