Jak porównać wartości oddzielone przecinkami w dwóch komórkach i zwrócić zduplikowane lub unikalne wartości w programie Excel?
Jak pokazano na poniższym zrzucie ekranu, są dwie kolumny - Kolumna1 i Kolumna2, każda komórka w kolumnie zawiera liczby oddzielone przecinkami. Co możesz zrobić, aby porównać liczby oddzielone przecinkami w Kolumnie 1 z zawartością komórek w tym samym wierszu Kolumny 2 i zwrócić wszystkie zduplikowane lub unikalne wartości?
W tym samouczku przedstawiono dwie metody ułatwiające wykonanie tego zadania.
Porównuj wartości oddzielone przecinkami w dwóch komórkach i zwracaj zduplikowane lub unikalne wartości za pomocą formuł
Ta sekcja zawiera dwie formuły ułatwiające porównywanie wartości rozdzielonych przecinkami w dwóch komórkach i zwracanie zduplikowanych lub unikatowych wartości między nimi.
Note: Poniższe formuły działają tylko w Excela dla wersji 365. Jeśli używasz innych wersji programu Excel, spróbuj użyć poniższej metody VBA.
Weźmy powyższe dwie kolumny jako przykład, aby porównać liczby oddzielone przecinkami w Kolumnie 1 z liczbami oddzielonymi przecinkami w tym samym wierszu Kolumny 2 i zwrócić zduplikowane lub unikalne wartości, wykonaj następujące czynności.
Zwróć zduplikowane wartości
1. Wybierz komórkę, aby wyprowadzić zduplikowane liczby między dwiema określonymi komórkami z liczbami oddzielonymi przecinkami, w tym przypadku wybieram komórkę D2, następnie wprowadź poniższą formułę i naciśnij przycisk Wchodzę klucz. Wybierz komórkę formuły i przeciągnij ją Uchwyt autouzupełniania w dół, aby uzyskać zduplikowane liczby między komórkami w innych wierszach.
=LET(x, TRANSPOSE(TEXTSPLIT(TEXTJOIN(", ",TRUE,A2:B2), ", ")),y,UNIQUE(x),z,UNIQUE(x,,1), TEXTJOIN(", ",TRUE,IF(ISERROR(MATCH(y,z,0)),y, "")))
Zwracaj unikalne wartości
Aby zwrócić unikalne liczby między dwiema określonymi komórkami z liczbami oddzielonymi przecinkami w tym samym wierszu, może pomóc następująca formuła.
1. Wybierz komórkę, aby wyświetlić unikalne liczby, w tym przypadku wybieram komórkę E2, a następnie wprowadź poniższą formułę i naciśnij przycisk Wchodzę klucz. Wybierz komórkę formuły i przeciągnij ją Uchwyt autouzupełniania w dół, aby uzyskać unikalne liczby między komórkami w innych wierszach.
=TEXTJOIN(", ",TRUE,UNIQUE(TRANSPOSE(TEXTSPLIT(TEXTJOIN(", ",TRUE,A2:B2), ", ")),,1))
Uwagi:
Porównaj dwie kolumny z wartościami oddzielonymi przecinkami i zwróć zduplikowane lub unikalne wartości za pomocą VBA
Funkcja zdefiniowana przez użytkownika dostępna w tej sekcji pomaga porównać wartości oddzielone przecinkami w dwóch określonych komórkach i zwrócić zduplikowane wartości lub wartości unikalne między nimi. Proszę wykonać następujące czynności.
Weź ten sam przykład, co powyżej, aby porównać liczby oddzielone przecinkami w Kolumnie 1 z liczbami oddzielonymi przecinkami w tym samym wierszu Kolumny 2 i zwrócić zduplikowane lub unikalne wartości, wypróbuj funkcję zdefiniowaną przez użytkownika w tej sekcji.
1. W otwartym skoroszycie naciśnij inny + F11 klawisze, aby otworzyć Microsoft Visual Basic for Applications okno.
2. w Microsoft Visual Basic for Applications okno, kliknij wstawka > Modułi skopiuj następujący kod VBA w pliku Moduł (kod) okno.
Kod VBA: Porównaj wartości oddzielone przecinkami w dwóch komórkach i zwróć zduplikowane/unikalne wartości
Private Function COMPARE(Rng1, Rng2 As Range, Op As Boolean)
'Updated by Extendoffice 20221019
Dim R1Arr As Variant
Dim R2Arr As Variant
Dim Ans1 As String
Dim Ans2 As String
Dim Separator As String
Dim d1 As New Dictionary
Dim d2 As New Dictionary
Dim d3 As New Dictionary
Application.Volatile
Separator = ", "
R1Arr = Split(Rng1.Value, Separator)
R2Arr = Split(Rng2.Value, Separator)
Ans1 = ""
Ans2 = ""
For Each ch In R2Arr
If Not d2.Exists(ch) Then
d2.Add ch, "1"
End If
Next
If Op Then
For Each ch In R1Arr
If d2.Exists(ch) Then
If Not d3.Exists(ch) Then
d3.Add ch, "1"
Ans1 = Ans1 & ch & Separator
End If
End If
Next
If Ans1 <> "" Then
Ans1 = Mid(Ans1, 1, Len(Ans1) - Len(Separator))
End If
COMPARE = Ans1
Else
For Each ch In R1Arr
If Not d1.Exists(ch) Then
d1.Add ch, "1"
End If
Next
For Each ch In R1Arr
If Not d2.Exists(ch) Then
If Not d3.Exists(ch) Then
d3.Add ch, "1"
Ans2 = Ans2 & ch & Separator
End If
End If
Next
For Each ch In R2Arr
If Not d1.Exists(ch) Then
If Not d3.Exists(ch) Then
d3.Add ch, "1"
Ans2 = Ans2 & ch & Separator
End If
End If
Next
If Ans2 <> "" Then
Ans2 = Mid(Ans2, 1, Len(Ans2) - Len(Separator))
End If
COMPARE = Ans2
End If
End Function3. Po wklejeniu kodu w Moduł (kod) okno, przejdź do kliknięcia Tools > Referencje otworzyć Referencje - VBAProject okno, sprawdź Środowisko wykonawcze skryptów firmy Microsoft Microsoft a następnie kliknij OK przycisk.
4. wciśnij inny + Q klucze do zamknięcia Microsoft Visual Basic for Applications okno.
5. Teraz musisz osobno zastosować dwie funkcje, aby zwrócić zduplikowane i unikalne wartości z dwóch komórek wartości oddzielonych przecinkami.
Zwróć zduplikowaną wartość
Wybierz komórkę, aby wyświetlić zduplikowane liczby, w tym przykładzie wybieram komórkę D2, następnie wprowadzam poniższą formułę i naciskam Wchodzę aby uzyskać zduplikowane liczby między komórkami A2 i B2.
Zaznacz komórkę formuły i przeciągnij jej uchwyt autouzupełniania w dół, aby uzyskać zduplikowane liczby między komórkami w innych wierszach.
=COMPARE(A2,B2,TRUE)
Zwracaj unikalne wartości
Wybierz komórkę, aby wyświetlić unikalne liczby, w tym przykładzie wybieram komórkę E2, a następnie wprowadzam poniższą formułę i naciskam Wchodzę aby uzyskać unikalne liczby między komórkami A2 i B2.
Zaznacz komórkę formuły i przeciągnij jej uchwyt autouzupełniania w dół, aby uzyskać unikalne liczby między komórkami w innych wierszach.
=COMPARE(A2,B2,FALSE)
Najlepsze narzędzia biurowe
Zwiększ swoje umiejętności Excela dzięki Kutools for Excel i doświadcz wydajności jak nigdy dotąd. Kutools dla programu Excel oferuje ponad 300 zaawansowanych funkcji zwiększających produktywność i oszczędzających czas. Kliknij tutaj, aby uzyskać funkcję, której najbardziej potrzebujesz...
Karta Office wprowadza 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ęć myszką każdego dnia!
