excel函数实例应用教程.xls

《excel函数实例应用教程.xls》由会员分享，可在线阅读，更多相关《excel函数实例应用教程.xls（84页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、文文本本与与数数字字函函数数之之: :AscTRUE为近似匹配 3 3、实实例例1 1： 题题目目: :提取数据B10:D15中寻找月份第四行数值100 一月二月三月行1寻找月份: 二月 108097行2需要挑出的行: 4 209069行3结果是: 100 3010045行4 4011051行5 5012077行6公公式式：=HLOOKUP(G10,$B$10:$D$15,G11,FALSE) 结结果果：100简简要要说说明明根据(寻找月份)提取(数据区)的,(需要挑出的行)第四行月份数据,采用精确匹配0. 4 4、实实例例2 2： 题题目目: :在价格表中求项目的单价 价格表砖砖木木材材玻玻

2、璃璃行1项目单价 0.501.002.50行2砖0.50公公式式： =HLOOKUP(F24,$B$23:$D$24,2,0) 结结果果：0.5 简简要要说说明明 根据(项目)提取(价格表)的,(需要挑出的行)第二行数据,采用精确匹配0.5 5、实实例例3 3：题题目目: :在数据A30:D33中，查找首行字母B,并返回同列第三行的值 AxlesAxlesBearingsBearingsBoltsBolts行15 449行2 5710行3 6811行4 公公式式： =HLOOKUP(“B“,$B$30:$D$33,3,TRUE)近似匹配 结结果果：5 简简要要说说明明 在首行查找字母B,并返回

3、同列中第3行的值。由于B不是精确匹配，因此将使用小于 B 的最大值 Axles6 6、实实例例4 4：HLOOKUPHLOOKUP函函数数数数组组的的应应用用 公公式式： =HLOOKUP(3,1,2,3;“A“,“B“,“C“;“D“,“E“,“F“,2,0) 结结果果：C 简简要要说说明明在数组常量的第一行中查找 3，并返回同列中第 2 行的值。C1,2,3;“A“,“B“,“C“;“1,2,3;“A“,“B“,“C“;“ D“,“E“,“F“D“,“E“,“F“相相当当于于123 ABCDEF 普普通通公公式式： =HLOOKUP(3,$D$42:$F$44,2,0) 结结果果：C 整整

4、理理人人:ExceL:ExceL精精英英培培训训网网 BIN_YANG168BIN_YANG1681,2,3;“A“,“B“,“C“;“1,2,3;“A“,“B“,“C“;“ D“,“E“,“F“D“,“E“,“F“相相当当于于在表格或数值数组的首行查找指定的数值，并由此返回表格或数组当前列中指定行处的数值HLOOKUPHLOOKUP(在数据表第一行中查找的数值,数据表,匹配值的行序号,匹配属性) 匹配属性 FALSE,0,省略为精确匹配;TRUE为近似匹配根据(寻找月份)提取(数据区)的,(需要挑出的行)第四行月份数据,采用精确匹配0.根据(项目)提取(价格表)的,(需要挑出的行)第二行数据

5、,采用精确匹配0.在首行查找字母B,并返回同列中第3行的值。由于B不是精确匹配，因此将使用小于 B 的最大值 Axles在数组常量的第一行中查找 3，并返回同列中第 2 行的值。Cexcelexcel函函数数实实例例应应用用教教程程简简要要说说明明：CELLCELL返回某一引用区域的左上角单元格的格式、位置或内容等信息。语语法法 : : CELL(info_type,reference)CELL(info_type,reference)Info_typeInfo_type 为一个文本值，指定所需要的单元格信息的类型。下面列出 info_type 的可能值及 相应的结果.ReferenceRef

6、erence 表示要获取其有关信息的单元格。如果忽略，则在 info_type 中所指定的信息将返 回给最后更改的单元格. INFOINFO返回有关当前操作环境的信息。语语法法 : : INFO(type_text)INFO(type_text)Type_textType_text 为文本，指明所要返回的信息类型。CELLCELL函函数数实实例例应应用用实实例例1 1：题题目目：CELLCELL返回某一引用区域的左上角单元格的格式、位置或内容等信息。语语法法 : : CELL(info_type,reference)CELL(info_type,reference)Info_typeInfo_

7、type 为一个文本值，指定所需要的单元格信息的类型。下面列出 info_type 的可能值及 相应的结果.ReferenceReference 表示要获取其有关信息的单元格。如果忽略，则在 info_type 中所指定的信息将返 回给最后更改的单元格. INFOINFO返回有关当前操作环境的信息。语语法法 : : INFO(type_text)INFO(type_text)Type_textType_text 为文本，指明所要返回的信息类型。公公式式：结结果果：简简要要说说明明实实例例2 2：题题目目：公公式式：结结果果：简简要要说说明明实实例例3 3：题题目目：公公式式1 1： 公公式式2

8、 2：结结果果1 1： 结结果果2 2：简简要要说说明明综综合合运运用用：题题目目：公公式式：结结果果：简简要要说说明明INFOINFO函函数数实实例例应应用用实实例例1 1：题题目目：公公式式：结结果果：简简要要说说明明实实例例2 2：题题目目：公公式式：结结果果：简简要要说说明明excelexcel函函数数实实例例应应用用教教程程CELLCELL返回某一引用区域的左上角单元格的格式、位置或内容等信息。语语法法 : : CELL(info_type,reference)CELL(info_type,reference)Info_typeInfo_type 为一个文本值，指定所需要的单元格信息

9、的类型。下面列出 info_type 的可能值及 相应的结果.ReferenceReference 表示要获取其有关信息的单元格。如果忽略，则在 info_type 中所指定的信息将返 回给最后更改的单元格. INFOINFO返回有关当前操作环境的信息。语语法法 : : INFO(type_text)INFO(type_text)Type_textType_text 为文本，指明所要返回的信息类型。取取得得本本工工作作表表名名CELLCELL返回某一引用区域的左上角单元格的格式、位置或内容等信息。语语法法 : : CELL(info_type,reference)CELL(info_type,

10、reference)Info_typeInfo_type 为一个文本值，指定所需要的单元格信息的类型。下面列出 info_type 的可能值及 相应的结果.ReferenceReference 表示要获取其有关信息的单元格。如果忽略，则在 info_type 中所指定的信息将返 回给最后更改的单元格. INFOINFO返回有关当前操作环境的信息。语语法法 : : INFO(type_text)INFO(type_text)Type_textType_text 为文本，指明所要返回的信息类型。CELLCELL函函数数实实例例应应用用=REPLACE(CELL(“FileName“,A1),1,F

11、IND(“,CELL(“FileName“,A1),“)=REPLACE(CELL(“FileName“,A1),1,FIND(“,CELL(“FileName“,A1),“)CellCell 其他为近似匹配3 3、实实例例1 1：题题目目：提取品名的单价品品名名单单价价查查找找品品名名单单价价 稿纸5.00稿纸5 台灯15.00 桌子75.00 铅笔0.50公公式式：=VLOOKUP(E12,B12:C15,2,0)结结果果：5简简要要说说明明根据(查找品名)提取(数据区)的,第二列单价数据,采用精确匹配0.4 4、实实例例2 2： 题题目目: :查找某业务员对某客户的销售额 辅助列业业务务

12、员员客客户户销销售售额额业业务务员员客客户户 张三丰商场A张三丰商场A100张三丰店铺C 张三丰超市B张三丰超市B200 李四光店铺C李四光店铺C300 李四光超市B李四光超市B400 张三丰店铺C张三丰店铺C500 李四光商场A李四光商场A600公公式式：=VLOOKUP(F22“KT002“,“李四光“;“KT003“,“王麻子“;“KT004“,“赵六儿“ 可以看到，在该内存数组中，【工号】位于【姓名】的左侧，Vlookup在该内存数组中查找注意1,0是一行二列（横向）常量数组，后面的两个区域是多行一列（纵向）， 即两个数组的方向不同，这样才会生成两列多行数组整整理理人人:ExceL:E

13、xceL精精英英培培训训网网 BIN_YANG168BIN_YANG168销销售售额额 500VLOOKUPVLOOKUP(在数据表第一列中查找的数值,数据表,匹配值的列序号,匹配属性)匹配属性 FALSE,0,省略为精确匹配;其他为近似匹配根据(查找品名)提取(数据区)的,第二列单价数据,采用精确匹配0.利用辅助列A21:A27,扩大数据区,来查找满足2个条件的引用=VLOOKUP(B40,IF(1,0,$C$34:$C$37,$B$34:$B$37),2,0)这个公式先由【IF(1,0,$C$34:$C$37,$B$34:$B$37)】计算出内存数组： “KT001“,“张三丰“;“KT0

14、02“,“李四光“;“KT003“,“王麻子“;“KT004“,“赵六儿“ 可以看到，在该内存数组中，【工号】位于【姓名】的左侧，Vlookup在该内存数组中查找注意1,0是一行二列（横向）常量数组，后面的两个区域是多行一列（纵向）， 即两个数组的方向不同，这样才会生成两列多行数组整整理理人人:ExceL:ExceL精精英英培培训训网网 BIN_YANG168BIN_YANG168统统计计函函数数之之: :COUNTIFCOUNTIF 函函数数1 1、简简要要说说明明：计算给定区域内满足特定条件的单元格的数目2 2、基基本本语语法法：= =COUNTIF(COUNTIF(区区域域, ,条条件件

15、) )区域：为需要计算其中满足条件的单元格数目的单元格区域条件：为确定哪些单元格将被计算在内的条件，其形式可以为数字、表达式或文本。3 3、实实例例1 1：题题目目：COUNTIFCOUNTIF基基础础运运用用数数据据类类型型Excel精英培训-1002 21 1学习Excel2 22 22001 11 1Excelpx 网1 12 2606 精英财务1 12 21 12 2财务学习4002 21 1进入Excelpx 2 22 2题题目目公公式式结结果果求真空单元格的个数:=COUNTIF($B$20:$C$27,“=“)3求非真空单元格的个数:=COUNTIF($B$20:$C$27,“=

16、“&200)3求大于100且小于606的单元格的 数=SUM(COUNTIF($B$20:$C$27,“&- 100,606)*1,-1)3求大于100且小于606的单元格的 数=SUM(COUNTIF($B$20:$C$27,“- 100“,“=606“)*1,-1)24 4、实实例例2 2：题题目目：求求不不重重复复值值个个数数EXCELPXEXCEL精英培训网EXCELPXEXCELEXCEL精英培训网EXCELPXEXCEL精英培训网EXCEL公公式式：=SUM(1/COUNTIF(B47:B54,B47:B54)=SUM(1/COUNTIF(B47:B54,B47:B54)结结果果：

17、3 3简简要要说说明明COUNTIF(B47:B54,B47:B54)COUNTIF(B47:B54,B47:B54)可可以以得得到到数数据据系系列列中中各各数数据据值值的的重重复复个个数数，再再求求其其倒倒 数数和和即即可可得得到到不不重重数数据据的的个个数数公公式式是是数数组组公公式式，需需用用EXCELEXCELSHIFTSHIFTENTERENTER同同时时按按下下才才能能生生成成5 5、实实例例3 3：题题目目：利利用用countif()countif()标标记记不不重重复复的的值值公公式式：IF(COUNTIF(B$69:B69,B69)=1,1,“)IF(COUNTIF(B$69

18、:B69,B69)=1,1,“)结结果果：编号记号计算中间过程示例a211111a21112a211211a215111a21122a212211a21222a212311a21232a213111a21312a214111a21412a21413a21414a21415a21512a21513a21514简简要要说说明明：COUNTIF(B$69:B69,B69)COUNTIF(B$69:B69,B69)中中，B$69:B69B$69:B69固固定定首首行行的的变变动动区区域域，countifcountif计计算算值值为为1 1的的即即 为为首首次次计计数数的的值值5 5、实实例例4 4：题

19、题目目：利利用用countif()countif()列列出出不不重重复复的的值值（数数据据为为延延用用“实实例例3”3”的的数数据据）公公式式：INDEX($B$69:$B$87,MATCH(,COUNTIF(B$94:B94,$B$69:$B$87),)INDEX($B$69:$B$87,MATCH(,COUNTIF(B$94:B94,$B$69:$B$87),)结结果果： a2111a2112a2151a2122a2123a2131a2141 处理错误值 处理错误值简简要要说说明明：本本例例利利用用INDEXINDEX、MATCHMATCH和和COUNTIFCOUNTIF相相结结合合，获获

20、取取数数据据系系列列中中不不重重复复值值，是是COUNTIFCOUNTIF较较 为为综综合合的的运运用用，对对于于公公式式的的理理解解需需要要一一定定的的函函数数基基础础。列列出出本本例例的的目目的的是是让让大大家家了了解解COUNTIFCOUNTIF函函数数与与其其他他函函数数组组织织后后的的强强大大的的功功能能。编编写写后后记记：countif()countif()函函数数是是EXCELEXCEL函函数数中中最最常常用用的的函函数数之之一一，在在其其基基本本用用法法下下蕴蕴含含着着十十分分丰丰富富 的的组组织织运运用用，教教程程中中列列出出该该函函数数常常基基本本用用法法，以以及及组组合合

21、用用法法，大大家家可可以以在在熟熟练练掌掌握握基基本本 用用情情况况下下，多多多多挖挖掘掘其其组组合合用用法法，从从而而更更巧巧妙妙地地解解决决实实际际问问题题。假假空空单单元元格格为为字字符符型型真真空空单单元元格格为为数数据据型型含含空空格格单单元元格格为为字字符符型型简简要要说说明明这为一数组公式，可求出半角、全角空格， 空格可能多于一个注：不包括空格单元格注：不包括空格单元格注意：这里用的是count()函数，值不包括真 空单元格所求字符可在任何位置*号代表任意字符COUNTIFCOUNTIF 函函数数计算给定区域内满足特定条件的单元格的数目= =COUNTIF(COUNTIF(区区域

22、域, ,条条件件) )区域：为需要计算其中满足条件的单元格数目的单元格区域条件：为确定哪些单元格将被计算在内的条件，其形式可以为数字、表达式或文本。每一个?代表一个字符求一区间内值的个数求一区间内值的个数，countif函数中的条件可 以是一条件组COUNTIF(B47:B54,B47:B54)COUNTIF(B47:B54,B47:B54)可可以以得得到到数数据据系系列列中中各各数数据据值值的的重重复复个个数数，再再求求其其倒倒 数数和和即即可可得得到到不不重重数数据据的的个个数数公公式式是是数数组组公公式式，需需用用EXCELEXCELSHIFTSHIFTENTERENTER同同时时按按下

23、下才才能能生生成成整整理理人人:ExceL:ExceL精精英英培培训训网网JssyJssyCOUNTIF(B$69:B69,B69)COUNTIF(B$69:B69,B69)中中，B$69:B69B$69:B69固固定定首首行行的的变变动动区区域域，countifcountif计计算算值值为为1 1的的即即 为为首首次次计计数数的的值值利利用用countif()countif()列列出出不不重重复复的的值值（数数据据为为延延用用“实实例例3”3”的的数数据据）INDEX($B$69:$B$87,MATCH(,COUNTIF(B$94:B94,$B$69:$B$87),)INDEX($B$69:

24、$B$87,MATCH(,COUNTIF(B$94:B94,$B$69:$B$87),)本本例例利利用用INDEXINDEX、MATCHMATCH和和COUNTIFCOUNTIF相相结结合合，获获取取数数据据系系列列中中不不重重复复值值，是是COUNTIFCOUNTIF较较 为为综综合合的的运运用用，对对于于公公式式的的理理解解需需要要一一定定的的函函数数基基础础。列列出出本本例例的的目目的的是是让让大大家家了了解解COUNTIFCOUNTIF函函数数与与其其他他函函数数组组织织后后的的强强大大的的功功能能。countif()countif()函函数数是是EXCELEXCEL函函数数中中最最常

25、常用用的的函函数数之之一一，在在其其基基本本用用法法下下蕴蕴含含着着十十分分丰丰富富 的的组组织织运运用用，教教程程中中列列出出该该函函数数常常基基本本用用法法，以以及及组组合合用用法法，大大家家可可以以在在熟熟练练掌掌握握基基本本 用用情情况况下下，多多多多挖挖掘掘其其组组合合用用法法，从从而而更更巧巧妙妙地地解解决决实实际际问问题题。日日期期与与时时间间函函数数之之: :DATEDATE函函数数1 1、简简要要说说明明：根据提供的年月日数字返回一个完整的日期2 2、基基本本语语法法：DATEDATE（年年，月月，日日）3 3、实实例例1 1：题题目目：公公式式：结结果果：简简要要说说明明4

26、 4、实实例例2 2：题题目目：公公式式：结结果果：简简要要说说明明5 5、实实例例3 3：题题目目：公公式式：结结果果：简简要要说说明明计计算算年年份份为为20062006、月月份份为为6 6月月、日日期期为为1212号号的的日日期期=DATE(2006,6,12)=DATE(2006,6,12)2006/6/122006/6/12根根据据给给出出的的年年月月日日返返回回一一个个完完整整的的日日期期根根据据下下面面给给出出的的1515位位身身份份证证号号, ,返返回回出出生生日日期期410105780512625410105780512625=DATE(MID(C25,7,2),MID(C2

27、5,9,2),MID(C25,11,2)=DATE(MID(C25,7,2),MID(C25,9,2),MID(C25,11,2)1978/5/121978/5/12根根据据身身份份证证号号利利用用MIDMID函函数数截截取取年年 月月 日日作作为为DATEDATE函函数数的的参参数数计计算算20062006年年8 8月月的的最最后后一一天天日日期期=DATE(2006,9,0)=DATE(2006,9,0)2006/8/312006/8/31DATEDATE第第3 3个个参参数数如如果果0,0,即即为为本本月月1 1日日的的前前一一天天, ,如如果果是是-1-1则则是是本本月月1 1号号的的

28、倒倒推推2 2天天根据提供的年月日数字返回一个完整的日期DATEDATE（年年，月月，日日）整整理理人人:ExceL:ExceL精精英英培培训训网网兰兰色色幻幻想想日日期期与与时时间间函函数数之之: :DAYDAY & MONTHMONTH & YEARYEAR 函函数数1 1、简简要要说说明明：DAY:返回指定日期的具体天数。MONTH:返回指定日期的具体月份。YEAR:返回指定日期的具体年龄。2 2、基基本本语语法法：= =dayday( (日日期期) )= =TODAYTODAY( (日日期期) )= =TODAYTODAY( (日日期期) )3 3、实实例例1 1：题题目目：公公式式：

29、返回天数返回月份返回年份结结果果：返回天数返回月份返回年份简简要要说说明明4 4、实实例例2 2：题题目目：公公式式：结结果果：简简要要说说明明5 5、实实例例3 3：题题目目：公公式式：结结果果：简简要要说说明明返返回回单单元元格格c18c18的的年年份份、月月份份和和天天数数2006-7-52006-7-5=Day(c19)=Day(c19)=Month(c19)=Month(c19)=Year(c19)=Year(c19)5 57 720062006公公式式一一般般用用于于将将日日期期转转化化为为具具体体的的年年份份、月月份份和和天天数数，以以有有利利用用于于下下一一步步进进行行筛筛选选

30、等等操操作作根根据据c36c36单单元元格格提提供供的的日日期期，返返回回该该日日期期+“+“收收入入报报表表”2001/5/62001/5/6YEAR(C36)&“YEAR(C36)&“年年“&MONTH(C36)&“&MONTH(C36)&“月月“&DAY(C36)&“&DAY(C36)&“日日收收入入报报表表“ “20012001年年5 5月月6 6日日收收入入报报表表DAYDAY & MONTHMONTH & YEARYEAR 函函数数DAY:返回指定日期的具体天数。MONTH:返回指定日期的具体月份。YEAR:返回指定日期的具体年龄。= =dayday( (日日期期) )= =TOD

31、AYTODAY( (日日期期) )= =TODAYTODAY( (日日期期) )如如果果用用公公式式C36&“C36&“收收入入报报表表“ “ ，结结果果为为“37017“37017收收入入报报表表”。当当然然本本题题用用TEXTTEXT函函数数也也较较为为 简简便便。在在此此不不再再介介绍绍。计计算算当当前前月月份份的的天天数数=DAY(DATE(YEAR(TODAY(),MONTH(TODAY()+1,0)=DAY(DATE(YEAR(TODAY(),MONTH(TODAY()+1,0)30301 1、YEAR(TODAY()YEAR(TODAY()返返回回当当天天日日期期的的年年份份 2 2、MONTH(TODAY()+1MONTH(TODAY()+1当当前前日日期期的的下下一一个个月月份份 3 3、DATE(YEAR(TODAY(),MONTH(TODAY()+1,0)DATE(YEAR(TODAY(),MONTH(TODAY()+1,0)即即是是指指当当前前月月份份的的下下一一个个月月份份1 1日日的的前前一一 天天，即即本本月月最最后后一一天天 4 4、用用dayday（）得得到到最最后后一一天天的的天天数数整整理理人人:ExceL:ExceL精精英英培培训训网网兰兰色色幻幻想想