Zgłosił się do mnie Klient, który chciał aby utworzyć mu formułę zaokrąglającą ceny w górę zależnie od przedziału w jakim znajduje się aktualna cena. Warunki były jednak skomplikowane.
Specyficzne Warunki
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:
- setki z ceny aktualnej (oryginalnej)
- 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}))
Screen
Wyniki możemy zobaczyć na poniższym screenie. Działa!
Analiza Formuły
Przeanalizujmy sposób działania tej formuły – będziemy sumować dwie składowe:
Najpierw wyciągamy setki. Wiemy, że możemy to zrobić w prosty sposób za pomocą funkcji ZAOKR.W.DÓŁ.
Omówię to na przykładzie spodni, 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ł. 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ą.
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. 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 posortowane rosnąco).
Więc 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ę.
