新手必藏！Excel效率翻倍的36个黄金函数公式，一篇全搞定！

你是否还在这样使用Excel？

机械重复地筛选、复制、粘贴、计算

，面对海量数据埋头苦干，函数书翻烂了，一到实战就卡壳？

问题不在于你记不住36个或360个函数，而在于你的使用思维是“手动的”。今天，我们将彻底转换视角：

把Excel看作一个自动化的智能助手，而函数就是你向它下达的精确指令。

本文不讲枯燥理论，只交付能直接复制粘贴、让效率发生质变的“指令集”。我们不仅完整保留并强化那36个黄金公式，更将注入

自动化工作流思维、高频组合技和避坑指南

。掌握它们，你的表格将学会“自己干活”。

核心理念：从“怎么算”到“我要什么结果”

在深入学习指令前，请先建立两个自动化思维：

目标导向

：不要想“我用哪个函数”，而是想“我要达成什么结果”（例如：“自动提取出某部门所有人的名单并排序”）。

流程组装

：复杂任务 = 简单指令 + 简单指令。用函数把数据处理的“流水线”搭建起来。

下面，我们按任务场景，将这36个核心指令归类为五大“自动化工具包”。

工具包一：数据汇总机器人（求和与计数类）

这类函数是你的

自动统计员

，能根据条件瞬间完成报表。

SUMIF（单条件求和）

指令

：=SUMIF(条件区域, 条件, 求和区域)

自动化场景

：让表格自动统计“华东区”的销售额总和。=SUMIF(大区列,"华东",销售额列)

SUMIFS（多条件求和）

指令

：=SUMIFS(求和区域, 条件区域1, 条件1, 条件区域2, 条件2…)

自动化场景

：自动计算“华东区”在“2024年1月”“A产品”的销售总额。这是制作动态仪表盘的核心。

COUNT / COUNTA

指令

：=COUNT(区域) (只计数字) / =COUNTA(区域) (计所有非空单元格)

核心差异

：统计人数用COUNTA，统计有成绩的单元格数量用COUNT。

COUNTIF（单条件计数）

指令

：=COUNTIF(区域, 条件)

自动化场景

：自动计算状态为“已完成”的项目数量。=COUNTIF(状态列,"已完成")

COUNTIFS（多条件计数）

指令

：=COUNTIFS(条件区域1,条件1, 条件区域2,条件2…)

自动化场景

：自动统计“销售部”“工龄>5年”的员工人数。

ROUND（四舍五入）

指令

：=ROUND(数字, 保留小数位数)

关键提示

：财务计算必备，确保金额精度。

MROUND（指定倍数舍入）

指令

：=MROUND(数字, 基数)

自动化场景

：自动将商品定价调整为最接近的“0.5”或“10”的倍数，便于定价策略。=MROUND(23.7, 0.5) → 23.5

工具包二：数据查找与组装引擎（查找与引用类）

这类函数是你的

智能检索与装配线

，能跨表抓取并动态组织数据。

VLOOKUP（经典查找）

指令

：=VLOOKUP(找谁, 在哪找, 返回第几列, 0)

致命局限提醒

：查找值必须在第一列；无法向左查找。

这是你转向XLOOKUP的最大理由

。

XLOOKUP（终极查找，强推）

指令

：=XLOOKUP(找谁, 在哪里找, 返回哪里的结果, [找不到的提示], [匹配模式])

自动化飞跃

： 可反向、多向查找。 默认精确匹配，无需记0/1。 支持通配符*和?。 示例：=XLOOKUP(A2, 员工ID列, 邮箱列, "查无此人")

INDEX + MATCH（灵活索引之王）

组合指令

：=INDEX(返回区域, MATCH(找谁, 查找列, 0))

应用场景

：当需要根据首行和首列两个条件进行“矩阵查询”时，它比VLOOKUP更灵活。

OFFSET（动态引用）

指令

：=OFFSET(起点, 下移几行, 右移几列, [高度], [宽度])

高级自动化

：它是定义

动态数据源

的核心，让图表和透视表随数据增加自动更新。例如，结合COUNTA函数自动确定数据范围。

INDIRECT（文本转引用）

指令

：=INDIRECT("工作表名!单元格地址")

自动化场景

：制作可切换月份的汇总表。假设A1单元格是工作表名“一月”，可用=SUM(INDIRECT(A1&"!B:B"))汇总该月数据。

CHOOSECOLS / CHOOSEROWS（按需提取）

指令

：=CHOOSECOLS(数据区域, 列序号1, 列序号2…)

自动化场景

：从包含20列的原始数据中，自动抽取仅需要的3列（如：姓名、部门、薪资）生成新报表。

FILTER（动态筛选，强推）

指令

：=FILTER(要筛选的数据区域, 筛选条件, [无结果提示])

革命性自动化

：输入公式，立刻得到一个

动态数组

，结果会随源数据变化而自动变化。可多层筛选：=FILTER(A:D, (B:B="销售部")*(C:C>10000))

ROW / COLUMN（生成序列）

指令

：=ROW 返回当前行号。常用于生成

自动序号

：=ROW-1（从第2行开始为1）。

工具包三：文本流水线处理器（文本处理类）

专门处理不规整的文本数据，实现自动清洗、拆分与合并。

16-18.

LEFT / MID / RIGHT（截取文本）

指令

：=MID(文本, 开始位置, 截取字数)

场景

：从身份证号中自动提取出生日期。=MID(A2, 7, 8)

TEXTSPLIT（分隔符拆分）

指令

：=TEXTSPLIT(文本, 列分隔符, [行分隔符])

自动化场景

：将“张三,销售部,4000”一键拆分成三列，彻底告别“分列”功能。

TEXTJOIN（智能合并）

指令

：=TEXTJOIN(分隔符, TRUE, 区域1, [区域2]…)

优势

：参数2设为TRUE时自动跳过空单元格，合并人名、地址时无比清爽。

21-22.

TEXTBEFORE / TEXTAFTER（智能提取）

指令

：=TEXTBEFORE(文本, 分隔符) / =TEXTAFTER(文本, 分隔符)

场景

：从邮箱“name@company.com”中自动提取用户名和域名。

LEN / LENB（计算长度）

指令

：=LEN(文本) 计算字符数，=LENB(文本)计算字节数（中文=2字节）。

场景

：检查文本输入是否符合长度限制。

24-25.

TOCOL / TOROW（多维转一维）

指令

：=TOCOL(多行多列区域)。将多个列的数据快速堆叠到一列中，是数据清洗的利器。

26-27.

WRAPCOLS / WRAPROWS（一维转多维）

指令

：=WRAPROWS(一维数组, 每行几个元素)。将一长列名单自动排布成多行多列的表格。

HYPERLINK（创建链接）

指令

：=HYPERLINK("#"&B2&"!A1", "跳转到"&B2)

自动化场景

：自动为目录表中的每个工作表名创建超链接，点击直达。

工具包四：逻辑判断中枢（逻辑判断类）

让表格拥有“大脑”，能根据条件自动决策并返回不同结果。

IF（基础判断）

指令

：=IF(条件, 条件成立时返回, 条件不成立时返回)

自动化基石

：所有智能判断的起点。

IFS（多条件判断）

指令

：=IFS(条件1,结果1, 条件2,结果2…)

简化革命

：直接替代复杂的多层IF嵌套，逻辑清晰不易错。

IFERROR / IFNA（容错处理）

指令

：=IFERROR(原公式, 出错时显示的值)

专业素养

：让报表永远干净整洁。=IFERROR(VLOOKUP(...), "数据缺失")

工具包五：数据整理与转换神器（其他实用类）

现代Excel的“黑科技”，让复杂的数据整理一键完成。

UNIQUE（提取唯一值）

指令

：=UNIQUE(区域)。自动生成不重复的列表，告别“删除重复项”操作。

SORT / SORTBY（动态排序）

指令

：=SORT(区域, 按第几列排序, 升序1/降序-1)

自动化场景

：=SORT(FILTER(...), 3, -1) 先筛选，再对结果按第三列降序排列，一气呵成。

SEQUENCE（生成序列）

指令

：=SEQUENCE(行数, [列数], [开始数], [步长])

场景

：自动生成日期序列、项目编号等。=SEQUENCE(100, 1, 2024001, 1) 生成100个从2024001开始的编号。

CONCAT（合并文本）

简化版

的TEXTJOIN，功能类似但参数更少。

DATE / YEAR / MONTH / DAY（日期处理）

指令

：=YEAR(日期单元格) 提取年份。

组合应用

：与TODAY函数结合，自动计算工龄、账龄。

NETWORKDAYS（计算工作日）

指令

：=NETWORKDAYS(开始日期, 结束日期, [假期列表])

自动化场景

：自动计算项目实际工作天数，排除周末和自定义假期。

LET函数（定义变量，高阶）

指令

：=LET(变量名1, 值1, 变量名2, 值2, 最终计算公式)

终极技巧

：让超长复杂公式变得像读文章一样清晰。例如，将一个重复计算的中间结果定义为变量“Bonus”，然后在最终计算中调用“Bonus”，极大简化并提升公式可读性和计算效率。

自动化升级路径：从掌握到精通

核心启动包（首周掌握）

：SUMIFS, XLOOKUP, IFS, FILTER, UNIQUE, SORT。用这6个函数解决60%的问题。

组合技练习（第二周）

：尝试 FILTER + SORT 制作动态报表；用 XLOOKUP + IFERROR 进行优雅查询；用 TEXTSPLIT + TEXTJOIN 清洗数据。

思维转变

：遇到任何重复性操作，先停下来思考：“能否用一个或一组函数公式，让Excel下一次自动完成？”

善用工具

：输入“=”后，按

Ctrl + A

可以调出完整的函数参数对话框，边看说明边填写。

记住，真正的效率提升，不在于背诵，而在于将

重复的手动操作，转化为一次设定、永久受益的自动化规则

。现在，就用这些指令去重构你的表格吧！

你需要从一张按城市和产品分类的销售总表中，

一键动态提取

出“北京”地区“产品A”的所有销售记录，并按销售额从高到低排列。下列哪个公式组合能最高效地实现？ A) =VLOOKUP("北京", 数据表, 3, 0) B) =SORT(FILTER(销售数据区, (城市列="北京")*(产品列="产品A")), 销售额列, -1) C) 先使用筛选器筛选出“北京”和“产品A”，然后手动复制粘贴到新表，再排序。 D) =SUMIFS(销售额列, 城市列, "北京", 产品列, "产品A")你收到一列杂乱的数据，格式为“姓名-电话-部门”（如“张三-13800138000-技术部”），你需要快速将这三部分信息拆分到三个独立的列中。最直接的“一条指令”是？ A) 使用“数据”选项卡中的“分列”功能。 B) =LEFT(A2, FIND("-",A2)-1)，然后向右拖拽填充。 C) =TEXTSPLIT(A2, "-") D) 手动复制粘贴。你在制作查询表，使用VLOOKUP查找员工工号对应的邮箱时，如果找不到，单元格会显示#N/A错误。你希望它更友好地显示“未录入”。应该用什么函数包裹？ A) =IF B) =IFERROR(VLOOKUP(...), "未录入") C) =ISERROR D) =IFNA(VLOOKUP(...), "未录入")

答案

: 1.B; 2.C; 3.B 或 D (在针对#N/A错误时，IFNA更精准，但IFERROR可屏蔽所有错误，两者在此场景下均可)