MS Excel alebo Excel je široko používaný tabuľkový procesor so širokou škálou vstavaných nástrojov a funkcií. Pomáha nám zaznamenávať rôzne množiny údajov a vykonávať v nich výpočty s viacerými bunkami. Príkladom klasického výpočtu je výpočet rozdielu medzi dvoma dátumami. Neznie to však ako užitočný výpočet. Rovnaká technika je však do istej miery kľúčová pri hľadaní alebo výpočte veku niekoho alebo niečoho v programe Microsoft Excel.
Okrem zistenia veku ľubovoľnej osoby môžeme pomocou rovnakého konceptu vypočítať trvanie akéhokoľvek projektu, roky existencie akejkoľvek spoločnosti, počet rokov, ktoré uplynuli medzi zadanými dátumami atď.
V tomto návode diskutujeme o rôznych metódach alebo riešeniach, ako vypočítať vek v Exceli. Výukový program nám pomáha naučiť sa počítať vek ako počet celých rokov, mesiacov a dní k aktuálnemu dátumu alebo k akémukoľvek konkrétnemu dátumu.
Ako môžeme vypočítať vek v Exceli?
Hoci v Exceli neexistuje žiadna špecifická funkcia na výpočet veku, máme mnoho spôsobov, ako vypočítať vek v rôznych scenároch odlišne. Musíme však poznať pôvodný dátum narodenia jednotlivca (D.O.B.). Potom dodáme dátum narodenia v spojení s funkciami Excelu, konkrétne DATEDIF a TODAY, na výpočet veku alebo rozlíšenie medzi dátumami. Poďme teraz diskutovať o bežných scenároch zisťovania veku:
Výpočet veku v rokoch
Pri výpočte veku akejkoľvek osoby zvyčajne zvažujeme niekoľko faktorov. Napríklad, možno budeme musieť vypočítať vek v rokoch, mesiacoch, dňoch alebo všetkom dohromady. Výpočet veku osoby v rokoch je však najčastejšie používanou úlohou Excelu.
Za predpokladu, že poznáme dátum narodenia, niekoľko rôznych funkcií v Exceli nám môže pomôcť vypočítať vek osoby v rokoch. Pozrime sa podrobne na každý užitočný vekový vzorec:
Pomocou funkcie DATEDIF
Funkcia DATEDIF v Exceli je najbežnejšou funkciou na výpočet veku osoby. Je to ľahko použiteľná, vstavaná a najvhodnejšia funkcia, ktorá akceptuje dátum narodenia ako vstupný dátum a vracia vek osoby ako výstupné údaje.
Iným spôsobom funkcia DATEDIF zvyčajne konvertuje dátum narodenia na vek zodpovedajúcej osoby. Hlavnou výhodou funkcie DATEDIF je, že ju možno použiť na výpočet veku v rôznych formátoch, ako sú len roky, iba mesiace, iba dni alebo kombinovaná forma rokov, mesiacov a dátumov atď.
Na rozdiel od ostatných funkcií Excelu sa funkcia DATEDIF nezobrazí v rýchlom zozname funkcií. To znamená, že nevidíme funkciu DATEDIF ako návrh, keď ju začneme písať do bunky Excelu po znamienku rovnosti. Funkcia však funguje vo všetkých verziách Excelu. Na použitie funkcie DATEDIF v Exceli musíme poznať syntax a požadované argumenty.
Všeobecná syntax funkcie DATEDIF je definovaná takto:
=DATEDIF(start_date, end_date, unit)
Ako je tu znázornené, funkcia vyžaduje nasledujúce tri argumenty:
Je dôležité poznamenať, že Y, M a D vracajú čísla v celých rokoch, mesiacoch a dňoch. Na rozdiel od toho YM vracia iba rozdiel dátumov v mesiacoch, pričom ignoruje zodpovedajúce dni a roky, MD vracia iba rozdiel dátumov v dňoch, pričom ignoruje súvisiace mesiace a roky, a YD vracia rozdiel dátumov v dňoch, pričom ignoruje zodpovedajúce roky.
Pri výpočte veku v rokoch pomocou funkcie DATEDIF možno definovať známejšiu syntax ako:
=DATEDIF(Dátum_narodenia,Konkrétny_dátum,'Y')
Ak chcete vypočítať vek od dátumu narodenia do dátumu, môžeme zadať aktuálny dátum na mieste Specific_Date. Okrem toho môžeme namiesto aktuálneho dátumu použiť aj funkciu DNES. Tu je príslušný vzorec na výpočet veku osoby v rokoch do dnešného dátumu:
=DATEDIF(Dátum_narodenia,DNES(),'Y')
Predpokladajme, že v bunke B2 máme dátum narodenia osoby a potrebujeme vypočítať aktuálny vek v rokoch. Potom môžeme použiť odkaz na dátum narodenia v poslednom vzorci nasledujúcim spôsobom:
=DATEDIF(B2,DNES(),'Y')
Niekedy môžeme vidieť konkrétny dátum namiesto veku v rokoch. V takom prípade musíme prejsť na kartu Domov > rozbaľovacia ponuka Formát čísla > vybrať „Všeobecné“ namiesto „Dátum“.
Používanie funkcie YEARFRAC
Ďalšou užitočnou metódou na výpočet veku v Exceli je použitie funkcie YEARFRAC. Je to jednoducho použiteľná funkcia Excelu a často sa používa na výpočet veku v rokoch. Pomáha nám to získať vek od daného dátumu narodenia po zadaný dátum.
Všeobecná syntax na výpočet veku osoby pomocou funkcie YEARFRAC je definovaná takto:
=YEARFRAC(Birth_Date,Specific_Date)
Ak potrebujeme vypočítať vek od narodenia po dnešný dátum, môžeme namiesto konkrétneho_dátumu zadať aktuálny dátum. Alternatívne môžeme tiež spojiť funkciu YEARFRAC s funkciou TODAY nasledujúcim spôsobom:
=YEARFRAC(Dátum_narodenia,DNES())
Predvolene vyššie uvedený vzorec vracia výsledky v desatinných číslach. Pri výpočte veku človeka to nevyzerá dobre. Takže vzorec skombinujeme alebo uzavrieme do funkcie INT, aby sme vrátili zodpovedajúci vek ako celé číslo. Úplný vzorec na výpočet veku v Exceli pomocou funkcie YEARFRAC je teda definovaný nižšie:
=INT(YEARFRAC(Dátum_narodenia,Konkrétny_dátum))
Predpokladajme, že máme aktuálny dátum v bunke A2 a dátum narodenia osoby v bunke B2. V takom prípade môžeme vypočítať vek konkrétnej osoby pomocou nasledujúceho vzorca:
=INT(YEARFRAC(B2,A2))
sieťovú architektúru
Ak na výpočet veku v rokoch použijeme funkciu YEARFRAC s funkciou TODAY, vzorec vyzerá takto:
=INT(YEARFRAC(B2, DNES()))
V kombinácii s funkciou DNES funkcia YEARFRAC vráti iba aktuálny alebo posledný vek v rokoch.
Pomocou funkcie ROUNDDOWN
Aj keď sa používa zriedka, môžeme použiť aj funkciu ROUNDDOWN na výpočet veku v Exceli. Nasleduje syntax na výpočet veku v Exceli pomocou vzorca ROUNDDOWN:
=ROUNDDOWN((Specific_Date - Birth_Date)/365.25,0)
Vo všeobecnosti funkcia ROUNDDOWN pomáha zaokrúhliť desatinné miesta nadol. Vzorec sme však upravili tak, že počíta vek v rokoch. Vo vzorci používame 365,25 pre priestupný rok (366 dní v roku), ktorý prichádza každé štyri roky. 0 používame ako posledný argument vo funkcii ROUNDDOWN, aby sme ignorovali desatinné miesta vo veku.
Vzorec ROUNDDOWN je dobrou praxou na výpočet veku, ale neodporúča sa, pretože nie je bezchybný. Predpokladajme, že dieťa ešte neprežilo žiadny priestupný rok a vypočítame vek pomocou tohto vzorca vydelením číslom 365,25; vzorec vráti nesprávny vek.
Delenie priemerným počtom dní v roku tiež funguje vo väčšine prípadov dobre, čo znamená, že môžeme deliť 365 dňami namiesto 365,25. Tento prípad má však aj určité problémy a niekedy prináša nesprávne výsledky. Predpokladajme napríklad, že niečí D.O.B. je 29. február a aktuálny dátum je 28. február. V takom prípade, ak vydelíme číslom 365, vek získaný pomocou vzorca bude o jeden deň starší. Na výpočet dátumu v tomto prípade musíme vydeliť 365,25. Tieto dva prístupy teda nie sú dokonalé. Vždy sa odporúča použiť funkciu DATEDIF na výpočet veku osoby v Exceli.
Predpokladajme, že máme aktuálny dátum v bunke A2 a niekoho D.O.B. v bunke B2. V takom prípade môžeme vypočítať vek konkrétnej osoby pomocou nasledujúceho vzorca:
=ROUNDDOWN((A2-B2)/365,25,0)
Okrem toho môžeme namiesto Špecifického_dátumu použiť aj funkciu DNES na výpočet veku osoby k aktuálnemu dátumu.
Pomocou funkcie DNES
Keďže vek sa najčastejšie počíta odčítaním dátumu narodenia od aktuálneho dátumu, do určitej miery nám pomáha pri výpočte veku aj funkcia DNES v Exceli. Podobne ako vzorec ROUNDDOWN, ani vzorec DNES nie je ideálny na výpočet niekoho veku v Exceli.
Predpokladajme, že v bunke B2 máme dátum narodenia niekoho iného; môžeme použiť vzorec DNES na výpočet veku nasledujúcim spôsobom:
=(DNES()-B2)/365
Na základe určitých prípadov môže byť niekedy potrebné deliť 365,25 namiesto 365. V tomto vzorci prvá časť (TODAY()-B2) zvyčajne vypočítava rozdiel medzi aktuálnym dátumom a dátumom narodenia. Druhá časť vzorca pomáha vydeliť rozdiel číslom 365 a získať tak počet rokov (t. j. vek v rokoch).
Bohužiaľ, vzorec TODAY tu poskytuje výsledky v desatinných číslach, rovnako ako funkcia YEARFRAC. Preto v rámci funkcie INT prikladáme vzorec DNES, aby sme zobrazili vek v celých rokoch alebo najbližšiu celočíselnú hodnotu. Takže konečný TOTAL vzorec na výpočet veku v rokoch vyzerá takto:
=INT((DNES()-B2)/365)
Výpočet veku v mesiacoch
Ako sme už povedali, funkcia DATEDIF nám môže pomôcť vypočítať vek niekoho iného v rôznych formátoch. Takže v našom pracovnom hárku môžeme znova použiť rovnaký vzorec DATEDIF. Musíme však zmeniť jednotku vo vzorci z „Y“ na „M“. Toto povie Excelu, aby zobrazil alebo vrátil vek v mesiacoch.
Znova zvážme tú istú vzorovú množinu údajov, kde máme v bunke B2 dátum narodenia niekoho iného. Potrebujeme vypočítať aktuálny vek v mesiacoch. Potom môžeme použiť odkaz na dátum narodenia vo vzorci DATEDIF nasledujúcim spôsobom:
=DATEDIF(B2,DNES(),'M')
Výpočet veku v dňoch
Výpočet veku v dňoch je jednoduchý, keď už poznáme syntax funkcie DATEDIF. Rovnako ako v predchádzajúcom príklade sme zmenili argument jednotky z „Y“ na „M“, čo nám pomohlo vypočítať vek osoby v mesiacoch. Podobne, ak zmeníme argument jednotky z 'M' na 'D', funkcia vráti vek v dňoch. Ak teda vezmeme do úvahy, že ak je D.O.B. je v bunke B2, vzorec bude nasledujúci:
=DATEDIF(B2,DNES(),'D')
Výpočet veku v rokoch, mesiacoch a dňoch spolu
Ako už bolo uvedené vyššie, výpočet veku človeka v jednotlivých rokoch, mesiacoch a dňoch je prekvapivo jednoduchý. To však nemusí vždy stačiť. Môžu nastať prípady, keď musíme nájsť alebo vypočítať presný vek osoby v rokoch, mesiacoch a dňoch. V takýchto prípadoch sa vzorec stáva trochu zdĺhavým, ale stále jednoduchým.
Na výpočet presného veku osoby v celých rokoch, mesiacoch a dňoch musíme použiť tri rôzne funkcie DATEDIF a spojiť ich do vzorca súčasne. Predpokladajme, že ak je dátum narodenia osoby v bunke B2, tri rôzne funkcie DATEDIF budú nasledovné:
- Na výpočet počtu celých rokov: =DATEDIF(B2,DNES(),'Y')
- Na výpočet počtu zostávajúcich mesiacov: =DATEDIF(B2,TODAY(),'YM')
- Na výpočet počtu zostávajúcich dní: =DATEDIF(B2,TODAY(),'MD')
Teraz skombinujeme všetky tieto funkcie DATEDIF pomocou operátora „&“ nasledujúcim spôsobom:
=DATEDIF(B2,DNES(),'Y')&DATEDIF(B2,DNES(),'YM')&DATEDIF(B2,DNES(),'MD')
Aj keď dostaneme vek v rokoch, mesiacoch a dátumoch ako jeden reťazec, nemá to význam. Aby boli výsledky (alebo vek) efektívne alebo zrozumiteľné, oddeľujeme každú jednotku pomocou čiarky a určujeme, čo každá hodnota znamená. Takže vzorec sa stáva týmto:
=DATEDIF(B2,DNES(),'Y') & 'roky, ' & DATEDIF(B2,DNES(),'YM') & 'mesiace, ' & DATEDIF(B2,DNES(),'MD') & ' Dni'
Vyššie uvedený obrázok ukazuje, že vekové výsledky sú porovnateľne zmysluplnejšie ako tie predchádzajúce. Zobrazuje však aj nejaké nulové hodnoty. Náš vzorec DATEDIF môžeme ďalej vylepšiť tak, že ho skombinujeme s tromi rôznymi príkazmi IF na kontrolu a odstránenie núl. Takže konečným vzorcom Excelu na výpočet aktuálneho veku v rokoch, mesiacoch a dňoch je tento:
=IF(DATEDIF(B2, TODAY(),'Y')=0,'',DATEDIF(B2, TODAY(),'Y')&' Years, ')& IF(DATEDIF(B2, TODAY(), 'YM')=0,'',DATEDIF(B2, TODAY(),'YM')&' Mesiace, ')& IF(DATEDIF(B2, TODAY(),'MD')=0,'',DATEDIF (B2, DNES(),'MD')&'Dni')
Na obrázku vyššie vidíme len nenulové hodnoty veku osoby. Vzorec však zistí iba aktuálny vek osoby.
konštrukčný vzor staviteľa
Výpočet veku v ktorýkoľvek konkrétny/konkrétny dátum
V syntaxi vzorcov vyššie sme už diskutovali o spôsobe, ktorý nám hovorí, že máme nájsť niečí vek k určitému dátumu. Napríklad vzorec DATEDIF uvedený nižšie vypočíta vek osoby k určitému dátumu:
=DATEDIF(Dátum_narodenia,Konkrétny_dátum,'Y')
Argument jednotky je možné podľa potreby zmeniť. Vo vyššie uvedenom vzorci môžeme zvyčajne poskytnúť odkaz na bunku pre oba dátumy a výsledok sa zobrazí v cieľovej bunke.
Ďalšou typickou metódou na použitie rovnakého vzorca na výpočet veku osoby k akémukoľvek konkrétnemu dátumu je dodanie požadovaného dátumu priamo do vzorca. Predpokladajme napríklad, že v bunke B2 máme niekoho D.O.B (30. 5. 1995) a chceme vedieť vek danej osoby k 1. 1. 2021. Takže môžeme použiť funkciu DATEDIF v spojení s funkciou DATE nasledujúcim spôsobom:
=DATEDIF(B2,DATE(2021;1;1);'Y')
Na obrázku vyššie používame funkciu DATE na poskytnutie konkrétneho dátumu priamo vo funkcii DATEDIF, zatiaľ čo odkaz B2 sa používa pre D.O.B.
Okrem toho, ak potrebujeme nájsť dátum osoby v rokoch, mesiacoch a dňoch k určitému dátumu, môžeme použiť rovnaký vyššie diskutovaný koncept DATEDIF, kde sme spojili tri funkcie DATEDIF. Musíme však nahradiť funkciu TODAY() v druhom argumente nami požadovaným dátumom.
Ak je teda dátum narodenia osoby v bunke B2 a my potrebujeme vypočítať vek k 01.01.2021, použijeme nasledujúci vzorec:
=IF(DATEDIF(B2, '1/1/2021','Y')=0,'',DATEDIF(B2, '1/1/2021','Y')&' Years, ')& IF( DATEDIF(B2, '1/1/2021','YM')=0,'',DATEDIF(B2, '1/1/2021','YM')&' mesiace, ')& IF(DATEDIF(B2) , '1/1/2021','MD')=0,'',DATEDIF(B2; '1/1/2021';'MD')&' Dni')
Namiesto uvedenia konkrétneho dátumu do vzorca môžeme pre konkrétny dátum použiť odkaz na bunku a urobiť náš vzorec ľahko zrozumiteľným. Predpokladajme, že dátum narodenia osoby je v bunke B2 a konkrétny dátum, ku ktorému chceme vypočítať vek, je v bunke C2, potom bude vzorec flexibilného veku vyzerať takto:
=IF(DATEDIF(B2, C2,'Y')=0,'',DATEDIF(B2, C2,'Y')&' Years, ')& IF(DATEDIF(B2, C2,'YM')=0 ,'',DATEDIF(B2, C2,'YM')&' Mesiace, ')& IF(DATEDIF(B2, C2,'MD')=0,'',DATEDIF(B2, C2,'MD')& ' Dni')
