linia de fund: Aflați cum să calculați Comisioanele în Excel pentru un plan de bază și un tabel de rate utilizând funcțiile IF, VLOOKUP sau XLOOKUP.

nivelul de calificare: intermediar

tutorial Video

calcularea comisioanelor în Excel poate fi o sarcină foarte dificilă. Acest lucru este valabil mai ales dacă ați încercat să utilizați mai multe declarații IF pentru a calcula Comisioanele pentru fiecare nivel dintr-un tabel de rate.

acest articol va explica modul de utilizare a funcției VLOOKUP pentru a face acest proces mult mai ușor. Secretul este stabilirea ultimului argument în vlookup la TRUE, pentru a găsi cel mai apropiat meci.

vă rugăm să verificați articolul meu despre cum să utilizați VLOOKUP pentru a găsi cea mai apropiată potrivire pentru o explicație detaliată privind setarea ultimului argument la TRUE.

actualizare: Postarea a fost actualizată pentru a include instrucțiuni pentru calcularea comisioanelor cu noua funcție XLOOKUP. Aici este un articol & video pe XLOOKUP.

Descărcați fișierul

Descărcați fișierul de urmat.

comisioane calcule – dacă Vs VLOOKUP Vs XLOOKUP.xlsx (31.5 KB)

calcularea comisioanelor cu VLOOKUP

cu un plan de comision simplu aveți de obicei un tabel de rate care listează rata de plată la fiecare nivel de vânzări. Pe măsură ce reprezentantul de vânzări face mai multe vânzări, rata de plată a acestuia va crește de obicei.

rata de plată ar putea fi o sumă forfetară în dolari, procent din venituri, procent din cotă etc. Sarcina VLOOKUP este de a găsi suma vânzărilor Reprezentantului în tabelul de rate și de a returna rata de plată corespunzătoare.

pentru acest exemplu, planul nostru de comisioane arată astfel:

• Rep vinde $0-$50.000, câștigă 5%
• Rep vinde $51.000-$100.000, Câștigă 7%
• Rep vinde $100.001-$150.000, câștigă 10%
• Rep vinde peste $150,001, ei câștigă 15%

reprezentantul va face o anumită sumă de vânzări în cursul lunii. El / ea va câștiga o anumită rată de plată în funcție de nivelul vânzărilor realizate.

putem folosi o formulă VLOOKUP pentru a calcula rata de plată pentru o anumită sumă de vânzări (valoare de căutare). Pentru ca acest lucru să funcționeze, trebuie să setăm ultimul argument din vlookup la TRUE.

cu ultimul argument setat la TRUE, vlookup va găsi cea mai apropiată potrivire cu valoarea de căutare care este mai mică sau egală cu suma de căutare. Acest lucru ne permite practic să găsim o valoare între intervale de două numere (niveluri).

acesta este motivul pentru care ultimul argument din VLOOKUP se numește range_lookup. Când acest argument este adevărat, VLOOKUP caută între intervale de Valori din coloana nivel minim pentru a găsi o potrivire exactă sau valoarea mai mică decât valoarea de căutare.

cum se configurează tabelul de Rate

când puneți acest tabel de rate în Excel, trebuie doar să listați nivelul minim pentru intervalul de căutare. Din nou VLOOKUP va căuta un „cel mai apropiat meci”, care este mai mică sau egală cu valoarea de căutare. Dacă găsește o valoare mai mare decât valoarea de căutare, atunci va returna rândul anterior.

în acest exemplu pentru un tabel al ratei comisioanelor, primul rând din intervalul de căutare trebuie să fie zero. Acest lucru se datorează faptului că reprezentantul de vânzări ar putea avea vânzări de 0 USD, iar valoarea de căutare ar fi zero. Dacă valoarea de căutare (suma vânzărilor) ar fi un număr negativ, atunci vlookup ar returna o eroare.

este important să știți acest lucru și să configurați tabelul de rate pentru toate valorile posibile de căutare.

dacă valoarea vânzărilor este mai mare decât ultimul rând din intervalul de căutare, atunci vlookup va returna ultimul rând. De exemplu, dacă reprezentantul a făcut vânzări de 175.000 de dolari, atunci vlookup ar returna 15%.

calculați Comisioanele pentru a returna o valoare în dolari

plata poate fi returnată și ca valoare în dolari, în loc de procent. Cu această configurare plata va fi o rată forfetară.

aceasta înseamnă că plata va fi aceeași, indiferent de valoarea vânzărilor din cadrul nivelului. În exemplul de mai jos, dacă plata va fi de 1.000 USD dacă suma vânzărilor este de 55.000 USD sau 95.000 USD.

plata nu este pe o scară de alunecare. Este o rată forfetară pentru fiecare nivel.

dacă sunteți în căutarea unui calcul la scară glisantă, consultați articolul meu despre calcularea comisionului cu o structură de rată pe niveluri folosind SUMPRODUCT.

calculele Comisiei cu XLOOKUP

de asemenea, putem folosi noua funcție XLOOKUP pentru acest calcul. Funcționează foarte asemănător cu VLOOKUP atunci când găsește cel mai apropiat meci.

avantaje cu XLOOKUP

există două diferențe principale și avantaje cu XLOOKUP:

1. cu XLOOKUP specificăm matricea de căutare și matricea de returnare ca intervale separate. Acesta este B4:B7 și D4: D7 în imaginea de mai sus. Acesta este un avantaj față de VLOOKUP, deoarece putem insera sau șterge rânduri între coloanele B și D, iar formula va funcționa în continuare.
   când adăugăm / ștergem coloane în matricea de tabele a VLOOKUP, trebuie să schimbăm manual numărul indexului coloanei sau să folosim o formulă pentru a-l calcula dinamic.
2. XLOOKUP nu necesită ca datele să fie sortate atunci când se utilizează potrivirea exactă sau următorul element mai mic/mai mare pentru modul de potrivire. XLOOKUP va căuta de fapt următorul element mai mic/mai mare și va returna rezultatul din acel rând/coloană.
   VLOOKUP necesită ca datele să fie sortate atunci când ultimul argument este adevărat. De obicei, veți dori să sortați datele pentru aceste tipuri de tabele oricum, dar este bine să știți că puteți pune doar noi niveluri de rată în partea de jos a tabelului și XLOOKUP va funcționa.

dezavantaj al XLOOKUP

principalul dezavantaj cu XLOOKUP este compatibilitatea. Atât dvs., cât și toți utilizatorii fișierului dvs. trebuie să aveți o versiune de Microsoft/Office 365 care are XLOOKUP. Este nu este compatibil înapoi sau disponibil pe versiunile mai vechi de Excel.

prin urmare, VLOOKUP funcționează excelent în acest scenariu și este mult mai ușor de scris decât o formulă imbricată IF.

evitați formulele IF imbricate

o abordare comună a calculării comisioanelor este utilizarea declarațiilor IF. Cu un tabel rata ca aceasta ar trebui să scrie mai multe dacă declarații. Practic, trebuie să scrieți o declarație IF pentru fiecare nivel (rând) din tabel.

apoi trebuie să se alăture toate declarațiile dacă într-o formulă lungă și urât. Acestea sunt numite imbricate dacă declarații.

încerc întotdeauna să evite IFS imbricate atunci când este posibil, deoarece acestea sunt dificil de citit și de înțeles, și ele pot fi mai lent pentru Excel pentru a calcula. Dacă aveți mii de formule IF imbricate în registrul de lucru, timpul de calcul ar putea încetini.

formula IF este mult mai dificil de întreținut. Este nevoie de modificare atunci când rândurile sunt adăugate/șterse din tabel. VLOOKUP și XLOOKUP nu necesită acest tip de întreținere.

dacă utilizați IF, asigurați-vă că faceți din fiecare celulă referință la tabelul de rate o referință absolută (F4 pe tastatură). În caz contrar, veți obține rezultate incorecte atunci când copiați formula în jos.

utilizarea VLOOKUP este mult mai ușoară și mai curată decât utilizarea formulelor imbricate IF. După cum puteți vedea cu acest exemplu, vlookup vă permite să utilizați o formulă pentru a calcula rata de plată a comisionului pentru orice sumă de vânzări dată.

resurse suplimentare

• cum se utilizează VLOOKUP pentru a găsi cea mai apropiată potrivire – ultimul Argument este egal cu adevărat
• cum se calculează Comisioanele cumulative pe niveluri cu SUMPRODUCT
• VLOOKUP explicat în termeni simpli la Starbucks
• VLOOKUP & MATCH – un duo dinamic
• noua funcție Xlookup pentru Excel + Tutorial video
• dacă funcția a explicat: cum se scrie o formulă declarație If în Excel

cum utilizați VLOOKUP cu cel mai apropiat meci?

VLOOKUP cu cea mai apropiată tehnică de potrivire poate fi, de asemenea, utilizat pentru calcule de suport fiscal, potrivirea prețurilor etc. Ce folosiți această tehnică pentru a calcula?