segítségével alsó sor: Ismerje meg, hogyan számíthatja ki a jutalékokat az Excelben egy alapszintű tervhez és aránytáblához az IF, a VLOOKUP vagy az XLOOKUP függvények használatával.
képzettségi szint: középfokú
videó bemutató
a jutalékok kiszámítása az Excelben nagyon trükkös feladat lehet. Ez különösen igaz, ha több IF utasítást próbált használni a jutalékok kiszámításához az egyes szintekhez egy árfolyamtáblában.
ez a cikk elmagyarázza, hogyan kell használni a VLOOKUP funkciót, hogy ez a folyamat sokkal könnyebb legyen. A titok az, hogy a vlookup utolsó Argumentumát igazra állítja, hogy megtalálja a legközelebbi egyezést.
kérjük, nézze meg a VLOOKUP használatával kapcsolatos cikkemet, hogy megtalálja a legközelebbi egyezést az utolsó argumentum TRUE értékre állításának részletes magyarázatához.
frissítés: A bejegyzés frissült, hogy tartalmazza a jutalékok kiszámítására vonatkozó utasításokat az új XLOOKUP funkcióval. Itt van egy cikk & videó az XLOOKUP – on.
Fájl letöltése
töltse le a fájlt a követéshez.
jutalékok számítások – ha Vs VLOOKUP Vs XLOOKUP.xlsx (31.5 KB)
jutalékok kiszámítása a vlookup segítségével
egy egyszerű jutalékterv segítségével általában rendelkezik egy díjszabási táblázattal, amely felsorolja a kifizetési arányt az értékesítés minden szintjén. Mivel az értékesítési képviselő több értékesítést végez, a kifizetési aránya általában növekszik.
a kifizetési arány lehet egy lapos dollárösszeg, a bevétel százalékos aránya, a kvóta százalékos aránya stb. A VLOOKUP feladata, hogy megtalálja a rep értékesítési összegét a kamatláb táblázatban, és visszaadja a megfelelő kifizetési arányt.
ebben a példában jutaléktervünk a következőképpen néz ki:
- Rep értékesít $0-$50,000, keresnek 5%
- Rep értékesít $51,000-$100,000, keresnek 7%
- Rep értékesít $100,001-$150,000, keresnek 10%
- Rep értékesít több mint $150,001, keresnek 15%
a képviselő a hónap folyamán bizonyos mennyiségű értékesítést hajt végre. Bizonyos kifizetési arányt fog keresni az elért értékesítés szintjétől függően.
a VLOOKUP képletet használhatjuk egy adott értékesítési összeg kifizetési arányának kiszámításához (keresési érték). Ahhoz, hogy ez működjön, a vlookup utolsó Argumentumát TRUE értékre kell állítanunk.
ha az utolsó argumentum TRUE értékre van állítva, a vlookup megtalálja a keresési értékhez legközelebb eső egyezést, amely kisebb vagy egyenlő a keresési összeggel. Ez alapvetően lehetővé teszi számunkra, hogy értéket találjunk két szám (szint) tartománya között.
ezért a VLOOKUP utolsó Argumentumának neve range_lookup. Ha ez az argumentum igaz, a VLOOKUP a tier minimum oszlop értéktartományai között keresi a pontos egyezést vagy a keresési értéknél kisebb értéket.
a Sebességtábla beállítása
amikor ezt a sebességtáblát Excel-be helyezi, csak a keresési tartomány minimális szintjét kell felsorolnia. A VLOOKUP ismét egy “legközelebbi egyezést” keres, amely kisebb vagy egyenlő a keresési értékkel. Ha nagyobb értéket talál, mint a keresési érték, akkor visszaadja az előző sort.
ebben a példában egy jutalékráta tábla esetében a keresési tartomány első sorának nullának kell lennie. Ez azért van, mert az értékesítési képviselő potenciálisan $0 értékesítést érhet el, és a keresési érték nulla lenne. Ha a keresési érték (értékesítési összeg) negatív szám volt, akkor a vlookup hibát ad vissza.
fontos tudni ezt, és állítsa be az arány táblázatot az összes lehetséges keresési értékhez.
ha az értékesítési összeg nagyobb, mint a keresési tartomány utolsó sora, akkor a vlookup az utolsó sort adja vissza. Például, ha a képviselő 175 000 dolláros eladást ért el, akkor a vlookup 15% – ot térít vissza.
Számítsa ki a jutalékokat egy dollár érték visszatérítéséhez
a kifizetés Dollár értékként is visszaküldhető százalék helyett. Ezzel a beállítással a kifizetés átalánydíj lesz.
ez azt jelenti, hogy a kifizetés ugyanaz lesz, függetlenül attól, hogy az értékesítési összeg milyen szinten van. Az alábbi példában, ha a kifizetés 1000 dollár lesz, ha az értékesítési összeg 55 000 dollár vagy 95 000 dollár.
a kifizetés nem csúszó skálán. Ez egy átalány minden szint.
ha csúszó skála számítást keres, olvassa el a Bizottság kiszámításáról szóló cikkemet többszintű kamatszerkezettel a SUMPRODUCT használatával.
Jutalékszámítások az XLOOKUP
segítségével az új XLOOKUP függvényt is használhatjuk ehhez a számításhoz. Nagyon hasonlóan működik, mint a VLOOKUP, amikor megtalálja a legközelebbi mérkőzést.
az XLOOKUP előnyei
az XLOOKUP esetében két fő különbség és előny van:
- az XLOOKUP segítségével külön tartományként adjuk meg a keresési tömböt és a visszatérési tömböt. Ez a B4:B7 és a D4: D7 a fenti képen. Ez előnyt jelent a VLOOKUP-hoz képest, mert a B és D oszlopok között sorokat szúrhatunk be vagy törölhetünk, és a képlet továbbra is működni fog.
amikor oszlopokat adunk hozzá/törölünk a VLOOKUP táblázatos tömbjében, manuálisan kell megváltoztatnunk az oszlop indexszámát, vagy egy képletet kell használnunk annak dinamikus kiszámításához. - az XLOOKUP nem igényli az adatok rendezését, ha pontos egyezést vagy következő kisebb/nagyobb elemet használ az egyezési módhoz. Az XLOOKUP valójában a következő kisebb / nagyobb elemet keresi, és az adott sor/oszlop eredményét adja vissza.
a VLOOKUP megköveteli az adatok rendezését, ha az utolsó argumentum igaz. Általában mindenképpen rendezni szeretné az ilyen típusú táblák adatait, de jó tudni, hogy csak új sebességszinteket helyezhet a táblázat aljára, és az XLOOKUP működni fog.
az XLOOKUP hátránya
az XLOOKUP fő hátránya a kompatibilitás. Mind Önnek, mind a fájl összes felhasználójának a Microsoft/Office 365 xlookup-ot tartalmazó verzióján kell lennie. Ez nem visszafelé kompatibilis vagy elérhető az Excel régebbi verzióiban.
ezért a VLOOKUP nagyszerűen működik ebben a forgatókönyvben, és sokkal könnyebb írni, mint egy beágyazott if képlet.
kerülje a beágyazott képleteket
a jutalékok kiszámításának általános megközelítése az IF utasítások használata. A sebesség táblázat, mint ez akkor kell írni több IF nyilatkozatok. Alapvetően meg kell írni egy IF utasítást minden réteg (sor) a táblázatban.
ezután össze kell kapcsolnia az összes if állítást egy hosszú és csúnya képletbe. Ezeket beágyazott IF utasításoknak nevezzük.
mindig megpróbálom elkerülni a beágyazott IFs-eket, ha lehetséges, mert nehezen olvashatók és érthetők, és az Excel számára lassabban számolhatók. Ha több ezer beágyazott if-képlet van a munkafüzetben, a számítási idő lelassulhat.
az IF képletet sokkal nehezebb fenntartani. Módosítani kell, ha sorokat adnak hozzá/törölnek a táblázatból. A VLOOKUP és az XLOOKUP nem igényel ilyen típusú karbantartást.
ha az IF-et használja, győződjön meg róla, hogy minden cellahivatkozás a sebességtáblára abszolút hivatkozás (F4 A billentyűzeten). Ellenkező esetben helytelen eredményeket kap, amikor lemásolja a képletet.
a VLOOKUP használata sokkal egyszerűbb és tisztább, mint a beágyazott if képletek használata. Amint ebből a példából látható, a vlookup lehetővé teszi, hogy egy képlet segítségével kiszámítsa az adott értékesítési összeg jutalékfizetési arányát.
további források
- a VLOOKUP használata a legközelebbi egyezés megtalálásához – az utolsó argumentum értéke igaz
- Hogyan számoljuk ki a kumulatív többszintű jutalékokat a SUMPRODUCT segítségével
- a VLOOKUP egyszerűen magyarázható a Starbucks – nál
- VLOOKUP & MATCH-dinamikus duó
- az új xlookup funkció az Excel + Video Tutorial
- if funkció magyarázata: hogyan írhatunk IF utasítás képletet Excel-ben
hogyan használjuk a VLOOKUP-ot a legközelebbi mérkőzéssel?
a legközelebbi egyezési technikával rendelkező VLOOKUP használható adókulcs-számításokhoz, árillesztéshez stb. Mire használja ezt a technikát a számításhoz?