Chia sẻ cách tạo vùng công thức Excel kiểm tra nhanh tồn kho

 Trong quản lý kho trên phần mềm hay trên Excel thì người dùng thường có phát sinh nhu cầu kiểm tra tồn kho nhanh. 

Bài viết này đưa ra phương án thiết kế với cấu trúc để kiểm tra nhanh tồn kho như sau:

- Dữ liệu nhập kho và xuất kho nhập trong 1 sheet, phân biệt thông qua cột "Loại nhập xuất". Giả sử ký hiệu N là Nhập, X là xuất. Trường hợp muốn phân chia thành nhiều loại nhập xuất thì có thể thêm các hậu tố. Ví dụ: X_SX, X_BAN, X_NB,..,N_MUA, N_SX, N_TL,...

- Vùng bên trên để khoảng chục dòng. Ví dụ từ dòng số 1-10 để làm công thức kiểm tra nhanh. Vùng bên dưới là nhập số liệu chi tiết

- Dữ liệu chi tiết phải đảm bảo cấu trúc theo cột

CÔNG THỨC EXCEL TẠI VÙNG KIỂM TRA NHANH

Giả sử các cột: A- ngày, C - Loại phiếu (Ký hiệu N là Nhập, X là xuất), F - Mã hàng, G - Số lượng, H - Đơn giá, I - Thành tiền, J - Tiền thuế (ngoài ra có thể có nhiều cột khác tùy theo nhu cầu quản trị thông tin của người dùng)

Công thức kiểm tra tồn kho nhanh tồn kho tại thời điểm:

Bản chất kiểm tra nhanh tồn kho theo thời điểm là cần biết tồn kho của 1 mặt hàng đến ngày nào. Như vậy công thức sẽ là: Tổng nhập trước ngày - Tổng xuất trước ngày

Ví dụ: Ô H2 nhập ngày kiểm tra, ô D3 nhập mã hàng thì công thức kiểm tra nhanh tồn kho tại thời điểm là:

=SUMIFS(G:G,F:F,H3,A:A,"<="&H2,C:C,"N*")-SUMIFS(G:G,F:F,H3,A:A,"<="&H2,C:C,"X*")

Trường hợp tồn kho đầu kỳ ở sheet khác thì sẽ dùng hàm VLOOKUP để cộng vào phép tính trên. Tức tồn kho tức thời =Tồn đầu kỳ + Tổng nhập trước ngày - Tổng xuất trước ngày

Công thức kiểm tra nhanh theo điều kiện Filter

Bản chất của việc này là khi Filter theo điều kiện gì thì sẽ hiển thị kết quả theo điều kiện Filter. Excel hỗ trợ 2 hàm cho phép tính toán theo Filter là hàm SUBTOTAL và AGGREGATE

Ví dụ: Công thức tính tổng nhanh cho cột số lượng

=SUBTOTAL(9,INDIRECT("G10:G10000"))

hoặc

=AGGREGATE(9,3,INDIRECT("G10:G10000"))

Liên hệ tư vấn khóa học Excel cho người đi làm & đặt hàng đào tạo tại doanh nghiệp

{Đt Zalo} - 038 696 1334