Formula generik
(=MAX(IF(TEXT(dates,"ddd")="Mon",values)))
Ringkasan
Untuk mendapatkan nilai maksimum pada hari kerja tertentu (iaitu hari Isnin, Selasa, Rabu, dll.) Anda boleh menggunakan formula susunan mudah berdasarkan fungsi MAX, IF, dan TEXT. Dalam contoh yang ditunjukkan, formula dalam sel F5 adalah:
=MAX(IF(TEXT(dates,"ddd")=F4,values))
Di mana tarikh (B5: B15) dan nilai (C5: C15) dinamakan julat.
Catatan: ini adalah formula larik dan mesti dimasukkan dengan Control + Shift + Enter.
Penjelasan
Berfungsi dari dalam ke luar, fungsi TEXT digunakan untuk mengekstrak nilai hari kerja untuk setiap tarikh:
=TEXT(dates,"ddd")
Ini menghasilkan susunan seperti ini:
("Mon";"Tue";"Wed";"Thu";"Fri";"Mon";"Tue";"Wed";"Thu";"Fri";"Mon")
yang kemudian dibandingkan dengan teks di F4, "Mon". Hasilnya adalah susunan lain, yang hanya mengandungi nilai BENAR dan SALAH:
(TRUE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;TRUE)
Perhatikan setiap BENAR sesuai dengan hari Isnin. Susunan ini dikembalikan di dalam fungsi IF sebagai ujian logik. Ia berfungsi sebagai penapis untuk menyaring nilai pada hari-hari lain dalam seminggu. Hasil akhir dari IF, yang dikembalikan terus ke fungsi MAX, kelihatan seperti ini:
=MAX((85;FALSE;FALSE;FALSE;FALSE;94;FALSE;FALSE;FALSE;FALSE;52))
MAX secara automatik mengabaikan nilai PALSU dan mengembalikan nilai baki tertinggi, 94.
Dengan AGGREGATE
Untuk formula yang agak lebih geeky yang tidak memerlukan kawalan + shift + enter, anda boleh menggunakan fungsi AGGREGATE seperti ini:
=AGGREGATE(14,6,values/(TEXT(dates,"ddd")=F4),1)
Ini adalah formula yang digunakan dalam sel F6 dalam contoh yang ditunjukkan. Di sini kami memberikan AGGREGATE 14 untuk argumen fungsi (BESAR) dan 6 untuk argumen pilihan (mengabaikan ralat). Kemudian kami membina ungkapan logik menggunakan fungsi TEXT untuk memeriksa semua tarikh untuk hari Isnin. Hasil operasi ini adalah susunan nilai BENAR / SALAH, yang menjadi penyebut nilai asal. Semasa digunakan dalam operasi matematik, SALAH menilai sebagai sifar, dan melemparkan # DIV / 0! kesilapan. BENAR menilai sebagai 1 dan mengembalikan nilai asal. Susunan nilai dan ralat terakhir bertindak seperti penapis. AGGREGATE mengabaikan semua kesalahan dan mengembalikan (maksimum) nilai yang masih ada.
MAKSIF
Fungsi MAXIFS, tersedia di Excel Office 365, dapat mengembalikan nilai maksimum menggunakan satu atau lebih kriteria tanpa memerlukan formula array. Namun, MAXIFS adalah fungsi berdasarkan jarak, dan tidak akan membenarkan fungsi lain seperti TEXT memproses nilai dalam rentang kriteria. Anda boleh menambahkan kolum pembantu ke data, menjana nilai hari kerja dengan TEXT, kemudian menggunakan MAXIFS dengan lajur pembantu sebagai rentang kriteria.
