Formula generik
(=TEXTJOIN("",1,VLOOKUP(T(IF(1,MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))),xtable,2,0)))
Ringkasan
Untuk menterjemahkan huruf dalam rentetan ke nombor, anda boleh menggunakan formula susunan berdasarkan fungsi TEXTJOIN dan VLOOKUP, dengan jadual terjemahan yang ditentukan untuk memberikan carian yang diperlukan. Dalam contoh yang ditunjukkan, formula dalam C5 adalah:
(=TEXTJOIN("",1,VLOOKUP(T(IF(1,MID(B5,ROW(INDIRECT("1:"&LEN(B5))),1))),xtable,2,0)))
di mana "xtable" adalah julat bernama E5: F10.
Catatan: ini adalah formula larik dan mesti dimasukkan dengan kawalan + shift + enter.
Penjelasan
Pada intinya, formula ini menggunakan operasi larik untuk menghasilkan susunan huruf dari teks input, menerjemahkan setiap huruf secara berasingan menjadi nombor, kemudian bergabung kembali semua nombor dan mengembalikan output sebagai rentetan.
Untuk menguraikan rentetan input ke dalam array atau huruf, kami menggunakan fungsi MID, ROW, LEN dan INDIRECT seperti ini:
MID(B5,ROW(INDIRECT("1:"&LEN(B5))),1)
LEN mengembalikan panjang teks input, yang digabungkan menjadi "1:" dan diserahkan kepada TIDAK LANGSUNG sebagai teks. INDIRECT menilai teks sebagai rujukan baris, dan fungsi ROW mengembalikan pelbagai nombor ke MID:
MID(B5,(1;2;3),1)
MID kemudian mengekstrak satu watak untuk setiap posisi permulaan dan kami mempunyai:
=TEXTJOIN("",1,VLOOKUP(T(IF(1,("a";"b";"c"))),xtable,2,0))
Pada dasarnya, kami meminta VLOOKUP untuk mencari padanan untuk "a", "b", dan "c" pada masa yang sama. Untuk alasan yang tidak jelas, kita perlu "menghindarkan" array ini dengan cara tertentu menggunakan fungsi T dan IF. Selepas VLOOKUP berjalan, kami mempunyai:
=TEXTJOIN("",1,(9;4;6))
dan TEXTJOIN mengembalikan rentetan "946".
Keluarkan nombor
Untuk mengeluarkan nombor sebagai hasil akhir (bukan rentetan), tambah sifar. Operasi matematik akan memaksa rentetan menjadi nombor.
Jumlahkan nombor
Untuk menjumlahkan nombor dan bukannya menyenaraikannya, anda boleh mengganti TEXTJOIN dengan SUM seperti ini:
=SUM(VLOOKUP(T(IF(1,MID(B5,ROW(INDIRECT("1:"&LEN(B5))),1))),xtable,2,0))
Catatan: fungsi TEXTJOIN diperkenalkan melalui program langganan Office 365 pada tahun 2018.
