MS Excel jeb Excel ir plaši izmantota izklājlapu programmatūra ar plašu iebūvēto rīku un funkciju klāstu. Tas palīdz mums reģistrēt dažādas datu kopas un veikt tajās aprēķinus, izmantojot vairākas šūnas. Klasiskā aprēķina piemērs ir divu datumu starpības aprēķināšana. Tomēr tas neizklausās pēc lietderīga aprēķina. Taču tas pats paņēmiens ir ļoti svarīgs, lai programmā Microsoft Excel atrastu vai aprēķinātu kādas personas vecumu.
Papildus jebkuras personas vecuma noteikšanai mēs varam izmantot to pašu jēdzienu, lai aprēķinātu jebkura projekta ilgumu, jebkura uzņēmuma pastāvēšanas gadus, gadu skaitu, kas pagājis starp norādītajiem datumiem utt.
Šajā apmācībā mēs apspriežam dažādas metodes vai risinājumus vecuma aprēķināšanai programmā Excel. Apmācība palīdz mums iemācīties aprēķināt vecumu kā pilnu gadu, mēnešu un dienu skaitu pašreizējā datumā vai jebkurā konkrētā datumā.
Kā mēs varam aprēķināt vecumu programmā Excel?
Lai gan programmā Excel nav noteiktas funkcijas vecuma aprēķināšanai, mums ir daudz veidu, kā dažādos scenārijos vecumu aprēķināt atšķirīgi. Tomēr mums ir jāzina personas sākotnējais dzimšanas datums (D.O.B.). Pēc tam mēs piegādājam dzimšanas datumu kopā ar Excel funkcijām, īpaši DATEDIF un TODAY, lai aprēķinātu vecumu vai atšķirtu datumus. Tagad apspriedīsim izplatītākos vecuma noteikšanas scenārijus:
Vecuma aprēķināšana gados
Aprēķinot jebkuras personas vecumu, mēs parasti ņemam vērā vairākus faktorus. Piemēram, mums var būt jāaprēķina vecums gados, mēnešos, dienās vai visos kopā. Tomēr personas vecuma aprēķināšana gados ir Excel visbiežāk izmantotais uzdevums.
Pieņemot, ka mēs zinām dzimšanas datumu, dažas dažādas funkcijas programmā Excel var palīdzēt mums aprēķināt personas vecumu gados. Detalizēti apspriedīsim katru noderīgo vecuma formulu:
Izmantojot funkciju DATEDIF
Funkcija DATEDIF programmā Excel ir visizplatītākā funkcija personas vecuma aprēķināšanai. Tā ir viegli lietojama, iebūvēta un vispiemērotākā funkcija, kas pieņem dzimšanas datumu kā ievades datumu un atgriež personas vecumu kā izejas datus.
Citā veidā funkcija DATEDIF parasti pārvērš dzimšanas datumu attiecīgās personas vecumā. Funkcijas DATEDIF galvenā priekšrocība ir tā, ka to var izmantot vecuma aprēķināšanai dažādos formātos, piemēram, tikai gados, tikai mēnešos, tikai dienās vai gadu, mēnešu un datumu kombinētajā formā utt.
Atšķirībā no citām Excel funkcijām funkcija DATEDIF neparādās ātrajā funkciju sarakstā. Tas nozīmē, ka mēs neredzam funkciju DATEDIF kā ieteikumu, kad sākam to rakstīt Excel šūnā pēc vienādības zīmes. Tomēr funkcija darbojas visās Excel versijās. Mums ir jāzina sintakse un nepieciešamie argumenti, lai programmā Excel izmantotu funkciju DATEDIF.
Funkcijas DATEDIF vispārējā sintakse ir definēta šādi:
=DATEDIF(start_date, end_date, unit)
Kā parādīts šeit, funkcijai ir nepieciešami šādi trīs argumenti:
Ir svarīgi atzīmēt, ka Y, M un D atgriež skaitļus attiecīgi veselos gados, mēnešos un dienās. Turpretim YM atgriež tikai datuma starpību mēnešos, ignorējot atbilstošās dienas un gadus, MD atgriež tikai datuma starpību dienās, ignorējot saistītos mēnešus un gadus, un YD atgriež datuma starpību dienās, ignorējot atbilstošos gadus.
Aprēķinot vecumu gados, izmantojot funkciju DATEDIF, pazīstamāko sintaksi var definēt kā:
=DATEDIF(dzimšanas_datums, noteiktais_datums,'Y')
Lai aprēķinātu vecumu no dzimšanas datuma līdz datumam, mēs varam norādīt pašreizējo datumu vietā Specific_Date. Turklāt pašreizējā datuma vietā varam izmantot funkciju TODAY. Šeit ir attiecīgā formula, lai aprēķinātu personas vecumu gados līdz šodienas datumam:
=DATEDIF(dzimšanas_datums,ŠODIEN(),'Y')
Pieņemsim, ka šūnā B2 ir personas dzimšanas datums, un mums ir jāaprēķina pašreizējais vecums gados. Pēc tam mēs varam izmantot dzimšanas datuma atsauci pēdējā formulā šādā veidā:
=DATEDIF(B2,ŠODIEN(),'Y')
Dažreiz mēs varam redzēt konkrētu datumu, nevis vecumu gados. Šādā gadījumā mums ir jāpārvietojas cilnē Sākums > Ciparu formāts nolaižamajā izvēlnē > atlasiet Vispārīgi, nevis Datums.
Funkcijas YEARFRAC izmantošana
Vēl viena noderīga metode vecuma aprēķināšanai programmā Excel ietver YEARFRAC funkcijas izmantošanu. Tā ir viegli lietojama Excel funkcija, un to bieži izmanto, lai aprēķinātu vecumu gados. Tas palīdz mums izgūt vecumu no dotā dzimšanas datuma līdz noteiktam datumam.
Vispārējā sintakse personas vecuma aprēķināšanai, izmantojot YEARFRAC funkciju, ir definēta šādi:
=YEARFRAC(Birth_Date,Specific_Date)
Ja mums ir jāaprēķina vecums no dzimšanas līdz šodienas datumam, mēs varam norādīt pašreizējo datumu Specific_Date vietā. Alternatīvi, mēs varam arī pievienoties funkcijai YEARFRAC ar funkciju TODAY šādā veidā:
=YEARFRAC(dzimšanas_datums,ŠODIEN())
Pēc noklusējuma iepriekš minētā formula atgriež rezultātus decimālskaitļos. Aprēķinot cilvēka vecumu, tas neizskatās labi. Tātad, mēs apvienojam vai iekļaujam formulu funkcijā INT, lai atgrieztu atbilstošo vecumu kā veselu skaitli. Tādējādi visa formula vecuma aprēķināšanai programmā Excel, izmantojot YEARFRAC funkciju, ir definēta šādi:
=INT(YEARFRAC(dzimšanas_datums, konkrētais_datums))
Pieņemsim, ka šūnā A2 ir pašreizējais datums un šūnā B2 ir personas dzimšanas datums. Tādā gadījumā mēs varam aprēķināt konkrētās personas vecumu, izmantojot šādu formulu:
=INT(GADU FRAC(B2,A2))
abstraktā klase pret interfeisu
Ja mēs izmantojam YEARFRAC funkciju ar funkciju TODAY, lai aprēķinātu vecumu gados, formula izskatās šādi:
=INT(YEARFRAC(B2, ŠODIEN()))
Apvienojot to ar funkciju TODAY, funkcija YEARFRAC atgriež tikai pašreizējo vai pēdējo vecumu gados.
Funkcijas ROUNDDOWN izmantošana
Lai gan to izmanto reti, mēs varam izmantot arī funkciju ROUNDDOWN, lai aprēķinātu vecumu programmā Excel. Tālāk ir norādīta sintakse vecuma aprēķināšanai programmā Excel, izmantojot ROUNDDOWN formulu:
=ROUNDDOWN((Specific_Date - Birth_Date)/365.25,0)
Parasti funkcija ROUNDDOWN palīdz noapaļot decimāldaļas uz leju. Tomēr mēs esam pielāgojuši formulu tā, lai tā aprēķina vecumu gados. Formulā mēs izmantojam 365.25 garajam gadam (366 dienas gadā), kas nāk ik pēc četriem gadiem. Mēs izmantojam 0 kā pēdējo argumentu funkcijā ROUNDDOWN, lai ignorētu vecuma zīmes aiz komata.
ROUNDDOWN formula ir laba prakse vecuma aprēķināšanai, taču tā nav ieteicama, jo tā nav nevainojama. Pieņemsim, ka bērns vēl nav nodzīvojis nevienu garo gadu, un mēs aprēķinām vecumu, izmantojot šo formulu, dalot ar 365,25; formula atgriezīs nepareizo vecumu.
Arī dalīšana ar vidējo dienu skaitu gadā darbojas labi vairumā gadījumu, kas nozīmē, ka mēs varam dalīt ar 365 dienām, nevis 365,25. Tomēr šajā gadījumā ir arī dažas problēmas, un dažkārt tiek iegūti nepareizi rezultāti. Piemēram, pieņemsim, ka kāds ir D.O.B. ir 29. februāris, un pašreizējais datums ir 28. februāris. Tādā gadījumā, ja dalīsim ar 365, vecums, kas iegūts pēc formulas, būs par vienu dienu vecāks. Tātad, lai šajā gadījumā aprēķinātu datumu, mums ir jādala ar 365,25. Tādējādi šīs divas pieejas nav ideālas. Personas vecuma aprēķināšanai programmā Excel vienmēr ieteicams izmantot funkciju DATEDIF.
Pieņemsim, ka šūnā A2 ir pašreizējais datums un kāda D.O.B. šūnā B2. Tādā gadījumā mēs varam aprēķināt konkrētās personas vecumu, izmantojot šādu formulu:
=APAĻA((A2-B2)/365.25,0)
Turklāt, lai aprēķinātu personas vecumu līdz pašreizējam datumam, mēs varam izmantot arī funkciju TODAY, nevis Specific_Date.
Funkcijas TODAY izmantošana
Tā kā vecumu visbiežāk aprēķina, no pašreizējā datuma atņemot dzimšanas datumu, Excel funkcija TODAY arī zināmā mērā palīdz mums aprēķināt vecumu. Tāpat kā ROUNDDOWN formula, arī TODAY formula nav ideāla, lai programmā Excel aprēķinātu kāda cilvēka vecumu.
Pieņemsim, ka šūnā B2 ir kāda cilvēka dzimšanas datums; formulu ŠODIEN vecuma aprēķināšanai varam pielietot šādi:
=(ŠODIEN()-B2)/365
Dažos gadījumos mums var būt nepieciešams dalīt ar 365,25, nevis 365. Šajā formulā pirmā daļa (TODAY()-B2) parasti aprēķina starpību starp pašreizējo datumu un dzimšanas datumu. Formulas otrā daļa palīdz dalīt starpību ar 365, lai iegūtu gadu skaitu (t.i., vecumu gados).
Diemžēl TODAY formula šeit sniedz rezultātus decimāldaļās, tāpat kā funkcija YEARFRAC. Tāpēc funkcijā INT iekļaujam formulu TODAY, lai parādītu vecumu veselos gados vai tuvākajā veselā skaitļa vērtībā. Tātad galīgā KOPĀ formula vecuma aprēķināšanai gados ir šāda:
=INT((ŠODIEN()-B2)/365)
Vecuma aprēķināšana mēnešos
Kā jau teicām iepriekš, funkcija DATEDIF var mums palīdzēt aprēķināt kāda cilvēka vecumu dažādos formātos. Tātad, mēs varam atkal izmantot to pašu DATEDIF formulu savā darblapā. Tomēr mums ir jāmaina formulas vienība no “Y” uz “M”. Tas liek programmai Excel parādīt vai atgriezt vecumu mēnešos.
Pārskatīsim to pašu datu kopas piemēru, kur šūnā B2 ir norādīts kāda cilvēka dzimšanas datums. Mums ir jāaprēķina pašreizējais vecums mēnešos. Pēc tam mēs varam izmantot dzimšanas datuma atsauci formulā DATEDIF šādā veidā:
=DATEDIF(B2,ŠODIEN(),'M')
Vecuma aprēķināšana dienās
Vecuma aprēķināšana dienās kļūst vienkārša, ja mēs jau zinām funkcijas DATEDIF sintaksi. Tāpat kā iepriekšējā piemērā, mēs mainījām vienības argumentu no “Y” uz “M”, kas mums palīdzēja aprēķināt personas vecumu mēnešos. Līdzīgi, ja mainām vienības argumentu no “M” uz “D”, funkcija atgriezīs vecumu dienās. Tādējādi, ņemot vērā, ka, ja personas D.O.B. ir šūnā B2, formula būs šāda:
=DATEDIF(B2,ŠODIEN(),'D')
25 c līdz k
Vecuma aprēķināšana gados, mēnešos un dienās kopā
Kā minēts iepriekš, cilvēka vecuma aprēķināšana atsevišķos gados, mēnešos un dienās ir pārsteidzoši vienkārša. Tomēr ar to ne vienmēr var pietikt. Var būt gadījumi, kad mums vispār jāatrod vai jāaprēķina precīzs cilvēka vecums gados, mēnešos un dienās. Šādos gadījumos formula kļūst nedaudz gara, bet tomēr vienkārša.
Lai aprēķinātu personas precīzu vecumu veselos gados, mēnešos un dienās, mums ir jāizmanto trīs dažādas DATEDIF funkcijas un vienlaikus tās jāapvieno formulā. Pieņemsim, ka, ja personas dzimšanas datums ir šūnā B2, trīs dažādās DATEDIF funkcijas būs šādas:
- Lai aprēķinātu pilno gadu skaitu: =DATEDIF(B2,ŠODIEN(),'Y')
- Lai aprēķinātu atlikušo mēnešu skaitu: =DATEDIF(B2,ŠODIEN(),'YM')
- Lai aprēķinātu atlikušo dienu skaitu: =DATEDIF(B2,ŠODIEN(),'MD')
Tagad mēs apvienojam visas šīs DATEDIF funkcijas, izmantojot operatoru '&' šādā veidā:
=DATEDIF(B2,ŠODIEN(),'Y')&DATEDIF(B2,ŠODIEN(),'YM')&DATEDIF(B2,ŠODIEN(),'MD')
Lai gan vecumu mēs iegūstam gados, mēnešos un datumos kā vienu virkni, tam nav nozīmes. Lai rezultāti (vai vecums) būtu efektīvi vai saprotami, mēs atdalām katru vienību, izmantojot komatu, un nosakām katras vērtības nozīmi. Tātad formula kļūst par šādu:
=DATEDIF(B2,ŠODIEN(),'Y') & ' Gadi, ' & DATEDIF(B2,ŠODIEN(),'YM') & ' Mēneši, ' & DATEDIF(B2,ŠODIEN(),'MD') & 'dienas'
Iepriekš redzamajā attēlā redzams, ka vecuma rezultāti ir salīdzinoši nozīmīgāki nekā iepriekšējie. Tomēr tas parāda arī dažas nulles vērtības. Mēs varam vēl vairāk uzlabot savu DATEDIF formulu, apvienojot to trīs dažādos IF priekšrakstos, lai pārbaudītu un izslēgtu nulles. Tātad galīgā Excel vecuma formula, lai aprēķinātu kāda cilvēka pašreizējo vecumu gados, mēnešos un dienās, kļūst par šādu:
=IF(DATEDIF(B2, TODAY(),'Y')=0,'',DATEDIF(B2, TODAY(),'Y')&' gadi, ')& IF(DATEDIF(B2, TODAY(), 'YM')=0,'',DATEDIF(B2, TODAY(),'YM')&' Mēneši, ')& IF(DATEDIF(B2, TODAY(),'MD')=0,'',DATEDIF (B2, TODAY(),'MD')&'Dienas')
Iepriekš redzamajā attēlā redzamas tikai personas vecuma vērtības, kas nav nulles. Tomēr formula atrod tikai personas pašreizējo vecumu.
Vecuma aprēķināšana jebkurā noteiktā/konkrētā datumā
Iepriekš minētajā formulu sintaksē mēs jau apspriedām veidu, kā noteikt kāda cilvēka vecumu noteiktā datumā. Piemēram, tālāk norādītā DATEDIF formula aprēķina personas vecumu noteiktā datumā:
=DATEDIF(dzimšanas_datums, noteiktais_datums,'Y')
Vienības argumentu var mainīt pēc vajadzības. Iepriekš minētajā formulā mēs parasti varam norādīt šūnas atsauci abiem datumiem, un rezultāts tiks parādīts mērķa šūnā.
Vēl viena tipiska metode, kā izmantot to pašu formulu, lai aprēķinātu personas vecumu jebkurā konkrētā datumā, ir norādīt vēlamo datumu tieši formulā. Piemēram, pieņemsim, ka šūnā B2 ir kāda cilvēka D.O.B (30.05.1995.) un mēs vēlamies uzzināt personas vecumu 1.01.2021. Tātad, mēs varam izmantot funkciju DATEDIF kopā ar funkciju DATE šādā veidā:
=DATEDIF(B2,DATE(2021;1;1),'Y')
Iepriekš redzamajā attēlā mēs izmantojam funkciju DATE, lai norādītu konkrētu datumu tieši funkcijā DATEDIF, savukārt atsauce B2 tiek izmantota D.O.B.
Bez tam, ja mums ir jāatrod personas datums gados, mēnešos un dienās noteiktā datumā, mēs varam izmantot to pašu iepriekš apspriesto DATEDIF koncepciju, kurā mēs apvienojām trīs DATEDIF funkcijas. Tomēr otrajā argumentā funkcija TODAY() ir jāaizstāj ar vēlamo datumu.
Tātad, ja personas dzimšanas datums ir šūnā B2 un mums ir jāaprēķina vecums uz 01.01.2021., mēs izmantojam šādu formulu:
=IF(DATEDIF(B2, '1/1/2021','Y')=0,'',DATEDIF(B2, '1/1/2021','Y')&' gadi, ')& IF( DATUMS(B2, '1/1/2021','YM')=0,'', DATEDIF(B2, '1/1/2021','YM')&' Mēneši, ')& IF(DATEDIF(B2) , '1/1/2021','MD')=0,'',DATEDIF(B2, '1/1/2021','MD')&' Days')
Tā vietā, lai formulā ievietotu konkrētu datumu, mēs varam izmantot konkrēta datuma šūnas atsauci un padarīt formulu viegli saprotamu. Pieņemsim, ka personas dzimšanas datums ir šūnā B2 un konkrētais datums, kurā mēs vēlamies aprēķināt vecumu, ir šūnā C2, tad elastīgā vecuma formula būs šāda:
=IF(DATEDIF(B2, C2,'Y')=0,'',DATEDIF(B2, C2,'Y')&' gadi, ')& IF(DATEDIF(B2, C2,'YM')=0 ,'',DATEDIF(B2, C2,'YM')&' Mēneši, ')& IF(DATEDIF(B2, C2,'MD')=0,'',DATEDIF(B2, C2,'MD')& 'Dienas')
