您现在的位置是：首页 > 办公软件 > Excel

Excel中有哪些值得必须掌握的公式技巧？

设·集合小编发布时间：2022-03-21 23:45:00 209次最后更新：2024-03-08 10:36:18

大家好，我是夏末叔，9年职场老司机，目前在某世界500强公司担任工程师，每天都会跟Excel打交道，今天跟大家分享28个跟公式相关的Excel技巧，让你事半功倍，听说掌握了这些技巧的职场老司机再也不加班了。

1、用TAB键输入函数

当你在输入一个很长的函数时，输入前两个字母后，Excel会列出一个相匹配的列表，这个时候可以直接用“↓”键进行选择，除了双击鼠标左键，还可以直接按 TAB 键，就能自动帮你输入整个函数：

2、函数的右括号不需要手动输入

比如你输入 =SUM(E3,F3 之后，直接按下Enter键，Excel会自动帮你输入右括号，完全没必要手动输入右括号！！！

备注：公式包含多个括号的情况除外

3、移动/复制公式时，如何只移动/复制内容本身？

Excel最强大的功能之一是相对地址：当把公式复制粘贴到新的单元格时，所有的相对地址都会跟随着改变，这本来就是我们常常想要的结果，比如对两个单元格求和之后，复制粘贴到下一个单元格，我们希望的结果也是求和。

但有时候我们仅仅是想移动或者将内容本身复制到新的单元格、而不是复制公式。那么有两种方法：

（1）使用拖拽，将内容直接拖拽到其他单元格：

将鼠标移动到单元格的边缘，出现一个“上下左右都有箭头”的图标后，长按鼠标左键，拖到其他位置即可：

（2）使用剪切（Ctrl X）和粘贴（Ctrl V）

4、同时复制粘贴多个公式时，如何只复制内容本身？

可以使用“查找和替换”功能，输入 Ctrl F，再选中“替换”选项卡，将所有的=号替换成#号，点击“全部替换”，公式就会变成文本格式；再选中多个单元格，复制粘贴到新的单元格，选中所有单元格，再进行“查找和替换”，将所有的#号替换成=号，就完成啦：

5、双击右下角的“填充句柄”（黑色小加号）可批量复制粘贴公式

通常情况下，如果我们要复制粘贴公式，会把鼠标移动到单元格的右下角，出现黑色的小＋号时，按住鼠标左键一直往下拉。

但是你有没有想过，如果你的Excel有上千行、甚至上万行数据时，你会发现这个办法非常的笨拙。实际上，使用Excel的“填充句柄”，让你一秒就能轻松搞定！

但前提是：不能存在空的行，如果有空行，效果如下：

6、使用“Table”功能来自动输入公式

输入公式更快的一种方式是先把表格转换成一个标准的Excel Table，大家可能会觉得听起来很奇怪，因为Excel本身就是一个Table（表），但我这里指的Table是指Excel本身的标准结构表，也就是用 Ctrl T 转换之后的样式。经过转换后，在第一行输入的所有公式将会自动填充到下面的所有行，好家伙！这个技巧才是真的好用，而且还不容易出错！！！

而且，一旦更新了其中的某一个公式之后，Excel会自动更新同一列的其他所有公式：

7、使用“Ctrl 点击鼠标左键”自动帮你输入参数之间的逗号！！！

当我们在输入函数参数时，如果觉得手动输入参数之间的逗号很麻烦，可以直接使用 Ctrl 点击左键来快速完成。

具体方法是：按住Ctrl键不放，点击单元格，Excel会自动输入逗号：

8、公式提示窗口遮住了单元格内容肿么办？

有时候输入公式时会发现公式的提示窗口会挡住右边的单元格，导致你看不到右边单元格的内容。可以把鼠标移动到提示窗口的边缘，直到出现“上下左右都是箭头”的符号时，拖动到其他位置，或者是在上方的公式栏内进行编辑：

9、快速显示工作表中的所有公式

直接使用快捷键 Ctrl ~

10、一次性选中工作表中的所有公式

另一种显示所有公式的方法就是用快捷键Ctrl G，进入“定位”对话框窗口，可以显示空白单元格、包含数字的单元格等等。点击“定位”窗口中的“定位条件”，选择“公式”，勾选所有，点击“确定”，所有包含有公式的单元格就会被选中；还能定位特定区域内的公式单元格，先选中要查找的区域，仍然是按Ctrl G：

11、用Ctrl Enter一次性向多个单元格输入同一个公式

比如，如果你要对同一列的多个单元格输入同一个公式，先选中该列的所有单元格，然后进入左上角的公示栏中输入公式，输完后，按Ctrl Enter键，就会向其余单元格填充同一个公式：

修改公式也是同理：选中所有单元格，修改其中一个公式后，按Ctrl Enter，其余单元格的公式也会同步被修改：

12、用Alt = 自动求和

在空白单元格中输入快捷键“Alt =”（mac系统是Command Shift T），Excel会自动猜测你要求和的单元格，并自动插入sum函数，直接按下Enter键就ok了；如果不是你想要的，可以重新选择范围：

还能对多行和多列单独进行求和。

对于多行：选择右边同一列的多个空白单元格，直接输入Alt =

对于多列：选择下方同一行的多个空白单元格，直接输入Alt =

最后，还能对多行以及多列同时求和、并将行和列的求和结果再次进行自动求和：

选中所有有数字的单元格，再多选中一行和一列，输入 Alt =：

13、使用“选择性粘贴”来修改单元格中的值

如果你的表格中有1000个产品的单价，你要对每个单价都上调10%，或者有1000个产品的生产日期，需要对所有日期都往前调个三五天，你会怎么做？

通常的做法：添加新的一列，计算出相应的结果，然后复制粘贴。

高级做法：使用“选择性粘贴”功能。

例子1）对批量日期统一往前调整5天

在任意一个空白单元格输入 5，然后按Ctrl C复制，再选中你要调整的所有日期，点击右键-选择性粘贴-选择性粘贴-运算-加，确定，如果日期变成了数字，再把格式设置为日期即可，那么所有的日期都往前调整了5天：

例子2）对所有价格上调10%，也用相同的办法

任意空白单元格输入1.1，复制，选中你要修改的所有价格，点击右键-选择性粘贴-选择性粘贴-运算-乘，确定，那么所有的价格都被乘以了1.1：

14、嵌套IF函数太多？用了Alt Enter后妈妈再也不用担心我看不懂函数了！！！

当嵌套的IF函数太多时，比如5个及以上，写在同一行时，往往很难阅读，很难分辨每个IF是跟哪两个值进行匹配的，我们可以使用快捷键Alt Enter换行来解决这个问题。

在上方的公示栏中点击IF的第一个参数后，会弹出“函数的提示窗口”，点击窗口中的“value_if_true”，会自动选中第二个参数，此时按下键盘上的“→”键将鼠标移动到右边，再按下Alt Enter，后面的IF语句就被换行了，用同样的方法完成剩余的IF函数：

15、如何编写更复杂公式

当你不知道如何写一个更复杂的公式时，可以先试着一步一步的完成：

先输入函数名字，然后用一些常量值或者硬编码，再用逻辑值把常量值替换掉。

举个例子，如果你要从一个人的名字中提取出他的姓氏，你知道要用Left函数，从左边把文字提取出来，但是不知道要提取几个文字，那么先把函数完整的输出来，比如 left(名字, 3)，然后再想办法把这个数字3替换成真实的值，那么你就知道下一步要用find函数来完成，返回第一个空格的位置，那么整个函数就完成了。

16、多用“公式提示窗口”来高亮参数

每次在使用公式时，都要记得Excel提供的“公式提示窗口”，如果你的公式很长很复杂，或者包含了很多嵌套的括号，这个提示窗口能帮你节省非常多的时间！如何使用？

首先，点击公式栏中你想选的那个参数值，然后Excel会在下方弹出一个公式提示窗口，显示了包含参数的完整函数；然后选择这个窗口中的参数，Excel会自动帮你高亮对应的参数值，是不是觉得很方便？

17、如何自动更新单元格中的部分数据？

如果你的Excel中有这样的数据：比如一杯咖啡的价格是10块钱，写的是“咖啡￥10”，结果后来你的老板说需要涨价到15块，还有其他各种奶茶、柠檬水等等都要涨价，而且每一款饮料涨的价各不相同，可以使用text函数来动态更新这个价格：

18、用 Ctrl Shift A一次性插入参数占位符

如果你所输入的函数参数个数太多，随着你的输入，常常忘记余下要输入的参数，可以在输入函数名字后，用快捷键Ctrl Shift A让Excel自动帮你填充所有的参数，然后再逐个修改成你想要的值：

19、用“选择性粘贴”将公式转换成常量值

假如你有这样一个需求：你的表格太复杂了，需要删除几列，但是这几列是被用来计算一个公式的，那么正常情况下你删除了该列之后会出现“#REF!”的错误，因为你把公式所引用的值给删除了，我们可以使用“选择性粘贴”来将公式的值转化成常量数据，然后再删除多余的列：

先复制你的公式单元格，然后按快捷键 Ctrl Alt V（MAC系统是Ctrl Cmd V）打开“选择性粘贴”对话框，选择“粘贴”-“数值”，然后确定，公式就被替换成了计算之后的值：

20、使用“命名范围”让公式更易于理解

我们都知道Excel的每一个单元格都是一个地址，比如A1、B2等等，当我们输入公式后，公式仍然显示的是比如 =A1*B2，如果数据量很大，往往不知道每个单元格所表示的含义，那么我们可以用“命名范围”来提升公式的可读性。

比如你要计算员工的月薪，A列是员工当月的出勤天数，B列是日薪，那么你的公式就是 A2*B2；选中单元格B2，在左上角的“名称框”中输入“日薪”，再按下Enter键，那么就为C2起了一个名字，再重新输入公式，公式就会变成 =A2*日薪，甚至还可以将A2改成“天数”，那么公式就变成了 =天数*日薪，是不是一看就明白了这个单元格的含义呢？