Имате ли формула на Excel, която влага множество IF функции? Когато стигнете до точката с твърде много вложени оператори IF, трябва да видите дали цялото нещо ще стане по-просто с проста функция VLOOKUP. Виждал съм формули с 1000 знака да бъдат опростени до формула с 30 знака VLOOKUP. Лесно е да се поддържа, когато трябва да добавите нови стойности по-късно.
Преди много време работех за вицепрезидента по продажбите на работното място. Винаги моделирах някаква нова бонус програма или план за комисионна. Привикнах доста да възлагам планове при всякакви условия. Този по-долу е доста опитомен.
Нормалният подход е да започнете да изграждате вложена IF формула. Винаги започвате или от горния, или от долния край на диапазона. „Ако продажбите са над $ 500 000, тогава отстъпката е 20%; в противен случай… ." Третият аргумент на функцията IF е изцяло нова функция IF, която тества второто ниво: Ако продажбите са над $ 250 000, отстъпката е 15%; в противен случай,… "
Тези формули стават все по-дълги и по-дълги, тъй като има повече нива. Най-трудната част от такава формула е да си спомним колко затварящи скоби да поставим в края на формулата.
Мини бонус съвет
Съвпадение на скобите
Excel преминава през различни цветове за всяко ново ниво на скоби. Докато Excel използва повторно цветовете, той използва черно само за отварящата скоба и за съответстващата затваряща скоба. Докато приключвате формулата по-долу, просто продължете да пишете затварящи скоби, докато въведете черна скоба.
Назад към вложената формула Съвет
Ако използвате Excel 2003, вашата формула вече е близо до лимита. Тогава не можехте да вложите повече от 7 IF функции. Беше грозен ден, когато правомощията, които трябва да бъдат променени, са комисионни и вие се нуждаете от начин да добавите осма функция IF. Днес можете да вложите 64 IF функции. Никога не трябва да правите това, но е хубаво да знаете, че няма проблем да вложите 8 или 9.
Вместо да използвате вложената функция IF, опитайте да използвате необичайната употреба за функцията VLOOKUP. Когато четвъртият аргумент на VLOOKUP се промени от False на True, функцията вече не търси точно съвпадение.
Е, първо VLOOKUP се опитва да намери точно съвпадение. Но ако не бъде намерено точно съвпадение, тогава Excel се настанява в реда малко по-малко от това, което търсите.
Помислете за таблицата по-долу. В клетка C13 Excel ще търси съвпадение за $ 28,355 в таблицата. Когато не може да намери 28355, Excel ще върне отстъпката, свързана със стойността, която е малко по-малка. В този случай 1% отстъпка за нивото от $ 10K.
Когато конвертирате правилата в таблицата в E13: F18, трябва да започнете от най-малкото ниво и да преминете към най-високото ниво. Въпреки че това не е посочено в правилата, ако някой продава продажби под $ 10 000, отстъпката ще бъде 0%. Трябва да добавите това като първия ред в таблицата.
Внимание
Когато използвате верната версия на VLOOKUP, вашата таблица трябва да бъде сортирана по възходящ ред. Много хора вярват, че всички справочни таблици трябва да бъдат сортирани. Но таблицата трябва да се сортира само в случай на приблизително съвпадение.
Ами ако вашият мениджър иска напълно самостоятелна формула и не иска да вижда таблицата с бонуси вдясно? След изграждането на формулата можете да вградите таблицата направо във формулата.
Поставете формулата в режим на редактиране, като щракнете двукратно върху клетката или като изберете клетката и натиснете F2.
С помощта на курсора изберете целия втори аргумент: $ E $ 13: $ F $ 18.
Натиснете клавиша F9. Excel ще вгради таблицата за търсене като константа на масива. В константата на масива точка и запетая показва нов ред, а запетая - нова колона.
Натиснете Enter. Копирайте формулата в останалите клетки.
Вече можете да изтриете таблицата. Крайната формула е показана по-долу.
Благодаря на Майк Гирвин, че ме научи за съвпадащите скоби. Техниката VLOOKUP е предложена от Danny Mac, Boriana Petrova, Andreas Thehos и @mvmcos.
Гледам видео
- С диференцирана комисионна, бонус или програма за отстъпки често се налага да влагате вашите IF функции
- Ограничението на Excel 2003 беше 7 вложени IF изрази.
- Вече можете да гнездите 32, но не мисля, че някога трябва да гнездите 32
- Кога бихте използвали приблизителната версия за съвпадение на VLOOKUP? Това е времето.
- Преведете програмата за отстъпки в справочна таблица
- VLOOKUP няма да намери отговора в повечето случаи.
- Поставяйки, True в края ще каже на VLOOKUP да намери стойността малко по-малко.
- Това е единственият път, когато таблицата VLOOKUP трябва да бъде сортирана.
- Не искате таблицата VLOOKUP да е отстранена? Вградете го във формулата.
- F2 за редактиране на формулата. Изберете справочната таблица. Натиснете F9. Въведете.
Видео транскрипция
Научете Excel от подкаст, епизод 2030 - Заместено вложено АКО с VLOOKUP!
Ще подкастирам цялата тази книга, щракнете върху „i“ в горния десен ъгъл, за да стигнете до плейлиста!
Хей, добре дошъл в мрежата, аз съм Бил Джелен. Добре, това наистина е често срещано или с комисионна програма, или с програма за отстъпки, или с бонус програма, където имаме нива, различни нива, нали? Ако сте над $ 10000, получавате 1% отстъпка, $ 50000 5% отстъпка. Трябва да бъдете внимателни, когато изграждате този вложен IF израз, стартирате го в горния край, ако търсите по-голямо от, или в долния край, ако търсите по-малко от. Така B10> 50000, 20%, в противен случай друг IF, B10> 250000, 25%, и така нататък и т.н. Това е просто дълга и сложна формула и ако вашата таблица е по-голяма, в Excel 2003, не можете да вложите повече от 7 IF израза, ние сме почти близо до границата тук. Можеш да отидеш на 32 сега, не мисля, че някога трябва да отидеш на 32 и дори да крещиш „Хей, почакай,какво ще кажете за новата функция, която току-що излезе в Excel 2016, изданието от февруари 2016 г., с функцията IFS? " Да, разбира се, тук има по-малко скоби и 87 знака вместо 97 знака, все още е бъркотия, нека се отървем от това и го направим с VLOOKUP!
Добре, ето стъпките, вие приемате тези правила и ги обръщате на главата им. Първото нещо, което трябва да кажем, е, че ако сте имали $ 0 в продажби, получавате 0% отстъпка, ако сте имали $ 10000 в продажби, получавате 1% отстъпка, ако имате $ 50000 в продажби, получавате 5% отстъпка . $ 100000, 10% отстъпка, 250000 $, 15% отстъпка и накрая всичко> $ 500000 получава 20% отстъпка, добре. Сега много пъти, всеки път, когато говоря за VLOOKUP, казвам, че всеки VLOOKUP, който някога сте създали, ще завърши на FALSE и хората ще кажат „Ами, изчакайте секунда, за какво е ИСТИНСКАТА версия там? Точно за този случай, когато търсим диапазон, така че търсим тази стойност, редовен VLOOKUP, разбира се, 2, FALSE няма да намери 550000, ще върне # N / A!Така че в този един много специален случай ще променим това FALSE на TRUE и това ще каже на Excel „Отидете да търсите 550000, ако не можете да го намерите, дайте ни стойността, която е просто по-малка. Така ни дава 20%, това е, което прави, нали? И това е единственият път, когато вашата таблица VLOOKUP трябва да бъде сортирана, всички останали пъти с, FALSE, може да бъде както искате. И да, можете да оставите, ИСТИНСКО, но винаги го поставям там, само за да си напомня, че това е един от онези странни невероятно опасни VLOOKUP и не искам да копирам тази формула някъде другаде. Добре, така че ще копираме и поставим специални формули, добре.добре? И това е единственият път, когато вашата таблица VLOOKUP трябва да бъде сортирана, всички останали пъти с, FALSE, може да бъде както искате. И да, можете да оставите, TRUE изключено, но винаги го поставям там, само за да си напомня, че това е един от онези странни невероятно опасни VLOOKUP и не искам да копирам тази формула някъде другаде. Добре, така че ще копираме и поставим специални формули, добре.добре? И това е единственият път, когато вашата таблица VLOOKUP трябва да бъде сортирана, всички останали пъти с, FALSE, може да бъде както искате. И да, можете да оставите, TRUE изключено, но винаги го поставям там, само за да си напомня, че това е един от онези странни невероятно опасни VLOOKUP и не искам да копирам тази формула някъде другаде. Добре, така че ще копираме и поставим специални формули, добре.
Следваща жалба: Вашият мениджър не иска да вижда справочната таблица, той я иска „Просто се отървете от тази справочна таблица.“ Добре, можем да го направим, като вградим справочната таблица, проверете това, страхотен трик, който получих от Майк Гирвин от ExcelIsFun. F2, за да поставите формулата в режим на редактиране и след това много внимателно изберете само диапазона за таблицата за търсене. Натиснете F9 и тя взема тази таблица и я поставя в номенклатура на масиви и след това просто натискам Enter така. Копирайте това, Поставете специални формули и тогава съм свободен да се отърва от таблицата за търсене, която всичко продължава да работи надолу по реда. Това е огромна караница, ако трябва да се върнете и да редактирате това, трябва наистина да го гледате с много яснота. Запетаята означава, че отиваме към следващата колона, а точката с запетая означава, че отиваме към следващия ред, добре,но на теория можете да се върнете там и да промените тази формула, ако се промени едно от числата на веригата.
Добре, така че използването на VLOOKUP вместо вложено IF изявление е съвет № 32 по пътя ни към 40, „40 най-добри съвета на Excel за всички времена“, можете да имате цялата тази книга. Щракнете върху това „i“ в горния десен ъгъл, $ 10 за електронна книга, $ 25 за печатната книга, добре. Така че днес ние се опитваме да разрешим бонус на ниво комисионна или програма за отстъпка. Вложените IF са наистина често срещани, внимавайте, 7 е всичко, което можете да имате в Excel 2003, днес можете да имате 32, но никога не бива да имате 32. И така, кога да използвате приблизителната MATCH версия на VLOOKUP, това е всичко. Вземете тази програма за отстъпки, обърнете я с главата надолу, превърнете я в таблица за търсене, като започнете с най-малкото число и отидете до най-голямото число. VLOOKUP, разбира се, няма да намери отговора, но като промени VLOOKUP на TRUE в края, ще му каже да намери стойността малко по-малко,това е единственият път, когато трябва да се сортира таблицата. Ако не искате да виждате тази таблица там, можете да я вградите във формулата, като използвате трика на Майк Гирвин, F2, за да редактирате формулата, изберете таблицата за търсене, натиснете F9 и след това Enter.
Добре, хей, искам да ви благодаря, че отбихте, ще се видим следващия път за поредното излъчване от!
Свали файл
Изтеглете примерния файл тук: Podcast2030.xlsx
