Kaip palyginti du „Excel“ lapus (dėl skirtumų)

Žiūrėkite vaizdo įrašą - kaip palyginti du „Excel“ lapus skirtumams

Palyginti du „Excel“ failus (arba palyginti du to paties failo lapus) gali būti sudėtinga, nes „Excel“ darbaknygėje vienu metu rodomas tik vienas lapas.

Tai tampa sunkiau ir dažniau pasitaiko klaidų, kai turite daug duomenų, kuriuos reikia palyginti.

Laimei, „Excel“ yra keletas puikių funkcijų, leidžiančių atidaryti ir lengvai palyginti du „Excel“ failus.

Šioje „Excel“ pamokoje parodysiu kelis būdus, kaip tai padaryti palyginti du skirtingus „Excel“ failus (arba lapus) ir patikrinkite, ar nėra skirtumų. Pasirinktas metodas priklausys nuo jūsų duomenų struktūros ir kokio palyginimo ieškote.

Pradėkime!

Palyginkite du „Excel“ lapus atskiruose „Excel“ failuose (vienas šalia kito)

Jei norite palyginti du atskirus „Excel“ failus (arba du lapus toje pačioje darbaknygėje), „Excel“ yra integruota funkcija.

Tai Žiūrėti greta variantas.

Tai rekomenduojama tik tada, kai turite nedidelį duomenų rinkinį ir rankiniu būdu lyginant šiuos failus greičiausiai prireiks mažiau laiko ir klaidų. Jei turite didelį duomenų rinkinį, rekomenduoju naudoti sąlyginį metodą arba formulės metodą, aprašytą vėliau šioje pamokoje.

Pažiūrėkime, kaip tai naudoti, kai reikia palyginti du atskirus failus arba du to paties failo lapus.

Tarkime, kad turite du failus du skirtingus mėnesius ir norite patikrinti, kokios šių dviejų failų vertės skiriasi.

Pagal numatytuosius nustatymus, kai atidarote failą, greičiausiai jis užims visą ekraną. Net jei sumažinsite dydį, viršuje visada matysite vieną „Excel“ failą.

Naudodami parinktį „View side-by-side“, galite atidaryti du failus ir juos išdėstyti horizontaliai arba vertikaliai. Tai leidžia lengvai palyginti vertes nekeičiant pirmyn ir atgal.

Žemiau pateikiami veiksmai, kaip sulyginti du failus šalia ir palyginti:

1. Atidarykite failus, kuriuos norite palyginti.
2. Kiekviename faile pasirinkite lapą, kurį norite palyginti.
3. Spustelėkite skirtuką Rodinys
4. „Windows“ grupėje spustelėkite parinktį „Žiūrėti šalia“. Tai pasiekiama tik atidarius du ar daugiau „Excel“ failų.

Kai tik spustelėsite parinktį Žiūrėti šalia, „Excel“ išdėstys darbaknygę horizontaliai. Abu failai bus matomi, o jūs galite laisvai redaguoti/palyginti šiuos failus, kol jie yra išdėstyti vienas šalia kito.

Jei norite išdėstyti failus vertikaliai, spustelėkite parinktį „Tvarkyti viską“ (skirtuke „Rodinys“).

Bus atidarytas dialogo langas „Tvarkyti langus“, kuriame galėsite pasirinkti „Vertikalus“.

Šiuo metu, jei slinkite žemyn vienu darbalapiu, kitas liktų toks, koks yra. Galite tai pakeisti taip, kad kai slinktumėte viename lape, kitas slinktų tuo pačiu metu. Tai leidžia lengviau palyginti eilutes pagal eilutes ir pastebėti skirtumus.

Bet norėdami tai padaryti, turite įjungti Sinchroninis slinkimas.

Norėdami įjungti sinchroninį slinkimą, spustelėkite skirtuką Rodinys (bet kurioje darbaknygėje), tada spustelėkite parinktį Sinchroninis slinkimas. Tai perjungimo mygtukas (taigi, jei norite jį išjungti, tiesiog spustelėkite jį dar kartą).

Kelių lapų palyginimas atskiruose „Excel“ failuose (vienas šalia kito)

Pasirinkę parinktį „Žiūrėti šalia“, vienu metu galite palyginti tik du „Excel“ failus.

Jei atidarote kelis „Excel“ failus, spustelėję parinktį „Žiūrėti šalia“, pamatysite dialogo langą „Palyginti vienas šalia kito“, kuriame galėsite pasirinkti, kurį failą norite palyginti su aktyvia darbaknyge.

Jei norite vienu metu palyginti daugiau nei du failus, atidarykite visus šiuos failus ir spustelėkite parinktį Tvarkyti viską (tai yra skirtuke Rodinys).

Dialogo lange „Tvarkyti Windows“ pasirinkite Vertikalus/horizontalus, tada spustelėkite Gerai.

Taip visi atidaryti „Excel“ failai bus išdėstyti pasirinkta tvarka (vertikaliai arba horizontaliai).

Palyginkite du lapus (vienas šalia kito) toje pačioje „Excel“ darbaknygėje

Jei norite palyginti du atskirus lapus toje pačioje darbaknygėje, negalite naudoti funkcijos Žiūrėti šalia (nes ji veikia tik atskiriems „Excel“ failams).

Bet jūs vis tiek galite atlikti tą patį lyginimą.

Tai įmanoma dėl „Nauja „Windows“ funkcija „Excel“, kuri leidžia atidaryti du egzempliorius toje pačioje darbaknygėje. Atidarę du egzempliorius, galite juos išdėstyti vienas šalia kito ir tada palyginti.

Tarkime, kad turite „Excel“ darbaknygę, kurioje yra du lapai dviem skirtingiems mėnesiams (sausio ir vasario mėn.), Ir norite juos palyginti, kad pamatytumėte, kaip pasikeitė vienos parduotuvės pardavimas:

Toliau pateikiami žingsniai, kaip palyginti du „Excel“ lapus:

1. Atidarykite darbaknygę, kurioje yra lapai, kuriuos norite palyginti.
2. Spustelėkite skirtuką Rodinys
3. Langų grupėje spustelėkite parinktį „Naujas langas“. Taip atidaromas antrasis tos pačios darbaknygės egzempliorius.
4. Skirtuke „Rodinys“ spustelėkite „Sutvarkyti viską“. Bus atidarytas dialogo langas „Windows tvarkymas“
5. Pasirinkite „Vertikali“, jei norite palyginti duomenis stulpeliuose (arba pasirinkite Horizontali, jei norite palyginti duomenis eilutėse).
6. Spustelėkite Gerai.

Pirmiau aprašyti veiksmai vertikaliai išdėstytų abu darbaknygės egzempliorius.

Šiuo metu abiejose darbaknygėse būtų pasirinktas tas pats darbalapis. Vienoje iš darbaknygių pasirinkite kitą lapą, kurį norite palyginti su aktyviu lapu.

Kaip tai veikia?

Spustelėjus „Naujas langas“, vėl atsidaro ta pati darbaknygė šiek tiek kitokiu pavadinimu. Pvz., Jei jūsų darbaknygės pavadinimas yra „Testas“ ir spustelėsite „Naujas langas“, ji jau atidarytą darbaknygę pavadins „Testas - 1“, o antrasis egzempliorius - „Testas - 2“.

Atminkite, kad tai vis tiek ta pati darbo knyga. Jei atliksite kokių nors pakeitimų bet kurioje iš šių darbo knygų, tai atsispindės abiejose.

Ir kai uždarysite vieną atviro failo egzempliorių, pavadinimas bus grąžintas į pradinį.

Jei norite, taip pat galite įjungti sinchroninį slinkimą (skirtuke „Rodinys“ spustelėję parinktį „Sinchroninis slinkimas“).

Palyginkite du lapus ir paryškinkite skirtumus (naudodami sąlyginį formatavimą)

Nors galite naudoti aukščiau pateiktą metodą, kad suderintumėte darbo knygas ir rankiniu būdu pereitumėte prie duomenų eilutėmis, tai nėra geras būdas, jei turite daug duomenų.

Be to, atlikus tokį lyginimo lygį rankiniu būdu, gali atsirasti daug klaidų.

Taigi, užuot tai darę rankiniu būdu, galite naudoti sąlyginio formatavimo galią, kad greitai paryškintumėte visus dviejų „Excel“ lapų skirtumus.

Šis metodas yra tikrai naudingas, jei turite dvi versijas dviejuose skirtinguose lapuose ir norite greitai patikrinti, kas pasikeitė.

Atkreipkite dėmesį, kad jūs NEGALIMA palyginkite du lapus skirtingose ​​darbaknygėse.

Kadangi sąlyginis formatavimas negali nurodyti išorinio „Excel“ failo, lapai, kuriuos reikia palyginti, turi būti toje pačioje „Excel“ darbaknygėje. Jei jų nėra, galite nukopijuoti lapą iš kito failo į aktyvią darbaknygę ir tada palyginti.

Tarkime, kad turite dviejų mėnesių (sausio ir vasario mėn.) Dviejų skirtingų lapų duomenų rinkinį, kaip parodyta žemiau, ir norite greitai palyginti šių dviejų lapų duomenis ir patikrinti, ar pasikeitė šių prekių kainos.

Žemiau pateikiami žingsniai, kaip tai padaryti:

1. Pasirinkite duomenis lape, kuriame norite paryškinti pakeitimus. Kadangi noriu patikrinti, kaip kainos pasikeitė nuo sausio iki vasario, aš pasirinkau vasario lapo duomenis.
2. Spustelėkite skirtuką Pagrindinis
3. Grupėje Stiliai spustelėkite „Sąlyginis formatavimas“
4. Pasirodžiusiose parinktyse spustelėkite „Nauja taisyklė“
5. Dialogo lange „Nauja formatavimo taisyklė“ spustelėkite „Naudoti formulę, kad nustatytumėte, kurias langelius formatuoti“
6. Formulės lauke įveskite šią formulę: = B2Jan! B2
7. Spustelėkite mygtuką Formatuoti
8. Rodomame dialogo lange Langelių formatavimas spustelėkite skirtuką „Užpildyti“ ir pasirinkite spalvą, kuria norite paryškinti neatitinkančius duomenis.
9. Spustelėkite Gerai
10. Spustelėkite Gerai

Pirmiau minėti veiksmai akimirksniu išryškintų visus abiejų lapų duomenų rinkinio pakeitimus.

Kaip tai veikia?

Sąlyginis formatavimas paryškina langelį, kai pateikta to langelio formulė grąžina TRUE. Šiame pavyzdyje mes lyginame kiekvieną vieno lapo langelį su atitinkamu kito lapo langeliu (tai daroma naudojant formulėje nelygų operatoriui).

Kai sąlyginis formatavimas nustato bet kokį duomenų skirtumą, jis pabrėžia, kad Jan lape (tame, kuriame pritaikėme sąlyginį formatavimą.

Atkreipkite dėmesį, kad šiame pavyzdyje naudojau santykinę nuorodą (A1, o ne $ 1 $ arba $ A1 arba A $ 1).

Kai naudojate šį metodą, kad palygintumėte du „Excel“ lapus, prisiminkite šiuos dalykus:

• Šis metodas tinka greitai nustatyti skirtumus, tačiau jūs negalite jo nuolat naudoti. Pavyzdžiui, jei įvesiu naują eilutę į bet kurį duomenų rinkinį (arba ištrinu eilutę), tai duos neteisingus rezultatus. Kai tik įterpiu/ištrinu eilutę, visos tolesnės eilutės laikomos skirtingomis ir atitinkamai paryškintos.
• Tame pačiame „Excel“ faile galite palyginti tik du lapus
• Galite palyginti tik vertę (ne formulės ar formatavimo skirtumą).

Palyginkite du „Excel“ failus/lapus ir sužinokite skirtumus naudodami formulę

Jei norite tik greitai palyginti ir nustatyti skirtumus tarp dviejų lapų, galite naudoti formulę, kad gautumėte tik tas vertes, kurios skiriasi.

Taikydami šį metodą, turėsite turėti atskirą darbalapį, kuriame galėsite gauti skirtumus.

Šis metodas veiktų, jei norite palyginti dvi atskiras „Excel“ darbaknyges ar tos pačios darbaknygės darbalapius.

Leiskite parodyti pavyzdį, kai lyginu du duomenų rinkinius dviejuose lapuose (toje pačioje darbaknygėje).

Tarkime, kad turite duomenų rinkinį, kaip parodyta žemiau, lape, pavadintame Jan (ir panašius duomenis lape, vadinamame vasario mėn.), Ir norite sužinoti, kokios vertės skiriasi.

Norėdami palyginti du lapus, pirmiausia įterpkite naują darbalapį (pavadinkime šį lapą skirtumu).

A1 langelyje įveskite šią formulę:

= IF (sausis! A1

Nukopijuokite ir įklijuokite šią diapazono formulę, kad ji apimtų visą abiejų lapų duomenų rinkinį. Kadangi turiu nedidelį duomenų rinkinį, nukopijuosiu ir įklijuosiu šią formulę tik A1: B10 diapazone.

Aukščiau pateikta formulė naudoja IF sąlygą skirtumams patikrinti. Jei reikšmės nesiskiria, ji grąžina tuščią, o jei yra skirtumas, grąžina abiejų lapų reikšmes atskirose to paties langelio eilutėse.

Geras šio metodo dalykas yra tai, kad jis tik suteikia jums skirtumų ir tiksliai parodo, koks skirtumas. Šiame pavyzdyje aš lengvai matau, kad B4 ir B8 langelių kaina skiriasi (taip pat ir tikslios šių langelių vertės).

Palyginkite du „Excel“ failus/lapus ir sužinokite skirtumus naudodami VBA

Jei jums reikia gana dažnai lyginti „Excel“ failus ar lapus, verta turėti paruoštą „Excel“ makrokomandos VBA kodą ir jį naudoti, kai reikia atlikti palyginimą.

Taip pat galite pridėti makrokomandą prie sparčiosios prieigos įrankių juostos, kad galėtumėte pasiekti vienu mygtuku ir iškart žinotumėte, kokios ląstelės skiriasi skirtinguose failuose/lapuose.

Tarkime, kad turite du lapus sausio ir vasario mėn. Ir norite palyginti ir paryškinti Jano lapo skirtumus, galite naudoti žemiau esantį VBA kodą:

Sub CompareSheets () Dim rngCell kaip diapazonas kiekvienam rngCell darbalapyje ("Jan"). UsedRange If Not rngCell = darbalapiai ("Vasaris"). Ląstelės (rngCell.Row, rngCell.Column) Tada rngCell.Interior.Color = vbGeltona pabaiga Jei Kitas rngCell End Sub

Pirmiau pateiktas kodas naudoja ciklą „Kitas“, kad pereitų kiekvieną „Jan“ lapo langelį (visą naudojamą diapazoną) ir palygintų jį su atitinkamu vasario lapo langeliu. Jei jis nustato skirtumą (kuris tikrinamas naudojant teiginį „Jei tada“), jis pažymi šias ląsteles geltona spalva.

Šį kodą galite naudoti įprastame VB redaktoriaus modulyje.

Ir jei jums tai reikia padaryti dažnai, geriau išsaugoti šį kodą asmeninės makrokomandos darbaknygėje ir tada pridėti jį prie sparčiosios prieigos įrankių juostos. Tokiu būdu jūs galėsite atlikti šį palyginimą spustelėdami mygtuką.

Štai žingsniai, kaip gauti asmeninę makrokomandų darbaknygę „Excel“ (ji pagal numatytuosius nustatymus nepasiekiama, todėl turite ją įgalinti).

Štai kaip išsaugoti šį kodą asmeninėje makrokomandos darbaknygėje.

Ir čia rasite žingsnius, kaip pridėti šį makro kodą prie QAT.

Trečiosios šalies įrankio naudojimas - XL Comparator

Kitas greitas būdas palyginti du „Excel“ failus ir patikrinti, ar nėra atitikčių ir skirtumų, yra nemokamas trečiosios šalies įrankis, pvz., „XL Comparator“.

Tai yra internetinis įrankis, kuriame galite įkelti du „Excel“ failus ir jis sukurs palyginimo failą, kuriame bus bendri duomenys (arba skirtingi duomenys, atsižvelgiant į pasirinktą parinktį).

Tarkime, kad turite du failus, turinčius klientų duomenų rinkinius (pvz., Vardą ir el. Pašto adresą), ir norite greitai patikrinti, kokie klientai yra, yra 1 failas, o ne 2 failas.

Žemiau aprašoma, kaip palyginti du „Excel“ failus ir sukurti palyginimo ataskaitą.

1. Atidarykite https://www.xlcomparator.net/
2. Naudodami parinktį Pasirinkti failą įkelkite du failus (maksimalus kiekvieno failo dydis gali būti 5 MB)
3. Spustelėkite mygtuką Kitas.
4. Abiejuose failuose pasirinkite bendrą stulpelį. Įrankis naudos šį įprastą stulpelį, kad ieškotų atitikčių ir skirtumų
5. Pasirinkite vieną iš keturių parinkčių, nesvarbu, ar norite gauti atitinkamus duomenis, ar skirtingus duomenis (remiantis 1 arba 2 rinkmenomis)
6. Spustelėkite Kitas
7. Atsisiųskite palyginimo failą, kuriame bus duomenys (atsižvelgiant į tai, kokią parinktį pasirinkote atlikdami 5 veiksmą)

Žemiau yra vaizdo įrašas, kuriame parodyta, kaip veikia „XL Comparator“ įrankis.

Vienas rūpestis, kurį galite patirti naudodami trečiosios šalies įrankį „Excel“ failų palyginimui, yra privatumas. Jei turite konfidencialių duomenų ir privatumas jiems yra labai svarbus, geriau naudoti kitus aukščiau nurodytus metodus. Atminkite, kad „XL Comparator“ svetainėje minima, kad jie ištrina visus failus po 1 valandos palyginimo.

Tai yra keletas metodų, kuriuos galite naudoti norėdami palyginti du skirtingus „Excel“ failus (arba to paties „Excel“ failo darbalapius). Tikimės, kad ši „Excel“ pamoka jums buvo naudinga.