Kahden sarakkeen vertailu Excelissä (vastaavuudet ja erot)
Katso video – Kahden sarakkeen vertailu Excelissä vastaavuuksien ja erojen löytämiseksi
Ensimmäinen kysely, jonka saan paljon, on – ”miten verrata kahta saraketta Excelissä?”.
Tämä voidaan tehdä monella eri tavalla, ja käytettävä menetelmä riippuu tietorakenteesta ja siitä, mitä käyttäjä haluaa siitä.
Voidaan esimerkiksi haluta vertailla kahta saraketta ja etsiä tai korostaa kaikki täsmäävät datapisteet (jotka ovat molemmissa sarakkeissa) tai vain erot (jossa datapiste on toisessa sarakkeessa, mutta ei toisessa) jne.
Koska minulta kysytään tästä niin paljon, päätin kirjoittaa tämän massiivisen opetusohjelman, jonka tarkoituksena on kattaa useimmat (ellei kaikki) mahdolliset skenaariot.
Jos tämä on mielestäsi hyödyllinen, välitä se eteenpäin muille Excelin käyttäjille.
Tämä opetusohjelma kattaa:
Huomaa, että tässä opetusohjelmassa esitetyt tekniikat sarakkeiden vertailemiseksi eivät ole ainoita.
Tietokokonaisuutesi perusteella saatat joutua muuttamaan tai säätämään menetelmää. Perusperiaatteet pysyisivät kuitenkin samoina.
Jos olet sitä mieltä, että tähän opetusohjelmaan voidaan lisätä jotakin, kerro se minulle kommenttiosioon
Vertaa kahta saraketta tarkan rivin vastaavuuden saamiseksi
Tämä on yksinkertaisin vertailun muoto. Tässä tapauksessa sinun täytyy tehdä rivikohtainen vertailu ja tunnistaa, millä riveillä on samat tiedot ja millä ei.
Esimerkki: Saman rivin solujen vertailu
Alhaalla on tietokokonaisuus, jossa minun on tarkistettava, onko sarakkeessa A oleva nimi sama sarakkeessa B vai ei.
Jos on vastaavuus, tarvitsen tulokseksi ”TRUE”, ja jos ei täsmää, tarvitsen tulokseksi ”FALSE”.
Alla oleva kaava tekisi tämän:
=A2=B2
Esimerkki: Saman rivin solujen vertailu (käyttämällä IF-kaavaa)
Jos haluat saada kuvaavamman tuloksen, voit käyttää yksinkertaista IF-kaavaa, joka palauttaa ”Match”, kun nimet ovat samat, ja ”Mismatch”, kun nimet ovat erilaiset.
=IF(A2=B2,"Match","Mismatch")
Huomautus: Jos haluat tehdä vertailusta suur- ja pienaakkoset huomioivan, käytä seuraavaa IF-kaavaa:
=IF(EXACT(A2,B2),"Match","Mismatch")
Yllä olevalla kaavalla ’IBM’ ja ’ibm’ katsottaisiin kahdeksi eri nimeksi, ja yllä oleva kaava palauttaisi tulokseksi ’Epäsuhta’.
Esim: Korosta rivit, joissa on täsmääviä tietoja
Jos haluat korostaa rivit, joissa on täsmääviä tietoja (sen sijaan, että saisit tuloksen erilliseen sarakkeeseen), voit tehdä sen käyttämällä ehdollista muotoilua.
Tässä on seuraavat vaiheet:
- Valitse koko tietokokonaisuus.
- Klikkaa ’Aloitus’-välilehteä.
- Klikkaa Tyylit-ryhmässä ’Ehdollinen muotoilu’ -vaihtoehtoa.
- Klikkaa pudotusvalikosta ’Uusi sääntö’.
- Klikkaa ’Uusi muotoilusääntö’ -valintaikkunassa ’Määritä kaavan avulla, mitkä solut muotoillaan’.
- Kirjoita kaavakenttään kaava: =$A1=$B1
- Klikkaa Muotoile-painiketta ja määritä muotoilu, jota haluat soveltaa vastaaviin soluihin.
- Klikkaa OK.
Tämä korostaakin kaikki solut, joissa nimet ovat samat jokaisella rivillä.
Vertaa kahta saraketta ja korosta täsmäävät tiedot
Jos haluat verrata kahta saraketta ja korostaa täsmäävät tiedot, voit käyttää ehdollisen muotoilun monistustoimintoa.
Huomaa, että tämä eroaa siitä, mitä olemme nähneet verratessamme jokaista riviä. Tässä tapauksessa emme tee rivikohtaista vertailua.
Esimerkki: Kahden sarakkeen vertailu ja täsmäävien tietojen korostaminen
Usein saat tietokokonaisuuksia, joissa on täsmääviä tietoja, mutta nämä eivät välttämättä ole samalla rivillä.
Jotain alla olevan kaltaista:
Huomaa, että sarakkeessa A oleva luettelo on suurempi kuin sarakkeessa B oleva luettelo. Myös joitakin nimiä on molemmissa luetteloissa, mutta ei samalla rivillä (kuten IBM, Adobe, Walmart).
Jos haluat korostaa kaikki yhtenevät yritysnimet, voit tehdä sen käyttämällä ehdollista muotoilua.
Tässä on seuraavat vaiheet:
- Valitse koko aineisto.
- Napsauta Aloitus-välilehteä.
- Napsauta Tyylit-ryhmässä ’Ehdollinen muotoilu’ -vaihtoehtoa.
- Siirrä kursori Korosta solusäännöt -vaihtoehdon kohdalle.
- Napsauta Kaksoisarvot.
- Varmista, että Kaksoisarvot-valintaikkunassa on valittuna vaihtoehto ’Kaksoiskappale’.
- Määritä muotoilu.
- Klikkaa OK.
Yllä olevilla vaiheilla saat alla olevan kaltaisen tuloksen.
Huomautus: Ehdollisen muotoilun päällekkäisyyssääntö ei ota huomioon isoja ja pieniä kirjaimia. Joten ’Apple’ ja ’omena’ katsotaan samoiksi ja ne korostettaisiin kaksoiskappaleina.
Esimerkki: Kahden sarakkeen vertailu ja yhteensopimattomien tietojen korostaminen
Jos haluat korostaa nimet, jotka esiintyvät toisessa luettelossa, mutta eivät toisessa, voit käyttää ehdollista muotoilua myös tähän.
- Valitse koko tietokokonaisuus.
- Klikkaa Aloitus-välilehteä.
- Klikkaa Tyylit-ryhmässä vaihtoehtoa ’Ehdollinen muotoilu’.
- Siirrä kursori Korosta solusäännöt -vaihtoehdon päälle.
- Klikkaa Kaksoisarvot.
- Varmista Kaksoisarvot-valintaikkunassa, että ’Yksilöllinen’ on valittu.
- Määritä muotoilu.
- Klikkaa OK.
Tällöin saat alla olevan kuvan mukaisen tuloksen. Se korostaa kaikki solut, joiden nimi ei esiinny toisessa luettelossa.
Vertaile kahta saraketta ja etsi puuttuvat datapisteet
Jos haluat selvittää, esiintyykö toisessa luettelossa oleva datapiste toisessa luettelossa, sinun on käytettävä hakukaavoja.
Esitetään, että sinulla on alla esitetyn kaltainen tietokokonaisuus ja haluat tunnistaa yritykset, jotka esiintyvät sarakkeessa A mutta eivät sarakkeessa B,
Tässä tarkoituksessa voin käyttää seuraavaa VLOOKUP-kaavaa.
=ISERROR(VLOOKUP(A2,$B:$B,1,0))
Tämä kaava käyttää VLOOKUP-toimintoa tarkistaakseen, esiintyykö sarakkeessa A esiintyvä yrityksen nimi sarakkeessa B vai ei. Jos se on läsnä, se palauttaa kyseisen nimen sarakkeesta B, muuten se palauttaa #N/A-virheen.
Nämä nimet, jotka palauttavat #N/A-virheen, ovat nimiä, jotka puuttuvat sarakkeesta B.
ISERROR-funktio palauttaisi arvon TRUE, jos on VLOOKUP-tulos on virhe ja arvon FALSE, jos se ei ole virhe.
Jos haluat saada luettelon kaikista nimistä, joissa ei ole vastaavuutta, voit suodattaa tulossarakkeen niin, että saat kaikki solut, joissa on TRUE.
Voit myös käyttää MATCH-funktiota samaan;
=NOT(ISNUMBER(MATCH(A2,$B:$B,0)))
Huomautus: Henkilökohtaisesti käytän mieluummin Match-funktiota (tai INDEX/MATCH-yhdistelmän yhdistelmää) VLOOKUP:n sijaan. Minusta se on joustavampi ja tehokkaampi. Voit lukea Vlookupin ja Index/Matchin eron täältä.
Vertaa kahta saraketta ja hae vastaavat tiedot
Jos sinulla on kaksi tietokokonaisuutta ja haluat verrata toisen luettelon kohteita toiseen ja hakea vastaavan datapisteen, sinun on käytettävä lookup-kaavoja.
Esimerkki:
Esimerkiksi alla olevassa luettelossa haluan hakea sarakkeen 2 markkina-arvon. Tätä varten minun on etsittävä kyseinen arvo sarakkeesta 1 ja haettava sitten vastaava markkina-arvon arvo.
Alhaalla on kaava, jolla tämä tehdään:
=VLOOKUP(D2,$A:$B,2,0)
tai
=INDEX($A:$B,MATCH(D2,$A:$A,0),2)
Esimerkki: Pull the Matching Data (Partial)
Jos saat tietokokonaisuuden, jonka kahdessa sarakkeessa olevissa nimissä on pieni ero, edellä esitettyjen hakukaavojen käyttäminen ei toimi.
Nämä hakukaavat tarvitsevat tarkan vastaavuuden, jotta ne antavat oikean tuloksen. VLOOKUP- tai MATCH-funktiossa on likimääräinen täsmäämisvaihtoehto, mutta sitä ei voi käyttää tässä.
Esitellään, että sinulla on alla esitetyn kaltainen tietokokonaisuus. Huomaa, että sarakkeessa 2 on nimiä, jotka eivät ole täydellisiä (kuten JPMorgan JPMorgan Chasen sijasta ja Exxon ExxonMobilin sijasta).
Tässä tapauksessa voit käyttää osittaista hakua käyttämällä jokerimerkkejä.
Tässä tapauksessa seuraava kaava antaa oikean tuloksen:
=VLOOKUP("*"&D2&"*",$A:$B,2,0)
tai
=INDEX($A:$B,MATCH("*"&D2&"*",$A:$A,0),2)
Yllä olevassa esimerkissä tähti (*) on jokerimerkki, joka voi edustaa mitä tahansa merkkejä. Kun hakuarvoa reunustetaan sillä molemmin puolin, mikä tahansa arvo sarakkeessa 1, joka sisältää hakuarvon sarakkeessa 2, katsotaan täsmääväksi.
Esimerkiksi *Exxon* olisi täsmäävä merkki ExxonMobilille (koska * voi edustaa mitä tahansa merkkimäärää).
Saatat myös pitää seuraavista Excel-vihjeistä & Tutorials:
- How to Compare Two Excel Sheets (for differences)
- How to Highlight Blank Cells in Excel.
- Highlight EVERY Other ROW in Excel.
- Excel Advanced Filter: Täydellinen opas esimerkkeineen.
- Highlight Rows Based on a Cell Value in Excel.