影刀RPA Excel查找函数：VLOOKUP与INDEX-MATCH

影刀RPA Excel查找函数VLOOKUP与INDEX-MATCH作者林焱什么情况用什么两份数据需要关联——比如员工名单和工资表通过工号匹配、产品列表和库存表通过SKU关联。手动在Excel里写VLOOKUP公式容易出错而且每次数据更新都要重写。在影刀RPA里用pandas的merge或Python字典查找不仅更快还能批量处理。适用场景两表关联取值、多条件匹配查找、批量VLOOKUP替代方案、跨文件数据关联。怎么做方式一pandas merge推荐importpandasaspd# 读取两份数据employeespd.read_excel(rC:\Data\employee_list.xlsx)# 工号、姓名、部门salariespd.read_excel(rC:\Data\salary_table.xlsx)# 工号、基本工资、绩效# 按工号关联相当于VLOOKUPresultpd.merge(employees,[video(video-bm65W8Po-1783502061790)(type-csdn)(url-https://live.csdn.net/v/embed/525010)(image-https://v-blog.csdnimg.cn/asset/f4faa587144cb7070f19e8b36813806b/cover/Cover0.jpg)(title-店群矩阵自动化突破运营极限)]salaries,on工号,# 关联键howleft# 左连接保留左表所有行)result.to_excel(rC:\Data\merged.xlsx,indexFalse)方式二Python字典查找速度最快importopenpyxldefvlookup_dict(source_file,target_file,key_col,return_cols,output_file): 用字典实现VLOOKUP key_col: 关联键列名 return_cols: 要返回的列名列表 # 1. 构建查找字典source_dfpd.read_excel(source_file)lookup_dict{}for_,rowinsource_df.iterrows():keystr(row[key_col]).strip()lookup_dict[key]{col:row[col]forcolinreturn_cols}# 2. 在目标表中查找填充target_dfpd.read_excel(target_file)forcolinreturn_cols:target_df[col]# 初始化列foridx,rowintarget_df.iterrows():keystr(row[key_col]).strip()ifkeyinlookup_dict:forcolinreturn_cols:target_df.at[idx,col]lookup_dict[key][col]else:forcolinreturn_cols:target_df.at[idx,col]未找到target_df.to_excel(output_file,indexFalse)returnoutput_file# 使用vlookup_dict(source_filerC:\Data\product_master.xlsx,# 产品主数据target_filerC:\Data\sales_orders.xlsx,# 销售订单key_colSKU,return_cols[产品名称,规格,单位成本],output_filerC:\Data\orders_filled.xlsx)方式三写入Excel公式如果目标文件需要保留公式方便用户在Excel中修改后自动更新importopenpyxl wbopenpyxl.load_workbook(rC:\Data\report.xlsx)wswb.active# 写入VLOOKUP公式forrowinrange(2,ws.max_row1):# 假设A列是工号需要从Sheet2的A:D范围查找对应姓名ws.cell(rowrow,column3).valuefVLOOKUP(A{row},Sheet2!A:D,2,FALSE)# 查找部门第3列ws.cell(rowrow,column4).valuefVLOOKUP(A{row},Sheet2!A:D,3,FALSE)wb.save(rC:\Data\report.xlsx)INDEX-MATCH替代方案VLOOKUP只能从左往右找INDEX-MATCH可以向左查找# 场景知道姓名要查工号姓名在工号右边# VLOOKUP做不到但pandas可以轻松实现defindex_match(df,lookup_value,lookup_col,return_col):INDEX-MATCH的Python实现maskdf[lookup_col]lookup_value matchesdf[mask]iflen(matches)0:returnNoneeliflen(matches)1:returnmatches[return_col].iloc[0]else:# 多个匹配返回列表returnmatches[return_col].tolist()# 使用dfpd.read_excel(rC:\Data\employee.xlsx)# 已知姓名张三查工号工号index_match(df,张三,姓名,工号)多条件查找defmulti_key_vlookup(source_df,target_df,keys,return_cols): 多条件关联查找 keys: 多个关联键的列名列表 # 构建复合键fordfin[source_df,target_df]:df[_merge_key]df[keys].astype(str).agg(|.join,axis1)# 构建查找字典lookup{}for_,rowinsource_df.iterrows():lookup[row[_merge_key]]{col:row[col]forcolinreturn_cols}# 查找填充forcolinreturn_cols:target_df[col]foridx,rowintarget_df.iterrows():keyrow[_merge_key]ifkeyinlookup:forcolinreturn_cols:target_df.at[idx,col]lookup[key][col]target_dftarget_df.drop(columns[_merge_key])returntarget_df# 使用按地区产品两个条件查找价格sourcepd.read_excel(rC:\Data\price_list.xlsx)targetpd.read_excel(rC:\Data\orders.xlsx)resultmulti_key_vlookup(source,target,keys[地区,产品],return_cols[单价,折扣])result.to_excel(rC:\Data\orders_priced.xlsx,indexFalse)有什么坑坑1关联键数据类型不一致一边工号是数字1001另一边是文本1001merge匹配不上# 问题左表工号是int右表工号是strresultpd.merge(df1,df2,on工号)# 匹配结果为空# 解决统一转为字符串df1[工号]df1[工号].astype(str).str.strip()df2[工号]df2[工号].astype(str).str.strip()resultpd.merge(df1,df2,on工号,howleft)# 更保险去除可能的零填充df1[工号]df1[工号].astype(str).str.strip().str.lstrip(0)df2[工号]df2[工号].astype(str).str.strip().str.lstrip(0)坑2merge产生意外重复行右表有重复键时merge会生成笛卡尔积行数暴增temu店群自动化报活动案例# 问题salaries表里一个工号有多条记录resultpd.merge(employees,salaries,on工号,howleft)# 100行变150行# 解决先去重或聚合salaries_uniquesalaries.drop_duplicates(subset工号,keeplast)resultpd.merge(employees,salaries_unique,on工号,howleft)# 或者只取第一条匹配resultpd.merge(employees,salaries,on工号,howleft)resultresult.drop_duplicates(subset工号,keepfirst)坑3VLOOKUP公式写入后显示#N/Aopenpyxl写入公式后文件未在Excel中打开过公式结果不计算读取时显示None或#N/A# 问题openpyxl写公式不会计算结果ws.cell(row2,column3).valueVLOOKUP(A2,Sheet2!A:D,2,FALSE)wb.save(file_path)# 重新读取时公式结果未计算wb2openpyxl.load_workbook(file_path,data_onlyTrue)print(ws2[C2].value)# None因为从未被Excel打开计算过# 解决1用win32com让Excel计算importwin32com.client excelwin32com.client.Dispatch(Excel.Application)wbexcel.Workbooks.Open(file_path)wb.Calculate()wb.Save()wb.Close()# 解决2直接用Python算好值写入不写公式坑4模糊匹配需求工号一样但姓名有细微差异多空格、全半角导致匹配失败# 统一标准化后匹配defnormalize_text(text):文本标准化ifpd.isna(text):returnimportunicodedata textstr(text).strip()textunicodedata.normalize(NFKC,text)# 全角转半角texttext.replace( ,).replace(\u3000,)# 去所有空格returntext# 标准化后匹配df1[姓名_std]df1[姓名].apply(normalize_text)df2[姓名_std]df2[姓名].apply(normalize_text)resultpd.merge(df1,df2,on姓名_std,howleft)坑5大数据量VLOOKUP公式导致Excel卡死在Excel里写几万行VLOOKUP公式文件打开就卡死。Python字典方案速度快几百倍# 对比# Excel VLOOKUP 50000行 → 打开计算需要3-5分钟# Python字典查找 50000行 → 0.5秒完成# 所以在影刀RPA中推荐用Python计算好值再写入Excel# 而不是写VLOOKUP公式