Excel函数应用实例.ppt

Microsoft Excel 2003 函数应用实例,培训讲师：,Excel能做什么？,最简单的一种回答是：Excel是用来做表格的。 更详细一些的答案版本可能是：Excel是一个电子表格软件，有强大的数据处理与分析功能，能够把数据做成各种商业图表，还能编程。 当你对Excel的了解越多，就会发觉Excel总能在适当的时候给你带来惊喜，做到一些原本你认为不可能的事情。此时你的答案或许应该是： Excel不能做什么？,提纲,函数应用基础单元格引用 查找函数应用数据合并 数据有效性应用下拉式输入 统计函数应用条件汇总 条件格式应用格式提醒 视频学习 数据透视表 邮件合并 宏基础 常用函数推荐,关于函数,函数是一些预定义的公式，通过使用一些称为参数的特定数值来按特定的顺序或结构执行计算。函数可用于执行简单或复杂的计算。例如，ROUND 函数可将单元格 A10 中的数字四舍五入。 函数的结构以等号 (=) 开始，后面紧跟函数名称和左括号，然后以逗号分隔输入该函数的参数，最后是右括号。,单元格引用,案例：如何实现快速制作九九乘法表？,关于单元格和区域引用,引用的作用在于标识工作表上的单元格或单元格区域，它们告诉 Excel 到哪里查找在公式中使用的值。 通过引用，可以在公式中使用工作表不同部分的数据，或者在多个公式中使用同一个单元格的数值。还可以引用同一个工作簿中不同工作表上的单元格和其他工作簿中的数据。,单元格引用,单元格引用，可以是单一单元格引用，也可是多个单元格引用，引用在数据处理中有三种方式。 绝对引用：例如 $F$6、 $F$6: $K$8，总是在指定位置引用单元格。如果公式所在单元格的位置改变，绝对引用的单元格始终保持不变。 相对引用：例如 A1、A1:B6，是基于包含公式和单元格引用的单元格的相对位置。如果公式所在单元格的位置改变，引用也随之改变。 混合引用：绝对列和相对行，或是绝对行和相对列，例如$A1、 F$6: K$8，如果公式所在单元的位置改变，引用的行或列随之改变。,文本连接符,如果希望将多个文本连接生成新的文本，可以使用“&”来实现。 如： A1单元格值为 ：“景鸿物流” B2单元格值为 ：“北京公司” 在C3单元格输入：=A1&”&B2，则C3单元格显示： “景鸿物流北京公司”,if函数,if（条件算式, 条件成立返回值,条件不成立返回值），用于执行真假值判断后，根据逻辑测试的真假值返回不同的结果，因此If函数也称之为条件函数。 例：=IF(B1160,“合格“,“不合格“) if函数在实际应用中，可以嵌套使用，但最多不能超过七重。,单元练习,利用单元格引用，快速制作九九乘法表。,Just do it,数据合并,案例：如何为出货清单添加海关税号？,Match函数,Match (查找值，查找范围，查找方式) ，查找元素的相应位置。 查找值：可以为数值（数字、文本或逻辑值）或对数字、文本或逻辑值的单元格引用。 查找方式：-1、0 或 1； 1，找小于或等于查找值的最大数值；0，查找等于查找值的第一个数值； -1，查找大于或等于查找值的最小数值。如果省略则默认为 1。,函数返回查找值在查找范围中的位置，而不是数值本身。例如，match(“b”,“a”,“b”,“c”,0) 返回 2，即“b”在数组 “a”,“b”,“c” 中的相应位置。 查找文本值时，不区分大小写字母。 如果查找不成功，则返回错误值 #N/A。 如果 查找方式为 0 且查找值为文本，则查找值可以包含通配符 (*) 和 (?)。,Index函数,Index(数据区域, 行号, 列号) 数据区域：单元格区域或数组常量。 如果数据区域只包含一行或一列，则相对应的参数 行号 或 列号 为可选参数。 例：=index(“a”,“b”,“c”,2)，返回b,单元练习,通过零件编号的关联，为出货清单上的货品添加海关税号。,Just do it,规范录入,案例:如何实现用二级下拉列表输入部门名称？,名称,在工作表中可以创建描述名称来代表单元格、单元格区域、公式或常量，名称更易于理解。 例如：用“产品”来代表区域“Sales!C20:C30”。 名称命名规则 名称的第一个字符必须是字母或下划线，名称中的字符可以是字母、数字、句号和下划线， 名称最多可以包含 255 个字符。,数据有效性,Excel可以通过指定有效的单元格输入项，限制输入单元格数据的规则和范围。 指定所需的数据有效性类型： 有序列的数值 数字有范围限制 日期或时间有范围限制 文本为指定长度 计算基于其他单元格内容的有效性数据 使用公式计算有效性数据 在“数据”菜单上，单击“有效性”命令，再单击“设置”选项卡。,Indirect函数,Indirect(文本字符)，返回由文本字符串指定的单元格引用并立即对引用进行计算，并显示其内容。 当需要更改公式中单元格的引用，而不更改公式本身，请使用函数 。 如果 文本字符 是对另一个工作簿的引用（外部引用），则那个工作簿必须被打开。如果源工作簿没有打开，函数 INDIRECT 返回错误值 #REF!。,Offset函数,Offset(参照引用，偏移行数，偏移列数，高度，宽度) ，以指定的引用为参照系，通过给定偏移量得到新的引用。返回的引用可以为一个单元格或单元格区域，并可以指定返回的行数或列数。 偏移行数（列数）：相对于参照引用的左上角单元格，上（下）偏移的行数（列数）。如果使用 5 作为偏移行数（列数），则说明目标引用区域的左上角单元格比参考引用低 5 行。行数（列数）可为正数或负数。 高度（宽度）：即所要返回的引用区域的行数（列数）。高度及宽度必须为正数。 Offset可用于任何需要将引用作为参数的函数。例如：SUM(OFFSET(C2,1,2,3,1) 将计算比单元格 C2 靠下 1 行并靠右 2 列的 3 行 1 列的区域的总值。,单元练习,以下拉列表的方式实现部门名称的规范输入，二级部门必须与一级部门相对应。,Just do it,条件汇总,案例：如何实现应收账款核对？,Sumif函数,Sumif（条件范围，条件值，求和范围），根据指定条件对区域中单元格求和。 条件范围：用于条件判断的单元格区域。 条件值：确定哪些单元格将被相加求和的条件，其形式可以为数字、表达式或文本。例如，条件可以表示为 A1、 “32” 或 “apples”。 求和范围：需要求和的实际单元格区域，可以用左上角单元格替代。,Countif函数,Countif（条件范围，条件值），计算区域中满足给定条件的单元格的个数。 条件范围：需要计算其中满足条件的单元格数目的单元格区域。 统计条件：确定哪些单元格将被计算在内的条件，其形式可以为数字、表达式、单元格引用或文本。例如，条件可以表示为 A1、“32“ 、“apples“ 。,单元练习,表二中为用友直接导出的数据，表一中为按照业务编号手工每笔每笔录入的数据，让表一中数据按照凭证编号和表二中数据一一对上，如果对不上要去查找原因。 表15表30为各作业点按目的港统计操作吨位日报，要求汇总成半月报，即时查询各作业点同一目的港吨位明细及汇总。,Just do it,格式提醒,案例：如何实现标识不同类别的数据？,条件格式,条件格式：当指定条件为真时，Excel 自动应用于单元格的格式，快速引起对差异的注意。例如，单元格底纹或字体颜色。 同一单元格，条件格式最多可以设置三重条件； 条件格式，可以通过公式计算来设置； 用定位功能，可以查找有条件格式的单元格。,单元练习,区分销售人员销售业绩。 月销售额低于1000元的，以红底白字以示强调 月销售额高于10000元的，以绿底白字以示鼓励。 针对一些限期完成的工作，如何实现提醒功能？,Just do it,视频学习,数据透视表 邮件合并 宏基础,常用函数推荐数学函数,Round（数值，保留小数位），将数字四舍五入到指定小数位数。 Int（数值），删除数值小数，保留整数部分。 Mod（被除数，除数），返回两数相除的余数。 Subtotal（汇总代号，汇总范围），返回列表或数据库中的统计汇总结果。 汇总代号： 为 1 到 11（包含隐藏值）或 101 到 111（忽略隐藏值）之间的数字，指定使用何种函数在列表中进行分类汇总计算。,常用函数推荐文本函数,Left（文本，提取字符位数）：从字符串的最左端位置提取指定数量的字符。 Mid（文本，提取起始位，提取字符位数）：从字符串任意指定位置提取指定数量的字符。 Right（文本，提取字符位数）：从字符串的最右端位置提取指定数量的字符。 Len（文本）：返回字符串中字符的个数，即计算字符串的长度。 Find（查找字符串，源字符串）：查找字符或字符串在指定字符串中首次出现的位置，如不存在，则返错误提示。,常用函数推荐日期函数,Now（）：用于获取系统当前时间。 Year（日期）、Month（日期）、DAY（日期）:用于获取指定日期的年份、月份和日期数值。 Date（年份，月份，日期）：根据指定年份、月份、日期数返回日期值。 DateDif（起始日期，终止日期，返回类别），计算两个日期之间的天数、月数或年数。 返回类别为“y”计算整年数；“m”