excel最常用函数个人整合版之呕心沥血之作Word格式文档下载.docx

excel最常用函数个人整合版之呕心沥血之作Word格式文档下载.docx

《excel最常用函数个人整合版之呕心沥血之作Word格式文档下载.docx》由会员分享，可在线阅读，更多相关《excel最常用函数个人整合版之呕心沥血之作Word格式文档下载.docx（17页珍藏版）》请在冰豆网上搜索。

C9，-D2，-D3，D4〕〞这样的形式。

应用实例三：

与格人数统计

假设B1:

B50区域存放学生性别，C1:

C50单元格存放某班学生的考试成绩，要想统计考试成绩与格的女生人数。

可以使用公式“=SUM〔IF〔B1:

B50=〃女〃，IF〔C1:

C50>

=60，1，0〕〕〕〞，由于它是一个数组公式，输入完毕后必须按住Ctrl+Shift键回车。

公式两边会自动添加上大括号，在编辑栏显示为“{=SUM〔IF〔B1:

=60，1，0〕〕〕}〞，这是使用数组公式必不可少的步骤。

2．条件求和函数SUMIF

SUMIF〔range，criteria，sum_range〕。

range是用于条件判断的单元格区域，criteria是由数字、逻辑表达式等组成的判定条件，sum_range为需要求和的单元格、区域或引用。

应用实例：

与格平均分统计

假设A1:

A36单元格存放某班学生的考试成绩，假设要计算与格学生的平均分，可以使用公式“=SUMIF〔A1:

A36，〃>

=60〃，A1:

A36〕/COUNTIF〔A1:

=60〃〕。

公式中的“=SUMIF〔A1:

A36〕〞计算与格学生的总分，式中的“A1:

A36〞为提供逻辑判断依据的单元格引用，“>

=60〞为判断条件，不符合条件的数据不参与求和，A1:

A36如此是逻辑判断和求和的对象。

公式中的COUNTIF〔A1:

=60〃〕用来统计与格学生的人数。

3.逻辑函数IF

IF〔①logical_test，②value_if_true，③value_if_false〕。

logical_test是结果为true〔真〕或false〔假〕的数值或表达式；

value_if_true是logical_test为true时函数的返回值，如果logical_test为ture并且省略了value_if_true，如此返回true。

而且value_if_true可以是一个表达式；

value_if_false是logical_test为false时函数的返回值。

如果logical_test为false并且省略value_if_false，如此返回false。

Value_if_false也可以是一个表达式。

红字的内容为，当①为真时如此将2的值返回，如果为假设此将3的值返回。

2，3可以是表达式或值，还可以是if语句，这就是if嵌套。

个人收入调节税计算

假设个人收入调节税的稽征方法是：

工资在1000元以下的免征调节税，工资1000元以上至1500元的超过局部按5%的税率征收，1500元以上至2000元的超过局部按8%的税率征收，高于2000元的超过局部按30%的税率征收。

假设工作表的B列存放职工某某，C列存放工资，选中D列的空白单元格输入公式“=IF〔C2<

=1000，〃〃，IF〔〔C2-1000〕<

=1500，〔C2-1000〕*0.05，IF〔C2-1500〕<

=2000，〔C2-1500〕*0.08，IF〔C2>

2000，〔C2-2000〕*0.3〕〕〕〕〞，回车后即可计算出C2工资应缴纳的收入调节税。

公式中的IF语句是逐次计算的，如果第一个逻辑判断C2<

=1000成立，如此公式所在单元格被填入空格；

如果第一个逻辑判断式不成立，如此计算第二个IF语句；

直至计算完毕。

如果税率征收标准发生了变化，只须改变逻辑和计算式中的值，如1000、1500和2000等即可。

消除无意义的零

用SUM函数计算工资总额等问题时，假设引用的全部参数均为空白单元格，公式仍然会计算出一个“0〞。

这样打印出来的报表不仅很不美观。

为此可将计算公式设计为“=IF〔SUM〔A1:

B1，D2:

E8〕<

>

0，SUM〔A1:

E8〕，〃〃〕〞，即是当SUM〔A1:

E8〕计算结果不等于零时，公式所在单元格显示SUM〔A1:

E8〕的计算结果，否如此显示一个空格。

上面这个问题在财务计算中依然存在，如公式“=A1-A6-A9〞有可能为零或显示负数。

为了防止打印零或负数，可以设计公式“=IF〔A2-A6-A9=0，〃〃，IF〔A2-A6-A9<

0，RMB〔A2-A6-A9〕，A2-A6-A9〕〕〞。

当A2-A6-A9=0时在单元格中填写零，否如此进展下一个逻辑判断。

如果A2-A6-A9<

0如此在结果前添加一个“￥〞符号，否如此进展正常运算直至完毕。

多条件求和

假设C1:

C460区域内存放着职工的职称，D1:

D460区域存放着职工的性别。

如果要统计具有高级职称的男性职工总数，可以使用公式“=SUM〔IF〔C1:

C460=〃高级〃，IF〔D1:

D460=〃男〃，1，0〕〕〕〞。

这是一个数组公式，输入完毕后按住Ctrl+Shift回车即可计算出结果。

4.平均值函数AVERAGE

AVERAGE〔number1，number2，．．．〕。

number1、number2．．．是需要计算平均值的1～30个参数。

参数可以是数字、包含数字的名称、数组或引用。

数组或单元格引用中的文字、逻辑值或空白单元格将被忽略，但单元格中的零如此参与计算。

如果需要将参数中的零排除在外，如此要使用特殊设计的公式，下面的介绍。

跨表计算平均值

标签名为“一班〞、“二班〞和“三班〞的工作表存放各班学生的成绩，如此它们的总平均分计算公式为“=AVERAGE〔一班!

C1:

C36，三班!

C32，三班!

C45〕〞。

式中的引用输入方法与SUM跨表求和时一样。

跨表求值：

最前面写标签名后面接!

再后面接所要求值的区域。

忽略零的平均值计算公式

A200随机存放包括零在内的48个数值，在AVERAGE参数中去掉零引用很麻烦，这种情况可以使用公式“=AVERAGE〔IF〔A1:

A200<

0，A1:

A200，〃〃〕〞。

公式输入完毕后按住Ctrl+Shift回车，即可对A1:

A200中的数值〔不包括零〕计算平均值。

5．快捷方式或创建函数HYPERLINK

HYPERLINK〔link_location，friendly_name〕。

link_location是文件的路径和文件名，它可以指向文档中的某个具体位置。

如Excel工作表中的特定单元格或区域，或某个Word文档中的书签，也可以指向硬盘中的文件或是Internet或Intranet的URL。

Friendly_name为单元格中显示的文字或数字，它用蓝色显示并带有下划线。

如果省略了friendly_name，单元格就将link_location显示为。

管理文档或地址

如果你拥有大量文档或收集了许多地址，管理起来一定有困难。

如果能够将其中的文档名、URL等与文档等对象起来，不仅管理方便，还可以直接打开文档或访问站点。

具体操作方法是：

首先根据文档类型建立管理工作表，其中的文件名或名必须使用以下方法输入：

选中一个空白单元格，单击ExcelXP工具栏中的“粘贴函数〞按钮，打开“粘贴函数〞对话框，在“函数分类〞框下选中“查找与引用〞，然后在“函数名〞框内找到HYPERLINK函数。

单击对话框中的“确定〞按钮，弹出“HYPERLINK〞函数向导。

在“link_location〞框中输入文件的完整路径和名称〔包括扩展名〕，如“C:

＼mydocuments＼IT集锦.doc〞，然后在“friendly_name〞框中输入文件名〔如“IT集锦〞〕。

确认输入无误单击“确定〞按钮，所选单元格即出现带下划线的紫色文件名“IT集锦〞。

此后你就可以从工作表中打开文档，方法是：

打开工作表，在其中找到需要打开的文件。

箭头光标指向文件名会变成手形，假设停留片刻如此会显示该文件的完整路径和名称，单击如此会调用关联程序将文件打开。

如果你收集了许多的URL，还可以用此法建立一个大型“收藏夹〞。

既可以用Excel的强大功能进展管理，又可以从工作表中直接访问Web站点。

6．计数函数COUNT

COUNT〔value1，value2，．．．〕。

value1，value2．．．是包含或引用各类数据的1～30个参数。

COUNT函数计数时数字、日期或文本表示的数字会参与计数，错误值或其他无法转换成数字的文字被忽略。

如果参数是一个数组或引用，那么只有数组或引用中的数字参与计数；

其中的空白单元格、逻辑值、文字或错误值均被忽略。

与格率统计

G42存放着42名学生的考试成绩，在一个空白单元格内输入公式“=COUNTIF〔C1:

C42，〃>

=60〃〕/COUNTA〔C1:

C42〕〞回车，即可计算出该列成绩的与格率〔即分数为60与以上的人数占总人数的百分比〕。

7．条件计数函数COUNTIF

COUNTIF〔range，criteria〕。

range为需要统计的符合条件的单元格区域；

criteria为参与计算的单元格条件，其形式可以为数字、表达式或文本〔如36、〃>

160〃和〃男〃等〕。

条件中的数字可以直接写入，表达式和文本必须加引号。

男女职工人数统计

假设A1:

A58区域内存放着员工的性别，如此公式“=COUNTIF〔A1:

A58，〃女〃〕〞统计其中的女职工数量，“=COUNTIF〔A1:

A58，〃男〃〕〞统计其中的男职工数量。

COUNTIF函数还可以统计优秀或与格成绩的数量，假设C1：

G42存放着42名学生的考试成绩，如此公式“=COUNTIF〔C2：

G2，〃>

=85〃〕〞可以计算出其中高于等于85分的成绩数目。

如将公式改为“=COUNTIF〔C2：

=60〃〕〞，如此可以计算出与格分数的个数。

8．最大值函数MAX、最小值函数MIN

MAX〔number1，number2，．．．〕，MIN〔number1，number2，．．．〕。

number1，number2．．．是需要找出最大值〔最小值〕的1至30个数值、数组或引用。

函数中的参数可以是数字、空白单元格、逻辑值或数字的文本形式，如果参数是不能转换为数字的内容将导致错误。

如果参数为数组或引用，如此只有数组或引用中的数字参与计算，空白单元格、逻辑值或文本如此被忽略。

查询最高分〔最低分〕

假设C1：

G42存放着42名学生的考试成绩，如此选中一个空白单元格，在编辑栏输入公式“=MAX〔C1:

C42〕〞，回车后即可计算出其中的最高分是多少。

如果将上述公式中的函数名改为MIN，其他不变，就可以计算出C1：

G42区域中的最低分。

9排序函数RANK

RANK〔number，ref，order〕。

number是需要计算其排位的一个数字；

ref是包含一组数字的数组或引用〔其中的非数值型参数将被忽略〕；

order是用来说明排序方式的数字〔如果order为零或省略，如此以降序方式给出结果，反之按升序方式〕。

产值排序

假设图1中的E2、E3、E4单元格存放一季度的总产值。

（图1）

计算各车间产值排名的方法是：

在F2单元格内输入公式“=RANK〔E2，$E$2:

$E$4〕〞，敲回车即可计算出铸造车间的产值排名是2。

再将F2中的公式复制到剪贴板，选中F3、F4单元格按Ctrl+V，就能计算出其余两个车间的产值排名3和1。

如果B1单元格中输入的公式为“=RANK〔E2，$E$2:

$E$4，1〕〞，如此计算出的序数按升序方式排列，即2、1和3。

需要注意的是：

一样数值用RANK函数计算得到的序数〔名次〕一样，但会导致后续数字的序数空缺。

假设上例中F2单元格存放的数值与F3一样，如此按本法计算出的排名分别是3、3和1〔降序时〕。

即176.7出现两次时，铸造和维修车间的产值排名均为3，后续金工车间的排名就是1〔没有2〕。

10.测函数ISNUMBER

ISNUMBER〔value〕。

如果value为数值时函数返回ture〔真〕，否如此返回false〔假〕。

无错误除法公式如果工作表为C1设计了公式“=A1/B1〞，一旦B1单元格没有输入除数，就会在C1中显示错误信息“＃DIV／0！

〞。

这不仅看起来很不美观，一旦作为报表打印还可能引起误会。

为防止上面提到的问题出现，可将C1单元格中的公式设计成“=IF〔ISNUMBER〔B1〕，A1/B1，〃〃〕〞。

式中的ISNUMBER函数对B1单元格进展探测，当B1被填入数值时返回true〔真〕，反之返回false〔假〕。

为真时IF函数执行A1/B1的运算，为假时在C1单元格中填入空格。

使用SUM函数计算工资总额时，假设引用的单元格区域没有数据，Excel仍然会计算出一个结果“0〞。

这样打印出来的报表不符合财务规定，为此可将公式设计成“=IF〔ISNUMBER〔A1:

B1〕，SUM〔A1:

B1〕，〃〃〕〞。

式中ISNUMBER函数测试SUM函数所引用的单元格区域是否全部为空，当不为空时返回true〔真〕，反之返回fales〔假〕。

为真时IF函数执行SUM〔A1:

B1〕，为假时在存放计算结果的F1单元格中填入空格，这样即可防止在F1单元格中出现“0〞。

11、ISERROR函数函数名称：

ISERROR

主要功能：

用于测试函数式返回的数值是否有错。

如果有错，该函数返回TRUE，反之返回FALSE。

使用格式：

ISERROR（value）

参数说明：

Value表示需要测试的值或表达式。

应用举例：

输入公式：

=ISERROR（A35/B35），确认以后，如果B35单元格为空或“0〞，如此A35/B35出现错误，此时前述函数返回TRUE结果，反之返回FALSE。

特别提醒：

此函数通常与IF函数配套使用，如果将上述公式修改为：

=IF（ISERROR（A35/B35）,"

"

A35/B35），如果B35为空或“0〞，如此相应的单元格显示为空，反之显示A35/B35的结果。

12、FREQUENCY函数函数名称：

FREQUENCY

以一列垂直数组返回某个区域中数据的频率分布。

FREQUENCY（data_array,bins_array）

Data_array表示用来计算频率的一组数据或单元格区域；

Bins_array表示为前面数组进展分隔一列数值。

如图2所示，同时选中B32至B36单元格区域，输入公式：

=FREQUENCY（B2:

B31,D2:

D36），输入完成后按下“Ctrl+Shift+Enter〞组合键进展确认，即可求出B2至B31区域中，按D2至D36区域进展分隔的各段数值的出现频率数目〔相当于统计各分数段人数〕。

上述输入的是一个数组公式，输入完成后，需要通过按“Ctrl+Shift+Enter〞组合键进展确认，确认后公式两端出现一对大括号〔{}〕，此大括号不能直接输入。

13、INDEX函数函数名称：

INDEX

返回列表或数组中的元素值，此元素由行序号和列序号的索引值进展确定。

INDEX（array,row_num,column_num）

Array代表单元格区域或数组常量；

Row_num表示指定的行序号〔如果省略row_num，如此必须有column_num〕；

Column_num表示指定的列序号〔如果省略column_num，如此必须有row_num〕。

如图3所示，在F8单元格中输入公式：

=INDEX（A1:

D11,4,3），确认后如此显示出A1至D11单元格区域中，第4行和第3列交叉处的单元格〔即C4〕中的内容。

此处的行序号参数〔row_num〕和列序号参数〔column_num〕是相对于所引用的单元格区域而言的，不是Excel工作表中的行或列序号。

14、MATCH函数

函数名称：

MATCH

返回在指定方式下与指定数值匹配的数组中元素的相应位置。

MATCH（lookup_value,lookup_array,match_type）

Lookup_value代表需要在数据表中查找的数值；

Lookup_array表示可能包含所要查找的数值的连续单元格区域；

Match_type表示查找方式的值〔-1、0或1〕。

如果match_type为-1，查找大于或等于lookup_value的最小数值，Lookup_array必须按降序排列；

如果match_type为1，查找小于或等于lookup_value的最大数值，Lookup_array必须按升序排列；

如果match_type为0，查找等于lookup_value的第一个数值，Lookup_array可以按任何顺序排列；

如果省略match_type，如此默认为1。

如图4所示，在F2单元格中输入公式：

=MATCH（E2,B1:

B11,0），确认后如此返回查找的结果“9〞。

Lookup_array只能为一列或一行。

15、VLOOKUP函数函数名称：

VLOOKUP

在数据表的首列查找指定的数值，并由此返回数据表当前行中指定列处的数值。

VLOOKUP（lookup_value,table_array,col_index_num,range_lookup）

Lookup_value代表需要查找的数值；

Table_array代表需要在其中查找数据的单元格区域；

Col_index_num为在table_array区域中待返回的匹配值的列序号〔当Col_index_num为2时,返回table_array第2列中的数值，为3时，返回第3列的值……〕；

Range_lookup为一逻辑值，如果为TRUE或省略，如此返回近似匹配值，也就是说，如果找不到准确匹配值，如此返回小于lookup_value的最大数值；

如果为FALSE，如此返回准确匹配值，如果找不到，如此返回错误值#N/A。

参见图7，我们在D65单元格中输入公式：

=VLOOKUP（B65,B2:

D63,3,FALSE），确认后，只要在B65单元格中输入一个学生的某某〔如丁48〕，D65单元格中即刻显示出该学生的语言成绩。

Lookup_value参数必须在Table_array区域的首列中；

如果忽略Range_lookup参数，如此Table_array的首列必须进展排序；

在此函数的向导中，有关Range_lookup参数的用法是错误的。

16.RANDBETWEEN

用途：

产生位于两个指定数值之间的一个随机数，每次重新计算工作表（按F9键）都将返回新的数值。

RANDBETWEEN（bottom，top）

Bottom是RANDBETWEEN函数可能返回的最小随机数，Top是RANDBETWEEN函数可能返回的最大随机数。

该函数只有在加载了“分析工具库〞以后才能使用。

实例：

公式“=RANDBETWEEN（1000，9999）〞将返回一个大于等于1000、小于等于9999的随机数。

第二章：

财务函数

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

ACCRINT（issue，first_interest，settlement，rate，par，frequency，basis）

Issue为有价证券的发行日，First_interest是证券的起息日，Settlement是证券的成交日（即发行日之后证券卖给购置者的日期），Rate为有价证券的年息票利率，Par为有价证券的票面价值（如果省略par，函数ACCRINT将par看作$1000），Frequency为年付息次数（如果按年支付，frequency=1；

按半年期支付，frequency=2；

按季支付，frequency=4）。

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

ACCRINTM（issue，maturity，rate，par，basis）

Issue为有价证券的发行日，Maturity为有价证券的到期日，Rate为有价证券的年息票利率，Par为有价证券的票面价值，Basis为日计数基准类型（0或省略时为30/360，1为实际天数/实际天数，2为实际天数/360，3为实际天数/365，4为欧洲30/360）。

返回每个会计期间的折旧值。

AMORDEGRC（cost，date_purchased，first_period，salvage，period，rate，basis）

Cost为资产原值，Date_purchased为购入资产的

日期，First_period为第一个期间完毕时的日期，Salvage为资产在使用寿命完毕时的残值，Period是期间，Rate为折旧率，Basis是所使用的年基准（0或省略时为360天，1为实际天数，3为一年365天，4为一年360天）。

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

如果某项资产是在会计期间内购入的，如此按线性折旧法计算。

AMORLINC（cost，date_purchased，first_period，salvage，period，rate，basis）

Date_purchased为购入资产的日期，First_period

为第一个期间完毕时的日期，Salvage为资产在使用寿命完毕时的残值，Period为期间，Rate为折旧率，Basis为所使用的年基准（0或省略时为360天，1为实际天数，3为