Python+openpyxl跨工作簿VLOOKUP公式循环应用失效问题求助

环境与处理流程 #

• 拥有文件：
  • 一份Excel基础模板文件
  • 一组Excel文件
• 处理流程：
  1. 用for循环遍历每个Excel文件
  2. 基于每个文件内容，用VLOOKUP函数填充基础模板
  3. 将填充后的模板以当前迭代的文件名保存

问题现象 #

循环写入的VLOOKUP公式未生效，文件对应单元格显示空白

相关代码 #

def aiakos() :
    import openpyxl as op
    import os, glob
    from openpyxl import load_workbook
    from openpyxl.utils import  

    outpath = r"C:\Users\pallist\AROTRON_OUT" ## 输出路径
    inpath = r"C:\Users\pallist\AROTRON_IN" ## 输入路径
    files = glob.glob(os.path.join(inpath, "*.xlsx")) ## 所有输入文件
    wb0 = load_workbook(os.path.join(outpath, "template.xlsx")) ## 模板文件
    sheet0 = wb0['Φύλλο1'] ## 模板工作表


    for f in files :  ## 遍历每个输入文件
        fwb=load_workbook(f)  ## 打开当前输入文件
        fsheet=fwb['Sheet1']  ## 获取文件的第一个工作表
        base_f = os.path.basename(f)
        i =2

        for row in sheet0['N2:N416']: ## 遍历N2到N416行  
             for cell in row:
                 formula     = f"=VLOOKUP(A{i},[{base_f}]Sheet1!'!$A:$P,16,0)".format(cell.row)
                 cell.value  = formula             
                 print(f"N{i}, {formula}")
                 i += 1
        for row in fsheet.iter_rows(min_row=2, max_row=fsheet.max_row, min_col=14, max_col=14): 
            for cell in row :
                sheet0.cell(row=i,column=17).value = cell.value
                print(cell.value)
                i += 1
                wb0.save(os.path.join(outpath,os.path.basename(f)))

问题分析与修复方案 #

1. 公式语法错误 #

公式里多了一个多余的单引号：[{base_f}]Sheet1!'!$A:$P 应该改成 [{base_f}]Sheet1!$A:$P，多余的单引号会导致Excel无法识别公式结构，直接失效。

2. 文件路径引用问题 #

你只用了文件名base_f，但输入文件和输出模板不在同一个目录（inpath和outpath路径不同），Excel找不到目标文件，公式自然返回空白或错误值。需要在公式里使用完整文件路径，并且把路径里的反斜杠转成Excel支持的格式（双反斜杠或正斜杠）。

修改后的公式代码片段：

# 获取输入文件的完整绝对路径，替换反斜杠为正斜杠
full_file_path = os.path.abspath(f).replace("\\", "/")
# 构造正确的VLOOKUP公式
formula = f"=VLOOKUP(A{i},'{full_file_path}'!$A:$P,16,0)"

3. 模板加载与保存逻辑混乱 #

• 你在循环外加载了一次模板wb0，处理完第一个文件后模板已经被修改，后续文件会基于修改后的模板继续操作，导致数据混乱。必须每次循环都重新加载干净的模板。
• 保存文件的代码放在第二个循环内部，会导致每处理一行就保存一次，效率极低，应该把wb0.save()移到第二个循环外面。

修复后的完整代码 #

def aiakos():
    import openpyxl as op
    import os, glob
    from openpyxl import load_workbook

    outpath = r"C:\Users\pallist\AROTRON_OUT"  # 输出路径
    inpath = r"C:\Users\pallist\AROTRON_IN"    # 输入路径
    template_full_path = os.path.join(outpath, "template.xlsx")  # 模板完整路径
    files = glob.glob(os.path.join(inpath, "*.xlsx"))       # 所有输入文件

    for f in files:  # 遍历每个输入文件
        # 每次循环重新加载干净的模板，避免数据污染
        wb0 = load_workbook(template_full_path)
        sheet0 = wb0['Φύλλο1']  # 模板工作表

        fwb = load_workbook(f)   # 打开当前输入文件
        fsheet = fwb['Sheet1']   # 获取文件的第一个工作表
        full_file_path = os.path.abspath(f).replace("\\", "/")  # 转换为Excel支持的路径格式
        i = 2

        # 填充N2:N416的VLOOKUP公式
        for row in sheet0['N2:N416']:
            for cell in row:
                # 修正公式语法，使用完整路径
                formula = f"=VLOOKUP(A{i},'{full_file_path}'!$A:$P,16,0)"
                cell.value = formula
                print(f"N{i}, {formula}")
                i += 1

        # 填充第17列（Q列）的数据
        for row in fsheet.iter_rows(min_row=2, max_row=fsheet.max_row, min_col=14, max_col=14):
            for cell in row:
                sheet0.cell(row=i, column=17).value = cell.value
                print(cell.value)
                i += 1

        # 移到循环外保存，避免重复保存
        save_target_path = os.path.join(outpath, os.path.basename(f))
        wb0.save(save_target_path)

额外注意事项 #

• 如果输入文件Sheet1的A列没有匹配数据，VLOOKUP会返回#N/A，看起来也像空白，可以给公式加错误处理：=IFERROR(VLOOKUP(...), "")。
• 打开生成的文件时，Excel可能会提示链接更新，需要允许更新才能让公式生效。

内容的提问来源于stack exchange，提问作者Θοδωρής Πάλλης