☰

复制版本

tag-icon • tag-icon microsoft-excel lookup
复制版本

在此先感谢您的帮助。场景：我有一些客户以约定的 x 倍数购买“积分”，我需要报告这些包何时耗尽。

我正在从我的数据库中挖掘数据，我的数据结构如下：

第 1 页：

客户ID 信用包 购买日期 累计信用余额
ABC 10 2023-01-01 10
ABC 10 2023-02-01 20
ABC 20 2023-03-01 40
防御 10 2023-01-01 10
防御 5 2023-02-01 15

第 2 页

客户ID 累计使用积分 日期
ABC 1 2023-01-01
ABC 2 2023-01-02
ABC 8 2023-01-03
ABC 三十 2023-01-15

我想要实现的是找到累计信用支出超过累计信用余额的日期，以便我显示信用包的到期日期 - 表格如下所示：

期望结果：

客户ID 信用包 购买日期 累计信用余额 到期日期
ABC 10 2023-01-01 10 2023-01-15
ABC 10 2023-02-01 20 2023-01-15
ABC 20 2023-03-01 40
防御 10 2023-01-01 10
防御 5 2023-02-01 15

当仅处理一个客户的数据集时，我可以通过连接客户 ID 和累计信用余额值，然后使用 xlookup 公式来获得我想要的结果：

第 1 页：

客户ID 信用包 购买日期 累计信用余额 钥匙
ABC 10 2023-01-01 10 ABC10
ABC 10 2023-02-01 20 ABC20
ABC 20 2023-03-01 40 ABC40
防御 10 2023-01-01 10 防御10
防御 5 2023-02-01 15 DEF15

第 2 页

钥匙 客户ID 累计使用积分 日期
ABC1 ABC 1 2023-01-01
ABC2 ABC 2 2023-01-02
ABC8 ABC 8 2023-01-03
ABC30 ABC 三十 2023-01-15

=XLOOKUP(sheet1Key,sheet2key,Date,,1,2)

我的问题是当我有额外的客户时 - 无疑是因为我的“1”匹配模式很吃力。

有人可以帮忙吗？

（编辑——修复预览中令人烦恼的表格格式！）

答案1

复制版本

检索到期日期（或其他列）

=IFERROR(
     XLOOKUP(
         SUM(FILTER(B:B,(A:A=A2)*(C:C<=C2))),
         FILTER(I:I,F:F=A2), FILTER(H:H,F:F=A2),"",1),
     "")

计算 Cumul_Used 的值（辅助列）

=SUM(FILTER(G:G,(F:F=F2)*(H:H<=H2)))

LAMBDA（单一公式）版本

检索到期日期（或其他列）

=LET( _arr, FILTER(A:C, (ROW(A:C)<>1)*(A:A<>"")),
      _id, INDEX(_arr,,1), _date, INDEX(_arr,,3),
      MAP(_id, _date, LAMBDA(id,dt, IFERROR(
         XLOOKUP(
            SUM(FILTER(B:B,(A:A=id)*(C:C<=dt))),
            FILTER(I:I,F:F=id), FILTER(H:H,F:F=id),"",1),""))))

计算 Cumul_Used 的值（辅助列）

=LET( _arr, FILTER(F:H,(ROW(F:F)<>1)*(F:F<>"")),
           _id, INDEX(_arr,,1), _dt, INDEX(_arr,,3),
           MAP(_id, _dt, LAMBDA(id,dt,
                SUM(FILTER(G:G,(F:F=id)*(H:H<=dt))))))

相关内容