Formula umum
=MAXIFS(sales,dates,">="&G4,dates,"<="&EOMONTH(G4,0))
Ringkasan
Untuk menemukan nilai maksimum dalam bulan tertentu, Anda dapat menggunakan fungsi MAXIFS atau salah satu alternatif lain di bawah ini. Pada contoh yang ditunjukkan, rumus di G5 adalah:
=MAXIFS(sales,dates,">="&G4,dates,"<="&EOMONTH(G4,0))
Dimana penjualan (D5: D15), tanggal (B5: B15) dan nilai (C5: C15) diberi nama rentang.
Penjelasan
Fungsi MAXIFS dapat menemukan nilai maksimum dalam rentang berdasarkan satu atau lebih kriteria. Dalam contoh yang ditampilkan, kami menggunakan MAXIFS untuk menemukan nilai penjualan maksimal berdasarkan bulan tertentu dengan "tanda kurung" tanggal antara hari pertama bulan itu dan hari terakhir bulan itu. Kriteria pertama memeriksa apakah tanggal lebih besar dari atau sama dengan tanggal pertama setiap bulan:
dates,">="&G4,dates // first of month
Catatan: kami mengasumsikan tanggal di G4 adalah tanggal "bulan pertama".
Kriteria kedua memeriksa apakah tanggal kurang dari atau sama dengan bulan terakhir, dihitung dengan fungsi EOMONTH:
dates,"<="&EOMONTH(G4,0) // last of month
Saat kedua kriteria mengembalikan TRUE, tanggal di bulan tertentu, dan MAXIFS mengembalikan nilai maksimal tanggal yang memenuhi kriteria.
Formula alternatif
Jika versi Excel Anda tidak menyertakan fungsi MAXIFS, ada opsi lain. Salah satu opsinya adalah rumus array sederhana berdasarkan fungsi MAX dan IF:
(=MAX(IF(TEXT(dates,"my")=TEXT(G4,"my"),sales)))
Catatan: Ini adalah rumus array dan harus dimasukkan dengan control + shift + enter.
Opsi lainnya adalah menggunakan fungsi AGGREGATE seperti ini:
=AGGREGATE(14,6,sales/(TEXT(dates,"my")=TEXT(G4,"my")),1)
Kedua opsi di atas menggunakan metode pengujian tanggal yang lebih sederhana berdasarkan fungsi TEXT yang menggunakan format angka untuk mengekstrak bulan dan tahun dari suatu tanggal. Fungsi TEXT tidak dapat digunakan seperti ini di dalam fungsi MAXIFS, karena MAXIFS memerlukan rentang. Pendekatan alternatif ini dijelaskan lebih rinci di sini.
Klien maks
Untuk mengambil klien yang terkait dengan nilai maksimal dalam bulan tertentu, rumus di G6 adalah:
(=INDEX(clients,MATCH(1,(sales=G5)*(TEXT(dates,"my")=TEXT(G4,"my")),0)))
Ini adalah rumus array dan harus dimasukkan dengan control + shift + enter.
Idenya di sini adalah kita sudah mengetahui nilai maksimal dalam satu bulan (G5) dan kita dapat menggunakan nilai itu sebagai "kunci" untuk mencari klien. Perhatikan bahwa kita harus mengulangi logika yang digunakan untuk mengisolasi nilai di bulan tertentu, untuk menghindari kecocokan palsu di bulan yang berbeda.
Seperti alternatif yang disebutkan di atas, rumus ini juga menggunakan tes yang disederhanakan untuk tanggal berdasarkan fungsi TEXT. Baca lebih lanjut tentang INDEX dan MATCH dengan beberapa kriteria di sini.
Untuk rumus all-in-one, ganti G5 dengan salah satu opsi rumus yang dijelaskan di atas untuk menentukan nilai maksimal dalam bulan tertentu.
