Nagyon régen dolgoztam egy vállalat értékesítési alelnökénél. Mindig valamilyen új bónuszprogramot vagy jutaléktervet modelleztem. Eléggé megszoktam a tervek megbízását mindenféle feltételekkel. Ami ebben a tippben látható, 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%; különben…"
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.
Ha az Excel 2003 programot használja, a képlete már közelít a határértékhez. Ezzel a verzióval 7 IF funkciónál többet nem lehet beágyazni. 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. Soha ne fészkelj ennyit, de jó tudni, hogy nincs probléma a 8. vagy 9. fészkelésével.
A beágyazott IF funkció használata helyett próbálkozzon a VLOOKUP funkció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, 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 ennél alacsonyabb értékhez társított kedvezményt - ebben az esetben az 1% -os kedvezményt a 10 000 USD szinthez.
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éshez 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 beágyazza a keresőtáblát tömbállandóvá. A tömbállandóban a pontosvessző új sort, a vessző pedig egy új oszlopot jelöl. Lásd: A tömbkonstansok megértése.
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.
