Excel如何使用函数根据时间值返回对应时段分类结果

分类规则 #

需根据播出时间返回三类标签：

• 23:00-次日5:59：Night Off Prime
• 6:00-17:59：Day Off Prime
• 18:00-22:59：Prime

原有写法的问题 #

之前的三个公式存在三类共性错误，导致结果不符合预期：

1. 数值小时列的IFS公式：第三个条件存在语法错误（G3,23缺少比较运算符），且条件里多余嵌套OR逻辑，IFS本身按顺序匹配的特性已经可以避免区间冲突，不需要额外加冗余判断。
2. hh:mm格式时间列的IFS公式：时间常量未加英文双引号导致Excel识别为单元格引用，第二个判断错误引用G列单元格，第三个条件同样存在语法错误。
3. VLOOKUP公式：查找表第一列存储的是文本格式的时段范围字符串，不是可比较的时间/数值阈值，近似匹配模式下无法和时间值做大小比对，完全无法触发正确匹配。

可用公式 #

根据源数据格式二选一即可：

针对G列数值格式存储的小时数 #

=IFS(OR(G3>=23,G3<6),"Night Off Prime",G3<18,"Day Off Prime",TRUE,"Prime")

IFS会按从上到下的顺序判断：先匹配跨午夜的区间，剩余值必然落在6-23区间，只要小于18就返回白天档，剩下的18-22区间直接返回黄金档，无逻辑漏洞。

针对BK列hh:mm格式存储的时间值 #

Excel中时间本质是0~1的浮点值（0对应0点，1对应24点），直接用带英文双引号的时间常量做判断即可：

=IFS(OR(BK3>="23:00",BK3<"06:00"),"Night Off Prime",BK3<"18:00","Day Off Prime",TRUE,"Prime")

注意：公式内的时间值必须使用英文半角双引号包裹，否则会返回#NAME?错误。

VLOOKUP实现方案 #

如果偏好查找引用写法，先修正查找表结构：第一列不要存时段文本，改为存每个区间的起始时间阈值，按升序排列即可：

• AC列（阈值）依次填入：0:00、6:00、18:00、23:00
• AD列（分类）依次填入：Night Off Prime、Day Off Prime、Prime、Night Off Prime

匹配公式如下：

=VLOOKUP(BK3,$AC$1:$AD$4,2,TRUE)

近似匹配模式下，Excel会自动定位小于等于当前查找时间的最大阈值，自动覆盖跨午夜的时段判断，不需要额外写OR逻辑处理跨天问题。

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