Tải xuống sổ làm việc mẫu
Hướng dẫn này sẽ trình bày cách tìm tổng giá trị văn bản trong đó mã duy nhất được gán cho mỗi giá trị văn bản như vậy trong Excel và Google Trang tính.
SUM số được lưu trữ dưới dạng văn bản
Đầu tiên chúng ta sẽ xem xét cách tính tổng các số được lưu trữ hoặc định dạng dưới dạng văn bản.
Trong ví dụ sau, cột Lương được lưu trữ dưới dạng văn bản. Nếu cố gắng tính tổng các giá trị, Excel sẽ hiển thị số không.
| 1 | = SUM (C3: C7) |
Thay vào đó, để thực hiện Thao tác SUM trên các số được lưu trữ dưới dạng văn bản, bạn có thể sử dụng công thức mảng với các hàm SUM và VALUE như sau:
| 1 | = SUM (VALUE (C3: C7)) |
Hàm VALUE chuyển đổi văn bản đại diện cho một số thành một số. Hàm SUM tính tổng các số đó.
Trong Excel 365 và phiên bản Excel mới hơn 2022, bạn có thể chỉ cần nhập công thức như bình thường. Tuy nhiên, khi sử dụng Excel 2022 trở về trước, bạn phải nhập công thức mảng bằng cách nhấn CTRL + SHIFT + ENTER (thay vì ĐI VÀO), cho Excel biết rằng công thức là một công thức mảng. Bạn sẽ biết đó là một công thức mảng bởi dấu ngoặc nhọn xuất hiện xung quanh công thức (xem hình ảnh trên cùng). Trong các phiên bản Excel và Excel 365 mới hơn, bạn chỉ cần nhấn ĐI VÀO thay thế.
Hãy cùng chúng tôi xem phần giải thích sau đây để hiểu rõ hơn về công thức.
Hàm VALUE
Được sử dụng như một công thức mảng, Hàm VALUE chuyển đổi toàn bộ phạm vi số được lưu trữ dưới dạng văn bản thành một mảng số và trả về nó dưới dạng đầu vào cho Hàm SUM.
| 1 | = SUM ({10000; 6500; 7500; 15000; 8000}) |
Để xem những gì Hàm VALUE trả về, hãy chọn chức năng cần thiết và nhấn F9.
SUM các giá trị văn bản
Để SUM một dải giá trị văn bản trong đó mã duy nhất được gán cho mỗi giá trị văn bản như vậy, một công thức mảng có thể được sử dụng.
Bảng sau đây ghi lại những gì mọi người chọn cho ba câu hỏi nhất định. Bảng bên phải liệt kê bốn câu trả lời có thể có, mỗi câu trả lời đã được gán một giá trị mã. Chúng tôi phải tổng hợp các mã cho từng người.
| 1 | = SUM (INDEX (I $ 3: I $ 6, N (IF (1, MATCH (C4: E4, H $ 3: H $ 6,0))))) |
Chúng ta sẽ đi qua công thức dưới đây.
Hàm MATCH
Hàm MATCH tìm kiếm một mục được chỉ định trong một phạm vi và trả về vị trí tương đối của nó trong phạm vi đó. Cú pháp của nó là:
Là một công thức mảng trong ví dụ này, hãy xem Hàm MATCH trả về gì.
| 1 | = SUM (INDEX (I $ 3: I $ 6, N (IF (1, {1,3,2})))) |
Đối với một người nhất định, Hàm MATCH tìm vị trí tương đối của mỗi câu trả lời trong phạm vi H3: H6. Kết quả là một mảng các vị trí.
Ghi chú: Trong công thức mảng, để xem hàm trả về gì, hãy chọn hàm cần thiết và nhấn F9.
Hàm IF & N
Hàm IF và N được sử dụng cùng nhau trả về mảng sau làm đầu vào cho Hàm INDEX.
| 1 | = SUM (INDEX (I $ 3: I $ 6, {1,3,2})) |
Ở đây, hai hàm trả về một mảng vị trí tương đối của các câu trả lời trong phạm vi H3: H6. Mục đích của việc sử dụng Hàm IF và N là để thực hiện một quá trình được gọi là hội nghị truyền hình. Nói một cách dễ hiểu, hai hàm buộc Hàm INDEX phải chuyển toàn bộ mảng giá trị mã cho Hàm SUM.
Chúng tôi giải thích điều này trong phần tiếp theo.
Hàm INDEX
Hàm INDEX trả về giá trị được đặt tại giao điểm của một hàng và cột được chỉ định trong một phạm vi. Cú pháp của nó là:
Hãy xem nó hoạt động như thế nào dưới dạng công thức mảng:
| 1 | = SUM ({10,5,8}) |
Hàm INDEX tìm các giá trị mã trong phạm vi I3: I6 theo các số vị trí đã cho. Sau đó, nó trả về một mảng giá trị, tức là mã tương ứng cho mỗi câu trả lời, cho Hàm SUM để thực hiện các thao tác.
Đảm bảo số hàng và cột trong cả Bài giải và Mã số cột giống nhau.
Hàm SUM
Hàm SUM sẽ tính tổng các giá trị mã được trả về bởi Hàm INDEX.
| 1 | = SUM ({10,5,8}) |
Tất cả những điều này kết hợp với nhau sẽ tạo ra công thức ban đầu của chúng tôi:
| 1 | {= SUM (INDEX (I $ 3: I $ 6, N (IF (1, MATCH (C4: E4, H $ 3: H $ 6,0)))))} |
TỔNG các giá trị văn bản - Không có hàm IF & N
Phần này giải thích cách Excel phản hồi nếu chúng ta không sử dụng hàm IF và N trong công thức được đề cập ở trên.
Ví dụ tương tự đang được sử dụng với các mã và câu trả lời giống nhau.
| 1 | {= SUM (INDEX (I $ 3: I $ 6, MATCH (C4: E4, H $ 3: H $ 6,0)))} |
Như bạn có thể thấy, Hàm INDEX chỉ chuyển mã cho câu trả lời đầu tiên cho Hàm SUM. Nếu bạn xem xét kỹ lưỡng Hàm INDEX bằng cách nhấn F9 bạn sẽ nhận được những điều sau:
Giá trị! Lỗi được trả về vì Hàm INDEX không thể đọc mảng số hàng dưới dạng một mảng. Do đó, việc sử dụng Hàm IF & N thực hiện được một mẹo nhỏ.
Ghi chú: Trong Excel 365, bạn có thể bỏ qua hoàn toàn việc sử dụng Hàm IF và N.
Tổng văn bản- Google Trang tính
Các công thức này hoạt động tương tự trong Google Trang tính cũng như trong Excel.
