以下为精心优化后的 Excel 高级公式用法指南，其内容广泛涵盖数据处理、统计分析、文本操作等核心应用场景。通过对函数组合与逻辑判断的深入剖析，助力您大幅提升数据处理的效率：
一、数据处理与统计分析
1. 多条件动态求和（SUMIFS + 通配符）
功能：依据多个动态条件（支持模糊匹配）开展求和运算。
公式：
=SUMIFS(C:C, A:A, "*苹果*", B:B, ">2025-01-01", B:B, "<=2025-06-01")
解析：
- 统计范畴：统计 A 列中包含“苹果”（*为通配符，可匹配任意字符）、B 列日期处于 2025 年上半年（即 1 月 1 日至 6 月 1 日）的 C 列数据总和。
- 应用场景：适用于商品销售数据按照类别与时间区间进行动态汇总。
2. 按频率排名（COUNTIF + 数组公式）
功能：对数据区域按照出现频次进行降序排名，若出现频率相同则共享同一名次。
公式（需按 Ctrl + Shift + Enter 结束）：
=SUMPRODUCT((COUNTIF(A$2:A$100, A2:A100)>=COUNTIF(A$2:A$100, A2))*1)+1
解析：
- 核心逻辑：借助 COUNTIF 函数统计每个值的出现次数，再利用 SUMPRODUCT 函数对比次数以生成非重复排名（结果示例：1, 1, 3, 4）。
- 注意事项：此公式需以数组形式执行，以此确保能正确计算区间内的所有数据。
3. 提取不重复值（UNIQUE + FILTERXML）
功能：从包含重复值的列中迅速提取唯一值，可兼容不同的 Excel 版本。
Excel 365 专属公式：
=UNIQUE(A:A) <!-- 直接返回唯一值数组 -->
低版本兼容方案（利用 XML 函数）：
=IFERROR(FILTERXML("<t><s>"&SUBSTITUTE(TEXTJOIN(",",TRUE,A:A),",","</s><s>")&"</s></t>","//s[not(preceding::*=.)]"),"")
解析：
- UNIQUE 函数可一键生成唯一值列表；低版本则通过文本拼接（TEXTJOIN ）与 XML 解析（FILTERXML）达成同等成效。
- 优势：规避手动删除重复项的繁琐操作，有效提升数据清洗的效率。
二、文本操作与动态引用
4. 合并同类项并分组求和（TEXTJOIN + SUMIFS）
功能：依照类别对文本描述予以合并，并计算相应数值的总和。
公式：
=TEXTJOIN(", ", TRUE, FILTER(A:A, A:A=D2, "")) & " | 总计：" & SUMIFS(C:C, A:A, D2)
解析：
- 参数阐释：D2为目标类别，FILTER函数用于筛选同类文本，TEXTJOIN对筛选结果进行合并（TRUE表示忽略空值），SUMIFS则计算对应数值的总和。
- 示例输出：“苹果, 香蕉 | 总计：500”。
5. 动态区间求和（OFFSET + COUNTA）
功能：依据数据量自动拓展求和范围，以适配动态增减的数据表。
公式：
=SUM(OFFSET(B2, 0, 0, COUNTA(B:B)-1, 1))
解析：
- 偏移原理：以 B2 为起始点，向下提取COUNTA(B:B)-1行（COUNTA用于统计非空单元格数量，减 1 是为排除标题行），从而实现无需手动调整范围的动态求和。
6. 条件计数与占比（COUNTIFS + SUMIFS）
功能：统计符合多个条件的记录数量，并计算其在总体中的占比。
公式：
=COUNTIFS(A:A, "合格", B:B, ">80") & "人，占比：" & SUMIFS(C:C, A:A, "合格", B:B, ">80")/SUM(C:C)*100&"%"
解析：
- 组合运用：COUNTIFS统计同时满足“A 列 = 合格”和“B 列 > 80”的记录数量，SUMIFS计算对应 C 列数据的总和，通过除法运算与百分比符号生成占比结果。
三、跨表引用与特殊场景处理
7. 跨表动态引用（INDIRECT + TEXT）
功能：基于变量（例如当前日期），实现对不同工作表数据的动态引用。
公式：
=SUM(INDIRECT("'"&TEXT(TODAY(), "yyyy-mm")&"'!A:C"))
解析：
- 动态拼接机制：借助TEXT(TODAY(), "yyyy - mm")生成当前年月（例如“2025 - 06”），再通过INDIRECT函数引用同名工作表的 A:C 列数据并进行求和操作。
- 应用场景：可用于月度报表的自动汇总，无需人工手动切换工作表。
8. 字符串按长度拆分（MID + SEARCH + 数组公式）
功能：从混合文本里提取固定长度的数字（比如身份证号中的出生日期）。
公式（需按 Ctrl + Shift + Enter 结束）：
= MID(A2, SEARCH("^[0 - 9]{8}", A2&"0123456789"), 8)
解析：
- 正则匹配逻辑：SEARCH("^[0 - 9]{8}",...)用于定位首个连续 8 位数字的起始位置，MID函数则提取相应长度的字符，此方法适用于从 18 位身份证号中提取第 7 - 14 位的出生日期。
9. 时间差按工作日计算（NETWORKDAYS.INTL）
功能：精准计算两个日期之间的工作日天数，并且支持对休息日与假期进行自定义设置。
公式：
=NETWORKDAYS.INTL(A2, B2, "0000001", $D$2:$D$10)
解析：
- 参数阐释： "0000001"：此为自定义休息日掩码（从周一至周日，1 代表休息日，在此设定下仅周日为休息日）； $D$2:$D$10：这是自定义假期列表（例如法定节假日）。
- 优势：相较于普通的日期差计算方式，该方法更契合实际工作场景。
10. 数据验证 + 动态下拉菜单（INDIRECT + 命名管理器）
功能：构建能够随数据源变化而自动更新的动态下拉菜单。
操作步骤：
- 定义名称： 操作路径：公式 → 定义名称，将名称设定为“Category”，在引用位置输入：
=OFFSET(Sheet1!$A$1,1,0,COUNTA(Sheet1!$A:$A)-1,1)
（从 A1 单元格下方第 1 行开始，提取非空数据行数减 1 的动态区域）。
2. 设置数据验证：
- 选中目标单元格，然后依次点击数据 → 数据验证，在“允许”选项中选择“序列”，在“来源”处输入=Category。
解析：
当 A 列新增类别时，命名区域会自动扩展，下拉菜单也会实时更新，从而避免了手动维护数据源的繁琐操作。
关键提示
- 引用与公式类型： 绝对引用（$）用于固定单元格引用，可防止在公式复制过程中出现错位情况； 数组公式需按 Ctrl + Shift + Enter 组合键执行，Excel 365 用户可尝试使用简化版的动态数组函数（如FILTER、SORT）。
- 版本兼容性： Excel 365 版本优先使用UNIQUE、FILTER等原生函数，低版本则可借助TEXTJOIN + FILTERXML等组合方案来实现相同功能。
- 复杂场景优化： 当涉及多步骤的数据清洗或批量处理时，建议结合 Power Query（操作路径：数据 → 获取和转换数据）或 VBA 脚本，以进一步提升处理效率。
通过灵活运用这些高级公式的组合，能够显著减少手动操作的工作量，使 Excel 成为高效的数据处理引擎！