如何高效验证贷款有效性？核对当前与历史未结清贷款记录

合规规则 #

发放当前贷款时，不得存在未结清贷款（即历史贷款中，到期日期晚于当前贷款发放日期的记录）

Excel 实现方案 #

方法1：动态数组公式（适用于Excel 365/2021，小数据量） #

1. 先将数据按**发放日期（Disbursement Date）**升序排序
2. 在is the loan valid列的首行数据单元格（如E2）输入公式：

   =NOT(MAX(IF($B$1:B1<$B2,$C$1:C1,""))>$B2)
   

   • 按回车自动填充（旧版Excel需按Ctrl+Shift+Enter作为数组公式输入）
   • 逻辑：提取当前行之前所有早于当前发放日期的贷款到期日，取最大值。若最大值大于当前发放日，说明存在未结清贷款，返回FALSE（无效）；反之返回TRUE（有效）

方法2：Power Query（适用于大数据量，避免公式卡顿） #

1. 选中数据区域，点击「数据」→「从表格/区域」进入Power Query编辑器
2. 按发放日期升序排序
3. 添加自定义列，输入公式：

   = List.AllTrue(List.Transform(List.Range(#"Sorted Rows"[Maturity Date],0,[Index]), (x) => x <= [Disbursement Date]))
   

   • 逻辑：检查当前行之前所有贷款的到期日是否都≤当前发放日，是则标记为TRUE，否则为FALSE
4. 点击「关闭并上载」，将结果导出回Excel

Python（Pandas）实现方案 #

适合超大数据量处理，运行效率远高于Excel公式

1. 读取并预处理数据：

   import pandas as pd
   
   # 读取贷款数据（支持Excel/CVS等格式）
   df = pd.read_excel("loans.xlsx")
   
   # 转换日期列为datetime类型
   df["Disbursement Date"] = pd.to_datetime(df["Disbursement Date"])
   df["Maturity Date"] = pd.to_datetime(df["Maturity Date"])
   

2. 按发放日期排序：

   df = df.sort_values("Disbursement Date").reset_index(drop=True)
   

3. 批量校验贷款有效性：

   # 计算当前行之前所有贷款的最晚到期日
   df["max_prev_maturity"] = df["Maturity Date"].expanding().max().shift(1)
   # 校验：最晚到期日≤当前发放日则有效，否则无效（空值填充为极小时间，确保首笔贷款有效）
   df["is the loan valid"] = df["max_prev_maturity"].fillna(pd.Timestamp.min) <= df["Disbursement Date"]
   

4. 导出结果：

   df.to_excel("validated_loans.xlsx", index=False)
   

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