Zadaniem jest obliczenie prowizji przysługującej handlowcom za wypracowaną marżę i liczbę zleceń. Po wpisaniu tychże danych (marża + liczba zleceń), kalkulator ma wyliczyć przysługującą prowizję. 

Formatka wraz z tabelą z warunkami wygląda tak: 

Formatka

Założenia kalkulatora prowizji 

Zauważmy, że handlowiec może otrzymać dwie różne prowizje: 

1. Wyższą, jeśli wypracuje odpowiednią marżę przy odpowiedniej liczbie zleceń 
2. Niższą, jeśli wypracuje marżę, jednak przy zbyt małej liczbie zleceń. 

Wszystkie warunki określa tabela pokazana na formatce powyżej. 

Zatem obliczając wysokość przysługującej prowizji, musimy określić: 

1. Przedział marżowy, do którego „wpada” wypracowana marża 
2. Minimalną liczbę zleceń dla tego przedziału 

Zauważ, że dla każdego przedziału marżowego są dwie prowizje, spośród których trzeba będzie wybrać tę należną. W naszym rozwiązaniu zatem najpierw określimy obie te prowizje oraz minimalną liczbę zleceń, a następnie – w osobnej formule, wybierzemy odpowiednią prowizję. 

Napiszemy w tym celu 3 formuły (prowizja wyższa, prowizja niższa, minimalna liczba zleceń), a w czwartej formule obliczymy wysokość ostatecznej prowizji. 

Dla każdej formuły pokażę Ci dwa warianty:  

1. Używając funkcji WYSZUKAJ.PIONOWO (ang.: VLOOKUP) 
2. Używając funkcji X.WYSZUKAJ (ang.: XLOOKUP) 

Do dzieła! 

Formuła: minimalna liczba zleceń 

Najpierw obliczymy minimalną liczbę zleceń na podstawie wypracowanej marży. W naszym przykładzie wypracowana marża to 5000 zł. 

Szukać będziemy więc wypracowanej marży (D12) w tabeli powyżej (B4:E9). 

Ponieważ będziemy wpasowywać liczbę w przedział, skorzystamy z szukania tzw. przybliżonego w funkcji WYSZUKAJ.PIONOWO (X.WYSZUKAJ). 

Oto formuła: 

=WYSZUKAJ.PIONOWO(D12;B4:E9;2) 

W trzecim argumencie wpisałam 2, ponieważ interesuje mnie wynik z drugiej kolumny przeszukiwanej tabeli, czyli z kolumny Min liczba zleceń. 

Efekt mamy taki: 

Minimalna liczba zleceń

Formuła z X.WYSZUKAJ (funkcja dostępna w wyższych wersjach Excela oraz w Excelu 365) wygląda następująco: 

=X.WYSZUKAJ(D12;B4:B9;C4:C9;0;-1) 

Zauważ, że uzupełniliśmy cztery argumenty tej funkcji: 

1. Trzeci argument (0) powoduje, że gdy funkcja nie znajdzie wartości, to zwróci zero 
2. Czwarty (-1) mówi funkcji, aby szukała w sposób przybliżony, zwracając wartość mniejszą niż szukana. Jest to analogiczne działanie do WYSZUKAJ.PIONOWO, gdy pominiemy jej ostatni argument. 

Obie funkcje zwrócą nam te same wyniki, zobacz: 

Minimalna liczba zleceń – XWYSZUKAJ

Zatem, aby otrzymać wyższą prowizję, należy wypracować co najmniej 25 zleceń. W naszym przykładzie widać, że się udało (wypracowano 56 zleceń) 👍. 

Pozostaje nam teraz określić pozostałe wartości. 

Formuła: wyższa prowizja 

Formuła będzie tutaj praktycznie identyczna, z jedną różnicą – kolumna, z której będziemy wyświetlać wynik, będzie to kolumna trzecia (dla X.WYSZUKAJ: zakres D4:D9). 

Formuły zatem wyglądają następująco: 

Dla WYSZUKAJ.PIONOWO 

=WYSZUKAJ.PIONOWO(D12;B4:E9;3) 

Dla X.WYSZUKAJ: 

=X.WYSZUKAJ(D12;B4:B9;D4:D9;0;-1) 

Formuła: niższa prowizja 

I analogicznie do formuły dla wyższej prowizji, tutaj kolumna, z której będziemy wyświetlać wynik, będzie to kolumna czwarta (dla X.WYSZUKAJ: zakres E4:E9). 

Formuły zatem wyglądają następująco: 

Dla WYSZUKAJ.PIONOWO 

=WYSZUKAJ.PIONOWO(D12;B4:E9;4) 

Dla X.WYSZUKAJ: 

=X.WYSZUKAJ(D12;B4:B9;E4:E9;0;-1) 

Wynik wszystkich tych formuł wygląda tak: 

Wynik wszystkich formuł

Jak widać – jest identyczny 😊. 

Formuła: wyliczanie ostatecznej prowizji 

To teraz została nam już tylko funkcja JEŻELI, która na podstawie wyliczonych wcześniej danych wybierze należną marżę. 

Sprawa jest prosta: jeśli liczba zleceń (D13) jest większa lub równa minimalnej liczbie zleceń (D15), to należy się wyższa prowizja (D16). Jeśli nie – niższa (D17). 

Formuła wygląda zatem tak: 

=JEŻELI(D13>=D15;D16;D17) 

I voila! Wszystko! 

Oto efekt zastosowania jej do wyliczenia należnej prowizji na podstawie marży i liczby zleceń: 

Obliczona prowizja

Mam nadzieję, że zaprezentowany praktyczny przykład Ci się przyda! Możesz z niego skorzystać nawet, gdy Twoje zadanie jest sformułowane nieco inaczej 🙌. 

Plik do pobrania:

https://malinowyexcel.pl/wp-content/uploads/2024/05/20240430-MalinowyExcel-Kalkulator-Premii-DW.xlsx

I film na Youtubie, gdzie pokazuję powyższe rozwiązanie: