Excel 函数基础.docx

1、Excel 函数基础Excel 函数基础第一节 函数的基本概念Excel函数的作用是根据特定的计算要求，Excel进行一系列特定操作后，得到一个或一组计算结果，得到的计算结果称为函数值。函数可应用于各种计算公式中。一、函数的基本格式函数的基本格式：函数名（参数）函数名由英文字母或单词或字母与单词的混合体组成，用于标识函数并在一定程度上说明功能的功能。例如：SUM（参数） 求和；AVERAGE（参数）求平均值；POWER（参数） 求乘方；RATE（参数） 求利率；SLN（参数） 用直线法求折旧额；HLOOKUP（参数）水平方向查找；VLOOKUP（参数）垂直方向查找；PI（） 求圆周率的值。函数

2、参数用于说明函数在执行的过程中所需要的数据、或数据所在的单元格区域，具体要求等，函数的参数是紧跟在函数名后且由一对圆括号括起来。例如：MATCH(27,A2:F2) 查找A2：F2区域中是否有值为27的单元格MATCH(27,A2:F2，1) 查找A2：F2区域中值为27或如果没有值为27的单元格，则找值小于27的最大值的单元格。EXCEL函数的参数比较灵活，可以以多种方式给定：（1）Excel函数的参数可以是常量，单元格引用，单元格区域引用，也可以是另一个函数的值。例如：=SUM(3,5,6) 参数为常量；=SUM(D35,F35) 参数为单元格引用；=SUM(D35:F35) 参数为单元格

3、区域引用；=SUM(AVERAGE(D34:F34),AVERAGE(D35,F35) 参数为其他函数为值（函数嵌套）。在函数嵌套的情况下，excel先计算出内层函数的值，再以内层函数的值作为外层函数的参数计算外层函数的值。（2）Excel中有一些特殊的函数，在使用时不需要参数，例如计算圆周率的函数PI( ) ，在这种情况下，函数名后面的圆括号必须要有，这样excel才知道这是一个函数。（3）有些函数对于参数的多少及顺序没有太多的要求，如SUM( )、AVERAGE( )等，可以有一个参数，也可以有多个参数，且哪个参数在前，哪个参数在后都没有关系。但在大多数函数对参数的个数、参数的顺序、参数的

4、含义等都有严格的规定，必须按规定的顺序、规定的格式正确地给定每一个参数，才能得到函数的正确结果。因此，在学习excel的函数时，要正确掌握excel各个函数的参数代表的含义，参数的顺序及参数的格式才能得到正确的计算结果。（4）有些函数的其中一些参数可以省略，例如，FV( )函数，在学习时应该掌握什么时候可以省略哪些参数，省略了的参数的默认值是多少。二、excel函数的分类根据函数的计算结果是一个或多个，可以分为单值函数和多值函数（也叫做数组函数）。一般的经常使用的函数都是单值函数，如sum( )函数。而有些函数的结果则是有多个计算结果，例如，计算逆矩阵的函数MINVERSE( )。对于单值函数

5、，其计算公式的输入和修改都比较简单，选定要输入或修改的单元格后，根据excel表达式的规则输入计算公式及函数，然后按回车键（Enter键）即可。包含多值函数的计算公式也叫做数组公式，其计算公式的输入和修改则有所不同。方法如下：1、根据函数及计算要求，选定要存放计算结果的单元格区域（一般是一个矩形区域），或选定要修改的数组公式所在的单元格区域；2、输入计算公式或修改计算公式；3、按组合Ctrl+Shift+Enter表示数组公式输入或修改完毕。组合键的输入方法：先用一只手按住Ctrl键和Shift键不放，然后用另一只手点击Enter键，最后松开Ctrl键和Shift键。注意，不能单独清除一个单元

6、格中的数组公式，必须删除数组公式所在的单元格区域中每一个单元格中的数据公式，清除方法是，先选定数组公式所在的区域，然后再清除。第二节 常用函数1、PI、EXP、LN等常用数值计算函数（1）PI函数格式：PI（）功能：计算圆周率的值PI（）函数是一个无参数的函数，在使用中，必须在函数名后加一对圆括号，说明这是一个函数。（2）EXP函数格式：EXP( x )功能：计算的值X可是一个常量，也可以将X的值先存放在一个单元格中，在函数中给出X的值所在单元格的引用即可。（3）LN函数格式：LN（x）功能：计算以自然数e为底的x的对数。（4）LOG函数格式：LOG（x，BASE）功能：计算以BASE为底的x

7、的对数。（5）SIN函数格式：SIN（x）功能：计算x弧度的正弦函数值（6）COS函数COS（x）计算x弧度的余弦函数值对于三角函数，其参数x的单位为弧度，如果参数x的单位是以360度的角度值，在计算时需要将角度值转换为弧度。EXCEL中角度转换为弧度的基本公式为：弧度角度*PI（）/180（7）例题=PI()*32作用为计算半径为3的圆的面积。=3.5+EXP(-2)作用为计算=SIN(45*PI()/180)作用为计算45度的正弦函数值2、SUM、AVERAGE、MIN、MAX函数（1）SUM函数格式：SUM（参数）功能：求参数中所给数值数据之和（2）AVERAGE函数格式：AVERAGE

8、（参数）功能：求参数中所给数值数据的平均值（3）MIN函数格式：MIN（参数）功能：求参数中所给数值数据的最小值（4）MAX函数格式：MAX（参数）功能：求参数中所给数值数据的最大值这几个函数中，参数可以1个，也可以是多个，参数之间不讲究顺序；参数可以是以常量的形式给定，也可以是单元格引用或单元格区域引用，也可以其他函数的计算结果；如果给定的参数中，包含了非数值数据，则非数值数据勿略不记。例如：表1：SUM等函数计算示例ABC112TRUE1900-1-102ABC3534计算公式计算结果5=SUM(A1:C2)576=AVERAGE(A1:C2)197=MIN(A1:C2)108=MAX(A

9、1:C2)35在这个例子中，参数使用的都是单元格区域引用，函数SUM（A1:C2）表示求A1:C2区域中所有数值数据之和，这个区域中，仅有3个数值数据：A1单元格中的12，C1单元格的1900-1-10（日期数据视为整数，自动转换为对应整数后再计算，1900-1-10对应整数10），C2单元格中的35，所以计算结果为57。AVERAGE（A1:C2）表示求A1:C2中所有数值数据的平均值，从上面所述知道这个区域中有3个数值数据，总和为57，所以平均值为19。因为1900-1-10对就的整数是10，所以这个区域的最小的数值数据为10。3、SMALL、LARGE函数（1）SMALL函数格式：SMA

10、LL（数据组，K）功能：求数据组中第K个最小值（2）LARGE函数格式：LARGE（数据组，K）功能：求数据组中第K个最大值数据组一般是以单元格区域引用给定一组数据所在的单元格区域；数据组也可以以常量的形式给定，格式这：数据1，数据2，数据n。例：表2：LARGE、SMALL函数示例ABCDEF1姓名哲学英语计算公式计算结果说明2陈曦阳9394=LARGE(B2:B17,1)95求哲学课第1名的成绩3李朝阳5775=LARGE(B2:B17,3)93求哲学课第3名的成绩4陈永8089=SMALL(B2:B17,1)57求哲学倒数第1名的成绩5李莉6978=SMALL(C2:C17,3)72求计

11、算机课倒数第3名的成绩6张剑95867李平国79868刘竹66489邓小飞879310郭荣858711封婷889012刘国治769013李午宴868714江朗756915张丽平957916孟浩然917217曹风8286在本例中，LARGE（B3：B17，3）得到数组B3：B17中，第3名的分数，因为成绩中有2个95分，占据了第一名和第二名，所以第三名的分数就是93。4、RANK函数格式：RANK（指定数据，数组，排序方式）功能：返回一个指定的数值数据在数组列表中的排位。数字的排位是其大小与数组列表中其他值的大小顺序。排序方式指的是按从大到小（降序，排序方式为0）或是从小到大（升序，排位方式为1

12、）。如果是使用降序排序方式，则第3个参数可以省略。数组列表必须先存放在工作表的一个连续区域中，在应用该函数时，只能以单元格区域引用的形式给出该数组，不能使用常量数组。指定数据可以是某个单元格中的数据，在应用该函数时可以使用该数据所在单元格的引用，也可以以常量的形式给定该数据。如果指定的数据不是给定数组中的一员，则得不到该数据在该数组中的排位，这时excel将给出错误信息#N/A。例：表3：RANK函数示例ABCDE1成绩表2姓名专业平均成绩平均成绩排 名计算公式3陈曦阳会计学90.50 1=RANK(C3,$C$3:$C$18,0)4李朝阳经济学71.75 14=RANK(C4,$C$3:$C

13、$18,0)5陈永会计学84.75 8=RANK(C5,$C$3:$C$18,0)6李莉金融学69.25 15=RANK(C6,$C$3:$C$18,0)7张剑统计学86.75 5=RANK(C7,$C$3:$C$18,0)8李平国会计学87.00 4=RANK(C8,$C$3:$C$18,0)9刘竹会计学58.00 16=RANK(C9,$C$3:$C$18,0)10邓小飞会计学88.75 2=RANK(C10,$C$3:$C$18,0)11郭荣会计学82.00 10=RANK(C11,$C$3:$C$18,0)12封婷经济学88.25 3=RANK(C12,$C$3:$C$18,0)13刘

14、国治经济学81.50 12=RANK(C13,$C$3:$C$18,0)14李午宴经济学85.50 7=RANK(C14,$C$3:$C$18,0)15江朗金融学74.75 13=RANK(C15,$C$3:$C$18,0)16张丽平金融学86.00 6=RANK(C16,$C$3:$C$18,0)17孟浩然电子商务82.00 10=RANK(C17,$C$3:$C$18,0)18曹风金融学83.25 9=RANK(C18,$C$3:$C$18,0)其中，C3单元格中的计算公式 =RANK(C3,$C$3:$C$18,0)的含义是计算C3单元格中的数据（陈曦阳的平均成绩）在数组C3：C18（平

15、均成绩数组）中的排名。在本例中，由于学生的成绩排名是按从高到低的顺序（降序），所以就该函数的第3个参数为0，当然也可以省略该参数。由于每个学生的平均成绩依次排在C3：C18单元格区域中，所以要计算第2个学生的平均成绩排名只需要将该公式中的C3改为C4即可，这可以利用excel相对引用在公式复制时自动变化的规律直接将上一行公式复制到下一行就可得到。但排名的范围还应该是C3：C18，为了使上一行的公式复制到下一行时，平均成绩数组的范围不发生变化，对平均成绩数组就必须使用单元格区域的绝对引用。这样只需要输入计算第一个学生平均成绩排名的公式，其余学生的排名计算公式通过“复制”、“粘贴”操作就可得到，节

16、省了公式输入，也能有效地减少输入错误。从排序结果可以看出，有两个学生的平均成绩均是82分，排名并列第10，这样就没有第11名了。5、COUNT、COUNTA、COUNTBLANK函数（1）COUNT函数格式：COUNT（参数）功能：求参数所给数据中，有多少个数值数据（2）COUNTA函数格式：COUNTA（参数）功能：求参数所给数据中，有多少个数据（参数一般是单元格区域引用，则是求有多少个单元格中有数据）（3）COUNTBLANK函数格式：COUNTBLANK（参数）功能：参数一般是单元格区域引用，则是求有多少个单元格中没有数据。例：表4：COUNT、COUNTA和COUNTBLANK函数示例

17、ABCD1数据表212TRUEABC#DIV/0!32014-3-53545计算公式计算结果说明6=COUNT(A2:D3)3统计数据表（A2：D3单元格区域）中有多少个数值数据7=COUNTA(A2:D3)6统计数据表（A2：D3单元格区域）中有多少个单元格有数据8=COUNTBLANK(A2:D3)2统计数据表（A2：D3单元格区域）中有多少个单元格没有数据在本例中，单元格区域A2：D3内有3个数值数据：12，2014-3-5（日期认为是一个整数），35，所以COUNT（A2：D3）的结果是3；而ABC，#DIV/0！，ABC虽然不是数值数据但也是数据，所以COUNTA（A2：D3）的结果

18、是6；最后在该区域中有2个单元格没有数据，所以COUNTBLANK（A2：D3）的结果是2。6、COUNTIF函数格式：COUNTIF（要进行统计的单元格区域，条件）功能：统计给定单元格区域中，满足给定条件的数据的个数。该函数中“条件”的格式为：”关系运算符 条件值”条件示例含义60判断单元格的值是否小于60=60判断单元格的值是否大于或等于60100判断单元格的值是否等于100教授判断单元格的值是否等于教授注意：（1）在判断是否相等时，关系运算符（=）必须省略。（2）如果条件直接在公式中给出时，则要加半角的双引号。（3）如果条件存放在一个单元格中，则条件不能加引号，且该函数在使用时，第2个参

19、数就是存放条件的单元格的引用。例：对学生成绩表做下列统计（1）判断有多少个学生的平均成绩在80分以上（2）统计各专业学生的人数学生成绩表在工作表中的存放位置如下所示：表5：学生成绩表ABCD1第1学期成绩表2姓名专业平均成绩平均成绩排名3陈曦阳会计学90.50 14李朝阳经济学71.75 145陈永会计学84.75 86李莉金融学69.25 157张剑统计学86.75 58李平国会计学87.00 49刘竹会计学58.00 1610邓小飞会计学88.75 211郭荣会计学82.00 1012封婷经济学88.25 313刘国治经济学81.50 1214李午宴经济学85.50 715江朗金融学74.

20、75 1316张丽平金融学86.00 617孟浩然电子商务82.00 1018曹风金融学83.25 919第1问判断条件=80（1）判断有多少个学生的平均成绩在80分以上在公式中直接给出判断条件，则计算公式为：=COUNTIF(C3:C18,”=80”)假设判断条件存放在C19单元格，则计算公式为：=COUNTIF(C3:C18,C19)（2）统计各专业学生的人数将统计结果做成一个统计表，存放在工作表的下述区域中：表6：根据学生成绩统计各专业学生人数ABC29专业人数B列的计算公式30经济学4=COUNTIF($B$3:$B$18,A30)31会计学6=COUNTIF($B$3:$B$18,A

21、31)32金融学4=COUNTIF($B$3:$B$18,A32)33电子商务1=COUNTIF($B$3:$B$18,A33)34统计学1=COUNTIF($B$3:$B$18,A34)35合计16=SUM(B30:B34)因为条件是“=XXXX”专业，根据excel的要求，在设置这种类型的判断时，对于相等关系的判断（=），要省略关系运算符等号，将判断条件存放在单元格中时不能加引号，所以各个判断条件依次存放在A30：A34单元格区域中，由于每个专业存放条件的单元格区域不同，所以在公式中用相对引用，而每个专业要使用的原始统计数据都是在B3：B18区域，所以在计算公式中用绝对引用。这样只需要输入

22、第一个专业的统计公式，然后“复制”、“粘贴”即可得到其他专业的统计计算公式。7、SUMIF函数格式： SUMIF（条件区域，条件，求和区域）功能：条件求和函数，如果条件区域中第k个单元格的值满足给定的条件，则将求和区域中对应的第k个单元格的数据进行累加。注意，条件区域和求和区域均为一维数组，且2个区域的数据个数要相等，求和区域的数据必须是数据值数据。例：根据商品销售表（1）编制商品销售汇总表，统计每种商品的销售数量和销售金额。（2）统计单价小于4.50元的商品的销售金额及单价大于等于4.50元的商品的销售金额及各自所占总销售金额的比例。表7：商品销售表ABCD1商品销售表2商品名称单价数量销售

23、金额3笔记本4.50 29.00 4毛巾10.20 110.20 5笔记本4.50 14.50 6笔记本4.50 14.50 7墨水3.00 13.00 8笔记本4.50 29.00 9橡皮擦0.40 10.40 10墨水3.00 13.00 11笔记本4.50 14.50 12墨水3.00 13.00 解：（1）编制商品销售汇总表，统计每种商品的销售数量和销售金额。销售汇总表可以设计如下：表8：按商品种类统计的销售汇总表格式ABC15销售销售汇总表16商品名称数量销售金额17笔记本18毛巾19墨水20橡皮擦21合计统计笔记本的销售数量就是统计“商品销售表”中销售的商品名称为“笔记本”的销售数

24、量，所以条件区域为销售商品的名称所在的区域，即A3：A12；求和区域是销售商品的数量所在的区域，即C3：C12；条件为“笔记本”，注意，判断等于关系时，关系运算符等号（=）要省略。所以统计笔记本的销售数量的计算公式为：=SUMIF ( A3:A12 , 笔记本 , C3:C12 )因为销售汇总表中，正好有商品名称列，且A17单元格中的商品名称为“笔记本”，所以，A17单元格中的数据正好就是SUMIF函数中的条件，因此，上述公式可以改写为：笔记本销售数量汇总计算公式：=SUMIF（A3:A12 , A17, C3:C12 ）其他商品的销售数量汇总计算公式中，条件区域和求和区域均相同，需要改变的是

25、条件求和的条件，所以其他商品的销售数量汇总计算公式依次为：毛 巾销售数量汇总计算公式：=SUMIF（A3:A12 , A18, C3:C12 ）墨 水销售数量汇总计算公式：=SUMIF（A3:A12 , A19, C3:C12 ）橡皮擦销售数量汇总计算公式：=SUMIF（A3:A12 , A20, C3:C12 ）可以看出，上述公式都非常相似，有变化的仅是SUMIF函数第2个参数（求和条件）且是有规律的变化，所以根据EXCEL的公式复制的特点，将这些公式为的条件区域和求和区域设置为绝对引用，而条件的引用设置为相对引用，如表9所示。这样在输入计算公式时，仅输入销售数量汇总计算公式中的第1个计算公

26、式，其余计算公式通过复制就可得到。销售金额汇总计算公式，条件区域和条件均与销售数量汇总计算公式相同，变化是是求和区域。因商品销售表中销售金额是在D列，所以公式中第3个参数为：$D$3:$D$12。其具体的计算公式见表9。最后的计算结果见表10。表9：销售数量、销售金额汇总计算公式商品名称销售数量汇总计算公式销售金额汇总计算公式笔记本=SUMIF($A$3:$A$12,A17,$C$3:$C$12 )=SUMIF($A$3:$A$12,A17,$D$3:$D$12 )毛巾=SUMIF($A$3:$A$12,A18,$C$3:$C$12 )=SUMIF($A$3:$A$12,A18,$D$3:$D

27、$12 )墨水=SUMIF($A$3:$A$12,A19,$C$3:$C$12 )=SUMIF($A$3:$A$12,A19,$D$3:$D$12 )橡皮擦=SUMIF($A$3:$A$12,A20,$C$3:$C$12 )=SUMIF($A$3:$A$12,A20,$D$3:$D$12 )合计=SUM(C3:C12)=SUM(D3:D12)表10：按商品种类统计的销售汇总表ABC15销售汇总表16商品名称数量销售金额17笔记本731.50 18毛巾110.20 19墨水39.00 20橡皮擦10.40 21合计1251.10 （2）统计单价小于4.50元的商品的销售金额及单价大于等于4.50元的商品的销售金额及各自所占总销售金额的比例。销售比例统计表可设计如下（见表11）：表11：按商品价格统计的销售汇总表格式ABC24销售比例统计表25项目销售金额百分比%26单价4.5027单价4.5028