使用Pandas读取Excel时VLOOKUP单元格显示为NaN的解决办法咨询

出现这个问题的核心原因是：原始Excel文件中仅存储了VLOOKUP公式本身，未保存公式计算后的结果，而Pandas的pd.read_excel()默认读取的是单元格的存储值，而非实时计算公式结果。LibreOffice打开保存的过程会触发公式计算并将结果写入单元格，因此后续能正常读取。以下是几种自动化解决方案：

方案1：用openpyxl触发公式计算后读取 #

openpyxl支持触发Excel公式计算，适合无Office环境（如AWS Lambda）：

1. 确保已安装openpyxl：pip install openpyxl
2. 代码实现：

from openpyxl import load_workbook
import pandas as pd
import tempfile
import os

# 加载Excel文件，保留公式（data_only=False）
wb = load_workbook(filename="target_file.xlsx", data_only=False)

# 遍历所有工作表触发计算
for sheet_name in wb.sheetnames:
    ws = wb[sheet_name]
    ws.calculate_dimension()  # 触发全表公式计算

# 保存到临时文件
with tempfile.NamedTemporaryFile(suffix=".xlsx", delete=False) as tmp_file:
    temp_path = tmp_file.name
wb.save(temp_path)
wb.close()

# 用Pandas读取计算后的文件
df = pd.read_excel(temp_path)

# 清理临时文件
os.unlink(temp_path)

⚠️ 注意：openpyxl的公式计算能力有限，若VLOOKUP引用外部文件或复杂嵌套公式，可能无法正确计算。

方案2：在生成Excel的源头预先计算结果 #

如果能控制生成该Excel文件的服务器流程，这是最彻底的解决方式：在生成文件时直接触发公式计算并保存结果，而非仅写入公式。比如用openpyxl生成时：

from openpyxl import Workbook

wb = Workbook()
ws = wb.active

# 写入VLOOKUP公式
ws["B2"] = '=VLOOKUP(A2, Sheet2!$A:$B, 2, FALSE)'

# 触发计算并保存结果
ws.calculate_dimension()
wb.save("calculated_result.xlsx")

后续用Pandas读取该文件时，直接就能拿到计算后的数值，无需额外处理。

方案3：用pyexcelerate处理公式计算 #

pyexcelerate的公式支持度优于openpyxl，适合处理复杂公式场景：

1. 安装依赖：pip install pyexcelerate
2. 代码实现：

import pyexcelerate
import pandas as pd
import tempfile
import os

# 加载目标Excel文件
wb = pyexcelerate.Workbook()
wb.load("target_file.xlsx")

# 计算所有工作表的公式
wb.calculate()

# 保存到临时文件
with tempfile.NamedTemporaryFile(suffix=".xlsx", delete=False) as tmp_file:
    temp_path = tmp_file.name
wb.save(temp_path)

# 读取数据
df = pd.read_excel(temp_path)
os.unlink(temp_path)

方案4：xlwings（仅本地/有Office环境可用） #

如果是本地环境或部署了Windows Office的服务器，可以用xlwings调用Office的计算引擎，兼容性最好，但AWS Lambda环境无法使用：

1. 安装依赖：pip install xlwings
2. 代码实现：

import xlwings as xw
import pandas as pd

# 后台打开Excel，不显示界面
app = xw.App(visible=False)
wb = app.books.open("target_file.xlsx")

# 刷新所有公式
wb.app.calculate()

# 保存计算后的文件
wb.save("temp_calculated.xlsx")
wb.close()
app.quit()

# 读取数据
df = pd.read_excel("temp_calculated.xlsx")

内容的提问来源于stack exchange，提问作者Márcio Marchiori