在日常工作中，我们经常用EXCEL函数处理表册，函数的运用，大幅度提升了我们的工作效率。笔者集合工作实际，把最常用的EXCEL函数进行逐一详细讲解，课程中有大量案例，为便于朋友们更加深入了解各个函数的用法，将于每天上午7:00同步发布视频教程和图文教程（包含公式）。只要不懈努力和不断实践，通过30天的系统学习，你也能成为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）在比较运算中的转换规则
在比较运算中，逻辑值与数值和文本之间存在一定的关系，即：“数值<文本 公式“=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】组合键即可完成复制。见下图： 与【Ctrl+D】用法类似的，还有【Ctrl+R】，两者的区别在于，【Ctrl+D】向下填充，【Ctrl+R】向右填充。 相邻列填充时，只要选中需要填充的单元格，按【Ctrl+R】组合键，你会发现，已经把相邻的左侧列的数据复制过来了，无需选择源数据。 间隔列填充时，需要同时选中数据源和需要填充的单元格，这是新版本中的“隔空填充”。例如：我要把D列的英语成绩复制到新的列，同时选中D列和G列的数据区域，然后按【Ctrl+R】组合键即可完成复制。见下图： 5.使用复制粘贴功能批量复制公式 在EXCEL中，软件提供了“选择性粘贴”功能，粘贴时设置粘贴选项为粘贴公式后，软件只将源单元格的公式批量粘贴到其他选中的单元格。 例如我在E3单元格中输入了公式，需要把公式复制到数据表中的其他空白单元格，首先复制E3单元格内容，然后选择需要填充公式的其他单元格，在“开始”选项卡下左侧“粘贴”两个字，在弹出的列表中选择红框中“公式”，即可完成公式的填充。见下图： 七、充分利用函数屏幕提示功能 （一）提示函数的功能和语法结构 在最近几个版本的EXCEL中，程序提供了屏幕提示功能，默认情况下，该功能是启用的，对我们学习函数提供了很好的作用。 在单元格或编辑栏中输入等号后，在输入函数名称过程中，会自动提示以你输入的内容为开头的多组函数，鼠标点击或按向上向下的箭头可以查看所提示函数的功能，双击需要的函数或按回车键即可录入单元格。见下图： 在单元格或编辑栏中输入等号、函数名称和左括号这3部分内容后，在弹出的屏幕提示信息中将会自动显示该函数对应的语法结构。见下图： 从上图可以看出，RANDBETWEEN函数共有2个参数。 除此之外，在屏幕提示中，还会智能地提示当前正在编辑的参数，文本插入点定位到哪个参数，当前参数在语法结构中会以加粗的字体格式显示。见下图： 输入公式后，如果你想查看参数对应表达式，只需要点击屏幕提示的参数名称，然后在单元格和编辑栏中自动选中对应的表达式。见下图： （二）快速获取函数的帮助信息 如上图，输入函数后，在屏幕提示中单击函数名称超链接，即可打开SUMIF函数的帮助信息。如下图： 以上就是学习EXCEL函数、公式需要掌握的基本知识和操作，想要学习好EXCEL函数，最基本的操作要熟记于心，通过后续大量的函数不断实践，学会多个函数的组合，后续课程中我们会讲到多函数结合的用法，能进行更为复杂的计算，对提升工作效率有很大帮助。 个人建议在学习EXCEL函数时，首先要熟悉函数的功能和语法，尽量多写，通过反复书写和实践，对照文中的案例，举一反三，我相信，你的EXCEL技巧会得到快速提升，日积月累，必成大器！ 一、SUM函数基础知识 SUM函数定义返回几个单元格值、某一单元格区域中数字、逻辑值及数字的文本表达式之和，或者将三者的组合相加，可以是连续和非连续区域求和。 语法SUM(参数1,参数2,……参数N) 参数1是指要相加的第一个数字，为必需参数，该参数可以是数字，或Excel中A1、A2之类的单元格引用值或A1:A5之类的单元格范围。 参数2是指要相加的第二个数字。 参数N是指要相加的第N个数字，N不超过255，但一般情况下不会出现这种用法。 注意 ——逻辑值（TRUE为1，FASLE为0）及数字的文本表达式将被计算。 例如在单元格输入"=SUM(1,5+5=10,3)"（输入时不包括引号），显示结果为5，因为5+5=10显示逻辑值TRUE，也就是1，参加计算，所以结果为1+1+3=5。 ——如果参数为数组或引用，只有其中的数字被计算，数组或引用中的空白单元格、逻辑值、文本将被忽略。 例如在B1单元格输入"=SUM(1,A1,3)"（输入时不包括引号），A1单元格的内容“=5+5=10”为逻辑值TRUE，显示结果为4，因为A1单元格为引用中的逻辑值，不参与计算，所以结果为1+3=4。 ——如果参数中有错误值或为不能转换成数字的文本，将会出现错误值“#VALUE!”。 二、SUM函数案例实践 清楚了SUM函数的定义和语法，下面，我们进行几个案例解析。 （一）相连区域求和 我们在计算某个单元格区域的数值总和时，对于相连的区域求和很简单，只需要用函数=SUM(单元格区域)即可快速计算。 案例计算“数值1”到“数值4”所有数值的总和。 输入公式：=SUM(A3:D7)，按回车键即可计算出A3到D7所有数字的总和。见下图： 朋友们是不是感觉很熟悉？对，这就是极大部分人对SUM函数最多的用法，操作很简单。 （二）快捷键快速求和 当我们在辛辛苦苦录入=SUM()时，别人已经计算出了多行合计数，并且只需要按一下键盘上的组合键。 案例计算“数值1”到“数值3”每行数值的总和。 选择A3到D7区域，按键盘【ALT+=】组合键即可快速求和，合计列已经自动填充了SUM函数公式。效果见下图： （三）不相连的多个单元格求和 对于不相连单元格求和，大部分人的做法是这样的：=A1+C1+F1……其实，我们用SUM函数也可以做到。 案例计算A4,B6,C5,D3几个单元格的总和。 首先输入=SUM()，把光标定位到括号内，先点击A4单元格，然后按住Ctrl键不放，依次点击B6、C5和D3就可以了，不用手动输入逗号，多个不相连单元格依次点击，直到点击完最后一个要计算的单元格后放开Ctrl键，按回车键后就计算出了刚才选择的单元格数值总和。见下图： （四）不相连多个区域求和 对于不相连的多个区域求和，其实和不相连的单元格的求和方式是一样的，只是前者是单元格区域，后者是单个单元格。 案例计算A列和D列的数值总和。 在单元格输入公式：=SUM(A3:A7,C3:C7)，按回车即可计算出A3:A7和C3:C7的数值总和。和选择单个单元格操作一样，拖动选择A3:A7区域后，按住Ctrl键不放，在选择C3:C7，同样自动添加逗号，要是不按住Ctrl键或不输入逗号，你选择下一个区域时自动替换上一次选择的区域。见下图： （五）单条件求和 相信多半人看到标题就懵了，SUM也能用条件进行求和？答案是肯定的。 案例1指定数值范围求和。 对下图中A3:A7区域小于5的单元格进行求和，输入公式：=SUM((A3:A7<5)*A3:A7)，重点来了，同时按【Ctrl+Shift+Enter】三键组合，这样就计算出了A3:A7区域所有小于5的数值总和。你会发现，刚才输入的公式自动被{}括住，因为这是数组公式。 本例中使用SUM()函数进行条件求和的核心是运用“*”运算符挑选需要进行求和的数据，然后将不符合条件的数据转换为0。“数据*TRUE”返回数据，“数据*FALSE”返回0。 本例中，使用A3:A7<5表达式可以返回指定范围中所有小于5的逻辑值数组{FALSE,TRUE,TRUE,TRUE,FALSE}，然后与A3:A7单元格区域中的数据进行“*”运算，返回数组值{7,3,4,3,5}（上图A3到A7区域的值），最后使用SUM()函数对数组中小于5的数据3,4,3进行求和。 案例2指定特定条件求和。 对下图“地区一”中“数值1”数据进行求和，在单元格中输入公式：=SUM((A3:A7="地区一")*B3:B7)，同时按【Ctrl+Shift+Enter】三键组合，这样就计算出了“地区一”中“数值1”的所有数值总和。见下图： 以上两个案例多数人没运用过，条件求和一般运用SUMIF、SUMIFS和SUMPRODUCT等函数进行计算，后续课程我们会讲到。 （六）多条件求和 在SUM中利用多条件求和，用法和单条件求和类似，用“*”连接 案例指定多个数值范围求和。 对下图中A3:A7区域大于5小于10的数据进行求和，输入公式：=SUM((A3:A7>5)*(A3:A7<10)*A3:A7)，同时按【Ctrl+Shift+Enter】三键组合，这样就计算出了A3:A7区域所有大于5小于10的数值总和。见下图： （七）SUM和LARGE函数配合计算成绩在前三名的总分 某学校在一次考试中，需要对年级前三名学生的成绩进行总分汇总。 本例属于对符合指定条件的数据进行求和，在不排序的情况下，首先需要使用LARGE函数提取排在前三名学生的总分，然后用SUM函数把前三名每名学生的总分进行求和计算。 对下图中E3:E12区域总分排在前三名的成绩进行提取汇总，在D13单元格输入公式：=SUM(LARGE(E3:E12,{1,2,3}))，回车后即可得出计算结果为764。见下图： 本例中使用了常量数组“{1,2,3}”作为LARGE函数的第二个参数得到排名前三的总分。如果需要提取的数据比较多的时候，再使用常量数组就显得很麻烦，这个时候就要考虑使用ROW函数来完成这个操作。 例如计算排名前五名或者更多名次的总分，我们以前五名为例，把ROW(1:5)作为LARGE的第二参数进行运算。在E13单元格输入公式：=SUM(LARGE(E3:E12,ROW(1:5)))，同时按【Ctrl+Shift+Enter】三键组合，得出排名前五名的总分。见下图： 提示此例要计算的是排名前五名的成绩，属于数组，所以要按【Ctrl+Shift+Enter】三键组合，否则结果为从E3单元格往下数的第五个数，显示的是E7单元格的结果264。 以上就是SUM函数单独进行求和时的几个经典案例，想要学习好EXCEL函数，要学会多个函数的组合，后续课程中我们会讲到多函数结合的用法，能进行更为复杂的计算，对提升工作效率有很大帮助。 一、SUMIF函数基础知识 SUMIF函数定义SUMIF函数是EXCEL的常用函数，使用SUMIF函数可以对区域中符合指定条件的值求和，经常应用在人事、工资和成绩统计中。 语法SUMIF(条件区域,求和条件,求和区域) 条件区域必需参数，是指用于条件判断的单元格区域，区域内的单元格必须是数字或名称、数组或包含数字的引用，空白和文本值将被忽略。 求和条件必需参数，是指由数字、逻辑表达式等组成的判定条件，可包括通配符问号（）匹配任意单个字符，星号（*）匹配任意字符序列。如果要查找实际的问号或星号，请在该字符前键入波形符（~）。 求和区域可选参数，是指需要求和的单元格、区域或引用。如果省略该参数，EXCEL就会对“条件区域”中指定的单元格进行求和。该参数的大小和形状应该与“条件区域”范围相同。 例如指定的“条件区域”为A1:A8，指定的“求和区域”为B1:B8，实际“求和区域”为B1:B8；如果指定的“条件区域”为A1:A8，指定的“求和区域”为B1:E8，实际“求和区域”为B1:B8。见下图： 二、SUMIF函数案例实践 清楚了SUMIF函数的定义和语法，下面，我们进行几个案例解析。 （一）计算书费在10元及以上的总和 某学校需要统计书费在10元及以上的总额，在D10单元格输入公式：=SUMIF(B3:B7,">=10")，按回车键即可计算出书费在10元及以上的总和。见下图： 使用SUMIF函数进行条件求和时，如果“条件区域”和“求和区域”完全相同，则可以省略“求和区域”，也就是说，如果“求和区域”被省略时，会直接对“条件区域”指定的单元格区域进行求和。 本例中如果不省略“求和区域”，其公式如下： =SUMIF(B3:B7,">=10",B3:B7) 本例也可使用SUM函数配合表达式来完成，上节课我们已经讲过，具体公式为：=SUM((B3:B7>=10)*B3:B7)，特别注意的是，该公式为数组公式，需同时按【Ctrl+Shift+Enter】三键组合，其结果不能在合并单元格中输出。 （二）汇总已收到的会员费总额 某部门需要收取会员费，每个人需交多少金额已经列出来，现需要汇总已收到的会员费总额。 在本例中，收取会员费有两种情况，“已交”和“未交”，要汇总已收到的会员费总额，就需要把所有标记为“已交”的金额求和，这是对满足一个条件的数据的求和，可以使用单条件函数SUMIF进行条件求和。 在D10单元格输入公式：=SUMIF(D3:D7,"已交",C3:C7)，按回车键，自动在当前单元格中汇总已收到的会员费总额。见下图： 使用SUMIF函数对数据区域进行有条件的求和，也可以使用SUM函数来完成，上节课我们已经讲过，具体公式为：=SUM((D3:D7="已交")*C3:C7)，同时按【Ctrl+Shift+Enter】三键组合得出结果，其结果不能在合并单元格中输出。 EXCEL中在对数组进行运算时，有时候为了计算需要，会自动扩充数组的大小，比如在本例中，就可以把SUMIF函数的“求和区域”简写为：=SUMIF(D3:D7,"已交",C3)，可以得到与简写前相同的计算结果。 （三）汇总总分在200分及以上的学生人数 某班的某次学生考试成绩，需要汇总成绩在200分及以上学生的人数。 本例中，汇总成绩在200分及以上的学生人数，这是一个有条件的计数问题，如果使用SUMIF函数，需要添加辅助列，把每个学生看做一个整体1。 在D10单元格输入公式：=SUMIF(E3:E7,">=200",F3:F7)，按回车键，自动在当前单元格中汇总总分在200及以上的学生人数。见下图： SUMIF函数有很大的局限性，“条件区域”和“求和区域”必须是单元格或单元格区域引用，不能是常量数组或其他形式的数组。因此，本例采用辅助列的方式来实现使用SUMIF函数计数，如果不使用辅助列而是直接输入常量数组或使用函数、表达式构成的数组，就会出现错误提示。如下图： 本例中，如果使用SUM函数和IF函数嵌套的数组公式，不需要辅助列也可以完成计算，公式为：=SUM(IF(L3:L7>=200,1))，同时按【Ctrl+Shift+Enter】三键组合得出结果。公式的意思是：用IF判断L3:L7区域大于等于200的数值，如果满足条件，则显示1，然后用SUM函数求出符合条件的个数和。见下图： 对于计数问题，虽然使用SUMIF、SUM等函数也能统计出结果，但是会相对麻烦，在实际工作中，我们一般使用COUNTIF、COUNT等函数进行个数统计，后续课程我们会详细讲解。 （四）计算啤酒库存总量 某酒吧对各类饮品库存量进行盘点，现需计算所有品牌啤酒的库存量。 本例中，需要统计“雪花啤酒”“大理啤酒”等多种品牌的总库存量，这是一个多条件求和的问题，可以使用SUMIF和SUM函数嵌套的形式得出库存量，但是这里包含了很多品牌的“啤酒”，因此，还需要使用通配符来查找各个品牌的“啤酒”库存量。 在E11单元格输入公式：=SUM(SUMIF(A3:A8,"*啤酒",C3:C8))，按回车即可计算出所有包含“啤酒”的库存总和。见下图： 公式=SUM(SUMIF(A3:A8,"*啤酒",C3:C8))的意思是，先用从A3:A8区域中，找出包含“啤酒”的单元格，在C3:C8中查找出包含“啤酒”的单个库存量，再用SUM嵌套计算出所有包含“啤酒”的库存量。 以上就是SUMIF函数进行单个条件求和以及配合上节课讲到的SUM函数求和时的几个经典案例，想要学习好EXCEL函数，要学会多个函数的组合，后续课程中我们会讲到多函数结合的用法，能进行更为复杂的计算，对提升工作效率有很大帮助。 一、SUMIFS函数基础知识 SUMIFS函数定义SUMIFS函数是一个数学与三角函数，用于计算其满足多个条件的全部参数的总和。使用该函数可快速对多个条件单元格求和，只有满足所有指定的条件时，才对该单元格求和。如果只是对符合其中任意一个条件就进行求和，则不能使用该函数，而是需要SUM和SUMIF函数嵌套的形式进行求和。 语法SUMIFS(求和区域,条件区域1,求和条件1,……条件区域N,求和条件N) 求和区域必需参数，是指需要求和的单元格、区域或引用。 条件区域1必需参数，与“求和条件1”设置用于搜索某个区域是否符合特定条件的搜索对。一旦在该区域中找到项，将计算“求和区域”中相应值的和。 求和条件1必需参数，定义将计算“条件区域1”中的哪些单元格的和的条件。例如：可以将条件输入为5、">5"、A5、"男"或"5"。 条件区域N、求和条件N附加的区域及其关联条件，最多可输入127个区域/条件对。 SUMIFS和SUMIF函数的区别 第一，SUMIFS和SUMIF的参数顺序有所不同。SUMIFS中的“求和区域”是第一个参数，而在SUMIF中，却是第三个参数，这是使用这些函数时出现问题的一个常见原因。 第二，SUMIF函数的参数可以简写，而SUMIFS函数的求和区域和条件区域大小必须一致，否则会出现错误。 如果要复制和编辑这些相似函数，请确保按正确的顺序放置参数。 二、SUMIFS函数案例实践 （一）计算售价10元以下、销售量100以上药品的总收入 某药店给出了月销售报表，其中有详细的统计数据，现需要计算售价10元以下、销售量100以上药品的总收入。 本例要计算售价10元以下、销售量100以上药品的总收入，是一个多条件求和的问题，可以使用SUMIFS函数进行计算。 在E11单元格输入公式：=SUMIFS(D3:D8,B3:B8,"<10",C3:C8,">100")，按回车键即可计算出售价10元以下、销售量100以上药品的总收入。见下图： 本例中，D3:D8是求和区域，是药品的销售价格合计；B3:B8,"<10"是第一对条件，是每种药品的单价；C3:C8,">100"是第二对条件，是每种药品的销售量。 （二）计算销售量100以上胶囊的总收入 跟上一个例子类似，某药店给出了月销售报表，其中有详细的统计数据，现需要计算销售量100以上胶囊的总收入，同样使用SUMIFS函数进行计算。 在E11单元格输入公式：=SUMIFS(D3:D8,A3:A8,"*胶囊*",C3:C8,">100")，按回车键即可计算出销售量100以上胶囊的总收入。见下图： 本例中，D3:D8是求和区域，是药品的销售价格合计；A3:A8,"*胶囊*"是第一对条件，是每种药品的单价，这里用到通配符“*”，“*胶囊*”表示只要包含“胶囊”的单元格都满足条件，如果换成“*胶囊”，表示只有以“胶囊”结尾的药品才满足条件；C3:C8,">100"是第二对条件，是每种药品的销售量。 提示如果要提取的条件中包含“*”符号，需要在其前面添加“～”表示其为符号，而不是通配符。比如：“～*”。 （三）统计多个科室参加会议迟到人数 某部门设置有多个科室，每个科室有多人，某次组织召开会议，要求全体干部职工参加，有些科室出现迟到的情况，需要统计科室一和科室二迟到的人数总和。 在D10单元格输入公式：=SUMIFS(C3:C7,A3:A7,"科室一")+SUMIFS(C3:C7,A3:A7,"科室四")，按回车键，自动在当前单元格中汇总两个科室迟到的人数。见下图： 本例的SUMIFS函数中，实际进行计算的是C3:C7单元格区域的数据，而A3:A7,"科室一"只是条件判断区域，表示在C3:C7单元格区域中查找科室一和科室四的数据。 SUMIFS函数的工作原理和SUMIF函数一样，都是对符合条件的数据进行求和，SUMIFS函数是对多个条件进行判断，然后进行求和；SUMIF函数是对单个条件进行判断，所以在一定意义上说，SUMIFS函数是SUMIF函数的延伸。 以上就是SUMIFS函数进行多条件求和的几个经典案例，想要学习好EXCEL函数，要学会多个函数的组合，后续课程中我们会讲到多函数结合的用法，能进行更为复杂的计算，对提升工作效率有很大帮助。 一、SUMPRODUCT函数基础知识 SUMPRODUCT函数定义SUMPRODUCT函数是在给定的几组数组中，将数组间对应的元素相乘，并将多组乘积进行求和。相当于将几个数组直接相乘，再使用SUM函数进行求和。数组参数必须具有相同的维数（即大小相同，如A3:B8,C3:D8），否则，函数SUMPRODUCT将返回错误值#VALUE!。SUMPRODUCT函数将非数值型的数组元素作为0处理。 语法SUMPRODUCT(数组1,数组2,……数组N) 数组1必需参数，是指相应元素需要进行相乘并求和的第一个数组参数。 数组2……数组N可选参数，2到255个数组参数，其相应元素需要进行相乘并求和。 例如要计算A、B列和C、D列的乘积之和，在E11单元格输入公式：=SUMPRODUCT(A3:B8,C3:D8)，即可得到对应乘积之和，对应的是A列乘C列，B列乘D列，再把所有乘积相加。见下图： 上图中，两个数组的所有元素对应相乘，然后把乘积相加，即9*2+9*7+5*5+8*10+10*2+6*8……4*8+9*2=395。SUMPRODUCT函数进行的运算是数组运算，因此在输入公式的时候可以直接以普通公式的形式输入，它的运算结果完全等同于数组公式。而使用直接相乘再使用SUM函数求和的方法，需要以数组公式的形式输入。 本例所返回的乘积之和，与以数组形式输入的公式=SUM(A3:B8*C3:D8)的计算结果相同。 二、SUMPRODUCT函数案例实践 （一）统计年龄在50岁及以上的职工人数 某部门的“人员信息表”中有姓名、年龄、性别等基本信息，现在需要计算年龄在50岁及以上的职工人数。 本例中，需要计算年龄在50岁及以上的职工人数，这是一个单条件计数问题，一般用COUNTIF函数处理，此例我们选择SUMPRODUCT函数。 在E11单元格输入公式：=SUMPRODUCT((C3:C8>=50)*1)，按回车键即可计算出年龄在50岁及以上的职工人数。见下图： 刚才讲过，如果SUMPRODUCT函数的参数中有非数值型数据，函数在计算时会自动将其当做0处理，逻辑值TRUE和FALSE也不例外。因此，在本例中，需要在表示条件的表达式C3:C8>=50计算出来的是文本数字，需要在后面加上“*1”将其转换为数值。“*1”的作用就是将文本数字转换为数值型数字。 使用SUMPRODUCT函数可以解决大部分单条件计数的问题，在计数时，其功能和COUNTIF函数几乎没有区别，本例中若使用COUNTIF函数，公式可写成：=COUNTIF(C3:C8,">=50")。 （二）统计年龄在50岁及以上的女职工人数 跟上一个例子类似，本例中，需要计算年龄在50岁及以上的女职工人数，这是一个多条件计数问题，一般用COUNTIFS函数处理，此例我们选择SUMPRODUCT函数。 在E11单元格输入公式：=SUMPRODUCT((B3:B8="女")*1,(C3:C8>=50)*1)，按回车键即可计算出年龄在50岁及以上的女职工人数。见下图： 本例中的公式可以简化，不使用“*1”转换数据类型，简化后的公式为：=SUMPRODUCT((B3:B8="女")*(C3:C8>=50))。为什么不使用“*1”呢？因为两个逻辑值相乘的结果为数值。 虽然SUMPRODUCT函数在解决大部分多条件计数问题时与COUNTIFS函数的功能相同，那么本例用COUNTIFS函数来实现的话，公式可以写为：=COUNTIFS(B3:B8,"女",C3:C8,">=50")。 （三）计算男职工交纳工会费的总和 某部门要计算男职工的工会费总和，这是一个单条件求和问题，一般用SUMIF函数来处理，本例用SUMPRODUCT函数进行讲解。 在E11单元格输入公式：=SUMPRODUCT((B3:B8="男")*E3:E8)，按回车键，即可计算出男职工交纳工会费的总和。见下图： 使用SUMPRODUCT函数可以解决大部分单条件求和问题，求和时的功能和SUMIF几乎没有区别。本例如果使用SUMIF函数求和，其公式可写成：=SUMIF(B3:B8,"男",E3:E8)。 本例同样可以使用SUM函数的数组公式计算，其公式可写成：=SUM((B3:B8="男")*E3:E8)，按【Ctrl+Shift+Enter】三键组合。 （四）计算科室二女职工工会费总额 某部门要计算科室二女职工的工会费总和，这是一个多条件求和问题，一般用SUMIFS函数来处理，本例用SUMPRODUCT函数进行讲解。 在E11单元格输入公式：=SUMPRODUCT((D3:D8="科室二")*E3:E8*(B3:B8="女"))，按回车键，即可计算出男职工交纳工会费的总和。见下图： 使用SUMPRODUCT函数可以解决大部分多条件求和问题，求和时的功能和SUMIFS几乎没有区别。本例如果使用SUMIFS函数求和，其公式可写成：=SUMIFS(E3:E8,D3:D8,"科室二",B3:B8,"女")。 使用SUMPRODUCT函数对参数的位置没有任何要求，SUMIFS函数对参数的位置要求十分严格。几乎没有区别。本例SUMPRODUCT函数公式可写成：=SUMPRODUCT((D3:D8="科室二")*(B3:B8="女")*E3:E8)，参数的位置可以随意摆放。 本例同样可以使用SUM函数的数组公式进行计算，其公式可写成：=SUM((D3:D8="科室二")*(B3:B8="女")*E3:E8)，按【Ctrl+Shift+Enter】三键组合。 以上就是SUMPRODUCT函数的几个经典案例，想要学习好EXCEL函数，要学会多个函数的组合，后续课程中我们会讲到多函数结合的用法，能进行更为复杂的计算，对提升工作效率有很大帮助。 一、PRODUCT函数基础知识 PRODUCT函数定义PRODUCT函数用于计算给出的数字的乘积，也就是将所有以参数形式给出的数字相乘，并返回乘积值。 PRODUCT函数相当于将所有的参数用乘号“*”相连起来，和使用SUM函数的求和功能极为相似，如果需要让许多单元格相乘，则使用PRODUCT函数很有用。例如：公式=PRODUCT(A1:A3，C1:C3)等同于=A1*A2*A3*C1*C2*C3。 语法PRODUCT(参数1，参数2，……参数N) 参数1……参数N为1到255个需要相乘的数字参数。 在函数公式里面，函数公式=A1*A2*A3可以写成=PRODUCT(A1，A2，A3)，也可以写成=PRODUCT(A1:A3)，在写公式时应当选择比较简便的公式。 使用PRODUCT函数注意事项如果直接在该函数的参数中输入文本型数字，该函数会将其当做数字计算；如果参数为数组或引用，只有其中的数字被计算；对于引用单元格中的文本、错误值、空单元格和错误值等，该函数会直接将其忽略；对于其中的逻辑值，该函数会将TRUE当做1处理，将FALSE当做0处理。 二、PRODUCT函数案例实践 （一）计算工程总价 某公司工程部在进行工程项目改造时，统计了每个工程的数量、面积和单价，现在要计算工程的总价。 在E11单元格输入公式：=PRODUCT(B3:D3)，按回车键即可计算出2*4墙体建设总价。见下图： 上图公式还可以写成：=PRODUCT(B3，C3，D3)或=B3*C3*D3。 如果给B3单元格的数量加上单位，那么就属于文本，不参与计算，参与计算的则只有C3和D3单元格。见下图： （二）跨表计算工程总价 跟上一个例子类似，本例中，各种工程项目的面积和单价分布在两个表格里，需要跨表格把对应项目的面积和单价进行相乘，计算出每个项目的总价。见下图： 在总价表B3单元格输入公式：=PRODUCT(面积:单价!B3)，按回车键即可跨表格计算出2*4墙体建设总价。见下图： 本例中的这种跨表格求积的公式，只对面积表和单价表中的数据对应单元格的情况适用，如果数据对应不一致，则不能使用本例中这种跨表格求积的方式进行计算。 三、MMULT函数基础知识 MMULT函数定义MMULT函数是指返回两个数组的矩阵乘积，结果矩阵的行数与“矩阵数组1”的行数相同，矩阵的列数与“矩阵数组2”的列数相同，而且两个数组中都只能包含数值。 如上图，有两个区域“数组1”和“数组2”，“数组2”的列数必须与“数组1”的行数相同。上图中，“数组2”是1列，几行都无所谓，“数组1”必须是1行，几列都无所谓。至于计算结果，想想小学时候学的乘法口诀，或者看看上图的结果区域，我想应该能理解。 语法MMULT(矩阵数组1，矩阵数组2) 矩阵数组1、矩阵数组2是要进行矩阵乘法运算的两个数组。“矩阵数组1”和“矩阵数组2”可以是单元格区域、数组常量或引用。 在以下情况下，MMULT将返回错误值#VALUE! 一是任意单元格为空或包含文字。 二是“矩阵数组1”的列数与“矩阵数组2”的行数不相等。 对于返回结果为数组的公式，必须以数组公式的形式输入。 MMULT不用三键结束的话就是第1行第1列的数，用三键结束则是一个数组；MMULT作为其他函数参数时则是一个数组参加运算，不需用三键结束。 MMULT两矩阵相乘的过程是：用第一参数的元素分别乘以第二参数对应的元素，然后将结果相加得到最终结果。为了让大家真正理解MMULT函数的计算过程，请看下图。 如上图所示，第一数组的第一个值10乘以第二数组的第一个值1，等于10，即C2乘以A4，以此类推。多步的乘积完成之后，按照计算理论，该函数还会将结果加起来，即10+40+90+160+250=550。 看上图就知道，其结果为550，和推导过程所得的结果是一致的。MMULT函数的用法虽然简单，但是实用性极强大，在很多统计工作中都会使用到，比如计算利润率、计算盈利和亏损等方面运用广泛。并且，该函数还有复杂的使用方法，本文中会提供几个案例给大家参考。 四、MMULT函数案例实践：计算不同单价的药品利润 某药店在节日促销活动后，需要将对应药品的利润进行计算，然后对营业额打折前后的利润进行对比。要计算药品打折前后的利润，要将销售量和单价进行乘法运算，如果逐一计算，将会是很大的工作量，若使用MMULT函数进行数组与数组的计算，将会大大减少工作量。 选择E3:F8单元格区域，输入公式：=MMULT(B3:B8,C3:D3)，按【Ctrl+Shift+Enter】三键组合，即可计算出所有药品打折前后的利润。见下图： 本例中，使用MMULT函数对药品打折前后的盈利进行计算时，一定要将结果输出区域（红框部分）和数据区域（绿框部分）相对应，也就是6行2列，其中6行对应的是函数的第一个参数区域（B3:B8）的行数，2列对应的是函数第二个参数区域（C3:D3）的列数。 在本例中，在设置MMULT函数的第二个参数时，因为有多组单价数据，所以这里只是相对引用一组数据，其他单价数据系统会自动引用。 以上就是PRODUCT和MMULT函数的几个经典案例，想要学习好EXCEL函数，要学会多个函数的组合，后续课程中我们会讲到多函数结合的用法，能进行更为复杂的计算，对提升工作效率有很大帮助。 一、SUBTOTAL函数基础知识 SUBTOTAL函数定义SUBTOTAL函数是指返回列表或数据库中的分类汇总。 在用EXCEL做数据统计时，常见有关于求和、平均值、计数和最大值、最小值等操作，我们需要使用SUM、AVERAGE、MAX和MIN等函数，但如果统计中涉及条件筛选变换统计口径，如果只使用上述几种函数，可能会增加很多工作量。 我们今天要讲的SUBTOTAL函数凭自己的一己之力就可以实现常用的统计功能，包括求和、平均值、计数和最大值、最小值等，而且可以在计算时只统计筛选结果中的行，也就是说，函数计算结果会随着筛选结果的变化而变化，只统计筛选出来的可见部分，是不是感觉它很强大呢？ 语法SUBTOTAL(值,参数1，参数2……) 值必需参数，数字1-11或101-111，用于指定要为分类汇总使用的函数。如果使用1-11，将包括手动隐藏的行，如果使用101-111，则排除手动隐藏的行。始终排除已筛选出隐藏的单元格。 参数1必需参数，是指要对其进行分类汇总计算的第一个命名区域或引用。 参数2可选参数，是指要对其进行分类汇总计算的第2个至第254个命名区域或引用。 当“值”为从1到11的常数时，SUBTOTAL函数将包括通过“隐藏行”命令所隐藏的行中的值，该命令位于EXCEL桌面应用程序中“开始”选项卡上“行和列”组中的“隐藏和取消隐藏”子菜单下面。当需要对列表中的隐藏和非隐藏数字进行分类汇总时，使用1到11的常数。当“值”为从101到111的常数时，SUBTOTAL函数将忽略通过“隐藏行”命令所隐藏的行中的值，当只想对列表中的非隐藏数字进行分类汇总时，请使用这些常数。 从上图可以看出，不隐藏任何行时，利用=SUBTOTAL(9,B3:B10)和=SUBTOTAL(109,B3:B10)，当我把药品3、4两行隐藏后，=SUBTOTAL(109,B3:B10)的功能就显现出来了，只计算没有隐藏的行的数据；而=SUBTOTAL(9,B3:B10)的计算结果连同隐藏的行一并计算了。 SUBTOTAL函数忽略任何不包括在筛选结果中的行，不论使用什么“值”，都只计算可见的单元格总和。见下图： SUBTOTAL函数适用于数据列或垂直区域，不适用于数据行或水平区域。例如：当“值”大于或等于101时需要分类汇总某个水平区域如SUBTOTAL(109,A1:E1)，隐藏某一列时不影响分类汇总，但是隐藏分类汇总的垂直区域中的某一行就会对其产生影响。 二、SUBTOTAL函数案例实践 （一）隐藏行时，让序号始终连续编排 我们要对药品从1开始编号，有时候需要进行筛选，如果直接编序号，在筛选后编号会出现断档的情况，其实，用SUBTOTAL函数就能轻松解决序号的问题。 在A3单元格输入公式：=SUBTOTAL(3,B$3:B3)，按回车键即可计算出第一个编号1，然后向下复制公式到最后一个项目，你会发现，序号已经从1开始编排了。见下图： 上图中，当我们筛选隐藏药品3、5、7后，序号直接跳过隐藏行连续编排，是不是很神奇呢？见下图： “值”3所对应的函数为COUNTA，统计非空单元格的个数。所以公式=SUBTOTAL(3,B$3:B3)统计的就是从B3开始到当前单元格累计非空单元格数。 （二）计算全年级总分最高分和最低分 某学校在期中考试中，要计算全年级总分最高分和最低分，我们可以在总分列用MAX函数得到最高分，用MIN函数得到最低分，这里用SUBTOTAL函数进行计算。 在单元格中输入公式：=SUBTOTAL(4,E3:E8)，即可计算总分区域中的最大值，因为“值”4对应的是“最大值”；输入公式：=SUBTOTAL(5,E3:E8)，即可计算总分区域中的最小值，因为“值”5对应的是最小值。见下图： （三）在全年级名单中计算3班的平均分 某学校在期中考试中，统计了八年级所有班级学生的分数，现在要计算3班的平均分，这里用SUBTOTAL函数进行计算，并且筛选3班名单。 在F11单元格输入公式：=SUBTOTAL(101,F3:F8)，按回车键即可计算出全年级平均分，因为“值”101对应的是“算术平均值”。见下图： 本例要计算的是3班的平均分，所以我们只需要筛选班级3班的名单即可，全年级平均分为229分，筛选后，3班的平均分为231分。见下图： SUBTOTAL函数忽略任何不包括在筛选结果中的行，本例中用到了筛选，所以这里用公式=SUBTOTAL(1,F3:F8)和=SUBTOTAL(101,F3:F8)的计算结果是一样的。 以上就是分类汇总函数SUBTOTAL的几个经典案例，想要学习好EXCEL函数，要学会多个函数的组合，后续课程中我们会讲到多函数结合的用法，能进行更为复杂的计算，对提升工作效率有很大帮助。 一、COUNT函数基础知识 COUNT函数定义COUNT函数计算的是包含数字的单元格个数以及参数列表中数字的个数。例如：输入公式=COUNT(A1:A8)，如果此区域中有6个单元格包含数字，则答案就是6。 语法COUNT(参数1，参数2,……参数N) 参数1必需参数，是指要计算其中数字个数的第一项、单元格引用或区域。 参数2……参数N可选参数，是指要计算其中数字个数的其他项、单元格引用或区域，最多可包含255个。 这些参数可以包含或引用各种类型的数据，但只有数字类型的数据才被计算在内。 使用COUNT函数的注意事项 如果参数为数字、日期或者代表数字的文本（例如，用引号引起的数字"1"），则将被计算在内；逻辑值和直接键入到参数列表中代表数字的文本被计算在内。如果参数为错误值或不能转换为数字的文本，则不会被计算在内。如果参数是一个数组或引用，则只计算其中的数字。数组或引用中的空单元格、逻辑值、文本或错误值将不计算在内。 二、COUNT函数案例实践 （一）统计参加考试的总人数 在G13单元格输入公式：=COUNT(G3:G10)，按回车键即可计算出参加考试的人数。这里要注意的是，总分列（即G列）必须为数字才能统计，空单元格、逻辑值、文本或错误值将不统计在内。见下图： （二）统计总分在200分及以上的人数 某学校在期中考试中，要统计总分在200分及以上的学生人数，这里用COUNT函数进行统计。 在G13单元格中输入公式：=COUNT((G3:G10>=200)^0)，按【Ctrl+Shift+Enter】三键组合，即可统计出总分在200分及以上的学生人数。见下图： 本例中使用了求逻辑值0次方的方法将逻辑真值TRUE转换为数字1，而逻辑假值FALSE转换为错误值#NUM!。除了这种方法外，还有许多方法可以达到相同的效果，比如用一个常数除以逻辑值，可以将它们分别转换为常数和错误值#DIV/0!，本例采用该方法的数组公式为：=COUNT(1/(G3:G10>=200)。 本例中的两种方法都是采用数学的手段，其实，还可以用COUNT和IF函数嵌套的方式完成统计，数组公式为：=COUNT(IF(G3:G10>=200,1,"文字"))，意思是用IF函数把G3:G10区域大于等于200的值转换为数字1，不满足条件时显示“文字”，然后用COUNT统计数字个数。 （三）统计语文和数学成绩均在90分及以上的学生人数 某学校在期中考试中，统计了所有学生的分数，现在要计算语文和数学成绩均在90分及以上的学生人数，这里用COUNT和IF函数嵌套的方法进行计算。 在F11单元格输入公式：=COUNT(IF(C3:C10>=90,IF(D3:D10>=90,1)))，按按【Ctrl+Shift+Enter】三键组合，即可计算出语文和数学成绩均在90分及以上的学生人数。见下图： 本例也是采用数学的手段达到相同的结果，比如数组公式=COUNT(1/(C3:C10>=90)+1/(D3:D10>=90))和数组公式=COUNT((C3:C10>=90)^0*(D3:D10>=90)^0)都是采用数学手段来实现，其中运用了错误值参与算术运算的结果仍为错误值的理论。 使用COUNT函数计数的公式，如果将需要计数的项目转换为1，其他转换为0，可以使用SUM和IF嵌套的方法进行计数，本例数组公式可以写成：=SUM(IF(C3:C10>=90,IF(D3:D10>=90,1)))，但一般对单条件计数用COUNTIF函数进行统计，多条件计数用COUNTIFS函数进行统计，后续课程会讲到。 以上就是数字统计函数COUNT的几个经典案例，想要学习好EXCEL函数，要学会多个函数的组合，后续课程中我们会讲到多函数结合的用法，能进行更为复杂的计算，对提升工作效率有很大帮助。 一、COUNTA函数基础知识 COUNTA函数定义COUNTA函数是指统计区域内不是空白的单元格个数。 语法COUNTA(参数1，参数2,……参数N) 参数1必需参数，是指要计数的值的第一个参数。 参数2……参数N可选参数，是指要计数的值的其他参数，最多可包含255个参数。 使用COUNTA函数的注意事项 COUNTA函数计算包含任何类型的信息（包括错误值和空文本""）的单元格。例如：如果区域中包含的公式返回空字符串，则COUNTA函数计算该值。COUNTA函数不会对空单元格进行计数。 如果只希望对符合某一条件的单元格进行计数，则使用COUNTIF函数或COUNTIFS函数。 COUNTA函数与COUNT函数的共同点都是返回非空单元格的个数。区别：COUNT函数只有当单元格内容是数值时才进行统计，而COUNTA函数则是无论当单元格是什么内容都统计（可以是文本、逻辑值、错误值和引用公式的空值），只要不是空白的就行。如果不需要对逻辑值、文本或错误值进行计数（换句话说，只希望对包含数字的单元格进行计数），则使用COUNT函数。 二、COUNTA函数案例实践 （一）统计某公司的总人数 某公司人员入职、辞职时，会导致人员数量发生变化，需要随时了解公司人员变化情况以作出相应的安排，现需要统计公司的总人数。 在E13单元格输入公式：=COUNTA(A3:A10)，按回车键即可计算出参加考试的人数。见下图： 本例中，使用的是员工的姓名作为统计对象，对于文本对象可以使用COUNTA函数来统计个数。COUNTA函数可以统计出所有非空值单元格的个数，如果COUNTA函数的参数列表中某些非空单元格被多次引用，COUNTA函数也会多次统计。 （二）自动生成药品序号 我们要对药品从1开始编号，与SUBTOTAL函数中的例子“让序号始终连续编排公式=SUBTOTAL(3,B$3:B3)”类似，这里可以用COUNTA函数解决。 在A3单元格输入公式：=COUNTA(B$3:B3)，按回车键即可计算出第一个编号1，然后向下复制公式到最后一个项目，你会发现，序号已经从1开始编排了。见下图： COUNTA和=SUBTOTAL(3,B$3:B3)不同的是，=SUBTOTAL(3,B$3:B3)在隐藏行时，序号连续编排，而用COUNTA函数进行编号时，隐藏行后则不会连续编排。见下图： 本例中使用了单元格的混合引用来解决问题，其中单元格区域B$3:B3实际上就是B3单元格，不会被COUNTA函数重复计数。 本例中使用COUNTA函数自动生成药品编号，相对于其他方法还有一个比较明显的好处，那就是在数据中任何位置插入空行，不会改变药品的编号，即空行不会被自动编号。见下表： 三、COUNTBLANK函数基础知识 COUNTBLANK函数定义COUNTBLANK函数是指统计指定区域内空白单元格的个数。 语法COUNTBLANK(统计区域) 统计区域必需参数，是指需要计算其中空白单元格个数的区域。 使用COUNTBLANK函数的注意事项 包含返回""（空文本）的公式的单元格会计算在内，包含零值的单元格不计算在内。 如上图，“库存1”中的3个空单元格里无任何内容，统计出的空白单元格就是3个；“库存2”中，C3单元格引用了空值，不显示内容，按空单元格统计在内，C7单元格引用了其他单元格的值，只是没有数字，显示0，不统计在内，所以结果为4个。 四、COUNTBLANK函数案例实践：统计职工未打卡总次数 某公司人员上下班需要打卡，每天4次，如果未打卡则留空，现需要统计某天职工未打卡总次数，对空单元格进行统计时，一般使用COUNTBLANK函数。 在E14单元格输入公式：=COUNTBLANK(B4:E11)，按回车键即可计算出职工未打卡总次数。见下图： 本例中，如果指定单元格区域没有涉及含有返回值为空文本（""）的公式，可以使用COUNTA函数实现使用COUNTBLANK函数的功能，公式可写成：=COUNTA(B4:E11*1)-COUNTA(B4:E11)，按【Ctrl+Shift+Enter】三键组合即可计算结果为7。公式的意思是：用COUNTA(B4:E11*1)统计指定区域的单元格总数，用COUNTA(B4:E11)统计指定区域的非空单元格个数，用总单元格数减去非空单元格数即得到空单元格数。 以上就是数字统计函数COUNTA和COUNTBLANK的几个经典案例，想要学习好EXCEL函数，要学会多个函数的组合，后续课程中我们会讲到多函数结合的用法，能进行更为复杂的计算，对提升工作效率有很大帮助。 一、COUNTIF函数基础知识 COUNTIF函数定义COUNTIF函数是对指定区域中符合指定单个条件的单元格计数的一个函数。用法和SUMIF函数类似，SUMIF是计算满足单个条件的总和，COUNTIF是统计满足单个条件的个数。 语法COUNTIF(查找区域,条件) 查找区域必需参数，是指要查找“条件”的区域。 条件必需参数，是指以数字、表达式或文本形式定义的条件，甚至可以使用通配符。 二、COUNTIF函数案例实践 （一）统计考试总分在200分及以上的学生人数 某小学在组织学生考试时，需要对某个年级总分在200分及以上的人数进行统计，这时候需要用到COUNTIF单条件计数函数。 在F13单元格输入公式：=COUNTIF(F3:F10,">=200")，按回车键即可计算出总分在200分及以上的人数。见下图： COUNTIF函数是用来计算指定单元格区域中满足指定条件的单元格的个数，其中第二个参数是用来指定第一个参数能够满足被计数的条件，该参数中只可以设置一个条件。 对于单条件计数问题，还可以以SUM和IF函数嵌套的数组公式完成统计，公式可写成：=SUM(IF(F3:F10>=200,1))。 （二）计算男员工的比例 我们要对某公司男员工的比例进行计算，首先要统计出男员工人数和该公司员工总数，然后用男员工人数除以员工总数 在F13单元格输入公式：=COUNTIF(C3:C10,"男")/COUNTA(C3:C10)，按回车键即可计算出男员工比例，将单元格格式设置为百分比。见下图： 本例中，用COUNTIF(C3:C10,"男")统计出男员工人数为6人，用COUNTA(C3:C10)统计出总员工数8人，用6除以8，等于0.75，设置单元格格式为百分比，结果为75%。 （三）计算年龄在25-35岁的员工比例 不同行业对员工的年龄有不同要求，对于一些技术性较强的行业，希望员工年龄大一些，而对于销售行业，则希望员工年龄小一些。要计算该年龄段的员工比例，首先要知道该年龄段的员工总数和员工总数，该年龄段员工总数可以用两个COUNTIF函数统计，员工总数可以用COUNTA函数统计。 在F13单元格输入公式：=(COUNTIF(D3:D10,">=25")-COUNTIF(D3:D10,">35"))/COUNT(D3:D10)，按回车键即可计算出年龄在25-35的员工比例，将单元格格式设置为百分比。见下图： 本例中，之所以使用两个COUNTIF函数相减的方式计算25-35岁之间的总员工数，因为年龄大于等于25岁的员工包含了35岁以上的员工。 本例中，对于这种条件计数，还可以使用SUM和IF函数嵌套的数组公式完成计算，公式可写成：=SUM(IF(D3:D10>=25,IF(D3:D10<=35,1)))/COUNTA(D3:D10)。 三、COUNTIFS函数基础知识 COUNTIFS函数定义COUNTIFS函数是指用来计算多个区域中满足指定条件的单元格的个数，可以同时设定多个条件，是COUNTIF函数的扩展 语法COUNTIFS(条件区域1,条件1,条件区域2,条件2……条件区域N,条件N) 条件区域1必需参数，是指计算其中满足某个条件的单元格数目的第一个单元格区域。 条件1必需参数，是指为“条件区域1”中将被计算在内的条件（简称条件），其形式可以为数字、表达式或文本。例如，条件可以表示为1、"1"、">1"或"男"等。 同理，“条件区域2”为第二个条件区域，“条件2”为第二个条件，依次类推，最终结果为多个区域中满足所有条件的单元格个数。每一个附加的区域都必须与“条件区域1”具有相同的行数和列数，这些区域无需彼此相邻。 COUNTIFS的用法与COUNTIF类似，但COUNTIF针对单一条件，而COUNTIFS可以实现多个条件同时求结果。如果条件参数是对空单元格的引用，两者会将该单元格的值视为0。您可以在条件中使用通配符，即问号()和星号(*)，问号匹配任意单个字符，星号匹配任意字符串，如果要查找实际的问号或星号，请在字符前键入波形符(~)。 四、COUNTIFS函数案例实践统计三科考试成绩均在90分及以上的学生人数 某小学在组织学生考试时，有语文、数学、英语三门学科，需要统计三科考试成绩均在90分及以上的学生人数，因为涉及多个条件，这时候需要用到COUNTIFS多条件计数函数。 在F13单元格输入公式：=COUNTIFS(C3:C10,">=90",D3:D10,">=90",E3:E10,">=90")，按回车键即可计算出三科考试成绩均在90分及以上的学生人数。见下图： COUNTIFS函数中各个条件是逻辑与（并且）的关系，相当于各个条件用AND函数连接在一起。如果只是计算满足诸多条件中任意一个条件，可以使用SUM和IF嵌套的数组公式完成，公式可写成：=SUM(IF(C3:C10>=90,IF(D3:D10>=90,IF(E3:E10>=90,1))))。虽然SUM和IF函数嵌套能完成大部分计数计算，但是使用通配符时嵌套无法使用。 以上就是单条件计数函数COUNTIF和多条件计数函数COUNTIFS的几个经典案例，想要学习好EXCEL函数，要学会多个函数的组合，后续课程中我们会讲到多函数结合的用法，能进行更为复杂的计算，对提升工作效率有很大帮助。