Да ли имате Екцел формулу која угнежђује више ИФ функција? Када дођете до тачке са превише угнежђених ИФ израза, морате да видите да ли би цела ствар постала једноставнија помоћу једноставне функције ВЛООКУП. Видео сам да се формуле од 1000 знакова поједностављују до формуле ВЛООКУП од 30 знакова. Лако је одржавати када касније морате да додате нове вредности.
Давно сам радио за потпредседника продаје на послу. Увек сам моделирао неки нови бонус програм или план провизије. Прилично сам се навикао да наручујем планове под најразличитијим условима. Овај испод је прилично питом.
Нормалан приступ је започети изградњу угнежђене ИФ формуле. Увек започињете са горњег или доњег краја опсега. „Ако је продаја већа од 500.000 УСД, попуст је 20%; иначе… ." Трећи аргумент функције ИФ је потпуно нова функција функције ИФ која тестира други ниво: Ако је продаја већа од 250.000 УСД, попуст је 15%; иначе,… "
Ове формуле постају све дуже и дуже како постоји више нивоа. Најтежи део такве формуле је памћење колико затварајућих заграда треба ставити на крај формуле.
Мини бонус савет
Подударање заграда
Екцел кружи кроз разне боје за сваки нови ниво заграда. Док Екцел поново користи боје, црну користи само за отварање заграда и за одговарајуће заграде за затварање. Док завршавате доњу формулу, само наставите да куцате закључне заграде док не откуцате црну заграду.
Повратак на угнеждени савет формуле
Ако користите Екцел 2003, ваша формула се већ приближила ограничењу. Тада нисте могли угнездити више од 7 ИФ функција. Био је то ружан дан када су овлашћења која ће бити промењена у плану комисије и требао вам је начин да додате осму функцију ИФ. Данас можете угнездити 64 функције ИФ. Никада то не бисте требали радити, али лијепо је знати да нема проблема при гнежђењу 8 или 9.
Уместо да користите угнежђену функцију ИФ, покушајте да употребите необичну употребу функције ВЛООКУП. Када се четврти аргумент ВЛООКУП-а промени из Фалсе у Труе, функција више не тражи тачно подударање.
Па, прво ВЛООКУП покушава да пронађе тачно подударање. Али ако се тачно подударање не пронађе, Екцел ће се поставити у ред само мање од онога што тражите.
Размотрите доњу табелу. У ћелији Ц13, Екцел ће тражити подударање за 28.355 долара у табели. Када не може да пронађе 28355, Екцел ће вратити попуст повезан са вредношћу која је само мања. У овом случају, попуст од 1% за ниво од 10.000 УСД.
Када претворите правила у табелу у Е13: Ф18, потребно је да почнете од најмањег нивоа и пређете на највиши ниво. Иако то није наведено у правилима, ако неко прода мање од 10.000 УСД, попуст ће бити 0%. Ово морате да додате као први ред у табели.
Опрез
Када користите верзију ВЛООКУП-а „Труе“, табела мора да се сортира узлазно. Многи људи верују да све табеле за претрагу морају бити сортиране. Али табелу је потребно сортирати само у случају да се изврши приближно поклапање.
Шта ако ваш менаџер жели потпуно самосталну формулу и не жели да види табелу бонуса десно? Након изградње формуле, можете уградити табелу директно у формулу.
Ставите формулу у режим уређивања двокликом на ћелију или избором ћелије и притиском на Ф2.
Помоћу курсора изаберите цео други аргумент: $ Е $ 13: $ Ф $ 18.
Притисните тастер Ф9. Екцел ће уградити табелу претраживања као константу низа. У константи низа тачка и зарез означава нови ред, а зарез нову колону.
Притисните ентер. Копирајте формулу у остале ћелије.
Сада можете да избришете табелу. Коначна формула је приказана у наставку.
Хвала Мајку Гирвину што ме научио о одговарајућим заградама. Технику ВЛООКУП предложили су Данни Мац, Бориана Петрова, Андреас Тхехос и @мвмцос.
Погледајте видео
- Уз разноврсни програм провизије, бонуса или попуста, често морате угнездити своје ИФ функције
- Ограничење за Екцел 2003 било је 7 угнеждених ИФ израза.
- Сада можете угнездити 32, али мислим да никада не бисте требали гнездити 32
- Када бисте икада користили приближну верзију ВЛООКУП-а за подударање? Ово је време.
- Преведите програм попуста у потражну табелу
- ВЛООКУП у већини случајева неће пронаћи одговор.
- Стављајући, Труе на крају ће рећи ВЛООКУП-у да пронађе вредност само мање.
- Ово је једини пут да се ВЛООКУП табела мора сортирати.
- Не желите да ВЛООКУП табела скрене са стране? Уградите га у формулу.
- Ф2 за уређивање формуле. Изаберите табелу претраживања. Притисните Ф9. Ентер.
Видео транскрипт
Научите Екцел из подцаста, епизода 2030 - Замењени угнежђени АКО са ВЛООКУП!
Подставићу целу ову књигу, кликните на „и“ у горњем десном углу да бисте дошли до плејлисте!
Хеј, добродошао назад на нетцаст, ја сам Билл Јелен. У реду, ово је заиста уобичајено или за програм провизије, или програм попуста, или бонус програм, где имамо нивое, различите нивое, зар не? Ако сте виши од 10000 УСД, остварујете попуст од 1%, попуст од 50000 УСД од 5%. Морате бити опрезни када градите ову угнежђену изјаву ИФ, започињете је на врху ако тражите веће од или на доњем крају ако тражите мање од. Дакле Б10> 50000, 20%, иначе још један ИФ, Б10> 250000, 25%, и тако даље и тако даље. Ово је само дуга и компликована формула, и ако је ваша табела већа, у програму Екцел 2003, нисте могли да угнезди више од 7 ИФ изјава, овде смо скоро близу границе. Можете ићи на 32 сада, мислим да никада не бисте требали ићи на 32, па чак и ако вичете „Хеј, чекај,шта је са новом функцијом која је управо изашла у програму Екцел 2016, издању из фебруара 2016. године, са функцијом ИФС? “ Да, наравно, овде је мање заграда, и 87 знакова уместо 97 знакова, и даље је неред, решимо се овога и урадимо то са ВЛООКУП-ом!
У реду, ево корака, ви се придржавате тих правила и окрећете им главу. Прво што морамо да кажемо је да ако сте имали 0 долара продаје, добијате попуст од 0%, ако сте продали 10000 долара, добијате попуст од 1%, ако имате 50000 долара продаје, добијате попуст од 5% . $ 100000, попуст од 10%, $ 250000, попуст од 15%, и на крају, све> 500000 $ добија попуст од 20%, у реду. Сад много пута, сваки пут кад говорим о ВЛООКУП-у, кажем да ће се сваки ВЛООКУП који икада направите завршити ЛАЖНО, а људи ће рећи „Па сачекајте мало, за шта је уопште ПРАВА верзија?“ То је управо у овом случају када тражимо опсег, па тражимо ову вредност, редовни ВЛООКУП, наравно,, 2, ФАЛСЕ неће наћи 550000, вратиће # Н / А!Тако да ћемо у овом једном врло посебном случају променити ФАЛСЕ у ТРУЕ (ИСТИНА) и то ће рећи Екцел-у „Идите потражите 550000, ако га не можете пронаћи, дајте нам вредност која је само мања.“ Дакле, даје нам 20%, то и ради, у реду? И ово је једини пут да ваша ВЛООКУП табела мора да се сортира, а сва остала времена са, ФАЛСЕ, може бити како год желите. И да, можете искључити, ТАЧНО, али увек је ставим тамо само да се подсетим да је ово један од оних чудних невероватно опасних ВЛООКУП-ова и не желим да копирам ову формулу негде другде. У реду, па ћемо копирати и налепити посебне формуле, у реду.у реду? И ово је једини пут да ваша ВЛООКУП табела мора да се сортира, а сва остала времена са, ФАЛСЕ, може бити како год желите. И да, могли бисте искључити, ТАЧНО, али увек је ставим тамо само да се подсетим да је ово један од оних чудних невероватно опасних ВЛООКУП-ова и не желим да копирам ову формулу негде другде. У реду, па ћемо копирати и налепити посебне формуле, у реду.у реду? И ово је једини пут да ваша ВЛООКУП табела мора да се сортира, а сва остала времена са, ФАЛСЕ, може бити како год желите. И да, можете искључити, ТАЧНО, али увек је ставим тамо само да се подсетим да је ово један од оних чудних невероватно опасних ВЛООКУП-ова и не желим да копирам ову формулу негде другде. У реду, па ћемо копирати и налепити посебне формуле, у реду.
Следећа жалба: ваш менаџер не жели да види табелу за претрагу, већ је жели „Само се отарасите те табеле за претрагу“. У реду, то можемо учинити уграђивањем табеле за претрагу, погледајте овај, сјајан трик који сам добио од Микеа Гирвина у програму ЕкцелИсФун. Ф2 да ставите формулу у режим Уређивање, а затим врло пажљиво одаберите само опсег за табелу претраживања. Притисните Ф9, а она узима ову табелу и ставља је у номенклатуру низа, а затим само притиснем Ентер. Прекопирајте то, налепите Специјалне формуле, и тада ћу се слободно ослободити тражилице, која све наставља да ради низ редова. Ово је велика гњаважа ако морате да се вратите и уредите ово, морате заиста да зурите у то са пуно јасноће. Зарез значи да идемо у следећу колону, тачка и зарез значи да идемо у следећи ред, у реду,али у теорији бисте могли да се вратите тамо и промените ту формулу ако се промени један од бројева ланаца.
У реду, дакле, коришћење ВЛООКУП-а уместо угнежђене изјаве ИФ је савет број 32 на нашем путу до 40, „40 највећих Екцел савета свих времена“, можете да имате целу ову књигу. Кликните оно „и“ у горњем десном углу, 10 УСД за е-књигу, 25 УСД за штампану књигу, у реду. Дакле, данас покушавамо да решимо бонус нивоа провизије или програм попуста. Угњежђени ИФ-ови су заиста уобичајени, пазите, 7 је све што можете да имате у програму Екцел 2003, данас их можете имати 32, али никада не бисте требали имати 32. Дакле, када користити приближну МАТЦХ верзију ВЛООКУП-а, то је то. Узмите тај програм попуста, окрените га наопако, претворите у табелу за претрагу која почиње са најмањим бројем и иде до највећег броја. ВЛООКУП, наравно, неће пронаћи одговор, али променом ВЛООКУП-а у, ТРУЕ на крају, рећи ће му да пронађе вредност само мање,ово је једини пут да се табела мора сортирати. Ако не желите да видите ту табелу, можете је уградити у формулу користећи трик Мике Гирвин-а, Ф2 да бисте уредили формулу, изаберите табелу за претраживање, притисните Ф9, а затим Ентер.
У реду, хеј, желим да вам се захвалим што сте навратили, видимо се следећи пут за још једно емитовање од!
Скини докуменат
Преузмите датотеку узорка овде: Подцаст2030.клск
