LẬP SỔ TỔNG HỢP N-X-T - Ứng dụng Công thức mảng trong Excel

[Tuanktcdcn]

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).
HỌC CÔNG THỨC MẢNG tại đây:
http://bluesofts.net/kien-thuc-exce...g-thuc-mang-phep-tinh-co-nhieu-dieu-kien.html
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/Products/daotao/excel/nangcao/thnxt/DU_LIEU_KHO.xlsx

 

[ng.thuthaogl]

Thanks bạn vì bài viết có ích

 

[trinhbuitungquan]

ad có thể cho em xin file này được không?em đang rất cần ạ
em xin cho hành chính bên em quản lý tài sản của công ty và văn phòng phẩm ạ
em cin cảm ơn nhiều ạ.

 

[trinhbuitungquan]

anh http://webketoan.com/members/1871-tuanktcdcn/ ơi,
anh có thể cho em xin file này được không ạ, em đang rất cần ạ
em cảm ơn anh nhiều

 

[cuudulieu116]

cảm ơn bạn chia sẻ rất chi tiết