Excel 2016自动化日报需求：基于数据工作表生成筛选报表

针对你16000+列的动态更新数据表，以下是三种适配Excel 2016的实现方案，按推荐优先级排序：

方案一：Power Query（推荐，适配大列数+动态更新） #

Power Query是Excel 2016内置的大数据处理工具，天生支持动态列识别，性能优于公式和VBA。

• 步骤1：导入数据到Power Query
  切换到数据选项卡，点击自表格/区域，选中data表的全部数据（勾选我的表格有标题），进入Power Query编辑器。
• 步骤2：添加筛选规则
  在编辑器中，找到国家/地区列（按实际列名调整），点击列标题筛选按钮，仅勾选America；再找到职位列，筛选出Manager。
• 步骤3：设置动态列同步
  点击主页→关闭并上载至，选择仅创建连接并勾选加载到工作表时刷新数据。回到report表，通过数据→现有连接将该连接加载到工作表，后续data表增删列后，只需刷新连接即可同步列结构。
• 步骤4：配置自动刷新
  右键Power Query连接→属性，在使用状况选项卡勾选打开文件时刷新数据，也可设置定时刷新（按需开启）。

方案二：超级表+数组公式（轻量场景适配） #

适合不想使用Power Query的场景，利用超级表自动扩展范围的特性实现列同步。

• 步骤1：将data表转为超级表
  选中data表所有数据，按Ctrl+T，勾选我的表格有标题，后续列增删会自动扩展表范围。
• 步骤2：编写筛选数组公式
  在report表A1单元格输入表头（如ID），A2单元格输入以下数组公式，按Ctrl+Shift+Enter确认（Excel 2016老版数组公式需此组合键）：

  =IFERROR(INDEX(data[ID], SMALL(IF((data[国家/地区]="America")*(data[职位]="Manager"), ROW(data[ID])-ROW(data[#Headers]), ""), ROW(A1))), "")
  

  下拉公式至出现空值即可，其他列只需替换公式中的data[ID]为对应列名。
• 步骤3：列增删同步
  data表新增列后，直接在report表复制对应列的公式即可；删除列时，公式会自动报错，清空对应列公式即可。

方案三：VBA脚本（完全自动化场景） #

适合需要一键生成或自动触发报表的场景，代码自动识别列的增删。

• 步骤1：编写VBA代码
  按Alt+F11打开VBA编辑器，插入新模块，粘贴以下代码（按实际列名调整Country和JobTitle）：

  Sub GenerateDailyReport()
      Dim wsData As Worksheet, wsReport As Worksheet
      Dim lastRow As Long, lastCol As Long
      Dim i As Long, reportRow As Long
      
      Set wsData = ThisWorkbook.Worksheets("data")
      Set wsReport = ThisWorkbook.Worksheets("report")
      
      ' 清空报表数据（保留表头）
      wsReport.Range("A2:" & wsReport.Cells(wsReport.Rows.Count, wsReport.Columns.Count).Address).ClearContents
      
      ' 获取数据范围
      lastRow = wsData.Cells(wsData.Rows.Count, 1).End(xlUp).Row
      lastCol = wsData.Cells(1, wsData.Columns.Count).End(xlToLeft).Column
      
      ' 复制表头
      wsData.Range(wsData.Cells(1, 1), wsData.Cells(1, lastCol)).Copy wsReport.Cells(1, 1)
      
      reportRow = 2
      ' 筛选并复制符合条件的行
      For i = 2 To lastRow
          If wsData.Cells(i, wsData.Rows(1).Find("Country").Column).Value = "America" And _
             wsData.Cells(i, wsData.Rows(1).Find("JobTitle").Column).Value = "Manager" Then
              wsData.Range(wsData.Cells(i, 1), wsData.Cells(i, lastCol)).Copy wsReport.Cells(reportRow, 1)
              reportRow = reportRow + 1
          End If
      Next i
      
      wsReport.Columns.AutoFit
  End Sub
  

• 步骤2：设置触发方式
  在report表添加表单控件按钮，关联该宏，点击即可生成报表；也可在ThisWorkbook模块添加以下代码，实现打开工作簿自动生成：

  Private Sub Workbook_Open()
      GenerateDailyReport
  End Sub
  

• 步骤3：列增删适配
  代码通过Find方法定位列，只要列名不变，新增或删除列时无需修改代码。

注意事项 #

• 16000+列的大数据场景下，Power Query性能最优，公式和VBA可能出现卡顿，优先推荐Power Query方案。
• 超级表和Power Query均支持自动识别列的增删，无需手动调整数据范围。

内容的提问来源于stack exchange，提问作者Mr.Paradox