Formula generik
=SUMPRODUCT(LARGE(rng,(1,2,N)))
Ringkasan
Untuk menjumlahkan nilai teratas dalam julat, anda dapat menggunakan formula berdasarkan fungsi BESAR, dibungkus dalam fungsi SUMPRODUCT. Dalam bentuk rumus generik (di atas), rng mewakili rangkaian sel yang mengandungi nilai angka dan N mewakili idea nilai Nth.
Dalam contohnya, sel aktif mengandungi formula ini:
=SUMPRODUCT(LARGE(B4:B13,(1,2,3)))
Penjelasan
Dalam bentuk termudah, BESAR akan mengembalikan nilai "Nth terbesar" dalam julat. Contohnya, formula:
=LARGE(B4:B13, 2)
akan mengembalikan nilai ke-2 terbesar dalam julat B4: B13 yang, dalam contoh di atas, adalah nombor 9.
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(B4:B13,(1,2,3))
akan mengembalikan nilai terbesar ke-1, ke-2, dan ke-3 dalam julat B4: B13. Dalam contoh di atas, di mana B4: B13 mengandungi angka 1-10, hasil dari LARGE akan menjadi array (8,9,10). SUMPRODUCT kemudian menjumlahkan nombor dalam tatasusunan ini dan mengembalikan jumlahnya, iaitu 27.
SUM bukannya SUMPRODUCT
SUMPRODUCT adalah fungsi fleksibel yang membolehkan anda menggunakan rujukan sel untuk k di dalam fungsi BESAR.
Walau bagaimanapun, jika anda menggunakan pemalar susunan hard-coded sederhana seperti (1,2,3), anda hanya boleh menggunakan fungsi SUM:
=SUM(LARGE(B4:B13,(1,2,3)))
Perhatikan bahawa anda mesti memasukkan formula ini sebagai formula array jika anda menggunakan rujukan sel dan bukan pemalar array untuk k di dalam LARGE.
Apabila N menjadi besar
Apabila N menjadi besar menjadi sukar untuk membuat pemalar larik dengan tangan - Sekiranya anda ingin menjumlahkan 20 atau 30 nilai teratas dalam senarai besar, menaip pemalar array dengan 20 atau 30 item akan memakan masa yang lama. Dalam kes ini, anda boleh menggunakan jalan pintas untuk membina pemalar array yang menggunakan fungsi ROW dan INDIRECT.
Contohnya, jika anda ingin MENGUMPULKAN 20 nilai teratas dalam julat yang disebut "rng", anda boleh menulis formula seperti ini:
=SUMPRODUCT(LARGE(rng,ROW(INDIRECT("1:20"))))
Pemboleh ubah N
Dengan data yang tidak mencukupi, N yang tetap dapat menyebabkan kesalahan. Dalam kes ini, anda boleh mencuba formula seperti ini:
=SUMPRODUCT(LARGE(rng,ROW(INDIRECT("1:"&MIN(3,COUNT(rng))))))
Di sini, kami menggunakan MIN dengan COUNT untuk menjumlahkan 3 nilai teratas, atau jumlah nilai, jika kurang dari 3.
