常用EXCEL函数使用详解ppt课件

常用EXCEL函数讲解 2018年9月18日 新手 初级用户 中级用户 高级用户和专家五个层次新手 学习者需要大致了解到Excel的基本操作方法和常用功能 诸如输入数据 查找替换 设置单元格格式 排序 汇总 筛选和保存工作簿 初级用户 可以开始在工作中运用Excel 比如建立一个简单的表格 画一张简单的图表 会一些基本简单的函数 如SUM IF等 中级用户三个标志 一是理解并熟练使用各个Excel菜单命令二是熟练使用数据透视表三是至少掌握20个常用函数以及函数的嵌套运用 掌握的基本函数有SUM函数 IF函数 VLOOKUP函数 INDEX函数 MATCH函数 OFFSET函数 TEXT函数等等 高级用户两个标志 一是熟练运用数组公式 也就是那种用花括号包围起来 必须用组合键才能完成录入的公式 Excel专家 从Excel的功能细分来看 精通全部的人想必寥寥无几 Excel是应用性太强的软件 意味着一个没有任何工作经验的普通学生是很难成为Excel专家的 所以Excel专家也必定是某个或多个行业的专家 他们都拥有丰富的行业知识和经验 高超的Excel技术配合行业经验来共同应用 才有可能把Excel发挥到极致 所以 如果希望成为Excel专家 就不能只单单学习Excel了 Excel函数的种类 财务函数日期函数时间函数数学与三角函数统计函数数据库管理函数文本函数信息类函数 函数的基本语法 函数的基本语法为 函数名 参数1 参数2 参数n 注意问题 函数名与其后的括号 之间不能有空格 当有多个参数时 参数之间要用逗号 分隔 参数部分总长度不能超过1024个字符 参数可以是数值 文本 逻辑值 单元格引用 也可以是各种表达式或函数 函数中的逗号 引号 等都是半角字符 而不是全角字符 常用函数介绍 求和函数 SUM SUMIF SUMPRODUCT函数数学函数AVERGAE MAX MIN ABS SQRT函数计数函数 COUNT COUNTA COUNTIF函数条件函数和逻辑函数 IF函数 AND函数 OR函数日期和时间函数 DAY DATE DAYS360 TODAY函数等分析工具库里的日期函数查找和引用函数 VLOOKUP HLOOKUP MATCH INDEX CHOOSE OFFSET函数四舍五入函数 ROUND FLOOR和CEILING函数取整函数 INT函数信息函数 ISBLANK ISTEXT ISNUMBER ISERROR函数文本函数 LEN LEFT RIGHT TRIM FIND TEXT等函数排序函数 RANK LARGE SMALL函数数据库函数 DGET DMAX DMIN DSUM DAVGEAGE函数其他函数 求和函数SUM SUMIF SUMPRODUCT函数 SUM函数 无条件求和 SUM 参数1 参数2 参数N SUMIF函数 条件求和 SUMIF range criteria sum range SUMPRODUCT函数 在给定的几组数组中 将数组间对应的元素相乘 并返回乘积之和 SUMPRODUCT array1 array2 array3 求和函数 应用举例 SUM函数SUMIF函数SUMPRODUCT函数 求和函数 应用举例 隔栏加总计算 某预算表如图所示 每个月分为 预算数 和 实际数 现要求计算年度 预算数 和 实际数 这实际上就是隔栏加总问题 如果一个一个单元格相加是很繁琐的 解决方法 巧妙应用第3行的标题 求和函数 应用举例 往下 或往右 累加汇总 往下 或往右 累加汇总是常见的实务问题 解决方法 使用SUM函数 但单元格引用的第一个地址应为绝对引用 而第二个地址为相对引用 求和函数 应用举例 动态汇总当天数据 工作表中存在每天的数据 要求动态汇总计算当前的数据 可使用SUMIF函数 但要注意条件的写法 数学函数AVERGAE MAX MIN ABS SQRT函数 常用的数学函数有 AVERGAE函数 求平均值MAX 求最大值MIN 求最小值ABS 求绝对值SQRT 计算平方根 平均值函数 AVERGAE函数 AVERGAE函数 求N个数的平均值 AVERGAE 参数1 参数2 参数N 最大值与最小值函数 MAX MIN函数 MAX函数 求N个数的最大值 MAX 参数1 参数2 参数N MIN函数 求N个数的最小值 MIN 参数1 参数2 参数N 绝对值函数 ABS函数 ABS函数 求某数的绝对值 ABS 参数 例如 ABS 100 100 开平方函数 SQRT函数 SQRT函数 求某数的平方根 SQRT 参数 例如 SQRT 2 1 4142135623731 计数函数 COUNT COUNTA COUNTIF函数 COUNT函数 计算给定区域内数值型参数的数目 COUNT 参数1 参数2 参数N COUNTA函数 返回参数列表中非空值的单元格个数 COUNTA 参数1 参数2 参数N COUNTIF函数 计算给定区域内满足特定条件的单元格的数目 COUNTIF range criteria 计数函数 应用举例 COUNT函数 COUNTA函数 COUNTIF函数 计数函数应用 计算销售业绩 计数函数 计算高于平均业绩的人数 条件函数和逻辑函数 IF函数 AND函数 OR函数 IF函数 IF函数也称条件函数 它根据参数条件的真假 返回不同的结果 IF 条件表达式 条件值为真时返回的值 条件值为假时返回的值 AND函数 AND函数表示逻辑与 当所有条件都满足时 即所有参数的逻辑值都为真时 AND函数返回TRUE 否则 只要有一个条件不满足即返回FALSE AND 条件1 条件2 条件N OR函数 只要有一个条件满足时 OR函数返回TRUE 只有当所有条件都不满足时才返回FALSE OR 条件1 条件2 条件N 说明 这3个函数常常联合使用 IF函数应用举例之一 计算奖金 IF函数应用举例之二 计算增长率 计算增长率当被比较年份没有数据时 会出现被除数为0的错误 可以利用IF函数进行处理 如图 去年没有数据 而今年有数据 就显示 新增项目 去年有数据 而今年没有数据 就显示 已经停产 IF函数应用举例之三 自动归类问题为了有效管理零用金 希望在输入现金开支数据后 该金额数据自动依部门归类到适当的列 从而更加醒目地标示出来 解决办法 使用IF函数如图 先在单元格F2输入公式 IF D2 F 1 C2 选定单元格区域F2 J2 按 F2 使处于编辑状态 按 Ctrl Enter 组合键注意单元格的引用方式 AND函数应用举例 OR函数应用举例 联合使用IF AND和OR函数计算奖金 日期函数 DAY DATE DAYS360 TODAY函数 DAY函数 返回以序列号表示的某日期的天数 用整数1 31表示 DAY 日期序列号 例如DAY 2006 12 22 22DATE函数 返回代表特定日期的序列号 DATE 年 月 日 例如DATE 2006 12 23 2006 12 23 DAYS360函数 按照一年360天计 每个月以30天 一年共计12个月 返回两个日期间相差的天数 DAYS360 开始日期 截止日期 逻辑值 例如DAYS360 2000 1 15 2005 12 16 2131天TODAY函数 返回系统当前的日期 日期函数应用 举例 设置动态标题 今天是 TEXT TODAY yyyy年m月d日 今天是 TEXT TODAY yyyy年m月d日 TEXT WEEKDAY TODAY aaaa 设置上月标题 IF MONTH TODAY 1 12 MONTH TODAY 1 月份收支情况 设置本月标题 MONTH TODAY 月份收支情况 将日期转换为星期中文星期 TEXT WEEKDAY A1 aaaa 英文星期 TEXT WEEKDAY A1 dddd 计算2年5个月20天后的日期 DATE YEAR A1 2 MONTH A1 5 DAY S1 20 计算实际岁数 INT YEARFRAC 出生日期 今天 1 DATEIF 出生日期 今天 Y 日期函数应用举例 确定具体日期 日期函数应用举例 确定两个日期间的天数 日期函数应用举例 设置日期显示格式 日期函数应用举例 编制下周计划表 编制下周计划表可以在本周的任何一天制作下周的计划表注意 任何一天的日期减去本身的星期数 就一定等于上一个星期天的日期 日期函数综合应用 设计考勤表 时间函数 输入时间输入22 00与输入10 00PM是一样的 如何计算跨午夜零时的时间间隔 输入 B2 B2 A2 A2或者 B2 IF B2 A2 1 0 A2思路 如果下班时间小于上班时间 就表示已经过了1天 因此要加1 否则 如果下班时间大于上班时间 就表示还在当前 因此不需要加1天 分析工具库里的日期函数 EDATE函数EOMONTH函数WEEKNUM函数WORKDAY函数NETWORKDAYS函数一个特殊的日期函数 DATEDIF注意 在使用这些函数之前 必须加载分析工具库 即 单击 工具 加载宏 命令 打开 加载宏 对话框 选择 分析工具库 如上图 EDATE函数 EDATE函数 返回指定日期往前或往后几个月的日期 例 2007年4月12日之后3个月的日期 EDATE 2007 4 12 3 为2007 7 122007年4月12日之前3个月的日期 EDATE 2007 4 12 3 为2007 1 12计算应付账款的到期日 如果一笔应付款的到期日为自交易日起3个月的那一天 比如交易日为2006年11月30日 满3个月后为2007 2 28 EDATE 2006 11 30 3 为2007 2 28 EOMONTH函数 EOMONTH函数 返回指定日期往前或往后几个月的特定月份的月底日期 例 2007年4月12日之后3个月的月末日期 EOMONTH 2007 4 12 3 为2007 7 312007年4月12日之前5个月的月末日期 EDATE 2007 4 12 5 为2006 11 30计算应付账款的到期日 如果一笔应付款的到期日为自交易日起满3个月后的下一个月的5号 比如交易日为2006年11月20日 满3个月后下个月5号就是2007 3 5 EOMONTH 2006 11 20 3 DAY A1 5 5 WEEKNUM函数 WEEKNUM函数 返回指定日期是该年的第几周 例 2007年4月12日是2007年的第15周 WEEKNUM 2007 4 12 为第15周 WORKDAY函数 WORKDAY函数 返回某指定日期之前或之后的给定工作日天数的日期 除去双休日和国家法定假日 例 指定日期为2007年4月12日 往后30个工作日的日期为 要出去国家法定的五一3天假日 2007年5月29日 WORKDAY 2007 4 12 30 2007 5 1 2007 5 2 2007 5 3 假若一项工程开始日期为2007年4月20日 预计需要60个工作日 那么预计完工的时间为哪天 NETWORKDAYS函数 NETWORKDAYS函数 返回两个工作日之间的工作天数 除去双休日和国家法定假日 例 2007年4月12日至2007年6月20日之间的工作天数 除去双休日和国家法定假日 为47天 一个特殊的日期函数 DATEDIF 确定两个日期间的年数 月数和天数 DATEDIF函数 计算两个日期之间的天数 月数或年数 这个函数是一个特殊函数 在函数清单中找不到 在帮助信息中也找不到 DATEDIF 开始日期 结束日期 单位 单位意义 Y 时间段中的总年数 M 时间段中的总月数 D 时间段中的总天数 MD 两日期中天数的差 忽略日期数据中的年和月 YM 两日期中月数的差 忽略日期数据中的年和日 YD 两日期中天数的差 忽略日期数据中的年例如 某职员进公司日期为1985年3月20日 离职时间为2007年8月9日 那么他在公司工作了多少年 多少月和多少天 工作年数 DATEDIF 1985 3 20 2007 8 9 Y 22年工作月数 DATEDIF 1985 3 20 2007 8 9 YM 4个月工作天数 DATEDIF 1985 3 20 2007 8 9 MD 20天问题 如果某人2月1日到职 2月28日离职 任职时间是28天还是1个月 注意 2月1日到职 开始日期应为1月31日 2月28日离职 结束日期应为3月1日 查找和引用函数 VLOOKUP HLOOKUP MATCH INDEX CHOOSE OFFSET函数 VLOOKUP函数 在表格或数值数组的首列查找指定的数值 并由此返回表格或数组当前行中指定列处的数值 HLOOKUP函数 从表格或数值数组的首行查找指定的数值 并由此返回表格或数组当前列中指定行处的数值 MATCH函数 返回在指定方式下与指定数值匹配的数组中元素的相应位置 INDEX函数 返回表格或区域中的数值或对数值的引用 CHOOSE函数 根据指定的索引值返回数组中的数据OFFSET函数 动态引用单元格 提示 我们还可以利用名称来查找数据 详见文件 通过名称查找数据 xls VLOOKUP函数应用举例之一 VLOOKUP函数应用举例之二制作采购表 根据两个表格制作采购表 如图所示 计算步骤如下 单元格区域B10 B13为各种商品的采购数量 由人工输入 在单元格C10中输入公式 VLOOKUP A10 A 3 B 6 2 FALSE 获取某商品的单价 在单元格D10中输入公式 VLOOKUP B10 D 3 H 6 MATCH A10 D 3 H 3 0 TRUE 获取某商品的折扣 在单元格E10中输入公式 B10 C10 1 D10 计算采购金额 选取单元格区域C10 E10 将其向下填充复制到单元格区域C13 E13 得到其他商品的单价 折扣和采购金额 VLOOKUP函数应用举例之三根据产品代码查询该产品的详细信息 根据产品代码查询该产品的详细信息 如图所示 说明 由于产品代码是唯一的标识符 没有重复 所以可以利用VLOOKUP函数或者HLOOKUP函数进行查找 HLOOKUP函数应用举例之一 HLOOKUP函数应用举例之二 根据指定的行 列查找数据 HLOOKUP函数应用举例之三计算底薪佣金制的薪金 推销员的薪金采用底薪佣金制 计算规则如下 营业额底薪佣金比率0 299 99925 0000 300 000 499 99925 0001 500 000 999 99930 0002 1 000 000 1 499 99935 0003 1 500 000以上40 0004 整理上述数据到工作表 并输入相应计算公式 即得各个推销人员的薪金 注意设计底薪佣金制计算规则表时要采用各级的底限值 MATCH函数应用举例之一基本应用 注意 MATCH返回的位置是相对于指定的单元格区域而言的 而不是对整个工作表区域而言的 例如 MATCH 455 A2 E2 0 3 MATCH函数应用举例之二比较两张工作表 将仅存在于其中某个工作表的数据筛选出来 如图 在两个工作表中有重复的数据 现在要在工作表 财务部上报 中将工作表 销售部上报 中重复的数据隐藏起来 筛选步骤 以工作表 财务部上报 为准 设计一个辅助列 在单元格B2输入公式 ISERROR MATCH A2 销售部上报 A 2 A 7 0 并向下复制到数据区域的末尾 单击 数据 筛选 自动筛选 命令 筛选出需要对数据 最后采用选择性粘贴的方法将数据复制到别处 INDEX函数应用举例 MATCH函数与INDEX函数联合使用 查找某部门某项费用的数额 根据指定的部门名称和费用项目 查找相应的金额 如下图 说明 先用MATCH查找费用项目在第几行 用MATCH查找部门在第几列 然后用INDEX函数取出行列交叉处的数据 利用数组公式处理多维数据的查询 我们也可以利用连字符 连接多个条件进行多维数据的查询 先定义名称 再利用MATCH函数和INDEX函数进行查询 CHOOSE函数应用举例 CHOOSE函数语法 CHOOSE 索引号 值1 值2 例如 CHOOSE 1 AA BB CC DD AA CHOOSE 3 AA BB CC DD CC 举例 依在本单位工作年限发放中秋节礼品礼品发放规定 本单位工龄未满3年的送咖啡壶 满3年未满6年者送饮水机 满6年未满9年者送电磁炉 9年以上者送电烤箱 说明 也可以使用IF函数 注意 本单位工龄的计算方法 Excel默认的日期系统是1900系统 也可以利用YEARFRAC函数计算工龄 或年龄 INT YEARFRAC C4 H 1 OFFSET函数应用 OFFSET函数语法 OFFSET 参照单元格 列位移量 行位移量 高度 宽度 例如 公式 OFFSET C3 2 3 1 1 将返回单元格F5中的值 这里 当前指定的引用为单元格C3 以此为参照系 向下偏移2行 为第5行 向右偏移3列 为F列 高度和宽度均为1 表示仅为一个单元格 计算步骤 在单元格B13输入 OFFSET INDIRECT A MATCH B 10 A 1 A 7 0 0 COLUMN A1 然后将单元格B13向右复制 在上述公式中 首先使用MATCH查找单元格B10中的数字所在A列的行 然后利用INDIRECT返回A列中该行所在单元格的数据 最后利用OFFSET函数取得该行各列的数据 在单元格B14输入 SUM OFFSET B1 1 B 10 计算到指定月份某部门的合计数 然后将单元格B14向右复制 说明 OFFSET函数在制作动态查询和动态图表时是非常有用的 从多个表格区域中查询数据之一 根据销售人员任期确定提成率 有多个表格需要查询 根据实际条件决定要查询那个表格 利用IF语句判断使用那个表格 实例如下 销售人员任期不同 则提成率也不同 从多个表格区域中查询数据之二 有多个表格区域 结构相同 根据指定的部门 月份 费用项目查询相应的费用金额 从多个工作表中查询数据 某公司将客户分为A B C三个等级 给于不同的客户编号 分别以A B C开头 不同等级客户的折扣率是不同的 而同一等级客户里不同产品的折扣率也是不同的 在确定客户等级和产品之后 还得依据订购数量给于该客户最终的折扣 怎样编写查询公式 太复杂了 从多个工作表中查询数据 考虑到从不同的工作表进行查询 而工作表名称即为 客户 加客户编号的第一个字母 因此可以利用INDIRECT函数获取要查询工作表的单元格区域 利用MATCH函数确定指定商品编号所在的列和采购金额所在的行 再利用INDEX函数取出相应的折扣率 单元格G2的公式如下 INDEX INDIRECT 客户 LEFT B2 1 B 4 E 7 MATCH F2 INDIRECT 客户 LEFT B2 1 A 4 A 7 MATCH C2 INDIRECT 客户 LEFT B2 1 B 3 E 3 区分大小写的查询 VLOOKUP函数和HLOOKUP函数不区分大小写 如果要区分大小写进行查询 可以联合使用INDEX函数 MATCH函数和EXACT函数 四舍五入函数 ROUND FLOOR和CEILING函数 ROUND函数 返回某个数字按指定位数舍入后的数字 FLOOR函数 根据指定基数 将数字沿绝对值减小的方向向下舍入到最接近的倍数 CEILING函数 将参数Number向上舍入 沿绝对值增大的方向 到最接近的倍数 例如 ROUND 300 5485 2 300 55FLOOR 2 5 1 2 FLOOR 2 5 2 2CEILING 2 5 1 3 CEILING 2 5 2 4 四舍五入函数应用举例 产品价格标定 应用举例 某贸易公司经常要以汇率换算产品的成本价格 加上必要的管理费及预期利润后就是产品的价格 因此 产品价格的百位数以下都会有零头 公司的政策是 凡是小于30的尾数去掉 而大于或等于30的尾数则进位成100 例如 4004被标成4000 4227被标成4200 而2145被标成2200 1765被标成1800 那么 该怎样设定计算公式计算价格标定 计算思路 以100为基数利用FLOOR函数取出元时定价的尾部部分 然后利用IF函数判断该尾数是否大于或等于30 然后再决定是利用CEILING函数还是FLOOR函数处理原数据 取整函数 INT函数 INT函数 将数字向下舍入到最接近的整数 INT 数字 例如 INT 300 5485 300 INT 300 5485 301例 某企业根据经营部门的完成率进行评分 标准如下 完成率59 99 以下为1分 超过60 为2分 超过70 为3分 超过80 为4分 超过90 为5分 分析 我们可以使用IF函数进行计算 但由于各个标准的间隔正好是10 因此也可以利用INT函数进行运算 并进行评分 说明 公式中 B2 0 49999 10用于计算超过49 999 的有多少个10 而 B2 0 5 表示只有在完成率超过50 时才利用公式 B2 0 49999 10进行计算 信息函数 ISBLANK ISTEXT ISNUMBER ISERROR函数 ISBLANK函数 判断单元格是否为空白单元格ISTEXT函数 判断单元格数据是否为文本ISNUMBER函数 判断单元格数据是否为数字ISERROR函数 判断单元格是否出现错误 文本函数LEN LEFT RIGHT TRIM FIND TEXT等函数 LEN函数 获取字符串的长度LEFT函数 获取字符串左边指定个数的文本RIGHT函数 获取字符串右边指定个数的文本TRIM函数 取消字符串两侧的空格FIND函数 查找某字符在字符串中第一次出现的位置TEXT函数 将数值转换为按指定数字格式表示的文本其他的文本函数 文本函数应用举例 例1 LEN ABCD 4LEFT ABCD 2 AB RIGHT ABCD 2 CD TRIM ABCD ABCD FIND 12345 65 6TEXT 12345 6687 0 00 12345 67例2 从身份证号码获取出生日期和性别 文本函数应用举例 例3 将科目编码与科目名称分离 假设科目编码与科目名称之间有一个空格 问题 如果科目编码与科目名称之间没有空格 该怎么