基于Vendor和Source分组查找95%订单量对应天数的公式求助

Excel Spillable Formula Approach #

Generate the full result table in one go using a spill formula with LET, BYROW, and FILTER:

=LET(
    uniqueGroups, UNIQUE(GroupedData[Vendor]:GroupedData[Source]),
    totalOrders, BYROW(uniqueGroups, LAMBDA(group, SUMIFS(GroupedData[Orders], GroupedData[Vendor], INDEX(group,1), GroupedData[Source], INDEX(group,2)))),
    threshold, totalOrders * 0.95,
    targetDays, BYROW(HSTACK(uniqueGroups, threshold), LAMBDA(row, 
        LET(vendor, INDEX(row,1), source, INDEX(row,2), thresh, INDEX(row,3),
            FILTER(GroupedData[Days], (GroupedData[Vendor]=vendor)*(GroupedData[Source]=source)*(GroupedData[Running Total]>=thresh), "")[1]
        )
    )),
    HSTACK(uniqueGroups, totalOrders, threshold, targetDays)
)

Breakdown: #

• uniqueGroups: Extracts distinct Vendor/Source pairs
• totalOrders: Calculates total orders for each group using SUMIFS
• threshold: Computes 95% of the total orders per group
• targetDays: Filters the original table for each group, finds the first Day where Running Total exceeds the threshold
• HSTACK: Combines all columns into a single spilled result table

PowerQuery Solution #

For a robust, scalable solution (ideal for large datasets), use PowerQuery:

1. Load your GroupedData table into PowerQuery (Data > From Table/Range)
2. Group by Vendor and Source:
   • Go to Transform > Group By, select "Advanced"
   • Add grouping columns: Vendor and Source
   • Add two aggregations:
     • Name: TotalOrders, Operation: Sum, Column: Orders
     • Name: GroupedRows, Operation: All Rows
3. Add a custom column to find the target Day:
   • Go to Add Column > Custom Column, enter this formula:

     let
         Threshold = [TotalOrders] * 0.95,
         FilteredRows = Table.SelectRows([GroupedRows], each [Running Total] >= Threshold),
         FirstDay = if Table.RowCount(FilteredRows) > 0 then FilteredRows[Days]{0} else null
     in
         FirstDay
     

4. (Optional) Add a Threshold column with the formula [TotalOrders] * 0.95
5. Remove the GroupedRows column if not needed
6. Click Close & Load to export the result to Excel

Why your XLOOKUP failed: #

XLOOKUP can't natively iterate over multiple groups in a spilled array. Using BYROW to loop through each unique Vendor/Source pair fixes this issue, as it processes one group at a time.

内容的提问来源于stack exchange，提问作者Darren Bartrup-Cook