Fungsi tersembunyi Excel DATEDIF

Artikel ini membawakan anda pengetahuan yang berkaitan tentang excel terutamanya isu yang berkaitan dengan fungsi tersembunyi, terutamanya fungsi DATEDIF Mari kita lihat bersama-sama.

Cadangan pembelajaran berkaitan: tutorial excel

Terdapat jenis fungsi dalam Excel yang dipanggil fungsi tersembunyi senarai fungsi Mereka tidak boleh ditemui, dan tiada arahan yang berkaitan walaupun dalam fail bantuan Microsoft, tetapi ia bukan sahaja berkuasa, tetapi juga digunakan secara meluas dalam kerja.

DATEDIF ialah fungsi tersembunyi ajaib yang wujud dalam Excel, tetapi ia tidak boleh ditemui dalam fail bantuan Excel.

Hari ini kita akan melihat fungsi tersembunyi ajaib ini.

1. Penjelasan terperinci tentang fungsi DATEDIF

Sintaks asas DATEDIF ialah:

DATEDIF (tarikh mula, tarikh tamat, jenis selang)

Antaranya, parameter tarikh_mula dan tarikh_akhir ialah dua tarikh, dan yang pertama mestilah tidak lebih besar daripada yang terakhir.

unit mempunyai 6 parameter berikut, yang digunakan untuk mengira perbezaan yang berbeza, seperti yang ditunjukkan dalam jadual di bawah.

Parameter DATEDIF

Dalam ejaan harian, sesetengah orang akan terlepas D di tengah-tengah nama fungsi dan menukarnya kepada DATEIF Ini salah, dan apabila input salah, Sistem Excel tidak akan digesa.

Kata fungsi ini mempunyai cara yang mudah untuk diingati: DATEDIF disingkatkan sebagai Date Different, yang diterjemahkan sebagai tarikh berbeza.

2. Kira selang tahun, bulan dan hari antara dua tarikh

Seperti yang ditunjukkan dalam rajah di bawah, ini ialah penggunaan konvensional DATEDIF Maksud sebenar enam parameter ini digabungkan dengan rajah Terangkan data dalam.

Penggunaan umum DATEDIF

Mula-mula, masukkan formula berikut dalam sel D16 dan D24 masing-masing, dan salinnya ke sel D21 dan D29, masing-masing. Kira perbezaan antara parameter berbeza:

=DATEDIF(E16,F16,C16)

=DATEDIF(E24,F24,C24)

D24 sel, parameter "Y " Melihat pada 2017 dan 2020 sahaja, perbezaan tahun sepatutnya 3, tetapi dari 2017/7/28 hingga 2020/2/8, 2 tahun berlalu dahulu ke 2019/7/28, dan ia masih belum mencapai 2020/ yang diperlukan. 2/ 8. Jika satu tahun berlalu, ia akan menjadi 2020/7/28, yang akan melebihi tarikh tamat, jadi hasilnya kembali 2 dan tidak boleh kembali 3.

Sel D25, parameter "M", 30 bulan selepas 28/7/2017, ia mencapai 28/1/2020, dan kemudian 1 bulan kemudian, ia mencapai 2020/2/28, yang melebihi tarikh tamat 2020/2/8, jadi hasilnya hanya boleh 30, bukan 31. Kita mesti memahami sepenuhnya maksud perkataan "keseluruhan" dalam "bilangan tahun keseluruhan" dan "bilangan bulan keseluruhan".

Sel D26, parameter "D", adalah bersamaan dengan menolak terus dua tarikh untuk mengira perbezaan hari.

Sel D27, parameter "MD", pengiraan ini mengabaikan bulan dan tahun, yang bersamaan dengan menarik tarikh_mula ke tarikh terdekat sebelum tarikh_akhir. Maksudnya, dekatkan 2017/7/28 dengan tarikh terdekat dengan tarikh 28 sebelum 2020/2/8, iaitu 2020/1/28, dan kemudian hitung jarak antara 2020/1/28 dan 2020 /2/8 Perbezaan hari ialah 11 hari.

Sel D28, parameter "YM", abaikan hari dan tahun untuk mengira bilangan bulan penuh, yang bersamaan dengan membawa 28/7/2017 kepada 28 Julai yang paling hampir sebelum 2020/2/8 2019/7/28, dan kemudian hitung perbezaan "keseluruhan" bulan antaranya dan 2020/2/8, iaitu 6 bulan.

Sel D29, parameter "YD", mengabaikan perbezaan hari dalam pengiraan tahun, yang bersamaan dengan menarik tarikh_mula ke tarikh terdekat pada bulan dan hari yang sama sebelum tarikh_akhir. Maksudnya, dekatkan 2017/7/28 kepada 2019/7/28, dan kemudian hitung bilangan perbezaan hari antara 2019/7/28 dan 2020/2/8, iaitu 195 hari.

Apabila menggunakan parameter "MD" dan "YD" untuk mengira perbezaan hari, disebabkan kewujudan tahun lompat, kadangkala akan ada perbezaan satu hari daripada nilai ideal mempengaruhi penggunaan harian kita.

3. Perbezaan antara tahun, bulan dan hari keseluruhan

Seperti gambar di bawah, 28/7/2017 hingga 27/7/2020 dan 28/7/2017 hingga 2020 /7 disenaraikan /28 perbandingan, walaupun tarikh_akhir hanya berbeza 1 hari, hasilnya agak berbeza. Prinsip pengiraan adalah sama, dan anda perlu memahami maksud perkataan "keseluruhan".

Perbezaan antara sepanjang tahun, bulan dan hari

Bagaimanakah anda perlu mengingati begitu banyak parameter? Pertama, anda mesti mengetahui fungsi fungsi ini dan memahami prinsip pengiraan setiap parameter. Jika anda sering perlu mengira tarikh di tempat kerja, anda boleh mencetaknya dan melekatkannya di sebelah meja anda untuk rujukan segera.

4. Kes: Pengiraan tempoh perkhidmatan

Andaikan hari ini ialah 28/7/2019, dan tarikh setiap pekerja mula bekerja adalah seperti yang ditunjukkan dalam lajur C dalam rajah di bawah, maka apakah adakah tempoh perkhidmatan setiap orang? Tempoh perkhidmatan boleh dinyatakan sebagai m tahun dan n bulan.

Pengiraan senioriti

boleh dilakukan langkah demi langkah. Mula-mula hitung bilangan "tahun penuh" dan kemudian bilangan "bulan keseluruhan". Apabila mengira bilangan bulan, anda perlu ambil perhatian bahawa nilai maksimum bilangan bulan tidak akan melebihi 11, kerana 12 bulan akan menjadi satu tahun, iaitu, kewujudan tahun mesti diabaikan untuk mengira bilangan bulan. . Jadi parameter mana yang hendak digunakan untuk pengiraan?

Seperti yang dapat dilihat daripada jadual perbandingan parameter DATEDIF yang dinyatakan dalam bahagian sebelumnya, parameter "Y" digunakan untuk mengira bilangan tahun keseluruhan, dan parameter "YM" digunakan untuk mengira bilangan bulan keseluruhan tanpa mengira tahun. Jadi formula fungsi sel D51 boleh ditulis sebagai:

(seret kiri dan kanan untuk melihat formula lengkap)

=DATEDIF(C51,"2019/7/28″,"Y ”)&” tahun “&DATEDIF(C51,”2019/7/28″,”YM”)&” Bulan”

Mari kita lihat julat sel D54:D56 hasil pengiraan berbeza dengan cuma beza 1 hari. Oleh itu, apabila menggunakan DATEDIF, anda mesti sentiasa mempunyai konsep "keseluruhan" dalam fikiran.

Selain itu, parameter Y, M dan D dalam DATEDIF ialah kedua-dua huruf besar dan huruf kecil.

5. Kes: Pengiraan Hari Cuti Tahunan

"Peraturan Cuti Tahunan Bergaji untuk Pekerja" menetapkan bahawa jika pekerja telah bekerja selama 1 tahun tetapi jumlah keseluruhannya kurang daripada 10 tahun, cuti tahunan ialah 5 hari; Jika pekerja mempunyai pengalaman kurang daripada 20 tahun, cuti tahunan ialah 10 hari jika pekerja mempunyai pengalaman melebihi 20 tahun, cuti tahunan ialah 15 hari;

Begitu juga, andaikan hari ini 28/7/2019, berapa hari cuti tahunan setiap pekerja?

Malah, soalan ini lebih mudah daripada kes di bahagian sebelumnya Anda hanya perlu mengetahui bilangan tahun setiap pekerja telah bekerja.

Seperti yang ditunjukkan dalam rajah di bawah, masukkan formula berikut dalam sel D66 untuk mengira bilangan tahun bekerja setiap pekerja:

=DATEDIF(C66,DATE(2019,7,28) ," Y”)

Pengiraan hari cuti tahunan

Saya menekankan sekali lagi bahawa jika anda menggunakan input pintasan untuk menyatakan tarikh dalam formula, anda mesti menambah petikan berganda, seperti di atas "DATEDIF(C51,"2019/7/28″,"Y")" dalam bahagian, jika anda tidak dapat menguasai penggunaan petikan berganda, hanya gunakan fungsi DATE dengan cara yang berdisiplin untuk memastikan bahawa tidak akan ada kesilapan.

Berdasarkan bilangan tahun dalam lajur D, anda boleh mengira bilangan hari cuti tahunan berkanun Masukkan formula berikut dalam sel E66:

=LOOKUP(D66,{0,. 1,10,20},{ 0,5,10,15})

Cadangan pembelajaran berkaitan: tutorial cemerlang

Atas ialah kandungan terperinci Fungsi tersembunyi Excel DATEDIF. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!