Formula generik
=SUMPRODUCT(LARGE((range=criteria)*(values),(1,2,3,N)))
Ringkasan
Untuk menjumlahkan nilai n teratas dalam kriteria pencocokan julat, anda dapat menggunakan formula berdasarkan fungsi BESAR, dibungkus dalam fungsi SUMPRODUCT. Dalam bentuk rumus generik (di atas), julat mewakili julat sel yang dibandingkan dengan kriteria , nilai mewakili nilai angka dari mana nilai teratas diambil, dan N mewakili idea nilai Nth.
Dalam contohnya, sel aktif mengandungi formula ini:
=SUMPRODUCT(LARGE((color=E5)*(value),(1,2,3)))
Di mana warna adalah julat bernama B5: B12 dan nilai adalah julat bernama C5: C12.
Penjelasan
Dalam bentuk termudah, LARGE mengembalikan nilai "Nth terbesar" dalam julat dengan pembinaan ini:
=LARGE (range,N)
Jadi, sebagai contoh:
=LARGE (C5:C12,2)
akan mengembalikan nilai ke-2 terbesar dalam julat C5: C12, iaitu 12 dalam contoh yang ditunjukkan.
Namun, jika anda memberikan "pemalar larik" (contohnya pemalar dalam bentuk (1,2,3)) kepada LARGE sebagai argumen kedua, LARGE akan mengembalikan pelbagai hasil dan bukan hasil tunggal. Jadi, formula:
=LARGE (C5:C12, (1,2,3))
akan mengembalikan nilai terbesar ke-1, ke-2, dan ke-3 C5: C12 dalam array seperti ini: (12,12,10)
Jadi, silap mata di sini adalah menapis nilai berdasarkan warna sebelum LARGE dijalankan. Kami melakukan ini dengan ungkapan:
(color=E5)
Yang menghasilkan array nilai BENAR / SALAH. Semasa operasi pendaraban, nilai-nilai ini dipaksakan menjadi satu dan nol:
=LARGE((1;0;1;0;1;1;0;0)*(12;12;10;9;8;8;7;5),(1,2,3))
Jadi hasil akhirnya adalah bahawa hanya nilai yang berkaitan dengan warna "merah" yang bertahan dalam operasi:
=SUMPRODUCT(LARGE((12;0;10;0;8;8;0;0),(1,2,3)))
dan nilai-nilai lain dipaksa untuk sifar.
Catatan: formula ini tidak akan menangani teks dalam julat nilai. Lihat di bawah.
Mengendalikan teks dalam nilai
Sekiranya anda mempunyai teks di mana-mana julat nilai, fungsi LARGE akan membuang ralat # NILAI dan menghentikan formula daripada berfungsi.
Untuk menangani teks dalam julat nilai, anda boleh menambahkan fungsi IFERROR seperti ini:
=SUM(IFERROR(LARGE(IF((color=E5),value),(1,2,3)),0))
Di sini, kami menjumpai ralat dari BESAR yang disebabkan oleh nilai teks dan menggantikannya dengan sifar. Menggunakan JIKA di dalam BESAR memerlukan formula dimasukkan dengan kawalan + shift + enter, jadi kita beralih ke SUM dan bukannya SUMPRODUCT.
Nota: Saya menemui formula ini yang disiarkan oleh Barry Houdini yang menakjubkan di stackoverflow.
