Katso video - Vertaa kahta Excelin saraketta otteluihin ja eroihin
Yksi kysely, jonka saan paljon, on "kuinka vertailla 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ä.
Voit esimerkiksi vertailla kahta saraketta ja löytää tai korostaa kaikki vastaavat datapisteet (jotka ovat molemmissa sarakkeissa) tai vain erot (jos datapiste on yhdessä sarakkeessa eikä toisessa) jne.
Koska minulta kysytään tästä niin paljon, päätin kirjoittaa tämän massiivisen opetusohjelman tarkoituksena kattaa useimmat (ellei kaikki) mahdolliset skenaariot.
Jos pidät tätä hyödyllisenä, välitä se muille Excel -käyttäjille.
Huomaa, että tässä opetusohjelmassa esitetyt sarakkeiden vertailutekniikat eivät ole ainoita.
Aineistosi perusteella sinun on ehkä muutettava tai muokattava menetelmää. Perusperiaatteet pysyisivät kuitenkin ennallaan.
Jos luulet, että tähän opetusohjelmaan voidaan lisätä jotain, kerro siitä kommenttiosassa
Vertaa kahta saraketta täsmälliseen rivikohtaan
Tämä on yksinkertaisin vertailumuoto. Tässä tapauksessa sinun on tehtävä rivikohtainen vertailu ja tunnistettava, millä riveillä on samat tiedot ja millä ei.
Esimerkki: Vertaa saman rivin soluja
Alla on tietojoukko, jossa minun on tarkistettava, onko sarakkeen A nimi sama sarakkeessa B vai ei.
Jos on osuma, tarvitsen tuloksen "TOSI", ja jos ei täsmää, tarvitsen tuloksen "EPÄTOSI".
Alla oleva kaava tekisi tämän:
= A2 = B2
Esimerkki: Vertaa soluja samalla rivillä (IF -kaavan avulla)
Jos haluat saada kuvaavamman tuloksen, voit käyttää yksinkertaista IF -kaavaa palauttaaksesi "Match", kun nimet ovat samat, ja "Mismatch", kun nimet ovat erilaisia.
= JOS (A2 = B2, "Match", "Mismatch")
Huomautus: Jos haluat tehdä vertailusta isot ja pienet kirjaimet, käytä seuraavaa IF -kaavaa:
= JOS (EXACT (A2, B2), "Match", "Mismatch")
Yllä olevalla kaavalla "IBM" ja "ibm" pidettäisiin kahdena eri nimenä ja yllä oleva kaava palauttaisi "Ei täsmää".
Esimerkki: Korosta rivit vastaavien tietojen kanssa
Jos haluat korostaa rivit, joilla on vastaavia tietoja (sen sijaan, että saisit tuloksen erilliseen sarakkeeseen), voit tehdä sen käyttämällä ehdollista muotoilua.
Tässä on seuraavat vaiheet:
- Valitse koko tietojoukko.
- Napsauta "Etusivu" -välilehteä.
- Valitse Tyylit -ryhmässä Ehdollinen muotoilu -vaihtoehto.
- Valitse avattavasta valikosta Uusi sääntö.
- Napsauta "Uusi muotoilusääntö" -valintaikkunassa "Käytä kaavaa määritettävien solujen määrittämiseen".
- Kirjoita kaava -kenttään kaava: = $ A1 = $ B1
- Napsauta Muoto -painiketta ja määritä muoto, jota haluat soveltaa vastaaviin soluihin.
- Napsauta OK.
Tämä korostaa kaikki solut, joiden nimet ovat samat kullakin rivillä.
Vertaa kahta saraketta ja kohokohtia
Jos haluat verrata kahta saraketta ja korostaa vastaavia tietoja, voit käyttää kaksoistoimintoa ehdollisessa muotoilussa.
Huomaa, että tämä on eri asia kuin mitä olemme nähneet vertaamalla kutakin riviä. Tässä tapauksessa emme vertaa rivi kerrallaan.
Esimerkki: Vertaa kahta saraketta ja korosta vastaavia tietoja
Usein saat tietojoukkoja, joissa on osumia, mutta ne eivät välttämättä ole samalla rivillä.
Jotain alla olevan kuvan mukaisesti:
Huomaa, että sarakkeen A luettelo on suurempi kuin B: n. Myös jotkin nimet ovat molemmissa luetteloissa, mutta eivät samalla rivillä (kuten IBM, Adobe, Walmart).
Jos haluat korostaa kaikki vastaavat yritysten nimet, voit tehdä sen käyttämällä ehdollista muotoilua.
Tässä on seuraavat vaiheet:
- Valitse koko tietojoukko.
- Napsauta Etusivu -välilehteä.
- Valitse Tyylit -ryhmässä Ehdollinen muotoilu -vaihtoehto.
- Vie hiiri Korosta solusäännöt -vaihtoehdon kohdalle.
- Napsauta Päällekkäiset arvot.
- Varmista, että Kopioi arvot -valintaikkunassa 'Kopioi' on valittuna.
- Määritä muotoilu.
- Napsauta OK.
Yllä olevat vaiheet antaisivat sinulle alla olevan tuloksen.
Huomautus: Ehdollisen muotoilun kaksoiskappaleen sääntö ei erota kirjainkokoa. Joten "omenaa" ja "omenaa" pidetään samana ja ne korostetaan kaksoiskappaleina.
Esimerkki: Vertaa kahta saraketta ja korosta ristiriitaisia tietoja
Jos haluat korostaa nimiä, jotka ovat yhdessä luettelossa eikä toisessa, voit käyttää ehdollista muotoilua myös tähän.
- Valitse koko tietojoukko.
- Napsauta Etusivu -välilehteä.
- Valitse Tyylit -ryhmässä Ehdollinen muotoilu -vaihtoehto.
- Vie hiiri Korosta solusäännöt -vaihtoehdon kohdalle.
- Napsauta Päällekkäiset arvot.
- Varmista, että Kopioi arvot -valintaikkunassa on valittuna Ainutlaatuinen.
- Määritä muotoilu.
- Napsauta OK.
Tämä antaa sinulle alla olevan tuloksen. Se korostaa kaikki solut, joiden nimi ei ole toisessa luettelossa.
Vertaa kahta saraketta ja etsi puuttuvia datapisteitä
Jos haluat tunnistaa, onko yhden luettelon datapiste olemassa toisessa luettelossa, sinun on käytettävä hakukaavoja.
Oletetaan, että sinulla on alla esitetyn kaltainen tietojoukko ja haluat tunnistaa yritykset, jotka ovat sarakkeessa A mutta eivät sarakkeessa B,
Tätä varten voin käyttää seuraavaa VLOOKUP -kaavaa.
= VIRHE (HAKU (A2, $ B $ 2: $ B $ 10,1,0))
Tämä kaava tarkistaa VLOOKUP -toiminnon avulla, onko A -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 virheen #N/A, puuttuvat sarakkeesta B.
ISERROR -funktio palauttaa TOSI, jos VLOOKUP -tulos on virhe, ja FALSE, jos se ei ole virhe.
Jos haluat saada luettelon kaikista nimistä, joissa ei ole vastaavuutta, voit suodattaa tulosarakkeen saadaksesi kaikki solut, joissa on TOSI.
Voit myös tehdä saman käyttämällä MATCH -toimintoa;
= EI (ISNUMBER (MATCH (A2, $ B $ 2: $ B $ 10,0)))
Huomautus: Henkilökohtaisesti mieluummin käytä hakutoimintoa (tai INDEX/MATCH -yhdistelmää) VLOOKUPin sijaan. Minusta se on joustavampi ja tehokkaampi. Voit lukea Vlookupin ja Index/Matchin välisen eron täältä.
Vertaa kahta saraketta ja vedä vastaavat tiedot
Jos sinulla on kaksi tietojoukkoa ja haluat vertailla luettelon kohteita toiseen ja hakea vastaavan datapisteen, sinun on käytettävä hakukaavoja.
Esimerkki: Vedä vastaavat tiedot (tarkka)
Esimerkiksi alla olevassa luettelossa haluan noutaa sarakkeen 2 markkina -arvon. Tätä varten minun on haettava kyseinen arvo sarakkeesta 1 ja haettava sitten vastaava markkina -arvo.
Alla on kaava, jolla tämä tehdään:
= HAKU (D2, $ A $ 2: $ B $ 14,2,0)
tai
= INDEKSI ($ A $ 2: $ B $ 14, MATCH (D2, $ A $ 2: $ A $ 14,0), 2)
Esimerkki: Vedä vastaavat tiedot (osittainen)
Jos saat tietojoukon, jossa kahden sarakkeen nimissä on pieni ero, yllä esitettyjen hakukaavojen käyttäminen ei toimi.
Nämä hakukaavat tarvitsevat tarkan vastaavuuden oikean tuloksen saamiseksi. VLOOKUP- tai MATCH -toiminnossa on likimääräinen otteluvaihtoehto, mutta sitä ei voi käyttää tässä.
Oletetaan, että sinulla on alla esitetyt tiedot. Huomaa, että sarakkeessa 2 on nimet, jotka eivät ole täydellisiä (kuten JPMorgan JPMorgan Chasen sijaan ja Exxon ExxonMobilin sijaan).
Tällaisessa tapauksessa voit käyttää osittaista hakua käyttämällä jokerimerkkejä.
Seuraava kaava antaa oikean tuloksen tässä tapauksessa:
= VLOOKUP ("*" & D2 & "*", $ A $ 2: $ B $ 14,2,0)
tai
= INDEKSI ($ A $ 2: $ B $ 14, MATCH ("*" & D2 & "*", $ A $ 2: $ A $ 14,0), 2)
Yllä olevassa esimerkissä tähti (*) on jokerimerkki, joka voi edustaa mitä tahansa määrää merkkejä. Kun hakuarvo on sen molemmin puolin, mitä tahansa sarakkeen 1 arvoa, joka sisältää hakuarvon sarakkeessa 2, pidetään osumana.
Esimerkiksi * Exxon * vastaa ExxonMobilia (koska * voi edustaa mitä tahansa merkkiä).
Saatat pitää myös seuraavista Excel -vihjeistä ja opetusohjelmista:
- Kahden Excel -taulukon vertaaminen (erojen vuoksi)
- Tyhjien solujen korostaminen Excelissä.
- Korosta JOKA muu rivi Excelissä.
- Excel Advanced Filter: Täydellinen opas esimerkkeineen.
- Korosta rivit solun arvon perusteella Excelissä.
