Ringkasan
Untuk menerjemahkan nilai teks menjadi angka dan menjumlahkan hasilnya, Anda bisa menggunakan rumus INDEX dan MATCH, dan fungsi SUM. Pada contoh yang ditampilkan, rumus di H5 adalah:
(=SUM(INDEX(value,N(IF(1,MATCH(C5:G5,code,0))))))
di mana "kode" adalah rentang bernama K5: K9, dan "nilai" adalah rentang bernama L5: L9.
Catatan: ini adalah rumus array, dan harus dimasukkan dengan control + shift + enter.
Penjelasan
Inti dari rumus ini adalah rumus INDEX dan MATCH dasar, yang digunakan untuk menerjemahkan nilai teks menjadi angka seperti yang ditentukan dalam tabel pencarian. Misalnya, untuk menerjemahkan "EX" ke nomor yang sesuai, kami akan menggunakan:
=INDEX(value,MATCH("EX",code,0))
yang akan menghasilkan 4.
Namun, twist dalam masalah ini adalah kami ingin menerjemahkan dan menjumlahkan rentang nilai teks di kolom C hingga G ke angka. Ini berarti kita perlu memberikan lebih dari satu nilai pencarian, dan kita membutuhkan INDEX untuk mengembalikan lebih dari satu hasil. Pendekatan standar adalah rumus seperti ini:
=SUM(INDEX(value,MATCH(C5:G5,code,0)))
Setelah MATCH berjalan, kami memiliki array dengan 5 item:
=SUM(INDEX(value,(2,2,3,2,5)))
Jadi tampaknya INDEX harus mengembalikan 5 hasil ke SUM. Namun, jika Anda mencoba ini, fungsi INDEX hanya akan mengembalikan satu hasil SUM. Agar INDEX mengembalikan beberapa hasil, kita perlu menggunakan trik yang agak tidak jelas, dan membungkus MATCH dalam N dan IF seperti ini:
N(IF(1,MATCH(C5:G5,code,0)))
Ini secara efektif memaksa INDEX untuk memberikan lebih dari satu nilai ke fungsi SUM. Setelah INDEX berjalan, kami memiliki:
=SUM((3,3,2,3,-1))
Dan fungsi SUM mengembalikan jumlah item dalam larik, 10. Untuk artikel yang bagus tentang perilaku ini, lihat artikel menarik ini di situs web EXCELXOR.
