Před dlouhou dobou jsem pracoval pro viceprezidenta prodeje ve společnosti. Vždy jsem modeloval nějaký nový bonusový program nebo provizní plán. Na provizní plány za nejrůznějších podmínek jsem si docela zvykl. Ten, který je uveden v tomto tipu, je docela krotký.
Normálním přístupem je zahájení vytváření vnořeného vzorce IF. Vždy začínáte na horním nebo dolním konci rozsahu. „Pokud jsou tržby vyšší než 500 000 USD, pak sleva činí 20%; v opačném případě,… ." Třetím argumentem funkce IF je zcela nová funkce IF, která testuje druhou úroveň: „Pokud jsou tržby vyšší než 250 000 USD, pak sleva činí 15%; jinak….“
Tyto vzorce se prodlužují, protože existuje více úrovní. Nejtěžší částí takového vzorce je vzpomínka na to, kolik závěrečných závorek umístit na konec vzorce.
Pokud používáte Excel 2003, váš vzorec se již blíží limitu. U této verze nemůžete vnořit více než 7 funkcí IF. Byl to ošklivý den, kdy pravomoci, které se změnily, provizní plán a vy jste potřebovali způsob, jak přidat osmou funkci IF. Dnes můžete vnořit 64 funkcí IF. Nikdy byste neměli hnízdit tolik, ale je hezké vědět, že není problém vnořit 8 nebo 9.
Místo použití vnořené funkce IF zkuste použít funkci VLOOKUP. Když se čtvrtý argument VLOOKUP změní z False na True, funkce již nebude hledat přesnou shodu. Nejprve se VLOOKUP pokusí najít přesnou shodu. Pokud však není nalezena přesná shoda, Excel se usadí v řádku jen o něco méně, než co hledáte.
Zvažte následující tabulku. V buňce C13 bude Excel hledat v tabulce shodu za 28 355 USD. Když nemůže najít 28355, Excel vrátí slevu spojenou s hodnotou, která je jen menší - v tomto případě 1% sleva na úrovni 10 000 $.
Když převedete pravidla na tabulku v E13: F18, musíte začít od nejmenší úrovně a pokračovat na nejvyšší úroveň. Ačkoli to v pravidlech nebylo uvedeno, pokud někdo dosáhne tržeb pod 10 000 $, sleva bude 0%. Musíte to přidat jako první řádek v tabulce.
Pozor
Pokud používáte „True“ verzi VLOOKUP, musí být vaše tabulka seřazena vzestupně. Mnoho lidí věří, že je nutné třídit všechny vyhledávací tabulky. Tabulka však musí být tříděna pouze pro přibližnou shodu.
Co když váš manažer chce zcela samostatný vzorec a nechce vidět bonusovou tabulku napravo? Po sestavení vzorce můžete tabulku vložit přímo do vzorce. Přepněte vzorec do režimu úprav poklepáním na buňku nebo výběrem buňky a stisknutím klávesy F2. Kurzorem vyberte celý druhý argument: $ E $ 13: $ F $ 18.
Stiskněte klávesu F9. Excel vloží vyhledávací tabulku jako maticovou konstantu. V konstantě pole středník označuje nový řádek a čárka označuje nový sloupec. Viz Porozumění konstantám polí.
Stiskněte Enter. Zkopírujte vzorec dolů do ostatních buněk.
Nyní můžete tabulku smazat. Konečný vzorec je uveden níže.
Děkuji Mikeu Girvinovi, že mě naučil o shodných závorkách. Techniku VLOOKUP navrhli Danny Mac, Boriana Petrova, Andreas Thehos a @mvmcos.
