Excel最常用函数.docx

1、Excel最常用函数1. SUM函数语法：SUM(numberl, number2,.)。参数：numberl、number2.为1到30个数值(包括逻辑值、文本表达式、数值、区域或引用)，各参数之间必须用逗号加以分隔。注意：参数中的数字、逻辑值及数字的文本表达式可以参与计算，其中逻辑值被转换为 1，文本则被转换为数字。如果参数为数组或引用，只有其中的数字参与计算，数组或引用中 的空白单元格、逻辑值、文本或错误值则被忽略。实例一：跨表/Sheet 求和(=SUM(Sheet2!C5,D5,Sheet1!B5)如果同一工作表中的其他区域需要参与计算， 可以单击“ number2框，再次选中工作表

2、中要计算的其他区域。上述操作完成后切换至第二个工作表，重复上述操作即可完成输入。“确定”后公式所在单元格将显示计算结果。实例二：SUM函数中的加减混合运算财务统计需要进行加减混合运算(其它运算不能操作)，例如扣除现金流量表中的若干支出 项目。按照规定，工作表中的这些项目没有输入负号。 这时可以构造“ =SUMB2:B6, C2:C9, -D2，-E2。”这样的公式。实例三：及格人数统计假如B1:B50区域存放学生性别，C1:C50单元格存放某班学生的考试成绩，要想统计考试成绩及格的女生人数。可以使用公式“ =SUMIF (B1:B50=女，IF(C1:C50=6Q1,0)”， 由于它是一个数组

3、公式,输入结束后必须按住Ctrl+Shift 键回车。公式两边会自动添加上 大括号，在编辑栏显示为 “=SUM(IF (B1:B50= 女，IF (C1:C50=6Q 1, 0) ” , 这是使用数组公式必不可少的步骤。2条件求和函数SUMIF语法：SUMIF（ range, criteria , sum_range）。参数：range是用于条件判断的单元格区域，criteria 是由数字、逻辑表达式等组成的判定条件，sum_range为需要求和的单元格、区域或引用。应用实例：及格平均分统计假如A1:A36单元格存放某班学生的考试成绩， 若要计算及格学生的平均分，可以使用公式“ =SUMIF（

4、A1:A36, =60，A1:A36）/COUNTIF（A1:A36, =60）。公式中的“ =SUMIF（A1:A36, =60，A1:A36）”计算及格学生的总分，式中的“ A1:A36”为提供逻辑判 断依据的单元格引用，“ =60为判断条件，不符合条件的数据不参与求和， A1:A36则是逻辑判断和求和的对象。公式中的 COUNTIRA1:A36, =60）用来统计及格学生的人数。3. 逻辑函数IF语法：IF （logical_test ，value_if_true ，value_if_false ）。参数：logical_test 是结果为true （真）或false （假）的数值或表达

5、式；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语句

6、，这就是if嵌套。应用实例一：个人收入调节税计算假设个人收入调节税的稽征办法是：工资在 1000元以下的免征调节税，工资1000元以上 至1500元的超过部分按5%勺税率征收，1500元以上至2000元的超过部分按8%勺税率征 收，高于2000元的超过部分按30%勺税率征收。假如工作表的B列存放职工姓名，C列存放工资，选中D列的空白单元格输入公式“ =IF(C2=1000 ，IF (C2-1000) =1500, ( C2-1000) * , IF (C2-1500) 2000 (C2-2000) *) ”，回车后即可计算出C2工资应缴纳的收入调节税。公式中的IF语句是逐次计算的，如果第一个逻

7、辑判断 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,

8、D2:E8)的计算结果，否则显示一个空格。上面这个问题在财务计算中依然存在， 如公式“ =A1-A6-A9有可能为零或显示负数。为了避免打印零或负数，可以设计公式“ =IF ( A2-A6-A9=0,， IF (A2-A6-A90, RMB(A2-A6-A9), A2-A6-A9) ”。当A2-A6-A9=0时在单元格中填写零，否则进行下一个逻辑 判断。如果A2-A6-A90则在结果前添加一个“Y”符号，否则进行正常运算直至结束应用实例三：多条件求和 假设C1:C460区域内存放着职工的职称，D1:D460区域存放着职工的性别。如果要统计具 有高级职称的男性职工总数，可以使用公式“ =SUMI

9、F( C1:C460=高级，IF( D1:D460= 男，1,0)”。这是一个数组公式，输入结束后按住Ctrl+Shift 回车即可计算出结果。4. 平均值函数 AVERAGE语法：AVERAGEnumber1，number2,.)。参数：number1、number2.是需要计算平均值的130个参数。注意：参数可以是数字、包含数字的名称、数组或引用。数组或单元格引用中的文字、逻 辑值或空白单元格将被忽略，但单元格中的零则参与计算。如果需要将参数中的零排除在 外，则要使用特殊设计的公式，下面的介绍。应用实例一：跨表计算平均值标签名为“ 一班”、“二班”和“三班”的工作表存放各班学生的成绩，则它

10、们的总平均分计算公式为“ =AVERAGE一班!C1:C36，三班!C1:C32，三班!C1:C45)”。式中的引用输入方法与SUM夸表求和时相同。跨表求值：最前面写标签名后面接！再后面接所要求值的区域。应用实例二：忽略零的平均值计算公式假设A1:A200随机存放包括零在内的48个数值，在AVERAG参数中去掉零引用很麻烦，这 种情况可以使用公式“ =AVERAGEIF (A1:A2000, A1:A200, )”。公式输入结束 后按住Ctrl+Shift 回车，即可对A1:A200中的数值(不包括零)计算平均值。5. 快捷方式或链接创建函数 HYPERLINK语法：HYPERLINKlink

11、_location , friendly_name ）。参数：link_location 是文件的路径和文件名，它可以指向文档中的某个具体位置。如Excel工作表中的特定单元格或区域， 或某个Word文档中的书签，也可以指向硬盘中的文件或是In ternet 或Intran et 的URL Frie ndly_name为单元格中显示的链接文字或数字，它用蓝 色显示并带有下划线。如果省略了 friendly_name，单元格就将link_location 显示为链接。应用实例：管理文档或网站地址如果你拥有大量文档或收集了许多网站地址，管理起来一定有困难。如果能够将其中的文 档名、URL等与文档等

12、对象链接起来，不仅管理方便，还可以直接打开文档或访问站点。具体操作方法是：首先根据文档类型建立管理工作表，其中的文件名或网站名必须使用以 下方法输入：选中一个空白单元格，单击Excel XP工具栏中的“粘贴函数”按钮，打开“粘 贴函数”对话框，在“函数分类”框下选中“查找与引用”，然后在“函数名”框内找到 HYPERLINK函数。单击对话框中的“确定”按钮，弹出“ HYPERLINK函数向导。在“link_location ”框中输入文件的完整路径和名称 （包括扩展名），如“C:mydocuments IT网站集锦.doc ”，然后在“ friendly_name ”框中输入文件名（如“ IT

13、网站集锦”）。 确认输入无误单击“确定”按钮，所选单元格即出现带下划线的紫色文件名“ IT 网站集锦”。此后你就可以从工作表中打开文档，方法是：打开工作表，在其中找到需要打开的文件。箭头光标指向文件名会变成手形，若停留片刻则会显示该文件的完整路径和名称，单击则 会调用关联程序将文件打开。Excel如果你收集了许多网站的 URL还可以用此法建立一个大型“收藏夹”。既可以用的强大功能进行管理，又可以从工作表中直接访问 Web站点。6. 计数函数COUNT语法：COUNTvaluel , value2参数：valuel , value2 .是包含或引用各类数据的130个参数。注意：COUNTS数计数

14、时数字、日期或文本表示的数字会参与计数，错误值或其他无法转 换成数字的文字被忽略。如果参数是一个数组或引用，那么只有数组或引用中的数字参与 计数；其中的空白单元格、逻辑值、文字或错误值均被忽略。应用实例：及格率统计假如C1:G42存放着42名学生的考试成绩，在一个空白单元格内输入公式“ =COUNTIF（C1:C42, =60）/COUNT（C1:C42） ”回车，即可计算出该列成绩的及格率（即分数为60及以上的人数占总人数的百分比）。7. 条件计数函数COUNTIF语法：COUNTIF（range，criteria ）。参数：range为需要统计的符合条件的单元格区域；criteria 为参

15、与计算的单元格条件，其形式可以为数字、表达式或文本（如 36、 160和男等）。条件中的数字可以直接写入，表达式和文本必须加引号。应用实例：男女职工人数统计假设A1:A58区域内存放着员工的性别，则公式“ =COUNTI（ A1:A58, 女。”统计其中的女职工数量，“ =COUNT（A1:A58, 男）”统计其中的男职工数量COUNTI函数还可以统计优秀或及格成绩的数量，假如 C1: G42存放着42名学生的考试成 绩，则公式“ =COUNTIFC2: G2 =85）”可以计算出其中高于等于85分的成绩数目。 如将公式改为“ =COUNTI（C2: G2 =60）”，则可以计算出及格分数的个

16、数。8. 最大值函数MAX最小值函数MIN语法：MAX（numberl, number2,. .），MIN （numberl, number2,.）。参数：number1，number2.是需要找出最大值（最小值）的1至30个数值、数组或引用。注意：函数中的参数可以是数字、空白单元格、逻辑值或数字的文本形式，如果参数是不能转换为数字的内容将导致错误。如果参数为数组或引用，则只有数组或引用中的数字参 与计算，空白单元格、逻辑值或文本则被忽略。应用实例：查询最高分（最低分）假如C1: G42存放着42名学生的考试成绩，则选中一个空白单元格，在编辑栏输入公式“ =MAX（ C1:C42）”，回车后即

17、可计算出其中的最高分是多少。如果将上述公式中的函数名改为 MIN,其他不变，就可以计算出 C1: G42区域中的最低分。9排序函数RANK语法：RANK（ number，ref， order ）。参数：number是需要计算其排位的一个数字；ref是包含一组数字的数组或引用（其中的 非数值型参数将被忽略）;order是用来说明排序方式的数字（如果 order为零或省略，则 以降序方式给出结果，反之按升序方式）。应用实例：产值排序 假如图1中的E2、E3 E4单元格存放一季度的总产值。（图1）计算各车间产值排名的方法是：在 F2单元格内输入公式“ =RAN（E2, $E$2:$E$4） ”，敲

18、回车即可计算出铸造车间的产值排名是 2。再将F2中的公式复制到剪贴板，选中 F3、F4 单元格按Ctrl+V，就能计算出其余两个车间的产值排名 3和1。如果B1单元格中输入的公 式为“ =RAN（E2, $E$2:$E$4, 1）”，则计算出的序数按升序方式排列，即 2、1和3。需要注意的是：相同数值用 RANK函数计算得到的序数（名次）相同，但会导致后续数字的序数空缺。假如上例中F2单元格存放的数值与F3相同，则按本法计算出的排名分别是 3、 3和1 （降序时）。即出现两次时，铸造和维修车间的产值排名均为 3,后续金工车间的排 名就是1 （没有2）。10. 测函数 ISNUMBER语法：IS

19、NUMBERvalue ）。参数：如果value为数值时函数返回ture （真），否则返回false （假）应用实例一：无错误除法公式如果工作表为C1设计了公式“ =A1/B1”，一旦B1单元格没有输入除数，就会在C1中显示错误信息“# DIV/0!”。这不仅看起来很不美观，一旦作为报表打印还可能引起误会。 为避免上面提到的问题出现，可将C1单元格中的公式设计成“=IF （ ISNUMBERB1），A1/B1,）”。式中的ISNUMBE函数对B1单元格进行探测，当B1被填入数值时返回true （真）,反之返回false （假）。为真时IF函数执行A1/B1的 运算，为假时在C1单元格中填入空格

20、。应用实例二：消除无意义的零使用SUM函数计算工资总额时，若引用的单元格区域没有数据， Excel仍然会计算出一个结果“0”。这样打印出来的报表不符合财务规定，为此可将公式设计成“ =IF( ISNUMBER(A1:B1)，SUM( A1:B1)，)”。式中ISNUMBE函数测试SUM函数所引用的单元格区域是否全部为空，当不为空时返回true (真)，反之返回fales (假)。为真时IF函数执行SUM(A1:B1)，为假时在存放计算结果 的F1单元格中填入空格，这样即可避免在 F1单元格中出现“ 0”。11、ISERRO函数函数名称：ISERROR主要功能：用于测试函数式返回的数值是否有错。

21、如果有错，该函数返回 TRUE反之返回FALSE使用格式：ISERROR(value)参数说明：Value表示需要测试的值或表达式。应用举例：输入公式：=ISERROR(A35/B35)确认以后，如果 B35单元格为空或“ 0”，贝U A35/B35出现错误，此时前述函数返回 TRUE结果，反之返回FALSE特别提醒：此函数通常与IF函数配套使用，如果将上述公式修改为： =IF(ISERROR(A35/B35),A35/B35)，如果B35为空或“ 0”，则相应的单元格显示为空，反之显示A35/B35的结果。12、FREQUENCY数函数名称：FREQUENCY主要功能：以一列垂直数组返回某个

22、区域中数据的频率分布。使用格式：FREQUENCY（data_array,bi ns_array）参数说明：Data_array表示用来计算频率的一组数据或单元格区域；Bins_array表示为前 面数组进行分隔一列数值。应用举例：如图2所示，同时选中B32至B36单元格区域，输入公式：=FREQUENCY（B2:B31,D2:D36）输入完成后按下“ Ctrl+Shift+Enter ”组合键进行确认，即可求出B2至B31区域中，按D2至D36区域进行分隔的各段数值的出现频率数目（相当于 统计各分数段人数）。特别提醒：上述输入的是一个数组公式，输入完成后，需要通过按“ Ctrl+Shift+

23、E nter ”组合键进行确认，确认后公式两端出现一对大括号（），此大括号不能直接输入。13、INDEX函数函数名称：INDEX主要功能：返回列表或数组中的元素值，此元素由行序号和列序号的索引值进行确定。使用格式：INDEX（array,row_ num ,colu mn_num）参数说明：Array代表单元格区域或数组常量； Row_num表示指定的行序号（如果省略row_num 贝U必须有 column_num） ； Column_nun表示指定的列序号（如果省略 column_num 则必须有row_num）。应用举例：如图3所示，在F8单元格中输入公式：=INDEX(A1:D11,4,

24、3)，确认后则显示出A1至D11单元格区域中，第4行和第3列交叉处的单元格(即C4)中的内容。特别提醒：此处的行序号参数(row_num)和列序号参数(column_num)是相对于所引用的 单元格区域而言的，不是 Excel工作表中的行或列序号。14、MATC函数函数名称：MATCH主要功能：返回在指定方式下与指定数值匹配的数组中元素的相应位置。使用格式：MATCH(lookup_value,lookup_array,match_type)参数说明：Lookup_value代表需要在数据表中查找的数值；Lookup_array表示可能包含所要查找的数值的连续单元格区域；Match_type表

25、示查找方式的值(-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

26、_array只能为一列或一行。15、VLOOKU函数函数名称：VLOOKUP主要功能：在数据表的首列查找指定的数值，并由此返回数据表当前行中指定列处的数值。使用格式：VLOOKUP(lookup_value,table_array,col_i ndex_ nu m,ra nge_lookup)参数说明：Lookup_value代表需要查找的数值；Table_array代表需要在其中查找数据的 单元格区域；Col_index_num为在table_array 区域中待返回的匹配值的列序号(当 Col_index_num为2时,返回table_array 第2列中的数值，为3时，返回第3列的值 )

27、;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的首

28、列必须进行排序；在此函数的向导中，有关 Rangeookup参数的用法是错误的用途：产生位于两个指定数值之间的一个随机数，每次重新计算工作表 （按F9键）都将返回新的数值。语法：RANDBETWEEN(bottontop)参数：Bottom 是RANDBETWEEN数可能返回的最小随机数， Top是RANDBETWEEN数可能返回的最大随机数注意：该函数只有在加载了“分析工具库”以后才能使用。实例：公式“ =RANDBETWEEN（1009999） ”将返回一个大于等于 1000、小于等于9999的随机数。第二章：财务函数用途：返回定期付息有价证券的应计利息。语法：ACCRINT（issue,

29、 firstnterest ， settlement ，rate， par，frequency， basis）参数：Issue为有价证券的发行日，Firstnterest 是证券的起息日，Settlement是证券的成交日（即发行日之后证券卖给购买者的日期），Rate为有价证券的年息票利率，Par 为有价证券的票面价值（如果省略par，函数ACCRINT将par看作$1000），Frequency为年 付息次数（如果按年支付，frequency = 1 ;按半年期支付，frequency = 2 ;按季支付， freque ncy = 4）。用途：返回到期一次性付息有价证券的应计利息。语法：A

30、CCRINTM(issue maturity , rate , par , basis)参数：Issue为有价证券的发行日，Maturity 为有价证券的到期日，Rate为有价证券的 年息票利率，Par为有价证券的票面价值，Basis为日计数基准类型（0或省略时为30/360, 1为实际天数/实际天数，2为实际天数/360，3为实际天数/365，4为欧洲30/360）。用途：返回每个会计期间的折旧值。语法：AMORDEGRC（cosdate_purchased，first_period ，salvage，period ，rate，basis）参数：Cost为资产原值，Date_purchase

31、d为购入资产的日期，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为一 年365天, 4为一年360天）。用途：返回当前付息期内截止到成交日的天数。语法：COUPDAYBS（settlemen,