Excel 2013下基于竞价历史自动判定Bid类型的公式需求

前提假设 #

假设你的数据列结构如下（需确保时间列为可计算的时间格式）：

• A列：唯一ID
• B列：竞价时间（精确到秒）
• D列：竞价金额
• E列：待生成的Type标记列（从E2开始输入公式）

核心公式（E2单元格，下拉填充） #

=IF(COUNTIF($A$2:$A2,A2)=1,
    IF(D2=AGGREGATE(14,6,$D$2:$D$30/($A$2:$A$30=A2),1),"Max","Bid"),
    IF((B2-INDEX($B$2:$B1,MATCH(2,1/($A$2:$A1=A2))))*86400<=15,
        IF(D2<AGGREGATE(14,6,$D$2:$D$30/($A$2:$A$30=A2),1),"Auto",
            IF(D2=AGGREGATE(14,6,$D$2:$D$30/($A$2:$A$30=A2),1),"Max","Rebid")),
        IF(D2=AGGREGATE(14,6,$D$2:$D$30/($A$2:$A$30=A2),1),"Max","Rebid")))

注意：输入公式后需按 Ctrl+Shift+Enter 触发数组计算（Excel 2013中部分场景可自动识别，但保险起见手动触发）。

公式逻辑拆解 #

1. Bid/Max（首次出现）：

   • 用COUNTIF($A$2:$A2,A2)=1判断当前行是否是该ID的首次竞价
   • 如果首次竞价金额等于该ID的最高竞价（通过AGGREGATE计算），标记为Max；否则标记为Bid

2. Auto（自动加价）：

   • 用INDEX+MATCH定位该ID上一次竞价的时间，计算时间差并转为秒数（*86400将日期差转为秒）
   • 若时间差≤15秒且当前竞价金额低于该ID最高竞价，标记为Auto

3. Rebid/Max（后续手动竞价）：

   • 非首次且超出15秒时间范围时，若当前金额等于该ID最高竞价，标记为Max；否则标记为Rebid

额外说明 #

• 若你的数据行数超过30行，需将公式中的$D$2:$D$30和$A$2:$A$30范围改为实际数据范围（比如$D$2:$D$1000）
• 时间列必须是Excel可识别的时间格式（如hh:mm:ss），否则时间差计算会出错
• 若需调整自动竞价的时间阈值（比如从15秒改为10秒），只需修改公式中的<=15为<=10即可

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