Czyli nietypowe zastosowanie WYSZUKAJ.PIONOWO

O zaokrąglaniu cen pisałam już jakiś czas temu tutaj. Natomiast był to zupełnie inny przypadek niż ten, który opiszę dzisiaj. Celem dzisiejszego przykładu bowiem jest zaokrąglenie cen zgodnie ze schematem: ceny od 200 zł do 204,99 zł mają być równe 199 zł. Ceny od 205 zł do 209,99 zł – 209 zł. Ceny 210 zł i powyżej – 219 zł. Najlepiej pokazuje to poniższy obrazek, a na nim tabela zaokrągleń:

Formatka

Ponieważ celem jest pewien rodzaj zaokrąglenia – od razu nasze myśli kierują się w stronę jakiejś funkcji zaokrąglającej. Pewnie dałoby się coś tutaj pokombinować, ale trzeba byłoby się nieźle natrudzić. Ja natomiast jestem zwolenniczką prostoty, więc pójdę na łatwiznę ;).

Dla mnie jest to idealna sytuacja do wykorzystania funkcji WYSZUKAJ.PIONOWO, w jej mniej znanym użyciu, czyli z 1 w ostatnim argumencie (albo w ogóle bez niego).

Takie użycie WYSZUKAJ.PIONOWO stosujemy, gdy chcemy wpasować liczbę w przedziały. Zobacz, że dokładnie to chcemy tutaj zrobić:

• Przedział 200 – 204,99 to 199 zł
• Przedział 205 – 209,99 to 209 zł
• itd.

Formuła zatem będzie wyglądała po prostu tak (D5):

=WYSZUKAJ.PIONOWO(C5;$F$7:$G$9;2)

Czyli:

argument 1: szukamy naszej ceny (C5)

argument 2: w tabeli zaokrągleń ($F$7:$G$9 – koniecznie zablokowana, ponieważ ma być taka sama dla wszystkich cen)

argument 3: po znalezieniu odpowiedniej ceny wyświetlamy w komórce drugą kolumnę (2) i

argument 4: dopasowujemy do przedziałów, czyli wpisujemy tutaj 1 lub pozostawiamy ten argument pusty.

Po skopiowaniu formuły do końca tabeli, otrzymujemy taki wynik:

Wynik

Minusem tej metody jest to, że wymaga ona od nas zdefiniowanie tabeli zaokrągleń. To fakt. Ja jednak myślę, że nie jest to jakiś specjalny problem – przynajmniej czarno na białym widać, jak mają być ceny zaokrąglane i dodatkowo w każdej chwili możemy to zmienić w prosty sposób zmieniając tabelę zaokrągleń. Nie ma tego złego ;).

Warto wiedzieć też, że użyta w przykładzie tabelka zaokrągleń zakłada, że mamy ceny większe lub równe 200 zł. Jeśli będziemy chcieli „zaokrąglić” cenę mniejszą niż 200 zł – funkcja zwróci błąd #N/D!. Takim wynikiem nie należy się przerażać, bo oznacza on jedynie, że funkcja nie znalazła odpowiedniego przyporządkowania. Tak, jak na rysunku:

Funkcja wyświetla błąd, gdy cena jest niższa niż 200

Można natomiast się w prosty sposób przed nim zabezpieczyć, mianowicie tak, że jeśli WYSZUKAJ.PIONOWO zwróci błąd, to my wyświetlimy np. oryginalną cenę. Najprościej to zrobić wrzucając funkcję WYSZUKAJ.PIONOWO w JEŻELI.BŁĄD, czyli tak:

=JEŻELI.BŁĄD(WYSZUKAJ.PIONOWO(C5;$F$7:$G$9;2);C5)

Efekt jest taki, jak na obrazku:

Niewyświetlanie błędu przy niższej cenie

No i to tyle :).

Plik do pobrania:
MalinowyExcel_Zaokrąglenia WP dw.xlsx

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