Kako primerjati dva lista Excel (za razlike)

Oglejte si video - Kako primerjati dva lista Excel za razlike

Primerjava dveh Excelovih datotek (ali primerjava dveh listov v isti datoteki) je lahko težavna, saj Excelov delovni zvezek prikazuje samo en list naenkrat.

To postane težje in nagnjeno k napakam, če imate veliko podatkov, ki jih je treba primerjati.

Na srečo obstaja nekaj zanimivih funkcij v Excelu, ki vam omogočajo, da odprete in preprosto primerjate dve Excelovi datoteki.

V tej vadnici Excel vam bom pokazal več načinov primerjajte dve različni Excelovi datoteki (ali listi) in preverite razlike. Izbrana metoda bo odvisna od tega, kako so vaši podatki strukturirani in kakšno primerjavo iščete.

Začnimo!

Primerjajte dva lista Excel v ločenih datotekah Excel (vzporedno)

Če želite primerjati dve ločeni datoteki Excel drug ob drugem (ali dva lista v istem delovnem zvezku), je v Excelu vgrajena funkcija za to.

To je Pogled drug ob drugem možnost.

To je priporočljivo le, če imate majhen nabor podatkov in bo ročna primerjava teh datotek verjetno manj zamudna in nagnjena k napakam. Če imate velik nabor podatkov, priporočam uporabo pogojne metode ali metode formule, obravnavane kasneje v tej vadnici.

Poglejmo, kako to uporabiti, ko morate primerjati dve ločeni datoteki ali dva lista v isti datoteki.

Recimo, da imate dve datoteki za dva različna meseca in želite preveriti, katere vrednosti se v teh dveh datotekah razlikujejo.

Ko odprete datoteko, bo privzeto zavzel celoten zaslon. Tudi če velikost zmanjšate, je na vrhu vedno prikazana ena datoteka Excel.

Z možnostjo pogleda vzporedno lahko odprete dve datoteki in jih nato razporedite vodoravno ali navpično. To vam omogoča enostavno primerjavo vrednosti brez preklapljanja naprej in nazaj.

Spodaj so navedeni koraki za poravnavo dveh datotek drug ob drugem in njihovo primerjavo:

1. Odprite datoteke, ki jih želite primerjati.
2. V vsaki datoteki izberite list, ki ga želite primerjati.
3. Kliknite zavihek Pogled
4. V skupini Windows kliknite možnost »Poglej drug ob drugem«. To je na voljo šele, ko imate odprti dve ali več Excelovih datotek.

Takoj, ko kliknete možnost Pogled ob strani, bo Excel vodoravno razporedil delovni zvezek. Obe datoteki bosta vidni in te datoteke lahko urejate/primerjate, medtem ko sta razporejeni drug ob drugem.

Če želite datoteke razporediti navpično, kliknite možnost Razvrsti vse (na zavihku Pogled).

S tem se odpre pogovorno okno 'Arrange Windows', kjer lahko izberete 'Vertical'.

Če se na enem od delovnih listov pomaknete navzdol, bo drugi ostal takšen, kot je. To lahko spremenite tako, da se med pomikanjem po enem listu istočasno pomika tudi drugi. To olajša primerjavo po vrsticah in opazi razlike.

Če želite to narediti, morate omogočiti Sinhrono drsenje.

Če želite omogočiti sinhrono drsenje, kliknite zavihek Pogled (v katerem koli delovnem zvezku) in nato kliknite možnost Sinhrono drsenje. To je preklopni gumb (zato, če ga želite izklopiti, ga preprosto znova kliknite).

Primerjava več listov v ločenih datotekah Excel (vzporedno)

Z možnostjo »Poglej drug ob drugem« lahko hkrati primerjate samo dve Excelovi datoteki.

Če imate odprtih več Excelovih datotek, se vam ob kliku na možnost Pogled vzporedno prikaže pogovorno okno »Primerjaj drug ob drugem«, kjer lahko izberete, katero datoteko želite primerjati z aktivnim delovnim zvezkom.

Če želite hkrati primerjati več kot dve datoteki, odprite vse te datoteke in kliknite možnost »Razvrsti vse« (na zavihku »Pogled«).

V pogovornem oknu Arrange Windows izberite Vertical/Horizontal in kliknite OK.

Tako bodo vse odprte Excelove datoteke razporejene v izbranem vrstnem redu (navpično ali vodoravno).

Primerjajte dva lista (drug ob drugem) v istem delovnem zvezku programa Excel

Če želite primerjati dva ločena lista v istem delovnem zvezku, ne morete uporabiti funkcije Pogled ob strani (saj deluje samo za ločene datoteke Excel).

Še vedno pa lahko naredite isto primerjavo drug ob drugem.

To omogoča 'Nova funkcija sistema Windows v Excelu to omogoča odpiranje dveh primerkov v istem delovnem zvezku. Ko imate odprta dva primerka, jih lahko uredite drug ob drugem in jih nato primerjate.

Recimo, da imate delovni zvezek Excel, ki ima dva lista za dva različna meseca (januar in februar) in jih želite primerjati drug ob drugem, da vidite, kako se je prodaja na trgovino spremenila:

Spodaj so navedeni koraki za primerjavo dveh listov v Excelu:

1. Odprite delovni zvezek, ki vsebuje liste, ki jih želite primerjati.
2. Kliknite zavihek Pogled
3. V skupini Okno kliknite možnost »Novo okno«. S tem se odpre drugi primerek istega delovnega zvezka.
4. Na zavihku »Pogled« kliknite »Uredi vse«. S tem se odpre pogovorno okno Arrange Windows
5. Izberite »Navpično«, če želite primerjati podatke v stolpcih (ali izberite Vodoravno, če želite primerjati podatke v vrsticah).
6. Kliknite V redu.

Zgornji koraki bi oba primerka delovnega zvezka uredili navpično.

V tem trenutku bi imela oba delovna zvezka izbran isti delovni list. V enem od delovnih zvezkov izberite drugi list, ki ga želite primerjati z aktivnim listom.

Kako to deluje?

Ko kliknete Novo okno, se znova odpre isti delovni zvezek z nekoliko drugačnim imenom. Če je na primer ime vašega delovnega zvezka "Test" in kliknete Novo okno, bo že odprti delovni zvezek poimenovan "Test - 1", drugi primerek pa kot "Test - 2".

Upoštevajte, da je to še vedno isti delovni zvezek. Če v katerem koli od teh delovnih zvezkov naredite kakršne koli spremembe, bi se to odrazilo v obeh.

In ko zaprete kateri koli primerek odprte datoteke, bi se ime vrnilo nazaj v izvirnik.

Če želite, lahko omogočite tudi sinhrono drsenje (s klikom na možnost »Sinhrono drsenje« na zavihku »Pogled«)

Primerjajte dva lista in označite razlike (z uporabo pogojnega oblikovanja)

Čeprav lahko uporabite zgornjo metodo za usklajevanje delovnih zvezkov skupaj in ročno pregledovanje podatkov po vrsticah, to ni dober način, če imate veliko podatkov.

Tudi ročno izvajanje te ravni primerjave lahko povzroči veliko napak.

Torej, namesto da to počnete ročno, lahko z močjo pogojnega oblikovanja hitro poudarite vse razlike v dveh Excelovih listih.

Ta metoda je resnično uporabna, če imate dve različici na dveh različnih listih in želite hitro preveriti, kaj se je spremenilo.

Upoštevajte, da vi NE MOŽEM primerjajte dva lista v različnih delovnih zvezkih.

Ker se pogojno oblikovanje ne more sklicevati na zunanjo Excelovo datoteko, morajo biti listi, ki jih morate primerjati, v istem Excelovem delovnem zvezku. V primeru, da to niso, lahko kopirate list iz druge datoteke v aktivni delovni zvezek in nato naredite to primerjavo.

V tem primeru recimo, da imate nabor podatkov, kot je prikazano spodaj, za dva meseca (januar in februar) na dveh različnih listih in želite hitro primerjati podatke na teh dveh listih in preveriti, ali so se cene teh postavk spremenile ali ne.

Spodaj so navedeni koraki za to:

1. Izberite podatke na listu, kjer želite označiti spremembe. Ker želim preveriti, kako so se cene spremenile od januarja do februarja, sem podatke izbral na listu februar.
2. Kliknite zavihek Domov
3. V skupini Slogi kliknite »Pogojno oblikovanje«
4. V prikazanih možnostih kliknite »Novo pravilo«
5. V pogovornem oknu »Novo pravilo oblikovanja« kliknite »Uporabi formulo, da določite, katere celice želite formatirati«
6. V polje formule vnesite naslednjo formulo: = B2Jan! B2
7. Kliknite gumb Oblika
8. V pogovornem oknu Oblikovanje celic, ki se prikaže, kliknite zavihek »Izpolni« in izberite barvo, v kateri želite označiti neujemajoče se podatke.
9. Kliknite V redu
10. Kliknite V redu

Zgornji koraki bi takoj poudarili vse spremembe v naboru podatkov na obeh listih.

Kako to deluje?

Pogojno oblikovanje označi celico, ko dana formula za to celico vrne TRUE. V tem primeru primerjamo vsako celico na enem listu z ustrezno celico na drugem listu (izvedeno z uporabo operatorja, ki ni enak formuli).

Ko pogojno oblikovanje ugotovi kakršno koli razliko v podatkih, to poudari na listu Jan (tistemu, v katerem smo uporabili pogojno oblikovanje.

Upoštevajte, da sem v tem primeru uporabil relativno referenco (A1 in ne $ A $ 1 ali $ A1 ali A $ 1).

Ko s to metodo primerjate dva lista v Excelu, si zapomnite naslednje;

• Ta metoda je dobra za hitro prepoznavanje razlik, vendar je ne morete uporabljati stalno. Če na primer vnesem novo vrstico v kateri koli od naborov podatkov (ali izbrišem vrstico), bi to dalo napačne rezultate. Takoj, ko vrstico vstavim/izbrišem, se vse naslednje vrstice štejejo za različne in ustrezno označijo.
• Primerjate lahko le dva lista v isti Excelovi datoteki
• Primerjate lahko samo vrednost (ne razlike v formuli ali oblikovanju).

Primerjajte dve Excelovi datoteki/listi in poiščite razlike s formulo

Če vas zanima le hitra primerjava in prepoznavanje razlik med dvema listoma, lahko s formulo pridobite le tiste vrednosti, ki so različne.

Za to metodo boste morali imeti ločen delovni list, kjer boste lahko pridobili razlike.

Ta metoda bi delovala, če želite primerjati dva ločena delovna zvezka ali delovne liste programa Excel v istem delovnem zvezku.

Naj vam pokažem primer, kjer primerjam dva nabora podatkov v dveh listih (v istem delovnem zvezku).

Recimo, da imate nabor podatkov, kot je prikazano spodaj na listu z imenom Jan (in podobne podatke na listu z imenom Feb), in želite vedeti, katere vrednosti so različne.

Če želite primerjati oba lista, najprej vstavite nov delovni list (imenujmo ta list »razlika«).

V celico A1 vnesite naslednjo formulo:

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

Kopirajte in prilepite to formulo za obseg, tako da zajema celoten nabor podatkov na obeh listih. Ker imam majhen nabor podatkov, bom to formulo kopiral in prilepil le v obseg A1: B10.

Zgornja formula uporablja pogoj IF za preverjanje razlik. Če ni razlike v vrednostih, se vrne prazno, v primeru razlike pa vrednosti iz obeh listov v ločenih vrsticah v isti celici.

Dobra stvar pri tej metodi je, da vam daje le razlike in vam natančno pokaže, v čem je razlika. V tem primeru lahko enostavno vidim, da so cene v celicah B4 in B8 različne (kot tudi natančne vrednosti v teh celicah).

Primerjajte dve Excelovi datoteki/listi in pridobite razlike z uporabo VBA

Če morate precej pogosto primerjati Excelove datoteke ali liste, je dobro, da imate pripravljeno Excelovo makro kodo VBA in jo uporabite kadar koli za primerjavo.

Makro lahko dodate tudi v orodno vrstico za hitri dostop, tako da lahko dostopate z enim samim gumbom in takoj ugotovite, katere celice so različne v različnih datotekah/listih.

Recimo, da imate dva lista januarja in februarja in želite primerjati in poudariti razlike v listu Jan, lahko uporabite spodnjo kodo VBA:

Sub CompareSheets () Zatemni rngCell kot obseg za vsako rngCell na delovnih listih ("Jan"). UsedRange If Not rngCell = Delovni listi ("Feb"). Celice (rngCell.Row, rngCell.Column) Nato rngCell.Interior.Color = vbYellow End Če je naslednji rngCell End Sub

Zgornja koda uporablja zanko For Next za prehod skozi vsako celico na listu Jan (celotno uporabljeno območje) in jo primerja z ustrezno celico na listu Feb. Če odkrije razliko (kar se preveri z stavkom Če-potem), te celice označi z rumeno.

To kodo lahko uporabite v običajnem modulu v urejevalniku VB.

Če morate to pogosto početi, je bolje, da to kodo shranite v delovni zvezek Personal Macro in jo nato dodate v orodno vrstico za hitri dostop. Na ta način boste to primerjavo lahko naredili s klikom na gumb.

Tu so koraki za pridobitev osebnega delovnega zvezka za makro v Excelu (privzeto ni na voljo, zato ga morate omogočiti).

Tu so koraki za shranjevanje te kode v delovnem zvezku osebnega makra.

Tu boste našli korake za dodajanje te makro kode v QAT.

Uporaba orodja tretje osebe - Primerjalnik XL

Drug hiter način za primerjavo dveh Excelovih datotek ter preverjanje ujemanja in razlik je uporaba brezplačnega orodja drugih proizvajalcev, kot je XL Comparator.

To je spletno orodje, kjer lahko naložite dve Excelovi datoteki in ustvari primerjalno datoteko, ki bo imela skupne podatke (ali različne podatke glede na izbrano možnost.

Recimo, da imate dve datoteki z naborom podatkov o strankah (na primer ime in e -poštni naslov) in želite hitro preveriti, katere stranke so v datoteki 1 in ne v datoteki 2.

Spodaj je opisano, kako primerjate dve Excelovi datoteki in ustvarite primerjalno poročilo:

1. Odprite https://www.xlcomparator.net/
2. Z možnostjo Izberi datoteko naložite dve datoteki (največja velikost vsake datoteke je lahko 5 MB)
3. Kliknite gumb Naprej.
4. Izberite skupni stolpec v obeh datotekah. Orodje bo uporabljalo ta skupni stolpec za iskanje ujemanj in razlik
5. Izberite eno od štirih možnosti, ne glede na to, ali želite dobiti ujemajoče se podatke ali različne podatke (glede na datoteko 1 ali datoteko 2)
6. Kliknite Naprej
7. Prenesite primerjalno datoteko, ki bo vsebovala podatke (glede na možnost, ki ste jo izbrali v 5. koraku)

Spodaj je videoposnetek, ki prikazuje, kako deluje orodje XL Comparator.

Pri uporabi orodja drugega proizvajalca za primerjavo Excelovih datotek vas lahko skrbi zasebnost. Če imate zaupne podatke in je zasebnost za njih res pomembna, je bolje uporabiti druge zgoraj prikazane metode. Upoštevajte, da spletno mesto XL Comparator omenja, da po 1 uri primerjave izbrišejo vse datoteke.

To je nekaj načinov, s katerimi lahko primerjate dve različni Excelovi datoteki (ali delovne liste v isti Excelovi datoteki). Upam, da vam je bila ta vadnica v Excelu uporabna.