Excel制表实例操作之(6)数据的查找、统计与分析.pdf

《Excel制表实例操作之(6)数据的查找、统计与分析.pdf》由会员分享，可在线阅读，更多相关《Excel制表实例操作之(6)数据的查找、统计与分析.pdf（48页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、第六章数据的查找、统计与分析6.1 数据的查找与筛选6.2 数据的统计与计算6.3 数据的排序操作6.4 数据链接与共享处理 数据的查找、统计与分析 CHAPTER 6154CHAPTER 6 数据的查找、统计与分析6.1 数据的查找与筛选例1 保护当前工作表中的重要数据除了对工作簿设置密码外，我们还可以对当前的工作表中的单元格或单元格区域中重要数据进行锁定保护。具体操作如下：（1）首先，选中你需要保护的单元格或单元格区域。（2）单击“格式”菜单中的“单元格”命令，弹出“单元格格式”对话框。（3）选中“保护”选项卡，在窗口中选中“锁定”复选框，单击“确定”按钮关闭对话框。“保护”选项卡设置（4

2、）单击“工具保护保护工作表”命令，在“取消工作表保护时使用的密码”框中键入你所设置的密码，单击“确定”按钮。这时所选中的单元格或单元格区域将被锁定，只有键入密码后才可以解除锁定。例2 使用“批注”功能对编辑好的数据，如果你想对其中某个特定的内容加以说明，或者是重要的地方留下记号。这时，可以使用Excel提供的“批注”功能。如何使用“批注”功能呢？具体的操作如下：（1）首先，在工作表中选中一个空白单元格。（2）单击“插入”菜单中的“批注”命令，弹出一个类似文本框的输入框。数据的查找、统计与分析 CHAPTER 6154155插入批注（3）在这里可以输入提示和说明的信息，然后单击需要批注的单元格，

3、在这个单元格中将会出现一个箭头，把鼠标移动到该单元格中，就会看到刚才我们输入的批注信息。（4）我们还可以对批注进行修改，选中该单元格单击鼠标右键，在快捷菜单中选中“编辑批注”选项，将会显示刚才设置的类似文本框的输入框，重新对内容进行修改即可。如果你想删除批注，只要在弹出的快捷菜单中选中“删除批注”选项即可。例3 使用“拼写检查”功能来检查文本要想使用“拼写检查”功能来检查文本，可以这样做：（1）单击“工具”菜单中的“拼写检查”命令，Excel就会自动对当前的工作表中的内容进行拼写检查，在错误的地方会弹出“拼写检查”对话框。拼写错误提示 数据的查找、统计与分析 CHAPTER 6156CHAPT

4、ER 6 数据的查找、统计与分析（2）在“不在词典中”框中显示错误的单词，下面“建议”框中显示了Excel默认的词典中的所有单词，你可以在“建议”框中选择正确的单词，然后单击“更改”按钮，可以把当前的词替换为正确的单词。你也可以单击“忽略”按钮，可以忽略此处错误。例4 快速修复Excel文件数据我们用Excel存储或分析较大的数据量时，出现了无效页面错误，确定后应用程序关闭，每次打开此文件都是如此。怎样修复出现的问题？这主要由下面原因引起：首先考虑是操作系统引起。重新启动机器，用Excel打开其他文件没有问题，但是打开此文件还是出现无效页面错误。排除操作系统引起原因。如果打开别的文件没有问题，

5、说明问题出在此文件上。用杀毒软件进行病毒扫描，如果没有发现病毒，排除病毒引起原因。此时，我们还有最后一个方法。利用MS SQL SERVER7.0修复。它是一个很不错的修复软件。按照其数据转换服务导入向导(Data Trans formation Services Import Wizard)，选择好源驱动程序(Microsoft Excel)和文件名(Excel文件)以及目标驱动程序(Microsoft OLE DB Provider for SQL Server)和数据库名，导入成功后，发现Excel文件中的每一个工作表都转换成SQL数据库中的数据表，并看出一个数据表的表名与其他的不一样，

6、带有单引号，并且不能浏览数据，怀疑是此问题引起的，将其单引号去掉，就能浏览数据。将其导出，此Excel文件制作完成了。例5 在Excel中实现数据合并和汇总要想在Excel中实现数据合并和汇总，可以这样做：（1）首先，选中需要汇总的数据单元格。（2）单击“数据”菜单中的“排序”命令，给数据进行排序。然后，单击“数据”菜单中的“分类汇总”命令，这样就可以对数据进行汇总了。数据的查找、统计与分析 CHAPTER 6156157排序设置如果你想对数据进行重新排序，必须清除上面的汇总。这样，再对数据进行重新排序和汇总。例6 将一张表格中的数据筛选到另一表格要想将一张表格中的数据筛选到另一表格，可以这样

7、做：我们可以使用Excel中的“高级筛选”功能将一张表格中的数据筛选到另一张表格中。（1）首先在可用作条件区域的数据清单上插入至少三个空白行，条件区域必须具有列标题。但是要在条件值与数据清单之间至少留一个空行。（2）在列标题下面的一行中，键入所要匹配的条件。如果要查找一些字符相同但其他字符不一定相同的文本值，则可以使用通配符。（3）单击数据清单中的单元格。然后，单击“数据筛选高级筛选”命令。如果要通过隐藏不符合条件的数据行来筛选数据清单，可单击“在原有区域显示筛选结果”项。高级筛选设置 数据的查找、统计与分析 CHAPTER 6158CHAPTER 6 数据的查找、统计与分析（4）如果想将符合

8、条件的数据行复制到工作表的其他位置来筛选数据清单，请单击“将筛选结果复制到其他位置”项，单击“复制到”编辑框，然后单击粘贴区域的左上角即可。（5）在“条件区域”编辑框中，输入条件区域的引用，并包括条件标志。如果要在选择条件区域时将“高级筛选”对话框移走，请单击“压缩对话框”按钮。例7 在每次结束数据库操作时自动压缩要想在每次结束数据库操作时自动压缩，具体操作如下：（1）单击“工具”菜单中的“选项”命令，打开“选项”对话框。（2）在窗口中选中“常规”选项卡，将“关闭时压缩”复选框选中，然后单击“确定”按钮即可。例8 使用“高级筛选”功能快速查找数据使用“高级筛选”功能可以一次性把你想要的数据全部

9、找出来。具体操作如下：（1）首先，在工作表中设置一个条件区域，在第一行中输入排序的字段名称，在第二行中输入想查找的条件，建立条件区域。（2）选中工作表中的所有的数据区域，单击“数据筛选高级筛选”命令，弹出“高级筛选”对话框。选择高级筛选（3）在“数据区域”中Excel自动选择好了筛选的区域，将光标移到“条件区域”框中，单击框中右边的按钮，弹出一个新的对话框，用鼠标选中你建立的条件区域。然后，再单击按钮回到“高级筛选”对话框。数据的查找、统计与分析 CHAPTER 6158159（4）单击“确定”按钮，现在工作表中就是你希望看到的结果了。例9 将数据库与Excel结合使用Excel强大的打印设置

10、，几乎可以打印出任何你想要的效果，只要把你想要的数据库用Excel打开就行了(按住Shift+点鼠标右键，在打开方式里选Excel，确定)。然后拖动你的鼠标，把表格变成你想要的样子就行了。这里需要说明一下的是，如果原来的数据库太大，而你只想要其中一部分的话，数据库的功能还是要充分利用的，就像COPY TO D:TEMP1.DBF FOR E23ca.xmm=XX FIELDS xmm,bzh,bbb,dai,ggg。TYPE FOXPLUS这种语句我还是经常要用到的。不习惯用命令？也没关系，文件菜单里有个“导出”就是用来完成这个任务的。选择文件类型时别忘了选Dbase，然后随便敲个名字作为导出

11、文件。选项要认真填写，范围ALL代表全部；For当然是你所需要的数据必须满足的条件，比如name=，如果你不想用手工输入，也可以通过下拉列表选择来完成。6.2 数据的统计与计算例1 行号统计法进行“数据统计”统计各科成绩中各分数段的人数，即统计90100，8089，7079，6069，60分以下的人数。将光标移动到成绩所在列，用鼠标单击工具栏中的“降序”按钮，成绩由高到低排列。90100分数段中最低分数是90分，所在行行号为11，则此分数段人数是11-110。在8089分数段所在行的行号在12到24之间，则8089分数段的人数是13。同样方法可统计出其他分数段的人数。如果数据不在一列，而是在同

12、一行内，能否迅速进行统计呢？那也难不住，可以用行列转换法将行内数据转换到列内，然后再进行统计。具体操作是：选中行中有关数据，单击工具栏中的“复制”按钮，再在目标列内右键单击，选择弹出菜单中的“选择性粘贴”命令，在打开的“选择性粘贴”对话框中选中“转置”前的复选框，单击“确定”退出，则数据都转换到一列内，根据行号统计法进行统计即可。数据的查找、统计与分析 CHAPTER 6160CHAPTER 6 数据的查找、统计与分析例2 选中统计法进行“数据统计”上例中在统计8089分数段人数时，也可以这样进行。首先进行排序，然后从最高分89分开始选中，向下拖动鼠标，到最低分81分结束，在拖动鼠标的过程中，

13、工具栏的左下角或选中部分的右下角出现一变动的提示条为“13R1C”，意指选中的部分为17行，1列，则统计得8089分数段的人数为13。注意此方法也适合在同一行内进行。例3 筛选法进行“数据统计”统计各分数段人数时首先要进行排序，打乱了原来的次序。若不打乱原次序能否进行统计呢？当然可以了！单击“数据筛选自动筛选”，则进入自动筛选状态，工作表的第一行都有一个向下的小箭头，单击“成绩”单元格中的下拉箭头，选择“自定义”，打开“自定义自动筛选方式”对话框，设置为“小于或等于99”与“大于或等于90”，单击“确定”按钮退出，则只显示出成绩在9099之间的学生。注意此时显示的行号是每位同学原来实际所在的行

14、号，不能根据“行号统计法”来统计人数的多少，而且也不能采用“选中统计法”来统计人数，那么如何统计人数呢？如果人数较少，逐个数一下就可以了，若人数较多，先选中所有符合条件的人数，单击工具栏中的“复制”按钮，选择另一个工作表，单击“粘贴”按钮，则将所有人数复制到一个新的工作表中，此时根据行号就可以快速统计出人数。依此方法可统计出其他分数段的人数。数据的查找、统计与分析 CHAPTER 6160161例4 查找法进行“数据统计”前面介绍的数据统计是在一行或一列中进行操作，如果要在多列即某一单元格区域内进行统计，那可使用查找的方法。如要统计各科成绩中59分的人数，操作步骤是：先打开“查找”对话框，在查

15、找项中输入“59”，单击“查找下一个”按钮一次，可找到一个59分，继续查找，就可快速确定成绩是59分的人数，记下查找到的“59”的次数就是最后的统计人数了。例5 函数统计法进行“数据统计”统计函数较多，主要介绍常用的以下几个。1.COUNT函数返回参数的个数。利用函数COUNT可以计算数组或单元格区域中数字项的个数。语法：COUNT（value1，value2，.）value1、value2、是包含或引用各种类型数据的参数（1-30个），但只有数字类型的数据才被计数。如统计参加考试的学生人数：COUNT（B2:B47）等于46。2.COUNTA函数返回参数组中非空值的数目。利用COUNTA函数

16、可以计算数组或单元格区域中数据项的个数。语法：COUNTA（value1，value2，.）value1、value2、所要计数的值，参数个数为1-30个。例如，统计参加考试的学生人数：COUNTA（B2:B47）。注意COUNT与COUNTA的区别：函数COUNT在计数时，将把数字、空值、逻辑值、日期或以文字代表的数计算进去；但是错误值或其他无法转化成数字的文字则被忽略。而COUNTA的参数值可以是任何类型，它们可以包括空字符，但不包括空白单元格。数据的查找、统计与分析 CHAPTER 6162CHAPTER 6 数据的查找、统计与分析3.COUNTIF函数计算给定区域内满足特定条件的单元格

17、的数目。语法：COUNTIF（range，criteria）Range：为需要计算其中满足条件的单元格数目的单元格区域。Criteria：为确定哪些单元格将被计算在内的条件，其形式可以为数字、表达式或文本。例如，条件可以表示为“80”、“80”。如统计政治课考试成绩中及格的人数。COUNTIF（B2:B47，=80）等于13。4.FREQUENCY函数频率统计分布函数，以一列垂直数组返回某个区域中数据的频率分布。例如：使用函数FREQUENCY可以计算在给定的值集和接收区间内，每个区间内的数据数目。由于函数FREQUENCY返回一个数组，必须以数组公式的形式输入。语法：FREQUENCY（da

18、ta_array，bins_array）Data_array：为一数组或对一组数值的引用，用来计算频率。如果data_array 中不包含任何数值，函数FREQUENCY返回零数组。Bins_array：为一数组或对数组区域的引用，设定对data_ array进行频率计算的分段点。如果 bins_array 中不包含任何数值，函数FREQUENCY返回data_array 元素的数目。利用此函数可以非常迅速地解决我们前面介绍的求各分数段人数的例子。如求成绩中各分数段的人数，B2:B47为data_array，C4:C7为bins_array，值为60，69，79，89，选定D4:D8为输出区域

19、，输入公式=FREQUENCY（B2:B47，C4:C7），按“Ctrl+Shift+Enter“键得到结果为4;0;6;13;10，即各分数段的人数是4、0、6、13、10。5.MAX和MIN函数MAX返回数据集中的最大数值。语法：MAX（number1，number2，.）number1，number2，为需要找出最大数值的1到30个数值。可以将参数指定为数字、空白单元格、逻辑值或数字的文本表达式。如果参数为错误值或不能转换成数字的文本，将产生错误。数据的查找、统计与分析 CHAPTER 6162163MIN返回给定参数表中的最小值。语法：MIN（number1，number2，.）num

20、ber1，number2，是要从中找出最小值的1到30个数字参数。参数可以是数字、空白单元格、逻辑值或表示数值的文字串。如果参数中有错误值或无法转换成数值的文字时，将引起错误。利用这两个函数可统计出各科成绩的最高分和最低分。举例：如果A1:A5包含数字 99、87、59、27 和 32，则：MAX（A1:A5）等于99MIN（A1:A5）等于27。例6 在Excel中实现复杂数据的排序在Excel编辑过程中你经常会遇到这种情况：340008265(机箱类型)100(台)入库号100295，这时要对入库的设备进行排序，如果按入库号进行排序，该怎样操作？虽然Excel的排序功能强大，但是遇到这种情

21、况它也无处着手了。这时候你就需要利用“分列”功能来解决这样的问题，具体操作如下：（1）选中要排序的列，单击“数据”菜单中的“分列”命令，弹出“文本分列向导”对话框。文本分列向导（2）这里提供了两种分列形式，分为“分隔符号”和“固定宽度”两种，你可以视情况进行选择。这里选中“分隔符号”复选框，也是系统默认的形式，然后单击“下一步”按钮。数据的查找、统计与分析 CHAPTER 6164CHAPTER 6 数据的查找、统计与分析（3）在“分隔符号”框中有五种形式提供我们进行分列，但是由于这个数据本身没有任何的分号、逗号等，所以在这里就需要选这些复选框。因为我们是要对入库号进行排序，所在只要选中“其他

22、”复选框，在框中输入“号”字，然后单击“下一步”按钮，在对话框中单击“完成”按钮。分隔符号选择这时，入库号100295所在列即被分离为另一列，现在你就可以按入库号进行排序了。例7 快速输入数据序列在表格中输入证件序号、项目序号或日期序号等一些特殊的数据系列，一般都是手工完成的。如果你想删除其中的一行，又想对以下的号码进行修改，真是费时又费力。如何才能解决这种问题？我们可以使用“自动填充”功能来完成这些操作，它不但操作起来方便、快速，而且修改起来也挺方便的。下面就来介绍一下具体的操作过程：（1）在第一个单元格中输入起始数据，在下一个单元格中输入接下来的第二个数据。（2）选中这两个单元格，将鼠标移

23、到单元格右下方，当鼠标指针变成黑十字架时，按住鼠标左键沿着填充的方向拖动。拖过的单元格将会自动按规定的序列进行填充，如果你想删除其中的某一行，那么下面的序列将自动改变。（3）如果你经常使用某些有规律的数据序列的话，你可以单击“工具”菜单中的“选项”命令，选中“自定义序列”选项卡，在“输入序列”框中输入新的序列，然后单击“添加”按钮并确定。这里需要注意的是新序列各项之间要输入半角符号的逗号加以分隔。数据的查找、统计与分析 CHAPTER 6164165自定义序列设置例8 在Excel中“克隆”相同的数据要想在Excel中“克隆”相同的数据，可以这样做：选中需要“克隆”的数据范围。单击“编辑”菜单

24、中的“填充”命令，选择“克隆”的方向，这时就可以看到单元格的数据按照你所选择的“克隆”方向进行填充。选择填充命令例9 使用“分列”功能来修改数据要想使用“分列”功能来修改数据，可以这样做：我们在输入一些号码比较长的数值时，最容易多输或漏掉数据，例如：为某学校输入学生的学号时，在数据中间多输入了一个“0”，即把 数据的查找、统计与分析 CHAPTER 6166CHAPTER 6 数据的查找、统计与分析1999001XXX输成19990001XXX，多个数据出现错误，一个个去修改也很费时。这时，我们就可以利用“分列”功能来解决，具体操作如下：（1）首先选中该列数据。（2）单击“数据”菜单中的“分列

25、”命令，弹出“文本分列向导”对话框。选择分列命令（3）选中“固定宽度”复选框，单击“下一步”按钮。在窗口中用鼠标建立两根分列线，一根位于19990后，另一根位于01XXX前，然后单击“下一步”按钮。（4）这时预览窗口中19990列已被选中，这时我们选中窗口右上角的“文本”复选框；然后选中01XXX列也同样将“文本”复选框选中。这时可是关键了，选中0列然后选中“不导入此列(跳过)”复选框。（5）单击“完成”按钮，这时就去掉了多输入的0了。但是现在你还必须将两列的数据合并到一起，如果当前分开的两列是M列和N列。选中O26单元格，单击“插入”菜单中的“函数”命令，弹出“函数”对话框。（6）在“选择类

26、型”框中选中“文本”选项，选中“选择函数”下的“CONCATENATE”选项，单击“确定”按钮。在“TEXT1”框中输入M26，在“TEXT2”框中输入N26，然后单击“确定”按钮，这时O26单元格就显示合并后的数据。将鼠标移到O26单元格右下角，当鼠标变成黑十字时，向下拖拉鼠标到列尾，这样所有的M列和N列将全部合并。通过上面的操作，我们很快地修改了错误数据。数据的查找、统计与分析 CHAPTER 6166167选择函数类型单击“编辑”菜单中的“删除行”命令，出现一个提示窗口，单击“确定”按钮即可。例10 快速互换Excel中两列数据要想快速互换Excel中两列数据，可以这样做：用鼠标选定A列

27、数据区域；把鼠标放在A列数据区域的右边；按下“Shift”键的同时，按下鼠标左键，这时鼠标变为向左的箭头；拖动鼠标至B列数据区域的右边，看到一条垂直的虚线(如果看到一条水平的虚线，表示在B列插入数据)，同时松开“Shift”键和鼠标左键，这样就实现了A、B列的数据互换。同样，也可以实现两行数据的互换。6.3 数据的排序操作例1 数据清单的默认排序顺序在Excel中使用特定的排序顺序，它是根据单元格中的数值而不是格式来排列数据。在排序文本项时，Excel会对数据从左到右逐一进行排序。例如，一个单元格含有文本“ABCD100”,则这个单元格将排在含有文本“ABCD1”单元格的后面，含有文本“ABC

28、D11”单元格的前面。在按升序排序时，Excel会使用如下顺序：数字从最小的负数到最大的正数排序。数据的查找、统计与分析 CHAPTER 6168CHAPTER 6 数据的查找、统计与分析文本以及包含数字的文本，按下列顺序排序：0 1 2 3 4 5 6 7 8 9（空格）!#$%&()*,.:;?_|+A B C D E F G H I J K L M N O P Q R S T U V W X Y Z 撇号()和连字符(-)会被忽略。但例外情况是：如果两个字符串除了连字符不同外其余都相同，则带连字符的文本排在后面。在逻辑值中,False排在True之前，所有错误值的优先级等效，空格排在最后

29、。注意在按降序排序时，除了空格总是在最后外，其他的顺序同升序排序正好相反。例2 根据两列或三列的内容对数据行排序数据清单是包含相关数据的一系列工作表数据行，在数据清单中,升序或降序的排序方法只能够对一列或多列采用同样的排列顺序进行。如果希望根据两列或三列的内容对数据进行排序，你可以按照如下的步骤进行操作：(1)在需要排序的数据清单中，单击任意单元格。(2)打开“数据”菜单，选择“排序”命令，出现“排序”对话框。选择排序(3)在“主要关键字”和“次要关键字”以及“第三关键字”下拉列表框中，选择需要排序的列。数据的查找、统计与分析 CHAPTER 6168169选择关键字顺序(4)如果希望对当前的

30、关键字用升序排序，可以选中“递增”单选按钮。否则，选择“递减”单选按钮。(5)如果当前的数据清单有标题行，则选中“有标题行”单选按钮，这时标题行不参与排序。否则，选中“无标题行”按钮，则第一行的数据同样参加排序。(6)设置完毕，单击“确定”按钮，对数据清单进行排序。(7)如果需要的话，还可以重复前面的步骤，继续对其他数据排序，同时还可以在排序设置里按照我们的需要设置参数。排序选项注意排序时还有一个小技巧：如果在“主要关键字”下拉列表框指定数据列中含有重复的内容，可以通过“次要关键字”下拉列表框指定另一列数据作进一步的排序。如果还有相同的内容，可以通过“第三关键字”下拉列表框指定第三列数据进行排

31、序。数据的查找、统计与分析 CHAPTER 6170CHAPTER 6 数据的查找、统计与分析如果进行排序的数据行是工作表分级显示的一部分，Excel将对最高级分组（第一级）进行排序。这时，即使数据行或列是隐藏的，它们也会保持不变，不被排序。例3 将数据清单按照固定宽度进行分列在一些情况下,待分割数据列中的数据比较整齐,这时我们可以利用固定宽度对数据列进行分割。例如在一个表格中显示了中国人的姓名，中国人姓名的特点是文字紧排，其中没有诸如空格之类的分隔符，因而我们不能用分隔符作为标准进行分列。我们都知道，大多数中国人第一个汉字是姓，后面的汉字是名，因而可以利用固定宽度对之进行分列。你可以按照如下

32、步骤进行操作：（1）选中待分割的列。（2）打开“数据”菜单，选择“分列”命令，这时会启动文本分列向导程序。（3）选中“固定宽度”单选按钮。（4）单击“下一步”按钮，会出现设置字段宽度的对话框。（5）在该对话框中，允许你用鼠标在字段上设置分列线，通过分列线将列分割。（6）单击“预览分列效果”区域，出现一条指向上方的箭头线条，该线条称作分列线。（7）拖动分列线，可以将分列线移动到适当位置。在这里，我们将分列线拖动到分割姓和名的位置，也即第一个文字之后，第二个文字之前的位置。（8）要添加分列线，可以在“预览分列效果”区域继续单击鼠标。（9）如果希望删除分列线，可以双击分列线。（10）单击“下一步”按

33、钮，设置分列格式对话框。（11）你可以在“预览分列效果”区域选中相应的列，然后设置其格式。（12）在“目标区域”文本框中输入分列后数据列的引用地址。（13）单击“高级”按钮，设置一些高级的文本导入设置对话框。（14）按下“完成”按钮，则完成对列的分割。数据的查找、统计与分析 CHAPTER 6170171完成数据分割例4 数据合并计算常见问题以下的建议只适用于由“数据”菜单中“合并计算”命令生成的合并计算，并不适用于使用三维引用生成的合并计算。（1）如果合并后计算的结果不正确，请确认：正确输入了对所有源区域的引用；在“合并计算”对话框中选择了适当的汇总函数；设定的目标区域足够大，能容纳合并计算

34、后的数据。为了避免由于目标区域的形状而产生的问题，创建合并计算数据表时，应单击目标区域的左上角单元格，并保证该单元格右边及下边留有足够的空单元格。（2）如果是按位置进行合并计算，要保证每个源区域中的类似数据以相同的顺序包含在相同的区域中。（3）如果是按类进行合并计算，应保证做到：在源区域中包含了行或列标志；在“合并计算”对话框“标志位置”选项框中，选定了“首行”或“最左列”复选框，(或二者都选)；合并计算设置 数据的查找、统计与分析 CHAPTER 6172CHAPTER 6 数据的查找、统计与分析在所在源区域中以同样的拼写和大小写输入了分类标志(例如，称作“Annual Avg”和“Annu

35、al Average”的标志是不同的，不会被合并计算)。注意不想合并计算的分类具有只出现在一个源区域中独一无二的标志。例5 进行数据分析的常用汇总函数一览函数名称 函数功能Sum 对数值求和，它是数值型源数据的默认函数Count 计算项的数目。汇总函数“Count”的功能与工作表函数“COUNTA”的功能相同。“Count”是除了数值型数据以外其他数据的默认函数Average 求数值平均值Max 求最大值Min 求最小值Product 求数值的乘积CountNums 计算含有数值型数据的行数。汇总函数“Count Nums”的功能与工作表函数“COUNT”的功能相同StdDev 估计样本群的标

36、准偏差。汇总的所有数据为样本群的抽样StdDevp 计算样本群的标准偏差。汇总的所有数据为样本群Var 估计样本群的方差。汇总的所有数据为样本群的抽样Varp 计算样本群的方差。汇总的所有数据为样本群例6 公式中使用数据透视表名称的规则计算字段能够为数据透视表在源数据中引用任何字段和数据项，其中包括在“数据透视表向导”中没有拖动到数据透视区中的字段和数据项。计算字段公式可以引用一个或多个字段名称。计算数据项的每个公式中则只能含有来自创建该数据项字段中的数据项。数据的查找、统计与分析 CHAPTER 6172173在包含多个字段的名称中，字段可以是任何顺序。在包含多个词、数字或符号的名称中可使用

37、单引号标志。公式中不能引用总计。可以在对数据项的引用中包含字段名称。数据项的名称必须用中括号括起来，例如“产品书籍”。当数据透视表中两个不同字段中数据项有同样的名称时，使用这种格式可以避免NAME错误。例如，如果数据透视表中的“产品”字段中有一个叫“书籍”的数据项，“类别”字段中也有一个叫“书籍”的数据项，用“产品书籍”和“类别书籍”的形式引用数据项可以防止NAME错误。在数据透视表中，可以根据当前排序和所显示数据项的位置引用数据项。如“产品1”是“书籍”，“产品2”是“软件”。索引中不包括隐藏的数据项。可以使用相对位置引用数据项。指定的位置相对于包含公式的计算数据项。如果“三季”是当前季度，

38、那么“三季1”代表“二季”，“三季+1”代表“四季”。例如，某一计算数据项可以使用公式“三季13。如果给定的位置在字段的第一个数据项之前或最后一个数据项之后，那么公式会产生REF错误。要关闭错误结果显示，可清除“数据透视表选项”对话框中的“显示出错值”复选框。在计算数据项公式中，如果根据绝对或相对位置引用数据项，那么“数据透视表字段高级选项”对话框中的“自动显示选项”和“自动排序选项”区域下的所有选项都将被重新设置成“人工”选项，而且这些选项都将变为无效。例7 对数据清单的大小和位置的要求（1）避免在一个工作表上建立多个数据清单。因为数据清单的某些处理功能(如筛选等)，一次只能在同一工作表的一

39、个数据清单中使用。（2）在工作表的数据清单与其他数据间至少留出一个空白列和一个空白行。在执行排序、筛选或插入、自动汇总等操作时，这将有利于Excel检测和选定数据清单。（3）避免将关键数据放到数据清单的左右两侧。因为这些数据在筛选数据清单时，可能会被隐藏。（4）避免在数据清单中放置空白列或空白行，这将有利于Excel检测和选定数据清单。数据的查找、统计与分析 CHAPTER 6174CHAPTER 6 数据的查找、统计与分析例8 对列标志的要求（1）在数据清单的第一行里创建列标志。Excel将使用这些标志创建报告，并查找和组织数据。（2）列标志使用的字体、对齐方式、格式、图案、边框及大小写样式

40、，应当与数据清单中其他数据的格式区别。（3）如果要将标志和其他数据分开，应使用单元格边框(而不是空格或短划线)，在标志行下插入一行直线。例9 对行和列内容的要求(1)在设计数据清单时，应使同一列中各行有近似的数据项。(2)在单元格开始处不要插入多余的空格，因为多余的空格会影响排序和查找。(3)不要使用空白行将列标志和第一行数据分开。例10 快速删除工作表中的特定数据如果你在编辑一份产品数量报表时，发现工作表中大量的产品数量都为0，工作表就不太美观。如果删除这些0行不会影响整个报表的情况，怎样才能快速地删除呢？（1）选中特定的区域或整个工作表区域。（2）单击“数据筛选自动筛选”命令，这时选中的区

41、域会出现一些下拉式列表(这是针对Excel来说的)。（3）在下拉列表中选中“0”选项，这时所有的0行将被选中。例11 对重要的数据使用颜色显示对重要的数值使用颜色显示，这样做就可以突出这些数据的重要程度。在Excel中可以通过设置来改变数值的显示颜色，具体操作如下：（1）按住Ctrl键选中所要改变显示颜色的数值。（2）单击“格式”菜单中的“单元格”命令，弹出“单元格格式”对话框。数据的查找、统计与分析 CHAPTER 6174175单元格格式（3）进入“数字”选项卡，在“分类”列表中选中“数值”选项，在“负数”框中选中不带括号的红色的“1234.10”。选中数字（4）你还可以在“小数位数”框中

42、设置小数保留的位数，如果你想用“千位分隔符”来显示当前选中数值，就可选中“使用千位分隔符”复选框。（5）单击“确定”按钮，这时选中的数值都将自动以红色显示。6.4 数据链接与共享处理例1 创建数据绑定电子表格Web部件创建数据绑定电子表格Web部件并将其导入到Web部件页的过程由以下四个主要步骤组成：（1）指定数据源并将数据导入到Excel。数据的查找、统计与分析 CHAPTER 6176CHAPTER 6 数据的查找、统计与分析（2）指定布局和公式，将该定义另存为XML电子表格文件(.xml)，并保存到要在其中使用电子表格Web部件的同一Windows SharePoint Services

43、服务器上的文档库中。（3）为Web部件创建解决方案说明文件(.xml)，并将其保存到要在其中使用电子表格Web部件的同一Windows SharePoint Services服务器上的文档库中。（4）为Web部件创建Web部件定义文件(.dwp)，并将其导入到Web部件页或Web部件目录。注意继续操作之前，请确保可访问保存XML电子表格和解决方案说明文件的文档库。由于这些文件并未默认为由你的Web部件的用户进行编辑，你需要在单独的文档库中保存这些文件以与服务器上的其他文档区分开。如果无权创建自己的文档库，请与网站管理员联系以获取帮助。例2 在网页上创建Web脚本要想在网页上创建Web脚本，可以

44、这样做：首先请将“插入脚本”命令添加到“工具”菜单（如果尚未执行此操作）。下面是具体操作方法：（1）在“工具”菜单上单击“自定义”命令，再单击“命令”选项卡。（2）在“类别”框中单击“工具”。（3）将“插入脚本”从“命令”框拖动到“工具”菜单上。拖动插入脚本命令 数据的查找、统计与分析 CHAPTER 6176177（4）当“工具”菜单显示菜单命令时，指向“宏”子菜单。（5）当“宏”子菜单显示菜单命令时，指向希望“插入脚本”在菜单上显示的位置，再放开鼠标键。（6）在“自定义”对话框中，单击“关闭”按钮。（7）然后在要添加脚本的网页中单击。执行“工具宏插入脚本”命令。使用“Microsoft脚本

45、编辑器”(Microsoft脚本编辑器：用于在数据访问页中添加文本、编辑HTML标记以及编辑任何Microsoft Visual Basic Scripting Edition(VBScript)代码。也可像在Web浏览器中一样，在脚本编辑器中查看页)编写脚本。（8）现在返回到Office程序。若要将更改更新到网页中，请单击“刷新”按钮。例3 查看或编辑Web脚本要想查看或编辑Web脚本，首先需要将“显示所有脚本”命令添加到“工具”菜单。单击“工具宏Microsoft脚本编辑器”命令。Microsoft脚本编辑器如果已在“Microsoft脚本编辑器”中编辑脚本，请返回到Office程序，再通

46、过单击“刷新”工具栏上的“刷新”按钮来更新网页。注意若要快速查看或编辑Web脚本，请双击网页上的脚本定位标记(脚本定位标记:在Microsoft Offi ce程序中打开的网页上脚本的可视表示形式。默认情况下不显示脚本定位标记。不同脚本定位标记表示以不同脚本语言编写的脚本)。数据的查找、统计与分析 CHAPTER 6178CHAPTER 6 数据的查找、统计与分析例4 保存在其他工作表中使用的Web查询要想保存在其他工作表中使用的Web查询，可以这样做：Web查询(用于检索存储在Intranet或Internet中的数据的查询)在当前工作表中使用时会自动保存。以.iqy文件的方式保存查询可使其

47、在不同的工作表中使用或者与其他用户共享。具体方法是：单击“数据导入外部数据新建Web查询”以创建一个新的查询，或者单击“外部数据”工具栏上的“编辑查询”按钮以编辑一个现有的查询。如果正在创建一个新的查询，可在“地址”框中定位所需页面，再单击要选择的表格旁边的向右指的黄色箭头。如果页面中表格的旁边没有，请单击对话框顶部的“显示图标”以显示图标。单击“导入”按钮，在“保存位置”框中将文件夹定位到要保存查询的位置。在“文件名称”框中输入文件名称，单击“保存”按钮。若要在其他工作表中运行该查询，请单击“数据导入外部数据导入数据”命令，定位并打开.iqy文件。导入数据源例5 解决共享工作簿中的修订冲突当

48、两个用户试图保存影响同一单元格的修订时，Excel为其中一个用户显示“解决冲突”对话框。如何才能解决这种问题？在“解决冲突”对话框中，可看到有关每一次修订以及其他用户所造成的修订冲突的信息。若要保留自己的修订或其他人的修订并转到下一个修订冲突上，请单击“接受本用户”或“接受其他用户”按钮。数据的查找、统计与分析 CHAPTER 6178179若要保留自己的所有剩余修订或所有其他用户的修订，请单击“全部接受本用户”或“全部接受其他用户”按钮。若要使自己的修订覆盖所有其他用户的修订，而且不再看到“解决冲突”对话框，请关闭此功能。操作方法：首先在“工具”菜单上，单击“共享工作簿”命令，然后单击“高级

49、”选项卡。然后单击“选用正在保存的修订”复选框。最后单击“确定”按钮。若要查看自己或其他人如何解决以前的冲突，可在冲突日志工作表中查看。操作方法如下：（1）单击“工具修订突出显示修订”命令。突出显示修订设置（2）在“时间”框中，单击“全部”按钮。（3）清除“修订人”和“位置”复选框。（4）选中“在新工作表上显示修订”复选框，再单击“确定”按钮。（5）在“冲突日志工作表”上，滚动到右边以查看“操作类型”和“操作失败”列。保留的修订冲突在“操作类型”列有“成功”字样。“操作失败”列中的行号用于标识记录有未保存的修订冲突信息的行，包括任何删除的数据。注意若要保存包含所有修订的工作簿的副本，请单击“解

50、决冲突”对话框中的“取消”按钮，再单击“文件”菜单上的“另存为”命令，然后为该文件键入新名称。数据的查找、统计与分析 CHAPTER 6180CHAPTER 6 数据的查找、统计与分析例6 将Excel数据放到网页上要想将Excel数据放到网页上，可以这样做：（1）打开或选择含有要发布的项的工作簿。（2）在“文件”菜单上，单击“另存为网页”命令。点选“另存为网页”命令（3）如果已选择要发布的项，或者要非交互式发布整个工作簿，请在“另存为网页”对话框中选择所需选项，单击“保存”按钮，这样就完成了操作。否则，请继续按下列步骤操作:（4）单击“发布”按钮。（5）在“发布内容”的“选择”框中，单击要发