Podsumowując: dowiedz się, jak obliczać prowizje w programie Excel dla podstawowego planu warstwowego i tabeli stawek za pomocą funkcji IF, VLOOKUP lub XLOOKUP.
Poziom umiejętności: Średniozaawansowany
Video Tutorial
Obliczanie prowizji w programie Excel może być bardzo trudnym zadaniem. Jest to szczególnie ważne, jeśli próbowano użyć wielu instrukcji IF do obliczania prowizji dla każdego poziomu w tabeli stawek.
Ten artykuł wyjaśni, jak korzystać z funkcji VLOOKUP, aby znacznie ułatwić ten proces. Sekret polega na ustawieniu ostatniego argumentu w wyszukiwaniu na TRUE, aby znaleźć najbliższy mecz.
proszę sprawdzić mój artykuł o tym, jak używać VLOOKUP, aby znaleźć najbliższy mecz, aby uzyskać szczegółowe wyjaśnienie dotyczące ustawienia ostatniego argumentu na TRUE.
aktualizacja: Post został zaktualizowany o instrukcje obliczania prowizji za pomocą nowej funkcji XLOOKUP. Oto artykuł & wideo na XLOOKUP.
Pobierz plik
Pobierz plik, aby przejść dalej.
obliczenia prowizji-IF Vs VLOOKUP Vs XLOOKUP.XLSX (31.5 KB)
Obliczanie prowizji za pomocą VLOOKUP
dzięki prostemu planowi prowizji zwykle masz tabelę stawek, która zawiera listę stawek wypłat na każdym poziomie sprzedaży. Ponieważ przedstawiciel handlowy robi więcej sprzedaży, jego / jej stopa wypłat zazwyczaj wzrośnie.
stawka wypłaty może być kwotą zryczałtowaną, procentem przychodów, procentem kwoty itp. Zadaniem VLOOKUP jest znalezienie kwoty sprzedaży rep w tabeli stawek i zwrócenie odpowiedniej stawki wypłaty.
dla tego przykładu nasz plan prowizji wygląda następująco:
- Rep sprzedaje $0-$50,000, zarabiają 5%
- Rep sprzedaje $51,000-$100,000, zarabiają 7%
- Rep sprzedaje $100,001-$150,000, zarabiają 10%
- Rep sprzedaje ponad $150 001, zarabiają 15%
przedstawiciel dokona pewnej sprzedaży w ciągu miesiąca. Otrzyma określoną stawkę wypłat w zależności od osiągniętego poziomu sprzedaży.
możemy użyć formuły VLOOKUP do obliczenia stawki wypłat dla danej kwoty sprzedaży (wartość wyszukiwania). Aby to zadziałało, musimy ustawić ostatni argument w vlookupie na TRUE.
z ostatnim argumentem ustawionym na TRUE, vlookup znajdzie najbliższą zgodność z wartością wyszukiwania, która jest mniejsza lub równa kwocie wyszukiwania. To w zasadzie pozwala nam znaleźć wartość między zakresami dwóch liczb (warstw).
dlatego ostatni argument w VLOOKUP nosi nazwę range_lookup. Jeśli ten argument jest prawdziwy, VLOOKUP szuka między zakresami wartości w kolumnie minimum warstwy, aby znaleźć dokładne dopasowanie lub wartość mniejszą niż wartość wyszukiwania.
jak skonfigurować tabelę stawek
umieszczając tę tabelę stawek w programie Excel, wystarczy podać minimum warstwy dla zakresu wyszukiwania. Ponownie VLOOKUP wyszukuje „najbliższe dopasowanie”, które jest mniejsze lub równe wartości wyszukiwania. Jeśli znajdzie wartość większą niż wartość lookup, wtedy zwróci poprzedni wiersz.
w tym przykładzie dla tabeli stawek prowizji pierwszy wiersz w zakresie wyszukiwania musi wynosić zero. Dzieje się tak dlatego, że przedstawiciel handlowy może potencjalnie mieć sprzedaż $0, a wartość wyszukiwania wyniesie zero. Jeśli wartość wyszukiwania (kwota sprzedaży) była liczbą ujemną, vlookup zwróci błąd.
ważne jest, aby to wiedzieć i skonfigurować tabelę stawek dla wszystkich możliwych wartości wyszukiwania.
jeśli kwota sprzedaży jest większa niż ostatni wiersz w zakresie wyszukiwania, vlookup zwróci ostatni wiersz. Na przykład, jeśli przedstawiciel dokonał sprzedaży $175,000 następnie vlookup zwróci 15%.
Oblicz prowizję, aby zwrócić wartość dolara
wypłata może być również zwrócona jako wartość dolara, zamiast procentu. Przy tej konfiguracji wypłata będzie ryczałtowa.
oznacza to, że wypłata będzie taka sama, niezależnie od tego, jaka jest kwota sprzedaży w ramach warstwy. W poniższym przykładzie, jeśli wypłata wyniesie $1,000, jeśli kwota sprzedaży wynosi $ 55,000 lub $ 95,000.
wypłata nie jest w skali przesuwnej. Jest to stawka ryczałtowa dla każdego poziomu.
Jeśli szukasz obliczenia skali przesuwnej, zobacz mój artykuł na temat obliczania prowizji z warstwową strukturą stawki za pomocą SUMPRODUCT.
obliczenia prowizji za pomocą XLOOKUP
możemy również użyć nowej funkcji XLOOKUP do tego obliczenia. Działa bardzo podobnie do VLOOKUP przy znajdowaniu najbliższego dopasowania.
zalety z XLOOKUP
istnieją dwie główne różnice i zalety z XLOOKUP:
- za pomocą XLOOKUP określamy tablicę wyszukiwania i zwracamy tablicę jako oddzielne zakresy. To jest B4: B7 i D4: D7 na powyższym obrazku. Jest to zaleta nad VLOOKUP, ponieważ możemy wstawiać lub usuwać wiersze między kolumnami B I D, a formuła nadal będzie działać.
kiedy dodajemy / usuwamy kolumny w tablicy tabeli VLOOKUP, musimy ręcznie zmienić numer indeksu kolumny lub użyć formuły do dynamicznego jej obliczenia. - XLOOKUP nie wymaga sortowania danych podczas korzystania z funkcji dokładne dopasowanie lub następny mniejszy/większy element w trybie dopasowania. XLOOKUP będzie szukał następnego mniejszego / większego elementu i zwróci wynik z tego wiersza / kolumny.
VLOOKUP wymaga sortowania danych, gdy ostatni argument jest prawdziwy. Zazwyczaj będziesz chciał sortować dane dla tych typów tabel, ale dobrze jest wiedzieć, że możesz po prostu umieścić nowe poziomy stawek na dole tabeli i XLOOKUP zadziała.
wadą XLOOKUP
główną wadą xlookup jest kompatybilność. Zarówno ty, jak i wszyscy użytkownicy Twojego pliku muszą być w wersji Microsoft/Office 365, która ma XLOOKUP. Nie jest kompatybilny wstecz ani dostępny w starszych wersjach programu Excel.
dlatego VLOOKUP działa świetnie w tym scenariuszu i jest znacznie łatwiejszy do napisania niż zagnieżdżona formuła IF.
unikaj zagnieżdżonych formuł IF
powszechnym podejściem do obliczania prowizji jest użycie instrukcji IF. Z taką tabelą stawek musisz napisać wiele instrukcji IF. Zasadniczo musisz napisać jedną instrukcję IF dla każdego poziomu (wiersza) w tabeli.
następnie musisz połączyć wszystkie instrukcje IF w jedną długą i brzydką formułę. Są to tzw. zagnieżdżone instrukcje IF.
zawsze staram się unikać zagnieżdżonych IFs, gdy to możliwe, ponieważ są one trudne do odczytania i zrozumienia, i mogą być wolniejsze dla Excela do obliczania. Jeśli masz tysiące zagnieżdżonych formuł IF w skoroszycie, czas obliczeń może spowolnić.
formuła IF jest znacznie trudniejsza do utrzymania. Wymaga modyfikacji, gdy wiersze są dodawane/usuwane z tabeli. VLOOKUP i XLOOKUP nie wymagają tego typu konserwacji.
jeśli używasz IF, upewnij się, że każda komórka odsyła do tabeli stawek jest bezwzględnym odniesieniem (F4 na klawiaturze). W przeciwnym razie otrzymasz nieprawidłowe wyniki podczas kopiowania formuły.
używanie VLOOKUP jest znacznie łatwiejsze i czystsze niż używanie zagnieżdżonych formuł IF. Jak widać w tym przykładzie, vlookup pozwala użyć jednej formuły do obliczenia stawki wypłaty prowizji dla dowolnej kwoty sprzedaży.
dodatkowe zasoby
- jak użyć VLOOKUP, aby znaleźć najbliższy argument Match – Last równa się TRUE
- Jak obliczyć skumulowane prowizje warstwowe za pomocą SUMPRODUCT
- VLOOKUP wyjaśniony w prostych słowach w Starbucks
- VLOOKUP & MATCH – a Dynamic Duo
- nowa funkcja xlookup dla Excela + samouczek wideo
- funkcja IF wyjaśniona: Jak napisać formułę instrukcji IF w Excelu
Jak korzystać z VLOOKUP z najbliższym dopasowaniem?
VLOOKUP z techniką najbliższego dopasowania może być również używany do obliczania przedziału podatkowego, dopasowania ceny itp. Co używasz tej techniki do obliczenia?