VLOOKUP -toiminnossa määritellään usein col_index_no staattinen. Koodaamme sen VLOOKUP -kaavassa, kuten VLOOKUP (id, data,3, 0). Ongelma syntyy, kun lisäämme tai poistamme sarakkeen tiedoista. Jos poistamme tai lisäämme sarakkeen ennen kolmatta saraketta tai sen jälkeen, kolmas sarake ei enää viittaa aiottuun sarakkeeseen. Tämä on yksi ongelma. Muu on, kun sinulla on useita sarakkeita etsittäväksi. Sinun on muokattava jokaisen kaavan sarakehakemistoa. Yksinkertainen kopiointi ei auta.
Mutta entä jos voit pyytää VLOOKUPia katsomaan otsikot ja palauttamaan vain vastaavat otsikot. Tätä kutsutaan kaksisuuntaiseksi VLOOKUPiksi.
Jos minulla on esimerkiksi VLOOKUP -kaavamerkit sarakkeen, sitten VLOOKUP pitäisi etsiä merkit sarakkeessa ja palauta arvo kyseisestä sarakkeesta. Tämä ratkaisee ongelmamme.
Hmm… Okei, niin miten teemme sen? Käyttämällä VLOOKUP -toiminnon Match -toimintoa.
Yleinen kaava
=VLOOKUP(haun_arvo, taulukon_matriisi, MATCH (haun_otsikko, taulukon otsikot, 0), 0)
Haun_arvo: hakuarvo taulukon_taulukon ensimmäisessä sarakkeessa.
Pöytäryhmä: alue, josta haluat tehdä haun. Esimerkiksi A2, D10.
Lookup_heading: otsikko, jonka haluat etsiä table_array -otsikoista.
Taulukon otsikot: Viittaus taulukon taulukon otsikoihin. Esim. jos taulukko on A2, D10 ja otsikot kunkin sarakkeen yläosassa, niin sen A1: D1.
Joten nyt tiedämme, mitä tarvitsemme dynaamista col_indexiä varten, poistetaan kaikki esimerkillä.
Dynaaminen VLOOKUP -esimerkki
Tässä esimerkissä meillä on tämä taulukko, joka sisältää tietoja alueen A4: E16 oppilaista.
Rullanumeron ja otsikon avulla haluan noutaa tiedot tästä taulukosta. Tässä tapauksessa haluan saada solussa H4 tiedot rullasta ei kirjoitettu soluun G4 ja otsikosta H3. Jos muutan otsikkoa, vastaavan alueen tiedot on haettava solusta H4.
Kirjoita tämä kaava soluun H4
= HAKU (G4, B4: E16, MATCH (H3, B3: E3,0), 0)
Koska taulukkomallimme on B4: E16, otsikkomallistamme tulee B3: E3.
Huomautus: Jos tietosi ovat hyvin jäsenneltyjä, sarakeotsikoissa on sama määrä sarakkeita ja se on taulukon ensimmäinen rivi.
Kuinka se toimii:
Joten pääosa arvioi sarakkeen hakemistonumeron automaattisesti. Tätä varten käytimme MATCH -toimintoa.
OSU (H3, B3: E3,0): Koska H3 sisältää "opiskelija", MATCH palauttaa 2. Jos H3: lla olisi "Arvosana", se olisi palauttanut 4 jne. VLOOKUP -kaavassa on vihdoin sen col_index_num.
= HAKU (G4, B4: E16,2,0)
Kuten tiedämme, MATCH-funktio palauttaa annetun yhdenulotteisen alueen arvon arvon. Siksi MATCH etsii kaikki arvot, jotka on kirjoitettu H3: een alueella B3: E3, ja palauttaa indeksinumeronsa.
Nyt kun muutat otsikkoa H3: ssa, jos se on otsikoissa, tämä kaava palauttaa arvon vastaavasta sarakkeesta. Muussa tapauksessa sinulla on #N/A -virhe.
VLOOKUP useissa sarakkeissa nopeasti
Yllä olevassa esimerkissä tarvitsimme vastauksen yhdestä sarakkeen arvosta. Mutta entä jos haluat saada useita sarakkeita kerralla. Jos kopioit yllä olevan kaavan, se palauttaa virheet. Meidän on tehtävä siihen pieniä muutoksia, jotta se olisi kannettava.
Absoluuttisten viitteiden käyttäminen VLOOKUPin kanssa
Kirjoita alla oleva kaava soluun H2.
= HAKU ($ G2, $ B $ 2: $ E $ 14, MATCH (H $ 1, $ B $ 1: $ E $ 1,0), 0)
Kopioi nyt H2 kaikista alueen H2: J6 soluista täyttääksesi sen tiedoilla.
Kuinka se toimii:
Tässä olen antanut ehdoton viittaus jokaisesta alueesta lukuun ottamatta VLOOKUP ($ G2) ja sarake haun_arvossa haulle MATCH (H $ 1).
$ G2: Tämä sallii rivin muuttaa hakuarvoa VLOOKUP -toiminnolle, kun kopioidaan alaspäin, mutta rajoittaa sarakkeen muuttumista, kun se kopioidaan oikealle. Tämä saa VLOOKUPin etsimään tunnusta G -sarakkeesta vain suhteellisen rivin kanssa.
Samoin, H $ 1 sallii sarakkeen muuttua, kun sitä kopioidaan vaakasuunnassa, ja rajoittaa riviä, kun kopioidaan alaspäin.
Nimettyjen alueiden käyttäminen
Yllä oleva esimerkki toimii hyvin, mutta tämän kaavan lukeminen ja kirjoittaminen on haastavaa. Ja tämä ei ole ollenkaan kannettava. Tätä voidaan yksinkertaistaa käyttämällä nimettyjä alueita.
Teemme ensin nimiä täällä. Tässä esimerkissä nimitin
$ B $ 2: $ E $ 14: kuten Data
$ B $ 1: $ E $ 1: otsikoina
1 $: Nimeä se otsikoksi. Tee sarakkeista suhteellisia. Voit tehdä tämän valitsemalla H1. Paina näppäinyhdistelmää CTRL+F3, napsauta uutta, viittaa kohtaan Poista $, joka näkyy H: n etuosassa.
$ G2: Nimeä se myös RollNo: ksi. Tämä aika tekee rivistä suhteellisen poistamalla $ -merkin 2: n edestä.
Kun sinulla on kaikki nimet taulukossa, kirjoita tämä kaava mihin tahansa Excel -tiedostoon. Se saa aina oikean vastauksen.
= VLOOKUP (RollNo, Data, MATCH (Heading, Headings, 0), 0)
Kuka tahansa voi lukea tämän ja ymmärtää sen.
Joten käyttämällä näitä menetelmiä voit tehdä col_index_num dynaamisesta. Kerro minulle, jos tämä oli hyödyllistä alla olevassa kommenttiosassa.
Kuinka käyttää tVLOOKUP -toiminto Excelissä
Suhteellinen ja ehdoton viite Excelissä
Nimetyt alueet Excelissä
Kuinka etsiä eri Excel -taulukosta
VLOOKUP Useita arvoja
Suosittuja artikkeleita
50 Excel -pikakuvake tuottavuuden lisäämiseksi : Nopeuta tehtävääsi. Nämä 50 pikanäppäintä tekevät työskentelystäsi entistä nopeampaa Excelissä.
Kuinka käyttää tVLOOKUP -toiminto Excelissä : Tämä on yksi eniten käytetyistä ja suosituimmista Excel -toiminnoista, jota käytetään arvon etsimiseen eri alueilta ja taulukoilta.
COUNTIF -toiminnon käyttäminen Excelissä : Laske arvot olosuhteilla tämän hämmästyttävän toiminnon avulla. Sinun ei tarvitse suodattaa tietoja laskeaksesi tiettyjä arvoja. Laskutoiminto on välttämätön kojelaudan valmistelemiseksi.
SUMIF -toiminnon käyttäminen Excelissä : Tämä on toinen kojelaudan olennainen toiminto. Tämä auttaa sinua laskemaan yhteen arvot tietyissä olosuhteissa.