EXCEL表单操作课件之函数篇.ppt

Excel 表单操作 函数篇 目录 基础概念 认识单元格引用 认识数组 常用引用函数 Offset函数 Index函数 函数综合应用 基础概念－引用 认识工作表区域 单元格引用方式 相对引用、绝对引用和混合引用 单元格引用的分类 一维引用：A1:A10、A2:G2 二维引用：A1:C5 三维引用：Sheet1:sheet3!A1 基础概念－数组 数组的概念 由文本、数值、日期、逻辑、错误值等元素组成的集合 常量数组和内存数组 数组的分类 一维数组：行数组（垂直数组）和列数组（水平数组） {1,2,3,4,5}，{“a”;“b”; “c”; “d”; “e”} 二维数组：多行多列数组 {0,”差“;60,”中”;80,”良“;90,”优”} 引用函数简介 Offset函数 将源引用根据指定的行、列偏移来产生新的单元格引用 Offset函数语法 OFFSET(reference基点,rows行数,cols列数,height,[高度]width[宽度]) 基点只能是单元格引用，而不能是数组且必须为对单元格或相连单元格区域的引用 高度、宽度用于制定目标的单元格区域大小，如果不指定，则目标引用尺寸与源相同 高度、宽度允许用户使用负数作为参数 Offset（A5,2,2,5,3） 问题 如何统计下面数据中最后5天的产品平均销量 引用函数简介 Index函数语法 INDEX(reference,row_num行号,column_num[列号],area_num[区域序号]) Reference?? 为引用时，结果返回为单元格引用 当Reference?使用联合区域时，还可以通过区域序号返回子区域 INDEX(array数组,row_num行号,column_num[列号]) Reference为数组时，结果返回为数组中的具体值 注意事项 通常情况下，行号、列号参数不支持使用数组 目录 基础概念 认识单元格引用 认识数组 常用查找函数 Lookup vlookup函数 Match 函数 函数综合应用 查找函数简介 Lookup 函数 Lookup（查找值，查找范围，[目标范围]） 通常情况下查找范围的首列或首行要求升序排列 VLOOKUP 函数 Vlookup （查找值，数据表，序列号，[匹配条件]） 匹配条件可用0或1代替false和ture；其中0表示精确查找，1表示近似查找；为0时，支持无序查找；为1时，首列升序排列进行查找 支持使用通配符进行查找 Lookup 和 Vlookup 的常规用法 员工信息查询 利用lookup进行查找 利用vlookup进行查找 多条件区间判断取值 两个疑问？ 在使用LOOKUP函数时，查找范围首列（首行）必须升序排列吗？ 使用VLOOKUP函数能不能实现逆向（如从右向左）查询？ 查找函数简介 定位函数－MATCH函数 MATCH（查找值，查找范围，[匹配条件]） [匹配条件]使用数字-1、0或1来确定查找模式 结果返回为查找值在范围中的位置序号 用户提醒 通常情况下，MATCH函数和INDEX组合应用实现类似VLOOKUP函数的应用，使用更加灵活 MATCH函数的疑惑？ 问题1：在D、E两列区域中能否通过MATCH函数能够实现从右向左查询吗？ 查找引用函数综合应用 利用公式组合运用实现两级下拉菜单 总结 引用和数组的概念 常用引用函数 OFFSET INDEX 函数语法 函数应用：数据排版、生成工资条、动态引用、动态下拉菜单等 常用查找函数 VLOOKUP、LOOKUP、MATCH函数语法 函数应用：多区间查找、数据定位、提取唯一数据、动态超级链接等 统计函数 基础概念 认识单元格引用 常用统计函数 Countif 函数 Sumif 函数 经典案例 统计函数综合应用 标记指定数据、动态数据汇总 常用统计函数 条件统计函数－countif 函数 主要用于单个条件的统计 函数语法 countif（条件范围，统计条件） 函数特征 条件范围只能是单元格引用 统计条件可以使用引用、字符串表达式或数组、同时支持通配符（“？”和“*”） Countif 函数的疑惑 问题 Countif 函数能够进行两个条件的统计吗？ 例：如何统计A列数据中大于等于5而且小于10的数值个数？ 条件求和函数 SUMIF 函数 主要用于单个条件的求和 函数语法 SUMIF（条件范围，统计条件，[求和范围]）如果”求和范围”不写就”条件范围”求和。 函数特征 条件范围和求和范围只能为单元格引用 统计条件可以使用引用或数组 求和范围可以简写为区域的左上角单元格引用 SUMIF 函数的疑问 问题：如何对下表中所有员工工号为B开头的产品销售量进行汇总？ 统计函数综合应用 使用统计函数汇总产品销售情况表 小结 单元格引用的三种方式