主页 / coding / 问题 / 78746924
Accepted
Albert
Asked: 2024-07-15 00:42:31 +0800 CST

[Excel][Power Query] 按照“先进先出”原则，减少已发货商品的退货清单

  • 772

我的公司正在将一些零件运送到外部喷漆店。有许多喷漆店和许多零件号。我有一份包含每次运输（发货和退货）历史记录的清单。我的目标是处理清单，以便只有最近发送的货物才会出现在清单上。为了更好地解释，请看以下示例：

我的列表：

供应商 零件编号 日期 数量 类型
S01 P0001 2024-05-05 -3 已收到
S01 P0001 2024-05-04 2 发送
S01 P0001 2024-05-03 -5 已收到
S02 P0005 2024-04-24 -6 已收到
S01 P0001 2024-04-02 3 发送
S01 P0001 2024-03-11 10 发送
S02 P0005 2024-03-05 4 发送
S02 P0005 2024-02-05 3 发送
S02 P0001 2024-01-25 -5 已收到
S02 P0001 2024-01-14 5 发送

现在，基于此列表，我想保留那些仍在供应商处（尚未退回）的“已发送”类型的记录。困难的部分是，并非所有已发送的数量都需要退回，这意味着特定数量需要减去已退回的数量（结果为单独的“余额”列）。

预期结果：

供应商 零件编号 日期 数量 平衡
S01 P0001 2024-05-04 2 2
S01 P0001 2024-04-02 3 3
S01 P0001 2024-03-11 10 2
S02 P0005 2024-03-05 4 1

解释：最早发运给供应商 S02 的部件 P0001 已被退回，因此最后两条记录将被删除。

零件 P0005 发送给供应商 S02 的数量为 3，然后又发送了 4，但后来退回了 6。考虑到“先进先出”规则，供应商 S02 仍剩下 1 个，因此结果表中显示第 4 行。

因此，零件 P0001 发送给供应商 S01（这必须独立于发往 S02 的零件），数量为 (10+3+2)，收到涂漆的零件数量为 (5+3)。退回的数量必须从第一批货物中扣除，而其他两批货物仍在供应商处。因此，结果表第 3 行中的余额为 (10-5-3=2)，而第 1 行和第 2 行中的其他两批货物将保持原样。

我的第一次尝试是使用 Power Query，但我不知道该怎么做。这可能吗？如果不行，我下次会尝试 VBA，但我真的希望 PQ 可以完成这项任务。

excel

2 个回答

  1. Best Answer

    在 Power Query 中尝试以下操作。它适用于您上面的示例表，需要使用您的实际数据进行彻底检查。

    let
    Source = YourTable,

    #"Filter Received" = Table.SelectRows(Source, each ([Type] = "Received")),
    #"Grouped Received" = Table.Group(#"Filter Received", {"Supplier", "Part No."}, {{"Received", each List.Sum([Quantity]), type nullable number}}),
    #"Received Table" = #"Grouped Received",

    #"Filtered Rows" = Table.SelectRows(Source, each ([Type] = "Sent")),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Type"}),
    #"Add RunningQty" = Table.AddColumn(#"Removed Columns", "Running Qty", each List.Sum(Table.SelectRows(#"Removed Columns", (x) => x[#"Part No."] = [#"Part No."] and x[Supplier] = [Supplier] and x[Date] <= [Date])[Quantity]) ),
    #"Merged Queries" = Table.NestedJoin(#"Add RunningQty", {"Supplier", "Part No."}, #"Received Table", {"Supplier", "Part No."}, "TblReceived", JoinKind.LeftOuter),
    #"Expanded TblReceived" = Table.ExpandTableColumn(#"Merged Queries", "TblReceived", {"Received"}, {"Received"}),
    #"Add Balance" = Table.AddColumn(#"Expanded TblReceived", "Balance", each let
prevTotal = [Running Qty] - [Quantity],
leftOver = [Running Qty] + [Received],
res = if leftOver <= 0 then null else if Number.Abs([Received]) <= prevTotal then [Quantity] else leftOver
in res),
    #"Filtered Rows1" = Table.SelectRows(#"Add Balance", each ([Balance] <> null)),
    #"Removed Columns1" = Table.RemoveColumns(#"Filtered Rows1",{"Running Qty", "Received"})
in
    #"Removed Columns1"

    在此处输入图片描述

    • 1
    • 使用 Dict 对象来跟踪每个的运行总数Supplier+Part No.
    Option Explicit
Sub Demo()
    Dim oDicIn As Object
    Dim i As Long, j As Long, sKey As String
    Dim arrData, arrRes, iR As Long, ColCnt As Long
    Set oDicIn = CreateObject("scripting.dictionary")
    With Range("A1").CurrentRegion
        .Sort Key1:=.Columns(3), order1:=xlAscending, Header:=xlYes
        arrData = .Value
    End With
    arrRes = arrData
    ColCnt = UBound(arrData, 2)
    For i = LBound(arrData) To UBound(arrData)
        sKey = arrData(i, 1) & "|" & arrData(i, 2)
        If arrData(i, 5) = "Received" Then
            If oDicIn.exists(sKey) Then
                oDicIn(sKey) = oDicIn(sKey) + arrData(i, 4)
            Else
                oDicIn(sKey) = arrData(i, 4)
            End If
        End If
    Next i
    Dim iIn As Long, iOut As Long, bFlag As Boolean
    For i = LBound(arrData) To UBound(arrData)
        If arrData(i, 5) = "Sent" Then
            sKey = arrData(i, 1) & "|" & arrData(i, 2)
            bFlag = False
            If Not oDicIn.exists(sKey) Then
                bFlag = True
            Else
                iIn = oDicIn(sKey)
                iOut = arrData(i, 4)
                oDicIn(sKey) = IIf(iOut + iIn >= 0, 0, iOut + iIn)
                bFlag = (iIn + iOut > 0)
            End If
            If bFlag Then
                oDicIn(sKey) = iOut + iIn
                iR = iR + 1
                For j = 1 To ColCnt - 1
                    arrRes(iR, j) = arrData(i, j)
                Next
                arrRes(iR, j) = IIf(iIn <= 0, iOut + iIn, iOut)
            End If
        End If
    Next i
    Sheets.Add
    Range("A1:E1") = Array("Supplier", "Part No.", "Date", "Quantity", "Balance")
    Range("A2").Resize(iR, 5) = arrRes
End Sub
    • 0

相关问题