Google Sheets/Excel中基于双条件的温度违规读数计数公式问题

问题场景 #

需要统计室内温度传感器读数中低于动态最低温度的次数：

• 晚10点（22:00）至早6点：最低温度为62℉，室内温度低于此值算违规
• 早6点至晚10点：若室外温度（Q列）<55℉，最低温度为68℉，室内温度低于此值算违规；若室外温度≥55℉，无最低温度要求，此类情况不计入违规

错误原因分析 #

你之前尝试的整合版SUMPRODUCT公式出错，是因为OR(HOUR($A6:$A)>22, HOUR($A6:$A)<6)在数组计算中会返回单个全局布尔值（判断是否存在任意一行满足条件），而非逐行判断每行的时间是否符合区间，导致IF分支逻辑错误，最终统计结果偏差。

正确无辅助列公式 #

使用逐行逻辑判断替代OR，同时处理无最低值的情况，公式如下：

=SUMPRODUCT(--(IF((HOUR(A6:A)>22)+(HOUR(A6:A)<6), B6:B<62, IF(Q6:Q<55, B6:B<68, FALSE))))

公式逻辑拆解

1. (HOUR(A6:A)>22)+(HOUR(A6:A)<6)：逐行判断时间是否在22:00-6:00区间，两个条件任意满足则结果>0（等价于逐行OR）
2. 第一层IF：若时间在夜间区间，判断室内温度是否<62℉
3. 第二层IF：若时间在日间区间，先判断室外温度是否<55℉：
   • 是：判断室内温度是否<68℉
   • 否：返回FALSE，不计入违规
4. --()：将布尔值（TRUE/FALSE）转换为1/0，SUMPRODUCT自动求和得到总违规次数

另一种等价写法（用逻辑乘/加替代IF） #

如果偏好更简洁的逻辑运算写法，也可以用以下公式：

=SUMPRODUCT(--(((HOUR(A6:A)>22)+(HOUR(A6:A)<6))*(B6:B<62) + ((HOUR(A6:A)>=6)*(HOUR(A6:A)<=22)*(Q6:Q<55))*(B6:B<68)))

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