Trợ giúp về báo cáo nhập xuất tồn kho

Quảng cáo

ducmagic88

Thành viên chính thức
Tham gia ngày
14 Tháng tư 2020
Bài viết
58
Được thích
2
Điểm
108
Tuổi
24
Em chào các bác ạ! Em đang có một số vấn đề về file nhập xuất tồn mong các bác hỗ trợ giúp em ạ. File của em em đã code phần lấy thành tiền và số lượng trong kỳ rồi ạ, chỉ có phần lấy giá vốn, số lượng, giá trị đầu kỳ của từng tháng em nghĩ mãi vẫn chưa ra, các bác giúp em với ạ.
Một vài lưu ý:
Giá vốn = (Thành tiền tồn đầu kỳ +thành tiền nhập trong kỳ)/(Số lượng tồn đầu kỳ+Số lượng nhập trong kỳ)
Thành tiền xuất trong kỳ = Giá vốn * số lượng xuất trong kỳ
Ví dụ giá vốn tháng 1 = (Thành tiền tồn của cuối kỳ năm trước + thành tiền nhập trong tháng 1)/(Số lượng tồn của cuối kỳ năm trước + số lượng nhập trong tháng 1)
Giá vốn tháng 2 = (Thành tiền tồn cuối tháng 1 + thành tiền nhập trong tháng 2)/(Số lượng tồn của cuối tháng 1 + số lượng nhập trong tháng 2)
Giấ vốn tháng 3 = (Thành tiền tồn cuối tháng 2 + thành tiền nhập trong tháng 3)/(Số lượng tồn của cuối tháng 2 + số lượng nhập trong tháng 3)
Cứ thế lần lượt cho các tháng ạ và em cũng luôn lây giá vốn tròn tháng chứ không lấy giữa chừng các tháng với nhau, các bác giúp em với ạ, em cảm ơn các bác nhiều!
 

File đính kèm

  • BCNXT.xlsm
    801.1 KB · Đọc: 19

HieuCD

Chuyên gia GPE
Tham gia ngày
14 Tháng chín 2010
Bài viết
7,822
Được thích
15,725
Điểm
4,668
Em chào các bác ạ! Em đang có một số vấn đề về file nhập xuất tồn mong các bác hỗ trợ giúp em ạ. File của em em đã code phần lấy thành tiền và số lượng trong kỳ rồi ạ, chỉ có phần lấy giá vốn, số lượng, giá trị đầu kỳ của từng tháng em nghĩ mãi vẫn chưa ra, các bác giúp em với ạ.
Một vài lưu ý:
Giá vốn = (Thành tiền tồn đầu kỳ +thành tiền nhập trong kỳ)/(Số lượng tồn đầu kỳ+Số lượng nhập trong kỳ)
Thành tiền xuất trong kỳ = Giá vốn * số lượng xuất trong kỳ
Ví dụ giá vốn tháng 1 = (Thành tiền tồn của cuối kỳ năm trước + thành tiền nhập trong tháng 1)/(Số lượng tồn của cuối kỳ năm trước + số lượng nhập trong tháng 1)
Giá vốn tháng 2 = (Thành tiền tồn cuối tháng 1 + thành tiền nhập trong tháng 2)/(Số lượng tồn của cuối tháng 1 + số lượng nhập trong tháng 2)
Giấ vốn tháng 3 = (Thành tiền tồn cuối tháng 2 + thành tiền nhập trong tháng 3)/(Số lượng tồn của cuối tháng 2 + số lượng nhập trong tháng 3)
Cứ thế lần lượt cho các tháng ạ và em cũng luôn lây giá vốn tròn tháng chứ không lấy giữa chừng các tháng với nhau, các bác giúp em với ạ, em cảm ơn các bác nhiều!

1/ Số liệu cuối năm trước ở đâu? số lượng sheet danh mục sao không có thành tiền và là thời điểm nào?
2/ Ngày nhập có 1 số dòng không chuẩn (Khi dùng bộ lọc thấy không nằm trong tháng nào) cần nhập lại
Cách làm cột tồn đầu kỳ thành tiền giống cột số lượng, chỉ cộng trừ không nhân chia
 

ducmagic88

Thành viên chính thức
Tham gia ngày
14 Tháng tư 2020
Bài viết
58
Được thích
2
Điểm
108
Tuổi
24
1/ Số liệu cuối năm trước ở đâu? số lượng sheet danh mục sao không có thành tiền và là thời điểm nào?
2/ Ngày nhập có 1 số dòng không chuẩn (Khi dùng bộ lọc thấy không nằm trong tháng nào) cần nhập lại
Cách làm cột tồn đầu kỳ thành tiền giống cột số lượng, chỉ cộng trừ không nhân chia
1. Số liệu cuối năm trước em để nó ở sheet BK_NHAP_156 (sheet nhập) luôn đấy ạ, nó là thời điểm 31/12/2019 ạ, em xin lỗi vì đã không ghi chú lại ạ, em có ghi chú lại ở file dưới rồi ạ!
2. Em cảm ơn ạ, em chỉnh lại ở file dưới luôn rồi đấy ạ
3. Cái khó nhất là nằm ở chỗ đơn giá xuất nó phụ thuộc vào đơn giá bình quân từng tháng ạ, nên nó sẽ ảnh hưởng tới cuối kỳ ạ
Ví dụ đơn giá bình quân tháng 1 = (Thành tiền tồn của cuối kỳ năm trước (31/12/2019) + thành tiền nhập trong tháng 1/2020)/(Số lượng tồn của cuối kỳ năm trước (31/12/2019) + số lượng nhập trong tháng 1/2020)
- Sau khi tìm được đơn giá bình quân này, em sẽ đem nhân với số lượng xuất trong tháng 1/2020 để ra được cột thành tiền xuất trong tháng 1
- Tìm được thành tiền xuất trong tháng 1 rồi thì sẽ lấy thành tiền cuối kỳ năm trước (năm 2019) cộng với thành tiền nhập trong tháng 1 và trừ đi thành tiền xuất trong tháng 1 để tìm được số dư cuối tháng 1 ạ
 

File đính kèm

  • BCNXT.xlsm
    801.3 KB · Đọc: 2

HieuCD

Chuyên gia GPE
Tham gia ngày
14 Tháng chín 2010
Bài viết
7,822
Được thích
15,725
Điểm
4,668
1. Số liệu cuối năm trước em để nó ở sheet BK_NHAP_156 (sheet nhập) luôn đấy ạ, nó là thời điểm 31/12/2019 ạ, em xin lỗi vì đã không ghi chú lại ạ, em có ghi chú lại ở file dưới rồi ạ!
2. Em cảm ơn ạ, em chỉnh lại ở file dưới luôn rồi đấy ạ
3. Cái khó nhất là nằm ở chỗ đơn giá xuất nó phụ thuộc vào đơn giá bình quân từng tháng ạ, nên nó sẽ ảnh hưởng tới cuối kỳ ạ
Ví dụ đơn giá bình quân tháng 1 = (Thành tiền tồn của cuối kỳ năm trước (31/12/2019) + thành tiền nhập trong tháng 1/2020)/(Số lượng tồn của cuối kỳ năm trước (31/12/2019) + số lượng nhập trong tháng 1/2020)
- Sau khi tìm được đơn giá bình quân này, em sẽ đem nhân với số lượng xuất trong tháng 1/2020 để ra được cột thành tiền xuất trong tháng 1
- Tìm được thành tiền xuất trong tháng 1 rồi thì sẽ lấy thành tiền cuối kỳ năm trước (năm 2019) cộng với thành tiền nhập trong tháng 1 và trừ đi thành tiền xuất trong tháng 1 để tìm được số dư cuối tháng 1 ạ
Sheet Xuất có cột thành tiền chưa? nếu có không cần tính giá xuất
 

ducmagic88

Thành viên chính thức
Tham gia ngày
14 Tháng tư 2020
Bài viết
58
Được thích
2
Điểm
108
Tuổi
24
Sheet Xuất có cột thành tiền chưa? nếu có không cần tính giá xuất
Dạ chưa có đơn giá xuất ạ, sheet xuất đó chỉ mới có đơn giá bán và thành tiền bán do em gõ từ hóa đơn vào thôi ạ, còn đơn giá xuất là đơn giá bình quân theo từng tháng đang cần đi tính ạ
 

File đính kèm

  • BCNXT.xlsm
    768.4 KB · Đọc: 5

HieuCD

Chuyên gia GPE
Tham gia ngày
14 Tháng chín 2010
Bài viết
7,822
Được thích
15,725
Điểm
4,668
Dạ chưa có đơn giá xuất ạ, sheet xuất đó chỉ mới có đơn giá bán và thành tiền bán do em gõ từ hóa đơn vào thôi ạ, còn đơn giá xuất là đơn giá bình quân theo từng tháng đang cần đi tính ạ
Túm lại file có dữ liệu gì và cần tính cột nào? xóa tất cả cột kết quả cần tính và gởi lại file
 

ducmagic88

Thành viên chính thức
Tham gia ngày
14 Tháng tư 2020
Bài viết
58
Được thích
2
Điểm
108
Tuổi
24
Em gửi lại file đã cắt hết tất cả cột thừa và xóa những dữ liệu cần tính ạ.
Dựa vào các sheet "DM_NVL"; "BK_NHAP_156"; "BK_XUAT_156" để lấy dữ liệu sang sheet "BCNXT" ạ
Sheet "BK_XUAT_156" còn cột đơn giá xuất (đơn giá bình quân theo tháng) là chưa có dữ liệu cần phải tính nữa anh ạ
 

File đính kèm

  • BCNXT.xlsm
    487.4 KB · Đọc: 9

HieuCD

Chuyên gia GPE
Tham gia ngày
14 Tháng chín 2010
Bài viết
7,822
Được thích
15,725
Điểm
4,668
Em gửi lại file đã cắt hết tất cả cột thừa và xóa những dữ liệu cần tính ạ.
Dựa vào các sheet "DM_NVL"; "BK_NHAP_156"; "BK_XUAT_156" để lấy dữ liệu sang sheet "BCNXT" ạ
Sheet "BK_XUAT_156" còn cột đơn giá xuất (đơn giá bình quân theo tháng) là chưa có dữ liệu cần phải tính nữa anh ạ
Dữ liệu thời gian tại sao không theo thứ tự thời gian? sort lại theo thứ tự được không?
 

ducmagic88

Thành viên chính thức
Tham gia ngày
14 Tháng tư 2020
Bài viết
58
Được thích
2
Điểm
108
Tuổi
24
Dữ liệu thời gian tại sao không theo thứ tự thời gian? sort lại theo thứ tự được không?
dạ, chuẩn xác là phải sort lại mới đúng ạ, nhưng vì có thể có hóa đơn về trước hóa đơn về sau nên cứ hóa đơn nào về trước thì sẽ được nhập tạm trước dẫn tới tình trạng chưa đúng thứ tự về thời gian, sau khi kết thúc 1 năm thì sẽ được sort lại cho chuẩn và đánh số chứng từ lại từ đầu ạ
 

HieuCD

Chuyên gia GPE
Tham gia ngày
14 Tháng chín 2010
Bài viết
7,822
Được thích
15,725
Điểm
4,668
dạ, chuẩn xác là phải sort lại mới đúng ạ, nhưng vì có thể có hóa đơn về trước hóa đơn về sau nên cứ hóa đơn nào về trước thì sẽ được nhập tạm trước dẫn tới tình trạng chưa đúng thứ tự về thời gian, sau khi kết thúc 1 năm thì sẽ được sort lại cho chuẩn và đánh số chứng từ lại từ đầu ạ
Chạy code tính giá xuất kho, kiểm tra lại dữ liệu một số mặt hàng tồn kho âm
Mã:
Sub GiaXuat()
  Dim aDM(), aNhap(), aXuat(), Res(), Dic As Object, ikey$
  Dim eRow&, srDM&, srN&, srX&, frN&, frX&, i&, iR&, t&
'Khai bao lai cac tham so sau
  Const dHang& = 500 'Tang so 500 neu Code loi do Ma hang qua nhieu
  Const d& = 0 'So so le thap phan cua Gia xuat
  Const fDay As Date = #1/31/2021# 'Ngay cuoi thang 1 nam 2021
'***********
  Set Dic = CreateObject("scripting.dictionary")
  With Sheets("DM_NVL")
    aDM = .Range("A3:C" & .Range("C" & Rows.Count).End(xlUp).Row + dHang).Value
    srDM = UBound(aDM) - dHang
  End With
  ReDim Preserve aDM(1 To srDM + dHang, 1 To 6)
  For i = 1 To srDM
    If aDM(i, 1) <> Empty Then Dic.Item(aDM(i, 1)) = i
  Next i
 
  With Sheets("BK_Nhap_156")
    eRow = EndRow(.Name, "C")
    .Range("A3:U" & eRow).Sort .[C3], 1, .[B3], , 1, Header:=xlNo
    aNhap = .Range("C3:L" & eRow).Value
  End With
  With Sheets("BK_Xuat_156")
    eRow = EndRow(.Name, "C")
    .Range("A3:X" & eRow).Sort .[C3], 1, .[B3], , 1, Header:=xlNo
    aXuat = .Range("C3:J" & eRow).Value
  End With
  srN = UBound(aNhap): srX = UBound(aXuat)
  ReDim Res(1 To srX, 1 To 2)
  frN = 1: frX = 1
  For n = 1 To 12
    For i = frN To srN
      If TypeName(aNhap(i, 1)) = "Date" Then
        If aNhap(i, 1) <= fDay Then t = 1 Else t = Month(aNhap(i, 1))
        If t > n Then frN = i: Exit For
        ikey = aNhap(i, 5)
        If Dic.exists(ikey) = False Then
          srDM = srDM + 1
          Dic.Add ikey, srDM
          aDM(srDM, 1) = ikey: aDM(srDM, 2) = aNhap(i, 6): aDM(srDM, 3) = aNhap(i, 7)
        End If
        iR = Dic.Item(ikey)
        aDM(iR, 4) = aDM(iR, 4) + aNhap(i, 8)
        aDM(iR, 5) = aDM(iR, 5) + aNhap(i, 10)
      End If
    Next i
    For i = 1 To srDM
      If aDM(i, 4) > 0 Then aDM(i, 6) = Round(aDM(i, 5) / aDM(i, 4), d)
    Next i
    
    For i = frX To srX
      If TypeName(aXuat(i, 1)) = "Date" Then
        t = Month(aXuat(i, 1))
        If t > n Then frX = i: Exit For
        ikey = aXuat(i, 5)
        If Dic.exists(ikey) = False Then
          srDM = srDM + 1
          Dic.Add ikey, srDM
          aDM(srDM, 1) = ikey: aDM(srDM, 2) = aXuat(i, 6): aDM(srDM, 3) = aXuat(i, 7)
        End If
        iR = Dic.Item(ikey)
        Res(i, 1) = aDM(iR, 6)
        Res(i, 2) = Res(i, 1) * aXuat(i, 8)
        'Res(i, 2) = Round(Res(i, 1) * aXuat(i, 8), 0)  'Khi d>0
        aDM(iR, 4) = aDM(iR, 4) - aXuat(i, 8)
        aDM(iR, 5) = aDM(iR, 5) - Res(i, 2)
        If aDM(iR, 4) < 0 Then MsgBox (" Ma Hang Ton Kho Am:" & Chr(10) & "Tháng " & t & ": " & ikey)
      End If
    Next i
  Next n
  Sheets("BK_Xuat_156").Range("K3").Resize(srX, 2) = Res
  'Sheets("DM_NVL").Range("A3").Resize(srDM, 3) = aDM 'Them Ma Hang
End Sub

Private Function EndRow(ByVal shName$, ByVal jCol$)
  Dim eRow&, i&
  With Sheets(shName)
    eRow = .Range("L" & Rows.Count).End(xlUp).Row
    For i = eRow To 3 Step -1
      If TypeName(.Range(jCol & i).Value) = "Date" Then EndRow = i: Exit Function
    Next i
  End With
End Function
 

HieuCD

Chuyên gia GPE
Tham gia ngày
14 Tháng chín 2010
Bài viết
7,822
Được thích
15,725
Điểm
4,668
dạ, chuẩn xác là phải sort lại mới đúng ạ, nhưng vì có thể có hóa đơn về trước hóa đơn về sau nên cứ hóa đơn nào về trước thì sẽ được nhập tạm trước dẫn tới tình trạng chưa đúng thứ tự về thời gian, sau khi kết thúc 1 năm thì sẽ được sort lại cho chuẩn và đánh số chứng từ lại từ đầu ạ
Chạy code tính giá xuất kho, kiểm tra và điều chỉnh xuất kho âm, ổn rồi mới chạy nhập xuất tồn
Mã:
Sub GiaXuat()
  Dim aDM(), aNhap(), aXuat(), Res(), Dic As Object, ikey$
  Dim eRow&, srDM&, srN&, srX&, frN&, frX&, i&, iR&, t&
'Khai bao lai cac tham so sau
  Const dHang& = 500 'Tang so 500 neu Code loi do Ma hang qua nhieu
  Const d& = 3 'So so le thap phan cua Gia xuat
  Const fDay As Date = #1/31/2021# 'Ngay cuoi thang 1 nam 2021
'***********
  Set Dic = CreateObject("scripting.dictionary")
  With Sheets("DM_NVL")
    aDM = .Range("A3:C" & .Range("C" & Rows.Count).End(xlUp).Row + dHang).Value
    srDM = UBound(aDM) - dHang
  End With
  ReDim Preserve aDM(1 To srDM + dHang, 1 To 6)
  For i = 1 To srDM
    If aDM(i, 1) <> Empty Then Dic.Item(aDM(i, 1)) = i
  Next i
 
  With Sheets("BK_Nhap_156")
    eRow = EndRow(.Name, "C")
    .Range("A3:U" & eRow).Sort .[C3], 1, .[B3], , 1, Header:=xlNo
    aNhap = .Range("C3:L" & eRow).Value
  End With
  With Sheets("BK_Xuat_156")
    eRow = EndRow(.Name, "C")
    .Range("A3:X" & eRow).Sort .[C3], 1, .[B3], , 1, Header:=xlNo
    aXuat = .Range("C3:J" & eRow).Value
  End With
  srN = UBound(aNhap): srX = UBound(aXuat)
  ReDim Res(1 To srX, 1 To 2)
  frN = 1: frX = 1
  For n = 1 To 12
    For i = frN To srN
      If TypeName(aNhap(i, 1)) = "Date" Then
        If aNhap(i, 1) <= fDay Then t = 1 Else t = Month(aNhap(i, 1))
        If t > n Then frN = i: Exit For
        ikey = aNhap(i, 5)
        If Dic.exists(ikey) = False Then
          srDM = srDM + 1
          Dic.Add ikey, srDM
          aDM(srDM, 1) = ikey: aDM(srDM, 2) = aNhap(i, 6): aDM(srDM, 3) = aNhap(i, 7)
        End If
        iR = Dic.Item(ikey)
        aDM(iR, 4) = aDM(iR, 4) + aNhap(i, 8)
        aDM(iR, 5) = aDM(iR, 5) + aNhap(i, 10)
      End If
    Next i
    For i = 1 To srDM
      If aDM(i, 4) > 0 Then aDM(i, 6) = Round(aDM(i, 5) / aDM(i, 4), d)
    Next i
    
    For i = frX To srX
      If TypeName(aXuat(i, 1)) = "Date" Then
        t = Month(aXuat(i, 1))
        If t > n Then frX = i: Exit For
        ikey = aXuat(i, 5)
        If Dic.exists(ikey) = False Then
          srDM = srDM + 1
          Dic.Add ikey, srDM
          aDM(srDM, 1) = ikey: aDM(srDM, 2) = aXuat(i, 6): aDM(srDM, 3) = aXuat(i, 7)
        End If
        iR = Dic.Item(ikey)
        Res(i, 1) = aDM(iR, 6)
        Res(i, 2) = Round(Res(i, 1) * aXuat(i, 8), 0)
        aDM(iR, 4) = aDM(iR, 4) - aXuat(i, 8)
        aDM(iR, 5) = aDM(iR, 5) - Res(i, 2)
        If aDM(iR, 4) < 0 Then MsgBox (" Ma Hang Ton Kho Am:" & Chr(10) & "Tháng " & t & ": " & ikey)
      End If
    Next i
  Next n
  Sheets("BK_Xuat_156").Range("K3").Resize(srX, 2) = Res
  'Sheets("DM_NVL").Range("A3").Resize(srDM, 3) = aDM 'Them Ma Hang
End Sub

Sub NXT()
  Dim aDM(), aNhap(), aXuat(), Res(), Dic As Object, ikey$
  Dim eRow&, srDM&, srN&, srX&, frN&, frX&, i&, iR&, j&
  Dim fDay As Date, eDay As Date, tmp, tTT
'Khai bao lai cac tham so sau
  Const dHang& = 500 'Tang so 500 neu Code loi do Ma hang qua nhieu
  Const d& = 3 'So so le thap phan cua Gia xuat
'***********
  Set Dic = CreateObject("scripting.dictionary")
  With Sheets("DM_NVL")
    aDM = .Range("A3:C" & .Range("C" & Rows.Count).End(xlUp).Row + dHang).Value
    srDM = UBound(aDM) - dHang
  End With
  ReDim Res(1 To srDM + dHang, 1 To 13)
  For i = 1 To srDM
    If aDM(i, 1) <> Empty Then Dic.Item(aDM(i, 1)) = i
  Next i
  On Error Resume Next
  With Sheets("BCNXT")
    eRow = .Range("A" & Rows.Count).End(xlUp).Row
    If eRow > 9 Then .Range("A10:M" & eRow).ClearContents
    fDay = .Range("N1").Value: eDay = .Range("N2").Value
    If fDay = Empty Or eDay = Empty Or Err > 0 Then MsgBox ("Thoi Gian Bao Cao Sai"): Exit Sub
  End With
  On Error GoTo 0
  With Sheets("BK_Nhap_156")
    eRow = EndRow(.Name, "C")
    aNhap = .Range("C3:L" & eRow).Value
  End With
  With Sheets("BK_Xuat_156")
    eRow = EndRow(.Name, "C")
    aXuat = .Range("C3:L" & eRow).Value
  End With
  srN = UBound(aNhap): srX = UBound(aXuat)

  For i = 1 To srN
    tmp = aNhap(i, 1)
    If TypeName(tmp) = "Date" Then
      ikey = aNhap(i, 5)
      If Dic.exists(ikey) = False Then
        srDM = srDM + 1
        Dic.Add ikey, srDM
        aDM(srDM, 1) = ikey: aDM(srDM, 2) = aNhap(i, 6): aDM(srDM, 3) = aNhap(i, 7)
      End If
      iR = Dic.Item(ikey)
      If tmp < fDay Then
        Res(iR, 5) = Res(iR, 5) + aNhap(i, 8)
        Res(iR, 6) = Res(iR, 6) + aNhap(i, 10)
      ElseIf tmp <= eDay Then
        Res(iR, 7) = Res(iR, 7) + aNhap(i, 8)
        Res(iR, 8) = Res(iR, 8) + aNhap(i, 10)
      Else
        Exit For
      End If
    End If
  Next i
  For i = 1 To srX
    tmp = aXuat(i, 1)
    If TypeName(tmp) = "Date" Then
      ikey = aXuat(i, 5)
      If Dic.exists(ikey) = False Then
        srDM = srDM + 1
        Dic.Add ikey, srDM
        aDM(srDM, 1) = ikey: aDM(srDM, 2) = aXuat(i, 6): aDM(srDM, 3) = aXuat(i, 7)
      End If
      iR = Dic.Item(ikey)
      If tmp < fDay Then
        Res(iR, 5) = Res(iR, 5) - aXuat(i, 8)
        Res(iR, 6) = Res(iR, 6) - aXuat(i, 10)
      ElseIf tmp <= eDay Then
        Res(iR, 9) = Res(iR, 9) + aXuat(i, 8)
        Res(iR, 10) = Res(iR, 10) + aXuat(i, 10)
      Else
        Exit For
      End If
    End If
  Next i
 
  For i = 1 To srDM
    For j = 5 To 11 Step 2
      If Res(i, j) > 0 Then Exit For
    Next j
    If j <= 11 Then
      k = k + 1
      For j = 1 To 3
        Res(k, j) = aDM(i, j)
      Next j
      For j = 5 To 10
        Res(k, j) = Res(i, j)
      Next j
      tmp = Res(k, 5) + Res(k, 7)
      If tmp > 0 Then Res(k, 4) = Round((Res(k, 6) + Res(k, 8)) / tmp, d)
      Res(k, 11) = Res(k, 5) + Res(k, 7) - Res(k, 9)
      Res(k, 12) = Res(k, 6) + Res(k, 8) - Res(k, 10)
    End If
  Next i

  Sheets("BCNXT").Range("A10").Resize(k, 12) = Res
  'Sheets("DM_NVL").Range("A3").Resize(srDM, 3) = aDM 'Them Ma Hang
End Sub

Private Function EndRow(ByVal shName$, ByVal jCol$)
  Dim eRow&, i&
  With Sheets(shName)
    eRow = .Range("L" & Rows.Count).End(xlUp).Row
    For i = eRow To 3 Step -1
      If TypeName(.Range(jCol & i).Value) = "Date" Then EndRow = i: Exit Function
    Next i
  End With
End Function
 

ducmagic88

Thành viên chính thức
Tham gia ngày
14 Tháng tư 2020
Bài viết
58
Được thích
2
Điểm
108
Tuổi
24
Chạy code tính giá xuất kho, kiểm tra lại dữ liệu một số mặt hàng tồn kho âm
Mã:
Sub GiaXuat()
  Dim aDM(), aNhap(), aXuat(), Res(), Dic As Object, ikey$
  Dim eRow&, srDM&, srN&, srX&, frN&, frX&, i&, iR&, t&
'Khai bao lai cac tham so sau
  Const dHang& = 500 'Tang so 500 neu Code loi do Ma hang qua nhieu
  Const d& = 0 'So so le thap phan cua Gia xuat
  Const fDay As Date = #1/31/2021# 'Ngay cuoi thang 1 nam 2021
'***********
  Set Dic = CreateObject("scripting.dictionary")
  With Sheets("DM_NVL")
    aDM = .Range("A3:C" & .Range("C" & Rows.Count).End(xlUp).Row + dHang).Value
    srDM = UBound(aDM) - dHang
  End With
  ReDim Preserve aDM(1 To srDM + dHang, 1 To 6)
  For i = 1 To srDM
    If aDM(i, 1) <> Empty Then Dic.Item(aDM(i, 1)) = i
  Next i

  With Sheets("BK_Nhap_156")
    eRow = EndRow(.Name, "C")
    .Range("A3:U" & eRow).Sort .[C3], 1, .[B3], , 1, Header:=xlNo
    aNhap = .Range("C3:L" & eRow).Value
  End With
  With Sheets("BK_Xuat_156")
    eRow = EndRow(.Name, "C")
    .Range("A3:X" & eRow).Sort .[C3], 1, .[B3], , 1, Header:=xlNo
    aXuat = .Range("C3:J" & eRow).Value
  End With
  srN = UBound(aNhap): srX = UBound(aXuat)
  ReDim Res(1 To srX, 1 To 2)
  frN = 1: frX = 1
  For n = 1 To 12
    For i = frN To srN
      If TypeName(aNhap(i, 1)) = "Date" Then
        If aNhap(i, 1) <= fDay Then t = 1 Else t = Month(aNhap(i, 1))
        If t > n Then frN = i: Exit For
        ikey = aNhap(i, 5)
        If Dic.exists(ikey) = False Then
          srDM = srDM + 1
          Dic.Add ikey, srDM
          aDM(srDM, 1) = ikey: aDM(srDM, 2) = aNhap(i, 6): aDM(srDM, 3) = aNhap(i, 7)
        End If
        iR = Dic.Item(ikey)
        aDM(iR, 4) = aDM(iR, 4) + aNhap(i, 8)
        aDM(iR, 5) = aDM(iR, 5) + aNhap(i, 10)
      End If
    Next i
    For i = 1 To srDM
      If aDM(i, 4) > 0 Then aDM(i, 6) = Round(aDM(i, 5) / aDM(i, 4), d)
    Next i
  
    For i = frX To srX
      If TypeName(aXuat(i, 1)) = "Date" Then
        t = Month(aXuat(i, 1))
        If t > n Then frX = i: Exit For
        ikey = aXuat(i, 5)
        If Dic.exists(ikey) = False Then
          srDM = srDM + 1
          Dic.Add ikey, srDM
          aDM(srDM, 1) = ikey: aDM(srDM, 2) = aXuat(i, 6): aDM(srDM, 3) = aXuat(i, 7)
        End If
        iR = Dic.Item(ikey)
        Res(i, 1) = aDM(iR, 6)
        Res(i, 2) = Res(i, 1) * aXuat(i, 8)
        'Res(i, 2) = Round(Res(i, 1) * aXuat(i, 8), 0)  'Khi d>0
        aDM(iR, 4) = aDM(iR, 4) - aXuat(i, 8)
        aDM(iR, 5) = aDM(iR, 5) - Res(i, 2)
        If aDM(iR, 4) < 0 Then MsgBox (" Ma Hang Ton Kho Am:" & Chr(10) & "Tháng " & t & ": " & ikey)
      End If
    Next i
  Next n
  Sheets("BK_Xuat_156").Range("K3").Resize(srX, 2) = Res
  'Sheets("DM_NVL").Range("A3").Resize(srDM, 3) = aDM 'Them Ma Hang
End Sub

Private Function EndRow(ByVal shName$, ByVal jCol$)
  Dim eRow&, i&
  With Sheets(shName)
    eRow = .Range("L" & Rows.Count).End(xlUp).Row
    For i = eRow To 3 Step -1
      If TypeName(.Range(jCol & i).Value) = "Date" Then EndRow = i: Exit Function
    Next i
  End With
End Function
Trên đây là cách tính giá xuất kho lấy giá xuất kho trung bình cho cả năm ạ, thực tế thì phải lấy nó theo từng tháng vì mỗi tháng mặt hàng thay đổi giá khác nhau nên sẽ có giá xuất kho khác nhau. Anh xem giúp em file dưới đây, đây là bảng em kẻ tính giá xuất kho theo từng tháng của mã IPC-A22EP-IMOU, anh nhìn bảng này anh sẽ hiểu cách đơn giá xuất nó được tính ra như thế nào ạ.
Bài đã được tự động gộp:

À anh Hiếu ơi, anh có thể cho em xin thông tin liên lạc với anh được không ạ (zalo, facebook,..), cũng không có gì nhiều em muốn gửi anh cốc cà phê coi như lời cảm ơn a ạ
 

File đính kèm

  • IPC_A22EP_IMOU.xlsx
    13.3 KB · Đọc: 8

HieuCD

Chuyên gia GPE
Tham gia ngày
14 Tháng chín 2010
Bài viết
7,822
Được thích
15,725
Điểm
4,668
Trên đây là cách tính giá xuất kho lấy giá xuất kho trung bình cho cả năm ạ, thực tế thì phải lấy nó theo từng tháng vì mỗi tháng mặt hàng thay đổi giá khác nhau nên sẽ có giá xuất kho khác nhau. Anh xem giúp em file dưới đây, đây là bảng em kẻ tính giá xuất kho theo từng tháng của mã IPC-A22EP-IMOU, anh nhìn bảng này anh sẽ hiểu cách đơn giá xuất nó được tính ra như thế nào ạ.
Bài đã được tự động gộp:

À anh Hiếu ơi, anh có thể cho em xin thông tin liên lạc với anh được không ạ (zalo, facebook,..), cũng không có gì nhiều em muốn gửi anh cốc cà phê coi như lời cảm ơn a ạ
Mình tính giá xuất kho theo từng tháng
File của bạn có thể khác số liệu file của mình nên kết quả khác nhau, bạn xem công thức kiểm tra tháng 12 trong file
 

File đính kèm

  • BCNXT (3).xlsm
    621.2 KB · Đọc: 28

ducmagic88

Thành viên chính thức
Tham gia ngày
14 Tháng tư 2020
Bài viết
58
Được thích
2
Điểm
108
Tuổi
24
Mình tính giá xuất kho theo từng tháng
File của bạn có thể khác số liệu file của mình nên kết quả khác nhau, bạn xem công thức kiểm tra tháng 12 trong file
- Anh ơi, chỗ cột thành tiền xuất trong kỳ của sheet "BCNXT" chưa đúng vì đang lấy số lượng xuất nhân với đơn giá xuất của cả năm ạ, có thể để nó nhân với đơn giá xuất của tháng đó được không ạ.
-Sheet "BK_nhap_156" cũng đang chạy đơn giá xuất của cả năm ạ chứ không phải của thời điểm đó, ví dụ tháng 1 em muốn lấy đơn giá của tháng 1, tháng 2 em lấy đơn giá của tháng 2 ạ
 

HieuCD

Chuyên gia GPE
Tham gia ngày
14 Tháng chín 2010
Bài viết
7,822
Được thích
15,725
Điểm
4,668
- Anh ơi, chỗ cột thành tiền xuất trong kỳ của sheet "BCNXT" chưa đúng vì đang lấy số lượng xuất nhân với đơn giá xuất của cả năm ạ, có thể để nó nhân với đơn giá xuất của tháng đó được không ạ.
-Sheet "BK_nhap_156" cũng đang chạy đơn giá xuất của cả năm ạ chứ không phải của thời điểm đó, ví dụ tháng 1 em muốn lấy đơn giá của tháng 1, tháng 2 em lấy đơn giá của tháng 2 ạ
Mình nhầm năm, chỉnh lệnh: Const fDay As Date = #1/31/2021#
Thành
Const fDay As Date = #1/31/2020#
 

ducmagic88

Thành viên chính thức
Tham gia ngày
14 Tháng tư 2020
Bài viết
58
Được thích
2
Điểm
108
Tuổi
24
Đúng rùi, và nếu cần khai báo lại các tham số khác mình ghi chú trong code: " 'Khai bao lai cac tham so sau"
Cảm ơn anh rất nhiều ạ! Cũng không biết cảm ơn anh thế nào thôi thì anh có zalo không cho em xin số điện thoại để kết bạn hoặc nếu tiện thì cho em xin tài khoản ngân hàng em gửi anh tách cà phê anh nhé!
 

HieuCD

Chuyên gia GPE
Tham gia ngày
14 Tháng chín 2010
Bài viết
7,822
Được thích
15,725
Điểm
4,668
Cảm ơn anh rất nhiều ạ! Cũng không biết cảm ơn anh thế nào thôi thì anh có zalo không cho em xin số điện thoại để kết bạn hoặc nếu tiện thì cho em xin tài khoản ngân hàng em gửi anh tách cà phê anh nhé!
Giúp được bạn là mình vui rồi
Chỉnh code tự tính fDay
Mã:
Sub GiaXuat()
  Dim aDM(), aNhap(), aXuat(), Res(), Dic As Object, ikey$, fDay As Date
  Dim eRow&, srDM&, srN&, srX&, frN&, frX&, i&, iR&, t&
'Khai bao lai cac tham so sau
  Const dHang& = 500 'Tang so 500 neu Code loi do Ma hang qua nhieu
  Const d& = 3 'So so le thap phan cua Gia xuat
'***********
 
  Set Dic = CreateObject("scripting.dictionary")
  With Sheets("DM_NVL")
    aDM = .Range("A3:C" & .Range("C" & Rows.Count).End(xlUp).Row + dHang).Value
    srDM = UBound(aDM) - dHang
  End With
  ReDim Preserve aDM(1 To srDM + dHang, 1 To 6)
  For i = 1 To srDM
    If aDM(i, 1) <> Empty Then Dic.Item(aDM(i, 1)) = i
  Next i
 
  With Sheets("BK_Nhap_156")
    eRow = EndRow(.Name, "C")
    .Range("A3:U" & eRow).Sort .[C3], 1, .[B3], , 1, Header:=xlNo
    aNhap = .Range("C3:L" & eRow).Value
  End With
  With Sheets("BK_Xuat_156")
    eRow = EndRow(.Name, "C")
    .Range("A3:X" & eRow).Sort .[C3], 1, .[B3], , 1, Header:=xlNo
    aXuat = .Range("C3:J" & eRow).Value
  End With
  srN = UBound(aNhap): srX = UBound(aXuat)
  fDay = DateSerial(Year(aXuat(1, 1)), 1, 31)
  ReDim Res(1 To srX, 1 To 2)
 
  frN = 1: frX = 1
  For n = 1 To 12
    For i = frN To srN 'Ton Dau + Nhap cua thang thu n
      If TypeName(aNhap(i, 1)) = "Date" Then
        If aNhap(i, 1) <= fDay Then t = 1 Else t = Month(aNhap(i, 1))
        If t > n Then frN = i: Exit For
        ikey = aNhap(i, 5)
        If Dic.exists(ikey) = False Then
          srDM = srDM + 1
          Dic.Add ikey, srDM
          aDM(srDM, 1) = ikey: aDM(srDM, 2) = aNhap(i, 6): aDM(srDM, 3) = aNhap(i, 7)
        End If
        iR = Dic.Item(ikey)
        aDM(iR, 4) = aDM(iR, 4) + aNhap(i, 8)
        aDM(iR, 5) = aDM(iR, 5) + aNhap(i, 10)
      End If
    Next i
    
    For i = 1 To srDM 'Gia Xuat kho thang thu n
      If aDM(i, 4) > 0 Then aDM(i, 6) = Round(aDM(i, 5) / aDM(i, 4), d)
    Next i
    
    For i = frX To srX 'nhap Gia va Thanh Tien Xuat kho
      If TypeName(aXuat(i, 1)) = "Date" Then
        t = Month(aXuat(i, 1))
        If t > n Then frX = i: Exit For
        ikey = aXuat(i, 5)
        If Dic.exists(ikey) = False Then
          srDM = srDM + 1
          Dic.Add ikey, srDM
          aDM(srDM, 1) = ikey: aDM(srDM, 2) = aXuat(i, 6): aDM(srDM, 3) = aXuat(i, 7)
        End If
        iR = Dic.Item(ikey)
        Res(i, 1) = aDM(iR, 6)
        Res(i, 2) = Round(Res(i, 1) * aXuat(i, 8), 0)
        aDM(iR, 4) = aDM(iR, 4) - aXuat(i, 8)
        aDM(iR, 5) = aDM(iR, 5) - Res(i, 2)
        If aDM(iR, 4) < 0 Then MsgBox (" Ma Hang Ton Kho Am:" & Chr(10) & "Tháng " & t & ": " & ikey)
      End If
    Next i
  Next n
  Sheets("BK_Xuat_156").Range("K3").Resize(srX, 2) = Res
  'Sheets("DM_NVL").Range("A3").Resize(srDM, 3) = aDM 'Them Ma Hang
End Sub
 

ducmagic88

Thành viên chính thức
Tham gia ngày
14 Tháng tư 2020
Bài viết
58
Được thích
2
Điểm
108
Tuổi
24
Giúp được bạn là mình vui rồi
Chỉnh code tự tính fDay
Mã:
Sub GiaXuat()
  Dim aDM(), aNhap(), aXuat(), Res(), Dic As Object, ikey$, fDay As Date
  Dim eRow&, srDM&, srN&, srX&, frN&, frX&, i&, iR&, t&
'Khai bao lai cac tham so sau
  Const dHang& = 500 'Tang so 500 neu Code loi do Ma hang qua nhieu
  Const d& = 3 'So so le thap phan cua Gia xuat
'***********

  Set Dic = CreateObject("scripting.dictionary")
  With Sheets("DM_NVL")
    aDM = .Range("A3:C" & .Range("C" & Rows.Count).End(xlUp).Row + dHang).Value
    srDM = UBound(aDM) - dHang
  End With
  ReDim Preserve aDM(1 To srDM + dHang, 1 To 6)
  For i = 1 To srDM
    If aDM(i, 1) <> Empty Then Dic.Item(aDM(i, 1)) = i
  Next i

  With Sheets("BK_Nhap_156")
    eRow = EndRow(.Name, "C")
    .Range("A3:U" & eRow).Sort .[C3], 1, .[B3], , 1, Header:=xlNo
    aNhap = .Range("C3:L" & eRow).Value
  End With
  With Sheets("BK_Xuat_156")
    eRow = EndRow(.Name, "C")
    .Range("A3:X" & eRow).Sort .[C3], 1, .[B3], , 1, Header:=xlNo
    aXuat = .Range("C3:J" & eRow).Value
  End With
  srN = UBound(aNhap): srX = UBound(aXuat)
  fDay = DateSerial(Year(aXuat(1, 1)), 1, 31)
  ReDim Res(1 To srX, 1 To 2)

  frN = 1: frX = 1
  For n = 1 To 12
    For i = frN To srN 'Ton Dau + Nhap cua thang thu n
      If TypeName(aNhap(i, 1)) = "Date" Then
        If aNhap(i, 1) <= fDay Then t = 1 Else t = Month(aNhap(i, 1))
        If t > n Then frN = i: Exit For
        ikey = aNhap(i, 5)
        If Dic.exists(ikey) = False Then
          srDM = srDM + 1
          Dic.Add ikey, srDM
          aDM(srDM, 1) = ikey: aDM(srDM, 2) = aNhap(i, 6): aDM(srDM, 3) = aNhap(i, 7)
        End If
        iR = Dic.Item(ikey)
        aDM(iR, 4) = aDM(iR, 4) + aNhap(i, 8)
        aDM(iR, 5) = aDM(iR, 5) + aNhap(i, 10)
      End If
    Next i
   
    For i = 1 To srDM 'Gia Xuat kho thang thu n
      If aDM(i, 4) > 0 Then aDM(i, 6) = Round(aDM(i, 5) / aDM(i, 4), d)
    Next i
   
    For i = frX To srX 'nhap Gia va Thanh Tien Xuat kho
      If TypeName(aXuat(i, 1)) = "Date" Then
        t = Month(aXuat(i, 1))
        If t > n Then frX = i: Exit For
        ikey = aXuat(i, 5)
        If Dic.exists(ikey) = False Then
          srDM = srDM + 1
          Dic.Add ikey, srDM
          aDM(srDM, 1) = ikey: aDM(srDM, 2) = aXuat(i, 6): aDM(srDM, 3) = aXuat(i, 7)
        End If
        iR = Dic.Item(ikey)
        Res(i, 1) = aDM(iR, 6)
        Res(i, 2) = Round(Res(i, 1) * aXuat(i, 8), 0)
        aDM(iR, 4) = aDM(iR, 4) - aXuat(i, 8)
        aDM(iR, 5) = aDM(iR, 5) - Res(i, 2)
        If aDM(iR, 4) < 0 Then MsgBox (" Ma Hang Ton Kho Am:" & Chr(10) & "Tháng " & t & ": " & ikey)
      End If
    Next i
  Next n
  Sheets("BK_Xuat_156").Range("K3").Resize(srX, 2) = Res
  'Sheets("DM_NVL").Range("A3").Resize(srDM, 3) = aDM 'Them Ma Hang
End Sub
Code chạy tốt rồi ạ
 
Quảng cáo
Top Bottom