Metode za poređenje tabela u programu Microsoft Excel

Često se Excel korisnici suočavaju sa zadatkom da uporede dve tabele ili liste da bi identifikovali razlike ili elemente koji nedostaju u njima. Svaki korisnik se nosi sa ovim zadatkom na svoj način, ali najčešće se dosta vremena troši na rešavanje ovog problema, jer nisu svi pristupi ovom problemu racionalni. Istovremeno, postoji nekoliko dokazanih algoritama akcije koji će vam omogućiti da uporedite liste ili tabelarne nizove u relativno kratkom vremenu uz minimalan napor. Pogledajmo izbliza ove opcije.

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

Comparison Methods

Postoji nekoliko načina za poređenje tabelarnih prostora u Excelu, ali sve se mogu podijeliti u tri velike grupe:

Na osnovu ove klasifikacije biraju se, pre svega, metode poređenja i određuju specifične akcije i algoritmi za izvršenje zadatka. Na primer, kada pravite poređenja u različitim knjigama, morate istovremeno da otvorite dve Excel datoteke.

Pored toga, treba reći da poređenje tabelarnih prostora ima smisla samo kada imaju sličnu strukturu.

Metoda 1: jednostavna formula

Najlakši način za upoređivanje podataka u dvije tablice je korištenje jednostavne formule jednakosti. Ako se podaci poklapaju, onda daje TRUE vrijednost, a ako ne, onda - FALSE. Moguće je porediti i numeričke podatke i tekst. Nedostatak ove metode je u tome što se može koristiti samo ako su podaci u tablici uređeni ili sortirani na isti način, sinkronizirani i imaju jednak broj linija. Da vidimo kako se ova metoda koristi u praksi na primjeru dviju tablica postavljenih na jednom listu.

Dakle, imamo dve jednostavne tabele sa spiskom zaposlenih i njihovim platama. Neophodno je uporediti liste zaposlenih i identifikovati nedoslednosti između kolona u kojima se nalaze imena.

1. Za ovo nam je potrebna dodatna kolona na listu. Upišite znak tamo "=". Zatim kliknite na prvu stavku koja se uspoređuje u prvoj listi. Ponovo smo stavili simbol "=" sa tastature. Zatim kliknite na prvu ćeliju kolone, koju poredimo, u drugoj tabeli. Izraz je 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 Enterda biste dobili rezultate poređenja. Kao što možete videti, kada upoređujete prve ćelije na obe liste, program je pokazao indikator "TRUE"što znači da se podaci podudaraju.



3. Sada je potrebno izvršiti sličnu operaciju s preostalim ćelijama obiju tablica u stupcima koje uspoređujemo. Ali možete jednostavno kopirati formulu, što će značajno uštedjeti vrijeme. Ovaj faktor je posebno važan kada se uspoređuju liste sa velikim brojem linija.

   Postupak kopiranja je najlakše izvršiti pomoću ručice za punjenje. Postavljamo kursor na donji desni ugao ćelije, gde smo dobili indikator "TRUE". U isto vreme, trebalo bi da se pretvori u crni krst. Ovo je marker za popunjavanje. Kliknite levim tasterom miša i prevucite kursor prema dole prema broju linija u poredjenim tabelarnim nizovima.



4. Kao što vidimo, sada se u dodatnoj koloni prikazuju svi rezultati poređenja podataka u dva stupca tabličnih nizova. U našem slučaju, podaci se ne podudaraju samo u jednoj liniji. Kada se uporedi, formula je dala rezultat "FALSE". Za sve druge linije, kao što vidite, formula za poređenje je dala indikator "TRUE".



5. Pored toga, moguće je izračunati broj odstupanja pomoću posebne formule. Da biste to uradili, izaberite element lista, gde će biti prikazan. Zatim kliknite na ikonu "Umetni funkciju".



6. U prozoru Funkcioneri u grupi operatora "Matematički" izaberite ime SUMPRODUCT. Kliknite na dugme "OK".



7. Aktivira se prozor argumenta funkcije. SUMPRODUCTčiji je glavni zadatak izračunati zbroj proizvoda odabranog raspona. Ali ova funkcija se može koristiti za naše potrebe. Njegova sintaksa je prilično jednostavna:

   = SUMPRODUCT (array1; array2; ...)

   Ukupno, kao argumente možete koristiti adrese do 255 polja. Ali u našem slučaju koristićemo samo dva niza, kao jedan argument.

   Stavite kursor u polje "Massive1" i odaberite raspon upoređenih podataka u prvom području na listu. Nakon toga stavljamo oznaku na terenu. "nije jednako" () i odaberite raspon u odnosu na drugi region. Zatim zamotajte rezultirajući izraz u zagrade, ispred kojih stavimo dva znaka "-". U našem slučaju dobijemo sljedeći izraz:

   - (A2: A7D2: D7)

   Kliknite na dugme "OK".



8. Operator izračunava i prikazuje rezultat. Kao što vidimo, u našem slučaju rezultat je jednak broju "1"To znači da je u poređenim listama pronađena jedna neusklađenost. Ako su liste potpuno identične, rezultat će biti jednak broju "0".

Na isti način možete uporediti podatke u tabelama koje se nalaze na različitim listovima. Ali u ovom slučaju poželjno je da su linije u njima numerisane. Ostatak postupka usporedbe je gotovo isti kao što je gore opisano, osim činjenice da kada napravite formulu, morate se prebacivati ​​između listova. U našem slučaju, izraz će imati sljedeći oblik:

= B2 = List2! B2

To je, kao što vidimo, pre nego što se koordinate podataka, koje se nalaze na drugim listovima, razlikuju od onih na kojima se prikazuje rezultat poređenja, označava broj lista i uzvičnika.

Metod 2: Izaberite Grupe ćelija

Usporedba se može napraviti pomoću alata za odabir grupe stanica. Pomoću njega možete porediti samo sinhronizovane i naručene liste. Pored toga, u ovom slučaju, liste treba da se nalaze jedna pored druge na istom listu.

1. Izaberite poredjene nizove. Idite na karticu "Home". Zatim kliknite na ikonu "Pronađi i istakni"koji se nalazi na traci u bloku alata Uređivanje. Otvara se lista u kojoj treba da izaberete poziciju. "Odabir grupe ćelija ...".

   

   Osim toga, u željenom prozoru odabira grupe ćelija može se pristupiti na drugi način. Ova opcija će biti posebno korisna za one korisnike koji su instalirali verziju programa ranije nego Excel 2007, jer je metoda preko gumba "Pronađi i istakni" Ove aplikacije ne podržavaju. Izaberite nizove koje želite da uporedite i pritisnite taster F5.

2. Aktiviran je mali prelazni prozor. Kliknite na dugme "Istakni ..." u donjem lijevom uglu.



3. Nakon toga, bilo koja od gore navedenih opcija koje ste odabrali, pokrenut će se prozor za odabir grupa ćelija. Postavite prekidač u položaj "Izaberi po redu". Kliknite na dugme "OK".



4. Kao što možete vidjeti, nakon toga, nepodudarne vrijednosti redaka će biti označene različitim nijansama. Pored toga, kao što se može oceniti iz sadržaja linije formula, program će napraviti jednu od ćelija aktivnih u navedenim neodgovorenim linijama.

Metoda 3: Uslovno formatiranje

Možete napraviti usporedbu pomoću metode uvjetnog formatiranja. Kao iu prethodnom postupku, oblasti u poređenju treba da budu na istom Excel radnom listu i da se sinhronizuju jedna sa drugom.

1. Prije svega, biramo koji ćemo tablični prostor razmotriti glavni i koji treba tražiti razlike. Poslednje ćemo uraditi u drugoj tabeli. Stoga izaberite listu zaposlenih u njoj. Prelazak na karticu "Home", kliknite na dugme "Uslovno oblikovanje"koji se nalazi na traci u bloku "Stilovi". Iz padajuće liste nastavite "Upravljanje pravilima".



2. Prozor upravitelja pravila je aktiviran. Pritisnemo ga na dugme "Napravi pravilo".



3. U prozoru za lansiranje napravite izbor položaja "Koristi formulu". Na terenu "Formatiranje ćelija" napišite formulu koja sadrži adrese prvih ćelija opsega upoređenih kolona, ​​odvojene znakom "nije jednako" (). Samo ovaj izraz će imati ovaj put znak. "=". Osim toga, apsolutno adresiranje treba primijeniti na sve koordinate stupaca u ovoj formuli. Da biste to uradili, izaberite formulu pomoću kursora i kliknite tri puta na taster F4. Kao što možete vidjeti, znak dolar se pojavio u blizini svih adresa kolone, što znači da se veze pretvaraju u apsolutne. Za naš konkretni slučaj, formula će imati sljedeći oblik:

   = $ A2 $ D2

   Ovaj izraz zapisujemo u gornje polje. Nakon toga kliknite na dugme "Format ...".



4. Aktivirani prozor "Formatiranje ćelija". Idite na karticu "Fill". Ovde u listi boja zaustavljamo izbor na boji sa kojom želimo da obojimo one elemente gde se podaci neće podudarati. Pritisnemo dugme "OK".



5. Vraćajući se na prozor za kreiranje pravila formatiranja, kliknite na gumb. "OK".



6. Nakon automatskog prelaska na prozor Upravitelj pravila kliknite na dugme "OK" iu njemu.



7. U drugoj tabeli, elementi koji imaju podatke koji se ne podudaraju sa odgovarajućim vrednostima prve tabele biće istaknuti u izabranoj boji.

Postoji još jedan način da se uslovno formatiranje koristi za izvršenje zadatka. Kao i prethodne opcije, on zahtijeva lokaciju obaju upoređenih područja na istom listu, ali za razliku od prethodno opisanih metoda, uvjet za sinkronizaciju ili sortiranje podataka neće biti potreban, što razlikuje ovu opciju od prethodno opisanih.

1. Napravite izbor oblasti koje treba uporediti.



2. Izvedite prijelaz na pozvanu karticu "Home". Kliknite na dugme. "Uslovno oblikovanje". Na aktiviranoj listi odaberite poziciju "Pravila za odabir ćelije". U sledećem meniju vršimo izbor pozicije. "Dvostruke vrijednosti".



3. Pokreće se prozor za podešavanje izbora duplih vrijednosti. Ako ste sve uradili ispravno, onda u ovom prozoru ostaje samo da kliknete na dugme. "OK". Iako, ako želite, možete odabrati drugu boju odabira u odgovarajućem polju ovog prozora.



4. Nakon što izvršimo navedenu akciju, svi duplicirani elementi će biti označeni u odabranoj boji. Elementi koji se ne podudaraju ostaju obojeni u originalnoj boji (podrazumevano bijelo). Dakle, možete odmah vizualno vidjeti koja je razlika između polja.

Ako želite, možete, naprotiv, naslikati ne-slučajne elemente, a one indikatore koji se poklapaju možete ostaviti sa istim ispunom boje. U ovom slučaju, algoritam akcija je gotovo isti, ali u prozoru postavki za isticanje duplih vrijednosti u prvom polju umjesto parametra "Duplicate" odaberite opciju "Unique". Nakon toga kliknite na dugme "OK".

Tako će biti istaknuti oni indikatori koji se ne podudaraju.

Lekcija: Uslovno formatiranje u Excelu

Metod 4: kompleksna formula

Također možete usporediti podatke koristeći složenu formulu koja se temelji na funkciji COUNTES. Pomoću ovog alata možete izračunati koliko se svaki element iz izabrane kolone u drugoj tabeli ponavlja u prvom.

Operator COUNTES odnosi se na statističku grupu funkcija. Njegov zadatak je da prebroji broj ćelija čije vrednosti zadovoljavaju određeni uslov. Sintaksa ovog operatora je sljedeća:

= BROJEVI (raspon; kriterij)

Argument "Range" je adresa polja u kojem se izračunavaju odgovarajuće vrijednosti.

Argument "Kriterijum" postavlja uvjet podudaranja. U našem slučaju, to će biti koordinate određenih ćelija u prvom prostoru tablice.

1. Odaberite prvi element dodatne kolone u kojoj će se izračunati broj podudaranja. Zatim kliknite na ikonu "Umetni funkciju".



2. Pojavljuje se pokretanje Funkcioneri. Idi u kategoriju "Statistical". Na listi pronađite ime "COUNTES". Nakon odabira, kliknite na dugme. "OK".



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

   Postavite kursor u polje "Range". 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 za naše potrebe, ova adresa treba da bude apsolutna. Da biste to uradili, izaberite koordinate u polju i kliknite na taster F4.

   Kao što možete vidjeti, veza je poprimila apsolutni oblik, koji karakterizira prisustvo dolarskih znakova.

   Onda idite na polje "Kriterijum"postavljanjem kursora tamo. Kliknite na prvi element sa prezimenima u prvom rasponu tabele. U tom slučaju, ostavite relativnu vezu. Nakon što se prikaže u polju, možete kliknuti na gumb "OK".



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



5. Sada trebamo kreirati sličan izraz za sve ostale elemente prve tablice. Da biste to uradili, kopirajte ga pomoću markera za popunu, kao što smo to ranije uradili. Postavite kursor u donji desni dio elementa lista koji sadrži funkciju COUNTES, i nakon pretvaranja u marker za popunjavanje, držite pritisnut lijevi gumb miša i povucite kursor prema dolje.



6. Kao što možete vidjeti, program je izračunao podudaranja tako što je usporedio svaku ćeliju prve tablice s podacima koji se nalaze u drugom rasponu tablica. U četiri slučaja rezultat je izašao "1", au dva slučaja - "0". To znači da program u drugoj tabeli nije mogao pronaći dvije vrijednosti koje se nalaze u prvom nizu tablica.

Naravno, ovaj izraz kako bi se usporedili tabelarni pokazatelji, može se primijeniti u postojećem obliku, ali postoji mogućnost da se on poboljša.

Napravimo tako da se one vrijednosti koje su dostupne u drugoj tablici, ali su odsutne u prvoj, prikazuju u zasebnoj listi.

1. Pre svega, preradimo našu formulu COUNTESto je jedan od argumenata operatora IF. Da biste to uradili, izaberite prvu ćeliju u kojoj se nalazi operater COUNTES. U formuli bar pre nje dodamo izraz "IF" t bez navodnika i otvorite zagradu. Zatim, da bi nam olakšali rad, biramo vrijednost u formuli bar. "IF" t i kliknite na ikonu "Umetni funkciju".



2. Otvara se prozor argumenta funkcije. IF. Kao što vidite, prvo polje prozora je već popunjeno sa vrednošću operatora. COUNTES. Ali moramo dodati nešto drugo u ovom polju. Postavimo kursor tamo i dodamo već postojeći izraz "=0" bez navodnika.

   Posle toga idite na teren "Vrednost ako je tačno". Ovdje ćemo koristiti drugu ugniježđenu funkciju - LINE. Unesite riječ "LINE" bez navodnika, zatim otvorite zagrade i odredite koordinate prve ćelije s prezimenom u drugoj tablici, zatim zatvorite zagrade. Naime, u našem slučaju na terenu "Vrednost ako je tačno" dobio je sljedeći izraz:

   LINE (D2)

   Sada operater LINE će prijaviti funkcije IF broj linije u kojoj se nalazi specifično prezime, au slučaju kada je ispunjen uvjet naveden u prvom polju, funkcija IF će dati taj broj ćeliji. Pritisnemo dugme "OK".



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



4. Koristeći marker za ispunjavanje, na uobičajeni način kopiramo izraz operatora IF na celoj koloni. Kao što možete vidjeti, u dvije pozicije koje su prisutne u drugoj tablici, ali ne u prvoj, formula daje brojeve redova.



5. Povucite se sa tablepace na desno i popunite kolonu brojevima u redu, počevši od 1. Broj brojeva mora odgovarati broju redova u drugoj usporednoj tablici. Da biste ubrzali postupak numeriranja, možete koristiti i marker za ispunjavanje.



6. Nakon toga, izaberite prvu ćeliju desno od kolone sa brojevima i kliknite na ikonu "Umetni funkciju".



7. Otvara Čarobnjak za funkcije. Idi u kategoriju "Statistical" i napravite izbor imena "IME". Kliknite na dugme "OK".



8. Funkcija THE LEAST, prozor argumenata koji je otvoren, dizajniran je da prikaže najnižu vrijednost koju je odredio račun.

   Na terenu "Array" odredite koordinate opsega dodatne kolone "Broj podudaranja"koje smo prethodno konvertovali upotrebom funkcije IF. Mi sve veze činimo apsolutnim.

   Na terenu "K" naznačiti koji račun treba prikazati najnižu vrijednost. Ovde navodimo koordinate prve ćelije kolone sa numeracijom, koju smo nedavno dodali. Adresa ostaje relativna. Kliknite na dugme "OK".



9. Operater prikazuje rezultat - broj 3. Ovo je najmanji broj neusklađenih redova nizova tabela. Koristeći marker za popunjavanje, kopirajte formulu na dno.



10. Sada, znajući brojeve redova nepodudarnih elemenata, možemo umetnuti u ćeliju i njihove vrijednosti koristeći funkciju INDEX. Izaberite prvi element lista koji sadrži formulu THE LEAST. Nakon toga idite na formulu i prije imena "IME" append name INDEX bez navodnika, odmah otvorite zagradu i stavite tačku i zarez (;). Zatim odaberite ime u formuli bar. INDEX i kliknite na ikonu "Umetni funkciju".



11. Nakon toga, otvara se mali prozor u kojem morate odrediti da li referenca treba imati funkciju INDEX ili dizajnirani za rad s nizovima. Treba nam druga opcija. On je postavljen po defaultu, tako da u ovom prozoru jednostavno kliknite na dugme. "OK".



12. Počinje prozor argumenta funkcije. INDEX. Ova naredba je dizajnirana da prikaže vrijednost koja se nalazi u određenom nizu u navedenoj liniji.

    Kao što vidite, polje "Broj linije" već su popunjene funkcijskim vrijednostima THE LEAST. Od vrijednosti koja već postoji, oduzmite razliku između numeracije Excel lista i internog numeriranja područja tablice. Kao što vidite, iznad vrednosti tabele imamo samo kapu. To znači da je razlika jedna linija. Zato dodajemo u polje "Broj linije" značenje "-1" bez navodnika.

    Na terenu "Array" odredite adresu raspona vrijednosti druge tablice. Istovremeno, sve koordinate činimo apsolutnim, odnosno stavljamo pred njih znak za dolar na način kako smo to ranije opisali.

    Pritisnemo dugme "OK".



13. Nakon što se rezultat prikaže na ekranu, protegnemo funkciju koristeći marker za ispunjavanje do kraja stupca. Kao što vidite, oba prezimena koja su prisutna u drugoj tabeli, ali ne u prvom, prikazana su u odvojenom opsegu.

Metod 5: Poređenje polja u različitim knjigama

Kada upoređujete opsege u različitim knjigama, možete koristiti gore navedene metode, izuzimajući one opcije koje zahtijevaju postavljanje obaju tabličnih prostora na jednom listu. Glavni uslov za sprovođenje postupka upoređivanja u ovom slučaju je istovremeno otvaranje prozora oba dokumenta. Nema problema za verzije programa Excel 2013 i kasnije, kao i za verzije prije Excel 2007. Ali u programu Excel 2007 i Excel 2010, da bi se istovremeno otvorili oba prozora, potrebne su dodatne manipulacije. Kako se to radi opisano je u posebnoj lekciji.

Lekcija: Kako otvoriti Excel u različitim prozorima

Kao što vidite, postoje brojne mogućnosti za međusobno poređenje tabela. Koja opcija se koristi ovisi o tome gdje se tablični podaci nalaze jedan u odnosu na drugi (na jednom listu, u različitim knjigama, na različitim listovima), kao i na tome kako korisnik želi da se ovo poređenje prikaže na ekranu.

Pogledajte video: Prelom Stranice (Novembar 2024).