Welcome! My friends!

MS Excel 高级课程--第三章

上一篇 / 下一篇 2007-09-21 00:00:00 / 个人分类：OA

三. 数据分析和自动化
1. 数据的导入、查询和导出
1.1 数据：导入用分隔符的文本文件
最常用的2种分隔符：逗号、制表符；
要导入数据，你首先需要单击‘数据’菜单，将鼠标指向‘导入外部数据’命令，并单击
‘导入数据’命令。然后，你可以选择相应的文本文件类型，单击打开，要指定分隔符的
类型，你需要选中相应的复选框，并单击‘下一步’按钮，然后，将数据格式应用于导入
的数据；下一步，你需要单击‘完成’按钮，并选择要导入文件的工作表。
1.2 数据：导入固定宽度的文本文件
要导入固定宽度的文件，你首先需要单击‘数据’菜单，将鼠标指向‘导入外部数据’命令，并单击
‘导入数据’命令。然后通过浏览选择要导入的文件。
1.3 从数据库导入数据
要从数据库中导入数据，首先单击Excel中的‘数据’菜单，将鼠标指向‘导入外部数据’命令，并单击
‘导入数据’命令。在‘选取数据源’对话框中，你可以指定文件格式和要导入的数据库，打开，
接下来，你需要查找要导入的表，并将其打开。最后，选择一个现有工作表将该表导入到其中，或
‘新建工作表’选项将该表导入新建工作表中。

1.4 从Web中导入数据
Web查询用于检索存储在Internet或Intranet中的数据，通过创建Web查询，你可以从XML、HTML数据源、
以及Web站点上检索数据。
通过创建Web查询，你可以从Web站点选择信息，并将信息导入现有的Excel工作表或新建的Excel工作表中。
单击‘数据’菜单，将鼠标指向‘导入外部数据’命令，并单击，再选择‘新建Web查询’命令。
然后，你可以在‘地址’栏输入Web地址并单击‘转换’按钮。接下来，你需要选择所导入的数据，
并单击‘导入’按钮。
1.5 RTD
实时数据函数(RTD)，Excel2002提供了一种全新的工作表函数RTD，使用该函数你可以调用组件对象
(COM)自动化服务器，从而达到实时检索数据的目的。
COM加载项时通过添加自定义命令和指定的功能来扩展Office程序的补充程序。COM加载项可以在一个
或多个Office XP应用程序中运行，他所使用的文件扩展名为'.dll'或'.exe'。
如果你所创建的工作簿包含实时更新的数据，如财务或研究数据等，你就可以使用RTD工作表函数。
优势：功能强大，稳定性可靠，操作简便；
RTD函数是基于COM技术开发的，其优势在于功能强大、稳定可靠和操作简便。RTD会根据RTD
服务器中为Excel提供实时数据。
RTD函数会在RTD服务器检索数据，并将数据作为工作簿提供，当服务器中出现新数据时，
就会更新该函数的结果，从而更新工作簿的内容。其它函数仅在重新计算工作表时才更新。
服务器会在Excel处于空闲状态时进行更新，这样开发人员就不必确定Excel是否能够接受更新
的内容了。
虽然RTD函数提供向服务器中数据的连接，但是这种链接不同于工作表或工作簿中的单元格引用。
在工作簿中使用RTD函数
在打开该工作簿时不会受到链接启动消息
你无法通过‘编辑链接’对话框对RTD函数的状态进行更改。
你必须在本地计算机上创建并注册RTD COM自动化加载项。Office XP没有RTD服务器，因此
如果你并未安装实时数据服务器，则在试图使用RTD函数时，将在单元格中出现一条错误消息。
如果服务器急促更新结果，那么与其它函数不同，RTD公式在Excel处于自动计算模式下进行更改。
RTD函数所使用的语法为：
=RTD(ProgID,server,topic1,[topic2]。
ProgID是一个字符串，他表示已安装在本地计算机上，经过注册的COM自动化加载宏的
ProgID名称。
Server表示运行加载宏的服务器名称，如果没有服务器，程序是在本地计算机上运算，
那么参数为空白，否则，用引号将服务器名称引起来。
其他参数‘topic1’和‘topic2’等等，是表示发送到RTD服务器的参数。你可以使用
1到28个topic，这些参数放在一起代表一个唯一的实时数据。
RTD函数可以在多种情况下使用，例如，你可以通过检索服务器中的信息来传输有关几位同时进行
竞选的参议员候选人的连续数据。Excel所提供的服务器是名为Election.dll的动态链接库(DLL),
它安装在用户的计算机上，而且它是由它自己的安装进程注册为COM加载项。
RTD函数在Excel处于空闲状态时更新数据，因此如果Excel处于手动计算模式，它将继续接受信息，
在这种情况下，Excel将对数据进行缓存，并在执行手动计算时使用当前值。
Excel的安全特性将RTD服务器视为宏，这一功能意味着，如果你将安全性设置为‘高’，则只有经过
数字签名且可信的RTD服务器才可使用。在RTD服务器不可用时，依赖其数据的那些单元格将显示
‘#N/A’。
如果你将安全性设置为‘中’，则在打开使用RTD函数的工作簿时，会收到一条错误提示信息。
1.6 以文本文件格式导出数据
文本文件格式也称为制表符分隔格式，其扩展名为‘.txt’；制表符分隔文件格式仅保存活动工作表
中但元格中的文本和值，数据列由制表符分隔，而每个数据行以回车符结束。
如果某个单元格中包含逗号，该单元格中的内容会由双引号括起来，如果单元格中显示的是公式，而不是
根据公式计算的值，Excel会将所显示的公式保存到文本文件中，所有的格式设置、图形和其他工作
表内容都将丢失。
你也可以按逗号分隔格式保存文件，其文件扩展名为‘.csv’。逗号分隔文件也是文本文件的一种类型，
此文件格式仅保存活动工作表但元格中的文本和值。
单击‘文件’菜单中的‘另存为’命令，并输入一个文件名，在‘保存类型’下拉列表框中选择
‘文本文件（制表符分隔）’选项，保存。
1.7 将数据另存为XML电子表格
XML是将结构化信息（如工作表中数据）放置到文本文件（标准化且可由各种应用程序读取）的一种方法。
XML是一种格式，用于描述Web文档的内容，与HTML不同，xml是对HTML的补充，HTML描述文档的外观。
使用XML，设计人员可以在文本文件中创建他们自己的数据描述（称为标记）。使用自定义标记，你可以
在不同的Web应用程序之间使用经过定义、传输、有效性验证和说明的结构化数据。
Microsoft还可以定义一个专为Excel工作表设计的XML电子表格格式，这意味着你可以打开和保存任何正确
结构化的XML文件，创建正确结构化的XML数据源的Web查询，并将整个工作簿另存为XML电子表格。
当你以XML电子表格格式保存工作簿时，Excel的许多特性会保留下来，某些特性则会丢失，不会保存的
特性有：图表和图形，宏和对话框表，自定义视图、绘制对象层、共享工作簿信息以及用户定义的
功能类别。
单击‘文件’菜单中的‘另存为’命令，然后在‘文件名’框中键入一个文件名。接着单击‘保存类型’
下拉表框，单击‘XML表格’选项。
要发布XML文件，你可以直接将文件保存到Web文件夹中，也可以将它保存到本地文件夹中，然后将其导入到
Web文件夹中。在保存XML文件之后，你就可以用FrontPage发布它了。
在FrontPage中，你需要打开包含XML文件的Web文件夹，如果Web文件夹中尚未包含你要的XML文件，则
可以通过‘文件’菜单中的‘导入’命令条用‘将文件添加到导入列表’功能来导入该文件。
要发布整个Web文件夹，你可以单击‘文件’菜单，点击‘发布站点’命令，然后指定要发布到的
目标站点。如果你希望仅发布XML文件，则可以单击文件夹，右键单击该文件夹，然后选择‘发布
选定文件’。
2. 生成数据透视表
2.1 创建数据透视表
首先选取数据清单中的一个单元格，然后单击‘数据’菜单中的‘数据透视表和数据透视图’
命令，打开‘数据透视表和数据透视图向导’。你可以接受前两页中的默认设置，然后单击
第三项中的‘布局’按钮，你可以将相应的字段拖拽到‘行’、‘列’、‘数据’和‘页’这些区域
中，然后单击‘确定’按钮关闭‘布局’对话框，最后单击‘完成’。
2.2 数据透视表报告的格式
单击‘数据透视表’工具栏中的‘设置报告格式’按钮，可以打开‘自动套用格式’对话框。
自动套用格式’对话框提供了10种报表选项。这些报表不会删除或更改任何数据，但会以不同的方式
显示你的数据；
自动套用格式’对话框还提供了10个表选项，这些表选项可以以不同的方式设置数据透视表的格式、
如字体、颜色、网格线等。
最后两个是‘传统数据透视表’和‘无’。
2.3 数据透视表：更改汇总函数
汇总函数是一种对数据透视表中的源数据值进行合并的计算类型，求和和平均值均属于汇总函数。
单击数据透视表汇总数据区域中的单元格，然后打开‘数据透视表字段’对话框，并从‘汇总方式’
列表中选择所需的汇总函数。
2.4 隐藏数据透视表中的数据
要隐藏页字段中的数据，单击页字段标题，然后打开‘数据透视表字段’对话框，并在‘隐藏数据项’
列表中选择要隐藏的项，单击‘确定’。
要隐藏行字段或列字段中的数据，你需要单击所需标题上的箭头。然后清除要隐藏的数据清单中的
复选标记。
2.5 处理数据透视表中的字段
你可以通过添加或移除字段来处理数据透视表中的数据。要添加字段，你可以将‘数据透视表字段列表’
任务窗格中的字段项拖拽到相应的区域中。要隐藏字段，单击要隐藏的字段标题，然后，单击
‘数据透视表’工具栏上的‘数据透视表’按钮，单击‘隐藏’命令。
2.6 数据透视图
数据透视图报表以图形形式表示数据透视表中的数据，对于常规图表，用户要为查看的每张数据视图
创建一张图表。而对于数据透视图报表，只要创建单张图表就可通过更改报表布局或明细数据
以不同的方式查看数据。
数据透视图中包含页字段、数据字段、系列字段和分类字段。你可以使用页字段按特定类别对数据进行
排序；
数据透视图可更改为除XY散点图，股价图或气泡图之外的其他任何图表类型。
使用数据透视图，你实际上无法移动或调整绘图区、图例、图表标题或坐标标题，但是，你可以为图例
选择一个预设位置，而且可以更改标题的字体大小。
3. 使用宏
3.1 录制宏
宏是一系列命令和操作，它存储在工作簿的Microsoft Visual Basic模块中，如果你需要经常在Microsoft
Excel中重复某项任务，那么可以用宏自动执行该任务。
要在工作簿中录制宏，单击‘工具’菜单，将鼠标指向‘宏’命令，单击‘录制新宏’命令后，
即会显示‘录制新宏’对话框。在该对话框中，你需要为宏命名，如果要
通过按键盘上的快捷键来运行宏，你可以在‘快捷键’框中输入一个字母，你还可以对宏进行说明，
并选择宏的保存位置，然后单击‘确定’按钮，接着执行需要记录的步骤，最后单击‘停止录制’结束。
3.2 运行宏
宏会记住操作过程中的每一步，然后自动地运行这些步骤，通常情况下，宏包含在录制它的工作簿中。
要运行宏，必须在Excel中将宏安全性设置为中或低，单击‘选项’对话框中的‘安全性’选项卡，再单击
‘宏安全性’按钮，可选择级别。
在设置安全性之后，你可以运行存储在所有打开的工作簿中的宏。打开包含宏的工作簿是很重要的，如果将
宏设计为从当前活动单元格运行，则你必须在运行宏之前，选择所需的单元格。
单击‘工具’菜单，将鼠标指针指向‘宏’命令，并单击子菜单中的‘宏’命令，然后，你需要从列表中
选择所需要的宏，单击‘运行’按钮，如果制定了快捷键，可用快捷键来运行。你可以通过ESC键或
Ctrl+break键中断运行。
3.3 编辑宏
你可以使用Visual Basic编辑器来修改宏。
要添加或修改步骤，你必须首先打开存贮宏的工作簿，单击‘工具’菜单，将鼠标指针指向‘宏’命令，
单击‘宏’子命令，显示‘宏’对话框，然后单击列表中所需的宏，在单击‘编辑’按钮后，
将打开Visual Basic编辑器，其中会列出所选宏包括的所有步骤。
3.4 为宏设置自定义按钮
当你需要频繁地使用宏时，可以在工具栏或菜单中为其指定一个自定义按钮。
要为宏设置自定义按钮，你必须将宏存储在当前打开的工作簿中，然后，当你在另一工作簿中
运行此宏时，相关文件就会打开并运行宏。
你也可以在工作表上为宏设置图形对象或按钮。如果你右键单击对象，则会显示一个快捷菜单。
你可以单击‘指定宏’命令，然后单击‘指定宏’对话框中所需的宏。
要将‘自定义按钮’放置在工具栏中，你需要单击‘工具’菜单，并单击‘自定义’命令，
在‘自定义’对话框中，你需要单击‘命令’选项卡，并向下滚动‘类别’列表，选择
‘宏’选项，并单击‘自定义按钮’命令，然后将它拖拽到工具栏中适当位置。
有两种方法可以为宏指定‘自定义按钮’，一种是在‘自定义’对话框打开时右键单击按钮，
并单击‘指定宏’，然后单击宏的名称，并单击‘确定’按钮。
另一种方法是，关闭‘自定义’对话框，并单击‘自定义按钮’，‘指定宏’对话框即会
自动打开。你可以单击宏的名称，并单击‘确定’按钮，再单击按钮后，就会运行宏。
4. 数据分析工具
4.1 创建方案
使用Excel中的‘方案管理器’，你可以创建各种方案；使用方案，你可以通过修改电子表格
的内容并查看这些更改对公式的影响，来查看假设分析结果。
要创建方案，你首先需要单击‘工具’菜单中的‘方案’命令，然后，单击‘添加’按钮，
键入方案名，并按下Tab键，使用Ctrl键，你可以选择工作表中包含可变值的单元格区域。
最后，你需要将可变值键入到每个可变单元格框中，按下Tab键并单击‘确定’按钮。
如果你需要创建另一个方案，请单击‘添加’按钮。
4.2 查看方案
单击‘工具’菜单中的‘方案’命令，然后在‘方案管理器’中选择要查看的方案，最后单击
‘显示’按钮。
4.3 创建方案报告
创建方案报告，Excel会自动地设置方案报告的格式，方案报告会对每个方案的可变值进行比较，
要创建方案报表，你首先需要单击‘工具’菜单中的‘方案’命令，然后选择方案，
并单击‘摘要’按钮。
‘方案摘要’报告类型是默认的报告类型；默认情况下，会显示所选方案的可变单元格。与
这些单元格值相关的所有方案都将包含在报告中，最后，单击‘确定’按钮可以确认操作。
4.4 单变量求解
‘单变量求解’可以通过调整另一个单元格中的值，求出指定单元格中的值。
‘单变量求解’只能根据一个变量来预测结果。‘单变量求解’对话框中包含三个框。包含要解析
公式的单元格称为‘目标单元格’。
在‘目标值’框中，你可以输入所需要的常量值；‘可变单元格’框中包含的是需要更改的值
所在的单元格。该单元格必须由‘目标单元格’框中指定的单元格的公式引用。
如果你的公式包含多个变量，则无法使用‘单变量求解’，屏幕中会显示一条警告消息；
‘单变量求解’只能根据其他已知的值计算一个变量。你必须在已知目标结果的情况下使用单变量求解。
要使用‘单变量求解’，你首先需要单击包含所解析公式的单元格，然后单击‘工具’菜单中的
‘单变量求解’命令。在‘单变量求解’对话框的‘目标值’框中，你可以键入所需的常量值，并按下
Tab键，然后，单击需要用作‘可变单元格’的单元格，最后单击‘确定’按钮。
4.5 规划求解
Excel提供了‘规划求解’加载项来帮助你分析使用‘单变量求解’无法解决的复杂方案，他用于
预测给定多个变量时的最佳解。
规划求解是一个加载项，所以在一些用户的‘工具’菜单中不会出现‘规划求解’命令。只有完全
安装Excel才可以使用‘规划求解’命令，要将‘规划求解’命令添加到‘工具’菜单，你需要单击
‘加载宏’命令，选中‘规划求解’复选框，并单击‘确定’按钮，
规划求解用于解决涉及更改多个变量或单元格值的问题。‘单变量求解’命令只能处理一个变量，
而‘规划求解’可以处理多个变量以及多个约束。
虽然约束不是必须的，但是他们可以根据你的需要构造问题。
在某些情况下，规划求解无法求出解，这时，会在目标单元格中显示最接近于问题解的数值。如果给
出的解不是你所希望的，则可以通过更改约束来进行编辑。
你必须先打开一个结构正确的工作表，才能使用‘规划求解’，此外，你还必须在相关的单元格中
输入公式。你可以使用‘工具’菜单中的‘公式审核’命令来检查公式。如果公式中存在错误，
则‘规划求解’将无法运行。
单击‘工具’菜单中‘规划求解’命令。在‘规划求解参数’对话框中，你可以选择目标单元
格，然后单击‘最大值’、‘最小值’或‘值为’单选按钮。单击‘可变单元格’的‘压缩对话’
按钮可以暂时压缩对话框，你可以再按住Ctrl键的同时单击单元格。要指定约束，你可以单击‘添加’
按钮，然后单击‘求解’按钮。
在‘规划求解结果’对话框中，你可以进行选择来应用‘规划求解’求得的新值，或恢复工作表的原始值，
还可以将新值另存为方案，最后，单击‘确定’按钮可以完成操作。