22 kwietnia 2021

Kurs Excel #2 Daty i godziny - Obliczenia

Jeżeli przeczytałeś pierwszy materiał dotyczący Excela, to już wiesz, jak są przez niego interpretowane daty i godziny. W tym wpisie:

  • nauczysz się wykonywać proste obliczenia z wykorzystaniem wcześniej nabytej wiedzy
  • poznasz sposób na automatyczne wypełnianie kolejnych komórek arkusza serią danych
  • dowiesz się jak przy użyciu dostępnych funkcji w prosty sposób uzyskać datę wybranego ostatniego dnia miesiąca
  • poznasz funkcję na obliczanie wieku

Krótkie przypomnienie

Daty i godziny reprezentowane są jako liczby. Zmiana formatowania komórki na Ogólne, pozwala podejrzeć jaką wartość ona zawiera.

excel data
Formaty dat i godzin.

Wykonywanie prostych obliczeń na datach

Skoro data jest wyrażona jako liczba dni to w prosty sposób możemy na niej wykonywać różne operacje matematyczne.

Obliczanie różnicy dni

odejmowanie dat excel
Różnica dat w Excelu.

Taką formułę jaką widać na powyższym zrzucie ekranu możemy zastosować, jeżeli mamy pewność, że w kolumnie B są daty późniejsze od tych występujących w kolumnie A. Co zrobić jednak, gdy nie mamy takiej wiedzy ? W takiej sytuacji pomocna okaże się funkcja MODUŁ.LICZBY(), która obliczy wartość bezwzględną z naszego wyniku.

excel zmienia liczby na daty
Zastosowanie funkcji MODUŁ.LICZBY().

Obliczanie sumy liczby i daty

sumowanie godzin excel
W Excelu można wykonywać obliczenia na datach.

Analogicznie możemy obliczać różnicę, iloczyn czy iloraz.

Obliczanie różnicy dat korzystając z formuły DATA.RÓŻNICA()

Formuła, której użyjemy nie jest już wspierana

Formuły DATA.RÓŻNICA() przyjmuje następujące argumenty:

  • data_początkowa – data wcześniejsza
  • data_końcowa – data późniejsza

Trzeba pamiętać, że jeżeli chcemy użyć tej formuły to daty należy podać we właściwej kolejności.

Jak obliczyć dowolny ostatni dzień tygodnia danego miesiąca?

Załóżmy, że w zadaniu koniecznym okazało się wyliczenie daty ostatnich niedziel w miesiącu. Oczywiście można by było użyć formuły DZIEŃ.TYG() i ręcznie wyszukać właściwe daty.

Przy małej ilości danych taki sposób byłby w porządku. Tak, jak na pokazanym niżej przykładzie, można z łatwością stwierdzić, że ostatnią niedzielą kwietnia jest dnia 25.04.2021.

suma godzin excel
Formuła DZIEŃ.TYG() w praktyce.

Aby szybko obliczyć daty ostatnich niedziel dla konkretnych miesięcy z naszego zakresu danych możemy skorzystać z prostych obliczeń matematycznych. Wykorzystamy do tego wiedzę o liczbie dni jaką trwają kolejne miesiące w roku oraz formuły DZIEŃ.TYG().

Na początku uzupełnię kolumnę o kilka wybranych ostatnich dni z miesięcy. Można to zrobić na kilka sposobów.

Sposób 1. Automatyczne wypełnianie komórek serią danych

Do wypisania kolejnych ostatnich dni miesięcy można wykorzystać automatyczne wypełnianie komórek serią danych (automatyczne wypełnianie komórek, na podstawie kilku podanych wartości). W tym celu zaznaczam dwie pierwsze daty przy pomocy kursora w kształcie białego krzyżyka, a następnie przytrzymując zielony kwadracik znajdujący się w prawym dolnym rogu ostatniej zaznaczonej komórki (kursor powinien zmienić kształt na czarny plusik) przesuwam zaznaczenie ku dołowi.

formatowanie daty excel
Naszą pracę w Excelu ułatwi automatyczne wypełnianie komórek.

Otrzymana przeze mnie kolumna z datami prezentuje się następująco.

daty w excelu
Automatycznie wypełnione danymi komórki.

Sposób 2. Użycie funkcji NR.SER.OST.DN.MS()

Funkcja NR.SER.OST.DN.MS() pozwala w łatwy sposób obliczyć ostatni dzień miesiąca z dowolnej daty. Funkcja ta przyjmuje takie argumenty jak:

  • Data_pocz – data, od której obliczamy ostatni dzień miesiąca
  • Miesiące - liczba miesięcy przed (liczby ujemne) Data_pocz, lub po (liczby dodatnie) .

Zatem, aby od dowolnej daty obliczyć datę ostatniego dnia miesiąca można użyć formuły, która jako argument miesiące przyjmie wartość 0.

Funkcja zwróci datę w postaci Ogólnej, zatem wyniki po zmianie formatu komórki na Data będą prezentowały się następująco:

Zwróć uwagę, że funkcja oblicza poprawnie daty ostatnich dni miesięcy również dla lat przestępnych.

jak odjąć daty w excelu
Zastosowanie funkcji NR.SER.OST.DN.MS().

Obliczanie dowolnego ostatniego dnia miesiąca

Po użyciu formuły DZIEŃ.TYG() ze zwracanym typem ustawionym na 2, można zauważyć, że niektóre daty mogą już być ostatnimi niedzielami miesiąca.

excel-data
Obliczanie dni tygodnia.

Zwróćmy teraz uwagę na kalendarz, np. od piątku do niedzieli upływa 5 dni. Jest to ta sama wartość jaką będzie zwracała dla piątku formuła DZIEŃ.TYG(). Zatem po odjęciu wyniku formuły DZIEŃ.TYG() od daty ostatniego dnia miesiąca uzyskamy datę ostatniej niedzieli. Oczywiście przy obliczeniach należy pominąć dni, które już są niedzielami.

Tak prezentują się otrzymane wyniki po zmianie formatu komórki na Data krótka.

odejmowanie dat excel
Komórki po zmianie formatu na Data krótka.

Analogicznie można obliczyć wszystkie inne ostatnie dni miesiąca. Wystarczy tylko w odpowiedni sposób zmieniać wartość argumentu zwracany_typ w formule DZIEŃ.TYG():

  • 2 lub 11 - do obliczenia ostatniej niedzieli
  • 12 - do obliczenia ostatniego poniedziałku
  • 13 - do obliczenia ostatniego wtorku
  • 14 - do obliczenia ostatniej środy
  • 15 - do obliczenia ostatniego czwartku
  • 16 - do obliczenia ostatniego piątku
  • 17 - do obliczenia ostatniej soboty
excel zmienia liczby na daty
Obliczamy inne ostatnie dni miesiąca.

Obliczanie wieku osób na podstawie daty urodzenia

Aby obliczyć wiek względem podanej daty urodzenia oraz obecnej daty należy porównać ze sobą datę urodzin w roku w bieżącym oraz obecną datę. Tą pierwszą wartość można uzyskać poprzez wykorzystanie formuły DATA(), która zwróci wartość liczbową daty na postawie podanego roku, miesiąca i numeru dnia. Jeżeli data urodzin w bieżącym roku odbyła się wcześniej lub jest tego samego dnia co wskazuje dzisiejsza data, to warunek będzie spełniony i funkcja zwróci różnicę bieżącego roku i roku urodzenia. W przeciwnym razie od różnicy dat należy odjąć 1.

=JEŻELI(DATA(ROK($D$2); MIESIĄC(A4);DZIEŃ(A4)) <= $D$2; ROK($D$2) - ROK(A4); ROK($D$2) - ROK(A4) - 1)

sumowanie godzin excel
W taki sposób obliczamy wiek.
Najnowsze wpisy

Jak pomóc dziecku w nauce programowania? Poradnik dla rodziców

Przeczytaj >>

Dlaczego warto uczyć dzieci programowania? – jakie umiejętności rozwija programowanie?

Przeczytaj >>

Python a C++

Przeczytaj >>

Algorytm Euklidesa w języku Python

Przeczytaj >>

10 podstawowych funkcji Excela, które trzeba znać

Przeczytaj >>

JESTEŚ AMBITNY?

Dołącz do nas jeszcze dziś i rozwijaj się w swojej ulubionej dziedzinie we współpracy z nauczycielami, którzy są autorami artykułów na naszym blogu!
POZNAJ OFERTĘ KUSÓW
© Ambitni Szkoła Informatyki 011111100101(2) | jesteś niemal gotowy!
crossmenu