全国计算机等级考试一级中的Excel函数.docx

全国计算机等级考试一级中的Excel函数.docx

《全国计算机等级考试一级中的Excel函数.docx》由会员分享，可在线阅读，更多相关《全国计算机等级考试一级中的Excel函数.docx（20页珍藏版）》请在冰豆网上搜索。

全国计算机等级考试一级中的Excel函数

全国计算机等级考试一级中的Excel函数

一级考试中的Excel函数

一、基本概念

1.什么是函数

按照大多数人的理解，Excel函数即是“预先定义，执行计算、分析等处理数据任务的特殊公式”。

以常用的求和函数SUM为例，它的语法是:

SUM（number1,number2,......）

这个函数的功能是求number1、number2等括号里面各参数之和。

如SUM（1,2,3,4）就表示求参数1、2、3、4之和。

当然，我们也可以自己通过计算器或者口算来得出结果，但函数提供了一个更便捷的方式，这就好像我们把一叠纸装订起来，我们可以用线缝，用胶水粘，当然也可以用订书机“咔嚓”一下解决问题，函数往往就像这个订书机，是我们计算和统计的好工具～

（1）函数的组成

一个函数由:

函数名、一对括号组成。

括号里面:

各个参数、逗号（分隔参数之用）。

函数名是函数的主体，一个函数只有唯一的一个名称，它决定了函数的功能和用途。

如SUM

是求和函数，MAX是求最大值函数等。

函数名后面必须有一对括号。

注意:

这对逗号是英文状态下的扩号。

括号内用逗号分隔的内容称为“参数”。

注意:

这个逗号是英文状态下的逗号。

2.函数的参数

看看一下几个函数:

SUM（1,2,3,4）

SUM（5）

SUM（x,y,z）

SUM（A1,B1,C1）

SUM（A1，5,B1,6,C1×D1）

参数可以是常量，如“1、2、3、4、5等”或是文本等;

也可以是变量，如未知数“x、y、z、a、b、c”等;

也可以是单元格引用，如“A1、B1、C1”等;

也可以是一个表达式，参数还可以是逻辑值（例如TRUE或FALSE）、数组、错误值（例如#N/A）或单元格引用（例如E1:

H1），甚至可以是另一个或几个函数等。

参数的类型和位置必须满足函数语法的要求。

这里我们最常用的参数就是“常量”和“单元格引用”。

如何输入和使用函数呢,在Excel中，函数一般是在公式中使用的。

（1）什么是公式

公式的一般形式是:

<表达式>

表达式可以是算术表达式、关系表达式和字符串表达式。

表达式可以由运算符、常量、单元格地址、函数及括号组成。

但不能含有空格。

特别要注意的是:

公式中必须有“,”号。

一级考试中的Excel函数,1，2，3，4

F1+F2+F3

=SUM（A1,B1,C1）

以上都是公式。

（2）函数和公式

函数和公式有什么联系呢,

函数可以是公式的一部分。

一般情况下，函数前面加上“,”号就是一个公式。

如“SUM（A1,B1,C1）”

是一个函数，加上“,”号，变成“=SUM（A1,B1,C1）”就是一个公式了。

（3）复制公式

公式本身就比较繁琐，有时要一一输入公式是很麻烦的，这里，我们可以对公式进行复制来加快我们

的操作。

在复制公式时，要特别注意一个问题:

相对地址和绝对地址。

如下图中，D2单元格中已经输入公式“=（C2-B2）/C2”，下面还需要

向D3单元格中输入“=（C3-B3）/C3”;

向D4单元格中输入“=（C4-B4）/C4”;

一遍一遍输入很繁琐，我们这里开始复制。

选定D2单元格，按住鼠标左键拖动单元格右下角的填充句柄。

一直拉到D4单元格后松开鼠标左键。

一级考试中的Excel函数

这时，我们发现D3和D4单元格中已经填入了公式，且都比较正确。

D2中的公式“=（C2-B2）/C2”复制到了D3单元格变成“=（C3-B3）/C3”，列号未变，行号均加了1。

D2中的公式“=（C2-B2）/C2”复制到了D4单元格变成“=（C4-B4）/C4”，列号未变，行号均加了2。

原来，D2中的公式的C2、B2都是绝对地址，其行号或列号会随着所在单元格的行列号变化而对等变化的额。

例如，如果将D2中的公式复制到D10中去，则公式中的C2会变成C10，B2也会变成B10。

那什么是绝对地址呢。

如下图中，要求计算各分公司销售额所占总计的比例。

这里先输入C3中的公式:

“B3/B9”。

然后复制公式到C4中，发现公式复制错误。

C4中的公式变成了“=B4/B10”。

根据前面我们了解的相对地址的知识，我们不难了解:

C3公式中的B3、B9都是相对地址，其所在地由C3变成了C4，行号增加了，则公式中各相对地址的行号都会增加1。

B3变成B4是正确的，B9变成B10就错了，我们看看B10单元格在哪里，完全是一个空白单元格。

原来，C3的公式中，B4我们需要复制过去后让它变化，而B9是一个固定的单元格，不能变，这里

一级考试中的Excel函数

就应该把B9变成一个固定的单元格，即绝对地址。

将B9改为$B$9就成了一个绝对地址了。

$相当于一把锁，$B$9是加了两把锁:

$B表示列号被锁定，$9表示行号被锁定了。

这样，我们把C3的公式改为:

“B3/$B$9”,然后复制到C4:

C8单元格中去。

二、使用函数

输入函数的方法有两种:

一是直接在要产生结果的单元格中输入公式，公式中包括函数名、括号、参数、逗号等。

二是通过“函数向导”的方式来输入函数。

如下图中，要求求每个考生的3项成绩之和。

（1）直接输入函数

这里，我们先计算学号为T1的学生总成绩。

首先，选定要产生结果的单元格。

这个单元格要存放函数，并存放和显示函数计算的结果。

这里我们首先选定E3单元格。

再单击E3单元格，使其进入编辑状态，直接输入或在编辑栏中输入“,SUM（）”。

然后，将光标置入括号内，再在括号中输入参数，这里可以直接输入“B3,C3,D3”，还可以用鼠标直

一级考试中的Excel函数

接单击用引用的单元格B3，然后按住Ctrl键，一一单击其他参数:

C3、D3。

最后，单击Entet键完成公式的输入。

这时，我们发现在E3中显示出求和的结果为“238”。

但在编辑栏区显示的却是该单元格中的实际内容——公式“,SUM（B3,C3,D3）”。

有的同学要问了，刚才我们一直在说函数，怎么这里出现了公式,另外，刚才介绍函数时，没说要加“,”号呀,

这里需要解释一下:

我们刚刚在E3单元格中输入的:

SUM（B3,C3,D3）

这并不是一个函数，而是一个公式。

这个公式由“,”号和函数“SUM（B3,C3,D3）”组成。

我们在单元格输入的都必须是公式，而任何公式都必须有一个“,”号。

当然，公式的表达方法也有多种方式，如,SUM（B3:

D3）

此公式中的SUM只有一个参数，就是B3:

D3，表示B3和D3间所有单元格（B3、C3、D3）。

其一个参数的效果和3个参数的最终效果是一样的。

当然，公式也可以不用函数而达到同样的效果，如:

B3+C3+D3

这里没用到函数，而是一个加法表达式。

（2）通过“函数向导”方式输入函数

选定E3单元格，单击“插入函数”按钮。

打开“插入函数”对话框。

在“选择函数”中选择“SUM”。

一级考试中的Excel函数

打开“函数参数”对话框，这里系统已经自动输入了一个参数“B3:

D3”，如果我们需要修改，可以原参数上直接修改，也可以单击输入框右侧的按钮。

这时，进入“函数参数”选择界面，我们可以用鼠标直接在工作表上选定一个范围，作为参数。

这里我们可以选择B3:

D3。

单击Enter键返回到“函数参数”界面。

我们可以对其他参数一一修改或通过按钮，在工作表上直接取值。

最后单击“确定”按钮完成函数的设置。

这时，我们发现，工作表E3单元格中已经输入了函数，并且自动加上了“,”号，即输入了一个公式。

三、必考函数

1.SUM求和函数

【格式】SUM（参数1，参数2，参数3„„）

【功能】求各参数之和。

2.AVERAGE求平均值函数

【格式】AVERAGE（参数1，参数2，参数3„„）

【功能】求各参数的平均值。

3.ABS求绝对值函数

一级考试中的Excel函数

【格式】ABS（参数）

【功能】求参数的绝对值。

【举例】函数“ABS（-5）”的结果是5。

4.MAX求最大值函数

【格式】MAX（参数1，参数2，参数3„„）

【功能】求各参数中的最大值。

【举例】函数“MAX（2,3,1,5,6）的结果是6。

5.MIN求最小值函数

【格式】MIN（参数1，参数2，参数3„„）

【功能】求各参数中的最小值。

【举例】函数“MIN（2,3,1,5,6）的结果是1。

6.RANK排名函数

【格式】RANK（参数1，参数2，参数3）

【功能】求参数1在参数2这组数字中的排名数。

参数3为0或没有，则按降序排列;非零值，则按

升序排。

【说明】参数1为指定的一个数或一个单元格，参数2为一组数或一组单元格（参数1也是这一组中

的一员），参数3是设置升序或降序。

所谓升序，就是最小的数排名为1;降序，就是最大的数排名为1。

【举例】求各学生总成绩排名。

我们首先计算学号为T1学生的排名，选定F3，启动“函数向导”，打开“插入函数”对话框。

在“搜

索函数”框中输入“RANK”，按Enter键。

在“选择函数”中选定“RANK”后单击“确定”按钮。

一级考试中的Excel函数

打开“函数参数”对话框，下面开始设置RANK函数的参数:

第1个参数:

就像参加一个比赛，首先我们要明确参加排名对比的“选手”是什么谁?

是学号,还是高等数学的成绩，还是大学语文的成绩，大学物理的成绩,仔细考虑好了，原来是学号为T1的“总成绩”，即单元格“E3”。

第2个参数:

参数2是确定所有“选手”。

这里参加对比的是应该是学号T1到T10的10个人的“总成绩”，即单元格“E3:

E12”。

现在明白了吧，RANK函数就是将参数1这个选手，放在参数2这个选手群中去对比，从而得出参数1这个选手的座次号（第*名）。

第3个参数:

所谓排序存在两种情况，如“1,9”9个数字，可以排成“1、2、3、4、5、6、7、8、9”，也可以排成“9、8、7、6、5、4、3、2、1”。

第一种排序为升序，数字越来越大，从排名看，数字“1”排名第一位，是老大;第二种排序为降序，数字越来越小，数字“9”是排第一，而数字“1”的排名是第9。

RANK函数的第3个参数就是设置降序或升序的。

这里输入“0”或什么都不输入就表示是降序，如果输入非零的任意数字，则按升序排序。

考虑成熟后，我们在3个参数中按下图输入数据。

单击“确定”按钮，看看计算的结果，如下图所示，学号为T1的总成绩排名为“4”，即第4名。

同理，在F4:

F12中分别输入公式，可以得到最终的排名。

注意:

这里如果复制公式的话。

公式“,RANK（E3,E3:

E12,0）”中，参数1中的E3应设为相对地址，参数2中的E3:

E12应改为绝对地址$E$3:

$E$12，否则复制过去的公式会是错误的。

一级考试中的Excel函数

7.IF条件函数

【格式】IF（参数1，参数2，参数3）

【功能】首先看参数1的条件是否满足。

如果满足，则转到参数2;如果不满足则转到参数3。

【说明】参数1为任意一个可判断TRUE或FALSE的数值或表达式，参数1为TRUE时参数2有效，为FALSE时，参数3有效。

【举例1】如果某学生的总成绩在200分以上，则在“奖金”列上显示“100”（表示给予奖金100元）。

首先，我们来理顺这其中的逻辑关系，以学号T1的学生为例:

如果其“总成绩>200”为真，则在奖金列（G3）显示“1000”;

如果其“总成绩>200”为假，则在奖金列什么都不显示;

这样，我们就清楚了:

“总成绩>200”就是那个条件，就是参数1;

奖金列（G3）为1000，就是参数2;

参数3是空白。

这里不能不填内容，否则会出现FALSE的字样。

这里输入""表示一个空字符串。

考虑成熟后，我们在3个参数中按下图输入数据。

单击“确定”按钮，看看计算的结果，如下图所示，学号为T1的总成绩超过200分，奖金为1000元。

同理，在G4:

G12中分别输入公式，可以得到最终的结果。

我们看看，T3的总成绩为198分，没有超过200分，其奖金列就没有任何显示。

一级考试中的Excel函数

8.COUNT计数函数

【格式】COUNT（参数1，参数2，参数3„„）

【功能】求各参数中包含数的单元格之和。

【说明】参数如果是单元格地址，只计算包含数字的单元格。

【举例】在G3中显示下图中的工作表中有多少个包含数字的单元格（注意:

数字+字母不算数字）。

直接在G3中输入“,COUNT（A2:

F12）”即可，可统计出是50组数字。

9.COUNTIF按条件计算数字的函数

一级考试中的Excel函数

【格式】COUNTIF（参数1，参数2）

【功能】求参数1区域中，负责参数2条件的数值型数据的个数之和。

【说明】参数1是计算的全部区域。

参数2是一个条件，在参数1这个区域中，统计符合参数2的数

据之和（数值型）。

【举例】在G3中显示下图中的工作表中有多少大于100的单元格个数（注意:

数字+字母不算数字）。

第1个参数:

整个区域，即A2:

F12。

第2个参数:

条件，即“>100”。

选定G3，驱动函数向导，选择COUNTIF函数，打开“函数参数”对话框。

按下图分别输入参数数据。

10.SUMIF按条件求和的函数

【格式】SUMIF（参数1，参数2，参数3）

【功能】对满足条件的单元格求和。

【说明】参数1:

要进行条件比较的区域;参数2:

条件，一般为一个表达式，将这个条件在参数1

中比较;参数3:

实际参加计算的单元格，如果省略就使用参数1中的区域。

【举例】在D2中显示出将总成绩120000以上的佣金之和。

一级考试中的Excel函数

第1个参数:

对比条件的整个区域，这里条件为“总成绩120000”，所以参数1即为所有成绩单元格B2:

B8。

第2个参数:

条件，即“>120000”。

第3个参数:

实际参加计算的单元格，即佣金列C2:

C8。

选定D2，打开函数向导，选择SUMIF函数，打开“函数参数”对话框。

按下图分别输入参数数据。

一级考试中的Excel函数

学后练习

一、基础题

1.求平均值一般使用（）函数。

2.“SUM（B3，C3，D3）”中，参数1是（）。

3.“RANK（E3,E3:

E12,0）”是公式吗,（）4.A1中有公式“,$G3，C3"，公式复制到D3中会变成（）。

5.B1中有公式“,$A$2/D9"，公式复制到B2中会变成（）。

6.“,A1,B1,C1”运算结果是（）。

二、连线题

请将函数和其对应功能连线。

SUM求数据的排名AVERAGE求数据个数ABS按条件输出不同结果MAX求最小值MIN按条件求数据之和RANK求数据之和COUNT求平均值IF求绝对值COUNTIF按条件求数据的个数SUMIF求最大值

三、实战题

1.如下图所示，要求如下:

（1）合并并居中A1:

G1单元格，修改工作表名为“成绩表”。

（2）计算各队的总积分;

（3）按升序计算各队的积分排名;

（4）凡总积分在150以上的，奖金列显示“好样的”字样;否则，显示“继续努力”字样。

2.如下图所示，要求如下:

（1）合并并居中A1:

E1单元格，修改工作表名为“统计表”。

一级考试中的Excel函数

（2）在E4中计算平均年龄;

（3）在E5中计算男职工人数;

（4）在E6中计算女职工人数;

（5）在E7中计算男职工平均年龄;

（6）在E8中计算女职工平均年龄。