第4章+电子表格处理软件Excel+2003_课件_2

一. 公式与函数

在Excel工作表中,通过使用公式和函数可以对数据进行复杂的运算和处理。本节将详细地讨论这方面的问题。

1．公式的使用

公式是通过运算符将数据常量、单元格地址、函数等连接在一起的式子。在输入公式时，必须以“=”号作为开始。如“=10+B2*4”就是一个公式，意思为:把单元格B2中的值取出来乘以4，再加上10，运算结果显示在建立该公式的单元格中。（特别注意：建立的公式中可以出现单元格地址，如这里的B2）

（1）公式中的运算符

   公式中可以有哪些运算符呢？

a. 算术运算符：加+、减-，乘*、除/，还有乘方^等

b. 比较运算符包括：=（等于）,<>（不等于）、>（大于）,>=（大于等于）、<（小于）,<=（小于等于）。例如，公式“=5>3”是合法的，不过它的运算结果是一个逻辑值即为TRUE。

c. 文本运算符：文本运算符只有一个“&”，其作用是把前后两个文本型数据连接在一起，如公式：="浙江"&"杭州"，运算结果为“浙江杭州”。

（2）在单元格中输入公式

下面将通过一个例子，介绍在单元格中直接输入公式的操作方法。

请打开“Excel演示题三.xls ”，并单击标签为“发放工资”工作表。

2．复制公式

在Excel中为了操作的快捷，常需要进行公式的复制。在此介绍如下两种复制公式的方法：

① 选定含有公式的单元格，拖动单元格的自动填充柄，可完成相邻单元格公式的复制，在上面的操作中曾用此方法复制公式。

② 用“复制”加“粘贴”完成公式的复制。

特别注意，在复制公式操作时，首先要求用户弄清楚单元格地址的引用概念，单元格地址分为相对地址、绝对地址、混合地址三种，因此单元格地址就有三种不同形式的引用。

（1）相对地址

单元格相对地址的形式比较简单，即用列标和行号组成，如A1、B2、C3等。如果在单元格中当含有相对地址的公式被复制到目标单元格时，那么单元格地址会发生变化，怎么变取决于公式原来位置和复制到的目标位置的“相对位置”，相对位置要保持不变。例如在刚才的例子中，在E2单元格中输入公式为：=B2+C2-D2，该公式含有单元格的相对地址，如果通过自动填充柄将之复制到E3，则公式变为：=B3+C3-D3。因为公式引用了相对地址，公式下移1行，公式中单元格地址也下移1行（因为要保证相对位置不变）。

（2）绝对地址

绝对地址由在列标和行号前加上符号“$”组成，如$A$1、$B$2、$C$3均为单元格的绝对地址表示。如果在单元格中当含有绝对地址的公式被复制到目标单元格时，那么公式中的绝对地址保持不变。

举个例子：在刚才的例子中，如果在E2单元格中输入公式：=$B$2+$C$2-$D$2，然后通过自动填充柄将之复制到E3，则公式并未发生变化，仍然是=$B$2+$C$2-$D$2，当然结果就是1450。

可见，在建立公式的时候要注意相对地址引用和绝对地址引用的差异。

（3）混合地址

混合地址是在单元格的地址表示中，既有相对地址形式又有绝对地址形式。如$A3就是一个混合地址，列标A前有$属于绝对地址形式，而行号3前无$属于相对地址形式。复制公式就必须注意哪个要变哪个不变。

（4）跨工作表的单元格地址引用（见p.150，自己看一下…）

请打开“Excel演示题四.xls ”，我们来做一个计算。

3．函数的使用

   在刚才的计算中，还可以把公式“=D3+D4+D5+D6+D7”写为：=SUM(D3:D7)

   其中函数SUM就是Excel中的求和函数。

因此开始介绍Excel的常用函数。

下面先介绍Excel常用函数中的求和函数SUM和求平均值函数AVERAGE的使用，然后再介绍其他常用函数的使用。

（1）求和函数SUM：函数格式：SUM(参数1，参数2，…)。

例如：公式：=SUM(1,5,9)是求3个参数的的和，其结果为15。公式：=SUM(D3:D7)是求5个单元格中的数字之和。D3:D7表示单元格区域，该单元格区域有哪几个单元格呢？

（2）求平均值函数AVERAGE：

例如：公式：= AVERAGE(1,5,9)的计算结果是5；公式：=AVERAGE(D3:D7)是求D3至D7这5个单元格中数字的平均值。

在单元格中可以把上面的公式直接输入，但是Excel系统还提供了用“函数向导”输入函数，其方法为：选取某单元格，单击“插入”菜单→“函数”命令，或者单击数据编辑栏的“插入函数”按钮，弹出“插入函数”对话框，在“插入”对话框中选取“常用函数”中的SUM函数，单击确定，这时会出现“函数参数”对话框，第一个参数Number1框内输入B3:G3，但最常用的方法是：单击“折叠对话框”按钮，对话框折叠后成为一个条状，这样就可在工作表中选定B3:G3区域，然后单击条状上的返回按钮，重新恢复显示“函数参数”对话框的全部内容，最后单击“确定”按钮。

在“Excel演示题四.xls ”中我操作给大家看。

简单介绍一下利用自动求和按钮….。（听我说…）

   在Excel中还要掌握其他常用函数，它们是：

（1）求最大值函数MAX

例如，假设B1:B5区域的值分别为：23、23、31、10、7，则公式：=MAX(B1:B5)返回结果值为31。

（2）求最小值函数MIN

例如，假设B1:B5区域的值分别为：23、23、31、10、7，则公式：=MIN(B1:B5)返回结果值为7。

（3）求包含数据的单元格的个数函数COUNT和COUNTA

功能：COUNT求包含数字（又称数值型数据）的单元格的个数，而COUNTA是求“非空”单元格的个数。

例如，A1:F1单元格区域中的数据（还是在“Excel演示题四.xls”中，有这里的数据）如图4-27所示，则公式：=COUNT(A1:F1)的计算结果是3，因为数值型数据只有3个,B1和C1为文本数据，E1为空单元格；而公式：=COUNTA(A1:F1)的计算结果是5，因为“非空”单元格的个数有5个。在此顺便介绍一个COUNTBLANK函数，从字面上也可以知道它是求“空”单元格的个数。如果把上文公式中的COUNT换成COUNTBLANK，则其结果为1。因为A1:F1数据区域有一个“空”单元格。

（4）条件计数函数COUNTIF

格式：COUNTIF(条件数据区,〝条件〞)。

功能：COUNTIF函数是统计数据区域中满足条件的单元格个数。

例如，数据如图4-27所示，在A3单元格中输入公式：= COUNTIF(A1:F1,"=50")，则计算结果是1，因为只有一个单元格满足条件。

   （5）条件函数IF

格式：if(逻辑表达式，表达式1，表达式2)。

功能：若“逻辑表达式”的值为真，则整个函数的结果为“表达式1”的值；否则为“表达式2”的值。例如，在某单元格A3中输入公式：=if(A1>=60，"通过"，"未通过")，若A1单元格中的值等于或大于60，则在A3单元格中显示“通过“字样，否则显示“未通过”字样。

（6）日期和时间函数NOW和TODAY

这两个函数没有参数，函数NOW( )返回的结果是计算机系统当前的日期和时间，TODAY( )返回的结果是系统当前的日期。对于日期函数而言，再介绍YEAR、MONTH和DAY这3个相关的函数，它们分别从日期值中取出年份、月份和天数。例如假设今天是2010年5月1日，则公式=YEAR(NOW( ))结果是2010（表示2010年），公式=YEAR(NOW( ))结果是5（表示5月份）。

结合刚才介绍的几个函数，我们一起来做一道题目。

请打开“Excel演示题五.xls ”，我们大家一起来做。

思考几个问题：

问题1：Excel工作表中第5行第4列的单元格地址是（ ？）。

a. 5D  /   b. 4E  /  c. D5  /  d. E4   (第4列对应的列标是D )

问题2：看p.172第25小题，在Excel中，单元格G8的绝对地址表示为______。

A．G8       B．$G$8        C．$G8        D．G$8

   补充：G8为相对地址表示；G$8或$G8为混合地址。

问题3：Excel主界面窗口中编辑栏上的“fx”按钮用来向单元格插入________。

A.函数   /   B.数字   /   C.公式   /   D.文字

问题4：Excel中，在向一个单元格输入公式或函数时，则使用的前导符为（？）。

A. % / B.> / C.<    /   D. =

问题5：在Excel工作表中，假定C3:C6区域内保存的数值依次为10、15、20和45，则函数=MAX(C3:C6)的值为______。

A.10     /   B.22.5     /   C.45     /  D.90

问题6：在Excel工作表中，求单元格区域B5:E5中数值型数据的平均值，则表达式为( ？ )。

     MAX(B5:E5)    / COUNT(B5:E5  /  AVERAGE(B5:E5) /  SUM(B5:E5)

问题7：在Excel中，求一组单元格中含数据（数据类型不限）单元格个数的函数为__________。

A. AVERAGE  /   B.COUNT   /   C. RAND   /  D. COUNTA

问题8（比较难）：看p.170，【例题4-7】在Excel中，假定B2单元格的内容为数值15，在某单元格中输入公式=if(B2>20,"好",IF(B2>10,"中差"))，计算结果为_______。

A．中          B．良          C．好          D．差

问题9：看书上p.169，【例题4-5】在Excel中，假定单元格D3中保存的公式为“=B3+C3”，若把它复制到E4中，则E4中保存的公式为_______。

A．=B3+C3        B．=C3+D3      C．=B4+C4    D．=C4+D4  

 （提示：改变了一列，还改变了一行）。

  类似题目还有p.172第31题，大家可以考虑一下，如果太难，我想就算了，首先把基本题目掌握。

二. Excel数据处理

Excel数据处理主要是指对数据进行排序、筛选（含自动筛选和高级筛选）、分类汇总等操作。要进行如上操作，要求数据表格必须是按“数据清单”存放。

什么是“数据清单”？数据数据清单一般具有一定的特征（看书上p.157倒数12行）：数据清单由标题行（又称表头）和数据两部分组成。如下图，几个术语看一下。① 字段：指数据表中的每1列；② 记录：指数据的每1行。

1．数据排序

排序操作比较简单，一般分为两种：整体排序和单列排序。注意：排序操作是对数据清单而言的。

1．整体排序

整体排序的操作步骤如下：①先单击数据区域的任意1个单元格，或者选定整个数据区域（含标题行）。

② 单击“数据”菜单→“排序”命令，打开排序对话框，按要求进行操作即可，对话框中有“主要关键字”，如果题目还要求第2个关键字，那就再选“次要关键字”等。（什么意思知道吗？我上课讲一下..）(待会儿操作….)

2．单列排序：先选择要排序的字段列，然后单击“常用”工具栏中的“升序”    或 “降序”。建议使用“整体排序”。

2．数据筛选

数据筛选是在工作表的数据清单中快速查找具有特定条件的记录，筛选后数据清单中只包含符合筛选条件的记录，以便于浏览。“数据/筛选”有“自动筛选和高级筛选”两种。注意：数据筛选功能也是对数据清单而言的。

（1）自动筛选：操作步骤：先单击数据区域的任意1个单元格，或者选定整个数据区域（含标题行）；单击“数据”菜单→“筛选”→“自动筛选”命令，数据表每列的标题部分（字段名处）出现下拉箭头；单击筛选条件列的标题右侧的箭头，就可以选择“自定义”，根据要求设定筛选条件。（待会儿操作给大家看…）

如何取消自动筛选呢？取消自动筛选非常简单，只要单击“数据”菜单→“筛选”，在打勾的“自动筛选”命令上单击一下，即可去掉自动筛选，重新恢复显示所有数据。

“自动筛选”如果用于筛选两个字段的，那么只能用于两个字段是“与”的关系。如果是“或”的关系则必须使用“高级筛选”

（2）高级筛选

Excel提供高级筛选功能，主要用于多字段条件的筛选。上文讲了“自动”筛选。上文讲了“自动”筛选也可以用来完成多字段条件的筛选，但这些多字段条件必须是“与”关系。而高级筛选提供了比自动筛选更完善的功能，例如多字段条件不论是“与”关系还是“或”关系，都可以用高级筛选完成。

操作提示：

使用高级筛选必须先建立一个条件区域，用来编辑筛选条件。条件区域的第一行是所有作为筛选条件的字段名（字段名要与数据清单中的字段名一致）。条件区域的其他行输入筛选条件，“与”关系的条件必须写在同一行内，“或”关系的条件不能写在同一行内。条件区域与数据清单区域必须用空行或空列隔开。（待会儿操作给大家看…）

3．分类汇总

数据的分类汇总是对相同类别的数据进行统计汇总，汇总方式包括求和、计数、平均值、最大值、最小值等。分类汇总也是只对数据清单进行，数据清单的第一行必须有列标题。

分类汇总步骤： 先对“分类”字段进行排序；单击“数据”菜单→“分类汇总”命令，弹出“分类汇总”对话框，有以下几个设置项目：

分类字段、汇总方式、选定汇总项”，其他几项一般采用默认值。

我们想通过一个问题来更详细的操作给大家看（下面一起来讲…）。

如何删除分类汇总呢？删除“已经创建的分类汇总”的方法比较简单，操作步骤如下：① 单击“数据”菜单→“分类汇总”命令，弹出“分类汇总”对话框，在该对话框中单击“全部删除”按钮，即可删除分类汇总。

----------------------------------------------------------------------

好了，一定要操作过才知道，下面我们就来操作：“排序、自动筛选、高级筛选和分类汇总。

---------------------------------------------------------------------------

三. Excel图表

在Excel中，不仅可以使用二维数据表的形式反映人们需要使用和处理的数据信息，而且也可以通过图表的形式更形象和直观地反映数据信息。图表是对已经存在的数据表的图形化表示，这些图形可以是：柱形图、折线图、饼图、面积图、圆柱图和圆锥图等，我们把它们称为图表类型。当数据表中的数据被修改时，与之联系的图表（即图形的大小）也随之发生相应的变化。

图表是用二维坐标系反映的，通常用横坐标x轴表示可区分的所有对象；用纵横坐标y轴表示对象所具有的某种属性的数值大小。

1．建立图表

在Excel中，可以用“图表向导”创建图表，步骤是：单击“插入”菜单→“图表”命令，打开“图表向导”对话框，按照图表向导每个步骤的提示，即可快速地完成图表的制作。

为了更具体的理解，我们将通过一个例子边讲边做。

假设某销售公司前3个月各种品牌汽车销售情况表如下：

要求使用图表向导制作数据图表，具体要求为：各种品牌汽车销售月份作为x分类轴，y轴是各月份的销售额，图表类型选择簇状柱形图，图表标题为：前3月各种品牌汽车销售统计，x轴、y轴名称分别为“销售月份”和“销售额”。

操作步骤：

① 选定图表数据区域A2:D8

② 图表向导步骤1：单击“插入”菜单→“图表”命令，打开“图表向导”对话框，如下图。在“标准类型”选项卡中选择图表类型中的“柱形图”，在右侧子图类型类中选择第1个子图类型（即簇状柱形图），单击“下一步”按钮，进入图表向导步骤2。（我在Excel中操作….）

③ 图表向导步骤2

在图表向导步骤2中，出现了如下“图表数据源”对话框，

在该对话框中显示所选定单元格数据区域以及系列产生在“列”，根据题意，“系列产生在”请选择“行”因为是用“月份”作为分类x轴。数据区域一般不改（因为在建之前已经选定数据区域）。单击“下一步”按钮。

④ 图表向导步骤3

在图表向导步骤3中，出现“图表选项”对话框。在“标题”选项卡中，图表标题对应框中输入标题“前3月各种品牌汽车销售统计”，分类x轴对应框中输入“销售月份”， 数值y轴对应框中输入“销售额”。若选择了“图例”和“数据标志”等选项卡还可分别进行相应的设置。进入图表向导步骤4。

⑤ 图表向导步骤4

在图表向导步骤4中，出现 “图表位置”对话框。在该对话框中选择图表插入的位置，本例子选择第2项（即把图表和和建立图表的数据源放置在同一张表Sheet1中），单击“完成”即可。

------------------------------------------------

操作结果看书上P.166图4—45 。也可以看我操作的结果。

-------------------------------------------------

2．编辑图表

我们一起来书P.167 ----- 

这里的要求就低一些了。我们来举个例子，

如更改“图表类型”项，即用“圆柱图”代替“柱形图”，结果会如何？

又如更改“图表选项”，让图例位于图表“底部”，看我操作。

-----------------------------------------------------------

3．饼图创建实例

假定某一大学2008年的招生数据如下：专科生为2450人，本科生为5800，硕士生为1400，博士生为300（如图4-47所示），要求在相应单元格中统计每类招生人数占总招生人数比例（用百分比表示），并要求以饼图的形式直观地表示出来。具体要求为：所创建的饼图以“学生类别”作为分类标志，数值列选择“占总招生人数比例”，在饼图中显示的标题内容为：某高校2008年招生图表，图例位于图表的底部，图表中显示每类学生占总招生人数的百分比。

看我操作….

先计算一下：=B3/SUM($B$3:$B$6)….具体操作为：选中“学生类别”列作为分类标志，再按住Ctrl键，选择“占总招生人数比例”作为数值列，然后单击“插入”→“图表”，在图表类型中选择“饼图”，单击 “下一步”按钮…..。

思考几个问题：

问题1：Excel中进行分类汇总操作之前，首先必须对数据清单中的分类字段进行（  ）。

求和　   /     筛选      /  查找      / 排序

这个题目和p.173第43题类似，一起看一下..。

问题2：在Excel的电子工作表中建立的数据表，通常把每一行称为一个（?）。

A.记录    /   B.字段   /   C.属性  /  D.关键字

问题3：在Excel 2003的自动筛选中，所选数据表的每个标题（属性名，或字段名）都对应着一个________。

A.下拉菜单   /   B.对话框   /   C.窗口   /   D.工具栏

问题4：看p.174第46题，Excel通过_________实现图表的创建。

A．数据库应用     B．图表向导   C．函数       D．数据地图

问题5：看p.174—50，在Excel的图表中，能反映出数据变化趋势的图表类型是________。

A．柱形图       B．折线图       C．饼图       D．气泡图

问题6：饼图所显示出不同于其它类型图表的关系是(  )

A. 能够反映数据的变动情况及变化趋势  

B. 能够表现个体与整体之间关系

C. 能够反映多个数据在幅度上连续的变化情况

问题7：下面关于图表与数据源关系的叙述中，正确的是___。

A. 图表中的标记对象会随着数据源中的数据变化而变化

B. 数据源的数据会随着图表中标记的变换而变化（可演示一下）

C. 删除数据源中某单元格的数据时，图表中某数据点也会随之被自动删除。

D. 以上都是正确的说法

问题8：在Excel 2003中，设置图表插入位置是创建图表过程的______。

   A.第1步   /   B.第2步   /  C.第3步  /  D.最后一步

问题9：最后思考一题，在处理学生成绩单时，对不及格的成绩用醒目的方式表示（如红色下划线表示），但要处理大量的学生成绩时，利用（  ）命令最为方便。

a. 查找　 /   b. 数据筛选     /  c. 定位    /  d. 条件格式

--------------------------------------------------------------------------------------------------