立即登录领取积分
  1. 文章列表
  2. 详情页

详情页

Excel常用函数实战使用手册完整指南

Excel常用函数实战使用手册完整指南

Excel常用函数实战使用手册完整指南

Excel常用函数实战使用手册完整指南

16 ·

CC 4.0 BY-SA版权

简介：《Excel常用函数使用手册》是一本面向数据分析爱好者和初学者的实用指南，系统介绍了Excel中常用函数的用法与应用场景。内容涵盖基础计算、逻辑判断、文本处理、日期时间操作、查找引用、多条件统计及数组公式等核心功能。通过本手册的学习，读者可掌握SUM、AVERAGE、IF、VLOOKUP、COUNTIFS、SUMPRODUCT等关键函数，并学会组合运用它们解决实际数据处理问题，显著提升工作效率与分析能力，助力在职场中脱颖而出。

Excel函数深度实战：从基础语法到企业级自动化系统构建

你有没有经历过这样的场景？

周五下午5点，老板突然发来消息：“把上季度的销售数据整理一下，明早开会要用。” 🕖

你打开那个名为“最终版_不要删_修订2.xlsx”的文件——没错，就是那个三年前创建、经过17次迭代、包含4个Sheet、300多列字段的巨型表格。

你深吸一口气，手指悬在键盘上，心里默念：

“求你了，这次别让我手动筛选华东区+Q2+高客单价客户的订单……” 😬

但现实是残酷的。你花了整整一个周末，复制粘贴、条件格式、VLOOKUP嵌套套娃，终于赶在周一早上交差。结果老板看了一眼说：“这个数据好像不对，再核对下吧。”

💥 崩溃！

其实，这一切本可以完全不同。

Excel 不只是一个“电子表格”，它是一台 微型计算机 ，而函数就是它的“编程语言”。掌握正确的函数思维，不仅能让你从重复劳动中解放出来，更能构建出像呼吸一样自然的 智能报表系统 ——每天自动更新、实时预警、一键生成分析报告。

今天，我们就来彻底揭开 Excel 函数的神秘面纱，带你从“公式搬运工”蜕变为“办公自动化架构师”。

一、别再死记硬背了！理解Excel函数的本质逻辑

很多人学Excel函数的方式就像背英语单词： SUM=求和 ， AVERAGE=平均值 ， IF=如果 ……然后遇到复杂问题就卡壳。

为什么？

因为你只记住了“做什么”，却没搞懂“怎么做”。

🧠 Excel函数的底层运行机制：一场无声的数据流水线

想象一下工厂里的装配线：

  • 原材料（原始数据）进入生产线；

  • 每道工序（函数）对其进行加工；

  • 最终产出成品（结果）。

Excel 的工作方式几乎一模一样。

以最简单的 =SUM(A1:A10) 为例，它的执行流程远比你想象的复杂：

flowchart TD
A[启动SUM函数] --> B{检查参数类型}
B -->|数值| C[加入累加器]
B -->|文本| D[跳过]
B -->|错误值| E[返回错误]
B -->|空单元格| F[忽略]
C --> G[继续下一参数]
G --> H{所有参数处理完毕？}
H -->|否| B
H -->|是| I[输出总和结果]
看到没？ SUM 并不是简单地“把数字加起来”，它其实是一个 具备类型感知能力的数据过滤器 + 累加器复合体 ！

这意味着：

  • 如果你的区域里混着“暂无记录”、“N/A”之类的文本，它们会被自动跳过 ✅

  • 但如果其中有 #DIV/0! 错误值，整个公式就会崩溃 ❌

所以，在真实业务环境中，更安全的做法是：

=SUM(IFERROR(B2:B10, 0))
💡 小贴士：这是个数组公式，在旧版 Excel 中需要按 Ctrl+Shift+Enter 输入；新版 Excel 支持动态数组，直接回车即可。

这种“容错封装”思维，才是高手与普通用户的关键区别。

二、数值类函数不只是加减乘除——它们是数据分析的地基

我们先来看看最常见的几个数值函数： SUM 、 AVERAGE 、 MAX 、 MIN 、 COUNT 。你以为它们很简单？那可能是你还没踩过坑。

🔢 SUM 函数的隐藏技能：不只是求和，更是“空间整合引擎”

跨表求和？用三维引用一行搞定！

假设你有四个 Sheet 分别记录每季度销售额，现在要在“汇总表”中计算全年总额。

大多数人会这样写：

=SUM(Sheet1!B2:B10) + SUM(Sheet2!B2:B10) + SUM(Sheet3!B2:B10) + SUM(Sheet4!B2:B10)
太啰嗦了！试试这个：

=SUM(Sheet1:Sheet4!B2:B10)
这就是所谓的“ 三维引用 ”—— Sheet1:Sheet4 表示从第一张到第四张表的连续区间，Excel 会自动遍历这些表中的 B2:B10 区域并相加。

✅ 优点：简洁高效，适合结构一致的多表汇总。

❌ 缺点：一旦中间插入或删除工作表，引用链可能断裂，导致结果偏差。

⚠️ 所以建议：对于关键报表，优先使用显式列举法，牺牲一点便捷性换取稳定性。

动态区域求和：让用户自己选择要看哪个月？

如果你做的是仪表板（Dashboard），用户希望在下拉菜单中选择“1月”、“2月”等，就能自动切换数据源。

这时候就得请出 INDIRECT 函数：

=SUM(INDIRECT(G1 & "!B2:B10"))
只要 G1 单元格的内容是 "Sheet1" ，公式就等价于 =SUM(Sheet1!B2:B10) 。

| 步骤 | 公式解析 |
| 1 | G1值为 “Sheet1” |
| 2 | G1 & "!B2:B10" → “Sheet1!B2:B10” |
| 3 | INDIRECT("Sheet1!B2:B10") 返回实际区域引用 |
| 4 | SUM(...) 对该区域求和 |

这招在做 可配置报表模板 时特别实用，堪称“参数驱动式计算”的典范 👍

📊 AVERAGE、MAX、MIN：别让异常值毁了你的统计结论！

有一次，财务同事告诉我：“本月平均销售额高达110万！” 我一看数据才发现，原来是某个客户误录了一笔300万的订单……

这就是 AVERAGE 的致命弱点： 对异常值极度敏感 。

销售额（万元）

300（误录）

正常均值 ≈ 90万，但因为多了个300万，变成了110万 —— 直接误导决策！

怎么办？这里有三种解法：

  1. 截尾均值 ：去掉最高最低各10%
    excel =TRIMMEAN(B2:B10, 0.2)

  2. 中位数替代 ：完全不受极端值影响
    excel =MEDIAN(B2:B10)

  3. 条件过滤 ：只计算合理范围内的数据
    excel =AVERAGEIF(B2:B10, "<200")

📌 总结一下不同函数的行为差异：

函数 是否忽略空值 是否忽略文本 是否受异常值影响
AVERAGE 是 是 高
AVERAGEA 否 视为0 极高
TRIMMEAN 是 是 中
MEDIAN 是 是 低

记住：除非特殊需求，否则永远不要用 AVERAGEA ，它会把“未填写”当成0处理，严重扭曲结果！

🔍 MAX/MIN + INDEX/MATCH：打造动态排行榜

想找出“谁是最佳销售员”？光用 MAX 只能得到数值，怎么拿到人名？

答案是组合拳：

=INDEX(A2:A10, MATCH(MAX(B2:B10), B2:B10, 0))
分解动作：

  1. MAX(B2:B10) 找出最大销售额；

  2. MATCH(..., B2:B10, 0) 返回该值的位置（比如第5行）；

  3. INDEX(A2:A10, 5) 提取对应姓名。

这套组合技几乎是所有 动态看板 的核心组件，无论是销售榜、绩效排名还是库存预警，都能派上用场。

🧮 COUNT vs COUNTA：两个计数函数，两种世界观

很多新人分不清这两个函数的区别，结果统计出来的“客户数量”莫名其妙多了几十条。

| 函数 | 计什么？ | 典型用途 |
| COUNT | 只算 数值型 单元格 | 统计有效交易笔数、样本量 |
| COUNTA | 所有 非空 单元格（含文本、错误、逻辑值） | 统计问卷回收率、信息完整度 |

举个例子：

数据 类型 COUNT计入？ COUNTA计入？
100 数值 ✅ ✅
张三 文本 ❌ ✅
TRUE 逻辑值 ❌ ✅
#N/A 错误 ❌ ✅
”“ 空字符串 ❌ ✅
（空） 空白 ❌ ❌

看出区别了吗？

更厉害的是，你可以用两者结合来做 数据质量诊断 ：

=(COUNTA(A1:A100) - COUNT(A1:A100)) / COUNTA(A1:A100)
这个公式返回“非数值内容占比”，超过一定阈值就说明数据录入有问题，值得警惕！

graph LR
A[原始数据] --> B{是否为空？}
B -->|是| C[不计入COUNTA]
B -->|否| D[进入COUNTA]
D --> E{是否为数值？}
E -->|是| F[计入COUNT]
E -->|否| G[不计入COUNT]
这张图清晰揭示了两者的筛选路径差异，是不是感觉一下子通透了？

三、逻辑与文本函数：让你的数据“活”起来

如果说数值函数是骨骼，那逻辑与文本函数就是肌肉和神经系统。没有它们，数据只是冰冷的数字。

🤖 IF 函数：Excel里的“大脑”，掌控一切判断逻辑

单层IF：二元世界的开关

最基础的写法：

=IF(C2>=60, "及格", "不及格")
但它背后藏着一个重要细节： 第三个参数可以省略 ！

=IF(ISBLANK(A2), "请补全信息")
如果A2为空，显示提示；否则什么都不显示（返回空文本）。这在做表单校验时非常有用。

多层嵌套IF：分类决策的“阶梯模型”

要划分多个等级怎么办？比如员工绩效评级：

=IF(E2>=90,"优秀",
IF(E2>=80,"良好",
IF(E2>=60,"合格","不合格")))
graph TD
A[开始] --> B{E2 >= 90?}
B -- 是 --> C[返回"优秀"]
B -- 否 --> D{E2 >= 80?}
D -- 是 --> E[返回"良好"]
D -- 否 --> F{E2 >= 60?}
F -- 是 --> G[返回"合格"]
F -- 否 --> H[返回"不合格"]
注意这个结构有个特性叫“ 短路评估 ”——一旦某个条件满足，后面的就不看了，效率很高。

不过别嵌太多层！超过5层就该考虑优化方案了：

  • 使用 IFS 函数（Office 365+）

  • 查找评级对照表： =VLOOKUP(E2, 等级表, 2, TRUE)

  • 或者更优雅的 CHOOSE + MATCH 组合

AND/OR 函数：构建复杂规则网络

现实世界的规则很少是单一条件的。比如贷款审批：

“收入 ≥ 8000 且 信用评分 > 700”

“已有抵押物”

翻译成公式：

=IF(OR(AND(收入>=8000,信用评分>700), 抵押物="有"), "批准", "拒绝")
| 收入≥8000 | 信用>700 | 抵押物=“有” | AND结果 | OR整体结果 | 最终判定 |
|---|---|---|---|---|---|
| TRUE | TRUE | FALSE | TRUE | TRUE | 批准 |
| TRUE | FALSE | FALSE | FALSE | FALSE | 拒绝 |
| FALSE | FALSE | TRUE | FALSE | TRUE | 批准 |

这种“复合逻辑”能力，让 Excel 能模拟真实的风控流程、审批流甚至简单的专家系统。

✂️ LEFT/RIGHT/MID：从混乱文本中提取黄金信息

公司数据库里经常有一堆奇葩字段，比如：

产品编码：ABC-2024-XJ

身份证号：110105199003072345

邮箱地址：zhangsan@abc.com
这些看似杂乱的信息，其实都藏着结构化数据。只要你会切，就能榨出价值。

拆分产品编码

品牌: =LEFT(G2,3) // "ABC"
年份: =MID(G2,5,4) // 从第5位取4位 → "2024"
地区: =RIGHT(G2,2) // 最后两位 → "XJ"

解析身份证号获取出生日期

中国身份证第7\~14位是出生年月日（YYYYMMDD格式）：

出生年: =MID(H2,7,4)
出生月: =MID(H2,11,2)
出生日: =MID(H2,13,2)
标准日期: =DATE(MID(H2,7,4), MID(H2,11,2), MID(H2,13,2))
⚠️ 注意： MID 返回的是 文本型数字 ，不能直接参与计算。必须用 VALUE() 转换，或者像上面那样嵌入 DATE 函数让它自动识别。

提取邮箱用户名（@前面的部分）

用户名长度不固定，怎么办？

=MID(I2, 1, FIND("@",I2)-1)
流程如下：

graph LR
A[输入邮箱] --> B[FIND查找@位置]
B --> C[计算用户名长度 = @位置 - 1]
C --> D[MID从第1位截取该长度]
D --> E[输出用户名]
这种方法无需预知长度，通用性强，可用于日志解析、URL参数提取等各种场景。

🔎 FIND vs SEARCH：精准定位 vs 模糊匹配

| 特性 | FIND | SEARCH |
| 大小写敏感 | 是 | 否 |
| 支持通配符 | 否 | 是（?、*） |
| 错误值 | #VALUE!（找不到） | #VALUE! |

👉 选哪个？

  • 密码、唯一标识符 → 用 FIND （严格匹配）

  • 用户输入、自然语言 → 用 SEARCH （宽容匹配）

实战：提取括号内的内容

原文：“项目名称（核心系统升级）”

目标：提取“核心系统升级”

=MID(J2, FIND("（",J2)+1, FIND("）",J2)-FIND("（",J2)-1)
⚠️ 重点提醒：中文括号“（）”和英文“()”ASCII码不同，务必确认符号类型，否则 FIND 找不到！

关键词检测：客服工单自动分类

想知道邮件里有没有提到“退款”？可以用：

=IF(ISNUMBER(SEARCH("退款",K2)), "需优先处理", "常规归档")
| FIND结果 | ISNUMBER(FIND(…)) | IF判断依据 | 输出 |
|---|---|---|---|
| 15 | TRUE | TRUE | 包含 |
| #VALUE! | FALSE | FALSE | 不包含 |

这一招在舆情监控、合同审查、客户投诉管理中简直是神器！

🔗 CONCATENATE vs CONCAT vs TEXTJOIN：文本拼接的进化史

早期我们只能这样拼接：

=CONCATENATE(A2," ",B2,", 工号:",C2)
又长又难改。后来有了 CONCAT ：

支持整片区域输入，还能自动跳过空值，清爽多了！

但真正强大的是 TEXTJOIN ：

=TEXTJOIN(" | ", TRUE, "姓名:"&A2, "电话:"&B2, "邮箱:"&C2)
- 第一个参数是分隔符；

  • 第二个参数决定是否忽略空项；

  • 后续是要合并的内容。

输出效果：

姓名:王芳 | 电话:138****1234 | 邮箱:wangfang@email.com
适用于生成标准化联系信息、导出CRM数据、制作二维码名片等场景。

📌 推荐选择指南：

| 需求场景 | 推荐函数 | 理由 |
| 固定几个字段拼接 | CONCATENATE | 兼容旧版Excel |
| 合并整行/整列数据 | CONCAT | 支持范围输入，自动忽略空值 |
| 需要分隔符美化输出 | TEXTJOIN | 自带分隔符管理，逻辑清晰 |
| 条件性拼接（如非空才加） | IF + & 连接 | 灵活控制每个字段是否加入 |

四、日期与查找函数：打通时间维度与信息孤岛

真正的数据分析，离不开时间和关联。

📅 TODAY/NOW/DATE/DAYS：时间不是装饰品，而是驱动力

Excel 中的所有日期本质上都是 数字 ——从1900年1月1日起经过的天数。

比如 2025年4月5日 = 45789。

正因为如此，日期才能参与运算，这才是它的真正威力所在！

函数 返回值示例 是否含时间 是否自动刷新
TODAY() 2025/4/5 否 每次重算时更新
NOW() 2025/4/5 14:30:22 是 每次重算时更新
DATE(…) 2025/4/5 否 固定不变

利用这一点，我们可以轻松实现“距今天数”、“剩余工期”、“是否逾期”等功能。

例如判断任务是否超期：

=IF(DUE_DATE < TODAY(), "已逾期", "正常")
再配合条件格式，让超期项自动标红，视觉冲击力拉满 🔴

🔍 VLOOKUP/HLOOKUP：查找函数的“平民英雄”

VLOOKUP 四大参数详解：

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
| 参数 | 示例 | 说明 |
| lookup_value | A2 | 要找的值（如工号） |
| table_array | A2:D100 | 查找范围（首列必须是关键字） |
| col_index_num | 3 | 返回第几列（从左起计数） |
| range_lookup | FALSE | 是否近似匹配 |

常见错误排查表：

| 错误码 | 原因 | 解决方法 |
| #N/A | 找不到匹配项 | 检查拼写、空格、数据类型 |
| #REF! | 列索引超出范围 | 确认不超过区域宽度 |
| #VALUE! | 列索引≤0或非数字 | 输入正整数 |

💡 提示：文本前后有空格是导致 #N/A 的常见原因，记得用 TRIM() 清理：

=VLOOKUP(TRIM(G2), $A$2:$D$100, 2, FALSE)

🚀 INDEX + MATCH：专业选手的终极武器

虽然 VLOOKUP 很好用，但它有两个致命缺陷：

  1. 只能向右查找，不能反向取左侧数据；

  2. 插入中间列会导致 col_index_num 偏移，公式出错。

解决方案： INDEX + MATCH 组合登场！

实现左向查找

现有表格：

| 姓名 | 部门 | 工号 |
| 张三 | 技术部 | E1001 |

已知工号查姓名（左列）， VLOOKUP 做不到，但 INDEX+MATCH 可以：

=INDEX(A:A, MATCH("E1001", C:C, 0))
逻辑拆解：

  1. MATCH("E1001", C:C, 0) 在C列找“E1001”，返回行号（如2）；

  2. INDEX(A:A, 2) 提取A列第2行的值“张三”。

✅ 优势总结：

  • 支持任意方向查找；

  • 插入列不影响公式；

  • 可扩展为二维查找（行+列均由MATCH确定）。

二维交叉查询完整示例：

=INDEX(DataRange,
MATCH(RowKey, RowHeader, 0),
MATCH(ColKey, ColHeader, 0))
适用于“某地区某月份销售额”这类交叉报表查询。

📊 性能与可读性对比：

| 特性 | VLOOKUP | INDEX+MATCH |
| 查找方向 | 仅右向 | 双向自由 |
| 列插入影响 | 需调整col_index | 自动适应 |
| 执行速度 | 快 | 稍慢（两次查找） |
| 多条件扩展 | 困难 | 可结合数组公式 |
| 可读性 | 高 | 中（需理解结构） |

建议：日常简单查询可用 VLOOKUP ，重要系统推荐统一使用 INDEX+MATCH 。

五、多条件统计与数组公式：迈向高级建模的关键一步

当业务变得复杂，单一条件已不够用。我们需要更强的聚合工具。

🎯 COUNTIFS/SUMIFS/AVERAGEIFS：多维统计的三大法宝

语法高度统一：

=SUMIFS(sum_range, criteria_range1, criteria1, ...)

    互动

    查看数
    9

      为您推荐的类似文章

      当下不少求职者在线求职陷入“低效高耗”困境，根源在于陷入“岗位可见=匹配可用”的思维陷阱和“概率博弈式”海投的效能悖论。本文针对这些求职误区，提出四大求职策略：一是精准定位，通过垂直行业招聘平台、企业官方渠道、行业活动寻找目标岗位专属通道；二是优化简历，遵循“针对性+数据化+故事化”原则打造求职敲门砖；三是拓宽思路，可从边缘岗位、中小企业入手，或通过实习兼职积累经验；四是主动出击，制作求职档案、跟进沟通、参与线下活动展现求职诚意，助力求职者走出困境，打造清晰求职路径。

      本文聚焦梅卡曼德（雄安）机器人科技股份有限公司商务与市场副总裁徐婷婷的创业故事。梅卡曼德是全球具身智能机器人领域的“独角兽”企业，其产品能为机器人装上“眼、脑、手”，自研的具身智能产品已在汽车、物流等多领域规模化落地。2024年，徐婷婷带领团队将公司总部从北京迁至雄安。她表示雄安将企业当作合伙人，高效包容、机会众多，让企业能安心扎根研发。徐婷婷亲历了企业从落地投产到产品出海、产业协作的发展，与这座未来之城同频共振，开启“AI+机器人”产业发展新征程。

      新工作适应速度直接影响职业起步质量，有人快速成为骨干，有人长期徘徊边缘，核心在于是否掌握系统的适应方法。本文提供覆盖“前期准备-中期融入-后期深耕”的职场破冰方法论：入职前72小时，从岗位、团队、业务三个维度做好信息预习，提前掌握核心信息；入职1-4周，遵循“三做三避”原则，以核心任务为锚点，通过精准执行、主动补位、及时反馈建立可靠形象，同时高效适配沟通习惯；入职1-3月，聚焦能力补位与价值输出，识别岗位需求差距并快速学习，主动创造价值实现从新人到团队贡献者的转变，助力职场人快速打破壁垒，在新岗位站稳脚跟。

      本手册为Sketch 2中文用户手册，适用于新手与熟练用户，会持续完善，用户可通过邮箱mail@bohemiancoding.com反馈问题。手册介绍了Sketch是一款面向全设计师的矢量绘图应用，主打网页、图标及界面设计，兼具矢量编辑与基础位图工具，易上手且功能强大，可替代Adobe Photoshop，专为图标与界面设计打造，有出色UI、多填充模式、优质文本功能、无限画布及切片工具等。此外还讲解了其简洁界面，包括顶端可自定义的工具栏、可管理图层与页面的图层列表、能调整参数的检查器，以及无限尺寸的画布，也提及了图形、矢量等图层类型。

      2025年10月13日，中国AI硬件公司未来智能宣布完成亿元级A轮融资，由蚂蚁集团领投、启明创投超额跟投，这是其年内第三次获得融资。未来智能自2021年布局AI办公耳机赛道，产品已从“记录工具”演进至“主动创作与交互”的个人智能办公助理，且已于2024年实现盈利。本轮融资将用于丰富AI办公硬件产品矩阵、建设推广海外自主品牌viaim、加大AI Agent等前沿技术探索投入，目前其海外品牌在北美、亚太市场增长迅速。

        为您推荐的相关资源

        多品类市场信息调研框架 | undefined

        企业销售利润核算表 | undefined

        存货计价审计工作底稿模板 | undefined

        客户销售额月榜：排名与数据一览 | undefined

        12城空调月度销售数据统计报表 | undefined