这5个Excel技巧,公司里老员工都在偷偷用

01 数据验证：给单元格装个"门禁"

适用场景：规范录入、防止乱填数据、做下拉菜单

你有没有遇到过这种情况：同事填表格，部门一栏有人写"市场部"，有人写"市场"，有人写"MKT"，还有人写"市场营销部"——明明是同一个部门，后期筛选统计时全对不上。

数据验证就是解决这个问题的。

操作步骤：

1. 选中要规范录入的列（比如"部门"列）
2. 点击【数据】→【数据验证】
3. 在"允许"下拉中选择"序列"
4. 在"来源"框中输入选项，用英文逗号分隔：市场部,技术部,财务部,人事部
5. 确定

现在这个列的每个单元格都会出现一个下拉箭头，只能从指定选项里选，不能随便打字。

进阶用法：来源也可以引用某个单元格区域，比如你有一张"部门对照表"，直接引用那一列，以后加部门只需要在对照表里加一行，下拉菜单自动更新。

还可以设置自定义验证规则，比如"工号必须是6位数字"——在数据验证里选"自定义"，输入公式：

=AND(LEN(A2)=6,ISNUMBER(A2))

填错了会弹出提示，不让保存。

一句话总结：数据验证就是给单元格装门禁，只有"合法的人"才能进去。

02 INDEX + MATCH：比VLOOKUP更灵活的查找组合

适用场景：向左查找、多条件查找、大数据量更高效

VLOOKUP是Excel里最常用的查找函数，但它有一个硬伤——只能从左往右找。查找值必须在数据表的第一列，返回值必须在右边。

如果你的数据长这样：A列是姓名，B列是工号，你想通过工号反查姓名——VLOOKUP就傻了，因为姓名在工号的左边。

这时候就该 INDEX + MATCH 出场了。

公式写法：

=INDEX(要返回的列, MATCH(查找值, 查找列, 0))

举个例子：A列是姓名，B列是工号，你想通过B列的工号查找A列的姓名：

=INDEX(A:A, MATCH(”202601”, B:B, 0))

 为什么比VLOOKUP好？

• 可以向左查找，不受列的位置限制
• 不需要数"返回值在第几列"——VLOOKUP的第三参数数错了就出bug
• 插入或删除列后不会出错，因为MATCH是按列引用的，不是按位置数字
• 大数据量下计算速度更快

如果你还在用VLOOKUP数列数，试试INDEX+MATCH，用过就回不去了。

03 IFERROR：给公式套个"安全气囊"

适用场景：消除#N/A、#DIV/0!等错误值，让表格干净专业

你用VLOOKUP或INDEX+MATCH查数据时，查不到的行会显示 #N/A。如果有除法公式，除数为0时会显示 #DIV/0!。

一个表格里满屏的错误值，打印出来给老板看，印象分直接归零。

IFERROR 的用法极简：

=IFERROR(你的公式, 出错时显示的内容)

实际应用：

原来：

=VLOOKUP(A2, Sheet2!A:D, 4, 0)

查不到就显示 #N/A

改后：

=IFERROR(VLOOKUP(A2, Sheet2!A:D, 4, 0), ”未找到”)

查不到就显示"未找到"

或者更简洁：

=IFERROR(VLOOKUP(A2, Sheet2!A:D, 4, 0), ””)

查不到就显示空白，表格干干净净。

进阶技巧：嵌套使用，处理不同类型的错误：

=IFERROR(1/0, IFERROR(A2/B2, ”除数为零”))

一个IFERROR，让你的表格从"满屏报错"变成"专业干净"。

04 分列：30秒拆分一列数据

适用场景：拆分姓名、地址、日期格式转换、清理脏数据

你收到一份数据，A列是"张三-市场部-13800138000"，姓名、部门、电话全挤在一个单元格里。你需要把它们拆成三列。

操作步骤：

1. 选中A列
2. 点击【数据】→【分列】
3. 选择"分隔符号"，下一步
4. 勾选"其他"，输入 -
5. 下一步，设置每列的数据格式（文本/日期/常规）
6. 完成

一列变三列，30秒搞定。

另一个超实用场景——批量转换日期格式：

你收到的数据里日期格式是 20260902（文本格式），Excel不认。需要转成 2026/09/02 才能用于日期计算。

用分列：

1. 选中该列
2. 【数据】→【分列】
3. 第一步、第二步直接下一步
4. 第三步，列数据格式选"日期"，选"YMD"
5. 完成

文本日期瞬间变成真正的Excel日期，可以排序、可以算差值、可以用TEXT函数格式化。

05 冻结窗格 + 自动筛选：大数据表浏览必备组合

适用场景：处理几百上千行的数据表

你有一张500行的销售明细表，往下翻几行，表头就消失了——不知道每列是什么数据。往右滚，A列的姓名也看不见了——不知道这行是谁的。

冻结窗格，3秒解决：

1. 点击B2单元格（即表头右侧、第一行数据下方）
2. 点击【视图】→【冻结窗格】→【冻结窗格】

现在无论你怎么往下滚、往右滚，第一行表头 and A列姓名始终固定在屏幕上。

然后加上自动筛选：

1. 选中表头行
2. 点击【数据】→【筛选】（或Ctrl+Shift+L）
3. 每个表头会出现下拉箭头

现在你可以：

• 按部门筛选，只看市场部的数据
• 按金额排序，从大到小
• 搜索某个关键词，快速定位

06 COUNTIFS：多条件计数神器

适用场景：统计满足多个条件的数据条数

老板说："帮我看看市场部本月销售额超过1万的员工有几个。"

你可能会先筛选市场部，再筛选金额大于1万，然后数行数。改一次条件就要重新操作一遍。

COUNTIFS一步到位：

=COUNTIFS(部门列, ”市场部”, 销售额列, ”>10000”)

语法：

=COUNTIFS(条件区域1, 条件1, 条件区域2, 条件2, ...)

条件可以加无限个，想加几个加几个：

• 统计市场部、北京区域、销售额>1万的人数：

=COUNTIFS(A:A,”市场部”,B:B,”北京”,C:C,”>10000”)

• 统计某日期范围内的订单数：
  

=COUNTIFS(日期列,”>=2026/9/1”, 日期列, ”<=2026/9/30”)

07 快捷键合集：每天省30分钟的肌肉记忆

前两篇提过一些快捷键，这里补充几个还没说过的：

其中 Alt + = 是被严重低估的快捷键——选中数据行末尾的空白单元格，按一下，Excel自动判断求和范围并填入SUM公式，比手动写公式快10倍。

写在最后

Excel这个工具，越用越觉得自己只用了10%。每次发现一个新技巧，都有种"原来还能这样"的感觉。

但技巧再多，有一个前提——你得先有干净的数据可以用。

现实工作中，大量的数据不是在Excel里等你的，而是锁在PDF文件里的。老板发来的报表、客户发来的合同、同事发来的统计表——全是PDF，表格复制出来全是乱码。

技巧学得再多，数据进不来Excel，也白搭。

所以记住这个顺序：先把PDF里的数据提取到Excel，然后再用这些技巧去分析和处理。

工欲善其事，必先利其器。先利器，再善事。

一键提取PDF里的表格：https://kaiww.cn

上一篇Excel技巧文章的读者反馈不错，这篇如果对你有帮助，转发给同事一起进步。