为什么需要Python替代VLOOKUP？

Excel的VLOOKUP函数在处理大型数据集时存在几个主要限制：

• 处理大量数据时速度缓慢
• 只能从左向右查找
• 不支持模糊匹配以外的复杂逻辑
• 公式维护困难

使用Python的openpyxl库，我们可以克服这些限制，实现更灵活、更强大的数据匹配功能。

准备工作：安装openpyxl

在开始之前，确保已安装openpyxl库：

pip install openpyxl

我们将使用以下Excel文件结构作为示例：

目标：将产品名称和价格从产品表匹配到订单表中

实现VLOOKUP功能的完整代码

from openpyxl import load_workbook

def excel_vlookup(lookup_file, data_file, lookup_col, return_col, sheet_name='Sheet1'):
    """
    实现类似Excel的VLOOKUP功能
    
    参数:
    lookup_file: 需要填充数据的Excel文件路径
    data_file: 包含查找数据的Excel文件路径
    lookup_col: 查找值所在的列索引(1-based)
    return_col: 返回值所在的列索引(1-based)
    sheet_name: 工作表名称(默认为'Sheet1')
    """
    # 加载数据源工作簿
    data_wb = load_workbook(data_file)
    data_ws = data_wb.active
    
    # 创建数据字典 {查找键: 返回值}
    data_dict = {}
    for row in data_ws.iter_rows(min_row=2, values_only=True):
        key = row[lookup_col - 1]  # 查找列
        value = row[return_col - 1]  # 返回列
        if key not in data_dict:  # 避免重复键
            data_dict[key] = value
    
    # 加载目标工作簿
    lookup_wb = load_workbook(lookup_file)
    lookup_ws = lookup_wb[sheet_name]
    
    # 执行VLOOKUP
    for row in lookup_ws.iter_rows(min_row=2):  # 从第2行开始
        lookup_value = row[lookup_col - 1].value  # 获取查找值
        
        # 如果找到匹配项，则写入返回值
        if lookup_value in data_dict:
            return_value = data_dict[lookup_value]
            # 计算返回值应该写入的列(查找列右侧)
            return_cell = row[return_col - 1].offset(column=1) 
            return_cell.value = return_value
    
    # 保存结果
    output_file = lookup_file.replace('.xlsx', '_with_vlookup.xlsx')
    lookup_wb.save(output_file)
    print(f"VLOOKUP完成! 结果已保存至: {output_file}")

# 使用示例
excel_vlookup(
    lookup_file='orders.xlsx',   # 需要填充的表
    data_file='products.xlsx',   # 数据源表
    lookup_col=2,                # 产品ID在订单表中是第2列
    return_col=2,                # 在产品表中，产品名称是第2列
    sheet_name='订单'            # 工作表名称
)

代码解析与关键点

data_dict = {}
for row in data_ws.iter_rows(min_row=2, values_only=True):
    key = row[lookup_col - 1]
    value = row[return_col - 1]
    data_dict[key] = value

for row in lookup_ws.iter_rows(min_row=2):
    lookup_value = row[lookup_col - 1].value
    if lookup_value in data_dict:
        return_value = data_dict[lookup_value]
        return_cell = row[return_col - 1].offset(column=1)
        return_cell.value = return_value

进阶技巧

1. 多列匹配

扩展函数以支持同时返回多列数据：

# 修改字典存储整行数据
data_dict = {}
for row in data_ws.iter_rows(min_row=2, values_only=True):
    key = row[lookup_col - 1]
    data_dict[key] = row  # 存储整行

# 匹配时写入多列
if lookup_value in data_dict:
    return_row = data_dict[lookup_value]
    for i, value in enumerate(return_row[return_col_start-1:return_col_end]):
        return_cell = row[return_col - 1].offset(column=i+1)
        return_cell.value = value

2. 近似匹配

实现类似VLOOKUP的近似匹配功能：

import bisect

# 创建排序的键列表
sorted_keys = sorted(data_dict.keys())

# 查找最近的键
def find_closest(key):
    pos = bisect.bisect_left(sorted_keys, key)
    if pos == 0:
        return sorted_keys[0]
    if pos == len(sorted_keys):
        return sorted_keys[-1]
    before = sorted_keys[pos - 1]
    after = sorted_keys[pos]
    if after - key < key - before:
        return after
    else:
        return before

# 使用近似匹配
closest_key = find_closest(lookup_value)
return_value = data_dict[closest_key]

最佳实践建议

• 始终处理可能的空值或异常情况
• 对于大型数据集，考虑使用Pandas提高性能
• 添加日志记录以跟踪处理进度
• 为函数添加类型注解提高可读性
• 使用try-except块处理文件操作异常

通过Python的openpyxl实现VLOOKUP功能，您可以获得比Excel更强大、更灵活的数据处理能力，尤其适合处理大型数据集和自动化任务。

Python数据处理 © 2023