Van olyan Excel-képlete, amely több IF-funkciót is beágyaz? Ha túl sok beágyazott IF utasítással jutunk a lényegre, meg kell látnunk, hogy az egész egyszerűbb lesz-e egy egyszerű VLOOKUP függvénnyel. Láttam, hogy az 1000 karakteres képletek leegyszerűsödnek egy 30 karakteres VLOOKUP képletre. Könnyen fenntartható, ha később új értékeket kell hozzáadnia.
Nagyon régen a munkahelyi értékesítési alelnöknél dolgoztam. Mindig valamilyen új bónuszprogramot vagy jutaléktervet modelleztem. Eléggé megszoktam a tervek megbízását mindenféle feltételekkel. Az alábbi elég szelíd.
A normális megközelítés az, hogy be kell építeni egy beágyazott IF képletet. Mindig a tartomány felső vagy alsó végén indul. „Ha az értékesítés meghaladja az 500 000 USD-t, akkor a kedvezmény 20%; másképp… ." Az IF függvény harmadik érve egy teljesen új IF függvény, amely a második szintet teszteli: Ha az eladások meghaladják a 250 000 USD-t, akkor a kedvezmény 15%; másképp,… "
Ezek a képletek egyre hosszabbak, mivel több szint van. Egy ilyen képlet legnehezebb része az, hogy emlékezzünk arra, hogy hány zárójelet kell tenni a képlet végére.
Mini bónusz tipp
A zárójelek egyezése
Az Excel különféle színekben mozog minden egyes zárójel szintjén. Míg az Excel újrafelhasználja a színeket, a feketét csak a nyitó zárójeleknél és az egyező záró zárójeleknél használja. Amint befejezi az alábbi képletet, csak írja be a zárójeleket, amíg be nem ír egy fekete zárójelet.
Vissza a beágyazott képlettipphez
Ha az Excel 2003 programot használja, a képlete már közelít a határértékhez. Akkor 7 IF-nél többet nem fészkelhetett be. Csúnya nap volt, amikor a megváltoztatott hatalmak megtervezik a jutalékot, és szükséged van egy módra a nyolcadik IF funkció hozzáadására. Ma 64 IF funkciót lehet beágyazni. Ezt soha nem szabad megtenni, de jó tudni, hogy nincs probléma a 8. vagy a 9. fészkelésével.
A beágyazott IF funkció használata helyett próbálkozzon a VLOOKUP funkció szokatlan használatával. Amikor a VLOOKUP negyedik argumentuma False-ról True-ra változik, a függvény már nem keresi a pontos egyezést.
Nos, először a VLOOKUP megpróbálja megtalálni a pontos egyezést. De ha nem található pontos egyezés, akkor az Excel csak kevesebbet áll be a sorba, mint amit keres.
Vegye figyelembe az alábbi táblázatot. A C13 cellában az Excel 28 355 dollárért keres egyezést a táblázatban. Ha nem találja a 28355 értéket, az Excel visszaadja az éppen alacsonyabb értékhez tartozó kedvezményt. Ebben az esetben az 1% -os kedvezmény a 10 000 USD szintre.
Amikor a szabályokat az E13: F18 táblázatba konvertálja, a legkisebb szintről kell indulnia, és a legmagasabb szintre kell lépnie. Bár a szabályokban nem szerepelt, ha valaki 10 000 USD alatt van, a kedvezmény 0% lesz. Ezt hozzá kell adnia a táblázat első soraként.
Vigyázat
A VLOOKUP „True” verziójának használatakor a táblázatot növekvő sorrendben kell rendezni. Sokan úgy vélik, hogy az összes keresőtáblát rendezni kell. De egy táblázatot csak hozzávetőleges mérkőzés esetén kell rendezni.
Mi van akkor, ha a menedzser egy teljesen önálló képletet akar, és nem akarja, hogy a bónusz táblázat jobbra legyen? A képlet felépítése után beágyazhatja a táblázatot közvetlenül a képletbe.
Helyezze a képletet Szerkesztés módba úgy, hogy duplán kattint a cellára, vagy kiválasztja a cellát, és lenyomja az F2 billentyűt.
A kurzor segítségével válassza ki a teljes második argumentumot: $ E $ 13: $ F $ 18.
Nyomja meg az F9 gombot. Az Excel a keresőtáblát tömb konstansként ágyazza be. A tömbállandóban a pontosvessző új sort, a vessző pedig egy új oszlopot jelöl.
Nyomd meg az Entert. Másolja le a képletet a többi cellába.
Most törölheti a táblázatot. A végső képletet az alábbiakban mutatjuk be.
Köszönet Mike Girvin-nek, hogy megtanított a megfelelő zárójelekre. A VLOOKUP technikát Danny Mac, Boriana Petrova, Andreas Thehos és @mvmcos javasolta.
Videót nézni
- Többszintű jutalék, bónusz vagy kedvezményes program esetén gyakran be kell fészkelnie az IF funkcióit
- Az Excel 2003 korlát 7 beágyazott IF utasítás volt.
- Most fészkelhet 32-et, de szerintem soha ne fészkeljen 32-et
- Mikor használja valaha a VLOOKUP hozzávetőleges egyezési verzióját? Itt az idő.
- Fordítsa le az akciós programot keresőtáblává
- A VLOOKUP a legtöbb esetben nem talál választ.
- A végén a True érték megmondja a VLOOKUP-nak, hogy találja meg az értéket.
- Ez az egyetlen alkalom, hogy a VLOOKUP táblát rendezni kell.
- Nem akarja, hogy a VLOOKUP asztal oldalra kerüljön? Beágyazza a képletbe.
- F2 a képlet szerkesztéséhez. Válassza ki a keresési táblázatot. Nyomja meg az F9 billentyűt. Belép.
Videó átirat
Tanulja meg az Excel-t a podcast-ból, 2030-as epizód - A beágyazott IF-t lecserélte a VLOOKUP-ra!
Podcastolom ezt az egész könyvet, kattintson a jobb felső sarokban található „i” gombra a lejátszási listához jutáshoz!
Hé, üdvözlöm újra a neten, Bill Jelen vagyok. Rendben, ez valóban gyakori vagy jutalékprogram, vagy diszkontprogram, vagy bónuszprogram esetében, ahol megvannak a szintjeink, a különböző szintjeink, igaz? Ha meghaladja a 10000 USD-t, 1% -os, 50000 USD 5% -os kedvezményt kap. Óvatosnak kell lennie, amikor elkészíti ezt a beágyazott IF utasítást, a felső végén kezdje el, ha nagyobbat keres, vagy az alsó végén, ha kevesebbet keres. Tehát B10> 50000, 20%, egyébként egy másik IF, B10> 250000, 25%, és így tovább, és így tovább. Ez csak egy hosszú és bonyolult képlet, és ha a táblázata nagyobb, még az Excel 2003-ban, akkor nem fészkelhetett be több mint 7 IF-utasítást, itt már majdnem a határérték közelében vagyunk. Mehetsz most 32-re, szerintem soha ne menj 32-re, és akkor is, ha azt visítod, hogy "Hé, várj,Mi van az új funkcióval, amely az Excel 2016-ban jelent meg, a 2016. februári kiadásban, az IFS funkcióval? " Igen, itt kevesebb a zárójel, és 97 karakter helyett 97 karakter, ez még mindig rendetlenség, szabaduljunk meg ettől és tegyük VLOOKUP-tal!
Rendben, itt vannak a lépések, megfogadod ezeket a szabályokat, és a fejükre fordítod őket. Az első dolog, amit el kell mondanunk: ha 0 dollár volt az értékesítésed, 0% -os kedvezményt kapsz, ha 10000 dolláros árbevételt ért el, akkor 1% -os kedvezményt kapsz, ha 50000 dolláros eladást értél el, akkor 5% -os kedvezményt kapsz . 100000 USD, 10% kedvezmény, 250000 USD, 15% kedvezmény, és végül minden, ami meghaladja az 500 000 USD értéket, a 20% kedvezményt kapja, rendben. Most sokszor, valahányszor a VLOOKUP-ról beszélek, azt mondom, hogy minden általad létrehozott VLOOKUP FALSE-val fog végződni, és az emberek azt mondják majd: "Várj egy kicsit, amúgy mi az IGAZ verzió?" Éppen erre az esetre keresünk tartományt, ezért keressük ezt az értéket, természetesen egy szokásos VLOOKUP, 2, a FALSE nem fogja megtalálni az 550000 értéket, visszaadja a # N / A értéket!Tehát ebben az egyetlen különleges esetben a FALSE-t IGAZRA változtatjuk, és ez azt mondja az Excel-nek: "Menjen, keresse meg az 550000-at, ha nem találja meg, adja meg nekünk azt az értéket, amely éppen kevesebb." Tehát megadja nekünk a 20% -ot, ezt teszi, rendben? És ez az egyetlen alkalom, amikor a VLOOKUP tábláját rendezni kell, minden más alkalommal a FALSE gombbal, ez tetszés szerinti lehet. És igen, hagyhatná a, IGAZ lehetőséget, de mindig csak azért tettem oda, hogy emlékeztessem magam, hogy ez az egyik furcsa, hihetetlenül veszélyes VLOOKUP, és ezt a képletet nem akarom máshova másolni. Rendben, tehát másoljuk és beillesztjük a Speciális képleteket, rendben.rendben? És ez az egyetlen alkalom, amikor a VLOOKUP tábláját rendezni kell, minden más alkalommal a FALSE gombbal, ez tetszés szerinti lehet. És igen, hagyhatná a, IGAZ lehetőséget, de mindig csak azért tettem oda, hogy emlékeztessem magam, hogy ez az egyik furcsa, hihetetlenül veszélyes VLOOKUP, és ezt a képletet nem akarom máshova másolni. Rendben, tehát másoljuk és beillesztjük a Speciális képleteket, rendben.rendben? És ez az egyetlen alkalom, amikor a VLOOKUP tábláját rendezni kell, minden más alkalommal a FALSE gombbal, ez tetszés szerinti lehet. És igen, hagyhatná a, IGAZ lehetőséget, de mindig csak azért tettem oda, hogy emlékeztessem magam, hogy ez az egyik furcsa, hihetetlenül veszélyes VLOOKUP, és ezt a képletet nem akarom máshova másolni. Rendben, tehát másoljuk és beillesztjük a Speciális képleteket, rendben.
Következő panasz: a menedzser nem akarja látni a keresési táblázatot, hanem azt, hogy „Csak szabaduljon meg attól a keresőtáblától”. Rendben, ezt megtehetjük a keresőtábla beágyazásával, ellenőrizze ezt, remek trükk, amelyet Mike Girvintől kaptam az ExcelIsFun-nál. F2 a képlet szerkesztési módba állításához, majd nagyon óvatosan válassza ki a keresési táblázat tartományát. Nyomja meg az F9 billentyűt, és elveszi ezt a táblázatot, és tömbök nómenklatúrájába helyezi, majd én csak így nyomom meg az Entert. Másolja ezt le, Illessze be a speciális képleteket, és akkor szabadon megszabadulhatok a keresőtáblától, ami csak tovább dolgozik a sorban. Ez óriási gond, ha vissza kell térnie és szerkesztenie kell, nagyon tisztán kell bámulnia. A vessző azt jelenti, hogy a következő oszlopra megyünk, a pontosvessző azt jelenti, hogy a következő sorra megyünk, rendben,de elméletileg visszatérhet oda, és megváltoztathatja ezt a képletet, ha az egyik láncszám megváltozik.
Rendben, tehát a beágyazott IF utasítás helyett a VLOOKUP használata a 32. tipp, a 40-es utunkhoz vezető úton, „Minden idők 40 legnagyobb Excel-tippje”, megkaphatja ezt az egész könyvet. Kattintson az „i” gombra a jobb felső sarokban, 10 dollár egy e-könyvért, 25 dollár a nyomtatott könyvért, rendben. Tehát ma megpróbálunk megoldani egy többszintű jutalékot vagy kedvezményes programot. A beágyazott IF-k valóban gyakoriak, vigyázzon, csak az 7 lehet az Excel 2003-ban, ma már 32, de soha nem kellene 32. Tehát mikor kell használni a VLOOKUP hozzávetőleges MATCH verzióját, ez az. Vegyük azt az árengedményprogramot, fordítsuk meg fejjel lefelé, és tegyük kereső táblává a legkisebb számmal kezdve és a legnagyobb számig. A VLOOKUP természetesen nem fogja megtalálni a választ, de ha a végén a VLOOKUP értéket IGAZra változtatja, akkor azt fogja mondani, hogy találja meg az értéket,ez az egyetlen alkalom, amikor a táblázatot rendezni kell. Ha nem akarja látni azt a táblázatot odakinn, beágyazhatja a képletbe a Mike Girvin F2 trükk használatával az képlet szerkesztéséhez, válassza ki a keresőtáblát, nyomja meg az F9 billentyűt, majd az Enter billentyűt.
Rendben, hé, szeretném megköszönni, hogy megálltál, legközelebb találkozunk egy újabb netcast-lal!
Fájl letöltése
Töltse le a minta fájlt innen: Podcast2030.xlsx
