Ringkasan
Untuk menampilkan daftar nama, diberi peringkat berdasarkan nilai numerik, Anda dapat menggunakan sekumpulan rumus berdasarkan LARGE, INDEX, MATCH, dengan bantuan dari fungsi TEXT. Pada contoh yang ditunjukkan, rumus di G5 adalah:
=LARGE(IF(TEXT(date,"mmmm")=G$4,amount),$F5)
Dan rumus di G10 adalah:
=INDEX(client,MATCH(1,(amount=G5)*(TEXT(date,"mmmm")=G$9),0))
di mana klien (B5: B17) tanggal (C5: C17) dan jumlah (C5: C17) diberi nama rentang.
Catatan: ini adalah rumus array dan harus dimasukkan dengan control + shift + enter, kecuali di Excel 365.
Penjelasan
Contoh ini disiapkan dalam dua bagian untuk kejelasan: (1) rumus untuk menentukan 3 jumlah teratas untuk setiap bulan dan (2) rumus untuk mengambil nama klien untuk masing-masing dari 3 jumlah bulanan teratas.
Perhatikan bahwa tidak ada peringkat sebenarnya dalam data sumber. Sebagai gantinya, kami menggunakan fungsi LARGE untuk bekerja secara langsung dengan jumlah. Pendekatan lain adalah menambahkan peringkat ke data sumber dengan fungsi RANK, dan menggunakan nilai peringkat untuk mengambil nama klien.
Bagian 1: dapatkan 3 jumlah teratas setiap bulan
Untuk mengambil 3 jumlah teratas untuk setiap minggu, rumus di G5 adalah:
=LARGE(IF(TEXT(date,"mmmm")=G$4,amount),$F5)
Catatan: ini adalah rumus array dan harus dimasukkan dengan control + shift + enter, kecuali di Excel 365.
Bekerja dari dalam ke luar, pertama-tama kita menggunakan fungsi TEXT untuk mendapatkan nama bulan untuk setiap tanggal dalam rentang tanggal bernama :
TEXT(date,"mmmm") // get month names
Format angka khusus "mmmm" akan mengembalikan string seperti "April", "Mei", "Juni" untuk setiap nama dalam tanggal rentang bernama . Hasilnya adalah array nama bulan seperti ini:
("April";"April";"April";"April";"May";"May";"May";"May";"May";"June";"June";"June";"June")
Fungsi TEXT mengirimkan array ini ke fungsi IF, yang dikonfigurasi untuk memfilter tanggal pada bulan tertentu dengan menguji nama bulan terhadap nilai di G4 (referensi campuran, sehingga rumus dapat disalin ke bawah dan ke seberang):
IF(TEXT(date,"mmmm")=G$4,amount) // filter on month
Hanya jumlah di bulan April yang bertahan dan berhasil melewati IF; semua nilai lainnya SALAH:
(10500;15200;18500;12500;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE)
Terakhir, fungsi LARGE menggunakan nilai dalam F5 (juga referensi campuran) untuk mengembalikan nilai terbesar "ke-n" yang tersisa. Di sel G5, LARGE mengembalikan 18.500, nilai terbesar "ke-1". Saat rumus disalin ke bawah dan di seluruh tabel, fungsi LARGE mengembalikan 3 jumlah teratas di masing-masing dari tiga bulan.
Sekarang setelah kita mengetahui 3 nilai teratas di setiap bulan, kita dapat menggunakan informasi ini seperti "kunci" untuk mengambil nama klien masing-masing.
Bagian 2: Ambil nama klien
Catatan: Ini adalah contoh penggunaan INDEX dan MATCH dengan beberapa kriteria. Jika konsep ini baru bagi Anda, berikut adalah contoh dasarnya.
Untuk mengambil nama yang terkait dengan tiga nilai teratas di G5: I7, kami menggunakan INDEX dan MATCH:
=INDEX(client,MATCH(1,(amount=G5)*(TEXT(date,"mmmm")=G$9),0))
Catatan: ini adalah rumus array dan harus dimasukkan dengan control + shift + enter, kecuali di Excel 365.
Bekerja dari dalam ke luar, fungsi MATCH dikonfigurasi untuk menggunakan logika Boolean seperti ini:
MATCH(1,(amount=G5)*(TEXT(date,"mmmm")=G$9),0)
Nilai pencarian adalah 1, dan array pencarian dibuat dengan ekspresi ini:
(amount=G5)*(TEXT(date,"mmmm")=G$9)
Ekspresi yang membuat array pencarian menggunakan logika Boolean untuk "memfilter" jumlah yang (1) bukan di bulan April, dan (2) bukan nilai di G5 (18.500). Hasilnya adalah larik 1 dan 0 seperti ini:
(0;0;1;0;0;0;0;0;0;0;0;0;0)
Dengan nilai pencarian 1 dan nol untuk jenis pencocokan (untuk memaksa pencocokan tepat) MATCH mengembalikan 3 langsung ke fungsi INDEX:
=INDEX(client,3) // returns "Janus"
INDEX mengembalikan nilai ketiga dalam klien rentang bernama, "Janus".
Saat rumus disalin ke bawah dan di seberang tabel, rumus tersebut mengembalikan 3 klien teratas di masing-masing dari tiga bulan.
