Salah satu tugas khas untuk pengguna Microsoft Excel. Kami memiliki dua rentang tanggal dari jenis "awal-akhir". Tantangannya adalah untuk menentukan apakah rentang ini tumpang tindih dan, jika demikian, berapa hari.
Berpotongan atau tidak?
Mari kita mulai dengan memecahkan pertanyaan apakah ada perpotongan interval pada prinsipnya? Misalkan kita memiliki tabel shift kerja untuk karyawan seperti ini:
Terlihat jelas bahwa shift kerja Yaroslav dan Elena berpotongan, tetapi bagaimana cara menghitungnya tanpa menggunakan jadwal kalender dan kontrol visual? Fungsinya akan membantu kita SUMPRODUK (SUMPRODUK).
Mari masukkan kolom lain ke dalam tabel kita dengan rumus yang menghasilkan nilai boolean TRUE jika tanggal berpotongan:
Berapa hari penyeberangan?
Jika pada dasarnya tidak mudah untuk memahami apakah interval kita berpotongan atau tidak, tetapi untuk mengetahui dengan tepat berapa hari tepatnya jatuh ke dalam persimpangan, maka tugasnya menjadi lebih rumit. Logikanya, perlu untuk "memompa" sebanyak 3 situasi berbeda dalam satu formula:
- interval tidak tumpang tindih
- salah satu interval sepenuhnya menyerap yang lain
- interval berpotongan sebagian
Dari waktu ke waktu, saya melihat implementasi pendekatan ini oleh pengguna lain menggunakan banyak fungsi IF bersarang, dll.
Faktanya, semuanya dapat dilakukan dengan indah menggunakan fungsi MEDIAN (MEDIA) dari kategori Statistik.
Jika kita secara kondisional menetapkan awal interval pertama sebagai N1, dan akhir untuk K1, dan awal detik N2 dan berakhir untuk K2, maka secara umum rumus kita dapat ditulis sebagai:
=MEDIA(N1;K1+ 1;K2+1)-MEDIAN(N1;K1+ 1;N2)
Kompak dan elegan, bukan?
- Bagaimana sebenarnya Excel bekerja dengan tanggal? Bagaimana cara menghitung jumlah kalender atau hari kerja antar tanggal?
- Bagaimana cara membuat jadwal kalender (liburan, pelatihan, shift…) di Excel menggunakan pemformatan bersyarat?
- Memeriksa satu atau lebih kondisi dengan fungsi IF (IF)