Czyli procenty, JEŻELI i… ułatwienie życia!

Jak pierwszy raz usłyszałam o co chodzi w tym „zadaniu”, pomyślałam: WYSZUKAJ.PIONOWO. W drugim podejściu jednak zobaczyłam, że da się to zrobić inaczej. I dobrze, bo o WYSZUKAJ.PIONOWO już ostatnio było (tutaj czy tutaj). Wszystko zależy oczywiście od danych, jakie mamy, a te bardzo mi pasowały do formuły, o której będzie dzisiaj. A o co w ogóle chodzi?

O rozliczanie dodatkowej premii, którą pracownicy dostają za staż pracy. I za każdy przepracowany rok ten procent jest większy o stałą wartość 20%.

Do dzieła!

Założenia

Nasze zadanie polega na tym, żeby obliczyć wysokość dodatkowej premii, jaką pracownik otrzymuje po przepracowaniu określonego czasu u nas w firmie. Premię może dostać dopiero wtedy, jak przepracuje minimum 10 lat. Po takim czasie należy mu się 300% kwoty bazowej, od której naliczamy premię (może to być jego wynagrodzenie). Następnie, z każdym przepracowanym rokiem należy mu się o 20% więcej, czyli jeśli przepracowałby np. 11 lat – powinien dostać 320%, 15 lat – 400% itd. Natomiast maksymalnym procentem, jaki może dostać jest 600%, który dostanie po 25 latach pracy. Jeśli przepracuje 26, czego serdecznie mu gratuluję, dostanie taką samą premię, czyli 600% swojej kwoty bazowej.

Formatka do zadania wygląda tak (i dlatego właśnie zrezygnowałam z WYSZUKAJ.PIONOWO):

Formatka

Czyli na podstawie lat pracy chcemy obliczyć należny procent premii oraz docelowo – jej wysokość. Oczywiście da się to zrobić WYSZUKAJ.PIONOWO, tylko ona wymagałaby utworzenia tabeli premiowej. Gdybym ją miała – od razu bym szła właśnie w to rozwiązanie.

Natomiast tutaj tabeli premiowej nie ma, natomiast jest pewna zasada. Mianowicie: z każdym rokiem premia przyrasta o stałą wartość 20%. Jeśli tak – możemy napisać prostą formułę, żeby obliczyć wynik. I takie właśnie zrobimy.

Potrzebne nam będą komórki definicyjne, zaznaczone na żółto na formatce:

1. minimalny staż pracy, za który się należy premia (C3): 10 lat
2. procent bazowy, czyli taki, od którego startujemy naliczanie (C4): 300%
3. % wzrostu, czyli przyrost procentów za każdy dodatkowy rok pracy (C5): 20%
4. Maksymalny procent premii, jaki można dostać (C6): 600%

Z tymi informacjami możemy przejść do formuły.

Formuła

Ograniczenie 10 lat załatwimy sobie funkcją JEŻELI. Po prostu weźmiemy pod uwagę tylko takich pracowników, którzy pracują 10 lat lub dłużej:

=JEŻELI(B9>=$C$3; "tutaj będzie najważniejsza część formuły" ;0)

Minimalny procent i przyrost 20% załatwimy sobie głównym elementem formuły, czyli: zauważmy, że 20% należy się za każdy dodatkowy rok. Czyli każdy, kto przepracował 10 lat lub więcej na pewno dostanie 300%. Super. Do tego więc będziemy dodawać przyrost (20%) pomnożony przez ilość dodatkowych lat. Czyli jak ktoś przepracował 11 lat, to ma jeden dodatkowy rok. Jak 12 – to 2 itd. Czyli Dla 12 lat będzie tak: 300% + (12-10)*20% = 340%. A dla 10? Tak samo, tylko nawias nam się wyzeruje i zostanie samo 300%:

=JEŻELI(B9>=$C$3;$C$4+(B9-$C$3)*$C$5;0)

Gorzej będzie z 26 latami pracy, ponieważ nasza formuła dorzuci nam kolejne 20%, czego nie chcemy, ponieważ ma być ograniczenie 600%. To ograniczenie z kolei załatwimy sobie funkcją MIN (o takim sprytnym jej użyciu pisałam też tutaj). Jest to świetna alternatywa dla JEŻELI, której też moglibyśmy tutaj użyć, ale nie lubię jej w tym zastosowaniu :). Zobaczcie bowiem, że moglibyśmy wrzucić naszą formułę w JEŻELI, które by sprawdzało, czy jej wynik jest większy niż 600%. Jeśli tak – funkcja wyświetlałaby 600%, a jeśli nie – jeszcze raz tę formułę, którą musiałaby ponownie obliczać. Not nice. Działałaby wolniej (choć pewnie bardzo by nas to nie bolało tutaj;)), a my musielibyśmy się powtarzać i w dwóch miejscach formuły pisać to samo. Tego nie cierpię, stąd funkcja MIN :).

Całą formuła wygląda więc tak:

=JEŻELI(B9>=$C$3;MIN($C$6;$C$4+(B9-$C$3)*$C$5);0)

A jej wynik, po skopiowaniu do całej kolumny, wygląda tak:

Obliczone procenty dodatkowe premii

Samo obliczenie wysokości premii na tej podstawie to już bułka z masłem. Zwykłe mnożenie, czyli taka formuła w komórce E9:

=C9*D9

I taki jest efekt wszystkiego:

Wynik

Tadam! Jak widać trochę matematyki, funkcji JEŻELI i MIN i sprawa z głowy 🙂 Mam nadzieję, że pomogłam!

A tutaj wersja wideo, na której krok po kroku pokazuję co robić: