求Excel公式：根据ON HAND QTY计算SKU的earliest date
2026-6-15
需求说明
第一个表格为SKU收货明细，包含SKU、收货数量（qty）及收货日期（receiving date）：
| SKU | qty | receiving date |
|---|---|---|
| A | 100 | 01/01/2023 |
| A | 200 | 01/02/2023 |
| A | 300 | 01/03/2023 |
| B | 300 | 01/04/2023 |
| B | 200 | 01/05/2023 |
| B | 100 | 01/06/2023 |
| C | 200 | 01/07/2023 |
| C | 300 | 01/08/2023 |
| C | 300 | 01/09/2023 |
第二个表格为各SKU的在手库存数量（ON HAND QTY），需计算对应的earliest date：
| SKU | ON HAND QTY | earliest date |
|---|---|---|
| A | 375 | ? |
| B | 450 | ? |
| C | 550 | ? |
计算规则
针对每个SKU，从最晚的收货日期开始向前累加收货数量，当累加总量达到在手库存数量时，对应的收货日期即为earliest date的结果。
预期结果
- SKU A：
01/02/2023 - SKU B：
01/04/2023 - SKU C：
01/08/2023
Excel公式实现
假设第一个表格数据位于A2:C10（SKU在A列，qty在B列，receiving date在C列），第二个表格中SKU在E2:E4，ON HAND QTY在F2:F4，可在G2单元格输入对应公式后下拉填充：
适用于Excel 365/2021（支持动态数组）
=XLOOKUP(TRUE,SCAN(0,SORT(FILTER($B$2:$C$10,$A$2:$A$10=E2),2,-1),LAMBDA(a,b,a+b))>=F2,SORT(FILTER($B$2:$C$10,$A$2:$A$10=E2),2,-1),,0,1)
兼容旧版Excel的数组公式
=LOOKUP(F2,SUMPRODUCT(($A$2:$A$10=E2)*($C$2:$C$10>=LARGE(IF($A$2:$A$10=E2,$C$2:$C$10),ROW(INDIRECT("1:"&COUNTIF($A$2:$A$10,E2)))))*$B$2:$B$10),LARGE(IF($A$2:$A$10=E2,$C$2:$C$10),ROW(INDIRECT("1:"&COUNTIF($A$2:$A$10,E2)))))
（注：旧版Excel需按Ctrl+Shift+Enter完成数组公式输入）
内容的提问来源于stack exchange，提问作者TIANG程
相关产品推荐
相关产品推荐
