如何查找与均值偏差最大的数值并在电子表格中实现均值重算

电子表格自动QC计算方案（兼容Excel、WPS表格、Google Sheets） #

假设你的4个原始数值存储在A1:A4单元格区域，可直接套用以下公式实现全流程自动计算：

核心逻辑拆解 #

• 先计算4个数值的原始均值
• 计算每个数值与原始均值的绝对偏差，找到偏差最大的数值
• 判断最大偏差是否大于1，符合条件则剔除该异常值后重新计算剩余3个值的均值，否则直接返回原始均值

通用完整公式 #

如果你的表格工具支持LET函数（新版Excel、WPS、Google Sheets均支持），推荐用可读性更高的版本，直接回车即可运行：

=LET(
原始均值,AVERAGE(A1:A4),
偏差数组,ABS(A1:A4-原始均值),
最大偏差,MAX(偏差数组),
IF(最大偏差>1,AVERAGE(FILTER(A1:A4,偏差数组<>最大偏差)),原始均值)
)

旧版工具兼容公式 #

如果使用的是2019及更早版本的Excel、旧版WPS，不支持LET和FILTER函数，使用以下数组公式，输入完成后按Ctrl+Shift+Enter生效：
=IF(MAX(ABS(A1:A4-AVERAGE(A1:A4)))>1,AVERAGE(IF(ABS(A1:A4-AVERAGE(A1:A4))<>MAX(ABS(A1:A4-AVERAGE(A1:A4))),A1:A4)),AVERAGE(A1:A4))

注意事项 #

• 如果出现两个数值与原始均值的偏差相同且均为最大值，上述公式会同时剔除所有符合最大偏差的数值，若仅需要剔除一个，可将FILTER的判断条件替换为ROW(A1:A4)<>MATCH(最大偏差,偏差数组,0)+ROW(A1)-1即可。
• 如果你定义的QC值不是绝对偏差值而是偏差除以其他基准值，仅需要调整最大偏差的计算逻辑即可。

内容的提问来源于stack exchange，提问作者Tiago Gois