EXCEL中函数的使用详解ppt课件

Excel函数应用17thDec2015JeffZheng 提高电脑操作速度的几个小心得 尽量不要将所有文件都放在桌面建立分等级的临时文件夹任务栏工作组的应用Alt Tab和Win Tab组合键的应用Win7搜索文件方法单击与双击的区别 我们为什么要学Excel函数 如何去重复值 打印体检表 幸存者偏差 学生打分 关于Excel的几个基本概念 工作簿与工作表行 列 单元格路径相对引用与绝对引用函数与公式Excel2003版与2007版数据引用时的问题 Excel函数应用 Excel函数的种类函数的基本语法函数的调用嵌套函数的使用使用Excel的帮助来理解函数常用函数介绍 Excel函数的种类 财务函数日期函数时间函数数学与三角函数统计函数数据库管理函数文本函数信息类函数 函数的基本语法 函数的基本语法为 函数名 参数1 参数2 参数n 注意问题 函数名与其后的括号 之间不能有空格 当有多个参数时 参数之间要用逗号 分隔 参数部分总长度不能超过1024个字符 参数可以是数值 文本 逻辑值 单元格引用 也可以是各种表达式或函数 函数中的逗号 引号 等都是半角字符 而不是全角字符 函数的调用 直接在单元格中输入函数利用插入函数按钮或命令插入函数 嵌套函数的使用 当一个函数中的参数为另外一个函数时 就是在使用嵌套函数 利用插入函数按钮或命令插入各个函数 使用Excel的帮助来理解函数 在Excel帮助里搜索函数名在单元格中输入函数名后点击下方提示中的函数名 常用函数介绍 求和函数 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函数应用举例 日期函数 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 TODAY aaaa 英文星期 TEXT WEEKDAY TODAY dddd 计算2年5个月20天后的日期 DATE YEAR TODAY 2 MONTH TODAY 5 DAY TODAY 20 日期函数应用举例 确定具体日期 日期函数应用举例 确定两个日期间的天数 日期函数应用举例 设置日期显示格式 分析工具库里的日期函数 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天 查找和引用函数 VLOOKUP HLOOKUP MATCH INDEX CHOOSE OFFSET函数 VLOOKUP函数 在表格或数值数组的首列查找指定的数值 并由此返回表格或数组当前行中指定列处的数值 HLOOKUP函数 从表格或数值数组的首行查找指定的数值 并由此返回表格或数组当前列中指定行处的数值 MATCH函数 返回在指定方式下与指定数值匹配的数组中元素的相应位置 INDEX函数 返回表格或区域中的数值或对数值的引用 CHOOSE函数 根据指定的索引值返回数组中的数据OFFSET函数 动态引用单元格 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函数应用举例之二 根据指定的行 列查找数据 MATCH函数应用举例之一基本应用 注意 MATCH返回的位置是相对于指定的单元格区域而言的 而不是对整个工作表区域而言的 例如 MATCH 455 A2 E2 0 3 MATCH函数应用举例之二删除重复值 如何快速将A列重复的单号标示出来在B2单元格内输入 IF MATCH A2 A A 0 ROW 重复 然后将公式下拉到底 INDEX函数应用举例 MATCH函数与INDEX函数联合使用 查找某部门某项费用的数额 根据指定的部门名称和费用项目 查找相应的金额 如下图 说明 先用MATCH查找费用项目在第几行 用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函数 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函数在制作动态查询和动态图表时是非常有用的 区分大小写的查询 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 表