Excel与数据处理（二）：ch9

第 9章 外部数据的导入与分析
? Excel是一个极好的数据分析和图表分析工具,
也是一个高效的数据输入工具 。 将它和专业数
据库应用系统相结合, 作为数据库系统的输入
工具或数据分析工具, 能解决数据库系统应用
过程中的许多问题, 极大地提高工作效率 。
本章 学习目标
1,了解 Excel与外部数据交换的方法
2,掌握 Excel访问外部数据的条件
3,了解 MsQuery的功能及用法
4,掌握 MSQuery单表和多表查证方法
5,掌握 Excel与 ACCESS,VFP交换数
据的方法
6,掌握 Excel与文本文件交换数据的
方法
9.1 导入 Web数据
1,概述
? Excel提供了 Web页数据的导入功能, 能够将因
特网中的网页数据导入 Excel进行分析 。
2,导入 Web数据的方法
1） 保证计算机已经与网络连接 。
2） 选择 Excel的, 数据, |“导入外部数据, |“新建
Web查询,, Excel会弹出, 新建 Web查询, 对话
框, 如下图 9所示
9.1 导入 Web数据
? 3） 在图中, 地址, 文本框中输入 Web页的网络地址, 单击图中的, 转
到, 按钮 。 稍后系统就会在该对话框中显示完整的 Web页 。 图中显示
的是新浪网的股票交易 Web页 。
9.1 导入 Web数据
? 4） 单击 Web页数据区域中要导入数据前面的按钮 。
? 5） 单击图中的, 导入, 按钮后, Excel会弹出数据存放位置
的设置对话框, 如图 （ a） 所示 。 图 （ b） 是导入到 Excel中
的 Web数据 。
9.2 Excel与 Word的数据交换
? 概述
? Miscrosoft Office系统内的各种软件之间的数据交
换非常方便, 通过复制和粘贴就能够传递数据 。
Excel与 Word是 Microsoft Office系统中最常用的两
种办公应用软件, Word常用于处理文字, Excel常
用于表格计算 。
? 方法
（ 1） 选中 Excel中的表格数据区域, 然后单击工具栏
中的, 复制, 按钮 （ 或按 Ctrl+C键 ） 。
（ 2） 单击 Word文档中要插入 Excel表格的位置, 然
后单击 Word 工 具 栏 中 的, 粘贴, 按钮 （ 或按
Ctrl+V键 ） 。
9.3 Excel与文本文件的数据转换
1,文本文件概述
? 文本文件是计算机中的一种通用格式的数据文件,
它可以在各软件系统之间传递数据 。 几乎所有的
软件系统都可直接操作文本文件 。 例如, Word、
Excel,Access,FoxPro,Porer Point,Sybase、
Oracle,C语言编辑器等, 都能建立, 修改或读入
文本文件 。
9.3 Excel与文本文件的数据转换
? 2,将 Excel工作表保存为文本文件
9.3 Excel与文本文件的数据转换
? 2,导入文本文件到 Excel工作表中
? 步骤之一：选择 Excel的, 文件, |“打开, 菜单, 并
在, 打开, 对话框中指定, 文本文件 （ *.prn） …,
类型 。
9.3 Excel与文本文件的数据转换
? 2,导入文本文件到 Excel工作表中
? 步骤之二：文本文件中各列的间隔方式
9.3 Excel与文本文件的数据转换
? 2,导入文本文件到 Excel工作表中
? 步骤之三：指定各数据之间的间隔符
9.3 Excel与文本文件的数据转换
? 2,导入文本文件到 Excel工作表中
? 步骤之四：指定各列数据的类型及是否导入 。
9.4 Excel与外部数据库
1,概述
? 将 Excel与数据库系统结合起来, 运用数据库系统
管理, 组织, 存储数据, 用 Excel为数据库输入数
据, 对数据库系统处理的结果数据进行分析, 制图
或作财务分析等, 将会极大地提高工作效率, 达到
事半功倍的效果 。
? ODBC（ Open Database Connetivity）
? 即人们常说的, 开放式数据库互连,, 它是数据库系统
的一种接口技术, 可用于不同的 应用程序, 如 Visual
Basic,Excel,MISCROSOFT Qurey等 。 ODBC主要用于
数据库系统与其他应用程序之间的通信, 它提供应用程
序与数据库系统之间进行通信的一些技术细节 。
9.4 Excel与外部数据库
? ODBC驱动程序
? ODBC 驱动程序是符合, 开放数据库互连, 标准
的数据源驱动程序 。 它提供了多种数据库管理系统
的驱动程序, 如 Access,SQL Sever等, 通过这
些驱动程序, 可以直接访问相关数据库系统中的数
据 。
? 数据源
? 就是数据的来源, 是要访问的数据库的一组存储信
息, 它包含了用于数据库连接所必需的信息 。 另外,
还包括数据库服务器的名称和位置, 用于连接数据
库的驱动程序的名称, 以及在登录到数据库时所需
的信息 。
9.4 Excel与外部数据库
2,访问外部数据库的条件
? 1） 具有访问外部数据的权限
? 2） 系统中安装了 Microsoft Query
? 3） 具有访问外部数据库的 ODBC 驱动程序
9.5 Microsoft Query介绍
? 1 Microsoft Query概述
? Microsoft Query可作为一个独立的工具单独运行,
用以查询各种数据库中的数据, 也可以在 Excel的
工作表中调用它, 用于将外部数据库中的数据导入
到 Excel的工作表中 。
? 使用 Query 从外部数据库中检索数据到 Excel的工
作表, 可以减少数据的重复输入 。
? 此外, 当外部数据库更新数据时, Miscrosoft
Query还可以刷新 Excel工作表中的数据, 以保持与
外部数据的一致性 。
9.5 Microsoft Query介绍
? MSquery 用于
将外 部数据 查
询到 Excel中分
析
9.5 Microsoft Query介绍
? 2 Miscrosoft Query,数据源及 ODBC
9.6 用 MS Query查询外部数据
? 案例
? 某人在 Access系统中建立了一个学生管理数据库系
统, 存放在, C:\My Documents”目录下, 名字是
db4.mdb。 该数据库中有学生, 教师, 学生选课及
成绩等数据表 。 其中, 学生档案数据表如下所示 。
? 现在要建立一个访问该数据库的数据源, 并且将该
数据库中的所有数据查询到 Excel中进行分析处理 。
? 注意：将,XCEL资料, 目录中的, 学生教学管理,mdb”文
件复制到 C:\My Document目录中, 且改名为 db4.mdb。
9.6 用 MS Query查询外部数据
? ACCESS学生管理数据库中的学生档案表
9.6 用 MS Query查询外部数据
1,查询外部数据步骤之一 ——建立数据源
? 选择, 数据, |“获取外部数据, |“新建数据库查
询, 菜单项, 系统会弹出如图示对话框 。
选中, 新数
据源,,然
后, 确定,
9.6 用 MS Query查询外部数据
? 设置数据源的各项参数
1、在此输入数据源
名字（可任取名字
2、在此选择正确
的数据库驱动程序
3、完成 1,2后单
击, 连接,,见下
面的对话框
9.6 用 MS Query查询外部数据
? 设置数据源对应的数据库
单击, 选择, 按钮然
后从弹出的 文件选择对
话框 中找到对应的数据
库文件，选定文件后,
单击, 确定,,会见到
下述对话框！
9.6 用 MS Query查询外部数据
? 建立的数据源
建立的数据源
9.6 用 MS Query查询外部数据
2,查询外部数据步骤之二 ——访问数据源
? 在上图中, 选中要访问的数据源, 然后单击, 确
定, 按钮, 就会见到下述对话框 。
1、选中要访
问的数据表
2,
单
击
9.6 用 MS Query查询外部数据
? 3,查询到 Excel中的数据表
? 根据, 查询向导, 提示, 不断单击向导对话框的
,下一步,, 会见到最后一步, 向导对话框,
? 选择其中的, 将数据返回 Miscrosft Office Excel”，
会得到下页所示的工作表 。
9.6 用 MS Query查询外部数据
? 从外部数据库查询到的数据表
9.6 用 MS Query查询外部数据
4,建立多表查询
? 数据库中的数据表与 Excel的工作表有许多差异, 数据库中
的表要讲究一定的数据规范, 这种规范可能导致数据库中
有许多不同的数据表 。
? 比如学生库中的成绩, 课程和学生档案就分散在不
同的数据表中 。
9.6 用 MS Query查询外部数据
? 多表查询的方法
? 指定了查询的数据源, 在, 查询向导 ——指定列, 的对话
框中, 从多个数据表中选中需要的全部数据字段名字 。
? 在本例中, 从学生表中选中学号, 姓名, 班级；从课程表
中选中课程编号, 课名；从成绩表中选中成绩 。
9.6 用 MS Query查询外部数据
? 按向导做完其余步骤, 最后得到如下工作表
9.7 操作 MS Query
1,概述
? Miscrosoft Query是一个非常有用的数据库查询软件, 它可
以在 Windows系统中独立运行, 也可以通过加载宏的方式
在 Excel中运行 。
? 在 Excel中操作 MS Query的方法是：在通过查询向导从外部
向导的最后一步骤中, 选择, 在 Microsoft Query中查看数
据或编辑查询 （ Y）,
9.7 操作 MS Query
? 2,MS Query的操作界面
9.7 操作 MS Query
? 3,添加, 删除查询结果中的列字段
用鼠标将这时的
列字段拖放到下
面的窗口中，即
可将对应列数据
查询出来！
选中按 Delete键
即可删除对应用
列的查询结果
9.7 操作 MS Query
? 4,添加或删除表窗口中的表
单击 Query中
的,可见添加
表窗口
选中表名，再, 添加,
9.7 操作 MS Query
? 5,多表查询
? 多表连接关系 只有正确地建立
了多表之间的连
接字段后，多表
查询才能有正确
的结果
9.7 操作 MS Query
? 建立多表连接关系的方法
? 方法 1：从一个表中将连接字段直接拖放到另一个表中
的连接字段上 。
? 方法 2：：选择 Miscrosoft Query“表, |“连接, 菜单
项, Miscrosoft Query会弹出一个建立关系的对话框,
9.7 操作 MS Query
?,6 条件查询
2、条件区
域，在此
区域输入
查询条件
1、若不见
条件区域，
单击
9.7 操作 MS Query
? 3,参数条件查询
? 在条件中输入 [],就表示对参数查询, 在 []内的文
字是提示信息 。
执行此查询时将显示下面
的对话框，输入一个名字，
将只查询对应学生的数据
9.8 Excel与其他文件相互转换
1,概述
? Excel可以直接打开多种不同类型的文件, 如文
本文件, dBASE文件, Web网页, Excel低版本
的文件, 模板文件, Miscrosoft Query建立的
查询文件, Lotus 1-2-3文件等, 然后将这些文
件转换成为 Excel工作簿；
? Excel也可以将工作簿文件保存为文本文件, 以
Web页的方式发布到网上, 或保存为 Lotus 1-2-
3文件等 。
9.8 Excel与其他文件相互转换
2,将 Excel文件转换成其它类型的文件
选择, 文
件, |,另存为,
菜单命令，从
保存类型的下
拉列表中选择
需要的文件类
型
9.8 Excel与其他文件相互转换
? 由 Excel工作表转换成的 WEB网页
9.9 导入外部数据应用举例
? 【 例 1】 某单位有一医疗管理系统是用 FoxPro
编写的, 其中有一个汇总数据库, 假设数据库
的文件名为, 医疗,dbf”,其中的数据如图所示 。
将此数据表中的数据导入 Excel
9.9 导入外部数据应用举例
? 【 例 2】 一家拥有 7000多职工的合资企业, 其人事档案管理
系统是用 FoxPro编写的, 该公司的财务部门每个月要对公司职
工的各项财务数据进行汇总和对比分析, 并作出各种分析报表
和图表 。 将此 VFP数据表导入 Excel并作出下页的分析图表 。
姓名
职工
号 类别
加班
时
间
加班
工
资 房屋补贴 其他津贴
生计津
贴
离职津
贴 餐卷
吴银 1664 STAFF 10 311 59.5 323.62 129.80 244.54 122.09
李小民 0987 STAFF 26 808.6 245.99 138.50 161.91 37.52 289.38
黄大能 0065 WORKER 11 342.1 239.71 451.43 238.95 324.67 3.20
张之勇 0298 WORKER 3 93.3 180.9 33.03 328.18 314.09 174.05
五军 0092 WORKER 9 279.9 260.55 276.54 328.42 395.91 165.15
赵百通 1005 STAFF 26 808.6 250.92 451.50 290.14 307.81 276.64
吴不晓 0942 STAFF 27 839.7 71.48 287.18 59.36 273.09 29.35
9.9 导入外部数据应用举例
? 例 2需要的最近两月对比分析表
9.9 导入外部数据应用举例
? 例 2需要的分析图 1
9.9 导入外部数据应用举例
? 例 2需要的分析图 2
9.8 Excel与其他文件相互转换
? 3 Excel与 Access数据交换
? Access与 Excel或 Word同是 Office软件系统中的
成员, 所以它们之间的数据交换远没有 Excel与
Fox系统之间进行数据交换那么复杂 。
? 在 Access系统中, 可以将数据库中的数据表直接
发送到 Excel的 工 作 表 中 进 行 分 析 ； 同 时, 在
Excel中可以直接启动 Access数据库系统, 将工作
表中的数据直接传递到 Access的数据表中 。
? 直接建立 Access数据库中的数据表与 Excel工作表
的链接, 使得在工作表中输入的数据可直接传递到
Access数据库中 。
9.8 Excel与其他文件相互转换
? 案例
? 某辅导员用 Access开发了一个学生管理系统, 名字
为, 学生档案,mdb”,其中的部分数据表及数据记
录如图所示 。
9.8 Excel与其他文件相互转换
4,直接将 Access数据发布到 EXCEL分析
9.8 Excel与其他文件相互转换
? 5,建立 Access数据表与 Excel工作表的链接
? Access提供了一种与 Excel进行通信的更高级的方
法, 那就是建立 Access数据表与 Excel工作表之间
的链接 。
? 方法
? 1） 选择 Access“文件, |“获取外部数据, |“链接, 命令 。
? 2） 根据链接向导提示作完其余步骤 。
? 1） 选择 Access“文件, |“获取外部数据, |“链接,
命令 。
? 2） 在, 链接, 对 话 框 的, 文 件 类 型, 中 指 定
,Microsoft Excel”,在, 文件名, 中指定要链接的
Excel 工 作 簿 名 称, 然 后 单 击, 链接, 按钮,
Access将弹出如图 9-80所示的链接向导第一步对话
框 。
The End