如何对支付数据做unpivot逆透视以构建结构化付款数据库？

需求核心要求 #

对现有A:J列支付数据做逆透视处理，拆分生成三类付款记录，最终输出按日期升序排序的结构化明细，明确每个客户每笔付款的到期日：

• 首付款支付记录
• 按G列分期月数生成对应月度付款记录，月度付款金额默认取整，最后一期月度付款调整金额避免分币差额
• 尾款支付记录

原始数据字段说明（A:J列） #

Google Sheets 实现公式 #

在输出区域的首个单元格（即L2）输入以下数组公式，无需下拉即可自动生成所有符合要求的付款明细：

=SORT(REDUCE({"付款日期","客户名称","邮箱","付款类型","应付金额"},A2:INDEX(A:A,COUNTA(A:A)),LAMBDA(acc,cur,LET(
  客户,OFFSET(cur,0,0),
  邮箱,OFFSET(cur,0,1),
  总金额,VALUE(SUBSTITUTE(SUBSTITUTE(OFFSET(cur,0,3),"$",""),",","")),
  首付金额,OFFSET(cur,0,4),
  首付日期,OFFSET(cur,0,5),
  分期月数,OFFSET(cur,0,6),
  首期月供日,OFFSET(cur,0,7),
  尾款金额,OFFSET(cur,0,8),
  尾款日期,OFFSET(cur,0,9),
  月供总金额,总金额-VALUE(SUBSTITUTE(SUBSTITUTE(首付金额,"$",""),",",""))-VALUE(SUBSTITUTE(SUBSTITUTE(尾款金额,"$",""),",","")),
  常规月供,ROUND(月供总金额/分期月数,0),
  首付行,{首付日期,客户,邮箱,"首付款",首付金额},
  月供行,MAKEARRAY(分期月数,5,LAMBDA(r,c,IF(c=1,EDATE(首期月供日,r-1),IF(c=2,客户,IF(c=3,邮箱,IF(c=4,"第"&r&"期月供",IF(r<分期月数,TEXT(常规月供,"$#,##0.00"),TEXT(月供总金额-常规月供*(分期月数-1),"$#,##0.00"))))))),
  尾款行,{尾款日期,客户,邮箱,"尾款",尾款金额},
  VSTACK(acc,首付行,月供行,尾款行)
))),1,TRUE)

输出字段说明（L:P列） #

注意事项 #

• 若原始数据的金额字段已为数值格式（非带$和逗号的文本格式），可删除公式中SUBSTITUTE转换相关的代码，减少不必要的计算
• 公式会自动识别A列的有效客户数据，新增原始数据时无需手动调整公式范围
• 如需修改金额格式，调整TEXT函数内的格式参数即可

内容的提问来源于stack exchange，提问作者Union Movil