MS Excel lub Excel to powszechnie używane oprogramowanie arkuszy kalkulacyjnych z szeroką gamą wbudowanych narzędzi i funkcji. Pomaga nam rejestrować różne zestawy danych i wykonywać na nich obliczenia z wieloma komórkami. Przykładem klasycznego obliczenia jest obliczenie różnicy między dwiema datami. Nie wygląda to jednak na użyteczną kalkulację. Jednak ta sama technika jest w pewnym stopniu kluczowa przy znajdowaniu lub obliczaniu wieku kogoś lub czegoś w programie Microsoft Excel.
Oprócz wieku dowolnej osoby, możemy użyć tej samej koncepcji do obliczenia czasu trwania dowolnego projektu, lat istnienia dowolnej firmy, liczby lat, które upłynęły pomiędzy określonymi datami itp.
W tym samouczku omawiamy różne metody i rozwiązania dotyczące obliczania wieku w programie Excel. Ten samouczek pomoże nam nauczyć się obliczać wiek jako liczbę pełnych lat, miesięcy i dni w bieżącej dacie lub dowolnej konkretnej dacie.
Jak obliczyć wiek w Excelu?
Chociaż w programie Excel nie ma określonej funkcji służącej do obliczania wieku, istnieje wiele sposobów obliczania wieku w różny sposób w różnych scenariuszach. Musimy jednak znać pierwotną datę urodzenia danej osoby (D.O.B.). Następnie podajemy datę urodzenia w połączeniu z funkcjami programu Excel, w szczególności DATA JEŻELI i DZISIAJ, aby obliczyć wiek lub rozróżnić daty. Omówmy teraz typowe scenariusze ustalania wieku:
Obliczanie wieku w latach
Obliczając wiek każdej osoby, zwykle bierzemy pod uwagę kilka czynników. Na przykład być może będziemy musieli obliczyć wiek w latach, miesiącach, dniach lub wszystkich razem. Jednak najczęściej używanym zadaniem programu Excel jest obliczanie wieku osoby w latach.
Zakładając, że znamy datę urodzenia, kilka różnych funkcji w Excelu może pomóc nam obliczyć wiek danej osoby w latach. Omówmy szczegółowo każdą przydatną formułę wieku:
Korzystanie z funkcji DATEDIF
Funkcja DATEDIF w programie Excel jest najczęstszą funkcją obliczania wieku osoby. Jest to łatwa w użyciu, wbudowana i najbardziej odpowiednia funkcja, która przyjmuje datę urodzenia jako datę wejściową i zwraca wiek osoby jako dane wyjściowe.
W inny sposób funkcja DATEDIF zazwyczaj konwertuje datę urodzenia na wiek odpowiedniej osoby. Podstawową zaletą funkcji DATEDIF jest to, że można jej używać do obliczania wieku w różnych formatach, np. tylko lata, tylko miesiące, tylko dni lub łączna forma lat, miesięcy i dat itp.
W przeciwieństwie do innych funkcji programu Excel, funkcja DATEDIF nie pojawia się na krótkiej liście funkcji. Oznacza to, że nie widzimy funkcji DATEDIF jako sugestii, gdy zaczynamy ją wpisywać w komórce programu Excel po znaku równości. Funkcja działa jednak we wszystkich wersjach Excela. Aby skorzystać z funkcji DATEDIF w Excelu, musimy znać składnię i wymagane argumenty.
Ogólna składnia funkcji DATEDIF jest zdefiniowana w następujący sposób:
=DATEDIF(start_date, end_date, unit)
Jak pokazano tutaj, funkcja wymaga następujących trzech argumentów:
Należy zauważyć, że Y, M i D zwracają liczby odpowiednio w pełnych latach, miesiącach i dniach. Natomiast YM zwraca tylko różnicę dat w miesiącach, ignorując odpowiadające im dni i lata, MD zwraca tylko różnicę dat w dniach, ignorując powiązane miesiące i lata, a YD zwraca różnicę dat w dniach, ignorując odpowiednie lata.
Przy obliczaniu wieku w latach za pomocą funkcji DATEDIF bardziej znaną składnię można zdefiniować jako:
=DATEDIF(data_urodzenia;konkretna_data;'Y')
Aby obliczyć wiek od daty urodzenia do daty urodzenia, możemy podać aktualną datę w miejscuSpecific_Date. Poza tym zamiast aktualnej daty możemy użyć także funkcji DZIŚ. Oto odpowiedni wzór pozwalający obliczyć wiek osoby w latach do dnia dzisiejszego:
=DATEDIF(data_urodzenia, DZIŚ(), „Y”)
Załóżmy, że w komórce B2 mamy datę urodzenia danej osoby i musimy obliczyć jej aktualny wiek w latach. Wówczas odwołanie do daty urodzenia w ostatnim wzorze możemy wykorzystać w następujący sposób:
=DATA.JEŻELI(B2,DZISIAJ(),'Y')
Czasami zamiast wieku w latach możemy zobaczyć konkretną datę. W takim przypadku musimy przejść do karty Strona główna > menu rozwijanego Format liczb > wybrać „Ogólne” zamiast „Data”.
Korzystanie z funkcji YEARFRAC
Inną przydatną metodą obliczania wieku w programie Excel jest użycie funkcji YEARFRAC. Jest to łatwa w obsłudze funkcja Excela, często używana do obliczania wieku w latach. Pomaga nam odczytać wiek od podanej daty urodzenia do określonej daty.
Ogólna składnia do obliczania wieku osoby za pomocą funkcji YEARFRAC jest zdefiniowana w następujący sposób:
=YEARFRAC(Birth_Date,Specific_Date)
Jeśli potrzebujemy obliczyć wiek od urodzenia do dzisiejszej daty, możemy podać aktualną datę w miejsceSpecific_Date. Alternatywnie możemy także połączyć funkcję YEARFRAC z funkcją DZIŚ w następujący sposób:
=YEARFRAC(data_urodzenia, DZIŚ())
Domyślnie powyższa formuła zwraca wyniki w liczbach dziesiętnych. Nie wygląda to dobrze przy obliczaniu wieku danej osoby. Zatem łączymy lub zamykamy formułę w funkcji INT, aby zwrócić odpowiedni wiek jako liczbę całkowitą. Zatem pełny wzór na obliczenie wieku w programie Excel za pomocą funkcji YEARFRAC definiuje się następująco:
=INT(YEARFRAC(data_urodzenia;konkretna_data))
Załóżmy, że mamy aktualną datę w komórce A2 i datę urodzenia danej osoby w komórce B2. W takim przypadku możemy obliczyć wiek tej konkretnej osoby korzystając z poniższego wzoru:
=INT(ROKFRAC(B2,A2))
nazwa miasta w USA
Jeśli do obliczenia wieku w latach użyjemy funkcji YEARFRAC z funkcją DZIŚ, wzór będzie wyglądał następująco:
=INT(ROKFRAC(B2, DZIŚ()))
W połączeniu z funkcją DZIŚ funkcja YEARFRAC zwraca tylko bieżący lub najnowszy wiek w latach.
Korzystanie z funkcji ZAOKR.DÓŁ
Chociaż rzadko używana, możemy również użyć funkcji ZAOKR.DÓŁ do obliczenia wieku w Excelu. Poniżej znajduje się składnia obliczania wieku w programie Excel przy użyciu formuły ZAOKR.DÓŁ:
=ROUNDDOWN((Specific_Date - Birth_Date)/365.25,0)
Ogólnie rzecz biorąc, funkcja ZAOKR.DÓŁ pomaga zaokrąglić miejsca dziesiętne w dół. Jednakże dostosowaliśmy formułę w taki sposób, aby obliczała wiek w latach. We wzorze używamy liczby 365,25 dla roku przestępnego (366 dni w roku), który występuje co cztery lata. Używamy 0 jako ostatniego argumentu funkcji ZAOKR.DÓŁ, aby zignorować miejsca dziesiętne w wieku.
Formuła ROUNDDOWN jest dobrą praktyką obliczania wieku, ale nie jest zalecana, ponieważ nie jest doskonała. Załóżmy, że dziecko nie przeżyło jeszcze żadnego roku przestępnego i wiek obliczamy za pomocą tego wzoru, dzieląc przez 365,25; formuła zwróci nieprawidłowy wiek.
Dzielenie przez średnią liczbę dni w roku również w większości przypadków działa dobrze, co oznacza, że możemy dzielić przez 365 dni zamiast 365,25. Jednak w tym przypadku również występują pewne problemy i czasami dają błędne wyniki. Załóżmy na przykład, że czyjś DOB. to 29 lutego, a bieżąca data to 28 lutego. W takim przypadku, jeśli podzielimy przez 365, wiek uzyskany ze wzoru będzie o jeden dzień starszy. Zatem w tym przypadku musimy podzielić przez 365,25, aby obliczyć datę. Zatem te dwa podejścia nie są doskonałe. Zawsze zaleca się użycie funkcji DATEDIF do obliczenia wieku osoby w programie Excel.
stopy kontra stopa
Załóżmy, że w komórce A2 mamy aktualną datę i czyjeś D.O.B. w komórce B2. W takim przypadku możemy obliczyć wiek tej konkretnej osoby korzystając z poniższego wzoru:
=ZAOKR.W DÓŁ((A2-B2)/365,25,0)
Oprócz tego zamiast określonej daty możemy także użyć funkcji DZIŚ, aby obliczyć wiek danej osoby do aktualnej daty.
Korzystanie z funkcji DZIŚ
Ponieważ wiek najczęściej oblicza się poprzez odjęcie daty urodzenia od daty bieżącej, funkcja DZIŚ w Excelu również w pewnym stopniu pomaga nam w obliczeniu wieku. Podobnie jak formuła ZAOKR.DÓŁ, formuła DZIŚ również nie jest idealna do obliczania czyjegoś wieku w programie Excel.
Załóżmy, że w komórce B2 mamy czyjąś datę urodzenia; możemy zastosować wzór DZIŚ do obliczenia wieku w następujący sposób:
=(DZISIAJ()-B2)/365
W niektórych przypadkach może być czasami konieczne podzielenie przez 365,25 zamiast 365. W tym wzorze pierwsza część (DZISIAJ()-B2) zwykle oblicza różnicę między bieżącą datą a datą urodzenia. Druga część wzoru pomaga podzielić różnicę przez 365, aby uzyskać liczbę lat (tj. wiek w latach).
Niestety, formuła DZIŚ podaje wyniki w postaci ułamków dziesiętnych, podobnie jak funkcja YEARFRAC. Dlatego formułę DZIŚ zamykamy w funkcji INT, aby wyświetlić wiek w pełnych latach lub w najbliższej wartości całkowitej. Zatem ostateczny wzór TOTAL na obliczenie wieku w latach wygląda następująco:
=INT((DZISIAJ()-B2)/365)
Obliczanie wieku w miesiącach
Jak powiedzieliśmy wcześniej, funkcja DATEDIF może pomóc nam obliczyć czyjś wiek w różnych formatach. Możemy więc ponownie użyć tej samej formuły DATEDIF w naszym arkuszu. Musimy jednak zmienić jednostkę we wzorze z „Y” na „M”. Dzięki temu program Excel ma wyświetlić lub zwrócić wiek w miesiącach.
Rozważmy ponownie ten sam przykładowy zestaw danych, w którym w komórce B2 mamy czyjąś datę urodzenia. Musimy obliczyć aktualny wiek w miesiącach. Następnie możemy użyć odniesienia do daty urodzenia we wzorze DATEDIF w następujący sposób:
=DATA.JEŻELI(B2,DZISIAJ(),'M')
Obliczanie wieku w dniach
Obliczanie wieku w dniach staje się łatwe, gdy znamy już składnię funkcji DATEDIF. Podobnie jak w poprzednim przykładzie zmieniliśmy argument jednostkowy z „Y” na „M”, co pomogło nam obliczyć wiek osoby w miesiącach. Podobnie, jeśli zmienimy argument jednostki z „M” na „D”, funkcja zwróci wiek w dniach. Zatem biorąc pod uwagę, że jeśli DOB danej osoby znajduje się w komórce B2, formuła będzie wyglądać następująco:
=DATA.JEŻELI(B2,DZISIAJ(),'D')
Łączne obliczanie wieku w latach, miesiącach i dniach
Jak wspomniano powyżej, obliczenie wieku danej osoby w poszczególnych latach, miesiącach i dniach jest zaskakująco łatwe. Jednak nie zawsze może to wystarczyć. Może się zdarzyć, że będziemy musieli znaleźć lub obliczyć dokładny wiek danej osoby w latach, miesiącach i dniach. W takich przypadkach formuła staje się nieco długa, ale nadal łatwa.
Aby obliczyć dokładny wiek danej osoby w pełnych latach, miesiącach i dniach, musimy użyć trzech różnych funkcji DATEDIF i połączyć je jednocześnie we wzorze. Załóżmy, że jeśli data urodzenia osoby znajduje się w komórce B2, trzy różne funkcje DATEDIF będą wyglądać następująco:
- Aby obliczyć liczbę pełnych lat: =DATA.JEŻELI(B2,DZISIAJ(),'Y')
- Aby obliczyć liczbę pozostałych miesięcy: =DATA.JEŻELI(B2,DZISIAJ(),'YM')
- Aby obliczyć liczbę pozostałych dni: =DATA.JEŻELI(B2,DZISIAJ(),'MD')
Łączymy teraz wszystkie te funkcje DATEDIF za pomocą operatora „&” w następujący sposób:
=DATAJEŻELI(B2,DZISIAJ(),'T')&DATAJEŻELI(B2,DZISIAJ(),'YM')&DATAJEŻELI(B2,DZISIAJ(),'MD')
Chociaż wiek wyrażamy w latach, miesiącach i datach jako pojedynczy ciąg znaków, nie ma to znaczenia. Aby wyniki (lub wiek) były skuteczne lub zrozumiałe, oddzielamy każdą jednostkę przecinkiem i określamy, co oznacza każda wartość. Zatem formuła staje się następująca:
=DATEDIF(B2,TODAY(),'Y') & ' Lata, ' & DATEDIF(B2,TODAY(),'YM') & ' Miesiące, ' & DATEDIF(B2,TODAY(),'MD') & „Dni”
Powyższy obraz pokazuje, że wyniki dotyczące wieku są stosunkowo bardziej znaczące niż wcześniejsze. Jednakże wyświetla również pewne wartości zerowe. Możemy jeszcze bardziej ulepszyć naszą formułę DATEDIF, łącząc ją w trzech różnych instrukcjach IF w celu sprawdzenia i wyeliminowania zer. Zatem ostateczny wzór na wiek w Excelu pozwalający obliczyć aktualny wiek danej osoby w latach, miesiącach i dniach wygląda następująco:
=JEŻELI(DATY.JEŻELI(B2, DZIŚ(),'Y')=0,'',DATAJEŻELI(B2, DZIŚ(),'Y')&' Lata, ')& JEŻELI(DATAJEŻELI(B2, DZIŚ(), 'YM')=0,'',DATEDIF(B2, DZIŚ(),'YM')&' Miesiące, ')& IF(DATEDIF(B2, DZIŚ(),'MD')=0,'',DATEDIF (B2, DZIŚ(), „MD”) i „Dni”)
Na powyższym obrazku widzimy tylko niezerowe wartości wieku osoby. Jednak formuła określa tylko aktualny wiek osoby.
Obliczanie wieku w dowolnej konkretnej/konkretnej dacie
W składni powyższych wzorów omówiliśmy już sposób, który mówi nam, aby znaleźć czyjś wiek w określonym dniu. Na przykład poniższy wzór DATEDIF oblicza wiek osoby w określonym dniu:
=DATEDIF(data_urodzenia;konkretna_data;'Y')
Argument jednostkowy można zmienić w razie potrzeby. W powyższej formule zazwyczaj możemy podać odwołanie do komórki dla obu dat, a wynik pojawi się w komórce docelowej.
Inną typową metodą wykorzystania tego samego wzoru do obliczenia wieku osoby w dowolnym konkretnym dniu będzie podanie żądanej daty bezpośrednio we wzorze. Załóżmy na przykład, że w komórce B2 mamy datę urodzenia danej osoby (30.05.1995) i chcemy poznać wiek tej osoby na dzień 01.01.2021. Zatem możemy użyć funkcji DATA.JEŻELI w połączeniu z funkcją DATA w następujący sposób:
=DATA.JEŻELI(B2;DATA(2021;1;1);'Y')
Na powyższym obrazku używamy funkcji DATE, aby podać konkretną datę bezpośrednio w funkcji DATEDIF, natomiast odwołanie B2 służy do D.O.B.
Poza tym, jeśli chcemy znaleźć datę osoby w latach, miesiącach i dniach w określonym dniu, możemy skorzystać z tej samej koncepcji DATEDIF, omówionej powyżej, w której połączyliśmy trzy funkcje DATEDIF. Musimy jednak zastąpić funkcję DZIŚ() w drugim argumencie żądaną datą.
Tak więc, jeśli data urodzenia danej osoby znajduje się w komórce B2 i musimy obliczyć wiek na 01.01.2021, używamy poniższego wzoru:
=JEŻELI(DATEDIF(B2, '1.01.2021','Y')=0,'',DATEDIF(B2, '1.01.2021','Y')&' Lata, ')& JEŻELI( DATEDIF(B2, '1.01.2021','YM')=0,'',DATEDIF(B2, '1.01.2021','YM')&' Miesiące, ')& JEŻELI(DATEDIF(B2 , '1.01.2021','MD')=0,'',DATEDIF(B2, '1.01.2021','MD')&' Dni')
Zamiast umieszczać w formule konkretną datę, możemy użyć odwołania do komórki dla konkretnej daty, dzięki czemu nasza formuła będzie łatwa do zrozumienia. Załóżmy, że data urodzenia danej osoby znajduje się w komórce B2, a konkretna data, dla której chcemy obliczyć wiek, znajduje się w komórce C2, wówczas elastyczna formuła wieku będzie wyglądać następująco:
=IF(DATEDIF(B2, C2;'Y')=0;'',DATEDIF(B2, C2;'Y')&' Lata, ')& JEŻELI(DATEDIF(B2, C2;'YM')=0 ,'',DATEDIF(B2, C2,'YM')&' Miesiące, ')& JEŻELI(DATEDIF(B2, C2,'MD')=0,'',DATEDIF(B2, C2,'MD')& „Dni”)
