Ringkasan
Untuk menterjemahkan nilai teks menjadi angka dan jumlah hasilnya, anda boleh menggunakan formula INDEX dan MATCH, dan fungsi SUM. Dalam contoh yang ditunjukkan, formula dalam H5 adalah:
(=SUM(INDEX(value,N(IF(1,MATCH(C5:G5,code,0))))))
di mana "kod" adalah julat bernama K5: K9, dan "nilai" adalah julat bernama L5: L9.
Catatan: ini adalah formula larik, dan mesti dimasukkan dengan kawalan + shift + enter.
Penjelasan
Inti formula ini adalah formula INDEX dan MATCH asas, digunakan untuk menterjemahkan nilai teks menjadi angka seperti yang ditentukan dalam jadual carian. Sebagai contoh, untuk menterjemahkan "EX" ke nombor yang sesuai, kami akan menggunakan:
=INDEX(value,MATCH("EX",code,0))
yang akan kembali 4.
Kelainan dalam masalah ini adalah bahawa kita ingin menerjemahkan dan menjumlahkan pelbagai nilai teks di lajur C hingga G hingga nombor. Ini bermaksud kita perlu memberikan lebih daripada satu nilai carian, dan kita memerlukan INDEX untuk mengembalikan lebih dari satu hasil. Pendekatan standard adalah formula seperti ini:
=SUM(INDEX(value,MATCH(C5:G5,code,0)))
Setelah MATCH dijalankan, kami mempunyai array dengan 5 item:
=SUM(INDEX(value,(2,2,3,2,5)))
Jadi nampaknya INDEX harus mengembalikan 5 keputusan ke SUM. Walau bagaimanapun, jika anda mencuba ini, fungsi INDEX akan mengembalikan hanya satu hasil SUM. Untuk mendapatkan INDEX mengembalikan banyak hasil, kita perlu menggunakan muslihat yang agak kabur, dan bungkus PERTANDINGAN di N dan JIKA seperti ini:
N(IF(1,MATCH(C5:G5,code,0)))
Ini secara berkesan memaksa INDEX untuk memberikan lebih dari satu nilai untuk fungsi SUM. Selepas INDEX berjalan, kami mempunyai:
=SUM((3,3,2,3,-1))
Dan fungsi SUM mengembalikan jumlah item dalam array, 10. Untuk menuliskan kelakuan ini dengan baik, lihat artikel menarik ini di laman web EXCELXOR.
