影刀RPA Excel公式自动化批量计算与公式填充署名林焱什么情况用什么做RPA流程时经常遇到这种需求从网页采集了一批商品数据到Excel需要自动计算利润率、加权评分、等级判定。手动在Excel里拖公式当然可以但你要跑100个文件呢影刀RPA提供了两种Excel公式处理方式方式适用场景特点影刀【写入Excel单元格】写公式简单公式、少量单元格可视化操作公式写在表达式框里Python代码块openpyxl批量公式、复杂逻辑代码控制灵活支持公式模板选择原则单个单元格写公式用影刀指令批量填充整列公式用Python代码块。怎么做方式一影刀指令写公式用【写入Excel单元格】指令直接把公式字符串写入单元格操作写入Excel单元格 文件路径D:\报表\销售数据.xlsx Sheet名Sheet1 单元格D2 [video(video-wIKJVphZ-1783500569661)(type-csdn)(url-https://live.csdn.net/v/embed/526818)(image-https://v-blog.csdnimg.cn/asset/582d14c3bd0451c5399cd990b56e2a0d/cover/Cover0.jpg)(title-拼多多店群自动化报活动上架)] 写入内容B2*C2 ← 注意公式以开头影刀会当作公式处理关键设置写入内容框里直接填公式字符串影刀会识别开头并作为公式写入而不是当作普通文本。方式二Python代码块批量填充公式更常见的场景——你有500行数据需要在D列填充B*C公式importopenpyxl wbopenpyxl.load_workbook(rD:\报表\销售数据.xlsx)wswb[Sheet1]# 获取数据行数max_rowws.max_row# 批量填充公式forrowinrange(2,max_row1):ws.cell(rowrow,column4).valuefB{row}*C{row}# 利润率公式E列forrowinrange(2,max_row1):ws.cell(rowrow,column5).valuef(B{row}-C{row})/B{row}ws.cell(rowrow,column5).number_format0.00%# 等级判定公式F列IF嵌套forrowinrange(2,max_row1):ws.cell(rowrow,column6).valuefIF(E{row}0.3,高利润,IF(E{row}0.1,正常,低利润))wb.save(rD:\报表\销售数据.xlsx)常用公式模板速查# 1. VLOOKUP查找匹配ws.cell(rowrow,column4).valuefVLOOKUP(A{row},Sheet2!A:B,2,FALSE)# 2. SUMIF条件求和ws.cell(rowrow,column4).valuefSUMIF(Sheet1!A:A,A{row},Sheet1!C:C)# 3. COUNTIF条件计数ws.cell(rowrow,column4).valuefCOUNTIF(Sheet1!A:A,A{row})# 4. CONCATENATE文本拼接ws.cell(rowrow,column4).valuefCONCATENATE(A{row},-,B{row})# 5. TEXT日期格式化ws.cell(rowrow,column4).valuefTEXT(C{row},yyyy-mm-dd)# 6. IFERROR错误处理ws.cell(rowrow,column4).valuefIFERROR(VLOOKUP(A{row},Sheet2!A:B,2,FALSE),未找到)# 7. ROUND四舍五入ws.cell(rowrow,column4).valuefROUND(B{row}*0.13,2)# 8. SUMPRODUCT加权平均ws.cell(rowrow,column4).valuefSUMPRODUCT(B{row}:D{row},E{row}:G{row})/SUM(E{row}:G{row})完整流程示例销售报表自动计算importopenpyxl# yd_input: file_path, 数据文件路径file_pathyd_input.get(file_path,rD:\报表\销售数据.xlsx)wbopenpyxl.load_workbook(file_path)wswb[销售明细]max_rowws.max_row# 添加表头ws.cell(row1,column4).value销售额ws.cell(row1,column5).value利润率ws.cell(row1,column6).value等级ws.cell(row1,column7).value排名# 批量填充公式forrowinrange(2,max_row1):# 销售额 单价 × 数量ws.cell(rowrow,column4).valuefB{row}*C{row}# 利润率 (销售额 - 成本) / 销售额假设成本在G列ws.cell(rowrow,column5).valuefIFERROR((D{row}-G{row})/D{row},0)ws.cell(rowrow,column5).number_format0.00%# 等级判定ws.cell(rowrow,column6).valuefIF(E{row}0.3,A,IF(E{row}0.15,B,C))# 销售额排名ws.cell(rowrow,column7).valuefRANK(D{row},D$2:D${max_row})# 底部汇总行summary_rowmax_row2ws.cell(rowsummary_row,column3).value合计ws.cell(rowsummary_row,column4).valuefSUM(D2:D{max_row})ws.cell(rowsummary_row,column5).valuefAVERAGE(E2:E{max_row})ws.cell(rowsummary_row,column5).number_format0.00%wb.save(file_path)yd_output{status:ok,rows:max_row-1}有什么坑坑一openpyxl写公式后读不到计算结果现象用openpyxl写入B2*C2后立即读取D2的值拿到的是公式字符串而不是计算结果。原因openpyxl只负责写入公式不执行计算。Excel打开文件后才会计算。解决两种方案# 方案A用影刀【读取Excel单元格】指令读取# 影刀的Excel操作调用了COM接口会触发Excel计算# 方案BPython里直接算好值写入不依赖Excel公式forrowinrange(2,max_row1):pricews.cell(rowrow,column2).valueor0qtyws.cell(rowrow,column3).valueor0ws.cell(rowrow,column4).valueprice*qty# 直接写值坑二公式里的单元格引用行列搞反现象写了公式但结果全错或者Excel报#REF!错误。原因Excel公式中列是字母A、B、C行是数字。B3表示第B列第3行不是第3列第2行。解决用openpyxl.utils.get_column_letter转换fromopenpyxl.utilsimportget_column_letter# 把列号转成字母col_letterget_column_letter(4)# Dws.cell(rowrow,column4).valuef{col_letter}{row}*0.13坑三公式字符串里的引号冲突现象写入IF(E20.3,高利润,低)时Python报语法错误。原因Python字符串用单引号或双引号包裹公式内部也有双引号冲突了。TEMU店群矩阵自动化运营核价报活动解决用f-string 单引号包裹或用转义# 正确写法1外层用单引号ws.cell(rowrow,column6).valuefIF(E{row}0.3,高利润,低)# 正确写法2用三引号ws.cell(rowrow,column6).valuefIF(E{row}0.3,高利润,低)# 错误写法外层双引号内部也双引号 → 语法错误# ws.cell(rowrow, column6).value fIF(E{row}0.3,高利润,低) ❌坑四公式拖拽填充的绝对引用和相对引用现象批量写公式后某些行引用了错误的单元格。原因公式从D2复制到D3时B2*C2会自动变成B3*C3相对引用这通常是对的。但如果公式引用了一个固定单元格就需要用$锁定绝对引用。解决# 相对引用行号随行变化ws.cell(rowrow,column4).valuefB{row}*C{row}# D2B2*C2, D3B3*C3# 绝对引用用$锁定ws.cell(rowrow,column4).valuefB{row}*$C$1# D2B2*C1, D3B3*C1C1始终不变# 混合引用锁列不锁行ws.cell(rowrow,column4).valuef$B{row}*C{row}# B列锁定行号变化坑五公式以符号开头导致旧版Excel不兼容现象用openpyxl写公式后某些Excel版本打开报错或公式不计算。原因openpyxl新版本默认在公式前加符号隐式交集运算符旧版Excel不认识。解决确保公式字符串以开头不要有# 正确ws.cell(row1,column1).valueSUM(B1:B10)# 检查是否有意外formulafSUM(B{row}:B{row10})ifformula.startswith():formulaformula[1:]ws.cell(rowrow,column1).valueformula
影刀RPA Excel公式自动化：批量计算与公式填充
影刀RPA Excel公式自动化批量计算与公式填充署名林焱什么情况用什么做RPA流程时经常遇到这种需求从网页采集了一批商品数据到Excel需要自动计算利润率、加权评分、等级判定。手动在Excel里拖公式当然可以但你要跑100个文件呢影刀RPA提供了两种Excel公式处理方式方式适用场景特点影刀【写入Excel单元格】写公式简单公式、少量单元格可视化操作公式写在表达式框里Python代码块openpyxl批量公式、复杂逻辑代码控制灵活支持公式模板选择原则单个单元格写公式用影刀指令批量填充整列公式用Python代码块。怎么做方式一影刀指令写公式用【写入Excel单元格】指令直接把公式字符串写入单元格操作写入Excel单元格 文件路径D:\报表\销售数据.xlsx Sheet名Sheet1 单元格D2 [video(video-wIKJVphZ-1783500569661)(type-csdn)(url-https://live.csdn.net/v/embed/526818)(image-https://v-blog.csdnimg.cn/asset/582d14c3bd0451c5399cd990b56e2a0d/cover/Cover0.jpg)(title-拼多多店群自动化报活动上架)] 写入内容B2*C2 ← 注意公式以开头影刀会当作公式处理关键设置写入内容框里直接填公式字符串影刀会识别开头并作为公式写入而不是当作普通文本。方式二Python代码块批量填充公式更常见的场景——你有500行数据需要在D列填充B*C公式importopenpyxl wbopenpyxl.load_workbook(rD:\报表\销售数据.xlsx)wswb[Sheet1]# 获取数据行数max_rowws.max_row# 批量填充公式forrowinrange(2,max_row1):ws.cell(rowrow,column4).valuefB{row}*C{row}# 利润率公式E列forrowinrange(2,max_row1):ws.cell(rowrow,column5).valuef(B{row}-C{row})/B{row}ws.cell(rowrow,column5).number_format0.00%# 等级判定公式F列IF嵌套forrowinrange(2,max_row1):ws.cell(rowrow,column6).valuefIF(E{row}0.3,高利润,IF(E{row}0.1,正常,低利润))wb.save(rD:\报表\销售数据.xlsx)常用公式模板速查# 1. VLOOKUP查找匹配ws.cell(rowrow,column4).valuefVLOOKUP(A{row},Sheet2!A:B,2,FALSE)# 2. SUMIF条件求和ws.cell(rowrow,column4).valuefSUMIF(Sheet1!A:A,A{row},Sheet1!C:C)# 3. COUNTIF条件计数ws.cell(rowrow,column4).valuefCOUNTIF(Sheet1!A:A,A{row})# 4. CONCATENATE文本拼接ws.cell(rowrow,column4).valuefCONCATENATE(A{row},-,B{row})# 5. TEXT日期格式化ws.cell(rowrow,column4).valuefTEXT(C{row},yyyy-mm-dd)# 6. IFERROR错误处理ws.cell(rowrow,column4).valuefIFERROR(VLOOKUP(A{row},Sheet2!A:B,2,FALSE),未找到)# 7. ROUND四舍五入ws.cell(rowrow,column4).valuefROUND(B{row}*0.13,2)# 8. SUMPRODUCT加权平均ws.cell(rowrow,column4).valuefSUMPRODUCT(B{row}:D{row},E{row}:G{row})/SUM(E{row}:G{row})完整流程示例销售报表自动计算importopenpyxl# yd_input: file_path, 数据文件路径file_pathyd_input.get(file_path,rD:\报表\销售数据.xlsx)wbopenpyxl.load_workbook(file_path)wswb[销售明细]max_rowws.max_row# 添加表头ws.cell(row1,column4).value销售额ws.cell(row1,column5).value利润率ws.cell(row1,column6).value等级ws.cell(row1,column7).value排名# 批量填充公式forrowinrange(2,max_row1):# 销售额 单价 × 数量ws.cell(rowrow,column4).valuefB{row}*C{row}# 利润率 (销售额 - 成本) / 销售额假设成本在G列ws.cell(rowrow,column5).valuefIFERROR((D{row}-G{row})/D{row},0)ws.cell(rowrow,column5).number_format0.00%# 等级判定ws.cell(rowrow,column6).valuefIF(E{row}0.3,A,IF(E{row}0.15,B,C))# 销售额排名ws.cell(rowrow,column7).valuefRANK(D{row},D$2:D${max_row})# 底部汇总行summary_rowmax_row2ws.cell(rowsummary_row,column3).value合计ws.cell(rowsummary_row,column4).valuefSUM(D2:D{max_row})ws.cell(rowsummary_row,column5).valuefAVERAGE(E2:E{max_row})ws.cell(rowsummary_row,column5).number_format0.00%wb.save(file_path)yd_output{status:ok,rows:max_row-1}有什么坑坑一openpyxl写公式后读不到计算结果现象用openpyxl写入B2*C2后立即读取D2的值拿到的是公式字符串而不是计算结果。原因openpyxl只负责写入公式不执行计算。Excel打开文件后才会计算。解决两种方案# 方案A用影刀【读取Excel单元格】指令读取# 影刀的Excel操作调用了COM接口会触发Excel计算# 方案BPython里直接算好值写入不依赖Excel公式forrowinrange(2,max_row1):pricews.cell(rowrow,column2).valueor0qtyws.cell(rowrow,column3).valueor0ws.cell(rowrow,column4).valueprice*qty# 直接写值坑二公式里的单元格引用行列搞反现象写了公式但结果全错或者Excel报#REF!错误。原因Excel公式中列是字母A、B、C行是数字。B3表示第B列第3行不是第3列第2行。解决用openpyxl.utils.get_column_letter转换fromopenpyxl.utilsimportget_column_letter# 把列号转成字母col_letterget_column_letter(4)# Dws.cell(rowrow,column4).valuef{col_letter}{row}*0.13坑三公式字符串里的引号冲突现象写入IF(E20.3,高利润,低)时Python报语法错误。原因Python字符串用单引号或双引号包裹公式内部也有双引号冲突了。TEMU店群矩阵自动化运营核价报活动解决用f-string 单引号包裹或用转义# 正确写法1外层用单引号ws.cell(rowrow,column6).valuefIF(E{row}0.3,高利润,低)# 正确写法2用三引号ws.cell(rowrow,column6).valuefIF(E{row}0.3,高利润,低)# 错误写法外层双引号内部也双引号 → 语法错误# ws.cell(rowrow, column6).value fIF(E{row}0.3,高利润,低) ❌坑四公式拖拽填充的绝对引用和相对引用现象批量写公式后某些行引用了错误的单元格。原因公式从D2复制到D3时B2*C2会自动变成B3*C3相对引用这通常是对的。但如果公式引用了一个固定单元格就需要用$锁定绝对引用。解决# 相对引用行号随行变化ws.cell(rowrow,column4).valuefB{row}*C{row}# D2B2*C2, D3B3*C3# 绝对引用用$锁定ws.cell(rowrow,column4).valuefB{row}*$C$1# D2B2*C1, D3B3*C1C1始终不变# 混合引用锁列不锁行ws.cell(rowrow,column4).valuef$B{row}*C{row}# B列锁定行号变化坑五公式以符号开头导致旧版Excel不兼容现象用openpyxl写公式后某些Excel版本打开报错或公式不计算。原因openpyxl新版本默认在公式前加符号隐式交集运算符旧版Excel不认识。解决确保公式字符串以开头不要有# 正确ws.cell(row1,column1).valueSUM(B1:B10)# 检查是否有意外formulafSUM(B{row}:B{row10})ifformula.startswith():formulaformula[1:]ws.cell(rowrow,column1).valueformula
