Jakiś czas temu jeden z czytelników blogu poprosił mnie o pomoc w bardzo nietypowym zadaniu. Skoro nietypowe, to na pewno ciekawe! 🙂 I faktycznie. Czytelnik chciał, aby ceny produktów przekształcić tak, aby zawsze kończyły się na 9 groszy. Czyli np. jak mamy cenę 5,71 zł, to chcemy otrzymać 5,79. Dla 1,05 zł chcemy uzyskać 1,09 zł. Dla pełnych liczb, czyli np. 8 zł, chcemy mieć cenę o grosz niższą – 7,99 zł. To jest całą filozofia tego ćwiczenia i obrazek dla zilustrowania:
Przykład
Aby dokonać takiego cudu, posłużę się dwiema funkcjami zaokrąglającymi i oczywiście funkcją JEŻELI. Do dzieła!
Formatka wygląda po prostu tak:
Formatka
A formuła ma następujące założenia:
- Jeżeli cena jest pełną liczbą (całkowitą), należy od niej odjąć 1 grosz (0,01 zł)
- W innym przypadku należy zaokrąglić ją w dół do jednego miejsca po przecinku i dodać 9 groszy (0,09 zł).
Jak sprawdzić, czy liczba jest liczbą całkowitą? Podam 2 sposoby – co kto woli.
Pierwszy to zaokrąglenie ceny X w dół do liczby całkowitej (co daje efekt odcięcia miejsc dziesiętnych, jakiekolwiek by one były) i odjęcie od wyniku naszej liczby X. Jeśli wynik wyjdzie zero, oznacza to, że liczba X jest całkowita.
Drugi sposób to użycie przeznaczonej do tego funkcji, a konkretnie wrzucenie naszej liczby X w funkcję LICZBA.CAŁK i sprawdzenie, czy równa się to naszej liczbie X. Jeśli tak – nasza liczba X jest całkowita.
Można się zastanawiać, po co zaokrąglać w dół w drugim przypadku (w funkcji będzie to drugi argument funkcji JEŻELI)? Zaokrąglenie bowiem obcina końcówkę (na drugim miejscu po przecinku), a dodawanie +0,09 dorzuca końcówkę 9 groszy. Takie było wymaganie. 🙂
Oto moje propozycje formuł – pierwsza, zaokrąglająca do całkowitych, druga – używająca funkcji LICZBA.CAŁK:
=JEŻELI(ZAOKR.DO.CAŁK(A3)-A3=0;A3-0,01;ZAOKR.DÓŁ(A3;1)+0,09)
Ewentualnie można jeszcze tak:
=JEŻELI(A3=LICZBA.CAŁK(A3);A3-0,01;ZAOKR.DÓŁ(A3;1)+0,09)
Wynik otrzymamy dokładnie ten sam. Do wyboru, do koloru.
Hehe, i to koniec. Tak patrzę, że to chyba jeden z “najdłuższych” artykułów na moim blogu. 🙂 Ale z drugiej strony – po co się rozpisywać? Wszystko już powiedziane. 🙂
MalinowyExcel_Ceny kończące się na 9 groszy_dw
I wersja wideo:
Redakcja językowa: Aleksandra Wasiak
www.kulturajezyka.pl
A jak zaokrąglić daną liczbę tak aby zawsze miała końcówkę 9,99? Np liczbę 63,08 –> 69,99. Jest na to jakaś formuła?
Ja bym zrobiła Zaokr.Góra(Liczba;0) – 0,01 🙂
To ja mam jeszcze jedno pytanie. Jak sprawić, aby liczby zaokrąglały się wg zasad matematycznych tak, aby na końcu było zawsze 9? Czyli przy końcówce 0-4 zaokrąglamy w dół, a przy 5-9 w górę. Tutaj uda się zaokrąglić w górę do 9, ale w przypadku np. liczby 5,32 chciałabym uzyskać 5,29 a nie 5,39. Czy da się to sformułować? Dziękuję!
Hej, a zobacz taką formułę: =ZAOKR(E2;1)-0,01
O to chodzi? 🙂