Excel

Jumlahkan nilai teks seperti nombor

Sum Text Values Like Numbers

Formula Excel: Jumlahkan nilai teks seperti nomborRingkasan

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 menerjemahkan 'EX' ke nombor yang sesuai, kami akan menggunakan:

 
= INDEX (value, MATCH ('EX',code,0))

yang akan kembali 4.



Kelainan dalam masalah ini ialah kita mahu menerjemahkan dan menjumlahkan a julat nilai teks dalam lajur C hingga G hingga nombor. Ini bermakna 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 susunan 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 yang lebih baik muslihat yang tidak jelas , dan bungkus PERTANDINGAN di N dan JIKA seperti ini:

 
 N ( IF (1, MATCH (C5:G5,code,0)))

Ini secara berkesan memaksa INDEX memberikan lebih daripada satu nilai untuk fungsi SUM. Selepas INDEX berjalan, kami mempunyai:

cara menggunakan pengesahan data di excel 2010
 
= 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 .

Pengarang Dave Bruns


^