Formula umum
(=MAX(IF(TEXT(dates,"ddd")="Mon",values)))
Ringkasan
Untuk menemukan nilai maksimum pada hari kerja tertentu (yaitu Senin, Selasa, Rabu, dll.) Anda dapat menggunakan rumus larik sederhana berdasarkan fungsi MAX, IF, dan TEXT. Dalam contoh yang diperlihatkan, rumus di sel F5 adalah:
=MAX(IF(TEXT(dates,"ddd")=F4,values))
Di mana tanggal (B5: B15) dan nilai (C5: C15) diberi nama rentang.
Catatan: ini adalah rumus array dan harus dimasukkan dengan Control + Shift + Enter.
Penjelasan
Bekerja dari dalam ke luar, fungsi TEXT digunakan untuk mengekstrak nilai hari kerja untuk setiap tanggal:
=TEXT(dates,"ddd")
Ini menghasilkan array seperti ini:
("Mon";"Tue";"Wed";"Thu";"Fri";"Mon";"Tue";"Wed";"Thu";"Fri";"Mon")
yang kemudian dibandingkan dengan teks di F4, "Mon". Hasilnya adalah larik lain, yang hanya berisi nilai TRUE dan FALSE:
(TRUE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;TRUE)
Perhatikan setiap TRUE sesuai dengan hari Senin. Larik ini dikembalikan di dalam fungsi IF sebagai pengujian logika. Ini bertindak sebagai filter untuk menyaring nilai pada hari-hari lain dalam seminggu. Hasil akhir dari IF, yang dikembalikan langsung ke fungsi MAX, terlihat seperti ini:
=MAX((85;FALSE;FALSE;FALSE;FALSE;94;FALSE;FALSE;FALSE;FALSE;52))
MAX secara otomatis mengabaikan nilai FALSE dan mengembalikan nilai sisa tertinggi, 94.
Dengan AGGREGATE
Untuk rumus yang sedikit lebih culun yang tidak memerlukan control + shift + enter, Anda dapat menggunakan fungsi AGGREGATE seperti ini:
=AGGREGATE(14,6,values/(TEXT(dates,"ddd")=F4),1)
Ini adalah rumus yang digunakan di sel F6 dalam contoh yang ditampilkan. Di sini kami berikan AGREGAT 14 untuk argumen fungsi (LARGE) dan 6 untuk argumen opsi (abaikan kesalahan). Kemudian kami membangun ekspresi logis menggunakan fungsi TEXT untuk memeriksa semua tanggal untuk hari Senin. Hasil dari operasi ini adalah larik nilai TRUE / FALSE, yang menjadi penyebut dari nilai aslinya. Saat digunakan dalam operasi matematika, FALSE mengevaluasi sebagai nol, dan melempar # DIV / 0! kesalahan. TRUE mengevaluasi sebagai 1 dan mengembalikan nilai aslinya. Array terakhir dari nilai dan kesalahan berfungsi seperti filter. AGGREGATE mengabaikan semua kesalahan dan mengembalikan nilai terbesar (maksimum) yang bertahan.
MAKSIF
Fungsi MAXIFS, tersedia di Excel Office 365, bisa mengembalikan nilai maksimal menggunakan satu atau beberapa kriteria tanpa memerlukan rumus array. Namun, MAXIFS adalah fungsi berbasis jarak, dan tidak mengizinkan fungsi lain seperti TEXT untuk memproses nilai dalam rentang kriteria. Namun Anda dapat menambahkan kolom pembantu ke data, menghasilkan nilai hari kerja dengan TEXT, kemudian menggunakan MAXIFS dengan kolom pembantu sebagai rentang kriteria.
