Bottom line: Opi kuinka provisiot lasketaan Excelissä porrastetulle peruskaaviolle ja rate-taulukolle käyttäen if -, VLOOKUP-tai XLOOKUP-funktioita.

taitotaso: välitunti

opetusvideo

provisioiden laskeminen Excelissä voi olla hyvin hankalaa. Tämä pätee erityisesti, jos olet yrittänyt käyttää useita IF lausumia laskemaan provisiot kunkin tason korko taulukossa.

tässä artikkelissa kerrotaan, miten vlookup-funktiota voi käyttää tämän prosessin helpottamiseksi. Salaisuus on asettaa Viimeinen argumentti vlookup to TRUE, löytää lähin ottelu.

checkout my article on How to Use VLOOKUP to Find the Closest Match for a detailed explanation on setting the last argument to TRUE.

päivitys: Postiin on päivitetty ohjeet provisioiden laskemiseksi uudella XLOOKUP-toiminnolla. Tässä juttu & video XLOOKUPISTA.

lataa tiedosto

lataa tiedosto seurattavaksi.

Komissiolaskelmat-IF Vs VLOOKUP Vs XLOOKUP.xlsx (31.5 KB)

provisioiden laskeminen vlookupilla

yksinkertaisella provisiosuunnitelmalla on tyypillisesti rate-taulukko, joka listaa voittoprosentin jokaisella myyntitasolla. Kun myyntiedustaja tekee enemmän myyntiä, hänen voittoprosenttinsa tyypillisesti kasvaa.

voittoprosentti voi olla kiinteä dollarimäärä, tuloprosentti, kiintiöprosentti jne. VLOOKUPIN tehtävänä on löytää REP: n Myyntisumma rate-taulukosta ja palauttaa vastaava voittoprosentti.

tämän esimerkin osalta toimeksiantosuunnitelmamme näyttää seuraavanlaiselta:

• Rep myy $0-$50,000, he ansaitsevat 5%
• Rep myy $51,000-$100,000, he ansaitsevat 7%
• Rep myy $100,001-$150,000, he ansaitsevat 10%
• Rep myy yli $150,001, he ansaitsevat 15%

edustaja tekee tietyn määrän myyntiä kuukauden aikana. Hän ansaitsee tietyn voittoprosentin riippuen saavutetun myynnin tasosta.

Voimme käyttää vlookupin kaavaa laskemaan tietyn myyntisumman voittoprosentin (hakuarvo). Jotta tämä toimisi, meidän täytyy asettaa vlookupin viimeinen argumentti todeksi.

kun viimeinen argumentti on tosi, vlookup löytää lähimmän täsmäyksen hakuarvolle, joka on pienempi tai yhtä suuri kuin hakusumma. Tämän avulla voimme periaatteessa löytää arvon kahden numeron väliltä (tasot).

tämän vuoksi vlookupin viimeinen argumentti on nimeltään range_lookup. Kun tämä argumentti on totta, VLOOKUP etsii tason vähimmäissarakkeessa olevien arvojen väliltä tarkkaa vastaavuutta tai hakuarvoa pienempää arvoa.

Kuinka asettaa Korkotaulukko

kun laitat tämän korkotaulukon Exceliin, sinun tarvitsee vain luetella hakualueen tason minimi. Jälleen VLOOKUP etsii ”lähimmän osuman”, joka on pienempi tai yhtä suuri kuin hakuarvo. Jos se löytää arvon, joka on suurempi kuin hakuarvo, se palauttaa edellisen rivin.

tässä esimerkissä palkkioiden korkotaulukon ensimmäisen rivin on oltava nolla. Tämä johtuu siitä, että myynti rep voisi mahdollisesti olla myynti $0 ja lookup arvo olisi nolla. Jos hakuarvo (myyntimäärä) olisi negatiivinen luku, vlookup palauttaisi virheen.

on tärkeää tietää tämä ja määrittää rate-taulukko kaikille mahdollisille hakuarvoille.

jos myyntimäärä on suurempi kuin hakualueen viimeinen rivi, vlookup palauttaa viimeisen rivin. Esimerkiksi, jos rep teki myyntiä $175,000 niin vlookup tuottaisi 15%.

laske palkkiot palauttaaksesi dollariarvon

voitto voidaan palauttaa myös dollariarvona prosenttiosuuden sijaan. Tällä setup voitto on kiinteä korko.

tämä tarkoittaa, että voitto on sama riippumatta siitä, mikä Myyntisumma on tason sisällä. Alla olevassa esimerkissä, jos voitto on $1,000 jos myyntimäärä on $55,000 tai $95,000.

the payout is not a sluding scale. Se on kiinteä hinta kullekin tasolle.

jos etsit liukuvaa asteikkolaskentaa, katso artikkelini provisioiden laskemisesta porrastetulla korkorakenteella käyttäen SUMPRODUCT-tuotetta.

komission laskelmat xlookup

voidaan käyttää myös uutta XLOOKUP-funktiota tähän laskelmaan. Se toimii hyvin samanlainen VLOOKUP kun löytää lähin ottelu.

edut xlookupilla

xlookupilla on kaksi pääasiallista eroa ja etua:

1. Xlookupilla määrittelemme hakuryhmän ja paluuryhmän erillisinä alueina. Tämä on B4:B7 ja D4: D7 yllä olevassa kuvassa. Tämä on etu VLOOKUPIIN nähden, koska voimme lisätä tai poistaa rivejä sarakkeiden B ja D välillä, ja kaava toimii edelleen.
   kun lisäämme / poistamme sarakkeita vlookupin taulukkorivistöön, joudumme manuaalisesti muuttamaan sarakkeen indeksilukua tai käyttämään kaavaa dynaamisesti laskettaessa sitä.
2. XLOOKUP ei vaadi tietojen lajittelua, kun käytetään tarkkaa matchia tai seuraavaa pienempää/suurempaa kohdetta match Modessa. XLOOKUP todella etsii seuraavan pienemmän / suuremman kohteen ja palauttaa tuloksen kyseiseltä riviltä / sarakkeesta.
   VLOOKUP vaatii tietojen lajittelua, kun viimeinen argumentti on tosi. Tyypillisesti haluat lajitella tiedot tämäntyyppisiä taulukoita anyways, mutta se on hyvä tietää, että voit vain laittaa uuden tason tasot alareunassa taulukon ja XLOOKUP toimii.

Xlookupin haitta

xlookupin suurin haitta on yhteensopivuus. Sekä sinun että kaikkien tiedostosi käyttäjien on oltava Microsoft / Office 365: n versiossa, jossa on XLOOKUP. Se ei ole taaksepäin yhteensopiva tai käytettävissä vanhemmissa versioissa Excel.

siksi VLOOKUP toimii tässä skenaariossa loistavasti ja on paljon helpompi kirjoittaa kuin sisäkkäinen IF-kaava.

Vältä sisäkkäisiä, jos kaavat

palkkioiden laskemisessa käytetään Ifin lausumia. Tällaisella korkotaulukolla joutuisi kirjoittamaan useita IF-lausekkeita. Sinun periaatteessa täytyy kirjoittaa yksi IF lausuma jokaiselle tasolle (rivi) taulukossa.

silloin on yhdistettävä kaikki IF-lauseet yhdeksi pitkäksi ja rumaksi kaavaksi. Näitä kutsutaan sisäkkäisiksi IF-lausekkeiksi.

pyrin aina mahdollisuuksien mukaan välttämään sisäkkäisiä if-laskelmia, koska niitä on vaikea lukea ja ymmärtää, ja ne voivat olla Excel-laskennan kannalta hitaampia. Jos työkirjassa on tuhansia sisäkkäisiä IF-kaavoja, laskenta-aika voi hidastua.

IF-kaava on paljon vaikeampi säilyttää. Se vaatii muutoksia, kun rivejä lisätään / poistetaan taulukosta. VLOOKUP ja XLOOKUP eivät vaadi tällaista huoltoa.

jos käytät IF: ää, varmista, että jokainen solu viittaa rate-taulukkoon absoluuttisena viitearvona (F4 näppäimistöllä). Muuten saat virheellisiä tuloksia kopioidessa kaava alas.

Vlookupin käyttäminen on paljon helpompaa ja puhtaampaa kuin sisäkkäisten if-kaavojen käyttö. Kuten näet tämän esimerkin, vlookup avulla voit käyttää yhtä kaavaa laskea provisio maksuprosentti tietyn myynnin määrä.

lisäresurssit

• miten VLOOKUPIA käytetään lähimmän vastineen löytämiseen – Viimeinen argumentti on totta
• Kuinka lasketaan kumulatiiviset porrastetut palkkiot SUMPRODUCTIN kanssa
• Vlookup selitettynä yksinkertaisesti Starbucksissa
• VLOOKUP & MATCH – dynaaminen Duo
• Uusi xlookup-toiminto Excel + Opetusvideolle
• IF-funktio selitti: miten if-Lausekekaava kirjoitetaan Excelissä

miten Vlookupia käytetään lähimpänä vastaavuutta?

lähimmällä täsmäystekniikalla varustettua VLOOKUPIA voidaan käyttää myös veroluokkien laskemiseen, hintasovitteluun jne. Mitä tällä tekniikalla lasketaan?