Excel函数与公式.pptx

第二篇 EXCEL高级应用 Excel2010是美国微软（Microsoft）公司推出的一种电子表格处理 软件，是Microsoft Office 2010套装办公软件的一个重要组件。它 具有以下几个方面的功能： 数据分析处理Excel2010具有超强的数据分析能力，能够创建 预算、分析调查结果以及进行财务数据分析。 创建图表使用图标工具能够根据表格的具体数据创建多种类型 的图表，这些既美观又实用的图表，可以让用户清楚地看到数字所 代表的意义。 绘制图形和结构图使用绘图工具和自选图形能够创建各种图形 及结构图，达到美化工作表和直观显示逻辑关系的目的。 使用外部数据库Excel2010能够通过访问不同类型的外部数据 库，来增强该软件处理数据这一方面的功能。 自动化处理Excel2010能够通过使用宏功能来进行自动化处理 ，实现单击鼠标就可以执行一个复杂任务的功能。 第4章 函数与公式 4.1 EXCEL实例介绍 4.1.1EXCEL表的建立 根据小张店铺的情况，经过相关的分析，整个 Excel工作簿中包含有进货清单、销售清单、 库存清单、销售统计、分类统计报表、分类统 计图表、商品资料、客户资料、其他资料设置 等工作表。 4.1.2 EXCEL中数据的管理与分析 数据输入 函数与公式 筛选与排序 分类汇总 数据透视表（图） 4.2 EXCEL中数据的输入 4.2.1 自定义下拉列表输入 4.2.2 自定义序列与填充柄 自定义填充序列是一组数据，可按重复方式填充列。通过工 作表中现有的数据项或以临时输入的方式，可以创建自定义 填充序列。 4.2.3 条件格式 在Excel中提供了一个功能非常独特的数据管理功能条件 格式。通过设置数据条件格式，可以让单元格中的数据满足 指定条件时就以特殊的标记（如：以红色、数据条、图标等 ）显示出来。该功能可以让单元格根据不同的应用环境所设 置的条件发生变化。 我们还可以在单元格中设置彩色的数据条，以 数据条的长度来表示数值的大小 4.2.4 数据输入技巧 1特殊数据输入 输入分数 在输入的分数前加上一个“0”和一个空格 输入负数 除了直接输入负号和数字外，也可以使用括号来完成 输入文本类型的数字 在输入的数据前面加上单引号 输入特殊字符 2快速输入大写中文数字 3自动超链接的处理 4.2.5 数据的舍入方法 函数名称 说说明 CEILING 函数将数字舍入为最接近的整数或最接近的指定基数的倍数 CEILING.PRECISE 函数将数字舍入为最接近的整数或最接近的指定基数的倍数。无论该 数字的符号如 何，该数字都向上舍入。 EVEN 函数将数字向上舍入到最接近的偶数 FLOOR 函数向绝对值 减小的方向舍入数字 FLOOR.PRECISE 函数将数字向下舍入为最接近的整数或最接近的指定基数的倍数。无论该 数字的符 号如何，该数字都向下舍入。 INT 函数将数字向下舍入到最接近的整数 MROUND 函数返回一个舍入到所需倍数的数字 ODD 函数将数字向上舍入为最接近的奇数 ROUND 函数将数字按指定位数舍入 ROUNDDOWN 函数向绝对值 减小的方向舍入数字 ROUNDUP 函数向绝对值 增大的方向舍入数字 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,.) 其中，number1，number2等这些是指定所要 进行求和的参数，参数类型可以是数字、逻辑 值和数字的文字表示等形式。 在Excel中的函数库中，还有一种类似求和函数的 条件求和函数SUMIF函数。该函数是用于计 算符合指定条件的单元格区域内的数值进行求 和。其完整的格式为： SUMIF(range, criteria, sum_range) 其中，range表示的是条件判断的单元格区域； criteria表示的是指定条件表达式；而sum_range 表示的是需要计算的数值所在的单元格区域。如 果省略 sum_range 参数，Excel 会对在范围参数 中指定的单元格（即应用条件的单元格）求和。 4.3.5 AVERAGE函数的应用 AVERAGE函数是返回指定参数所对应数值的算术平均数。 其完整的格式为： 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) 其中，第一个参数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) 其中，参数Rate表示贷款利率；Nper表示该项贷款的 付款总期数；Pv表示现值，或一系列未来付款的当前 值的累积和，也称为本金；Fv表示未来值，或在最后 一次付款后希望得到的现金余额，如果省略 fv，则假 设其值为 0（零），也就是一笔贷款的未来值为 0； Type 为数字 0（零）或 1，用以指示各期的付款时间 是在期初还是期末。 2使用IPMT函数计算贷款指定期数应付的利 息额 IPMT函数是基于固定利率及等额分期付款方 式，返回指定期数内对贷款的利息偿还额。其 完整的格式为： IPMT(rate, per, nper, pv, fv, type) 其中，参数Per表示用于计算其利息数额的期 数，必须在 1 到 nper 之间，其它参数同PMT 函数。 3使用FV函数计算投资未来收益值 FV函数是基于固定利率及等额分期付款方式，返回某项投资的未来值。其 完整的格式为： FV(rate,nper,pmt,pv,type) 其中，参数Pmt表示各期所应支付的金额，其数值在整个年金期间保持不 变，其它参数表示与前面相同。 4使用PV函数计算某项投资所需要的金额 PV函数计算的是一系列未来付款的当前值的累积和，返回的是投资现值。 其完整的格式为： PV(rate, nper, pmt, fv, type) 参数表示与前面相同。 5. 使用RATE函数计算年金利率 RATE函数计算年金的各期利率。其完整的格式为 ： 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) 其中，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) 其中，参数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为指定函数所使用的数据列。数据清单中的数据列必须在 第一行具有标志项。field可以是文本，即两端带引号的标志项，也 可以是代表数据清单中数据列位置的数字：1表示第一列，2表示第 二列，依此类推。 参数criteria为一组包含给定条件的单元格区域。任意区域都可以指 定给参数criteria，但是该区域中至少包含一个列标志和列标志下方 用于设定条件的单元格。 2DCOUNT函数 3DGET函数 4DAVERAGE函数 5DMAX函数 6DPRODUCT函数 7DSUM函数 4.5.6 统计函数 1. AVERAGEIF函数 AVERAGEIF函数返回某个区域内满足给定条件 的所有单元格的平均值（算术平均值）。 2计数函数COUNT COUNT函数是用于返回数值参数的个数，即统计数组或单元格区域中含有 数值类型的单元格个数。 类似于COUNT函数这样的还有： COUNTA函数返回参数组中非空值的数目； COUNTBLANK函数计算某个单元格区域中空白 单元格的数目； COUNTIF函数计算区域内符合给定条件的单元 格的数量； COUNTIFS函数计算区域内符合多个条件的单 元格的数量。 我们可以在