Nämä esimerkit opettavat sinulle, kuinka Vlookupissa voi olla useita ehtoja, palauttaa tietty esiintymä tai kaikki ottelut, tehdä dynaamista Vlookupia useita arkkia ja enemmän.
Se on sarjan toinen osa, joka auttaa sinua hyödyntämään Excel VLOOKUPin tehoa. Esimerkit viittaavat siihen, että tiedät kuinka tämä toiminto toimii. Jos ei, on järkevää aloittaa VLOOKUPin käyttötavoista Excelissä.
Ennen kuin siirrymme eteenpäin, muistutan lyhyesti syntaksista:
Nyt kun kaikki ovat samalla sivulla, katsotaanpa tarkemmin edistyneitä VLOOKUP-kaavan esimerkkejä:
- Kuinka etsiä useita ehtoja Excelissä
- Vlookup ja return nth ottelu
- Vlookup useita arvoja
- Vlookup rivien ja sarakkeiden arvojen perusteella
- Sisäkkäinen Vlookup-kaava
- VLOOKUP ja INDIRECT tietojen noutamiseksi useista arkeista
Useiden ehtojen Vlookup
Excel VLOOKUP -toiminnosta on todella hyötyä tietokannasta haulle tietylle arvolle. Siitä puuttuu kuitenkin tärkeä ominaisuus – sen syntaksissa sallitaan vain yksi hakuarvo. Mutta entä jos haluat etsiä useita ehtoja? Voit valita muutaman erilaisen ratkaisun.
Kaava 1. VLOOKUP perustuu tw o arvot
Oletetaan, että sinulla on luettelo tilauksista ja haluat löytää määrän kahden kriteerin, asiakkaan nimen ja tuotteen perusteella. Hankalaa on, että jokainen asiakas tilasi useita tuotteita alla olevan taulukon mukaisesti:
Tavallinen VLOOKUP-kaava ei toimi tässä Tilanne, koska se palauttaa ensimmäisen löydetyn vastaavuuden määritetyn yksittäisen hakuarvon perusteella.
Tämän ratkaisemiseksi voit lisätä apusarakkeen ja liittää arvot kahdesta hakusarakkeesta (Asiakas ja Tuote). On tärkeää, että auttaja-sarake on taulukon taulukon vasemmanpuoleisin sarake, koska Excel VLOOKUP etsii aina hakuarvoa.
Lisää siis sarake taulukon vasemmalle puolelle ja kopioi alla olevan kaavan koko sarakkeessa. Tämä täyttää auttajasarakkeen arvot sarakkeista B ja C (välilyönti on ketjutettu väliin paremman luettavuuden vuoksi):
=B2&" "&C2
Käytä sitten tavallista VLOOKUP-kaavaa ja sijoita molemmat ehdot lookup_value-argumenttiin välilyönnillä erotettuna:
=VLOOKUP("Jeremy Sweets", A2:D11, 4, FALSE)
Tai kirjoita kriteerit erillisiin soluihin (tapauksessamme G1 ja G2) ja ketjuta ne solut:
=VLOOKUP(G1&" "&G2, A2:D11, 4, FALSE)
Kuten me Haluamme palauttaa arvon sarakkeesta D, joka on taulukon taulukossa neljäs, käytämme arvoa 4 sarakkeeseen col_index_num. Range_lookup-argumentiksi on asetettu FALSE, jotta Vlookup on tarkka haku. Alla oleva kuvakaappaus näyttää tuloksen:
Jos hakutaulukko on toisessa taulukossa, sisällytä taulukon nimi VLOOKUP-kaavaan. Esimerkki:
=VLOOKUP(G1&" "&G2, Orders!A2:D11, 4, FALSE)
Vaihtoehtoisesti voit luoda kaavion helpottamiseksi nimitetyn alueen hakutaulukolle (esimerkiksi Tilaukset). luettava:
=VLOOKUP(G1&" "&G2, Orders, 4, FALSE)
Lisätietoja on ohjeaiheessa Vlookupin luominen toisesta Excel-taulukosta.
Kaava 2. Excel VLOOKUP useilla ehdoilla
Teoriassa voit käyttää yllä olevaa lähestymistapaa Vlookupiin enemmän kuin kahta ehtoa. H On kuitenkin olemassa pari varoitusta. Ensinnäkin hakuarvo on rajoitettu 255 merkkiin, ja toiseksi laskentataulukon suunnittelu ei välttämättä salli apusarakkeen lisäämistä.
Onneksi Microsoft Excel tarjoaa usein useamman kuin yhden tavan tehdä sama asia. Vlookup useille ehdoille voit käyttää joko INDEX MATCH -yhdistelmää tai XLOOKUP-funktiota, joka on äskettäin otettu käyttöön Office 365: ssä.
Voit esimerkiksi etsiä 3 eri arvon perusteella (päivämäärä, asiakkaan nimi ja tuote), käytä yhtä seuraavista kaavoista:
=INDEX(D2:D11, MATCH(1, (G1=A2:A11) * (G2=B2:B11) * (G3=C2:C11), 0))
=XLOOKUP(1, (G1=A2:A11) * (G2=B2:B11) * (G3=C2:C11), D2:D11)
Missä:
Yksityiskohtainen selitys kaavoista:
- XLOOKUP useilla ehdoilla
- INDEX MATCH -kaava, jolla on useita ehtoja
Kuinka käyttää VLOOKUPia saadaksesi 2., 3. tai n. ottelu
Kuten jo tiedät, Excel VLOOKUP voi noutaa vain yhden vastaavan arvon, tarkemmin sanottuna se palauttaa ensimmäisen löydetyn vastaavuuden. Mutta entä jos hakutaulukossa on useita otteluita ja haluat saada toisen tai kolmannen esiintymän? Tehtävä kuulostaa melko monimutkaiselta, mutta ratkaisu on olemassa!
Kaava 1. Vlookup N: n esiintymä
Oletetaan, että sinulla on yhdessä sarakkeessa asiakkaiden nimiä, toisessa heidän ostamansa tuotteet ja sinä etsimme tietyn asiakkaan ostamaa toista tai kolmatta tuotetta.
Yksinkertaisin tapa on lisätä avustaja-sarake taulukon vasemmalle puolelle, kuten teimme ensimmäisessä esimerkissä. Mutta tällä kertaa täytämme sen asiakkaiden nimillä ja esiintymisnumeroilla, kuten ”John Doe1”, ”John Doe2” jne.
Saadaksesi tapahtuman, käytä COUNTIF-funktiota sekoitetun alueen viitteellä ( ensimmäinen viite on absoluuttinen ja toinen on suhteellinen kuten $ B $ 2: B2). Koska suhteellinen viite muuttuu solun sijainnin perusteella, johon kaava kopioidaan, rivillä 3 siitä tulee $ B $ 2: B3, rivillä 4 – $ B $ 2: B4 ja niin edelleen.
Yhdistettynä asiakkaan nimiin (B2), kaava on seuraava:
=B2&COUNTIF($B$2:B2, B2)
Yllä oleva kaava menee A2: een ja sitten kopioit sen alas niin moniin soluihin kuin tarvitset.
Syötä sen jälkeen kohdenimi ja esiintymänumero erillisiin soluihin (F1 ja F2) ja käytä alla olevaa kaavaa Vlookupin tiettyyn tapahtumaan:
=VLOOKUP(F1&F2, A2:C11, 3, FALSE)
Kaava 2. Vlookupin toinen esiintymä
Jos etsit hakuarvon 2. esiintymää, voit tehdä ilman auttaja-saraketta. Luo sen sijaan taulukotaulukko dynaamisesti käyttämällä INDIRECT-funktiota yhdessä MATCH: n kanssa.
=VLOOKUP(E1, INDIRECT("A"&(MATCH(E1, A2:A11, 0)+2)&":B11"), 2, FALSE)
Missä:
- E1 on hakuarvo
- A2: A11 on hakualue
- B11 on hakutaulukon viimeinen (oikeassa alakulmassa) solu
Huomaa, että yllä oleva kaava on kirjoitettu erityistapaukselle, jossa hakutaulukon datasolut alkavat rivillä 2. Jos taulukko on jonnekin taulukon keskellä, käytä tätä yleiskaavaa, jossa A1 on sarakkeen otsikon sisältävän hakutaulukon vasen yläkulma:
=VLOOKUP(E1, INDIRECT("A"&(MATCH(E1, A2:A11, 0)+1+ROW(A1))&":B11"), 2, FALSE)
Kuinka tämä kaava toimii
Tässä on kaavan avainosa, joka luo dynaamisen vlookup-alueen:
INDIRECT("A"&(MATCH(E1, A2:A11, 0)+2)&":B11")
Tarkalle haulle määritetty MATCH-funktio (0 viimeisessä argumentissa) vertaa kohteen nimeä (E1) nimiluetteloon (A2: A11) ja palauttaa ensimmäisen löydetyn vastaavuuden sijainnin, joka on 3 sisään o ur tapaus. Tätä numeroa käytetään vlookup-alueen alkurivikoordinaattina, joten lisäämme siihen 2 (+1 poistaaksesi ensimmäisen esiintymän ja +1 sulkeaksesi rivin 1 pois sarakeotsikoilla). Vaihtoehtoisesti voit käyttää 1 + RIVI (A1) laskeaksesi tarvittavan säädön automaattisesti otsikkorivin sijainnin perusteella (tapauksessamme A1).
Tuloksena saadaan seuraava tekstimerkkijono, mikä INDIRECT muuntaa alueen viitteeksi:
INDIRECT("A"&5&":B11") -> A5:B11
Tämä alue menee VLOOKUPin table_array-argumenttiin, joka pakottaa sen aloittamaan etsimisen riviltä 5, jättämällä hakuarvon ensimmäinen esiintymä pois:
VLOOKUP(E1, A5:B11, 2, FALSE)
Vlookupin palauttaminen ja useiden arvojen palauttaminen Excelissä
Excel VLOOKUP -toiminto on suunniteltu palauttamaan vain yksi ottelu. Onko olemassa tapa Vlookupiin useita instansseja? Kyllä, on olemassa, vaikkakaan ei helppo. Tämä edellyttää useiden toimintojen, kuten INDEX, SMALL ja ROW on matriisikaava, yhdistettyä käyttöä.
Esimerkiksi alla olevasta löytyy kaikki hakuarvon F2 esiintymät hakualueella B2: B16 ja return useita vastaavuuksia sarakkeesta C:
{=IFERROR(INDEX($C$2:$C$16, SMALL(IF($F$2=B2:B16, ROW(C2:C16)-1,""), ROW()-3)),"")}
Kaava voidaan kirjoittaa laskentataulukkoon kahdella tavalla:
- Kirjoita kaava ensimmäiseen soluun, paina Ctrl + Vaihto + Enter ja vedä se sitten alas muutamaan soluun.
- Valitse useita vierekkäisiä soluja yhdestä sarakkeesta (F1: F11 in alla oleva kuvakaappaus), kirjoita kaava ja suorita se painamalla Ctrl + Vaihto + Enter.
Kummassakin tapauksessa solujen lukumäärän, johon kaava syötetään, tulisi olla yhtä suuri tai suurempi kuin enimmäismäärä mahdollisia vastaavuuksia.
Yksityiskohtainen selitys kaavan logiikasta ja lisää esimerkkejä on artikkelissa Useiden arvojen hakeminen Excelissä .
Vlookup riveillä ja sarakkeilla (kaksisuuntainen haku)
Kaksisuuntainen haku (alias matriisihaku tai kaksiulotteinen haku) on hieno sana arvon etsimiseen tietyn rivin ja sarakkeen risteyksestä.Kaksiulotteista hakua Excelissä voi tehdä muutamalla eri tavalla, mutta koska tässä opetusohjelmassa keskitytään VLOOKUP-toimintoon, käytämme sitä luonnollisesti.
Tässä esimerkissä otamme alla olevan taulukon kuukausimyynnillä ja laadi VLOOKUP-kaava tietyn tuotteen myyntiluvun saamiseksi tiettynä kuukautena.
Kohteen nimillä A2: A9, kuukauden nimillä B1: F1 kohdekohde kaavassa I1 ja kohdekuukaudessa I2 kaava on seuraava:
=VLOOKUP(I1, A2:F9, MATCH(I2, A1:F1, 0), FALSE)
Kuinka tämä kaava toimii
Kaavan ydin on tavallinen VLOOKUP-funktio, joka etsii tarkkaa vastaavuutta I1: n hakuarvoon. Mutta koska emme tiedä missä sarakkeessa on tietyn kuukauden myynti, emme voi toimittaa sarakkeen numeroa suoraan col_index_num-argumenttiin. Sarakkeen löytämiseksi käytämme seuraavaa MATCH-funktiota:
MATCH(I2, A1:F1, 0)
Englanniksi käännettynä kaava sanoo: etsi I2-arvo A1: F1: ssä ja palauttaa sen suhteellinen sijainti taulukossa. Antamalla 0 kolmannelle argumentille, annat MATCH: lle löytää arvon, joka on täsmälleen sama kuin hakuarvo (se käyttää FALSE: ta VLOOKUP: n range_lookup-argumentissa).
Koska Mar on 4. sarakkeessa hakutaulukossa MATCH-funktio palauttaa arvon 4, joka siirtyy suoraan VLOOKUP: n col_index_num-argumenttiin:
VLOOKUP(I1, A2:F9, 4, FALSE)
Ole hyvä ja kiinnitä huomiota Vaikka kuukausien nimet alkavat sarakkeesta B, käytämme hakutaulukkoa A1: I1.Tämä tehdään, jotta MATCH: n palauttama numero vastaa sarakkeen sijaintia taulukon_kuvassa VLOOKUP.
Jos haluat oppia lisää tapoja tehdä matriisihaku Excelissä, katso INDEX MATCH MATCH ja muut 2-ulotteisen haun kaavat.
Kuinka tehdä useita Vlookup-tiedostoja Excelissä (sisäkkäinen Vlookup)
Joskus voi käydä niin, että päätaulukossasi ja hakutaulukossasi ei ole yhtä yhteistä saraketta, mikä estää sinua tekemästä Vlookupia kahden taulukon välillä. On kuitenkin olemassa toinen taulukko, joka ei sisällä etsimääsi tietoa, mutta jolla on yksi yhteinen sarake päätaulukon kanssa ja toinen yhteinen sarake hakutaulukon kanssa.
Alla olevassa kuvassa havainnollistetaan tilannetta:
Tavoitteena on kopioida hinnat päätaulukkoon tuotetunnusten perusteella. Ongelmana on, että hintoja sisältävässä taulukossa ei ole tuotetunnuksia, joten meidän on tehtävä kaksi Vlookupia yhdessä kaavassa.
Mukavuuden vuoksi luo ensin pari nimettyä aluetta :
- Hakutaulukon 1 nimi on Tuotteet (D3: E3).
- Hakutaulukon 2 nimi on Hinnat (G3: H3).
Taulukot voivat olla samoissa tai erilaisissa laskentataulukoissa.
Ja nyt suoritamme ns. kaksois-Vlookupin eli sisäkkäisen Vlookupin.
Tee ensin VLOOKUP kaava tuotteen nimen etsimiseen hakutaulukosta 1 (nimeltään Tuotteet) tuotetunnuksen (A3) perusteella:
=VLOOKUP(A3, Products, 2, FALSE)
Laita sitten yllä oleva kaava toisen VLOOKUP-funktion lookup_value-argumenttiin ja vedä hinnat hakutaulukosta 2 (nimeltään Hinnat) sisäkkäisen VLOOKUPin palauttaman tuotenimen perusteella:
=VLOOKUP(VLOOKUP(A3, Products, 2, FALSE), Prices, 2, FALSE)
Alla olevassa kuvakaappauksessa näkyy sisäkkäinen Vlookup-kaava toiminnassa:
Kuinka Vlookup useita taulukot dynaamisesti
Joskus tietoja voi olla samassa muodossa jaettuna useisiin laskentataulukoihin. Ja tavoitteena on saada tietoja tietyltä taulukolta tietyn solun avainarvon mukaan.
Tämä voi olla helpompi ymmärtää esimerkistä. Sanotaan, että sinulla on muutama alueellinen myyntiraportti samassa muodossa, ja haluat saada tietyn tuotteen myyntiluvut tietyillä alueilla:
Kuten edellisessä esimerkissä, aloitetaan muutaman nimen määritteleminen:
- Alue A2: CA-taulukon B5 on nimeltään CA_Sales.
- Alue A2 : FL-taulukon B5 on nimeltään FL_Sales.
- Alue A2: KS-taulukon B5 on nimeltään KS_Sales.
Kuten näette, kaikilla nimetyillä alueilla on yhteinen osa (myynti) ja ainutlaatuiset osat (CA, FL, KS). Muista nimetä alueesi samalla tavalla kuin se on välttämätöntä rakennettavan kaavan kannalta.
Kaava 1. EPÄSUORA VLOOKUP tietojen hakemiseksi dynaamisesti eri taulukoista
Jos tehtävänäsi on hakea tietoja useista arkeista, VLOOKUP INDIRECT -kaava on paras ratkaisu – kompakti ja helposti ymmärrettävä.
Tässä esimerkissä järjestämme yhteenvetotaulukon tällä tavalla:
- Syötä kiinnostavat tuotteet taulukoihin A2 ja A3. Nämä ovat hakuarvomme.
- Syötä nimettyjen alueiden yksilölliset osat B1, C1 ja D1.
Ja nyt yhdistämme ainutlaatuisen osan (B1) sisältävän solun yhteiseen osaan (”_Sales”) ja syötämme tuloksena olevan merkkijonon epäsuoraan:
INDIRECT(B$1&"_Sales")
INDIRECT-funktio muuttaa merkkijonon nimeksi, jota Excel ymmärtää, ja laitat sen VLOOKUP: n table_array-argumenttiin:
=VLOOKUP($A2, INDIRECT(B$1&"_Sales"), 2, FALSE)
Yllä oleva kaava menee muotoon B2 ja kopioit sen sitten alas ja oikealle.
Huomioi, hakuarvossa ($ A2) olemme lukinneet sarakekoordinaatin absoluuttisella soluviitteellä, jotta sarake pysyy kiinteänä, kun kaava kopioidaan oikealle. B $ 1 -viitteessä lukitsimme rivin, koska haluamme sarakkeen koordinaatti muuttaa ja toimittaa sopiva nimiosa INDIRECTille sen sarakkeen mukaan, johon kaava on kopioitu:
Jos päätaulukosi on järjestetty eri tavalla, haun arvot rivillä ja alueen nimien ainutlaatuiset osat sarakkeessa mn, sinun tulisi sitten lukita rivikoordinaatti hakuarvoon (B $ 1) ja sarakekoordinaatti nimen osiin ($ A2):
=VLOOKUP(B$1, INDIRECT($A2&"_Sales"), 2, FALSE)
Kaava 2. VLOOKUP ja sisäkkäiset IF: t etsimään useita taulukoita
Tilanteessa, kun olet juuri Kahden tai kolmen hakulomakkeen avulla voit käyttää melko yksinkertaista VLOOKUP-kaavaa sisäkkäisillä IF-funktioilla valitaksesi oikean taulukon tietyn solun avainarvon perusteella:
=VLOOKUP($A2, IF(B$1="CA", CA_Sales, IF(B$1="FL", FL_Sales, IF(B$1="KS", KS_Sales,""))), 2, FALSE)
Jossa $ A2 on hakuarvo (kohteen nimi) ja B $ 1 on avainarvo (tila):
Tässä tapauksessa sinun ei tarvitse välttämättä määrittää nimiä, ja voit käyttää ulkoisia viitteitä viitataksesi toiseen taulukkoon tai työkirjaan.
Lisää kaavaesimerkkejä on artikkelissa VLOOKUP useiden arkkien sisällä Excelissä .
Näin VLOOKUPia käytetään Excelissä. Kiitän sinua lukemisesta ja toivon tapaavasi sinut blogissamme ensi viikolla!
Harjoittelukirja ladattavaksi
Edistyneet VLOOKUP-kaavan esimerkit (.xlsx-tiedosto)
- Excelin VLOOKUP-opetus aloittelijoille
- Kuinka Vlookup useita vastaavuuksia Excelissä
- Kuinka käyttää VLOOKUP & SUM- tai SUMIF-toimintoja Excel
- INDEX & MATCH-funktiot Excelissä – parempi vaihtoehto VLOOKUPille
- 4 tapaa tehdä kirjainkoon mukaan Vlookup Excelissä
- Excel VLOOKUP ei toimi – N / A-, NAME- ja VALUE-virheet