Anh huuthang_bd giúp em tính giá vốn với (1 người xem)

[vhh038]

Function GiaXK(SL_Xuat, Rng As Range, Optional TT As Boolean = False)
Dim TongXuat, TongNhap, XuatCon
If SL_Xuat = 0 Then
    GiaXK = ""
    Exit Function
End If
TongXuat = Application.WorksheetFunction.Sum(Rng.Offset(, 1).Resize(, 1))
For Each Cll In Rng.Resize(, 1)
    TongNhap = TongNhap + Cll.Value
    If TongNhap > TongXuat Then
        If TongNhap - TongXuat >= SL_Xuat Then
        GiaXK = GiaXK & " (" & SL_Xuat & "*" & Cll.Offset(, 2).Value & ")"
        Exit For
        Else
        GiaXK = GiaXK & " (" & (TongNhap - TongXuat) & "*" & Cll.Offset(, 2).Value & ")"
        SL_Xuat = SL_Xuat - (TongNhap - TongXuat)
        TongXuat = TongNhap
        End If
    End If
Next
GiaXK = Replace(Trim(GiaXK), " ", "+")
    If TT = True Then
    GiaXK = IIf(GiaXK = "", 0, Evaluate(GiaXK))
    End If
End Function

Code này trong bài ví dụ của anh đã giải. Em có gửi file đính kèm. Bài này anh giải rất hay nhưng chỉ đúng với 1 loại hàng (Gạch ống 8x18)

Trường hợp có nhiều loại hàng hóa khác nhau, code này ko phân biệt được tên hàng.

Anh Thắng giúp em giải bài này với!

 

[ThuNghi]

Function GiaXK(SL_Xuat, Rng As Range, Optional TT As Boolean = False)
Dim TongXuat, TongNhap, XuatCon
If SL_Xuat = 0 Then
    GiaXK = ""
    Exit Function
End If
TongXuat = Application.WorksheetFunction.Sum(Rng.Offset(, 1).Resize(, 1))
For Each Cll In Rng.Resize(, 1)
    TongNhap = TongNhap + Cll.Value
    If TongNhap > TongXuat Then
        If TongNhap - TongXuat >= SL_Xuat Then
        GiaXK = GiaXK & " (" & SL_Xuat & "*" & Cll.Offset(, 2).Value & ")"
        Exit For
        Else
        GiaXK = GiaXK & " (" & (TongNhap - TongXuat) & "*" & Cll.Offset(, 2).Value & ")"
        SL_Xuat = SL_Xuat - (TongNhap - TongXuat)
        TongXuat = TongNhap
        End If
    End If
Next
GiaXK = Replace(Trim(GiaXK), " ", "+")
    If TT = True Then
    GiaXK = IIf(GiaXK = "", 0, Evaluate(GiaXK))
    End If
End Function

Code này trong bài ví dụ của anh đã giải. Em có gửi file đính kèm. Bài này anh giải rất hay nhưng chỉ đúng với 1 loại hàng (Gạch ống 8x18)

Trường hợp có nhiều loại hàng hóa khác nhau, code này ko phân biệt được tên hàng.

Anh Thắng giúp em giải bài này với!

Mình sửa lại UDF của Thắng, thêm phần Mahh, bạn kiểm tra OK, mình sẽ chuyển sang Array cho nhanh.

Function GiaXK_MH(sMaHH As String, SL_Xuat As Long, rngMaHH As Range, rngSlNh As Range, rngDgNh As Range, rngSlXu As Range, Optional TT As Boolean = False)
Dim TongXuat As Long, TongNhap As Long, i As Long
Dim GiaXuat
If SL_Xuat = 0 Or sMaHH = "" Then
    GiaXuat = ""
    Exit Function
End If
i = Application.WorksheetFunction.CountIf(rngMaHH, sMaHH)
TongXuat = Application.WorksheetFunction.SumIf(rngMaHH, sMaHH, rngSlXu)
If i = 0 Then Exit Function
TongNhap = 0
For i = 1 To rngMaHH.Rows.Count
  If rngMaHH(i) = sMaHH Then
  TongNhap = TongNhap + rngSlNh(i).Value
    If TongNhap > TongXuat Then
      If TongNhap - TongXuat >= SL_Xuat Then
        GiaXuat = GiaXuat & " (" & SL_Xuat & "*" & rngDgNh(i).Value & ")"
        Exit For
      Else
        GiaXuat = GiaXuat & " (" & (TongNhap - TongXuat) & "*" & rngDgNh(i).Value & ")"
        SL_Xuat = SL_Xuat - (TongNhap - TongXuat)
        TongXuat = TongNhap
      End If
    End If
  End If
Next i
GiaXK_MH = Replace(Trim(GiaXuat), " ", "+")
If TT = True Then
  GiaXuat = Replace(Trim(GiaXuat), " ", "+")
  GiaXK_MH = Evaluate(Trim(GiaXuat))
End If
End Function

Cú pháp:
J18=giaxk_mh($D18,$G18,$D$2:$D17,$F$2:$F17,$H$2:$H17,$G$2:$G17,1)
$D18: MaHH
$G18: SL Xuất
$D$2:$D17: Cột Mã HH
$F$2:$F17: SL Nhập
$H$2:$H17: DG Nhập
$G$2:$G17: SL Xuất

 

[huuthang_bd]

Function GiaXK(SL_Xuat, Rng As Range, Optional TT As Boolean = False)
Dim TongXuat, TongNhap, XuatCon
If SL_Xuat = 0 Then
    GiaXK = ""
    Exit Function
End If
TongXuat = Application.WorksheetFunction.Sum(Rng.Offset(, 1).Resize(, 1))
For Each Cll In Rng.Resize(, 1)
    TongNhap = TongNhap + Cll.Value
    If TongNhap > TongXuat Then
        If TongNhap - TongXuat >= SL_Xuat Then
        GiaXK = GiaXK & " (" & SL_Xuat & "*" & Cll.Offset(, 2).Value & ")"
        Exit For
        Else
        GiaXK = GiaXK & " (" & (TongNhap - TongXuat) & "*" & Cll.Offset(, 2).Value & ")"
        SL_Xuat = SL_Xuat - (TongNhap - TongXuat)
        TongXuat = TongNhap
        End If
    End If
Next
GiaXK = Replace(Trim(GiaXK), " ", "+")
    If TT = True Then
    GiaXK = IIf(GiaXK = "", 0, Evaluate(GiaXK))
    End If
End Function

Code này trong bài ví dụ của anh đã giải. Em có gửi file đính kèm. Bài này anh giải rất hay nhưng chỉ đúng với 1 loại hàng (Gạch ống 8x18)

Trường hợp có nhiều loại hàng hóa khác nhau, code này ko phân biệt được tên hàng.

Anh Thắng giúp em giải bài này với!

Tôi đã sửa code lại để áp dụng cho trường hợp dữ liệu có nhiều loại hàng hoá và chuyển sang sử lý trên mã để cải thiện tốc độ theo ý kiến của bạn ThuNghi.

Function FIFO(SL_Xuat As Double, Ma As String, DataRng As Range, MaCol As Long, SLNhapCol As Long, SLXuatCol As Long, GiaNhapCol As Long, Optional TT As Boolean = False)
Dim TongXuat As Double, TongNhap As Double, Data As Variant, i As Long
If SL_Xuat = 0 Then
    FIFO = IIf(TT, 0, "")
    Exit Function
End If
TongXuat = Application.WorksheetFunction.SumIf(DataRng.Offset(, MaCol - 1).Resize(, 1), Ma, DataRng.Offset(, SLXuatCol - 1).Resize(, 1))
Data = DataRng.Value
For i = 1 To UBound(Data, 1)
    If Data(i, MaCol) = Ma Then
        TongNhap = TongNhap + Data(i, SLNhapCol)
        If TongNhap > TongXuat Then
            If TongNhap - TongXuat >= SL_Xuat Then
            FIFO = FIFO & " (" & SL_Xuat & "*" & Data(i, GiaNhapCol) & ")"
            Exit For
            Else
            FIFO = FIFO & " (" & (TongNhap - TongXuat) & "*" & Data(i, GiaNhapCol) & ")"
            SL_Xuat = SL_Xuat - (TongNhap - TongXuat)
            TongXuat = TongNhap
            End If
        End If
    End If
Next
FIFO = Replace(Trim(FIFO), " ", "+")
    If TT = True Then
    FIFO = IIf(FIFO = "", 0, Evaluate(FIFO))
    End If
End Function

Cú pháp hàm:
=FIFO(Số lượng xuất, Mã hàng hóa, Bảng dữ liệu, Vị trí cột mã hàng hóa, Vị trí cột SL Nhập, Vị trí cột SL Xuất, Vị trí cột giá nhập, [Thành tiền])
Bạn xem thêm trong file ví dụ.
----------------------------
Công ty chuyển sang dùng OOo nên chẳng còn làm ăn được gì hết. Rầu thiệt.

 

[ThuNghi]

Tôi đã sửa code lại để áp dụng cho trường hợp dữ liệu có nhiều loại hàng hoá và chuyển sang sử lý trên mã để cải thiện tốc độ theo ý kiến của bạn ThuNghi.
Cú pháp hàm:
=FIFO(Số lượng xuất, Mã hàng hóa, Bảng dữ liệu, Vị trí cột mã hàng hóa, Vị trí cột SL Nhập, Vị trí cột SL Xuất, Vị trí cột giá nhập, [Thành tiền])
Bạn xem thêm trong file ví dụ.
----------------------------

Chuyển hẳn sang Array toàn bộ luôn, kg biết có nhanh hơn chút nào, test thử với 1.000 dòng thì OK.
Cú pháp như trên nhưng lưu ý Data Range bao gồm dòng có mã HH cần tính. Cũng có thể lược bớt UDF trên nhưng để tác giả kiểm tra đã.

Option Explicit
Function GiaFifo(SL_Xuat As Double, sMaHH As String, RngData As Range, MaCol As Long, SLNhapCol As Long, SLXuatCol As Long, DGNhapCol As Long, Optional TT As Boolean = False)
On Error GoTo Exit_UDF
Dim TongXuat As Double, TongNhap As Double
Dim i As Long, s As Long, j As Long
Dim GiaXuat(), ArrData(), ArrSlNh(), ArrDgNh()
If SL_Xuat = 0 Or Len(sMaHH) = 0 Then
  GiaFifo = ""
  GoTo Exit_UDF
End If
ArrData() = RngData.Value
TongXuat = 0: s = 0
For i = 1 To UBound(ArrData) - 1
  If ArrData(i, MaCol) = sMaHH Then
    s = s + 1
    ReDim Preserve ArrSlNh(1 To s): ReDim Preserve ArrDgNh(1 To s)
    ArrSlNh(s) = ArrData(i, SLNhapCol)
    ArrDgNh(s) = ArrData(i, DGNhapCol)
    TongXuat = TongXuat + ArrData(i, SLXuatCol)
  End If
Next i
If s = 0 Then
  GiaFifo = ""
  GoTo Exit_UDF
End If
TongNhap = 0: j = 0
For i = 1 To s
  TongNhap = TongNhap + ArrSlNh(i)
  If TongNhap > TongXuat Then
    j = j + 1
    ReDim Preserve GiaXuat(1 To j)
    If TongNhap - TongXuat >= SL_Xuat Then
      GiaXuat(j) = "(" & SL_Xuat & "*" & ArrDgNh(i) & ")"
      Exit For
    Else
      GiaXuat(j) = "(" & (TongNhap - TongXuat) & "*" & ArrDgNh(i) & ")"
      SL_Xuat = SL_Xuat - (TongNhap - TongXuat)
      TongXuat = TongNhap
    End If
  End If
Next i
GiaFifo = Join(GiaXuat, "+")
If TT = True Then
  GiaFifo = Evaluate(GiaFifo)
End If
Erase GiaXuat
Exit_UDF:
Erase ArrData(), ArrSlNh(), ArrDgNh()
If Err <> 0 Then
  MsgBox Err.Description, vbCritical
  Exit Function
End If
End Function

 

[ngo15]

To huuthang_bd: nếu trên cùng 1 dòng có cả số lượng xuất và số lượng nhập thì kết quả không đúng. Nếu trên 1 dòng chỉ được ghi số lượng xuất hoặc nhập thì công thức của Đơn giá xuất không cần /(SUM(D:E)

 

[ThuNghi]

To huuthang_bd: nếu trên cùng 1 dòng có cả số lượng xuất và số lượng nhập thì kết quả không đúng. Nếu trên 1 dòng chỉ được ghi số lượng xuất hoặc nhập thì công thức của Đơn giá xuất không cần /(SUM(D:E)

1/ Đơn giá xuất không cần /(SUM(D:E) chỉ cần chia cho E: DG=Thành tiền xuất / SL Xuất.
2/ Nếu trên cùng 1 dòng có cả số lượng xuất và số lượng nhập. Bạn muốn vậy thì thêm 1 if nữa. Nhưng mà Data thì xuất nhập nằm ở hai dòng riêng chớ và data phải sort theo ngày, nhập, xuất thì mới OK.
Bạn dùng thử UDF của tôi thử xem. Nhớ là dataRng phải gồm luôn dòng có mahh cần lấy Giá Xuất.

 

[huuthang_bd]

To huuthang_bd: nếu trên cùng 1 dòng có cả số lượng xuất và số lượng nhập thì kết quả không đúng. Nếu trên 1 dòng chỉ được ghi số lượng xuất hoặc nhập thì công thức của Đơn giá xuất không cần /(SUM(D:E)

Yêu cầu khi sử dụng UDF này thì bạn ThuNghi cũng đã nói rồi đó, dữ liệu phải được sắp xếp theo thời gian và data thì phải rõ ràng, không có chuyện vừa nhập vừa xuất. Còn ở topic này tôi chỉ quan tâm đến cái UDF, còn các công thức khác là của tác giả.

 

[wap]

Cảm ơn vì File rất hay và hữu ích. Anh Huuthang_bd cho mình hỏi chút. Đơn giá trong phần nhập xuất khi nhập số lẻ (ví dụ như 152,52 đồng) thì nó lại không hiểu là số. Nó hiểu là chữ nên không tính được và hiển thị #VALUE!. Mình mong bạn giúp đỡ. Cảm ơn nhiều.

 

[caokhoe77]

Anh Huuthang_bd cho e hỏi em coppy qua file khac và đã tạo vba đây đủ nhưng khi nhập công thức thi nó lại báo #VALUE!

 

[chuyên gia excel]

Mọi người cho e hỏi nếu ko dùng VBA mà dùng công thức excel không thôi thì có áp dụng được với phương pháp này không ạ?

 

[HieuCD]

Mọi người cho e hỏi nếu ko dùng VBA mà dùng công thức excel không thôi thì có áp dụng được với phương pháp này không ạ?

Chắc chắn được, nhưng làm như thế nào là chuyện khác, tùy theo dữ liệu thực tế, bạn có thể tìm trên diễn đàn