|
建立和使用Excel数据清单 在Microsoft Excel 2000中，不需要把建立的数据清单变为数据库，只要执行了数据库的操作，例如查找、排序或分类汇总，Microsoft Excel 2000就会自动认为你的清单是一个数据库，即清单中的列被认为是数据库的字段，清单中的列标记被认为是数据库的字段名，清单中的每一行被认为是数据库的一条记录。
12.2.1 准备工作
如果你对数据库非常熟悉，或者使用过类似Access、DBASE、FoxPro等数据库产品，可以跳过该小节。
建立一个数据库首先是要进行一个好的规划。我们应该花一些时间，考虑数据库做什么用，实现它的最好方法是什么。考虑处理的数据的类型和数量，希望从数据库中收集什么信息等等。然后考虑数据库的字段，关于它们的选择，位置安排，名字等。
选择字段
为数据库选择字段是极其重要的，它决定了在每个数据库记录中的信息及数据库本身的能力。例如，要决定在应收帐数据库中是否应包括公司名称、发票日期、金额等。通用的原则是，根据要建立的数据库，将字段分得更细些。例如，要建立一个客户欠款的数据库，可以定义下列字段：公司名称、地址、电话、欠款日期、欠款金额等等。在建立字段时要根据当前和将来的需要。通常字段越多越灵活。对于数据库中的每个字段应该是唯一的。用这种方式，数据库的记录也是唯一的，因为记录中的每个字段将包含特有的信息。例如一个属于该记录的日期或时间。在这种方式下，字段与当前可存取的记录确定了数据项的唯一性。
安排字段位置
字段名称构成数据库顶部的行，因此它们必须安排在一行连续的每一列中。字段名称应该按逻辑顺序组织，相似的信息应该组织到一起。例如，使用发票号、日期、公司名称作为相邻的字段描述信息会使数据库更易于理解和使用。
命名字段
字段名称是数据库操作的标识成分。Excel根据字段名称来执行排序和查找等数据库操作。因此在选择字段名时应该慎重，最好选择容易记忆的字段名。和命名区域和文件的规则一样，数据库的字段名字必须遵循以下的规则：字段名只能是文字或文字公式（如="1992"）。字段名不能包含数字、数值公式、逻辑值。字段名可以使用1~255个字符，字段名必须是唯一的。
12.2.2 建立一个数据库
当完成了对一个数据库的结构设计后，就可以在工作表中建立它了。首先在工作表的首行依次输入各个字段：员工姓名、地区、日期、产品名称、销售数量、销售金额、销售金额，如图12-1所示。当我们输入完字段后，就可以在工作表中按照记录输入数据了。
提示：字段名放在工作表的某一行的各相邻单元中。也可以为字段名和尚未建立的数据库记录建立一个格式。格式只是使数据库容易理解，并不影响Excel对数据库进行操作。
12.2.3 输入数据
要加入数据至所规定的数据库内,有两种方法,一种是直接键入数据至单元格内, 一种是利用“记录单”输入数据。使用“记录单”是经常使用的方法，其操作步骤如下：
（1）在想加入记录的数据清单中选中任一个单元格。
（2） 从“数据”菜单中选择“记录单”命令。屏幕上会出现一个如图12-2的对话框，选择“确定”按钮，出现图12-3的对话框。
在各个字段中输入新记录的值。要移动到下一个域中，按［Tab］键。当你输完所有的记录内容后，按[Enter]键即可加入一条记录。如此重复加入更多的记录，当你加完所有记录后，选择“关闭”按钮。我们就会看到在清单底部加入了新增的记录，如图12-4所示。
我们也可以直接插入记录到数据库中，首先在现有记录的中间插入空的单元格，然后键入记录数据；原来所输入的数据库内容会自动下移。要插入记录到现有的数据库中，可按照下列步骤执行：
(1) 选定要插入记录的单元格。在“插入”菜单中选择“单元格”命令，在屏幕上出现一个对话框。
(2) 选择“整行”，按下“确定”按钮，我们就可以看到插入的单元格。输入记录内容到单元格中。
12.2.4 编辑记录
对于数据库中的记录，我们可以采用在相应的单元格上进行编辑，也可以对记录单进行编辑，其操作过程如下：
(1) 选择数据清单中的任一单元格。
(2) 从“数据”菜单中选择“记录单”命令，出现一个记录单对话框。
(3) 查找并显示出要修改数据的记录。编辑该记录的内容。(4) 选择“关闭”按钮退出。
12.2.5 删除一条记录
对于数据库中不再需要的记录，我们可以使用“删除”命令将其从数据库中删除。使用记录单删除一条记录的操作步骤如下：
(1) 选择数据清单中的任一单元格。从“数据”菜单中选择“记录单”命令。出现一个记录单对话框。
(2) 查找并显示出要删除的记录。选择“删除”按钮，出现一个如图12-5的确认对话框。
(3) 回答确认信息。按下“确定”按钮。
注意：当我们使用数据记录单来删除数据时，不能通过“恢复”按钮或“取消”命令来恢复数据。
什么是Excel数据清单 随着Excel提供的新改善的数据清单功能。你会发现Excel的表和数据管理能力正是自己需要的。本章的内容可以帮助我们更有效地管理数据。
一个数据库（也被称为一个表），是以具有相同结构方式存储的数据集合。例如电话簿、公司的客户名录、库存帐等等。利用数据库技术我们可以方便地管理这些数据，例如对数据库排序和查找那些满足指定条件的数据等等。
在Excel 2000中，数据库是作为一个数据清单来看待。我们可以理解数据清单就是数据库。在一个数据库中，信息按记录存储。每个记录中包含信息内容的各项，称为字段。例如，公司的客户名录中，每一条客户信息就是一个记录，它由字段组成。所有记录的同一字段存放相似的信息（例如，公司名称、街道地址、电话号码等）。Microsoft Excel 2000提供了一整套功能强大的命令集，使得管理数据清单（数据库）变得非常容易。我们可以完成下列工作：
排序——在数据清单中，针对某些列的数据，我们可以用数据菜单中的排序命令来重新组织行的顺序。可以选择数据和选择排序次序，或建立和使用一个自定义排序次序。
筛选——可以利用“数据”菜单中的“筛选”命令来对清单中的指定数据进行查找和其它工作。一个经筛选的清单仅显示那些包含了某一特定值或符合一组条件的行，暂时隐藏其它行。
数据记录单——一个数据记录单提供了一个简单的方法，让我们从清单或数据库中查看、更改、增加和删除记录，或用你指定的条件来查找特定的记录。
自动分类汇总——利用“数据”菜单的“分类汇总”命令，在清单中插入分类汇总行，汇总你所选的任意数据。当你插入了分类汇总后，Microsoft Excel自动为你在清单底部插入一个“总计”行。
Microsoft Excel 提供有一系列功能，可以很容易地在数据清单中处理和分析数据。在运用这些功能时，请根据下述准则在数据清单中输入数据。
数据清单的大小和位置
避免在一个工作表上建立多个数据清单，因为数据清单的某些处理功能（如筛选等），一次只能在同一工作表的一个数据清单中使用。
在工作表的数据清单与其它数据间至少留出一个空白列和一个空白行。在执行排序、筛选或插入自动汇总等操作时，这将有利于 Microsoft Excel 检测和选定数据清单。
避免在数据清单中放置空白行和列，这将有利于 Microsoft Excel 检测和选定数据清单。
避免将关键数据放到数据清单的左右两侧。因为这些数据在筛选数据清单时可能会被隐藏。
列标志
在数据清单的第一行里创建列标志。Microsoft Excel 使用这些标志创建报告，并查找和组织数据。
列标志使用的字体、对齐方式、格式、图案、边框或大小写样式，应当与数据清单中其它数据的格式相区别。
如果要将标志和其它数据分开，应使用单元格边框（而不是空格或短划线），在标志行下插入一行直线。
行和列内容
在设计数据清单时，应使同一列中的各行有近似的数据项。
在单元格的开始处不要插入多余的空格，因为多余的空格影响排序和查找。
不要使用空白行将列标志和第一行数据分开。
Excel 对合并计算的编辑 对于一个建立合并计算的工作表文件，我们还可以进一步编辑，分别介绍如下。
在执行这些操作前，还应注意，这些操作适用于仅当我们没有建立与源区域的链接，才可以调整源区并在目的区中重新合并计算数据。如果我们已经建立了到源区域的链接同时又要调整合并计算，那么在执行下列过程之前先要删除合并计算的结果并移去分级显示。
11.2.1将一个源区域增加到一个存在的合并计算中
将一个源区域增加到一个存在合并计算中的操作步骤如下：
（1）选定一存在的目标区域。
（2）执行“数据”菜单中的“合并计算”命令，出现合并计算对话框。
（3）选定“引用位置”输入框，如果包含新的源区域的工作表处于打开状态，选定源区域。
（4）选择“添加”按钮，利用新的源区域来重新合并计算。
（5）按下“确定”按钮。
11.2.2 改变源区域的引用
改变源区域引用的操作步骤如下：
选定一存在的目标区域。执行“数据”菜单中的“合并计算”命令，出现一个如图11-3的对话框。在“所有引用位置”框中，选定想改变的源区域。
在“引用位置”框中，编辑引用。选择“添加”按钮。如果不想保留原有引用，选择“删除”按钮。利用新的源区域来重新合并计算，按下“确定”按钮。
11.2.3 删除一个源区域的引用
删除一个源区域引用的操作步骤如下：
选定一存在的目标区域。选择“数据”菜单中的“合并计算”命令，出现一个如图11-3的对话框。
在“所有引用位置”框中，选定想删除的源区域。选择“删除”按钮。利用新的源区域来重新合并计算，按下“确定”按钮。
11.2.4 建立与源数据的链接
以下过程我们假设该合并计算表以前没有建立一个链接。若建立与源数据的链接，其操作步骤如下：
（1）执行“数据”菜单中的“合并计算”命令，出现一个如图11-3的对话框。
（2）选定“链接到源”选择框。
（3）按下“确定”按钮。
注意：如果包含目标区域的工作表同时也包含一个分级显示或被用于一个链接的合并计算，如果我们没有移去分级显示和链接公式，那么目标工作表会被破坏。因此，在建立链接之前要检查合并计算，先进行一个无链接的合并计算，再进行链接的合并计算。
理解Excel工作画面 当进入Microsoft Excel时，会出现如图2-2所示的工作画面。在这个工作画面中包含了Excel的基本工作画面，由标题栏、菜单、工具栏、滚动条、数据编辑栏、工作表选项卡和状态栏等组成，如图2-3显示。
图2-3 Microsoft Excel屏幕组成
标题栏 显示当前工作簿文件的名字。例如我们打开了一个名为“财务报表”的工作簿，就可以看到“财务报表”出现在该栏目中。在上图中，由于是新的工作簿文件，所以看到的是“Book 1”，它是由Excel自动建立的文件名。
菜单 在每组下拉菜单中包括了一组相关操作或命令，可以根据需要选取菜单中的项，完成相关操作。
工具栏 工具栏是一些图标，每一个按钮都代表了一个命令，这些命令都等价于菜单中相关的命令。使用工具栏会使操作更加简便。一般情况下，只取“常用”和“格式”工具栏。
数据编辑区 数据编辑区是用来输入或编辑单元格或图表的值或公式。可以显示出活动单元格中使用的常数或公式。
滚动条 滚动条沿着窗口右边和底边的阴影条。使用滚动条可以在长工作表中来回移动。滚动条在滚动框中的位置指示当前显示于窗口中的工作表或标题的一部分。若要快速地滚动到工作表或标题的另一个部分，拖动该框即可。
状态栏 状态栏即屏幕底端有关执行过程中的选定命令或操作的信息。当选定命令时，状态栏左边便会出现该命令的简单描述。状态栏左边也可以指示出过程中的操作，如打开或保存文件，复制单元格或记录宏表等。状态栏右边则是用来显示CAPS LOCK、SCROLL LOCK 或 NUM LOCK 等键是否打开的。
Excel工作簿与工作表 当我们拿到一本书时，会首先看到书名；打开这本书时，会看到书的目录，以后才是各章的内容。而各章的内容可以是文字、图形、表格等等。而在Excel系统中，一个工作簿文件就是类似于一本书组成的一个文件，在其中又会包含许多工作表，这些工作表可以储存不同类型的数据等等。工作簿是Excel中重要的基础概念之一。
工作簿 所谓工作簿是指：在Excel环境中用来储存并处理工作数据的文件。在一本工作簿中，可以拥有多张具有不同类型的工作表。在打开一个新的工作簿文件时，会看到如图2-2所示的画面。例如，我们可以在一个工作簿文件中保存全年的十二张资产负债表，或者保存年销售报表的数据，以及由这些数据所绘制的统计图表，如图2-4所示的按地区销售表。
Excel 2000和早期的电子表格程序相比，有一很重大的改进，就是现在Excel储存的单位是工作簿，而不是单一的工作表。当启动Excel时，就自动打开了工作簿，一个工作簿内最多可以有255个工作表，工作簿内除了可以存放工作表外，还可以存放宏表、图表等。一个工作簿内，可以有数个工作表，即可以同时处理多张工作表。
在默认情况下，每一个工作簿文件会打开3个工作表文件，分别以Sheet1、Sheet2、Sheet3来命名。工作表的名字显示在工作簿文件窗口底部的选项卡里（如图2-3所示）。所谓选项卡也就是指每一个工作表的名字。我们可以在选项卡上单击工作表的名字，来实现在同一工作簿中切换到不同的工作表。如果我们要找的工作表名没在底部的选项卡中显示，则可以通过按下选项卡滚动按钮来将它移动到当前的显示选项卡中。
工作表 工作表是指由“65536 ”个行和“256”个列所构成的一个表格，如图2-5所示。行号的编号是由上自下从“1”到“65536 ”编号；列号则由左到右采用字母编号为“A”…“IV”。每一个行、列坐标所指定的位置称为单元格。
注意：在一个工作簿文件中，无论有多少个工作表，在将其保存时都将会保存在一个工作簿文件中，而不是按照工作表的个数保存。
单元格、单元地址及活动单元格 每张工作表是由多个长方形的“存储单元”所构成的，这些长方形的“存储单元”被称为“单元格”，如图2-6所示。我们输入的任何数据都将保存在这些“单元格”中。这些数据可以是一个字符串、一组数字、一个公式或者一个图形、声音等等。
对于每个单元格都有其固定的地址。比如“A3”，就代表了“A”列的第“3”行的单元格。同样，一个地址也唯一地表示一个单元格，例如：“B5”指的是“B”列与第“5”行交叉位置上的单元格。在Excel环境中，对于每一张工作表会有65536×256个单元格所构成。在屏幕上我们会看到行号的编号是由上到下从“1”到“65536”编号；列号则由左到右采用字母编号为“A”…“IV”。例如：“A3”、“AB12”等等。
由于一个工作簿文件可能会有多个工作表，为了区分不同工作表的单元格，要在地址前面增加工作表名称。例如：Sheet2 ! A6。就说明了该单元格是“Sheet2”工作表中的“A6”单元格。
注意：工作表名与单元格之间必须使用“!”号来分隔。
活动单元格是指正在使用的单元格，在其外有一个黑色的方框（见图2-6），这时输入的数据会被保存在该单元格中。
Excel工具栏的使用 在Microsoft Excel中有13个包含按钮的工具栏，这些工具可以帮助我们更快速、更容易地工作。当我们第一次运行Microsoft Excel时，“常用”工具栏和“格式”工具栏会直接显示在菜单栏下面。
2.6.1 使用工具栏
使用工具栏的方法很简单，我们只需在要使用的工具按钮上单击即可。当指向某个按钮时，其下方会出现相应的功能说明，如图2-7所示。
由于每个工具按钮代表了一些操作命令的组合，所以按下工具按钮的结果可能会执行一个动作，或者出现一个对话框。例如“ ”按钮代表了“文件”菜单中的“保存”命令。当我们要保存正在工作的工作簿文件时，不必执行一连串的命令操作，而只需按下“保存”按钮即可。
提示：在Excel 2000中，如果某个工具按钮的颜色变浅，表示不能使用该工具。
2.6.2 显示工具栏
当要使用的工具栏不在当前工作窗口上时，可以将其打开，显示在窗口中。步骤如下：
(1) 执行“视图”菜单中的“工具栏”命令，这时窗口中出现如图2-8所示的子菜单。
(2)可以看到在“工具栏”中出现的系统工具栏，选择需要使用的工具栏。
(3)单击选中的工具栏，可以看到在工具栏前面的方框中出现“√”符号。之后即可看到选定的工具栏出现在屏幕上。
2.6.3 隐藏工具栏
当设定了太多的工具按钮时，屏幕上会显得过于杂乱，我们可以暂时将不用的工具栏隐藏起来，扩大工作空间。隐藏工具栏的步骤是：
(1)执行“视图”菜单中的“工具栏”命令，出现“工具栏”子菜单。
(2)在需要隐藏的工具栏前方单击，使前面方框中的“√”符号消失即可。
(3)最后按下“确定”按钮，即可看到选定的工具栏从屏幕上消失。
2.6.4 移动工具栏
我们也可以将“工具栏”移动到应用程序窗口中所希望的任何地方，或者沿Microsoft Excel工作区的边界将工具栏定位。移动工具栏时，我们只需用鼠标指向工具栏的标题栏，然后按下鼠标左键，拖动工具栏到指定位置，松开鼠标键即可。
折叠对话框 Excel 2000 新增的在对话框中输入区域引用可以折叠的按钮，这样用户在工作表上选定所需的区域时，对话框就不会阻挡视线。对这类对话框的操作是，在折叠按钮上单击可以将其折叠，再次单击则返回到对话框，图2-9和图2-10分别显示了折叠前后的效果。
当心Excel四舍五入的陷阱 如果有人问你，Excel c2单元格中显示的数字是48，那么d2=c2×1.36的值是多少，你一定会脱口而出：65.28，其实不然。若48是你输入的，则“回答正确”；如果48是某个公式计算出来的结果，那么实际计算结果则可能是65.90。为了便于说明问题，还是从一个与工资计算有关的实例说起。 假设Excel工作表中有d2=356.68、e2=128.12，将d2与e2之和乘以0.1，将结果四舍五入取整，再将结果乘以1.36，取两位小数得到最终结果。 有人认为，可以事先选中f2单元格，使用“格式”选单下的“单元格”命令，将f2的小数位数设置为零。然后将（d2+e2）×0.1的结果放入f2单元格，即可四舍五入显示计算结果的整数部分。再将g2单元格的小数位数设成两位，最后把f2×1.36的结果存入其中就可以了。从表面上看，上述方法没有什么问题。在f2单元格的小数位数为零的条件下，（d2+e2）×0.1经四舍五入后的结果就是48。接下去却出了问题，因为f2×1.36的计算结果是65.90，根本不是48×1.36的正确结果65.28，其中65.90是（d2+e2）×0.1未经四舍五入直接乘以1.36的结果。 笔者经研究发现，在“单元格格式→数字”选项卡中设置的“小数位数”，只能将单元格数值的显示结果四舍五入，并不能对所存放的数值进行四舍五入。换句话说，数值的显示结果与单元格的“小数位数”设置有关，而显示结果和实际存放（即参与运算）的内容并不完全一致，如果不注意这个问题，计算工资等敏感数据就会出现错误。如上例中，f2单元格内的数值虽然显示为48，但实际存放的却是48.45，自然得出了48.45×1.36=65.90的结果（按计算要求应为65.28）。 要解决此问题，不能用设置“小数位数”的方法四舍五入。对本文所述的问题，只需在g2单元格内输入公式“=（round（（d2+e2）×0.1,0））×1.36”，就可以按要求计算出正确结果65.28。式中的round函数按指定位数对“（d2+e2）×0.1”进行四舍五入，函数中的参数0将“（d2+e2）×0.1”四舍五入到最接近的整数。由于四舍五入是一种应用很广的运算规则，笔者将round函数的使用方法简单介绍如下： round函数的语法为“round（number，num_digits）”，其中“number” 为需要四舍五入的数字或运算公式（其计算结果必须是数字）。num_digits指定四舍五入的位数，如果num_digits大于0，则四舍五入到指定的小数位，例如round（2.15，1）等于 2.2；如果num_digits等于0，则将数字四舍五入到整数，例如round（315.68，0）等于316；如果 num_digits 小于 0，则在小数点左侧的指定位数进行四舍五入，例如round（21.5，-1）等于20。 本文所述实例均在Excel 2000中验证通过。 在Excel中获取与更新外部数据 其实Excel有一个非常强大的功能，那就是获得网络数据并自动更新，这对于一些股票爱好者是比较有用的，这里就以一个时间例子来说明获取网络数据的具体方法。 一、导入并刷新网站信息 打开Excel 2000，在数据菜单中选择“获取外部数据”，在子菜单中选择“新建Web查询”，随后就会弹出一个对话框。在地址栏中输入数据所在的网站地址， 这里输入http://www./index.html，在下面选择“仅表格”。单击保存查询按钮，给查询定义一个名字，然后单击保存就可以了。返回到“新Web查询”对话框，直接单击“确定”就可以了，稍等一会所有数据就导入到Excel中了。
二、刷新Internet数据 选择需要更新的数据所在单元格，然后选择“数据”菜单下的“刷新数据”命令，就可以对数据进行更新了。或者我们通过工具栏来完成，右键单击工具栏，在弹出的菜单中选择“外部数据”，外部数据工具栏就显示出来了，其中有两个按钮可以更新数据，“更新数据”只更新选择的数据，“全部更新”会更新工作表中所有的外部数据。 三、自动更新Internet数据 在外部数据工具栏中，单击“数据区域属性”按钮，选择“允许后台刷新”，并选择下面的“刷新频率”复选框，设置需要的更新时间，最后选择下面的“打开工作簿是，自动更新”，这样就不用自己手动更新数据了，Excel会定时的自动为你从Internet上下载最新的数据（如下图所示）。
四、运行一个保存的查询 如果以前已经保存了一个网络数据查询，只要选择数据菜单，然后选择“获取外部数据”下的“运行保存的查询”，找到一个查询名，最后选择打开就可以了。 处理折线图表的缺失数据 当在Excel中创建了一个折线图表时，如果数据区域有些单元格数据缺失，也就是该单元格为空白，则折线图中就会出现缺口，这样就导致折线图不美观，或者不太能够直观地反映出数据的变化趋势。 如图1所示为存在缺失数据的一个折线图，可以看到折线上的缺口。
图1 Excel提供了两种方法来处理折线图中的缺失数据： - 以零值代表：将空白单元格作为零处理，对于零值数据点，行跌落至零。
- 以内插值替换：用内插值数据点代替空白单元格，填充空距以连接行。
我们先来看一下这两种方法所实现的效果。如图2所示为以零值代表的折线图。如图3所示为以内插值替换的折线图。
图2
图3 是怎么实现的呢？请按照如下步骤进行操作。 1.在Excel中选中要处理缺失数据的折线图表。 2.选择菜单命令“工具>选项”，打开“选项”对话框。 3.单击“图表”选项卡。 4.选择“空单元格的绘制方式为”中的“以零值代表”或“以内插值替换”，如图4所示。
图4 5.单击“确定”按钮，就可以看到图表的变化了。以上选择会作用到所选图表的所有数据系列。 还有一种办法是在空白单元格中输入公式=NA()。这样一来图表就会为包含该公式的单元格使用内插值，而不管在“选项”对话框中选择的是什么。(完)
|
