Máte vzorec aplikace Excel, který hnízdí více funkcí IF? Když se dostanete k bodu s příliš mnoha vnořenými příkazy IF, musíte zjistit, zda by se to celé zjednodušilo pomocí jednoduché funkce VLOOKUP. Viděl jsem 1000 znaků vzorce být zjednodušeny na 30 znaků vzorce VLOOKUP. Je snadné jej udržovat, když budete muset později přidat nové hodnoty.
Kdysi dávno jsem pracoval pro viceprezidenta prodeje v práci. 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 níže 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 prodeje vyšší než 250 000 USD, pak je sleva 15%; v opačném případě,… "
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.
Mini bonusový tip
Odpovídající závorky
Excel cykluje různými barvami pro každou novou úroveň závorek. Zatímco Excel znovu používá barvy, používá černou pouze pro úvodní závorky a pro odpovídající uzavírací závorky. Když dokončujete níže uvedený vzorec, pokračujte v zadávání závorek, dokud nezadáte černou závorku.
Zpět na vnořený tip vzorce
Pokud používáte Excel 2003, váš vzorec se již blíží limitu. V té době jste nemohli 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 to neměli dělat, 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 neobvyklé použití funkce 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, pak se Excel usadí do řá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 v případě přibližné shody.
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.
Pomocí kurzoru vyberte celý druhý argument: $ E $ 13: $ F $ 18.
Stiskněte klávesu F9. Excel vloží vyhledávací tabulku jako konstantu pole. V konstantě pole středník označuje nový řádek a čárka označuje nový sloupec.
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.
Sledovat video
- U stupňovitého provizního, bonusového nebo slevového programu musíte často vnořit své IF funkce
- Limit aplikace Excel 2003 byl 7 vnořených příkazů IF.
- Nyní můžete hnízdit 32, ale nemyslím si, že byste měli hnízdit 32
- Kdy byste někdy použili verzi VLOOKUP s přibližnou shodou? To je čas.
- Přeložte slevový program do vyhledávací tabulky
- VLOOKUP ve většině případů nenajde odpověď.
- Putting, True at the end will tell VLOOKUP to find the value just less.
- Toto je jediný čas, kdy je třeba tabulku VLOOKUP třídit.
- Nechcete, aby tabulka VLOOKUP byla stranou? Vložte to do vzorce.
- F2 upravte vzorec. Vyberte vyhledávací tabulku. Stiskněte klávesu F9. Enter.
Přepis videa
Naučte se Excel z podcastu, epizoda 2030 - Nahrazeno Vnořené IF pomocí VLOOKUP!
Budu podcastovat celou tuto knihu, kliknutím na „i“ v pravém horním rohu přejdete na seznam skladeb!
Hej, vítej zpět na netcastu, jsem Bill Jelen. Dobře, to je opravdu běžné buď s provizním programem, nebo se slevovým programem, nebo s bonusovým programem, kde máme úrovně, různé úrovně, že? Pokud jste nad 10 000 $, získáte 1% slevu, 5 000 $ 5% slevu. Při vytváření tohoto vnořeného příkazu IF musíte být opatrní, spustíte jej na horním konci, pokud hledáte větší než, nebo na dolním konci, pokud hledáte menší než. Takže B10> 50000, 20%, jinak další IF, B10> 250000, 25% atd. A tak dále. Toto je jen dlouhý a komplikovaný vzorec a pokud je vaše tabulka větší, zpět v aplikaci Excel 2003, nemůžete vnořit více než 7 příkazů IF, jsme zde téměř blízko limitu. Teď můžeš jít na 32, nemyslím si, že bys měl jít na 32, a dokonce i když křičíš „Hej, počkej,co nová funkce, která právě vyšla v aplikaci Excel 2016, vydání z února 2016, s funkcí IFS? “ Ano, je zde méně závorek a 87 znaků místo 97 znaků, to je pořád nepořádek, pojďme se toho zbavit a udělat to s VLOOKUP!
Dobře, tady jsou kroky, ty přijmete tato pravidla a otočíte je na hlavu. První věc, kterou musíme říct, je, že pokud jste měli tržby 0 $, získáte slevu 0%, pokud jste měli tržby 10 000 $, získáte slevu 1%, pokud máte tržby 50000 $, získáte slevu 5% . 100 000 $, 10% sleva, 250000 $, 15% sleva a nakonec vše> 500 000 $ získá 20% slevu, dobře. Mnohokrát pokaždé, když mluvím o VLOOKUP, říkám, že každý VLOOKUP, který jste kdy vytvořili, skončí FALSE a lidé řeknou: „Počkejte chvilku, jaká je tam vlastně PRAVÁ verze?“ Je to právě pro tento případ, kdy hledáme rozsah, takže hledáme tuto hodnotu, běžný VLOOKUP, samozřejmě, 2, FALSE nenajde 550000, vrátí # N / A!V tomto velmi zvláštním případě tedy změníme FALSE na PRAVDU a to řekne aplikaci Excel „Jděte hledat 550000, pokud ji nemůžete najít, dejte nám hodnotu, která je o něco menší.“ Dává nám tedy těch 20%, to je to, co dělá, dobře? A toto je jediný čas, kdy musí být vaše tabulka VLOOKUP tříděna. Jindy s, FALSE, může to být jakkoli chcete. A ano, můžete to opustit, PRAVDIVÉ, ale vždy jsem to dal jen proto, abych si připomněl, že toto je jeden z těch podivných neuvěřitelně nebezpečných VLOOKUPŮ, a nechci tento vzorec kopírovat někam jinam. Dobře, takže zkopírujeme a vložíme speciální vzorce, dobře.v pořádku? A toto je jediný čas, kdy musí být vaše tabulka VLOOKUP tříděna. Jindy s, FALSE, může to být jakkoli chcete. A ano, můžete to opustit, PRAVDIVÉ, ale vždy jsem to dal jen proto, abych si připomněl, že toto je jeden z těch divných neuvěřitelně nebezpečných VLOOKUPů, a nechci tento vzorec kopírovat někam jinam. Dobře, takže zkopírujeme a vložíme speciální vzorce, dobře.v pořádku? A toto je jediný čas, kdy musí být vaše tabulka VLOOKUP tříděna. Jindy s, FALSE, může to být jakkoli chcete. A ano, můžete to opustit, PRAVDIVÉ, ale vždy jsem to dal jen proto, abych si připomněl, že toto je jeden z těch podivných neuvěřitelně nebezpečných VLOOKUPů, a nechci tento vzorec kopírovat někam jinam. Dobře, takže zkopírujeme a vložíme speciální vzorce, dobře.
Další stížnost: váš manažer nechce vidět vyhledávací tabulku, chce ji „Jen se zbavte této vyhledávací tabulky.“ Dobře, můžeme to udělat vložením vyhledávací tabulky, podívejte se na to, skvělý trik, který jsem dostal od Mikea Girvina na ExcelIsFun. Klávesou F2 přepněte vzorec do režimu úprav a poté velmi opatrně vyberte pouze rozsah vyhledávací tabulky. Stiskněte klávesu F9 a vezme tuto tabulku a vloží ji do nomenklatury pole a potom prostě stisknu Enter. Zkopírujte to dolů, vložte speciální vzorce a pak se můžu zbavit vyhledávací tabulky, která vše nadále pokračuje v řadě. To je obrovský problém, pokud se musíte vrátit a upravit to, musíte se na to opravdu dívat se spoustou jasnosti. Čárka znamená, že jdeme do dalšího sloupce, středník znamená, že jdeme do dalšího řádku, dobře,ale teoreticky byste se tam mohli vrátit a změnit tento vzorec, pokud se změní jedno z čísel řetězce.
Dobře, takže použití VLOOKUP namísto vnořeného příkazu IF je tip č. 32 na naší cestě k 40 „40 největším tipům aplikace Excel všech dob“, můžete mít celou tuto knihu. Klikněte na to „i“ v pravém horním rohu, 10 $ za e-knihu, 25 $ za tištěnou knihu, dobře. Dnes se tedy snažíme vyřešit stupňovitý provizní bonus nebo slevový program. Vnořené IF jsou opravdu běžné, pozor, 7 je vše, co můžete mít v aplikaci Excel 2003, dnes můžete mít 32, ale nikdy byste neměli mít 32. Takže kdy použít přibližnou MATCH verzi VLOOKUP, je to ono. Vezměte tento slevový program, otočte jej vzhůru nohama, vytvořte z něj vyhledávací tabulku začínající nejmenším číslem a přejděte na největší číslo. VLOOKUP, samozřejmě, nenajde odpověď, ale změnou VLOOKUP na, TRUE na konci, řekne mu, aby našel hodnotu jen méně,toto je jediný čas, kdy musí být tabulka tříděna. Pokud tu tabulku nechcete vidět, můžete ji do vzorce vložit pomocí triku Mikea Girvina, klávesou F2 upravit vzorec, vybrat vyhledávací tabulku, stisknout klávesu F9 a poté Enter.
Dobře, chci vám poděkovat, že jste se zastavili, uvidíme se příště na dalším netcastu od!
Stáhnout soubor
Stáhněte si ukázkový soubor zde: Podcast2030.xlsx
