Gledajte video - usporedite dva stupca u Excelu za podudarnosti i razlike
Jedan od upita koji dobivam puno je - ‘kako usporediti dva stupca u Excelu?’.
To se može učiniti na mnogo različitih načina, a način korištenja ovisit će o strukturi podataka i o tome što korisnik želi od nje.
Na primjer, možda ćete htjeti usporediti dva stupca i pronaći ili označiti sve podudarajuće podatkovne točke (koje se nalaze u oba stupca), ili samo razlike (gdje se podatkovna točka nalazi u jednom stupcu, a ne u drugom) itd.
Budući da me toliko pitaju o ovome, odlučio sam napisati ovaj opsežni vodič s namjerom da pokrijem većinu (ako ne i sve) moguće scenarije.
Ako smatrate da je ovo korisno, proslijedite ga drugim korisnicima programa Excel.
Imajte na umu da tehnike za usporedbu stupaca prikazane u ovom vodiču nisu jedine.
Na temelju vašeg skupa podataka možda ćete morati promijeniti ili prilagoditi metodu. Međutim, osnovni principi ostali bi isti.
Ako mislite da se nešto može dodati ovom vodiču, javite mi u odjeljku za komentare
Usporedite dva stupca za točno podudaranje redova
Ovo je najjednostavniji oblik usporedbe. U tom slučaju morate napraviti usporedbu redaka po redaka i identificirati koji retci imaju iste podatke, a koji ne.
Primjer: Usporedite ćelije u istom retku
Ispod je skup podataka u kojem moram provjeriti je li ime u stupcu A isto u stupcu B ili nije.
Ako postoji podudaranje, trebam rezultat kao "TRUE", a ako se ne podudara, trebam rezultat kao "FALSE".
Formula u nastavku bi to učinila:
= A2 = B2
Primjer: Usporedite ćelije u istom redu (koristeći IF formulu)
Ako želite dobiti opisniji rezultat, možete upotrijebiti jednostavnu formulu IF da biste vratili "Podudaranje" kada su nazivi isti i "Nepodudaranje" kada su nazivi različiti.
= IF (A2 = B2, "Podudaranje", "Nepodudaranje")
Napomena: U slučaju da usporedbu usporedbe želite osjetiti, upotrijebite sljedeću formulu IF:
= IF (TOČNO (A2, B2), "Podudaranje", "Nepodudaranje")
S gornjom formulom, 'IBM' i 'ibm' smatrali bi se dva različita imena, a gornja formula bi vraćala 'Nepodudaranje'.
Primjer: Istaknite redove s podudarajućim podacima
Ako želite istaknuti retke koji imaju odgovarajuće podatke (umjesto da rezultat dobijete u posebnom stupcu), to možete učiniti pomoću uvjetnog oblikovanja.
Evo koraka za to:
- Odaberite cijeli skup podataka.
- Kliknite karticu "Početna".
- U grupi Stilovi kliknite opciju ‘Uvjetno oblikovanje’.
- Na padajućem izborniku kliknite "Novo pravilo".
- U dijaloškom okviru "Novo pravilo oblikovanja" kliknite "Koristi formulu za određivanje ćelija koje ćete oblikovati".
- U polje formule unesite formulu: = $ A1 = $ B1
- Pritisnite gumb Oblikovanje i navedite format koji želite primijeniti na odgovarajuće ćelije.
- Pritisnite U redu.
Time će se istaknuti sve ćelije u kojima su nazivi isti u svakom retku.
Usporedite dvije kolone i označite podudaranja
Ako želite usporediti dva stupca i istaknuti podudarne podatke, možete upotrijebiti dupliciranu funkcionalnost u uvjetnom oblikovanju.
Imajte na umu da se ovo razlikuje od onoga što smo vidjeli usporedbom svakog retka. U ovom slučaju nećemo raditi usporedbu red po red.
Primjer: Usporedite dvije kolone i istaknite podudaranje podataka
Često ćete dobiti skupove podataka gdje postoje podudaranja, ali oni možda nisu u istom retku.
Nešto kao što je prikazano ispod:
Imajte na umu da je popis u stupcu A veći od onog u B. Također se neki nazivi nalaze na oba popisa, ali ne u istom retku (kao što su IBM, Adobe, Walmart).
Ako želite istaknuti sve odgovarajuće nazive tvrtki, to možete učiniti pomoću uvjetnog oblikovanja.
Evo koraka za to:
- Odaberite cijeli skup podataka.
- Kliknite karticu Početna.
- U grupi Stilovi kliknite opciju ‘Uvjetno oblikovanje’.
- Zadržite pokazivač miša na opciji Označi pravila ćelije.
- Pritisnite Duplicate Values.
- U dijaloškom okviru Duplicate Values provjerite je li odabrano "Duplicate".
- Odredite oblikovanje.
- Pritisnite U redu.
Gore navedeni koraci dali bi vam rezultat kao što je prikazano u nastavku.
Napomena: Uvjetno oblikovanje dupliciranih pravila ne razlikuje velika i mala slova. Dakle, 'Apple' i 'apple' smatraju se istim te bi bile istaknute kao duplikati.
Primjer: Usporedite dva stupca i označite neusklađene podatke
U slučaju da želite istaknuti imena koja su prisutna na jednom popisu, a ne na drugom, možete upotrijebiti uvjetno oblikovanje i za ovo.
- Odaberite cijeli skup podataka.
- Kliknite karticu Početna.
- U grupi Stilovi kliknite opciju ‘Uvjetno oblikovanje’.
- Zadržite pokazivač miša na opciji Označi pravila ćelije.
- Pritisnite Duplicate Values.
- U dijaloškom okviru Duplicate Values provjerite je li odabrano "Unique".
- Odredite oblikovanje.
- Pritisnite U redu.
To će vam dati rezultat kao što je prikazano u nastavku. Ističe sve ćelije s imenom koje nema na drugom popisu.
Usporedite dvije kolone i pronađite nedostajuće podatkovne točke
Ako želite utvrditi je li podatkovna točka s jednog popisa prisutna na drugom popisu, morate upotrijebiti formule za pretraživanje.
Pretpostavimo da imate skup podataka kao što je prikazano u nastavku i želite identificirati tvrtke koje su prisutne u stupcu A, ali ne i u stupcu B,
Da bih to učinio, mogu koristiti sljedeću formulu VLOOKUP.
= POGREŠKA (VLOOKUP (A2, $ B $ 2: $ B $ 10,1,0))
Ova formula koristi funkciju VLOOKUP za provjeru je li naziv tvrtke u A prisutan u stupcu B ili ne. Ako je prisutan, vratit će to ime iz stupca B, inače će vratiti #N/A pogrešku.
Ti nazivi koji vraćaju grešku #N/A nedostaju u stupcu B.
ISERROR funkcija bi vratila TRUE ako postoji VLOOKUP rezultat je pogreška i FALSE ako nije greška.
Ako želite dobiti popis svih imena gdje nema podudaranja, možete filtrirati stupac rezultata kako biste dobili sve ćelije s TRUE.
Također možete koristiti funkciju MATCH da učinite isto;
= NOT (ISNUMBER (MATCH (A2, $ B $ 2: $ B $ 10,0)))
Napomena: Osobno radije koristim funkciju Match (ili kombinaciju INDEX/MATCH) umjesto VLOOKUP -a. Smatram ga fleksibilnijim i moćnijim. Razliku između Vlookupa i Indeksa/podudaranja možete pročitati ovdje.
Usporedite dvije kolone i izvucite odgovarajuće podatke
Ako imate dva skupa podataka i želite usporediti stavke s jednog popisa s drugim i dohvatiti odgovarajuću podatkovnu točku, morate upotrijebiti formule za pretraživanje.
Primjer: Povucite odgovarajuće podatke (točno)
Na primjer, na donjem popisu želim dohvatiti tržišnu vrijednost za stupac 2. Da bih to učinio, moram potražiti tu vrijednost u stupcu 1, a zatim donijeti odgovarajuću tržišnu vrijednost.
Ispod je formula koja će to učiniti:
= VLOOKUP (D2, $ A $ 2: $ B $ 14,2,0)
ili
= INDEKS ($ A $ 2: $ B $ 14, MATCH (D2, $ A $ 2: $ A $ 14,0), 2)
Primjer: Povucite podudarajuće podatke (djelomično)
U slučaju da dobijete skup podataka u kojem postoji manja razlika u nazivima u dva stupca, korištenje gore prikazanih formula za pretraživanje neće funkcionirati.
Ove formule za traženje trebaju točno podudaranje kako bi dale pravi rezultat. Postoji mogućnost približnog podudaranja u funkciji VLOOKUP ili MATCH, ali to se ovdje ne može koristiti.
Pretpostavimo da imate skup podataka kao što je prikazano u nastavku. Imajte na umu da postoje imena koja nisu potpuna u stupcu 2 (poput JPMorgan umjesto JPMorgan Chase i Exxon umjesto ExxonMobil).
U tom slučaju možete upotrijebiti djelomično pretraživanje pomoću zamjenskih znakova.
Sljedeća formula će dati pravi rezultat u ovom slučaju:
= VLOOKUP ("*" & D2 & "*", $ A $ 2: $ B $ 14,2,0)
ili
= INDEKS ($ A $ 2: $ B $ 14, MATCH ("*" & D2 & "*", $ A $ 2: $ A $ 14,0), 2)
U gornjem primjeru zvjezdica (*) je zamjenski znak koji može predstavljati bilo koji broj znakova. Kad je vrijednost pretraživanja spojena s obje strane, svaka vrijednost u stupcu 1 koja sadrži vrijednost traženja u stupcu 2 smatrat će se podudaranjem.
Na primjer, * Exxon * bi odgovarao ExxonMobilu (jer * može predstavljati bilo koji broj znakova).
Možda će vam se svidjeti i sljedeći Excel savjeti i vodiči:
- Kako usporediti dva Excel lista (za razlike)
- Kako označiti prazne ćelije u Excelu.
- Označite SVAKI DRUGI RED u Excelu.
- Napredni filter Excel: Potpuni vodič s primjerima.
- Istaknite redove na temelju vrijednosti ćelije u Excelu.
