Công thức chung
(=TEXTJOIN("",1,VLOOKUP(T(IF(1,MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))),xtable,2,0)))
Tóm lược
Để dịch các chữ cái trong chuỗi thành số, bạn có thể sử dụng công thức mảng dựa trên các hàm TEXTJOIN và VLOOKUP, với một bảng dịch được xác định để cung cấp các tra cứu cần thiết. Trong ví dụ được hiển thị, công thức trong C5 là:
(=TEXTJOIN("",1,VLOOKUP(T(IF(1,MID(B5,ROW(INDIRECT("1:"&LEN(B5))),1))),xtable,2,0)))
trong đó "xtable" là dải ô được đặt tên E5: F10.
Lưu ý: đây là công thức mảng và phải được nhập bằng control + shift + enter.
Giải trình
Về cốt lõi, công thức này sử dụng một phép toán mảng để tạo một mảng các chữ cái từ văn bản đầu vào, dịch từng chữ cái riêng lẻ thành một số, sau đó nối tất cả các số lại với nhau và trả về kết quả đầu ra dưới dạng chuỗi.
Để phân tích cú pháp chuỗi đầu vào thành một mảng hoặc các chữ cái, chúng tôi sử dụng các hàm MID, ROW, LEN và INDIRECT như sau:
MID(B5,ROW(INDIRECT("1:"&LEN(B5))),1)
LEN trả về độ dài của văn bản đầu vào, được nối với "1:" và chuyển cho INDIRECT dưới dạng văn bản. INDIRECT đánh giá văn bản dưới dạng tham chiếu hàng và hàm ROW trả về một mảng số cho MID:
MID(B5,(1;2;3),1)
Sau đó MID trích xuất một ký tự cho mỗi vị trí bắt đầu và chúng ta có:
=TEXTJOIN("",1,VLOOKUP(T(IF(1,("a";"b";"c"))),xtable,2,0))
Về cơ bản, chúng tôi đang yêu cầu hàm VLOOKUP tìm một kết quả phù hợp cho "a", "b" và "c" cùng một lúc. Vì những lý do khó hiểu, chúng ta cần "bỏ tham chiếu" mảng này theo một cách cụ thể bằng cách sử dụng cả hàm T và IF. Sau khi hàm VLOOKUP chạy, chúng ta có:
=TEXTJOIN("",1,(9;4;6))
và TEXTJOIN trả về chuỗi "946".
Xuất một số
Để xuất một số dưới dạng kết quả cuối cùng (thay vì một chuỗi), hãy thêm số không. Phép toán sẽ buộc chuỗi thành một số.
Tổng số
Để tổng hợp các số lại với nhau thay vì liệt kê chúng, bạn có thể thay thế TEXTJOIN bằng SUM như sau:
=SUM(VLOOKUP(T(IF(1,MID(B5,ROW(INDIRECT("1:"&LEN(B5))),1))),xtable,2,0))
Lưu ý: chức năng TEXTJOIN đã được giới thiệu thông qua chương trình đăng ký Office 365 vào năm 2018.
