Kun käsittelet suuria tietojoukkoja Excelissä, saatat joutua vertaamaan kahta saraketta löytääksesi yhtäläisyyksiä tai eroja.
VLOOKUP on tehokas toiminto, jonka avulla voit etsiä vastaavia tietoja kahden sarakkeen välillä. Toiminnon avulla voit etsiä yhdestä sarakkeesta arvoja, jotka näkyvät toisessa.
Tässä artikkelissa kerrotaan, kuinka voit verrata kahta saraketta VLOOKUPin avulla, jotta voit analysoida tietojasi tehokkaasti.
Sisällysluettelo
Vlookup-funktion perusteet
Oletetaan, että sinulla on laskentataulukko, jossa on kaksi luetteloa sarakkeista A ja B. Haluat löytää luettelon 1 kohteet, jotka näkyvät myös luettelossa 2.
Voit kuvitella, että tuhansien kohteiden luetteloiden manuaalinen käsittely vie valtavasti aikaa. Onneksi tuo VLOOKUPin apuun!
Termi VLOOKUP tarkoittaa vertikaalista hakua. Funktio vertaa kahta saraketta, etsii vastaavuuksia niiden välillä ja palauttaa niihin liittyvät arvot.
Vlookup-toiminto
Tässä on VLOOKUP-funktion perussyntaksi:
=VHAKU(hakuarvo, taulukon_taulukko, sarakkeen_indeksin_määrä, [alueen_haku])
Tämä on erittely elementeistä:
lookup_value : arvo, jota haluat etsiä taulukon_taulukon ensimmäisestä sarakkeesta.
table_array : solualue, joka sisältää tiedot, joista haluat etsiä.
col_index_num : sarakkeen numero taulukossa, josta haluat palauttaa arvon.
range_lookup : Valinnainen. Se on joko TOSI (likimääräinen vastaavuus) tai EPÄTOSI (tarkka vastaavuus). Oletusarvo on TOSI.
Tietojesi valmistelu VLOOKUPia varten
Ennen kuin käytät VLOOKUPia kahden vertaamiseen, sinun on valmisteltava tietosi.
Luo laskentataulukkoon kaksi erillistä saraketta, joissa haluat verrata arvoja. Tässä artikkelissa käytetään sarakkeita A ja sarakkeita B esimerkeissämme.
Tässä on tässä artikkelissa käytetyt esimerkkitiedot:
Tämä on esimerkkitaulukko, jonka otsikko lisätään osioon "Tietojen valmistelu VLOOKUPia varten":
Luettelo 1 Luettelo 2
Kohde 1
Kohde 3 Kohde
2 Kohde 4 Kohde 3 Kohde 1
Kohde 4 Kohde 5
Tietojen muotoilu
On tärkeää varmistaa, että molempien sarakkeiden tiedot on muotoiltu samalla tavalla. VLOOKUP on isot ja pienet kirjaimet erotteleva, mikä tarkoittaa, että isot ja pienet kirjaimet ovat tärkeitä.
Lopputuloksessa voi esiintyä täsmäämisvirheitä, jos muotoilu on epäjohdonmukainen.
On myös hyvä idea poistaa päällekkäiset arvot tai tyhjät solut virheriskin minimoimiseksi.
Päällekkäisten arvojen poistaminen:
Valitse sarake.
Siirry ylimmän nauhan Tiedot-välilehteen.
Napsauta Tietotyökalut-osiossa Poista kaksoiskappaleet.
Painike voi olla hieman vaikea nähdä. Tämä kuva auttaa sinua:
Tyhjien solujen poistaminen:
Valitse sarake.
Siirry ylänauhan Etusivu-välilehteen.
Laajenna Etsi ja valitse -valikko.
Valitse "Siirry erityiseen".
Valitse vaihtoehdoista "Tyhjät".
Napsauta "Poista" Solut-osiossa.
Kuinka käyttää VLOOKUPia kahden sarakkeen vertailuun
Kun tiedot on valmis, voit kirjoittaa VLOOKUP-kaavan vertaillaksesi kahta saraketta Excelissä ja tunnistaaksesi osumat. Toimi seuraavasti:
Valitse uudesta sarakkeesta solu, jossa haluat näyttää vertailutulokset (esim. solu C2).
Kirjoita seuraava kaava: =VHAKU(A2, B:B, 1, FALSE))
Ota kaava käyttöön painamalla Enter.
Jos arvo täsmää, arvo näytetään tulossarakkeen samalla rivillä (esim. sarake C).
Vedä kaava alas C2:sta kopioidaksesi sen niin moneen soluun kuin tarvitset.
Jos käytät näytetietojamme, sijoita vertailutulokset kolmanteen sarakkeeseen. Kaava löytää kolme vastaavuutta listan 1 neljästä kohdasta.
Huomaa, että kohta 2 näkyy muodossa #N/A. Excel kertoo meille, että vastaavuutta ei voida soveltaa eli sitä ei löydy.
Se on oikein ja hyödyllistä tietoa puuttuvista arvoista. Jotkut Excelin käyttäjät saattavat kuitenkin ajatella, että datassa on jokin ongelma tai toimintovirhe.
On hyvä käytäntö näyttää eri merkkiä siitä, että vastaavuutta ei löytynyt. Se voi olla yksinkertaisesti tyhjä.
Voit tehdä tämän yhdistämällä VLOOKUP-funktion IFNA-toimintoon seuraavasti:
=JOS(VHAKU(A1, B:B, 1, EPÄTOSI), "")
IFNA-toiminto havaitsee #N/A-virheen ja korvaa lähdön tyhjällä tilalla (""). Voit myös käyttää ISNA-toimintoa tai käyttää lisälogiikkaa IF-funktiolla.
Muiden virheiden käsittely
VLOOKUP voi joskus tuottaa muita virheitä vertailutuloksissasi. #REF ! Virhe Excelissä on todellinen ongelma tiedoissasi.
Se tapahtuu yleensä, kun määrittämäsi alue on väärä. Esimerkkimme on viitannut koko sarakkeeseen, mutta voit käyttää myös pystysuuntaisia solualueita.
Varmista, että hakualue, johon viittaat, kattaa kaikki arvot, joita haluat vertailla.
Vaihtoehtoja VLOOKUPille sarakkeiden vertailua varten
On kaksi vaihtoehtoista hakutoimintoa, kun verrataan kahta saraketta Excelissä vastaavuuksien löytämiseksi.
1. Indeksi- ja hakufunktioiden käyttäminen
VLOOKUP-toiminnon sijaan voit verrata kahta saraketta Excelissä yhdistämällä INDEX- ja MATCH-funktiot.
Tämä menetelmä tarjoaa joustavamman tavan etsiä tietoja ja on erityisen hyödyllinen, kun työskentelet ei-viereisten sarakkeiden kanssa tai kun sarakeindeksi saattaa muuttua.
Tämä on tulossarakkeeseen lisättävä syntaksi:
=INDEKSI(paluuväli, VASTAA(hakuarvo, hakuväli, 0))
Palautusalue : solualue, joka sisältää palautettavat tiedot.
Lookup_value : arvo, jota haluat etsiä hakualueelta.
Hakualue : solualue, josta haluat löytää hakuarvon.
Käyttämällä samoja tietoja edellisissä esimerkeissä, korvaamme VLOOKUP-kaavan seuraavasti:
=(INDEKSI(B:B, MATCH(A2, B:B, 0)))
MATCH-funktio palauttaa hakuarvon suhteellisen sijainnin hakualueella ja INDEX-funktio palauttaa vastaavan arvon palautusalueelta.
Tulokset samoista tiedoista ovat samat kuin aiemmin käyttämämme VLOOKUP. Tässä kuvassa näkyvät käytössä olevat toiminnot:
Voit myös korvata #N/A-virheen mukautetulla viestillä. Tässä on esimerkki IFERROR-funktion käytöstä.
=IFORROR(INDEKSI(B:B, MATCH(A2, B:B, 0)), "Ei löydy"
2. XLOOKUPin käyttäminen Excelissä
Käyttäjille, joilla on Excel 365 tai Excel 2019, XLOOKUP on vaihtoehtoinen looginen testi VLOOKUPille yhteisten arvojen löytämiseksi kahdesta sarakkeesta.
XLOOKUP tarjoaa useita etuja. Sen avulla voit etsiä tietoja sekä vaaka- että pystysuunnassa, työskennellä muiden kuin vierekkäisten sarakkeiden kanssa ja määrittää mukautettuja arvoja virheille.
XLOOKUPin syntaksi on:
=XHAKU(hakuarvo, haun_alue, paluuväli, [jos_ei_löyty], [vastaavuustila], [hakutila])
lookup_value : arvo, jota haluat etsiä hakualueelta.
lookup_range : solualue, josta haluat löytää hakuarvon.
paluualue : solualue, joka sisältää palautettavat tiedot.
Lisää mukautettuja virhearvoja, vastaavuustilaa ja hakutilan parametreja tarpeen mukaan.
XLOOKUP löytää hakuarvon hakualueelta ja palauttaa vastaavan arvon palautusalueelta.
Kaava on erityisen hyödyllinen, kun sinulla on kaksi luetteloa upotettuna useiden sarakkeiden tietosarjoihin. Aiemmissa esimerkeissämme luettelot ovat olleet ensimmäisessä sarakkeessa ja toisessa sarakkeessa, mutta taulukossasi voi olla enemmän tietoja.
Tässä on esimerkki kahdesta luettelosta laskentataulukon ensimmäisessä ja kolmannessa sarakkeessa:
XLOOKUP-kaava näyttää tältä:
=XHAKU(A2,C:C,C:C"")
Tässä kuvassa on tulos sarakkeen D vertailusta. Ensimmäinen arvo on olemassa, mutta toinen puuttuu.
Huomaa, että minulla ei ole ylimääräisiä virhekaavoja, mutta puuttuva arvo näytetään tyhjänä. Tämä johtuu siitä, että käytän neljättä parametria, joka on mukautettu virhearvo. Tässä tapauksessa se on tyhjä merkkijono.
Bonuksena näytän sinulle, kuinka voit käyttää XLOOKUPia rivien vertailuun. Jos sinulla on kaksi listaa, jotka ovat rivillä 1 ja rivillä 2, kaavan yksinkertaisin käyttö näyttää tältä:
=XHAKU(B1, 2:2, 2:2, "")
Tämä kuva näyttää tulokset kahdelta riviltä.
Parannetun toiminnallisuuden ja joustavuuden ansiosta XLOOKUP on erinomainen vaihtoehto VLOOKUP:lle Excelin luetteloiden vertailuun sen määrittämiseksi, löytyykö vastaavuuksia.
Viisi vinkkiä VLOOKUP-suorituskyvyn parantamiseen
VLOOKUP-toiminnon optimointi voi auttaa sinua välttämään pitkiä odotusaikoja ja parantamaan Excel-laskentataulukon yleistä reagointikykyä.
Tässä on kuusi vinkkiä ja temppua, joita voit soveltaa parantaaksesi VLOOKUPin suorituskykyä Excelissä.
1. Rajoita hakualuettasi
Käytin esimerkeissä kokonaisia sarakkeita tehdäkseni niistä yksinkertaisia. Jos työskentelet suuren tietomäärän kanssa, sinun tulee välttää niin. Täyden sarakkeen käyttäminen voi hidastaa Excelin laskentaprosessia.
Yritä sen sijaan käyttää tarkkaa datasi edellyttämää aluetta (esim. A1:A100). Tämä vähentää niiden solujen määrää, jotka VLOOKUP-funktion on arvioitava.
2. Käytä absoluuttisia viittauksia
Kun määrität tietoalueen (esim. solut B2:sta B5:een), käytä absoluuttisia viittauksia. Tämä varmistaa, että kaava on johdonmukainen ja tietoja on olemassa, kun kopioit sen useisiin soluihin.
Tässä on esimerkki:
=HAKU(A2, $B$2:$B$5, 1, EPÄTOSI)
3. Lajittele tietosi
Jos tiedät, että hakusarakkeen tiedot on lajiteltu nousevaan järjestykseen, voit käyttää VLOOKUP-arvoa TRUE- tai 1-arvon kanssa "range_lookup"-argumentissa.
Tämä saa Excelin suorittamaan likimääräisen vastaavuuden, joka on nopeampi kuin kaikkien solujen tarkka vastaavuus. Ole kuitenkin varovainen käyttäessäsi tätä vaihtoehtoa, koska väärä lajittelu voi johtaa vääriin tuloksiin.
4. Käytä kaksoishakua
Voit käyttää kahta VLOOKUP-toimintoa nopeuttaaksesi hakuprosessia Excelissä.
Ensimmäinen VHAKU määrittää, onko hakuarvo olemassa asettamalla 'col_index_num'-arvoksi 1 ja 'range_lookup'-arvoksi TOSI.
Jos se palauttaa TOSI, toinen VLOOKUP hakee halutun arvon 'range_lookup' -asetuksena TOSI.
5. Käytä ehdollista muotoilua
Voit käyttää ehdollista muotoilua Excel-laskentataulukossa korostaaksesi vastaavat tai puuttuvat arvot määritetyssä sarakkeessa. Voit myös käyttää värejä ainutlaatuisiin arvoihin. Näin tietosi on helpompi lukea.
Löydät ehdollisen muotoilun valikon Koti-välilehden tyyliryhmästä.
VLOOKUPin lisäkäyttö
Parinäytteet T-testejä käytetään vertaamaan kahden toisiinsa liittyvän näytteen keskiarvoja. Tämä video näyttää niiden käytön Excelissä.
Jos sinulla on muita tietoja tai muuttujia, joihin sinun on viitattava analyysin aikana, voit käyttää VLOOKUP-toimintoa hakeaksesi tarvittavat arvot toisesta taulukosta tai laskentataulukosta.
Voit esimerkiksi käyttää VLOOKUP-toimintoa demografisten tietojen tai hoitoolosuhteiden hakemiseen jokaisesta tietojoukossasi olevasta havainnosta.
Viimeinen sanamme
Tähän mennessä sinulla on vankka käsitys VLOOKUP:n käytöstä. Tämän tehokkaan toiminnon avulla voit nopeasti tunnistaa erot ja vastaavat arvot kahden luettelon välillä, mikä tekee tietojen analysoinnista tehokkaampaa ja tarkempaa.
Näit myös esimerkkejä muista haku- ja viitefunktioista, kuten XLOOKUP, INDEX ja MATCH. Näiden elementtien lisääminen taitoihin vahvistaa edelleen data-analyysikykyäsi.
Jatka VLOOKUP-taitojen harjoittelua ja hiomista, niin sinusta tulee pian asiantuntija Excelin sarakkeiden vertailussa, mikä säästää aikaa ja lisää tuottavuuttasi.
Tämä blogi sisältää Shape Map -visualisoinnin tilaanalyysiä varten LuckyTemplatesissa. Näytän sinulle, kuinka voit käyttää tätä visualisointia tehokkaasti sen ominaisuuksien ja elementtien kanssa.
Tässä opetusohjelmassa esittelen ainutlaatuisen idean talousraportoinnista, joka allokoi tulokset LuckyTemplatesin sisällä olevien taulukkopohjien ennalta määrittämiseen.
Luo DAX-suureita LuckyTemplatesissa käyttämällä olemassa olevia suureita tai kaavoja. Tätä kutsun mittahaaroitustekniikaksi.
Tässä blogissa tutustu LuckyTemplates-tietojoukkoon, tehokkaimpaan funktiokutsuun, joka tuo tuhansia M- ja DAX-funktioita sormiesi ulottuville.
Tämän päivän opetusohjelmassa jaan muutamia datamallinnustekniikoita DAX-mittausten järjestämiseksi paremmin tehokkaamman työnkulun aikaansaamiseksi.
LuckyTemplates on loistava työkalu talousraportointiin. Tässä on opetusohjelma räätälöityjen taulukoiden luomisesta LuckyTemplates-talouden hallintapaneelillesi.
Tässä opetusohjelmassa keskustellaan Power Query Language Flowsta ja siitä, kuinka se voi auttaa luomaan sujuvan ja tehokkaan dataraportin.
Keskustelen yhdestä suosikkitekniikoistani mukautettujen LuckyTemplates-kuvakkeiden ympärillä, joka käyttää mukautettuja kuvakkeita dynaamisella tavalla LuckyTemplates-visuaaleissa.
Tässä blogissa näytän sinulle, kuinka voit luoda LuckyTemplates-taulukoita käyttämällä kaavaa, joka yhdistää UNION-funktion ja ROW-funktion.
Tutustu siihen, kuinka On-premises-tietoyhdyskäytävä antaa Power Automaten käyttää työpöytäsovelluksia, kun käyttäjä on poissa tietokoneesta.
