W tym poście obliczymy kwotę, jaką należy wypłacić pracownikowi za ilość przepracowanych godzin. Trudność polega na tym, że stawka godzinowa wzrasta wraz z ilością przepracowanych godzin przez danego pracownika.
Zadanie to wykonamy na podstawie przykładowych danych z rys. nr 1.
Jak widać na rysunku powyżej, jeśli pracownik przepracował mniej niż 100 godzin, to stawkę bierzemy z kolumny F. Jeśli przepracowana została ilość godzin między 100 a 200 stawka godzinowa jest brana z kolumny G, a powyżej 200 godzin, mamy stawkę z kolumny H. Nasze zadanie polega na obliczeniu kwoty, jaka należy się pracownikowi za przepracowane godziny.
Zaczniemy od funkcji WYSZUKAJ.PIONOWO, za której pomocą odnajdziemy wiersz, w którym są dane dla wybranego pracownika. Pierwszy argument funkcji to szukana_wartość, czyli konkretny pracownik (komórka A2). Drugi argument funkcji to tabela_tablica, czyli dane w tabeli z rozpisanymi stawkami godzinowymi (zakres zablokowany bezwzględnie za pomocą klawisza F4, bo będziemy kopiować formułę w dół $E$2:$H$5). W tym zakresie nie zaznaczamy nagłówka, bo nie jest potrzebny. Trzeci argument funkcji to nr_indeksu_kolumny, czyli z której kolumny będziemy wybierać dane. Na tą chwilę wpiszemy tu na stałe wartość 2, czyli dane z drugiej kolumny dla ilości przepracowanych godzin do 100. W miejsce tego argumentu będziemy musieli napisać formułę, która będzie wybierać odpowiednią kolumnę w zależności od ilości przepracowanych godzin. Czwarty argument to przeszukiwany_zakres, czyli rodzaj dopasowania. Wpisujemy tutaj wartość 0 dla dopasowania dokładnego co widać na rys. nr 2.
Zapis formuły powinien wyglądać następująco:
=WYSZUKAJ.PIONOWO(A2;$E$2:$H$5;2;0)
Zatwierdzamy powyższą formułę i kopiujemy w dół. Otrzymamy stawki godzinowe dla poszczególnych pracowników przedstawione na rys. nr 3.
Wyciągnęliśmy stawki godzinowe dla poszczególnych pracowników. Ale ta stawka jest wyciągnięta z kolumny F, czyli dla ilości przepracowanych godzin do 100. Stawka ta jest zależna od ilości przepracowanych godzin każdego pracownika podanych w kolumnie B. Aby uwzględnić te zmiany, musimy w miejsce wpisanego na stałe argumentu nr_indeksu_kolumny wstawić funkcję PODAJ.POZYCJĘ. Pierwszym argumentem funkcji jest szukana_wartość, czyli liczba przepracowanych godzin (B2). Drugi argument to przeszukiwana_tab, czyli nagłówki naszych danych z progami ilości godzin zablokowane bezwzględnie ($F$1:$H$1). Trzeci argument to typ_porównania, czyli mniejsze niż w naszym przykładzie – wpisujemy wartość 1 (rys. nr 4).
Funkcja PODAJ.POZYCJĘ będzie szukała w której kolumnie znajduje się ilość przepracowanych godzin przez danego pracownika. Jeśli np. ilość godzin będzie wynosiła 200 to funkcja poda numer kolumny H. Musimy zwrócić uwagę, że wynik funkcji PODAJ.POZYCJĘ jest o 1 mniejsza niż ilość kolumn, których potrzebujemy w funkcji WYSZUKAJ.PIONOWO, bo zaznaczaliśmy o jedną kolumnę mniejszy zakres (tylko kolumny od F do H). Dlatego do tego argumentu musimy dodać wartość 1.
Zapis całej formuły powinien wyglądać następująco:
=WYSZUKAJ.PIONOWO(A2;$E$2:$H$5;PODAJ.POZYCJĘ(B2;$F$1:$H$1;1)+1;0)
Powyższą formułę zatwierdzamy i kopiujemy w dół. Otrzymamy dane przedstawione na rys. nr 5.
Otrzymaliśmy prawidłowe stawki godzinowe dla poszczególnych pracowników. Pozostaje nam pomnożyć stawkę godzinową przez ilość przepracowanych godzin. Zapis formuły powinien wyglądać następująco:
=B2*WYSZUKAJ.PIONOWO(A2;$E$2:$H$5;PODAJ.POZYCJĘ(B2;$F$1:$H$1;1)+1;0)
Powyższą formułę zatwierdzamy i kopiujemy w dół. Otrzymamy kwoty do wypłaty dla poszczególnych pracowników przedstawione na rys. nr 6.
