影刀RPA Excel条件求和：SUMIF与COUNTIF
影刀RPA Excel条件求和SUMIF与COUNTIF作者林焱什么情况用什么需要统计某个地区的销售总额、计算达成目标的订单数、按部门统计平均工资——这些条件统计在Excel里用SUMIF、COUNTIF、SUMIFS、COUNTIFS。但在影刀RPA处理大批量数据时用pandas的分组聚合或条件筛选更快更灵活还能写复杂条件。适用场景按条件汇总金额、统计满足条件的记录数、多条件交叉统计、分类排名计算。怎么做COUNTIF条件计数importpandasaspd dfpd.read_excel(rC:\Data\sales.xlsx)# COUNTIF统计销售额大于10000的订单数countlen(df[df[金额]10000])# 统计某地区的订单数count_bjlen(df[df[地区]北京])[video(video-kX15gV2g-1783502000801)(type-csdn)(url-https://live.csdn.net/v/embed/526818)(image-https://v-blog.csdnimg.cn/asset/582d14c3bd0451c5399cd990b56e2a0d/cover/Cover0.jpg)(title-拼多多店群自动化报活动上架)]# 统计包含某关键词的记录数count_phonedf[产品名].str.contains(手机,naFalse).sum()# 多条件计数COUNTIFScount_multilen(df[(df[地区]北京)(df[金额]10000)(df[状态]已完成)])SUMIF条件求和# SUMIF统计北京地区销售总额sum_bjdf[df[地区]北京][金额].sum()# SUMIFS多条件求和sum_multidf[(df[地区]北京)(df[状态]已完成)(df[金额]1000)][金额].sum()# 按地区分组求和相当于所有地区的SUMIFregion_sumdf.groupby(地区)[金额].sum().reset_index()region_sum.columns[地区,总金额]AVERAGEIF条件平均# AVERAGEIF北京地区平均订单金额avg_bjdf[df[地区]北京][金额].mean()# 多条件平均avg_multidf[(df[地区]北京)(df[状态]已完成)][金额].mean()生成条件统计报表defgenerate_summary_report(df,output_path):生成多维度条件统计报表withpd.ExcelWriter(output_path,engineopenpyxl)aswriter:# 1. 按地区统计region_statsdf.groupby(地区).agg(订单数(订单ID,count),总金额(金额,sum),平均金额(金额,mean),最大金额(金额,max),最小金额(金额,min)).round(2).reset_index()region_stats.to_excel(writer,sheet_name按地区统计,indexFalse)# 2. 按状态统计status_statsdf.groupby(状态).agg(订单数(订单ID,count),总金额(金额,sum)).reset_index()status_stats.to_excel(writer,sheet_name按状态统计,indexFalse)# 3. 按地区×状态交叉统计crosspd.pivot_table(df,values金额,index地区,columns状态,aggfuncsum,fill_value0,marginsTrue,margins_name合计)cross.to_excel(writer,sheet_name交叉统计)# 4. 条件筛选统计conditions{大额订单(1万):df[df[金额]10000],小额订单(1千):df[df[金额]1000],北京已完成:df[(df[地区]北京)(df[状态]已完成)],上海待处理:df[(df[地区]上海)(df[状态]待处理)],}summary_data[]forname,subsetinconditions.items():summary_data.append({统计项:name,订单数:len(subset),总金额:subset[金额].sum(),平均金额:round(subset[金额].mean(),2)iflen(subset)0else0})condition_dfpd.DataFrame(summary_data)condition_df.to_excel(writer,sheet_name条件统计,indexFalse)returnoutput_path# 使用dfpd.read_excel(rC:\Data\sales.xlsx)generate_summary_report(df,rC:\Data\sales_summary.xlsx)动态条件统计defdynamic_summary(df,group_col,value_col,conditionsNone): 动态条件统计 conditions: dict of {条件名: 筛选条件函数} # 基础分组统计resultdf.groupby(group_col).agg(计数(value_col,count),求和(value_col,sum),平均(value_col,mean),).round(2).reset_index()# 添加条件统计列ifconditions:forcond_name,cond_funcinconditions.items():cond_resultdf[cond_func(df)].groupby(group_col)[value_col].sum()result[f{cond_name}_金额]result[group_col].map(cond_result).fillna(0)returnresult# 使用dfpd.read_excel(rC:\Data\sales.xlsx)summarydynamic_summary(df,group_col地区,value_col金额,conditions{大额:lambdad:d[金额]10000,已完成:lambdad:d[状态]已完成,})写入Excel公式版本importopenpyxldefwrite_sumif_formulas(file_path):在Excel中写入SUMIF/COUNTIF公式wbopenpyxl.load_workbook(file_path)ws_datawb[销售数据]ws_summarywb[统计]# 假设统计表中A列是地区名称从第2行开始forrowinrange(2,ws_summary.max_row1):regionws_summary.cell(rowrow,column1).valueifnotregion:continue# COUNTIF统计该地区订单数ws_summary.cell(rowrow,column2).value\fCOUNTIF(销售数据!B:B,A{row})# SUMIF统计该地区总金额ws_summary.cell(rowrow,column3).value\fSUMIF(销售数据!B:B,A{row},销售数据!D:D)# SUMIFS该地区已完成订单总金额ws_summary.cell(rowrow,column4).value\fSUMIFS(销售数据!D:D,销售数据!B:B,A{row},销售数据!E:E,已完成)wb.save(file_path)有什么坑坑1条件筛选时空值导致报错金额列有空值直接比较会报错或结果不对# 问题空值比较df[df[金额]1000]# NaN参与比较结果为False不会报错但可能遗漏# 更安全先处理空值df[金额]pd.to_numeric(df[金额],errorscoerce).fillna(0)resultdf[df[金额]1000]# 或者排除空值resultdf[df[金额].notna()(df[金额]1000)]坑2groupby结果缺少某些分组某些地区没有数据groupby后结果里缺少该地区行TEMU店群矩阵自动化运营核价报活动# 问题只有北京和上海有数据广州没有resultdf.groupby(地区)[金额].sum()# 结果只有北京和上海# 解决用reindex补全all_regions[北京,上海,广州,深圳]resultdf.groupby(地区)[金额].sum().reindex(all_regions,fill_value0)坑3SUMIF公式范围不匹配写入Excel公式时条件范围和求和范围行数不一致# 错误条件范围是B列求和范围是D2:D100ws.cell(rowrow,column3).valueSUMIF(B:B,A2,D2:D100)# 范围不一致# 正确两个范围要一致ws.cell(rowrow,column3).valueSUMIF(B:B,A2,D:D)# 都是整列# 或ws.cell(rowrow,column3).valueSUMIF(B2:B1000,A2,D2:D1000)# 同范围坑4浮点数精度问题条件求和后金额出现0.00000001的偏差# 问题浮点数精度totaldf[df[地区]北京][金额].sum()# 可能是100000.00000000001# 解决用roundtotalround(df[df[地区]北京][金额].sum(),2)# 或用Decimal处理金额fromdecimalimportDecimal df[金额]df[金额].apply(lambdax:Decimal(str(x)))totaldf[df[地区]北京][金额].sum()totalfloat(total)坑5COUNTIF统计中文条件统计包含中文字符的记录时编码或全半角不一致导致漏统计# 问题全角和半角括号不一致df[产品名].str.contains(手机智能,naFalse)# 只匹配全角括号# 解决统一全半角后再匹配importunicodedata df[产品名]df[产品名].astype(str).apply(lambdax:unicodedata.normalize(NFKC,x))countdf[产品名].str.contains(手机(智能),naFalse).sum()
