《excel技巧之6函数高级应用实战技巧》由会员分享，可在线阅读，更多相关《excel技巧之6函数高级应用实战技巧（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中，输入有关员工工资的信息，然后选择单元格B4，单击“公式”选项卡下“函数库”组中的“查找与引用”下拉按钮，在弹出的下拉菜单中选择VLOOKUP函数，如下图所示。 弹出“函数参数”对话框，从中设置相应的参数，如下图所示。设置拖动 单击【确定】按钮，关闭对话框，得到公式结果，然后拖动填充柄填充公式，如下图所示。例9 返回最后一个非空单元格在包含空的单元格中，要查找最后一个非空
