Excel函数与公式.pptx

第二篇第二篇 EXCEL高级应用高级应用Excel2010是美国微软（Microsoft）公司推出的一种电子表格处理软件，是Microsoft Office 2010套装办公软件的一个重要组件。它具有以下几个方面的功能：数据分析处理Excel2010具有超强的数据分析能力，能够创建预算、分析调查结果以及进行财务数据分析。创建图表使用图标工具能够根据表格的具体数据创建多种类型的图表，这些既美观又实用的图表，可以让用户清楚地看到数字所代表的意义。绘制图形和结构图使用绘图工具和自选图形能够创建各种图形及结构图，达到美化工作表和直观显示逻辑关系的目的。使用外部数据库Excel2010能够通过访问不同类型的外部数据库，来增强该软件处理数据这一方面的功能。自动化处理Excel2010能够通过使用宏功能来进行自动化处理，实现单击鼠标就可以执行一个复杂任务的功能。第第4章章 函数与公式函数与公式4.1 EXCEL实例介绍表的建立表的建立根据小张店铺的情况，经过相关的分析，整个Excel工作簿中包含有进货清单、销售清单、库存清单、销售统计、分类统计报表、分类统计图表、商品资料、客户资料、其他资料设置等工作表。4.1.2 EXCEL中数据的管理与分析中数据的管理与分析数据输入函数与公式筛选与排序分类汇总数据透视表（图）4.2 EXCEL中数据的输入4.2.1 自定义下拉列表输入自定义下拉列表输入4.2.2 自定义序列与填充自定义序列与填充柄柄自定义填充序列是一组数据，可按重复方式填充列。通过工作表中现有的数据项或以临时输入的方式，可以创建自定义填充序列。条件格式条件格式在Excel中提供了一个功能非常独特的数据管理功能条件格式。通过设置数据条件格式，可以让单元格中的数据满足指定条件时就以特殊的标记（如：以红色、数据条、图标等）显示出来。该功能可以让单元格根据不同的应用环境所设置的条件发生变化。我们还可以在单元格中设置彩色的数据条，以数据条的长度来表示数值的大小4.2.4 数据输入技巧1特殊数据输入输入分数 在输入的分数前加上一个“0”和一个空格输入负数 除了直接输入负号和数字外，也可以使用括号来完成输入文本类型的数字 在输入的数据前面加上单引号输入特殊字符2快速输入大写中文数字3自动超链接的处理4.2.5 数据的舍入方法函数名称函数名称说明明CEILING CEILING 函数函数将数字舍入为最接近的整数或最接近的指定基数的倍数CEILING.PRECISE CEILING.PRECISE 函数函数将数字舍入为最接近的整数或最接近的指定基数的倍数。无论该数字的符号如何，该数字都向上舍入。EVEN EVEN 函数函数将数字向上舍入到最接近的偶数FLOOR FLOOR 函数函数向绝对值减小的方向舍入数字FLOOR.PRECISE FLOOR.PRECISE 函数函数将数字向下舍入为最接近的整数或最接近的指定基数的倍数。无论该数字的符号如何，该数字都向下舍入。INT INT 函数函数将数字向下舍入到最接近的整数MROUND MROUND 函数函数返回一个舍入到所需倍数的数字ODD ODD 函数函数将数字向上舍入为最接近的奇数ROUND ROUND 函数函数将数字按指定位数舍入ROUNDDOWN ROUNDDOWN 函数函数向绝对值减小的方向舍入数字ROUNDUP ROUNDUP 函数函数向绝对值增大的方向舍入数字TRUNC TRUNC 函数函数将数字截尾取整表 41 数值舍入函数1舍入到最接近的基数的倍数这样的函数有5个，其中MROUND函数按四舍五入进行舍入，CEILING函数按绝对值增大的方向舍入，FLOOR函数按绝对值减小的方向舍入，CEILING.PRECISE函数按算术值增大的方向舍入，FLOOR.PRECISE函数按算术值减小的方向舍入。另外，EVEN函数和ODD函数都是按绝对值增大的方向舍入。例如：公式“=MROUND(167,5)”的返回值为“165”；公式“=MROUND(168,5)”的返回值为“170”；公式“=CEILING(167,5)”的返回值为“170”；公式“=CEILING(-2.3,-1)”的返回值为“-3”；公式“=CEILING.PRECISE(-2.3,-1)”的返回值为“-2”；公式“=FLOOR(168,5)”的返回值为“165”；公式“=FLOOR.PRECISE(-2.3,-1)”的返回值为“-3”；公式“=EVEN(2.9)”的返回值为“4”；公式“=EVEN(-2.9)”的返回值为“-4”；公式“=ODD(1.9)”的返回值为“3”；公式“=ODD(-1.9)”的返回值为“-3”。2使用INT和TRUNC函数INT函数和TRUNC函数很相似，都能将一个数值转换为整数，但TRUNC函数是简单地将一个数值的小数部分去掉，INT函数则是将一个数值基于其小数部分的值向下取整。当参数为非负数时，这两个函数将返回相同的结果；当参数为负数时，它们将返回不同的结果。例如，公式“TRUNC（-17.3.）”其返回值为“-17”；而公式“INT（-17.3）”其返回值为“-18”。如果TRUNC函数接受一个额外（可选）的参数，可用于截取小数。例如，公式“TRUNC（75.6666，3）”将返回“75.666”（数值被截取为保留三位小数）。3.按小数点位数舍入按小数点位数进行的舍入的有ROUND函数、ROUNDDOWN函数和ROUNDUP函数。当小数点位数大于零时，即保留小数点后几位，当小数点位数小于零时，其实是指在小数点的左侧进行舍入，当小数点位数等于零时，即舍入到整数。这三个函数中ROUND函数按四舍五入进行舍入，ROUNDDOWN按绝对值减小的方向舍入，而ROUNDUP按绝对值增大的方向舍入。例如，公式：ROUND（3.1415926，4），其值为3.1416；公式：ROUND（3.1415926，0），其值为3；公式：ROUND（759.7852，2），其值为800；公式：=ROUNDDOWN(3.1415926，4)，其值为3.1415；公式：=ROUNDUP(3.1415926,2)，其值为3.15。4舍入为n位有效数字在进行数据处理时，有时候可能需要将一个数值舍入为特定位数的有效数字。如果要处理的数值是一个不带小数的正数，可使用公式“ROUNDDOWN（E1，3LEN（E1）”来进行处理，该公式将E1单元格中的数字舍入为三位有效数字。如果需要的结果不是两位有效数字，那么用需要的值替换公式中的3即可。如果要处理的数值是一个非整数或者一个负数，则可使用公式“ROUND（E1，E21INT（LOG10（ABS（E1）”来进行处理，该公式可以将E1单元格中的数字舍入为E2中指定了有效数字位数的数字；该公式可用于正负整数和非整数。例如，如果单元格E1的数据是2.546587，那么公式“ROUND（E1，E21INT（LOG10（ABS（E1）”将返回2.55（舍入为三个有效数字的数值）。5时间值舍入有些时候可能需要把一个时间值舍入到特定的分钟数。例如，在输入公司员工的工时记录时可能需要舍入到最接近的15分钟的倍数。以下给出了几种舍入时间值的不同方法：将B1单元格中的时间值舍入为最接近的分钟数，可使用公式“ROUND（B1*1440，0）/1440”，该公式将时间值乘以1440（以得到总分钟数），然后计算结果传递给ROUND函数，再把计算出的结果除以1440。如果B1单元格中的时间是“13：42：56”，则使用该公式将会返回“13：43：00”。将B1单元格中的时间值舍入为最接近的小时数，可使用公式“ROUND（B1*24，0）/24”，如果B1单元格中的时间值是“9：21：45”，公式将返回“9：00：00”。将B1单元格中的时间值舍入为最接近的15分钟的倍数，可使用公式“ROUND（B1*24/0.25，0）*（0.25/24）”，如果B1单元格中的时间值为“15：35：12”，公式将返回“15：45”。4.3 EXCEL中函数与公式4.3.1 公式的概述公式就是对工作表中的数值进行计算的式子，由操作符和运算符两个基本部分组成。操作符可以是常量、名称。数组、单元格引用和函数等。运算符用于连接公式中的操作符，是工作表处理数据的指令。1公式元素2运算符在公式中，每个运算符都有一个优先级。对于不同优先级的运算，按照从高到低的优先级顺序进行计算。对于同一优先级的运算，按照从左到右的书讯进行计算。4.3.2 单元格的引用相对引用绝对引用混合引用三维引用循环引用4.3.3 创建名称及其使用在Excel中，可以通过一个名称来代表工作表、单元格、常量、图表或公式等。如果在Excel中定义一个名称，就可以在公式中直接使用它。完成名称的定义之后，就可以再工作表中使用了4.3.4 SUM函数的应用SUM函数是返回指定参数所对应的数值之和。其完整的结构为：SUM(number1,number2,.)SUM(number1,number2,.)其中，number1，number2等这些是指定所要进行求和的参数，参数类型可以是数字、逻辑值和数字的文字表示等形式。在Excel中的函数库中，还有一种类似求和函数的条件求和函数SUMIF函数。该函数是用于计算符合指定条件的单元格区域内的数值进行求和。其完整的格式为：SUMIF(range,criteria,sum_range)SUMIF(range,criteria,sum_range)其中，range表示的是条件判断的单元格区域；criteria表示的是指定条件表达式；而sum_range表示的是需要计算的数值所在的单元格区域。如果省略 sum_range 参数，Excel 会对在范围参数中指定的单元格（即应用条件的单元格）求和。4.3.5 AVERAGE函数的应用AVERAGE函数是返回指定参数所对应数值的算术平均数。其完整的格式为：AVERAGE(number1,number2,.)AVERAGE(number1,number2,.)其中，number1，number2等这些是指定所要进行求平均值的参数。该函数只对参数的数值求平均数，如区域引用中包含了非数值的数据，则AVERAGE不把它包含在内。例如：A1：A4中分别存放着数据14，如果输入在A5中输入“AVERAGE（A1：A4，10）”，则A5中的值为4，即为（123410）/5。但如果在上例中的A2和A3单元格分别输入了文本，比如“语文”和“英语”，则A5的值就变成了5，即为（1410）/3，A2和A3虽然包含在区域引用内，但并没有参与平均值计算。4.3.6 IF函数的应用IF函数是一个条件函数，其完整的的格式为：IF(logical_test,value_if_true,value_if_false)IF(logical_test,value_if_true,value_if_false)其中，第一个参数logical_test是当值函数的逻辑条件，第二个参数value_if_true是当值为“真”时的返回值，第三个参数value_if_false是当值为“假”时的返回值。IF函数的功能为对满足条件的数据进行处理，条件满足则输出Value_if_true，不满足则输出Value_if_false。注意，在IF函数中三个参数中可以省略Value_if_true或Value_if_false，但不能同时省略。另外，在IF函数的使用中，还可用在IF函数中使用嵌套函数，最多可用嵌套7层。IF函数也能进行嵌套函数的使用，在实例中的许多表格中都用到了这方面的知识。例如在销售清单中，当用户选择了一个用户ID之后，为什么后面的一些单元格（如姓名、发货地址、固定电话、手机、邮编）能自动的填充上相关的信息？其实这里这些单元格都应用了IF函数，同时在IF函数中嵌套了一个查询函数VLOOKUP函数。例如，在销售清单中的E2单元格选择“user1”，则F2的单元格输入“=IF($E2=,VLOOKUP($E2,客户资料,2,0)”。该输入的意思就是根据E2的输入，如果E2非空，则查询客户资料表中的第二列，将其填入到F2单元格中，如图 448所示。4.4 EXCEL中数组的使用4.4.1 数组的概述数组就是单元的集合或是一组处理的值的集合。可以写一个数组公式，即输入一个单个的公式，它执行多个输入操作并产生多个结果每个结果显示在一个单元格区域中。数组公式可以看成有多重数值的公式。与单值公式的不同之处在于它可以产生一个以上的结果。一个数组公式可以占用一个或多个单元区域。4.4.2 使用数组常数数组常量可以分为一维数组与二维数组。一维数组又包括垂直和水平数组。在一维水平数组中元素用逗号分开，如10,20,30,40,50；在一维垂直数组中，元素用分号分开，如100;200;300;400;500。而对于二维数组中，常用逗号将一行内的元素分开，用分号将各行分开。4.4.3 编辑数组一个数组包含数个单元格，这些单元格形成一个整体，所以，数组中的单元格不能单独的进行编辑、清除和移动单，也不能插入或删除单元格，在对数组进行操作（编辑、清楚、移动单元格，插入、删除单元格）之前，必须先选取整个数组，然后进行相应的操作。4.4.4 数组公式的应用4.5 EXCEL的函数介绍Excel 2010共有13类，400余个函数，涵盖了财务、日期、工程、信息、逻辑、数学、统计、文本等各种不同领域的数据处理任务。这其中有一类特别的函数被称为“兼容性函数”，这些函数实际上已经由新函数替换，但为了实现向后兼容，依然在Excel 2010中提供这些函数，但在以后的版本中可能不再提供。4.5.1 财务函数1使用PMT函数计算贷款按年、按月的偿还金额PMT函数是基于固定利率及等额分期付款方式，返回贷款的每期付款额。其完整的格式为PMT(rate,nper,pv,fv,type)PMT(rate,nper,pv,fv,type)其中，参数Rate表示贷款利率；Nper表示该项贷款的付款总期数；Pv表示现值，或一系列未来付款的当前值的累积和，也称为本金；Fv表示未来值，或在最后一次付款后希望得到的现金余额，如果省略 fv，则假设其值为 0（零），也就是一笔贷款的未来值为 0；Type 为数字 0（零）或 1，用以指示各期的付款时间是在期初还是期末。2使用IPMT函数计算贷款指定期数应付的利息额IPMT函数是基于固定利率及等额分期付款方式，返回指定期数内对贷款的利息偿还额。其完整的格式为：IPMT(rate,per,nper,pv,fv,type)IPMT(rate,per,nper,pv,fv,type)其中，参数Per表示用于计算其利息数额的期数，必须在 1 到 nper 之间，其它参数同PMT函数。3使用FV函数计算投资未来收益值FV函数是基于固定利率及等额分期付款方式，返回某项投资的未来值。其完整的格式为：FV(rate,nper,pmt,pv,type)FV(rate,nper,pmt,pv,type)其中，参数Pmt表示各期所应支付的金额，其数值在整个年金期间保持不变，其它参数表示与前面相同。4使用PV函数计算某项投资所需要的金额PV函数计算的是一系列未来付款的当前值的累积和，返回的是投资现值。其完整的格式为：PV(rate,nper,pmt,fv,type)PV(rate,nper,pmt,fv,type)参数表示与前面相同。5.使用RATE函数计算年金利率RATE函数计算年金的各期利率。其完整的格式为：RATE(nper,pmt,pv,fv,type,guess)RATE(nper,pmt,pv,fv,type,guess)其中，参数Guess表示预期利率，如果省略预期利率，则假设该值为 10%，其它参数与前面相同。函数 RATE 通过迭代法计算得出，并且可能无解或有多个解。如果在进行 20 次迭代计算后，函数 RATE 的相邻两次结果没有收敛于 0.0000001，函数 RATE 将返回错误值#NUM!。如果函数 RATE 不收敛，请改变 guess 的值。通常当 guess 位于 0 到 1 之间时，函数 RATE 是收敛的。6使用SLN函数计算设备每日、每月、每年的折旧值SLN函数计算的是某项资产在一个期间中的线性折旧值。其完整的格式为：SLN(cost,salvage,life)SLN(cost,salvage,life)其中，cost表示的是资产原值；salvage表示的是资产在折旧期末的价值，即资产残值；life表示的是折旧期限，即资产的使用寿命。4.5.2 文本函数1EXACT函数2CONCATENATE函数3SUBSTITUTE函数4REPLACE函数5SEARCH函数6.TEXT函数7.MID函数4.5.3 日期与时间函数1DATE函数2DAY函数3TODAY函数4TIME函数5.WORKDAY函数6.WEEKNUM函数4.5.4 查找与引用函数1VLOOKUP函数VLOOKUP函数可以从一个数组或表格的最左列中查找含有特定值的字段，再返回同一列中某一指定单元格中的值。其完整的格式为：VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)其中，参数look_value是要在数组中搜索的数据，它可以是数值、引用地址或文字字符串。参数table_array是要搜索的数据表格、数组或数据库。参数col_index_num则是一个数字，代表要返回的值位于table_array中的第几列。参数rang_lookup是个逻辑值，如果其值为“TRUE”或被省略，则返回精确匹配值或近似匹配值。如果找不到精确匹配值，则返回小于 lookup_value 的最大值。；如果该值为“FALSE”时，VLOOKUP函数将只查找精确匹配值。如果 table_array 的第一列中有两个或更多值与 lookup_value 匹配，则使用第一个找到的值。如果找不到精确匹配值，则返回错误值#N/A。另外，如果range_lookup为“TRUE”，则table_array第一列的值必须以递增次序排列，这样才能找到正确的值。如果rang_lookup是“FALSE”，则table_array不需要先排序。一般情况下，我们都是利用VLOOKUP函数来实现对单个条件的查询，但也可以结合IF函数实现对多个条件的查询。例如，在G2单元格中输入数组公式“=VLOOKUP(E2&F2,IF(1,0,A1:A7&B1:B7,C1:C7),2,0)”，即可获得王五的铅球成绩了。在特定情况下，修改这个公式还可以实现从右边的列查询左边列的对应值。2HLOOKUP函数3CHOOSE函数CHOOSE函数的具体语法结构为：CHOOSE(index_num,value1,value2,.)其中，参数Index_num指定所选定的值参数。Index_num 必须为 1 到 254 之间的数字，或者为公式或对包含 1 到 254 之间某个数字的单元格的引用。Value1,value2,是值参数列表，个数介于 1 到 254 之间，函数 CHOOSE 基于 index_num 从这些值参数中选择一个数值或一项要执行的操作。参数可以为数字、单元格引用、已定义名称、公式、函数或文本。例如，输入“=CHOOSE(WEEKDAY(DATEVALUE(2012-3-28),星期天,星期一,星期二,星期三,星期四,星期五,星期六)”，则返回“星期三”。4.5.5 数据库函数1数据库函数的参数含义典型的数据库函数表达的完整格式为：函数名称函数名称(database,field,criteria)(database,field,criteria)其中，参数database为构成数据清单或数据库的单元格区域。数据库是包含一组相关数据的数据清单，其中包含相关信息的行为记录，而包含数据的列为字段。数据清单的第一行包含着每一列的标志项。参数field为指定函数所使用的数据列。数据清单中的数据列必须在第一行具有标志项。field可以是文本，即两端带引号的标志项，也可以是代表数据清单中数据列位置的数字：1表示第一列，2表示第二列，依此类推。参数criteria为一组包含给定条件的单元格区域。任意区域都可以指定给参数criteria，但是该区域中至少包含一个列标志和列标志下方用于设定条件的单元格。2DCOUNT函数3DGET函数4DAVERAGE函数5DMAX函数6DPRODUCT函数7DSUM函数4.5.6 统计函数1.AVERAGEIF函数AVERAGEIF函数返回某个区域内满足给定条件的所有单元格的平均值（算术平均值）。2计数函数COUNTCOUNT函数是用于返回数值参数的个数，即统计数组或单元格区域中含有数值类型的单元格个数。类似于COUNT函数这样的还有：COUNTA函数返回参数组中非空值的数目；COUNTBLANK函数计算某个单元格区域中空白单元格的数目；COUNTIF函数计算区域内符合给定条件的单元格的数量；COUNTIFS函数计算区域内符合多个条件的单元格的数量。我们可以在输入数据时，不允许输入重复值，例如先选择A列，然后在数据有效性对话框中“允许”项下选择“自定义”，在“公式”项中输入“=COUNTIF(A:A,A1)=1”，则在A列中不能输入重复值。3排位统计函数RANK.EQRANK.EQ函数的功能是返回一个数值在一组数值中的排位，如果多个值具有相同的排位，则返回该组数值的最高排位。另外还有RANK.AVG函数，也是返回一个数字在数字列表中的排位，数字的排位是其大小与列表中其他值的比值；如果多个值具有相同的排位，则将返回平均排位。在前面的例子中，RANK.AVG(A4,A1:A5,1)等于3.5。Excel以前版本中的RANK函数现在被归类在兼容性函数中，其功能同RANK.EQ函数。4.5.7 其他类型的函数1IS类函数函数名称函数名称说 明明ISBLANK ISBLANK 如果如果值为空，空，则返回返回 TRUE TRUEISERR ISERR 如果如果值为除除#N/A#N/A 以外的任何以外的任何错误值，则返回返回 TRUE TRUEISERROR ISERROR 如果如果值为任何任何错误值，则返回返回 TRUE TRUEISEVEN ISEVEN 如果数字如果数字为偶数，偶数，则返回返回 TRUE TRUEISLOGICAL ISLOGICAL 如果如果值为逻辑值，则返回返回 TRUE TRUEISNA ISNA 如果如果值为错误值#N/A#N/A，则返回返回 TRUE TRUEISNONTEXT ISNONTEXT 如果如果值不是文本，不是文本，则返回返回 TRUE TRUEISNUMBER ISNUMBER 如果如果值为数字，数字，则返回返回 TRUE TRUEISODD ISODD 如果数字如果数字为奇数，奇数，则返回返回 TRUE TRUEISREF ISREF 如果如果值为引用引用值，则返回返回 TRUE TRUEISTEXT ISTEXT 如果如果值为文本，文本，则返回返回 TRUE TRUE表 45 IS函数2TYPE函数3.数学函数ABS函数返回数字的绝对值；MOD 函数返回除法的余数；PRODUCT 函数返回其参数的乘积；QUOTIENT函数返回除法的整数部分；RAND函数返回 0 和 1 之间的一个随机数；RANDBETWEEN 函数返回位于两个指定数之间的一个随机整数。4.逻辑函数IF函数AND函数OR函数NOT函数判断今年是否是闰年，其公式为“=OR(MOD(YEAR(TODAY(),400)=0,AND(MOD(YEAR(TODAY(),4)=0,MOD(YEAR(TODAY(),100)0)”。