Ringkasan
Untuk memaparkan senarai nama, diberi peringkat berdasarkan nilai angka, anda dapat menggunakan sekumpulan formula berdasarkan LARGE, INDEX, MATCH, dengan bantuan fungsi TEKS. Dalam contoh yang ditunjukkan, formula dalam G5 adalah:
=LARGE(IF(TEXT(date,"mmmm")=G$4,amount),$F5)
Dan formula dalam G10 adalah:
=INDEX(client,MATCH(1,(amount=G5)*(TEXT(date,"mmmm")=G$9),0))
di mana pelanggan (B5: B17) Tarikh (C5: C17) dan jumlah (C5: C17) dinamakan julat.
Catatan: ini adalah formula larik dan mesti dimasukkan dengan kawalan + shift + enter, kecuali di Excel 365.
Penjelasan
Contoh ini disusun dalam dua bahagian untuk kejelasan: (1) formula untuk menentukan 3 jumlah teratas untuk setiap bulan dan (2) formula untuk mendapatkan nama pelanggan untuk setiap 3 jumlah bulanan teratas.
Perhatikan tidak ada peringkat sebenarnya dalam data sumber. Sebaliknya, kami menggunakan fungsi BESAR untuk bekerja secara langsung dengan jumlah. Pendekatan lain adalah dengan menambahkan peringkat ke data sumber dengan fungsi RANK, dan menggunakan nilai peringkat untuk mengambil nama klien.
Bahagian 1: dapatkan 3 jumlah teratas setiap bulan
Untuk mendapatkan 3 jumlah teratas untuk setiap minggu, formula dalam G5 adalah:
=LARGE(IF(TEXT(date,"mmmm")=G$4,amount),$F5)
Catatan: ini adalah formula larik dan mesti dimasukkan dengan kawalan + shift + enter, kecuali di Excel 365.
Bekerja dari dalam ke luar, pertama kami menggunakan fungsi TEXT untuk mendapatkan nama bulan untuk setiap tarikh dalam tarikh julat yang dinamakan :
TEXT(date,"mmmm") // get month names
Format nombor khusus "mmmm" akan mengembalikan rentetan seperti "April", "Mei", "Jun" untuk setiap nama pada tarikh julat yang dinamakan . Hasilnya adalah pelbagai nama bulan seperti ini:
("April";"April";"April";"April";"May";"May";"May";"May";"May";"June";"June";"June";"June")
Fungsi TEXT menyampaikan array ini ke fungsi IF, yang dikonfigurasi untuk menyaring tarikh pada bulan tertentu dengan menguji nama bulan terhadap nilai di G4 (rujukan campuran, sehingga rumus dapat disalin ke bawah dan di seberang):
IF(TEXT(date,"mmmm")=G$4,amount) // filter on month
Hanya jumlah pada bulan April yang bertahan dan berjaya melalui JIKA; semua nilai lain SALAH:
(10500;15200;18500;12500;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE)
Akhirnya, fungsi LARGE menggunakan nilai dalam F5 (juga rujukan campuran) untuk mengembalikan nilai terbesar "nth" yang masih ada. Dalam sel G5, LARGE mengembalikan 18,500, nilai terbesar "1". Oleh kerana formula disalin ke bawah dan di seberang jadual, fungsi BESAR mengembalikan 3 jumlah teratas dalam setiap tiga bulan.
Sekarang setelah kita mengetahui 3 nilai teratas dalam setiap bulan, kita dapat menggunakan maklumat ini seperti "kunci" untuk mendapatkan nama pelanggan untuk masing-masing.
Bahagian 2: mendapatkan semula nama pelanggan
Catatan: Ini adalah contoh penggunaan INDEX dan MATCH dengan pelbagai kriteria. Sekiranya konsep ini baru bagi anda, berikut adalah contoh asas.
Untuk mendapatkan semula nama yang dikaitkan dengan tiga nilai teratas dalam G5: I7, kami menggunakan INDEX dan MATCH:
=INDEX(client,MATCH(1,(amount=G5)*(TEXT(date,"mmmm")=G$9),0))
Catatan: ini adalah formula larik dan mesti dimasukkan dengan kawalan + shift + enter, kecuali di Excel 365.
Berfungsi dari dalam ke luar, fungsi MATCH dikonfigurasi untuk menggunakan logik Boolean seperti ini:
MATCH(1,(amount=G5)*(TEXT(date,"mmmm")=G$9),0)
Nilai carian adalah 1, dan tatasusunan dibina dengan ungkapan ini:
(amount=G5)*(TEXT(date,"mmmm")=G$9)
Ungkapan yang membuat array pencarian menggunakan logik Boolean untuk "menyaring" jumlah yang (1) bukan pada bulan April, dan (2) bukan nilai dalam G5 (18,500). Hasilnya ialah pelbagai 1s dan 0s seperti ini:
(0;0;1;0;0;0;0;0;0;0;0;0;0)
Dengan nilai carian 1 dan sifar untuk jenis perlawanan (untuk memaksa padanan tepat) MATCH mengembalikan 3 terus ke fungsi INDEX:
=INDEX(client,3) // returns "Janus"
INDEX mengembalikan nilai ketiga dalam pelanggan julat bernama, "Janus".
Oleh kerana formula disalin ke bawah dan di seberang meja, ia mengembalikan 3 pelanggan teratas dalam setiap tiga bulan.
