EXCEL办公实用函数使用技巧.ppt

EXCEL办公实用函数使用技巧 目录 EXCEL函数的基本知识逻辑函数的应用文本函数的应用日期与时间函数数学与统计函数查询与引用函数数据库函数的应用 Unit1EXCEL函数的基本知识 什么是函数函数的参数和返回值函数的地址符函数的套用 1 什么是函数 Excel中所提的函数其实是一些预定义的公式 它们使用一些称为参数的特定数值按特定的顺序或结构进行计算 简单点说 函数是一组功能模块 使用函数能帮助你实现某个功能 举例 Sum函数可以实现数值的加法运算 max函数可以返回一组数的最大值 2 函数的参数及返回值 1 参数可以是数字 文本 形如TRUE或FALSE的逻辑数值 数组 形如 N A的错误值或单元格引用 函数的参数使用逗号隔开如sum A1 2 B2 其中 A1 2 B2都是sum函数的参数 用逗号隔开另外要注意的是 函数使用的参数所用的符号 如逗号 引号要全部在英文输入状态下输入 半角符号 数字 1 2 100 10000文本 astron 412398784737847 以文本格式存储的数字 逻辑值 TRUE 真 或非零 一般用数字1表示 FALSE 假 或0比如13这个不等式的值为假 单元格 比如A47 B16数组 同一种格式存储的单元格区域 2 大部分函数都有一个返回值 返回你通过函数运算得出的结果 返回值可以是一个数字 一个逻辑值 一个地址 另外要注意的是 要想要函数返回正确的值 使用公式的单元格数值格式 快捷键ctrl 1 一定要正确 举例 sum 1 2 3 我们的目的是得出1 2 3相加的和 sum函数返回的值是6另外值得注意的是 对于函数的返回值如数值 它等同一个数值 而不要把它认为是一个函数 它可以如其它数值一样使用加 减 乘 除 如上例中 可以A1 10 sum 1 2 3 那么A1 4 在这里 因为sum 函数的返回值是一个数值 可以直接把它当数值用 2 函数的参数及返回值 3 函数的地址符 在我们使用函数时 会引用一些地址作为参数 比如A47 B46等EXCEL中有专门的地址引用符 使用是 引用地址时 是绝对引用 不使用 时 是相对引用 举例 A47 相对引用 引用A47这个单元格 当使用这种类型的地址时 我们使用拖拉功能将公式引用到其它单元格时 这个相对地址会发生变化 3 函数的地址符 A 47 绝对引用 无论将公式引用到哪一个单元格 这个地址不会改变 永远定格在A47这个单元格 A47和A 47 混合引用 只定格单元格的行或列 相对来说有点复杂 不常用 另外在引用地址时 冒号 在与单元格一起引用时 代表一片区域 如 A1 A47 代表的是从A1一直到A47的长条形区域 总共47个单元格 如 A1 B3 代表以A1和B3为对角的一个方形区域 共9个单元格 注 在引用地址符时 我们可以使用F4鍵在这些地址间进行切换 4 函数的套用 函数的套用 也就是函数的组合使用 单个函数已经很厉害了 组合起来的函数威力会更强大 举例 IF SUM 1 2 3 5 100 1000 在这个函数中 逻辑函数IF套用了SUM函数 它把SUM函数返回的结果作为参数 如果1 2 3之和小于5 返回100 反之 返回1000 Unit2逻辑函数的应用 IF函数AND函数OR函数NOT函数 1 IF函数 IF在英文里是 假如 如果 的意思 顾名思义就是如果满足某个条件 为真 则返回前面的一个值 不满足条件 为假 返回后面的一个值 语法 IF 逻辑值 返回值1 返回值2 其中逻辑值类型为真或假 另外返回值是文本格式时 要用双引号 如果为数字 可以不用双引号 举例 如何判断考试成绩是否及格 IF A184 优秀 及格 这是IF函数套用IF函数 如果A1小于60 返回不及格 另外如果大于等于85 则返回优秀 否则返回及格 2 AND函数 AND在英文里是 和 与 的意思 只有当所有条件都满足时 才返回ture 真 否则返回false 假 一般要与其它函数套用 比如IF函数 语法 AND 条件1 条件2 条件30 返回值 TURE或FALSE举例 1 and A1 60 B280 只有A1 60 B280这三个条件都满足时 才返回TRUE 否则FALSE 2 IF and A1 60 B280 1 2 三个条件都满足时 返回1 否则返回2 3 OR函数 OR在英文里是 或 或者 的意思 只要满足任一条件就返回ture 真 否则返回false 假 一般要与其它函数套用 比如IF函数 语法 OR 条件1 条件2 条件30 举例 or A1 60 B280 当A1 60 B280这三个条件有一个满足时 返回TRUE 否则FALSE 4 not函数 not在英文里是 不 非 的意思 返回相反的值一般要与其它函数套用 比如IF函数 语法 not 条件 举例 not A1 60 当A1 60时 返回FALSE 否则TRUE Unit3文本函数的应用 比较函数excat与等号 联结函数concatenate与联结号 Lower upper proper trim函数left right mid函数 1 比较函数excat与等号 Excat是一个比较函数 等号 与之有相同的功能 返回值为逻辑值 对于返回值为逻辑值的函数 一般与if等函数套用 语法 excat A1 B1 举例 1 excat A1 B1 当A1与B1相同时 返回TRUE 否则FALSE 与if函数套用 if excat A1 B1 相同 不相同 2 使用等号比较时 如单元格C1 A1 B1 如果A1与B1相同 C1 TRUE 否则C1 FALSE与if函数套用 if A1 B1 相同 不相同 2 联结函数concatenate与联结号 concatenate是一个联结函数 联结号 与之有相同的功能 语法 concatenate 字符1 字符2 单独的文本要使用双引号举例 1 concatenate A1 B1 tron 如果A1 A B1 s 那么函数就会返回Astron2 如果C1 A1 B1 tron 那么C1 Astron 3 Lower upper proper trim函数 Lower函数是将文本中的字母转换成小写字母 Upper函数是将文本中的字母转换成大写字母Proper函数是将文本中的字符串首字母转换成大写字母 其余字符转换成小写字母 Trim函数是去除字符串中多余空白字符 语法 XXXX 字符串 其中XXXX代表以上所有函数 它们的语法规则一样 字符串代表内容是字符的地址引用比如A1 B2 也可以是单独的字符串 要使用双引号如 astron 3 Lower upper proper trim函数 举例 假如A1 ASTRON B1 astron C1 dongguanastron D1 DongGuanAstronlower A1 astronUpper B1 ASTRONProper C1 DongguanAstronTrim D1 DongGuanAstron 4 left right mid函数 Left函数是自字符串左边开始提取字符串中的字符Right函数是自字符串左边开始提取字符串中的字符Mid函数是从字符串指定位置开始提取字符串中的字符语法 1 Left 字符串 数值 其中数值为要提取的字符数2 Right 字符串 数值 其中数值为要提取的字符数3 Mid 字符串 数值1 数值2 其中数值1代表要提取的第一个字符的位置 数值2代表要提取的字符数 4 left right mid函数 举例 假如A1 AstronLeft A1 2 AsRight A1 2 onMid A1 2 2 st Unit4日期与时间函数 now today函数year month day hour函数dateif函数 1 now today函数 now函数是提取当前系统日期和时间Today函数是提取当前系统日期语法 Now 参数为空Today 参数为空举例 假定系统当前时间为2013 03 309 481 如果A1 now 那么A1 2013 03 309 482 如果B1 today 那么B1 2013 03 30 2 year month day hour函数 year函数作用是提取日期中的年份month函数作用是提取日期中的月份Day函数作用是提取日期中的日数Hour函数作用是提取日期中的小时数语法 XXX A1 其中XXX代表上面所有函数 它们语法规则一样 A1代表包含日期与时间的单元格 举例 假定系统当前时间为A1 2011 11 179 581 B1 year A1 那么B1 20112 C1 month A1 那么C1 113 D1 day A1 那么D1 174 E1 hour A1 那么E1 9 3 dateif函数 dateif函数作用是计算两个日期之间相差的年数 月数 日数 这个函数比较适用于统计出生年月语法 Dateif 起始日期 结束日期 参数类型 其中参数类型包括 Y 时间段中的整年数 M 时间段中的整月数 D 时间段中的天数举例 假定系统当前日期为2011 11 17单元格A1中日期为2000 01 01 下面例子分别计算起始日期与当前日期间相差的年数 月数 日数 1 如果B1 DATEDIF A1 TODAY y 那么B1 112 如果B1 DATEDIF A1 TODAY m 那么B1 1423 如果B1 DATEDIF A1 TODAY d 那么B1 4338 Unit5数学与统计函数 Sum average abs mod函数sumif countif函数max min small large rank函数value isodd iseven函数 1 Sum average abs mod power函数 Sum函数作用是求和Average函数作用是求平均数Abs函数作用是求绝对值Mod函数作用是求余Power函数作用是求n次方语法 Sum 数值1 数值2 其中数值可以直接是数字 也可以是包含数字的单元格 也可以是一片单元格区域 Average语法规则同上Abs 数值 Mod 数值1 数值2 其中数值1是被除数 数值2是除数Power 数值1 数值2 其中数值1是数 数值2是多少次方以上函数返回值皆为数字 1 Sum average abs mod函数 举例 假设A1 1 B1 2 C1 21 sum A1 B1 32 average A1 B1 1 53 abs A1 1 abs C1 24 mod B1 A1 0 mod A1 B1 15 power B1 2 4 另外 数字6上面的符号 也有这个功能 比如B1 3 8另外要注意的是 想要减或者求负数 直接在单元格前加 号就可以了比如上面例子中sum A1 B1 1 2 sumif countif函数 Sumif函数作用是对区域内符合条件的单元格求和Countif函数作用是统计区域内符合条件的单元格的数量语法 Sumif 条件区域 条件 要计算和的区域 Countif 区域 条件 举例 2 sumif countif函数 假设有如下表格1 假如我们要计算1班所有人的手工课的总分 可以使用如下函数SUMIF A2 A5 1 G2 G5 在函数里面 前一区域A2 A5代表查寻条件 1 的查找区域 中间的 1 表示查找的条件 要使用双引号 后一区域G2 G5代表要加总的数字区域2 假如我们要统计手工课大于90分的人数 可以使用countif G2 G5 90 前面代表要查找的区域 后面表示查找条件 记住要使用双引号 3 max min small large rank函数 Max求一组数据的最大值Min求一组数据的最小值Small求一组数据的第N小的值Large求一组数据的第N大的值Rank作用是将一组数组排序语法 Max 数字1 数字n 参数可以是单独的数字 也可以是包含数字的单元格 MIN函数语法同上 Small 单元格区域 数字N 其中前者为包含数字的单元格区域 后者为要查找的第N小的数字 3 max min small large rank函数 large 单元格区域 数字N 其中前者为包含数字的单元格区域 后者为要查找的第N大的数字 Rank 需排序的数字 需排序的数组 排序类型 其中排序类型为0时 按降序排列 不为0时 按升序排列 举例 假设A1 A2 A3 A4 A5分别为12 9 7 7 21Max A1 A5 21Min A1 A5 7Small A1 A5 3 9Large A1 A5 5 7 3 max min small large rank函数 举例假设A1 A2 A3 A4 A5分别为12 9 7 7 21 使用Rank 函数对这个数组进行排序 按降序 从大到小 排列 其中B列是排序后的序号B1 RANK A1 A 1 A 5 0 其中A1表示要排序的数字 A1 A5表示排序的数组 0表示按降序排列 另外要注意的是 引用排序的数组时 我们用了绝对地址引用 A 1 A 5 这个可以用F4鍵在地圵符间进行切换 这样我们在引用公式到B2 B3单元格时 这个地址区域不会变 4 value函数 Value函数作用是将文本类型的数字转换成数字类型 常用于出生日期 银行卡等数据类型的转换 语法 Value 文本 其中文本必须为数字 只是存储类型为文本格式举例 假设A1 20111121 A2 5如果B1 2011 VALUE MID A1 3 2 那么B1 2000 我们知道 MID函数的作用是取文本中的字符 第三章第4小节 在这里 我们从A1单元格的第3个字符开始取 取2个字符 得到字符11 然后将用value函数将其转换成数字类型11 最后与2011相减得2000 Unit6查询与引用函数 Vlookup hlookup函数Match index函数 1 vlookup hlookup函数 Vlookup函数用于在表格或数值数组的首列查找指定的数值 并由此返回表格或数组当前行中指定列处的数值 Hlookup函数用于在表格或数值数组的首行查找指定的数值 并由此返回表格或数组当前列中指定行处的数值 语法 Vlookup 要查找的值 查找的区域 要返回列的序号 查找类型 其中要注意的问题 1 要查找的值一定要位于查找区域的首列2 查找的区域中不要有隐藏的行或列 否则返回的值会出错3 查找类型为0或false 假 时 为精准匹配 当省略或为true时 为近似匹配 没有找到要查找的值时 返回最接近于 查找值 的值 这里我们常常使用精准匹配 保证查找的准确性 Hlookup函数语法与Vlookup函数语法相同 1 vlookup hlookup函数 举例 表1 sheet1 表2 sheet2 如果我们要在表2中根据姓名导出手工课的成绩 可以使用如下函数B2 VLOOKUP A2 表1的 A 2 表1的 F 5 6 0 填写参数时 可以在公式输入栏里选择Vlookup函数后直接用鼠标点击输入1 第一个参数代表我们要查找的值 根据姓名查找手工成绩 要注意的是 查找值有相同的 有同名的 返回第一个值所对应的返回值2 第二个参数代表包含查找值的查找区域 这个查找区域可以在其他工作表上 也可以在同一张工作表上 在本例中我们的查找区域位于工作表一 查找区域使用绝对引用 固定的查找区域我们要使用绝对引用 不然将公式引用到其它单元格时 查找区域也会变动 另外在上面例子中我们的查找值位于查找区域的第一列 必须的 1 vlookup hlookup函数 举例 B2 VLOOKUP A2 表1的 A 2 表1的 F 5 6 0 3 第三个参数6代表我们要返回的值的列号 手工成绩位于第6列 4 第四个参数0代表包含查找类型为精准查找 一般我们要使用精准查找 保证查找值的准确性Hlookup函数使用与Vlookup函数相似 只是将行列互换而已 具体区别大家可以自己体验一下 2 Match index函数 Match函数查找某值在数组中的位置 Index函数作用是返回数组中某行某列的值语法 1 Match 要查找的值 数组 查找类型 其中要查找的值可以是单独输入的值 要使用双引号 也可以是包含查找值的单元格 数组包含查找值的一组单元格 查找类型包含3种 为 1时 参数中的数组须降序排列 为1时 数组须升序排列 为0时 可以是无序排列 2 index 数组 行 列 其中的行列是数组的相对的行列值 不是工作表的行列 2 Match index函数 举例 在上面的数组中1 A8 MATCH 4 A2 A6 0 其中 第一个参数4代表我们要在数组 A2 A4 中查找4这个值 最后一个参数0代表我们查找的数组是无序排列 2 B8 INDEX A2 B6 3 2 其中 第1个参数表示数组的区域 第2 3个参数表示这个查找区域的行和列 Unit7数据库函数的应用 Dget Dcount函数Dmax Dmin Dsum Daverage函数 1 Dget Dcount函数 D 函数是数据库类型函数 与其它类型函数不同的是前面多了一个字母D 数据库database的首字母 数据库函数的使用功能更强大 可以在查找引用中使用更加复杂的查找关系 1 Dget函数提取满足条件的单个记录 有多个值符合条件时返回错误2 Dcount函数返回满足条件的单元格的个数语法 D 函数的语法格式都一样D 数据库区域 函数要使用的数据列 查找条件 其中第一个参数表示构成数据库的单元格区域 如下例中的A3 F10 第二个参数表示函数要使用的数据列 可以是用双引号引用的文本 也可以是列号 如下例中的 语文 1 第三个参数代表包含给定条件的单元格区域 它至少包含一个列标志和列标志下方用于设定条件的单元格 如下例中的B1 B2 B1 C2 1 Dget Dcount函数 举例 1 Dcount 在上表中 如果我们想要统计语文 89的人数数量 可以在B1中设置列标志 语文 在B2中设置给定条件 89 在这里我们在A12单元格求结果A12 DCOUNT