Kako usporediti dva Excel lista (za razlike)

Pogledajte video - Kako usporediti dva Excel lista za razlike

Usporedba dviju Excel datoteka (ili usporedba dva lista u istoj datoteci) može biti zeznuta jer Excel radna knjiga prikazuje samo jedan po jedan list.

To postaje teže i sklonije pogreškama kada imate puno podataka koje je potrebno usporediti.

Srećom, u Excelu postoje neke izvrsne značajke koje vam omogućuju otvaranje i jednostavno uspoređivanje dviju Excel datoteka.

U ovom Excel vodiču pokazat ću vam više načina usporedite dvije različite Excel datoteke (ili listove) i provjerite postoje li razlike. Način koji odaberete ovisit će o tome kako su vaši podaci strukturirani i kakvu usporedbu tražite.

Započnimo!

Usporedite dva Excelova lista u zasebnim Excel datotekama (usporedno)

Ako želite usporediti dvije zasebne Excel datoteke (ili dva lista u istoj radnoj knjizi), za to postoji ugrađena značajka u Excelu.

To je Pogled Rame uz rame opcija.

To se preporučuje samo ako imate mali skup podataka, a ručno uspoređivanje ovih datoteka vjerojatno će oduzimati manje vremena i sklono je pogreškama. Ako imate veliki skup podataka, preporučujem korištenje uvjetne metode ili metode formule obrađene kasnije u ovom vodiču.

Pogledajmo kako to koristiti kada morate usporediti dvije zasebne datoteke ili dva lista u istoj datoteci.

Pretpostavimo da imate dvije datoteke za dva različita mjeseca i želite provjeriti koje su vrijednosti različite u ove dvije datoteke.

Prema zadanim postavkama, kada otvorite datoteku, vjerojatno će zauzeti cijeli zaslon. Čak i ako smanjite veličinu, uvijek vidite jednu Excel datoteku na vrhu.

S opcijom prikaza paralelno, možete otvoriti dvije datoteke, a zatim ih rasporediti vodoravno ili okomito. To vam omogućuje jednostavno uspoređivanje vrijednosti bez prebacivanja naprijed -natrag.

U nastavku su navedeni koraci za usklađivanje dvije datoteke jednu pored druge i njihovu usporedbu:

1. Otvorite datoteke koje želite usporediti.
2. U svakoj datoteci odaberite list koji želite usporediti.
3. Kliknite karticu Pogled
4. U grupi Windows kliknite opciju ‘View Side by Side’. To postaje dostupno samo kad imate otvorene dvije ili više Excel datoteka.

Čim pritisnete opciju Pogled jedan pored drugog, Excel će vodoravno rasporediti radnu knjigu. Obje će datoteke biti vidljive, a vi ih možete urediti/usporediti dok su poredane jedna pored druge.

U slučaju da želite postaviti datoteke okomito, kliknite na opciju Rasporedi sve (na kartici Pogled).

Ovo će otvoriti dijaloški okvir "Rasporedi Windows" u kojem možete odabrati "Okomito".

U ovom trenutku, ako se pomaknete prema dolje na jednom od radnih listova, drugi će ostati isti. To možete promijeniti tako da se prilikom listanja po jednom listu i drugi pomiče u isto vrijeme. To olakšava usporedbu linija po linija i uočava razlike.

No da biste to učinili, morate omogućiti Sinkrono pomicanje.

Da biste omogućili sinkrono pomicanje, kliknite karticu Prikaz (u bilo kojoj radnoj knjizi), a zatim kliknite opciju Sinkrono pomicanje. Ovo je gumb za prebacivanje (pa ako ga želite isključiti, jednostavno ponovno kliknite na njega).

Usporedba više listova u zasebnim Excel datotekama (usporedno)

Pomoću opcije ‘View Side by Side’ možete usporediti samo dvije Excel datoteke odjednom.

U slučaju da imate više otvorenih Excel datoteka, kad kliknete na opciju View Side by Side, prikazat će vam se dijaloški okvir "Usporedi usporedno" u kojem možete odabrati koju datoteku želite usporediti s aktivnom radnom knjigom.

U slučaju da želite usporediti više od dvije datoteke odjednom, otvorite sve te datoteke, a zatim kliknite opciju Rasporedi sve (nalazi se na kartici Pogled).

U dijaloškom okviru Rasporedi Windows odaberite vertikalno/vodoravno, a zatim kliknite U redu.

To će rasporediti sve otvorene Excel datoteke po odabranom redoslijedu (okomito ili vodoravno).

Usporedite dva lista (jedan do drugog) u istoj radnoj knjizi programa Excel

U slučaju da želite usporediti dva odvojena lista u istoj radnoj knjizi, ne možete koristiti značajku Prikaz jedan pored drugog (jer radi samo za zasebne datoteke programa Excel).

Ali ipak možete napraviti istu usporedbu usporedo.

To omogućuje "Nova značajka sustava Windows u Excelu to vam omogućuje otvaranje dvije instance u istoj radnoj knjizi. Nakon što otvorite dvije instance, možete ih poredati jedan pored drugog, a zatim ih usporediti.

Pretpostavimo da imate radnu knjigu programa Excel koja ima dva lista za dva različita mjeseca (siječanj i veljača) i želite ih usporediti usporedno kako biste vidjeli kako se promijenila prodaja po trgovini:

U nastavku su navedeni koraci za usporedbu dva lista u Excelu:

1. Otvorite radnu knjigu s listovima koje želite usporediti.
2. Kliknite karticu Pogled
3. U grupi Prozor kliknite opciju ‘Novi prozor’. Time se otvara druga instanca iste radne knjige.
4. Na kartici "Pogled" kliknite "Rasporedi sve". Ovo će otvoriti dijaloški okvir Rasporedi Windows
5. Odaberite "Okomito" za usporedbu podataka u stupcima (ili odaberite Vodoravno ako želite usporediti podatke u retcima).
6. Pritisnite U redu.

Gore navedeni koraci okomito će rasporediti obje instance radne knjige.

U ovom trenutku obje radne knjige imale bi isti radni list. U jednoj od radnih knjiga odaberite drugi list koji želite usporediti s aktivnim listom.

Kako ovo radi?

Kada kliknete na Novi prozor, ponovno se otvara ista radna knjiga s nešto drugačijim imenom. Na primjer, ako je naziv vaše radne knjige "Test" i kliknete na Novi prozor, već će otvorenu radnu knjigu nazvati "Test - 1", a drugu instancu kao "Test - 2".

Imajte na umu da se radi o istoj radnoj knjizi. Unesete li bilo koju promjenu u bilo koju od ovih knjiga, to će se odraziti na obje.

A kad zatvorite bilo koju instancu otvorene datoteke, naziv će se vratiti na izvornik.

Također možete omogućiti sinkrono pomicanje ako želite (klikom na opciju "Sinkrono pomicanje" na kartici "Prikaz")

Usporedite dva lista i istaknite razlike (koristeći uvjetno oblikovanje)

Iako možete koristiti gornju metodu za usklađivanje radnih knjiga i ručno pregledavanje podataka redak po redak, to nije dobar način u slučaju da imate puno podataka.

Također, ručno izvođenje ove razine usporedbe može dovesti do mnogo pogrešaka.

Stoga, umjesto da to radite ručno, možete upotrijebiti moć uvjetnog oblikovanja za brzo isticanje razlika u dva Excel lista.

Ova je metoda doista korisna ako imate dvije inačice na dva različita lista i želite brzo provjeriti što se promijenilo.

Imajte na umu da vi NE MOŽETE usporedite dva lista u različitim radnim bilježnicama.

Budući da se uvjetno oblikovanje ne može odnositi na vanjsku Excel datoteku, listovi koje trebate usporediti moraju biti u istoj radnoj knjizi programa Excel. U slučaju da nisu, možete kopirati list iz druge datoteke u aktivnu radnu knjigu i zatim napraviti ovu usporedbu.

U ovom primjeru, pretpostavimo da imate skup podataka kao što je prikazano dolje za dva mjeseca (siječanj i veljača) na dva različita lista i želite brzo usporediti podatke u ta dva lista i provjeriti jesu li se cijene ovih stavki promijenile ili ne.

U nastavku su navedeni koraci za to:

1. Odaberite podatke na listu gdje želite istaknuti promjene. Budući da želim provjeriti kako su se cijene promijenile od siječnja do veljače, odabrao sam podatke u listu za veljaču.
2. Kliknite karticu Početna
3. U grupi Stilovi kliknite "Uvjetno oblikovanje"
4. U opcijama koje se pojavljuju kliknite "Novo pravilo"
5. U dijaloškom okviru "Novo pravilo oblikovanja" kliknite "Koristi formulu za određivanje ćelija koje ćete oblikovati"
6. U polje formule unesite sljedeću formulu: = B2Jan! B2
7. Pritisnite gumb Oblikovanje
8. U dijaloškom okviru Oblikovanje ćelija koje se pojavi kliknite na karticu "Ispunite" i odaberite boju u kojoj želite istaknuti neusklađene podatke.
9. Pritisnite U redu
10. Pritisnite U redu

Gore navedeni koraci odmah bi istaknuli sve promjene u skupu podataka na oba lista.

Kako ovo radi?

Uvjetno oblikovanje označava ćeliju kada zadana formula za tu ćeliju vrati TRUE. U ovom primjeru uspoređujemo svaku ćeliju na jednom listu s odgovarajućom ćelijom na drugom listu (učinjeno pomoću operatora koji nije jednak operatoru u formuli).

Kad uvjetno oblikovanje pronađe bilo kakvu razliku u podacima, to se ističe u listu Jan (onom u kojem smo primijenili uvjetno oblikovanje.

Imajte na umu da sam u ovom primjeru koristio relativnu referencu (A1, a ne $ A $ 1 ili $ A1 ili A $ 1).

Kada koristite ovu metodu za usporedbu dva lista u Excelu, zapamtite sljedeće;

• Ova je metoda dobra za brzo prepoznavanje razlika, ali ne možete je koristiti stalno. Na primjer, ako unesem novi redak u bilo koji skup podataka (ili izbrišem redak), to bi mi dalo netočne rezultate. Čim umetnem/izbrišem redak, svi sljedeći retci smatraju se različitim i sukladno tome označeni.
• Možete usporediti samo dva lista u istoj Excel datoteci
• Možete usporediti samo vrijednost (ne i razliku u formuli ili oblikovanju).

Usporedite dvije Excel datoteke/listove i saznajte razlike pomoću formule

Ako vas zanima samo brzo uspoređivanje i identificiranje razlika između dva lista, možete koristiti formulu za dohvaćanje samo onih vrijednosti koje su različite.

Za ovu metodu morat ćete imati zaseban radni list na kojem možete dohvatiti razlike.

Ova bi metoda funkcionirala ako želite usporediti dvije zasebne radne knjige ili radne listove programa Excel u istoj radnoj knjizi.

Dopustite mi da vam pokažem primjer gdje uspoređujem dva skupa podataka u dva lista (u istoj radnoj knjizi).

Pretpostavimo da imate skup podataka kako je dolje prikazano na listu pod nazivom Jan (i slične podatke na listu koji se zove veljača), i želite znati koje su vrijednosti različite.

Za usporedbu dva lista najprije umetnite novi radni list (nazovimo ovaj list "razlika").

U ćeliju A1 unesite sljedeću formulu:

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

Kopirajte i zalijepite ovu formulu za raspon tako da pokriva cijeli skup podataka na oba lista. Budući da imam mali skup podataka, kopirat ću i zalijepiti ovu formulu samo u rasponu A1: B10.

Gornja formula koristi IF uvjet za provjeru razlika. U slučaju da nema razlike u vrijednostima, vratit će se prazno, a u slučaju razlike, vratit će vrijednosti s oba lista u zasebnim retcima u istoj ćeliji.

Dobra stvar s ovom metodom je što vam samo daje razlike i pokazuje vam u čemu je razlika. U ovom primjeru lako mogu vidjeti da su cijene u ćelijama B4 i B8 različite (kao i točne vrijednosti u tim ćelijama).

Usporedite dvije Excel datoteke/listove i saznajte razlike pomoću VBA -a

Ako često morate uspoređivati ​​Excel datoteke ili listove, dobro je imati spreman Excel makro VBA kôd i koristiti ga kad god trebate za usporedbu.

Također možete dodati makronaredbu na Alatnu traku za brzi pristup tako da možete pristupiti jednim gumbom i odmah znati koje su ćelije različite u različitim datotekama/listovima.

Pretpostavimo da imate dva lista u siječnju i veljači i želite usporediti i istaknuti razlike u listu Jan, možete upotrijebiti donji VBA kôd:

Sub CompareSheets () Dim rngCell As Range for each rngCell In Worksheets ("Jan"). UsedRange If Not rngCell = Worksheets ("Feb"). Cells (rngCell.Row, rngCell.Column) Zatim rngCell.Interior.Color = vbYellow End Ako sljedeće rngCell End Sub

Gornji kôd koristi petlju For Next za prolazak kroz svaku ćeliju na listu Jan (cijeli korišteni raspon) i uspoređuje je s odgovarajućom ćelijom na listu Feb. U slučaju da pronađe razliku (što se provjerava pomoću naredbe If-Then), one ćelije označiti žutom bojom.

Ovaj kôd možete koristiti u običnom modulu u VB Editoru.

A ako to morate često raditi, bolje je spremiti ovaj kôd u radnu knjigu Osobni makro, a zatim ga dodati na alatnu traku za brzi pristup. Na taj ćete način ovu usporedbu moći izvršiti pritiskom na gumb.

Evo koraka za dobivanje radne knjige za osobne makroe u Excelu (nije zadano dostupna pa je morate omogućiti).

Evo koraka za spremanje ovog koda u osobnu radnu knjigu makronaredbi.

Ovdje ćete pronaći korake za dodavanje ovog makro koda u QAT.

Korištenje alata treće strane - XL komparator

Još jedan brz način za usporedbu dviju Excel datoteka i provjeru podudarnosti i razlika je korištenje besplatnog alata treće strane, poput XL Comparator-a.

Ovo je web-alat na koji možete učitati dvije Excel datoteke i stvorit će datoteku za usporedbu koja će sadržavati zajedničke podatke (ili različite podatke ovisno o tome koju ste opciju odabrali.

Pretpostavimo da imate dvije datoteke koje imaju skupove podataka o korisnicima (kao što su ime i adresa e -pošte), i želite brzo provjeriti koji su to korisnici, postoji li datoteka 1, a ne datoteka 2.

U nastavku donosimo kako uspoređujete dvije Excel datoteke i stvarate izvješće o usporedbi:

1. Otvorite https://www.xlcomparator.net/
2. Upotrijebite opciju Odaberi datoteku za prijenos dvije datoteke (maksimalna veličina svake datoteke može biti 5 MB)
3. Pritisnite gumb Dalje.
4. Odaberite zajednički stupac u obje ove datoteke. Alat će koristiti ovaj uobičajeni stupac za traženje podudarnosti i razlika
5. Odaberite jednu od četiri opcije, želite li dobiti odgovarajuće podatke ili različite podatke (na temelju datoteke 1 ili datoteke 2)
6. Pritisnite Dalje
7. Preuzmite datoteku za usporedbu koja će sadržavati podatke (na temelju opcije koju ste odabrali u koraku 5)

Ispod je videozapis koji prikazuje kako alat XL Comparator radi.

Jedna briga koju možete upotrijebiti pri korištenju alata treće strane za usporedbu Excel datoteka odnosi se na privatnost. Ako imate povjerljive podatke i privatnost im je zaista važna, bolje je upotrijebiti druge metode prikazane gore. Imajte na umu da web mjesto XL Comparator spominje da brišu sve datoteke nakon 1 sata od usporedbe.

Ovo su neke od metoda koje možete koristiti za usporedbu dviju različitih Excel datoteka (ili radnih listova u istoj Excel datoteci). Nadam se da vam je ovaj Excel vodič bio koristan.