Két Excel-lap összehasonlítása a másolatokhoz: 5 gyors módszer

Amikor nagy adatkészletekkel dolgozik az Excelben, gyakran előfordul, hogy több munkalap is tartalmaz hasonló adatokat. E lapok összehasonlítása ismétlődő rekordok esetén nagy kihívást jelenthet, különösen akkor, ha az adatok szétszórva vannak különböző oszlopokban és sorokban.

A leggyakoribb módszerek a duplikációk keresésére két Excel munkalapon a következők:

1. VLOOKUP, COUNTIF vagy EXACT függvények

2. Feltételes formázás

3. Power Query

4. Külső eszközök és bővítmények

5. Az ismétlődések vizuális ellenőrzése

Ez a cikk lépésről lépésre végigvezeti ezt az öt módszert a több munkalapon található ismétlődések meghatározásához és kezeléséhez. A végére látni fogja, milyen egyszerű az ismétlődő értékek kezelése.

Kezdjük el!

Tartalomjegyzék

• Minta munkalapok és adatok
• 1. VLOOKUP, COUNTIF vagy EXACT függvények használata az ismétlődések kereséséhez
  • V. Hogyan használhatja a VLOOKUP funkciót duplikátumok keresésére két lapon?
    • Mi van, ha különböző munkafüzeteket kezel?
  • B. Hogyan használja a COUNTIF függvényt a munkalapok ismétlődéseinek keresésére?
  • C. Hogyan használja az EXACT függvényt, hogy ismétlődéseket keressen a munkalapokon?
• 2. A feltételes formázás használata ismétlődő sorokhoz
  • A Feltételes formázási szabályok kezelőjének használata
• 3. A Power Query használata a munkalapok ismétlődéseinek keresésére
• 4. Eszközök és bővítmények a munkalapokon található ismétlődések azonosítására
• 5. Hogyan lehet vizuálisan ellenőrizni az ismétlődéseket két lapon
• 3 tipp az Excel-munkalapok elkészítéséhez
• A hibák és következetlenségek kezelése
• Végső gondolatok

Minta munkalapok és adatok

A cikkben szereplő példák követéséhez hozzon létre egy új Excel-munkafüzetet ezekkel az adatokkal az első munkalap A oszlopában:

• alma

• narancs

• Körte

• Eper

Írja be ezeket az adatokat a második lap A oszlopába:

• Körte

• Eper

• alma

• Ananász

A munkalapjai így néznek ki:

Miután beállította a munkalapokat, most áttekinthetjük a duplikátumok keresésének különböző módjait két munkalapon, kezdve a VLOOKUP, COUNTIF és EXACT függvényekkel.

Gyerünk!

1. VLOOKUP, COUNTIF vagy EXACT függvények használata az ismétlődések kereséséhez

Az Excel három beépített funkcióval rendelkezik, amelyek megkönnyítik a másolatok megtalálását: VLOOKUP , COUNTIF és EXACT .

Ezeket a funkciókat arra tervezték, hogy segítsenek megtalálni, megszámolni és összehasonlítani az adatokat a táblázatokban, így ideális eszközök az ismétlődő bejegyzések megtalálásához.

Mindegyik funkció egyedi célt szolgál, és használatuk megértése jelentősen leegyszerűsítheti az adatelemzési folyamatot.

A következő szakaszokban megvizsgáljuk, hogyan használhatjuk a VLOOKUP, COUNTIF és EXACT függvényeket az ismétlődések hatékony azonosítására az Excel-munkalapokon.

V. Hogyan használhatja a VLOOKUP funkciót duplikátumok keresésére két lapon?

A VLOOKUP a Vertical Lookup rövidítése. Ezzel a funkcióval két oszlop között ismétlődő értékeket kereshet. Ez a szintaxis:

=VLOOKUP(keresési_érték, táblázat_tömb, oszlop_index_száma, [tartomány_keresése])

• lookup_value : A keresni kívánt érték a table_array első oszlopában.

• table_array : A keresni kívánt adatokat tartalmazó cellák tartománya.

• col_index_num : A táblázat_tömb oszlopának száma, amelyből az értéket vissza kívánja adni.

• range_lookup : Nem kötelező. Ez vagy IGAZ (hozzávetőleges egyezés) vagy HAMIS (pontos egyezés). Az alapértelmezés TRUE.

Ha egy Excel-fájlban két munkalapon szeretné használni a függvényt, tudnia kell, hogyan hivatkozhat egy külön lapra egy képletben. Ehhez írja be a munkalap nevét, majd a felkiáltójelet (!) és a cellát vagy cellatartományt.

Például ez ugyanazon munkafüzet 2. lapjának A2–A5 celláira hivatkozik: 2. munkalap!$A$2:$A$5.

A VLOOKUP függvény használatához a mintatáblázatban kövesse az alábbi lépéseket:

1. Válassza ki a B2 cellát az első összehasonlítási eredmény megjelenítéséhez.

2. Írja be ezt a képletet: =VLOOKUP(A2,Sheet2!$A$2:$A$5, 1, FALSE).

3. Nyomja meg az Enter billentyűt az összehasonlítás eredményének megjelenítéséhez.

4. Töltse ki a képletet az első lap többi sorának értékeinek összehasonlításához.

Az eredmény az alábbi képen lesz:

A kijelzőt úgy teheti rendbe, hogy hibaüzenet helyett felhasználóbarát üzenetet jelenít meg, ha nem található másolat.

Például ez a képlet „Igen” vagy „Nem” jelenik meg a talált és a nem található értékek esetében:

=HA(ISNA(VLOOKUP(A2, 2. lap!$A$2:$A$5, 1, FALSE)), "Nem", "Igen")

A funkcióra további példák találhatók a címen található cikkünkben.

Mi van, ha különböző munkafüzeteket kezel?

Ha a munkalapok külön munkafüzetekben vannak, a függvényhasználat megegyezik két Excel-fájllal. A második munkalapra való hivatkozás azonban egy kicsit bonyolultabb.

Neked kell:

• Tegye zárójelbe az Excel-munkafüzet nevét

• Kövesse a munkalap nevét

• A munkafüzetet és a munkalapot tegye idézőjelek közé.

Például, ha a cellák a „WB 2.xlsx” nevű munkafüzet Lap2 nevű lapján találhatók, a formátum így fog kinézni:

'[WB 2.xlsx]Sheet2'!$A$2:$A$5

A képlet bevitele előtt zárja be a második munkafüzetet. Ellenkező esetben hibaüzenetet kap.

B. Hogyan használja a COUNTIF függvényt a munkalapok ismétlődéseinek keresésére?

Az Excel COUNTIF függvénye a megadott tartományon belüli, adott feltételeknek megfelelő cellák számának megszámlálására szolgál.

Több munkalap összehasonlításához megszámolhatja, hogy a második munkalap hány cellája egyezik az első munkalap cellájával.

Ez a függvény szintaxisa:

=COUNTIF(tartomány, feltételek)

• Tartomány : az a cellatartomány, amelyet a megadott feltételek alapján meg kíván számolni.

• Feltételek : az a feltétel, amelynek teljesülnie kell egy cella megszámlálásához.

A függvény mintaadatokkal való használatához kövesse az alábbi lépéseket:

1. Válassza ki a B2 cellát az első összehasonlítási eredmény megjelenítéséhez.

2. Írja be ezt a képletet: =COUNTIF(lap2!$A$2:$A$5, A2)

3. Nyomja meg az Enter billentyűt az összehasonlítás eredményének megjelenítéséhez.

4. Töltse ki a képletet az első lap többi sorának értékeinek összehasonlításához.

A függvény egyes cellákhoz egy egyezést fog találni, míg másoknak nem. Az összehasonlító cella a számlálást jeleníti meg. Íme a mintaadatok eredménye:

A COUNTIF függvény más hasznos feladatokhoz is használható, mint pl.

C. Hogyan használja az EXACT függvényt, hogy ismétlődéseket keressen a munkalapokon?

Az Excel EXACT funkciója arra is használható, hogy két különböző Excel munkalapon ugyanazon cellákon belüli ismétlődéseket keressen. A szintaxis a következő:

=PONTOS(szöveg1, szöveg2)

• text1 az első szöveges karakterlánc, amelyet összehasonlítani szeretne.

• text2 a második szöveges karakterlánc, amelyet összehasonlítani szeretne.

Kovesd ezeket a lepeseket:

1. Válassza ki a B2 cellát.

2. Írja be az =EXACT(A2, Sheet2!A2) képletet

3. Nyomja meg az Enter billentyűt az összehasonlítás eredményének megjelenítéséhez. A képlet IGAZ értéket ad vissza, ha mindkét érték azonos, vagy HAMIS értéket egyébként.

4. Töltse ki a képletet az első lap többi sorának értékeinek összehasonlításához.

Vegye figyelembe, hogy ez a módszer nem keres ismétlődéseket egy cellatartományban. Ehelyett csak az azonos cella alapján keres egyezéseket egy másik munkalapon.

Ez hasznos lehet rendezett adatoknál, ahol csak néhány kivételre számíthatunk.

A VLOOKUP, COUNTIF és EXACT függvények hasznosak az ismétlődések kereséséhez, de az Excel sokoldalú program, és vannak más lehetőségek is. A következő részben megvizsgáljuk, hogyan használhatja a feltételes formázást az ismétlődések azonosítására két lapon.

2. A feltételes formázás használata ismétlődő sorokhoz

Ebben a részben megtudhatja, hogyan használhatja a feltételes formázást az ismétlődő sorok megkeresésére és kiemelésére két Excel-munkalapon.

Feltételes formázási szabály létrehozásához kövesse az alábbi lépéseket:

1. Válassza ki az adatokat tartalmazó cellák tartományát (ebben az esetben A2:A5).

2. Kattintson a „Kezdőlap” fülre az Excel szalagon.

3. Kattintson a „Feltételes formázás” elemre a „Stílusok” csoportban.

4. A legördülő menüből válassza az „Új szabály” lehetőséget.

A következő feladat egy képlet megadása a szabályhoz. Kovesd ezeket a lepeseket:

1. A párbeszédpanelen válassza a „Képlet használata a formázandó cellák meghatározásához” lehetőséget

2. Írja be a következő képletet: =COUNTIF(Sheet2!$A$2:$A$5, A2) > 0

Végül alkalmazza a kívánt formázást az ismétlődő cellákhoz.

1. Kattintson a Formázás gombra a Cellák formázása párbeszédpanel megnyitásához.

2. Válasszon formátumot, pl. töltse ki a másolatokat sárga háttérszínnel.

3. Kattintson az OK gombra.

Az ismétlődő adatok most sárga színnel vannak kiemelve.

A Feltételes formázási szabályok kezelőjének használata

Miután létrehozta a feltételes formázási szabályt, kezelheti azt a Feltételes formázási szabályok kezelőjével.

A menedzser elérése:

1. Lépjen a Kezdőlap fülre.

2. Kattintson a Feltételes formázás lehetőségre.

3. Válassza a „Szabályok kezelése” lehetőséget.

Megjelenik a kiválasztott lapra alkalmazott összes feltételes formázási szabály listája. Szerkesztheti, törölheti vagy módosíthatja a szabályok sorrendjét a szabály kiválasztásával és a megfelelő gombokra kattintva.

Ha ugyanazt a szabályt szeretné alkalmazni a másik munkalapra is, kövesse az alábbi lépéseket:

1. Válassza ki az összehasonlítani kívánt tartományt a második lapon.

2. Nyissa meg a Feltételes formázási szabályok kezelőjét.

3. Válassza ki a szabályt, kattintson a Szabály megkettőzése lehetőségre, majd nyomja meg a Szabály szerkesztése gombot.

4. Cserélje ki a „Sheet2”-t az első összehasonlítandó lap nevére.

Most, hogy mindkét lapra alkalmazta a feltételes formázási szabályt, az ismétlődések kiemelve lesznek a kiválasztott formázásnak megfelelően.

Ügyeljen arra, hogy szükség szerint állítsa be a tartományt és a cellahivatkozásokat a képletekben, hogy lefedje az összes összehasonlítani kívánt adatot.

A feltételes formázás kissé primitívnek tűnhet. Ha finomabb vezérlést szeretne, akkor a Power Query lehet a válasz! A következő részben bemutatjuk, hogyan használhatja a Power Queryt az ismétlődések keresésére.

3. A Power Query használata a munkalapok ismétlődéseinek keresésére

A Power Query egy adatátalakítási és adat-előkészítő eszköz a Microsoft Excelben. Ugyanazon értékek azonosítása csak egy az eszközzel végrehajtható számos elemzési feladat közül.

Ehhez először importálnia kell a két munkalap adatait külön táblákba. Kövesse az alábbi lépéseket az egyes lapokon:

1. Kattintson a jobb gombbal a cellatartományra.

2. Válassza az „Adatok lekérése táblázatból/tartományból” lehetőséget.

3. Módosítsa a táblázat nevét megfelelőre.

Mindkét lap importálása után az első feladat az adatok egyesítése:

1. Lépjen az Adatok fülre.

2. Kattintson az „Adatok lekérése” gombra.

3. Válassza a „Lekérdezések kombinálása” lehetőséget.

4. Válassza az „Egyesítés” lehetőséget, és válassza ki a két táblázatot.

5. Kattintson a két kulcsoszlopra.

6. Válassza az „Inner”-t „Join Kind”-ként, majd kattintson az OK gombra.

Megnyílik a Power Query szerkesztő az Excel-munkalap mindkét táblázatának egyesített adataival. Két oszlopot fog látni, mindegyik táblázatból egyet. Mivel Önt csak az ismétlődő értékek érdeklik, eltávolíthatja a második oszlopot.

Kattintson a „Bezárás és betöltés” ​​lehetőségre a Power Query szerkesztőben a másolatok új munkalapra való betöltéséhez.

Ennek a nagy teljesítményű funkciónak a további szempontjainak felfedezéséhez kövesse a címen található cikkünkben található példákat.

Az Excel rendelkezik harmadik féltől származó eszközökkel és bővítményekkel is, amelyek lehetővé teszik az ismétlődések zökkenőmentes megtalálását, ezért vessünk egy pillantást ezekre az eszközökre a következő részben.

4. Eszközök és bővítmények a munkalapokon található ismétlődések azonosítására

A külső eszközök és bővítmények olyan fejlett funkciókat kínálhatnak, amelyek esetleg nem érhetők el a natív Excel-szolgáltatásokban. Ezek az eszközök tovább egyszerűsíthetik a munkalapok ismétlődő összehasonlításának folyamatát.

A Spreadsheet Compare egy Microsoft-eszköz, amely lehetővé teszi két munkafüzet egymás melletti összehasonlítását, kiemelve a különbségeket és könnyen azonosítva az ismétlődéseket. Letöltheti a Microsoft webhelyéről .

Számos bővítmény telepíthető az ismétlődések keresésének automatizálására. Az egyik példa a „ Duplikátumeltávolító ”. Bővítmény telepítése:

1. Lépjen a Beszúrás fülre.

2. Kattintson a „Bővítmény letöltése” gombra.

3. Keresse meg a „Duplikáció” kifejezést.

4. Kattintson a „Hozzáadás” gombra a választott eszközön.

5. Hogyan lehet vizuálisan ellenőrizni az ismétlődéseket két lapon

Ha minden más nem segít, használd a szemed! Az Excel Windows rendezése párbeszédpanelje lehetővé teszi több munkalap vagy munkafüzet egymás melletti megtekintését.

Bár közvetlenül nem találja meg az ismétlődéseket, segíthet a munkalapok vagy munkafüzetek adatainak vizuális összehasonlításában az ismétlődések észleléséhez. Kovesd ezeket a lepeseket:

1. Kattintson az Excel szalag „Nézet” fülére.

2. Kattintson az „Összes elrendezése” elemre az „Ablak” csoportban.

3. Válasszon egy elrendezési lehetőséget, pl. „Függőleges” vagy „Vízszintes”.

Ez a két lapot egymás mellett vagy egymás fölött jeleníti meg. Most már manuálisan is összehasonlíthatja az egyes lapokon található adatokat a duplikációk azonosításához.

Végig kell görgetnie az adatokat, és vizuálisan meg kell vizsgálnia az egyes értékeket, hogy megtalálja az egyezéseket.

Vegye figyelembe, hogy ez a módszer nem hatékony nagy adatkészletek esetén, mivel manuális összehasonlítást igényel. A cikkben szereplő többi módszer használatával hatékonyabban találhat ismétlődéseket nagyobb adatkészletekben.

És ez az utolsó módszerünk az ismétlődő értékek megkeresésére az Excel-lapokon! A következő részben adunk néhány tippet a munkalapok elkészítéséhez.

3 tipp az Excel-munkalapok elkészítéséhez

Mielőtt elkezdené több munkalap összehasonlítását, győződjön meg arról, hogy az adatkészletek oszlopai és sorai megfelelően sorakoznak.

Győződjön meg arról, hogy mindkettőnek ugyanaz a szerkezete és ugyanaz a fejléc neve. Ha szükséges, átrendezheti mindkét lap oszlopait, hogy megfeleljenek egymásnak.

Íme három javaslat a pontos összehasonlítások biztosítására:

1. Rendezd adataidat ugyanabban a sorrendben mindkét lapon. Ez megkönnyíti az Excel-funkciók hatékony működését.

2. Normalizálja adatait konzisztens formázással, nagybetűkkel és adattípusokkal. Ezzel elkerülhető a kisebb eltérések miatti nem egyező bejegyzések.

3. Távolítsa el a szükségtelen üres sorokat vagy oszlopokat, mert zavarhatják az összehasonlítási folyamatot.

Az egyedi értékek megszámlálásával felmérheti, hogy mekkora duplikáció van egy adatkészletben. Ez a videó több módszert is bemutat:

A hibák és következetlenségek kezelése

Az adatok következetlenségei hatással lehetnek az összehasonlítási folyamatra. Íme négy tipp a következetlenségek feloldására:

1. Ellenőrizze, hogy nincsenek-e eltérések az adattípusokban, például nem keveredik-e szöveg és számérték ugyanabban az oszlopban.

2. Győződjön meg arról, hogy a dátumok, számok és egyéb adattípusok egységes formázását használják.

3. Vizsgálja meg adatait, hogy nincsenek-e hiányzó vagy hibás bejegyzések, és szükség esetén frissítse.

4. Szabványosítsa a rövidítéseket vagy az inkonzisztens elnevezési konvenciókat az adatkészletekben.

Végső gondolatok

Az adatok sértetlenségének és pontosságának biztosítása érdekében az adatkezelés és -elemzés alapvető feladata az ismétlődések keresése két Excel munkalapon. Az Excel többféle technikát kínál az ismétlődések azonosítására, mindegyiknek megvannak a maga előnyei és korlátai.

A módszer kiválasztása a felhasználó igényeitől, az adatkészlet méretétől és összetettségétől, valamint a kívánt eredménytől függ. Kisebb adatkészletekhez és egyszerű összehasonlításhoz elegendő lehet a , COUNTIF vagy feltételes formázás.

Nagyobb adatkészletek vagy összetettebb adatátalakítások esetén a Power Query egy hatékony és rugalmas eszköz, amely az adat-előkészítési feladatok széles skáláját képes kezelni, beleértve a duplikátumok keresését.

Végezetül, az ismétlődések összehasonlítása rendkívül praktikus készség, amelyet az eszköztárban tárolhatsz. A cikkben található trükkök segítségével észreveheti a bosszantó ismétlődéseket, és tisztán tarthatja adatait.

Bízzon bennünk, ahogy egyre jobb lesz ebben, gyorsan átvészelheti az adatkezelési feladatokat, és mindenkit lenyűgöz körülötte!