如何标记按指定交易维度收费不等于2.25美元的交易行？

核心公式（直接在「Concern?」列下拉使用） #

假设你的数据列对应：

• A列：交易类型ID
• B列：交易日期
• C列：实际与订单数量
• D列：账户ID
• E列：实际收费金额
• F列：Concern?（用于标记异常）

在F2单元格输入以下公式，下拉即可批量标记：

=IF(AND(E2<>2.25, COUNTIFS($A:$A,A2,$B:$B,B2,$C:$C,C2,$D:$D,D2)=1), "收费错误（同维度唯一但金额不符）", IF(AND(E2<>2.25, COUNTIFS($A:$A,A2,$B:$B,B2,$C:$C,C2,$D:$D,D2,$E:$E,2.25)>=1), "收费错误（同维度组存在正确收费参考）", IF(AND(E2=2.25, COUNTIFS($A:$A,A2,$B:$B,B2,$C:$C,C2,$D:$D,D2)>1), "收费异常（同维度组有多笔但按唯一标准收费）", "正常")))

公式逻辑拆解 #

1. 第一判断：如果当前交易是「交易类型ID+交易日期+实际与订单数量+账户ID」维度唯一的单条记录，但收费≠2.25美元 → 标记为「收费错误（同维度唯一但金额不符）」
2. 第二判断：如果当前交易收费≠2.25美元，但同维度组内存在至少一笔按2.25美元正确收费的记录 → 标记为「收费错误（同维度组存在正确收费参考）」
3. 第三判断：如果当前交易收费=2.25美元，但同维度组内有多条记录（不符合「维度唯一才收2.25」的规则） → 标记为「收费异常（同维度组有多笔但按唯一标准收费）」
4. 默认情况：符合收费规则 → 标记为「正常」

更易懂的辅助列方案 #

如果觉得长公式难排查，可以拆分到辅助列：

1. G列（组内交易总数）：=COUNTIFS($A:$A,A2,$B:$B,B2,$C:$C,C2,$D:$D,D2)
2. H列（组内正确收费数）：=COUNTIFS($A:$A,A2,$B:$B,B2,$C:$C,C2,$D:$D,D2,$E:$E,2.25)
3. F列（Concern?）：

=IF(AND(G2=1,E2<>2.25),"收费错误（组内唯一但金额不符）",IF(AND(G2>1,E2=2.25),"收费异常（组内多笔但按唯一标准收费）",IF(AND(E2<>2.25,H2>=1),"收费错误（组内存在正确收费参考）",IF(AND(E2<>2.25,H2=0),"收费错误（组内无正确收费参考）","正常"))))

注意事项 #

• 确保交易日期格式统一：如果存在文本型日期和数字型日期混合的情况，可将COUNTIFS中的日期匹配改为$B:$B,TEXT(B2,"yyyy-mm-dd")，避免统计偏差
• 实际与订单数量列需为数值格式：如果是文本型数字，先通过「数据→分列」或VALUE()函数转换为数值，否则COUNTIFS无法正确匹配
• 大数据量优化：如果数据行数较多，将公式中的整列引用（如$A:$A）改为实际数据范围（如$A$2:$A$10000），提升计算速度

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