表格常用公式大全(表格公式怎么设置)

魁首哥

作者

在日常工作中，我们经常用EXCEL函数处理表册，函数的运用，大幅度提升了我们的工作效率。笔者集合工作实际，把最常用的EXCEL函数进行逐一详细讲解，课程中有大量案例，为便于朋友们更加深入了解各个函数的用法，将于每天上午7:00同步发布视频教程和图文教程（包含公式）。只要不懈努力和不断实践，通过30天的系统学习，你也能成为EXCEL函数高手，从此告别加班，让同事和朋友刮目相看。

今天，我将和大家一起分享学好EXCEL函数和公式必须学会的基础知识和操作，涉及的知识点有点多，如果有恒心和毅力想学好EXCEL函数和公式，请耐心往下看；如果你只是想随便看看，抱着无所谓的态度，那请止步，首先你的学习态度就不端正，谈何学好？我花费大量时间整理学习资料，把每个知识点做成实例进行讲解，自己从中又巩固了一遍，进步不是一点点，要说学习感受，真的非常辛苦。

俗话说得好：“基础不牢，地动山摇”“不积跬步，无以至千里；不积小流，无以成江海”，只有把基础打好了、打牢了，一点一点积累经验，才会在以后的学习过程中得心应手。真心希望点进来的朋友抱着必学会的态度认真学习，反复练习，对课程中的知识点要学会举一反三进行利用，学会变通。学无止境，只有学到手的东西才是自己的，只有自己学会了，才会在工作中如鱼得水，在职场上如虎添翼，否则，神马都是浮云。

说得有点多，戳到痛点的朋友请多谅解，这也说明你有学习的态度和决心。

下面，我们进行今天的课程。

本套课程以WPS2019版的EXCEL进行讲解，其实，在一般用户使用过程中，WPS和OFFICE基本上没太大差别，甚至WPS更加人性化，但是在一些专业领域，WPS处理EXCEL表格的某些复杂函数可能就不如微软的OFFICE。

一、什么是EXCEL工作簿、工作表、单元格

（一）工作簿

所谓工作簿，是指EXCEL环境中用来储存并处理工作数据的文件。也就是说，EXCEL文档就是工作簿，它是EXCEL工作区中一个或多个工作表的集合，其扩展名有XLS、XLSX、XLSM、XLSB等。每一本工作簿可以拥有许多不同的工作表，工作簿中最多可建立255个工作表。

（二）工作表

工作表是显示在工作簿窗口中的表格，在后续新版本的EXCEL中，一个工作表可以由1048576行和256列构成，行的编号从1到1048576，列的编号依次用字母A、B……XFD表示，行号显示在工作簿窗口的左边，列号显示在工作簿窗口的上边。

EXCEL默认一个工作簿有三个工作表，用户可以根据需要添加工作表，但每一个工作簿中的工作表个数受可用内存的限制，当前的主流配置已经能轻松建立超过255个工作表了。

每个工作表有一个名字，工作表名字显示在工作表标签上。工作表标签显示了系统默认的前三个工作表名：Sheet1、Sheet2、Sheet3。其中白色的工作表标签表示活动工作表。单击某个工作表标签，可以选择该工作表为活动工作表。

工作薄中的每一张表格称为工作表。工作薄如同活页夹，工作表如同其中的一张张活页纸。工作表是EXCEL存储和处理数据最重要的部分，其中包含排列成行和列的单元格，它是工作簿的一部分，也称电子表格。可以同时在多张工作表上输入并编辑数据，并且可以对来自不同工作表的数据进行汇总计算。

（三）单元格

单元格是表格中行与列的交叉部分，它是组成表格的最小单位，可拆分或者合并。单个数据的输入和修改都是在单元格中进行的。

单元格按所在的行列位置来命名，例如：地址“A1”指的是“A”列与第1行交叉位置上的单元格；地址“A1:D8”指的是单元格A1到D8之间的区域。

二、什么是函数、公式和运算符

（一）EXCEL公式

EXCEL中，公式是对EXCEL工作表中的值进行计算的等式，在EXCEL中可以使用常量和算术运算符创建简单的公式。

公式总是以“=”开始，然后将各种计算数据（单元格地址、名称、常量、函数等）使用不同的运算符连接起来，从而有目的地完成某种数据结果的计算。

例如：=A1+B1+C1就是一个简单的公式。

（二）EXCEL函数

EXCEL中，函数实际上是一个预先定义的特定计算公式，按照这个特定的计算公式对一个或多个参数进行计算，并得出一个或多个计算结果，叫做函数值。使用这些函数不仅可以完成许多复杂的计算，而且还可以简化公式的繁杂程度。

例如：SUM(A1:A8)就是一个简单的函数。

每个函数都有唯一的名称，并且不区分大小写，它决定了函数的功能和用途。在函数中，括号是成对出现的，它是函数的标识。

要想让函数起作用，必须在公式中使用，直接在函数前添加“=”，是函数最简单的应用。因此，从广义上讲，函数是一种特殊的公式。

（三）运算符

运算符是公式的基本要素，利用它可对公式中的参数进行特定类型的运算，它是影响数据计算结果的重要因素之一。

在EXCEL中，运算符包括：算术运算符、比较运算符、文本连接运算符和引用运算符等4种类型。

1.算术运算符：算术运算符主要用于对数据进行各种数学运算，各种算术运算符的具体作用和实例如下：

提示：“+”和“-”运算符的特殊用法：“+”和“-”运算符既可以连接两个参数，也可以连接一个参数，当只连接一个参数时，用于标识数据的正负数，例如：“+8”表达式表示正数8，“-8”表达式表示负数8。

2.比较运算符：比较运算符主要用于比较两个不同数据的值，当等式成立，则结果返回逻辑值TRUE；如果等式不成立，则结果返回逻辑值FALSE。各种比较运算符的具体作用和实例如下：

3.文本运算符：在EXCEL中，文本运算符只有一个，即和号（&），通常被称为文本串联符或文本连接符，其具体作用和实例如下：

4.引用运算符：引用运算符主要用于对指定的单元格区域进行合并计算，在EXCEL中，引用运算符只有两个，即“:”和“,”，其具体作用和实例如下：

通过以上学习，我们已经了解几种运算符的作用，那么运算顺序是怎样的呢？

在使用公式计算数据时，单纯的一个运算符的数据计算比较少，通常都是同时使用多个运算符，此时就有必要了解计算过程中的运算顺序，这样才能知道数据的计算结果是什么。

在EXCEL中，系统遵循从高到低的顺序进行计算，相同优先级的运算符，遵循从左到右的原则进行计算。

引用运算符>算术运算符>文本连接运算符>比较运算符

对于算术运算符来说，同级运算符的优先顺序为：负数→百分比→乘方→乘和除→加和减。

如果要改变运算符的优先顺序，可以使用括号，而且在Excel中允许括号嵌套使用，系统将按照先内后外的顺序优先处理最内部括号的内容，然后依次向外扩展。

例如：

要计算((3+2)*5-1)/6，计算过程为：

第一步，计算嵌套括号中的3+2，结果为(5*5-1)/6

第二步，在括号中先计算5*5，结果为(25-1)/6

第三步，计算括号中的25-1，结果为24/6

第四步，计算24/6，结果为4

三、EXCEL中的引用方式

（一）数据的引用方式

在EXCEL中，数据的引用方式有三种，分别是相对引用、绝对引用和混合引用，各种引用方式的说明如下：

在公式中，如果要快速在各种引用方式之间进行切换，可以选择单元格地址或者将文本插入点定位到单元格地址的前、中、后，然后按键盘上的【F4】键切换。

例如：在公式“=A1”中，将文本插入点定位到“A”和“1”之间（或在“A”的前面，或在“1”的后面），连续按键盘上的【F4】键的顺序效果如下：

第一次按【F4】键，结果为=$A$1

第二次按【F4】键，结果为=A$1

第三次按【F4】键，结果为=$A1

第四次按【F4】键，结果为=A1

无论单元格的初始状态为哪种引用，按【F4】键后都会按如上顺序变化，如初始状态为上面第二次按【F4】键的效果，在A1单元格地址中按【F4】键后变为如上第三次按【F4】键的效果，而不是如上第一次按【F4】键的效果。

知识拓展：

复制$A$1到其他单元格，引用的单元格永远是A1单元格。

复制A$1，往右复制时，会变为B$1、C$1，以此类推；往下复制时，会固定在A1单元格。

复制$A1，往右复制时，会固定在A1单元格；往下复制时，会变为$A2、$A3，以此类推。

复制A1，往右复制时，会变为B1、C1，以此类推；往下复制时，会变为A2、A3，以此类推。

（二）同一工作簿跨表引用

在使用公式和函数计算数据时，有时候会引用当前工作簿的其他工作表的数据，这时就涉及到跨表引用。

在同一工作簿的不同工作表中引用单元格或单元格区域，其引用格式为：“工作表名称!单元格地址”，这里的单元格地址引用也可以包括相对引用、绝对引用和混合引用。

例如：“工作表!A1”或“工作表!A1:D1”

除了按照引用格式直接手动输入公式外，还可以通过选择单元格的方式快速生成引用，具体操作步骤如下：

第一，打开文件，切换到“个人基本信息”工作表，在B2单元格中输入“=”，如下图：

第二，选择“信息汇总表”工作表，用鼠标点击A3单元格，然后按回车键，即可引用该单元格的数据，见下图：

返回“个人基本信息”工作表，我们发现，这时候已经把数据引用过来了，见下图：

提示：如果跨表引用的工作表的名称以数字、空格、特殊符号（如$、%、#、~、!、+、-、@、=等）开头，则在公式中引用工作表名称时，需要使用一对半角单引号引起来，如：“1月会员费!A1”。

（三）跨工作簿引用

在EXCEL中，除了在同一个工作簿中引用数据以外，使用公式计算数据时，还经常会跨工作簿引用数据，具体的引用格式为：“=[工作簿名称]工作表名称!单元格地址”。在跨表、跨工作簿引用数据时，有两种方法，具体如下：

1.选择工作表数据。如果当前在EXCEL中同时打开了被引用的工作簿，可以通过直接选择工作表中的数据单元格完成引用，具体操作方法与上面讲的方法类似。

2.手动输入引用。如果被引用的工作簿当前没有打开，只能通过手动输入的方式完成引用，需要注意的是，在输入引用时，必须包含完整的文件路径，并且要用半角单引号将文件路径、工作簿和工作簿名称引起来。

例如：='[工作簿名称]工作表名称!单元格地址’

四、逻辑值和常用的逻辑函数

（一）逻辑值

在EXCEL中，逻辑值有两个，分别是TRUE和FALSE，主要作用是对某条件判定是否成立，成立则为真（TRUE），不成立则为假（FALSE）。

例如：在单元格中输入”=5+5=10″（输入时不包括引号），按回车键后显示结果为TRUE，5+5=10是正确的，即真值，说明等式成立；如果在单元格中输入”=5+5=8″，则结果显示FALSE，计算结果不正确，说明等式不成立。

1.逻辑值的转换规则

在EXCEL公式计算时，逻辑值可以用相应的数值代替，但是，在不同的运算中有不同的要求，逻辑值的转换规则有三种，具体如下：

（1）在四则运算中的转换规则

在四则运算中，通常用1代替TRUE，用0代替FALSE。

例如：

公式“=1*TRUE”，结果为1

公式“=1+TRUE”，结果为2

公式“=1*FALSE”，结果为0

公式“=1+FALSE”，结果为1

（2）在逻辑运算中的转换规则

在逻辑运算中，通常用非零值（不是0的任何数值）代替TRUE，用0代替FALSE。

例如：

公式“=IF(1,”正确”,”错误”)”，结果为：正确

公式“=IF(-1,”正确”,”错误”)”，结果为：正确

公式“=IF(0.1,”正确”,”错误”)”，结果为：正确

公式“=IF(0,”正确”,”错误”)”，结果为：错误

（3）在比较运算中的转换规则

在比较运算中，逻辑值与数值和文本之间存在一定的关系，即：“数值<文本<FALSE<TRUE”。

例如：

公式“=TRUE>2”，结果为：TRUE

公式“=TRUE<2”，结果为：FALSE

公式“=FALSE=2”，结果为：FALSE

公式“=FALSE>2”，结果为：TRUE

以上三条转换规则，对编写公式和优化公式运算有很大的作用，在以后的课程中你将会体会得到。

2.逻辑值在EXCEL中的运用案例：根据性别计算退休年龄

我们在填写职工信息表时，需要计算职工的退休年龄，不同性别的职工，其退休年龄不同，男性职工的退休年龄为60岁，女性职工的退休年龄为55岁，现在需要根据B3单元格中职工的性别自动填写退休年龄。见下图：

B3单元格中的性别为“女”，则C3单元格计算退休年龄的公式过程如下：

公式：=60-(B3=”女”)*5

第一步，引用B3单元格的值，结果为：60-(“女”=”女”)*5

第二步，先计算出括号中的值(“女”=”女”)，逻辑值为TRUE，结果为：60-(TRUE)*5

第三步，根据转换规则，将TRUE转换为1，结果为：60-1*5

第四步，计算1*5，结果为：60-5

第五步，计算60-5，结果为：55

最终结果和公式见下图：

本例是利用逻辑值计算不同性别职工的退休年龄，只要理解了，不用其他函数也可以计算。当然，利用逻辑值计算类似的数据，一般只针对简单的运算，复杂的公式需要用函数处理，后续课程中会针对此类运算作详细讲解。

（二）常用的逻辑函数

在公式计算中，对于逻辑判断，我们通常会使用逻辑函数OR（“或者”）和AND（“与”或“并且”）进行运算。

其中，AND函数是“与”或“并且”的意思，即所有的条件都满足时返回TRUE，只要任意一个条件不满足则返回FALSE；OR函数是“或者”的意思，即在多条件时，满足任意一个条件或所有条件都满足返回TRUE，如果一个条件都不满足则返回FALSE。

在复杂的公式中，为了简化公式，我们可以用数学运算代替相应的逻辑函数，加法运算代替OR，乘法运算代替AND。

1.用加法运算代替逻辑函数OR

例如：要判断只有局长和副局长才有审批权限，否则无法审批，此时需要用条件判断函数IF和逻辑函数OR进行运算。见下图：

2.用乘法运算代替逻辑函数AND

例如：要判断学生成绩考试语文、数学、英语分数都大于等于60才算及格，否则不及格，此时需要用条件判断函数IF和逻辑函数AND进行运算。见下图：

当然，以上只是举例，并不是说只要一个科目不及格就统统不及格，大家不要纠结。

五、空单元格和空文本的区别

在EXCEL中，默认状态下，单元格中不显示任何内容，或将有内容的单元格清空后，此时的单元格即为空单元格。而空文本则是使用一对半角双引号（””）来表示。

如果某单元格的数据是通过公式引用的空文本，即单元格的值为“=”””，此时，该单元格的显示效果和空单元格的显示效果是一样的，不过，二者在公式运用中有明显的区别。

（一）两者的区别

1.从公式的角度看，空单元格和空文本等同于一样

例如：A1单元格为空单元格，A2单元格为空文本，在A3单元格中输入公式“=A1=A2”，计算结果为TRUE。

2.空单元格的值视为0

例如：A1单元格为空单元格，在A2单元格中输入公式“=A1=0”，计算结果为TRUE；在A2单元格中输入公式“=A1*2”，计算结果为0。

3.空文本的值不能视为0

例如：A1单元格为空文本，在A2单元格中输入公式“=A1=0”，计算结果为FALSE；在A1单元格中输入公式“=A1*2”，计算结果为#VALUE错误值。

（二）屏蔽空单元格显示0的方法

在EXCEL中，如果公式的结果是对某个空单元格的引用，公式的计算结果不是空文本，而是数值0，在某些情况下，数值0和空单元格还是有区别的。

例如：下图所示的是学生的语数外考试成绩，学生“姓名三”的数学考试成绩为空，我们在对该名学生的数据进行查询时，在数学列显示为0，这个0值代表该生的数学成绩为0，还是缺考或者漏录成绩呢？

为了区别这两种情况，避免公式结果引用空单元格时公式结果显示0的情况，可以在公式末尾使用&运算符强制将引用空单元格的结果转换为空文本。如下图：

此时，程序虽然查到了该生的数学成绩单元格为空单元格，但没有显示0值，而是显示空文本。

六、处理数据计算的几种方法

（一）计算公式结果

输入公式结束后，可通过多种方法计算公式结果，如点击编辑栏中的“√”（即“输入”按钮）显示公式结果。

此外，还可以通过按快捷键的方式计算公式结果，这是最便捷、快速的计算公式结果的方法，具体快捷键及计算效果如下：

1.【Enter】键：输入公式后，按【Enter】键可在计算出结果的同时选择该单元格下方的单元格。

2.【Tab】键：输入公式后，按【Tab】键可在计算出结果的同时选择该单元格右侧的单元格。

3.【Ctrl+Enter】组合键：输入公式后，按【Ctrl+Enter】组合键，在计算出结果的同时仍然保持该单元格的选中状态，该方法可同时查看数据结果和所使用的公式。

4.【Ctrl+Shift+Enter】组合键：如果是数组公式，要计算出结果，必须在输入公式结束后按【Ctrl+Shift+Enter】组合键完成，以上3种方法都不能正确计算公式结果。数组公式不能在合并单元格使用。

（二）使用【F9】键将公式转换为运算结果

【F9】键在公式应用中具有公式重算的功能，如果将文本插入点定位到公式中，按【F9】键后系统自动将整个公式转换为运算结果。

例如：未按【F9】键前显示的是公式。见下图：

当我按下【F9】键后，直接显示公式结果。见下图：

提示：在使用【F9】键将公式转换为结果后，原来的公式将被替换，如果想改回公式，可以通过撤销操作将结果还原为公式。

（三）批量输入和复制公式

1.拖动控制柄复制公式

在EXCEL中，选择单元格后，其右下角有一个实心的小方块，我们将其称之为控制柄，将鼠标光标移动到控制柄上时，鼠标光标会变成黑色十字形状，通过拖动该控制柄到需要的位置，可完成公式的批量复制。

拖动控制柄批量复制公式后，在结束位置将出现“自动填充选项”按钮，单击该按钮，在弹出的列表中包括“复制单元格”“仅填充格式”“不带格式填充”和“智能填充”4个单选按钮。见下图：

在公式运用中，这4个选项的作用如下：

“复制单元格”：选择该选项，程序自动将源单元格的公式和单元格格式全部填充到其他单元格。

“仅填充格式”：选择该选项，程序只将源单元格的格式填充到其他单元格，而不复制源单元格的公式。

“不带格式填充”：选择该选项，程序自动将源单元格的公式填充到其他单元格，而不复制源单元格的格式。

“智能填充”：快捷键【Ctrl+E】，是一个相当不错的快捷键，可以批量提取身份证号码中的出生日期，从姓名手机号中提取手机号，轻松合并多列数据等。

2.双击控制柄复制公式

双击控制柄批量复制公式比拖动控制柄复制更加快捷，只需要双击包含公式的单元格的控制柄，程序自动向下填充公式到整个表格的结束位置。见下图：

双击后的最终结果见下图：

需要注意的是，双击控制柄完成公式的批量复制时，对数据源的表格结构有一定的条件限制，具体如下：

第一，需要批量复制公式的单元格是在某列单元格上向下填充，不能在某列向上填充，也不能在某行上向右填充。

第二，批量复制公式的单元格相邻列必须有数据，否则双击控制柄无效。

3.用【Ctrl+Enter】组合键批量输入公式

在EXCEL中，如果某一列数据的计算公式相似，那么可以用【Ctrl+Enter】组合键批量录入公式。首先选择所有的结果单元格，输入公式后，按【Ctrl+Enter】组合键即可快速录入选中单元格的公式结果。

例如：要计算每位同学的考试总成绩，首先选择总分列需要计算总分的E3:E8区域，输入公式“=SUM(B3:D3)”，见下图：

然后按【Ctrl+Enter】组合键即可得出所有学生的总分，如下图：

4.用【Ctrl+D】和【Ctrl+R】组合键批量复制公式

我们在处理表册数据时，遇到内容相同的单元格，往往采用复制【Ctrl+C】、粘贴【Ctrl+V】的办法进行，需要按两次按键，虽然省去了输入，但还是不够快，对于需要向下复制公式的时候，我们可以使用【Ctrl+D】组合键。这个功能其实很实用，因为一般我们都是从上往下填充数据，如果这个单元格的内容同上，就可以直接按【Ctrl+D】组合键。

例如：我在E3单元格中输入的公式，需要把公式向下填充到数据表中的最后一个单元格，只需要选择该单元格和最后一个单元格的区域，然后按【Ctrl+D】组合键即可完成复制。见下图：