Formula generik
(=SUM(SMALL(IF(range1=criteria,range2),(1,2,3,N))))
Ringkasan
Untuk menjumlahkan nilai n bawah dalam kriteria pemadanan jarak jauh, Anda dapat menggunakan formula larik berdasarkan fungsi KECIL, yang dibungkus dalam fungsi SUM. Dalam bentuk rumus generik (di atas), range1 mewakili julat sel dibandingkan dengan kriteria , range2 berisi nilai angka dari mana nilai bawah diambil, dan N mewakili "nth".
Dalam contohnya, sel aktif mengandungi formula ini:
=SUM(SMALL(IF(color=E5,value),(1,2,3)))
Di mana warna dinamakan julat B5: B12 dan nilainya adalah julat bernama C5: C12.
Catatan: ini adalah formula larik dan mesti dimasukkan dengan kawalan + shift + enter.
Penjelasan
Dalam bentuk termudah, KECIL mengembalikan nilai "Nth terkecil" dalam julat dengan pembinaan ini:
=SMALL (range,N)
Jadi, sebagai contoh:
=SMALL (C5:C12,2)
akan mengembalikan nilai terkecil ke-2 dalam julat C5: C12, iaitu 5 dalam contoh yang ditunjukkan.
Tetapi, jika anda memberikan "pemalar larik" (mis. Pemalar dalam bentuk (1,2,3)) kepada KECIL sebagai argumen kedua, KECIL akan mengembalikan array hasil dan bukan hasil tunggal. Jadi, formula:
=SMALL (C5:C12, (1,2,3))
akan mengembalikan nilai terkecil 1, 2 dan 3 terkecil C5: C12 dalam array seperti ini: (4,5,7)
Jadi, silap mata di sini adalah menapis nilai berdasarkan warna sebelum KECIL dijalankan. Kami melakukan ini dengan ungkapan berdasarkan fungsi IF:
IF(color=E5,value)
Ini membina susunan nilai yang dimasukkan ke dalam KECIL. Pada dasarnya, hanya nilai yang berkaitan dengan warna merah yang menjadikannya larik. Di mana warna sama dengan "merah", array mengandungi nombor, dan di mana warnanya tidak merah, array mengandungi FALSE:
SMALL((12;FALSE;10;FALSE;8;4;FALSE;FALSE),(1,2,3)))
Fungsi KECIL mengabaikan nilai-nilai PALSU dan mengembalikan 3 nilai terkecil dalam array: (4,8,10). Fungsi SUM mengembalikan hasil akhir, 22.
