如何实现基于Excel原始数据表的自动迭代计算任务？

方法一：使用OpenPyXL（保留原文件公式，调用Excel计算引擎） #

适合原公式文件逻辑复杂、无法轻易转成Python代码的场景，能直接操作带公式的Excel文件并触发计算。

步骤 #

1. 安装依赖

   pip install openpyxl pandas
   

2. 编写Python脚本

   from openpyxl import load_workbook
   import pandas as pd
   
   # 加载原始数据
   dir_data = pd.read_excel("meandirection.xlsx", header=None)
   speed_data = pd.read_excel("meanspeed.xlsx", header=None)
   
   # 校验列数一致性
   if dir_data.shape[1] != speed_data.shape[1]:
       raise ValueError("两个原始数据表的列数必须相同")
   
   # 加载带公式的Excel文件（data_only=False保留公式）
   formula_wb = load_workbook("公式文件.xlsx", data_only=False)
   sheet1 = formula_wb["sheet1"]
   sheet2 = formula_wb["sheet2"]
   
   results = []
   
   # 遍历每一列执行迭代
   for col_idx in range(dir_data.shape[1]):
       # 获取当前列数据（剔除空值）
       dir_col = dir_data.iloc[:, col_idx].dropna().tolist()
       speed_col = speed_data.iloc[:, col_idx].dropna().tolist()
       max_row = max(len(dir_col), len(speed_col))
   
       # 写入meandirection列到sheet1.F列
       for row in range(1, max_row + 1):
           sheet1[f"F{row}"] = dir_col[row-1] if row-1 < len(dir_col) else None
   
       # 写入meanspeed列到sheet2.M列
       for row in range(1, max_row + 1):
           sheet2[f"M{row}"] = speed_col[row-1] if row-1 < len(speed_col) else None
   
       # 保存并重新加载以获取计算结果（解决公式实时计算问题）
       formula_wb.save("temp_calc.xlsx")
       formula_wb = load_workbook("temp_calc.xlsx", data_only=True)
       sheet3 = formula_wb["sheet3"]
   
       # 提取sheet3.P列结果
       p_results = [sheet3[f"P{row}"].value for row in range(1, sheet3.max_row + 1) if sheet3[f"P{row}"].value is not None]
       results.append(p_results)
   
   # 保存最终结果
   pd.DataFrame(results).T.to_excel("最终计算结果.xlsx", index=False, header=False)
   

3. 注意点

   • 确保公式文件开启自动计算（Excel选项→公式→自动计算）
   • 原始数据的空值处理可根据实际需求调整，脚本中默认剔除空值

方法二：使用Pandas（将Excel公式转为Python代码） #

如果原Excel的公式逻辑可以用Python实现，此方法效率更高，无需依赖Excel软件。

步骤 #

1. 分析并转换公式逻辑
   比如原sheet3.P列公式为=F1*M1+10，则编写对应Python函数：

   def calc_p(dir_val, speed_val):
       return dir_val * speed_val + 10
   

2. 编写Python脚本

   import pandas as pd
   
   # 加载原始数据
   dir_data = pd.read_excel("meandirection.xlsx", header=None)
   speed_data = pd.read_excel("meanspeed.xlsx", header=None)
   
   # 校验列数一致性
   if dir_data.shape[1] != speed_data.shape[1]:
       raise ValueError("两个原始数据表的列数必须相同")
   
   results = []
   
   # 遍历每列计算
   for col_idx in range(dir_data.shape[1]):
       dir_col = dir_data.iloc[:, col_idx]
       speed_col = speed_data.iloc[:, col_idx]
       # 执行计算（替换为实际公式逻辑）
       p_col = calc_p(dir_col, speed_col)
       results.append(p_col.dropna().tolist())
   
   # 保存结果
   pd.DataFrame(results).T.to_excel("最终计算结果.xlsx", index=False, header=False)
   

3. 注意点

   • 复杂Excel内置函数（如VLOOKUP、INDEX）需找对应Python实现方式
   • 纯Python环境运行，无需安装Excel

替代方案：Excel VBA宏（在Excel内直接实现） #

不想用Python的话，可在公式文件中编写VBA宏完成自动化：

1. 打开公式文件，按Alt+F11打开VBA编辑器，插入模块并写入代码

   Sub IterateCalc()
       Dim dirWb As Workbook, speedWb As Workbook
       Dim dirWs As Worksheet, speedWs As Worksheet
       Dim sheet1 As Worksheet, sheet2 As Worksheet, sheet3 As Worksheet
       Dim resultWs As Worksheet
       Dim colCount As Integer, i As Integer
   
       ' 绑定工作簿和工作表
       Set dirWb = Workbooks.Open("meandirection.xlsx")
       Set speedWb = Workbooks.Open("meanspeed.xlsx")
       Set dirWs = dirWb.Sheets(1)
       Set speedWs = speedWb.Sheets(1)
       Set sheet1 = ThisWorkbook.Sheets("sheet1")
       Set sheet2 = ThisWorkbook.Sheets("sheet2")
       Set sheet3 = ThisWorkbook.Sheets("sheet3")
   
       ' 创建结果工作表
       On Error Resume Next
       Set resultWs = ThisWorkbook.Sheets("计算结果")
       If Err.Number <> 0 Then
           Set resultWs = ThisWorkbook.Sheets.Add(After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count))
           resultWs.Name = "计算结果"
       End If
       On Error GoTo 0
   
       ' 校验列数
       colCount = dirWs.Cells(1, Columns.Count).End(xlToLeft).Column
       If colCount <> speedWs.Cells(1, Columns.Count).End(xlToLeft).Column Then
           MsgBox "两个数据表列数不一致！"
           Exit Sub
       End If
   
       ' 逐列迭代计算
       For i = 1 To colCount
           ' 写入数据
           dirWs.Columns(i).Copy sheet1.Columns("F")
           speedWs.Columns(i).Copy sheet2.Columns("M")
           ' 等待计算完成
           Application.CalculateUntilAsyncQueriesDone
           ' 复制结果到工作表
           sheet3.Columns("P").Copy resultWs.Columns(i)
       Next i
   
       ' 关闭原始文件
       dirWb.Close SaveChanges:=False
       speedWb.Close SaveChanges:=False
       MsgBox "计算完成！"
   End Sub
   

2. 运行宏：回到Excel界面，按Alt+F8选择IterateCalc执行即可

3. 注意点

   • 原始数据文件需和公式文件在同一目录，或修改代码中文件路径
   • 需启用宏（Excel选项→信任中心→宏设置→启用所有宏）

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