Thông thường khi lập báo cáo trên Excel chúng ta hay quan tâm tới việc dùng hàm gì. Nhưng hầu hết không nhận ra rằng cấu trúc báo cáo như thế nào mới là yếu tố quan trọng. Cấu trúc khoa học, hợp lý sẽ giúp chúng ta dễ dàng hơn trong việc lựa chọn hàm, sử dụng hàm. Nếu chỉ tập trung vào nghĩ dùng hàm gì thì chúng ta sẽ gặp nhiều khó khăn với những yêu cầu báo cáo phức tạp, nhiều điều kiện. Hãy cùng Học Excel Online tìm hiểu cách lập báo cáo tổng hợp theo nhiều điều kiện trên Excel nhé:

Để giúp các bạn dễ hình dung 1 yêu cầu lập báo cáo tổng hợp theo nhiều điều kiện thì chúng ta xét ví dụ sau:

Cho bảng dữ liệu như hình trên (khoảng 60 dòng dữ liệu). Yêu cầu của chúng ta là phải lập báo cáo số lượng nhập xuất của từng mặt hàng trong từng kho, và chỉ tính cho tháng 5

Phân tích yêu cầu lập báo cáo

Nghe thật phức tạp phải không nào. Với yêu cầu này chúng ta có tới 4 điều kiện:

• Điều kiện 1: Tên mặt hàng
• Điều kiện 2: Loại nhập – xuất
• Điều kiện 3: Theo từng kho
• Điều kiện 4: Tính trong tháng 5

Vậy thì để thực hiện yêu cầu này, quan trọng nhất là chúng ta cần phải xác định được cấu trúc của báo cáo này như thế nào? 4 điều kiện sẽ được bố trí trên 1 bảng báo cáo ra sao để có thể sử dụng công thức tính một cách dễ dàng nhất?

Xem thêm: Tìm hiểu về các dạng báo cáo thường gặp trên Excel

Cách tổ chức bảng báo cáo nhiều điều kiện

Bạn có biết báo cáo có tối đa bao nhiêu chiều không? Cách bố trí sắp xếp các chiều điều kiện như thế nào? Câu trả lời là 1 bảng báo cáo có tối đa là 3 chiều. Với số điều kiện lập báo cáo lớn hơn 3 thì chúng ta vẫn có thể tổ chức vào trong 3 chiều mà thôi. Cách tổ chức như sau:

• Row: Điều kiện được tổ chức trên nhiều dòng, trong cùng 1 cột, thường nằm bên trái bảng báo cáo. Các đối tượng chi tiết hơn của điều kiện này thì sẽ nằm ở các cột bên phải mỗi nhóm và ở các dòng chi tiết so với điều kiện nhóm.
• Column: Điều kiện được tổ chức trên nhiều cột, trong cùng 1 dòng, thường nằm phía trên bảng báo cáo. Các đối tượng chi tiết hơn của điều kiện này thì sẽ nằm ở các cột bên trong mỗi nhóm và ở dòng bên dưới
• Filter: là những điều kiện có tác dụng lọc, giới hạn phạm vi tính toán của báo cáo mà không phải yếu tố được liệt kê như trong Row và Column.

Cụ thể trong yêu cầu này chúng ta có thể bố cục như sau:

• Tháng = 5: điều kiện có vai trò lọc, giới hạn phạm vi tính toán => Vị trí Filter
• Tên mặt hàng: nằm ở Row
• Tên kho và chi tiết Nhập-xuất mỗi kho: nằm ở Column

Cách viết công thức tính kết quả báo cáo

Ngoài việc xác định các điều kiện của báo cáo, chúng ta còn cần phải xác định yêu cầu tính toán của báo cáo để lựa chọn ra công thức, hàm phù hợp. Trong yêu cầu này ta có:

• Tính theo nhiều điều kiện (4 điều kiện)
• Tính tổng số lượng => Tính tổng, vùng tính tổng là cột Số lượng.

Do đó hàm sử dụng trong trường hợp này là hàm SUMIFS

Cách viết hàm SUMIFS trong trường hợp này như sau:

• Sum_range: Cột E là cột Số lượng (vì bảng dữ liệu có từ dòng 2 tới dòng 57 nên ta sẽ tính trong vùng E2:E57
• Criteria_range1: Vùng điều kiện 1 là Tên mặt hàng (cột D), vùng D2:D57
• Criteria1: Điều kiện 1 nằm trong vùng điều kiện 1. Đó là tên mặt hàng được lấy theo phần Row của báo cáo
• Criteria_range2: Vùng điều kiện 2 là Tên kho (cột C), vùng C2:C57
• Criteria2: Điều kiện 2 nằm trong cột Tên Kho là Kho, tương ứng Kho A, Kho B, Kho C
• Criteria_range3: Vùng điều kiện 3 là Loại (cột B), vùng B2:B57
• Criteria3: Điều kiện 3 nằm trong cột Loại là “Nhập” hoặc “Xuất” tương ứng theo cột
• Criteria_range4: Vùng điều kiện 4 là thời gian (cột A), vùng A2:A57
• Criteria4: ở đây là giới hạn trong tháng 5. Trong khi dữ liệu là dữ liệu ngày. Vì vậy phải tách điều kiện này ra 2 điều kiện nhỏ hơn là Từ ngày và đến ngày. Cụ thể là từ ngày 01/05 đến ngày 31/05 (để phù hợp với dữ liệu trong vùng điều kiện).

Kết quả của hàm như sau:

Công thức tại ô B4

=SUMIFS(Sheet1!$G$2:$G$57,Sheet1!$D$2:$D$57,$A4,Sheet1!$C$2:$C$57,B$2,Sheet1!$B$2:$B$57,B$3,Sheet1!$A$2:$A$57,“>=”&DATE(2018,5,1),Sheet1!$A$2:$A$57,“<=”&DATE(2018,5,31))

Công thức tại ô C4

=SUMIFS(Sheet1!$G$2:$G$57,Sheet1!$D$2:$D$57,$A4,Sheet1!$C$2:$C$57,B$2,Sheet1!$B$2:$B$57,C$3,Sheet1!$A$2:$A$57,“>=”&DATE(2018,5,1),Sheet1!$A$2:$A$57,“<=”&DATE(2018,5,31))

Chúng ta thấy công thức khá dài phải không nào? Nhưng khi phân tích cụ thể từng điều kiện (phần in đậm trong công thức) và từng vùng điều kiện, vùng tính tổng thì chúng ta có thể hiểu được công thức một cách dễ dàng rồi.

Các bạn có thể tải về file mẫu tại địa chỉ: http://bit.ly/2zd3SbZ

Xem thêm:

Hướng dẫn cách viết điều kiện hàm SUMIFS để đạt hiệu quả tốt nhất

Hàm SUMIFS – tính tổng theo tuần, tháng, năm

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: