令人叫绝的EXCEL函数大全、实例、教程

《令人叫绝的EXCEL函数大全、实例、教程》由会员分享，可在线阅读，更多相关《令人叫绝的EXCEL函数大全、实例、教程（197页珍藏版）》请在万象文库上搜索。

1、目录序序号号函函数数名名称称分分类类1DCOUNT2DGET3自定义格式4拆分姓名5公式中的括号6百分率7文件名函数8年龄计算9突破SUM函数参数的限制10ABS11ACCRINT12ACOS13ACOSH14ADDRESS15AMORDEGRC16ACCRINTM17AMORLINC18AND19CELL20CEILING21COUNTA22Excel链接23HLOOKUP24CHOOSE25AVERAGE26BIN2DEC27CHAR28CLEAN29CODE30COMBIN31CONCATENATE32CONVERT33COUNT34COUNTBLANK35COUNTIF36VLOOKUP37VLOOKUP38DATEDIF39DATEVALUE40DAVERAGE41DAY42DAYS36043DMAX44DMIN45DOLLAR或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-81-82-83-84-85-86-87-88-89-90-91-92-93-94-95-96-97-98-99-100-备备注注绝对值函数运用文本函数的组合运用运算次序CELL(filename)、宏表函数get.cell(或get.cell(DATEDIF()函数这个函数是用来计算一个数的绝对值，与正负数符号没有关系。

返回定期付息有价证券的应计利息。

返回数字的反余弦值。

反余弦值是角度，它的余弦值为数字。

返回的角度值以弧度表示，范围是0到pi。

返回number参数的反双曲余弦值。

参数必须大于或等于1。

2、反双曲余弦值的双曲余弦即为该函数的参数，按照给定的行号和列标，建立文本类型的单元格地址。

返回每个结算期间的折旧值。

该函数主要为法国会计系统提供。

如果某项资产是在该结算期的中期购入的，则按直线折旧法计算。

3、该函数与函数AMORLINC相似，不同之处在于该函数中用于计算的折旧系数取决于资产的寿命。

返回到期一次性付息有价证券的应计利息。

返回每个结算期间的折旧值，该函数为法国会计系统提供。

4、如果某项资产是在结算期间的中期购入的，则按这个函数用于检验两个或更多（最多30个）条件，看它们是否都为TRUE.返回某一引用区域的左上角单元格的格式、位置或内容等信息。

这个函数可以根据用户要求把一个数字向上舍入。

5、统计列表中含数字或文本的单元格个数在表格或数值数组的首列查找指定的数值，并由此返回表格或数组当前行中指定列处的数值。

6、当比较值位于数据表首列时，可以使用函数VLOOKUP代替函数HLOOKUP。

在VLOOKUP中的V代表垂直。

可以使用index_num返回数值参数列表中的数值。

7、使用函数CHOOSE可以基于索引号返回多达这个函数能够计算一组数字的平均数这个函数将二进制数转换为十进制数。

负数用二进制数补码表示返回对应于数字代码的字符。

函数CHAR可将其他类型计算机文件中的代码转换为字符。

删除文本中不能打印的字符。

返回文本字符串中第一个字符的数字代码。

返回的代码对应于计算机当前使用的字符集。

计算从给定数目的对象集合中提取若干对象的组合数。

利用函数COMBIN可以确定一组对象所有可能的组合数。

将几个文本字符串合并为一个文本字符串。

8、将数字从一个度量系统转换到另一个度量系统中返回包含数字以及包含参数列表中的数字的单元格的个数。

利用函数COUNT可以计算单元格区域或数字数组该函数用于统计一个区域中空白单元格的个数计算区域中满足给定条件的单元格的个数目录这个函数在表格左侧的行标题中查找指定的内容这个函数在表格左侧的行标题中查找指定的内容这个函数可以计算两个日期间的间隔返回date_text所表示的日期的序列号。

函数DATEVALUE的主要功能是将以文本表示的这个函数示例列出了产品的平均价格.这个函数可以从一个完整的日期中提取出日按照一年360天的算法（每个月以30天计，一年共计12个月），返回两日期间相差的天数，这在一些会计计算返回数据清单或数据库的列中满足指定条件的最大数值。

返回数据清单或数据库的列中满足指定条件的最小数字。

该函数依照货币格式将小数四舍五入到指定的位数并转换成文本。

使用的格式为($#,#00_);($#,#。

返回数据清单或数据库的列中满足指定条件的数字之和。

DCOUNT产产品品瓦瓦数数寿寿命命(小小时时)商商标标单单价价每每盒盒数数量量采采购购盒盒数数价价值值白炽灯上海5043￥00氖管上海00152￥00日光灯60￥00其它108000北京80256￥00白炽灯801000上海20403￥00日光灯100未知上海25104￥00日光灯上海50150￥00其它25未知北京50103￥00白炽灯北京0032￥00氖管北京80205￥00白炽灯100未知北京25105￥50白炽灯10800上海20252￥00白炽灯601000北京15250￥00白炽灯801000北京20302￥00白炽灯上海80105￥00白炽灯401000上海10205￥00统计某一有使用寿命(小时)的产品的商标数商标这两个单元格是数据范围.商标名称:北京有寿命(小时)的“北京”商标数是:5=DCOUNT(BI19,D3,EEDCOUNTDCOUNT功功能能返回数据库或数据清单的列中满足指定条件并且包含数字的单元格个数。

DCOUNTDCOUNT语语法法DCOUNT(database,field,criteria)Database构成数据清单或数据库的单元格区域。

数据库是包含一组相关数据的数据清单，其中包含相关信息的行为记录，而包含数据的列为字段。

数据清单的第一行包含着每一列的标志项。

Field指定函数所使用的数据列。

数据清单中的数据列必须在第一行具有标志项。

Field可以是文本，即两端带引号的标志项，如示例2中的“使用年数”或“产量”；此外，Field也可以是代表数据清单中数据列位置的数字：1表示第一列，2表示第二列，等等。

Criteria为一组包含指定条件的单元格区域。

可以为参数criteria指定任意区域，只要它至少包含一个列标志和列标志下方用于设定条件的单元格。

参数field为可选项，如果省略，函数DCOUNT返回数据库中满足条件criteria的所有记录数。

DCOUNTDCOUNT格格式式没有专门的格式DCOUNTDCOUNT示示例例11指指定定产产品品已已知知采采购购盒盒数数的的总总数数。

产品采购盒数白炽灯5采购盒数为5的产品白炽灯的数量是：3=DCOUNT(BI19,H3,EF这是相同的计算，但是用采购盒数代替了单元格地址3=DCOUNT(BI19,采购盒数,EF产产品品白白炽炽灯灯具具体体指指定定瓦瓦数数为为100100的的总总数数是是产品瓦数白炽灯100总数是:2=DCOUNT(BI19,采购盒数,EF产产品品白白炽炽灯灯瓦瓦数数在在=80=80=的数据行。

类型销售人员销售额农产品程香宙1000某某一一列列或或另另一一列列上上具具有有单单个个条条件件若要找到满足一列条件或另一列条件的数据，请在条件区域的不同行中输入条件。

例如，下面的条件区域将显示所有在类型列中包含农产品、在销售人员列中包含程龙或销售额1000的行。

类型销售人员销售额农产品程龙1000两两列列上上具具有有两两组组条条件件之之一一若要找到满足两组条件（每一组条件都包含针对多列的条件）之一的数据行，请在各行中键入条件。

例如，下面的条件区域将显示所有在销售人员列中包含程香宙且销售额3000的行，同时也显示程龙销售商的销售额1500的行。

销售人员销售额程香宙3000程龙1500一一列列有有两两组组以以上上条条件件若要找到满足两组以上条件的行，请用相同的列标包括多列。

例如，下面条件区域显示5000和8000之间以及AVERAGE($d$$D$注注意意用作条件的公式必须使用相对引用（相对单元格引用：在公式中，基于包含公式的单元格与被引用的单元格之间的相对位置的单元格地址。

如果复制公式，相对引用将自动调整。

相对引用采用A1样式。

）来引用列标志（例如“销售”），或者引用第一个记录的对应字段。

公式中的所有其他引用都必须是绝对引用（绝对单元格引用：公式中单元格的精确地址，与包含公式的单元格的位置无关。

绝对引用采用的形式为$A$1。

），并且公式必须计算出结果TRUE或FALSE。

您可以在公式中使用列标志来代替相对的单元格引用或区域名称。

当MicrosoftExcel在包含条件的单元格中显示错误值#NAME?或#VALUE!时，您可以忽略这些错误，因为它们不影响列表的筛选。

MicrosoftExcel在计算数据时不区分大小写。

Database构成数据清单或数据库的单元格区域。

数据库是包含一组相关数据的数据清单，其中包含Field指定函数所使用的数据列。

数据清单中的数据列必须在第一行具有标志项。

Field可以是文本，即两端带引号的标志项，如示例2中的“使用年数”或“产量”；此外，Field也可以是代表数据清单中数Criteria为一组包含指定条件的单元格区域。

可以为参数criteria指定任意区域，只要它至少包含一此函数查找高度在10到16英尺之间的苹果树的记录，并且计算这些记录中“使用年数”字段包含数字的单元格个数。

(此函数查找高度在10到16英尺之间的苹果树记录，并计算这些记录中“利润”字段为非空的单元格个数。

(如果数据库中的数据只是整个果园的一个样本，则此值是苹果树和梨树产量的估算标准偏差。

(如果数据库中的数据为整个果园的样本总体，则此值是苹果树和梨树产量的真实标准偏差。

(如果数据库中的数据只是整个果园的一个样本，那么此值是苹果树和梨树产量的估算方差。

(如果数据库中的数据为整个果园的样本总体，则此值是苹果树和梨树产量的真实方差。

(可以为参数criteria指定任意区域，只要它至少包含一个列标志和列标志下方用于设定条件的单元格。

例如，如果区域GG2在G1中包含列标志Ine，在G2中包含数量10,000，可将此区域命名为虽然条件区域可以在工作表的任意位置，但不要将条件区域置于数据清单的下方。

如果使用“数据”菜单中的“记录单”命令在数据清单中添加信息，新的信息将被添加在数据清单下方的第一行上。

如果数据清单下如果对于某一列具有两个或多个筛选条件，那么可直接在各行中从上到下依次键入各个条件。

例如，下面若要在两列或多列中查找满足单个条件的数据，请在条件区域的同一行中输入所有条件。

例如，下面的条若要找到满足一列条件或另一列条件的数据，请在条件区域的不同行中输入条件。

例如，下面的条件区域若要找到满足两组条件（每一组条件都包含针对多列的条件）之一的数据行，请在各行中键入条件。

例如，可以将公式（公式：单元格中的一系列值、单元格引用、名称或运算符的组合，可生成新的值。

公式总是以等号(=)开始。

）的计算结果作为条件使用。

用公式创建条件时，不要将列标志作为条件标记使用，应该将条件标记置空，或者使用清单中非列标志的标记。

例如，下面的条件区域显示在列d中，其值大于用作条件的公式必须使用相对引用（相对单元格引用：在公式中，基于包含公式的单元格与被引用的单元格之间的相对位置的单元格地址。

如果复制公式，相对引用将自动调整。

相对引用采用A1样式。

）来引用列标志（例如“销售”），或者引用第一个记录的对应字段。

公式中的所有其他引用都必须是绝对引用（绝对单元格引用：公式中单元格的精确地址，与包含公式的单元格的位置无关。

绝对引用采用的形式您可以在公式中使用列标志来代替相对的单元格引用或区域名称。

当MicrosoftExcel在包含条件的单元DGET这是一个数据库区域产产品品瓦瓦数数寿寿命命(小小时时)商商标标单单价价每每盒盒数数量量采采购购盒盒数数价价值值白炽灯上海00氖管上海.00日光灯00其它108000北京.00白炽灯801000上海.00日光灯100未知上海.00日光灯上海.50其它25未知北京.00白炽灯北京00氖管北京.00白炽灯100未知北京.50白炽灯10800上海.00白炽灯601000北京75白炽灯801000北京.00白炽灯上海.00白炽灯401000上海.00指定的符合条件的产品共采购多少?产产品品瓦瓦数数寿寿命命(小小时时)商商标标白炽灯100上海数量是:5=DGET(BI19,H3,CF功功能能从数据清单或数据库的列中提取符合指定条件的单个值。

如果没有满足条件的记录，则函数DGET将返回错误值#VALUE!。

如果有多个记录满足条件，则函数DGET将返回错误值#NUM!。

语语法法DGET(database,field,criteria)Database构成数据清单或数据库的单元格区域。

数据库是包含一组相关数据的数据清单，其中包含相关信息的行为记录，而包含数据的列为字段。

数据清单的第一行包含着每一列的标志项。

Field指定函数所使用的数据列。

数据清单中的数据列必须在第一行具有标志项。

Field可以是文本，即两端带引号的标志项；此外，Field也可以是代表数据清单中数据列位置的数字：1表示第一列，2表示第二列，等等。

Criteria为一组包含给定条件的单元格区域。

可以为参数criteria指定任意区域，只要它至少包含一个列标志和列标志下方用于设定条件的单元格。

格格式式没有专门的格式示示例例1这个示例从正确的记录中提取信息采购了多少盒指定的产品？产产品品瓦瓦数数寿寿命命(小小时时)商商标标白炽灯100上海采购数量是:5=DGET(BI19,H3,CF示示例例2这个示例从两个并联的记录中提取信息，因此显示为#NUM错误.采购了多少盒指定的产品？产产品品瓦瓦数数寿寿命命(小小时时)商商标标白炽灯100采购盒数是:#NUM!=DGET(BI19,H3,CF示示例例3这个示例从不存在的记录中提取信息，因此显示为#VALUE错误.采购了多少盒指定的产品？产产品品瓦瓦数数寿寿命命(小小时时)商商标标白炽灯9999采购盒数是:#VALUE!=DGET(BI19,H3,CF示示例例4这个示例使用了=IF()函数使之在出现错误时得到提示信息.采购了多少盒指定的产品？产产品品瓦瓦数数寿寿命命(小小时时)商商标标白炽灯1000上海采购盒数是:#VALUE!=DGET(BI19,H3,CF没有这个产品.=IF(ISERR(F,CHOOSE(ERROR.TYPE(F/3,没有这个产品.,找到多个产品。

),找到一个产品.)Criteria为一组包含给定条件的单元格区域。

可以为参数criteria指定任意区域，只要它至少包含一个=IF(ISERR(F,CHOOSE(ERROR.TYPE(F/3,没有这个产品.,找到多个产品。

),找到一个产品.)自自定定义义格格式式单单元元格格输输入入使使用用文文本本#,#0元人民币1500答复:通用格式1500总额是#,#0美元1500分分隔隔长长数数字字#,#0,#,#0,#,#0,1234数数据据确确认认00;仅显示正数!00;仅显示正数!-00;仅显示正数!000用用破破折折号号#,#0_);(#,#;-0-_)0#,#0_);(#,#;-0-_)2#,#0_);(#,#;-0-_)-102电电话话号号码码(#)#-#52378E+12#/#-#52378E+12福福利利号号#-#-#社会福利号#-#-#日日期期格格式式mmmm-yy1995/6/1mmmmd,yyyy1995/6/1dddd1995/6/1mmmmd,yyyy(dddd)1995/6/1Itsdddd1995/6/1不不同同的的颜颜色色和和格格式式红色=1#,G/通用格式1红色=1#,G/通用格式-1红色=1#,G/通用格式45G/通用格式;G/通用格式;G/通用格式;红色G/通用格式程香宙G/通用格式;G/通用格式;G/通用格式;红色G/通用格式234专专用用符符号号G/通用格式20XXG/通用格式;G/通用格式;G/通用格式;G/通用格式注册G/通用格式;G/通用格式;G/通用格式;G/通用格式号码G/通用格式;G/通用格式;G/通用格式;G/通用格式文本引述G/通用格式;G/通用格式;G/通用格式;G/通用格式123描描述述正数;负数;零;文本32正数;负数;零;文本-32正数;负数;零;文本0正数;负数;零;文本程香宙隐隐藏藏;1234;-145条条件件语语句句100#,000;99100#,000;102100#,000;-54100#,000;程香宙显显示示为为1,500元人民币答复:1500总额是1,500美元123,45712,345,.00仅显示正数!00-0-12((/-89-7322社会福利号421-89-7322六月-95六月1,1995星期四六月1,1995(星期四)Its星期四0%45程香宙注册号码文本引述123正数负数零文本102拆拆分分姓姓名名当你需要拆分某个含有文本格式内容的单元格内文字时要用到这个公式下面几个例子可以是用来提取姓名中一部分的通常方法。

需要指出的是：这些文法一般只适应于老外的英文姓名(名在前，姓在后)，对于中文姓名，由于书写习惯是姓在前且文字中间没有空格，所以不太适用。

这个公式是使用文本函数达到目的的其技巧就在于姓名中间有空格分开提提取取名名姓名名AlanJonesAlan=LEFT(C14,FIND(,C14,BobSmithBob=LEFT(C15,FIND(,C15,CarolWilliamsCarol=LEFT(C16,FIND(,C16,提提取取姓姓姓名姓AlanJonesJones=RIGHT(C22,LEN(C-FIND(,CBobSmithSmith=RIGHT(C23,LEN(C-FIND(,CCarolWilliamsWilliams=RIGHT(C24,LEN(C-FIND(,C当当有有中中间间名名字字出出现现时时提提取取姓姓这个公式不能用于姓名有两个以上单词的情况如果有中间名，公式的套用将出现不正确的结果解决这个问题的办法是用一个较长的公式姓名姓AlanDavidJonesJonesBobJohnSmithSmithCarolSusanWilliamsWilliams=RIGHT(C37,LEN(C-FIND(#,SUBSTITUTE(C37,#,LEN(C-LEN(SUBSTITUTE(C37,)提提取取中中间间名名姓名中间名AlanDavidJonesDavidBobJohnSmithJohnCarolSusanWilliamsSusan=LEFT(RIGHT(C45,LEN(C-FIND(,C45,,FIND(,RIGHT(C45,LEN(C-FIND(,C45,,=RIGHT(C37,LEN(C-FIND(#,SUBSTITUTE(C37,#,LEN(C-LEN(SUBSTITUTE(C37,)公公式式中中的的括括号号有时你在公式中可能使用到括号才能保证计算按你需要的顺序进行在进行加、减、乘、除混合运算时需要用括号分开数学上的*和/比+和-优先。

*和/的运算将在+和-之前进行.例答案不正确!=C12+C13*C14你可能是想计算10+20得到30然后30*2得到60但是，因为*在Excel中首先计算结果是20*2得到40然后10+40得到错误的结果50例正确答案.=(C27+C*C29在(10+两边放上括号后Excel首先运行它并得到结果30然后用30乘以2得到60这都是你想要的结果。

百百分分率率excelhomechengxiangExcel并没有提供专门用于计算百分率的函数但我们完全可以利用在学校学习的数学知识分类掌握下面这些技巧已已知知百百分分率率求求一一个个数数起始数120百分率25%得数30=D8*D9示示例例1一个公司将要给职员加薪财务部门需要计算职员应该增加多少职员工资级别不同增加的比例不同级别增幅A10%B15%C20%姓名级别原工资增加的工资程香宙A￥10,000￥1,000=E23*LOOKUP(D23,$C$$C$20,$D$$D$刘冰B￥20,000￥3,000=E24*LOOKUP(D24,$C$$C$20,$D$$D$程龙C￥30,000￥6,000=E25*LOOKUP(D25,$C$$C$20,$D$$D$程香峰B￥25,000￥3,750=E26*LOOKUP(D26,$C$$C$20,$D$$D$程坤C￥32,000￥6,400=E27*LOOKUP(D27,$C$$C$20,$D$$D$张凤玲A￥12,000￥1,200=E28*LOOKUP(D28,$C$$C$20,$D$$D$已已知知百百分分率率求求一一个个增增加加了了的的数数起始数120百分率25%得数150=D33*D34+D33示示例例2一个公司将要给职员加薪财务部门需要计算职员工资增加后是多少职员工资级别不同增加的比例不同级别增幅A10%B15%C20%姓名级别原工资增加后工资程香宙A10,00011,000=E48*LOOKUP(D48,$C$$C$20,$D$$D$+E48刘冰B20,00023,000=E49*LOOKUP(D49,$C$$C$20,$D$$D$+E49程龙C30,00036,000=E50*LOOKUP(D50,$C$$C$20,$D$$D$+E50程香峰B25,00028,750=E51*LOOKUP(D51,$C$$C$20,$D$$D$+E51程坤C32,00038,400=E52*LOOKUP(D52,$C$$C$20,$D$$D$+E52张凤玲A1。