Excel 2021：如何用函数/宏获取年龄差最大家庭的规模

使用Excel 2021处理住院患者数据表格，需找出家庭中最年长者与最年轻者年龄差最大的家庭对应的hhsize（家庭规模）。数据字段包含：

• hserial：家庭序列号（用于识别同一家庭）
• hhsize：家庭规模
• age：患者年龄

数据样例：

以下是两种可行的解决方案：

方法一：函数公式法（Excel 2021动态数组支持） #

分步实现 #

1. 计算每个家庭的年龄差
   在空白单元格（如D2）输入公式，自动溢出所有家庭的年龄差：

   =BYROW(UNIQUE(A2:A11),LAMBDA(x,MAX(FILTER(C2:C11,A2:A11=x))-MIN(FILTER(C2:C11,A2:A11=x))))
   

   • UNIQUE(A2:A11)：提取所有唯一家庭序列号
   • BYROW+LAMBDA：遍历每个家庭，计算该家庭年龄的最大差值

2. 定位最大年龄差对应的家庭
   在空白单元格（如E2）输入公式：

   =INDEX(UNIQUE(A2:A11),MATCH(MAX(D2:D5),D2:D5,0))
   

   替换D2:D5为步骤1中年龄差结果的实际范围

3. 获取对应家庭规模
   在空白单元格（如F2）输入公式：

   =INDEX(B2:B11,MATCH(E2,A2:A11,0))
   

一步到位简化公式 #

使用LET函数整合逻辑，直接输出结果：

=LET(
    unique_hh, UNIQUE(A2:A11),
    age_diffs, BYROW(unique_hh, LAMBDA(x, MAX(FILTER(C2:C11,A2:A11=x))-MIN(FILTER(C2:C11,A2:A11=x)))),
    max_diff_pos, MATCH(MAX(age_diffs), age_diffs, 0),
    target_hh, INDEX(unique_hh, max_diff_pos),
    INDEX(B2:B11, MATCH(target_hh, A2:A11, 0))
)

方法二：VBA宏方法（自动化处理） #

适合数据频繁更新的场景，步骤如下：

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

Sub FindMaxAgeDiffHHSize()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim hhDict As Object
    Dim hserial As String
    Dim age As Integer
    Dim maxDiff As Integer, targetHHSize As Integer
    
    Set ws = ActiveSheet
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    Set hhDict = CreateObject("Scripting.Dictionary")
    
    ' 遍历数据，记录每个家庭的最小/最大年龄及规模
    For i = 2 To lastRow
        hserial = ws.Cells(i, "A").Value
        age = ws.Cells(i, "C").Value
        
        If Not hhDict.Exists(hserial) Then
            hhDict(hserial) = Array(age, age, ws.Cells(i, "B").Value)
        Else
            currentMin = hhDict(hserial)(0)
            currentMax = hhDict(hserial)(1)
            If age < currentMin Then currentMin = age
            If age > currentMax Then currentMax = age
            hhDict(hserial) = Array(currentMin, currentMax, hhDict(hserial)(2))
        End If
    Next i
    
    ' 找出年龄差最大的家庭规模
    maxDiff = -1
    For Each key In hhDict.Keys
        diff = hhDict(key)(1) - hhDict(key)(0)
        If diff > maxDiff Then
            maxDiff = diff
            targetHHSize = hhDict(key)(2)
        End If
    Next key
    
    ' 输出结果到H2单元格
    ws.Cells(2, "G").Value = "年龄差最大的家庭规模："
    ws.Cells(2, "H").Value = targetHHSize
    MsgBox "计算完成，结果已输出到H2单元格"
End Sub

3. 返回Excel，按下Alt+F8运行宏FindMaxAgeDiffHHSize即可得到结果

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