excel最常用函数个人整合版之呕心沥血之作.docx

excel最常用函数个人整合版之呕心沥血之作.docx

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

excel最常用函数个人整合版之呕心沥血之作

第一章成绩查询常用函数

1．求和函数SUM

语法：

SUM（number1，number2，．．．）。

参数：

number1、number2．．．为1到30个数值（包括逻辑值和文本表达式）、区域或引用，各参数之间必须用逗号加以分隔。

注：

number1可以是b1：

h3，这样的形式也可以是整个表格，并且number2也可以和number1一样，他们这间需要用逗号隔开，number2还可以是另一个表格的数据。

注意：

参数中的数字、逻辑值及数字的文本表达式可以参与计算，其中逻辑值被转换为1，文本则被转换为数字。

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

应用实例一：

跨表求和（高）

使用SUM函数在同一工作表中求和比较简单，如果需要对不同工作表的多个区域进行求和，可以采用以下方法：

选中ExcelXP“插入函数”对话框中的函数，“确定”后打开“函数参数”对话框。

切换至第一个工作表，鼠标单击“number1”框后选中需要求和的区域。

如果同一工作表中的其他区域需要参与计算，可以单击“number2”框，再次选中工作表中要计算的其他区域。

上述操作完成后切换至第二个工作表，重复上述操作即可完成输入。

“确定”后公式所在单元格将显示计算结果。

应用实例二：

SUM函数中的加减混合运算

财务统计需要进行加减混合运算，例如扣除现金流量表中的若干支出项目。

按照规定，工作表中的这些项目没有输入负号。

这时可以构造“=SUM（B2:

B6，C2:

C9，-D2，-E2）”这样的公式。

其中B2:

B6，C2:

C9引用是收入，而D2、E2为支出。

由于Excel不允许在单元格引用前面加负号，所以应在表示支出的单元格前加负号，这样即可计算出正确结果。

即使支出数据所在的单元格连续，也必须用逗号将它们逐个隔开，写成“=SUM（B2:

B6，C2:

C9，-D2，-D3，D4）”这样的形式。

应用实例三：

及格人数统计

假如B1:

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

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

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

B50=〃女〃，IF（C1:

C50>=60，1，0）））”，由于它是一个数组公式，输入结束后必须按住Ctrl+Shift键回车。

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

B50=〃女〃，IF（C1:

C50>=60，1，0）））}”，这是使用数组公式必不可少的步骤。

2．条件求和函数SUMIF

语法：

SUMIF（range，criteria，sum_range）。

参数：

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

应用实例：

及格平均分统计

假如A1:

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

A36，〃>=60〃，A1:

A36）/COUNTIF（A1:

A36，〃>=60〃）。

公式中的“=SUMIF（A1:

A36，〃>=60〃，A1:

A36）”计算及格学生的总分，式中的“A1:

A36”为提供逻辑判断依据的单元格引用，“>=60”为判断条件，不符合条件的数据不参与求和，A1:

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

公式中的COUNTIF（A1:

A36，〃>=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:

B1，D2:

E8），〃〃）”，即是当SUM（A1:

B1，D2:

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

B1，D2:

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，三班!

C1:

C32，三班!

C1:

C45）”。

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

跨表求值：

最前面写标签名后面接!

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

应用实例二：

忽略零的平均值计算公式

假设A1:

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函数计数时数字、日期或文本表示的数字会参与计数，错误值或其他无法转换成数字的文字被忽略。

如果参数是一个数组或引用，那么只有数组或引用中的数字参与计数；其中的空白单元格、逻辑值、文字或错误值均被忽略。

应用实例：

及格率统计

假如C1:

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：

G2，〃>=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的随机数。

第二章：

财务函数

1.ACCRINT

用途：

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

语法：

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）。

2.ACCRINTM

用途：

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

语法：

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

参数：

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

3.AMORDEGRC

用途：

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

语法：

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天）。

4.AMORLINC

用途：

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

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

语法：

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

参数：

Date_purchased为购入资产的日期，First_period

为第一个期间结束时的日期，Salvage为资产在使用寿命结束时的残值，Period为期间，Rate