Metode poređenja tablica u Microsoft Excel-u

Korisnici Excel-a često se suočavaju sa zadatkom da uspoređuju dvije tablice ili liste kako bi identificirali razlike ili nedostajuće elemente u njima. Svaki se korisnik na svoj način nosi s ovim zadatkom, ali najčešće se prilično dugo vremena troši na rješavanje ovog pitanja, jer nisu svi pristupi ovom problemu racionalni. U isto vrijeme, postoji nekoliko provjerenih algoritama djelovanja koji će vam omogućiti da usporedite popise ili tablice nizova u prilično kratkom vremenu uz minimalan napor. Pogledajmo bliže ove mogućnosti.

Vidi također: Poređenje dva dokumenta u MS Word-u

Metode poređenja

Postoji nekoliko načina za usporedbu tabličnih prostora u Excelu, ali ih je sve moguće podijeliti u tri velike grupe:

Na temelju ove klasifikacije odabire se prije svega metoda usporedbe, kao i određene akcije i algoritmi za zadatak. Na primjer, kada uspoređujete različite knjige, morate istovremeno otvoriti dvije datoteke Excela.

Pored toga, treba reći da usporedba područja tablica ima smisla samo kada imaju sličnu strukturu.

1. metoda: jednostavna formula

Najlakši način za usporedbu podataka u dvije tablice je korištenje jednostavne formule jednakosti. Ako se podaci podudaraju, tada daje indikator TRUE, a ako ne, onda FALSE. Možete uporediti i numeričke i tekstualne podatke. Nedostatak ove metode je taj što se ona može koristiti samo ako su podaci u tablici poredani ili poredani na isti način, sinkronizirani i imaju isti broj redaka. Pogledajmo kako koristiti ovu metodu u praksi na primjeru dviju tablica smještenih na jednom listu.

Dakle, imamo dvije jednostavne tablice sa popisima zaposlenih i njihovim plaćama. Potrebno je uporediti liste zaposlenih i utvrditi nedosljednosti između stupaca u koja su postavljena imena.

1. Da bismo to učinili, potrebna nam je dodatna kolona na listu. Tamo unosimo znak "=". Zatim kliknemo na prvu stavku koju želite uporediti u prvoj listi. Opet stavljamo simbol "=" sa tastature. Zatim kliknite na prvu ćeliju stupca koju uspoređujemo u drugoj tablici. Rezultat je izraz sljedeće vrste:

   = A2 = D2

   

   Iako će, naravno, u svakom slučaju koordinate biti različite, ali suština će ostati ista.

2. Kliknite na dugme Unesiteda biste dobili rezultate upoređivanja Kao što možete vidjeti, uspoređujući prve ćelije obje liste, program je pokazao indikator "TRUE", što znači podudaranje podataka.



3. Sada je potrebno izvršiti sličnu operaciju s ostalim ćelijama obje tablice u stupcima koje uspoređujemo. Ali jednostavno možete kopirati formulu, što će značajno uštedjeti vrijeme. Ovaj faktor je posebno važan pri uspoređivanju popisa s velikim brojem redaka.

   Postupak kopiranja najjednostavnije se provodi pomoću markera za unos. Prelazimo pokazivač iznad donjeg desnog ugla ćelije, gde smo dobili indikator "TRUE". U isto vrijeme to bi trebalo pretvoriti u crni križ. Ovo je marker za ispunu. Pritisnite lijevu tipku miša i povucite kursor prema dolje na broju redaka u upoređenim nizovima tablica.



4. Kao što vidite, sada su u dodatnom stupcu prikazani svi rezultati usporedbe podataka u dva stupca niza tablica. U našem slučaju podaci o samo jednoj liniji nisu se podudarali. Kada ih upoređujemo, formula je dala rezultat FALSE. Kao što vidimo, za sve ostale retke, formula za usporedbu dala je pokazatelj "TRUE".



5. Pored toga, moguće je izračunati broj odstupanja pomoću posebne formule. Da biste to učinili, odaberite element lista na kojem će biti prikazan. Zatim kliknite na ikonu "Umetanje funkcije".



6. U prozoru Čarobnjaci za funkcije u grupi operatora "Matematički" odaberite ime SUMPRODUCT. Kliknite na dugme "OK".



7. Prozor argumenta funkcije je aktiviran. SUMPRODUCTčiji je glavni zadatak izračunati zbroj proizvoda odabranog raspona. Ali ova se funkcija može iskoristiti u naše svrhe. Sintaksa je prilično jednostavna:

   = SUMPRODUCT (array1; array2; ...)

   Kao argumenti mogu se koristiti adrese do 255 nizova. Ali u našem slučaju koristit ćemo samo dva niza, osim toga, kao jedan argument.

   Stavite kursor u polje "Array1" i na listu odaberite upoređeni raspon podataka u prvom području. Nakon toga, u polje stavite znak nije jednako () i odaberite upoređeni raspon druge regije. Dalje, zamotajte dobiveni izraz u zagrade ispred kojih stavljamo dva znaka "-". U našem slučaju, ispalo je:

   - (A2: A7D2: D7)

   Kliknite na dugme "OK".



8. Operator izračunava i prikazuje rezultat. Kao što vidite, u našem slučaju rezultat je jednak broju "1", to znači da je pronađena jedna neusklađenost na upoređenim popisima. Da su popisi potpuno identični, rezultat bi bio jednak broju "0".

Na isti način možete usporediti podatke u tablicama koje se nalaze na različitim listovima. Ali u ovom slučaju, poželjno je da linije u njima budu numerisane. Inače, postupak usporedbe gotovo je potpuno isti kao što je opisano gore, osim činjenice da kad unesete formulu morate prelaziti između listova. U našem slučaju izraz će izgledati ovako:

= B2 = Sheet2! B2

To je, kako vidimo, prije koordinata podataka, koji se nalaze na drugim listovima, osim gdje je prikazan rezultat usporedbe, označen broj lista i uskličnik.

Metoda 2: odabir grupa ćelija

Poređenje se može izvršiti korištenjem alata za odabir ćelija. Također se može koristiti za usporedbu samo sinkroniziranih i naručenih popisa. Uz to, u ovom slučaju bi se popisi trebali nalaziti jedan pored drugog na istom listu.

1. Biramo upoređene nizove. Idite na karticu "Početna". Zatim kliknite na ikonu Pronađi i označikoji se nalazi na vrpci u okviru s alatima "Uređivanje". Otvara se spisak na kome možete odabrati poziciju "Odabir grupe ćelija ...".

   

   Pored toga, možemo doći do željenog prozora za odabir grupe ćelija na drugi način. Ova će opcija biti posebno korisna za one korisnike koji su instalirali verziju programa ranije od Excel 2007, budući da je metoda preko gumba Pronađi i označi ove aplikacije ne podržavaju. Odaberemo nizove koje želimo usporediti i pritisnite tipku F5.

2. Aktiviran je mali prelazni prozor. Kliknite na dugme "Odaberite ..." u njegovom donjem levom uglu.



3. Nakon toga, bilo koja od gore navedenih opcija koje odaberete, pokreće se prozor za odabir skupina ćelija. Podesite prekidač u položaj "Odaberi liniju po liniju". Kliknite na dugme "OK".



4. Kao što vidite, nakon toga će se neusklađene vrijednosti linija istaknuti s drugačijom nijansom. Osim toga, kao što se može prosuditi iz sadržaja trake formule, program će aktivirati jednu od ćelija smještenih u navedenim neusklađenim linijama.

Metoda 3: uslovno oblikovanje

Možete uporediti pomoću metode uslovnog oblikovanja. Kao i u prethodnoj metodi, usporedjena područja trebaju biti na istom Excel-ovom radnom listu i biti međusobno sinkronizirana.

1. Prije svega, biramo koju ćemo tablicu smatrati glavnom, a u kojoj ćemo tražiti razlike. Uradimo posljednje u drugoj tabeli. Zbog toga biramo listu radnika koja se nalazi u njemu. Prelaskom na karticu "Početna"kliknite na gumb Uslovno oblikovanjekoji se nalazi na traci u bloku Stilovi. Idite na padajuću listu Upravljanje pravilima.



2. Prozor upravitelja pravila je aktiviran. Kliknite na dugme u njemu Kreirajte pravilo.



3. U prozoru koji se pokrene odaberite položaj Koristite formulu. U polju "Formatiraj ćelije" napisati formulu koja sadrži adrese prvih ćelija raspona uspoređenih stupaca, odijeljenih znakom "nije jednako" () Samo će se ovaj put suočiti s ovim izrazom. "=". Nadalje, apsolutno adresiranje mora se primijeniti na sve koordinate stupca u ovoj formuli. Da biste to učinili, izaberite formulu sa kursorom i pritisnite tipku tri puta F4. Kao što vidite, znak dolara prikazan je u blizini svih adresa stupaca, što znači pretvaranje veza u apsolutne. Za naš konkretni slučaj, formula će imati sljedeći oblik:

   = A2 $ D2

   Ovaj izraz pišemo u gore polje. Nakon toga kliknite na gumb "Formatiraj ...".



4. Prozor je aktiviran Format ćelije. Idite na karticu "Fill". Ovdje na listi boja zaustavljamo izbor na boji kojom želimo obojiti one elemente kod kojih se podaci neće podudarati. Kliknite na dugme "OK".



5. Vraćajući se prozoru za kreiranje pravila za formatiranje, kliknite na dugme "OK".



6. Nakon automatskog prelaska na prozor Menadžer pravila kliknite na gumb "OK" i u njemu.



7. U drugoj će se tablici elementi koji sadrže podatke koji se ne podudaraju sa odgovarajućim vrijednostima područja prve tablice biti označeni odabranom bojom.

Postoji još jedan način primjene uvjetnog oblikovanja na zadatak. Kao i prethodne opcije zahtijeva postavljanje oba uspoređena područja na istom listu, ali za razliku od prethodno opisanih metoda, uvjet za sinkronizaciju ili sortiranje podataka neće biti obavezan, što razlikuje ovu opciju od prethodno opisane.

1. Odaberemo područja koja će se upoređivati.



2. Idite na karticu zvanu "Početna". Kliknite na dugme Uslovno oblikovanje. Na aktiviranom spisku odaberite položaj Pravila za odabir ćelija. U sljedećem izborniku donosimo izbor položaja Duplikatne vrijednosti.



3. Pokreće se prozor za postavljanje izbora duplikata vrijednosti. Ako ste sve učinili ispravno, u ovom prozoru ostaje samo klikanje na gumb "OK". Iako po želji u odgovarajućem polju ovog prozora možete odabrati drugu boju isticanja.



4. Nakon što izvršimo navedenu radnju, svi elementi koji se ponavljaju bit će istaknuti odabranom bojom. Oni elementi koji se ne podudaraju ostat će obojeni u izvornoj boji (bijela prema zadanim postavkama). Tako odmah možete vizualno vidjeti koja je razlika između niza.

Po želji možete, naprotiv, obojiti neusklađene elemente, a one pokazatelje koji se podudaraju, ispunjenje ostaviti istom bojom. Algoritam postupaka gotovo je isti, ali u prozoru postavki za isticanje dupliciranih vrijednosti u prvom polju umjesto Duplikat treba odabrati "Unique". Nakon toga kliknite na gumb "OK".

Tako će se istaknuti upravo oni pokazatelji koji se ne poklapaju.

Lekcija: Uslovno oblikovanje u Excelu

Metoda 4: složena formula

Također možete usporediti podatke koristeći složenu formulu na osnovu funkcije RAČUNANJE. Pomoću ovog alata možete izračunati koliko se svaki element iz odabranog stupca druge tablice ponavlja u prvom.

Operator RAČUNANJE odnosi se na statističku grupu funkcija. Njegov je zadatak da broji broj ćelija čije vrijednosti zadovoljavaju dani uvjet. Sintaksa ovog operatora je sljedeća:

= COUNTIF (raspon; kriterijum)

Argument "Domet" predstavlja adresu matrice u kojoj su izračunate podudarne vrijednosti.

Argument "Kriterij" postavlja uvjet podudarnosti. U našem slučaju to će biti koordinate određenih ćelija u području prve tablice.

1. Odabiremo prvi element dodatne kolone u koji će se brojati broj podudaranja. Zatim kliknite na ikonu "Umetanje funkcije".



2. Pokretanje Čarobnjaci za funkcije. Idite u kategoriju "Statistički". Pronađite ime na listi "COUNTIF". Nakon što ste ga odabrali, kliknite na gumb "OK".



3. Pokreće se prozor argumenata operatora RAČUNANJE. Kao što vidite, imena polja u ovom prozoru odgovaraju imenima argumenata.

   Postavite kursor u polje "Domet". Nakon toga, držeći lijevu tipku miša, odaberite sve vrijednosti stupca s imenima druge tablice. Kao što vidite, koordinate odmah padaju u specificirano polje. Ali u naše svrhe, ovu bi adresu trebalo učiniti apsolutnom. Da biste to učinili, odaberite ove koordinate u polju i pritisnite tipku F4.

   Kao što vidite, veza je poprimila apsolutnu formu, koju karakteriše prisustvo znakova dolara.

   Zatim idite na teren "Kriterij"postavljanjem kursora tamo. Kliknemo na prvi element s prezimenima u rasponu prve tablice. U ovom slučaju ostavite srodnika veze. Nakon što je prikazano u polju, možete kliknuti na gumb "OK".



4. Rezultat je prikazan u elementu lista. To je jednako broju "1". To znači da je u popisu imena druge tablice prezime "Grinev V.P.", koja je prva na listi prvog niza tablice, pojavljuje se jednom.



5. Sada moramo kreirati sličan izraz za sve ostale elemente prve tablice. Da bismo to učinili, kopirat ćemo koristeći marker za ispunjavanje, kao što smo već radili. Postavite kursor u donji desni dio elementa lista koji sadrži funkciju RAČUNANJE, a nakon pretvorbe u marker za punjenje držite lijevu tipku miša i povucite kursor prema dolje.



6. Kao što vidite, program je izračunao slučajnosti usporedbom svake ćelije prve tablice s podacima smještenim u drugom rasponu tablice. U četiri slučaja ispao je rezultat "1"i u dva slučaja - "0". Odnosno, program u drugoj tablici nije mogao pronaći dvije vrijednosti koje se nalaze u prvoj tablici.

Naravno da se ovaj izraz, kako bi se uporedili tabelarni pokazatelji, može upotrijebiti u postojećem obliku, ali postoji prilika da se poboljša.

Osiguravamo da se one vrijednosti koje se nalaze u drugoj tablici, ali nisu u prvoj, prikazuju na zasebnom popisu.

1. Prvo ćemo malo preraditi našu formulu RAČUNANJEnaime, to činimo jednim od argumenata operatera AKO. Da biste to učinili, odaberite prvu ćeliju u kojoj se nalazi operator RAČUNANJE. U red formula prije njega dodajte izraz AKO bez navodnika i otvorite nosač. Zatim, da biste nam olakšali posao, odaberite vrijednost u traci s formulama AKO i kliknite na ikonu "Umetanje funkcije".



2. Otvara se prozor argumenata funkcije AKO. Kao što vidite, prvo polje prozora je već popunjeno vrijednošću operatera RAČUNANJE. Ali ovom polju moramo dodati još nešto. Tamo smo postavili kursor i dodali postojećem izrazu "=0" bez citata

   Nakon toga otiđite na teren "Znači ako je tačno". Ovdje ćemo koristiti još jednu ugniježđenu funkciju - LINE. Unesite reč LINE bez navodnika, a zatim otvorite zagrade i odredite koordinate prve ćelije s prezimenom u drugoj tablici, a zatim zatvorite zagrade. Točnije, u našem slučaju na terenu "Znači ako je tačno" Ispalo je sledeće:

   LINE (D2)

   Sada operator LINE će prijaviti funkcije AKO broj retka u kojem se nalazi određeno prezime i u slučaju kada je ispunjen uvjet naveden u prvom polju, funkcija AKO će prikazati ovaj broj u ćeliji. Kliknite na dugme "OK".



3. Kao što vidite, prvi se rezultat prikazuje kao FALSE. To znači da vrijednost ne zadovoljava uvjete operatera. AKO. Odnosno, prvo prezime je prisutno na obje liste.



4. Koristeći oznaku ispunjavanja, kopiramo izraz operatera na uobičajeni način AKO na celoj koloni. Kao što vidite, za dvije pozicije koje su prisutne u drugoj tablici, ali ne i u prvoj, formula daje brojeve linija.



5. Odlazimo iz područja tablice s desne strane i stupac ispunjavamo brojevima redom, počevši od 1. Broj brojeva mora odgovarati broju redaka u drugoj tablici za usporedbu. Da biste ubrzali postupak numeriranja, možete koristiti i marker za ispunjavanje.



6. Nakon toga odaberite prvu ćeliju desno od stupca s brojevima i kliknite na ikonu "Umetanje funkcije".



7. Otvara se Čarobnjak za funkcije. Idite u kategoriju "Statistički" i birajte ime "LEAST". Kliknite na dugme "OK".



8. Funkcija Najmanječiji je prozor argumenta otvoren, namijenjen je za prikaz najmanje vrijednosti navedene u računu.

   U polju Niz odredite koordinate raspona dodatnog stupca "Broj podudaranja"koju smo prethodno pretvorili pomoću funkcije AKO. Sve veze činimo apsolutnima.

   U polju "K" označava koji račun treba prikazati najnižu vrijednost. Ovdje označavamo koordinate prve ćelije kolone s brojenjem, koju smo nedavno dodali. Ostavimo adresu srodnika. Kliknite na dugme "OK".



9. Operator prikazuje rezultat - broj 3. To je najmanji od brojanja neusklađenih redova tabličnih nizova. Kopirajte formulu na dno oznake za unos.



10. Sada, znajući brojeve linija neusklađenih elemenata, u funkciju možemo umetnuti u ćeliju njihove vrijednosti INDEX. Odaberite prvi element lista koji sadrži formulu Najmanje. Nakon toga, prijeđite na red formula i prije imena "LEAST" dodaj ime INDEX bez navodnika, odmah otvorite nosač i stavite tačku sa zarezom (;) Zatim odaberite ime u retku formula INDEX i kliknite na ikonu "Umetanje funkcije".



11. Nakon toga otvara se mali prozor u kojem trebate odrediti koji bi referentni prikaz trebao imati funkciju INDEX ili dizajnirani za rad s nizovima. Treba nam druga opcija. Instalirano je prema zadanim postavkama, pa u ovom prozoru samo kliknite gumb "OK".



12. Pokreće se prozor argumenta funkcije INDEX. Ovaj je operater namijenjen za iskazivanje vrijednosti koja se nalazi u određenom nizu u navedenom nizu.

    Kao što vidite, polje Broj linije već ispunjeno funkcijskim vrijednostima Najmanje. Od tamo već postojeće vrijednosti treba oduzeti razliku između numeriranja Excel lista i internog numeriranja područja tablice. Kao što vidite, imamo samo zaglavlje nad vrijednostima tablice. To znači da je razlika jedna linija. Stoga na terenu dodajemo Broj linije vrijednost "-1" bez citata

    U polju Niz odredite adresu raspona vrijednosti druge tablice. U isto vrijeme sve koordinate pravimo apsolutno, odnosno stavljamo ispred njih znak dolara na način na koji smo prethodno opisali.

    Kliknite na dugme "OK".



13. Nakon prikaza rezultata na ekranu, funkciju proširujemo pomoću markera ispunjavanja na dnu stupca. Kao što vidite, oba prezimena koja su prisutna u drugoj tabeli, ali nisu u prvoj, prikazana su u zasebnom rasponu.

5. način: usporedite nizove u različitim knjigama

Pri uspoređivanju raspona u različitim knjigama, možete koristiti gore navedene metode, osim onih opcija gdje želite obje površine tablice smjestiti na jedan list. Glavni uvjet postupka usporedbe u ovom slučaju je otvaranje prozora obje datoteke istovremeno. Za verzije Excel-a 2013 i novijih, kao i za verzije prije Excel-a 2007, nema problema s ovim stanjem. Ali u programima Excel 2007 i Excel 2010, kako bi se istovremeno otvorila oba prozora, potrebne su dodatne manipulacije. Kako se to radi opisano je u zasebnoj lekciji.

Lekcija: Kako otvoriti Excel u različitim prozorima

Kao što vidite, postoji niz mogućnosti za usporedbu tablica među sobom. Koja će se opcija koristiti ovisi o tome gdje su tačno tablični podaci smješteni jedan prema drugom (na jednom listu, u različitim knjigama, na različitim listovima), kao i o tome kako tačno korisnik želi da se ova usporedba prikaže na ekranu.