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:
Naciśnij przycisk Wchodzę 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.
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:
Naciśnij przycisk Wchodzę 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:
Naciśnij przycisk Wchodzę 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.
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ć 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
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.
Naciśnij przycisk Wchodzę 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)
W ramach projektu 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 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 i 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ł 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ł 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”.
Naciśnij przycisk Wchodzę , 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.
Naciśnij przycisk Wchodzę 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.
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
Naciśnij przycisk Wchodzę , 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łę:
Naciśnij przycisk 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.
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.
Naciśnij przycisk Wchodzę 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.
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ć 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
Naciśnij przycisk Wchodzę 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 Wyszukaj_wartość 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
Naciśnij przycisk Wchodzę , 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.
Naciśnij przycisk Wchodzę 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 i 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.
Naciśnij przycisk 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ąć Wchodzę 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:
Naciśnij przycisk Wchodzę i przeciągnij uchwyt automatycznego wypełniania w dół do komórki D8.
Następnie zastosuj Filtruj funkcja w przewyższać aby odfiltrować różnice lub dopasować wartość.
Wybierz kolumnę z formułą, a następnie kliknij Dane > Filtruj.
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ąć Usuń klucz, aby je usunąć.
Teraz kliknij Dane > Filtruj 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.
Naciśnij przycisk Wchodzę 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 i 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.
Możesz być także zainteresowany |
|
Porównaj daty, jeśli są większe niż inna data w programie Excel Porównaj dwa arkusze obok siebie Wyszukaj porównanie dwóch list w oddzielnych arkuszach roboczych Znajdź, zaznacz, filtruj, policz, usuń duplikaty w programie Excel |
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ę...
- 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!
