一、Excel中公式与函数的认识

在Excel中，对工作表的数据进行计算的算式称为“公式”。而函数是一些预定义的公式，通过使用一些称为参数的特定数值来按特定的顺序或结构执行计算。Excel中包含众多的函数，利用函数可执行简单和复杂的计算。

在单元格输入公式时，要以等号“＝”开始，等号后面输入算式。算式由运算对象和运算符号组成。运算对象可以是具体数据、单元格名称、单元格区域或函数等，运算符表示进行某种特定的运算，如算术符“＋”、“－”等，运算符必须在英文状态下输入。函数有基本格式：函数名（参数系列），每一个函数后一定有一个小括号，括号内一般有一个或多个参数，这些参数多以逗号分开。函数都在公式中出现，公式都以等号开始。如图1－56所示。

图1－56 函数的格式

二、函数的种类

Excel的函数共有12类，分别是数据库函数、日期与时间函数、工程函数、财务函数、信息函数、逻辑函数、查询和引用函数、数学和三角函数、外部函数、统计函数、文本和数据函数以及用户自定义函数。Excel中常用的基本函数有以下几种，如表1－1所示。

表1－1 常用的几种基本函数

三、公式与函数的使用

打开“Excel基本操作系统”工作簿，选择“教职工基本工资表”为当前工作表，如图1－57所示。用函数方法得出“岗位工资”、“资质工资”、“年资”和“总工资”。

图1－57 教职工基本工资表

（一）IF函数的使用

IF函数是执行真假值判断，根据逻辑计算的真假值，返回不同结果。可以使用函数IF对数值和公式进行条件检测。

函数语法：IF（logical＿test，value＿if＿true，value＿if＿false）

参数说明：

Logical＿test：表示计算结果为TRUE或FALSE的任意值或表达式。

Value＿if＿true：logical＿test为TRUE时返回的值。

Value＿if＿false：logical＿test为FALSE时返回的值。

例如，由四种职称来评定岗位工资的多少。职称是教授的，岗位工资为7200；职称是副教授的，岗位工资为6000；职称是讲师的，岗位工资为4500；职称是助教的，岗位工资为3600。操作方法如下所述。

第一步：选中H3单元格，单击“自动求和”右边小三角按钮，选择“其他函数”，打开“插入函数”对话框，或是单击【插入】｜【函数】打开“插入函数”对话框，此时H3单元格自动会输入一个等号“＝”，进入函数计算。如图1－58所示。

图1－58 打开插入函数窗口

第二步：选择“常用函数”中的IF函数，打开“函数参数”对话框，如图1－59所示。

图1－59 IF函数参数窗口

第三步：设置IF函数参数，这里有四种职称的判断，所以在判断条件的时候需要再嵌套两个IF函数，先在Logical＿test栏输入“E3＝“教授””，在Value＿if＿true栏输入“7200”，然后把鼠标光标定位于Value＿if＿false栏处，如图1－60所示。

图1－60 IF函数参数设置对话框

第四步：嵌套一个IF函数，单击名称框右边小三角按钮，选择IF函数，如图1－61所示，打开一个新的“函数参数”窗口，设置参数条件如图1－62所示。

图1－61 嵌套函数

图1－62 设置参数

第五步：再次嵌套一个IF函数，设置参数如图1－63所示，单击【确定】，则H3单元格自动计算出结果显示为6000，即在编辑栏显示的公式为：＝IF（E3＝“教授”， 7200，IF（E3＝“副教授”，6000，IF（E3＝“讲师”，4500，3600））），如图1－64所示。

图1－63 嵌套IF函数

图1－64 确定后显示效果

第六步：通过自动填充复制公式，完成岗位工资列的计算，如图1－65所示。

图1－65 自动填充完成

注意：公式中的标点符号均为英文标点符号。

同样，资质工资也可以用IF函数完成，即编辑栏公式为：＝IF（D3＝“教研室主任”，800，500），如图1－66所示。

图1－66 用IF函数计算资质工资

（二）SUM函数的使用

SUM函数是返回某一单元格区域中所有数字之和。

函数语法：SUM（number1，number2，．．．）

参数说明：

Number1，number2，．．． 为1～30个需要求和的参数。

在“教职工基本工资表”工作表中用SUM函数计算出总工资。

单击K3单元格，单击【插入】｜【函数】，打开“插入函数”对话框，选择SUM函数，打开“函数参数”对话框，如图1－67所示。在“函数参数”对话框中单击“Number1”右边按钮，然后在工作表中选取正确的单元格区域；如果是两个不连续的区域求和，需要在“Number2”中设置第二个区域的单元格地址，单击【确定】即可完成计算。使用自动填充完成总工资计算。

如果是连续的区域求和运算，可以选中K3单元格，直接单击自动求和按钮，将自动显示求和公式，单击【确定】输入按钮或是按Enter完成计算，即公式为“＝SUM（H3：J3）”。再使用自动填充完成所有总工资计算。如图1－68所示。

图1－67 SUM函数参数设置对话框

图1－68 直接单击自动求和按钮计算

注意：表中“年资”J3单元格中的计算公式为“＝YEAR（TODAY（））－YEAR（G3）＊100”，然后向下填充完成年资计算，其值会随着年份的变化而发生变化。本书编写年份为2012年。

（三）COUNT与COUNTA函数的使用

COUNT函数是返回包含数字以及包含参数列表中的数字的单元格的个数。利用函数COUNT可以计算单元格区域或数字数组中数字字段的输入项个数。

函数语法：COUNT（value1，value2，．．．）

参数说明：

Value1，Value2，．．．：为包含或引用各种类型数据的参数（1～30个），但只有数字类型的数据才被计算。

COUNTA函数是返回参数列表中非空值的单元格个数。利用函数COUNTA可以计算单元格区域或数组中包含数据的单元格个数。

函数语法：COUNTA（value1，value2，．．．）

参数说明：

Value1，Value2，．．．：为所要计算的值，参数个数为1到30个。在这种情况下，参数值可以是任何类型，它们可以包括空字符（""），但不包括空白单元格。如果参数是数组或单元格引用，则数组或引用中的空白单元格将被忽略。如果不需要统计逻辑值、文字或错误值，请使用函数COUNT。(www.daowen.com)

例如需要在“教职工基本工资表”工作表中B13单元格中求总人数。单击B13单元格，单击【插入】｜【函数】，打开“插入函数”对话框，选择COUNT函数，打开“函数参数”对话框，如图1－69所示。在“Value1”中设置单元格区域为B3：B12，单击【确定】，此时，发现在B13中显示出来的结果是0，如图1－70所示，这是因为COUNT函数只能对数字型数据进行计数，不能对文本及文本型数据进行计数，因此应该使用另一个计数函数COUNTA函数。

图1－69 COUNT函数参数设置对话框

图1－70 错误显示效果

下面用COUNTA函数来计算人数。用同样的方法打开“插入函数”对话框，选择COUNTA函数，打开“函数参数”对话框，如图1－71所示。在“Value1”中设置单元格区域为B3：B12，单击【确定】即可完成计算，此时，B13单元格显示为10。如图1－72所示。

图1－71 COUNTA函数参数设置对话框

图1－72 正确显示效果

（四）AVERAGE函数的使用

AVERAGE是返回参数的平均值（算术平均值）。

函数语法：AVERAGE（number1，number2，．．．）

参数说明：

Number1，number2，．．．：为需要计算平均值的1到30个参数。

例如需要在“教职工基本工资表”工作表中K13单元格中求总工资的平均值。选择K13单元格，单击【插入】｜【函数】，打开“插入函数”对话框，选择AVERAGE函数，打开“函数参数”对话框，在“Number1”中设置单元格区域为K3：K12，即编辑栏显示公式为“＝AVERAGE（K3：K12）”，单击【确定】，即可完成计算。如图1－73所示。

图1－73 AVERAGE函数参数设置对话框

（五）MAX与MIN函数的使用

MAX函数是返回一组值中的最大值。

函数语法：MAX（number1，number2，．．．）

参数说明：

Number1，number2，．．．：是要从中找出最大值的1～30个数字参数。

MIN函数是返回一组值中的最小值。

函数语法：MIN（number1，number2，．．．）

参数说明：

Number1，number2，．．．：是要从中找出最小值的1～30个数字参数。

例如需要在“教职工基本工资表”工作表中J13单元格中计算年资的最大值，在J14单元格中计算年资的最小值。选择J13单元格，单击【插入】｜【函数】，打开“插入函数”对话框，选择MAX函数，打开“函数参数”对话框，在“Number1”中设置单元格区域为J3：J12，即编辑栏显示公式为“＝MAX（J3：J12）”，单击【确定】，即可完成计算。如图1－74所示。选择J14单元格，单击【插入】｜【函数】，打开“插入函数”对话框，选择MIN函数，打开“函数参数”对话框，在“Number1”中设置单元格区域为J3：J12，即编辑栏显示公式为“＝MIN（J3：J12）”，单击【确定】，即可完成计算。如图1－75所示。

图1－74 最大值计算

图1－75 最小值计算

（六）RANK函数的使用

RANK函数是返回一个数字在数字列表中的排位。数字的排位是其大小与列表中其他值的比值（如果列表已排过序，则数字的排位就是它当前的位置）。

函数语法：RANK（number，ref，order）

参数说明：

Number：为需要找到排位的数字。

Ref：为数字列表数组或对数字列表的引用。Ref中的非数值型参数将被忽略。

Order：为一数字，指明排位的方式。如果为0（零）或省略，则按照降序排列；如果不为零，则按照升序排列。

例如需要在“教职工基本工资表”工作表中L3：L12单元格区域中给各职工的总工资进行排名。选择L3单元格，单击【插入】｜【函数】，打开“插入函数”对话框，选择RANK函数，打开“函数参数”对话框，在“Number”中设置单元格区域为K3，在“Ref”中设置为“K3：K12”，“Order”可以设置为0或者不设置，意思是：将L3在固定单元格区域K3：K12中按照降序进行排序。即编辑栏显示公式为“＝RANK（K3：K12）”，单击【确定】，如图1－76所示。

此时L3单元格显示为1，当直接使用自动填充完成所有排名时，如图1－77所示，我们发现，这样做不正确。如使用自动填充来完成排名，此处K3：K12区域我们应使用绝对地址，如图1－78所示。

图1－76 RANK函数参数设置对话框

图1－77 直接填充显示效果

图1－78 正确填充完成排名显示效果

选中H3：K12单元格区域，单击鼠标右键打开设置单元格格式快捷菜单，单击“数字”中的“会计专用”货币符号选“”，保留小数设置为“0”，单击【确定】，如图1－79所示。按照图1－80所示完成教职工基本工资表，保存并退出。

图1－79 设置会计专用货币符号

图1－80 完成教职工基本工资表

注意：表中“年资”的计算公式为“＝（YEAR（TODAY（））－YEAR（G3））＊100”

（七）COUNTIF函数的使用

COUNTIF函数是计算区域中满足给定条件的单元格的个数。

函数语法：COUNTIF（range，criteria）

参数说明：

Range：为需要计算其中满足条件的单元格数目的单元格区域。

Criteria：为确定哪些单元格将被计算在内的条件，其形式可以为数字、表达式或文本。

打开工作簿“Excel基本操作系统．xls”，插入一个新工作表，并重命名为“教职工考勤表”，在“教职工考勤表”中建立“教职工2012年7月考勤表”。如图1－81所示。

图1－81 考勤表

现在要求利用COUNTIF函数计算出“加班天数”、“旷工天数”、“病假天数”和“事假天数”。选择AI3单元格，单击【插入】｜【函数】，打开“插入函数”对话框，选择COUNTIF函数，打开“函数参数”对话框，在“Range”中设置单元格区域为D3：AH3，在“Criteria”中设置为"＋"，即编辑栏公式为“＝COUNTIF（D3：AH3，"＋"）”，单击【确定】即可完成操作。即在AI3单元格显示结果为0。用自动填充方法完成“加班天数”的计算。如图1－82所示。

用同样的方法算出“旷工天数”、“病假天数”和“事假天数”，其公式分别为：“＝COUNTIF（D3：AH3，"X"）”、“＝COUNTIF（D3：AH3，"○"）”和“＝COUNTIF（D3：AH3，"△"）”。“补发合计”和“扣发合计”的计算公式分别为：“＝100＊AI3”和“＝100＊AJ3＋50＊（AK3＋AL3）”。完成如图1－83所示。保存并退出。

图1－82 COUNTIF函数参数设置对话框

图1－83 教职工考勤表完成效果