Usporedba tablica vrijednosti 1c 8. Kako usporediti dva stupca u Excelu za podudaranja

Čitanje ovog članka oduzet će vam oko 10 minuta. U sljedećih 5 minuta možete jednostavno usporediti dva stupca u Excelu i otkriti postoje li duplikati u njima, izbrisati ih ili označiti bojom. Dakle, došlo je vrijeme!

Excel je vrlo moćna i stvarno cool aplikacija za stvaranje i obradu velikih količina podataka. Ako imate više radnih knjiga s podacima (ili samo jednu ogromnu tablicu), tada ćete vjerojatno htjeti usporediti 2 stupca, pronaći duplicirane vrijednosti i zatim učiniti nešto s njima, poput brisanja, označavanja ili brisanja sadržaja. Stupci mogu biti u istoj tablici, susjedni ili nesusjedni, smješteni na 2 različita lista ili čak u različitim radnim knjigama.

Zamislite da imamo 2 stupca imena ljudi - 5 imena po stupcu A i 3 imena u stupcu B. Morate usporediti imena u ova dva stupca i pronaći duplikate. Kao što razumijete, ovo su fiktivni podaci uzeti samo u ilustrativne svrhe. U stvarnim tablicama imamo posla s tisućama ili čak desecima tisuća zapisa.

Opcija A: oba su stupca na istom listu. Na primjer, stupac A i stupac B.

Opcija B: Stupci se nalaze na različitim listovima. Na primjer, stupac A na plahti List2 i stupac A na plahti List3.

Excel 2013, 2010 i 2007 imaju ugrađeni alat Ukloni duplikat(Remove Duplicates) ali je u ovoj situaciji nemoćan jer ne može usporediti podatke u 2 stupca. Štoviše, može ukloniti samo duplikate. Nema drugih opcija, poput isticanja ili mijenjanja boja. I točka!

Usporedite 2 stupca u Excelu i pronađite dvostruke unose pomoću formula

Opcija A: oba su stupca na istom listu

Trag: U velikim tablicama kopiranje formule bit će brže ako koristite tipkovničke prečace. Odaberite ćeliju C1 i pritisnite Ctrl+C(za kopiranje formule u međuspremnik), zatim kliknite Ctrl+Shift+End(za odabir svih ćelija koje nisu prazne u stupcu C) i na kraju kliknite Ctrl+V(za lijepljenje formule u sve odabrane ćelije).

Opcija B: dva stupca su na različitim listovima (u različitim knjigama)

Obrada pronađenih duplikata

Odlično, pronašli smo unose u prvom stupcu koji su prisutni i u drugom stupcu. Sada moramo nešto učiniti s njima. Ručno prolaženje kroz sve duplicirane unose u tablici prilično je neučinkovito i oduzima previše vremena. Ima boljih načina.

Prikaži samo duplicirane retke u stupcu A

Ako vaši stupci nemaju naslove, morate ih dodati. Da biste to učinili, postavite kursor na broj koji označava prvi redak i on će se pretvoriti u crnu strelicu, kao što je prikazano na slici ispod:

Kliknite desnom tipkom miša i odaberite iz kontekstnog izbornika Umetnuti(Umetnuti):

Dajte nazive stupcima, na primjer, " Ime"I" Duplikat?” Zatim otvorite karticu Podaci(Podaci) i pritisnite filtar(Filtar):

Nakon toga kliknite malu sivu strelicu pored " Duplikat?“ za proširenje izbornika filtera; poništite odabir svih stavki na ovom popisu osim Duplikat, i pritisnite u redu.

To je to, sada vidite samo te elemente stupaca A, koji se dupliciraju u stupcu U. U našem stolu za vježbanje postoje samo dvije takve ćelije, ali, kao što razumijete, u praksi će ih biti mnogo više.

Za ponovno prikazivanje svih redaka stupca A, kliknite simbol filtra u stupcu U, koji sada izgleda kao lijevak s malom strelicom i odaberite Odaberi sve(Odaberi sve). Ili možete učiniti isto putem vrpce klikom Podaci(Podaci) > Odaberite i filtrirajte(Sortiraj i filtriraj) > Čisto(Očisti) kao što je prikazano na slici ispod:

Promijenite boju ili označite pronađene duplikate

Ako su oznake " Duplikat” nije dovoljno za vaše potrebe, a vi želite označiti duplicirane ćelije drugom bojom fonta, bojom ispune ili na neki drugi način...

U tom slučaju filtrirajte duplikate kao što je gore prikazano, odaberite sve filtrirane ćelije i kliknite Ctrl+1 za otvaranje dijaloškog okvira Formatiraj ćelije(Format ćelije). Na primjer, promijenimo boju ispune ćelija u redovima s duplikatima u svijetložutu. Naravno, možete promijeniti boju ispune pomoću alata Ispunite(Boja ispune) kartica Dom(Home), ali prednost dijaloškog okvira Formatiraj ćelije(Format Cells) znači da možete konfigurirati sve opcije oblikovanja odjednom.

Sada sigurno nećete propustiti nijednu ćeliju s duplikatima:

Uklanjanje dupliciranih vrijednosti iz prvog stupca

Filtrirajte tablicu tako da prikazuje samo ćelije s dupliciranim vrijednostima i odaberite te ćelije.

Ako su 2 stupca koja uspoređujete na različitim listovima, odnosno u različitim tablicama desnom tipkom miša kliknite odabrani raspon i odaberite iz kontekstnog izbornika Izbriši redak(Izbriši redak):

Klik u redu kada vas Excel pita da potvrdite da stvarno želite izbrisati cijeli red radnog lista i zatim poništite filtar. Kao što vidite, preostali su samo redovi s jedinstvenim vrijednostima:

Ako se na jednom listu nalaze 2 stupca, blizu jedan drugome (susjedni) ili ne blizu (nisu susjedni), tada će postupak uklanjanja duplikata biti malo teži. Ne možemo izbrisati cijeli redak s dupliciranim vrijednostima jer bismo time izbrisali i ćelije iz drugog stupca. Dakle, da zadržite samo jedinstvene unose u stupcu A, učinite sljedeće:

Kao što vidite, uklanjanje duplikata iz dva stupca u Excelu pomoću formula nije tako teško.

Ponekad je potrebno usporediti dvije MS Excel datoteke. To može biti pronalaženje odstupanja u cijenama za određene artikle ili promjena bilo kakvih indikacija, nije važno, glavna stvar je da je potrebno pronaći određena odstupanja.

Ne bi bilo na odmet spomenuti da ako postoji par zapisa u MS Excel datoteci, onda nema smisla posezati za automatizacijom. Ako datoteka sadrži nekoliko stotina, pa čak i tisuća zapisa, tada je nemoguće učiniti bez pomoći računalne snage računala.

Simulirajmo situaciju u kojoj dvije datoteke imaju isti broj redaka, a odstupanje treba tražiti u određenom stupcu ili u nekoliko stupaca. Ova situacija je moguća, primjerice, ako trebate usporediti cijene robe prema dva cjenika ili usporediti mjere sportaša prije i nakon sezone treninga, iako ih za takvu automatizaciju mora biti puno.

Kao radni primjer, uzmimo datoteku s učinkom fiktivnih sudionika: trčanje na 100 metara, trčanje na 3000 metara i zgibovi. Prva datoteka je mjerenje na početku sezone, a druga na kraju sezone.

Prvi način rješavanja problema. Rješenje je samo korištenje MS Excel formula.

Budući da su zapisi raspoređeni okomito (najlogičniji raspored), potrebno je koristiti funkciju. Ako koristite horizontalno postavljanje zapisa, morat ćete koristiti funkciju.

Za usporedbu performansi trčanja na 100 metara, formula je sljedeća:
=IF(VLOOKUP($B2,Sheet2!$B$2:$F$13,3,TRUE)<>D2;D2-VLOOKUP($B2;Sheet2!$B$2:$F$13,3,TRUE);"Nema razlike")
Ako nema razlike, ispisuje se poruka da nema razlike, ako postoji razlika se oduzima vrijednost na kraju sezone od vrijednosti na početku sezone.

Formula za trčanje na 3000 metara je sljedeća:
=IF(VLOOKUP($B2,Sheet2!$B$2:$F$13,4,TRUE)<>E2;"Postoji razlika";"Nema razlike")
Ako konačna i početna vrijednost nisu jednake, prikazuje se odgovarajuća poruka. Formula za zgibove može biti slična bilo kojoj od prethodnih, nema smisla davati je dodatno. Konačna datoteka s pronađenim nedosljednostima prikazana je u nastavku.

Malo pojašnjenje. Radi lakšeg čitanja formula, podaci iz dvije datoteke premješteni su u jednu (na različite listove), ali to se nije moglo učiniti.

Video koji uspoređuje korištenje i funkcije dviju MS Excel datoteka.

Drugi način rješavanja problema. Rješenje pomoću MS Accessa.

Ovaj se problem može riješiti ako prvo uvezete MS Excel datoteke u Access. Što se tiče same metode uvoza vanjskih podataka, nema razlike u pronalaženju različitih polja (bilo koja od prikazanih opcija će poslužiti).

Potonji je veza između Excel i Access datoteka, tako da kada promijenite podatke u Excel datotekama, odstupanja će se automatski pronaći kada pokrenete upit u MS Accessu.

Sljedeći korak nakon uvoza je stvaranje odnosa između tablica. Kao spojno polje odaberite jedinstveno polje “Br.
Treći korak je stvaranje jednostavnog upita odabira pomoću Query Buildera.

U prvom stupcu označavamo koji zapisi se trebaju prikazati, au drugom - pod kojim uvjetima će se zapisi prikazati. Naravno, za drugo i treće polje akcije će biti slične.

Video koji uspoređuje MS datoteke s Excelom pomoću MS Accessa.

Kao rezultat izvršenih manipulacija, prikazuju se svi zapisi, s različitim podacima u polju: "Trčanje na 100 metara." MS Access datoteka je predstavljena u nastavku (nažalost, SkyDrive ne dopušta ugrađivanje kao Excel datoteka)

Ove dvije metode postoje za pronalaženje odstupanja u MS Excel tablicama. Svaki ima i prednosti i nedostatke. Očito, ovo nije iscrpan popis usporedbi između dvije Excel datoteke. Čekamo vaše prijedloge u komentarima.

Imamo dvije tablice narudžbi kopirane u jedan radni list. Potrebno je usporediti podatke iz dvije tablice u Excelu i provjeriti koje su pozicije u prvoj tablici, a ne u drugoj. Nema smisla ručno uspoređivati vrijednost svake ćelije.

Usporedite dva stupca za podudaranja u Excelu

Kako usporediti vrijednosti u Excelu dva stupca? Da biste riješili ovaj problem, preporučujemo korištenje uvjetnog oblikovanja, koje brzo ističe stavke koje se nalaze u samo jednom stupcu. Radni list s tablicama:

Prvi korak je imenovanje obje tablice. To olakšava razumijevanje koji se rasponi ćelija uspoređuju:

Odaberite alat FORMULE - Definirani nazivi - Dodijeli naziv.
U prozoru koji se pojavi, u polje “Naziv:” unesite vrijednost – Table_1.
Lijevom tipkom miša kliknite na polje za unos “Raspon:” i odaberite raspon: A2:A15. I kliknite OK.

Za drugu listu izvršite iste korake, samo joj dodijelite ime – Table_2. I odredite raspon C2:C15 - respektivno.

Koristan savjet! Nazivi raspona mogu se brže dodijeliti pomoću polja imena. Nalazi se lijevo od trake formule. Jednostavno odaberite raspone ćelija, au polje za naziv unesite odgovarajući naziv za raspon i pritisnite Enter.

Sada upotrijebimo uvjetno oblikovanje za usporedbu dva popisa u Excelu. Moramo dobiti sljedeći rezultat:

Stavke koje se nalaze u Tablici_1, ali ne i u Tablici_2 bit će prikazane zelenom bojom. Istovremeno, pozicije koje se nalaze u Tablici_2, ali ne iu Tablici_1, bit će označene plavom bojom.

Princip usporedbe podataka između dva stupca u Excelu

Prilikom definiranja uvjeta za oblikovanje ćelija stupca koristili smo funkciju COUNTIF. U ovom primjeru ova funkcija provjerava koliko se puta vrijednost drugog argumenta (na primjer, A2) pojavljuje na popisu prvog argumenta (na primjer, Tablica_2). Ako je broj puta = 0, tada formula vraća TRUE. U tom slučaju ćeliji se dodjeljuje prilagođeni format naveden u opcijama uvjetnog oblikovanja.

Veza u drugom argumentu je relativna, što znači da će sve ćelije odabranog raspona biti provjerene jedna po jedna (na primjer, A2:A15). Na primjer, usporediti dva cjenika u Excelu, čak i na različitim listovima. Druga formula radi slično. Isti princip može se primijeniti na razne slične zadatke.

Možda se svatko tko radi s podacima u Excelu suočava s pitanjem kako usporediti dva stupca u Excelu za sličnosti i razlike. Postoji nekoliko načina za to. Pogledajmo pobliže svaki od njih.

Kako usporediti dva stupca u Excelu red po red

Kada se uspoređuju dva stupca podataka, često je potrebno usporediti podatke u svakom pojedinačnom retku radi podudarnosti ili razlika. Takvu analizu možemo napraviti pomoću funkcije. Pogledajmo kako to funkcionira pomoću primjera u nastavku.

Primjer 1: Kako usporediti dva stupca za podudaranja i razlike u istom retku

Kako bismo usporedili podatke u svakom retku od dva stupca u Excelu, napišimo jednostavnu formulu. Formulu treba umetnuti u svaki redak u susjednom stupcu, pokraj tablice u kojoj se nalaze glavni podaci. Nakon što smo izradili formulu za prvi redak tablice, možemo je rastegnuti/kopirati u preostale retke.

Kako bismo provjerili sadrže li dva stupca istog retka iste podatke, potrebna nam je formula:

=IF(A2=B2; “Podudaranje”; “”)

Formula koja određuje razlike između podataka dvaju stupaca u jednom retku izgledat će ovako:

= AKO (A2<>B2; "Ne podudaraju"; “”)

Možemo uklopiti test podudaranja i razlike između dva stupca u jednom retku u jednoj formuli:

=IF(A2=B2; “Podudaranje”; “Ne podudaranje”)

= AKO (A2<>B2; "Ne podudaraju"; "Podudarati se")

Primjer rezultata izračuna mogao bi izgledati ovako:

Za usporedbu podataka u dva stupca istog retka na način koji razlikuje velika i mala slova upotrijebite formulu:

=IF(MATCH(A2,B2), “Podudaranje”, “Jedinstveno”)

Kako usporediti više stupaca za podudaranja u jednom Excel retku

Excel vam omogućuje usporedbu podataka u više stupaca istog retka pomoću sljedećih kriterija:

Pronađite retke s istim vrijednostima u svim stupcima tablice;
Pronađite retke s istim vrijednostima u bilo koja dva stupca tablice;

Primjer 1. Kako pronaći podudaranja u jednom retku u više stupaca tablice

Zamislimo da se naša tablica sastoji od nekoliko stupaca podataka. Naš zadatak je pronaći retke u kojima se vrijednosti podudaraju u svim stupcima. U tome će nam pomoći Excel i funkcije. Formula za određivanje podudaranja bit će sljedeća:

=IF(AND(A2=B2,A2=C2); “Podudaraju se”; “ “)

Ako naša tablica ima puno stupaca, tada će biti lakše koristiti funkciju u kombinaciji s:

=IF(COUNTIF($A2:$C2,$A2)=3;”Podudaranje”;” “)

U formuli "5" označava broj stupaca tablice za koju smo izradili formulu. Ako vaša tablica ima više ili manje stupaca, ta bi vrijednost trebala biti jednaka broju stupaca.

Primjer 2: Kako pronaći podudaranja istog retka u bilo koja dva stupca tablice

Zamislimo da je naš zadatak iz tablice s podacima u nekoliko stupaca identificirati one retke u kojima su podaci isti ili se ponavljaju u najmanje dva stupca. U tome će nam pomoći funkcije i . Napišimo formulu za tablicu koja se sastoji od tri stupca podataka:

=IF(ILI(A2=B2,B2=C2,A2=C2);”Podudaranje”;” “)

U slučajevima kada u našoj tablici ima previše stupaca, naša će formula s funkcijom biti vrlo velika, budući da u njezinim parametrima moramo navesti kriterije podudaranja između svakog stupca tablice. Jednostavniji način, u ovom slučaju, je korištenje .

=IF(COUNTIF(B2:D2,A2)+COUNTIF(C2:D2,B2)+(C2=D2)=0; “Jedinstveni niz”; “Nije jedinstven niz”)

=IF(COUNTIF($B:$B,$A5)=0; “Nema podudaranja u stupcu B”; “Ima podudaranja u stupcu B”)

Ova formula provjerava vrijednosti u stupcu B da vidi podudaraju li se s ćelijama u stupcu A.

Ako se vaša tablica sastoji od fiksnog broja redaka, možete navesti jasan raspon u formuli (na primjer, $B2: $B10). To će ubrzati formulu.

Kako usporediti dva stupca u Excelu radi podudaranja i označiti ih bojom

Kada tražimo podudaranja između dva stupca u Excelu, možda ćemo trebati vizualizirati pronađena podudaranja ili razlike u podacima, na primjer korištenjem isticanja boja. Najlakši način za isticanje podudaranja i razlika u boji je korištenje uvjetnog oblikovanja u Excelu. Pogledajmo kako to učiniti pomoću primjera u nastavku.

Pronalaženje i označavanje podudaranja u boji u više stupaca u Excelu

U slučajevima kada trebamo pronaći podudaranja u nekoliko stupaca, tada nam je potrebno:

Odaberite stupce s podacima u kojima želite izračunati podudaranja;
Na kartici "Početna" na alatnoj traci kliknite na stavku izbornika "Uvjetno oblikovanje" -> "Pravila za označavanje ćelija" -> "Ponavljanje vrijednosti";
U skočnom dijaloškom okviru odaberite "Ponavljanje" na lijevom padajućem popisu, a na desnom padajućem popisu odaberite kojom bojom će biti istaknute vrijednosti koje se ponavljaju. Pritisnite gumb "OK":
Nakon toga, podudaranja će biti označena u odabranom stupcu:

Pronađite i označite odgovarajuće retke u Excelu

Pronalaženje odgovarajućih ćelija s podacima u dva ili više stupaca i traženje podudaranja cijelih redaka s podacima različiti su pojmovi. Imajte na umu dvije tablice u nastavku:

Gornje tablice sadrže iste podatke. Njihova razlika je u tome što smo u primjeru s lijeve strane tražili podudarne ćelije, a s desne smo pronašli čitave ponavljajuće retke s podacima.

Pogledajmo kako pronaći podudarne retke u tablici:

Desno od tablice s podacima napravit ćemo pomoćni stupac u kojem ćemo nasuprot svakog retka s podacima staviti formulu koja kombinira sve vrijednosti retka tablice u jednu ćeliju:

=A2&B2&C2&D2

U pomoćnom stupcu vidjet ćete kombinirane podatke tablice:

Sada, da biste odredili podudarne retke u tablici, učinite sljedeće korake:

Odaberite područje s podacima u pomoćnom stupcu (u našem primjeru to je raspon ćelija E2:E15 );
Na kartici "Početna" na alatnoj traci kliknite na stavku izbornika "Uvjetno oblikovanje" -> "Pravila za označavanje ćelija" -> "Ponavljanje vrijednosti";
U skočnom dijaloškom okviru odaberite "Ponavljanje" na lijevom padajućem popisu, a na desnom padajućem popisu odaberite kojom bojom će biti istaknute vrijednosti koje se ponavljaju. Pritisnite gumb "OK":
Nakon toga, dvostruke linije bit će označene u odabranom stupcu:

Često je zadatak usporediti dvije liste elemenata. Ručno obavljanje toga je previše zamorno, a također se ne može isključiti mogućnost pogrešaka. Excel olakšava ovu operaciju. Ovaj savjet opisuje metodu koja koristi uvjetno oblikovanje.

Na sl. Slika 164.1 prikazuje primjer dva popisa imena u više stupaca. Korištenje uvjetnog oblikovanja može učiniti razlike u popisima očiglednima. Ovi primjeri popisa sadrže tekst, ali predmetna metoda radi i s numeričkim podacima.

Prva lista je A2:B31, ovaj raspon se zove OldList. Druga lista je D2:E31, raspon se zove NewList. Rasponi su imenovani pomoću naredbe Formule Definirani nazivi Dodijelite naziv. Raspone nije potrebno imenovati, ali to olakšava rad s njima.

Počnimo s dodavanjem uvjetnog oblikovanja starom popisu.

Odaberite ćelije u rasponu OldList.
Izaberi.
U prozoru Stvorite pravilo oblikovanja odaberite pozvanu stavku Koristite formulu
Unesite ovu formulu u polje prozora (Sl. 164.2): =COUNTIF(NewList;A2)=0.
Pritisnite gumb Format i odredite oblikovanje koje će se primijeniti kada je uvjet istinit. Najbolje je odabrati različite boje ispune.
Pritisnite OK.

Ćelije u dometu NewList koristite sličnu formulu uvjetnog oblikovanja.

Odaberite ćelije u rasponu NewList.
Izaberi Početna Uvjetno oblikovanje Stvorite pravilo za otvaranje dijaloškog okvira Stvorite pravilo oblikovanja.
U prozoru Stvorite pravilo oblikovanje odaberite stavku Koristite formulu za definiranje ćelija koje treba formatirati.
Unesite ovu formulu u polje prozora: =COUNTIF(OldList;D2)=0 .
Pritisnite gumb Format i postavite oblikovanje koje će se primijeniti kada je uvjet istinit (različita boja ispune).
Pritisnite OK.

Kao rezultat toga, imena koja se nalaze na starom popisu, ali ne i na novom, bit će istaknuta (Sl. 164.3). Osim toga, imena u novom popisu koja nisu u starom popisu također su označena, ali drugom bojom. Imena koja se pojavljuju na oba popisa nisu istaknuta.

Obje formule uvjetnog oblikovanja koriste funkciju COUNTIF. Izračunava koliko se puta određena vrijednost pojavljuje u rasponu. Ako formula vrati 0, to znači da stavka nije u rasponu. Na taj se način uvjetno oblikovanje preuzima i mijenja se boja pozadine ćelije.