Lập sổ tổng hợp N-X-T- Ứng dụng công thức mảng
.jpg.aspx)
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:
=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).
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"