Hogyan lehet összehasonlítani a vesszővel elválasztott értékeket két cellában, és hogyan lehet ismétlődő vagy egyedi értékeket visszaadni az Excelben?
Amint az alábbi képernyőképen látható, két oszlop van - Oszlop1 és Oszlop2, az oszlop minden cellája vesszővel elválasztott számokat tartalmaz. Mit tehetne az 1. oszlop vesszővel elválasztott számainak összehasonlításához a 2. oszlop ugyanazon sorában lévő cellatartalommal, és az összes ismétlődő vagy egyedi érték visszaadásához?
Ez az oktatóanyag két módszert kínál a feladat végrehajtásához.
Hasonlítsa össze a vesszővel elválasztott értékeket két cellában, és adja vissza az ismétlődő vagy egyedi értékeket képletekkel
Ez a szakasz két képletet tartalmaz, amelyek segítenek összehasonlítani a két cellában lévő vesszővel elválasztott értékeket, és visszaadni a közöttük lévő ismétlődő vagy egyedi értékeket.
Megjegyzések: Csak a következő képletek működnek Excel 365-höz. Ha az Excel más verzióit használja, próbálja meg az alábbi VBA módszert használni.
Vegyük példának a fenti két oszlopot az 1. oszlop vesszővel tagolt számainak összehasonlításához a 2. oszlop ugyanazon sorában lévő vesszővel tagolt számokkal, és ismétlődő vagy egyedi értékeket ad vissza, kérjük, tegye a következőket.
Ismétlődő értékek visszaadása
1. Jelöljön ki egy cellát a két megadott cella közötti ismétlődő számok vesszővel elválasztott számokkal történő kiadásához, ebben az esetben kiválasztom a D2 cellát, majd beírom az alábbi képletet, és megnyomom a belép kulcs. Jelölje ki a képletcellát, és húzza át AutoFill fogantyú lefelé, hogy a többi sor cellái között ismétlődő számok jelenjenek meg.
=LET(x, TRANSPOSE(TEXTSPLIT(TEXTJOIN(", ",TRUE,A2:B2), ", ")),y,UNIQUE(x),z,UNIQUE(x,,1), TEXTJOIN(", ",TRUE,IF(ISERROR(MATCH(y,z,0)),y, "")))
Egyedi értékeket ad vissza
A következő képlet segíthet abban, hogy a két megadott cella közötti egyedi számokat ugyanabban a sorban vesszővel elválasztott számokkal adja vissza.
1. Válasszon ki egy cellát az egyedi számok kiadásához, ebben az esetben kiválasztom az E2 cellát, majd beírom az alábbi képletet és megnyomom a belép kulcs. Jelölje ki a képletcellát, és húzza át AutoFill fogantyú lefelé az egyedi számok megjelenítéséhez a többi sor cellái között.
=TEXTJOIN(", ",TRUE,UNIQUE(TRANSPOSE(TEXTSPLIT(TEXTJOIN(", ",TRUE,A2:B2), ", ")),,1))
Megjegyzések:
Hasonlítson össze két oszlopot vesszővel elválasztott értékekkel, és adja vissza az ismétlődő vagy egyedi értékeket a VBA segítségével
Az ebben a szakaszban megadott, felhasználó által definiált függvény segít összehasonlítani a két megadott cellában lévő vesszővel elválasztott értékeket, és visszaadni a köztük lévő ismétlődő vagy egyedi értékeket. Kérjük, tegye a következőket.
Vegyük a fenti példát az 1. oszlop vesszővel elválasztott számainak összehasonlításához a 2. oszlop ugyanazon sorában lévő vesszővel tagolt számokkal, és ismétlődő vagy egyedi értékeket ad vissza, próbálja meg ebben a részben a felhasználó által definiált függvényt.
1. A nyitó munkafüzetben nyomja meg a gombot más + F11 billentyűk megnyitásához Microsoft Visual Basic for Applications ablak.
2. Ban,-ben Microsoft Visual Basic for Applications ablakban kattintson betétlap > Modulok, és másolja be a következő VBA-kódot a Modul (kód) ablak.
VBA-kód: Hasonlítsa össze a vesszővel elválasztott értékeket két cellában, és adja vissza az ismétlődő/egyedi értékeket
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. A kód beillesztése után a Modul (kód) ablakban kattintson a gombra Eszközök > Referenciák megnyitni Referenciák - VBAProject ablakban ellenőrizze a Microsoft Scripting Runtime majd kattintson a OK gombot.
4. megnyomni a más + Q gombok a Microsoft Visual Basic for Applications ablak.
5. Most két függvényt külön kell alkalmaznia, hogy visszaadja a duplikált és egyedi értékeket két vesszővel elválasztott értékcellából.
Ismétlődő érték visszaadása
Válasszon ki egy cellát a duplikált számok kiadásához, ebben a példában kiválasztom a D2 cellát, majd beírom az alábbi képletet, és megnyomom a belép gombot az A2 és B2 cella közötti ismétlődő számok megjelenítéséhez.
Jelölje ki a képletcellát, és húzza le az Automatikus kitöltési fogantyút, hogy a többi sor cellája között ismétlődő számok jelenjenek meg.
=COMPARE(A2,B2,TRUE)
Egyedi értékeket ad vissza
Válasszon ki egy cellát az egyedi számok kiadásához, ebben a példában kiválasztom az E2 cellát, majd beírom az alábbi képletet, és megnyomom a belép kulcsot az A2 és B2 cella közötti egyedi számok megjelenítéséhez.
Jelölje ki a képletcellát, és húzza le az Automatikus kitöltési fogantyút, hogy a többi sor cellája között egyedi számok jelenjenek meg.
=COMPARE(A2,B2,FALSE)
A legjobb irodai hatékonyságnövelő eszközök
Töltsd fel Excel-készségeidet a Kutools for Excel segítségével, és tapasztald meg a még soha nem látott hatékonyságot. A Kutools for Excel több mint 300 speciális funkciót kínál a termelékenység fokozásához és az időmegtakarításhoz. Kattintson ide, hogy megszerezze a leginkább szükséges funkciót...
Az Office lap füles felületet hoz az Office-ba, és sokkal könnyebbé teszi a munkáját
- Füles szerkesztés és olvasás engedélyezése Wordben, Excelben és PowerPointban, Publisher, Access, Visio és Project.
- Több dokumentum megnyitása és létrehozása ugyanazon ablak új lapjain, mint új ablakokban.
- 50% -kal növeli a termelékenységet, és naponta több száz kattintással csökkenti az egér kattintását!
