Formula umum
=SUMPRODUCT(LARGE(rng,(1,2,N)))
Ringkasan
Untuk menjumlahkan nilai teratas dalam suatu rentang, Anda dapat menggunakan rumus berdasarkan fungsi LARGE, yang dibungkus di dalam fungsi SUMPRODUCT. Dalam bentuk umum rumus (di atas), rng mewakili rentang sel yang berisi nilai numerik dan N mewakili gagasan nilai ke-N.
Dalam contoh, sel aktif berisi rumus ini:
=SUMPRODUCT(LARGE(B4:B13,(1,2,3)))
Penjelasan
Dalam bentuknya yang paling sederhana, LARGE akan mengembalikan nilai "terbesar ke-N" dalam suatu rentang. Misalnya rumusnya:
=LARGE(B4:B13, 2)
akan mengembalikan nilai terbesar ke-2 dalam rentang B4: B13 yang, dalam contoh di atas, adalah angka 9.
Namun, jika Anda memberikan "konstanta array" (misalnya, konstanta dalam bentuk (1,2,3)) ke LARGE sebagai argumen kedua, LARGE akan mengembalikan array hasil, bukan hasil tunggal. Jadi, rumusnya:
=LARGE(B4:B13,(1,2,3))
akan mengembalikan nilai terbesar ke-1, ke-2, dan ke-3 dalam rentang B4: B13. Pada contoh di atas, dimana B4: B13 berisi angka 1-10, maka hasil dari LARGE adalah array (8,9,10). SUMPRODUCT lalu menjumlahkan angka dalam larik ini dan mengembalikan total, yaitu 27.
SUM alih-alih SUMPRODUCT
SUMPRODUCT adalah fungsi fleksibel yang memungkinkan Anda menggunakan referensi sel untuk k di dalam fungsi LARGE.
Namun, jika Anda menggunakan konstanta array hard-coded sederhana seperti (1,2,3), Anda dapat menggunakan fungsi SUM:
=SUM(LARGE(B4:B13,(1,2,3)))
Catatan Anda harus memasukkan rumus ini sebagai rumus array jika Anda menggunakan referensi sel dan bukan konstanta array untuk k di dalam LARGE.
Saat N menjadi besar
Ketika N menjadi besar, akan membosankan untuk membuat konstanta array dengan tangan - Jika Anda ingin menjumlahkan 20 atau 30 nilai teratas dalam daftar besar, mengetikkan konstanta array dengan 20 atau 30 item akan memakan waktu lama. Dalam kasus ini, Anda dapat menggunakan pintasan untuk membuat konstanta array yang menggunakan fungsi ROW dan INDIRECT.
Misalnya, jika Anda ingin menjumlahkan 20 nilai teratas dalam rentang yang disebut "rng", Anda dapat menulis rumus seperti ini:
=SUMPRODUCT(LARGE(rng,ROW(INDIRECT("1:20"))))
Variabel N
Dengan data yang tidak mencukupi, N yang diperbaiki dapat menyebabkan kesalahan. Dalam hal ini, Anda dapat mencoba rumus 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 hitungan nilai, jika kurang dari 3.
