Cách lập sổ tổng hợp N-X-T ứng dụng công thức mảng trên Excel

[bluesoftsbl1]

​

Trong công tác quản lý vật tư, hàng hóa trong kho chúng ta cần phải làm thẻ kho, báo cáo tổng hợp nhập-xuất-tồn để quản lý tình hình biến động của vật tư, hàng hóa tại kho trong một khoảng thời gian nào đó (ngay1-ngay2).
Phương pháp tính:
Bước 1: quan sát cấu trúc bảng dữ liệu gốc trong sheet "KHO":
Cột B=>Ngày CT; G=>Mã hàng; H=>Số lượng; J=>Loại phiếu; K=> Thành tiền
Các cột dữ liệu trong sheet "KHO" sẽ được dùng để làm báo cáo TH NXT.
Bước 2: Lập công thức
1. Tồn đầu: lấy tổng số lượng nhập - tổng lượng xuất của mỗi mặt hàng mà ngày chứng từ < ngay1
Công thức tính tổng số lượng nhập mà ngày CT < NGAY1: Tính tổng số lượng, nếu vùng hàng hóa =mã hàng; nếu vùng loại phiếu = "N"; nếu ngày ct < NGAY1. Công thức mảng trong Excel như sau:....
Xem chi tiết tại đây:
http://bluesofts.net/kien-thuc-excel/214-lapsotonghopnxt.html

 

[bluesoftsbl1]

Phươngpháptính:
Bước 1: quan sát cấu trúc bảng dữ liệu gốc trong sheet "KHO":
Cột B=>Ngày CT; G=>Mã hàng; H=>Số lượng; J=>Loại phiếu; K=>Thành tiền
Các cột dữ liệu trong sheet "KHO" sẽ được dùng để làm báo cáo TH NXT.
Bước 2: Lậpcôngthức
1. Tồn đầu: lấy tổng số lượng nhập - tổng lượng xuất của mỗi mặt hàng mà ngày chứng từ< ngay1
Công thức tính tổng số lượng nhập mà ngày CT < NGAY1: Tính tổng số lượng, nếu vùng hàng hóa =mã hàng; nếu vùng loại phiếu = "N"; nếu ngày ct< NGAY1. Công thức mảng trong Excel như sau:
=SUM(IF(KHO!$G$4:$G$68=$C12,
IF(KHO!$J$4:$J$68="N",
IF(KHO!$B$4:$B$68<NGAY1,KHO!$H$4:$H$68,0),0),0))
Để tính tổng số lượng xuất giống như nhập nhưng thay loại phiếu (cột J) = "X". Đến đây ta có công thức tính tồn đầu (tổng nhập-tổng xuất; ngày<NGAY1) đầy đủ như sau:
=SUM(IF(KHO!$G$4:$G$68=$C12,
IF(KHO!$J$4:$J$68="N",
IF(KHO!$B$4:$B$68<NGAY1,KHO!$H$4:$H$68,0),0),0))-
SUM(IF(KHO!$G$4:$G$68=$C12,
IF(KHO!$J$4:$J$68="X",
IF(KHO!$B$4:$B$68<NGAY1,KHO!$H$4:$H$68,0),0),0))
Kết thúc phải nhấn CTRL+SHIFT+ENTER. Vì đây là cấu trúc công thức mảng (Một vùng so sánh với một giátrị, rồi tính cả một vùng. Muốn Excel kiểm tra và tính từng phần tử trong mảng thì phải "ép" nó thành công thức mảng).
HỌC CÔNG THỨC MẢNG tạiđây:
http://ketoanmay2611.blogspot.com/…/sumproduct-va-cong-thuc…
2. Nhập: Tính tổng số lượng nhập của mỗi mặt hàng mà loại phiếu ="N", ngày chứng từ>= ngay1 và<=ngay2. Công thức mảng là:
=SUM(IF(KHO!$G$4:$G$68=$C12,
IF(KHO!$J$4:$J$68="N",
IF(KHO!$B$4:$B$68>=NGAY1,
IF(KHO!$B$4:$B$68<=NGAY2,
KHO!$H$4:$H$68,0),0),0),0))
3. Xuất: Copy công thức bên Nhập rồi sửa "N" thành "X"
4. Tồn: =Tồnđầu + Nhập - Xuất (đây là công thức thường nên chỉ cần ENTER.
Các cột thành tiền phương pháp giống hệt với số lượng, chỉ cần copy rồi đổi tên cột "H" thành cột "K"
DOWNLOAD file dữ liệu và công thức tạo báo cáo
http://bluesofts.net/…/daotao/excel/…/thnxt/DU_LIEU_KHO.xlsx
Bên cạnh đó các bạn tham khảo thêm cách làm báo nhanh gọn và đặc biệt giúp cho báo cáo được tạo ra có dung lượng thấp thông qua công cụ Add-in A-Tools, một công cụ chuyên trích lọc dữ liệu và tạo báo động trong Excel qua mạng và cho nhiêu người sử dụng.
DOWNLOAD dùng thử miễn phí tại đây