原文引自:整理了5个小时,Excel公式的所有用法共16种,全在这里了!-今日头条 (toutiao.com)
在Excel输入公式可以说是人人都必须要会的,对于初学者,在使用过程中会出现各种各样的问题。小编花了3个小时,终于将16种用法都整理全了,收藏起来慢慢看。
1.输入公式后,没进行计算 ![](https://p3-sign.toutiaoimg.com/tos-cn-i-qvj2lq49k0/dfac4f6630804c4b95798f82d15e6b90~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1711582687&x-signature=xNnucpmYb%2FilbANls9%2FxnWQiP2M%3D)
将公式的单元格设置为常规,再重新输入公式即可。 =TEXT(MID(B2,7,8),"0-00-00")
![](https://p3-sign.toutiaoimg.com/tos-cn-i-qvj2lq49k0/ea2baad786814155a2889aa14f8c90e6~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1711582687&x-signature=dG7iesUvqgbv4PgiSc72E%2B6VRl8%3D)
2.显示所有公式
点公式,显示公式。如果要恢复正常,再重新点一次显示公式就可以。 ![](https://p3-sign.toutiaoimg.com/tos-cn-i-qvj2lq49k0/d9af694a81b442f9b24235040fb13a63~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1711582687&x-signature=65L4W%2BD7G0P4RFSSGN4QkuFdq44%3D)
3.在隔壁列显示公式
在隔壁列输入FORMULATEXT就可以。 =FORMULATEXT(C2)
![](https://p26-sign.toutiaoimg.com/tos-cn-i-qvj2lq49k0/ef106db0e1c7421e8c9097100c840abb~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1711582687&x-signature=pSEvPR1cYnjoOHoORQx1jMTbZs4%3D)
4.填充公式
可以用下拉,也可以双击单元格的右下角填充公式。 ![](https://p3-sign.toutiaoimg.com/tos-cn-i-qvj2lq49k0/4f0b7aa89c2c42f6b38c02f27fd0096e~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1711582687&x-signature=nRFO7B%2F8eVotQy1D5OUcZY91Ypk%3D)
5.自动更新公式
普通的填充公式,输入新数据,需要再填充一次,而自动更新公式是一劳永逸。
选择A1,插入表格,输入新的身份证,出生日期的公式也随着自动更新。 ![](https://p3-sign.toutiaoimg.com/tos-cn-i-qvj2lq49k0/1e54016495434cb49ad9cd4b6af0c28d~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1711582687&x-signature=IeyqDK3gnnMlKhn1%2F%2BaD4dcGeTY%3D)
6.将公式粘贴成值
公式太多的时候,表格会卡,可以选择区域,复制,右键选择123就转换成值。 ![](https://p3-sign.toutiaoimg.com/tos-cn-i-qvj2lq49k0/3e120250788a4ed79e2b62ebfe7f9546~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1711582687&x-signature=vSzJwahTr4oNx0%2FclVXNL2KNX74%3D)
7.合并单元格填充公式
选择区域A2:A9,输入公式,按Ctrl+Enter结束。 =MAX($A$1:A1)+1
![](https://p3-sign.toutiaoimg.com/tos-cn-i-qvj2lq49k0/a737df5d5efe41e6935cff681ecb341d~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1711582687&x-signature=p%2BWckGblcqFrYsBwLq%2Ba3ct28Dc%3D)
01 不能在A2直接输入公式,下拉。合并单元格输入公式的方法跟常规公式不同,切记! ![](https://p3-sign.toutiaoimg.com/tos-cn-i-qvj2lq49k0/6abf911ce8d0479e915c48b6c91ab56c~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1711582687&x-signature=cXSnXgjrjqKDcA%2FL5GEsIzug3to%3D)
02 不能在没有标题的情况下输入公式,否则会出错! ![](https://p3-sign.toutiaoimg.com/tos-cn-i-qvj2lq49k0/5b55971c76ba4b779fed07c9a540b746~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1711582687&x-signature=pHxJpT076m04AVYoITBivkD2Q8s%3D)
8.不连续区域输入公式
操作方法跟合并单元格填充公式类似,按住Ctrl键选择区域,输入公式,按Ctrl+Enter结束。 ![](https://p9-sign.toutiaoimg.com/tos-cn-i-qvj2lq49k0/0e38df3b1fbe46b18ea1f67e01b5247f~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1711582687&x-signature=MWVZ1YzYMHkkOOAFjkniNA643JE%3D)
9.替换公式
将公式中的C列改成D列。
也就是将公式中的C2、C3、C4、C5 这些改成成D2、D3、D4、D5。 ![](https://p3-sign.toutiaoimg.com/tos-cn-i-qvj2lq49k0/30ff6930f7034225bcf4a354da6e8623~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1711582687&x-signature=qIeahttbDNQTHqvfrtQgNuHVdZs%3D)
查找替换,不仅可以直接替换实际内容,也可以替换公式。这里的C,刚好全部前面有$,因此可以查找$C,替换成$D。 ![](https://p3-sign.toutiaoimg.com/tos-cn-i-qvj2lq49k0/56101692b4344ff0bb03e03818125e7b~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1711582687&x-signature=SG%2FwbyMWh98QUqynbXzAEx9SoEw%3D)
同理,有的时候公式只是换了个工作表名称,其他不变,也可以直接查找替换哦。
10.计算表达式
单击B2单元格,再单击公式,定义名称,名称输入:表达式,引用位置为下面的公式,确定。 =EVALUATE(A2)
![](https://p3-sign.toutiaoimg.com/tos-cn-i-qvj2lq49k0/398710fcaeda4199bb8e67a6f2db63b4~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1711582687&x-signature=yR5wX8QM0S39WeW4XEzog3qOqEk%3D)
在B2单元格输入公式,并向下复制。 ![](https://p3-sign.toutiaoimg.com/tos-cn-i-qvj2lq49k0/fa3910746b78443887a62e7fb4268db0~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1711582687&x-signature=a%2FLuY%2BvGO5mSR5stGiT96BNP2mI%3D)
11.给公式加注释 =TEXTJOIN(",",1,A2:A5)&T(N("将A列的内容合并"))
![](https://p3-sign.toutiaoimg.com/tos-cn-i-qvj2lq49k0/d6bc7bd53d6249e0a403e440576d2787~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1711582687&x-signature=yea%2FAIGpgZnSIhTAjKUcL0oL5FI%3D)
N的作用就是将文本转换成0,比如在算累计的时候是不需要C1这个标题的。如果没有这个转换,会出错,文本是不能直接计算的。 =N(C1)+B2
![](https://p3-sign.toutiaoimg.com/tos-cn-i-qvj2lq49k0/7046e35d21c24d9f9d65694e843d1743~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1711582687&x-signature=dzbCvyHxEEzN3%2FUgra3wfgODppI%3D)
Excel的计算结果,可以是数字,也可以是文本。文本是无法直接+0的,而再嵌套T函数,可以将0转换成"",这样就能正常。
12.解读公式
对于不懂含义的公式,可以点公式,公式求值,看运算结果。 ![](https://p3-sign.toutiaoimg.com/tos-cn-i-qvj2lq49k0/ce8b22b3ebc1444fb2fd41d19629d412~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1711582687&x-signature=COUx8BwJYoxyrEEkGfr4Qu5KSVw%3D)
还能用F9键(笔记本用Fn+F9)解读,更加智能,哪里不懂,选哪里。
如根据番号精确查找俗称。 =LOOKUP(1,0/($A$2 ![](static/image/smiley/default/shy.gif) A$14=D2),$B$2 ![](static/image/smiley/default/shy.gif) B$14)
![](https://p3-sign.toutiaoimg.com/tos-cn-i-qvj2lq49k0/fe6430ed71194154bf1cb43a39c02bbc~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1711582687&x-signature=6qdZS1Q1MnfhWDFxYALnprZRxv0%3D)
卢子喜欢将这个称为以大欺小法,就是用1查找0。
0/($A$2 A$14=D2)的作用就是将符合条件的值转换成0,其他转换成错误值。在这里认识下有独孤九剑之称的F9键。
哪里不懂抹哪里,公式理解so easy。
现在$A$2 A$14=D2这部分不理解,直接在编辑栏抹黑,按F9键,就看到原来这部分是番号的逐一比较,如果满足就显示TRUE,否则显示FALSE。 ![](https://p3-sign.toutiaoimg.com/tos-cn-i-qvj2lq49k0/7e8952edb32a48e3b9f0d091b976394e~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1711582687&x-signature=EVgea1IJw0Vwrwci%2FTs%2Fh%2BnK9DM%3D)
了解后,记得按Esc键或Ctrl+Z组合键返回,否则公式就变了。
一次看完0/(条件),得到的是由0和错误值转成的数组。 ![](https://p3-sign.toutiaoimg.com/tos-cn-i-qvj2lq49k0/12a616f8d1b24f8fbe47c0d4ee7abb53~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1711582687&x-signature=OfINjXfDbUhH3iX0qlO0T%2Fsv4Yw%3D)
LOOKUP函数喜欢以大欺小,用1查找0,也就是查找小于或者等于他的值,因为在查找的时候,自动忽略错误值,所以能找到0。 ![](https://p3-sign.toutiaoimg.com/tos-cn-i-qvj2lq49k0/8011b6519b854a4b84a582baabf1cfed~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1711582687&x-signature=roqAO4Dq1se%2FZdGsXY9modxTvnk%3D)
13.数组公式
需要按Ctrl+Shift+Enter三键结束的公式,点链接进去查看完整教程:骗你爱上数组公式,10年经验全分享,再也找不到比这更全的资料了
14.输入公式出现的问题
输入公式,出现错误#NUM! ![](https://p3-sign.toutiaoimg.com/tos-cn-i-qvj2lq49k0/e764a095c4424135aaf9d446dd7b1ce8~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1711582687&x-signature=04du7CwdgnopiO7GvRCwIDR5tGc%3D)
2021/6/1放在单元格是表示日期,而放在公式是一个计算式,这里/就等同于÷,也就是说得到336.8333。 ![](https://p3-sign.toutiaoimg.com/tos-cn-i-qvj2lq49k0/c4df58950e604366841434283d2591d6~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1711582687&x-signature=2wZZTwQ3QyJQqj%2B72jmfVLnKELc%3D)
公式中的日期,有两种比较常用的表示法,"2021/6/1"和DATE(2021,6,1)。
于是,就有了不管三七二十一,全部内容加双引号这种错误方法。A2是单元格,不能加双引号,否则就变成文本了,这样就无法运算。 ![](https://p3-sign.toutiaoimg.com/tos-cn-i-qvj2lq49k0/b3dffedea8a443b7b601284ec5eacf21~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1711582687&x-signature=TXLrLZXJ0j%2B2BLn1dkFMVpCxZik%3D)
但公式准确无误输入后,又出现了新的问题,单元格显示1900/1/16,怎么回事? ![](https://p3-sign.toutiaoimg.com/tos-cn-i-qvj2lq49k0/099467dd3c044ab9b51b507f70317c10~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1711582687&x-signature=00ezI1nvGcjSkm6CqgHt4nuzZw4%3D)
公式有的时候,受隔壁列的影响,单元格格式变成日期格式,需要设置为常规才能恢复正常。 =DATEDIF(A2,"2021/6/1","m")
![](https://p3-sign.toutiaoimg.com/tos-cn-i-qvj2lq49k0/b3e36f531d264f10922d94f4e121a9dd~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1711582687&x-signature=TxxX5kBARywGa%2BcotAKR4KHYqkQ%3D)
15.公式错误
NO1:“#DIV/0!” 错误 当一个数除以零 (0) 或空白单元格时,Excel 将显示此错误。 ![](https://p3-sign.toutiaoimg.com/tos-cn-i-qvj2lq49k0/64ea361d645f44f7b799872ccfc517f3~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1711582687&x-signature=RewKpcCMSN5xE3CBVXOrrY1UBG8%3D)
用IF函数进行一个判断即可。 =IF(B2=0,"",C2/B2)
NO2:“#NAME?”错误 Excel 无法识别公式中的文本时会显示此错误,比如由于在公式当中,文本字符串没有添加英文双引号或函数名称拼写错误。 ![](https://p3-sign.toutiaoimg.com/tos-cn-i-qvj2lq49k0/0aab6c9b1c9f4728ab15d827646fcbf8~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1711582687&x-signature=3UDLcA5HTTb1%2FMXMpeN%2B%2Fkli0c4%3D)
NO3:“ #NULL!”错误 当指定两个不相交的区域的交集时(交集运算符是分隔公式中的引用的空格字符),而导致的错误值,它其实是一种值的返回结果,例如,区域A1:B2和C6 7不相交,因此,输入公式=SUM(A1:B2 C6 7)将返回 #NULL! 错误。 ![](https://p3-sign.toutiaoimg.com/tos-cn-i-qvj2lq49k0/5bbf4ca7cced4b85bece373361315adf~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1711582687&x-signature=GfVcdDmRVGpAHG%2B%2BIjSlcoUTjec%3D)
NO4: “#NUM!” 错误 当公式或函数包含无效数值时,如求负数的平方根,导致出错。 ![](https://p3-sign.toutiaoimg.com/tos-cn-i-qvj2lq49k0/a44ab133fdc2463b944d3593160b4ce3~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1711582687&x-signature=%2FaqAMOwJbv%2BcbUAStDDqCzRCphw%3D)
NO5:“ #REF! ”错误 引用的区域被删除后,如良品数的区域被删除,导致出错。 ![](https://p3-sign.toutiaoimg.com/tos-cn-i-qvj2lq49k0/b77759a6eeec4ca98ffbe31cfe642782~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1711582687&x-signature=RFtA5uETbWDXqppBAm%2BEwddLa2o%3D)
NO6: “ #VALUE! ” 错误 单元格内含有文本,运算导致出错。 ![](https://p3-sign.toutiaoimg.com/tos-cn-i-qvj2lq49k0/faa07a25acd0442ea878b25861cf908e~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1711582687&x-signature=fC1rZxAQEPUdHyIha10JnRDdcgw%3D)
NO7: “ #NA ” 错误 这个主要是VLOOKUP查找不到对应值导致的,详见文章:VLOOKUP函数你知错了吗?
16.公式隐身术
这种一般用于设置表格模板,因为是发送给别人录入数据,为防止别人误操作导致运算出错,所以设置了工作表保护,将公式隐藏起来。
假设现在这个表就是最终的模板,E列已经设置好了公式,现在只允许在其他列修改内容,E列禁止修改,也看不到公式,但结果可以自动更新。 ![](https://p3-sign.toutiaoimg.com/tos-cn-i-qvj2lq49k0/5ad819dbd0d34fec8c49cc7a23479574~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1711582687&x-signature=PcIl31L7mIt7o%2Bk8UkA9m2XrZXA%3D)
效果如动画 ![](https://p3-sign.toutiaoimg.com/tos-cn-i-qvj2lq49k0/3a1bb1ee47c14d269870df305f232e18~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1711582687&x-signature=uRRQUXn%2FVUezn50o5C1S6yE4Mb8%3D)
Step 01 点击全选,按Ctrl+1点击保护,取消锁定、隐藏的勾选,确定。 ![](https://p3-sign.toutiaoimg.com/tos-cn-i-qvj2lq49k0/a74905cfc35245a09b400ff32baf2f59~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1711582687&x-signature=DFpUcRyjSN6ooYcTDw49bdkn06Q%3D)
Step 02 选择E列公式的区域,Ctrl+1选择保护,勾选锁定、隐藏,确定。 ![](https://p3-sign.toutiaoimg.com/tos-cn-i-qvj2lq49k0/473f654245444e26b243a50ba5ff8939~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1711582687&x-signature=lCU%2FaYYnzmXCoGcfzRjaNqvUaS0%3D)
Step 03 点审阅,保护工作表,设置密码,确定,重新输入密码,再次确定。 ![](https://p3-sign.toutiaoimg.com/tos-cn-i-qvj2lq49k0/845ddf0f6cd7471a8664bb2188efaf37~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1711582687&x-signature=WoS2tIgK0LqEDo4MvQ%2BN00541TU%3D)
大功告成!收工!
|