Преди много време работех за вицепрезидент по продажбите във фирма. Винаги моделирах някаква нова бонус програма или план за комисионна. Бях свикнал да възлагам планове при всякакви условия. Показаният в този съвет е доста опитомен.
Нормалният подход е да започнете да изграждате вложена IF формула. Винаги започвате или от горния, или от долния край на диапазона. „Ако продажбите са над $ 500 000, тогава отстъпката е 20%; в противен случай,… ." Третият аргумент на функцията IF е изцяло нова функция IF, която тества второто ниво: "Ако продажбите са над $ 250 000, тогава отстъпката е 15%; в противен случай, …"
Тези формули стават все по-дълги и по-дълги, тъй като има повече нива. Най-трудната част от такава формула е да си спомним колко затварящи скоби да поставим в края на формулата.
Ако използвате Excel 2003, вашата формула вече е близо до лимита. С тази версия не можете да влагате повече от 7 IF функции. Беше грозен ден, когато правомощията, които трябва да бъдат променени, са комисионни и вие се нуждаете от начин да добавите осма функция IF. Днес можете да вложите 64 IF функции. Никога не трябва да гнездите толкова много, но е хубаво да знаете, че няма проблем да влагате 8 или 9.
Вместо да използвате вложената функция IF, опитайте да използвате функцията VLOOKUP. Когато четвъртият аргумент на VLOOKUP се промени от False на True, функцията вече не търси точно съвпадение. Е, първо VLOOKUP се опитва да намери точно съвпадение. Но ако не бъде намерено точно съвпадение, Excel се настанява в реда малко по-малко от това, което търсите.
Помислете за таблицата по-долу. В клетка C13 Excel ще търси съвпадение за $ 28,355 в таблицата. Когато не може да намери 28355, Excel ще върне отстъпката, свързана със стойността, която е просто по-малка - в този случай 1% отстъпка за нивото от 10 000 щ.д.
Когато конвертирате правилата в таблицата в E13: F18, трябва да започнете от най-малкото ниво и да преминете към най-високото ниво. Въпреки че това не е посочено в правилата, ако някой продава продажби под $ 10 000, отстъпката ще бъде 0%. Трябва да добавите това като първия ред в таблицата.
Внимание
Когато използвате верната версия на VLOOKUP, вашата таблица трябва да бъде сортирана по възходящ ред. Много хора вярват, че всички справочни таблици трябва да бъдат сортирани. Но таблицата трябва да се сортира само за приблизително съвпадение.
Ами ако вашият мениджър иска напълно самостоятелна формула и не иска да вижда таблицата с бонуси вдясно? След изграждането на формулата можете да вградите таблицата направо във формулата. Поставете формулата в режим на редактиране, като щракнете двукратно върху клетката или като изберете клетката и натиснете F2. Използвайте курсора, за да изберете целия втори аргумент: $ E $ 13: $ F $ 18.
Натиснете клавиша F9. Excel вгражда справочната таблица като константа на масива. В константата на масива точка и запетая показва нов ред, а запетая - нова колона. Вижте Разбиране на константите на масива.
Натиснете Enter. Копирайте формулата в останалите клетки.
Вече можете да изтриете таблицата. Крайната формула е показана по-долу.
Благодаря на Майк Гирвин, че ме научи за съвпадащите скоби. Техниката VLOOKUP е предложена от Danny Mac, Boriana Petrova, Andreas Thehos и @mvmcos.
