Excel制表技巧(72)数据校验.doc

1、Excel 制表技巧 (72) 数据校验Excel高效数据校验两法在用Excel录入完大量数据后，不可避免地会产生许多错误。通常而言，多数用户都是一手拿着原始数据，一手指着计算机屏幕，进行手工的数据校验，这不但容易导致眼睛疲劳，而且效率不高。 为此，笔者特向大家介绍两种轻松且高效的数据校验方法。一、语音校验法 我们可以使用 Excel 的“文本到语音”功能，将 Excel 工作表中的数据读出来给我们听，这样就可让耳朵和眼睛并行工作，以轻松实现数据的校验。具体操作如下：在Excel工作表中选定需要校验的数据，执行“工具语音文本到语音”命令，打开“文本到语音”工具栏（见图 1）。 图 1 然后，根

2、据自己的需要， 单击工具栏上的“按行”或“按列”按钮来设定朗读顺序。接着，准备好原始数据，再单击“朗读单元格”按钮即可开始数据校验（提示：在朗读前别忘了打开音箱或戴上耳机哟）。不过，有些用户可能会发现自己的计算机只能朗读数字或英文单词，而不能朗读中文。 这该怎么办？别急！此时，你还需要做点小小的设置。打开控制面板，双击其中的“语音”项。在“文本语音转换”选项卡中将“语音选择”由“ Microsoft Sam”改为“ Microsoft Simplified Chinese再 ”即可。试试，是不是中文也可以读出来了？ 当然，也有用户可能会问，能不能在录入时就通过语音来校验数据呀？行，没问题！单击

3、工具栏上的“按回车键开始朗读”。此时，当你在单元格中录入数据并回车后， Excel 就会立即将数据读出来了。小提示：该项功能不会随 Excel 的默认安装而安装，首次使用时，系统会提示你添加，请按提示操作。如果你在安装office 时选择了删除备份文件，系统会提示你插入安装盘。当你发现有错时， 可及时单击图 1 中的第二个按钮停止朗读，以便进行修改，修改后再单击第一个按钮，校对又能继续进行。事实上，很多人为了追求快速校对，一般是左手拿纸稿，右手修改，这时只需按一下 Esc 键即可暂停朗读。而单击第三、四个按钮，可设置按行进行校对还是按列进行校对。追求更高效率上述方法很省事，但速度较慢。专业的录

4、入员在操作 Excel 时，一般是用专业的指法从数字键盘录入数据，与其听 Excel 读一遍还不如重新输入一遍来得快，当然，这里的重输一遍是有目的的快速校对。图 2 是一张工资表示意图。当然，一般的工资表列数都比较多，可将用于校对的列安排到后面去，要用时才让其显示出来。下面是具体的设置方法：图 2 单击“视图”“工具栏”“窗将体窗”体，工具栏显示出来，在 U1 单元格插入一个“组合框”。在 V4 ：V6 单元格中输入“基本工资”、“奖金”、“扣除”等内容，这里的内容一定要与工资表表头即B3 ：D3 中的内容保持一致。再右击组合框，选择“设置控制格式”“控制”选项卡，在“数据源区域”后的文本框中

5、输入V4:V6 ，在“单元格链接”后输入 U1 。今后，我们只要单击组合框就能很方便地选择到“基本工资”、“奖金”、“扣除”同，时，U1 单元格中会自动填入1、2、3 等数字。在U3 单元格中输入函数IF(T4=CHOOSE($U$1,B4,C4,D3),1,0)，再用 Excel 的自动填充功能将其复制到U3 下面的单元格中。至此，基本功能已经具备，可以试用一下了。单击组合框，选择要对哪一项进行校对，比如“基本工资”，接着，在 T4 ： T8单元格中将每人的基本工资重输一遍，如果 B4 中的数据与T4 中的数据相等， U4 中会自动填入1 以示正确，如不等，则自动填入 0 以示错误。同理，如

6、果用组合框选择另一项，如“奖金”，U 列中的函数会自动将T 列的值与对应的 C 列的值进行比较并提示正误。小提示：为使给出的出错提示更加明显， 还可在 U 列设置条件格式， 比如让出错信息显示为红色等，设置条件格式的方法：选中U4 ：U8单元格，单击“格式”“条件格式”，得到3图对话框，可在图3 的上面一排三个框中分别选择“单元格数值”、“等于”、“0”。接着单击“格式”，打开的下一级对话框与对单元格式进行设置时的内容完全相同，可对符合条件的单元格数字设置字体、字号、下划线等，你认为哪种最醒目就选择哪一个。图 3 二、公式审核法 有时，我们录入的数据是要符合一定条件的，例如，对于学生成绩表而言

7、， 其中的数据通常是 0100 之间的某个数值。但是，在录入时由于错误按键或重复按键等原因，我们可能会录入超出此范围的数据。那么，对于这样的无效数据，我们如何快速将它们“揪”出并予以更正呢？让我们用公式审核法来解决这类问题吧。下面笔者以一张学生成绩表为例进行介绍。 首先，打开一张学生成绩表。然后，选中数据区域（注意：这里的数据区域不包括标题行或列），单击“数据有效性”命令，进入“数据有效性”对话框。在“设置”选项卡中，将“允许”项设为“小数”，“数据”项设为“介于”并，在“最小值”和“最大值”中分别输入“ （0”见和图“2）100，”最后单击“确定”退出。 图 2 接着，执行“视图工具栏公式审

8、核”命令，打开“公式审核”工具栏。好了，现在直接单击“圈释无效数据”按钮即可。 此时，我们就可看到表中的所有无效数据立刻被红圈给圈识出来了（见图3）。 图 3 根据红圈标记的标志，对错误数据进行修正。修正后红圈标记会自动消失。这样，无效数据就轻松地被更正过来了。巧用Excel实现成绩的校对Excel 强大的数据处理功能使很多学校的成绩处理都用上了它。但是有些老师在用Excel 输入成绩时，总担心自己输入会出现错误。其实，用 Excel 提供的“条件格式”功能完全能够解决这个难题， 真正确保学生成绩准确无误。现把这一方法在此交流，供老师们参考。1. 输入成绩（第一遍录入）打开学生成绩表， 依次录

9、入各学科的成绩。2. 校对成绩（第二遍录入）（ 1）在“语文”成绩所在列（列 B）后插入一列（列 C ），命名为“语文 2”。在“语文2”列中再一次录入语文成绩（如图 1）。根据有关规定， 所有学生成绩必须要经过录入和审核两道程序，所以我们采用两次录入成绩再由Excel 进行校对的方法， 从而达到审核的目的。图 1 （2）把活动单元格移到“ C2”单，击菜单“格式条件格式 ”，在弹出的“条件格式”对话框中将“条件1”设定为“单元格数值”、“不等于”“、=B2（”实际操作时不必输入双引号） 。然后单击“格式（ F） ”，弹出“单元格格式”对话框，选择“图案”选项卡，在“单元格底纹颜色”下选取红色

10、，单击确定返回到“条件格式”对话框。单击确定，完成对单元格 C2的设定（如图 2）。这样，若单元格 C2 与单元格 B2 数值不相等，则单元格 C2 的底纹变为红色，若相等，则不会有任何变化。图 2（ 3）单击工具栏中的格式刷按钮，然后用鼠标点击 C 列顶上的列字母“ C”，使这列中的所有单元格格式与C2 相同。这样，凡第二次与第一次成绩录入不相同的单元格都会以红色底纹显示，表示必有一次录入的成绩是错误的。而两次录入相同的单元格就不会有任何变化。根据单元格的颜色变化，可以一目了然地知道录入成绩是否有错误（如图3）。图 3（4 ）把以红色底纹显示的成绩，对照原始数据，在列 B 或列 C 中逐一予

11、以纠错， 使全部底纹都变成白色后， 就完成了语文成绩的校对。（ 5）再用同样的方法，完成其余各学科成绩校对。（ 6）检查确信没有红色底纹的单元格后，删去用于校对成绩的列“语文2”、“数学2” Excel在公式中使用运算符号在Excel 2000中，公式可以使用数学运算符号来完成。比如加法、减法等。通过对这些运算的组合，我们就可以完成各种复杂的运算。在Excel 中可以使用的数学运算符号见表7-1 。在执行算术操作时，基本上都是要求两个或者两个以上的数值、变量，例如=102*15 。但对于百分数来说只要一个数值也可以运算，例如=5% ，百分数运算符号会自动地将5 除以 100 ，得出 0.05来

12、。在 Excel 中不仅可以进行算术运算，还提供了可以操作正文（文字）的运算。利用这些操作，我们可以将文字连接起来，例如我们可以利用“”符号将一个字符串和某一个单元格的内容连接起来。 表 7-2 给出了文字操作的例子。此外，Microsoft Excel 还提供了比较运算。 这些运算符号会根据公式判断条件， 返回逻辑结果 TRUE（真）和 FALSE（假）。比较运算符号见表 7-3 。表 7-4 则是使用比较运算的例子。在 Excel 环境中，不同的运算符号具有不同的优先级。如果要改变这些运算符号的优先级可以使用括号，以此来改变表达式中的运算次序。在 Excel 中规定所有的运算符号都遵从“由

13、左到右”的次序丛怂恪 纾?A1+B2/100 和=(A1+B2)/100的结果是不同的。运算符号的次序见表7-5 。注意：在公式中输入负数时，只需在数字前面添加“-”即可，而不能使用括号。例如：=5*-10 的结果是“-50”。Excel公式的显示在Mircosoft Excel 2000中，通常当我们输入公式后，在单元格中反映的不是公式本身，而是由公式计算的结果，公式本身则在编辑栏的输入框中反映，如图7-2 的显示。我们也可以改变设定，在单元格中显示输入的公式。要达到此目的，可以按照下列步骤操作：(1) 执行“工具”菜单中的“选项”命令，出现选项对话框，单击“视图”选项卡，如图 7-3 所示

14、。(2) 选定“窗口选项”框中的“公式”，按下“确定”按钮。就会看到单元格显示的不再是公式的结果，而是公式本身了。如图7-4 所示。我们也可以使用键盘组合达到同样的效果。通过按下Ctrl 键，就可以在显示结果和显示公式之间切换， 就像按开关一样。查找与引用函数 1.ADDRESS 用途：以文字形式返回对工作簿中某一单元格的引用。 语法：ADDRESS(row_num ，column_num ，abs_num ，a1 ，sheet_text) 参数： Row_num是单元格引用中使用的行号 ;Column_num 是单元格引用中使用的列标 ;Abs_num 指明返回的引用类型 (1 或省略为绝对

15、引用， 2 绝对行号、相对列标， 3 相对行号、绝对列标， 4 是相对引用 );A1 是一个逻辑值，它用来指明是以 A1 或 R1C1返回引用样式。如果 A1 为 TRUE 或省略，函数 ADDRESS 返回 A1 样式的引用 ;如果 A1 为 FALSE ，函数 ADDRESS 返回 R1C1 样式的引用。 Sheet_text 为一文本，指明作为外部引用的工作表的名称，如果省略sheet_text ，则不使用任何工作表的名称。实例：公式“=ADDRESS(1，4， 4， 1) ”返回 D1 。2.AREAS用途：返回引用中包含的区域个数。语法：AREAS(reference)。参数： Re

16、ference是对某一单元格或单元格区域的引用，也可以引用多个区域。注意 :如果需要将几个引用指定为一个参数，则必须用括号括起来，以免Excel将逗号作为参数间的分隔符。实例：公式“=AREAS(a2:b4) ”返回 1，=AREAS(A1:A3，A4:A6 ，B4:B7 ，A16:A18) 返回4。 3.CHOOSE用途：可以根据给定的索引值，从多达29个待选参数中选出相应的值或操作。语法：CHOOSE(index_num，value1，value2，.) 。参数：Index_num是用来指明待选参数序号的值，它必须是1 到 29 之间的数字、或者是包含数字1 到 29 的公式或单元格引用;

17、value1 ，value2 ，.为 1 到 29 个数值参数， 可以是数字、单元格，已定义的名称、 公式、函数或文本。 实例：公式“ =CHOOSE(2，电脑 ，爱好者 )返回“爱好者”。公式“ =SUM(A1:CHOOSE(3，A10 ， A20， A30)”与公式“=SUM(A1:A30) ”等(因价为CHOOSE(3 ， A10 ， A20 ， A30)途：返回给定引用的列标。语法：返回 A30) 。 4.COLUMNCOLUMN(reference)用。参数： Reference为需要得到其列标的单元格或单元格区域。如果省略 reference ，则假定函数 COLUMN 是对所在单

18、元格的引用。如果 reference 为一个单元格区域，并且函数 COLUMN 作为水平数组输入， 则 COLUMN 函数将 reference中的列标以水平数组的形式返回。实例：公式“ =COLUMN(A3)”返回 1， =COLUMN(B3:C5) 返回 2。5.COLUMNS用途：返回数组或引用的列数。语法：COLUMNS(array)。参数：Array 为需要得到其列数的数组、数组公式或对单元格区域的引用。实例：公式“ =COLUMNS(B1:C4)”返回 2， =COLUMNS(5 ， 4;4 ，5) 返回 2。 6.HLOOKUP 用途：在表格或数值数组的首行查找指定的数值，并由此

19、返回表格或数组当前列中指定行处的数值。语法：HLOOKUP(lookup_value， table_array，row_index_num， range_lookup)参数：Lookup_value是需要在数据表第一行中查找的数值，它可以是数值、引用或文字串 ;Table_array是需要在其中查找数据的数据表，可以使用对区域或区域名称的引用，Table_array的第一行的数值可以是文本、数字或逻辑值。 Row_index_num为 table_array中待返回的匹配值的行序号。Range_lookup为一逻辑值，指明函数 HLOOKUP查找时是精确匹配，还是近似匹配。 实例：如果A1:B

20、3 区域存放的数据为34 、23 、 68 、 69 、 92 、36 ，则公式“=HLOOKUP(34， A1:B3 ， 1， FALSE) 返回34;=HLOOKUP(3， 1， 2， 3;a ， b ， c;d ， e ， f ，2， TRUE) 返回“ c”7。.HYPERLINK用途：创建一个快捷方式，用以打开存储在网络服务器、Intranet(Internet)或本地硬盘的其它文件。语法：HYPERLINK(link_location，friendly_name) 参数： Link_location是文件的路径和文件名，它还可以指向文档中的某个更为具体的位置，如表或工作簿中特定的单

21、元格或命名区域，或是指向Execl 工作word 文档中的书签。路径可以是存储在硬盘驱动器上的文件，或是Internet 或 Intranet 上的 URL 路径 ;Friendly_name为单元格中显示的链接文字或数字，它用蓝色显示并带有下划线。如果省略了 Friendly_name ，单元格就将 link_location 显示为链接。实例： HYPERLINK( ， 驱动之家 )会在工作表中显示文本“驱动之家”，单击它即可连接到“ ”。公式“ =HYPERLINK(D: README.TXT ， 说明文件 ) ”在工作表中建立一个的蓝色“说明文件”链接，单击它可以打开 D 盘上的 RE

22、ADME.TXT 文件。 8.INDEX 用途：返回表格或区域中的数值或对数值的引用。 函数 INDEX() 有两种形式 :数组和引用。数组形式通常返回数值或数值数组 ;引用形式通常返回引用。语法： INDEX(array ， row_num ， column_num) 返回数组中指定的单元格或单元格数组的数值。 INDEX(reference ，row_num ， column_num ， area_num) 返回引用中指定单元格或单元格区域的引用。参数：Array 为单元格区域或数组常数 ;Row_num为数组中某行的行序号，函数从该行返回数值。如果省略row_num ，则必须有colum

23、n_num;Column_num是数组中某列的列序号，函数从该列返回数值。 如果省略 column_num ，则必须有row_num 。Reference是对一个或多个单元格区域的引用，如果为引用输入一个不连续的选定区域，必须用括号括起来。Area_num是选择引用中的一个区域，并返回该区域中row_num 和column_num的交叉区域。选中或输入的第一个区域序号为1，第二个为 2，以此类推。如果省略area_num ，则 INDEX函数使用区域1。实例：如果A1=68 、 A2=96 、 A3=90 ，则公式“ =INDEX(A1:A3 ，1， 1) ”返回68 ，=INDEX(A1:A

24、3， 1，1， 1) 返回 68 。 9.INDIRECT用途：返回由文字串指定的引用。此函数立即对引用进行计算，并显示其内容。当需要更改公式中单元格的引用，而不更改公式本身，即可使用INDIRECT函数。语法：INDIRECT(ref_text， a1) 。参数：Ref_text是对单元格的引用，此单元格可以包含A1样式的引用、 R1C1 样式的引用、定义为引用的名称或对文字串单元格的引用;A1为一逻辑值，指明包含在单元格ref_text中的引用的类型。 如果a1为TRUE或省略，ref_text被解释为A1- 样式的引用。如果a1为FALSE，ref_text被解释为R1C1-样式的引用。

25、实例：如果单元格A1存放有文本B1 ，而B1单元格中存放了数值68.75 ，则公式“=INDIRECT($A$1) ”返回 68.75 。10.LOOKUP 用途：返回向量 (单行区域或单列区域)或数组中的数值。该函数有两种语法形式 :向量和数组，其向量形式是在单行区域或单列区域 (向量 )中查找数值，然后返回第二个单行区域或单列区域中相同位置的数值 ;其数组形式在数组的第一行或第一列查找指定的数值，然后返回数组的最后一行或最后一列中相同位置的数值。语法 1(向量形式 ):LOOKUP(lookup_value，lookup_vector ，result_vector)语法 2( 数组形式 )

26、:LOOKUP(lookup_value ，array) 。参数 1( 向量形式 ):Lookup_value 为函数 LOOKUP 在第一个向量中所要查找的数值。 Lookup_value 可以为数字、 文本、逻辑值或包含数值的名称或引用。 Lookup_vector为只包含一行或一列的区域。 Lookup_vector的数值可以为文本、数字或逻辑值。参数 2( 数组形式 ):Lookup_value为函数 LOOKUP 在数组中所要查找的数值。 Lookup_value可以为数字、 文本、逻辑值或包含数值的名称或引用。如果函数LOOKUP找不到lookup_value ，则使用数组中小于或

27、等于lookup_value 的最大数值。 Array 为包含文本、数字或逻辑值的单元格区域，它的值用于与 lookup_value进行比较。 注意 :Lookup_vector的数值必须按升序排列，否则LOOKUP函数不能返回正确的结果，参数中的文本不区分大小写。实例：如果A1=68 、A2=76 、A3=85 、 A4=90 ，则公式“=LOOKUP(76， A1:A4) ”返回 2，=LOOKUP(bump，a ，1;b ，2;c ，3) 返回 2。11.MATCH用途：返回在指定方式下与指定数值匹配的数组中元素的相应位置。如果需要找出匹配元素的位置而不是匹配元素本身，则应该使用MATC

28、H 函数。语法：MATCH(lookup_value，lookup_array ，match_type) 。参数：Lookup_value为需要在数据表中查找的数值，它可以是数值(或数字、文本或逻辑值) 、对数字、文本或逻辑值的单元格引用。Lookup_array是可能包含所要查找的数值的连续单元格区域， Lookup_array 可以是数组或数组引用 ;Match_type 为数字 -1 、 0 或 1，它说明 Excel 如何在 lookup_array 中查找 lookup_value 。如果 match_type 为 1，函数 MATCH 查找小于或等于 lookup_value 的最

29、大数值。如果 match_type为 0，函数 MATCH 查找等于 lookup_value 的第一个数值。如果 match_type 为-1 ，函数 MATCH 查找大于或等于lookup_value的最小数值。注意:MATCH函数返回lookup_array中目标值的位置，而不是数值本身。如果match_type为 0 且 lookup_value为文本， lookup_value可以包含通配符( “ * ”和“ ?” ) 。星号可以匹配任何字符序列，问号可以匹配单个字符。实例：如果A1=68 、 A2=76 、A3=85 、A4=90 ，则公式“ =MATCH(90，A1:A5 ，0)

30、 ”返回3。12.OFFSET用途：以指定的引用为参照系，通过给定偏移量得到新的引用。返回的引用可以是一个单元格或单元格区域，并可以指定返回的行数或列数。语法：OFFSET(reference， rows ，cols ， height ， width) 。参数： Reference是作为偏移量参照系的引用区域，它必须是单元格或相连单元格区域的引用;Rows 是相对于偏移量参照系的左上角单元格，上 (下 )偏移的行数。如果使用 5 作为参数 Rows ，则说明目标引用区域的左上角单元格比 reference 低 5 行。行数可为正数 (代表在起始引用的下方 )或负数 (代表在起始引用的上方 );

31、Cols 是相对于偏移量参照系的左上角单元格，左 (右 )偏移的列数。如果使用 5 作为参数 Cols ，则说明目标引用区域的左上角的单元格比 reference 靠右 5 列。列数可为正数 (代表在起始引用的右边 )或负数 ( 代表在起始引用的左边 );Height 是要返回的引用区域的行数， Height 必须为正数 ;Width 是要返回的引用区域的列数， Width 必须为正数。实例：如果 A1=68 、A2=76 、A3=85 、A4=90 ，则公式“ =SUM(OFFSET(A1:A2， 2，0，2，1) ”返回177 。13.ROW 用途：返回给定引用的行号。语法：ROW(ref

32、erence)。Reference为需要得到其行号的单元格或单元格区域。实例：公式“=ROW(A6)”返回6，如果在 C5 单元格中输入公式“=ROW()”，其计算结果为5。14.ROWS用途：返回引用或数组的行数。语法：ROWS(array)。参数：Array 是需要得到其行数的数组、数组公式或对单元格区域的引用。 实例：公式“ =ROWS(A1:A9)”返回9，=ROWS(1 ，2，3;4 ，5，6;1 ，2，3) 返回 3。15.RTD 用途：从支持COM自动化的程序中返回实时数据。语法：RTD(ProgID ，server ，topic1 ， topic2 ， .) 参数： ProgI

33、D 已安装在本地计算机中，经过注册的COM 自动化加载宏的ProgID 名称，该名称用引号引起来。 Server 是运行加载宏的服务器的名称。如果没有服务器，程序是在本地计算机上运行，那么该参数为空白。topic1 ， topic2 ， . 为 1 到 28 个参数，这些参数放在一起代表一个唯一的实时数据。16.TRANSPOSE用途：返回区域的转置 (所谓转置就是将数组的第一行作为新数组的第一列，数组的第二行作为新数组的第二列，以此类推)。语法：TRANSPOSE(array)。参数： Array 是需要转置的数组或工作表中的单元格区域。实例：如果 A1=68 、A2=76 、B1=85 、

34、B2=90 ，那么公式“=TRANSPOSE(A1:B1) ”返回C1=56 、D1=98 、C2=90 、D2=87 。17.VLOOKUP用途：在表格或数值数组的首列查找指定的数值，并由此返回表格或数组当前行中指定列处的数值。当比较值位于数据表首列时，可以使用函数 VLOOKUP代替函数 HLOOKUP 。语法：VLOOKUP(lookup_value，table_array ， col_index_num ，range_lookup) 参数： Lookup_value为需要在数据表第一列中查找的数值，它可以是数值、引用或文字串。Table_array为需要在其中查找数据的数据表，可以使用

35、对区域或区域名称的引用。 Col_index_num 为 table_array 中待返回的匹配值的列序号。 Col_index_num 为 1 时，返回 table_array 第一列中的数值 ;col_index_num为 2，返回 table_array第二列中的数值，以此类推。Range_lookup为一逻辑值，指明函数 VLOOKUP返回时是精确匹配还是近似匹配。如果为TRUE 或省略，则返回近似匹配值，也就是说，如果找不到精确匹配值，则返回小于lookup_value的最大数值 ;如果range_value 为 FALSE ，函数 VLOOKUP 将返回精确匹配值。如果找不到，则返回错误值 #N/A 。实例：如果 A1=23 、A2=45 、 A3=50 、A4=65 ，则公式“ =VLOOKUP(50，A1:A4 ，1，TRUE)”返回 50 。 解析 Excel