《Excel-2016数据处理与分析》教学课件05公式与函数的应用

模块5公式与函数的应用本模块主要学习Excel公式与函数的使用方法：从基础应用到行业函数应用。不仅让读者掌握所讲解的公式与函数的使用方法，还能教会大家快速学习本模块中没有讲解的函数，并在办公中进行实际应用。学习目标本模块讲解的公式与函数知识较多，需要重点掌握的最常用的函数有：简单公式、IF、AND、OR、TEXT、RIGHT、PMT系列、SUM、AVERAGE、MIN、MAX、YEAR、COUNTIF、RANK、ROUND、INT函数等。学习重点5.1

函数的基础函数是Excel中被定义好的公式模块，通过参数和运算方式得出结果，是计算数据的一大利器。在Excel中有11类函数，大约400个，虽然应用在不同的领域，有不同的使用方法，但是它们有共同的应用基础。5.1.1

函数结构Excel的函数结构大体相同，都是由几个关键部分构成的：等号、函数名、括号、参数和分隔符。其中，等号、函数名、括号、参数是必须具有的。例如，“=AVERAGE（B3：B30）+D20-10”就是一个典型的公式，因为它既包含了函数及函数参数、运算符，也包含了单元格地址和数值，如图5-1所示。5.1.1

函数结构5.1.1函数结构·=（等号）：公式必须以“=”开始，这也是其区别于其他常规数据的主要特点，否则Excel系统会自动将其识别为常规数据。·单元格引用：要进行运算的单元格地址，分为单个单元格、单元格区域、同一工作簿其他工作表中的单元格或其他工作簿某张工作表中的单元格。·运算符：公式中的基本元素就是运算符，与日常使用的运算符号相似，是指对公式中的元素进行特定类型的运算，不同的运算符进行不同的运算，如“+（加）”“-（减）”“*（乘）”和“/（除）”等。5.1.1函数结构·数值或字符串：包括文本、数字等各类数据，如图5-1中所示的“10”。同时，员工家庭住址、500和CD0001等都属于数值或字符串。·函数及参数：函数及函数参数也是公式的基本元素之一。参数主要是指参加运算的一系列数据，可以是函数、数值、单元格引用及自定义的名称等。在公式中，参数的个数不等，用户可以根据实际需要计算的数据来设置相应的参数个数。5.1.2

查看函数帮助系统函数帮助系统分为在使用函数之前和使用函数中的帮助系统，能够帮助用户了解函数的含义及函数的适用范围，下面简单介绍3种查看函数帮助系统的方法。1.在使用函数前查看函数帮助系统在使用函数前，用户可能还不太明白某些函数的意义，这时可以使用函数帮助系统了解函数，如查找“SUM”函数的具体含义和使用范围，操作步骤如下。步骤1打开空白工作簿，单击“文件”选项卡，进入“信息”界面，单击“MicrosoftExcel帮助”按钮，打开网页，如图5-2所示。步骤2在搜索栏中输入“SUM”，再按Enter键，关于“SUM”函数的相关知识都会出现，如图5-3所示。5.1.2查看函数帮助系统实现步骤视频5.1.2查看函数帮助系统2.在函数使用中查看函数帮助系统在设置参数过程中，发现函数或其中参数的含义和设置方法不清楚或完全不知道，可以使用查看函数帮助系统来具体了解，操作步骤如下。步骤1打开空白文件簿，在编辑栏中输入函数“=COUNTIF（”，这时需要查看函数“COUNTIF”的含义，单击编辑栏下的“COUNTIF（range，criteria）”，打开网页，如图5-4所示。步骤2直接查看到COUNTIF函数的含义、适用范围和使用方法，如图5-5所示。5.1.2查看函数帮助系统实现步骤视频5.1.2

查看函数帮助系统3.在函数使用中利用“插入函数”查看函数帮助系统在函数使用中，不懂参数的含义，在函数框中直接查看，操作步骤如下。步骤1在编辑栏中将鼠标指针定位在函数参数括号中，单击“插入函数”按钮，打开“函数参数”对话框，如图5-6所示。步骤2在“函数参数”对话框下方有简单的函数使用说明，如图5-7所示。5.1.2查看函数帮助系统实现步骤视频5.2

逻辑函数在Excel2016中，常用的逻辑函数包括IF函数、AND函数、OR函数、TRUE函数、FALSE函数、NOT函数等。5.2.1IF函数IF函数是Excel中常用的逻辑函数，如果满足规定的数值条件，就返回TRUE，如果不满足规定的数值条件，就返回FALSE。当需要进行条件检测时，可使用IF函数对数值和公式进行条件检测。函数语法：IF（logical_test，value_if_true，[value_if_false]）。参数解析：logical_test，必填，是指要测试的条件。value_if_true，必填，是指logical_test的结果为TRUE时希望返回的值。value_if_false，可选填，是指logical_test的结果为FALSE时希望返回的值。例如：=IF（C2=“Yes”，1，2），若C2等于Yes，则返回1，否则返回2。=IF（C2=1，“Yes”，“No”），若C2等于1，则返回Yes，否则返回No。5.2.1IF函数【实例5-1】使用IF函数判定员工绩效是否完成公司销售部老王要对业务员6月的销售绩效进行考核，已统计各个员工6月的实际销售业绩数据，规定销售额需要大于等于174000才算完成考核任务，反之则未完成。老王使用IF函数，快速地得出判定结果，他的操作步骤如下。步骤1打开素材文件中的“素材\模块5\5.2\员工绩效表.xlsx”文件，选择D3单元格，在编辑栏中输入函数“=IF（C3>=174000，"完成"，"未完成"）”，按Enter键确认，如图5-8所示。步骤2选中D3单元格，拖动填充柄填充数据公式至D9单元格，判断出哪些员工完成了任务，哪些员工未完成任务，如图5-9所示。5.2.1IF函数实现步骤视频5.2.2AND函数AND函数是一个逻辑函数，可把它理解为“一错全错，全对才对”函数。任一条件不满足全错，所有条件满足才通过，即当所有参数的逻辑值为真时，返回TRUE，当有一个参数的逻辑值为假时，返回FALSE。函数功能：它是一个逻辑函数，用于确定测试中的所有条件是否均为TRUE。函数语法：AND（logical1，[logical2]，...）。函数解析：logical1，必填，是指第一个想要测试且计算结果可为TRUE或FALSE的条件。logical2，可选填，是指其他想要测试且计算结果可为TRUE或FALSE的条件（最多255个条件）。例如：=AND（A2>1，A2<100），若A2大于1且小于100，则显示TRUE，否则显示FALSE。5.2.2AND函数【实例5-2】使用AND函数判断员工是否可以晋升公司需要对人事结构进行调整，同时也需要对员工职位进行变动，这样才能更好地安排员工到合适岗位，调动其工作的积极性。判断员工是否可以晋升，并不能单纯地根据决策者的主观感受，还需要结合员工的工作能力综合判断，这时使用AND函数再恰当不过了，操作步骤如下。步骤1打开素材文件中的“素材\模块5\5.2\员工信息管理表.xlsx”文件，选择I2单元格，在编辑栏中输入函数“=IF（AND（员工评定!E2：E16="优秀"），"晋"，””）”，按Enter键，得出晋升结果，如图5-10所示。步骤2选中I2单元格，拖动填充柄填充数据公式至G16单元格，如图5-11所示。5.2.2AND函数实现步骤视频5.2.3OR函数任一条件满足，则OR函数结果显示为TRUE，若全部条件不满足，则结果显示为FALSE。函数功能：它是一个逻辑函数，用于确定测试中的所有条件是否均为TRUE。函数语法：OR（logical1，[logical2]，...）。函数解析：logical1，必填，是指第一个想要测试且计算结果可为TRUE或FALSE的条件。logical2，可选填，是指其他想要测试且计算结果可为TRUE或FALSE的条件（最多255个条件）。例如：=OR（A2>1，A2<100），若A2大于1或小于100，则显示TRUE，否则显示FALSE。【实例5-3】使用OR函数判断员工是否退休员工到了退休年龄，就需要协商是否退休或返聘。每隔一段时间都会安排员工的退休事宜，在员工信息表中老李直接用OR函数一次性判断出员工是否退休，操作步骤如下。5.2.3OR函数步骤1打开素材文件中的“素材\模块5\5.2\员工信息管理表1.xlsx”文件，选择N3单元格，在编辑栏中输入函数“=IF（OR（AND（I4="男"，H4>=60），AND（I4="女"，H4>=55）），"退休"，""）”，按Enter键，可以得出退休结果，如图5-12所示。步骤2选中N3单元格，拖动填充柄填充数据公式至N17单元格，如图5-13所示。5.2.3OR函数实现步骤视频5.2.4TRUE函数和FALSE函数TRUE函数与FALSE函数是逻辑函数，使用频率相对较低。它们都可以对数值结果进行判定，一般符合条件判定的结果表达为TRUE，不符合条件判定的结果表达为FALSE。函数功能：TRUE函数和FALSE函数是逻辑函数，可运用到判断设置的条件是否成立。函数解析：返回逻辑值TRUE。希望基于条件返回值TRUE时，可使用此函数。例如，“=IF（A1=1，TRUE（））”。还可直接在单元格和公式中输入值TRUE，而不使用此函数，如“=IF（A1=1，"TRUE"）”。如果满足条件，Excel在两个示例中均返回TRUE；如果不满足条件，Excel在两个示例中均返回FALSE。5.2.4TRUE函数和FALSE函数【实例5-4】使用TRUE函数和FALSE函数对面试评定结果进行判断公司人事部小李需要对面试者进行评定，判断是否继续参加复试，小李使用了TRUE函数和FALSE函数来对结果进行判断，符合的结果为TRUE，不符合的结果为FALSE，操作步骤如下。步骤1打开素材文件中的“素材\模块5\5.2\面试评估.xlsx”文件，选择C2单元格，在编辑栏中输入函数“=IF（B2>60，"TRUE"）”，按Enter键确认，如图5-14所示。步骤2选中C2单元格，拖动填充柄填充数据公式至G17单元格，TRUE为能参加复试的面试者，FALSE为没有资格参加复试的面试者，如图5-15所示。5.2.4TRUE函数和FALSE函数实现步骤视频5.2.5NOT函数NOT函数是逻辑函数，可将其理解为一种反函数。原本是错的判定结果为对的，对的判定结果为错的，即当判定结果为TRUE时，NOT函数返回的结果相反，结果为FALSE。例如，“NOT（3+2）=5”，3+2=5，正常判定结果为TRUE，但因为是NOT函数，所以最终判定结果为FALSE。函数功能：NOT函数是逻辑函数，它会对其参数的值进行求反。函数语法：NOT（logical）。函数解析：logical，是指可以计算出TRUE或FALSE的值或表达式。如果logical为FALSE，NOT返回Yes；如果logical为TRUE，NOT返回No。例如：“=NOT（FALSE）”，对FALSE求反（结果为Yes）。“=NOT（1+1=2）”，对计算结果为TRUE的等式求反（结果为No）。5.2.5NOT函数【实例5-5】使用NOT函数筛选应聘人员的信息公司人事部小李需要对面试者进行进一步评定，判断面试者是否通过了第一轮面试，第一轮面试需要分数达到80分以上才能参加复试，小李使用了NOT函数来对结果进行判断，操作步骤如下。步骤1打开素材文件中的“素材\模块5\5.2\面试评估1.xlsx”文件，选择C2单元格，在编辑栏中输入函数“=NOT（B2<80）”，按Enter键确认，如图5-16所示。步骤2选中C2单元格，拖动填充柄填充数据公式至G17单元格，TRUE为能参加复试的面试者，FALSE为没有资格参加复试的面试者，如图5-17所示。5.2.5NOT函数实现步骤视频5.3

文本函数编辑处理表格中的文本数据，不能用蛮力解决，如提取字符中的某部分数据、将几个文本合并为一个字符串等，依靠Excel中的文本函数才是“王道”。下面介绍几个常用和实用的文本函数。5.3.1LEFT函数LEFT函数统计出从字符串左侧算起指定数量的字符。简单理解为从文本字符串第一个字符开始返回指定个数的字符，方向从左到右。函数功能：可以统计出从字符串左侧算起指定数量的字符。函数语法：LEFT（string，length）。函数解析：string，必填，是指从中返回最左边的字符的字符串表达式。若字符串包含Null，则返回空值。length，必填，变体（长）。数值表达式指示返回多少个字符。若为0，零长度字符串，则返回空值。若大于或等于字符串中的字符数，则返回整个字符串。5.3.1LEFT函数【实例5-6】使用LEFT函数获取货物长度为了更好地对存储货物进行管理，现需要对新进的一批货物统计其规格大小，然后将统计出的规格数据详细地记录在表格中，仓库管理员小王运用LEFT函数对货物的长度进行了快速统计，他的操作步骤如下。步骤1打开素材文件中的“素材\模块5\5.3\仓储管理表.xlsx”文件，选中D2单元格，在编辑栏中输入函数“=LEFT（C2，1）”，按Enter键确认，如图5-18所示。步骤2选中D2单元格，拖动填充柄填充数据公式至D16单元格，就会知道所有物品的长度，如图5-19所示。5.3.1LEFT函数实现步骤视频5.3.2MID函数MID函数可以统计出字符串中指定数量的字符，也就是从字符串的中间位置提取指定字符串。所以，它的参数有3个：字符串文本、开始的位置和提取字符长度。函数语法：MID（string，start，［length］）。函数解析：string，必填，返回字符串表达式，若字符串包含Null，则返回Null。start，必填，字符串中字符的开始位置，如果start大于字符串中的字符数，MID返回零长度字符串（“”）。length，可选填，变体（长），要返回的字符数，若省略或超过文本中的字符数（包括开始处的字符），则返回整个字符串。5.3.2MID函数【实例5-7】使用MID函数获取物品宽度仓库管理员小王为了更好地对存储货物进行管理，现需要对新进的一批货物统计规格大小，然后将统计出的规格数据详细地记录在表格中，小王统计出了物品长度，需要继续统计出货物的宽度，他运用MID函数将物品的宽度也一并统计出，操作步骤如下。步骤1打开素材文件中的“素材\模块5\5.3\仓储管理表1.xlsx”文件，选择E2单元格，在编辑栏中输入函数“=MID（C2，3，1）”，按Enter键确认，如图5-20所示。步骤2选中E2单元格，拖动填充柄填充数据公式至E16单元格，所有物品的宽度将会自动显现在表格中，如图5-21所示。5.3.2MID函数实现步骤视频5.3.3RIGHT函数RIGHT函数是文本函数，该函数的功能是从指定单元格数据的右端取指定个数字符，提取方向从右到左，与LEFT提取字符方向相反。函数语法：RIGHT（string，length）。函数解析：string，必填，是指字符串表达式从中返回最右侧的字符。若字符串包含Null，则返回Null。length，必填，变体（长），指示返回多少个字符的数值表达式。若为0，零长度字符串（“”），则返回空值。若大于或等于字符串中的字符数，则返回整个字符串。例如：“=RIGHT（A2，5）”，返回第一个字符串的最后5个字符。5.3.3RIGHT函数【实例5-8】使用RIGHT函数获取物品高度仓库管理员小王需要向外包装加工厂提供仓储物品的体积数据，依此让工厂生产出一批能最大限度容纳仓储物品的包装盒，如何在数据表格中快速获取物品的高度呢？小王使用RIGHT函数简单、快捷地获取了仓储物品的高度，他的操作步骤如下。步骤1打开素材文件中的“素材\模块5\5.3\仓储管理表2.xlsx”文件，选择F2单元格，在编辑栏中输入函数“=RIGHT（C2，1）”，按Enter键，如图5-22所示。步骤2选中F2单元格，拖动填充柄填充数据公式至F16单元格，所有物品的宽度将会自动显现在表格中，如图5-23所示。5.3.3RIGHT函数实现步骤视频5.3.4TEXT函数TEXT函数可理解为更改数值显示方式的函数，它可用于将数值转换为按指定数字格式表示的文本，如财务报表中的大写数字。函数功能：TEXT函数可通过格式代码向数字应用格式，进而更改数字的显示方式。函数语法：=TEXT（想要设置格式的值，"相应格式代码"）。函数解析：如“=TEXT（1234.567，"$#，##0.00"）”，货币带有1个千位分隔符和2个小数，如$1，234.57。例如：=TEXT（TODAY（），"MM/DD/YY"），目前日期采用YY/MM/DD格式，如12/03/14。=TEXT（TODAY（），"DDDD"），一周中的当天，如周日。=TEXT（NOW（），"H：MMAM/PM"），当前时间，如下午1：29。5.3.4TEXT函数【实例5-9】使用TEXT函数将小写数字转换为大写数字某公司财务部小张为了确保财务报表中数据金额的准确性（通常这些数据都是由阿拉伯数字和数字的中文大写形式组成的）避免输入出现偏差，使用TEXT函数，将数据表格中的数字转换为中文大写数字，操作步骤如下。步骤1打开素材文件中的“素材\模块5\5.3\外勤报销单.xlsx”文件，选择D13单元格，在编辑栏中输入函数“=TEXT（D12*100，"[DBnum2]0佰0拾0圆0角0分"）”，按Ctrl+Enter组合键确认，如图5-24所示。步骤2Excel自动将D12单元格中的合计数字转换为中文大写数字并显示在D13单元格中，如图5-25所示。5.3.4TEXT函数实现步骤视频5.4

财务函数在各类办公活动中，财务函数是十分常用的，运用范围也十分广泛。其中最为常用的有8个：PMT函数、PPMT函数、IPMT函数、ISPMT函数、PV函数、FV函数、SLN函数和SYD函数。5.4.1PMT函数PMT函数即年金函数，该函数可根据固定利率等额分期付款方式，判定结果为贷款的每期付款额，用于计算贷款后每期应还款金额。函数语法：PMT（rate，nper，pv，[fv]，[type]）。函数解析：rate，必需，贷款利率。nper，必需，该项贷款的付款总数。pv，必需，现值，可理解为一系列未来付款额在现在所价值的总额，也称本金。fv，可选填，未来值，或在最后一次付款后希望得到的现金余额。若省略fv，则假定其值为0，即贷款的未来值是0。type，可选填。数字0（零）或1指示支付时间。例如：“=PMT（A12/12，A13*12，0，A14）”得到要在18年以后有一笔￥500000的年金，每月需存入的金额。5.4.1PMT函数【实例5-10】使用PMT函数计算出每期的还款本金额每月（或每期）还银行多少钱是大多数贷款人或准备贷款的人最关心的事情，不过，他们不能准确计算出具体金额。其实这是很简单的，用PMT函数就能轻松完成，操作步骤如下。步骤1打开素材文件中的“素材\模块5\5.4\每期还款金额.xlsx”文件，选中B4单元格，在编辑栏中输入函数“=PMT（B1，B2，B3）”，按Enter键确认，如图5-26所示。步骤2计算出每年需要的还款额，如图5-27所示。5.4.1PMT函数实现步骤视频5.4.2PPMT函数和IPMT函数PPMT函数是财务函数，该函数可根据固定利率和等额分期付款方式，判定结果为投资在某一给定期间内的本金偿还额。常用于计算贷款后每期所需要还款的本金和利息。函数语法：PPMT（rate，per，nper，pv，[fv]，[type]）、IPMT（rate，per，nper，pv，[fv]，[type]）函数解析：rate必需，各期利率。per必需，指定期数，该值必须在1到nper范围内。nper必需，年金的付款总期数。pv必需，现值即一系列未来付款当前值的总和。fv可选，未来值或在最后一次付款后希望得到的现金余额。若省略fv，则假定其值为0，即贷款的未来值是0。type可选，数字0或1，用于指定各期的付款时间是在期初还是期末。例如，“=PPMT（A2/12，1A3*12，A4）”得到贷款第1个月的本金偿付额。5.4.2PPMT函数和IPMT函数【实例5-11】使用PPMT函数和IPMT函数计算本金和利息额小王向银行贷款后，每年需要还款的金额分为本金和利息。因此，他想分别计算出每期需要还款的本金和利息，对每年的收入进行合理分配，聪明的小王使用了PPMT函数和IPMT函数，操作步骤如下。步骤1打开素材文件中的“素材\模块5\5.4\本金和利息.xlsx”文件，选中B4单元格，在编辑栏中输入函数“=PPMT（10%/12，10，8*12，B3，，1）”，按Enter键确认，完成对第8年支付本金的计算，如图5-28所示。步骤2选中B5单元格，在编辑栏中输入函数“=IPMT（10%/12，10，8*12，B3，，1）”，按Enter键确认，完成对第8年支付本金和支付利息的计算，如图5-29所示。5.4.2PPMT函数和IPMT函数实现步骤视频5.4.3ISPMT函数ISPMT函数是一种财务函数，ISPMT函数可计算特定投资期内要支付的利息。该函数可用于计算累计贷款利息。函数语法：ISPMT（rate，per，nper，pv）。函数解析：rate，必填，投资利率。per，必填，为计算利息的期数，且必须在1到nper之间的时间段。nper，必填，投资的付款期总数。pv，必填，投资的现值。对于贷款，pv是贷款金额。【实例5-12】使用ISPMT函数计算特定投资期内支付的利息小王需要一次性计算出8年的银行贷款累计利息，银行工作人员建议他使用ISPMT函数，操作步骤如下。5.4.3ISPMT函数步骤1打开素材文件中的“素材\模块5\5.4\贷款利息.xlsx”文件，选中B4单元格，在编辑栏中输入函数“=ISPMT（B1，8，20，B3）”，如图5-30所示。步骤2按Enter键，计算出8年累计所需要支付的利息，如图5-31所示。实现步骤视频5.4.4PV函数PV函数用于根据固定利率计算贷款或投资的现值。现值为一系列未来付款的当前值的累积和。常用于项目贷款或投资回报未来值的计算。函数语法：PV（rate，nper，pmt，[fv]，[type]）。函数解析：rate，必需，各期利率。nper，必需，年金的付款总期数。pmt，必需，每期的付款金额，在年金周期内不能更改。pmt，包括本金和利息，但不含其他费用或税金。fv，可选填，未来值，或在最后一次付款后希望得到的现金余额。如果省略fv，则假定其值为0。如果省略fv，则必须包括pmt参数。type，可选填，数字0或1，用于指定各期的付款时间是在期初还是期末。例如，“=PV（A3/12，12*A4，A2，，0）”得到在A2：A4中的条件下年金的现值。5.4.4PV函数【实例5-13】使用PV函数计算投资现值小王打算向银行贷一笔款，但不知道能向银行贷多少，已知银行的利率、分期时间和每期偿还金额，在疑惑之际朋友小李告诉他只需要使用PV函数就可以轻松计算出结果，操作步骤如下。步骤1打开素材文件中的“素材\模块5\5.4\贷款金额.xlsx”文件，选中B4单元格，在编辑栏中输入函数“=PV（B1/12，B2，-B3）”，如图5-32所示。步骤2按Enter键，在B4单元格中完成对贷款金额的计算，如图5-33所示。5.4.4PV函数实现步骤视频5.4.5FV函数FV函数是根据固定利率和等额分期付款方式，判定结果为投资的未来值的函数。用于计算投资的未来值。函数语法：FV（rate，nper，pmt，[pv]，[type]）。函数解析：rate，必需，指各期利率。nper，必需，指年金的付款总期数。pmt，必需，指各期所应支付的金额，在整个年金期间保持不变。通常pmt包括本金和利息，但不包括其他费用或税款。若省略pmt，则必须包括pv参数。pv为现值，即从该项投资开始计算时已经入账的款项，或为一系列未来付款的当前值的累积和。若省略pv，则假定其值为0，并且必须包括pmt参数。type，可选填，数字0或1，用于指定各期的付款时间是在期初还是期末。若省略type，则假定其值为0。5.4.5FV函数【实例5-14】使用FV函数预测投资效果小李计划在银行中存一笔钱，想了解在某银行存储5年且每年都在该银行继续存储一笔新的资金，5年后将收获本金和利息金额，从而判定是否值得投资。小李使用了FV函数计算出未来5年的收益，操作步骤如下。步骤1打开素材文件中的“素材\模块5\5.4\定存收益.xlsx”文件，选中B5单元格，在编辑栏中输入函数“=FV（B2，B3，B4，，1）”，如图5-34所示。步骤2按Enter键，在B5单元格中完成对未来5年投资收益的计算，如图5-35所示。5.4.5FV函数实现步骤视频5.4.6SLN函数SLN函数是一个期间内的资产的直线折旧函数。它是固定资产最理想的折旧方式：直线折旧。每一期的折旧值基本相同，但又区别于直接的平均法。函数语法：SLN（cost，salvage，life）。函数解析：cost，必需，指资产原值。salvage，必需，指折旧末尾时的值（有时也称资产残值）。life，必需，指资产的折旧期数（有时也称资产的使用寿命）。5.4.6SLN函数【实例5-15】使用SLN函数计算平均每年的折旧额工厂用20000元购进生产机器，使用年限为10年，预计净残值为200元，工厂负责人想知道未来10年的折旧额，财务部的小吴计划使用SLN函数，他的操作步骤如下。步骤1打开素材文件中的“素材\模块5\5.4\直线折旧.xlsx”文件，选中B6单元格，在编辑栏中输入函数“=SLN（$B$1，$B$2，$B$3）”，按Ctrl+Enter组合键，在B6单元格中完成第1年的年折旧率的计算，如图5-36所示。步骤2选中B6单元格，拖动填充柄填充数据公式到B15单元格，得到第2年到第10年的生产机器平均每年折旧额，如图5-37所示。5.4.6SLN函数实现步骤视频5.4.7SYD函数SYD函数用于计算某项资产在一定期间用年数总计法计算的折旧值，较多用于设备年限折旧额的计算。函数语法：SYD（cost，salvage，life，per）。函数解析：cost，必需，指资产原值。salvage，必需，指折旧末尾时的值（有时也称为资产残值）。life，必需，指资产的折旧期数（有时也称为资产的使用寿命）。per，必需，指期间，必须与life使用相同的单位。5.4.7SYD函数【实例5-16】使用SYD函数计算指定期折旧值某工厂的生产机器为20000元购进，使用年限为10年，预计净残值为200元，工厂负责人想求得未来10年的折旧率，财务部的小吴使用SYD函数计算出了年总的折旧额，他的操作步骤如下。步骤1打开素材文件中的“素材\模块5\5.4\固定资产.xlsx”文件，选中B6单元格，在编辑栏中输入函数“=SYD（$B$1，$B$2，$B$3，A6）”，按Enter键，如图5-38所示。步骤2选中B6单元格，拖动填充柄填充数据公式至B15单元格，在B6：B15单元格区域中完成每年折旧额的计算，如图5-39所示。5.4.7SYD函数实现步骤视频5.5

数学和统计函数在日常生活和工作中，数学和统计函数也是十分常用的。其运用范围也十分广泛，从统计个人一个月的消费情况到统计一个上千人公司的消费情况，都可以运用合适的数学和统计函数。5.5.1SUM函数SUM是求和函数，计算单元格区域中数字、逻辑值及数字的文本表达式之和。若参数中有错误或不能转换成数字的文本，则会导致错误。函数语法：SUM（number1，number2，…）。函数解析：number1，number2，…为1到255个待求和的参数。参数可以是常量、单元格区域、单元格引用、数组或另外函数的结果。例如：=SUM（1，2，3）表示求1、2、3数字的和，即=1+2+3=6。=SUM（A1：A5）表示求表中A列中A1至A5的和，即=A1+A2+A3+A4+A5。=SUM（A1：B3）表示求A1至B3这个区域单元格的和，即=A1+A2+A3+B1+B2+B3。5.5.1SUM函数【实例5-17】使用SUM函数计算季度销售总和电器城张老板要在一季度之前把公司的季度销售表制作出来，需要统计出一季度店铺各种电器的销售量，若是手动计算特别费时费力。张老板在助理小张的建议下使用SUM函数，快速计算出了一季度各种电器的销售量，他的操作步骤如下。步骤1打开素材文件中的“素材\模块5\5.5\季度销售表.xlsx”文件，选中B7单元格，在编辑栏中输入函数“=SUM（B3：B6）”，按Ctrl+Enter组合键确认并计算出“电脑”1~4月的销量总和，如图5-40所示。步骤2使用填充柄填充SUM函数到F7单元格，如图5-41所示。5.5.1SUM函数实现步骤视频5.5.2AVERAGE函数AVERAGE函数用于计算算术平均值，是最常用的函数之一。AVERAGE函数的参数可以是数字，或涉及数字的名称、数组或引用，若数组或单元格引用参数中有文字或空单元格，则忽略其值。若单元格包含零值，则计算在内。函数语法：AVERAGE（number1，number2，…）。函数解析：number1，number2，…为1到255个需要求平均值的参数。参数可以是常量、单元格区域、单元格引用、数组或另外函数的结果。例如：=AVERAGE（1，2，3）表示求1、2、3数字的平均值，即=（1+2+3）/3=2。=AVERAGE（A1：A5）表示求表中A列中A1至A5的平均值，即=（A1+A2+A3+A4+A5）/5。=AVERAGE（A1：B3）表示求A1至B3这个区域单元格的平均值，即=（A1+A2+A3+B1+B2+B3）/6。5.5.2AVERAGE函数【实例5-18】使用AVERAGE函数计算平均销售数据公司老王需要制作季度的销售数据分析表，其中需要计算4个季度销售数据平均值，以此来分析公司的销售均值情况。此时，他直接使用AVERAGE函数完成操作，操作步骤如下。步骤1打开素材文件中的“素材\模块5\5.5\区域销售.xlsx”文件，选中F12单元格，在编辑栏中输入函数“=AVERAGE（F2：F11）”，如图5-42所示。步骤2按Enter键确认，得到第1季度到第4季度销售的平均值，如图5-43所示。5.5.2AVERAGE函数实现步骤视频5.5.3MAX函数和MIN函数MAX函数可返回一组数值中的最大值，MIN函数与之相反。可将两函数理解为“最大”“最小”函数。MAX函数与MIN函数通常可用于有数据比较的表格中，如销售表中查看销售业绩最好的人员。函数语法：MAX（number1，number2，…）、MIN（number1，number2，…）。函数解析：number1，number2，…为1到255个需要求平均值的参数。参数可以是常量、单元格区域、逻辑值和文本数值等的结果。例如：=MAX（1，2，3）表示求1、2、3数字的最大值，即=（1，2，3）=3。=MIN（A1：A5）表示求表中A列中A1至A5的最小值，即=（A1，A2，A3，A4，A5）。5.5.3MAX函数和MIN函数【实例5-19】使用MAX和MIN函数计算销售最高和最低数据公司老王需要对本年销售做出总结和分析，已经统计出各个区域的每个季度的销售总额，为了调动积极性，老王决定统计出销售数量最多的地区和最少的地区，他的操作步骤如下。步骤1打开素材文件中的“素材\模块5\5.5\区域销售1.xlsx”文件，选中F12单元格，在编辑栏中输入函数“=MAX（F2：F11）”，按Enter键确认，如图5-44所示。步骤2选中F13单元格，在编辑栏中输入函数“=MIN（F2：F11）”，按Enter键确认，如图5-45所示。5.5.3MAX函数和MIN函数实现步骤视频5.5.4VLOOKUP函数VLOOKUP函数是纵向（按列）查找和引用函数，返回结果为该列查询序列对应值，主要用于数据查找和匹配。函数结构：VLOOKUP（lookup_value，table_array，col_index_num，[range_lookup]）。函数解析：lookup_value表示要查找的值；table_array表示目标区域；col_index_num表示查找目标值所在的列数。5.5.4VLOOKUP函数【实例5-20】使用VLOOKUP函数自动查找各班次工资在生产或服务企业中，会安排早班、中班和晚班。不同的班次基本工资可能不一样（或补贴不一样），在这种情况下，财务小张需要快速自动地为不同班次的员工匹配对应的班次工资，为此他使用了VLOOKUP函数，操作方法如下。步骤1打开素材文件中的“素材\模块5\5.5\倒班工资.xlsx”文件，选中D4单元格，在编辑栏中输入函数“=VLOOKUP（$C2，班次工资表!$A$2：$B$4，2，0）”，按Enter键确认，如图5-46所示。步骤2选中D2单元格，拖动填充柄填充数据公式至D12单元格，如图5-47所示。5.5.4VLOOKUP函数实现步骤视频5.5.5CHOOSE函数CHOOSE函数根据给定的索引值，从参数串中选出相应值或操作。可简单理解为一个选择函数，从单元格区域中选择一个值，然后做出相应操作。函数语法：CHOOSE（index_num，value1[value2]，…）。函数解析：index_num，必需，用于指定所选定的数值参数。index_num必须是介于1到254之间的数字，或是包含1到254之间的数字的公式或单元格引用。若index_num为1，则CHOOSE返回value1；若为2，则CHOOSE返回value2，以此类推。若index_num小于1或大于列表中最后一个值的索引号，则CHOOSE返回#VALUE!错误值。【实例5-21】使用CHOOSE函数自动计算梯度提成比例在销售工作中，大部分都会根据提成的等级确定提成比例（提成等级会根据销售额的多少而定），提成比例往往都有规定，财务小林深知这点，因此，直接使用CHOOSE函数自动根据提成等级返回提成比例，操作方法如下。5.5.5CHOOSE函数步骤1打开素材文件中的“素材\模块5\5.5\提成等级.xlsx”文件，选中D2单元格，在编辑栏中输入函数“=CHOOSE（C2，5%，10%，15%，20%）”，按Enter键确认，如图5-48所示。步骤2选中D2单元格，拖动填充柄填充数据公式至D16单元格，如图5-49所示。实现步骤视频5.6

日期与时间函数日期和时间对于职场中或商务活动中的人员而言相当重要。时间就是金钱，一点都不夸张。遵守约定日期和时间重要，把握日期和时间也很重要。相对而言，更重要的是要计算出日期和时间，否则再守时的人也会不守时，因为前提不对。下面介绍几个常用的日期和时间函数。5.6.1YEAR函数和TODAY函数YEAR函数返回日期的年份值，可理解为“年”函数，该函数可将系列数转换为年。TODAY函数返回日期函数的当前日期，可理解为“当前日期”函数。函数语法：YEAR（日期数据）、TODAY（）。【实例5-22】使用YEAR函数和TODAY函数计算员工年龄员工到了国家规定的退休年龄，就需要同公司协商是否退休或返聘。某公司人事部老李需要对公司的员工安排退休事宜，对员工的年龄进行统计，了解员工是否符合退休年龄，他混合使用YEAR函数和TODAY函数来计算员工的年龄，操作步骤如下。步骤1打开素材文件中的“素材\模块5\5.6\人事信息表.xlsx”文件，选中F2单元格，在编辑栏中输入函数“=YEAR（TODAY（））-YEAR（$E2）”，按Enter键确认，如图5-50所示。步骤2选中F2单元格，拖动填充柄填充数据公式至F29单元格，如图5-51所示。5.6.1YEAR函数和TODAY函数实现步骤视频5.6.2WORKDAY函数WORKDAY函数返回在指定的若干个工作日之前或之后的日期（不包括周末和法定假日），常用于计算发票到期日、预期交货日或工作天数等。函数语法：WORKDAY（起始日期，天数，法定假日）。函数解析：其中法定假日参数可以不用设置，系统自动设别。【实例5-23】使用WORKDAY函数计算出任务完成日期工厂车间管理员小王需要计算出生产任务完成的日期，已知生产车间的开工日期和所给定的任务工期，如何计算出任务完成的具体日期呢？他使用了WORKDAY函数来完成，操作步骤如下。步骤1打开素材文件中的“素材\模块5\5.6\工作任务.xlsx”文件，选择E2单元格，在编辑栏中输入函数“=WORKDAY（C2，D2）”，按Enter键确认，如图5-52所示。步骤2选中E2单元格，拖动填充柄填充数据公式至E90单元格，计算出任务完成日期，如图5-53所示。5.6.2WORKDAY函数实现步骤视频5.6.3HOUR函数HOUR函数返回时间值的小时数，范围为0~23。由于判定结果为“小时”，我们可将其理解为“小时”函数。函数功能：小时数是介于0（12：00AM）到23（11：00PM）之间的整数。函数语法：HOUR（serial_number）。函数解析：serial_number，必需，时间值，其中包含要查找的小时数。时间值有多种输入方式：带引号的文本字符串（如“6：45PM”）、十进制数（如0.78125表示6：45PM）或其他公式或函数的结果，如TIMEVALUE（“6：45PM”）。例如：“=HOUR（0.75）”返回24小时的75%，为18；“=HOUR（2018-7-187：45）”返回日期/时间值的小时部分，结果为7。5.6.3HOUR函数【实例5-24】使用HOUR函数计算出停车时长某小区物业需要对近一周的停车场的外来车辆停车状况做出统计，以便进行停车收费，已知车辆进入停车场的时间和车辆离开停车场的时间，如何计算出车辆的停车时长呢？物业的小明使用HOUR函数快速地计算出车辆停车时长，操作步骤如下。步骤1打开素材文件中的“素材\模块5\5.6\停车时长.xlsx”文件，选中D4单元格，在编辑栏中输入函数“=HOUR（C4-B4）”，如图5-54所示。步骤2按Enter键确认，选中D4单元格，拖动填充柄填充数据公式至D17单元格，如图5-55所示。5.6.3HOUR函数实现步骤视频5.6.4MINUTE函数MINUTE函数返回时间值中的分钟整数（介于0到59之间的整数），最直白的记忆为“分钟”函数。函数语法：MINUTE（serial_number）。函数解析：serial_number，必需。一个时间值，其中包含要查找的分钟。时间值有多种输入方式：带引号的文本字符串（如“6：45PM”）、十进制数（如0.78125表示6：45PM）或其他公式或函数的结果，如TIMEVALUE（“6：45PM”）。例如：“=MINUTE（12：45：00）”返回12：45：00中时间值的分钟部分，结果为45。5.6.4MINUTE函数【实例5-25】使用MINUTE函数计算停车分钟数停车场负责人老万需要统计近一周停车场的停车情况，已知车辆的进入停车场时间和离开停车场时间，需要精确地计算出停车时间至分钟数，为此他使用了MINUTE函数，操作步骤如下。步骤1打开素材文件中的“素材\模块5\5.6\停车时长.xlsx”文件，选中D4单元格，在编辑栏中输入函数“=MINUTE（C4-B4）”，按Ctrl+Enter组合键确认，如图5-56所示。步骤2选中D4单元格，拖动填充柄填充数据公式至D17单元格，如图5-57所示。实现步骤视频5.7

其他函数除了以上所讲的逻辑函数、文本函数、财务函数及数学和统计函数外，还有其他函数在日常生活和工作中比较常见，如COUNTIF函数、RANK函数、ISERROR函数、ROUND函数、INT函数和RAND函数等。5.7.1COUNTIF函数COUNTIF函数是统计函数，是对指定区域中符合指定条件的单元格进行统计计数的函数。函数语法：COUNTIF（range，criteria）。函数解析：range，必需，指要进行计数的单元格组，区域可以包括数字、数组、命名区域或包含数字的引用。空白和文本值将被忽略。criteria，必需，用于决定要统计哪些单元格的数字、表达式、单元格引用或文本字符串。实例5-26使用COUNTIF函数统计学历情况在企业人才调查表中，领导需要快速知晓公司内部的本科学历人数，老王接到这个临时任务后，随即用COUNTIF函数统计，他的操作如下。步骤1打开素材文件中的“素材\模块5\5.7\员工信息管理表.xlsx”文件，选中B20单元格，在编辑栏中输入函数“=COUNTIF（G3：G17，A20）”，如图5-58所示。步骤2按Enter键确认，统计出本科学历的人数情况，如图5-59所示。5.7.1COUNTIF函数实现步骤视频5.7.2RANK函数RANK函数返回一列数字的数字排位，也就是从小到大或从大到小排序，最常见的是销量排名、成绩排名、生产排名等，因此，可简单理解为排名函数。函数语法：RANK（number，ref，[order]）。函数解析：number，必需，指要找到其排位的数字。ref，必需，指数字列表的数组，对数字列表的引用。ref中的非数字值会被忽略。order，可选填，是指一个指定数字排位方式的数字。如果order为0（零）或省略，MicrosoftExcel对数字的排位是基于ref为按照降序排列的列表。如果order不为零，MicrosoftExcel对数字的排位是基于ref为按照升序排列的列表。5.7.2RANK函数【实例5-27】使用RANK函数对销量进行排名销售部门需要对各地区的销售情况进行排名，以此调动各个地区的工作竞争积极性：奖励销量排名较高的地区，为销量排名不理想的地区进行分析总结。销售部门总负责人老李使用了RANK函数，操作步骤如下。步骤1打开素材文件中的“素材\模块5\5.7\区域销售.xlsx”文件，选中G2单元格，在编辑栏中输入公式“=RANK（F2，$F$2：$F$11，0）”，如图5-60所示。步骤2按Enter键确认，选中G2单元格，拖动填充柄填充数据公式至G11单元格，如图5-61所示。5.7.2RANK函数实现步骤视频5.7.3ISERROR函数ISERROR判断函数值是否正确，常被用到嵌套函数中，用于判定参数值是否为空、是否为计算错误值等，最终让嵌套主函数返回正确结果。与IF函数联用的频率通常较高。函数语法：ISERROR（表达式）。函数解析：表达式参数可以是任何有效的表达式。【实例5-28】使用ISERROR函数判断指定数据是否为错误值在考勤动态表中，老李看到：一旦A18单元格中的姓名值为空，B18：D18单元格区域中的值为“#N/A”，影响表格的外观样式，此时老李用IF函数和ISERROR函数的嵌套让B18：D18单元格区域显示为“—”，操作方法如下。5.7.3ISERROR函数步骤1打开素材文件中的“素材\模块5\5.7\考勤动态报表1.xlsx”文件，选中B18单元格，在编辑栏中输入函数“=IF（ISERROR（VLOOKUP（$A$18，$A$2：$E$13，2，0）），"—"，VLOOKUP（$A$18，$A$2：$E$13，2，0））”，按Enter键，选中C18单元格，在编辑栏中输入函数“=IF（ISERROR（VLOOKUP（$A$18，$A$2：$E$13，3，0）），"—"，VLOOKUP（$A$18，$A$2：$E$13，3，0））”，按Enter键，如图5-62所示。步骤2选中D18单元格，在编辑栏中输入函数“=IF（ISERROR（VLOOKUP（$A$18，$A$2：$E$13，4，0）），"—"，VLOOKUP（$A$18，$A$2：$E$13，4，0））”，如图5-63所示。5.7.3ISERROR函数5.7.3ISERROR函数步骤3在A18单元格中输入姓名时，B18：D18单元格区域中显示自动匹配的数据，如图5-64所示。实现步骤视频5.7.4ROUND函数ROUND函数将数字四舍五入到指定的位数。语法结构为ROUND（number，num_digits），其中number必需，指要四舍五入的数字。num_digits必需，指要进行四舍五入运算的位数。【实例5-29】使用ROUND函数将工资金额保留到“角”作为出纳的老李，从会计处收到的工资表单都是保留到分的实发工资数据，由于已没有分币，老李使ROUND函数统一将实发工资数据保留到“角”，操作方法如下。步骤1打开素材文件中的“素材\模块5\5.7\8月工资表.xlsx”文件，选中I2单元格，在编辑栏中输入函数“=ROUND（H2，1）”，如图5-65所示。步骤2按Ctrl+Enter组合键确认，并向下填充到I12单元格，Excel自动将对