Trị giá hàng tồn được tính theo giá bình quân liên hoàn

Với cách tính này, mỗi lần nhập hàng sẽ tính lại đơn giá cho lần xuất kế tiếpTa sẽ đi từ việc thiết lập các công thức tính toán từ dễ đến khó. Giả sử ta có 2 Sheet :Sheet DMMH có chứa mã MH và các số dư đầu kỳ. Tại Sheet này ta đặt 3 tên :- TonMaMH cho cột chứa Mã MH, - TonDauTG cho trị giá tồn đầu kỳ- TonDauSL cho số lượng tồn đầu kỳSheet thứ 2 là Sheet NhapXuatHH, Sheet này dùng để nhập các dữ liệu về Nhập Xuất HH phát sinh...
Nội dung trích xuất từ tài liệu:
Trị giá hàng tồn được tính theo giá bình quân liên hoàn Trị giá hàng tồn được tính theo giá bình quân liên hoànVới cách tính này, mỗi lần nhập hàng sẽ tính lại đơn giá cho lần xuất kế tiếpTa sẽ đi từ việc thiết lập các công thức tính toán từ dễ đến khó.Giả sử ta có 2 Sheet :Sheet DMMH có chứa mã MH và các số dư đầu kỳ. Tại Sheet này ta đặt 3 tên :- TonMaMH cho cột chứa Mã MH,- TonDauTG cho trị giá tồn đầu kỳ- TonDauSL cho số lượng tồn đầu kỳSheet thứ 2 là Sheet NhapXuatHH, Sheet này dùng để nhập các dữ liệu về NhậpXuất HH phát sinh trong thángTại Sheet này ta có các cột sau : Cột C là Mã MH, cột D là Số Lượng Nhap, cột Elà TGNhap, cột F là SLXuat, cột G là TGXuat, cột H tính đơn giá vốnTại Cell đầu tiên tính đơn giá vốn, Cell H5, ta có công thức sau :=IF(OR($C5=,SUMIF(TonMaMH,$C5,TonDauTG)=0),0,SUMIF(TonMaMH,$C5,TonDauTG)/SUMIF(TonMaMH,$C5,TonDauSL))Bắt đầu Cell H6, công thức sẽ trở thành :=IF(C6=,0,(SUMIF(TonMaMH,C6,TonDauTG)+SUMPRODUCT(($C$5:C5=C6)*($E$5:E5-$G$5:G5)))/(SUMIF(TonMaMH,C6,TonDauSL)+SUMPRODUCT(($C$5:C5=C6)*($D$5:D5-$F$5:F5))))Một cách khác để rút gọn công thức là đặt tên cho từng đoạn công thức nhu sau :Bạn đặt con trỏ ngay tại Cell H5 , rồi vào Insert / Name/ DefineĐặt tên cho các công thức sau :SLDuDau = SUMPRODUCT((TonMaMH=NhapXuatHH!$C5)*TonDauSL)TGDuDau = SUMPRODUCT((TonMaMH=NhapXuatHH!$C5)*TonDauTG)Công thức trong Cell H5 sẽ trở thành :=IF(OR(H5=,SLDuDau=0),0,TGDuDau/SLDuDau)Bây giờ, ta đặt con trỏ tại Cell H6, và tiếp tục đặt tên cho công thức :SLDuCuoi =SLDuDau+SUMPRODUCT((NhapXuatHH!$C$5:$C5=NhapXuatHH ! $C6)*(NhapXuatHH!$D$5:D5-NhapXuatHH!$F$5:F5))TGDuCuoi =TGDuDau+SUMPRODUCT((NhapXuatHH!$C$5:$C5=NhapXuatHH!$C6)*(NhapXuatHH!$E$5:$E5-NhapXuatHH!$G$5:$G5)))Công thức tại Cell H6 sẽ được viết thành :=IF(OR(C6=,SLDuCuoi=0),0,TGDuCuoi/SLDuCuoi)Ta cũng có thể thiết lập CSDL với các cột sau :Đặt tên cho các mảng dữ liệu :- $C$5:$C$20 = MH- $D$5:$D$20 = SL- $E$5:$E$20 = DGNhap- $F$5:$F$20 = DGVon- $G$5:$G$20 = TGVới bảng này, cột số lượng được nhập chung cả SL Nhập và SL Xuất với quy ướcSLNhap >0, và SL Xuất < 0Cột TG cũng vậy với công thức tính là :G5 = =IF(D5>0,D5*E5,D5*F5) (D5 là cột SL, nếu SL>0, DGNhap*SL và ngượclại)Cột DG xuất (Cột F) sẽ có các công thức sau :F5 = IF(OR($C5=,SUMIF(TonMaMH,$C5,TonDauSL)=0),0,SUMIF(TonMaMH,$C5,TonDauTG)/SUMIF(TonMaMH,$C5,TonDauSL))Từ F6 trở xuống, công thức sẽ là :F6 =IF(C6=,0,(SUMIF(TonMaMH,C6,TonDauTG)+SUMPRODUCT((C$5:$C5=C6)*($G$5:$G5)))/(SUMIF(TonMaMH,C6,TonDauSL)+SUMPRODUCT((C$5:$C5=C6 )*(D$5:$D5))))Ta cũng có thể đặt tên cho các công thức để rút gọn như đã nói ở phần trênDùng công thức SUMIF kết hợp với OFFSET :- SUMPRODUCT(($C$5:$C5=C6)*($G$5:G5)) : Mảng $C$5:$C$20 được đặt tênlà MH, mảng $G$5:$G$20 là TG nên công thức này tương đương với= SUMIF(OFFSET(MH,0,0,ROW()-5,1),C6,OFFSET(TG,0,0,ROW()-5,1))Công thức này hơi khó hiểu, nhưng hầu như được sử dụng nhiều trong những hàmtính FIFO sẽ nói ở phần sau