Excel数据透视表与公式/VBA需求：同一年份多值留空、利率一致则填充

需求回顾 #

你有「利率」和「到期年份」两列数据，希望按年份汇总时：

• 若某一年份下所有利率值完全相同（比如示例中的2021年），则显示该利率值
• 若年份下存在多个不同利率值（比如示例中的2023年），则对应单元格留空

示例数据：

方案1：Excel动态数组公式（适合Excel 365/2021及以后版本） #

不需要依赖数据透视表，直接用动态数组公式生成汇总结果，步骤如下：

1. 选中空白单元格（比如D1），输入以下公式：

=LET(
    唯一年份, UNIQUE(B2:B10),
    汇总利率, BYROW(唯一年份, LAMBDA(年份, LET(
        年份利率集合, FILTER(A2:A10, B2:B10=年份),
        IF(COUNTA(UNIQUE(年份利率集合))=1, 年份利率集合[1], "")
    ))),
    HSTACK(唯一年份, 汇总利率)
)

2. 按下回车，公式会自动生成包含「到期年份」和「统一利率」的汇总表格

公式说明 #

• UNIQUE(B2:B10)：提取所有不重复的到期年份
• BYROW：遍历每个唯一年份，对每个年份执行逻辑判断
• FILTER(A2:A10, B2:B10=年份)：筛选出当前年份的所有利率值
• COUNTA(UNIQUE(年份利率集合))=1：判断该年份的利率是否全相同（唯一值数量为1）
• HSTACK：把年份列和汇总利率列合并成最终表格

方案2：辅助列+数据透视表（兼容所有Excel版本） #

如果你的Excel不支持动态数组，可以用辅助列配合数据透视表实现：

1. 添加辅助列（比如C列，命名为「统一利率标识」），在C2单元格输入公式：

=IF(COUNTIF($B:$B,B2)=SUMPRODUCT(--($B:$B=B2)*($A:$A=A2)),A2,"")

2. 把公式下拉填充到所有数据行
3. 插入数据透视表：
   • 行字段选择「到期年份」
   • 值字段选择「统一利率标识」，然后将值汇总方式设置为最大值（或最小值，因为相同利率的情况下最大值/最小值就是该利率，不同利率的情况下会忽略空值，最终显示空）

辅助列公式说明 #

• COUNTIF($B:$B,B2)：统计当前年份的总行数
• SUMPRODUCT(--($B:$B=B2)*($A:$A=A2))：统计当前年份下，和当前行利率相同的行数
• 两者相等时，说明该年份所有利率都一致，显示利率；否则留空

方案3：VBA代码（适合批量处理或自动化场景） #

如果需要频繁处理这类数据，可以用VBA宏一键生成汇总结果：

1. 按下Alt + F11打开VBA编辑器
2. 插入新模块，粘贴以下代码：

Sub SummarizeRatesByYear()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim yearDict As Object
    Dim i As Long
    Dim currentYear As Variant
    Dim currentRate As String
    Dim outputRow As Long
    
    ' 源数据所在工作表，可根据实际修改
    Set ws = ThisWorkbook.Worksheets("Sheet1")
    lastRow = ws.Cells(ws.Rows.Count, "B").End(xlUp).Row
    Set yearDict = CreateObject("Scripting.Dictionary")
    
    ' 遍历数据，收集每个年份的唯一利率
    For i = 2 To lastRow
        currentYear = ws.Cells(i, "B").Value
        currentRate = ws.Cells(i, "A").Value
        
        If Not yearDict.Exists(currentYear) Then
            yearDict.Add currentYear, New Collection
        End If
        
        ' 利用集合的Key唯一性，只保留唯一利率
        On Error Resume Next
        yearDict(currentYear).Add currentRate, Key:=CStr(currentRate)
        On Error GoTo 0
    Next i
    
    ' 创建新工作表存放结果
    Set ws = ThisWorkbook.Worksheets.Add
    ws.Name = "利率汇总结果"
    ws.Cells(1, 1).Value = "到期年份"
    ws.Cells(1, 2).Value = "统一利率"
    outputRow = 2
    
    ' 输出汇总结果
    For Each currentYear In yearDict.Keys
        ws.Cells(outputRow, 1).Value = currentYear
        ' 集合大小为1说明所有利率一致
        If yearDict(currentYear).Count = 1 Then
            ws.Cells(outputRow, 2).Value = yearDict(currentYear)(1)
        Else
            ws.Cells(outputRow, 2).Value = ""
        End If
        outputRow = outputRow + 1
    Next currentYear
    
    ' 格式化结果表格
    ws.Rows(1).Font.Bold = True
    ws.Columns.AutoFit
End Sub

3. 回到Excel，按下Alt + F8运行宏「SummarizeRatesByYear」，即可在新工作表中得到汇总结果

VBA代码说明 #

• 用字典存储每个年份对应的唯一利率集合（集合自动去重）
• 遍历字典判断每个年份的唯一利率数量，数量为1则显示利率，否则留空
• 自动创建新工作表并格式化结果，无需手动操作

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