108. Bardzo nietypowe zaokrąglanie cen (case study)

Swój ostatni wpis poświęciłem wykazaniu różnic pomiędzy funkcjami zaokrąglającymi. W dzisiejszej notce wykorzystam jedną z nich aby skonstruować bardzo ciekawą formułę zaokrąglającą ceny w górę. Niedawno zgłosił się do mnie Klient, który chciał aby utworzyć mu skomplikowaną formułę zaokrąglającą ceny w górę zależnie od przedziału w jakim znajduje się aktualna cena. Warunki były na tyle złożone, że musiałem posilić się VBA i napisać funkcję własną. Ale to tak na marginesie 😉

To co przykuło moją uwagę, to dość nietypowe warunki dla produktów, których cena jest wyższa od 100zł, ale jednocześnie niższa od 500zł. Klient chciał aby te ceny były zaokrąglane zawsze w górę i kończyły się na 39,99zł, 69,99zł lub 99,99zł. Sprytny pomysł!

Wiedziałem, że w formule będę musiał zsumować dwie rzeczy:
a) setki z ceny aktualnej (oryginalnej)
b) końcówkę ceny, która będzie nie tyle zaokrągleniem, co przypisaniem określonej liczby dla dziesiątek z ceny oryginalnej

Skonstruowałem taką formułę.

=SUMA(ZAOKR.W.DÓŁ($A2;100);WYSZUKAJ($A2-ZAOKR.W.DÓŁ($A2;100);{0;0,01;40;70};{0;39,99;69,99;99,99}))

Wyniki możemy zobaczyć na poniższym screenie. Działa! 🙂

 
Przeanalizujmy sposób działania tej formuły – jak wspomniałem będziemy sumować dwie składowe:
a) Najpierw wyciągamy setki. Wiemy, że możemy to zrobić w prosty sposób za pomocą funkcji ZAOKR.W.DÓŁ(). W poprzednim artykule przywołałem przykład z ceną lodów, teraz zwiększamy skalę – niech to będą spodnie, które kosztują 100zł. Wiemy, że mając w portfelu 432,89zł możemy kupić cztery sztuki (nie będę używał słowa “pary” aby nie wprowadzić w błąd 😉 ) takich spodni. W takiej sytuacji zapłacilibyśmy 400zł (“W związku z licznymi pytaniami o RABAT informujemy, że jest to stolica Maroka”) i o taką liczbę chodzi nam w formule – zaokrąglamy wynik do wielokrotności 100zł, ale tak aby nie przekroczyć kwoty 432,89zł. Pierwszą część formuły mamy więc gotową.
b) Druga część formuły jest trudniejsza. Musimy tutaj zostawić dziesiątki z ceny oryginalnej, sprawdzić przedział (w którym się znajdują) i przypisać im odpowiednią końcówkę ceny. W tym celu wykorzystałem funkcję WYSZUKAJ(), która pozwala utworzyć dwie niezależne tablice i w zależności od pozycji danej wartości w pierwszej tablicy, przypisuje w wyniku wartość z tej samej pozycji drugiej tablicy (musimy tutaj jedynie pamiętać aby dane były podane/posortowane rosnąco).
I tak:
– dla zera funkcja WYSZUKAJ() powinna zwrócić zero (jeśli oryginalna cena to np. 100,00zł lub 200,00zł – nie powinna ona zostać w żaden sposób zmieniona)
– dla liczby z przedziału od 0,01 do 39,99 powinna to być liczba 39,99, czyli np. w drugim wierszu mamy 32,89zł, powinno się to przeistoczyć w 39,99zł
– dla liczby z przedziału od 40,00 do 69,99 powinna to być liczba 69,99, czyli np. w piątym wierszu mamy 60,31zł, powinno się to przeistoczyć w 69,99zł
– dla liczby z przedziału od 70,00 do 99,99 powinna to być liczba 99,99, czyli np. w szóstym wierszu mamy 91,86zł, powinno się to przeistoczyć w 99,99zł

Czyli mamy dwie składowe ceny: setki + przypisaną końcówkę. Za pomocą funkcji SUMA() możemy dodać jedno do drugiego i otrzymać w wyniku nową cenę.