Excel 2007跨多工作表多行条件统计及公式故障求助
需求背景
现有Excel 2007工作簿，包含12个以月份命名的工作表（January至December），以及1个「Trade Statistics」统计工作表。需将以下8个仅针对January工作表的条件统计公式，修改为可跨所有12个月份工作表生效的版本：
原单工作表公式：
- 正数平均值：
=AVERAGEIF(January!$M$5:$M$100,">0",January!$M$5:$M$100) - 负数平均值：
=AVERAGEIF(January!$M$5:$M$100,"<0",January!$M$5:$M$100) - 正数求和：
=SUM(IF(January!$N$5:$N$100>0,January!$N$5:$N$100)) - 负数求和：
=SUM(IF(January!$N$5:$N$100<0,January!$N$5:$N$100)) - 正数占比：
=COUNTIF(January!$M$5:$M$100,">0")/COUNT(January!$M$5:$M$100) - 负数占比：
=COUNTIF(January!$M$5:$M$100,"<0")/COUNT(January!$M$5:$M$100) - 正数最小值：
=MIN(IF(January!$M$5:$M$100>0,January!$M$5:$M$100)) - 负数最大值：
=MAX(IF(January!$M$5:$M$100<0,January!$M$5:$M$100))
遇到的问题
- 部分月份工作表的M5:M100或N5:N100区域无数据，使用
=AVERAGE(AVERAGEIF(INDIRECT(...)))这类公式统计时，无数据的工作表会返回#DIV/0!错误，导致整体公式失效。需要在保留>0/<0统计条件的同时，忽略空白单元格及错误值。 - 使用含IF的数组公式时，在February至December工作表的G/H列输入数据会导致Excel崩溃。推测是因为M列公式
=IFERROR((H5-G5)/ABS(G5),"")、N列公式=IF(AND(H5="",G5="",J5=""),"",(H5-G5)*(J5))与跨表统计公式存在冲突，需要替换方案来隐藏#DIV/0!错误，同时隐藏货币格式单元格的0值。
解决方案
针对问题1：处理无数据工作表的错误值
使用SUMPRODUCT结合ISNUMBER、IFERROR规避错误，同时汇总符合条件的数据：
跨表正数平均值：
=IFERROR(SUMPRODUCT(SUMIF(INDIRECT("'"&{"January","February","March","April","May","June","July","August","September","October","November","December"}&"'!$M$5:$M$100"),">0"))/SUMPRODUCT(COUNTIF(INDIRECT("'"&{"January","February","March","April","May","June","July","August","September","October","November","December"}&"'!$M$5:$M$100"),">0")),"")
先汇总所有工作表中M列正数的总和，再除以正数总个数，外层IFERROR处理无数据时的空值。跨表负数平均值：
=IFERROR(SUMPRODUCT(SUMIF(INDIRECT("'"&{"January","February","March","April","May","June","July","August","September","October","November","December"}&"'!$M$5:$M$100"),"<0"))/SUMPRODUCT(COUNTIF(INDIRECT("'"&{"January","February","March","April","May","June","July","August","September","October","November","December"}&"'!$M$5:$M$100"),"<0")),"")跨表正数求和：
=SUMPRODUCT(SUMIF(INDIRECT("'"&{"January","February","March","April","May","June","July","August","September","October","November","December"}&"'!$N$5:$N$100"),">0"))跨表负数求和：
=SUMPRODUCT(SUMIF(INDIRECT("'"&{"January","February","March","April","May","June","July","August","September","October","November","December"}&"'!$N$5:$N$100"),"<0"))跨表正数占比：
=IFERROR(SUMPRODUCT(COUNTIF(INDIRECT("'"&{"January","February","March","April","May","June","July","August","September","October","November","December"}&"'!$M$5:$M$100"),">0"))/SUMPRODUCT(COUNTA(INDIRECT("'"&{"January","February","March","April","May","June","July","August","September","October","November","December"}&"'!$M$5:$M$100"))),"")
用COUNTA统计非空单元格总数，替代原公式的COUNT，避免空白单元格干扰。跨表负数占比：
=IFERROR(SUMPRODUCT(COUNTIF(INDIRECT("'"&{"January","February","March","April","May","June","July","August","September","October","November","December"}&"'!$M$5:$M$100"),"<0"))/SUMPRODUCT(COUNTA(INDIRECT("'"&{"January","February","March","April","May","June","July","August","September","October","November","December"}&"'!$M$5:$M$100"))),"")跨表正数最小值：
=MIN(IFERROR(SUBTOTAL(5,INDIRECT("'"&{"January","February","March","April","May","June","July","August","September","October","November","December"}&"'!$M$5:$M$100")),""))SUBTOTAL(5)对应MIN函数，结合IFERROR忽略错误值，最后取所有工作表的最小值。跨表负数最大值：
=MAX(IFERROR(SUBTOTAL(4,INDIRECT("'"&{"January","February","March","April","May","June","July","August","September","October","November","December"}&"'!$M$5:$M$100")),""))SUBTOTAL(4)对应MAX函数，结合IFERROR忽略错误值，最后取所有工作表的负数最大值。
针对问题2：解决公式冲突与Excel崩溃
替换M列公式：
将原M列公式修改为：=IF(OR(G5=0,G5=""),"",(H5-G5)/ABS(G5))
提前判断G5是否为0或空白，避免触发除法错误，减少IFERROR的计算开销。替换N列公式：
将原N列公式修改为：=IF(OR(G5="",H5="",J5=""),"",(H5-G5)*J5)
简化判断逻辑，避免不必要的空值计算。隐藏货币格式单元格的0值：
选中目标单元格区域，右键选择「设置单元格格式」→「数字」→「货币」，在「负数」选项下方勾选「隐藏零值」，无需公式处理，减少计算负载。
内容的提问来源于stack exchange，提问作者J Fav
