Že dolgo nazaj sem delal za podpredsednika prodaje v podjetju. Vedno sem oblikoval kak nov bonusni program ali načrt provizije. Precej sem se navadila naročiti načrte v najrazličnejših pogojih. Tista, prikazana v tem nasvetu, je precej pitoma.
Običajni pristop je začetek gradnje ugnezdene formule IF. Vedno začnete na zgornjem ali spodnjem koncu razpona. »Če prodaja presega 500.000 USD, je popust 20%; drugače pa … " Tretji argument funkcije IF je povsem nova funkcija IF, ki preizkuša drugo raven: "Če je prodaja več kot 250 tisoč dolarjev, je popust 15%, drugače pa …"
Te formule so vedno daljše, saj je ravni več. Najtežji del takšne formule je spomin, koliko zaključnih oklepajev naj bo na koncu formule.
Če uporabljate Excel 2003, je vaša formula že blizu meje. S to različico ne morete ugnezditi več kot 7 funkcij IF. Bil je grd dan, ko so pooblastila, ki bodo spremenila načrt provizije, in potrebovali način, kako dodati osmo funkcijo IF. Danes lahko ugnezdiš 64 funkcij IF. Nikoli ne smete gnezditi toliko, vendar je lepo vedeti, da pri gnezdenju 8 ali 9 ni težav.
Namesto da uporabite ugnezdeno funkcijo IF, poskusite uporabiti funkcijo VLOOKUP. Ko se četrti argument VLOOKUP spremeni iz False v True, funkcija ne išče več natančnega ujemanja. No, najprej VLOOKUP poskuša najti natančno ujemanje. Če pa natančnega ujemanja ni mogoče najti, se Excel poravna v vrstico le manj kot tisto, kar iščete.
Upoštevajte spodnjo tabelo. V celici C13 bo Excel v tabeli iskal ujemanje za 28.355 USD. Ko ne bo mogel najti 28355, bo Excel vrnil popust, povezan z vrednostjo, ki je le manjša - v tem primeru 1-odstotni popust za raven 10 tisoč dolarjev.
Ko pretvorite pravila v tabelo v E13: F18, morate začeti z najmanjše ravni in nadaljevati na najvišjo raven. Čeprav v pravilih ni bilo navedenega, bo popust 0%, če nekdo proda manj kot 10.000 USD. To morate dodati kot prvo vrstico v tabeli.
Previdno
Ko uporabljate "True" različico VLOOKUP, mora biti tabela razvrščena naraščajoče. Mnogi ljudje verjamejo, da je treba vse iskalne tabele razvrstiti. Toda mizo je treba razvrstiti samo za približno ujemanje.
Kaj pa, če vaš upravitelj želi popolnoma samostojno formulo in ne želi videti deske z bonus mizo? Po izdelavi formule lahko tabelo vdelate naravnost v formulo. Formulo postavite v način urejanja tako, da dvokliknete celico ali tako, da izberete celico in pritisnete F2. S kazalko izberite celoten drugi argument: $ E $ 13: $ F $ 18.
Pritisnite tipko F9. Excel vdela iskalno tabelo kot konstanto matrike. V konstanti polja podpičje označuje novo vrstico, vejica pa nov stolpec. Glejte Razumevanje konstant matrike.
Pritisnite Enter. Kopirajte formulo v druge celice.
Zdaj lahko tabelo izbrišete. Končna formula je prikazana spodaj.
Hvala Miku Girvinu, ker me je poučil o ujemajočih se oklepajih. Tehniko VLOOKUP so predlagali Danny Mac, Boriana Petrova, Andreas Thehos in @mvmcos.
