搜索
本章导读函数是Excel中重要的内容，本章将在上一章的基础上，从不同的函数类型着手，详细为读者讲述函数高级应用的内容和技巧，以帮助读者完全掌握这部分的知识。6函数高级应用实战技巧日期函数实战技巧日期和时间函数用于对日期和时间进行计算，修改等操作中。查找和引用函数用于在数据表中查找某一特定的内容，或者引用公式中的相关信息内容。财务函数用于对财务数据进行分析和计算，是财务工作中必不可少的工具。统计函数主要有对单元格数量统计、排位、检验与回归分析等功能。查找和引用函数实战技巧财务函数实战技巧统计函数实战技巧文本函数用于对文本数据进行提取、删除、替换以及组合等运算。文本函数实战技巧函数的嵌套是指某个函数引用另一个函数的返回值来进行计算。嵌套函数实战技巧2046.1日期和时间函数实战技巧例例11快快速速将将日日期期转转换换为为星星期期在Excel中使用函数可以快速将日期转换为星期，具体操作方法如下：在工作表中输入几个日期，然后在单元格D3中输入函数“=TEXT(WEEKDAY(D3),”dddd””，如下图所示。按【Enter】键，将日期转换成星期，在单元格D4中输入“=TEXT(WEEKDAY(C4),”aaaa””，如下图所示。按【Enter】键，得到中文的星期，结果如下图所示。例例22计计算算当当前前日日期期是是全全年年的的第第几几天天在Excel中使用函数可以快速将日期转换为星期，具体操作方法如下：打开“台历”工作表，复制并修改。在单元格G5中输入公式“=E4-DATE(YEAR(E4),1,0)”，如下图所示。按【Ctrl+Enter】组合键，得到公式计算结果，如下图所示。例例33计计算算上上个个月月的的天天数数在Excel中计算上个月的天数，可使用如下的两种方法。方法一：使用DAY函数计算新建工作表并输入数据内容，在单元格输入输入输入205Chapter6图形和图表实战技巧C4中输入公式“=DAY(B4-DAY(B4))”，如下图所示。按【Ctrl+Enter】组合键，得到公式计算结果，然后用拖动填充柄填充公式，最终效果如下图所示。方法二：使用DAY函数计算在单元格D4中输入公式“=DAY(EOMONTH(B4,-1))”，如下图所示。按【Ctrl+Enter】组合键，得到公式计算结果，然后拖动填充柄填充公式，结果如下图所示。例例44轻轻松松求求得得当当前前日日期期所所在在的的周周数数每一年的1月1日所在的周是一年中的第一周，使用WEEKNUM函数可求得某个日期所在的周数，具体操作方法如下：打开“台历”工作表，在单元格G6中输入函数“=WEEKNUM(E5)”，如下图所示。单击【Ctrl+Enter】组合键，得到当前日期所在年中的周数，如下图所示。DAY函数用于返回所引用日期中的天数，DAY(B4)计算得出天数5，DAY(B4-DAY(B4))的含义是返回5天前的天数，即上个月最后一天的天数。EOMONTH函数用于返回所引用日期之前或之后的月份最后一天的序列号。EOMONTH(B4,-1)的含义是返回单元格B4中日期前一个月的最后一天的序列。拖动输入拖动输入206例例55巧巧妙妙计计算算员员工工年年龄龄在Excel中，如果已知员工的出生年月，就可使用YEAR函数快速的计算出员工的年龄，具体操作方法如下：在单元格D3中输入公式“=YEAR(TODAY()-C3)-1900”，如下图所示。按【Ctrl+Enter】组合键，得到结果，如下图所示。在“开始”选项卡下“数字”组中的“数字格式”下拉列表框中，将该单元格的数字格式设置为“常规”，该单元格中将自动显示年龄，如下图所示。将鼠标指针移动单元格D3的右下角，拖动填充柄，填充公式，效果如下图所示。例例66计计算算两两个个日日期期之之间间工工作作日日的的天天数数计算两个日期之间工作日的天数的具体操作方法如下：因为Excel中使用了1900年时间系统，公式TODAY()-C3进行计算时将自动将相减的结果加上1900，所有要得到正确的年龄，还需减去1900。输入输入WEEKNUM函数的语法为WEEKNUM(serial_num,return_type)，Serial_num代表一周中的日期，Return_type为1个数字，确定星期计算从哪一天开始，如果为1，说明一周开始于星期日如果为2，则说明一周开始于星期一，如果省略不写，则默认为1。输入207Chapter6图形和图表实战技巧在单元格C6中输入公式“=NETWORKDAYS(C4,D4)”，如下图所示。按【Ctrl+Enter】组合键，得出计算结果，如下图所示。例例77计计算算同同年年中中两两个个日日期期之之间间的的月月数数计算同年中两个日期之间的月数的具体操作方法如下：在单元格E4中输入公式“=MONTH(D4)-MONTH(C4)”，按【Ctrl+Enter】键即可计算出结果，如下图所示。例例88计计算算跨跨年年份份两两个个日日期期之之间间的的月月数数在单元格E5中输入公式“=YEAR(D5)-YEAR(C5)*12+MONTH(D5)-MONTH(C5)”，按【Ctrl+Enter】键即可计算出结果，如下图所示。例例99计计算算两两个个日日期期之之间间的的年年数数计算年数的方法很简单，只需用YEAY函数将两个返回值相减即可，如下图所示。NETWORKDAYS返回参数start_date和end_date之间完整的工作日数值。其中不包括周日和假期。。输入2086.2查找和引用函数实战技巧例例11使使用用函函数数巧巧查查询询在Excel中，VLOOKUP函数用于在表格数组的首列查找指定的值，并由此返回表格数组当前行中其他列的值。下面使用该函数来快速查找所需的值，具体操作方法如下：打开“楼盘概览”工作表，在单元格区域E14:F15中输入数据内容和公式，如下图所示。在单元格E15中输入相应的楼盘名称，将在单元格F15中显示该楼盘的开盘时间，如下图所示。输入209Chapter6图形和图表实战技巧例例22用用VV..LLOOOOKKUUPP函函数数逆逆向向查查询询从上一个技巧中，已知VLOOKUP函数只能根据首列中指定的值查询其他列与之相对应的内容。其实，使用该函数和其他函数的嵌套也可以实现通过其他列的数据来查询首列的内容，即逆向查询，其具体操作方法如下：如果已知楼盘的开盘时间，要查询楼盘名称，可输入公式“=VLOOKUP(F16,IF({0,1},D5:D12,E5:E12),2,FALSE)”，如下图所示。按【Ctrl+Enter】组合键，将得到楼盘的名称，如下图所示。例例33CCHHOOOOSSEE函函数数的的用用法法CHOOSE函数可以根据索引号返回数值参数列表中的数值。下面通过使用该函数得到奖金等级为例，介绍其使用方法。在员工的奖金名单中，选择单元格D3，在其中输入公式“=CHOOSE(IF(C3=3000,3,IF(C3=5000,1,2)),一等奖,二等奖,三等奖)”，如下图所示。按【Ctrl+Enter】组合键，得到奖金等级，如下图所示。输入5.单击VLOOKUP函数最后一个参数为逻辑值，其中false值代表精确匹配，true值代表模糊匹配。输入210通过拖动填充柄，复制公式，得到其他员工的销售奖金等级，如下图所示。例例44快快速速查查找找化化工工原原料料的的数数量量继续上一个实例，通过将INDEX函数与MATCH函数配合使用，可以查找某种化学原料的数量，其具体操作方法如下：在化工原料信息工作表中，单击单元格D48，从中输入公式“=INDEX(C39:C45,MATCH(草酸盐,B39:B45,0))”，如下图所示。按【Ctrl+Enter】组合键确认，将得到“草酸盐”的数量为460，如下图所示。例例55使使用用函函数数进进行行动动态态求求和和下面通过实例来介绍使用函数进行动态求和的过程，具体操作方法如下：新建工作表，从中输入产品销售额的相关信息，选择单元格D11，然后单击“数据”选项卡下“数据工具”组中的“数据有效性”按钮下方的下拉按钮，在弹出的下拉菜单中选择“数据有效性”选项，如下图所示。将弹出“数据有效性”对话框，在其中的“允许”下拉列表框中选择“序列”选项，在“来源”文本框中设置序列来源，如下图所示。输入拖动2.选择1.输入3.单击4.选择1.选择青年以2.设置211Chapter6图形和图表实战技巧单击“确定”按钮，在单元格D11右侧将出现一个下拉按钮，单击它，将弹出下拉列表，可以从中进行选择，效果如下图所示。选择单元格区域C4:C8，然后单击“公式”选项卡下“定义的名称”组中的“定义名称”按钮，将弹出“新建名称”对话框，保持默认即可，如下图所示。分别选择单元格区域D4:D8、E4:E8和F4:F8，定义名称，然后单击“定义的名称”组中的“名称管理器”按钮，弹出“名称管理器”对话框，从中可查看定义的名称，如下图所示。选择单元格D12，从中输入公式“=SUM(INDIRECT($D$11))”，按【Ctrl+Enter】组合键确认，将得到“销售点1”的所有产品的销售额，如下图所示。例例66判判断断员员工工姓姓名名是是否否重重复复录录入入在员工信息表中，要判断是否重复录入了员工的信息，可通过查找员工姓名是否重复，来进行判断（假如员工中没有重名者），1.选择2.单击1.选择2.输入212其具体操作方法如下：新建员工信息表，选择单元格H4，从中输入“=IF((ROW()=MATCH(C4,$C$1:$C$16,0)),,重复)”，如下图所示。按【Ctrl+Enter】组合键确认输入，然后拖动填充柄进行填充，将查找出重复的姓名，如下图所示。例例77快快速速生生成成数数字字序序列列制作工作表时，经常要输入数字序列，输入数字序列的方法有很多，这里我们使用函数来快速输入数字序列，其具体操作方法如下：新建工作表，选择单元格区域A1:A10，然后按【F2】键，从中输入“=ROW(1:10)”，如下图所示。按【Ctrl+Shift+Enter】组合键，确认数组输入，即可快速填充好数字序列，如下图所示。要在一行中填充数字序列，可以先选择要输入数字序列的单元格区域，然后按【F2】键输入函数“=COLUMN(A:F)”，如下图所示。按【Ctrl+Shift+Enter】组合键，确认数组输入，也可快速填充字序列，如下图所示。输入拖动1.选择2.输入输入213Chapter6图形和图表实战技巧例例88快快速速引引用用不不同同工工作作表表中中的的数数据据在制作工作表时，经常用在不同的工作表中引用数据信息，下面介绍一种使用函数引用其他工作表中的数据的方法。在Sheet3中创建一张员工档案表，存放员工的基本信息，如下图所示。切换到Sheet1中，输入有关员工工资的信息，然后选择单元格B4，单击“公式”选项卡下“函数库”组中的“查找与引用”下拉按钮，在弹出的下拉菜单中选择VLOOKUP函数，如下图所示。弹出“函数参数”对话框，从中设置相应的参数，如下图所示。单击【确定】按钮，关闭对话框，得到公式结果，然后拖动填充柄填充公式，如下图所示。例例99返返回回最最后后一一个个非非空空单单元元格格在包含空的单元格中，要查找最后一个非空单元格中的值，可按如下方法操作：1.选择2.单击3.选择设置拖动214建工作表，从中制作有关产品的信息数据表，新然后在单元格E13中输入公式“LOOKUP(CHAR(255),B:B)”，然后按【Ctrl+Enter】组合键确认，得到公式查找的结果，如下图所示。在单元格E14中输入公式“＝LOOKUP(9E+307,C:D)，然后按【Ctrl+Enter】组合键，得到最后结果，如下图所示。例例1100快快速速查查找找最最后后非非空空单单元元格格的的位位置置快速查找最后一个非空单元格的位置的具体操作步骤如下：在单元格F3中输入公式“=MATCH(CHAR(1),B:B,-1)”，如下图所示。按【Ctrl+Enter】组合键，查找到的最后一个非空单元格的位置为11，如下图所示。例例1111快快速速定定位位到到空空