整理了3个小时,Excel公式的所有用法共16种,全在这里了!
- 2026-11-07 08:53:32
点击👆Excel不加班，关注星标★不迷路
在Excel输入公式可以说是人人都必须要会的，对于初学者，在使用过程中会出现各种各样的问题。卢子花了3个小时，终于将16种用法都整理全了，收藏起来慢慢看。
1.输入公式后，没进行计算
将公式的单元格设置为常规，再重新输入公式即可。
=TEXT(MID(B2,7,8),"0-00-00")
2.显示所有公式
点公式，显示公式。如果要恢复正常，再重新点一次显示公式就可以。
3.在隔壁列显示公式
在隔壁列输入FORMULATEXT就可以。
=FORMULATEXT(C2)
4.填充公式
可以用下拉，也可以双击单元格的右下角填充公式。
5.自动更新公式
普通的填充公式，输入新数据，需要再填充一次，而自动更新公式是一劳永逸。
选择A1，插入表格，输入新的身份证，出生日期的公式也随着自动更新。
6.将公式粘贴成值
公式太多的时候，表格会卡，可以选择区域，复制，右键选择123就转换成值。
7.合并单元格填充公式
选择区域A2:A9，输入公式，按Ctrl+Enter结束。
=MAX($A$1:A1)+1
01 不能在A2直接输入公式，下拉。合并单元格输入公式的方法跟常规公式不同，切记！
02 不能在没有标题的情况下输入公式，否则会出错！
8.不连续区域输入公式
操作方法跟合并单元格填充公式类似，按住Ctrl键选择区域，输入公式，按Ctrl+Enter结束。
9.替换公式
将公式中的C列改成D列。
也就是将公式中的C2、C3、C4、C5 这些改成成D2、D3、D4、D5。
查找替换，不仅可以直接替换实际内容，也可以替换公式。这里的C，刚好全部前面有$，因此可以查找$C，替换成$D。
同理，有的时候公式只是换了个工作表名称，其他不变，也可以直接查找替换哦。
10.计算表达式
单击B2单元格，再单击公式，定义名称，名称输入：表达式，引用位置为下面的公式，确定。
=EVALUATE(A2)
在B2单元格输入公式，并向下复制。
11.给公式加注释
=TEXTJOIN("，",1,A2:A5)&T(N("将A列的内容合并"))
N的作用就是将文本转换成0，比如在算累计的时候是不需要C1这个标题的。如果没有这个转换，会出错，文本是不能直接计算的。
=N(C1)+B2
Excel的计算结果，可以是数字，也可以是文本。文本是无法直接+0的，而再嵌套T函数，可以将0转换成""，这样就能正常。
12.解读公式
对于不懂含义的公式，可以点公式，公式求值，看运算结果。
还能用F9键（笔记本用Fn+F9）解读，更加智能，哪里不懂，选哪里。
如根据番号精确查找俗称。
=LOOKUP(1,0/($A$2:$A$14=D2),$B$2:$B$14)
卢子喜欢将这个称为以大欺小法，就是用1查找0。
0/($A$2:$A$14=D2)的作用就是将符合条件的值转换成0，其他转换成错误值。在这里认识下有独孤九剑之称的F9键。
哪里不懂抹哪里，公式理解so easy。
现在$A$2:$A$14=D2这部分不理解，直接在编辑栏抹黑，按F9键，就看到原来这部分是番号的逐一比较，如果满足就显示TRUE，否则显示FALSE。
了解后，记得按Esc键或Ctrl+Z组合键返回，否则公式就变了。
一次看完0/(条件)，得到的是由0和错误值转成的数组。
LOOKUP函数喜欢以大欺小，用1查找0，也就是查找小于或者等于他的值，因为在查找的时候，自动忽略错误值，所以能找到0。
13.数组公式
需要按Ctrl+Shift+Enter三键结束的公式，点链接进去查看完整教程：骗你爱上数组公式，10年经验全分享，再也找不到比这更全的资料了
14.输入公式出现的问题
输入公式，出现错误#NUM!
2021/6/1放在单元格是表示日期，而放在公式是一个计算式，这里/就等同于÷，也就是说得到336.8333。
公式中的日期，有两种比较常用的表示法，"2021/6/1"和DATE(2021,6,1)。
于是，就有了不管三七二十一，全部内容加双引号这种错误方法。A2是单元格，不能加双引号，否则就变成文本了，这样就无法运算。
但公式准确无误输入后，又出现了新的问题，单元格显示1900/1/16，怎么回事？
公式有的时候，受隔壁列的影响，单元格格式变成日期格式，需要设置为常规才能恢复正常。
=DATEDIF(A2,"2021/6/1","m")
15.公式错误
NO1：“#DIV/0!” 错误
当一个数除以零 (0) 或空白单元格时，Excel 将显示此错误。
用IF函数进行一个判断即可。
=IF(B2=0,"",C2/B2)
NO2：“#NAME?”错误
Excel 无法识别公式中的文本时会显示此错误，比如由于在公式当中，文本字符串没有添加英文双引号或函数名称拼写错误。
NO3：“ #NULL!”错误
当指定两个不相交的区域的交集时（交集运算符是分隔公式中的引用的空格字符），而导致的错误值，它其实是一种值的返回结果，例如，区域A1:B2和C6:D7不相交，因此，输入公式=SUM(A1:B2 C6:D7)将返回 #NULL! 错误。
NO4: “#NUM!” 错误
当公式或函数包含无效数值时，如求负数的平方根，导致出错。
NO5:“ #REF! ”错误
引用的区域被删除后，如良品数的区域被删除，导致出错。
NO6: “ #VALUE! ” 错误
单元格内含有文本，运算导致出错。
NO7: “ #NA ” 错误
这个主要是VLOOKUP查找不到对应值导致的，详见文章：VLOOKUP函数你知错了吗？
16.公式隐身术
这种一般用于设置表格模板，因为是发送给别人录入数据，为防止别人误操作导致运算出错，所以设置了工作表保护，将公式隐藏起来。
假设现在这个表就是最终的模板，E列已经设置好了公式，现在只允许在其他列修改内容，E列禁止修改，也看不到公式，但结果可以自动更新。
效果如动画
Step 01 点击全选，按Ctrl+1点击保护，取消锁定、隐藏的勾选，确定。
Step 02 选择E列公式的区域，Ctrl+1选择保护，勾选锁定、隐藏，确定。
Step 03 点审阅，保护工作表，设置密码，确定，重新输入密码，再次确定。
大功告成，这样就可以放心将模板发送给别人。
推荐：Excel常用公式大全共23条，助你纵横职场，碾压90%的同事！
上篇：Excel内置功能实现不了，手工要几天，WorkBuddy几分钟就搞定
作者：卢子，清华畅销书作者，《Excel效率手册 早做完，不加班》系列丛书创始人，个人公众号：Excel不加班（ID：Excelbujiaban）
请把「Excel不加班」推荐给你的朋友
别忘了点赞支持卢子哦↓↓↓
