Как да сравните стойности, разделени със запетая в две клетки и да върнете дублирани или уникални стойности в Excel?
Както е показано на екранната снимка по-долу, има две колони - Колона1 и Колона2, всяка клетка в колоната съдържа числа, разделени със запетаи. Какво можете да направите, за да сравните числата, разделени със запетая, в Колона1 със съдържанието на клетката в същия ред на Колона2 и да върнете всички дублирани или уникални стойности?
Този урок предоставя два метода, които да ви помогнат да изпълните тази задача.
Сравнете стойности, разделени със запетая, в две клетки и върнете дублирани или уникални стойности с формули
Този раздел предоставя две формули, за да ви помогне да сравните стойностите, разделени със запетая в две клетки и да върнете дублиращите се или уникални стойности между тях.
Забележка: Следните формули работят само в Excel за 365. Ако използвате други версии на Excel, опитайте да използвате метода на VBA по-долу.
Вземете горните две колони като пример, за да сравните числата, разделени със запетая в Колона 1 с числата, разделени със запетая в същия ред на Колона 2 и да върнете дублирани или уникални стойности, моля, направете следното.
Връщане на дублирани стойности
1. Изберете клетка, за да изведете дублиращите се числа между двете посочени клетки с числа, разделени със запетая, в този случай избирам клетка D2, след това въведете формулата по-долу и натиснете Въведете ключ. Изберете клетката с формула и я плъзнете Манипулатор за автоматично попълване надолу, за да получите дублиращите се числа между клетките в другите редове.
=LET(x, TRANSPOSE(TEXTSPLIT(TEXTJOIN(", ",TRUE,A2:B2), ", ")),y,UNIQUE(x),z,UNIQUE(x,,1), TEXTJOIN(", ",TRUE,IF(ISERROR(MATCH(y,z,0)),y, "")))
Връща уникални стойности
Следната формула може да помогне за връщане на уникалните числа между двете посочени клетки с числа, разделени със запетаи в същия ред.
1. Изберете клетка за извеждане на уникалните числа, в този случай избирам клетка E2, след това въведете формулата по-долу и натиснете Въведете ключ. Изберете клетката с формула и я плъзнете Манипулатор за автоматично попълване надолу, за да получите уникалните числа между клетките в другите редове.
=TEXTJOIN(", ",TRUE,UNIQUE(TRANSPOSE(TEXTSPLIT(TEXTJOIN(", ",TRUE,A2:B2), ", ")),,1))
бележки:
Сравнете две колони със стойности, разделени със запетая, и върнете дублирани или уникални стойности с VBA
Дефинираната от потребителя функция, предоставена в този раздел, помага да се сравнят стойностите, разделени със запетая, в две посочени клетки и да се върнат дублиращите се стойности или уникалните стойности между тях. Моля, направете следното.
Вземете същия пример като по-горе, за да сравните числата, разделени със запетая в Колона 1 с числата, разделени със запетая в същия ред на Колона 2 и да върнете дублирани или уникални стойности, моля, опитайте дефинираната от потребителя функция в този раздел.
1. В отварящата работна книга натиснете Друг + F11 за да отворите Microsoft Visual Basic за приложения прозорец.
2. В Microsoft Visual Basic за приложения прозорец, кликнете Поставете > Модулии копирайте следния VBA код в Модул (код) прозорец.
VBA код: Сравнете стойностите, разделени със запетая в две клетки и върнете дублирани/уникални стойности
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. След като поставите кода в Модул (код) прозорец, отидете на щракване Инструменти > Препратки за да отворите Препратки – VBAProject прозорец, проверете Microsoft Scripting Runtime и след това кликнете върху OK бутон.
4. Натисни Друг + Q ключове за затваряне на Microsoft Visual Basic за приложения прозорец.
5. Сега трябва да приложите две функции поотделно, за да върнете дублиращи се и уникални стойности от две клетки със стойности, разделени със запетая.
Връща дублирана стойност
Изберете клетка, за да изведете дублиращите се числа, в този пример избирам клетка D2, след това въведете формулата по-долу и натиснете Въведете ключ, за да получите дублиращите се числа между клетка A2 и B2.
Изберете клетката с формула и плъзнете манипулатора й за автоматично попълване надолу, за да получите дублиращите се числа между клетките в другите редове.
=COMPARE(A2,B2,TRUE)
Връща уникални стойности
Изберете клетка, за да изведете уникалните числа, в този пример аз избирам клетка E2, след това въведете формулата по-долу и натиснете Въведете ключ за получаване на уникалните числа между клетка A2 и B2.
Изберете клетката с формула и плъзнете манипулатора й за автоматично попълване надолу, за да получите уникалните числа между клетките в другите редове.
=COMPARE(A2,B2,FALSE)
Най-добрите инструменти за продуктивност в офиса
Усъвършенствайте уменията си за Excel с Kutools за Excel и изпитайте ефективност, както никога досега. Kutools за Excel предлага над 300 разширени функции за повишаване на производителността и спестяване на време. Щракнете тук, за да получите функцията, от която се нуждаете най-много...
Раздел Office Внася интерфейс с раздели в Office и прави работата ви много по-лесна
- Разрешете редактиране и четене с раздели в Word, Excel, PowerPoint, Publisher, Access, Visio и Project.
- Отваряйте и създавайте множество документи в нови раздели на един и същ прозорец, а не в нови прозорци.
- Увеличава вашата производителност с 50% и намалява стотици кликвания на мишката за вас всеки ден!
