Excel đã phát triển công cụ mạnh mẽ và hữu ích để cộng giá trị thỏa mãn một hay nhiều điều kiện là hàm SUMIF và SUMIFS. Nhưng để tìm tổng các giá trị có các ô có một đoạn mã giống nhau thì sẽ sử dụng công thức nào? Đây là câu hỏi rất thú vị mà nhiều bạn đọc gửi cho Thủ Thuật Phần Mềm. Hãy tìm hiểu cách làm trong bài viết sau đây nhé.
Sử dụng hàm SUMIF và cột phụ
Bạn xét ví dụ cụ thể sau:
Bước 1: Sử dụng cột phụ để nhận diện các dòng có kí tự thỏa mãn điều kiện.
(1) Bạn tạo cột phụ với tiêu đề là Áo sơ mi và Nữ.
(2) Trên cột điều kiện Áo sơ mi, cụ thể ô H3, bạn gõ công thức là: H3 =IFERROR(SEARCH(“Áo sơ mi”,D3),0). Có nghĩa là Excel sẽ tìm trong ô D3 có kí tự nào thỏa mãn Áo sơ mi và trả về vị trí của kí tự đó trong chuỗi. Còn nếu không có kí tự nào thỏa mãn điều kiện thì sẽ trả về vị trí 0. Sao chép công thức cho toàn bộ các ô trong cột bạn thu được kết quả như sau:
(3) Tương tự với cột Nữ, bạn thay điều kiện từ Áo sơ mi => Nữ và thu được kết quả:
Như vậy, những dòng có mã thỏa mãn điều kiện thì cột phụ sẽ có giá trị lớn hơn 0.
Bước 2: Cộng các dòng có giá trị lớn hơn không ở cột phụ bằng hàm SUMIF.
Để cộng Doanh thu theo điều kiện sản phẩm là Áo sơ mi, ở ô K3 bạn gõ công thức: K3 =SUMIF(H3:H12,”>0″,G3:G12).
Tương tự với ô K4, bạn gõ công thức K4 =SUMIF(I3:I12,”>0″,G3:G12) để cộng Doanh thu thỏa mãn điều kiện sản phẩm chứa mã kí tự là nữ.
Lưu ý: Với cách làm trên, Excel không phân biệt kí tự là chữ in hoa hay in thường.
Sử dụng hàm SUMIF và ký tự đại diện
Nếu như bạn không muốn sử dụng cách thêm cột phụ, bạn có thể sử dụng kết hợp với dấu sao (*) là kí tự đại diện cho bất kì kí tự nào theo công thức:
=SUMIF(mảng tham chiếu điều kiện,”*”& “chuỗi kí tự tìm kiếm“&”*”,vùng cần tính tổng). Bạn thay đổi mục chữ in nghiêng phù hợp với báo cáo của mình.
Với ví dụ tương tự như trên Thủ Thuật gõ công thức: K3 =SUMIF(D3:D12,”*”&”Áo sơ mi”&”*”,G3:G12), K4 =SUMIF(D3:D12,”*”&”nữ”&”*”,G3:G12).
Và kết quả thu được là:
Lưu ý: Để có thể sao chép công thức, bạn nên sử dụng vị trí tuyệt đối để cố định mảng tham chiếu và vùng cần tính tổng. Bạn thay thế công thức trên bằng K3=SUMIF($D$3:$D$12,”*”&J3&”*”,$G$3:$G$12). Trong đó J3 là ô chứa chuỗi kí tự cần tìm kiếm. Kết quả của ô tính không thay đổi.
Sử dụng hàm SUMPRODUCT
Hàm SUMPRODUCT là một trong những hàm dạng mảng đặc biệt của Excel.
SUMPRODUCT sử dụng phạm vi ô làm đối số của nó cùng nhau các mục trong các mảng, rồi tổng kết quả.
Với ví dụ trên, bạn nhập công thức: K3 =SUMPRODUCT((G3:G12)*(IFERROR(SEARCH(“áo sơ mi”, D3:D12), 0)>0 )), rồi ấn Ctrl +Shif + Enter.
- Với công thức trên, hàm IFERROR(SEARCH(“áo sơ mi”, D3:D12), 0) sẽ tìm kiếm các ô trong cột D có kí tự là áo sơ mi và trả về giá trị lớn hơn 0, nếu không có sẽ trả về bằng 0.
- Khi đặt công thức trên vào hàm SUMPRODUCT, Excel sẽ so sánh kết quả đó có >0 hay không, và cộng tổng doanh thu trong cột Doanh thu (Cột G) tương ứng với giá trị trên >0.
Lưu ý: Mặc dù công thức SUMPRODUCT là công thức dạng mảng, có nghĩa là bạn ko phải áp dụng tổ hợp phím Ctrl + Shift + Enter ở trường hợp thông thường. Tuy hiên với trường hợp này, bắt buộc bạn phải sử dụng tổ hợp phím Ctrl +Shif + Enter nếu không sẽ không ra kết quả chính xác.
Với cách làm tương tự với ô K4, Thủ Thuật Phần Mềm tìm ra tổng doanh thu thỏa mãn các mặt hàng có kí tự nữ. Chúc các bạn thành công!