Phân bổ số lượng nguyên vật liệu từ kho hoặc từ đơn hàng mua vào thành phẩm

[ButBi2020]

Chào các bạn, mình có một bài toán cần phân bổ số lượng nguyên vật liệu từ kho hoặc từ các đơn mua hàng đang mua về, ghi chú là từ kho hay từ số đơn hàng cho từng dòng thành phẩm. Hoặc từ cả 2 (kho và đơn hàng) hoặc từ 2 đơn hàng đang mua về.
Với số lượng nguyên vật liệu tồn kho chỉ là 11, nhưng do cấu trúc file mình đang làm nên số lượng nguyên vật liệu tồn kho hiển thị ở tất cả các dòng.
Không biết excel có công thức nào có thể liệt kê ra được như ví dụ trong file không?
Nhờ các bạn giúp đỡ. Mình cám ơn!

 

[HieuCD]

Chào các bạn, mình có một bài toán cần phân bổ số lượng nguyên vật liệu từ kho hoặc từ các đơn mua hàng đang mua về, ghi chú là từ kho hay từ số đơn hàng cho từng dòng thành phẩm. Hoặc từ cả 2 (kho và đơn hàng) hoặc từ 2 đơn hàng đang mua về.
Với số lượng nguyên vật liệu tồn kho chỉ là 11, nhưng do cấu trúc file mình đang làm nên số lượng nguyên vật liệu tồn kho hiển thị ở tất cả các dòng.
Không biết excel có công thức nào có thể liệt kê ra được như ví dụ trong file không?
Nhờ các bạn giúp đỡ. Mình cám ơn!

Trong khi chờ công thức của Excel 365, dùng đỡ hàm tự tạo bằng VBA

Function PhanBo(ByVal NVL As Range, ByVal TP_PO As Range, ByVal DinhMuc As Range, ByVal MaNVL$, ByVal Ton As Double, Optional dong& = 0)
  Dim arr(), sRow&, i&, r&, fR&, k&, res$(), DM#
 
  sRow = NVL.Rows.Count
  ReDim arr(0 To sRow, 1 To 2)
  ReDim res(1 To sRow, 1 To 1)
  For i = 1 To sRow
    If NVL(i, 1) = MaNVL Then
      If DinhMuc(i, 1) < 0 Then
        If arr(0, 1) = Empty Then
          arr(0, 1) = " TU KHO"
          arr(0, 2) = Ton
        End If
      ElseIf DinhMuc(i, 1) > 0 Then
        k = k + 1
        arr(k, 1) = " TU PO " & TP_PO(i, 1)
        arr(k, 2) = DinhMuc(i, 1)
      End If
    End If
  Next i

  For i = 1 To sRow
    If NVL(i, 1) = MaNVL Then
      If DinhMuc(i, 1) < 0 Then
        DM = -DinhMuc(i, 1)
        For r = fR To k
          If arr(r, 2) > DM Then
            If res(i, 1) = Empty Then
              res(i, 1) = DM & arr(r, 1)
            Else
              res(i, 1) = res(i, 1) & " & " & DM & arr(r, 1)
            End If
            arr(r, 2) = arr(r, 2) - DM
            fR = r
            Exit For
          Else
            If res(i, 1) = Empty Then
              res(i, 1) = arr(r, 2) & arr(r, 1)
            Else
              res(i, 1) = res(i, 1) & " & " & arr(r, 2) & arr(r, 1)
            End If
            DM = DM - arr(r, 2)
            If DM = 0 Then fR = r + 1:        Exit For
          End If
        Next r
      End If
    End If
  Next i
  If dong = 0 Then PhanBo = res Else PhanBo = res(dong, 1)
End Function

Công thức ô E2

=PhanBo($A$2:$A$22,$B$2:$B$22,$D$2:$D$22,A2,$C$2,ROW(A1))

Copy xuống
Hoặc nhập công thức mảng nhiều ô theo gợi ý trong file

 

[ButBi2020]

Cám ơn bạn nhiều, đúng là những gì mình cần.

 

[ButBi2020]

Sau khi thử nghiệm các tình huống thì mình thấy còn vẫn chưa đúng ở trường hợp nếu một yêu cầu Nguyên Vật Liệu (NVL) nào đó nhiều hơn số lượng trên đơn hàng đang về thì công thức bắt đầu không tính toán đúng:
1. Như ban đầu, công thức tính tốt.
2. Dòng thứ 13, số lượng NVL yêu cầu cho thành phẩm tăng lên -100 thì công thức vẫn đúng.
3. Dòng thứ 13, số lượng NVL yêu cầu cho thành phẩm tăng lên từ -106 trở lên thì bắt đầu công thức không còn đúng nữa.
Mình có thêm một sự trợ giúp nữa là với trường hợp số lượng trên đơn hàng mua về nhiều hơn số lượng yêu cầu cho thành phẩm thì bắt đầu có thể ghi thêm: "NHẬP KHO ......"
Cám ơn các bạn!

 

[HieuCD]

Chỉnh lại tí . .

Function PhanBo(ByVal NVL As Range, ByVal TP_PO As Range, ByVal DinhMuc As Range, ByVal MaNVL$, ByVal Ton As Double, Optional dong& = 0)
  Dim arr(), sRow&, i&, r&, fR&, k&, res$(), DM#
 
  sRow = NVL.Rows.Count
  ReDim arr(0 To sRow, 1 To 2)
  ReDim res(1 To sRow, 1 To 1)
  For i = 1 To sRow
    If NVL(i, 1) = MaNVL Then
      If DinhMuc(i, 1) < 0 Then
        If arr(0, 1) = Empty Then
          arr(0, 1) = " TU KHO"
          arr(0, 2) = Ton
        End If
      ElseIf DinhMuc(i, 1) > 0 Then
        k = k + 1
        arr(k, 1) = " TU PO " & TP_PO(i, 1)
        arr(k, 2) = DinhMuc(i, 1)
      End If
    End If
  Next i

  For i = 1 To sRow
    If NVL(i, 1) = MaNVL Then
      If DinhMuc(i, 1) < 0 Then
        DM = -DinhMuc(i, 1)
        For r = fR To k
          If arr(r, 2) > DM Then
            If res(i, 1) = Empty Then
              res(i, 1) = DM & arr(r, 1)
            Else
              res(i, 1) = res(i, 1) & " & " & DM & arr(r, 1)
            End If
            arr(r, 2) = arr(r, 2) - DM
            fR = r
            Exit For
          Else
            If res(i, 1) = Empty Then
              res(i, 1) = arr(r, 2) & arr(r, 1)
            Else
              res(i, 1) = res(i, 1) & " & " & arr(r, 2) & arr(r, 1)
            End If
            DM = DM - arr(r, 2)
            fR = r + 1
            If DM = 0 Then Exit For
          End If
        Next r
      End If
    End If
  Next i
  If dong = 0 Then PhanBo = res Else PhanBo = res(dong, 1)
End Function

 

[ButBi2020]

Trên cả tuyệt vời, đúng là những gì mình cần. Nhờ bạn xem thêm dùm mình một cái nữa là nếu dòng thành phẩm cuối cùng dùng đến NVL cuối cùng mà vẫn chưa đủ số yêu cầu thì ghi thêm: " & thiếu "SL" ".

Ví dụ theo hình là "& thiếu 89"