Két oszlopot hasonlítson össze az Excel egyezései és különbségei között
Ez az oktatóanyag arról beszél, hogyan lehet összehasonlítani az Excel két oszlopát, ami mindennapi munkánk során normális Excel-feladat. Két oszlop összehasonlítása különböző módon történhet az Excel programban, például két oszlop egymás utáni összehasonlítása, vagy cellák cellák szerinti összehasonlítása, két oszlop összehasonlítása az egyezések vagy különbségek kiemeléséhez, stb. Ez a cikk a két oszlop összehasonlításának lehetséges forgatókönyveit ismerteti. találkozni, és remélem, hogy ez segíthet.
Megjegyzések
Ebben az oktatóanyagban néhány példát ad a két oszlop összehasonlításának módszereinek jobb megmagyarázásához különböző esetekben. Adatkészlete alapján lehet, hogy módosítania vagy módosítania kell néhány tartalmat (hivatkozást), azonban az alapelvek változatlanok maradnának. Vagy közvetlenül töltse le az egyes esetek mintáit, ha csak azt szeretné ellenőrizni, hogy a módszerek működnek-e vagy sem.
Az alábbiakban egy adathalmaz található, ahol ugyanabban a sorban kell ellenőriznem, hogy az A oszlopban szereplő nevek megegyeznek-e a B oszlopban szereplő nevekkel vagy sem.
Kattintson a minta fájl letöltéséhez
1.1 Hasonlítsa össze az ugyanazon sor celláit, hogy pontosan egyezzenek
Általában, ha két oszlopot egymás után szeretne összehasonlítani a pontos egyezés érdekében, használhatja az alábbi képletet:
nyomja meg belép gombot, és húzza a kitöltő fogantyút a D8 cellába. Ha a képlet visszaadja az IGAZ értéket, két oszlop értéke teljesen megegyezik, ha a HAMIS értéket adja vissza, akkor azok eltérnek.
Ha két oszlopot szeretne egymás után összehasonlítani a kis- és nagybetűk megkülönböztetéséhez, vagy további leírást szeretne kapni, például Match, Mismatch, használhatja az IF függvényt.
Az azonos sorban lévő cellák összehasonlítása a pontos egyezés érdekében
Ha az „Összehasonlítás” és a „Mismatch” szövegeket szeretné használni az összehasonlító eredmények leírására, használja az alábbi képletet:
nyomja meg belép gombot az első eredmény eléréséhez, majd húzza az automatikus kitöltő fogantyút a D8 cellába.
Az azonos sorban lévő cellák összehasonlítása a kis- és nagybetűk közötti egyezéshez
Ha összehasonlítani szeretné a cellákat a kis- és nagybetűk szempontjából, használhatja az alábbi képletet:
nyomja meg belép gombot az első eredmény eléréséhez, majd húzza az automatikus kitöltő fogantyút az E8 cellába.
Megjegyzés
A fenti képletekben megváltoztathatja a „Match” és a „Mismatch” szöveget saját leírására.
Ha ki szeretné emelni az egyező vagy különböző értékeket, akkor a Feltételes formázás funkció segíthet.
1. Jelölje ki a két oszlopot, amelyekhez összehasonlítani szokott (B2: C8, az oszlopfejlécek kivételével), majd kattintson Kezdőlap > Feltételes formázás > Új szabály.
2. A durranásban Új formázási szabály párbeszédpanelen kattintson a kiválasztáshoz Képlet segítségével határozza meg, hogy mely cellákat formázza a Válasszon egy szabálytípust szakasz, majd írja be = $ B2 = $ C2 a szövegmezőbe Formázza az értékeket, ahol ez a képlet igaz.
3. Most kattintson kialakított megjeleníteni a Cellák formázása párbeszédpanelen, majd a alatt Tölt lapon válasszon egy színt, amelyre szüksége van a mérkőzések kiemeléséhez.
Vagy megváltoztathatja a betűméretet, a betűméretet, a cellahatárokat vagy a számformátumot, hogy a többi lapon szükség szerint felülmúlja az egyezést.
4. kettyenés OK > OK a párbeszédablakok bezárásához az azonos sorban lévő cellák kiemelésre kerülnek, ha azonosak.
Ha ki akarja emelni az eltérési értékeket, használhatja ezt a = $ B2 <> $ C2 be a Formázza az értékeket, ahol ez a képlet igaz szövegmező a A formázási szabály szerkesztése párbeszéd.
Ezután ugyanazon sor két oszlopának különbségeit egy meghatározott színnel emeljük ki.
1.4 Két oszlop összehasonlítása soronként, és az eltérési értékek kiemelése (VBA használatával)
Ha két oszlopot szeretne egymás után összehasonlítani a VBA kóddal, akkor ez az oktatóanyag kielégíti Önt.
1. Engedélyezze az összehasonlításhoz használt két oszlopot tartalmazó munkalapot, nyomja meg a gombot Alt + F11 gombok a Microsoft Visual Basic for Applications ablak.
2. A felbukkanó párbeszédpanelen kattintson a gombra betétlap > Modulok.
3. Ezután másolja be és illessze be a makró alá az új modul szkriptjébe.
VBA: Hasonlítson egymás után két oszlopot, és emelje ki a különbségeket
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. nyomja meg F5 gombot a kód futtatásához, majd megjelenik egy párbeszédpanel a két oszlop kiválasztásához.
5. kettyenés OK. Ezután két oszlop különbségeit háttérszínnel emeltük ki.
Megjegyzés
Megváltoztathatja a kiemelő színt saját igényei alapján, ha megváltoztatja a kód színindexét, a Színindex hivatkozást:
2． Hasonlítson össze két oszlopot a cellákban, és jelöljön ki vagy jelöljön ki ismétlődő vagy egyedi adatokat
Ebben a részben az adatkészlet az alábbiak szerint jelenik meg, és meg szeretné találni az összes értéket, amelyek egyszerre szerepelnek mind a B, mind a C oszlopban, vagy csak a B oszlopban találhatók.
Kattintson a minta fájl letöltéséhez
Itt használhatja az IF és a COUNTIF függvényekkel kombinált képletet két oszlop összehasonlításához és a B oszlopban, de a C oszlopban nem szereplő értékek megkereséséhez.
nyomja meg belép gombot, és húzza az automatikus kitöltő fogantyút a D8 cellába.
Megjegyzés
1. Ez a képlet két oszlopot hasonlít össze kis- és nagybetűk nélkül.
2. A „No in C” és az „Yes in C” leírást megváltoztathatja másokra.
3. Ha két teljes oszlopot szeretne összehasonlítani, módosítsa a $ C $ 2: $ C $ 8 rögzített tartományt $ C: $ C értékre.
2.2. Ismétlődő vagy egyedi adatok összehasonlítása és kiemelése (feltételes formázás használatával)
A Feltételes formázás Az Excel szolgáltatás hatékony, itt két oszlop cellánként történő összehasonlításához használhatja, majd szükség szerint kiemelheti a különbségeket vagy az egyezéseket.
Jelölje ki az összes másolatot vagy egyedi értéket két oszlopban
1. Jelöljön ki két oszlopot, amelyekkel összehasonlítani fogja, majd kattintson Kezdőlap > Feltételes formázás > Jelölje ki a Cellák szabályait > Ismétlődő értékek.
2. A durranásban Ismétlődő értékek párbeszédpanelen válasszon ki egy kiemelési formátumot a kívánt értékek legördülő listájából.
3. kettyenés OK. Ezután a két oszlopban található másolatok kiemelésre kerültek.
Megjegyzés
Ha két oszlopban szeretné kiemelni az egyedi értékeket (a különbségeket), kattintson a gombra is Kezdőlap > Feltételes formázás > Jelölje ki a Cellák szabályait > Ismétlődő értékek megjeleníteni a Ismétlődő értékek párbeszédpanelen módosítsa a Duplicate nak nek Egyedülálló a bal oldali legördülő listában, majd válasszon egy másik formátumot a legördülő lista értékei közül, kattintson a gombra OK.
Az egyedi értékek kiemelésre kerülnek.
Keresse meg és emelje ki az értékeket a B oszlopban, ha azok a C oszlopban is szerepelnek
Ha ki szeretné emelni a B oszlop azon értékeit, amelyek szintén a C oszlopban vannak, akkor a Ctovábbi formázás funkció is tehet egy szívességet.
1. Válassza ki a B2: B8 oszlopot, kattintson a gombra Kezdőlap > Feltételes formázás > Új szabály.
2. Ban,-ben Új formázási szabály párbeszédablakban válassza ki Képlet segítségével határozza meg, hogy mely cellákat formázza tól Válasszon egy szabálytípust szakasz, majd írja be = COUNTIF ($ C $ 2: $ C $ 8, $ B2)> 0 a szövegmezőbe Formázza az értékeket, ahol ez a képlet igaz.
3. kattintson kialakított menni Cellák formázása párbeszéd, alatt Tölt lapon válasszon egy színt az egyezések kiemeléséhez.
Más formátumokkal is kiemelheti az értékeket a Betűtípus, Szám, Határ füleken.
4. kettyenés OK > OK. Ezután a B. oszlopban a C oszlopban is létező értékeket kiemelték meghatározott színnel.
Ha ki szeretné emelni azokat az értékeket, amelyek csak a B oszlopban, de a C oszlopban nem, ismételje meg a fenti lépéseket, de a 2. lépésben szereplő képletet módosítsa = COUNTIF ($ C $ 2: $ C $ 8, $ B2) = 0 a Új formázási szabály párbeszéd.
Ezután válasszon egy másik színt az értékek formázásához.
Megjegyzés
Itt a Feltételes formázás két oszlopot hasonlít össze kis- és nagybetűk nélkül.
Néha két oszlop összehasonlítása után más műveleteket hajthat végre a mérkőzésekkel vagy a különbségekkel kapcsolatban, például kijelöléssel, törléssel, másolással stb. Ebben az esetben egy praktikus eszköz - Válassza az Ugyanaz és különböző cellák lehetőséget of Kutools for Excel közvetlenül kiválaszthatja a mérkőzést vagy a különbséget a következő művelet jobb elvégzése érdekében, és közvetlenül kiemelheti az értékeket is.
Után ingyenes telepítés Kutools for Excel, kérjük, tegye az alábbiak szerint:
1. kettyenés Kutools > választ > Válassza az Ugyanaz és különböző cellák lehetőséget.
2. Ezután a Válassza az Ugyanaz és különböző cellák lehetőséget párbeszédpanelt, kérjük, tegye az alábbiak szerint:
1) A Keressen értékeket és Szerint szakaszokat, válasszon két oszlopot, amelyekkel összehasonlítani kívánja;
2) Válasszon Minden sor választási lehetőség;
3) Válasszon Ugyanazok az értékek or Különböző értékek amire szüksége van;
4) Ha ki akarja emelni az értékeket, válassza ki a kívánt színt.
3. kettyenés Ok, megjelenik egy párbeszédpanel, amely emlékeztet a megtalált értékek számára, kattintson a gombra OK a párbeszéd bezárásához. Ugyanakkor kiválasztották az értékeket, most törölheti vagy másolhatja, vagy más műveleteket végezhet.
Ha ellenőrzi a Töltse ki a háttérszínt és Töltse ki a betű színét jelölőnégyzeteket, az eredmény a következőképpen jelenik meg:
Megjegyzés
Ha összehasonlítani szeretné a kis- és nagybetűk megkülönböztetését, ellenőrizze a Kis-nagybetű érzékeny opciót.
Ez az eszköz támogatja a két munkalap két oszlopának összehasonlítását.
Ha egy másik oszlopban szeretné felsorolni az egyező értékeket, miután két oszlopot cellánként összehasonlított, itt az alábbi makrókód segíthet.
1. Engedélyezze a két oszlop összehasonlításához használni kívánt lapot, majd nyomja meg a gombot Alt + F11 gombok a Microsoft Visual Basic for Applications ablak.
2. kettyenés betétlap > Modulok a Microsoft Visual Basic for Applications ablak.
3. Ezután másolja és illessze be a kódot az új üres modul szkriptbe.
VBA: Két oszlop összehasonlítása után soroljon fel duplikátumokat az oszlop mellé
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. nyomja meg F5 gombot a kód futtatásához, két párbeszédpanel jelenik meg egyenként, hogy két oszlopot külön válasszon.
Megjegyzések: Először válassza ki a bal oldali oszlopot, majd válassza a jobb oldali oszlopot a második párbeszédpanelen, különben a másolatok helyettesítik az eredeti adatokat a második oszlopban.
5. kettyenés OK > OK, akkor a mérkőzések automatikusan megjelennek a két oszlop jobb oszlopában.
Megjegyzés
A VBA kód két oszlopot hasonlít össze a kis- és nagybetűkkel.
2.5 Két oszlop összehasonlítása és a duplikátumok kiemelése (VBA kód használatával)
Ha két oszlopot cellánként szeretne összehasonlítani, majd kiemeli az egyezéseket, akkor kipróbálhatja az alábbi kódot.
1. Engedélyezze a két oszlop összehasonlításához használni kívánt lapot, majd nyomja meg a gombot Alt + F11 gombok a Microsoft Visual Basic for Applications ablak.
2. kettyenés betétlap > Modulok a Microsoft Visual Basic for Applications ablak.
3. Másolja a makrókód alá, és illessze be az új üresbe Modulok szkripteket.
VBA: Hasonlítson össze két oszlopot, és emelje ki a duplikátumokat
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. nyomja meg F5 gombot a kód futtatásához. Az első felbukkanó párbeszédpanelen válassza ki azt az oszlopot, amely szerint összehasonlítani kívánja az ismétlődő értékeket.
5. kettyenés OK. A második párbeszédpanelen válassza ki azt az oszlopot, amelyben ki szeretné emelni az ismétlődő értékeket.
6. kettyenés OK. Most a második oszlopban található másolatokat háttérszínnel emeltük ki, miután összehasonlítottuk az első oszloppal.
Megjegyzés
1. A kód összehasonlítja az oszlopokat a kis- és nagybetűkkel.
2. Megváltoztathatja a kiemelő színt saját igényei alapján, ha megváltoztatja a kód színindexét, a Színindex hivatkozást:
Előfordulhat, hogy egynél több oszlopot szeretne összehasonlítani ugyanabban a sorban, például az alábbi képernyőképen látható adatkészletet. Itt, ebben a szakaszban, különféle módszereket sorol fel több oszlop összehasonlítására.
Kattintson a minta fájl letöltéséhez
3.1 Keressen teljes egyezéseket ugyanabban a sorban az összes cellában (IF képlet segítségével)
Ha teljes egyezéseket szeretne találni ugyanazon sor oszlopai között, az alábbi IF képlet segíthet.
Ha ugyanazon sor cellái egyeznek egymással, akkor a „Teljes egyezés” vagy a „Nem” felirat jelenik meg.
nyomja meg belép gombot az első összehasonlító eredmény megszerzéséhez, majd húzza az automatikus kitöltő fogantyút az E7 cellába.
Megjegyzés
1. A képlet összehasonlítja az oszlopokat kis- és nagybetűk nélkül.
2. Ha három vagy több oszlopot kell összehasonlítania, használhatja az alábbi képletet:
A képletben a 3 az oszlopok száma, az igényeinek megfelelően módosíthatja.
3.2 Megtalálja az egyező bármely két cellájában az egyezést (IF képlet segítségével)
Előfordulhat, hogy meg szeretné tudni, hogy egyazon sor két oszlopa egyezik-e, használhatja az IF képlet alatt.
Ebben a képletben össze kell hasonlítania az azonos sor bármely cellapárját. Az „Egyezés” azt jelzi, hogy van két egyező cella, ha nincs egyező cella, akkor a képlet a „Nem” feliratot jeleníti meg, a szövegeket szükség szerint módosíthatja.
nyomja meg belép gombot, és húzza át a kitöltő fogantyút az E7 cellába.
Megjegyzés
1. Ez a képlet nem támogatja a kis- és nagybetűket.
2. Ha sok oszlopra van szükség az összehasonlításhoz, a képletben bármely cellapár összehasonlítása túl hosszú lehet. Ebben az esetben fontolóra veheti az alábbi képlet használatát, amely egyesíti az IF és a COUNTIF függvényeket.
Ha ki akarja emelni azokat a sorokat, amelyeknek minden cella megfelel egymással, használhatja a Feltételes formázás funkció az Excelben.
1. Válassza ki a használt tartományt, majd kattintson a gombra Kezdőlap > Feltételes formázás > Új szabály.
2. Ban,-ben Új formázási szabály párbeszédablakban válassza ki Képlet segítségével határozza meg, hogy mely cellákat formázza tól Válasszon egy szabálytípust szakaszban, akkor az alábbi képletek egyikét használhatja a Formázza az értékeket, ahol ez a képlet igaz szövegdoboz.
Or
Megjegyzések: Ha az oszlopok száma meghaladja a három értéket, tegyük fel, hogy 5, a képleteket a következőkre kell cserélni:
Or
3. kettyenés kialakított gombbal léphet a Cellák formázása párbeszédpanelen, majd válasszon egy kitöltési színt vagy más cellaformázást a sorok megjelenítéséhez.
4. kettyenés OK > OK, most csak azok a sorok jelennek meg, amelyeken belül az összes cella egyezik.
Megjegyzés
A fenti képletek nem támogatják a kis- és nagybetűk megkülönböztetését.
3.4 Hasonlítson össze több oszlopot, és emelje ki a sorok különbségeit
Ha ki akarja emelni a sorkülönbségeket, ami azt jelenti, hogy egyesével összehasonlítja az oszlop cellákat, és az első oszlopnak megfelelően megtalálja a különböző cellákat, használhatja az Excel beépített szolgáltatását-Ugrás a különlegességre.
1. Jelölje ki azt a tartományt, amelyen kiemelni kívánja a sorkülönbségeket, majd kattintson a gombra Kezdőlap > Keresés és kiválasztás > Ugrás a különlegességre.
2. A durranásban Ugrás a különlegességre párbeszédpanelen ellenőrizze Sorkülönbségek opciót.
3. kettyenés OK. Most a sorkülönbségeket választottuk ki.
4. Most tartsa a cellákat kijelölve, kattintson Kezdőlap > Kitöltési szín egy szín kiválasztásához a legördülő menüből.
Megjegyzés
Ez a módszer összehasonlítja a cellákat kis- és nagybetűk nélkül.
Feltéve, hogy két oszlop van, a B oszlop hosszabb és a C oszlop rövidebb, ahogy az alábbi képernyőképen látható. A B oszlophoz képest hogyan lehet megtudni a C oszlop hiányzó adatait?
Kattintson a minta fájl letöltéséhez
4.1 Hasonlítsa össze és keresse meg a hiányzó adatpontokat (VLOOKUP vagy MATCH képlet segítségével)
Ha csak két oszlop összehasonlítása után szeretné meghatározni, hogy mely adatok hiányoznak, használhatja az alábbi képletek egyikét:
Or
nyomja meg belép gombot, majd húzza az automatikus kitöltő fogantyút a D10 cellára. Ha az adatok mind a B, mind a C oszlopban vannak, a képlet HAMIS értéket ad vissza, ha az adatok csak a B oszlopban vannak, de hiányoznak a C oszlopból, akkor a képlet IGAZ.
Megjegyzés
Két képlet felett az adatok összehasonlítása kis- és nagybetűk nélkül.
Ha a hiányzó adatokat két oszlop összehasonlítása után a rövidebb oszlop alatt szeretné felsorolni, akkor az INDEX tömbképlet segíthet.
A rövidebb oszlop alábbi cellájában tegyük fel, hogy a C7 cella írja be az alábbi képletet:
nyomja meg Shift + Ctrl + Enter gombot az első hiányzó adatok megszerzéséhez, majd húzza lefelé az automatikus kitöltő fogantyút, amíg vissza nem adja a # N / A hibaértéket.
Ezután eltávolíthatja a hiba értékét, és az összes hiányzó adatot felsorolta a rövidebb oszlop alatt.
Megjegyzés
Ez a képlet összehasonlítja a cellákat kis- és nagybetűk nélkül.
Ha két oszlop összehasonlítása után szeretne nyomon követni a hiányzó adatokat, például felsorolni a hiányzó adatokat egy másik oszlopba, vagy kiegészíteni a hiányzó adatokat a rövidebb oszlop alatt, megpróbálhat egy praktikus eszközt-Válassza az Ugyanaz és különböző cellák lehetőséget of Kutools for Excel.
1. kettyenés Kutools > választ > Válassza az Ugyanaz és különböző cellák lehetőséget.
2. Ban,-ben Válassza az Ugyanaz és különböző cellák lehetőséget párbeszédpanelt tegye az alábbiak szerint:
1) In Keressen értékeket itt szakaszban válassza ki a hosszabb oszlopot, amely tartalmazza a teljes listát.
In Szerint szakaszban válassza ki a rövidebb oszlopot, amelyből hiányzik néhány adat.
2) Válasszon Minden sor opciót.
3) Válasszon Különböző értékek opciót.
3. kettyenés Ok, megjelenik egy párbeszédpanel, amely emlékeztet a hiányzó adatok számára, kattintson a gombra OK hogy bezárja. Ezután kiválasztották a hiányzó adatokat.
Most megnyomhatja Ctrl + C gombokat a kiválasztott hiányzó adatok másolásához, majd a gombbal illessze be őket Ctrl + V gombokat a rövidebb oszlop vagy egy másik új oszlop alatt, amire szüksége van.
Megjegyzés
Ketyeg a Kis- és nagybetűk Az azonos és különböző cellák kiválasztása párbeszédpanelen két oszlopot összehasonlít a kis- és nagybetűkkel.
4.4 Két oszlop összehasonlítása és a hiányzó adatok felsorolása az alábbiakban (a VBA használatával)
Itt van egy makrókód, amely kitöltheti a hiányzó adatokat a két oszlop alatt.
1. Nyissa meg a használt lapot, nyomja meg a gombot Alt + F11 billentyűk megnyitásához Microsoft Visual Basic for Applications ablak.
2. kettyenés betétlap > Modulok létrehozni egy új modult a VBA kód alá.
VBA: Két oszlop összehasonlítása és a hiányzó adatok kitöltése
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. Ezután nyomja meg a gombot F5 kulcs a kód futtatásához, megjelenik egy párbeszédpanel a két összehasonlító oszlop kiválasztásához.
4. kettyenés OK. Most a hiányzó adatokat felsoroltuk a két oszlop alatt.
Megjegyzés
A kód összehasonlítja a cellákat kis- és nagybetűk nélkül.
Ha két dátumoszlop van, az alábbi képernyőképen látható, érdemes összehasonlítani, hogy melyik dátum van később ugyanabban a sorban.
Kattintson a minta fájl letöltéséhez
5.1 Két oszlop összehasonlítása, ha nagyobb vagy kisebb (képlet segítségével)
Az egyszerű képlettel gyorsan megállapíthatja, hogy az 1. dátum minden sorban későbbi-e, mint a 2. dátum.
nyomja meg belép gombot az első összehasonlított eredmény eléréséhez, majd az összes eredmény eléréséhez húzza az automatikus kitöltő fogantyút a C6 cellába.
Megjegyzés
1. Az Excelben a dátumokat számsorozatként tárolják, valójában számok. Ezért a dátum közvetlen összehasonlításához alkalmazza a képletet.
2. Ha összehasonlítani kívánja, hogy az 1. dátum minden sorban korábbi-e a 2. dátumnál, változtassa meg a szimbólumot > nak nek < a képletben.
Ha az 1. dátum oszlop celláit szeretné kiemelni, ha azok nagyobbak, mint a 2. dátum, használhatja a Feltételes formázás funkció az Excelben.
1. Válassza ki a dátumokat a B oszlopban (Dátum1), majd kattintson Kezdőlap > Feltételes formázás > Új szabály.
2. Ban,-ben Új formázási szabály párbeszédablakban válassza ki Képlet segítségével határozza meg, hogy mely cellákat formázza a Válasszon egy szabálytípust szakasz, majd írja be a képletet = $ B2> $ C2 a szövegmezőbe Formázza az értékeket, ahol ez a képlet igaz.
Ha ki akarja emelni a B oszlop azon celláit, amelyek kisebbek, mint a C oszlop cellái, használja a képletet = $ B2 <$ C2.
3. kettyenés kialakított gomb megnyomásával Cellák formázása párbeszédpanelt, majd válassza ki a kívánt formátumot.
4. kettyenés OK > OK. Ezután a Date1 oszlop azon celláit emelte ki, amelyek nagyobbak, mint a Date2 oszlop cellái.
Például két táblázat van, most össze akarja hasonlítani a B és az E oszlopot, majd keresse meg a relatív árakat a C oszlopból, és adja vissza az F oszlopba.
Kattintson a minta fájl letöltéséhez
6.1 Húzza le a pontos egyezési adatokat (képlet vagy praktikus eszköz segítségével)
Itt bemutat néhány hasznos képletet és eszközt a feladat megoldására.
Formula módszer
Az F2 cellában (abban a cellában, ahová a visszaadott értéket be akarja helyezni) használja az alábbi képletek egyikét:
Or
nyomja meg belép kulcsot, és az első érték megtalálható. Ezután húzza az automatikus kitöltő fogantyút lefelé az F6 celláig, minden érték kibontásra került.
Megjegyzés
1. A képletek nem támogatják a kis- és nagybetűket.
2. A képletben szereplő 2. szám azt jelzi, hogy az egyező értékeket megtalálja a táblázat tömb második oszlopában.
3. Ha a képletek nem találják meg a relatív értéket, akkor a # N / A hibaértéket adja vissza.
Egy praktikus eszköz módszer
Ha összetévesztette a képletekkel, kipróbálhatja a praktikus eszközt - Formula Segítő of Kutools for Excel, amely több képletet tartalmaz az Excel legtöbb problémájának megoldására. Ezzel csak ki kell választania a tartományt, de nem kell emlékeznie a képletek használatára.
Után ingyenes telepítés Kutools for Excel, kérjük, tegye az alábbiak szerint:
1. Jelölje ki az F2 cellát (azt a cellát, amelybe vissza szeretné állítani a visszatérési értéket), majd kattintson Kutools > Formula Segítő > Keresés és referencia > Keressen egy értéket a listában.
2. Ban,-ben Képletek segítője párbeszédpanel, jobbra Érvek bevitele szakaszban válassza ki a tábla tömbtartományát, a keresési értéket, és adja meg, hogy melyik oszlopban keresse meg a visszatérési értéket.
típus: Ne felejtse el megváltoztatni az abszolút referenciát a Keresési_érték négyzetet a rokonhoz, vagy csak az első értéket találja meg.
3. kettyenés Ok, az első értéket megtaláltuk, majd húzzuk le az automatikus kitöltő fogantyút az F6 cellába.
6.2 Húzza meg a részleges egyezési adatokat (képlet segítségével)
Ha van néhány kisebb különbség a két összehasonlított oszlop között, amint az az alábbi képernyőképen látható, a fenti módszerek nem működhetnek.
A feladat megoldásához válassza az alábbi képletek egyikét:
Or
nyomja meg belép gombot, majd húzza le az automatikus kitöltő fogantyút az F5 celláig, az összes érték megtalálható.
Megjegyzés
1. A képletek nem támogatják a kis- és nagybetűket.
2. A képletben szereplő 2. szám azt jelzi, hogy a visszatérési értékeket megtalálja a táblázat tömbjének második oszlopában.
3. Ha a képletek nem találják meg a relatív értéket, akkor a # N / A hibaértéket adja vissza.
4. A képletben * egy helyettesítő karakter, amely bármilyen karaktert vagy karakterláncot jelöl.
Az adatkészlet alatt egy példa az egyezések vagy különbségek összehasonlítására és számolására.
Kattintson a minta fájl letöltéséhez
7.1 Két oszlop összehasonlítása és egyezések számlálása (a SUMPRODUCT képlet segítségével)
A SUMPRODUCT képlet két oszlopban gyorsan meg tudja számlálni az egyezéseket.
nyomja meg belép kulcs az eredmény eléréséhez.
Megjegyzés
A képlet a kis- és nagybetűk megkülönböztetése nélkül számolja a cellákat.
Ha meg szeretné számolni a két oszlop közötti egyezéseket vagy különbségeket, akkor a praktikus eszköz - Válassza az Ugyanaz és különböző cellák lehetőséget.
Után ingyenes telepítés Kutools for Excel, kérjük, tegye az alábbiak szerint:
1. kettyenés Kutools > választ > Válassza az Ugyanaz és különböző cellák lehetőséget.
2. Ban,-ben Válassza az Ugyanazok és a különbség cellák lehetőséget párbeszédpanelen válassza ki a két oszloptartományt Keressen értékeket itt és Szerint szakaszokat külön, majd válassza Minden sor, és válasszon Ugyanazok az értékek or Különböző értékek opció, amire szüksége van.
3. kettyenés Ok. Egy párbeszédablak jelenik meg, amely megmondja, hogy hány egyező vagy különböző cella van kijelölve.
Cellák egyeztetése
Különböző sejtek
Tegyük fel, hogy itt a B. oszlopban található adatok listája, és meg szeretné számolni azokat a cellákat, amelyek a D oszlopban tartalmazzák az „Apple” vagy „Candy” elemet, az alábbi képernyőkép szerint:
Kattintson a minta fájl letöltéséhez
Ha meg szeretné számolni, hogy egy cella tartalmaz-e egy vagy több értéket, akkor a probléma megoldásához helyettesítő karakterekkel ellátott képletet használhat.
nyomja meg Shift + Ctrl + Enter gombot az első ellenőrzés elvégzéséhez, majd húzza le az automatikus kitöltő fogantyút az F8 cellába.
Ha a kapcsolódó cella egy vagy több értéket tartalmaz a D oszlopban, az eredmény 0-nál nagyobb számot jelenít meg, ha pedig nem tartalmaz értéket a D oszlopban, akkor 0-t ad vissza.
Ha meg akarja számolni a D oszlopban szereplő értékeket tartalmazó cellák teljes számát, használja az F8 cella alábbi képletét:
Megjegyzés
1. Használhatja a képletet arra is, hogy megszámolja, ha a cella tartalmaz értékeket egy másik oszlopban
Ezt a képletet csak meg kell nyomni belép gombot, majd húzza az automatikus kitöltő fogantyút.
2. A képletekben a * a helyettesítő karakter, amely bármilyen karaktert vagy karakterláncot jelöl.
Ha törölni szeretné a különbségeket vagy az egyezéseket, miután összehasonlította két oszlopot, az alábbi képernyőképen:
Kattintson a minta fájl letöltéséhez
A képlet segítségével először megtalálja a különbséget vagy az egyezéseket:
nyomja meg belép gombot, és húzza az automatikus kitöltő fogantyút a D8 cellába.
Ezután alkalmazza a Szűrő funkció Excel a különbségek vagy az egyezések értékének kiszűrésére.
Válassza ki a képlet oszlopot, majd kattintson a gombra dátum > Szűrő.
Ezután megjelenik a szűrő gomb a C oszlopban, kattintson a szűrő gombra a legördülő menü kibontásához, válassza a lehetőséget Különbség or Mérkőzés ahogy kell. Ezután kattintson a gombra OK befejezni.
Most csak a különbségeket vagy az egyezéseket szűrték ki. Kiválaszthatja őket, és megnyomhatja töröl gombot az eltávolításukhoz.
Most kattintson dátum > Szűrő ismét a szűrő törléséhez.
Távolítsa el a képlet oszlopot, ha már nincs rá szüksége.
Két oszlop van, az egyik az eredeti árakat, a másik az eladási árakat tartalmazza. Most ez a rész bemutat egy képletet e két oszlop összehasonlítására, majd a két oszlop közötti százalékos változás kiszámítására.
Kattintson a minta fájl letöltéséhez
Az alábbi képlet segítségével kiszámíthatja az ugyanazon sorban szereplő két ár közötti százalékos változást.
nyomja meg belép gombot a szám megszerzéséhez, majd húzza az automatikus kitöltő fogantyút a D7 cellába.
Ezután formázza a képlet eredményét százalékban. Válassza ki az eredményeket, kattintson a gombra Kezdőlap fülre, és lépjen a következőre: Szám csoportra kattintva Százalékos stílus.
A képlet eredményeit százalékban formázta.
Most már tudja, hogyan lehet két oszlopot összehasonlítani a fenti módszerek elolvasása után. Bizonyos esetekben érdemes két tartományt összehasonlítani (két sorozat több oszloppal). A fenti módszerekkel (képletek vagy feltételes formázás) oszloponként összehasonlíthatja őket, de itt bemutat egy hasznos eszközt - Kutools for Excel képletmentesen gyorsan megoldhatja ezt a feladatot különböző esetekben.
Kattintson a minta fájl letöltéséhez
11.1 Hasonlítson össze két tartományt cellánként
Itt van két tartomány, amelyet össze kell hasonlítani a cellák között, használhatja a Válassza az Ugyanaz és különböző cellák lehetőséget a Kutools for Excel segédprogram a kezeléséhez.
Után ingyenes telepítés Kutools for Excel, kérjük, tegye az alábbiak szerint:
1. kettyenés Kutools > választ > Válassza az Ugyanaz és különböző cellák lehetőséget.
2. A durranásban Válassza az Ugyanaz és különböző cellák lehetőséget párbeszédpanelt tegye az alábbiak szerint:
1) A Keressen értékeket itt szakaszban válassza ki azt a tartományt, amelynek két tartomány összehasonlítása után meg szeretné találni az egyezéseket vagy a különbségeket.
2) A Szerint szakaszban válassza ki az összehasonlított tartományhoz használt másik tartományt.
3) In Alapján szakaszban válassza Egyetlen cella.
4) Ezután a Találj szakaszban válassza ki a kijelölni vagy kiemelni kívánt cellatípust.
5) A Az eredmények feldolgozása szakaszban kiemelheti a cellákat kitöltési háttérszínnel vagy betűszínnel, ha nem szükséges kiemelni, ne jelölje be a jelölőnégyzeteket.
3. kettyenés Ok. Megjelenik egy párbeszédpanel, amely emlékezteti, hogy hány cellát / sort jelöltek ki, kattintson OK hogy bezárja. Most azokat a cellákat választotta ki és emelte ki, amelyek különböznek a másik tartomány celláitól.
Ugyanazon értékek kiemelése
Megjegyzés
Ha két tartományt szeretne összehasonlítani soronként, alkalmazhatja a Válassza az Ugyanaz és különböző cellák lehetőséget funkciót, de ebben az esetben válassza a Minden sor opciót.
11.2 Hasonlítson össze két tartományt, ha az adatok ugyanabban a sorrendben vannak
Ha két tartományt szeretne összehasonlítani soronként, akkor a Cellák összehasonlítása funkció a Kutools for Excel segíthet.
Után ingyenes telepítés Kutools for Excel, kérjük, tegye az alábbiak szerint:
Tegyük fel, hogy az F2: H7 tartomány modell, most meg szeretné találni, hogy a B2: D7 tartomány adatai megfelelő sorrendben vannak-e az F2: H7 tartomány szerint.
1. kettyenés Kutools > Hasonlítsa össze a cellákat.
2. Ban,-ben Hasonlítsa össze a cellákat párbeszédpanelt, állítsa be az alábbiak szerint:
1) Válassza ki a két tartományt a Keressen értékeket itt és Szerint dobozokat külön.
2) Válassza ki azt a cellatípust, amelyet ki szeretne emelni Találj szakasz.
3) Válassza ki a kiemelés típusát a Az eredmények feldolgozása szakasz.
3. kettyenés Ok. Megjelenik egy párbeszédpanel, amely emlékezteti, hogy hány cellát választott ki, kattintson a gombra OK hogy bezárja. Most azokat a cellákat választotta ki és emelte ki, amelyek különböznek a másik tartomány celláitól.
Ön is érdekelhet |
|
Hasonlítsa össze a dátumokat, ha az Excel más dátumnál nagyobb Hasonlítson össze két lapot egymás mellett Vlookup két lista összehasonlításához külön munkalapokon Ismétlődések keresése, kiemelése, szűrése, számlálása, törlése az Excelben |
A legjobb irodai termelékenységi eszközök
A Kutools for Excel megoldja a legtöbb problémát, és 80% -kal növeli a termelékenységet
- Szuper Formula Bár (könnyedén szerkeszthet több szöveget és képletet); Olvasás elrendezés (könnyen olvasható és szerkeszthető nagyszámú cella); Beillesztés a Szűrt tartományba...
- Cellák / sorok / oszlopok egyesítése és az adatok megőrzése; Osztott cellák tartalma; Kombinálja az ismétlődő sorokat és az Összeg / Átlagot... megakadályozza az ismétlődő cellákat; Hasonlítsa össze a tartományokat...
- Válassza a Másolat vagy az Egyedi lehetőséget Sorok; Válassza az Üres sorok lehetőséget (az összes cella üres); Super Find és Fuzzy Find sok munkafüzetben; Véletlenszerű kiválasztás ...
- Pontos másolás Több cella a képletreferencia megváltoztatása nélkül; Automatikus referenciák létrehozása több lapra; Helyezze be a golyókat, Jelölőnégyzetek és még sok más ...
- Kedvenc és gyorsan beszúrható képletek, Tartományok, diagramok és képek; Cellák titkosítása jelszóval; Levelezőlista létrehozása és e-maileket küldeni ...
- Kivonat szöveg, Szöveg hozzáadása, Eltávolítás pozíció szerint, Hely eltávolítása; Hozz létre és nyomtasson személyhívó részösszegeket; Konvertálás a cellatartalom és a megjegyzések között...
- Szuper szűrő (mentse el és alkalmazza a szűrősémákat más lapokra); Haladó rendezés hónap / hét / nap, gyakoriság és egyebek szerint; Speciális szűrő félkövér, dőlt betűvel ...
- Kombinálja a munkafüzeteket és a munkalapokat; Táblázatok egyesítése kulcsoszlopok alapján; Az adatok felosztása több lapra; Kötegelt konvertálás xls, xlsx és PDF...
- Pivot tábla csoportosítás hét száma, a hét napja és egyebek ... Mutassa a Feloldott, Zárt cellákat különböző színekkel; Jelölje ki azokat a cellákat, amelyeknek képlete / neve van...
- 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 minden nap több száz kattintással csökkenti az egér kattintását!
You are guest
or post as a guest, but your post won't be published automatically.
Be the first to comment.