VLOOKUP Excelissä, osa 2 VLOOKUPin käyttäminen ilman tietokantaa
Tuoreessa artikkelissa esiteltiin Excel-toiminto, jota kutsutaan VLOOKUP ja selitti, miten sitä voitaisiin käyttää tietojen hakemiseksi tietokannasta paikallisessa laskentataulukossa olevaan soluun. Mainitussa artikkelissa mainitsimme, että VLOOKUP: ssa oli kaksi käyttötarkoitusta, ja vain yksi niistä käsitteli tietokantoja. Tässä artikkelissa, toinen ja viimeinen VLOOKUP-sarjassa, tutkimme tätä muuta, vähemmän tunnettua VLOOKUP-toiminnon käyttöä.
Jos et ole jo tehnyt niin, lue ensimmäinen VLOOKUP-artikkeli - tässä artikkelissa oletetaan, että lukuisat tässä artikkelissa selitetyt käsitteet ovat jo lukijalle tiedossa..
Tietokantojen kanssa työskennellessä VLOOKUPilla on "yksilöllinen tunniste", jonka avulla voidaan tunnistaa tietokantaan haluamasi tietue (esim. Tuotekoodi tai asiakastunnus). Tämä yksilöllinen tunniste on pakko olemassa tietokannassa, muuten VLOOKUP palauttaa meille virheen. Tässä artikkelissa tarkastellaan tapaa käyttää VLOOKUPia, jossa tunnisteen ei tarvitse olla tietokannassa ollenkaan. Se on melkein yhtä kuin jos VLOOKUP voi hyväksyä "lähelle tarpeeksi hyvää" lähestymistapaa etsimäsi tiedot takaisin. Tietyissä olosuhteissa tämä on tarkalleen mitä tarvitsemme.
Esitämme tämän artikkelin todellisella esimerkillä - sellaisten palkkioiden laskemisesta, jotka syntyvät myyntilukujen joukosta. Aloitamme hyvin yksinkertaisesta skenaariosta ja sen jälkeen asteittain sen monimutkaisemmaksi, kunnes ongelman ainoa järkevä ratkaisu on käyttää VLOOKUPia. Fiktiivisen yrityksen ensimmäinen skenaario toimii näin: Jos myyjä luo yli 30 000 dollarin arvosta myyntiä tiettynä vuonna, palkkiot, jotka ne ansaitsevat kyseisestä myynnistä, ovat 30%. Muuten niiden palkkio on vain 20%. Tähän mennessä tämä on melko yksinkertainen laskentataulukko:
Tämän laskentataulukon käyttämiseksi myyjä syöttää myyntilukunsa soluun B1, ja solussa B2 oleva kaava laskee oikean palkkion, jonka heillä on oikeus saada, jota käytetään solussa B3 laskemaan kokonaismäärä, jonka myyjä on velkaa (mikä on yksinkertainen B1: n ja B2: n kertolasku).
Kenno B2 sisältää tämän laskentataulukon ainoan mielenkiintoisen osan - kaavan, jolla päätetään, mikä palkkio on käytettävä: yksi alla 30 000 dollarin kynnysarvo edellä kynnys. Tämä kaava käyttää Excel-toimintoa JOS. Niille lukijoille, jotka eivät tunne IF: tä, se toimii näin:
JOS(kunto, arvo, jos totta, arvo, jos väärä)
Missä kunto on lauseke, joka arvioi joko totta tai väärä. Edellä olevassa esimerkissä kunto on ilmaisu B1
Kuten huomaatte, 20 000 dollarin myyntimäärän käyttäminen antaa meille 20 prosentin palkkion solussa B2. Jos annamme arvoksi 40 000 dollaria, saamme erilaisen palkkion:
Laskentataulukko toimii siis.
Tehkäämme sen monimutkaisemmaksi. Otetaan käyttöön toinen kynnys: Jos myyjä ansaitsee yli 40 000 dollaria, niiden palkkio nousee 40 prosenttiin:
Helppo ymmärtää todellisessa maailmassa, mutta solussa B2 kaava on yhä monimutkaisempi. Jos tarkastelet tarkasti kaavaa, näet, että alkuperäisen IF-toiminnon kolmas parametri ( arvo, jos väärä) on nyt koko IF-toiminto itsessään. Tätä kutsutaan nimellä a sisäkkäinen toiminto (toiminto funktion sisällä). Se on täysin voimassa Excelissä (se toimii jopa!), Mutta on vaikeampi lukea ja ymmärtää.
Emme aio mennä pähkinöihin ja pultteihin siitä, miten ja miksi tämä toimii, emmekä tarkastele sisäkkäisten toimintojen vivahteita. Tämä on opetusohjelma VLOOKUPissa, ei Excelissä yleensä.
Joka tapauksessa, se pahenee! Entä kun päätämme, että jos he ansaitsevat yli 50 000 dollaria, heillä on oikeus 50 prosentin palkkioon ja jos he ansaitsevat yli 60 000 dollaria, heillä on oikeus 60 prosentin palkkioon?
Nyt solun B2 kaava on oikeassa, mutta siitä on tullut käytännössä lukukelvoton. Kukaan ei tarvitse kirjoittaa kaavoja, joissa toiminnot ovat sisäkkäin neljä tasoa syvällä! Varmasti on oltava yksinkertaisempi tapa?
Siellä on varmasti. VLOOKUP pelastukseen!
Suunnittele työarkki uudelleen hieman. Pidämme kaikki samat luvut, mutta järjestämme sen uudella tavalla taulukkomuodossa tapa:
Ota hetki ja tarkista itse, että uusi Hinta-taulukko toimii täsmälleen samalla tavalla kuin edellä mainittujen kynnysarvojen sarja.
Käsitteellisesti, mitä aiomme tehdä, on käyttää VLOOKUPia etsimään myyjän myyntimäärää (B1: stä) korkotaulukossa ja palauttamaan meille vastaavan palkkion. Huomaa, että myyjä on ehkä luonut myyntiä ei yksi viidestä arvosta taulukon arvosta ($ 0, $ 30,000, $ 40,000, $ 50,000 tai $ 60,000). He ovat saattaneet luoda 34,988 dollarin myynnin. On tärkeää huomata, että 34,988 dollaria tekee ei näkyvät korkotaulukossa. Katsotaanpa, voiko VLOOKUP ratkaista ongelmamme joka tapauksessa…
Valitsemme solun B2 (sijainti, johon haluamme laittaa kaavan), ja aseta sitten VLOOKUP-toiminto kaavat välilehti:
Toiminto Argumentit näyttöön tulee VLOOKUP-ruutu. Täytämme argumentit (parametrit) yksi kerrallaan alkaen hakuarvo, joka on tässä tapauksessa solun B1 kokonaismyynti. Asettamme kohdistimen kohtaan hakuarvo ja napsauta kerran solussa B1:
Seuraavaksi meidän on määriteltävä VLOOKUP: lle, mitä taulukkoa nämä tiedot haetaan. Tässä esimerkissä se on tietysti nopeus taulukko. Asettamme kohdistimen kohtaan Pöytäryhmä kenttään ja korosta sitten koko hintataso - lukuun ottamatta otsakkeita:
Seuraavaksi meidän on määritettävä, mikä taulukon sarake sisältää tiedot, joita haluamme, että kaava palaa meille. Tällöin haluamme palkkion, joka löytyy taulukon toisesta sarakkeesta, joten syötämme a 2 osaksi Col_index_num ala:
Lopuksi annamme arvon hakualue ala.
Tärkeää: Tämän kentän käyttö erottaa kaksi tapaa käyttää VLOOKUPia. Jos haluat käyttää VLOOKUP-tietokantaa, tämä lopullinen parametri, hakualue, täytyy aina olla VÄÄRÄ, mutta tämän toisen VLOOKUP-käytön avulla meidän on joko jätettävä se tyhjäksi tai annettava arvo TOTTA. Kun käytät VLOOKUPia, on tärkeää, että teet oikean valinnan tähän lopulliseen parametriin.
Jotta voisimme olla selkeitä, annamme arvon totta vuonna hakualue ala. Olisi myös hienoa jättää se tyhjäksi, koska tämä on oletusarvo:
Olemme suorittaneet kaikki parametrit. Napsautamme nyt kunnossa ja Excel rakentaa VLOOKUP-kaavan meille:
Jos kokeilemme muutamia eri myyntimääriä, voimme varmistaa, että kaava toimii.
johtopäätös
VLOOKUPin tietokannan versiossa, jossa hakualue parametri on VÄÄRÄ, ensimmäisessä parametrissa kulunut arvo (hakuarvo) on pakko olla tietokannassa. Toisin sanoen etsimme täsmällistä ottelua.
Mutta tässä muussa VLOOKUPin käytössä emme välttämättä etsi tarkkaa ottelua. Tässä tapauksessa ”riittävän lähellä on tarpeeksi hyvää”. Mutta mitä tarkoitamme ”riittävän lähellä”? Käyttäkäämme esimerkkiä: Kun etsit palkkioita 34,988 dollarin kokonaismyynnistä, VLOOKUP-kaava palauttaa meille 30%: n arvon, joka on oikea vastaus. Miksi se valitsi taulukon rivin, joka sisälsi 30%? Mitä itse asiassa tarkoittaa ”riittävän lähellä” tässä tapauksessa? Olkaamme tarkkoja:
Kun hakualue on asetettu TOTTA (tai jätetty pois), VLOOKUP näyttää sarakkeessa 1 ja vastaa korkein arvo, joka ei ole suurempi kuin hakuarvo parametri.
On myös tärkeää huomata, että tämä järjestelmä toimii, taulukko on lajiteltava nousevassa järjestyksessä sarakkeessa 1!
Jos haluat harjoitella VLOOKUPin avulla, tässä artikkelissa kuvattu esimerkkitiedosto voidaan ladata täältä.
