Excel 函数基础文档格式.docx

Excel 函数基础文档格式.docx

《Excel 函数基础文档格式.docx》由会员分享，可在线阅读，更多相关《Excel 函数基础文档格式.docx（74页珍藏版）》请在冰豆网上搜索。

3、按组合Ctrl+Shift+Enter表示数组公式输入或修改完毕。

组合键的输入方法：

先用一只手按住Ctrl键和Shift键不放，然后用另一只手点击Enter键，最后松开Ctrl键和Shift键。

注意，不能单独清除一个单元格中的数组公式，必须删除数组公式所在的单元格区域中每一个单元格中的数据公式，清除方法是，先选定数组公式所在的区域，然后再清除。

第二节常用函数

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的对数。

（5）SIN函数

SIN（x）

计算x弧度的正弦函数值

（6）COS函数

COS（x）计算x弧度的余弦函数值

对于三角函数，其参数x的单位为弧度，如果参数x的单位是以360度的角度值，在计算时需要将角度值转换为弧度。

EXCEL中角度转换为弧度的基本公式为：

弧度＝角度*PI（）/180

（7）例题

=PI（）*3^2

作用为计算半径为3的圆的面积。

=3.5+EXP（-2）

作用为计算

=SIN（45*PI（）/180）

作用为计算45度的正弦函数值

2、SUM、AVERAGE、MIN、MAX函数

（1）SUM函数

SUM（参数）

求参数中所给数值数据之和

（2）AVERAGE函数

AVERAGE（参数）

求参数中所给数值数据的平均值

（3）MIN函数

MIN（参数）

求参数中所给数值数据的最小值

（4）MAX函数

MAX（参数）

求参数中所给数值数据的最大值

这几个函数中，参数可以1个，也可以是多个，参数之间不讲究顺序；

参数可以是以常量的形式给定，也可以是单元格引用或单元格区域引用，也可以其他函数的计算结果；

如果给定的参数中，包含了非数值数据，则非数值数据勿略不记。

表1：

SUM等函数计算示例

A

B

C

1

12

TRUE

1900-1-10

2

ABC

35

3

4

计算公式

计算结果

5

=SUM（A1:

C2）

57

6

=AVERAGE（A1:

19

7

=MIN（A1:

10

8

=MAX（A1:

在这个例子中，参数使用的都是单元格区域引用，函数SUM（A1:

C2）表示求A1:

C2区域中所有数值数据之和，这个区域中，仅有3个数值数据：

A1单元格中的12，C1单元格的1900-1-10（日期数据视为整数，自动转换为对应整数后再计算，1900-1-10对应整数10），C2单元格中的35，所以计算结果为57。

AVERAGE（A1:

C2中所有数值数据的平均值，从上面所述知道这个区域中有3个数值数据，总和为57，所以平均值为19。

因为1900-1-10对就的整数是10，所以这个区域的最小的数值数据为10。

3、SMALL、LARGE函数

（1）SMALL函数

SMALL（数据组，K）

求数据组中第K个最小值

（2）LARGE函数

LARGE（数据组，K）

求数据组中第K个最大值

数据组一般是以单元格区域引用给定一组数据所在的单元格区域；

数据组也可以以常量的形式给定，格式这：

{数据1，数据2，……，数据n}。

例：

表2：

LARGE、SMALL函数示例

D

E

F

姓名

哲学

英语

说明

陈曦阳

93

94

=LARGE（B2:

B17,1）

95

求哲学课第1名的成绩

李朝阳

75

B17,3）

求哲学课第3名的成绩

陈永

80

89

=SMALL（B2:

求哲学倒数第1名的成绩

李莉

69

78

=SMALL（C2:

C17,3）

72

求计算机课倒数第3名的成绩

张剑

86

李平国

79

刘竹

66

48

9

邓小飞

87

郭荣

85

11

封婷

88

90

刘国治

76

13

李午宴

14

江朗

15

张丽平

16

孟浩然

91

17

曹风

82

在本例中，LARGE（B3：

B17，3）得到数组B3：

B17中，第3名的分数，因为成绩中有2个95分，占据了第一名和第二名，所以第三名的分数就是93。

4、RANK函数

RANK（指定数据，数组，排序方式）

返回一个指定的数值数据在数组列表中的排位。

数字的排位是其大小与数组列表中其他值的大小顺序。

排序方式指的是按从大到小（降序，排序方式为0）或是从小到大（升序，排位方式为1）。

如果是使用降序排序方式，则第3个参数可以省略。

数组列表必须先存放在工作表的一个连续区域中，在应用该函数时，只能以单元格区域引用的形式给出该数组，不能使用常量数组。

指定数据可以是某个单元格中的数据，在应用该函数时可以使用该数据所在单元格的引用，也可以以常量的形式给定该数据。

如果指定的数据不是给定数组中的一员，则得不到该数据在该数组中的排位，这时excel将给出错误信息——#N/A。

表3：

RANK函数示例

成绩表

专业

平均成绩

排名

会计学

90.50

=RANK（C3,$C$3:

$C$18,0）

经济学

71.75

=RANK（C4,$C$3:

84.75

=RANK（C5,$C$3:

金融学

69.25

=RANK（C6,$C$3:

统计学

86.75

=RANK（C7,$C$3:

87.00

=RANK（C8,$C$3:

58.00

=RANK（C9,$C$3:

88.75

=RANK（C10,$C$3:

82.00

=RANK（C11,$C$3:

88.25

=RANK（C12,$C$3:

81.50

=RANK（C13,$C$3:

85.50

=RANK（C14,$C$3:

74.75

=RANK（C15,$C$3:

86.00

=RANK（C16,$C$3:

电子商务

=RANK（C17,$C$3:

18

83.25

=RANK（C18,$C$3:

其中，C3单元格中的计算公式=RANK（C3,$C$3:

$C$18,0）的含义是计算C3单元格中的数据（陈曦阳的平均成绩）在数组C3：

C18（平均成绩数组）中的排名。

在本例中，由于学生的成绩排名是按从高到低的顺序（降序），所以就该函数的第3个参数为0，当然也可以省略该参数。

由于每个学生的平均成绩依次排在C3：

C18单元格区域中，所以要计算第2个学生的平均成绩排名只需要将该公式中的C3改为C4即可，这可以利用excel相对引用在公式复制时自动变化的规律直接将上一行公式复制到下一行就可得到。

但排名的范围还应该是C3：

C18，为了使上一行的公式复制到下一行时，平均成绩数组的范围不发生变化，对平均成绩数组就必须使用单元格区域的绝对引用。

这样只需要输入计算第一个学生平均成绩排名的公式，其余学生的排名计算公式通过“复制”、“粘贴”操作就可得到，节省了公式输入，也能有效地减少输入错误。

从排序结果可以看出，有两个学生的平均成绩均是82分，排名并列第10，这样就没有第11名了。

5、COUNT、COUNTA、COUNTBLANK函数

（1）COUNT函数

COUNT（参数）

求参数所给数据中，有多少个数值数据

（2）COUNTA函数

COUNTA（参数）

求参数所给数据中，有多少个数据（参数一般是单元格区域引用，则是求有多少个单元格中有数据）

（3）COUNTBLANK函数

COUNTBLANK（参数）

参数一般是单元格区域引用，则是求有多少个单元格中没有数据。

表4：

COUNT、COUNTA和COUNTBLANK函数示例

数据表

#DIV/0!

2014-3-5

=COUNT（A2:

D3）

统计数据表（A2：

D3单元格区域）中有多少个数值数据

=COUNTA（A2:

D3单元格区域）中有多少个单元格有数据

=COUNTBLANK（A2:

D3单元格区域）中有多少个单元格没有数据

在本例中，单元格区域A2：

D3内有3个数值数据：

12，2014-3-5（日期认为是一个整数），35，所以COUNT（A2：

D3）的结果是3；

而ABC，#DIV/0！

，ABC虽然不是数值数据但也是数据，所以COUNTA（A2：

D3）的结果是6；

最后在该区域中有2个单元格没有数据，所以COUNTBLANK（A2：

D3）的结果是2。

6、COUNTIF函数

COUNTIF（要进行统计的单元格区域，条件）

统计给定单元格区域中，满足给定条件的数据的个数。

该函数中“条件”的格式为：

”关系运算符条件值”

条件示例

含义

"

<

60"

判断单元格的值是否小于60

=100"

判断单元格的值是否小于或等于100

>

=60"

判断单元格的值是否大于或等于60

100"

判断单元格的值是否等于100

教授"

判断单元格的值是否等于教授

注意：

（1）在判断是否相等时，关系运算符（=）必须省略。

（2）如果条件直接在公式中给出时，则要加半角的双引号。

（3）如果条件存放在一个单元格中，则条件不能加引号，且该函数在使用时，第2个参数就是存放条件的单元格的引用。

对学生成绩表做下列统计

（1）判断有多少个学生的平均成绩在80分以上

（2）统计各专业学生的人数

学生成绩表在工作表中的存放位置如下所示：

表5：

学生成绩表

第1学期成绩表

平均成绩排名

第1问判断条件

=80

在公式中直接给出判断条件，则计算公式为：

=COUNTIF（C3:

C18,”>

=80”）

假设判断条件存放在C19单元格，则计算公式为：

C18,C19）

将统计结果做成一个统计表，存放在工作表的下述区域中：

表6：

根据学生成绩统计各专业学生人数

29

人数

B列的计算公式

30

=COUNTIF（$B$3:

$B$18,A30）

31

$B$18,A31）

32

$B$18,A32）

33

$B$18,A33）

34

$B$18,A34）

合计

=SUM（B30:

B34）

因为条件是“=XXXX”专业，根据excel的要求，在设置这种类型的判断时，对于相等关系的判断（=），要省略关系运算符等号，将判断条件存放在单元格中时不能加引号，所以各个判断条件依次存放在A30：

A34单元格区域中，由于每个专业存放条件的单元格区域不同，所以在公式中用相对引用，而每个专业要使用的原始统计数据都是在B3：

B18区域，所以在计算公式中用绝对引用。

这样只需要输入第一个专业的统计公式，然后“复制”、“粘贴”即可得到其他专业的统计计算公式。

7、SUMIF函数

SUMIF（条件区域，条件，求和区域）

条件求和函数，如果条件区域中第k个单元格的值满足给定的条件，则将求和区域中对应的第k个单元格的数据进行累加。

注意，条件区域和求和区域均为一维数组，且2个区域的数据个数要相等，求和区域的数据必须是数据值数据。

根据商品销售表

（1）编制商品销售汇总表，统计每种商品的销售数量和销售金额。

（2）统计单价小于4.50元的商品的销售金额及单价大于等于4.50元的商品的销售金额及各自所占总销售金额的比例。

表7：

商品销售表

商品名称

单价

数量

销售金额

笔记本

4.50

9.00

毛巾

10.20

墨水

3.00

橡皮擦

0.40

解：

销售汇总表可以设计如下：

表8：

按商品种类统计的销售汇总表格式

销售销售汇总表

20

21

统计笔记本的销售数量就是统计“商品销售表”中销售的商品名称为“笔记本”的销售数量，所以条件区域为销售商品的名称所在的区域，即A3：

A12；

求和区域是销售商品的数量所在的区域，即C3：

C12；

条件为“笔记本”，注意，判断等于关系时，关系运算符等号（=）要省略。

所以统计笔记本的销售数量的计算公式为：

=SUMIF（A3:

A12,"

笔记本"

C3:

C12）

因为销售汇总表中，正好有商品名称列，且A17单元格中的商品名称为“笔记本”，所以，A17单元格中的数据正好就是SUMIF函数中的条件，因此，上述公式可以改写为：

笔记本销售数量汇总计算公式：

=SUMIF（A3:

A12,A17,C3:

其他商品的销售数量汇总计算公式中，条件区域和求和区域均相同，需要改变的是条件求和的条件，所以其他商品的销售数量汇总计算公式依次为：

毛巾销售数量汇总计算公式：

A12,A18,C3:

墨水销售数量汇总计算公式：

A12,A19,C3:

橡皮擦销售数量汇总计算公式：

A12,A20,C3:

可以看出，上述公式都非常相似，有变化的仅是SUMIF函数第2个参数（求和条件）且是有规律的变化，所以根据EXCEL的公式复制的特点，将这些公式为的条件区域和求和区域设置为绝对引用，而条件的引用设置为相对引用，如表9所示。

这样在输入计算公式时，仅输入销售数量汇总计算公式中的第1个计算公式，其余计算公式通过复制就可得到。

销售金额汇总计算公式，条件区域和条件均与销售数量汇总计算公式相同，变化是是求和区域。

因商品销售表中销售金额是在D列，所以公式中第3个参数为：

$D$3:

$D$12。

其具体的计算公式见表9。

最后的计算结果见表10。

表9：

销售数量、销售金额汇总计算公式

销售数量汇总计算公式

销售金额汇总计算公式

=SUMIF（$A$3:

$A$12,A17,$C$3:

$C$12）

$A$12,A17,$D$3:

$D$12）

$A$12,A18,$C$3:

$A$12,A18,$D$3:

$A$12,A19,$C$3:

$A$12,A19,$D$3:

$A$12,A20,$C$3:

$A$12,A20,$D$3:

=SUM（C3:

C12）

=SUM（D3:

D12）

表10：

按商品种类统计的销售汇总表

销售汇总表

31.50

51.10

（2）统计单价小于4.50元的商品的销售金额及单价大于等于4.50元的商品的销售金额及各自所占总销售金额的比例。

销售比例统计表可设计如下（见表11）：

表11：

按商品价格统计的销售汇总表格式

24

销售比例统计表

25

项目

百分比%

26

单价<

4.50

27

单价≧4.50

28