Công thức Excel: Tính tổng các giá trị văn bản như số -

Mục lục

Tóm lược

Để dịch các giá trị văn bản thành số và tính tổng kết quả, bạn có thể sử dụng công thức INDEX và MATCH và hàm SUM. Trong ví dụ được hiển thị, công thức trong H5 là:

(=SUM(INDEX(value,N(IF(1,MATCH(C5:G5,code,0))))))

trong đó "mã" là phạm vi được đặt tên K5: K9 và "giá trị" là phạm vi được đặt tên L5: L9.

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

Trung tâm của công thức này là một công thức INDEX và MATCH cơ bản, được sử dụng để dịch các giá trị văn bản thành các số như được xác định trong bảng tra cứu. Ví dụ: để dịch "EX" sang số tương ứng, chúng tôi sẽ sử dụng:

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

mà sẽ trả về 4.

Tuy nhiên, điều khó khăn trong vấn đề này là chúng ta muốn dịch và tính tổng một loạt các giá trị văn bản trong các cột từ C đến G thành số. Điều này có nghĩa là chúng tôi cần cung cấp nhiều hơn một giá trị tra cứu và chúng tôi cần INDEX để trả về nhiều kết quả. Cách tiếp cận tiêu chuẩn là một công thức như sau:

=SUM(INDEX(value,MATCH(C5:G5,code,0)))

Sau khi MATCH chạy, chúng ta có một mảng có 5 mục:

=SUM(INDEX(value,(2,2,3,2,5)))

Vì vậy, có vẻ như INDEX sẽ trả về 5 kết quả cho SUM. Tuy nhiên, nếu bạn thử điều này, hàm INDEX sẽ chỉ trả về một kết quả SUM. Để INDEX trả về nhiều kết quả, chúng ta cần sử dụng một thủ thuật khá khó hiểu và đặt MATCH trong N và IF như thế này: