VHAKU kahden työkirjan väliltä: helppo vaiheittainen opas

Jos työskentelet useiden työkirjojen kanssa Excelissä, saatat joutua käyttämään VLOOKUP-toimintoa tietojen hakemiseen työkirjasta toiseen.

VHAKU-funktio etsii tiettyä arvoa erillisen työkirjan taulukosta ja palauttaa vastaavan arvon samalta riviltä. Syntaksin on sisällettävä täydellinen viittaus toiseen työkirjaan ja laskentataulukon nimeen.

Viitteiden saaminen oikein voi olla haastavaa, kun käytät toimintoa ensimmäistä kertaa. Tässä artikkelissa käytetään esimerkkilaskentataulukoita, jotka osoittavat tarkalleen, kuinka kaavat syötetään.

Opit myös joitain parannuksia VLOOKUPiin ja useita vaihtoehtoja toiminnolle.

Aloitetaan!

Sisällysluettelo

• Mikä on VLOOKUP?
• Kuinka käyttää VLOOKUPia kahden työkirjan välillä
  • Esimerkkitiedot
  • Vaihe 1: Pidä molemmat työkirjat auki
  • Vaihe 2: Aloita VLOOKUP-kaavan syöttäminen
  • Vaihe 3: Toiseen työkirjaan viittaaminen
• 3 yleistä VLOOKUP-virhettä ja niiden korjaaminen
  • 1. #N/A -virhe
  • 2. #Ref! Sarakenumeron virhe
  • 3. Lainausmerkkien ja hakasulkeiden virhe
• 4 vinkkiä erilaisten työkirjojen hallintaan
• Kuinka päivittää tiedot automaattisesti
• VLOOKUP-funktion lisäkäytöt
• 2 Tärkeimmät vaihtoehdot VLOOKUP:lle
  • 1. INDEKSIOTTO
  • 2. XLOOKUP
• VLOOKUP:n käyttäminen useiden arkkien kesken
  • Vaihe 1: Tunnista komponentit
  • Vaihe 2: Määritä tietosi
  • Vaihe 3: Käytä VLOOKUP-toimintoa
  • (Valinnainen) Vaihe 4: #N/A-virheiden vianetsintä
• Lopulliset ajatukset

Mikä on VLOOKUP?

Oletetaan, että sinulla on työkirja tuotemyynnistä ja erillinen työkirja, jossa on kunkin tuotteen hinnat. Haluat tarkastella toisen työkirjan hintoja ja käyttää niitä ensimmäisen työkirjan laskelmissa.

VLOOKUP on yksi ratkaisu tähän tehtävään. Termi tarkoittaa "Vertical Lookup" . "

Se on sisäänrakennettu toiminto, jonka avulla voit etsiä tiettyä arvoa sarakkeesta ja palauttaa vastaavan arvon toiseen sarakkeeseen sijainnin perusteella.

Sitä voidaan käyttää seuraavien kanssa:

• yksi laskentataulukko

• useita arkkeja samassa työkirjassa

• kaksi Excel-työkirjaa

Jos sinulla ei ole useiden työkirjojen monimutkaisuutta, katso tämä opetusohjelma osoitteessa .

Kuinka käyttää VLOOKUPia kahden työkirjan välillä

VLOOKUP-toiminto on erityisen hyödyllinen, kun haluat etsiä tietoja useista laskentataulukoista tai työkirjoista.

Kolme päävaihetta sen tekemiseksi ovat:

1. Avaa molemmat työkirjat.

2. Kirjoita VLOOKUP-kaava ensimmäiseen Excel-työkirjaan.

3. Anna täydellinen viittaus toiseen työkirjaan.

Esimerkkitiedot

Tämä yksinkertainen opetusohjelma toimii esimerkkitietoihin perustuvien esimerkkien avulla. Luo uusi työkirja nimeltä "Myynti" ja kirjoita nämä tiedot sarakkeisiin A ja B:

• Omenat, 40

• Appelsiinit, 30

• Päärynät, 70

• Banaanit, 20

Lisää kolmas sarake hintatietojen säilyttämiseen:

Luo toinen Excel-työkirja nimeltä "Tuote" ja nimeä Sheet1 uudelleen nimellä "Hinnat". Syötä nämä tiedot sarakkeisiin A ja B:

• Omenat, 1,50

• Appelsiinit, 2.30

• Päärynät, 3,60

• Banaanit, 2.10

Data näyttää tältä:

Laskeaksemme ensimmäisen laskentataulukon myyntitulot meidän on etsittävä hinta toisesta laskentataulukosta. VLOOKUP apuun!

Vaihe 1: Pidä molemmat työkirjat auki

Molempien työkirjojen on oltava auki käytettäessä VLOOKUP-kaavaa kahden työkirjan välillä. Jos toinen työkirja suljetaan, funktio palauttaa virheilmoituksen.

Voit joko avata ne erikseen tai käyttää Excelin Avaa-komentoa avataksesi ne molemmat kerralla. On kaksi tapaa avata jompikumpi työkirja:

• Siirry Tiedosto-välilehteen ja valitse "Avaa".

• Käytä "Ctrl + O" -pikanäppäintä .

Kun molemmat työkirjat ovat auki, voit vaihtaa niiden välillä napsauttamalla asianmukaista välilehteä Excel-ikkunan alaosassa.

Vaihe 2: Aloita VLOOKUP-kaavan syöttäminen

Tässä on VLOOKUP-funktion perussyntaksi:

=VHAKU(hakuarvo, taulukon_taulukko, sarakkeen_indeksin_määrä, [alueen_haku])

• lookup_value : Arvo, jonka haluat etsiä toisesta työkirjasta.

• table_array : Toisen työkirjan solualue, joka sisältää tiedot, jotka haluat noutaa.

• col_index_num : sarakkeen numero alueella, joka sisältää tiedot, jotka haluat noutaa.

• range_lookup : Tämä viimeinen parametri on valinnainen. Jos asetat sen arvoon TOSI tai jätät sen pois, VLOOKUP etsii likimääräistä vastaavuutta. Jos asetat sen arvoon FALSE, VLOOKUP etsii vain tarkkaa vastaavuutta.

Oletetaan, että haluat etsiä omenoiden hintoja näytetietojemme toisesta työkirjasta. Näin kaava hajoaa:

• Hakuarvo on ensimmäisen työkirjan solussa A2.

• Taulukkotaulukko on toisen työkirjan solualueella A1:B5.

• Col_index_num on toinen sarake, joka sisältää hinnan.

VLOOKUP-kaavan monimutkaisin osa on viittaus toiseen työkirjaan oikein.

Vaihe 3: Toiseen työkirjaan viittaaminen

Täydentääksesi VLOOKUP-kaavan kahdelle työkirjalle, sinun on käytettävä täysin pätevää viitettä, joka sisältää nämä tiedot toisesta työkirjasta:

• työkirjan nimi

• arkin nimi

• solualue

Tämä varmistaa, että Excel tietää tarkalleen, mistä löytää etsimäsi tiedot. Täydellistä viittausta käytetään VLOOKUP-funktion table_array-argumentissa.

Nämä ovat näytetietojen arvot:

• työkirjan nimi: Tuote.xlsx

• levyn nimi: Hinnat

• solualue: $A1:$B5

Voit etsiä ensimmäisen tuotteen hinnan kirjoittamalla seuraavan kaavan ensimmäisen työkirjan soluun C2:

=VHAKU(A2, [Tuote.xlsx]Hinnat!$A$1:$B$5, 2, EPÄTOSI)

Tämä kuva näyttää funktion toiminnassa ensimmäisen solun kaavapalkissa:

Huomaa, että työkirjan nimi on suljettu hakasulkeissa ja arkin nimeä seuraa huutomerkki.

Jos Excel-tiedoston nimi sisältää välilyönnin tai erikoismerkkejä, sinun on suljettava koko työkirja ja taulukko yksittäisiin lainausmerkkeihin.

Kaava käyttää solualueen absoluuttisia arvoja ($-etuliite). Tämän avulla voit kopioida kaavan alas muille riveille napsauttamalla täyttökahvaa.

Tässä ovat VLOOKUP-tulokset:

Ja siinä se! Tiedät nyt, kuinka VLOOKUPia käytetään tehokkaasti. Olemme koonneet seuraavaan osioon kolme yleisintä VLOOKUP-virhettä auttaaksemme sinua siinä, että kohtaat virheitä.

3 yleistä VLOOKUP-virhettä ja niiden korjaaminen

Nyt kun olet oppinut käyttämään VLOOKUPia toisesta työkirjasta, saatat kohdata joitain yleisiä virheitä.

Tässä on kolme yleisintä virhettä ja niiden korjaaminen.

1. #N/A -virhe

#N/A -virhe ilmenee, kun hakuarvoa ei löydy taulukkotaulukosta. Voit korjata tämän virheen kokeilemalla seuraavia:

• Tarkista hakuarvon oikeinkirjoitus.

• Varmista, että hakuarvo on taulukkotaulukon ensimmäisessä sarakkeessa.

• Tarkista, että hakuarvoa ei ole muotoiltu tekstiksi kahdessa taulukossa.

• Varmista, että range_lookup-argumentin arvo on FALSE löytääksesi tarkan vastaavuuden.

Yllä oleva esimerkki ei löytänyt vastaavaa arvoa Cherriesille toisesta työkirjasta.

2. #Ref! Sarakenumeron virhe

#Ref ! Virhe tapahtuu, kun argumentti col_index_num on suurempi kuin taulukkotaulukon sarakkeiden määrä.

Voit korjata tämän virheen tarkistamalla, että argumentti col_index_num on pienempi tai yhtä suuri kuin lähdetaulukon sarakkeiden määrä.

Alla olevassa kuvassa col_index_num on asetettu arvoon 3, mutta viitealueella on vain kaksi saraketta.

3. Lainausmerkkien ja hakasulkeiden virhe

Lainausmerkkien ja hakasulkeiden virhe tapahtuu, kun suoritat VHAKU-toiminnon arvolla, joka sisältää lainausmerkit tai hakasulkeet.

Voit korjata tämän virheen kokeilemalla seuraavia:

• Käytä lainausmerkkejä hakuarvon ympärillä.

• Käytä CHAR(34) -funktiota lainausmerkkien lisäämiseen.

• Käytä IFERROR-funktiota palauttaaksesi tyhjän solun, jos hakuarvo sisältää lainausmerkkejä tai hakasulkeita.

Kun olet tutustunut näihin virheisiin, niiden korjaaminen on helppoa! Jotta sinusta tulisi VLOOKUP-ammattilainen, olemme antaneet seuraavassa osiossa neljä vinkkiä erilaisten työkirjojen hallintaan.

4 vinkkiä erilaisten työkirjojen hallintaan

Kun työskentelet useiden työkirjojen kanssa, on tärkeää seurata, mikä työkirja sisältää mitäkin tietoja.

Tässä on neljä parasta vinkkiämme mielenterveyden ylläpitämiseen:

1. Käytä nimeämiskäytäntöjä auttaaksesi muistamaan, mitkä Excel-laskentataulukot sisältävät mitäkin tietoja.

2. Käytä kaavoissasi absoluuttisia viittauksia varmistaaksesi, että hakualue viittaa oikeisiin soluihin, vaikka siirrät tai kopioit kaavan toiseen paikkaan tai Excel-taulukkoon.

3. Käytä IFERROR-funktiota käsitelläksesi #N/A-virheitä, joita saattaa ilmetä, jos hakuarvoja ei löydy lähdetiedoista.

4. Harkitse, ettei niitä voi ylikirjoittaa.

Kuinka päivittää tiedot automaattisesti

Kun työskentelet useiden Excel-tiedostojen kanssa, voi olla aikaa vievää.

Jos haluat VLOOKUP-kaavan päivittyvän automaattisesti, kun lisäät tai poistat tietoja muista työkirjoista, voit käyttää EPÄSUORA-toimintoa.

Näin:

• Korvaa VHAKU-kaavassa taulukkotaulukon argumentti EPÄSUORA-funktiolla.

• Käytä EPÄSUORA-funktiossa viittausta soluun, joka sisältää taulukkotaulukon toisessa työkirjassa.

Voit saavuttaa tämän esimerkkityökirjoillamme noudattamalla näitä ohjeita.

1. Käytä yhtä solua sarakkeessa D syöttääksesi viittaus "Tuote"-työkirjan "hinnat"-taulukkoon ja solualueeseen A1:B5 seuraavasti: [Tuote.xlsx]Hinnat!A:B

2. Käytä "Sales"-työkirjan solussa C2 tätä kaavaa: =VHAKU(A2, INDIRECT($D$1), 2, FALSE)

3. Kopioi kaava alas (absoluuttiset viittaukset varmistavat, että laskentataulukon solu säilyy).

Nämä toimet varmistavat, että alkuperäisen taulukon muutokset päivitetään automaattisesti yhdistetyissä soluissa.

Tässä kuvassa näkyy EPÄSUORA-toiminto yhdistettynä VHAKU-toimintoon:

Huomaa, että tämä esimerkki käyttää aluetta "A:B" viittaamaan kahteen sarakkeeseen. Tämä tarkoittaa, että kun lisäät tuotteita ja hintoja, kaava toimii edelleen nykyisessä työkirjassa.

Nyt kun olet saanut maistaa VLOOKUPin tehoa, katsotaanpa joitain toiminnon edistyneitä käyttötapoja osiossa!

VLOOKUP-funktion lisäkäytöt

Tämän artikkelin esimerkit palauttavat yhden vastaavuuden jokaiselle hakuarvolle. Jos haluat käsitellä useita eri työkirjan osumia, voit käyttää sen sijaan taulukkokaavaa.

Voit myös yhdistää VLOOKUPin muihin toimintoihin. Voit esimerkiksi upottaa sen IF-funktioon monimutkaisempaa analysointia varten.

Monimutkainen analyysi sisältää tekniikoita, kuten khin neliötestejä ja parinäytteiden T-testejä. Tässä on esimerkki khin neliötestin käyttämisestä Excelissä:

Edistyneet käyttäjät hyödyntävät VLOOKUP-palvelua datajoukkojen vertailuun ja yhteensopimattomien tunnistamiseen. Löydät muita tapoja tästä artikkelista .

Mutta entä jos et halua käyttää VLOOKUPia mistä tahansa syystä? Olet onnekas! On olemassa joitain vaihtoehtoja, joita käsittelemme seuraavassa osiossa.

2 Tärkeimmät vaihtoehdot VLOOKUP:lle

Kun kyse on arvojen etsimisestä eri taulukoista, VHAKU on usein perustoiminto. On kuitenkin joitain vaihtoehtoja, joista voi olla hyötyä tietyissä tilanteissa.

Kaksi yleisintä ovat:

• INDEKSIOTTELU

• XHAKU

1. INDEKSIOTTO

INDEX ja MATCH on kahden funktion tehokas yhdistelmä, joita voidaan käyttää arvojen etsimiseen taulukosta. Toisin kuin VLOOKUP, joka edellyttää hakuarvon olevan taulukon vasemmanpuoleisessa sarakkeessa, INDEX MATCH voi etsiä arvoja mistä tahansa sarakkeesta.

Näin se toimii:

1. INDEKSI-funktio palauttaa arvon määritetyltä alueelta rivin ja sarakkeen numeron perusteella.

2. MATCH-funktio palauttaa hakuarvon sijainnin määritetyllä alueella.

Jotta voit käyttää INDEX MATCHia, sinun on määritettävä alue, josta haluat etsiä, rivi, josta haluat löytää hakuarvon, ja sarake, johon haluat palauttaa tuloksen.

Tässä on esimerkkikaava:

=INDEKSI(Taulukkoalue,MATCH(HakuArvo,Hakualue,0),Sarakeindeksi)

Voit käyttää tätä esimerkkilaskentataulukoiden kanssa seuraavasti:

=INDEKSI([Tuote.xlsx]Hinnat!$B$1:$B$5, VASTAA(A2, [Tuote.xlsx]Hinnat!$A$1:$A$5, 0))

Tämä jakautuu seuraavasti:

• MATCH-funktio etsii arvoa solusta A2 (tuotteen nimi) alueelta A1:A5 "Tuote"-työkirjan "Hinnat"-sivulla.

• INDEKSI-funktio palauttaa arvon alueelta B1:B5 "Tuote"-työkirjan "Hinnat"-välilehdellä MATCH-funktion antaman rivinumeron perusteella.

Tämä kuva näyttää kaavan toiminnassa:

2. XLOOKUP

XLOOKUP on uudempi toiminto, joka esiteltiin Excel 365: ssä . Se on samanlainen kuin VLOOKUP, mutta sisältää joitain lisäominaisuuksia.

Yksi XLOOKUPin suurimmista eduista on, että se voi etsiä arvoja hakusarakkeen vasemmalta puolelta.

Näin se toimii:

1. XHAKU-funktio palauttaa arvon määritetyltä alueelta hakuarvon perusteella.

2. Voit määrittää, haluatko tarkan vai likimääräisen vastaavuuden.

3. Voit myös määrittää, mitä palauttaa, jos vastaavuutta ei löydy.

XLOOKUP-käyttöä varten sinun on määritettävä hakuarvo, alue, josta haluat etsiä, ja sarake, johon haluat palauttaa tuloksen.

Tässä on esimerkkikaava:

=XHAKU(HakuArvo,Hakualue,Palautusalue,Ei löydy”,0)

Voit käyttää tätä esimerkkilaskentataulukoiden kanssa seuraavasti:

=XHAKU(A2, [Tuote.xlsx]Hinnat!$A$1:$A$5, [Tuote.xlsx]Hinnat!$B$1:$B$5)

Tämä jakautuu seuraavasti:

• A2: Arvo, jonka haluat etsiä (tuotteen nimi).

• [Product.xlsx]Hinnat!$A$1:$A$5: Alue, josta haluat etsiä hakuarvoa (tuotenimet "Tuote"-työkirjan Hinnat-välilehdellä).

• [Product.xlsx]Hinnat!$B$1:$B$5: Alue, joka sisältää palautusarvot (hinnat "Tuote"-työkirjan Hinnat-välilehdellä).

Tämä on kaava toiminnassa:

VLOOKUP:n käyttäminen useiden arkkien kesken

Jos sinun on suoritettava VHAKU kahden työkirjan välillä, saatat joutua käyttämään VHAKU-toimintoa useiden arkkien kesken.

Onneksi sen tekeminen on hyvin samanlainen kuin VHAKU-haku kahden työkirjan välillä.

Vaiheita on neljä.

Vaihe 1: Tunnista komponentit

Sen sijaan, että sisällytät taulukkotaulukon samaan tapaan kuin yhdelle taulukolle, sinun on sisällytettävä myös taulukon nimi.

Jos esimerkiksi haluat etsiä arvoa välillä A2:B6 Sales_reports.xlsx-työkirjan tammi-arkilta, kaava näyttää tältä:

=VHAKU(A2,[Sales_reports.xlsx]tam!$A$2:$B$6,2,EPÄTOSI)

Vaihe 2: Määritä tietosi

Varmista, että tietosi on järjestetty siten, että ne on helppo viitata useille taulukoille.

Sinun kannattaa harkita eri välilehtien luomista tietyille viitetiedoille.

Vaihe 3: Käytä VLOOKUP-toimintoa

Kun tietosi on määritetty, voit käyttää VLOOKUP-toimintoa tietojen etsimiseen ja noutamiseen muista taulukoista.

Varmista vain, että sisällytät taulukon nimen kaavaan yllä olevan kuvan mukaisesti.

(Valinnainen) Vaihe 4: #N/A-virheiden vianetsintä

Jos kohtaat #N/A-virheitä käyttäessäsi VLOOKUP-toimintoa useiden taulukoiden kesken, tarkista kaavasi varmistaaksesi, että olet viitannut oikein kaikkiin tarvittaviin taulukoihin ja alueisiin.

Voit myös tarkistaa, että suoritat tarkkaa vastaavuutta alueen haun sijaan.

Lopulliset ajatukset

Olet oppinut käyttämään VLOOKUPia tietojen hakemiseen ja noutamiseen kahdesta laskentataulukosta. Tämän monipuolisen toiminnon avulla on helppo etsiä arvoja yhdestä työkirjasta toisen vastaavien arvojen perusteella.

VLOOKUP-toiminnon teho ulottuu paljon yksittäisten laskentataulukoiden ulkopuolelle, ja se ulottuu eri työkirjoihin tietojen linkittämiseksi ja analysoimiseksi.

Tämän toiminnon hallitseminen on merkittävä askel kohti Excelin taitoa, sillä se avaa uusia mahdollisuuksia tietojen hallintaan ja tulkintaan.

Sisällyttämällä toimintoja, kuten INDIRECT, INDEX-MATCH tai XLOOKUP, voit edelleen parantaa tietohakujesi joustavuutta ja tehokkuutta.

Jatka harjoittelua ja Excelin laajojen ominaisuuksien tutkimista; tämä on vain jäävuoren huippu. Hyvää datan metsästystä!