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

Trong công việc quản lý kho, chúng ta thường xuyên phải theo dõi chi tiết tình hình nhập-xuất-tồn (NXT) của 1 mặt hàng trong 1 khoảng thời gian cụ thể. Tuy nhiên việc này có thể khiến bạn gặp nhiều khó khăn khi không biết phải dùng công thức nào để báo cáo, báo cáo như thế nào cho đẹp. Sau đây Học Excel Online sẽ hướng dẫn cho bạn cách lập báo cáo chi tiết NXT kho bằng cách sử dụng công cụ Advanced Filter trong Excel.

Trước tiên các bạn cần nắm được cách lọc dữ liệu sang sheet khác bằng Advanced Filter trong Excel. Chúng ta sẽ dựa trên tính năng đó của Advanced Filter để lập báo cáo chi tiết.

1. Yêu cầu

Cho bảng dữ liệu kho tại Sheet1. Yêu cầu lập báo cáo chi tiết N-X kho cho mặt hàng Táo trong Sheet2 theo thời gian tùy chọn tại ô B3 và B4 trong Sheet2

2. Cách thực hiện

a. Trong Sheet2 chúng ta sẽ thiết lập lại vùng điều kiện để có thể sử dụng cho Advanced Filter như sau:

Khi thiết lập điều kiện trong Advanced Filter chúng ta lưu ý: Phải bao gồm tên tiêu đề cột và Điều kiện cần lọc trong cột đó:

• Bởi vì giá trị thời gian nằm trong cột Ngày, trong đó Từ ngày được hiểu là Lớn hơn hoặc bằng ngày đó, còn Đến ngày được điều là Nhỏ hơn hoặc bằng ngày đó => Lập điều kiện cho 2 giá trị thời gian tại 2 ô F3 và G3 riêng
• Mặt hàng Táo nằm trong cột Tên hàng.

b. Sử dụng Advanced Filter

Muốn trích xuất ra báo cáo chi tiết những nội dung gì thì chúng ta cần xác định rõ những trường (cột) dữ liệu cần lấy. Cụ thể ở đây chúng ta sẽ lấy kết quả ở các cột Ngày, Loại và Số lượng. (không cần lấy tên hàng bởi báo cáo chi tiết đã xác định rõ tên hàng cần lấy là mặt hàng nào)

Do đó chúng ta sẽ lấy tên tiêu đề tương ứng cần lấy trong báo cáo chi tiết:

Trong Advanced Filter chúng ta thiết lập như sau:

Trong đó:

• List range: Vùng bảng dữ liệu tại sheet1, từ A1:D24
• Criteria range: Vùng điều kiện đặt tại sheet2 (Sheet báo cáo), từ E2:G3
• Copy to: Vị trí đặt báo cáo: tại sheet2, từ A6:C6 (chính là vùng tiêu đề của 3 cột cần lấy kết quả)

Bấm OK để hoàn tất thao tác.

Kết quả thu được như sau:

c. Sử dụng hàm SUMIFS để tính số tồn

Việc sử dụng Advanced Filter chỉ giúp chúng ta trích lọc được phần phát sinh của mặt hàng chứ không xác định được số tồn kho (Tồn đầu và Tồn cuối)

Để có thể tính được số Tồn đầu và Tồn cuối chúng ta thực hiện như sau:

Tồn đầu = Tổng nhập tới trước ngày báo cáo – Tổng xuất tới trước ngày báo cáo

Tồn cuối = Tồn đầu + Nhập trong kỳ – Xuất trong kỳ

Tổng nhập trước ngày báo cáo

=SUMIFS(Sheet1!$D$2:$D$24,Sheet1!$B$2:$B$24,Sheet2!B2,Sheet1!$A$2:$A$24,”<“&Sheet2!B3,Sheet1!$C$2:$C$24,”Nhập”)

• Sheet1!$D$2:$D$24: Phần nội dung của cột Số lượng
• Sheet1!$B$2:$B$24: Phần nội dung của cột Tên hàng
• Sheet2!B2: Tên mặt hàng cần báo cáo
• Sheet1!$A$2:$A$24: Phần nội dung của cột Ngày
• “<“&Sheet2!B3: Nhỏ hơn ngày bắt đầu tại ô B3
• Sheet1!$C$2:$C$24: Phần nội dung của cột Loại
• “Nhập”: Điều kiện của cột Loại là các phát sinh Nhập

Tương tự ta có Tổng xuất trước ngày báo cáo

=SUMIFS(Sheet1!$D$2:$D$24,Sheet1!$B$2:$B$24,Sheet2!B2,Sheet1!$A$2:$A$24,”<“&Sheet2!B3,Sheet1!$C$2:$C$24,”Xuất”)

(Chỉ cần thay đổi điều kiện “Nhập” thành “Xuất”)

Kết quả báo cáo hoàn thiện như sau:

3. Đánh giá ưu nhược điểm

a. Ưu điểm

Với việc sử dụng Advanced Filter thì chúng ta không cần sử dụng nhiều công thức, chỉ cần sử dụng tốt Advanced Filter là được.

Với các dạng báo cáo phức tạp thì chỉ cần làm đúng vùng điều kiện là có thể ra được kết quả báo cáo. Việc này dễ hơn nhiều so với các phương pháp khác

b. Nhược điểm

Mỗi khi thay đổi đối tượng cần báo cáo (tên hàng, thời gian báo cáo…) cần thực hiện lại lệnh Advanced Filter. Việc này có thể khiến bạn thấy bất tiện.

Cách khắc phục: sử dụng VBA để tự động hóa thao tác Advanced Filter

Tải về file mẫu: http://bit.ly/2IYawWY