Tutorial menunjukkan cara yang berbeza untuk mendapatkan umur dari hari jadi di Excel. Anda akan mempelajari segelintir formula untuk mengira umur sebagai beberapa tahun yang lengkap, mendapat umur tepat pada tahun, bulan dan hari pada tarikh hari ini atau tarikh tertentu.

Tiada fungsi khas untuk mengira umur dalam Excel, namun terdapat beberapa cara yang berbeza untuk menukar tarikh lahir hingga umur. Tutorial ini akan menjelaskan kelebihan dan kelemahan setiap cara, menunjukkan cara membuat formula pengiraan usia yang sempurna dalam Excel dan tweak untuk menyelesaikan beberapa tugas tertentu.

Cara mengira umur dari tarikh lahir di excel

Dalam kehidupan seharian, soalan " Berapa umur anda? " Biasanya membayangkan jawapan yang menunjukkan berapa tahun anda telah hidup. Di Microsoft Excel, anda boleh membuat formula untuk mengira umur yang tepat dalam bulan, hari, jam dan bahkan minit. Tetapi mari kita menjadi tradisional, dan belajar bagaimana mengira umur dari DOB pada tahun pertama.

Formula Excel Asas untuk umur bertahun -tahun

Bagaimana anda biasanya mengetahui usia seseorang? Hanya dengan menolak tarikh lahir dari tarikh semasa. Formula umur konvensional ini juga boleh digunakan dalam Excel.

Dengan mengandaikan tarikh lahir berada dalam sel B2, formula untuk mengira umur dalam tahun -tahun seperti berikut:

=(TODAY()-B2)/365

Bahagian pertama formula (hari ini ()-b2) mengembalikan perbezaan antara tarikh semasa dan tarikh lahir adalah hari, dan kemudian anda membahagikan nombor itu sebanyak 365 untuk mendapatkan bilangan tahun.

Formula ini jelas dan mudah ingat, bagaimanapun, terdapat masalah kecil. Dalam kebanyakan kes, ia mengembalikan nombor perpuluhan seperti yang ditunjukkan dalam tangkapan skrin di bawah.

Untuk memaparkan bilangan tahun yang lengkap, gunakan fungsi int untuk pusingan perpuluhan ke integer terdekat:

=INT((TODAY()-B2)/365)

Kekurangan: Menggunakan formula umur ini dalam Excel menghasilkan hasil yang cukup tepat, tetapi tidak sempurna. Membahagikan dengan purata bilangan hari dalam setahun berfungsi dengan baik sepanjang masa, tetapi kadang -kadang ia mendapat usia yang salah. Sebagai contoh, jika seseorang dilahirkan pada 29 Februari dan hari ini adalah 28 Februari, formula akan membuat seseorang satu hari lebih tua.

Sebagai alternatif, anda boleh membahagikan 365.25 dan bukannya 365 sejak setiap tahun keempat mempunyai 366 hari. Walau bagaimanapun, pendekatan ini tidak sempurna sama ada. Sebagai contoh, jika anda mengira umur kanak -kanak yang belum hidup melalui tahun lompat, membahagikan 365.25 menghasilkan hasil yang salah.

Secara keseluruhannya, mengurangkan tarikh lahir dari tarikh semasa berfungsi hebat dalam kehidupan normal, tetapi bukan pendekatan yang ideal dalam Excel. Selanjutnya dalam tutorial ini, anda akan mempelajari beberapa fungsi khas yang mengira usia tanpa henti tanpa mengira tahun ini.

Kirakan umur dari tarikh lahir dengan fungsi tahunfrac

Cara yang lebih dipercayai untuk menukar DOB ke usia dalam Excel menggunakan fungsi tahunan yang mengembalikan pecahan tahun ini, iaitu bilangan hari keseluruhan antara dua tarikh.

Sintaks fungsi tahunan adalah seperti berikut:

Tahunfrac (start_date, end_date, [asas])

Dua hujah pertama adalah jelas dan tidak memerlukan penjelasan tambahan. Asas adalah hujah pilihan yang mentakrifkan asas kiraan hari untuk digunakan.

Untuk membuat formula usia yang sempurna, berikan nilai berikut kepada fungsi tahunfrac:

• Start_date - tarikh lahir.
• End_date - hari ini () berfungsi untuk mengembalikan tarikh hari ini.
• Asas - Gunakan Asas 1 yang memberitahu Excel untuk membahagikan bilangan sebenar hari sebulan dengan bilangan hari sebenar setahun.

Memandangkan di atas, formula Excel untuk mengira umur dari tarikh lahir adalah seperti berikut:

Tahunfrac ( tarikh lahir , hari ini (), 1)

Dengan mengandaikan tarikh lahir berada dalam sel B2, formula mengambil bentuk berikut:

=YEARFRAC(B2, TODAY(), 1)

Seperti dalam contoh terdahulu, hasil fungsi tahunfrac juga merupakan nombor perpuluhan. Untuk membetulkannya, gunakan fungsi bulat dengan 0 dalam hujah terakhir kerana anda tidak mahu tempat perpuluhan.

Jadi, inilah formula tahunan yang lebih baik untuk mengira usia dalam Excel:

=ROUNDDOWN(YEARFRAC(B2, TODAY(), 1), 0)

Hitung usia dalam excel dengan penjagaan

Satu lagi cara untuk menukar tarikh lahir hingga usia dalam Excel menggunakan fungsi DADIF:

DISEDIF (start_date, end_date, unit)

Fungsi ini boleh mengembalikan perbezaan antara dua tarikh dalam pelbagai unit masa seperti tahun, bulan dan hari, bergantung kepada nilai yang anda berikan dalam argumen unit :

• Y - Mengembalikan bilangan tahun lengkap antara tarikh permulaan dan akhir.
• M - Mengembalikan bilangan bulan lengkap antara tarikh.
• D - Mengembalikan bilangan hari antara kedua -dua tarikh.
• YM - Mengembalikan bulan , mengabaikan hari dan tahun.
• MD - Mengembalikan perbezaan hari , mengabaikan bulan dan tahun.
• YD - Mengembalikan perbezaan hari , mengabaikan tahun.

Oleh kerana kami berhasrat untuk mengira umur bertahun -tahun , kami menggunakan unit "y":

DateF ( tarikh lahir , hari ini (), "y")

Dalam contoh ini, DOB berada dalam sel B2, dan anda merujuk sel ini dalam formula umur anda:

=DATEDIF(B2, TODAY(), "y")

Tiada fungsi pembulatan tambahan yang diperlukan dalam kes ini kerana formula DateF dengan unit "Y" mengira bilangan tahun penuh:

Cara Mendapatkan Umur Dari Hari Lahir pada Tahun, Bulan Dan Hari

Seperti yang telah anda lihat, mengira umur sebagai bilangan tahun penuh yang telah hidup adalah mudah, tetapi ia tidak selalu mencukupi. Jika anda ingin mengetahui usia yang tepat, iaitu berapa tahun, bulan dan hari terdapat antara tarikh lahir seseorang dan tarikh semasa, tulis 3 fungsi yang berbeza:

1. Untuk mendapatkan bilangan tahun: =DATEDIF(B2, TODAY(), "Y")
2. Untuk mendapatkan bilangan bulan: =DATEDIF(B2, TODAY(), "YM")
3. Untuk mendapatkan bilangan hari: =DATEDIF(B2,TODAY(),"MD")

Di mana B2 adalah tarikh lahir.

Dan kemudian, menggabungkan fungsi di atas dalam formula tunggal, seperti ini:

=DATEDIF(B2,TODAY(),"Y") & DATEDIF(B2,TODAY(),"YM") & DATEDIF(B2,TODAY(),"MD")

Formula di atas mengembalikan 3 nombor (tahun, bulan, dan hari) yang digabungkan dalam satu rentetan teks, seperti yang ditunjukkan dalam tangkapan skrin di bawah:

Tidak masuk akal, uh? Untuk menjadikan hasilnya lebih bermakna, memisahkan nombor dengan koma dan tentukan apa maksud setiap nilai:

=DATEDIF(B2,TODAY(),"Y") & " Years, " & DATEDIF(B2,TODAY(),"YM") & " Months, " & DATEDIF(B2,TODAY(),"MD") & " Days"

Hasilnya kelihatan lebih baik sekarang:

Formula berfungsi hebat, tetapi anda boleh memperbaikinya lebih jauh dengan menyembunyikan nilai sifar. Untuk ini, tambahkan 3 jika pernyataan yang memeriksa 0, satu setiap setiap hari:

=IF(DATEDIF(B2, TODAY(),"y")=0,"",DATEDIF(B2, TODAY(),"y")&" years, ")& IF(DATEDIF(B2, TODAY(),"ym")=0,"",DATEDIF(B2, TODAY(),"ym")&" months, ")& IF(DATEDIF(B2, TODAY(),"md")=0,"",DATEDIF(B2, TODAY(),"md")&" days")

Tangkapan skrin berikut menunjukkan formula umur Excel akhir dalam tindakan - ia kembali umur dalam tahun, bulan, dan hari, hanya memaparkan nilai bukan sifar :

Petua. Jika anda sedang mencari formula Excel untuk mengira umur dalam tahun dan bulan , ambil formula di atas dan keluarkan blok terakhir jika (penjara ()) yang mengira hari.

Formula khusus untuk mengira usia dalam excel

Formula pengiraan usia generik yang dibincangkan di atas bekerja hebat dalam kebanyakan kes. Walau bagaimanapun, dalam beberapa situasi, anda mungkin memerlukan sesuatu yang sangat spesifik. Sudah tentu, tidak mungkin untuk menampung setiap dan setiap senario, tetapi contoh -contoh berikut akan memberi anda beberapa idea tentang bagaimana anda boleh tweak formula umur bergantung pada tugas tertentu anda.

Cara mengira usia pada tarikh tertentu di excel

Sekiranya anda ingin mengetahui umur seseorang pada tarikh tertentu, gunakan formula umur yang dibincangkan di atas, tetapi gantikan fungsi hari ini () dalam hujah ^ke -2 dengan tarikh tertentu.

Dengan mengandaikan tarikh lahir berada di B1, formula berikut akan kembali umur pada 1 Januari 2020:

=DATEDIF(B1, "1/1/2020","Y") & " Years, " & DATEDIF(B1, "1/1/2020","YM") & " Months, " & DATEDIF(B1, "1/1/2020", "MD") & " Days"

Untuk menjadikan formula umur anda lebih fleksibel, anda boleh memasukkan tarikh dalam beberapa sel dan merujuk sel dalam formula anda:

=DATEDIF(B1, B2,"Y") & " Years, "& DATEDIF(B1,B2,"YM") & " Months, "&DATEDIF(B1,B2, "MD") & " Days"

Di mana B1 adalah DOB, dan B2 adalah tarikh yang anda mahu mengira umur.

Hitung umur pada tahun tertentu

Formula ini berguna dalam situasi apabila tarikh lengkap untuk dikira tidak ditakrifkan, dan anda hanya tahu tahun ini.

Katakan anda sedang bekerja dengan pangkalan data perubatan, dan matlamat anda adalah untuk mengetahui usia pesakit pada masa mereka menjalani peperiksaan perubatan yang terakhir.

Dengan mengandaikan tarikh kelahiran berada dalam lajur B bermula dengan baris 3, dan tahun peperiksaan perubatan terakhir adalah dalam lajur C, formula pengiraan umur pergi seperti berikut:

=DATEDIF(B3,DATE(C3, 1, 1),"y")

Kerana tarikh tepat peperiksaan perubatan tidak ditakrifkan, anda menggunakan fungsi tarikh dengan argumen tarikh dan bulan sewenang -wenangnya, contohnya tarikh (C3, 1, 1).

Fungsi tarikh mengekstrak tahun dari Cell B3, membuat tarikh lengkap menggunakan nombor bulan dan hari yang anda dibekalkan (1-Jan dalam contoh ini), dan melepasi tarikh tersebut kepada DISTEDIF. Hasilnya, anda mendapat umur pesakit pada 1 Januari tahun tertentu:

Cari tarikh apabila seseorang mencapai usia tahun

Andaikan rakan anda dilahirkan pada 8 Mac 1978. Bagaimana anda tahu pada tarikh apa dia melengkapkan umur 50 tahun? Biasanya, anda hanya akan menambah 50 tahun ke tarikh lahir seseorang. Dalam Excel, anda melakukan perkara yang sama menggunakan fungsi tarikh:

=DATE(YEAR(B2) 50, MONTH(B2), DAY(B2))

Di mana B2 adalah tarikh lahir.

Daripada pengkodan keras bilangan tahun dalam formula, anda boleh merujuk sel tertentu di mana pengguna anda boleh memasukkan beberapa tahun (F1 dalam tangkapan skrin di bawah):

Hitung umur dari hari, bulan dan tahun dalam sel yang berbeza

Apabila tarikh lahir dibahagikan kepada 3 sel yang berbeza (contohnya tahun dalam B3, bulan dalam C3 dan hari di D3), anda boleh mengira usia dengan cara ini:

• Dapatkan tarikh lahir dengan menggunakan Fungsi Tarikh dan Datevalue: DATE(B3,MONTH(DATEVALUE(C3&"1")),D3)
• Menanam formula di atas ke dalam penjara untuk mengira usia dari tarikh lahir pada tahun, bulan, dan hari: =DATEDIF(DATE(B3, MONTH(DATEVALUE(C3&"1")), D3), TODAY(), "y") & " Years, "& DATEDIF(DATE(B3, MONTH(DATEVALUE(C3&"1")), D3),TODAY(), "ym") & " Months, "& DATEDIF(DATE(B3, MONTH(DATEVALUE(C3&"1")), D3), TODAY(), "md") & " Days"

  

Untuk lebih banyak contoh mengira bilangan hari sebelum/selepas tarikh, sila lihat cara mengira hari sejak atau sehingga kini di Excel.

Kalkulator umur di Excel

Sekiranya anda ingin mempunyai kalkulator umur anda sendiri dalam Excel, anda boleh membuatnya dengan menggunakan beberapa formulas yang berbeza yang dijelaskan di bawah. Jika anda lebih suka tidak mencipta semula roda, anda boleh menggunakan kalkulator umur yang dicipta oleh profesional Excel kami.

Cara membuat kalkulator umur di Excel

Sekarang anda tahu bagaimana membuat formula umur di Excel, anda boleh membina kalkulator umur tersuai, contohnya ini:

Nota. Untuk melihat buku kerja tertanam, sila berikan kuki pemasaran.

Apa yang anda lihat di atas adalah lembaran dalam talian Excel yang tertanam, jadi jangan ragu untuk memasuki tarikh lahir anda di sel yang sepadan, dan anda akan mendapat umur anda dalam seketika.

Kalkulator menggunakan formula berikut untuk mengira umur berdasarkan tarikh lahir di sel A3 dan tarikh hari ini.

• Formula dalam B5 mengira umur dalam tahun, bulan, dan hari: =DATEDIF(B2,TODAY(),"Y") & " Years, " & DATEDIF(B2,TODAY(),"YM") & " Months, " & DATEDIF(B2,TODAY(),"MD") & " Days"
• Formula dalam B6 mengira umur dalam bulan: =DATEDIF($B$3,TODAY(),"m")
• Formula dalam B7 mengira umur pada hari: =DATEDIF($B$3,TODAY(),"d")

Jika anda mempunyai pengalaman dengan kawalan bentuk Excel, anda boleh menambah pilihan untuk mengira umur pada tarikh tertentu, seperti yang ditunjukkan dalam tangkapan skrin berikut:

Untuk ini, tambahkan beberapa butang pilihan ( tab pemaju > masukkan > Kawalan borang > butang pilihan ), dan hubungkannya ke beberapa sel. Dan kemudian, tulis formula IF/DISEDIF untuk mendapatkan umur sama ada pada tarikh hari ini atau pada tarikh yang ditentukan oleh pengguna.

Formula ini berfungsi dengan logik berikut:

• Jika kotak pilihan tarikh hari ini dipilih, nilai 1 muncul dalam sel yang dipautkan (i5 dalam contoh ini), dan formula umur mengira berdasarkan tarikh hari ini: IF($I$5=1, DATEDIF($B$3,TODAY(),"Y") & " Years, " & DATEDIF($B$3,TODAY(), "YM") & " Months, " & DATEDIF($B$3, TODAY(), "MD") & " Days")
• Jika butang pilihan tarikh tertentu dipilih dan tarikh dimasukkan dalam sel B7, umur dikira pada tarikh yang ditentukan: IF(ISNUMBER($B$7), DATEDIF($B$3, $B$7,"Y") & " Years, " & DATEDIF($B$3, $B$7,"YM") & " Months, " & DATEDIF($B$3, $B$7,"MD") & " Days", ""))

Akhirnya, sarang fungsi di atas antara satu sama lain, dan anda akan mendapat formula pengiraan usia lengkap (dalam B9):

=IF($I$5=1, DATEDIF($B$3, TODAY(), "Y") & " Years, " & DATEDIF($B$3, TODAY(), "YM") & " Months, " & DATEDIF($B$3, TODAY(), "MD") & " Days", IF(ISNUMBER($B$7), DATEDIF($B$3, $B$7,"Y") & " Years, " & DATEDIF($B$3, $B$7,"YM") & " Months, " & DATEDIF($B$3, $B$7,"MD") & " Days", ""))

Formula dalam B10 dan B11 berfungsi dengan logik yang sama. Sudah tentu, mereka lebih mudah kerana mereka termasuk hanya satu fungsi yang dipedikan untuk kembali umur sebagai bilangan bulan atau hari yang lengkap.

Untuk mengetahui butirannya, saya menjemput anda untuk memuat turun kalkulator umur Excel ini dan menyiasat formula dalam sel B9: B11.

Muat turun Kalkulator Umur untuk Excel

Kalkulator Umur Sedia Untuk Digunakan untuk Excel

Pengguna suite muktamad kami tidak perlu bersusah payah membuat kalkulator umur mereka sendiri di Excel - hanya beberapa klik jauhnya:

1. Pilih sel di mana anda ingin memasukkan formula umur, pergi ke tab Alat AbleBits > Kumpulan Tarikh & Masa , dan klik butang Wizard Tarikh & Masa .

   

2. Wizard Tarikh & Masa akan bermula, dan anda pergi terus ke tab Umur .
3. Pada tab umur , terdapat 3 perkara untuk anda nyatakan:
   • Data kelahiran sebagai rujukan sel atau tarikh dalam format mm/dd/yyyy.
   • Umur pada tarikh hari ini atau tarikh tertentu .
   • Pilih sama ada untuk mengira umur dalam hari, bulan, tahun, atau umur yang tepat.
4. Klik butang Sisipkan Formula .

Selesai!

Formula dimasukkan ke dalam sel yang dipilih seketika, dan anda klik dua kali mengendalikan pemegang untuk menyalinnya ke lajur.

Seperti yang anda perhatikan, formula yang dicipta oleh kalkulator umur Excel kami lebih kompleks daripada yang telah kami bincangkan setakat ini, tetapi ia memenuhi syarat untuk unit masa dan jamak seperti "Hari" dan "Hari".

Sekiranya anda ingin menyingkirkan unit sifar seperti "0 hari", pilih kotak semak tidak tunjukkan kotak sifar :

Sekiranya anda ingin menguji kalkulator umur ini dan juga untuk menemui 60 tambahan penjimatan masa untuk Excel, anda dialu-alukan untuk memuat turun versi percubaan suite muktamad kami di akhir jawatan ini.

Cara menyerlahkan usia tertentu (di bawah atau lebih dari usia tertentu)

Dalam sesetengah situasi, anda mungkin bukan sahaja mengira umur dalam Excel, tetapi juga menyerlahkan sel -sel yang mengandungi umur yang berada di bawah atau lebih dari usia tertentu.

Jika formula pengiraan umur anda mengembalikan bilangan tahun yang lengkap, maka anda boleh membuat peraturan pemformatan bersyarat secara tetap berdasarkan formula mudah seperti ini:

• Untuk menyerlahkan umur bersamaan dengan atau lebih besar daripada 18: = $ c2> = 18
• Untuk menyerlahkan umur di bawah 18: = $ c2

Di mana C2 adalah sel teratas dalam lajur umur (tidak termasuk tajuk lajur).

Tetapi bagaimana jika formula anda memaparkan umur tahun dan bulan, atau pada tahun, bulan dan hari? Dalam kes ini, anda perlu membuat peraturan berdasarkan formula berpatangan yang mengira umur dari tarikh lahir pada tahun -tahun.

Menganggap tarikh lahir berada dalam lajur B bermula dengan baris 2, formula adalah seperti berikut:

• Untuk menyerlahkan usia di bawah 18 (kuning): =DATEDIF($B2, TODAY(),"Y")
• Untuk menyerlahkan usia antara 18 dan 65 (hijau): =AND(DATEDIF($B2, TODAY(),"Y")>=18, DATEDIF($B2, TODAY(),"Y")
• Untuk menyerlahkan usia melebihi 65 (biru): =DATEDIF($B2, TODAY(),"Y")>65

Untuk membuat peraturan berdasarkan formula di atas, pilih sel atau keseluruhan baris yang ingin anda sorot, pergi ke tab Home > Styles Group, dan klik pemformatan bersyarat > Peraturan baru ... > Gunakan formula untuk menentukan sel mana yang hendak dipenuhi .

Langkah -langkah terperinci boleh didapati di sini: Bagaimana membuat peraturan pemformatan bersyarat berdasarkan formula.

Inilah cara anda mengira umur dalam Excel. Saya harap formula mudah untuk anda belajar dan anda akan memberi mereka percubaan dalam lembaran kerja anda. Terima kasih kerana membaca dan berharap dapat melihat anda di blog kami minggu depan!

Muat turun yang ada

Contoh Pengiraan Umur Excel (Fail .xlsx) Versi Ultimate Suite 14 hari berfungsi sepenuhnya (.exe File)

Atas ialah kandungan terperinci Cara mengira usia dalam excel dari hari jadi. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!