Ringkasan
Contoh ini menunjukkan cara mendapatkan tarikh paling awal dan terkini yang berkaitan dengan projek. Dalam contoh yang ditunjukkan, formula dalam H5 dan I5 adalah:
=MINIFS(data(Start),data(Project),G5) // earliest =MAXIFS(data(End),data(Project),G5) // latest
di mana "data" adalah jadual Excel seperti yang ditunjukkan, dan nama projek di lajur G sepadan dengan lajur B.
Catatan: MINIFS dan MAXIFS hanya tersedia di Excel 365 dan Excel 2019. Dalam versi Excel yang lain, anda boleh menggunakan formula susunan mudah, seperti yang dijelaskan di bawah.
Pengenalan
Tugas di sini adalah untuk mencari tarikh paling awal dan terkini yang berkaitan dengan projek tertentu. Tarikh terawal berasal dari lajur Mula , dan tarikh terkini berasal dari lajur Akhir .
Anda mungkin tergoda untuk menggunakan fungsi pencarian seperti VLOOKUP, XLOOKUP, atau INDEX dan MATCH. Namun, kerana setiap projek mempunyai lebih dari satu entri, dan entri mungkin tidak selalu disusun mengikut tarikh, ini menjadi mencabar.
Pendekatan yang lebih baik adalah menggunakan proses penghapusan: membuang tarikh untuk projek lain, dan bekerja hanya dengan tarikh yang tinggal.
Penjelasan
Fungsi MINIFS mengembalikan nilai angka terkecil yang memenuhi kriteria yang dibekalkan, dan fungsi MAXIFS mengembalikan nilai angka terbesar yang memenuhi kriteria yang dibekalkan.
Seperti COUNTIFS dan SUMIFS, fungsi ini menggunakan rentang / kriteria "pasangan" untuk menerapkan syarat. Untuk kedua formula, kita hanya memerlukan satu syarat: nama projek mesti sama dengan nama di lajur G:
data(Project),G5 // condition
Untuk mendapatkan tarikh mula yang paling awal, kami menggunakan:
=MINIFS(data(Start),data(Project),G5) // earliest date
Di sini, MINIFS mengembalikan nilai minimum di ruangan Mula di mana projek itu sama dengan "Omega" (dari sel G5). Oleh kerana tarikh Excel hanyalah nombor, tarikh minimum adalah sama dengan tarikh paling awal.
Untuk mendapatkan tarikh akhir terkini, kami menggunakan:
=MAXIFS(data(End),data(Project),G5) // latest date
Di sini, MAXIFS mengembalikan nilai maksimum di lajur Akhir di mana projek itu sama dengan "Omega". Seperti di atas, nilai maksimum adalah sama dengan tarikh terkini.
Array formula alternatif
Sekiranya anda tidak mempunyai MINIFS dan MAXIFS, anda boleh menggunakan formula susunan mudah, berdasarkan fungsi MIN dan MAX, untuk mendapatkan hasil yang sama. Untuk tarikh mula terawal:
(=MIN(IF(data(Project)=G5,data(Start))))
Untuk tarikh akhir terkini:
(=MAX(IF(data(Project)=G5,data(End))))
Catatan: kedua-dua formula adalah formula larik dan mesti dimasukkan dengan kawalan + shift + enter, di Excel 2019 atau lebih awal. Dengan Excel 365, anda boleh memasukkan formula secara normal, kerana formula array adalah asli.
Dalam kedua kes tersebut, fungsi IF digunakan untuk "menyaring" nilai tarikh seperti ini:
IF(data(Project)=G5,data(End)) // filter dates by project
Apabila G5 adalah "Omega", JIKA mengembalikan tarikh akhir. Jika tidak, JIKA mengembalikan SALAH. Oleh kerana kami menguji semua nama projek dalam jadual pada masa yang sama, hasilnya adalah pelbagai nilai seperti ini:
(43936;43983;43990;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE)
Nombor siri yang besar adalah tarikh Excel yang berkaitan dengan projek Omega. Nilai lain adalah SALAH, kerana projek ini bukan Omega. Kerana MIN dan MAX diprogramkan untuk mengabaikan nilai logik BENAR dan SALAH, mereka hanya beroperasi pada nilai yang tinggal. MIN mengembalikan tarikh terkecil (paling awal), dan MAX mengembalikan tarikh (terbaru) terbesar.
