Excel跨工作表动态排序分组员工数据实现方法咨询

实现方案选型结论 #

• 如果你使用的是Microsoft 365/Excel 2021及以上版本，可以用动态数组公式 + 数据有效性组合实现90%以上需求，仅城市分组行的下拉菜单需要手动做一次模板或者配合极少量事件宏完成
• 如果你使用的是更低版本的Excel，或者需要完全自动触发、不需要手动操作，推荐用VBA宏实现

公式实现方案（仅适用于Excel 365/2021+） #

1. 首先在Sheet2的A1单元格输入筛选+排序的动态数组公式，假设Sheet1的A列是FIRSTNAME、B列是LASTNAME、C列是CITY、D列是ACTIVE，可根据实际列号调整：
   =SORT(FILTER(Sheet1!A:D,Sheet1!D:D="是","无符合条件的在职员工"),{3,2},1)
   该公式会自动筛选出所有在职员工，按城市、姓氏升序排列，数据源更新后会自动刷新结果。
2. 城市分组行的插入和下拉菜单需要额外处理：你可以在公式输出区域旁用UNIQUE函数提取所有城市列表，=UNIQUE(INDEX(SORT(FILTER(Sheet1!A:D,Sheet1!D:D="是"),{3,2},1),0,3))，再手动给每个城市对应的分组行末尾添加数据有效性下拉菜单，下拉选项可以提前在隐藏列定义好。

VBA宏实现方案（全版本适配，完全自动化） #

用Worksheet_Change事件即可实现全自动化同步，逻辑如下：

1. 每次Sheet1的数据发生修改时自动触发宏程序
2. 程序先清空Sheet2原有内容，然后遍历Sheet1所有行，筛选出ACTIVE为是的行
3. 对筛选出的行按城市、姓氏排序，按城市分组插入分组标题行
4. 给每个分组标题行的最后一列批量添加数据有效性下拉菜单
5. 全程不需要手动操作，修改Sheet1数据后自动更新Sheet2结果

你可以把以下示例代码放到Sheet1（Employers）的代码模块中，根据自己的实际列号、下拉选项调整参数即可使用：

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim wsSource As Worksheet, wsTarget As Worksheet
    Dim lastRow As Long, i As Long, currentCity As String
    Dim targetRow As Long
    
    Set wsSource = ThisWorkbook.Sheets("Employers")
    Set wsTarget = ThisWorkbook.Sheets("Sheet2")
    targetRow = 1
    currentCity = ""
    
    ' 清空目标表原有内容
    wsTarget.Cells.Clear
    
    ' 源表按城市、姓氏排序
    lastRow = wsSource.Cells(wsSource.Rows.Count, "C").End(xlUp).Row
    With wsSource.Sort
        .SortFields.Clear
        .SortFields.Add Key:=wsSource.Range("C1:C" & lastRow), Order:=xlAscending
        .SortFields.Add Key:=wsSource.Range("B1:B" & lastRow), Order:=xlAscending
        .SetRange wsSource.Range("A1:D" & lastRow)
        .Header = xlYes
        .Apply
    End With
    
    ' 遍历筛选写入目标表，插入分组行
    For i = 2 To lastRow
        If wsSource.Cells(i, "D").Value = "是" Then
            ' 新城市插入分组标题行
            If wsSource.Cells(i, "C").Value <> currentCity Then
                currentCity = wsSource.Cells(i, "C").Value
                wsTarget.Cells(targetRow, 1).Value = currentCity & " 员工分组"
                ' 给分组行加下拉菜单，可自定义选项
                With wsTarget.Cells(targetRow, 5).Validation
                    .Delete
                    .Add Type:=xlValidateList, Formula1:="已完成,处理中,未开始"
                End With
                targetRow = targetRow + 1
            End If
            ' 写入员工明细
            wsSource.Range("A" & i & ":D" & i).Copy wsTarget.Range("A" & targetRow)
            targetRow = targetRow + 1
        End If
    Next i
End Sub

注意：如果选择宏方案，需要将文件保存为.xlsm格式，启用宏后才能正常运行。

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