Excel 第三节 数据计算、管理与分析

公式与函数的使用

◇ Excel的数据计算是通过公式实现的，它既可以对工作表中的数据进行加、减、乘、除等运算，也可以对字符、日期型数据进行字符的处理和日期的运算。
◇ Excel的单元格具有存储数学公式的能力，它可以根据公式或函数的变化，自动更新计算结果。
◇ 对一些复杂而常用的计算，Excel还提供了函数供用户使用，从而减少了用户创建计算公式的麻烦。

运算符 运算符是进行数据计算的基础。Excel的运算符包括算术运算符、关系运算符、连接运算符和引用运算符。

算术运算符 +（加）、—（减）、*（乘）、/（除）、%（百分比）、^（乘方） 运算结果：数值型

关系（比较）运算符 =（等于）、>（大于）、<（小于）、>=（大于等于）、<=（小于等于）、<>（不等于） 运算结果：逻辑值TRUE、FALSE

连接运算符 &

运算结果：连续的文本值
（1）字符型单元格连接 例：B1=足球比赛 B2=赛程表 D1=B1&B2 运算结果：D1=足球比赛赛程表

注意：要在公式中直接输入文本，必须用双引号把输入的文本括起来。
（2）数字连接 例：E1=123&456 运算结果：E1=123456

引用运算符

：冒号，区域运算符，完成单元格区域中数据的引用；

，联合运算符，完成对单元格数据的引用。
例：A1：A4 运算结果：表示由A1、A2、A3、A4四个单元格组成的区域
例：SUM（A1，A2，A5） 运算结果：表示对A1、A7、A5三个单元格中的数据求和

这4类运算符的优先级从高到低依次为：引用运算符、算术运算符、连接运算符、关系运算符。每类运算符根据优先级计算，当优先级相同时，按照自左向右规则计算。

公式的创建

公式是利用单元格的引用地址对存放在其中的数值进行计算的等式。在Excel中要正确地创建一个公式，就是要将等式中参与运算的每个运算数和运算符正确地书写出来。

Excel的公式以等号开头，后面是用运算符连接对象组成的表达式。表达式中可以使用圆括号“( )”改变运算优先级。公式中的对象可以是常量、变量、函数以及单元格引用。如：=B3+B4、=B6*5-B7、=sum(C3:C8)等。当工作表中的数据发生变化时，公式的计算结果也会自动更改。

至于Excel中允许什么样的数据参与运算，我们可以从下面的例子中获得答案。公式“=(SUM(F4:F12)-G13)*0.5”由三部分组成：等号、运算数和运算符。

运算数可以是数值常量，也可以是单元格或单元格区域，甚至是Excel提供的函数。

通过以下步骤创建公式：
① 选中输入公式的单元格；
② 输入等号“=”；
③ 在单元格或者编辑栏中输入公式具体内容；
④ 按Enter键，完成公式的创建。

公式的复制 在Excel中，公式和函数是可以复制和自动填充的，这样可以减少不必要的重复操作，在复制或自动填充公式时，如果公式中有单元格的引用，则自动填充的公式会根据单元格引用的情况产生不同的变化。Excel之所以有如此功能是由单元格的相对引用地址和绝地引用地址所致。为了介绍这两个重要的概念——相对引用地址和绝对引用地址，我们先看一个例子。例如，我们利用公式计算出可乐的销售额，如果把这个公式自动填充到其他商品销售额的单元格中，结果如何呢？

为自动填充后的结果，单击单元格F8，我们会自动填充后的公式随目的单元格位置的变化相应变化为“D8*E8”，如图所示。

下面我们来介绍相对引用地址和绝对引用地址的概念。
相对引用地址 在公式的复制或自动填充时，该地址相对目的单元格发生变化，相对引用地址由列号、行号表示，如E8。比如前面的例子，单元格F4中的公式“=D4*E4”填充到F8时，公式随着目的位置自动变化为“=D8*E8”，其他单元格的填充效果也是类似的。

绝对引用地址 该地址不随复制或填充目的单元格的变化而变化。绝对引用地址的表示方法是在行号和列号之前都加上一个“＄”符号，例如$E$8。如果把单元格F4中的公式改为“=$D$4*$E$4，然后在执行自动填充，结果会如何呢？ 其他商品的销售额都是85.00元。可见“$”符号就像一把“锁”，锁住了参与运算的单元格，使他们不会随着复制或填充的目的单元格的变化而变化。

混合引用地址 如果单元格地址的一部分为绝对引用地址，另一部分为相对引用地址，例如$F4或F$4，这类地址称为“混合引用地址”。如果“$”符号在行号前，表示该行位置是“绝对不变”的，而列位置会随目的位置的变化而变化。反之，如果“$”符号在列号前，表示该列位置是“绝对不变”的，而列位置会随目的位置的变化而变化。

非当前工作表中单元格的引用 如果要从Excel工作簿的其它工作表中(非当前工作表)引用单元格，其引用方法为：“工作表标签!单元格引用”
例如：设当前工作表为“Sheet1”，要引用“Sheet3”工作表中的D3单元格，其方法是：Sheet3!D3

使用函数

◇ 函数是Excel自带的一些已经定义好的公式，是公式的一种形式。
◇ 函数处理数据的方式和公式的处理方式是相似的。例如使用公式“=F4+F5+F6+F7+F8+F9+F10+F11+F12”与使用函数“=SUM(F4:F12)”，其结果是相同的。
◇ 使用函数不但可以减少计算的工作量，而且可以减少出错的概率。
◇按功能将函数分为财务、日期与时间、数学与三角函数、统计、查找与引用、数据库、文本、逻辑、信息。另外为了便于用户使用经常用的一些函数，Excel将它们以常用函数列出，从而方便用户引用函数。
◇ 函数的基本格式为：函数名（参数1，参数2，……）
① 函数名代表了该函数的功能，例如常用的SUM函数实现数值相加功能；MAX函数计算最大值；MIN函数计算最小值；AVERAGE函数计算平均数。
② 不同类型的函数要求不同类型的参数，可以是数值、文本、单元格地址等。

创建函数 例如，在下例中，我们通过插入函数，计算商品的销售总额。
① 选中单元格F13。
② 在编辑栏中输入等号“=”，然后单击【插入函数】按钮，打开【插入函数】对话框。

③ 在【常用函数】/【选择函数】列表框中选择SUM函数，单击【确定】按钮。打开【函数参数】对话框。
④ 在Number1文本框中显示出求和的单元格区域F4:F12，如果该区域符合要求，可直接单击【确定】，计算结果立即显示在单元格F13中。如果不符合要求，可单击文本框右侧的【拾取】按钮，在工作表中选取正确的区域。

几个列入常用函数，但用的不多的函数：

IF判断函数使用举例：

=if(f8>60,红牛的销售额不错,继续努力)

参数说明：f8>60 为判断条件；返回结果只有二个，True真，Falase假，如果为真，函数单元格显示：红牛的销售额不错，如果为真，函数单元格显示：继续努力

HYPERLINK链接函数使用举例：

=HYPERLINK(c:\1.doc,打开1.doc文件)

参数说明： c:\1.doc，所链接的文件地址；打开1.doc文件单元格显示的链接文本。

再例

=HYPERLINK(****,打开网易网站)

STDEV计算标准偏差函数：

=STDEV(B4：L8)

值的大小，反映一组数据的离散情况

快速计算

自动求和

Excel在工具栏中给用户提供了一个“自动求和”按钮(Σ)，它可以快速的求出行和列的和。

①首先选择求和的单元格区域。注意，在选取的单元格区域右边多选一列(行求和)，下边多选一行（列求和）。

②用鼠标单击工具栏上的“自动求和”按钮。此时各行列数据之和分别显示在选择的单元格区域最右边一列和最下面一行内。

数据管理与分析

Excel具有强大的数据库管理功能，可以方便地组织、管理和分析大量的数据信息。可以对数据库的数据进行筛选、排序、分类汇总等操作。

数据清单的概念 在Excel中，数据清单是包含相似数据组并带有标题的一组工作表数据行。我们可以把“数据清单”看成是简单的“数据库”，其中行作为库中的记录，列作为字段，列标题作为数据库中字段的名称。借助数据清单，我们就可以实现数据库中的数据管理功能能——筛选、排序等。

如果要使用Excel的数据管理功能，首先必须将工作表格创建为数据清单。数据清单必须包括两个部分——列标题和数据。

要正确创建数据清单，应遵守以下原则：
① 避免在一张工作表中建立多个数据清单。
② 在数据清单的第一行建立列标题。
③ 列标题名唯一。
④ 单元格中数据的对齐方式可以用【单元格格式】命令来设置，不要用输入空格的方法调整。

编辑数据清单 如果数据清单中的记录、字段数很多，那么查找或编辑某些记录是比较麻烦的。这时，我们可以使用“记录单”功能完成对数据清单的编辑操作。
① 选中数据清单中的任一单元格。
② 单击【数据】/【记录单】命令，打开【记录单】对话框。
③ 【记录单】对话框中从第一条记录开始显示，可以拖动滚动框或单击【上一条】和【下一条】按钮来显示其他的记录内容。另外，我们还可以根据需要进行记录的添加、删除、条件查询等操作。

数据清单排序 排序是组织数据的基本手段之一。通过排序管理可将表格中的数据按字母顺序、数值大小、时间顺序进行排列，也可以按行或按列、升序或降序、是否区分大小写等方式排序。

快速排序 如果仅仅需要对数据清单中的某列数据进行排序，只需要单击此列中的任一单元格，再单击【常用】工具栏中的【升序】按钮或【降序】按钮即可。

高级排序 如果我们将图示例中数据按照进货数量的升序排列，那么有些产品的进货数量是相同的，当遇到这种情况该如何解决呢？我们可以根据多列数据进行排序。

单击数据清单中任一单元格，单击【数据】/【排序】命令，打开【排序】对话框。在此对话框中最多可以设置三个层次的排序标准：【主要关键字】 【次要关键字】 【第三关键字】。例如，我们将【主要关键字】设置为“进货数量”（升序），【次要关键字】设置为“销售总额”（降序），这表示，在“进货数量”相同的情况下，会自动按照“销售总额”排序，即销售额越高，排序位置越靠前。

数据筛选 数据筛选可以实现在数据清单中提炼出满足某种条件的数据，不满足条件的数据只是被暂时隐藏起来，并未真正被删除；一旦筛选条件被取消，这些数据又重新出现。 Excel提供了两种条件筛选命令。

自动筛选 按照选定内容自定义筛选，它适合简单条件的筛选。

下面介绍自动筛选的具体步骤。
① 选中数据清单中任意一单元格。
② 单击【数据】/【筛选】/【自动筛选】命令，此时在数据清单中，每一列的列标题右侧都会出现“自动筛选箭头”按钮。
③ 用鼠标单击筛选箭头，在打开的下拉列表中显示了该列的所有信息，在其中按需要选择一个值，就会在数据清单中只显示满足条件的数据，而其他数据将被隐藏起来。
如果要取消某一筛选条件，只需选择下拉列表中的【全部】选项即可。如果要退出筛选操作可再单击【数据】/【筛选】/【自动筛选】命令，取消【自动筛选】命令前的筛选标识。

下面我们思考这样的问题：如果要筛选单价在1000~2000之间的产品，如何实现呢？对于这样的特殊条件，可以用自定义自动筛选来完成。
单击【单价】右侧的筛选箭头，在下拉列表中选择【自定义】命令，打开【自定义自动筛选方式】对话框。在对话框中可以设定两个筛选条件并确定它们之间的关系。例如筛选出单价大于等于1000元并且小于等于2000元的产品。

高级筛选 高级筛选适合复杂条件筛选。例如我们要筛选出上海地区销售总额在30000元以上的产品信息，具体操作步骤如下。
① 在当前工作表的空白区域键入筛选条件。
② 选中工作表中任意一单元格，单击【数据】/【筛选】/【高级筛选】命令，打开【高级筛选】对话框。【方式】选择默认选项，表示在原数据清单中显示筛选结果；【列表区域】和【条件区域】都可以通过拾取按钮 ，在数据清单中选定相应的数据区域。 ③ 设置完成，单击【确定】按钮。 单击【数据】/【筛选】/【全部显示】命令，即可恢复显示所有数据。

数据分类汇总 所谓分类汇总就是首先将数据分类，然后将数据按照类进行汇总分析处理。分类汇总可以使数据清单中大量数据更明确化和条理化。仍以数据清单为例，如果要查看每类产品销售总额的汇总信息，可以通过以下步骤实现。
① 以【产品名称】为主关键字，对记录进行降序排列。
② 选中数据清单中任意一单元格，单击【数据】/【分类汇总】命令，打开【分类汇总】对话框，如图4-44所示。
③ 打开【分类字段】下的列表，选中【产品名称】，表示数据是按【产品名称】进行排序分类的。
④ 打开【汇总方式】下拉列表，选中【求和】函数，用以计算每类产品的销售总额。
⑤ 在【选定汇总项】列表中，选中【销售总额】复选框，指定分类汇总的计算对象。
⑥ 设置完成后，单击【确定】按钮。 可以看出，在数据清单的左侧，有“隐藏明细数据符号”（-）的标记。单击“-”号，可隐藏原始数据清单数据而只显示汇总后的数据结果，同时“-”号变成“+”号，单击“+”号即可显示明细数据。 如果要取消分类汇总效果，需要再次打开【分类汇总】对话框，单击【全部删除】按钮即可。

数据**表 前面介绍的分类汇总适合于按一个字段进行分类，对一个或多个字段进行汇总。如果我们要实现按多个字段分类并汇总，那么用分类汇总命令就困难了。为此，Excel提供了一个有力的工作——数据**表来解决问题。 以数据清单为例，我们要统计不同产地种类产品的库存问题，此时既要按产地分类，又要按产品名称为类，因此可以利用数据**表来解决。

具体的创建步骤如下：
① 选中数据清单中任意一单元格。
② 单击【数据】/【数据**表和数据**图】命令，打开【数据**表和数据**图向导-3步骤之1】。此时可以选择数据源类型和报表类型，默认系统选项，如图所示。
③ 单击【下一步】按钮，打开【数据**表和数据**图向导-3步骤之2】对话框，在此选取用于创建数据**表的数据源区域，一般Excel会自动庙宇数据区域。如果要重新选定数据区域，可以通过拾取按钮进行选择。
④ 单击【下一步】按钮，打开【数据**表和数据**图向导-3步骤之3】对话框，用于选择**表的显示位置，如图所示。这里保持默认选项，单击【完成】按钮。 此时Excel在新工作表Sheet2中创建了一个空白的透视表，并打开【数据**表字段列表】对话框，如图所示。 在数据**表的“布局”框中，分为页字段、行字段、列字段和数据项。
◆ 页字段：在数据**表中指定为页方向的字段。在页字段中，既可以显示所有项的汇总，也可以一次显示一个项，而筛选掉其他数据。
◆ 行字段：数据**表中按行显示的字段。
◆ 列字段：数据**表中按列显示的字段。
◆ 数据项：在数据**表中要汇总的数据。 我们按照统计要求，交“产地”字段拖至“将列字段拖至此处”字样的位置，将“产品名称”字段拖至“将行字段拖至此处”字样的位置，交“库存数量”字段拖至“请将数据项拖至此处”字样的位置。关闭【数据**表字段列表】对话框，**表结果。双击透视表中的字段，可以打开【数据**表字段】对话框，进行编辑。