excel2007技巧之6函数高级应用实战技巧教学文案.doc
《excel2007技巧之6函数高级应用实战技巧教学文案.doc》由会员分享,可在线阅读,更多相关《excel2007技巧之6函数高级应用实战技巧教学文案.doc(63页珍藏版)》请在淘文阁 - 分享文档赚钱的网站上搜索。
1、Chapter 6 图形和图表实战技巧嵌套函数实战技巧函数的嵌套是指某个函数引用另一个函数的返回值来进行计算。文本函数实战技巧文本函数用于对文本数据进行提取、删除、替换以及组合等运算。统计函数实战技巧财务函数实战技巧查找和引用函数实战技巧统计函数主要有对单元格数量统计、排位、检验与回归分析等功能。财务函数用于对财务数据进行分析和计算,是财务工作中必不可少的工具。查找和引用函数用于在数据表中查找某一特定的内容,或者引用公式中的相关信息内容。日期和时间函数用于对日期和时间进行计算,修改等操作中。日期函数实战技巧函数高级应用实战技巧6函数是Excel中重要的内容,本章将在上一章的基础上,从不同的函数
2、类型着手,详细为读者讲述函数高级应用的内容和技巧,以帮助读者完全掌握这部分的知识。本章导读6.1 日期和时间函数实战技巧257例1 快速将日期转换为星期输入输入输入在Excel中使用函数可以快速将日期转换为星期,具体操作方法如下: 在工作表中输入几个日期,然后在单元格D3中输入函数“=TEXT(WEEKDAY(D3),”dddd”,如下图所示。 按【Enter】键,将日期转换成星期,在单元格D4中输入“=TEXT(WEEKDAY(C4),”aaaa”,如下图所示。 按【Enter】键,得到中文的星期,结果如下图所示。例2 计算当前日期是全年的第几天在Excel中使用函数可以快速将日期转换为星期
3、,具体操作方法如下: 打开“台历”工作表,复制并修改。在单元格G5中输入公式“=E4-DATE(YEAR(E4),1,0)”,如下图所示。 按【Ctrl+Enter】组合键,得到公式计算结果,如下图所示。例3 计算上个月的天数在Excel中计算上个月的天数,可使用如下的两种方法。方法一:使用DAY函数计算输入 新建工作表并输入数据内容,在单元格C4中输入公式“=DAY(B4-DAY(B4)”,如下图所示。拖动 按【Ctrl+Enter】组合键,得到公式计算结果,然后用拖动填充柄填充公式,最终效果如下图所示。输入DAY函数用于返回所引用日期中的天数,DAY(B4)计算得出天数5,DAY(B4-D
4、AY(B4)的含义是返回5天前的天数,即上个月最后一天的天数。方法二:使用DAY函数计算输入 在单元格D4中输入公式“=DAY(EOMONTH(B4,-1)”,如下图所示。拖动 按【Ctrl+Enter】组合键,得到公式计算结果,然后拖动填充柄填充公式,结果如下图所示。EOMONTH函数用于返回所引用日期之前或之后的月份最后一天的序列号。EOMONTH(B4,-1)的含义是返回单元格B4中日期前一个月的最后一天的序列。例4 轻松求得当前日期所在的周数每一年的1月1日所在的周是一年中的第一周,使用WEEKNUM函数可求得某个日期所在的周数,具体操作方法如下: 打开“台历”工作表,在单元格G6中输
5、入函数“=WEEKNUM(E5)”,如下图所示。 单击【Ctrl+Enter】组合键,得到当前日期所在年中的周数,如下图所示。WEEKNUM函数的语法为WEEKNUM(serial_num,return_type),Serial_num代表一周中的日期,Return_type为1个数字,确定星期计算从哪一天开始,如果为1,说明一周开始于星期日如果为2,则说明一周开始于星期一,如果省略不写,则默认为1。例5 巧妙计算员工年龄在Excel中,如果已知员工的出生年月,就可使用YEAR函数快速的计算出员工的年龄,具体操作方法如下:输入 在单元格D3中输入公式“=YEAR(TODAY()-C3)-190
6、0”,如下图所示。 按【Ctrl+Enter】组合键,得到结果,如下图所示。输入输入 在“开始”选项卡下“数字”组中的“数字格式”下拉列表框中,将该单元格的数字格式设置为“常规”,该单元格中将自动显示年龄,如下图所示。 将鼠标指针移动单元格D3的右下角,拖动填充柄,填充公式,效果如下图所示。因为Excel中使用了1900年时间系统,公式TODAY()-C3进行计算时将自动将相减的结果加上1900,所有要得到正确的年龄,还需减去1900。例6 计算两个日期之间工作日的天数计算两个日期之间工作日的天数的具体操作方法如下: 在单元格C6中输入公式“=NETWORKDAYS(C4,D4)”,如下图所示
7、。 按【Ctrl+Enter】组合键,得出计算结果,如下图所示。NETWORKDAYS返回参数 start_date 和 end_date 之间完整的工作日数值。其中不包括周日和假期。例7 计算同年中两个日期之间的月数计算同年中两个日期之间的月数的具体操作方法如下:在单元格E4中输入公式“=MONTH(D4)-MONTH(C4)”,按【Ctrl+Enter】键即可计算出结果,如下图所示。输入输入例8 计算跨年份两个日期之间的月数 在单元格E5中输入公式“=YEAR(D5)-YEAR(C5)*12+MONTH(D5)-MONTH(C5)”,按【Ctrl+Enter】键即可计算出结果,如下图所示。
8、例9 计算两个日期之间的年数计算年数的方法很简单,只需用YEAY函数将两个返回值相减即可,如下图所示。输入6.2 查找和引用函数实战技巧例1 使用函数巧查询在Excel中,VLOOKUP函数用于在表格数组的首列查找指定的值,并由此返回表格数组当前行中其他列的值。下面使用该函数来快速查找所需的值,具体操作方法如下: 打开“楼盘概览”工作表,在单元格区域E14:F15中输入数据内容和公式,如下图所示。输入 在单元格E15中输入相应的楼盘名称,将在单元格F15中显示该楼盘的开盘时间,如下图所示。VLOOKUP函数最后一个参数为逻辑值,其中false值代表精确匹配,true值代表模糊匹配。例2 用V.
9、LOOKUP函数逆向查询从上一个技巧中,已知VLOOKUP函数只能根据首列中指定的值查询其他列与之相对应的内容。其实,使用该函数和其他函数的嵌套也可以实现通过其他列的数据来查询首列的内容,即逆向查询,其具体操作方法如下: 如果已知楼盘的开盘时间,要查询楼盘名称,可输入公式“=VLOOKUP(F16,IF(0,1,D5:D12,E5:E12),2,FALSE)”,如下图所示。输入5.单击 按【Ctrl+Enter】组合键,将得到楼盘的名称,如下图所示。例3 CHOOSE函数的用法CHOOSE函数可以根据索引号返回数值参数列表中的数值。下面通过使用该函数得到奖金等级为例,介绍其使用方法。 在员工的
10、奖金名单中,选择单元格D3,在其中输入公式“=CHOOSE(IF(C3=5000,1,2),一等奖,二等奖,三等奖)”,如下图所示。输入 按【Ctrl+Enter】组合键,得到奖金等级,如下图所示。 通过拖动填充柄,复制公式,得到其他员工的销售奖金等级,如下图所示。例4 快速查找化工原料的数量继续上一个实例,通过将INDEX函数与MATCH函数配合使用,可以查找某种化学原料的数量,其具体操作方法如下: 在化工原料信息工作表中,单击单元格D48,从中输入公式“=INDEX(C39:C45, MATCH(草酸盐,B39:B45,0)”,如下图所示。输入 按【Ctrl+Enter】组合键确认,将得到
11、“草酸盐”的数量为460,如下图所示。例5 使用函数进行动态求和下面通过实例来介绍使用函数进行动态求和的过程,具体操作方法如下:1.输入2.选择拖动 新建工作表,从中输入产品销售额的相关信息,选择单元格D11,然后单击“数据”选项卡下“数据工具”组中的“数据有效性”按钮下方的下拉按钮,在弹出的下拉菜单中选择“数据有效性”选项,如下图所示。单击2.设置1.选择青年以3.单击4.选择 将弹出“数据有效性”对话框,在其中的“允许”下拉列表框中选择“序列”选项,在“来源”文本框中设置序列来源,如下图所示。 单击“确定”按钮,在单元格D11右侧将出现一个下拉按钮,单击它,将弹出下拉列表,可以从中进行选择
12、,效果如下图所示。2.单击1.选择 选择单元格区域C4:C8,然后单击“公式”选项卡下“定义的名称”组中的“定义名称”按钮,将弹出“新建名称”对话框,保持默认即可,如下图所示。 分别选择单元格区域D4:D8、E4:E8和F4:F8,定义名称,然后单击“定义的名称”组中的“名称管理器”按钮,弹出“名称管理器”对话框,从中可查看定义的名称,如下图所示。2.输入1.选择 选择单元格D12,从中输入公式“=SUM(INDIRECT($D$11)”,按【Ctrl+Enter】组合键确认,将得到“销售点1”的所有产品的销售额,如下图所示。例6 判断员工姓名是否重复录入在员工信息表中,要判断是否重复录入了员
13、工的信息,可通过查找员工姓名是否重复,来进行判断(假如员工中没有重名者),其具体操作方法如下: 新建员工信息表,选择单元格H4,从中输入“=IF(ROW()=MATCH(C4,$C$1:$C$16,0),重复)”,如下图所示。输入拖动 按【Ctrl+Enter】组合键确认输入,然后拖动填充柄进行填充,将查找出重复的姓名,如下图所示。例7 快速生成数字序列制作工作表时,经常要输入数字序列,输入数字序列的方法有很多,这里我们使用函数来快速输入数字序列,其具体操作方法如下: 新建工作表,选择单元格区域A1:A10,然后按【F2】键,从中输入“=ROW(1:10)”,如下图所示。2.输入1.选择 按【
14、Ctrl+Shift+Enter】组合键,确认数组输入,即可快速填充好数字序列,如下图所示。输入 要在一行中填充数字序列,可以先选择要输入数字序列的单元格区域,然后按【F2】键输入函数“=COLUMN(A:F)”,如下图所示。 按【Ctrl+Shift+Enter】组合键,确认数组输入,也可快速填充字序列,如下图所示。例8 快速引用不同工作表中的数据在制作工作表时,经常用在不同的工作表中引用数据信息,下面介绍一种使用函数引用其他工作表中的数据的方法。 在Sheet3中创建一张员工档案表,存放员工的基本信息,如下图所示。3.选择2.单击1.选择 切换到Sheet1中,输入有关员工工资的信息,然后
15、选择单元格B4,单击“公式”选项卡下“函数库”组中的“查找与引用”下拉按钮,在弹出的下拉菜单中选择VLOOKUP函数,如下图所示。 弹出“函数参数”对话框,从中设置相应的参数,如下图所示。设置拖动 单击【确定】按钮,关闭对话框,得到公式结果,然后拖动填充柄填充公式,如下图所示。例9 返回最后一个非空单元格在包含空的单元格中,要查找最后一个非空单元格中的值,可按如下方法操作: 建工作表,从中制作有关产品的信息数据表,新然后在单元格E13中输入公式“LOOKUP(CHAR(255),B:B)”,然后按【Ctrl+Enter】组合键确认,得到公式查找的结果,如下图所示。输入输入 在单元格E14中输入
16、公式“LOOKUP(9E+307,C:D),然后按【Ctrl+Enter】组合键,得到最后结果,如下图所示。CHAR函数用于返回对应于数字代码的字符,它可将其他类型计算机文件中的代码转换为字符;9E+307为Excel中可以输入的最大数值。例10 快速查找最后非空单元格的位置快速查找最后一个非空单元格的位置的具体操作步骤如下:输入 在单元格F3中输入公式“=MATCH(CHAR(1),B:B,-1)”,如下图所示。 按【Ctrl+Enter】组合键,查找到的最后一个非空单元格的位置为11,如下图所示。例11 快速定位到空单元格通过创建单元格名称和使用链接函数可以快速定位到空单元格中。下面以具体
17、的实例介绍快速定位到空单元格的方法。2.单击1.输入 新建工作表,从中输入产品信息,单击“公式”选项卡下“定义的名称”组中的“定义名称”按钮,如下图所示。 弹出“新建名称”对话框,在“名称”文本框中输入文字“空单元格”,在“引用位置”填充2.输入单击2.输入2.输入1.输入文本框中输入公式“=OFFSET($B$1,COUNTA($B:$B)+1,)”如下图所示。 单击“确定”按钮,创建动态名称,然后选择单元格F3,从中输入“=HYPERLINK(#空单元格,快速定位)”,如下图所示。 公式确认后,将在单元格F3中显示链接文字“快速定位”,单击它,将定位到“产品”列下方的空单元格中,如下图所示
18、。例12 快速返回唯一值列表有的工作表中有不少重复的数据信息,要过滤这些重复的信息,让表中显示唯一的数据值,可以采用如下的方法。 打开一张产品信息表,在该表中“产品”列中有重复的产品信息,如下图所示。 在单元格B14中输入公式“=OFFSET($B$2,SMALL(IF(MATCH($B$3:$B$11,$B$3:$B$11,0)=ROW($B$3:$B$11)-2,ROW($B$3:$B$11)-2),ROW(A1),)”,如下图所示。 按【ctrl+shift+enter】组合键,返回产品值“AC001”,使用填充柄进行填充,将得到不重复的值,结果如下图所示。MATCH($B$3:$B$1
19、1,$B$3:$B$11,0)返回各产品名称所在的单元格位置,该位置如果为该产品名称所在表中的位置,说明该产品名称不重复,否则产品名称重复。6.3 财务函数实战技巧例1 使用PMT函数计算还款金额输入选择PMT函数返回在固定利率下,投资或贷款的等额分期偿还额。使用该函数计算贷款的还款额的具体操作方法如下: 打开“分期付款计算表”工作表,选择单元格E4,如下图所示。选择 单击“公式”选项卡下“函数库”组中的“插入函数”按钮,弹出“插入函数”对话框,在“选择函数”列表框中选择PMT函数,如下图所示。 单击“确定”按钮,弹出“函数参数”对话框,从中输入参数,如下图所示。 单击“确定”按钮,得到计算结
20、果,用前面所讲的复制公式的方法,复制公式,计算出其他的月偿还金额,如下图所示。PMT函数的语法结构为PMT(rate, nper,pv,fv,type),其中,rate为贷款利率。nper为该项贷款的付款期总数。pv为现值,或一系列未来付款的当前值的累积和,也称为本金。Fv为未来值,或在最后一次付款后希望得到的现金余额,如果省略 fv,则假设其值为0,也就是一笔贷款的未来值为0。Type为数字 0 或 1,用以指定各期的付款时间是在期初还是期末。如果省略,则默认为0,代表期末付款。例2 使用PMT函数计算存款金额假设在银行存款,要使10年后得到7万元,则计算每月的存款金额,可使用如下的方法得出
21、: 新建工作表,从中输入数据,并在单元格C5中输入公式“=PMT(C4/12,C3*12,0,70000)”如下图所示。输入 按【Ctrl+Enter】组合键,计算出每个月应存款的金额,如下图所示。例3 使用PPMT函数求本金偿还额PPMT函数基于固定利率及等额分期付款方式,用于返回投资在某一给定期间内的本金偿还额。使用该函数计算本金偿还金额的具体操作方法如下: 新建工作表,从中输入数据,选择单元格C5,输入“=PPMT(”,如下图所示。输入设置 按【Shift+F3】组合键,弹出“函数参数”对话框,从中设置参数,如下图所示。拖动 单击“确定”按钮,求得返还的第一年本金额,然后拖动填充柄,填充
22、公式,最终效果如下图所示。例4 使用IPMT函数求本金偿还额IPMT函数基于固定利率及等额分期付款方式,用于返回给定期数内对的利息偿还额。使用该函数计算利息偿还金额的具体操作方法如下: 以上个技巧中的表格为基础,添加“返还利息”列,在单元格D5中输入函数“=IPMT($D$3,B5,$C$3,$B$3)”,如下图所示。填充输入 按【Ctrl+Enter】组合键,得到第一年的返还利息额,通过拖动填充柄,填充公式,得到每年的返还利息,最终效果如下图所示。例5 使用FV函数计算投资价值使用FV函数可以返回投资的现值。现值为一系列未来付款的当前值的累积和。该函数的使用方法如下: 在“投资计算表”中单击
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- excel2007 技巧 函数 高级 应用 实战 教学 文案
限制150内