Ringkasan
Contoh ini menunjukkan cara mengambil tanggal paling awal dan terbaru yang terkait dengan sebuah proyek. Pada contoh yang ditampilkan, rumus di H5 dan I5 adalah:
=MINIFS(data(Start),data(Project),G5) // earliest =MAXIFS(data(End),data(Project),G5) // latest
di mana "data" adalah tabel Excel seperti yang ditunjukkan, dan nama proyek di kolom G cocok dengan kolom B.
Catatan: MINIFS dan MAXIFS hanya tersedia di Excel 365 dan Excel 2019. Di versi Excel lainnya, Anda dapat menggunakan rumus array sederhana, seperti yang dijelaskan di bawah ini.
pengantar
Tugas di sini adalah menemukan tanggal paling awal dan terbaru yang terkait dengan proyek tertentu. Tanggal paling awal berasal dari kolom Mulai , dan tanggal terbaru berasal dari kolom Akhir .
Anda mungkin tergoda untuk menggunakan fungsi pencarian seperti VLOOKUP, XLOOKUP, atau INDEX dan MATCH. Namun, karena setiap proyek memiliki lebih dari satu entri, dan entri tidak selalu diurutkan berdasarkan tanggal, ini menjadi tantangan.
Pendekatan yang lebih baik adalah dengan menggunakan proses eliminasi: buang tanggal untuk proyek lain, dan kerjakan hanya dengan tanggal yang tersisa.
Penjelasan
Fungsi MINIFS mengembalikan nilai numerik terkecil yang memenuhi kriteria yang disediakan, dan fungsi MAXIFS mengembalikan nilai numerik terbesar yang memenuhi kriteria yang disediakan.
Seperti COUNTIFS dan SUMIFS, fungsi ini menggunakan "pasangan" rentang / kriteria untuk menerapkan ketentuan. Untuk kedua rumus, kita hanya membutuhkan satu syarat: nama proyek harus sama dengan nama di kolom G:
data(Project),G5 // condition
Untuk mendapatkan tanggal mulai paling awal, kami menggunakan:
=MINIFS(data(Start),data(Project),G5) // earliest date
Di sini, MINIFS mengembalikan nilai minimum di kolom Mulai di mana proyek sama dengan "Omega" (dari sel G5). Karena tanggal Excel hanya berupa angka, tanggal minimum sama dengan tanggal paling awal.
Untuk mendapatkan tanggal akhir terbaru, kami menggunakan:
=MAXIFS(data(End),data(Project),G5) // latest date
Di sini, MAXIFS mengembalikan nilai maksimum di kolom Akhir di mana proyek sama dengan "Omega". Seperti di atas, nilai maksimumnya sama dengan tanggal terakhir.
Alternatif rumus array
Jika Anda tidak memiliki MINIFS dan MAXIFS, Anda dapat menggunakan rumus array sederhana, berdasarkan fungsi MIN dan MAX, untuk mendapatkan hasil yang sama. Untuk tanggal mulai paling awal:
(=MIN(IF(data(Project)=G5,data(Start))))
Untuk tanggal akhir terbaru:
(=MAX(IF(data(Project)=G5,data(End))))
Catatan: kedua rumus adalah rumus array dan harus dimasukkan dengan control + shift + enter, di Excel 2019 atau yang lebih lama. Dengan Excel 365, Anda bisa memasukkan rumus secara normal, karena rumus array adalah asli.
Dalam kedua kasus tersebut, fungsi IF digunakan untuk "memfilter" nilai tanggal seperti ini:
IF(data(Project)=G5,data(End)) // filter dates by project
Jika G5 adalah "Omega", IF mengembalikan tanggal akhir. Jika tidak, IF mengembalikan FALSE. Karena kami menguji semua nama proyek dalam tabel pada saat yang sama, hasilnya adalah larik nilai seperti ini:
(43936;43983;43990;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE)
Nomor seri yang besar adalah tanggal Excel yang terkait dengan proyek Omega. Nilai lainnya SALAH, karena proyeknya bukan Omega. Karena MIN dan MAX diprogram untuk mengabaikan nilai logika TRUE dan FALSE, mereka hanya beroperasi pada nilai yang tersisa. MIN mengembalikan tanggal terkecil (paling awal), dan MAX mengembalikan tanggal terbesar (terbaru).
