Excel là một công cụ mạnh mẽ và hữu ích để tính toán giá trị theo nhiều điều kiện khác nhau. Với các hàm SUMIF và SUMIFS, bạn có thể tính tổng các giá trị từ các ô chứa một đoạn mã giống nhau. Điều này đặt ra một câu hỏi thú vị: làm thế nào để làm điều này? Hãy cùng khám phá trong bài viết này nhé!
Áp dụng hàm SUMIF và tạo cột phụ
Hãy xem xét ví dụ sau:
Bước 1: Sử dụng cột phụ để xác định các dòng chứa kí tự thỏa mãn điều kiện.
(1) 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, đặt tại ô H3, nhập công thức: H3 =IFERROR(SEARCH('Áo sơ mi',D3),0)
. Công thức này 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. Nếu không có kí tự nào thỏa mãn điều kiện, sẽ trả về vị trí 0. Sao chép công thức cho toàn bộ các ô trong cột để có kết quả như sau:
(3) Tương tự với cột Nữ, thay đổi điều kiện từ Áo sơ mi sang Nữ để thu được kết quả:
Những hàng chứa mã thỏa mãn điều kiện sẽ có giá trị lớn hơn 0 trong cột phụ.
Bước 2: Tổng các hàng có giá trị lớn hơn 0 trong cột phụ sử dụng hàm SUMIF.
Để tính tổng Doanh thu theo điều kiện sản phẩm là Áo sơ mi, tại ô K3 nhập công thức: K3 =SUMIF(H3:H12,'>0',G3:G12)
.
Tương tự, ở ô K4, nhập công thức K4 =SUMIF(I3:I12,'>0',G3:G12)
để tính tổng Doanh thu thỏa mãn điều kiện sản phẩm chứa mã kí tự là nữ.
Chú ý: Trong quá trình thực hiện, Excel không phân biệt kí tự là chữ in hoa hay chữ thường.
Sử dụng hàm SUMIF và ký tự đại diện
Nếu bạn không muốn thêm cột phụ, có thể kết hợp với dấu sao (*) – ký tự đại diện cho bất kỳ kí tự nào trong 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)
. Thay đổi “mục in nghiêng” theo nhu cầu của bạn.
Đối với ví dụ trước, Mytour đề xuất cách gõ công thức như sau: K3 =SUMIF(D3:D12,'*'&'Áo sơ mi'&'*',G3:G12), K4 =SUMIF(D3:D12,'*'&'nữ'&'*',G3:G12)
.
Kết quả thu được là:
Chú ý: Để sao chép công thức một cách hiệu quả, 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. Thay thế công thức 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 công cụ đặc biệt của Excel để xử lý dữ liệu mảng.
SUMPRODUCT sử dụng các phạm vi ô làm đối số, nhân và cộng các mục tương ứng trong các mảng để tổng kết quả.
Trong ví dụ này, bạn nhập công thức: K3 =SUMPRODUCT((G3:G12)*(IFERROR(SEARCH('áo sơ mi', D3:D12), 0)>0 ))
, sau đó nhấ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 chứa kí tự áo sơ mi và trả về giá trị lớn hơn 0, nếu không trả về 0. - Khi đặt công thức này vào hàm SUMPRODUCT, Excel sẽ kiểm tra kết quả có >0 hay không, và tổng hợp doanh thu tương ứng trong cột Doanh thu (Cột G) nếu giá trị >0.
Lưu ý: Trong trường hợp này, bạn cần sử dụng tổ hợp phím Ctrl +Shif + Enter
khiến công thức SUMPRODUCT trở nên đặc biệt.
Tương tự, bạn có thể áp dụng cách tương tự để tính tổng doanh thu từ các mặt hàng chứa kí tự nữ. Chúc bạn thành công!
Trên trang web sara.edu.vn, chúng tôi cập nhật thông tin nhanh chóng, với các tin tức đa dạng về ẩm thực, phong thủy, công nghệ, giáo dục, làm đẹp,… liên tục và chính xác 24/7.