Formula generik
=SUM(INDEX(range,N(IF(1,(1,2,3)))))
Ringkasan
Untuk mendapatkan INDEX mengembalikan pelbagai item ke fungsi lain, anda boleh menggunakan trik yang tidak jelas berdasarkan fungsi IF dan N. Dalam contoh yang ditunjukkan, formula di E5 adalah:
=SUM(INDEX(data,N(IF(1,(1,2,3)))))
di mana "data" adalah julat bernama B5: B10.
Penjelasan
Sangat mengejutkan apabila INDEX mengembalikan lebih daripada satu nilai ke fungsi lain. Sebagai ilustrasi, formula berikut dapat digunakan untuk mengembalikan tiga item pertama dalam rentang bernama "data", ketika dimasukkan sebagai formula array multi-sel.
(=INDEX(data,(1,2,3)))
Hasilnya dapat dilihat dalam julat D10: F10, yang benar mengandungi 10, 15, dan 20.
Namun, jika kita membungkus formula dalam fungsi SUM:
=SUM(INDEX(data,(1,2,3)))
Hasil akhir adalah 10, sementara itu mestilah 45, walaupun dimasukkan sebagai formula larik. Masalahnya adalah bahawa INDEX hanya mengembalikan item pertama dalam array ke fungsi SUM. Untuk memaksa INDEX mengembalikan banyak item ke SUM, anda boleh membungkus pemalar array dalam fungsi N dan IF seperti ini:
=SUM(INDEX(data,N(IF(1,(1,2,3)))))
yang mengembalikan hasil yang betul dari 45. Begitu juga formula ini:
=SUM(INDEX(data,N(IF(1,(1,3,5)))))
mengembalikan 60, jumlah 10, 20 dan 30 dengan betul.
Teknik tidak jelas ini kadang-kadang disebut "dereferencing", kerana ia menghentikan INDEX daripada menangani hasil sebagai rujukan sel, dan kemudian menjatuhkan semua kecuali item pertama dalam array. Sebagai gantinya, INDEX memberikan pelbagai nilai untuk SUM in. Jeff Weir mempunyai penjelasan yang baik di sini mengenai stackoverflow.
Catatan kepada pembaca: Saya tidak pasti mengapa ini berfungsi. Sekiranya anda dapat memberikan penjelasan yang jelas, saya akan mengemas kini untuk memasukkan.
