Chúng ta thường hay gặp phải một số yêu cầu về lập báo cáo trên Excel như: báo cáo chi tiết cho mặt hàng A trong tháng 5, báo cáo chi tiết cho nhân viên X bộ phận Y… và với những yêu cầu này thì chúng ta xử lý như thế nào? Hãy cùng Học Excel Online tìm hiểu về cách lập báo cáo chi tiết theo nhiều điều kiện trên Excel nhé.

Cấu trúc của báo cáo chi tiết nhiều điều kiện

Báo cáo chi tiết thường là dạng báo cáo mô tả diễn biến, chi tiết từng lần nội dung phát sinh hoặc xảy ra. Do đó báo cáo chi tiết thường có cấu trúc như sau:

Phần điều kiện của báo cáo sẽ nằm bên trên, bên ngoài bảng nội dung chi tiết. Có thể có nhiều hơn 1 điều kiện.

Phần nội dung chi tiết sẽ nằm phía dưới. Trong bảng bao gồm tên tiêu đề của các cột dữ liệu, nội dung tương ứng theo từng cột.

Xem thêm: Cách lập báo cáo tổng hợp trên Excel với công cụ VBA

Cách thiết lập vùng điều kiện trong báo cáo chi tiết

Mỗi điều kiện của báo cáo chi tiết đều gắn liền với 1 trường dữ liệu trong bảng dữ liệu gốc. Có 2 dạng cơ bản:

• Dạng nhập trực tiếp giá trị: thường gắn với các dữ liệu dạng Ngày tháng, dạng Số
• Dạng chọn từ 1 danh sách: thường gắn với các dữ liệu dạng Chuỗi văn bản (Text)

Do đó để đảm bảo điều kiện lập báo cáo là chính xác thì chúng ta cần thiết lập điều kiện nhập (Data validation) cho vùng điều kiện này.

• Dạng nhập trực tiếp giá trị: sử dụng Data validation chỉ cho phép nhập dữ liệu dạng Ngày tháng hoặc dạng Số
• Dạng chọn từ 1 danh sách: sử dụng Data validation tạo danh sách chọn để chọn 1 đối tượng

Xem thêm:

Cách thiết lập chỉ nhập dữ liệu dạng ngày tháng trong ô trên Excel

Hướng dẫn sử dụng Data Validation để nhập nhanh dữ liệu từ select box

Ví dụ như sau:

Dựa vào bảng dữ liệu trên lập báo cáo chi tiết theo tên mặt hàng trong thời gian từ ngày 01/5 đến ngày 31/5

Với yêu cầu như trên, vùng điều kiện của báo cáo chi tiết có thể xác định như sau:

• Điều kiện 1: Từ ngày 01/05/2018
• Điều kiện 2: Đến ngày 31/05/2018
• Điều kiện 3: Tên mặt hàng: chọn theo danh sách tên mặt hàng

Cách xác định nội dung trong báo cáo chi tiết

Nội dung trong báo cáo chi tiết là những thông tin trong bảng dữ liệu thỏa mãn các điều kiện lập báo cáo. Do đó chúng ta có thể kiểm tra nội dung này bằng cách sử dụng chức năng Auto Filter và tiến hành lọc thủ công trên từng trường dữ liệu.

Ví dụ: Thao tác lọc dữ liệu trong cột Ngày

Kết quả sau khi lọc bằng Auto Filter tại 2 cột Ngày và Tên mặt hàng

Như vậy nội dung cần báo cáo chính là kết quả lọc từ bảng dữ liệu. Tuy nhiên thao tác lọc bằng AutoFilter chỉ giúp chúng ta kiểm tra kết quả báo cáo, chứ không phải đây là báo cáo.

Để lấy kết quả ra báo cáo, chúng ta có thể dùng 3 cách:

Cách thứ 1: Copy kết quả lọc bằng Auto Filter

Các thao tác thực hiện như sau:

• Copy dữ liệu sau khi đã lọc bằng Auto Filter (bao gồm cả tiêu đề)
• Bỏ chức năng Auto Filter (chọn thẻ Data rồi bấm lại vào mục Filter)
• Đặt chuột tại ô cần dán kết quả, chọn Paste Special > Value để dán phần giá trị
• Tiếp tục chọn Paste Special > Format để dán phần định dạng
• Chỉnh độ rộng cho các cột của báo cáo và hoàn thành

Cách này khá thủ công, phải thực hiện qua nhiều thao tác tay, không tùy biến được phần nội dung báo cáo nên không khuyến khích áp dụng. Tuy nhiên trong những trường hợp bí và chưa có cách nào hay hơn thì đây cũng là 1 cách cho ra kết quả đúng.

Cách thứ 2: Dùng hàm Logic lọc giá trị phù hợp

Hàm logic là các hàm IF, AND, OR để biện luận tìm ra giá trị phù hợp. Các giá trị không phù hợp sẽ bị loại bỏ thành ô trống

Với cách này chúng ta có thể tùy biến cấu trúc phần nội dung báo cáo: chỉ báo cáo cho 1 số cột nhất định

Ví dụ như sau:

Trong cột Ngày:

Để lấy giá trị cột ngày, xét nếu từng nội dung ở dòng 2 thỏa mãn đồng thời cả 3 điều kiện (trong hàm AND gồm 3 điều kiện) thì lấy kết quả theo ô A2. Nếu không thỏa mãn thì trả về giá trị rỗng (ô trống)

Như vậy chỉ có 4 giá trị thỏa mãn

Các trường dữ liệu còn lại thì chúng ta chỉ cần xét: Nếu giá trị ngày của báo cáo là ô trống thì không lấy nội dung, còn có giá trị thì lấy tương ứng theo cột đang xét.

Để có thể loại bỏ các dòng trống xen kẽ, chúng ta sẽ tạo thêm 1 cột phụ để lọc bỏ dòng trống với hàm logic:

Nếu giá trị cột ngày là rỗng thì kết quả là rỗng, nếu không rỗng thì lấy giá trị bất kỳ (ví dụ là “x”)

Sau đó sử dụng Auto filter tại cột lọc này, loại bỏ các giá trị rỗng (blank) đi. Kết quả thu được là báo cáo chi tiết (không bao gồm cột lọc)

Cách này giảm bớt được thao tác tay khi lập báo cáo. Tuy nhiên vẫn còn thao tác tay khi lọc tại cột phụ và xuất hiện thêm cột phụ

Xem thêm: Hàm IF, lồng ghép hàm IF với nhiều điều kiện, IFERROR, IFNA và nhiều hơn nữa

Cách thứ 3: Sử dụng Advanced Filter để lập báo cáo chi tiết

Cách này các bạn có thể xem tại bài viết:

Hướng dẫn cách lập báo cáo chi tiết NXT kho bằng Advanced Filter trong Excel

Đây là cách làm khá hay, khi vùng điều kiện được kiểm soát tốt hơn là làm trực tiếp trong công thức, giúp giảm dung lượng file nhờ hạn chế công thức.

Ngoài ra việc kết hợp VBA để làm báo cáo tự động thông qua thao tác Advanced Filter cũng khá dễ dàng.

Như vậy thông qua bài viết này, chúng ta đã có thể biết cách xây dựng 1 mẫu báo cáo chi tiết theo nhiều điều kiện, và có tới 3 cách để hoàn thành báo cáo chi tiết đó.

Những kiến thức này các bạn đều có thể học được trong khóa học Excel từ cơ bản tới chuyên gia của Học Excel Online. Trong khóa học các bạn còn học thêm được nhiều kiến thức khác liên quan tới các hàm, các công cụ trong Excel để ứng dụng vào công việc được tốt hơn. Thông tin chi tiết mời bạn bấm vào hình bên dưới: