Excel单元格公式实现调查编号按时间排序自动赋值问题

嗨，我来帮你搞定这个调查编号的问题！原来的公式之所以出错，是因为它把**所有包含该用户的日期记录（哪怕没有调查数据）**都算进了统计范围，导致编号混乱。我们需要调整公式，只统计那些有调查数据的行，同时按日期顺序给每个用户的调查正确编号。

修改后的公式（适用于所有Excel版本） #

在Survey Number列的第一个数据行（比如F2）输入以下公式，然后下拉填充：

=IF(OR(C2<>"",D2<>"",E2<>""),SUMPRODUCT(--(A$2:A$11=A2),--(B$2:B$11<=B2),--(OR(C$2:C$11<>"",D$2:D$11<>"",E$2:E$11<>""))),"")

公式解释 #

1. 外层IF判断：和你原来的逻辑一致，检查当前行是否有调查数据（Q1/Q2/Stress任意一列非空），没有就返回空值，避免给无调查的行生成编号。
2. SUMPRODUCT统计核心：
   • --(A$2:A$11=A2)：筛选出和当前行姓名相同的记录，转换为1（符合）/0（不符合）的数组。
   • --(B$2:B$11<=B2)：筛选出日期早于或等于当前行日期的记录，同样转换为1/0数组。
   • --(OR(C$2:C$11<>"",D$2:D$11<>"",E$2:E$11<>""))：只保留有调查数据的行，转换为1/0数组。
   • 三个数组对应相乘后求和，得到的就是当前用户在当前日期及之前完成的调查次数，也就是正确的Survey Number。

针对Excel 365/2021的简化公式 #

如果你用的是新版Excel，也可以用更简洁的动态数组公式：

=IF(OR(C2<>"",D2<>"",E2<>""),COUNT(FILTER(B$2:B$11,(A$2:A$11=A2)*(B$2:B$11<=B2)*(OR(C$2:C$11<>"",D$2:D$11<>"",E$2:E$11<>"")))),"")

注意事项 #

• 把公式中的A$2:A$11、B$2:B$11等范围替换成你实际的数据范围（不要用整列如A:A，会拖慢计算速度）。
• 日期列需要确保是Excel可识别的日期格式，否则日期比较会出错。

用这个公式测试你提供的示例数据，Tony Stark的调查编号会正确显示为1、2、3，Steve Rogers和Nick Fury的编号也会完全符合预期。

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