影刀RPA新手教程Excel公式与数据验证完全指南——VLOOKUP、条件格式、数据有效性自动化本文作者林焱 | 转载请注明出处开篇案例用VLOOKUP把两个表合在一起结果对不上前几个月帮财务部门做一个RPA需求是把订单表和退款表按订单号关联找出已退款的订单。我想当然地在Excel里用了VLOOKUPVLOOKUP(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,timeout60): 等待文件下载完成检查文件大小是否稳定 last_size-1foriinrange(timeout):ifnotos.path.exists(filepath):time.sleep(1)continuecurrent_sizeos.path.getsize(filepath)ifcurrent_sizelast_sizeandcurrent_size0:returnTruelast_sizecurrent_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开始算basedatetime.datetime(1899,12,30)returnbasedatetime.timedelta(daysint(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_nameNone): 取消所有合并单元格并把值填充到每个单元格 wbload_workbook(wb_path)wswb[sheet_name]ifsheet_nameelsewb.active# 获取所有合并单元格的范围merged_rangeslist(ws.merged_cells.ranges)formerged_rangeinmerged_ranges:# 获取合并单元格的值在左上角单元格top_leftws.cell(merged_range.min_row,merged_range.min_column)valuetop_left.value# 把值填到每个单元格forrowinrange(merged_range.min_row,merged_range.max_row1):forcolinrange(merged_range.min_column,merged_range.max_column1):ws.cell(row,col).valuevalue# 取消合并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开始 wbload_workbook(wb_path)wswb[sheet_name]# 假设A列是要查找的值从第2行开始forrowinrange(2,ws.max_row1):lookup_valuefA{row}formulafVLOOKUP({lookup_value},{lookup_range},{result_col_idx},FALSE)ws[f{output_col}{row}].valueformula wb.save(wb_path)print(f已写入VLOOKUP公式共{ws.max_row-1}行)VLOOKUP的常见错误错误原因解决方案#N/A找不到匹配值检查格式是否一致是否有多余空格#REF!引用范围不存在检查sheet名和范围是否正确#VALUE!参数类型错误检查result_col_idx是否是数字匹配到错误的值没加FALSE参数VLOOKUP第4个参数必须是FALSE精确匹配模块七数据处理——条件格式自动化用openpyxl设置条件格式比如金额大于1000的单元格标红fromopenpyxlimportload_workbookfromopenpyxl.formatting.ruleimportCellIsRulefromopenpyxl.stylesimportPatternFilldefapply_conditional_format(wb_path,sheet_name,range_addr): 设置条件格式金额1000标红0标黄 wbload_workbook(wb_path)wswb[sheet_name]# 定义颜色red_fillPatternFill(start_colorFFCCCC,end_colorFFCCCC,fill_typesolid)yellow_fillPatternFill(start_colorFFFFCC,end_colorFFFFCC,fill_typesolid)# 大于1000标红ws.conditional_formatting.add(range_addr,CellIsRule(operatorgreaterThan,formula[1000],fillred_fill))# 小于0标黄ws.conditional_formatting.add(range_addr,CellIsRule(operatorlessThan,formula[0],fillyellow_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: 下拉选项列表如 [待处理, 已处理, 已取消] wbload_workbook(wb_path)wswb[sheet_name]# 创建数据有效性规则dvDataValidation(typelist,formula1f{{,.join(values)}},# Excel的下拉列表公式格式allow_blankTrue)# 应用到整列从第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_numberDataValidation(typewhole,operatorbetween,formula11,formula299999)# 限制只能输入日期dv_dateDataValidation(typedate,operatorgreaterThan,formula12024-01-01)# 限制文本长度dv_textDataValidation(typetextLength,operatorlessThanOrEqual,formula150)模块九鼠标键盘与图像操作Excel文件密码破解有些Excel文件有密码保护影刀无法直接打开。如果是打开密码文件级加密需要用专门工具解除。如果是工作表保护密码可以打开但不能编辑可以用以下方法解除fromopenpyxlimportload_workbookdefremove_sheet_password(wb_path,output_path): 移除工作表保护密码只适用于弱保护强加密无效 wbload_workbook(wb_path)forwsinwb.worksheets:ws.protection.sheetFalse# 尝试直接设为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 orderspd.read_excel(orders_path)refundspd.read_excel(refunds_path)# 用merge做关联类似SQL的JOINresultpd.merge(orders,refunds,onorder_id,# 关联字段howleft,# 左关联保留所有订单suffixes(,_refund))result.to_excel(output_path,indexFalse)print(f关联完成结果保存到{output_path})技能二动态公式根据数据行数自动扩展写入公式时不要写死行数用ws.max_row动态获取fromopenpyxlimportload_workbookdefwrite_dynamic_formula(wb_path,sheet_name):wbload_workbook(wb_path)wswb[sheet_name]max_rowws.max_rowprint(f共{max_row-1}行数据)# 写入求和公式动态范围total_cellws.cell(max_row1,4)# 在最后一行后面写总计total_cell.valuefSUM(D2:D{max_row})total_cell.fonttotal_cell.font.copy(boldTrue)wb.save(wb_path)技能三保护工作表防止公式被改fromopenpyxlimportload_workbookdefprotect_sheet(wb_path,sheet_name,password): 保护工作表隐藏公式 wbload_workbook(wb_path)wswb[sheet_name]# 先把要隐藏公式的单元格设为隐藏forrowinws.iter_rows():forcellinrow:ifcell.valueandstr(cell.value).startswith():cell.protectioncell.protection.copy(hiddenTrue)# 保护工作表ws.protection.passwordpassword ws.protection.enable()wb.save(wb_path)print(工作表已保护公式已隐藏)模块十一平台实战把Excel处理流程部署到影刀控制台时注意以下几点。要点一Excel文件用绝对路径影刀控制台执行流程时当前目录可能不是流程文件所在目录。所有Excel文件路径用绝对路径或者用配置变量管理。TEMU店群如何管理运营要点二处理完成的Excel自动发邮件# 伪代码用影刀的发送邮件指令# 把处理完成的Excel作为附件发送email_contentf Excel处理完成 文件{output_path}处理行数{row_count}# 在影刀里配置邮件服务器发送附件要点三用任务监控查看Excel处理异常如果Excel公式里有#N/A或#REF!说明数据有问题。可以在流程里用Python检查处理结果把有错误的行写到另一个文件fromopenpyxlimportload_workbookdefcheck_excel_errors(wb_path,sheet_name): 检查Excel里的错误值 wbload_workbook(wb_path)wswb[sheet_name]errors[]forrowinrange(2,ws.max_row1):forcolinrange(1,ws.max_column1):cell_valuews.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)# 打开复制后的文件填数据wbload_workbook(output_path)wswb.activefori,row_datainenumerate(data,start2):# 从第2行开始第1行是表头forj,valueinenumerate(row_data,start1):ws.cell(i,j).valuevalue wb.save(output_path)print(f已用模板生成文件{output_path})工程化规范二Excel操作日志每次操作Excel记录日志importloggingfromdatetimeimportdatetimedeflog_excel_operation(operation,filepath,row_count):loggerlogging.getLogger(excel_ops)logger.info(f{datetime.now()}|{operation}|{filepath}|{row_count}行)速查表Excel公式与RPA对应Excel操作影刀指令Python库打开工作簿打开Excel工作簿openpyxl.load_workbook读取单元格读取Excel单元格ws.cell(row, col).value写入单元格写入Excel单元格ws.cell(row, col).value xVLOOKUP写入公式ws.cell().value “VLOOKUP(…)”条件格式设置条件格式ws.conditional_formatting.add数据验证用PythonDataValidation报错排查指南报错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 #数据验证作者林焱
