如何读取含多工作表的Excel文件并动态跨表读写指定单元格数据

下面提供三种不同场景下的可行方案，均支持动态调整规则，无需频繁修改核心逻辑：

方案1：Excel VBA（适合Excel原生环境用户） #

核心是通过配置表定义所有提取/写入规则，VBA读取配置后自动执行，完全避免硬编码。

• 步骤1：在数据表1中新建名为「配置表」的工作表，按以下列名设置表头并填充规则：

  数据源Sheet名	源单元格/区域	目标数据表路径	目标Sheet名	目标起始单元格
  Sheet1	A1	D:\数据表2.xlsx	汇总表	B2
  Sheet2	C3:E5	D:\数据表2.xlsx	汇总表	B5

• 步骤2：插入VBA代码（按Alt+F11打开编辑器）：

  Sub DynamicDataTransfer()
      Dim configWs As Worksheet, dataWs As Worksheet
      Dim targetWb As Workbook
      Dim lastRow As Long, i As Long
      
      Set configWs = ThisWorkbook.Sheets("配置表")
      lastRow = configWs.Cells(Rows.Count, 1).End(xlUp).Row
      
      For i = 2 To lastRow ' 跳过表头行
          ' 读取当前行的配置规则
          Dim sourceSheet$, sourceRng$, targetPath$, targetSheet$, targetRng$
          sourceSheet = configWs.Cells(i, 1).Value
          sourceRng = configWs.Cells(i, 2).Value
          targetPath = configWs.Cells(i, 3).Value
          targetSheet = configWs.Cells(i, 4).Value
          targetRng = configWs.Cells(i, 5).Value
          
          ' 读取源数据，异常捕获
          On Error Resume Next
          Set dataWs = ThisWorkbook.Sheets(sourceSheet)
          If Err.Number <> 0 Then
              MsgBox "数据源Sheet " & sourceSheet & " 不存在，跳过该行"
              Err.Clear
              GoTo NextLoop
          End If
          On Error GoTo 0
          
          Dim sourceData As Variant
          sourceData = dataWs.Range(sourceRng).Value
          
          ' 写入目标数据，异常捕获
          On Error Resume Next
          Set targetWb = Workbooks.Open(targetPath)
          If Err.Number <> 0 Then
              MsgBox "目标文件 " & targetPath & " 无法打开，跳过该行"
              Err.Clear
              GoTo NextLoop
          End If
          On Error GoTo 0
          
          targetWb.Sheets(targetSheet).Range(targetRng).Value = sourceData
          targetWb.Close SaveChanges:=True
          
  

NextLoop:
Next i

MsgBox "所有任务执行完成"

End Sub

- 动态化优势：新增/修改规则只需在配置表中编辑，无需改动代码；异常捕获逻辑避免单个错误中断全部任务。

## 方案2：Python + Pandas（适合批量/复杂逻辑场景）
用**配置文件**存储所有任务规则，Python读取后批量处理，支持自定义数据转换、定时执行等扩展功能。

- 步骤1：创建JSON配置文件（比如`data_config.json`），定义每个提取任务：
```json
[
    {
        "source_file": "数据表1.xlsx",
        "source_sheet": "Sheet1",
        "source_cell": "A1",
        "target_file": "数据表2.xlsx",
        "target_sheet": "汇总表",
        "target_cell": "B2"
    },
    {
        "source_file": "数据表1.xlsx",
        "source_sheet": "Sheet3",
        "source_cell": "D7",
        "target_file": "数据表2.xlsx",
        "target_sheet": "汇总表",
        "target_cell": "B4"
    }
]

• 步骤2：编写Python代码：

  import json
  from openpyxl import load_workbook
  
  def transfer_single_task(task):
      # 读取源单元格数据
      source_wb = load_workbook(task['source_file'], data_only=True)
      source_ws = source_wb[task['source_sheet']]
      source_value = source_ws[task['source_cell']].value
      source_wb.close()
  
      # 写入目标单元格
      target_wb = load_workbook(task['target_file'])
      target_ws = target_wb[task['target_sheet']]
      target_ws[task['target_cell']] = source_value
      target_wb.save(task['target_file'])
      target_wb.close()
  
  def dynamic_transfer(config_path):
      with open(config_path, 'r', encoding='utf-8') as f:
          tasks = json.load(f)
      
      for task in tasks:
          try:
              transfer_single_task(task)
              print(f"任务完成：从{task['source_sheet']}的{task['source_cell']}写入到{task['target_sheet']}的{task['target_cell']}")
          except Exception as e:
              print(f"任务失败：{str(e)}")
  
  if __name__ == "__main__":
      dynamic_transfer('data_config.json')
  

• 动态化优势：配置文件可随时新增任务；支持读取单元格区域、添加数据清洗/转换逻辑；可结合schedule库实现定时自动执行。

方案3：Power Query（无代码/低代码，适合快速配置） #

通过参数化查询实现动态提取，全程可视化操作，无需编写代码。

• 步骤1：打开Excel，点击「数据」选项卡 → 「获取数据」→ 「自文件」→ 「自工作簿」，选择数据表1，导入任意一个Sheet的数据到Power Query编辑器。

• 步骤2：创建参数：点击「主页」→ 「管理参数」→ 「新建参数」，分别创建：

  • 源Sheet名称：类型选「文本」，允许的值选「列表」，输入Sheet1、Sheet2、Sheet3、Sheet4
  • 源单元格地址：类型选「文本」，默认值设为A1
  • 目标单元格地址：类型选「文本」，默认值设为B2

• 步骤3：修改Power Query的数据源，将固定Sheet名替换为源Sheet名称参数，提取指定单元格的数据。

• 步骤4：关闭并加载数据到数据表2的指定位置（通过修改加载选项选择目标单元格）。

• 步骤5：动态调整：修改参数值后，点击「数据」→ 「全部刷新」即可更新结果；可复制查询创建多个任务，批量刷新。

• 动态化优势：完全可视化操作，无需代码；参数调整直观，适合非技术用户；支持设置自动刷新频率。

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