Oglejte si video - Primerjajte dva stolpca v Excelu za ujemanje in razlike
Eno poizvedovanje, ki ga dobim veliko, je - 'kako primerjati dva stolpca v Excelu?'.
To je mogoče storiti na različne načine, način uporabe pa bo odvisen od strukture podatkov in tega, kaj uporabnik od nje želi.
Na primer, primerjajte dva stolpca in poiščite ali označite vse ujemajoče se podatkovne točke (ki sta v obeh stolpcih) ali le razlike (kjer je podatkovna točka v enem stolpcu in ne v drugem) itd.
Ker me o tem toliko sprašujejo, sem se odločil, da napišem to obsežno vadnico z namenom zajeti večino (če ne vse) možne scenarije.
Če se vam zdi to uporabno, ga posredujte drugim uporabnikom Excela.
Upoštevajte, da tehnike za primerjavo stolpcev, prikazane v tej vadnici, niso edine.
Na podlagi vašega nabora podatkov boste morda morali spremeniti ali prilagoditi metodo. Vendar bi osnovna načela ostala enaka.
Če menite, da bi lahko k tej vadnici kaj dodali, mi to sporočite v razdelku za komentarje
Primerjajte dva stolpca za natančno ujemanje vrstic
Ta je najpreprostejša oblika primerjave. V tem primeru morate opraviti primerjavo vrstice in ugotoviti, katere vrstice imajo enake podatke in katere ne.
Primer: Primerjajte celice v isti vrstici
Spodaj je niz podatkov, kjer moram preveriti, ali je ime v stolpcu A enako v stolpcu B ali ne.
Če obstaja ujemanje, potrebujem rezultat kot »TRUE«, če pa se ne ujema, potem potrebujem rezultat kot »FALSE«.
Spodnja formula bi to naredila:
= A2 = B2
Primer: Primerjajte celice v isti vrstici (po formuli IF)
Če želite dobiti bolj opisen rezultat, lahko uporabite preprosto formulo IF, da vrnete »Ujemanje«, če so imena enaka, in »Neujemanje«, če so imena drugačna.
= IF (A2 = B2, "Ujemanje", "Neujemanje")
Opomba: Če želite primerjalno črko narediti občutljivo, uporabite naslednjo formulo IF:
= IF (TOČNO (A2, B2), "Ujemanje", "Neujemanje")
Z zgornjo formulo bi „IBM“ in „ibm“ veljali za dve različni imeni, zgornja formula pa bi vrnila „Neskladje“.
Primer: Označite vrstice z ustreznimi podatki
Če želite označiti vrstice z ujemajočimi se podatki (namesto da rezultat dobite v ločenem stolpcu), lahko to storite s pogojnim oblikovanjem.
Tu so naslednji koraki:
- Izberite celoten nabor podatkov.
- Kliknite zavihek »Domov«.
- V skupini Slogi kliknite možnost »Pogojno oblikovanje«.
- V spustnem meniju kliknite »Novo pravilo«.
- V pogovornem oknu »Novo pravilo oblikovanja« kliknite »Uporabi formulo, da določite, katere celice želite formatirati«.
- V polje formule vnesite formulo: = $ A1 = $ B1
- Kliknite gumb Oblika in določite obliko, ki jo želite uporabiti za ujemajoče se celice.
- Kliknite V redu.
S tem bodo označene vse celice, kjer so imena v vsaki vrstici enaka.
Primerjaj dva stolpca in označi ujemanja
Če želite primerjati dva stolpca in označiti ujemajoče se podatke, lahko uporabite podvojeno funkcijo pri pogojnem oblikovanju.
Upoštevajte, da se to razlikuje od tistega, kar smo videli pri primerjavi vsake vrstice. V tem primeru ne bomo delali primerjave vrstice po vrsticah.
Primer: Primerjajte dva stolpca in označite ujemajoče se podatke
Pogosto boste dobili nabore podatkov, kjer se ujemajo, vendar ti morda niso v isti vrstici.
Nekaj, kot je prikazano spodaj:
Upoštevajte, da je seznam v stolpcu A večji od tistega v B. Tudi na obeh seznamih je nekaj imen, vendar ne v isti vrstici (na primer IBM, Adobe, Walmart).
Če želite označiti vsa ustrezna imena podjetij, lahko to storite s pogojnim oblikovanjem.
Tu so naslednji koraki:
- Izberite celoten nabor podatkov.
- Kliknite zavihek Domov.
- V skupini Slogi kliknite možnost »Pogojno oblikovanje«.
- Premaknite kazalec miške na možnost Označi pravila celice.
- Kliknite na Podvojene vrednosti.
- V pogovornem oknu Podvojene vrednosti preverite, ali je izbrana možnost »Podvoji«.
- Določite oblikovanje.
- Kliknite V redu.
Zgornji koraki bi dali rezultat, kot je prikazano spodaj.
Opomba: Podvojeno pravilo pogojnega oblikovanja ne razlikuje med velikimi in malimi črkami. Tako „Apple“ in „apple“ veljata za enaka in bi bila označena kot dvojnika.
Primer: Primerjajte dva stolpca in označite neujemajoče se podatke
Če želite označiti imena, ki so prisotna na enem seznamu in ne na drugem, lahko za to uporabite tudi pogojno oblikovanje.
- Izberite celoten nabor podatkov.
- Kliknite zavihek Domov.
- V skupini Slogi kliknite možnost »Pogojno oblikovanje«.
- Premaknite kazalec miške na možnost Označi pravila celice.
- Kliknite na Podvojene vrednosti.
- V pogovornem oknu Podvojene vrednosti preverite, ali je izbrano »Unique«.
- Določite oblikovanje.
- Kliknite V redu.
Tako boste dobili rezultat, kot je prikazano spodaj. Poudarja vse celice z imenom, ki ni na drugem seznamu.
Primerjajte dva stolpca in poiščite manjkajoče podatkovne točke
Če želite ugotoviti, ali je na drugem seznamu podatkovna točka z enega seznama, morate uporabiti iskalne formule.
Recimo, da imate nabor podatkov, kot je prikazano spodaj, in želite identificirati podjetja, ki so prisotna v stolpcu A, ne pa v stolpcu B,
Če želite to narediti, lahko uporabim naslednjo formulo VLOOKUP.
= NAPAKA (VLOOKUP (A2, $ B $ 2: $ B $ 10,1,0))
Ta formula uporablja funkcijo VLOOKUP za preverjanje, ali je ime podjetja v A prisotno v stolpcu B ali ne. Če je prisotno, bo vrnilo to ime iz stolpca B, sicer pa napako #N/A.
Ta imena, ki vrnejo napako #N/A, so tista, ki manjkajo v stolpcu B.
Funkcija ISERROR bi vrnila TRUE, če je rezultat VLOOKUP napaka, in FALSE, če ni napaka.
Če želite dobiti seznam vseh imen, pri katerih ni ujemanja, lahko filtrirate stolpec z rezultati, da dobite vse celice z vrednostjo TRUE.
Za isto lahko uporabite tudi funkcijo MATCH;
= NE (ISNUMBER (MATCH (A2, $ B $ 2: $ B $ 10,0)))
Opomba: Osebno raje uporabljam funkcijo Match (ali kombinacijo INDEX/MATCH) namesto VLOOKUP. Zdi se mi, da je bolj prilagodljiv in močan. Razliko med Vlookup in Index/Match lahko preberete tukaj.
Primerjajte dva stolpca in povlecite ustrezne podatke
Če imate dva nabora podatkov in želite primerjati elemente na enem seznamu z drugim in pridobiti ujemajočo se podatkovno točko, morate uporabiti iskalne formule.
Primer: Povlecite podatke za ujemanje (natančno)
Na spodnjem seznamu želim na primer pridobiti tržno vrednost za stolpec 2. Če želite to narediti, moram poiskati to vrednost v stolpcu 1 in nato pridobiti ustrezno tržno vrednost.
Spodaj je formula, ki bo to naredila:
= VLOOKUP (D2, $ A $ 2: $ B $ 14,2,0)
ali
= INDEX ($ A $ 2: $ B $ 14, MATCH (D2, $ A $ 2: $ A $ 14,0), 2)
Primer: povlecite ujemajoče se podatke (delno)
V primeru, da dobite nabor podatkov, pri katerem so imena v dveh stolpcih manjša, uporaba zgoraj prikazanih iskalnih formul ne bo delovala.
Te formule iskanja potrebujejo natančno ujemanje, da dajo pravi rezultat. V funkciji VLOOKUP ali MATCH obstaja možnost približnega ujemanja, vendar je tukaj ni mogoče uporabiti.
Recimo, da imate nabor podatkov, kot je prikazano spodaj. Upoštevajte, da v stolpcu 2 obstajajo imena, ki niso popolna (na primer JPMorgan namesto JPMorgan Chase in Exxon namesto ExxonMobil).
V takem primeru lahko uporabite delno iskanje z uporabo nadomestnih znakov.
Naslednja formula bo v tem primeru dala pravi rezultat:
= VLOOKUP ("*" & D2 & "*", $ A $ 2: $ B $ 14,2,0)
ali
= INDEX ($ A $ 2: $ B $ 14, MATCH ("*" & D2 & "*", $ A $ 2: $ A $ 14,0), 2)
V zgornjem primeru je zvezdica (*) nadomestni znak, ki lahko predstavlja poljubno število znakov. Ko je iskalna vrednost obojestransko obrobljena, se vsaka vrednost v stolpcu 1, ki vsebuje iskalno vrednost v stolpcu 2, šteje za ujemanje.
Na primer, * Exxon * bi se ujemal z ExxonMobil (saj * lahko predstavlja poljubno število znakov).
Morda vam bodo všeč tudi naslednji Excelovi nasveti in vaje:
- Kako primerjati dva lista Excel (za razlike)
- Kako označiti prazne celice v Excelu.
- V Excelu označite VSAKO drugo vrstico.
- Napredni filter Excel: Popoln vodnik s primeri.
- Označite vrstice na podlagi vrednosti celice v Excelu.
