Excel周期统计公式失效排查及列显示与排序优化需求

问题原因分析 #

• 日期格式问题：真实业务文件中日期列（对应原公式的FRUITS!A:A）大概率是文本格式而非标准日期格式，导致日期比较逻辑aa;">="&TODAY()-...失效。
• 人名匹配问题：人名（如Doe, John）可能带前后空格，造成COUNTIFS无法精准匹配唯一值。

修改后的公式（适配需求） #

=LET(
    a; FRUITS!A2:INDEX(FRUITS!B:B; LOOKUP(2; 1/(FRUITS!A:A<>""); ROW(FRUITS!B:B)));
    aa; INDEX(a;;1);
    ab; TRIM(INDEX(a;;2));
    u; UNIQUE(ab);
    period_days; VLOOKUP(SUBSTITUTE(TRIM(D2); " "; ""); {"24HOURS"\0;"2DAYS"\1;"3DAYS"\4;"7DAYS"\7;"2WEEKS"\14;"1MONTH"\30;"3MONTHS"\90;"6MONTHS"\180;"1YEAR"\365;"2YEARS"\730;"3YEARS"\1095;"TOTAL"\999999}; 2; 0);
    d; COUNTIFS(ab; u; aa;">="&TODAY()-period_days);
    sorted_result; SORT(CHOOSE({1\2}; u; d); 2; -1);
    sorted_result
)

关键调整说明 #

• 人名空格处理：用TRIM(INDEX(a;;2))去除人名前后空格，确保匹配精准；唯一值提取也基于处理后的人名。
• 输出列简化：CHOOSE({1\2}; u; d)直接生成「唯一数据项名称」「周期统计数」两列。
• 排序逻辑优化：SORT(..., 2, -1)直接按第二列（统计数）降序排列，符合需求。
• 周期参数优化：将周期天数单独提取为period_days提升可读性；同时对D2的选择值用TRIM处理，避免空格干扰VLOOKUP匹配。

验证步骤 #

1. 检查日期列：在空白单元格输入=ISNUMBER(FRUITS!A2)，若返回FALSE，选中日期列→「数据」选项卡→「分列」→直接完成，即可转换为标准日期格式。
2. 验证VLOOKUP匹配：确认D2的选择值（如TOTAL）与公式常量数组中的字符串无大小写或空格差异。

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