Hôm nay Học Excel Online sẽ hướng dẫn các bạn một hàm có tính ứng dụng rất cao trong Excel. Hàm được sử dụng nhiều trong việc tính tổng theo nhiều điều kiện. Tuy nhiên trong những báo cáo mà điều kiện được lặp lại nhiều lần theo một nguyên tắc thì phải làm thế nào? Bài viết này sẽ hướng dẫn các bạn cách viết điều kiện trong hàm SUMIF để đạt hiệu quả tốt nhất, tức là có thể sao chép công thức sang vị trí khác mà vẫn xác định đúng điều kiện cần tính.

Để tìm hiểu nội dung này chúng ta sẽ đi giải quyết bài toán sau:

Đề bài: Cho bảng dữ liệu tại vùng F2:H10, tính các kết quả tại vùng B4:D7 theo các điều kiện tương ứng tại cột A (Mã) và dòng 3 (Ngày)

Cách viết điều kiện hàm SUMIFS

Bước 1: Phân tích đề bài

• Đề bài yêu cầu tính tổng theo 2 điều kiện là Ngày và Mã, do đó ta không thể sử dụng hàm SUMIF mà phải sử dụng hàm SUMIFS (ở đây chúng ta bỏ qua các hàm khác mà chỉ xét tính ứng dụng của hàm SUMIF/SUMIFS)
• Điều kiện cần tính không phải cố định mà tùy biến, viết 1 hàm SUMIFS tại B4 rồi copy hàm đó sang các ô khác để tính
• Điều kiện Mã và Ngày có thể thay đổi trong công thức.

Bước 2: Xây dựng công thức SUMIFS

Cấu trúc hàm SUMIFS(sum_range, Criteria_range1, Criteria1, …)

• Sum_range: là cột Số lượng trong vùng bảng dữ liệu F2:H10
• Criteria_range1: là cột Mã trong vùng bảng dữ liệu F2:H10
• Criteria1: là điều kiện về Mã (để tương ứng với Criteria_range1), với ô B4 là điều kiện tại ô A4
• Criteria_range2: là cột Ngày trong vùng bảng dữ liệu F2:H10
• Criteria2: là điều kiện về Ngày (tương ứng với Criteria_range2), với ô B4 là điều kiện tại ô B3

Do đó công thức tại ô B4 có thể viết như sau:

B4=SUMIFS(H3:H10,F3:F10,A4,G3:G10,B3)

Xem thêm: Cách cố định dòng, cố định cột trong Excel

Bước 3: Tùy biến điều kiện trong hàm SUMIFS

Với việc copy sang bên phải (FillRight từ B4 tới D4): với thao tác này cần cố định điều kiện tại cột A, nên ô A4 sẽ cố định cột là $A4

Với việc copy xuống dưới (FillDown từ B4 tới B7): với thao tác này cần cố định điều kiện tại dòng 3, nên ô B3 sẽ cố định cột là B$3

Vùng dữ liệu F3:H10 cũng cần cố định để khi copy công thức thì vùng này không thay đổi

Do đó công thức tại B4 sẽ được xây dựng lại thành:

B4=SUMIFS($H$3:$H$10,$F$3:$F$10,$A4,$G$3:$G$10,B$3)

Khi sao chép công thức sang các ô còn lại ta sẽ được kết quả đúng như sau:

* Áp dụng:

Cách này thường dùng trong các yêu cầu về lập báo cáo chi tiết, báo cáo theo nhiều đối tượng cùng lúc.

Tham khảo:

Cách lập báo cáo tổng hợp theo từng tháng với hàm SUMIFS

Công thức SUMIFS tính tổng hợp Nhập – Xuất – Tồn báo cáo kho

Ngoài ra Học Excel Online xin giới thiệu với các bạn Khóa học Excel từ cơ bản tới nâng cao dành cho người đi làm. Đây là 1 khóa học rất đầy đủ kiến thức và bổ trợ rất tuyệt vời cho bạn trong việc làm quen với Excel, sử dụng các công cụ, các hàm trong Excel để giúp bạn làm việc trên Excel được tốt hơn, hiệu quả hơn. Chi tiết xem tại: