Formula generik
=MAXIFS(sales,dates,">="&G4,dates,"<="&EOMONTH(G4,0))
Ringkasan
Untuk mencari nilai maksimum pada bulan tertentu, anda boleh menggunakan fungsi MAXIFS atau salah satu alternatif lain di bawah. Dalam contoh yang ditunjukkan, formula dalam G5 adalah:
=MAXIFS(sales,dates,">="&G4,dates,"<="&EOMONTH(G4,0))
Di mana penjualan (D5: D15), tarikh (B5: B15) dan nilai (C5: C15) dinamakan julat.
Penjelasan
Fungsi MAXIFS dapat mencari nilai maksimum dalam julat berdasarkan satu atau lebih kriteria. Dalam contoh yang ditunjukkan, kami menggunakan MAXIFS untuk mencari nilai jualan maksimum berdasarkan bulan tertentu dengan tarikh "kurungan" antara hari pertama bulan dan hari terakhir bulan tersebut. Kriteria pertama memeriksa sama ada tarikh lebih besar daripada atau sama dengan yang pertama bulan:
dates,">="&G4,dates // first of month
Catatan: kami menganggap tarikh di G4 adalah tarikh "pertama bulan".
Kriteria kedua memeriksa sama ada tarikh kurang dari atau sama dengan bulan terakhir, dikira dengan fungsi EOMONTH:
dates,"<="&EOMONTH(G4,0) // last of month
Apabila kedua-dua kriteria mengembalikan BENAR, tarikhnya pada bulan tertentu, dan MAXIFS mengembalikan nilai maksimum tarikh yang memenuhi kriteria.
Rumusan alternatif
Sekiranya versi Excel anda tidak termasuk fungsi MAXIFS, ada pilihan lain. Satu pilihan adalah formula susunan mudah berdasarkan fungsi MAX dan IF:
(=MAX(IF(TEXT(dates,"my")=TEXT(G4,"my"),sales)))
Catatan: Ini adalah formula larik dan mesti dimasukkan dengan kawalan + shift + enter.
Pilihan lain adalah menggunakan fungsi AGGREGATE seperti ini:
=AGGREGATE(14,6,sales/(TEXT(dates,"my")=TEXT(G4,"my")),1)
Kedua-dua pilihan di atas menggunakan kaedah pengujian tarikh yang lebih mudah berdasarkan fungsi TEXT yang menggunakan format nombor untuk mengekstrak bulan dan tahun dari tarikh. Fungsi TEXT tidak dapat digunakan seperti ini di dalam fungsi MAXIFS, kerana MAXIFS memerlukan julat. Pendekatan alternatif ini dijelaskan dengan lebih terperinci di sini.
Pelanggan maksimum
Untuk mendapatkan klien yang dikaitkan dengan nilai maksimum pada bulan tertentu, formula dalam G6 adalah:
(=INDEX(clients,MATCH(1,(sales=G5)*(TEXT(dates,"my")=TEXT(G4,"my")),0)))
Ini adalah formula larik dan mesti dimasukkan dengan kawalan + shift + enter.
Ideanya di sini adalah bahawa kita sudah mengetahui nilai maksimum dalam sebulan (G5) dan kita dapat menggunakan nilai tersebut sebagai "kunci" untuk mencari pelanggan. Perhatikan bahawa kita mesti mengulangi logik yang digunakan untuk mengasingkan nilai pada bulan tertentu, untuk mengelakkan pencocokan palsu pada bulan yang berbeza.
Seperti alternatif yang dinyatakan di atas, formula ini juga menggunakan ujian dipermudahkan untuk tarikh berdasarkan fungsi TEKS. Baca lebih lanjut mengenai INDEX dan MATCH dengan pelbagai kriteria di sini.
Untuk formula all-in-one, ganti G5 dengan salah satu pilihan formula yang dijelaskan di atas untuk menentukan nilai maksimum pada bulan tertentu.
