Hướng dẫn tính tổng cho các ô chứa ký tự giống nhau trong Excel

Khi cần tính tổng các ô dựa trên điều kiện là sự xuất hiện của một ký tự nhất định, hàm nào trong Excel sẽ phù hợp? Đó là câu hỏi hay mà Siêu Marketing thường xuyên nhận được. Hãy cùng khám phá cách thực hiện như thế nào qua bài viết này.

Xét ví dụ dưới đây:

Ở cột ID nhân viên gồm những mã như KD1, KD2. Tuy nhiên, các ký tự này có thể nằm ở vị trí bất kỳ trong mỗi ô miễn là ô đó chứa các ký tự cần tìm.

Mục tiêu là xác định tổng doanh thu của nhân viên có mã ID lần lượt là KD1 và KD2.

Cách thực hiện tính toán với các ô có ký tự trùng khớp trong Excel

Bài toán này có vẻ phức tạp khi nghe lần đầu, nhưng thật ra có nhiều phương pháp tính tổng trong Excel và bạn sẽ ngạc nhiên khi biết rằng có một số cách giải quyết:

Cách 1: Sử dụng cột phụ để nhận diện các giá trị theo điều kiện

Với trường hợp các ký tự nằm lẫn trong chuỗi ký tự của mỗi ô, ta có thể dùng hàm để xác định từng ô có chứa ký tự cần tìm hay không. Ta chèn cột mới sau cột Doanh thu và dùng hàm SEARCH để xác định như sau:

Trong hàm IFERROR(SEARCH($C$1,A2),0), ta quan sát thấy hai điểm:

  • Đầu tiên: Ta tìm giá trị ở ô C1 (là KD1) trong ô A2. Nếu tìm thấy, kết quả trả về là vị trí đầu tiên mà ký tự xuất hiện trong ô A2. Nếu không tìm thấy, sẽ trả lỗi #VALUE.
  • Thứ hai: Hàm IFERROR được sử dụng để xử lý trường hợp error #VALUE không được tìm thấy bởi hàm SEARCH, qua đó thay thế lỗi bằng số 0.

Phương pháp tương tự được áp dụng cho giá trị KD2 ở ô D1.

Xem thêm: Hướng dẫn nhận biết loại lỗi và cách xử lý lỗi trong Excel

Giá trị nào lớn hơn 0 tức là bao gồm giá trị cần tìm, còn bằng 0 là không chứa giá trị cần tìm.

Giờ đây công việc đã đơn giản hơn, chỉ cần dùng hàm SUMIF để tính kết quả như sau:

Kết quả tổng doanh thu dành cho nhân viên có mã là KD1 là SUMIF(C2:C10,”>0″,B2:B10)

Đối với nhân viên có mã KD2, kết quả là SUMIF(D2:D10,”>0″,B2:B10)

Cách 2: Tính toán trực tiếp không cần cột phụ sử dụng hàm SUMIF

Thay vì phải thêm cột mỗi khi có điều kiện mới, tại sao không tính trực tiếp để làm gọn công việc cho Excel? Thực tế chúng ta hoàn toàn có thể tiến hành mà không cần cột phụ.

Dưới đây là cách viết công thức:

Đối với điều kiện là KD1, ta có:

H3=SUMIF(A2:A10,”*”&F3&”*”,B2:B10)

  • Khoảng điều kiện nằm ở cột A, từ A2 đến A10, chứa ID của nhân viên.
  • Điều kiện “*”&F3&”*” nghĩa là thêm hai dấu sao ở trước và sau giá trị trong ô F3. Do * là ký tự đặc biệt, cần được đặt trong dấu nháy kép và kết nối với ô F3 bằng dấu &.
  • Khoảng cần tính tổng là ở cột Doanh thu, từ ô B2 đến B10.

Kết quả nhận được cũng tương đương với cách thứ nhất.

Nếu bạn không muốn dùng hàm SUMIF, bạn có thể thay thế bằng hàm SUMIFS như sau:

H3=SUMIFS(B2:B10,A2:A10,”*”&F3&”*”)

CácChức năng của hàm SUMIF được giữ nguyên, tuy nhiên có sự đổi chỗ nhẹ khi phần tính tổng nay được xếp lên vị trí đầu tiên trong hàm.

Đọc thêm: Hàm SUMIF / SUMIFS Tính tổng dựa theo điều kiện cụ thể

Phương pháp thứ 3: Áp dụng hàm SUMPRODUCT

Có lẽ bạn đã được biết đến hàm SUMPRODUCT làm thay thế được cho hàm SUMIF, SUMIFS. Trong trường hợp này, chúng tôi sẽ phân tích xem liệu hàm SUMPRODUCT có khả năng ứng dụng hay không:

Cụm công thức tại ô I3 khi dùng hàm SUMPRODUCT được ghi như sau:

=SUMPRODUCT(($B$2:$B$10)*(IFERROR(SEARCH(F3,$A$2:$A$10),0)>0))

Bạn có thể thấy đoạn IFERROR(SEARCH(F3,$A$2:$A$10),0) chính là phần đã được xử lý ở cột bên cạnh như đã hướng dẫn trước đây.

Đưa nó vào bên trong hàm SUMPRODUCT, chúng ta sẽ kiểm tra kết quả thu được có lớn hơn 0 không, để từ đó lựa chọn những giá trị phù hợp để tính tổng ở cột về Doanh thu.

* Chú ý:

Phong cách ghi sau đây sẽ không mang lại kết quả chính xác:

=SUMPRODUCT(($B$2:$B$10)*($A$2:$A$10=”*”&F3&”*”)

Quả là thật thú vị đúng không. Như vậy, qua hướng dẫn từ Siêu Marketing, bạn đã biết thêm 3 cách giải quyết vấn đề này. Nếu bạn có phương pháp nào khác, đừng ngần ngại chia sẻ với chúng tôi.

Ngoài ra, bạn cũng có thể tham khảo thêm những bài viết liên quan đến chủ đề này:

Tại sao lại ưu tiên sử dụng hàm SUMIFS thay vì SUMPRODUCT để tính tổng theo nhiều điều kiện

Hướng dẫn chi tiết cách dùng hàm SUMPRODUCT để đếm dựa trên nhiều điều kiện

So sánh giữa hàm COUNTIFS và SUMPRODUCT về khả năng đếm theo nhiều điều kiện khác nhau

Trương Thành Tài
0
    0
    Đơn hàng
    Đơn hàng trốngQuay lại Shop