企业官网建设流程全解析

影刀RPA新手教程：Excel公式与数据验证完全指南——VLOOKUP、条件格式、数据有效性自动化

本文作者：林焱 | 转载请注明出处

开篇案例：用VLOOKUP把两个表合在一起，结果对不上

前几个月帮财务部门做一个RPA，需求是：把"订单表"和"退款表"按订单号关联，找出已退款的订单。

我想当然地在Excel里用了VLOOKUP：

=VLOOKUP(A2,退款表!A:B,2,FALSE)

跑出来结果，有200多个订单显示 #N/A。

我仔细检查，发现订单号在订单表里是文本格式（前面有个不可见字符），在退款表里是数字格式。

VLOOKUP要求两个表的关联字段格式一致，否则匹配不上。

这个问题花了我半天时间排查。

从此以后，凡是涉及Excel表关联的RPA流程，我都会在关联前先统一格式。

本文所有案例，围绕"财务订单与退款数据核对"这条真实业务线展开。

模块一：安装与准备工作

影刀RPA操作Excel，有两种方式。

方式一：用影刀自带的Excel指令

指令面板里搜索"Excel"，可以看到：打开/关闭工作簿、读取/写入单元格、插入行列等。

优点是：不需要安装Excel，影刀自带的处理引擎可以直接读写xlsx文件。

缺点是：不支持某些高级功能（如条件格式、数据验证的详细配置）。

方式二：用Python的openpyxl库

安装：pip install openpyxl

优点：功能完整，支持公式、条件格式、数据验证等所有Excel特性。

缺点：需要安装库，且复杂操作代码量较大。

建议：简单读写用影刀自带指令，涉及公式和数据验证用openpyxl。

环境配置教程在 home.linyan.cloud 有详细图文。

新建流程，命名为"Excel公式与验证Demo"。

模块二：元素定位（从网页导出Excel）

很多场景下，Excel文件是从网页导出的。

先用网页自动化把文件下载到本地，再用Excel指令处理。

检查文件下载完成的可靠方法

importosimporttimedefwait_download_complete(filepath,timeout=60):""" 等待文件下载完成（检查文件大小是否稳定） """last_size=-1foriinrange(timeout):ifnotos.path.exists(filepath):time.sleep(1)continuecurrent_size=os.path.getsize(filepath)ifcurrent_size==last_sizeandcurrent_size>0:returnTruelast_size=current_size time.sleep(1)returnFalse

模块三：变量与数据类型（Excel里的数据类型陷阱）

Excel单元格的数据类型，经常和你看到的不一样。

陷阱一：数字存成了文本

在Excel里，数字左对齐是文本，右对齐是数字。

但用影刀读取时，文本类型的数字会被读成字符串。

订单号：13812345678（文本）-> 读取后是 "13812345678"（字符串） 订单号：13812345678（数字）-> 读取后是 13812345678（整数）

如果两份数据的同一列，一个是文本一个是数字，关联时会匹配失败。

陷阱二：日期格式

Excel里的日期，读取后可能是字符串（“2024-06-01”），也可能是浮点数（45474，这是Excel的日期序列号）。

用Python处理时：

importdatetimedefexcel_date_to_py(excel_date):""" 把Excel日期序列号转成Python日期 """ifisinstance(excel_date,(int,float)):# Excel的日期从1899-12-30开始算base=datetime.datetime(1899,12,30)returnbase+datetime.timedelta(days=int(excel_date))elifisinstance(excel_date,str):returndatetime.datetime.strptime(excel_date,"%Y-%m-%d")else:returnexcel_date

模块四：流程控制（Excel处理的标准流程）

处理Excel的标准流程：

1. 打开工作簿 2. 读取数据（全部或指定范围） 3. 数据清洗（格式统一、去空行） 4. 写入公式或数据验证 5. 保存并关闭

在影刀里，步骤1、2、5用Excel指令，步骤3、4根据复杂度选择用指令还是Python代码。

模块五：网页自动化（结合Excel操作）

从网页导出的Excel，有时候格式不规范（比如合并单元格、多余的空行）。

用影刀的网页自动化重新导出，或者导出后用openpyxl处理合并单元格：

fromopenpyxlimportload_workbookdefunmerge_cells(wb_path,sheet_name=None):""" 取消所有合并单元格，并把值填充到每个单元格 """wb=load_workbook(wb_path)ws=wb[sheet_name]ifsheet_nameelsewb.active# 获取所有合并单元格的范围merged_ranges=list(ws.merged_cells.ranges)formerged_rangeinmerged_ranges:# 获取合并单元格的值（在左上角单元格）top_left=ws.cell(merged_range.min_row,merged_range.min_column)value=top_left.value# 把值填到每个单元格forrowinrange(merged_range.min_row,merged_range.max_row+1):forcolinrange(merged_range.min_column,merged_range.max_column+1):ws.cell(row,col).value=value# 取消合并ws.unmerge_cells(str(merged_range))wb.save(wb_path)print("已取消所有合并单元格")

模块六：数据处理——VLOOKUP公式写入

用openpyxl在Excel里写入VLOOKUP公式：

fromopenpyxlimportload_workbookdefwrite_vlookup(wb_path,sheet_name,output_col,lookup_range,result_col_idx):""" 写入VLOOKUP公式 output_col: 公式要写入的列（如 "D"） lookup_range: 查找范围（如 "退款表!A:B"） result_col_idx: 返回第几列（从1开始） """wb=load_workbook(wb_path)ws=wb[sheet_name]# 假设A列是要查找的值，从第2行开始forrowinrange(2,ws.max_row+1):lookup_value=f"A{row}"formula=f'=VLOOKUP({lookup_value},{lookup_range},{result_col_idx},FALSE)'ws[f"{output_col}{row}"].value=formula wb.save(wb_path)print(f"已写入VLOOKUP公式，共{ws.max_row-1}行")

VLOOKUP的常见错误

模块七：数据处理——条件格式自动化

用openpyxl设置条件格式，比如"金额大于1000的单元格标红"：

fromopenpyxlimportload_workbookfromopenpyxl.formatting.ruleimportCellIsRulefromopenpyxl.stylesimportPatternFilldefapply_conditional_format(wb_path,sheet_name,range_addr):""" 设置条件格式：金额>1000标红，<0标黄 """wb=load_workbook(wb_path)ws=wb[sheet_name]# 定义颜色red_fill=PatternFill(start_color="FFCCCC",end_color="FFCCCC",fill_type="solid")yellow_fill=PatternFill(start_color="FFFFCC",end_color="FFFFCC",fill_type="solid")# 大于1000标红ws.conditional_formatting.add(range_addr,CellIsRule(operator="greaterThan",formula=["1000"],fill=red_fill))# 小于0标黄ws.conditional_formatting.add(range_addr,CellIsRule(operator="lessThan",formula=["0"],fill=yellow_fill))wb.save(wb_path)print("条件格式设置完成")

用影刀指令设置条件格式（简单场景）

在影刀指令面板搜索"设置条件格式"，可以可视化配置，不需要写代码。

但影刀指令的功能有限，复杂的多条件格式还是要用openpyxl。

模块八：数据处理——数据有效性（下拉列表）

数据有效性可以让用户只能输入规定范围内的值，防止脏数据。

用openpyxl设置下拉列表：

fromopenpyxlimportload_workbookfromopenpyxl.worksheet.datavalidationimportDataValidationdefset_dropdown_validation(wb_path,sheet_name,col_letter,values):""" 设置下拉列表数据有效性 col_letter: 要设置的列（如 "C"） values: 下拉选项列表（如 ["待处理", "已处理", "已取消"]） """wb=load_workbook(wb_path)ws=wb[sheet_name]# 创建数据有效性规则dv=DataValidation(type="list",formula1=f'{"{"+",".join(values)+"}"}',# Excel的下拉列表公式格式allow_blank=True)# 应用到整列（从第2行开始，跳过表头）dv.add(f"{col_letter}2:{col_letter}10000")ws.add_data_validation(dv)wb.save(wb_path)print(f"已设置{col_letter}列的下拉列表：{values}")

数据有效性的其他类型

# 限制只能输入数字dv_number=DataValidation(type="whole",operator="between",formula1="1",formula2="99999")# 限制只能输入日期dv_date=DataValidation(type="date",operator="greaterThan",formula1="2024-01-01")# 限制文本长度dv_text=DataValidation(type="textLength",operator="lessThanOrEqual",formula1="50")

模块九：鼠标键盘与图像操作（Excel文件密码破解）

有些Excel文件有密码保护，影刀无法直接打开。

如果是"打开密码"（文件级加密），需要用专门工具解除。

如果是"工作表保护密码"（可以打开，但不能编辑），可以用以下方法解除：

fromopenpyxlimportload_workbookdefremove_sheet_password(wb_path,output_path):""" 移除工作表保护密码（只适用于弱保护，强加密无效） """wb=load_workbook(wb_path)forwsinwb.worksheets:ws.protection.sheet=False# 尝试直接设为Falsewb.save(output_path)print(f"已保存无密码版本到：{output_path}")

如果上面方法不行，说明密码较强，需要用专业的密码恢复工具。

模块十：进阶技能

技能一：用Python做VLOOKUP（比Excel公式快）

当数据量超过5万行时，Excel的VLOOKUP很慢。

用Python的字典做关联，速度提升100倍：

importpandasaspddefvlookup_with_pandas(orders_path,refunds_path,output_path):""" 用Pandas做VLOOKUP """orders=pd.read_excel(orders_path)refunds=pd.read_excel(refunds_path)# 用merge做关联（类似SQL的JOIN）result=pd.merge(orders,refunds,on="order_id",# 关联字段how="left",# 左关联（保留所有订单）suffixes=("","_refund"))result.to_excel(output_path,index=False)print(f"关联完成，结果保存到：{output_path}")

技能二：动态公式（根据数据行数自动扩展）

写入公式时，不要写死行数，用ws.max_row动态获取：

fromopenpyxlimportload_workbookdefwrite_dynamic_formula(wb_path,sheet_name):wb=load_workbook(wb_path)ws=wb[sheet_name]max_row=ws.max_rowprint(f"共{max_row-1}行数据")# 写入求和公式（动态范围）total_cell=ws.cell(max_row+1,4)# 在最后一行后面写总计total_cell.value=f"=SUM(D2:D{max_row})"total_cell.font=total_cell.font.copy(bold=True)wb.save(wb_path)

技能三：保护工作表（防止公式被改）

fromopenpyxlimportload_workbookdefprotect_sheet(wb_path,sheet_name,password):""" 保护工作表，隐藏公式 """wb=load_workbook(wb_path)ws=wb[sheet_name]# 先把要隐藏公式的单元格设为"隐藏"forrowinws.iter_rows():forcellinrow:ifcell.valueandstr(cell.value).startswith("="):cell.protection=cell.protection.copy(hidden=True)# 保护工作表ws.protection.password=password ws.protection.enable()wb.save(wb_path)print("工作表已保护，公式已隐藏")

模块十一：平台实战

把Excel处理流程部署到影刀控制台时，注意以下几点。

要点一：Excel文件用绝对路径

影刀控制台执行流程时，当前目录可能不是流程文件所在目录。

所有Excel文件路径用绝对路径，或者用配置变量管理。

要点二：处理完成的Excel自动发邮件

# 伪代码：用影刀的"发送邮件"指令# 把处理完成的Excel作为附件发送email_content=f""" Excel处理完成 文件：{output_path}处理行数：{row_count}"""# 在影刀里配置邮件服务器，发送附件

要点三：用任务监控查看Excel处理异常

如果Excel公式里有#N/A或#REF!，说明数据有问题。

可以在流程里用Python检查处理结果，把有错误的行写到另一个文件：

fromopenpyxlimportload_workbookdefcheck_excel_errors(wb_path,sheet_name):""" 检查Excel里的错误值 """wb=load_workbook(wb_path)ws=wb[sheet_name]errors=[]forrowinrange(2,ws.max_row+1):forcolinrange(1,ws.max_column+1):cell_value=ws.cell(row,col).valueifisinstance(cell_value,str)andcell_value.startswith("#"):errors.append((row,col,cell_value))returnerrors

模块十二：系统联动与工程化规范

工程化规范一：Excel模板单独管理

不要把数据直接写到新Excel里。

创建一个模板Excel（包含表头、公式、条件格式、数据有效性），每次处理时先复制模板，再往里填数据。

importshutildefprocess_with_template(template_path,output_path,data):""" 用模板处理Excel """# 复制模板shutil.copy(template_path,output_path)# 打开复制后的文件，填数据wb=load_workbook(output_path)ws=wb.activefori,row_datainenumerate(data,start=2):# 从第2行开始（第1行是表头）forj,valueinenumerate(row_data,start=1):ws.cell(i,j).value=value wb.save(output_path)print(f"已用模板生成文件：{output_path}")

工程化规范二：Excel操作日志

每次操作Excel，记录日志：

importloggingfromdatetimeimportdatetimedeflog_excel_operation(operation,filepath,row_count):logger=logging.getLogger("excel_ops")logger.info(f"{datetime.now()}|{operation}|{filepath}|{row_count}行")

速查表：Excel公式与RPA对应

报错排查指南

报错：ValueError: Cannot convert {…} to Excel

原因：尝试把Python的set或复杂对象写入Excel单元格。

解决：把值转成字符串或数字再写入。

报错：公式在Excel里显示的是文本，不是计算结果

原因：用ws.cell().value = "=SUM(A1:A10)"写入公式后，Excel默认不自动计算。

解决：在Excel里按F9刷新，或者在openpyxl里设置wb.calculation = True（部分版本支持）。

总结

Excel公式和数据验证自动化的核心：关联前先统一格式、大量数据用Pandas不用VLOOKUP、重要文件用模板不用从头创建。

把这三个原则记住，Excel自动化就不会再踩坑。

更多Excel高级技巧，访问 home.linyan.cloud 获取完整教程。

#影刀RPA #RPA教程 #Excel自动化 #VLOOKUP #数据验证

作者：林焱