计算机二级第三章公式和函数

计算机二级第三章公式和函数第一页，共五十二页，编辑于2023年，星期一3.3Excel公式和函数本节案例背景：Excel提供大量实用函数满足各类计算的需要。通过公式和函数计算出的结果不但正确率有保证，而且在原始数据发生改变后，计算结果能够自动更新。在人事档案管理和工资表的创建过程中，公式和函数给书娟的工作提供了相当的便利，极大地提高了工作的效率和效果。本节将通过对员工档案表和员工工资表的处理，帮助书娟实现以下工作目标：了解公式和函数的基本作用，了解在公式中各种引用的含义及方法。掌握公式的构成、输入方法，学会构建公式以帮助计算。了解Excel提供了哪些类型的函数，掌握函数的基本输入方法。掌握Excel中常用、重要函数的使用方法，并在实际工作中应用它们。了解公式及函数应用过程中各种常见问题及解决方法。2第二页，共五十二页，编辑于2023年，星期一3.3.1使用公式的基本方法1.认识公式公式是一组表达式，由单元格引用、常量、运算符、括号组成，复杂的公式还可以包括函数，用于计算生成新的值。在Excel中，公式总是以等号“=”开始。单元格引用：即单元格地址，用于表示单元格在工作表上所处位置的坐标。例如，显示在第B列和第3行交叉处的单元格，其引用形式为“B3”。常量：固定的数值或文本。例如，数字“210”和文本“姓名”均为常量。运算符：运算符用于连接常量、单元格引用，从而构成完整的表达式。常用的运算符有：算术运算符，字符连接符，关系运算符。33章通过Excel创建并处理电子表格3.3Excel公式和函数第三页，共五十二页，编辑于2023年，星期一3.3.1使用公式基本方法2.公式的输入与编辑1）输入公式=常量/单元格引用/表达式必须以等号“=”开始，在公式中所使用的运算符都必须是西文的半角字符例如：=30，=B3，=A5*10%，=C8&C943章通过Excel创建并处理电子表格3.3Excel公式和函数第四页，共五十二页，编辑于2023年，星期一3.3.1使用公式基本方法2）修改公式用鼠标双击公式进入编辑状态，在单元格或者编辑栏中均可对公式进行修改即可。删除公式：按Del键。3.公式的复制与填充拖动公式单元格右下角的填充柄“开始”选项卡→“编辑”组→“填充”按钮。进行公式的复制填充时，填充的实际上是公式而非数据本身，填充时公式中对单元格的引用采用的是相对引用。53章通过Excel创建并处理电子表格3.3Excel公式和函数第五页，共五十二页，编辑于2023年，星期一3.3.1使用公式基本方法4.单位格引用在公式中最常用的是单元格引用。可以在单元格中引用一个单元格、一个单元格区域、引用另一个工作表或工作簿中的单元格或区域。单元格引用方式分为以下几类：相对引用：如“=A1”。绝对引用：如“=$A$1”。混合引用：如“=A$1”、“=$A1”、“=$A$1”63章通过Excel创建并处理电子表格3.3Excel公式和函数第六页，共五十二页，编辑于2023年，星期一3.3.2名称的定义与引用为单元格或区域指定一个名称，是实现绝对引用的方法之一。可以定义为名称的对象包括：常量、单元格或单元格区域、公式。1.了解名称的语法规则唯一性原则：名称在其适用范围内必须始终唯一，不可重复。有效字符：名称中第一个字符必须是字母、下划线(_)或反斜杠(\)，名称中不能使用大小写字母“C”、“c”、“R”或“r”。不能与单元格地址相同：例如，名称是A1、B$2等。不能使用空格：可选用下划线(_)和句点(.)作为单词分隔符。名称长度有限制：一个名称最多可以包含255个西文字符。不区分大小写：例如，Sales与SALES视为同名。73章通过Excel创建并处理电子表格3.3Excel公式和函数第七页，共五十二页，编辑于2023年，星期一3.3.2名称的定义与引用2.为单元格或单元格区域定义名称定义好的名称将会在公式及函数中被引用。1）快速定义名称选择要命名的单元格或单元格区域，在编辑栏的“名称框”中输入名称后按Enter键确认。在案例工作簿文档“3.3员工档案及工资表”的“员工档案表”工作表中，将数据列表区域A3:N38的名称定义为“全体员工资料”。83章通过Excel创建并处理电子表格3.3Excel公式和函数第八页，共五十二页，编辑于2023年，星期一3.3.2名称的定义与引用2）将现有行和列标题转换为名称选择要命名的区域，必须包括行或列标题→“公式”选项卡→“定义的名称”组→“从所选内容创建”按钮→在对话框中，通过选中“首行”、“左列”、“末行”或“右列”复选框来指定包含标题的位置。在案例工作簿文档“3.3员工档案及工资表”的“员工档案表”中，将“基本工资”和“工龄工资”两列的首行转换为相应列数据的名称。93章通过Excel创建并处理电子表格3.3Excel公式和函数第九页，共五十二页，编辑于2023年，星期一3.3.2名称的定义与引用3）使用“新名称”对话框定义名称“公式”选项卡→“定义的名称”组→“定义名称”按钮→在“名称”文本框中输入名称→在“范围”下设定名称的适用范围→在“备注”说明性批注。→“引用位置”框中修改命名对象（可以是单元格区域、常量、公式），可选择下列操作之一。在案例工作簿文档“3.3员工档案及工资表”的“员工档案表”中，将工龄工资常量“50”元定义为名称“工龄工资_每年”。103章通过Excel创建并处理电子表格3.3Excel公式和函数第十页，共五十二页，编辑于2023年，星期一3.3.2名称的定义与引用3.引用名称名称可直接用来快速选定已命名的区域，更重要的是可以在公式中引用名称以实现精确引用。1）通过“名称框”引用单击编辑栏中“名称框”右侧的黑色箭头，打开“名称”下拉列表→单击选择某一名称提示：通过名称框打开的“名称”下拉列表中不包括常量和公式的名称。113章通过Excel创建并处理电子表格3.3Excel公式和函数第十一页，共五十二页，编辑于2023年，星期一3.3.2名称的定义与引用2）在公式中引用“公式”选项卡→“定义的名称”组→“用于公式”按钮→选择名称4.更改或删除名称如果更改了某个已定义的名称，则工作簿中所有已引用该名称的位置均会自动随之更新。更改名称：“公式”选项卡→“定义的名称”组→“名称管理器”按钮→在名称列表中选择要更改的名称→“编辑”按钮→在“编辑名称”对话框中修改名称属性。删除名称：“公式”选项卡→“定义的名称”组→“名称管理器”按钮→在名称列表中选择名称→“删除”按钮。提示：若公式中已引用的某个名称被删除，可能导致公式出错。123章通过Excel创建并处理电子表格3.3Excel公式和函数第十二页，共五十二页，编辑于2023年，星期一3.3.3使用函数的基本方法1.认识函数函数实际上特殊的公式，主要是为解决那些复杂计算需求而提供的一种预置算法，如求和函数SUM，平均值函数EVERAGE，条件函数IF等。函数通常表示为：函数名（[参数1],

[参数2],……）函数中的参数可以是常量、单元格地址、数组、已定义的名称、公式、函数等。输入函数时必须以等号“=”开始。2.

Excel函数分类Excel提供大量工作表函数，并按其功能进行分类。Excel2010目前默认提供的函数类别共13大类，见表3.3中所列。133章通过Excel创建并处理电子表格3.3Excel公式和函数第十三页，共五十二页，编辑于2023年，星期一表3.3Excel2010函数类别函数类别常用函数示例及说明财务函数NPV(rate,value1,[value2],...)返回一项投资的净现值。日期和时间函数YEAR(serial_number)返回某日期对应的年份数学和三角函数INT(number)将数字向下舍入到最接近的整数。统计函数AVERAGE(number1,[number2],...)返回参数的算术平均值查找和引用函数VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup])搜索某个单元格区域的第一列，然后返回该区域相同行上任何单元格中的值数据库函数DCOUNTA(database,field,criteria)返回满足指定条件的非空单元格的个数。文本函数MID(text,start_num,num_chars)返回文本字符串中从指定位置开始的特定数目的字符逻辑函数IF(logical_test,[value_if_true],[value_if_false])若指定条件的计算结果为TRUE，将返回某个值；若该条件的计算结果为FALSE，则返回另一个值。信息函数ISBLANK(value)检验单元格值是否为空，若为空则返回TRUE工程函数CONVERT(number,from_unit,to_unit)将数字从一个度量系统转换到另一个度量系统中。兼容性函数RANK(number,ref,[order])返回一个数字在数字列表中的排位多维数据集函数CUBEVALUE(connection,member_expression1,member_expression2…)从多维数据集中返回汇总值与加载项一起安装的用户自定义函数如果在系统中安装了某一包含函数的应用程序，该程序做为Excel的加载项，其所包含的函数作用自定义函数显示在这里以供选用。3.3Excel公式和函数14第十四页，共五十二页，编辑于2023年，星期一3.3.3使用函数的基本方法3.函数的输入与编辑函数的输入方式与公式类似，可以直接在单元格中输入“=函数名(所引用的参数)”，但是要想记住每一个函数名并正确输入所有参数是有相当困难的。因此，通常情况采用参照的方式输入一个函数。1）通过“函数库”组插入“公式”选项卡→“函数库”组中的某一函数类别→从函数列表中单击函数→在“函数参数”对话框中输入或选择参数153章通过Excel创建并处理电子表格3.3Excel公式和函数第十五页，共五十二页，编辑于2023年，星期一3.3.3使用函数的基本方法2）通过“插入函数”按钮插入“公式”选项卡→“函数库”组→“插入函数”按钮，打开“插入函数”对话框→在“选择类别”下拉表中选择函数类别，或者在“搜索函数”框中输入函数的简单描述后单击“转到”按钮→在“选择函数”列表中选择函数→在“函数参数”对话框中输入参数。3）修改函数在包含函数的单元格的双击鼠标，进入编辑状态，对函数参数进行修改后按Enter键确认。163章通过Excel创建并处理电子表格3.3Excel公式和函数第十六页，共五十二页，编辑于2023年，星期一3.3.4Excel中常用函数的应用1.

Excel中常用函数简介1）求和函数SUM(number1,[number2],...])功能：将指定的参数number1、number2……相加求和。例如：=SUM(A1:A5)是将单元格A1至A5中的所有数值相加=SUM(A1,A3,A5)是将单元格A1、A3和A5中的数字相加。173章通过Excel创建并处理电子表格3.3Excel公式和函数第十七页，共五十二页，编辑于2023年，星期一3.3.4Excel中常用函数的应用2）条件求和函数SUMIF(range,criteria,[sum_range])功能：对指定单元格区域中符合指定条件的值求和。提示：在函数中任何文本条件或任何含有逻辑或数学符号的条件都必须使用双引号(")括起来。如果条件为数字，则无需使用双引号。例如：=SUMIF(B2:B25,">5")表示对B2:B25区域大于5的数值进行相加；=SUMIF(B2:B5,"John",C2:C5)，表示对单元格区域C2:C5中与单元格区域B2:B5中等于“John”的单元格对应的单元格中的值求和。183章通过Excel创建并处理电子表格3.3Excel公式和函数第十八页，共五十二页，编辑于2023年，星期一3.3.4Excel中常用函数的应用3）多条件求和函数SUMIFS(sum_range,criteria_range1,criteria1,[criteria_range2,criteria2],...)功能：对指定单元格区域中满足多个条件的单元格求和。例如：=SUMIFS(A1:A20,B1:B20,">0",C1:C20,"<10")表示对区域A1:A20中符合以下条件的单元格的数值求和：B1:B20中的相应数值大于零、且C1:C20中的相应数值小于10。193章通过Excel创建并处理电子表格3.3Excel公式和函数第十九页，共五十二页，编辑于2023年，星期一3.3.4Excel中常用函数的应用4）绝对值函数ABS(number)功能：返回数值number的绝对值例如：=ABS(-2)表示求-2的绝对值；=ABS(A2)，表示对单元格A2中的数值求取绝对值。5）向下取整函数INT(number)功能：将数值number向下舍入到最接近的整数例如：=INT(8.9)表示将8.9向下舍入到最接近的整数，结果为8；=INT(-8.9)表示将-8.9向下舍入到最接近的整数，结果为-9。203章通过Excel创建并处理电子表格3.3Excel公式和函数第二十页，共五十二页，编辑于2023年，星期一3.3.4Excel中常用函数的应用6）四舍五入函数ROUND(number,num_digits)功能：将指定数值number按指定的位数num_digits进行四舍五入。例如：=ROUND(25.7825,2)，表示将数值25.7825四舍五入为小数点后两位。提示：如果希望始终进行向上舍入，可使用ROUNDUP函数；如果希望始终始终进行向下舍入，则应使用ROUNDDOWN函数。213章通过Excel创建并处理电子表格3.3Excel公式和函数第二十一页，共五十二页，编辑于2023年，星期一3.3.4Excel中常用函数的应用7）取整函数TRUNC(number,[num_digits])功能：将指定数值number的小数部分截去，返回整数。num_digits为取整精度，默认为0。例如：=TRUNC(8.9)表示取8.9的整数部分，结果为8；=TRUNC(-8.9)表示取-8.9的整数部分，结果为-8。223章通过Excel创建并处理电子表格3.3Excel公式和函数第二十二页，共五十二页，编辑于2023年，星期一3.3.4Excel中常用函数的应用8）垂直查询函数VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup])功能：搜索指定单元格区域的第一列，然后返回该区域相同行上任何指定单元格中的值。例如：=VLOOKUP(1,A2:C10,2)要查找的区域为A2:C10，因此A列为第1列，B列为第2列，C列则为第3列。表示使用近似匹配搜索A列（第1列）中的值1，如果在A列中没有1，则近似找到A列中与1最接近的值，然后返回同一行中B列（第2列）的值。=VLOOKUP(0.7,A2:C10,3,FALSE)表示使用精确匹配在A列中搜索值0.7。如果A列中没有0.7这个值，则所以返回一个错误#N/A。233章通过Excel创建并处理电子表格3.3Excel公式和函数第二十三页，共五十二页，编辑于2023年，星期一3.3.4Excel中常用函数的应用9）逻辑判断函数IF(logical_test,[value_if_true],[value_if_false])功能：如果指定条件的计算结果为TRUE，IF函数将返回某个值；如果该条件的计算结果为FALSE，则返回另一个值。提示：在Excel2010中，最多可以使用64个IF函数进行嵌套，以构建更复杂的测试条件。也就是说，IF函数也可以作为value_if_true和value_if_false参数包含在另一个IF函数中。243章通过Excel创建并处理电子表格3.3Excel公式和函数第二十四页，共五十二页，编辑于2023年，星期一3.3.4Excel中常用函数的应用9）逻辑判断函数（续）例如：=IF(A2>=60,"及格","不及格")表示，如果单元格A2中的值大于等于60，则显示“及格”字样，否则显示“不及格”字样；=IF(A2>=90,"优秀",IF(A2>=80,"良好",IF(A2>=60,"及格","不及格")))表示下列对应关系：253章通过Excel创建并处理电子表格3.3Excel公式和函数单元格A2中的值公式单元格显示的内容A2>=90优秀90>A2>=80良好80>A2>=60及格A2<60不及格第二十五页，共五十二页，编辑于2023年，星期一3.3.4Excel中常用函数的应用10）当前日期和时间函数NOW()功能：返回当前计算机系统的日期和时间。当将数据格式设置为数值时，将返回当前日期和时间所对应的序列号，该序列号的整数部分表明其与1900年1月1日之间的天数。11）函数YEAR(serial_number)

功能：返回指定日期对应的年份。返回值为1900到9999之间的整数例如：=YEAR(A2)当在A2单元格中输入日期2008/12/27时，该函数返回年份2008。注意：公式所在的单元格不能是日期格式。263章通过Excel创建并处理电子表格3.3Excel公式和函数第二十六页，共五十二页，编辑于2023年，星期一3.3.4Excel中常用函数的应用12）当前日期函数

TODAY()功能：返回今天的日期。通过该函数，可以实现无论何时打开工作簿时工作表上都能显示当前日期；该函数也可以用于计算时间间隔，可以用来计算一个人的年龄。例如：=YEAR(TODAY())-1963假设一个人出生在1963年，该公式使用TODAY函数作为YEAR函数的参数来获取当前年份，然后减去1963，最终返回对方的年龄。273章通过Excel创建并处理电子表格3.3Excel公式和函数第二十七页，共五十二页，编辑于2023年，星期一3.3.4Excel中常用函数的应用13）平均值函数AVERAGE(number1,[number2],...)功能：求指定参数number1、number2……的算术平均值，最多可包含255个参数。例如：=AVERAGE(A2:A6)表示对单元格区域A2到A6中的数值求平均值=AVERAGE(A2:A6,C6)表示对单元格区域A2到A6中数值与C6中的数值求平均值。283章通过Excel创建并处理电子表格3.3Excel公式和函数第二十八页，共五十二页，编辑于2023年，星期一3.3.4Excel中常用函数的应用14）条件平均值函数AVERAGEIF(range,criteria,[average_range])

功能：对指定区域中满足给定条件的所有单元格中的数值求算术平均值例如：=AVERAGEIF(A2:A5,"<5000")表示求单元格区域A2:A5中小于5,000的数值的平均值；=AVERAGEIF(A2:A5,">5000",B2:B5)表示对单元格区域B2:B5中与单元格区域A2:A5中大于5000的单元格所对应的单元格中的值求平均值。293章通过Excel创建并处理电子表格3.3Excel公式和函数第二十九页，共五十二页，编辑于2023年，星期一3.3.4Excel中常用函数的应用15）多条件平均值函数AVERAGEIFS(average_range,criteria_range1,criteria1,[criteria_range2,criteria2],...)

功能：对指定区域中满足多个条件的所有单元格中的数值求算术平均值例如：=AVERAGEIFS(A1:A20,B1:B20,">70",C1:C20,"<90")表示对区域A1:A20中符合以下条件的单元格的数值求平均值：B1:B20中的相应数值大于70、且C1:C20中的相应数值小于90。303章通过Excel创建并处理电子表格3.3Excel公式和函数第三十页，共五十二页，编辑于2023年，星期一3.3.4Excel中常用函数的应用16）计数函数COUNT(value1,[value2],...)功能：统计指定区域中包含数值的个数。只对包含数字的单元格进行计数。例如：=COUNT(A2:A8)表示统计单元格区域A2到A8中包含数值的单元格的个数。17）计数函数COUNTA(value1,[value2],...)功能：统计指定区域中不为空的单元格的个数。可对包含任何类型信息的单元格进行计数。例如：=COUNTA(A2:A8)表示统计单元格区域A2到A8中非空单元格的个数。313章通过Excel创建并处理电子表格3.3Excel公式和函数第三十一页，共五十二页，编辑于2023年，星期一3.3.4Excel中常用函数的应用18）条件计数函数COUNTIF(range,criteria)功能：统计指定区域中满足单个指定条件的单元格的个数例如：=COUNTIF(B2:B5,">55")表示统计单元格区域B2到B5中值大于55的单元格的个数。323章通过Excel创建并处理电子表格3.3Excel公式和函数第三十二页，共五十二页，编辑于2023年，星期一3.3.4Excel中常用函数的应用19）多条件计数函数COUNTIFS(criteria_range1,criteria1,[criteria_range2,criteria2]…)

功能：统计指定区域内符合多个给定条件的单元格的数量。可以将条件应用于跨多个区域的单元格，并计算符合所有条件的次数。例如：=COUNTIFS(A2:A7,">80",B2:B7,"<100")统计单元格区域A2到A7中包含大于80的数，同时在单元格区域B2到B7中包含小于100的数的行数。333章通过Excel创建并处理电子表格3.3Excel公式和函数第三十三页，共五十二页，编辑于2023年，星期一3.3.4Excel中常用函数的应用20）最大值函数MAX(number1,[number2],...)功能：返回一组值或指定区域中的最大值例如：=MAX(A2:A6)表示从单元格区域A2:A6中查找并返回最大数值。21）最小值函数MIN(number1,[number2],...)功能：返回一组值或指定区域中的最小值例如：=MIN(A2:A6)表示从单元格区域A2:A6中查找并返回最小数值。343章通过Excel创建并处理电子表格3.3Excel公式和函数第三十四页，共五十二页，编辑于2023年，星期一3.3.4Excel中常用函数的应用22）排位函数RANK.EQ(number,ref,[order])

RANK.AVG(number,ref,[order])功能：返回一个数值在指定数值列表中的排位；如果多个值具有相同的排位，使用函数RANK.AVG将返回平均排位；使用函数RANK.EQ则返回实际排位。例如：=RANK.EQ("3.5",A2:A6,1)表示求取数值3.5在单元格区域A2:A6中的数值列表中的升序排位。353章通过Excel创建并处理电子表格3.3Excel公式和函数第三十五页，共五十二页，编辑于2023年，星期一3.3.4Excel中常用函数的应用23）文本合并函数CONCATENATE(text1,[text2],...)功能：将几个文本项合并为一个文本项。可将最多255个文本字符串联接成一个文本字符串。联接项可以是文本、数字、单元格地址或这些项目的组合。例如：=CONCATENATE(B2,"",C2)表示将单元格B2中的字符串、空格字符以及单元格C2中的值相连接，构成一个新的字符串。提示：也可以用文本连结运算符“&”代替CONCATENATE函数来联接文本项。例如，=A1&B1与=CONCATENATE(A1,B1)返回的值相同。363章通过Excel创建并处理电子表格3.3Excel公式和函数第三十六页，共五十二页，编辑于2023年，星期一3.3.4Excel中常用函数的应用24）截取字符串函数MID(text,start_num,num_chars)功能：从文本字符串中的指定位置开始返回特定个数的字符。例如：=MID(A2,7,4)表示从单元格A2中的文本字符串中的第7个字符开始提取4个字符。373章通过Excel创建并处理电子表格3.3Excel公式和函数第三十七页，共五十二页，编辑于2023年，星期一3.3.4Excel中常用函数的应用25）左侧截取字符串函数LEFT(text,[num_chars])

功能：从文本字符串最左边开始返回指定个数的字符，也就是最前面的一个或几个字符。例如：=LEFT(A2,4)表示从单元格A2中的文本字符串中提取前四个字符。26）右侧截取字符串函数RIGHT(text,[num_chars])功能：从文本字符串最右边开始返回指定个数的字符，也就是最后面的一个或几个字符。例如：=RIGHT

(A2,4)表示从单元格A2中的文本字符串中提取后四个字符。383章通过Excel创建并处理电子表格3.3Excel公式和函数第三十八页，共五十二页，编辑于2023年，星期一3.3.4Excel中常用函数的应用27）删除空格函数TRIM(text)

功能：删除指定文本或区域中的空格。在从其他应用程序中获取带有不规则空格的文本时，可以使用函数TRIM。例如：=TRIM("第1季度")表示删除中文文本的前导空格、尾部空格以及字间空格。28）字符个数函数LEN(text)功能：统计并返加指定文本字符串中的字符个数。例如：=LEN(A2)表示统计位于单元格A2中的字符串的长度。393章通过Excel创建并处理电子表格3.3Excel公式和函数第三十九页，共五十二页，编辑于2023年，星期一3.3.4Excel中常用函数的应用2.常用函数在实际工作中的应用在案例工作簿文档“3.3员工档案及工资表（1）”中运用公式及函数。1）运用公式及函数完善员工档案表在“员工档案表”工作表中，需要运用公式和函数分别提取员工的生日、计算出员工的年龄、工龄以及工龄工资。①提取员工生日：公式1：=CONCATENATE(MID(F4,7,4),"年",MID(F4,11,2),"月",MID(F4,13,2),"日")公式2：=MID(F5,7,4)&"年"&MID(F5,11,2)&"月"&MID(F5,13,2)&"日"403章通过Excel创建并处理电子表格3.3Excel公式和函数第四十页，共五十二页，编辑于2023年，星期一3.3.4Excel中常用函数的应用②计算员工年龄：输入函数“=INT((TODAY()-G4)/365)”③计算员工的工龄：输入函数“=INT((TODAY()-J4)/365)”④计算工龄工资：每满一年工龄工资增加50元，用工龄乘以50即可计算工龄工资，可以通过绝对引用或已定义名称来计算。使用绝对引用：“=K4*基础数据!$B$4”，使用定义名称：常量“50”已事先被命名为“工龄工资_每年”。输入公式“=K5*工龄工资_每年”413章通过Excel创建并处理电子表格3.3Excel公式和函数第四十一页，共五十二页，编辑于2023年，星期一3.3.4Excel中常用函数的应用⑤计算基础工资：输入函数“=SUM(L4:M4)”。⑥统计全部员工数量：在“基础数据”工作表的“员工总人数”处输入函数“=COUNTA(员工档案表!A4:A38)”。⑦统计女员工的数量：在“基础数据”工作表的“女性员工”处输入函数“=COUNTIF(员工档案表!C4:C38,"女")”。423章通过Excel创建并处理电子表格3.3Excel公式和函数第四十二页，共五十二页，编辑于2023年，星期一3.3.4Excel中常用函数的应用⑧统计工资数据在“基础数据”工作表中的相应单元格中依次输入下列函数以计算相关数据：基本工资总额：=SUM(基本工资)，基本工资列已被定义名称，所以可直接被求和函数引用。管理人员工资总额：=SUMIF(员工档案表!D4:D38,"管理",基本工资)，用条件求和函数计算“部门”属于“管理”的所有人员的基本工资总和。平均基本工资：=AVERAGE(基本工资)。本科生平均基本工资：=AVERAGEIF(员工档案表!I4:I38,"本科",基本工资)，用条件求平均值函数计算“学历”为“本科”的所有人员的平均基本工资。最高基本工资：=MAX(基本工资)最低基本工资：=MIN(基本工资)433章通过Excel创建并处理电子表格3.3Excel公式和函数第四十三页，共五十二页，编辑于2023年，星期一3.3.4Excel中常用函数的应用2）运用公式及函数完善1月工资表在“1月工资表”中，需要利用函数和公式获取员工的姓名、所在的部门、员工的基础工资，并计算应付工资、应交个人所得税、实付工资等工资项目。①获取员工姓名、部门和基础工资：利用VLOOKUP函数从员工档案表中直接获取相应数据。获取姓名：=VLOOKUP(B4,全体员工资料,2,FALSE)获取部门：=VLOOKUP(B4,全体员工资料,4,FALSE)获取基础工资：=VLOOKUP(B4,全体员工资料,14,FALSE)443章通过Excel创建并处理电子表格3.3Excel公式和函数第四十四页，共五十二页，编辑于2023年，星期一3.3.4Excel中常用函数的应用②计算应付工资应付工资合计＝基础工资+奖金+补贴－扣除病事假。③计算应纳税所得额应纳税所得额＝应付工资合计－社保费用－费用减除标准。应纳税所得额必须大于等于零，小于零则不用交税，需要用IF函数进行判断。输入函数“=IF((I4-J4-3500)>0,I4-J4-3500,0)”；也可以通过绝对引用基础数据表中的减除标准构建函数“=IF((I5-J5-基础数据!$F$12)>0,I5-J5-基础数据!$F$12,0)”453章通过Excel创建并处理电子表格3.3Excel公式和函数第四十五页，共五十二页，编辑于2023年，星期一3.3.4Excel中常用函数的应用④计算个人所得税通过多级IF函数嵌套，可构建出个人所得税计算公式，并通过ROUND函数对计算结果保留2位小数。个人所得税税率表可参见“基础数据”表中所列。=ROUND(IF(K4<=1500,K4*0.03,IF(K4<=4500,K4*0.1-105,IF(K4<=9000,K4*0.2-555,IF(K4<=35000,K4*0.25-1005,IF(K4<=55000,K4*0.3-2755,IF(K4<=80000,K4*0.35-5505,K4*0.45-13505)))))),2)⑤计算实付工资实付工资＝应付工资合计-扣除社保-应交个人所得税。463章通过Excel创建并处理电子表格3.3Excel公式和函数第四十六页，共五十二页，编辑于2023年，星期一3.3.5公式与函数常见问题在输入公式或函数的过程中，当输入有误时，单元格中常常会出现各种不同的错误结果。对这些提示的含义有所了解，有助于更好地发现并修正公式或函数中的错误。1.常见错误值列表473章通过Excel创建并处理电子表格3.3Excel