Hogyan lehet összehasonlítani két Excel -lapot (a különbségekért)

Videó megtekintése - Hogyan lehet összehasonlítani két Excel -táblázatot a különbségekért

Két Excel fájl összehasonlítása (vagy két lap összehasonlítása ugyanabban a fájlban) bonyolult lehet, mivel az Excel munkafüzet egyszerre csak egy lapot jelenít meg.

Ez nehezebbé és hibásabbá válik, ha sok adatot kell összehasonlítani.

Szerencsére az Excelben van néhány jó funkció, amelyek lehetővé teszik két Excel fájl megnyitását és egyszerű összehasonlítását.

Ebben az Excel oktatóanyagban több módszert mutatok be két különböző Excel fájl (vagy munkalap) összehasonlítása és ellenőrizze a különbségeket. A választott módszer attól függ, hogy az adatok hogyan vannak felépítve, és milyen összehasonlítást keres.

Kezdjük el!

Két Excel-lap összehasonlítása különálló Excel-fájlokban (egymás mellett)

Ha két különálló Excel-fájlt szeretne összehasonlítani egymás mellett (vagy két lapot ugyanabban a munkafüzetben), akkor az Excelben van egy beépített funkció.

Ez a Nézet egymás mellett választási lehetőség.

Ez csak akkor ajánlott, ha kis adatkészlete van, és ezeknek a fájloknak a manuális összehasonlítása valószínűleg kevésbé időigényes és hibalehetőséget jelent. Ha nagy adathalmaza van, azt javaslom, hogy használja a feltételes módszert vagy a képletmódszert, amelyet az oktatóanyag később ismertet.

Lássuk, hogyan kell ezt használni, ha két külön fájlt vagy két lapot kell összehasonlítania ugyanabban a fájlban.

Tegyük fel, hogy két fájlja van két különböző hónapra, és ellenőrizni szeretné, hogy ezek a két fájlban milyen értékek különböznek egymástól.

Alapértelmezés szerint egy fájl megnyitásakor valószínűleg az egész képernyőt elfoglalja. Még ha kicsinyíti is a méretét, mindig egy Excel -fájlt lát a tetején.

Az egymás melletti nézet opcióval két fájlt nyithat meg, majd vízszintesen vagy függőlegesen elrendezheti. Ez lehetővé teszi az értékek egyszerű összehasonlítását anélkül, hogy oda -vissza kapcsolnánk.

Az alábbiakban bemutatjuk a két fájl egymás melletti összehangolásának és összehasonlításának lépéseit:

1. Nyissa meg az összehasonlítani kívánt fájlokat.
2. Minden fájlban válassza ki az összehasonlítani kívánt lapot.
3. Kattintson a Nézet fülre
4. A Windows csoportban kattintson a „Nézet egymás mellett” lehetőségre. Ez csak akkor válik elérhetővé, ha két vagy több Excel fájl van megnyitva.

Amint rákattint a Nézet egymás mellett lehetőségre, az Excel vízszintesen rendezi a munkafüzetet. Mindkét fájl látható lesz, és szabadon szerkesztheti/összehasonlíthatja ezeket a fájlokat, miközben egymás mellett vannak elrendezve.

Ha függőlegesen szeretné rendezni a fájlokat, kattintson az Összes rendezés opcióra (a Nézet lapon).

Ez megnyitja a "Windows rendezése" párbeszédpanelt, ahol kiválaszthatja a "Függőleges" lehetőséget.

Ezen a ponton, ha lefelé görget az egyik munkalapon, a másik változatlan marad. Ezt úgy módosíthatja, hogy az egyik lap görgetésekor a másik is görgessen egyszerre. Ez megkönnyíti a soronkénti összehasonlítást és a különbségek észlelését.

Ehhez azonban engedélyeznie kell Szinkron görgetés.

A szinkron görgetés engedélyezéséhez kattintson a Nézet fülre (bármelyik munkafüzetben), majd kattintson a Szinkron görgetés lehetőségre. Ez egy kapcsológomb (tehát ha ki szeretné kapcsolni, egyszerűen kattintson rá újra).

Több lap összehasonlítása különálló Excel-fájlokban (egymás mellett)

A „Nézet egymás mellett” opcióval csak két Excel fájlt hasonlíthat össze egyszerre.

Ha több Excel fájl van megnyitva, akkor a Nézet egymás mellett lehetőségre kattintva megjelenik az „Összehasonlítás egymás mellett” párbeszédpanel, ahol kiválaszthatja, hogy melyik fájlt szeretné összehasonlítani az aktív munkafüzettel.

Ha egynél több fájlt szeretne összehasonlítani, nyissa meg ezeket a fájlokat, majd kattintson az Összes rendezés opcióra (ez a Nézet lapon található).

A Windows rendezése párbeszédpanelen válassza a Függőleges/vízszintes lehetőséget, majd kattintson az OK gombra.

Ez az összes megnyitott Excel fájlt a kiválasztott sorrendben rendezi (függőleges vagy vízszintes).

Hasonlítsa össze két lapot (egymás mellett) ugyanabban az Excel munkafüzetben

Abban az esetben, ha két külön lapot szeretne összehasonlítani ugyanabban a munkafüzetben, akkor nem használhatja a Nézet egymás mellett funkciót (mivel csak különálló Excel -fájlok esetén működik).

De továbbra is elvégezheti ugyanazt az egymás melletti összehasonlítást.

Ezt lehetővé teszi a „Az új Windows szolgáltatás az Excelben, amely lehetővé teszi két példány megnyitását ugyanazon a munkafüzeten. Ha két példánya nyitva van, ezeket egymás mellé rendezheti, majd összehasonlíthatja.

Tegyük fel, hogy van egy Excel munkafüzete, amely két lapot tartalmaz két különböző hónapra (január és február), és ezeket szeretné összehasonlítani, hogy lássa, hogyan változtak az áruházankénti eladások:

Az alábbiakban bemutatjuk az Excel két lap összehasonlításának lépéseit:

1. Nyissa meg azt a munkafüzetet, amely tartalmazza az összehasonlítani kívánt lapokat.
2. Kattintson a Nézet fülre
3. Az Ablak csoportban kattintson az „Új ablak” lehetőségre. Ezzel megnyitja ugyanazon munkafüzet második példányát.
4. A „Nézet” lapon kattintson az „Összes rendezése” gombra. Ezzel megnyílik a Windows rendezése párbeszédpanel
5. Válassza a „Függőleges” lehetőséget az oszlopok adatainak összehasonlításához (vagy válassza a Vízszintes lehetőséget, ha a sorokban lévő adatokat szeretné összehasonlítani).
6. Kattintson az OK gombra.

A fenti lépések függőlegesen rendeznék a munkafüzet mindkét példányát.

Ezen a ponton mindkét munkafüzet ugyanazt a munkalapot választotta ki. Az egyik munkafüzetben válassza ki azt a másik lapot, amelyet az aktív lappal szeretne összehasonlítani.

Hogy működik ez?

Ha rákattint az Új ablak lehetőségre, ugyanazt a munkafüzetet nyitja meg kissé más néven. Például, ha a munkafüzet neve „Teszt”, és az Új ablak gombra kattint, akkor a már megnyitott munkafüzet „Teszt - 1”, a második példány pedig „Teszt - 2” nevet kap.

Vegye figyelembe, hogy ezek még mindig ugyanazok a munkafüzetek. Ha ezen munkafüzetek bármelyikén módosít, az mindkettőben megjelenik.

És amikor bezárja a megnyitott fájl bármelyik példányát, a név visszaáll az eredetihez.

Ha szeretné, engedélyezheti a szinkron görgetést is (kattintson a „Szinkron görgetés” lehetőségre a „Nézet” lapon)

Két lap összehasonlítása és a különbségek kiemelése (feltételes formázás használatával)

Bár a fenti módszerrel összehangolhatja a munkafüzeteket, és manuálisan végigmehet soronként az adatokon, ez nem jó módszer, ha sok adat áll rendelkezésére.

Ezenkívül az ilyen szintű összehasonlítás manuális elvégzése sok hibához vezethet.

Tehát ahelyett, hogy ezt manuálisan tenné, a Feltételes formázás segítségével gyorsan kiemelheti a két Excel -lap közötti különbségeket.

Ez a módszer akkor igazán hasznos, ha két verziója van két különböző lapon, és gyorsan szeretné ellenőrizni, hogy mi változott.

Vegye figyelembe, hogy Ön NEM TUD hasonlítson össze két lapot különböző munkafüzetekben.

Mivel a Feltételes formázás nem hivatkozhat külső Excel fájlra, az összehasonlítandó lapoknak ugyanabban az Excel munkafüzetben kell lenniük. Ha nem, akkor másolhat egy lapot a másik fájlból az aktív munkafüzetbe, majd elvégezheti ezt az összehasonlítást.

Ebben a példában tegyük fel, hogy két hónap (január és február) között két különböző lapon van adathalmaza, amint az alább látható, és gyorsan össze szeretné hasonlítani a két lap adatait, és ellenőrizni, hogy ezeknek az elemeknek az árai megváltoztak -e vagy sem.

Ennek lépései az alábbiakban találhatók:

1. Válassza ki azokat az adatokat azon a lapon, ahol ki szeretné emelni a módosításokat. Mivel szeretném ellenőrizni, hogyan változtak az árak januártól februárig, ezért kiválasztottam a februári lap adatait.
2. Kattintson a Kezdőlap fülre
3. A Stílusok csoportban kattintson a „Feltételes formázás” elemre
4. A megjelenő lehetőségek közül kattintson az „Új szabály” gombra
5. Az „Új formázási szabály” párbeszédpanelen kattintson a „Képlet használata a formázandó cellák meghatározására” lehetőségre
6. A képlet mezőbe írja be a következő képletet: = B2Jan! B2
7. Kattintson a Formázás gombra
8. A megjelenő Cellaformázás párbeszédpanelen kattintson a „Kitöltés fülre”, és válassza ki azt a színt, amelyen ki szeretné emelni a nem megfelelő adatokat.
9. Kattintson az OK gombra
10. Kattintson az OK gombra

A fenti lépések azonnal rávilágítanak az adatkészletben bekövetkezett bármilyen változásra mindkét lapon.

Hogy működik ez?

A feltételes formázás kiemeli a cellát, ha a cellához adott képlet IGAZ értéket ad vissza. Ebben a példában az egyik lap minden celláját összehasonlítjuk a másik lap megfelelő cellájával (a képletben nem egyenlő az operátorral).

Amikor a feltételes formázás bármilyen különbséget talál az adatokban, kiemeli, hogy a Jan -lapon (amelyen a feltételes formázást alkalmaztuk.

Vegye figyelembe, hogy ebben a példában relatív hivatkozást használtam (A1 és nem $ A $ 1 vagy $ A1 vagy A $ 1).

Ha ezzel a módszerrel két lapot hasonlít össze az Excelben, ne feledje a következőket;

• Ez a módszer jó a különbségek gyors azonosítására, de nem használhatja folyamatosan. Például, ha új sort adok meg az adatkészletek bármelyikében (vagy törlök egy sort), az hibás eredményeket adna. Amint beillesztem/törlöm a sort, minden további sor eltérőnek tekintendő, és ennek megfelelően kiemelve.
• Csak két lapot hasonlíthat össze ugyanabban az Excel fájlban
• Csak az értéket hasonlíthatja össze (nem a képlet vagy a formázás közötti különbséget).

Hasonlítson össze két Excel fájlt/lapot, és nézze meg a különbségeket a képlet segítségével

Ha csak a két lap közötti különbségek gyors összehasonlítása és azonosítása érdekli, akkor egy képlet segítségével csak azokat az értékeket töltse le, amelyek eltérnek egymástól.

Ehhez a módszerhez külön munkalappal kell rendelkeznie, ahol lekérheti a különbségeket.

Ez a módszer akkor működik, ha két különálló Excel munkafüzetet vagy munkalapot szeretne összehasonlítani ugyanazon a munkafüzeten.

Hadd mutassak egy példát, ahol két adatkészletet hasonlítok össze két lapon (ugyanabban a munkafüzetben).

Tegyük fel, hogy rendelkezik az alábbi adathalmazzal a Jan nevű lapon (és hasonló adatokkal a február nevű lapon), és szeretné tudni, hogy milyen értékek különböznek egymástól.

A két lap összehasonlításához először helyezzen be egy új munkalapot (nevezzük ezt a lapot „Különbségnek”).

Az A1 cellába írja be a következő képletet:

= HA (Jan! A1Feb! A1, "Jan Value:" & Jan! A1 & CHAR (10) & "Feb Value:" & Feb! A1, "")

Másolja ki és illessze be ezt a képletet egy tartományhoz úgy, hogy az mindkét lap teljes adatkészletét lefedje. Mivel van egy kis adatkészletem, ezt a képletet csak az A1: B10 tartományba másolom és illesztem be.

A fenti képlet IF feltételt használ a különbségek ellenőrzésére. Abban az esetben, ha nincs különbség az értékekben, akkor üresen tér vissza, és ha eltérés van, akkor mindkét lap értékeit ugyanazon cellában külön sorokban adja vissza.

Ezzel a módszerrel az a jó, hogy csak a különbségeket adja meg, és pontosan megmutatja, mi a különbség. Ebben a példában könnyen látom, hogy a B4 és B8 cella ára eltérő (valamint a pontos értékek ezekben a cellákban).

Hasonlítson össze két Excel fájlt/lapot, és ismerje meg a különbségeket a VBA használatával

Ha gyakran kell összehasonlítania az Excel fájlokat vagy lapokat, akkor érdemes kész Excel Excel makró VBA -kóddal rendelkeznie, és azt használnia, amikor összehasonlításra van szüksége.

Hozzáadhatja a makrót a gyorselérési eszköztárhoz is, hogy egyetlen gombbal érhesse el, és azonnal megtudja, milyen cellák különböznek a különböző fájlokban/lapokban.

Tegyük fel, hogy két lapja van januárban és februárban, és szeretné összehasonlítani és kiemelni a különbségeket a Jan -lapon, az alábbi VBA -kódot használva:

Sub CompareSheets () Dim rngCell tartományként minden rngCell munkalaphoz ("Jan"). UsedRange If Not rngCell = Munkalapok ("február"). Cells (rngCell.Row, rngCell.Column) Ezután rngCell.Interior.Color = vbSárga vége Ha Következő rngCell End Sub

A fenti kód a Következő ciklus segítségével átmegy a Jan lap minden celláján (a teljes használt tartományon), és összehasonlítja a februári lap megfelelő cellájával. Abban az esetben, ha különbséget talál (amelyet az If-then utasítással ellenőriznek), sárgával kiemeli ezeket a cellákat.

Ezt a kódot használhatja a VB Editor rendszeres moduljában.

És ha gyakran kell ezt tennie, akkor jobb, ha ezt a kódot elmenti a Személyes makró munkafüzetbe, majd hozzáadja a Gyorselérés eszköztárhoz. Ilyen módon egyetlen kattintással elvégezheti ezt az összehasonlítást.

Íme a lépések a Személyes makró munkafüzet Excelben való letöltéséhez (alapértelmezés szerint nem érhető el, ezért engedélyeznie kell).

Az alábbi lépésekkel mentheti ezt a kódot a Személyes makró munkafüzetbe.

És itt megtalálja a lépéseket ennek a makrókódnak a QAT -hoz való hozzáadásához.

Harmadik féltől származó eszköz használata - XL Comparator

Egy másik gyors módszer két Excel fájl összehasonlítására, valamint az egyezések és különbségek ellenőrzésére egy ingyenes, harmadik féltől származó eszköz, például az XL Comparator használatával.

Ez egy webalapú eszköz, ahol két Excel fájlt tölthet fel, és összehasonlító fájlt hoz létre, amely tartalmazza a közös adatokat (vagy a kiválasztott beállítás alapján eltérő adatokat).

Tegyük fel, hogy két olyan fájlja van, amelyek ügyfél adatkészletekkel rendelkeznek (például név és e -mail cím), és gyorsan szeretné ellenőrizni, hogy milyen ügyfelek vannak, az 1. és nem a 2. fájl.

Az alábbiakban bemutatjuk, hogyan hasonlíthat össze két Excel fájlt, és hogyan hozhat létre összehasonlítási jelentést:

1. Nyissa meg a https://www.xlcomparator.net/
2. Használja a Fájl kiválasztása lehetőséget két fájl feltöltéséhez (az egyes fájlok maximális mérete 5 MB lehet)
3. Kattintson a Tovább gombra.
4. Válassza ki a közös oszlopot mindkét fájlban. Az eszköz ezt az általános oszlopot használja az egyezések és különbségek keresésére
5. Válassza ki a négy lehetőség egyikét, függetlenül attól, hogy egyező adatokat vagy különböző adatokat szeretne kapni (az 1. vagy a 2. fájl alapján)
6. Kattintson a Tovább gombra
7. Töltse le az összehasonlító fájlt, amely tartalmazza az adatokat (az 5. lépésben kiválasztott opció alapján)

Az alábbiakban egy videó látható, amely bemutatja az XL Comparator eszköz működését.

Az egyik aggálya az, ha harmadik féltől származó eszközt használ az Excel fájlok összehasonlítására, a magánélet védelme. Ha bizalmas adatokkal rendelkezik, és a magánélet nagyon fontos számára, akkor jobb, ha más, fent bemutatott módszereket használ. Ne feledje, hogy az XL Comparator webhely megemlíti, hogy az összes fájlt törli 1 óra összehasonlítás után.

Íme néhány módszer, amellyel két különböző Excel -fájlt (vagy ugyanazon Excel -fájl munkalapjait) hasonlíthat össze. Remélem, hasznosnak találta ezt az Excel oktatóanyagot.