令人叫绝的EXCEL函数大全、实例、教程.xls
《令人叫绝的EXCEL函数大全、实例、教程.xls》由会员分享,可在线阅读,更多相关《令人叫绝的EXCEL函数大全、实例、教程.xls(169页珍藏版)》请在得力文库 - 分享文档赚钱的网站上搜索。
1、目 录序序号号函函数数名名称称分分类类1DCOUNT2DGET3自定义格式4拆分姓名5公式中的括号6百分率7文件名函数8年龄计算9突破SUM函数参数的限制10ABS11ACCRINT12ACOS13ACOSH14ADDRESS15AMORDEGRC16ACCRINTM17AMORLINC18AND19CELL20CEILING21COUNTA22Excel链接23HLOOKUP24CHOOSE25AVERAGE26BIN2DEC27CHAR28CLEAN29CODE30COMBIN31CONCATENATE32CONVERT33COUNT34COUNTBLANK35COUNTIF36VLOOK
2、UP37VLOOKUP38DATEDIF39DATEVALUE40DAVERAGE41DAY42DAYS36043DMAX44DMIN45DOLLAR 或 RMB46DSUM47OFFSET48高级筛选示例149高级筛选示例250高级筛选示例351高级筛选示例452高级筛选示例553高级筛选示例654-55-56Vlookup示例57IndexMatch示例58CountCells59自制推算性格程序(vlookup 模糊查找的力量)60VLOOKUP示例261VLOOKUP示例362-63-64-65-66-67-68-69-70-71-72-73-74-75-76-77-78-79-80-
3、81-82-83-84-85-86-87-88-89-90-91-92-93-94-95-96-97-98-99-100-备备注注绝对值函数运用文本函数的组合运用运算次序CELL(“filename“)、宏表函数get.cell(32)或get.cell(66)DATEDIF() 函数这个函数是用来计算一个数的绝对值,与正负数符号没有关系。返回定期付息有价证券的应计利息。 返回数字的反余弦值。反余弦值是角度,它的余弦值为数字。返回的角度值以弧度 表示,范围是 0 到 pi。 返回 number 参数的反双曲余弦值。参数必须大于或等于 1。反双曲余弦值的双曲 余弦即为该函数的参数, 按照给定的行
4、号和列标,建立文本类型的单元格地址。 返回每个结算期间的折旧值。该函数主要为法国会计系统提供。如果某项资产是在 该结算期的中期购入的,则按直线折旧法计算。该函数与函数 AMORLINC 相似,不 同之处在于该函数中用于计算的折旧系数取决于资产的寿命。 返回到期一次性付息有价证券的应计利息。 返回每个结算期间的折旧值,该函数为法国会计系统提供。如果某项资产是在结算 期间的中期购入的,则按 这个函数用于检验两个或更多(最多30个)条件,看它们是否都为TRUE.返回某一引用区域的左上角单元格的格式、位置或内容等信息。这个函数可以根据用户要求把一个数字向上舍入。统计列表中含数字或文本的单元格个数在表格
5、或数值数组的首列查找指定的数值,并由此返回表格或数组当前行中指定列 处的数值。当比较值位于数据表首列时,可以使用函数 VLOOKUP 代替函数 HLOOKUP 。在 VLOOKUP 中的 V 代表垂直。 可以使用 index_num 返回数值参数列表中的数值。使用函数 CHOOSE 可以基于索 引号返回多达 这个函数能够计算一组数字的平均数这个函数将二进制数转换为十进制数。负数用二进制数补码表示 返回对应于数字代码的字符。函数 CHAR 可将其他类型计算机文件中的代码转换为 字符。 删除文本中不能打印的字符。 返回文本字符串中第一个字符的数字代码。返回的代码对应于计算机当前使用的字 符集。 计
6、算从给定数目的对象集合中提取若干对象的组合数。利用函数 COMBIN 可以确定 一组对象所有可能的组合数。 将几个文本字符串合并为一个文本字符串。将数字从一个度量系统转换到另一个度量系统中 返回包含数字以及包含参数列表中的数字的单元格的个数。利用函数 COUNT 可以计 算单元格区域或数字数组 该函数用于统计一个区域中空白单元格的个数计算区域中满足给定条件的单元格的个数这个函数在表格左侧的行标题中查找指定的内容目 录这个函数在表格左侧的行标题中查找指定的内容这个函数可以计算两个日期间的间隔 返回 date_text 所表示的日期的序列号。函数 DATEVALUE 的主要功能是将以文 本表示的
7、这个函数示例列出了产品的平均价格.这个函数可以从一个完整的日期中提取出日 按照一年 360 天的算法(每个月以 30 天计,一年共计 12 个月),返回两日期 间相差的天数,这在一些会计计算 返回数据清单或数据库的列中满足指定条件的最大数值。返回数据清单或数据库的列中满足指定条件的最小数字。 该函数依照货币格式将小数四舍五入到指定的位数并转换成文本。使用的格式为 ($#,#0.00_);($#,#0.00)。 返回数据清单或数据库的列中满足指定条件的数字之和。DCOUNT产产品品瓦瓦数数寿寿命命( (小小时时)商商标标单单价价每每盒盒数数量量采采购购盒盒数数价价值值 白炽灯2003000上海4
8、.5043¥54.00 氖管1002000上海2.00152¥60.00 日光灯60¥0.00 其它108000北京0.80256¥120.00 白炽灯801000上海0.20403¥24.00 日光灯100未知上海1.25104¥50.00 日光灯2003000上海2.50150¥0.00 其它25未知北京0.50103¥15.00 白炽灯2003000北京5.0032¥30.00 氖管1002000北京1.80205¥180.00 白炽灯100未知北京0.25105¥12.50 白炽灯10800上海0.20252¥10.00 白炽灯601000北京0.15250¥0.00 白炽灯80100
9、0北京0.20302¥12.00 白炽灯1002000上海0.80105¥40.00 白炽灯401000上海0.10205¥10.00统计某一有使用寿命(小时)的产品的商标数商标这两个单元格是数据范围. 商标名称: 北京有寿命(小时)的“北京”商标数是 :5 =DCOUNT(B3:I19,D3,E23:E24)DCOUNTDCOUNT功功能能 返回数据库或数据清单的列中满足指定条件并且包含数字的单元格个数。DCOUNTDCOUNT语语法法 DCOUNT(database,field,criteria)Database 构成数据清单或数据库的单元格区域。数据库是包含一组相关数据的数据清单,其中包
10、含相关信息的行为记录,而包含数据的列为字段。数据清单的第一行包含着每一列的标志项。Field 指定函数所使用的数据列。数据清单中的数据列必须在第一行具有标志项。Field 可以是文本,即两端带引号的标志项,如示例2中的“使用年数”或“产量”;此外,Field 也可以是代表数据清单中数据列位置的数字:1 表示第一列,2 表示第二列,等等。 Criteria 为一组包含指定条件的单元格区域。可以为参数 criteria 指定任意区域,只要它至少包含一个列标志和列标志下方用于设定条件的单元格。参数 field 为可选项,如果省略,函数 DCOUNT 返回数据库中满足条件 criteria 的所有记录
11、数。DCOUNTDCOUNT格格式式 没有专门的格式DCOUNTDCOUNT示示例例1 1指指定定产产品品已已知知采采购购盒盒数数的的总总数数。产品采购盒数 白炽灯5采购盒数为5的产品白炽灯的数量是:3 =DCOUNT(B3:I19,H3,E50:F51)这是相同的计算,但是用采购盒数代替了单元格地址3=DCOUNT(B3:I19,“采购盒数“,E50:F51)产产品品白白炽炽灯灯具具体体指指定定瓦瓦数数为为100100的的总总数数是是产品瓦数 白炽灯100总数是: 2 =DCOUNT(B3:I19,“采购盒数“,E61:F62)产产品品白白炽炽灯灯瓦瓦数数在在=80=80=80101000的
12、数据行。类型销售人员销售额 农产品程香宙1000某某一一列列或或另另一一列列上上具具有有单单个个条条件件 若要找到满足一列条件或另一列条件的数据,请在条件区域的不同行中输入条件。例如,下面的条件区域 将显示所有在类型列中包含农产品、在销售人员列中包含程龙或销售额1000的行。类型销售人员销售额 农产品 程龙 1000两两列列上上具具有有两两组组条条件件之之一一 若要找到满足两组条件(每一组条件都包含针对多列的条件)之一的数据行,请在各行中键入条件。例如, 下面的条件区域将显示所有在销售人员列中包含程香宙且销售额3000的行,同时也显示 程龙销售商的销售额1500的行。 销售人员销售额 程香宙3
13、000 程龙1500一一列列有有两两组组以以上上条条件件 若要找到满足两组以上条件的行,请用相同的列标包括多列。例如,下面条件区域显示5000和 5000AVERAGE($d$81:$D$84)注注意意用作条件的公式必须使用相对引用(相对单元格引用:在公式中,基于包含公式的单元格与被引用的单元 格之间的相对位置的单元格地址。如果复制公式,相对引用将自动调整。相对引用采用 A1 样式。)来引 用列标志(例如“销售”),或者引用第一个记录的对应字段。公式中的所有其他引用都必须是绝对引 用(绝对单元格引用:公式中单元格的精确地址,与包含公式的单元格的位置无关。绝对引用采用的形式 为 $A$1。),并
14、且公式必须计算出结果 TRUE 或 FALSE。您可以在公式中使用列标志来代替相对的单元格引用或区域名称。当 Microsoft Excel 在包含条件的单元 格中显示错误值 #NAME? 或 #VALUE! 时,您可以忽略这些错误,因为它们不影响列表的筛选。 Microsoft Excel 在计算数据时不区分大小写。DGET这是一个数据库区域产品瓦数寿命(小时)商标单价每盒数量采购盒数价值 白炽灯2003000上海4.504354.00 氖管1002000上海2.0015260.00 日光灯600.00 其它108000北京0.80256120.00 白炽灯801000上海0.2040324
15、.00 日光灯100未知上海1.2510450.00 日光灯2003000上海2.5015137.50 其它25未知北京0.5010315.00 白炽灯2003000北京5.003230.00 氖管1002000北京1.80205180.00 白炽灯100未知北京0.2510512.50 白炽灯10800上海0.2025210.00 白炽灯601000北京0.152513.75 白炽灯801000北京0.2030212.00 白炽灯1002000上海0.8010540.00 白炽灯401000上海0.1020510.00指定的符合条件的产品共采购多少?产品瓦数寿命(小时)商标 白炽灯100上海
16、数量是: 5 =DGET(B3:I19,H3,C23:F24)功能 从数据清单或数据库的列中提取符合指定条件的单个值。 如果没有满足条件的记录,则函数 DGET 将返回错误值 #VALUE!。 如果有多个记录满足条件,则函数 DGET 将返回错误值 #NUM!。语法 DGET(database,field,criteria)Database 构成数据清单或数据库的单元格区域。数据库是包含一组相关数据的数据清单,其中包含相关信息的行为记录,而包含数据的列为字段。数据清单的第一行包含着每一列的标志项。Field 指定函数所使用的数据列。数据清单中的数据列必须在第一行具有标志项。Field 可以是文
17、本,即两端带引号的标志项;此外,Field 也可以是代表数据清单中数据列位置的数字:1 表示第一列,2 表示第二列,等等。 Criteria 为一组包含给定条件的单元格区域。可以为参数 criteria 指定任意区域,只要它至少包含一个 列标志和列标志下方用于设定条件的单元格。格式 没有专门的格式示例 1这个示例从正确的记录中提取信息采购了多少盒指定的产品?产品瓦数寿命(小时)商标 白炽灯100上海采购数量是: 5 =DGET(B3:I19,H3,C51:F52)示例 2 这个示例从两个并联的记录中提取信息,因此显示为#NUM错误.采购了多少盒指定的产品?产品瓦数寿命(小时)商标 白炽灯100
18、采购盒数是: #NUM! =DGET(B3:I19,H3,C63:F64)示例 3 这个示例从不存在的记录中提取信息,因此显示为 #VALUE错误.采购了多少盒指定的产品?产品瓦数寿命(小时)商标 白炽灯9999采购盒数是: #VALUE! =DGET(B3:I19,H3,C64:F65)示例 4 这个示例使用了=IF()函数使之在出现错误时得到提示信息.采购了多少盒指定的产品?产品瓦数寿命(小时)商标 白炽灯1000上海采购盒数是: #VALUE! =DGET(B3:I19,H3,C85:F86)没有这个产品.=IF(ISERR(F88),CHOOSE(ERROR.TYPE(F88)/3,“
19、没有这个产品.“,“找到多个产品。“),“找到一个产品.“)自自定定义义格格式式单单元元格格输输入入使使用用文文本本 #,#0 “元人民币“1500 “答复: “通用格式1500 “总额是“#,#0“ 美元“1500分分隔隔长长数数字字 #,#0,123456789 #,#0,12345678912 #,#0,1234 数数据据确确认认 0.00;“仅显示正数!“123 0.00;“仅显示正数!“-123 0.00;“仅显示正数!“00 0用用破破折折号号 #,#0_);(#,#0);-0-_)0 #,#0_);(#,#0);-0-_)12.2 #,#0_);(#,#0);-0-_)-102电
20、电话话号号码码 (#) #-#8.52378E+12 #“/“#-#8.52378E+12福福利利号号 #-#-#421897322 社会福利号 #-#-#421897322日日期期格格式式 mmmm-yy1995/6/1 mmmm d, yyyy1995/6/1 dddd1995/6/1 mmmm d, yyyy (dddd)1995/6/1 “Its“ dddd1995/6/1不不同同的的颜颜色色和和 格格式式 红色=1#,#0;G/通用格式1 红色=1#,#1;G/通用格式-1 红色=1#,#2;G/通用格式45G/通用格式;G/通用格式;G/通用格式;红色G/通用格式程香宙 G/通用格
21、式;G/通用格式;G/通用格式;红色G/通用格式234专专用用符符号号 G/通用格式2004 G/通用格式;G/通用格式;G/通用格式;G/通用格式注册G/通用格式;G/通用格式;G/通用格式;G/通用格式号码G/通用格式;G/通用格式;G/通用格式;G/通用格式文本引述G/通用格式;G/通用格式;G/通用格式;G/通用格式123描描述述正数;“负数“;“零“;“文本“32 正数;“负数“;“零“;“文本“-32 正数;“负数“;“零“;“文本“0 正数;“负数“;“零“;“文本“程香宙隐隐藏藏;1234 ;-145条条件件语语句句 100#,000;99 100#,000;102 100#,
22、000;-54 100#,000;程香宙显显示示为为1,500 元人民币 答复: 1500 总额是1,500美元123,457 12,345,679 1123.00 仅显示正数! 0.00-0- 12 (102)(852377) 659-6295 852377/659-6295421-89-7322 社会福利号 421-89-7322六月-95 六月 1, 1995 星期四 六月 1, 1995 (星期四) Its 星期四1 100.0% 45程香宙 2342004 注册号码 文本引述 123正数 负数 零 文本099 102拆拆分分姓姓名名当你需要拆分某个含有文本格式内容的单元格内文字时要用
23、到这个公式下面几个例子可以是用来提取姓名中一部分的通常方法。需要指出的是:这些文法一般只适应于老外的英文姓名(名在前,姓在后),对于中文姓名,由于书写习惯是姓在前且文字中间没有空格,所以不太适用。这个公式是使用文本函数达到目的的其技巧就在于姓名中间有空格分开提提取取名名姓名名 Alan JonesAlan =LEFT(C14,FIND(“ “,C14,1)Bob SmithBob =LEFT(C15,FIND(“ “,C15,1)Carol WilliamsCarol =LEFT(C16,FIND(“ “,C16,1)提提取取姓姓姓名姓 Alan JonesJones =RIGHT(C22,L
24、EN(C22)-FIND(“ “,C22)Bob SmithSmith =RIGHT(C23,LEN(C23)-FIND(“ “,C23)Carol WilliamsWilliams =RIGHT(C24,LEN(C24)-FIND(“ “,C24)当当有有中中间间名名字字出出现现时时提提取取姓姓这个公式不能用于姓名有两个以上单词的情况如果有中间名,公式的套用将出现不正确的结果解决这个问题的办法是用一个较长的公式姓名姓 Alan David JonesJonesBob John SmithSmithCarol Susan Williams Williams=RIGHT(C37,LEN(C37)
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 令人 叫绝 EXCEL 函数 大全 实例 教程
限制150内