Không thể tính tổng giá trị văn bản trong Google Sheets hoặc bất kỳ ứng dụng bảng tính nào khác mà không gán điểm số cho các văn bản.
Ví dụ, tôi có một bảng có các điểm đánh giá nhân viên dưới dạng văn bản. Các văn bản được sử dụng cho việc đánh giá là “Xuất sắc”, “Rất tốt”, “Tốt”, “Trung bình” và “Rất kém”.
Mỗi giá trị văn bản này có điểm số tương ứng lần lượt là 5, 4, 3, 2 và 1.
Bảng #1:
| Tên | Bài kiểm tra 1 | Bài kiểm tra 2 | Bài kiểm tra 3 |
|---|
Hãy xem cách gán điểm cho 5 loại giá trị văn bản trên và tính tổng giá trị văn bản trong Google Sheets. Tôi sẽ sử dụng công thức Vlookup và Sum để làm điều đó.
Nếu bạn muốn tính tổng giá trị văn bản như các số trong mỗi hàng trong Google Sheets, bạn có thể sử dụng công thức sao chép dán Vlookup + Sum hoặc một công thức mảng Vlookup + Mmult.
Phương pháp dễ nhất để tính tổng giá trị văn bản dựa trên số đã được gán trong Google Sheets là sử dụng công thức Vlookup + Sum không phải mảng. Vì vậy, hãy bắt đầu với phương pháp đó.
Logic công thức và Chuẩn bị bảng tính
Có hai bước trong công thức. Hiểu điều đó sẽ giúp bạn viết công thức một cách dễ dàng. Chúng là:
- Gán điểm/số cho văn bản – Vlookup.
- Tổng giá trị văn bản – Sum(Vlookup).
Để tính tổng giá trị văn bản, chúng ta phải trước tiên gán điểm cho văn bản. Làm thế nào để làm điều đó?
Vlookup sẽ đảm nhận việc gán số điểm cho các giá trị văn bản ở đây. Để làm điều đó, bạn cần tạo một bảng (gọi là ‘range’ trong Vlookup) trước tiên.
Phạm vi chứa các giá trị văn bản trong một cột và điểm số tương ứng của chúng. Xem bảng đó, nó sẽ hoạt động như ‘range’ trong Vlookup.
Bảng #2:
| Mô tả | Điểm |
|---|
Nhiệm vụ của chúng ta là gán các điểm trong Bảng #2 cho các giá trị tương ứng trong Bảng #1 và nhận tổng trong cột Tổng trong Bảng #1.
Công thức không mảng để tính tổng giá trị văn bản trong Google Sheets
Vlookup để Gán Điểm cho Các Giá Trị Văn Bản Tương Ứng
Như đã đề cập, bước đầu tiên để tính tổng giá trị văn bản giống như các số là gán số cho các giá trị văn bản.
Vlookup sau sẽ làm điều đó.
=ArrayFormula(vlookup(D3:F3,$A$3:$B$7,2,0))
Vlookup nhận nhiều khóa tìm kiếm từ D3:F3 và vì vậy tôi đã sử dụng hàm ArrayFormula với Vlookup.
‘Phạm vi trong Vlookup là $A$3:$B$7 và cột chỉ mục (đầu ra) là cột 2 trong $A$3:$B$7.
Vlookup trên gán số 5 cho văn bản “Xuất sắc”, 3 cho “Tốt” và lại 5 cho “Xuất sắc”.
Tính tổng các điểm đã được gán bởi Vlookup
Vlookup nằm trong ô G3. Chỉ cần sử dụng chức năng Sum để tổng các số trả về bởi Vlookup.
Sau đó kéo công thức xuống.
=ArrayFormula(SUM(vlookup(D3:F3,$A$3:$B$7,2,0)))
Để tính tổng các giá trị văn bản sau khi đã gán điểm cho chúng trong Google Sheets.
Lưu ý: Có thể có khả năng lỗi #N/A! với công thức trên trong trường hợp có bất kỳ lỗi gõ sai / ô trống nào trong Bảng #1.
Để ngăn chặn điều đó, hãy bao gồm IFNA với công thức trên. Bạn phải chèn IFNA trước SUM.
=ArrayFormula(SUM(IFNA(vlookup(D3:F3,$A$3:$B$7,2,0))))
Công thức Mảng để tính tổng giá trị văn bản trong Google Sheets
Với một số thay đổi, chúng ta có thể biến công thức trên thành một công thức mảng.
Thay đổi đầu tiên là trong Vlookup. Trong ví dụ trên, khóa tìm kiếm đến từ mảng D3:F3. Hãy thay thành D3:F12.
=ArrayFormula(vlookup(D3:F12,$A$3:$B$7,2,0))
Ở đây, bạn phải bọc công thức Vlookup kết quả bằng IFNA để trả về 0 trong trường hợp #N/A! Điều này là bắt buộc để MMULT hoạt động đúng.
=ArrayFormula(ifna(vlookup(D3:F12,$A$3:$B$7,2,0),0))
MMULT được sử dụng để trả về tích chập hai ma trận, phải không?
Cú pháp: MMULT(matrix1, matrix2)
Công thức trên là ‘matrix1’. Chúng tôi không muốn tính tích chập hai ma trận và chúng tôi thực sự không có ‘matrix2’.
Hãy tạo ‘matrix2’ bằng cách sử dụng công thức Sequence như bên dưới.
=ArrayFormula(sign(sequence(3,1)))
Công thức này sẽ trả về số 1 ba lần trong một cột (trong 3 hàng). Bạn có thể thử nghiệm trên bảng tính của mình.
Số cột trong ‘matrix1’ là 3. Vì vậy, theo tiêu chuẩn, ‘matrix2’ phải chứa cùng số lượng hàng. Bây giờ chúng ta có kích thước ma trận tương thích trong MMULT.
Nếu bạn có nhiều cột trong Bảng #1 của mình, hãy thay đổi công thức Sequence cho phù hợp.
Đây là công thức mảng trong ô G2 để tính tổng giá trị văn bản như số trong Google Sheets.
=ArrayFormula(mmult(ifna(vlookup(D3:F12,$A$3:$B$7,2,0),0),sequence(3,1)^0))
Muốn sử dụng một mảng mở, như D3:F thay vì D3:F12?
Hãy đọc hướng dẫn này – Sử Dụng Đúng MMULT trong Các Hàng Vô Hạn trong Google Sheets.
Cảm ơn vì đã đọc bài viết này. Chúc bạn thích thú!
