ch9外部数据的导入与分析.ppt

第第9 9章章 外部数据的导入与分析外部数据的导入与分析 n n ExcelExcel是一个极好的数据分析和图表分析工具，是一个极好的数据分析和图表分析工具， 也是一个高效的数据输入工具。将它和专业数也是一个高效的数据输入工具。将它和专业数 据库应用系统相结合，作为数据库系统的输入据库应用系统相结合，作为数据库系统的输入 工具或数据分析工具，能解决数据库系统应用工具或数据分析工具，能解决数据库系统应用 过程中的许多问题，极大地提高工作效率。过程中的许多问题，极大地提高工作效率。 本章本章学习目标学习目标 1 1、了解、了解ExcelExcel与外部数据交换的方法与外部数据交换的方法 2 2、掌握、掌握ExcelExcel访问外部数据的条件访问外部数据的条件 3 3、了解、了解MSQueryMSQuery的功能及用法的功能及用法 4 4、掌握、掌握MSQueryMSQuery单表和多表查证方法单表和多表查证方法 5 5、掌握、掌握ExcelExcel与与ACCESSACCESS、VFPVFP交换数交换数 据的方法据的方法 6 6、掌握、掌握ExcelExcel与文本文件交换数据的与文本文件交换数据的 方法方法 9.1 9.1 导入导入WebWeb数据数据 1.1.概述概述 nExcel提供了Web页数据的导入功能 ，能够将因 特网中的网页数据导入Excel进行分析。 2.2.导入导入WebWeb数据的方法数据的方法 1）保证计算机已经与网络连接。 2）选择Excel的“数据”|“导入外部数据”|“新建Web查 询”，Excel会弹出“新建Web查询”对话框，如下 图9所示 9.1 9.1 导入导入WebWeb数据数据 n3）在图中“地址”文本框中输入Web页的网络地址，单击图中的“转到” 按钮。稍后系统就会在该对话框中显示完整的Web页。图中显示的是 新浪网的股票交易Web页。 9.1 9.1 导入导入WebWeb数据数据 n4）单击Web页数据区域中要导入数据前面的按钮。 n5）单击图中的“导入”按钮后，Excel会弹出数据存放位置的 设置对话框，如图（a）所示。图（b）是导入到Excel中的 Web数据。 9.2 Excel9.2 Excel与与WordWord的数据交换的数据交换 n n 概述概述 nMiscrosoft Office系统内的各种软件之间的数据交 换非常方便，通过复制和粘贴就能够传递数据。 Excel与Word是Microsoft Office系统中最常用的两 种办公应用软件，Word常用于处理文字，Excel常 用于表格计算。 n n 方法方法 （1）选中Excel中的表格数据区域，然后单击工具栏 中的“复制”按钮（或按Ctrl+C键）。 （2）单击Word文档中要插入Excel表格的位置，然 后单击Word工具栏中的“粘贴”按钮（或按Ctrl+V键 ）。 9.3 Excel9.3 Excel与文本文件的数据转换与文本文件的数据转换 1.1.文本文件概述文本文件概述 n文本文件是计算机中的一种通用格式的数据文件 ，它可以在各软件系统之间传递数据。几乎所有 的软件系统都可直接操作文本文件。例如，Word 、Excel、Access、FoxPro、Power Point、 Sybase、Oracle、C语言编辑器等，都能建立、 修改或读入文本文件。 9.3 Excel9.3 Excel与文本文件的数据转换与文本文件的数据转换 n n 2 2将将ExcelExcel工作表保存为文本文件工作表保存为文本文件 9.3 Excel9.3 Excel与文本文件的数据转换与文本文件的数据转换 n n 2 2导入文本文件到导入文本文件到ExcelExcel工作表中工作表中 n步骤之一：选择Excel的“文件”|“打开”菜单，并在“ 打开”对话框中指定“文本文件（*.prn）”类型。 9.3 Excel9.3 Excel与文本文件的数据转换与文本文件的数据转换 n n 2 2导入文本文件到导入文本文件到ExcelExcel工作表中工作表中 n步骤之二：文本文件中各列的间隔方式 9.3 Excel9.3 Excel与文本文件的数据转换与文本文件的数据转换 n n 2 2导入文本文件到导入文本文件到ExcelExcel工作表中工作表中 n步骤之三：指定各数据之间的间隔符 9.3 Excel9.3 Excel与文本文件的数据转换与文本文件的数据转换 n n 2 2导入文本文件到导入文本文件到ExcelExcel工作表中工作表中 n步骤之四：指定各列数据的类型及是否导入。 9.4 Excel9.4 Excel与外部数据库与外部数据库 1 1、概述、概述 n将Excel与数据库系统结合起来，运用数据库系统 管理、组织、存储数据，用Excel为数据库输入数 据，对数据库系统处理的结果数据进行分析、制图 或作财务分析等，将会极大地提高工作效率，达到 事半功倍的效果。 nODBC（Open Database Connetivity） n即人们常说的“开放式数据库互连”，它是数据库系统的 一种接口技术，可用于不同的应用程序，如Visual Basic 、Excel、MISCROSOFT Qurey等。ODBC主要用于数据 库系统与其他应用程序之间的通信，它提供应用程序与 数据库系统之间进行通信的一些技术细节。 9.4 Excel9.4 Excel与外部数据库与外部数据库 n n ODBCODBC驱动程序驱动程序 nODBC 驱动程序是符合“开放数据库互连”标准的 数据源驱动程序。它提供了多种数据库管理系统的 驱动程序，如Access、SQL Sever等，通过这些 驱动程序，可以直接访问相关数据库系统中的数据 。 n n 数据源数据源 n就是数据的来源，是要访问的数据库的一组存储信 息，它包含了用于数据库连接所必需的信息。另外 ，还包括数据库服务器的名称和位置，用于连接数 据库的驱动程序的名称，以及在登录到数据库时所 需的信息。 9.4 Excel9.4 Excel与外部数据库与外部数据库 2 2、访问外部数据库的条件、访问外部数据库的条件 n1）具有访问外部数据的权限 n2）系统中安装了 Microsoft Query n3）具有访问外部数据库的ODBC 驱动程序 9.5 Microsoft Query9.5 Microsoft Query介绍介绍 n n 1 1 MicrosoftMicrosoft Query Query概述概述 nMicrosoft Query可作为一个独立的工具单独运行， 用以查询各种数据库中的数据，也可以在Excel的 工作表中调用它，用于将外部数据库中的数据导入 到Excel的工作表中。 n使用 Query 从外部数据库中检索数据到Excel的工 作表，可以减少数据的重复输入。 n此外，当外部数据库更新数据时，Microsoft Query 还可以刷新Excel工作表中的数据，以保持与外部 数据的一致性。 9.5 Microsoft Query9.5 Microsoft Query介绍介绍 n n MSqueryMSquery用于用于 将外部数据查将外部数据查 询到询到ExcelExcel中分中分 析析 9.5 Microsoft Query9.5 Microsoft Query介绍介绍 n n 2 Miscrosoft Query2 Miscrosoft Query、数据源及、数据源及ODBCODBC 9.6 9.6 用用MS QueryMS Query查询外部数据查询外部数据 n n 案例案例 n某人在Access系统中建立了一个学生管理数据库系 统，存放在“C:My Documents”目录下，名字是 db4.mdb。该数据库中有学生、教师、学生选课及 成绩等数据表。其中，学生档案数据表如下所示。 n现在要建立一个访问该数据库的数据源，并且将该 数据库中的所有数据查询到Excel中进行分析处理 。 n注意：将“XCEL资料”目录中的“学生教学管理.mdb”文件 复制到C:My Document目录中，且改名为db4.mdb。 9.6 9.6 用用MS QueryMS Query查询外部数据查询外部数据 n n ACCESSACCESS学生管理数据库中的学生档案表学生管理数据库中的学生档案表 9.6 9.6 用用MS QueryMS Query查询外部数据查询外部数据 1.1.查询外部数据步骤之一查询外部数据步骤之一建立数据源建立数据源 n选择“数据”|“获取外部数据”|“新建数据库查询”菜 单项，系统会弹出如图示对话框。 选中新数 据源，然 后“确定” 9.6 9.6 用用MS QueryMS Query查询外部数据查询外部数据 n设置数据源的各项参数 1、在此输入数据源 名字（可任取名字 2、在此选择正确 的数据库驱动程序 3、完成1、2后单 击“连接”，见下 面的对话框 9.6 9.6 用用MS QueryMS Query查询外部数据查询外部数据 n设置数据源对应的数据库 单击“选择”按钮然 后从弹出的文件选择对 话框中找到对应的数据 库文件，选定文件后， 单击“确定”，会见到 下述对话框！ 9.6 9.6 用用MS QueryMS Query查询外部数据查询外部数据 n建立的数据源 建立的数据源建立的数据源 9.6 9.6 用用MS QueryMS Query查询外部数据查询外部数据 2 2、查询外部数据步骤之二、查询外部数据步骤之二访问数据源访问数据源 n在上图中，选中要访问的数据源，然后单击“确定 ”按钮，就会见到下述对话框。 1、选中要访 问的数据表 2、 单 击 9.6 9.6 用用MS QueryMS Query查询外部数据查询外部数据 n n 3 3、查询到、查询到ExcelExcel中的数据表中的数据表 n根据“查询向导”提示，不断单击向导对话框的“下 一步”，会见到最后一步“向导对话框” n选择其中的“将数据返回Miscrosft Office Excel”， 会得到下页所示的工作表。 9.6 9.6 用用MS QueryMS Query查询外部数据查询外部数据 n n 从外部数据库查询到的数据表从外部数据库查询到的数据表 9.6 9.6 用用MS QueryMS Query查询外部数据查询外部数据 4 4、建立多表查询、建立多表查询 n数据库中的数据表与Excel的工作表有许多差异，数据库中 的表要讲究一定的数据规范，这种规范可能导致数据库中 有许多不同的数据表 。 n比如学生库中的成绩、课程和学生档案就分散在不 同的数据表中。 9.6 9.6 用用MS QueryMS Query查询外部数据查询外部数据 n n 多表查询的方法多表查询的方法 n指定了查询的数据源，在“查询向导指定列”的对话框 中，从多个数据表中选中需要的全部数据字段名字。 n在本例中，从学生表中选中学号、姓名、班级；从课程表 中选中课程编号、课名；从成绩表中选中成绩。 9.6 9.6 用用MS QueryMS Query查询外部数据查询外部数据 n按向导做完其余步骤，最后得到如下工作表 9.7 9.7 操作操作MS QueryMS Query 1 1、概述、概述 nMiscrosoft Query是一个非常有用的数据库查询软件，它可 以在Windows系统中独立运行，也可以通过加载宏的方式 在Excel中运行。 n在Excel中操作MS Query的方法是：在通过查询向导从外部 向导的最后一步骤中，选择“在Microsoft Query中查看数据 或编辑查询（Y）” 9.7 9.7 操作操作MS QueryMS Query n n 2 2、MS QueryMS Query的操作界面的操作界面 9.7 9.7 操作操作MS QueryMS Query n n 3 3、 添加、删除查询结果中的列字段添加、删除查询结果中的列字段 用鼠标将这时的 列字段拖放到下 面的窗口中，即 可将对应列数据 查询出来！ 选中按Delete键 即可删除对应用 列的查询结果 9.7 9.7 操作操作MS QueryMS Query n n 4 4添加或删除表窗口中的表添加或删除表窗口中的表 单击Query中 的 ，可见添加 表窗口 选中表名，再“添加” 9.7 9.7 操作操作MS QueryMS Query n n 5 5、多表查询、多表查询 n多表连接关系 只有正确地建立 了多表之间的连 接字段后，多表 查询才能有正确 的结果 9.7 9.7 操作操作MS QueryMS Query n建立多表连接关系的方法 n方法1：从一个表中将连接字段直接拖放到另一个表中 的连接字段上。 n方法2：选择Miscrosoft Query“表”|“连接”菜单项 ，Miscrosoft Query会弹出一个建立关系的对话框， 9.7 9.7 操作操作MS QueryMS Query n n .6 .6 条件查询条件查询 2、条件区 域，在此 区域输入 查询条件 1、若不见 条件区域 ，单击 9.7 9.7 操作操作MS QueryMS Query n n 3 3参数条件查询参数条件查询 n在条件中输入，就表示对参数查询，在内的文 字是提示信息。 执行此查询时将显示下面 的对话框，输入一个名字 ，将只查询对应学生的数 据 9.8 Excel9.8 Excel与其他文件相互转换与其他文件相互转换 1.1.概述概述 nExcel可以直接打开多种不同类型的文件，如文 本文件、dBASE文件、Web网页、Excel低版本 的文件、模板文件、Miscrosoft Query建立的 查询文件、Lotus 1-2-3文件等，然后将这些文 件转换成为Excel工作簿； nExcel也可以将工作簿文件保存为文本文件、以 Web页的方式发布到网上，或保存为Lotus 1-2- 3文件等。 9.8 Excel9.8 Excel与其他文件相互转换与其他文件相互转换 2 2、将、将ExcelExcel文件转换成其它类型的文件文件转换成其它类型的文件 选择“文件 ”|“另存为” 菜单命令，从 保存类型的下 拉列表中选择 需要的文件类 型 9.8 Excel9.8 Excel与其他文件相互转换与其他文件相互转换 n由Excel工作表转换成的WEB网页 9.9 9.9 导入外部数据应用举例导入外部数据应用举例 n n 【例例1 1】 某单位有一医疗管理系统是用某单位有一医疗管理系统是用FoxProFoxPro 编写的，其中有一个汇总数据库，假设数据库编写的，其中有一个汇总数据库，假设数据库 的文件名为的文件名为“ “医疗医疗.dbf.dbf” ”，其中的数据如图所示，其中的数据如图所示 。将此数据表中的数据导入。将此数据表中的数据导入ExcelExcel 9.9 9.9 导入外部数据应用举例导入外部数据应用举例 n n 【例例2 2】 一家拥有一家拥有70007000多职工的合资企业，其人事档案管理多职工的合资企业，其人事档案管理 系统是用系统是用FoxProFoxPro编写的，该公司的财务部门每个月要对公司职编写的，该公司的财务部门每个月要对公司职 工的各项财务数据进行汇总和对比分析，并作出各种分析报表工的各项财务数据进行汇总和对比分析，并作出各种分析报表 和图表。将此和图表。将此VFPVFP数据表导入数据表导入ExcelExcel并作出下页的分析图表。并作出下页的分析图表。 姓名职工 号类别 加班 时 间 加班 工 资 房屋补 贴其他津贴 生计津 贴 离职津 贴餐卷 吴银1664STAFF1031159.5323.62129.80244.54122.09 李小民0987STAFF26808.6245.99138.50161.9137.52289.38 黄大能0065WORKER11342.1239.71451.43238.95324.673.20 张之 勇0298WORKER393.3180.933.03328.18314.09174.05 五军0092WORKER9279.9260.55276.54328.42395.91165.15 赵百 通1005STAFF26808.6250.92451.50290.14307.81276.64 吴不 晓0942STAFF27839.771.48287.1859.36273.0929.35 9.9 9.9 导入外部数据应用举例导入外部数据应用举例 n n 例例2 2需要的最近两月对比分析表需要的最近两月对比分析表 9.9 9.9 导入外部数据应用举例导入外部数据应用举例 n n 例例2 2需要的分析图需要的分析图1 1 9.9 9.9 导入外部数据应用举例导入外部数据应用举例 n n 例例2 2需要的分析图需要的分析图2 2 9.8 Excel9.8 Excel与其他文件相互转换与其他文件相互转换 n n 3 Excel3 Excel与与AccessAccess数据交换数据交换 nAccess