Dávno som pracoval pre viceprezidenta predaja v spoločnosti. Stále som modeloval nejaký nový bonusový program alebo provízny plán. Na plány provízie za najrôznejších podmienok som si dosť zvykol. Ten, ktorý je uvedený v tomto tipe, je dosť krotký.
Normálnym prístupom je začať budovať vnorený vzorec IF. Vždy začínate buď na hornom konci, alebo na dolnom konci rozsahu. „Ak sú tržby vyššie ako 500 000 USD, potom je zľava 20%; inak,… ." Tretím argumentom funkcie IF je úplne nová funkcia IF, ktorá testuje na druhej úrovni: „Ak sú tržby vyššie ako 250 000 USD, potom je zľava 15%; v opačnom prípade….“
Tieto vzorce sa predlžujú, pretože ich je viac. Najťažšou časťou takéhoto vzorca je zapamätať si, koľko záverečných zátvorkách treba dať na koniec vzorca.
Ak používate program Excel 2003, váš vzorec sa už blíži k hranici limitu. S touto verziou nemôžete vnoriť viac ako 7 funkcií IF. Bol to škaredý deň, keď mocnosti, ktoré sa zmenili, plánovali provízie a vy ste potrebovali spôsob, ako pridať ôsmu funkciu IF. Dnes môžete vnoriť 64 funkcií IF. Nikdy by ste nemali hniezdiť toľko, ale je pekné vedieť, že nie je problém s vnorením 8 alebo 9.
Namiesto použitia vnorenej funkcie IF skúste použiť funkciu VLOOKUP. Keď sa štvrtý argument VLOOKUP zmení z False na True, funkcia už nebude hľadať presnú zhodu. Prvý VLOOKUP sa pokúša nájsť presnú zhodu. Ak sa však presná zhoda nenájde, program Excel sa usadí v riadku iba o toľko, čo hľadáte.
Zvážte nasledujúcu tabuľku. V bunke C13 bude Excel hľadať v tabuľke zhodu za 28 355 dolárov. Ak nenájde 28355, Excel vráti zľavu spojenú s hodnotou, ktorá je len menšia - v tomto prípade zľava 1% pre úroveň 10 000 USD.
Keď prevádzate pravidlá na tabuľku v E13: F18, musíte začať od najmenšej úrovne a postupovať po najvyššiu úroveň. Aj keď to nebolo v pravidlách uvedené, ak je niekto v tržbách pod 10 000 dolárov, zľava bude 0%. Musíte to pridať ako prvý riadok v tabuľke.
Pozor
Ak používate verziu „True“ programu VLOOKUP, musí sa vaša tabuľka zoradiť vzostupne. Mnoho ľudí verí, že všetky vyhľadávacie tabuľky musia byť zoradené. Tabuľku je však potrebné triediť iba pre približnú zhodu.
Čo ak váš manažér chce úplne samostatný vzorec a nechce vidieť tabuľku bonusov vpravo? Po vytvorení vzorca môžete tabuľku vložiť priamo do vzorca. Vložte vzorec do režimu úprav dvojitým kliknutím na bunku alebo výberom bunky a stlačením klávesu F2. Kurzorom vyberte celý druhý argument: $ E $ 13: $ F $ 18.
Stlačte kláves F9. Excel vloží vyhľadávaciu tabuľku ako konštantu poľa. V konštante poľa bodkočiarka označuje nový riadok a čiarka označuje nový stĺpec. Pozri Pochopenie konštánt poľa.
Stlačte Enter. Skopírujte vzorec dole do ďalších buniek.
Teraz môžete tabuľku odstrániť. Konečný vzorec je uvedený nižšie.
Ďakujem Mikeovi Girvinovi, že ma naučil o zhodných zátvorkách. Techniku VLOOKUP navrhli Danny Mac, Boriana Petrova, Andreas Thehos a @mvmcos.
