判断是否存在连续5次超标的数据

在Excel中，如何判断是否存在连续5次超标的数据

在SPC质量控制管理工作中，通常需要应用统计分析技术对生产过程进行实时监控，科学的区分出生产过程中产品质量的随机波动与异常波动，从而对生产过程的异常趋势提出预警，以便生产管理人员及时采取措施，消除异常，恢复过程的稳定，从而达到提高和控制质量的目的。如图 110‑1所示A:C列为某工厂3台设备生产的产品的检测数据，其理论标准值为7.3890，如果检测数据连续5次大于或小于标准值，则说明该组数据异常。如何用公式判断A、B、C三列数据是否异常呢？

图110‑1 SPC检测数据

→ 解决方案：

使用COUNTIF+OFFSET的多维引用方法判断是否存在连续5次大于或小于标准值的数据。

→ 操作方法

在F3单元格输入如下数组公式，按<Ctrl+Shift+Enter>组合键结束。

{=IF(OR(COUNTIF(OFFSET(A2,ROW(1:21),,5),{'<','>'}&$E$3)=5),'异常','正常')}

将F3单元格公式向右填充至H3单元格。公式结果如图 110‑2所示。

图110‑2判断结果

→ 原理分析

COUNTIF+OFFSET多维引用计算连续超标次数

1．本例使用OFFSET产生多维引用，将一组数据拆分成每5个连续数据为一个区域，如OFFSET(A2,ROW(1:21),,5)，将A列数据拆分为A3:A7、A4:A8、A5:A9、……、A23:A27共21个区域。

2．利用COUNTIF函数支持多维引用的特性，分别统计每一个区域小于和大于标准值的数据个数，返回数组：

{3,2;4,1;3,2;3,2;4,1;4,1;3,2;4,1;3,2;2,3;2,3;2,3;1,4;2,3;2,3;1,4;1,4;2,3;1,4;1,4;1,4}

3．使用OR函数判断数组中是否存在等于5的数值，如果存在，则说明该组数据有连续5次超标的数据，使用IF函数返回'异常'，否则返回'正常'。

版权所有 转载须经Excel技巧网/Office学吧允许