大学计算机excel函数课件,《Excel函数教程》PPT课件.ppt

《《Excel函数教程》PPT课件.ppt》由会员分享，可在线阅读，更多相关《《Excel函数教程》PPT课件.ppt(46页珍藏版)》请在装配图网上搜索。

1、欧派财务部 Excel函数教程 作成：杨春梅 欧派财务部 一，数组介绍 二， SUMIF函数 三， VLOOKUP函数 四， COUNTIF函数 五， MATCH函数 六， SUMPRODUCT函数 七， LOOKUP函数 八，日期与时间函数 九，数学函数 十，统计函数 十一，查找与引用函数 十二，文本函数 十三，逻辑、信息函数 01 05 09 14 18 21 27 31 32 35 37 39 43 生产管理科 复杂函数的运算原理 数组运算 运算类型： 1)四则运算：、 、 2)逻辑运算：、 、 、 生产管理科 以数组的角度理解函数应用 理解最基本原理，学会举一反三 授人于鱼，不如授人于。

2、渔 数组的运算原理 1， 1， 0， 0， 1， 0， 0， 0， 1， 0， 1 0， 1， 1， 0， 1， 1， 0， 0， 1， 1， 1 = ? 0 , 1 , 0 , 0 , 1 , 0 , 0 , 0 , 1 , 0 , 1 1， 1， 0， 0， 1， 0， 0， 0， 1， 0， 1 0， 1， 1， 0， 1， 1， 0， 0， 1， 1， 1 0， 1， 0， 0， 1， 0， 0， 0， 1， 0， 1 | | | | | | | | | | | 1)相运算的数组里数字的个数必须相等，如例子，数组 1与数组 2均为 11个。 2)运算时，数组中的数字，按顺序分别进行计算。

3、，不会交叉或错乱。 3)数组进行求和， SUM(0,1,0,0,1,0,0,0,1,0,1)=0+1+0+0+1+0+0+0+1+0+1=4 相当于： SUM(0,1,0,0,1,0,0,0,1,0,1) 运算方法如下 SumIF 单条件求和函数 H7:H19=E,E,D,E,E,A,C,B,C,A,D,A,B M7=A，在函数中，自动转换成数组形 式，即 A,A,A,A,A,A,A,A,A,A,A,A,A E,E,D,E,E,A,C,B,C,A,D,A,B A,A,A,A,A,A,A,A,A,A,A,A,A | | | | | | | | | | | | | 0,0,0,0,0,1,0,0,。

4、0,1,0,1,0 1)按次序逐对进行比较。 2)相符( TRUE)为 1，不相符( FALSE) 为 0。 第步得出的 结果，再进行 下一步运算 需要求 A组 的段取时 间累计， 怎么做呢？ SumIF 单条件求和函数 0,0,0,0,0,1,0,0,0,1,0,1,0 第步比较运算得出的结果： 再与 K7： K19(如下)数据按顺序相乘 0.50,0.83,1.170.50,0.50,0.33,0.67 结果 0,0,0,0,0,0.50,0,0,0,0.50,0,0.33,0 最后求和得： 1.33 1 1=1 0 1=0 SumIF 单条件求和函数 SumIf( Range， Crit。

5、eria， Sum_Range) Range：判断区域，一维数组 Criteria：条件，可以是固定值 Sum_Range：求和区域，一维数组 运算步骤及注意点： 1) Range与 Criteria进行逻辑运算，得出结果 2)结果 与 Sum_Range进行相乘运算 3) Range与 Sum_Range的范围大小必须一致 SumIF 单条件求和函数 SumIf( Range， Criteria， Sum_Range) 例子： 1) SUMIF( A1:A100， D2， B1:B100) 2) SUMIF( A1:A100，” A” ， B1:B100) 3) SUMIF( A1:A100。

6、，” 3” ， B1:B100) 解释： 1)汇总等于 D2单元格的所有值 2)汇总等于” A” 的所有值 3)汇总大于 3的所有值 Vlookup 单条件引用函数 A1,A2,A3,A4,A5,A6,A7 B1,B2,B3,B4,B5,B6,B7 二维数组 D2， D2， D2， D2， D2， D2， D2 A1， A2， A3， A4， A5， A6， A7 首先 D2与首列进行逐个比较运算 逐一按 顺序进 行比较 | 0， 0， 0， 0， 1， 0 结果 Vlookup 单条件引用函数 A1,A2,A3,A4,A5,A6,A7 B1,B2,B3,B4,B5,B6,B7 二维数组 第 。

7、步结果 0， 0， 0， 0， 1， 0 与第 2列比较 B1,B2,B3,B4,B5,B6,B7 返回 1对 应的数据 B6=Z-0 Vlookup 单条件引用函数 Vlookup( lookup_value,table_array,col_index_num,range_lookup) Lookup_value： 条件值，是值，非区域，比如 A1，而 非 A1:A100，即使 A1:A100，也只选择左上角值 A1 Table_array： 搜索的区域 /范围，是一个多维数组，如 A1:D100之类 Col_index_num： 需要返回的值所在列，从搜索范围 的起始列开始算 Range_。

8、lookup： 匹配方式，一般使用精确匹配，即 0 或 FALSE I:10-15成品库报表 .xls Vlookup 单条件引用函数 Vlookup( lookup_value,table_array,col_index_num,range_lookup) 例子： 1) Vlookup( D2， A1:C100， 2， 0) 2) Vlookup(” 848K 15293” ， A1:C100， 2， 0) 3) Vlookup( D2COUNTBLAN用 于计算区域内空值单元格的个数 2)空值单元格不等于含空格或零值的单元格，它是指单元格内 字符长度为零的一个空值，函数中常用两个双引号表示。

9、，如”， 另外可用 LEN函数检测其与空格单元格的差别 Max(区域 )、 Min(区域 ) 求区域内的最大值( MAX)和最小值( MIN)，文本型的数值将 不参与运算 统计函数 Large(区域，排位) -第几大 1)此函数用于计算所属区域中，从大到小排行第几位的数值， 比如， Large( A1:A100,5)意思是，在 A1:A100范围内，求得第 5大的数值，利用其它函数(如 ROW和 COLUMN等函数或单元格 值)，将”排位”替换成变量，可以快速地从一组数据中提取从 大到小的排列顺序 2)不允许存在并列第几的状态，如果有相同的数值，将顺位排 序，如 100,90,90,85,82。

10、 取第二大数值时是 90，第三大是 90， 第四大是 85. Small(区域，排位) -第几小 原理与 LARGE相同，参照如上。 其它查找与引用函数 ROW、 COLUMN 1)直接 =ROW()用于取得当前单元格的行数，进行拖拉时，可以 取得与 EXCEL行数相对应的递增或递减的数值。 2)添加参数，如 =ROW(单元格 )，如 =ROW(A1)可返回该单元格 的行号，如 =ROW(B2)返回 2 第 2行，作用同上。 3)将一些函数中固定系数、逻辑值按需要替换成变量，可利用 这两个函数，以方便拖拉，而不须逐个更改函数中的固定系数值， 提高效率。 参数值可变量代替，可以实现一次性拖拉得出。

11、，节省时间 其它查找与引用函数 INDIRECT(用字符表示的引用区域 ) 1)在函数中引用位置可以变成变量形式， INDIRECT(” A1” ) 即等于单元格 A1的值 注意两者形式比较 B2相当 于” Sheet2” 在统计年、月度报表(日报表分散成一个一个工作表时)时 用处较大，可巧妙地运用此函数，快速地汇总所需数据。 2)此函数属易失函数，即在引用另一个工作薄数据时，必须 在两个 EXCEL同时打开的情况下，才有效。 3)我们在工作表中直接引用，即 =A1之类，在删除单元格时， 引用会出错( #REF)，如果改用 =INDIRECT(A1)则不管如何 删除，它都能保证引用返回 A1的。

12、值。 文本函数 FIND(单个字符，字符串，起始位置 ) 函数说明： =FIND(“ A” ,” BDFCAFDAD” ,2)在字符串中，从 第 2位开始，查找” A” 首次出现的位置，即返回 5，如不包含， 则返回错误值 #VALUE!，如果省略”起始位置”，则默认为从第 1位开始查找。 LEN(单元格或字符 ) 函数说明： =LEN(A1)，计算 A1单元格中字符串的长度，不能用 区域，如 LEN(A1:A10)，也只返回最左上角 A1单元格的长度值 VALUE(单元格或字符 ) 函数说明： =VALUE(A1)，将 A1单元格中文本型的数字转换成数 值型，以便参与函数公式的运算。相当于 。

13、=-A1，即在 A1前面添 加两个减号，含“负负得正”的意思。另一种方法是用数值性粘 贴方法，将 A1单元格乘于 1，即转换成数值型。 文本函数 LOWER、 UPPER、 TRIM、 REPT 1) LOWER、 UPPER用于将小 /大写字母转换成大 /小写字母，如 下图 : 2) TRIM函数用于删除字符前后的空格，字符中间的空格无法删 除，此函数常用于整理手工输入时误输空格符的情况 3) REPT(字符，重复个数 )函数用于产生 N个重复的字符，当函 数中需要用到多个重复字符时，用这个函数减少函数体积 4) TEXT(字符，格式类型 )它相当于单元格格式设置中的自定义 设置，一般在嵌套。

14、在其它函数中使用，如下图： 使资料规 范统一 两种方式的效 果对比 文本函数 SUBSTITUTE 字符替换函数 1) Substitute(需要替换掉的字符，字符串，需要替换成的字符 ) 如 Substitute(“ a” ,” AFGERHAaFe” ,” 中” )意思是将字符串 中 A字符全部替换成”中”，返回结果是“中 FGERH中中 Fe” ， 故它是不区分大小写的。 2)经常用途，需要去掉部番中间的空格时( 848K 15293)如 果单纯用查找替换方式，容易将一些 059E开头的部番变成科学 记数法，而成为乱码，影响数据准确性，此时，可先用本函数 去除空格，再数值性粘贴到文本区域。

15、。 3)替换空格时，可以简写成这样： SUBSTITUTE(“ ”， A1，)即将 A1中的空格清除 空 格 字符 空 值 字符，可以不填，或 用”表示 文本函数 LEFT、 RIGHT、 MID 1) Left(字符串，位数 N) 截取字符串左边的 N个 字符，用法示例： Left(“ 中华人民共和 国” ,3)=“ 中华人” 2) Right(字符串，位数 N) 截取字符串右边的 N 个字符，用法示例： Right(“ 中华人民共和 国” ,3)=“ 共和国” 3) Mid(字符串，起始位置 Y，位数 N) 从字符串 的第 Y位开始，截取 N位字符，用法示例： MID(“ 中华人民共和国”。

16、， 2， 3)=“ 人民共” 逻辑、信息函数 IF函数 IF(条件，条件成立时返回结果，条件不成立时返回结果) 1) IF( A160，“合格”，“不合格”)即判断 A1单元格的值， 如果从于 60，则返回“合格”，反之，则返回“不合格” 2)嵌套使用，如 IF(A190,” 优秀” ,” 合 格” )意思为如果 A1小于 60则返回“不合格”，大于 60但小于 90，返回“合格”，大于 90则返回“优秀”，依此类推，共可 以嵌套七层，也就是说只可以区分七个等级，超过则会出错，同 时，如果函数判断层级达到 4-5个以上，函数式会变得很长，极 不利于检查核对，故此时需要考虑改用 LOOKUP函数。

17、来替代 3)使用此函数时，逻辑关系务必 100%严密，否则将返回不到正 确的结果 逻辑、信息函数 ANDOR函数 AND(条件 1，条件 2，条件 3 ) 1) AND表示多个条件并列，如 AND(A12,B160,A12,B12或 B13时 条件成立，返回 TRUE，用 OR时，所有条件只要有一个符合， 就返回 TRUE， IF(OR(A1=2,A1=3),” 良好” ,” 其它等级” ) 即 A1在等于 2或 3时都返回“良好” OR (条件 1，条件 2，条件 3 ) 逻辑、信息函数 Iserror、 Isna函数 用于判断错误值， ISNA只判断 #NA型的错误值， ISERROR可判 断所有错误值，一般结果其它函数使用，如下： 无法计 算合计 数 添加 ISERROR 函数后，避免无 法合计现象。