Máte vzorec programu Excel, ktorý hniezdi viac funkcií IF? Keď sa dostanete k bodu s príliš veľkým počtom vnorených príkazov IF, musíte zistiť, či by sa to celé zjednodušilo pomocou jednoduchej funkcie VLOOKUP. Videl som, že 1000-znakové vzorce sú zjednodušené na 30-znakový vzorec VLOOKUP. Ľahko sa udržuje, keď budete musieť neskôr pridať nové hodnoty.
Dávno som pracoval pre viceprezidenta predaja v práci. 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 dole je pekne 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, zľava je 15%; inak,… "
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.
Mini bonusový tip
Zhoda so zátvorkami
Excel cykluje rôznymi farbami pre každú novú úroveň zátvorky. Aj keď program Excel znova používa farby, použije čiernu farbu iba pre úvodnú zátvorku a pre zodpovedajúcu záverečnú zátvorku. Keď dokončujete nasledujúci vzorec, pokračujte v písaní zatváracích zátvoriek, kým nezadáte čiernu zátvorku.
Späť na vnorený tip na formulu
Ak používate program Excel 2003, váš vzorec sa už blíži k hranici limitu. V tom čase ste nemohli 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. Toto by ste nikdy nemali robiť, 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ť neobvyklé použitie funkcie 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, potom sa Excel usadí v riadku len o kúsok menej, ako 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% na úrovni 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 v prípade vykonania približnej zhody.
Č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.
Pomocou kurzora 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.
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.
Pozeraj video
- Pri stupňovitom províznom, bonusovom alebo zľavovom programe musíte často vnoriť svoje funkcie IF
- Limit pre program Excel 2003 bol 7 vnorených príkazov IF.
- Teraz môžete vnoriť 32, ale nemyslím si, že by ste mali niekedy vnoriť 32
- Kedy by ste niekedy použili verziu VLOOKUP s približnou zhodou? Toto je čas.
- Preložte zľavový program do vyhľadávacej tabuľky
- VOĽNÝ VÝHĽAD nenájde vo väčšine prípadov odpoveď.
- Ak na konci uvedieme True, povieme VLOOKUP, aby našiel hodnotu len o niečo menej.
- Toto je jediný prípad, kedy je potrebné triediť tabuľku VLOOKUP.
- Nechcete, aby bol stôl VLOOKUP bokom? Vložte to do vzorca.
- F2 na úpravu vzorca. Vyberte vyhľadávaciu tabuľku. Stlačte kláves F9. Zadajte.
Prepis videa
Naučte sa program Excel z podcastu, epizóda 2030 - Nahradené vnorené, AK VLOOKUP!
Budem podcastovať celú túto knihu. Kliknutím na písmeno „i“ v pravom hornom rohu sa dostanete do zoznamu skladieb!
Ahoj, vitaj späť na netcastu, som Bill Jelen. Dobre, je to naozaj bežné buď s províznym programom, alebo so zľavovým programom alebo s bonusovým programom, kde máme úrovne, rôzne úrovne, však? Ak ste nad 10 000 dolárov, získate 1% zľavu, 5 50000 dolárov 5% zľavu. Pri zostavovaní tohto vnoreného príkazu IF musíte byť opatrní, spustíte ho na hornom konci, ak hľadáte hodnotu väčšiu ako, alebo na dolnom konci, ak hľadáte hodnotu menšiu ako. Takže B10> 50000, 20%, inak ďalší IF, B10> 250000, 25%, a tak ďalej a tak ďalej. Toto je iba dlhý a komplikovaný vzorec a ak je vaša tabuľka väčšia, späť v programe Excel 2003, nemôžete vnoriť viac ako 7 príkazov IF, sme tu takmer blízko limitu. Teraz môžeš ísť na 32 rokov, nemyslím si, že by si mal niekedy ísť na 32 rokov, a aj keď kričíš „Hej počkaj,čo nová funkcia, ktorá práve vyšla v Exceli 2016, vydaní vo februári 2016, s funkciou IFS? “ Áno, určite je tu menej zátvoriek a 87 znakov namiesto 97 znakov, to je stále neporiadok, zbavme sa toho a urobme to so ZHĽADOM!
Dobre, tu sú kroky, tieto pravidlá prijmete a otočíte ich im ich hlavou. Prvá vec, ktorú musíme povedať, je, že ak ste mali tržby 0 dolárov, získate zľavu 0%, ak ste mali tržby 10 000 dolárov, získate zľavu 1%, ak máte tržby 50000 dolárov, získate zľavu 5% . 100 000 dolárov, 10% zľava, 2 50000 dolárov, 15% zľava a nakoniec čokoľvek> 500 000 dolárov získa 20% zľavu, dobre. Teraz, vždy, keď hovorím o VLOOKUP, hovorím, že každý VLOOKUP, ktorý ste kedy vytvorili, skončí FALSE a ľudia si povedia: „Počkajte chvíľu, aká je tam vlastne PRAVÁ verzia?“ Je to práve v tomto prípade, keď hľadáme rozsah, takže hľadáme túto hodnotu. Bežný VLOOKUP, samozrejme, 2, FALSE nenájde 550000, vráti hodnotu # N / A!Takže v tomto jednom veľmi zvláštnom prípade zmeníme túto NEPRAVDU na PRAVDU a bude to pre Excel znamenať: „Choďte hľadať 550000, ak ho nenájdete, dajte nám hodnotu, ktorá je len menšia.“ Takže nám dáva tých 20%, to je to, čo robí, dobre? A toto je jediný čas, kedy musí byť vaša tabuľka VLOOKUP triedená, inokedy s, FALSE, môže to byť akokoľvek chcete. A áno, môžete opustiť, PRAVDA, ale vždy som to vložil len preto, aby som si pripomenul, že toto je jeden z tých čudných, neuveriteľne nebezpečných VLOOKUPOV a nechcem tento vzorec kopírovať niekam inam. Dobre, takže skopírujeme a prilepíme špeciálne vzorce, dobre.dobre? A toto je jediný čas, kedy musí byť vaša tabuľka VLOOKUP triedená, inokedy s, FALSE, môže to byť akokoľvek chcete. A áno, môžete opustiť, PRAVDA, ale vždy som to vložil len preto, aby som si pripomenul, že toto je jeden z tých čudných, neuveriteľne nebezpečných VLOOKUPOV a nechcem tento vzorec kopírovať niekam inam. Dobre, takže skopírujeme a prilepíme špeciálne vzorce, dobre.dobre? A toto je jediný čas, kedy musí byť vaša tabuľka VLOOKUP triedená, inokedy s, FALSE, môže to byť akokoľvek chcete. A áno, mohli by ste opustiť, PRAVDA, ale vždy som to vložil len preto, aby som si pripomenul, že toto je jeden z tých čudných, neuveriteľne nebezpečných VLOOKUPOV a nechcem tento vzorec kopírovať niekam inam. Dobre, takže skopírujeme a prilepíme špeciálne vzorce, dobre.
Ďalšia sťažnosť: váš manažér nechce vidieť vyhľadávaciu tabuľku, chce ju „Len sa tejto vyhľadávacej tabuľky zbavte.“ Dobre, dokážeme to vložením vyhľadávacej tabuľky, pozrite sa na tento skvelý trik, ktorý som dostal od Mika Girvina z ExcelIsFun. Klávesom F2 prepnite vzorec do režimu úprav a potom veľmi opatrne vyberte iba rozsah vyhľadávacej tabuľky. Stlačte kláves F9, vezme túto tabuľku a vloží ju do nomenklatúry poľa. Potom iba stlačím kláves Enter. Skopírujte to, prilepte špeciálne vzorce a potom sa môžem zbaviť vyhľadávacej tabuľky, ktorá všetko pokračuje v riadení ďalej. Toto je obrovské trápenie, ak sa musíte vrátiť a upraviť to, musíte sa na to skutočne dívať s dostatkom jasnosti. Čiarka znamená, že ideme do ďalšieho stĺpca, bodkočiarka znamená, že ideme do ďalšieho riadku, dobre,teoreticky by ste sa však mohli vrátiť a zmeniť tento vzorec, ak sa zmení jedno z čísel reťazca.
Dobre, takže použitie VLOOKUPU namiesto vnoreného príkazu IF je tip č. 32 na našej ceste k 40 „40 najlepším tipom pre Excel všetkých čias“, môžete mať celú túto knihu. Kliknite na to „i“ v pravom hornom rohu, 10 dolárov za elektronickú knihu, 25 dolárov za tlačenú knihu, v poriadku. Dnes sa teda snažíme vyriešiť stupňovitý provízny bonus alebo zľavový program. Vnorené IF sú skutočne bežné, dávajte si pozor, v Exceli 2003 môžete mať iba 7, dnes ich môžete mať 32, ale nikdy by ste ich nemali mať 32. Takže kedy použiť približnú MATCH verziu VLOOKUP, je to ono. Vezmite tento zľavový program, obráťte ho naruby, urobte z neho vyhľadávaciu tabuľku začínajúcu od najmenšieho čísla a po najväčšom počte. VLOOKUP, samozrejme, nenájde odpoveď, ale zmenou VLOOKUP na, TRUE na konci, bude mu hovoriť, aby našiel hodnotu len menej,toto je jediný prípad, kedy musí byť tabuľka triedená. Ak túto tabuľku nechcete vidieť, môžete ju vložiť do vzorca pomocou triku Mike Girvin, klávesu F2 na úpravu vzorca, vyberte vyhľadávaciu tabuľku, stlačte kláves F9 a potom stlačte kláves Enter.
V poriadku, chcem sa vám poďakovať za návštevu, uvidíme sa nabudúce pri ďalšom netcaste od!
Stiahnuť súbor
Stiahnutie vzorového súboru nájdete tu: Podcast2030.xlsx
