VLOOKUP Excelissä, osa 2: VLOOKUPin käyttäminen ilman tietokantaa

Tuoreessa artikkelissa esittelemme Excel-toiminnon nimeltään VLOOKUP ja selitettiin, 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ä ainutkertainen tunniste on tietokannassa, 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. Joissakin tapauksissa 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 solun B2 kaava laskee oikean maksun, jonka heillä on oikeus saada, jota käytetään solussa B3 laskeamyyntipalkkiota( joka on yksinkertainen B1: n ja B2: n kertolasku).

Solu B2 on ainoa mielenkiintoinen osa tätä laskentataulukkoa - kaava päättää, mikä käyttöprosentti on käytettävä: yksi alle kynnys $ 30,000 tai yksi edellä kynnys. Tämä kaava hyödyntää Excel-toimintoa nimeltä IF .Niille lukijoille, jotka eivät tunne IF: tä, se toimii näin:

IF( ehto, arvo jos tosi, arvo jos väärä )

Jos -ehto on ilmaus, joka arvioi joko : n todellisen : n tai : n väärennetyn : n. Yllä olevassa esimerkissä -ehto on ilmaus B1 , jota voidaan lukea "Onko B1 pienempi kuin B5?" Tai, toisin sanoen, "Onko kokonaismyynti pienempi kuin kynnysarvo".Jos vastaus tähän kysymykseen on "kyllä"( true), käytämme -arvoa, jos kyseessä on -parametrin todellinen funktio, nimittäin B6 tässä tapauksessa - palkkion määrä, jos myynti yhteensä alle kynnyksen. Jos vastaus kysymykseen on "ei"( false), käytämme -arvoa, jos tässä tapauksessa käytetään vääriä -parametria, nimittäin B7 tässä tapauksessa - provisiokurssia, jos myynti kokonaismäärä oli edellä kynnysarvo.

Kuten näette, käyttämällä myyntiä yhteensä 20 000 dollaria antaa meille 20 prosentin provisiotason solussa B2.Jos annamme arvon 40 000 dollaria, saamme eri provisiota:

Joten laskentataulukko toimii.

Tehdään monimutkaisemmaksi. Otetaan käyttöön toinen kynnys: jos myyjä ansaitsee yli 40 000 dollaria, niiden palkkioaste nousee 40 prosenttiin:

Riittävän helppo ymmärtää reaalimaailmassa, mutta solussa B2 kaava on entistä monimutkaisempi. Jos tarkastelet tarkasti kaavaa, näet, että alkuperäisen IF-toiminnon kolmas parametri( -arvo, jos väärä ) on nyt täysin IF-toiminto itsessään. Tätä kutsutaan -sisäkkäiseksi funktioksi ( funktion funktio).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?

On varmasti olemassa. VLOOKUP pelastamiseen!

Työskentelemme uudelleen laskentataulukkoon. Pidämme kaikki samat luvut, mutta järjestämme sen uudella tavalla, lisää -taulukkomuotoista -tapaa:

Ota hetki ja tarkista itsellesi, että uusi -taulukko toimii täsmälleen sama 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 saattanut todellakin luoda myyntiä, joka on eikä yksi viitearvoista kurssitaulukossa( $ 0, $ 30,000, $ 40,000, $ 50,000 tai $ 60,000).He ovat voineet luoda 34 988 dollarin myyntiä.On tärkeää huomata, että 34 988 dollaria ei näytä : tä eikä : tä.Katsotaanpa, voiko VLOOKUP ratkaista ongelmamme joka tapauksessa. ..

Valitsemme solun B2( paikka, johon haluamme laittaa kaavan) ja lisätään sitten VLOOKUP-funktio -kaavojen -välilehdeltä:

-funktiota koskevat argumentit VLOOKUP-ruutu tulee näkyviin. Täytämme argumentit( parametrit) yksitellen alkaen : n Lookup_value : stä, joka on tässä tapauksessa myyntituotto solusta B1.Sijoitamme kohdistimen -hakuvaluutan kenttään ja klikkaamme sitten kerran soluun B1:

Seuraavaksi meidän on määritettävä VLOOKUP-taulukko, jonka taulukko etsiä nämä tiedot. Tässä esimerkissä tietenkin on nopeustaulukko. Asemoimme kohdistimen Table_array -kenttään ja korostamme koko kurssitaulukon - pois otsikoista :

Seuraavaksi meidän on määritettävä taulukon sarake, jonka sisältämät tiedot haluamme kaavan palauttamiseksi. Tässä tapauksessa haluamme taulukon toisessa sarakkeessa olevan palkkioasteen, joten annamme 2 : n Col_index_num -kenttään:

Lopuksi syötetään arvo Range_lookup -kenttään.

Tärkeää: Tämän kentän käyttö eroaa kahdesta tavasta käyttää VLOOKUP: ia. VLOOKUPin käyttämiseksi tietokantaan tämä lopullinen parametri Range_lookup on aina asetettava FALSE : ksi, mutta tämän muun VLOOKUPin käytön kanssa meidän on jätettävä se tyhjäksi tai syötettävä arvo TRUE .Kun käytät VLOOKUPia, on tärkeää, että teet oikean valinnan tälle viimeiselle parametrille.

Jotta selkeä, annamme : n todellisen : n arvon Range_lookup -kenttään. Olisi myös hyvä jättää tyhjäksi, koska tämä on oletusarvo:

Olemme saaneet kaikki parametrit valmiiksi. Nyt klikkaamme OK -painiketta ja Excel rakentaa meille VLOOKUP-kaavan:

Jos kokeilemme muutamia eri myyntimääriä, voimme varmistaa, että kaava toimii.

Päätelmä

VLOOKUP-tietokannan version, jossa Range_lookup -parametri on FALSE , ensimmäisessä parametrissa( Lookup_value ) on syötettävä tietokantaan .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 Range_lookup on TRUE ( tai jätetty pois), VLOOKUP näyttää sarakkeessa 1 ja vastaa : n korkein arvo, joka ei ole suurempi kuin Lookup_value -parametri.

On myös tärkeää huomata, että tämän järjestelmän toimimiseksi taulukko on lajitettava nousevaan järjestykseen sarakkeessa 1 !

Jos haluat harjoitella VLOOKUP: llä, tässä artikkelissa kuvattu esimerkkitiedosto on ladattavissa täältä.