Tuoreessa artikkelissa esittelemme Excel-toiminnon, jota kutsutaan VLOOKUP ja selitti, miten sitä voitaisiin käyttää hakemaan tietoja tietokannasta paikalliseen laskentataulukkoon. Tässä artikkelissa mainittiin, että VLOOKUP: lla oli kaksi käyttötarkoitusta, ja vain yksi niistä käsitteli tietokantoja. Tässä artikkelissa, toinen ja viimeinen VLOOKUP-sarjassa, tarkastelemme tätä muuta, vähemmän tunnettua käyttöä VLOOKUP-toiminnolle.
Jos et ole vielä tehnyt niin, lue ensimmäinen VLOOKUP-artikkeli - tässä artikkelissa oletetaan, että monet artikkelissa selitetyt käsitteet ovat jo tiedossa lukijalle.
Yhteistyössä tietokantojen kanssa VLOOKUP siirretään "yksilöivä tunniste", jonka avulla tunnistetaan tietojoukko, jonka haluamme löytää tietokannasta (esimerkiksi tuotekoodi tai asiakas-tunnus). Tämä yksilöllinen tunniste on pakko tietokantaan, muuten VLOOKUP palauttaa meille virheen. Tässä artikkelissa tarkastellaan tapaa käyttää VLOOKUPia, jossa tunnisteen ei tarvitse olla tietokannassa lainkaan. VLOOKUP on lähes yhtä hyvä kuin tarpeeksi hyvä lähestymistapa palauttamaan etsimämme tiedot. Tietyissä olosuhteissa tämä on tarkalleen mitä tarvitsemme.
Havainnollistamme tätä artikkelia reaalimaailmallisella esimerkillä - laskemalla myyntilukujen tuottamat palkkiot. Aloitamme hyvin yksinkertaisella skenaariolla ja vähitellen tekemme siitä entistä monimutkaisemman, kunnes ainoa järkevä ratkaisu ongelmaan on käyttää VLOOKUPia. Fiktiivisen yrityksen alkuperäinen skenaario toimii näin: Jos myyjä luo tietyn vuoden aikana yli 30 000 dollarin myyntivoiton, kyseisestä myynnistä ansaitut palkkiot ovat 30%. Muuten heidän palkkansa on vain 20%. Tähän mennessä tämä on melko yksinkertainen laskentataulukko:
Tämän laskentataulukon käyttämiseksi myyjä syöttää myyntiluvut soluun B1 ja kaava B2 solmuttaa oikean palkkion määrän, jonka heillä on oikeus vastaanottaa, jota käytetään solussa B3 laskemaan myyjän maksettavaksi tulevan kokonaispalkkion (mikä on yksinkertainen B1: n ja B2: n kertolasku).
Solu B2 on ainoa mielenkiintoinen osa tätä laskentataulukkoa - kaava päättää, mikä palkkion määrä käyttää: yksi alla kynnys $ 30,000, tai yksi edellä kynnys. Tämä kaava hyödyntää kutsuttua Excel-funktiota JOS. Niille lukijoille, jotka eivät tunne IF: tä, se toimii näin:
JOS(ehto, arvo jos on tosi, arvo jos se on väärä)
Missä kunto on ilmaus, joka arvioi joko totta tai väärä. Yllä olevassa esimerkissä kunto on ilmaus B1<>, joka voidaan lukea "Onko B1 pienempi kuin B5?" tai, toisin sanoen, "Onko kokonaismyynti pienempi kuin kynnysarvo". Jos vastaus tähän kysymykseen on "kyllä" (totta), käytämme sitä arvo, jos se on totta funktion parametri, nimittäin B6 tässä tapauksessa - provisiota, jos myynti oli kokonaisuudessaan alla kynnys. Jos vastaus kysymykseen on "ei" (false), käytämme arvo, jos se on väärä funktion parametri, nimittäin B7 tässä tapauksessa - provisiota, jos myynti oli kokonaisuudessaan edellä kynnys.
Kuten näette, käytämme 20 000 dollarin myyntivoittoa, joka antaa meille 20 prosentin korkoasteen solussa B2. Jos annamme arvon 40 000 dollaria, saamme eri provisiot:
Joten laskentataulukko toimii.
Tehdään monimutkaisempi. Otetaan käyttöön toinen kynnysarvo: jos myyjä ansaitsee yli 40 000 dollaria, niiden palkkioaste nousee 40 prosenttiin:
Se on helppo ymmärtää reaalimaailmassa, mutta solussa B2 kaava on yhä monimutkaisempi. Jos tarkastelet tarkasti kaavaa, näet, että alkuperäisen IF-toiminnon kolmas parametri ( arvo, jos se on väärä) on nyt täysin IF-toiminto itsenäisesti. Tätä kutsutaan a sisäkkäinen toiminto (funktio funktiossa). Se on täysin Excelissä kelvollinen (se toimii jopa!), Mutta sitä on vaikeampi lukea ja ymmärtää.
Emme aio mennä pähkinöihin ja pultteihin siitä, miten ja miksi tämä toimii, emmekä tutkii sisäkkäisten toimintojen vivahteita. Tämä on opetusohjelma VLOOKUP: lla, ei Excelissä yleensä.
Joka tapauksessa se pahenee! Entä jos 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 palkkioksi?
Nyt kaava B2, kun se on oikein, on tullut käytännössä lukukelvottomaksi. Kukaan ei saisi kirjoittaa kaavoja, joissa toiminnot ovat sisäkkäisiä neljään tasoon! Varmasti on oltava yksinkertaisempi tapa?
Siinä on varmasti. VLOOKUP pelastamiseen!
Tehdään uudelleen muotoiltu laskentataulukko hieman. Pidämme kaikki samat luvut, mutta järjestämme sen uudella tavalla, enemmän taulukkomuodossa tapa:
Ota hetki ja varmista itsellesi, että uusi Arviointitaulukko toimii täsmälleen samalla tavalla kuin yläraja-arvojen sarja.
Käsitteellisesti, mitä aiomme tehdä, käytämme VLOOKUP-palvelua etsiäkseen myyntipisteen myynnin kokonaismäärän (B1: stä) kurssitaulukossa ja palauttaaksemme meille vastaavan provisiokoron. Huomaa, että myyjä on voinut todella luoda myyntitapahtumia ei yksi viitearvoista (0, 30 000, 40 000, 50 000 tai 60 000 dollaria). He ovat voineet luoda 34 988 dollarin myyntiä. On tärkeää huomata, että 34 988 dollaria ei näkyvät kurssitaulukossa. Katsotaanpa, voiko VLOOKUP ratkaista ongelmamme joka tapauksessa ...
Valitsemme B2-solun (paikka, johon haluamme laittaa kaavan), ja lisää sitten VLOOKUP-funktio kaavat välilehti:
Function Arguments ilmestyy VLOOKUP-ruutu. Täytämme argumentit (parametrit) yksi kerrallaan alkaen hakuarvo, joka tässä tapauksessa on sellun kokonaismäärä solusta B1. Sijoitamme osoittimen sisään hakuarvo kenttä ja napsauta sitten kerran solussa B1:
Seuraavaksi meidän on täsmennettävä VLOOKUP-taulukon tarkastelemaan tätä dataa. Tässä esimerkissä tietenkin on nopeustaulukko. Sijoitamme osoittimen sisään Pöytäryhmä kenttä ja korosta sitten koko kurssitaulukko - lukuun ottamatta nimikkeitä:
Seuraavaksi meidän on määritettävä taulukon sarake, joka sisältää tiedot, joita kaava haluamme palauttaa meille. Tällöin haluamme taulukon toisessa sarakkeessa olevan palkkioasteen, joten kirjoitamme 2 osaksi Col_index_num ala:
Lopuksi annamme arvot hakualue ala.
Tärkeää: Tämän kentän käyttö eroaa kahdesta tavasta käyttää VLOOKUP: ia. Jos haluat käyttää VLOOKUPia tietokantaan, tämä viimeinen parametri, hakualue, on aina asetettava VÄÄRÄ, mutta tämän muun VLOOKUPin käytön kanssa meidän on jätettävä se tyhjäksi tai syötettävä arvo TOTTA. Kun käytät VLOOKUPia, on tärkeää, että teet oikean valinnan tälle viimeiselle parametrille.
Jotta voisimme olla selkeä, annamme arvon totta vuonna hakualue ala. Olisi myös hyvä jättää tyhjäksi, koska tämä on oletusarvo:
Olemme täyttäneet kaikki parametrit. Napsautamme nyt kunnossa -painiketta ja Excel rakentaa meille VLOOKUP-kaavan:
Jos kokeilemme muutamia eri myyntimääriä, voimme varmistaa, että kaava toimii.
johtopäätös
VLOOKUP-tietokannassa, jossa hakualue parametri on VÄÄRÄ, ensimmäisen parametrin (hakuarvo) on pakko olla mukana tietokannassa. Toisin sanoen, etsimme tarkkaa ottelua.
Mutta tässä muussa VLOOKUP-sovelluksessa emme välttämättä etsi tarkkaa ottelua. Tällöin "tarpeeksi lähellä on tarpeeksi hyvä". Mutta mitä tarkoitamme "riittävän lähellä"? Käytämme esimerkkiä: Kun etsit komennustekijää 34.988 dollarin myyntituloksella, VLOOKUP-kaava palauttaa meidät arvoon 30%, mikä on oikea vastaus. Miksi se valitsi 30%: n taulukon rivin? Mitä tässä tapauksessa tosiasiassa tarkoittaa "riittävän lähellä"? Let's tarkka:
Kun hakualue on asetettu TOTTA (tai jätetty pois), VLOOKUP näyttää sarakkeelta 1 ja vastaa suurin arvo, joka ei ole suurempi kuin hakuarvo parametri.
On myös tärkeää huomata, että tämä järjestelmä toimii, taulukko on lajitettava nousevaan järjestykseen sarakkeessa 1!
Jos haluat harjoitella VLOOKUPin avulla, tässä artikkelissa kuvattu esimerkkitiedosto on ladattavissa täältä.
