Excel的函数库庞大而复杂，大多数人仅熟悉SUM、VLOOKUP、IF等常见公式。然而，许多实用却鲜为人知的公式能大幅提升数据处理效率。本文将盘点那些被低估的Excel公式，并附上详细使用教程，助你解锁更高阶的数据处理技能。

一、数据清洗类公式：告别杂乱数据

1.TRIM函数

功能：清除字符串首尾空格及中间多余空格，仅保留单个空格。
语法：=TRIM(文本)
案例：若A2单元格内容为" Excel 技巧 "，输入=TRIM(A2)后结果为"Excel 技巧"。

2.SUBSTITUTE函数

功能：精准替换文本中的特定字符，支持指定替换次数。
语法：=SUBSTITUTE(原文本, 旧字符, 新字符, [替换次数])
案例：

• 替换所有空格：=SUBSTITUTE(A2," ","")
• 仅替换第2个括号：=SUBSTITUTE(A2,"(","|",2)（常用于提取特定位置字符前的文本）。

3.CLEAN函数

功能：删除文本中的非打印字符（如换行符）。
语法：=CLEAN(文本)
适用场景：处理从网页或系统导出的含乱码数据。

二、数据提取与处理：精准拆分与加密

1.MID+LEFT/RIGHT组合

功能：从字符串中提取指定位置的字符。
语法：

• =LEFT(文本, 字符数)：提取左侧字符
• =MID(文本, 起始位置, 字符数)：提取中间字符
  案例：
• 身份证信息提取：
• 出生日期：=TEXT(MID(A2,7,8),"0-00-00")
• 性别：=IF(MOD(MID(A2,15,3),2),"男","女")。

2.REPLACE函数

功能：替换字符串中指定位置的字符，常用于数据加密。
语法：=REPLACE(原文本, 起始位置, 替换长度, 新内容)
案例：

• 手机号加密：=REPLACE(A2,4,4,"****") → 将第4位起的4位替换为星号。

三、高效查找与匹配：突破常规限制

1.INDEX+SMALL+IF组合

功能：提取不重复值或按条件提取多行记录。
语法（数组公式，需按Ctrl+Shift+Enter输入）：

• 提取不重复值：
  =INDEX(A:A, SMALL(IF(MATCH($A$2:$A$10,$A$2:$A$10,)=ROW($2:$10)-1, ROW($2:$10),4^8), ROW(1:1)))&""
• 按条件提取记录：
  =INDEX(A:A, SMALL(IF($A$2:$A$10=条件, ROW($2:$10),4^8), ROW(1:1)))&""
  适用场景：动态生成唯一值列表或筛选复杂条件下的数据。

2.CHOOSE函数

功能：根据序号返回预设的多个结果之一。
语法：=CHOOSE(序号, 结果1, 结果2, ...)
案例：

• 编号101-105对应部门名称：
  =CHOOSE(B2-100,"总办","销售","财务","客服","人事")。

四、条件判断与加密：灵活应对复杂逻辑

1.IFERROR函数

功能：屏蔽错误值，提升表格美观性。
语法：=IFERROR(原公式, 错误时返回值)
案例：=IFERROR(VLOOKUP(A2, 数据表,2,0), "无匹配")。

2.MOD函数

功能：取余数，常用于奇偶判断。
语法：=MOD(数值, 除数)
案例：结合MID函数判断性别（身份证第17位奇数为男）：
=IF(MOD(MID(A2,17,1),2),"男","女")。

五、高级组合公式：解决复杂问题

1.SUMPRODUCT多条件统计

功能：多条件求和或计数，无需依赖辅助列。
语法：

• 多条件计数：=SUMPRODUCT((区域1=条件1)*(区域2=条件2))
• 多条件求和：=SUMPRODUCT((区域1=条件1)*(区域2=条件2), 求和区域)
  案例：统计25岁及以下女性的业绩：
  =SUMPRODUCT((年龄区域<=25)*(性别区域="女"), 业绩区域)。

2.TEXT函数格式化输出

功能：自定义数据显示格式。
语法：=TEXT(数值, "格式代码")
案例：

• 日期格式化：=TEXT(A2,"yyyy-mm-dd")
• 数字补零：=TEXT(A2,"00000")（输入1显示为00001）。

结语

以上公式虽不如VLOOKUP等函数知名，但在实际工作中能解决诸多复杂场景问题。建议结合具体需求尝试应用。