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

第六章 数据的查找、统计与分析 6.1 数据的查找与筛选 6.2 数据的统计与计算 6.3 数据的排序操作 6.4 数据链接与共享处理 数据的查找、统计与分析 CHAPTER 6154CHAPTER 6 数据的查找、统计与分析 6.1 数据的查找与筛选 例1 保护当前工作表中的重要数据 除了对工作簿设置密码外，我们还可以对当前的工作表中的单元格或 单元格区域中重要数据进行锁定保护。具体操作如下： （1）首先，选中你需要保护的单元格或单元格区域。 （2）单击“格式”菜单中的“单元格”命令，弹出“单元格格式”对 话框。 （3）选中“保护”选项卡，在窗口中选中“锁定”复选框，单击“确 定”按钮关闭对话框。 “保护”选项 卡设置 （4）单击“工具保护保护工作表”命令，在“取消工作表保护时 使用的密码”框中键入你所设置的密码，单击“确定”按钮。这时所选中 的单元格或单元格区域将被锁定，只有键入密码后才可以解除锁定。 例2 使用“批注”功能 对编辑好的数据，如果你想对其中某个特定的内容加以说明，或者是 重要的地方留下记号。这时，可以使用Excel提供的“批注”功能。如何使 用“批注”功能呢？ 具体的操作如下： （1）首先，在工作表中选中一个空白单元格。 （2）单击“插入”菜单中的“批注”命令，弹出一个类似文本框的输 入框。 数据的查找、统计与分析 CHAPTER 6154155 插入批注 （3）在这里可以输入提示和说明的信息，然后单击需要批注的单元 格，在这个单元格中将会出现一个箭头，把鼠标移动到该单元格中，就会 看到刚才我们输入的批注信息。 （4）我们还可以对批注进行修改，选中该单元格单击鼠标右键，在快 捷菜单中选中“编辑批注”选项，将会显示刚才设置的类似文本框的输入 框，重新对内容进行修改即可。 如果你想删除批注，只要在弹出的快捷菜单中选中“删除批注”选项 即可。 例3 使用“拼写检查”功能来检查文本 要想使用“拼写检查”功能来检查文本，可以这样做： （1）单击“工具”菜单中的“拼写检查”命令，Excel就会自动对当 前的工作表中的内容进行拼写检查，在错误的地方会弹出“拼写检查”对 话框。 拼写错误提示 数据的查找、统计与分析 CHAPTER 6156CHAPTER 6 数据的查找、统计与分析 （2）在“不在词典中”框中显示错误的单词，下面“建议”框中显示 了Excel默认的词典中的所有单词，你可以在“建议”框中选择正确的单 词，然后单击“更改”按钮，可以把当前的词替换为正确的单词。你也可 以单击“忽略”按钮，可以忽略此处错误。 例4 快速修复Excel文件数据 我们用Excel存储或分析较大的数据量时，出现了无效页面错误，确定 后应用程序关闭，每次打开此文件都是如此。怎样修复出现的问题？ 这主要由下面原因引起： 首先考虑是操作系统引起。重新启动机器，用Excel打开其他文件没有 问题，但是打开此文件还是出现无效页面错误。排除操作系统引起原因。 如果打开别的文件没有问题，说明问题出在此文件上。用杀毒软件进 行病毒扫描，如果没有发现病毒，排除病毒引起原因。 此时，我们还有最后一个方法。利用MS SQL SERVER7.0修复。它 是一个很不错的修复软件。 按照其数据转换服务导入向导(Data Trans formation Services Import Wizard)，选择好源驱动程序(Microsoft Excel)和文件名(Excel文 件)以及目标驱动程序(Microsoft OLE DB Provider for SQL Server) 和数据库名，导入成功后，发现Excel文件中的每一个工作表都转换成SQL 数据库中的数据表，并看出一个数据表的表名与其他的不一样，带有单引 号，并且不能浏览数据，怀疑是此问题引起的，将其单引号去掉，就能浏 览数据。将其导出，此Excel文件制作完成了。 例5 在Excel中实现数据合并和汇总 要想在Excel中实现数据合并和汇总，可以这样做： （1）首先，选中需要汇总的数据单元格。 （2）单击“数据”菜单中的“排序”命令，给数据进行排序。然后，单 击“数据”菜单中的“分类汇总”命令，这样就可以对数据进行汇总了。 数据的查找、统计与分析 CHAPTER 6156157 排序设置 如果你想对数据进行重新排序，必须清除上面的汇总。这样，再对数 据进行重新排序和汇总。 例6 将一张表格中的数据筛选到另一表格 要想将一张表格中的数据筛选到另一表格，可以这样做： 我们可以使用Excel中的“高级筛选”功能将一张表格中的数据筛选到 另一张表格中。 （1）首先在可用作条件区域的数据清单上插入至少三个空白行，条件 区域必须具有列标题。但是要在条件值与数据清单之间至少留一个空行。 （2）在列标题下面的一行中，键入所要匹配的条件。如果要查找一些 字符相同但其他字符不一定相同的文本值，则可以使用通配符。 （3）单击数据清单中的单元格。然后，单击“数据筛选高级筛 选”命令。如果要通过隐藏不符合条件的数据行来筛选数据清单，可单击 “在原有区域显示筛选结果”项。 高级筛选设置 数据的查找、统计与分析 CHAPTER 6158CHAPTER 6 数据的查找、统计与分析 （4）如果想将符合条件的数据行复制到工作表的其他位置来筛选数 据清单，请单击“将筛选结果复制到其他位置”项，单击“复制到”编辑 框，然后单击粘贴区域的左上角即可。 （5）在“条件区域”编辑框中，输入条件区域的引用，并包括条件标 志。如果要在选择条件区域时将“高级筛选”对话框移走，请单击“压缩 对话框”按钮。 例7 在每次结束数据库操作时自动压缩 要想在每次结束数据库操作时自动压缩，具体操作如下： （1）单击“工具”菜单中的“选项”命令，打开“选项”对话框。 （2）在窗口中选中“常规”选项卡，将“关闭时压缩”复选框选中， 然后单击“确定”按钮即可。 例8 使用“高级筛选”功能快速查找数据 使用“高级筛选”功能可以一次性把你想要的数据全部找出来。具体 操作如下： （1）首先，在工作表中设置一个条件区域，在第一行中输入排序的字 段名称，在第二行中输入想查找的条件，建立条件区域。 （2）选中工作表中的所有的数据区域，单击“数据筛选高级筛 选”命令，弹出“高级筛选”对话框。 选择高级筛选 （3）在“数据区域”中Excel自动选择好了筛选的区域，将光标移到 “条件区域”框中，单击框中右边的按钮，弹出一个新的对话框，用鼠标 选中你建立的条件区域。然后，再单击按钮回到“高级筛选”对话框。 数据的查找、统计与分析 CHAPTER 6158159 （4）单击“确定”按钮，现在工作表中就是你希望看到的结果了。 例9 将数据库与Excel结合使用 Excel强大的打印设置，几乎可以打印出任何你想要的效果，只要把你 想要的数据库用Excel打开就行了(按住Shift+点鼠标右键，在打开方式里 选Excel，确定)。然后拖动你的鼠标，把表格变成你想要的样子就行了。 这里需要说明一下的是，如果原来的数据库太大，而你只想要其中一 部分的话，数据库的功能还是要充分利用的，就像COPY TO D:TEMP1. DBF FOR E23ca.xmm = “XX“ FIELDS xmm,bzh,bbb,dai,ggg 。 TYPE FOXPLUS这种语句我还是经常要用到的。不习惯用命令？ 也没关系，文件菜单里有个“导出”就是用来完成这个任务的。选择文件 类型时别忘了选Dbase，然后随便敲个名字作为导出文件。选项要认真填 写，范围ALL代表全部；For当然是你所需要的数据必须满足的条件，比如 name =“，如果你不想用手工输入，也可以通过下拉列表选择来完成。 6.2 数据的统计与计算 例1 行号统计法进行“数据统计” 统计各科成绩中各分数段的人数，即统计90100，8089，7079， 6069，60分以下的人数。 将光标移动到成绩所在列，用鼠标单击工具栏中的“降序”按钮，成 绩由高到低排列。90100分数段中最低分数是90分，所在行行号为11，则 此分数段人数是11-110。在8089分数段所在行的行号在12到24之间， 则8089分数段的人数是13。同样方法可统计出其他分数段的人数。 如果数据不在一列，而是在同一行内，能否迅速进行统计呢？那也难 不住，可以用行列转换法将行内数据转换到列内，然后再进行统计。具体 操作是：选中行中有关数据，单击工具栏中的“复制”按钮，再在目标列 内右键单击，选择弹出菜单中的“选择性粘贴”命令，在打开的“选择性 粘贴”对话框中选中“转置”前的复选框，单击“确定”退出，则数据都 转换到一列内，根据行号统计法进行统计即可。 数据的查找、统计与分析 CHAPTER 6160CHAPTER 6 数据的查找、统计与分析 例2 选中统计法进行“数据统计” 上例中在统计8089分数段人数时，也可以这样进行。首先进行排 序，然后从最高分89分开始选中，向下拖动鼠标，到最低分81分结束，在 拖动鼠标的过程中，工具栏的左下角或选中部分的右下角出现一变动的提 示条为“13R1C”，意指选中的部分为17行，1列，则统计得8089分数 段的人数为13。 注意 此方法也适合在同一行内进行。 例3 筛选法进行“数据统计” 统计各分数段人数时首先要进行排序，打乱了原来的次序。若不打乱 原次序能否进行统计呢？当然可以了！ 单击“数据筛选自动筛选”，则进入自动筛选状态，工作表的 第一行都有一个向下的小箭头，单击“成绩”单元格中的下拉箭头，选择 “自定义”，打开“自定义自动筛选方式”对话框，设置为“小于或等于 99”与“大于或等于90”，单击“确定”按钮退出，则只显示出成绩在 9099之间的学生。 注意 此时显示的行号是每位同学原来实际所在的行号，不能根 据“行号统计法”来统计人数的多少，而且也不能采用“选中统 计法”来统计人数，那么如何统计人数呢？如果人数较少，逐个 数一下就可以了，若人数较多，先选中所有符合条件的人数，单 击工具栏中的“复制”按钮，选择另一个工作表，单击“粘贴” 按钮，则将所有人数复制到一个新的工作表中，此时根据行号就 可以快速统计出人数。依此方法可统计出其他分数段的人数。 数据的查找、统计与分析 CHAPTER 6160161 例4 查找法进行“数据统计” 前面介绍的数据统计是在一行或一列中进行操作，如果要在多列即 某一单元格区域内进行统计，那可使用查找的方法。如要统计各科成绩 中59分的人数，操作步骤是：先打开“查找”对话框，在查找项中输入 “59”，单击“查找下一个”按钮一次，可找到一个59分，继续查找，就 可快速确定成绩是59分的人数，记下查找到的“59”的次数就是最后的统 计人数了。 例5 函数统计法进行“数据统计” 统计函数较多，主要介绍常用的以下几个。 1.COUNT函数 返回参数的个数。利用函数COUNT可以计算数组或单元格区域中数字 项的个数。 语法：COUNT（value1，value2，.） value1、value2、是包含或引用各种类型数据的参数（1-30 个），但只有数字类型的数据才被计数。 如统计参加考试的学生人数：COUNT（B2:B47）等于46。 2.COUNTA函数 返回参数组中非空值的数目。利用COUNTA函数可以计算数组或单元 格区域中数据项的个数。 语法：COUNTA（value1，value2， .） value1、value2、所要计数的值，参数个数为1-30个。 例如，统计参加考试的学生人数：COUNTA（B2:B47）。 注意 COUNT与COUNTA的区别：函数COUNT在计数时，将 把数字、空值、逻辑值、日期或以文字代表的数计算进去；但 是错误值或其他无法转化成数字的文字则被忽略。而COUNTA 的参数值可以是任何类型，它们可以包括空字符，但不包括空 白单元格。 数据的查找、统计与分析 CHAPTER 6162CHAPTER 6 数据的查找、统计与分析 3.COUNTIF函数 计算给定区域内满足特定条件的单元格的数目。 语法：COUNTIF（range，criteria） Range：为需要计算其中满足条件的单元格数目的单元格区域。 Criteria：为确定哪些单元格将被计算在内的条件，其形式可以为 数字、表达式或文本。例如，条件可以表示为“80”、“80”。 如统计政治课考试成绩中及格的人数。 COUNTIF（B2:B47，“=80“）等于13。 4.FREQUENCY函数 频率统计分布函数，以一列垂直数组返回某个区域中数据的频率分 布。例如：使用函数FREQUENCY可以计算在给定的值集和接收区间内， 每个区间内的数据数目。由于函数FREQUENCY返回一个数组，必须以数 组公式的形式输入。 语法：FREQUENCY（data_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为输出区域，输入公式 =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 6162163 MIN 返回给定参数表中的最小值。 语法：MIN（number1，number2， .） number1，number2，是要从中找出最小值的1到30个数字参数。 参数可以是数字、空白单元格、逻辑值或表示数值的文字串。如果参 数中有错误值或无法转换成数值的文字时，将引起错误。 利用这两个函数可统计出各科成绩的最高分和最低分。 举例： 如果A1:A5包含数字 99、87、59、27 和 32，则： MAX（A1:A5）等于99 MIN（A1:A5）等于27。 例6 在Excel中实现复杂数据的排序 在Excel编辑过程中你经常会遇到这种情况：340008265(机箱类 型)100(台)入库号100295，这时要对入库的设备进行排序，如果按入库号进 行排序，该怎样操作？ 虽然Excel的排序功能强大，但是遇到这种情况它也无处着手了。这时 候你就需要利用“分列”功能来解决这样的问题，具体操作如下： （1）选中要排序的列，单击“数据”菜单中的“分列”命令，弹出 “文本分列向导”对话框。 文本分列向导 （2）这里提供了两种分列形式，分为“分隔符号”和“固定宽度”两 种，你可以视情况进行选择。这里选中“分隔符号”复选框，也是系统默 认的形式，然后单击“下一步”按钮。 数据的查找、统计与分析 CHAPTER 6164CHAPTER 6 数据的查找、统计与分析 （3）在“分隔符号”框中有五种形式提供我们进行分列，但是由于这 个数据本身没有任何的分号、逗号等，所以在这里就需要选这些复选框。因 为我们是要对入库号进行排序，所在只要选中“其他”复选框，在框中输入 “号”字，然后单击“下一步”按钮，在对话框中单击“完成”按钮。 分隔符号选择 这时，入库号100295所在列即被分离为另一列，现在你就可以按入库 号进行排序了。 例7 快速输入数据序列 在表格中输入证件序号、项目序号或日期序号等一些特殊的数据系 列，一般都是手工完成的。如果你想删除其中的一行，又想对以下的号码 进行修改，真是费时又费力。如何才能解决这种问题？ 我们可以使用“自动填充”功能来完成这些操作，它不但操作起来 方便、快速，而且修改起来也挺方便的。下面就来介绍一下具体的操作 过程： （1）在第一个单元格中输入起始数据，在下一个单元格中输入接下来 的第二个数据。 （2）选中这两个单元格，将鼠标移到单元格右下方，当鼠标指针变成 黑十字架时，按住鼠标左键沿着填充的方向拖动。拖过的单元格将会自动 按规定的序列进行填充，如果你想删除其中的某一行，那么下面的序列将 自动改变。 （3）如果你经常使用某些有规律的数据序列的话，你可以单击“工 具”菜单中的“选项”命令，选中“自定义序列”选项卡，在“输入序 列”框中输入新的序列，然后单击“添加”按钮并确定。这里需要注意的 是新序列各项之间要输入半角符号的逗号加以分隔。 数据的查找、统计与分析 CHAPTER 6164165 自定义序列设置 例8 在Excel中“克隆”相同的数据 要想在Excel中“克隆”相同的数据，可以这样做： 选中需要“克隆”的数据范围。单击“编辑”菜单中的“填充”命 令，选择“克隆”的方向，这时就可以看到单元格的数据按照你所选择的 “克隆”方向进行填充。 选择填充命令 例9 使用“分列”功能来修改数据 要想使用“分列”功能来修改数据，可以这样做： 我们在输入一些号码比较长的数值时，最容易多输或漏掉数据，例 如：为某学校输入学生的学号时，在数据中间多输入了一个“0”，即把 数据的查找、统计与分析 CHAPTER 6166CHAPTER 6 数据的查找、统计与分析 1999001XXX输成19990001XXX，多个数据出现错误，一个个去修改也很 费时。这时，我们就可以利用“分列”功能来解决，具体操作如下： （1）首先选中该列数据。 （2）单击“数据”菜单中的“分列”命令，弹出“文本分列向导”对 话框。 选择分列命令 （3）选中“固定宽度”复选框，单击“下一步”按钮。在窗口中用鼠 标建立两根分列线，一根位于19990后，另一根位于01XXX前，然后单击 “下一步”按钮。 （4）这时预览窗口中19990列已被选中，这时我们选中窗口右上角的 “文本”复选框；然后选中01XXX列也同样将“文本”复选框选中。这时 可是关键了，选中0列然后选中“不导入此列(跳过)”复选框。 （5）单击“完成”按钮，这时就去掉了多输入的0了。但是现在你还 必须将两列的数据合并到一起，如果当前分开的两列是M列和N列。选中 O26单元格，单击“插入”菜单中的“函数”命令，弹出“函数”对话框。 （6）在“选择类型”框中选中“文本”选项，选中“选择函数”下的 “CONCATENATE”选项，单击“确定”按钮。在“TEXT1”框中输入 M26，在“TEXT2”框中输入N26，然后单击“确定”按钮，这时O26单元 格就显示合并后的数据。将鼠标移到O26单元格右下角，当鼠标变成黑十字 时，向下拖拉鼠标到列尾，这样所有的M列和N列将全部合并。 通过上面的操作，我们很快地修改了错误数据。 数据的查找、统计与分析 CHAPTER 6166167 选择函数类型 单击“编辑”菜单中的“删除行”命令，出现一个提示窗口，单击 “确定”按钮即可。 例10 快速互换Excel中两列数据 要想快速互换Excel中两列数据，可以这样做： 用鼠标选定A列数据区域；把鼠标放在A列数据区域的右边；按下 “Shift”键的同时，按下鼠标左键，这时鼠标变为向左的箭头；拖动鼠标 至B列数据区域的右边，看到一条垂直的虚线(如果看到一条水平的虚线， 表示在B列插入数据)，同时松开“Shift”键和鼠标左键，这样就实现了 A、B列的数据互换。同样，也可以实现两行数据的互换。 6.3 数据的排序操作 例1 数据清单的默认排序顺序 在Excel中使用特定的排序顺序，它是根据单元格中的数值而不是格 式来排列数据。在排序文本项时，Excel会对数据从左到右逐一进行排序。 例如，一个单元格含有文本“ABCD100”,则这个单元格将排在含有文本 “ABCD1”单元格的后面，含有文本“ABCD11”单元格的前面。 在按升序排序时，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之前，所有错误值的优先级等效，空格排 在最后。 注意 在按降序排序时，除了空格总是在最后外，其他的顺序同 升序排序正好相反。 例2 根据两列或三列的内容对数据行排序 数据清单是包含相关数据的一系列工作表数据行，在数据清单中,升 序或降序的排序方法只能够对一列或多列采用同样的排列顺序进行。如果 希望根据两列或三列的内容对数据进行排序，你可以按照如下的步骤进行 操作： (1)在需要排序的数据清单中，单击任意单元格。 (2)打开“数据”菜单，选择“排序”命令，出现“排序”对话框。 选择排序 (3)在“主要关键字”和“次要关键字”以及“第三关键字”下拉列表 框中，选择需要排序的列。 数据的查找、统计与分析 CHAPTER 6168169 选择关键字顺序 (4)如果希望对当前的关键字用升序排序，可以选中“递增”单选按 钮。否则，选择“递减”单选按钮。 (5)如果当前的数据清单有标题行，则选中“有标题行”单选按钮，这 时标题行不参与排序。否则，选中“无标题行”按钮，则第一行的数据同 样参加排序。 (6)设置完毕，单击“确定”按钮，对数据清单进行排序。 (7)如果需要的话，还可以重复前面的步骤，继续对其他数据排序，同 时还可以在排序设置里按照我们的需要设置参数。 排序选项 注意 排序时还有一个小技巧：如果在“主要关键字”下拉列表 框指定数据列中含有重复的内容，可以通过“次要关键字”下拉 列表框指定另一列数据作进一步的排序。如果还有相同的内容， 可以通过“第三关键字”下拉列表框指定第三列数据进行排序。 数据的查找、统计与分析 CHAPTER 6170CHAPTER 6 数据的查找、统计与分析 如果进行排序的数据行是工作表分级显示的一部分，Excel将对最高级 分组（第一级）进行排序。这时，即使数据行或列是隐藏的，它们也会保 持不变，不被排序。 例3 将数据清单按照固定宽度进行分列 在一些情况下,待分割数据列中的数据比较整齐,这时我们可以利用固 定宽度对数据列进行分割。例如在一个表格中显示了中国人的姓名，中国 人姓名的特点是文字紧排，其中没有诸如空格之类的分隔符，因而我们不 能用分隔符作为标准进行分列。 我们都知道，大多数中国人第一个汉字是姓，后面的汉字是名，因而 可以利用固定宽度对之进行分列。 你可以按照如下步骤进行操作： （1）选中待分割的列。 （2）打开“数据”菜单，选择“分列”命令，这时会启动文本分列向 导程序。 （3）选中“固定宽度”单选按钮。 （4）单击“下一步”按钮，会出现设置字段宽度的对话框。 （5）在该对话框中，允许你用鼠标在字段上设置分列线，通过分列线 将列分割。 （6）单击“预览分列效果”区域，出现一条指向上方的箭头线条，该 线条称作分列线。 （7）拖动分列线，可以将分列线移动到适当位置。在这里，我们将分 列线拖动到分割姓和名的位置，也即第一个文字之后，第二个文字之前的 位置。 （8）要添加分列线，可以在“预览分列效果”区域继续单击鼠标。 （9）如果希望删除分列线，可以双击分列线。 （10）单击“下一步”按钮，设置分列格式对话框。 （11）你可以在“预览分列效果”区域选中相应的列，然后设置其格式。 （12）在“目标区域”文本框中输入分列后数据列的引用地址。 （13）单击“高级”按钮，设置一些高级的文本导入设置对话框。 （14）按下“完成”按钮，则完成对列的分割。 数据的查找、统计与分析 CHAPTER 6170171 完成数据分割 例4 数据合并计算常见问题 以下的建议只适用于由“数据”菜单中“合并计算”命令生成的合并 计算，并不适用于使用三维引用生成的合并计算。 （1）如果合并后计算的结果不正确，请确认： 正确输入了对所有源区域的引用； 在“合并计算”对话框中选择了适当的汇总函数； 设定的目标区域足够大，能容纳合并计算后的数据。为了避免由于目 标区域的形状而产生的问题，创建合并计算数据表时，应单击目标区域的 左上角单元格，并保证该单元格右边及下边留有足够的空单元格。 （2）如果是按位置进行合并计算，要保证每个源区域中的类似数据以 相同的顺序包含在相同的区域中。 （3）如果是按类进行合并计算，应保证做到： 在源区域中包含了行或列标志； 在“合并计算”对话框“标志位置”选项框中，选定了“首行”或 “最左列”复选框，(或二者都选)； 合并计算设置 数据的查找、统计与分析 CHAPTER 6172CHAPTER 6 数据的查找、统计与分析 在所在源区域中以同样的拼写和大小写输入了分类标志(例如，称作 “Annual Avg”和“Annual Average”的标志是不同的，不会被合并计算)。 注意 不想合并计算的分类具有只出现在一个源区域中独一无二 的标志。 例5 进行数据分析的常用汇总函数一览 函数名称 函数功能 Sum 对数值求和，它是数值型源数据的默认函数 Count 计算项的数目。汇总函数“Count”的功能与工作表 函数“COUNTA”的功能相同。“Count”是除了数值型数据以外其他数 据的默认函数 Average 求数值平均值 Max 求最大值 Min 求最小值 Product 求数值的乘积 CountNums 计算含有数值型数据的行数。汇总函数“Count Nums”的功能与工作表函数“COUNT”的功能相同 StdDev 估计样本群的标准偏差。汇总的所有数据为样本群的 抽样 StdDevp 计算样本群的标准偏差。汇总的所有数据为样本群 Var 估计样本群的方差。汇总的所有数据为样本群的抽样 Varp 计算样本群的方差。汇总的所有数据为样本群 例6 公式中使用数据透视表名称的规则 计算字段能够为数据透视表在源数据中引用任何字段和数据项，其中 包括在“数据透视表向导”中没有拖动到数据透视区中的字段和数据项。 计算字段公式可以引用一个或多个字段名称。计算数据项的每个公式 中则只能含有来自创建该数据项字段中的数据项。 数据的查找、统计与分析 CHAPTER 6172173 在包含多个字段的名称中，字段可以是任何顺序。在包含多个词、数 字或符号的名称中可使用单引号标志。公式中不能引用总计。 可以在对数据项的引用中包含字段名称。数据项的名称必须用中括号 括起来，例如“产品书籍”。当数据透视表中两个不同字段中数据项 有同样的名称时，使用这种格式可以避免NAME错误。例如，如果数据 透视表中的“产品”字段中有一个叫“书籍”的数据项，“类别”字段中 也有一个叫“书籍”的数据项，用“产品书籍”和“类别书籍” 的形式引用数据项可以防止NAME错误。 在数据透视表中，可以根据当前排序和所显示数据项的位置引用数据 项。如“产品1”是“书籍”，“产品2”是“软件”。索引中不 包括隐藏的数据项。 可以使用相对位置引用数据项。指定的位置相对于包含公式的计算数 据项。如果“三季”是当前季度，那么“三季1”代表“二季”， “三季+1”代表“四季”。例如，某一计算数据项可以使用公式“三 季13。如果给定的位置在字段的第一个数据项之前或最后一个 数据项之后，那么公式会产生REF错误。要关闭错误结果显示，可清除 “数据透视表选项”对话框中的“显示出错值”复选框。 在计算数据项公式中，如果根据绝对或相对位置引用数据项，那么 “数据透视表字段高级选项”对话框中的“自动显示选项”和“自动排序 选项”区域下的所有选项都将被重新设置成“人工”选项，而且这些选项 都将变为无效。 例7 对数据清单的大小和位置的要求 （1）避免在一个工作表上建立多个数据清单。因为数据清单的某些处 理功能(如筛选等)，一次只能在同一工作表的一个数据清单中使用。 （2）在工作表的数据清单与其他数据间至少留出一个空白列和一个空 白行。在执行排序、筛选或插入、自动汇总等操作时，这将有利于Excel检 测和选定数据清单。 （3）避免将关键数据放到数据清单的左右两侧。因为这些数据在筛选 数据清单时，可能会被隐藏。 （4）避免在数据清单中放置空白列或空白行，这将有利于Excel检测 和选定数据清单。 数据的查找、统计与分析 CHAPTER 6174CHAPTER 6 数据的查找、统计与分析 例8 对列标志的要求 （1）在数据清单的第一行里创建列标志。Excel将使用这些标志创建 报告，并查找和组织数据。 （2）列标志使用的字体、对齐方式、格式、图案、边框及大小写样 式，应当与数据清单中其他数据的格式区别。 （3）如果要将标志和其他数据分开，应使用单元格边框(而不是空格或 短划线)，在标志行下插入一行直线。 例9 对行和列内容的要求 (1)在设计数据清单时，应使同一列中各行有近似的数据项。 (2)在单元格开始处不要插入多余的空格，因为多余的空格会影响排序 和查找。 (3)不要使用空白行将列标志和第一行数据分开。 例10 快速删除工作表中的特定数据 如果你在编辑一份产品数量报表时，发现工作表中大量的产品数量都 为0，工作表就不太美观。如果删除这些0行不会影响整个报表的情况，怎 样才能快速地删除呢？ （1）选中特定的区域或整个工作表区域。 （2）单击“数据筛选自动筛选”命令，这时选中的区域会出现一 些下拉式列表(这是针对Excel来说的)。 （3）在下拉列表中选中“0”选项，这时所有的0行将被选中。 例11 对重要的数据使用颜色显示 对重要的数值使用颜色显示，这样做就可以突出这些数据的重要程 度。在Excel中可以通过设置来改变数值的显示颜色，具体操作如下： （1）按住Ctrl键选中所要改变显示颜色的数值。 （2）单击“格式”菜单中的“单元格”命令，弹出“单元格格式”对 话框。 数据的查找、统计与分析 CHAPTER 6174175 单元格格式 （3）进入“数字”选项卡，在“分类”列表中选中“数值”选项，在 “负数”框中选中不带括号的红色的“1234.10”。 选中数字 （4）你还可以在“小数位数”框中设置小数保留的位数，如果你想用 “千位分隔符”来显示当前选中数值，就可选中“使用千位分隔符”复选框。 （5）单击“确定”按钮，这时选中的数值都将自动以红色显示。 6.4 数据链接与共享处理 例1 创建数据绑定电子表格Web部件 创建数据绑定电子表格Web部件并将其导入到Web部件页的过程由以 下四个主要步骤组成： （1）指定数据源并将数据导入到Excel。 数据的查找、统计与分析 CHAPTER 6176CHAPTER 6 数据的查找、统计与分析 （2）指定布局和公式，将该定义另存为XML电子表格文件(.xml)， 并保存到要在其中使用电子表格Web部件的同一Windows SharePoint Services服务器上的文档库中。 （3）为Web部件创建解决方案说明文件(.xml)，并将其保存到要在其 中使用电子表格Web部件的同一Windows SharePoint Services服务器 上的文档库中。 （4）为Web部件创建Web部件定义文件(.dwp)，并将其导入到Web部 件页或Web部件目录。 注意 继续操作之前，请确保可访问保存XML电子表格和解决方 案说明文件的文档库。由于这些文件并未默认为由你的Web部件 的用户进行编辑，你需要在单独的文档库中保存这些文件以与服 务器上的其他文档区分开。如果无权创建自己的文档库，请与网 站管理员联系以获取帮助。 例2 在网页上创建Web脚本 要想在网页上创建Web脚本，可以这样做： 首先请将“插入脚本”命令添加到“工具”菜单（如果尚未执行此操 作）。下面是具体操作方法： （1）在“工具”菜单上单击“自定义”命令，再单击“命令”选项卡。 （2）在“类别”框中单击“工具”。 （3）将“插入脚本”从“命令”框拖动到“工具”菜单上。 拖动插入脚 本命令 数据的查找、统计与分析 CHAPTER 6176177 （4）当“工具”菜单显示菜单命令时，指向“宏”子菜单。 （5）当“宏”子菜单显示菜单命令时，指向希望“插入脚本”在菜单 上显示的位置，再放开鼠标键。 （6）在“自定义”对话框中，单击“关闭”按钮。 （7）然后在要添加脚本的网页中单击。执行“工具宏插入脚本” 命令。使用“Microsoft脚本编辑器”(Microsoft脚本编辑器：用于在数 据访问页中添加文本、编辑HTML标记以及编辑任何Microsoft Visual Basic Scripting Edition(VBScript)代码。也可像在Web浏览器中一样， 在脚本编辑器中查看页)编写脚本。 （8）现在返回到Office程序。若要将更改更新到网页中，请单击“刷 新”按钮。 例3 查看或编辑Web脚本 要想查看或编辑Web脚本，首先需要将“显示所有脚本”命令添加到 “工具”菜单。 单击“工具宏Microsoft脚本编辑器”命令。 Microsoft脚本 编辑器 如果已在“Microsoft脚本编辑器”中编辑脚本，请返回到Office程 序，再通过单击“刷新”工具栏上的“刷新”按钮来更新网页。 注意 若要快速查看或编辑Web脚本，请双击网页上的脚本定位 标记(脚本定位标记: 在 Microsoft Offi ce程序中打开的网页上脚本 的可视表示形式。默认情况下不显示脚本定位标记。不同脚本定 位标记表示以不同脚本语言编写的脚本 )。 数据的查找、统计与分析 CHAPTER 6178CHAPTER 6 数据的查找、统计与分析 例4 保存在其他工作表中使用的Web查询 要想保存在其他工作表中使用的Web查询，可以这样做： Web查询(用于检索存储在Intranet或Internet中的数据的查询)在当前 工作表中使用时会自动保存。以.iqy文件的方式保存查询可使其在不同的工 作表中使用或者与其他用户共享。 具体方法是：单击“数据导入外部数据新建Web查询”以创建一 个新的查询，或者单击“外部数据”工具栏上的“编辑查询”按钮以编辑 一个现有的查询。 如果正在创建一个新的查询，可在“地址”框中定位所需页面，再单 击要选择的表格旁边的向右指的黄色箭头。如果页面中表格的旁边没有， 请单击对话框顶部的“显示图标”以显示图标。 单击“导入”按钮，在“保存位置”框中将文件夹定位到要保存查询 的位置。 在“文件名称”框中输入文件名称，单击“保存”按钮。 若要在其他工作表中运行该查询，请单击“数据导入外部数据导 入数据”命令，定位并打开.iqy文件。 导入数据源 例5 解决共享工作簿中的修订冲突 当两个用户试图保存影响同一单元格的修订时，Excel为其中一个用户 显示“解决冲突”对话框。如何才能解决这种问题？ 在“解决冲突”对话框中，可看到有关每一次修订以及其他用户所造 成的修订冲突的信息。 若要保留自己的修订或其他人的修订并转到下一个修订冲突上，请单 击“接受本用户”或“接受其他用户”按钮。 数据的查找、统计与分析 CHAPTER 6178179 若要保留自己的所有剩余修订或所有其他用户的修订，请单击“全部 接受本用户”或“全部接受其他用户”按钮。 若要使自己的修订覆盖所有其他用户的修订，而且不再看到“解决冲 突”对话框，请关闭此功能。 操作方法： 首先在“工具”菜单上，单击“共享工作簿”命令，然后单击“高 级”选项卡。然后单击“选用正在保存的修订”复选框。最后单击“确 定”按钮。 若要查看自己或其他人如何解决以前的冲突，可在冲突日志工作表中 查看。操作方法如下： （1）单击“工具修订突出显示修订”命令。 突出显示修 订设置 （2）在“时间”框中，单击“全部”按钮。 （3）清除“修订人”和“位置”复选框。 （4）选中“在新工作表上显示修订”复选框，再单击“确定”按钮。 （5）在“冲突日志工作表”上，滚动到右边以查看“操作类型”和 “操作失败”列。 保留的修订冲突在“操作类型”列有“成功”字样。“操作失败”列中 的行号用于标识记录有未保存的修订冲突信息的行，包括任何删除的数据。 注意 若要保存包含所有修订的工作簿的副本，请单击“解决冲 突”对话框中的“取消”按钮，再单击“文件”菜单上的“另存 为”命令，然后为该文件键入新名称。 数据的查找、统计与分析 CHAPTER 6180CHAPTER 6 数据的查找、统计与分析 例6 将Excel数据放到网页上 要想将Excel数据放到网页上，可以这样做： （1）打开或选择含有要发布的项的工作簿。 （2）在“文件”菜单上，单击“另存为网页”命令。 点选“另存为网 页”命令 （3）如果已选择要发布的项，或者要非交互式发布整个工作簿，请在 “另存为网页”对话框中选择所需选项，单击“保存”按钮，这样就完成 了操作。否则，请继续按下列步骤操作: （4）单击“发布”按钮。 （5）在“发布内容”的“选择”框中，单击要发布的内容。 （6）在“查看选项”之下，选中或清除“添加交互对象”复选框，如 有必要，单击所需的功能类型。 如果你希望用户在浏览器中只查看而不处理发布的数据，那么就可将 这些数据发布为非交互式数据。 例7 从网页上复制数据 要想从网页上复制数据，可以这样做： 此过程要求使用Microsoft Internet Explorer 4.01或更高版本。 （1）在Web浏览器中，选择要复制的数据。 （2）在“编辑”菜单上单击“复制”命令。如果浏览器中没有此命 数据的查找、统计与分析 CHAPTER 6180181 令，请参阅浏览器的帮助以获取有关复制的详细信息。 （3）切换到Microsoft Excel中。 （4）单击要显示复制数据的工作表区域的左上角。 （5）在“编辑”菜单上单击“粘贴”命令。 （6）如果数据显示不正确，请单击“粘贴选项”，然后执行下列操作 之一： “保持原有格式”：不做任何更改。 “匹配目标格式”：匹配原有单元格格式。 “创建可刷新的Web查询”：可以创建复制的网页的查询。也可刷新 网页稍后要更改的数据。 例8 创建指向网页上特定位置的超链接 在Excel中，用鼠标右键单击希望用来代表超链接的文本或图形，再单 击快捷菜单上的“超链接”命令。 在对话框左边的“链接到”选项之下，单击“原有文件或网页”。然 后请执行下列操作之一： （1）若要从当前文件夹中选择网页，请单击“当前文件夹”，再单击 所要链接的网页。 （2）若要从浏览过的网页列表中选择网页，请单击“浏览过的页”， 再单击要链接的网页。 （3）若要从最近使用过的文件列表中选择网页，请单击“近期文 件”，再单击要链接的网页(如果知道要链接的网页名称和位置，请在“地 址”框中键入相关信息)。 （4）若要通过打开浏览器并搜索页来选择网页，请单击“浏览 Web”，打开要链接的网页，然后不关闭浏览器切换回Excel。单击“书 签”，再双击所需书签。 （5）若希望鼠标停放在超链接上时可显示提示，请单击“屏幕提 示”，接着在“屏幕提示文字”框中键入所需文本，然后单击“确定” 按钮。 例9 创建指向电子邮件地址的超链接 要想创建指向电子邮件地址的超链接，可以这样做： 数据的查找、统计与分析 CHAPTER 6182CHAPTER 6 数据的查找、统计与分析 （1）用鼠标右键单击代表超链接的文本或图形，然后单击快捷菜单上 的“超链接”命令。 插入超链接命令 （2）在对话框左边的“链接到”选项下，单击“电子邮件地址”。 （3）在“电子邮件地址”框中，键入所需电子邮件地址。 （4）在“主题”框中，键入电子邮件主题。 注意 某些Web浏览器和电子邮件程序不一定能识别主题行。 若希望鼠标停放在超链接上时可显示提示，请单击“屏幕提示” 按钮，接着在“屏幕提示文字”框中键入所需文本，然后单击“确 定”按钮。 例10 多人编辑的共享Excel文件 要想进行多人编辑一个Excel文件，可以这样做： （1）首先打开一个Excel工作簿。 （2）单击“工具”菜单中的“共享工作簿”命令，弹出“共享工作 簿”对话框。 （3）单击“编辑”选项卡，然后选中“允许多用户同时编辑，同时允 许工作簿合并”复选框。 （4）单击“确定”按钮，弹出“另存为”对话框，将文件共享于局域 网中即可。 数据的查找、统计与分析 CHAPTER 6182183 设置共享工作簿 例11 使用Excel的VBA编辑器编制座位表 下面我们就使用Excel中的VBA编辑器来编制一个学生座位表编排系 统，下面以一个40个学生的班为例，编制过程如下： （1）首先，将学生分为4个组，每组10人，选定A1:H5，并设置其 外边框作为座位表的范围。在D7: E9画一个梯形，并将梯形上输入“讲 台”。在边框线外部的区域G7:K14内输入本班学生的姓名。 （2）根据学生的身高、视力等情况，使用鼠标拖动单元格数据将学生 的姓名移到A1:H5范围内，编好座位。 （3）单击“工具宏Visual Basic编辑器”命令，打开“Visual Basic编辑器”界面。在编辑窗口中输入如下代码： Sub anelozw() Application.ScreenUpdating = False 关闭执行程序时发生的屏幕更新现象，加快运行速度。 With A1:B5 .Borders(xlEdgeLeft).LineStyle = xlDouble .Borders(xlEdgeTop).LineStyle = xlDouble .Borders(xlEdgeBottom).LineStyle = xlDouble .Borders(xlEdgeRight).LineStyle = xlDouble 数据的查找、统计与分析 CHAPTER 6184CHAPTER 6 数据的查找、统计与分析 .Borders(xlInsideVertical).LineStyle = xlDot .Borders(xlInsideHorizontal).Line- Style = xlContinuous End With 设置A1:B5的边框格式 A1:B5.Copy C1.PasteSpecial Paste:=xlFormats E1.PasteSpecial Paste:=xlFormats g1.PasteSpecial Paste:=xlFormats 将A1B5的边框格式选择性