Cara Mengira Kes berdasarkan Tarikh Mula dan Tamat

Dalam banyak situasi, kita perlu mengira kes yang jatuh dalam setahun berdasarkan tarikh mula dan tamat. Sebagai contoh, terdapat banyak kontrak dalam syarikat yang bermula dan berakhir pada tarikh yang berbeza.

Anda hanya boleh mempunyai kemungkinan berikut untuk semua kontrak pada 2015 apabila mempertimbangkan tarikh mula dan tamat.

  • Mulakan selepas 31 Dis, tidak dikira dalam;
  • Bermula antara 1 Jan dan 31 Dis, dan berakhir selepas 31 Dis, kira dalam;
  • Bermula antara 1 Jan dan 31 Dis, dan berakhir sebelum 31 Dis, kira dalam;
  • Bermula sebelum 1 Jan dan berakhir antara 1 Jan dan 31 Dis, kira dalam;
  • Bermula sebelum 1 Jan dan berakhir selepas 31 Dis, kira dalam;
  • Bermula sebelum 1 Jan dan berakhir sebelum 1 Jan, tidak dikira.

Untuk dapat mengira semua kontrak yang pernah wujud dalam setahun dari 1 Jan 2015 hingga 31 Dis 2015 berdasarkan tarikh mula dan tamat, kita perlu mendapatkan 4 kemungkinan dengan garis hijau.



Sila gunakan formula berikut dan tukar tarikh mula dan tamat kepada anda sendiri.

=IF(OR(AND(A2>=DATEVALUE('2015/01/01'), A2<=DATEVALUE('2015/12/31'), AND(B2>=DATEVALUE('2015/01/01' ), B2<=DATEVALUE('2015/12/31'), DAN(A2<=DATEVALUE('2015/01/01'), B2>=DATEVALUE('2015/12/31')), ' 2015', '')

Di mana A2 ialah tarikh Mula dan B2 ialah Tarikh Tamat;

  • ' DAN(A2>=DATEVALUE('2015/01/01'), A2<=DATEVALUE('2015/12/31') ' adalah untuk mengetahui bahawa tarikh mula adalah dari 2015-01-01 hingga 2015-12-31;
  • ' DAN(B2>=DATEVALUE('2015/01/01'), B2<=DATEVALUE('2015/12/31')) ' adalah untuk mengetahui bahawa tarikh tamat adalah dari 2015-01-01 hingga 2015-12-31;
  • ' DAN(A2DATEVALUE('2015/12/31')) ' adalah untuk mengetahui yang bermula sebelum 2015-01-01 dan selesai selepas 2015-12-31.

Formula ini berfungsi tetapi terlalu panjang. Jika anda melihat lukisan di atas dengan teliti, anda akan mendapati semua kontrak dalam setahun (atau mana-mana tempoh) mempunyai tarikh mula pada atau sebelum 31 Disember 2015 dan semua tarikh tamat adalah pada atau selepas 1 Januari 2015. Perkara berikut formula juga akan melakukan kerja tetapi ia adalah sangat singkat.

=IF(DAN(A2<=DATEVALUE('2015/12/31'),B2>=DATEVALUE('2015/01/01'),'2015','')

Di mana A2 ialah sel dengan tarikh mula dan B2 ialah sel dengan tarikh tamat.

Selepas anda melabelkan setiap kontrak, anda boleh mengira jumlah dengan mudah dengan menggunakan fungsi COUNTIF . Dalam contoh di atas, kiraan kontrak pada 2015:

=COUNTIF(D2:D7,'2015')

Keputusan akan kembali kepada 4.

Microsoft Excel