Ile klient powinien zapłacić za usługę (np. konsultację księgowej), która trwa 1 godzinę i 15 minut? A ile w przypadku, gdy za pierwszą godzinę płaci więcej niż za każde kolejne? A ile, jeśli ma płacić za każdą rozpoczętą godzinę?

Z takimi pytaniami zgłosił się do mnie jeden z czytelników bloga. Podobne pytania zadają sobie wszystkie te osoby, które mają ustalić cenę za usługę w zależności od czasu jej trwania. Oczywiście tego typu sprawy można dość szybko wyliczyć ręcznie, jednak na blogu piszę o usprawnieniach pracy, a ten przypadek aż się prosi, żeby go usprawnić 🙂

Ponieważ mogą być różne zasady rozliczania usług, w tym artykule rozpatrzę dwa ich przypadki.

wariant 1: klient płaci proporcjonalnie za każdą minutę kolejnej godziny,
wariant 2: klient płaci za każdą rozpoczętą godzinę pełną stawkę.

W obu przypadkach:

1. pierwsza godzina ma inną stawkę niż kolejne,
2. za pierwszą godzinę płaci się pełną stawkę.

Formatka

Formatka do tego zadania wygląda następująco:

Formatka

W komórce C3 należy wpisać czas trwania usługi, oraz uzupełnić ceny w komórkach C6 i C7. W moim przykładzie jest 20 zł za pierwszą godzinę i 10 zł za każdą kolejną.

W komórkach kolumny C będzie rozwiązanie dla wariantu 1. (klient płaci proporcjonalnie za każdą minutę kolejnej godziny), a w komórkach kolumny D – wariantu 2. (klient płaci za każdą rozpoczętą godzinę pełną stawkę).

Rozwiązanie – wariant 1.

Przypomnę, że tutaj klient płaci proporcjonalnie za każdą minutę kolejnej godzin. Zajmijmy się szarymi komórkami, czyli obliczaniem ile pełnych godzin i pełnych minut trwała usługa. Dla naszego przykładu będzie to oczywiście 1 godzina i 15 minut.

Obliczanie godzin – komórka C10

=JEŻELI(ORAZ(C3<1/24;C3>0);1;GODZINA(C3))

Zwróćcie uwagę, że wyliczenie liczby godzin jest bardzo proste: całą robotę załatwia za nas funkcja GODZINA. Problem pojawia się jednak, gdy usługa trwała mniej niż godzinę, a zgodnie z założeniami, za pierwszą godzinę klient ma zapłacić pełną stawkę (i to w dodatku wyższą!). Musimy więc sprawdzić, czy przypadkiem usługa nie trwała mniej niż godzinę, ale dłużej niż 0. Do tego celu użyjemy funkcji ORAZ (sprawdzanie, czy trwała mniej niż godzinę, ale dłużej niż zero). Jeżeli tak – chcemy wpisać 1 (1 godzinę), a jeżeli nie – po prostu wyświetlamy liczbę pełnych godzin, za pomocą funkcji GODZINA.

Obliczanie minut – komórka C11
Analogicznie jak dla godzin, tylko używamy funkcji MINUTA, aby wyświetlić liczbę minut.

=JEŻELI(ORAZ(C3<1/24;C3>0);0;MINUTA(C3))

Obliczanie ceny za godziny

Cena za pełne godziny (C13) będzie to cena za pierwszą godzinę plus cena za kolejną godzinę razy liczba tych godzin.

=JEŻELI(C3=0;0;C6)+JEŻELI(C10>1;C10-1;0)*C7

Pamiętajmy, że jeśli użytkownik nic nie wpisze, to cena ma się równać zero, stąd pierwsze JEŻELI. Następne JEŻELI liczy wynagrodzenie dla kolejnych godzin.

Obliczanie ceny za minuty

Tutaj po prostu liczymy stawkę za minuty (C14):

=C11/60*C7

Łączna kwota do zapłaty

A to już banał – zwykła funkcja SUMA w komórce C16:

=SUMA(C13:C14)

A oto wynik dla wariantu 1.:

Wynik wariantu 1.

Rozwiązanie – wariant 2.

Dla wariantu 2., czyli gdy klient płaci za każdą rozpoczętą godzinę pełną stawkę, będzie trochę mnie wyliczeń. Odpada nam bowiem wyliczanie minut. Chcemy zatem obliczyć tylko liczbę rozpoczętych godzin. Oto formuła, która to obliczy (D10):

=JEŻELI(ORAZ(C3<1/24;C3>0);1;GODZINA(C3))+JEŻELI(C11>0;1;0)

A ta formuła policzy wynagrodzenie (D13):

=JEŻELI(C3=0;0;C6)+JEŻELI(D10>1;D10-1;0)*C7

I na koniec, żeby było ładnie :), wynagrodzenie w szarej komórce: zwykła SUMA:

=SUMA(D13:D14)

Ten krok można oczywiście pominąć, gdyż da w wyniku to, co poprzednia formuła.

Oto wynik wszystkiego:

Wynik

Zaproponowane przeze mnie rozwiązanie zabezpiecza się na wszystkie przypadki (niewpisanie danych, usługa krótsza niż godzina, usługa dłuższa niż godzina). Można to oczywiście zrobić bez tych zabezpieczeń, wtedy formuła dla wariantu 1. wygląda tak:

=C6+(GODZINA(C3)-1)*C7+MINUTA(C3)/60*C7

a dla wariantu 2. tak:

=C6+JEŻELI(D10>1;D10-1;0)*C7

Wszystko 🙂 Przyda Wam się? Mam nadzieję, że tak!

Ponieważ poprosiliście mnie o plik źródłowy, zamieszczam poniżej: