《Excel在数据管理与分析中的应用.pptx》由会员分享，可在线阅读，更多相关《Excel在数据管理与分析中的应用.pptx（101页珍藏版）》请在taowenge.com淘文阁网|工程机械CAD图纸|机械工程制图|CAD装配图下载|SolidWorks_CaTia_CAD_UG_PROE_设计图分享下载上搜索。
1、本章学习目标1 1、了解加载宏的使用方法、了解加载宏的使用方法2 2、掌握数据审核的方法、掌握数据审核的方法3 3、掌握模拟运算表的建立方法、掌握模拟运算表的建立方法4 4、掌握单变量求解的方法、掌握单变量求解的方法5 5、掌握方案分析的方法、掌握方案分析的方法6 6、掌握线性规划求解的方法、掌握线性规划求解的方法7 7、掌握数据分析工具的应用方法、掌握数据分析工具的应用方法8.1加载宏与分析工具安装加载宏与分析工具安装1.加载宏的概念加载宏是一种可选择性地安装到计算机中的软件组件，用户可根据需要决定是否安装。其作用是为 Excel 添加命令和函数，扩充Excel的功能。Excel加载宏的扩展
2、名是.xla或.xll。8.1加载宏与分析工具安装加载宏与分析工具安装加加 载载 宏宏描描 述述与与 Access 链接链接创建可以使用创建可以使用 Excel 数据的数据的 Access 窗体和报表，并将窗体和报表，并将 Excel 数据导入到数据导入到 Access 中。要使用中。要使用 AccessLinks 加载宏，必须先安装加载宏，必须先安装 Microsoft Access 分析工具库分析工具库添加财务、统计和工程分析工具和函数添加财务、统计和工程分析工具和函数“自动保存自动保存”加载宏加载宏以指定间隔自动保存工作簿以指定间隔自动保存工作簿条件求和向导条件求和向导对于数据清单中满足
3、指定条件的数据进行求和计算对于数据清单中满足指定条件的数据进行求和计算欧元工具欧元工具将数值的格式设置为欧元的格式，并提供将数值的格式设置为欧元的格式，并提供EUROCONVERT函数以用于货币转换函数以用于货币转换查阅向导查阅向导创建一个公式，通过数据清单中的已知值查找所需数据创建一个公式，通过数据清单中的已知值查找所需数据ODBC 加载宏加载宏利用安装的利用安装的 ODBC 驱动程序，通过开放式数据库互连（驱动程序，通过开放式数据库互连（ODBC）功能与外部数据源相连）功能与外部数据源相连报告管理器报告管理器为工作簿创建含有不同打印区域、自定义视图以及方案的报告为工作簿创建含有不同打印区域
4、、自定义视图以及方案的报告规划求解加载宏规划求解加载宏对基于可变单元格和条件单元格的假设分析方案进行求解计算对基于可变单元格和条件单元格的假设分析方案进行求解计算MS Query 加载宏加载宏将采用将采用 Microsoft Excel 97 和和 Microsoft Excel 2000 格式的外部数据区域转换为格式的外部数据区域转换为 Microsoft Excel 5.0/95 格格式，并允许使用通过以前版本式，并允许使用通过以前版本 Excel 中的中的 Visual Basic for Applications 所创建的宏所创建的宏模板工具模板工具提供提供 Excel 的内置模板所使
5、用的工具。使用内置模板时就可自动访问这些工具的内置模板所使用的工具。使用内置模板时就可自动访问这些工具数据追踪模板向导数据追踪模板向导生成模板，通过与之链接的数据库中的记录进行数据跟踪和分析生成模板，通过与之链接的数据库中的记录进行数据跟踪和分析更新加载宏链接更新加载宏链接更新对更新对 Excel 4.0 版加载宏的链接，以便能直接访问版加载宏的链接，以便能直接访问 Excel 5.0/7.0、Excel 97 和和 Excel 2000 的内置功能的内置功能Internet Assistant VBA通过使用通过使用 Excel 97 Internet Assistant 语法，开发者可将语
6、法，开发者可将 Excel 数据发布到数据发布到 Web 上上2 2、ExcelExcel内置加载宏内置加载宏内置加载宏内置加载宏8.1加载宏与分析工具安装加载宏与分析工具安装3、加载宏的安装选择“工具”|“加载宏”菜单8.2数据审核及跟踪分析数据审核及跟踪分析1、概念数据审核是一种查找单元格数据错误来源的工具，通过它可以快速地找出具有引用关系的单元格，借此分析造成错误的单元格。数据审核使用追踪箭头，通过图形的方式显示或追踪单元格与公式之间的关系。2、数据审核的方式追踪引用单元格 追踪从属单元格8.2数据审核及跟踪分析数据审核及跟踪分析3、追踪引用单元格示例8.2数据审核及跟踪分析数据审核及跟
7、踪分析4、数据有效性 限制数据的范围 案例某班要建立一个成绩登记表，为了减少成绩输入错误，可以对成绩表中数据的输入类型及范围进行限制。限制学号为限制学号为8位字符，不能小于位字符，不能小于8位，也不能多于位，也不能多于8位。位。限制所有学科成绩为限制所有学科成绩为0100之间的整数。之间的整数。限制科目列标题的取值范围，如限制科目列标题的取值范围，如“高数高数”不能输入不能输入为为“高等数学高等数学”。8.2数据审核及跟踪分析数据审核及跟踪分析成绩表示例8.2数据审核及跟踪分析数据审核及跟踪分析限制方法选择“数据”|“有效性”限定数据类型限定数据类型限定方式限定方式设置数据大小范围或长度设置数
8、据大小范围或长度8.2数据审核及跟踪分析数据审核及跟踪分析5、圈释无效数据、圈释无效数据8.3模拟运算表模拟运算表1、概念模拟运算表是对工作表中一个单元格区域内的数据进行模拟运算，测试使用一个或两个变量的公式中变量对运算结果的影响。2、模拟运算表的类型基于一个输入变量的表，用这个输入变量测试它对多个公式的影响；单模拟运算表基于两个输入变量的表，用这两个变量测试它们对于单个公式的影响双模拟运算表8.3模拟运算表模拟运算表1、单变量模拟运算表概念在单变量模拟运算表中，输入数据的值被安排在一行或一列中。同时，单变量模拟表中使用的公式必须引用“输入单元格”。所谓输入单元格，就是被替换的含有输入数据的单
9、元格 案例假设某人正考虑购买一套住房，要承担一笔250 000元的贷款，分15年还清。现想查看每月的还贷金额，并想查看在不同的利率下，每月的应还贷金额。8.3模拟运算表模拟运算表1、建立模拟运算表、建立模拟运算表3、C4的公式中引用了的公式中引用了B4单元格，在实际计算单元格，在实际计算时，将用时，将用B列列B5:B11的值逐一代替公式中的值逐一代替公式中的的B4。2、选择、选择“数据数据”|“模拟运算表模拟运算表”8.3模拟运算表模拟运算表案例假设某人想贷款45万元购买一部车，要查看在不同的利率和不同的偿还年限下，每个月应还的贷款金额。假设要查看贷款利率为5%、5.5%、6.5%、7%、7.
10、5%、8%，偿还期限为10年、15年、20年、30年、35年时，每月应归还的贷款金额是多少 8.3模拟运算表模拟运算表模拟运算表的设置=PMT(B1/12,B2*12,D1)8.4单变量求解单变量求解概念所谓单变量求解，就是求解具有一个变量的方程，Excel通过调整可变单元格中的数值，使之按照给定的公式来满足目标单元格中的目标值.案例某公司想向银行贷款900万元人民币，贷款利率是8.7%，贷款限期为8年，每年应偿还多少金额？如果公司每年可偿还120万元，该公司最多可贷款多少金额？8.4单变量求解单变量求解单变量求解方法1、建立求解公式：2、设置求解公式3、求解结果8.5方案分析方案分析1、概念
11、方案是已命名的一组输入值，是 Excel 保存在工作表中并可用来自动替换某个计算模型的输入值，用来预测模型的输出结果。2、案例已知某茶叶公司2001年的总销售额及各种茶叶的销售成本，现要在此基础上制订一个五年计划。由于市场竟争的不断变化，所以只能对总销售额及各种茶叶销售成本的增长率做一些估计。最好的方案当然是总销售额增长率高，各茶叶的销售成本增长率低。最好的估计是总销售额增长13%，花茶、绿茶、乌龙茶、红茶的销售成本分别增长10%、6%、10%、7%。8.5方案分析方案分析建立方案解决工作表建立方法如下，输入下表A列、B列及第3行的所有数据；在C4单元格中输入公式“=B4*(1+$B$16)”
12、，然后将其复制到D4F4；在C7中输入公式“=B7*(1+$B$17)”,并将其复制到D7F7；在C8中输入公式“=B8*(1+$B$18)”，并将其复制到D8和F8；在C9中输入公式“=B9*(1+$B$19)”，并将其复制到D9F9；在C10中输入公式“=B9*(1+$B$20)”，并将其复制到D10F10；第11行数据是第7，8，9，10行数据对应列之和；净收入是相应的总销售额和销售成本之差，E19的总净收入是第13行数据之和。8.5方案分析方案分析建立方案（1）选择“工具”|“方案”菜单单击单击添加添加按钮，按钮，然后在然后在添加方案添加方案对话对话框中输入方案名框中输入方案名8.5方
13、案分析方案分析输入方案变量8.5方案分析方案分析建立方案报告8.5方案分析方案分析建立方案透视图8.6线性规划求解线性规划求解1、规划求解问题的特点：问题有单一的目标，如求运输的最佳路线、求生产的最低成本、求产品的最大盈利，求产品周期的最短时间等。问题有明确的不等式约束条件，例如生产材料不能超过库存，生产周期不能超过一个星期等。问题有直接或间接影响约束条件的一组输入值。8.6线性规划求解线性规划求解2、Excel规划求解问题由以下3部分组成（1）可变单元格）可变单元格（2）目标函数）目标函数（3）约束条件）约束条件8.6线性规划求解线性规划求解3、案例某肥料厂专门收集有机物垃圾，如青草、树枝、
14、凋谢的花朵等。该厂利用这些废物，并掺进不同比例的泥土和矿物质来生产高质量的植物肥料，生产的肥料分为底层肥料、中层肥料、上层肥料、劣质肥料4种。为使问题简单，假设收集废物的劳动力是自愿的，除了收集成本之外，材料成本是低廉的。该厂目前的原材料、生产各种肥料需要的原材料比例，各种肥料的单价等如下各表所示。问题：求出在现有的情况下，即利用原材料的现有库存，应生产各种类型的肥料各多少数量才能获得最大利润，最大利润是多少？8.6线性规划求解线性规划求解表2 生产肥料的库存原材料库存情况库存情况现有库存现有库存泥土泥土4100有机垃圾有机垃圾3200矿物质矿物质3500修剪物修剪物1600表1 各肥料成品用
15、料及其价格表产品产品泥土泥土有机垃圾有机垃圾矿物质矿物质修剪物修剪物单价单价底层肥料底层肥料55547623105.00中层肥料中层肥料6432452084.00上层肥料上层肥料43329844105.00劣质肥料劣质肥料1845231857.00表表3单位原材料成本单价单位原材料成本单价项 目单位成本泥土0.20有机垃圾0.15矿物质0.10修剪物0.238.6线性规划求解线性规划求解规划求解第一步建立求解工作表8.6线性规划求解线性规划求解规划求解第二步设置求解参数选择“工具”|“规划求解”菜单，设置求解的各项参数，如下图所示。8.6线性规划求解线性规划求解规划求解第3步求解8.6线性规划
16、求解线性规划求解修改资源 肥料厂接到一个电话：只要公司肯花10元的运费就能得到150个单位的矿物。这笔交易稍稍降低了矿物质的平均价格，但这些矿物质值10元吗？解决该问题的方法是，将库存矿物3 500改为3 650，用规划求解重新计算最大盈余。看除去￥10的成本后，盈余是否增加 8.6线性规划求解线性规划求解 修改约束条件 肥料厂接到一个电话，一个老顾客急需25个单位的上层肥料，公司经理在检查打印结果后，发现没有安排生产上层肥料。决定增加约束条件，为他生产25个单位的上层肥料。增加的增加的约束条约束条件件8.6线性规划求解线性规划求解 规划求解的结果报告1.运算结果报告运算结果报告8.6线性规划
17、求解线性规划求解2、敏感性报告8.6线性规划求解线性规划求解3、极限报告、极限报告8.7数据分析工具库数据分析工具库1、概述分析工具库由Excel自带的加载宏提供，如果启动Excel后，在Excel的“工具”菜单中没有“数据分析”菜单项，就需启动“工具”中的“加载宏”菜单项，将“分析工具库”加载到Excel系统中。Excel的“分析工具库”加载宏还提供了一些统计函数、财务函数和工程函数。这些函数只有在安装了“分析工具库”后才能使用 8.7数据分析工具库数据分析工具库分析工具名称分析工具名称说说 明明方差分析方差分析 包括包括3种类型的分析，它们是单因素方差分析、可重复双因素分析、无重复双因素种
18、类型的分析，它们是单因素方差分析、可重复双因素分析、无重复双因素分析分析相关系数分析相关系数分析用于判断两组数据集（可以使用不同的度量单位）之间的关系。用于判断两组数据集（可以使用不同的度量单位）之间的关系。协方差分析协方差分析用于返回各数据点的一对均值偏差之间的乘积的平均值。用于返回各数据点的一对均值偏差之间的乘积的平均值。描述统计分析描述统计分析用于生成对输入区域中数据的单变值分析，提供有关数据趋中性和易变性的信息用于生成对输入区域中数据的单变值分析，提供有关数据趋中性和易变性的信息指数平滑分析指数平滑分析基于前期预测值导出相应的新预测值，并修正前期预测值的误差。基于前期预测值导出相应的新
19、预测值，并修正前期预测值的误差。傅里叶分析傅里叶分析解决线性系统问题，并能通过快速傅里叶变换（解决线性系统问题，并能通过快速傅里叶变换（FFT）分析周期性的数据。）分析周期性的数据。F-检验检验用来比较两个样本总体的方差用来比较两个样本总体的方差直方图分析直方图分析在给定工作表中数据单元格区域和接收区间的情况下，计算数据的个别和累计频率在给定工作表中数据单元格区域和接收区间的情况下，计算数据的个别和累计频率移动平均分析移动平均分析基于特定的过去某段时期中变量的均值，对未来值进行预测基于特定的过去某段时期中变量的均值，对未来值进行预测t-检验分析检验分析双样本等方差假设双样本等方差假设t-检验，
20、双样本异方差假设检验，双样本异方差假设t-检验，平均值的成对双样本检验，平均值的成对双样本t-检验检验回归分析回归分析通过对一组观察值使用通过对一组观察值使用“最小二乘法最小二乘法”直线拟合，进行线形回归分析。直线拟合，进行线形回归分析。抽样分析抽样分析以输入区域为总体构造总体的一个样本以输入区域为总体构造总体的一个样本z-检验检验双样本平均差检验双样本平均差检验2、Excel分析工具库中的工具分析工具库中的工具8.7数据分析工具库数据分析工具库3、统计分析 Excel的分析工具库提供了3种统计观测分析的工具：指数平滑分析、移动平均分析和回归分析。三种工具的用法相同，以指数平滑分析为例说明其用
21、法。（1）在工作表的一列上输入各时间点上的观察值，如下图A列所示。8.7数据分析工具库数据分析工具库（2）选择“工具”“数据分析”菜单项，从弹出的对话框中选择“指数平滑”。（3）设置“输入”、“输出”选项，如下图所示。8.7数据分析工具库数据分析工具库4、假设检验、假设检验Excel的分析工具库中也提供了一些假设分析工具，如：的分析工具库中也提供了一些假设分析工具，如：t-检验、检验、z-检验、检验、F-检验。检验。运用检验工具可以轻松地完成均值、方差的假设检验。运用检验工具可以轻松地完成均值、方差的假设检验。案例案例某种子公司为比较两个稻种的产量，选择了某种子公司为比较两个稻种的产量，选择了
22、25块条件块条件相似的试验田，采用相同的耕种方法进行耕种试验，相似的试验田，采用相同的耕种方法进行耕种试验，结果播种甲稻种的结果播种甲稻种的13块田的亩产量（单位：市斤）块田的亩产量（单位：市斤）分别是：分别是：880、1120、980、885、828、927、924、942、766、1180、780、1068、650；播种乙；播种乙稻种的稻种的12块试验田的亩产量分别是：块试验田的亩产量分别是：940、1142、1020、785、645、780、1180、680、810、824、846、780。问这两个稻种的产量有没有明显的高。问这两个稻种的产量有没有明显的高低之分。低之分。8.7数据分析工
23、具库数据分析工具库输入A、B两列的样本数据8.7数据分析工具库数据分析工具库设置t检验的各项参数8.7数据分析工具库数据分析工具库5、回归分析Excel通过对一组观察值使用“最小二乘法”直线拟合，进行线性回归分析，该回归分析可同时解决一元回归与多元回归问题。案例下图所示的工作表中，列出了美国19561970年间历年的人均可支配收入和人均可消费支出的数据。试用图中的数据拟合模型。模型中的趋势变量t，用于反映除人均收入之外的所有其他因素对人均消费的影响 8.7数据分析工具库数据分析工具库8.7数据分析工具库数据分析工具库设置回归分析模型的参数8.7数据分析工具库数据分析工具库回归分析结果The E
24、nd第9章 外部数据的导入与分析Excel是一个极好的数据分析和图表分析工具，也是一个高效的数据输入工具。将它和专业数据库应用系统相结合，作为数据库系统的输入工具或数据分析工具，能解决数据库系统应用过程中的许多问题，极大地提高工作效率。本章学习目标1 1、了解、了解ExcelExcel与外部数据交换的方法与外部数据交换的方法2 2、掌握、掌握ExcelExcel访问外部数据的条件访问外部数据的条件3 3、了解、了解MsQueryMsQuery的功能及用法的功能及用法4 4、掌握、掌握MSQueryMSQuery单表和多表查证方法单表和多表查证方法5 5、掌握、掌握ExcelExcel与与ACC
25、ESSACCESS、VFPVFP交换数交换数据的方法据的方法6 6、掌握、掌握ExcelExcel与文本文件交换数据的与文本文件交换数据的方法方法9.1导入导入Web数据数据1.概述Excel提供了Web页数据的导入功能，能够将因特网中的网页数据导入Excel进行分析。2.导入Web数据的方法1）保证计算机已经与网络连接。2）选择Excel的“数据”|“导入外部数据”|“新建Web查询”，Excel会弹出“新建Web查询”对话框，如下图9所示 9.1导入导入Web数据数据3）在图中）在图中“地址地址”文本框中输入文本框中输入Web页的网络地址，单击图中的页的网络地址，单击图中的“转转到到”按钮
26、。稍后系统就会在该对话框中显示完整的按钮。稍后系统就会在该对话框中显示完整的Web页。图中显示页。图中显示的是新浪网的股票交易的是新浪网的股票交易Web页。页。9.1导入导入Web数据数据4）单击Web页数据区域中要导入数据前面的按钮。5）单击图中的“导入”按钮后，Excel会弹出数据存放位置的设置对话框，如图（a）所示。图（b）是导入到Excel中的Web数据。9.2 Excel与Word的数据交换 概述Miscrosoft Office系统内的各种软件之间的数据交换非常方便，通过复制和粘贴就能够传递数据。Excel与Word是Microsoft Office系统中最常用的两种办公应用软件，
27、Word常用于处理文字，Excel常用于表格计算。方法（1）选中Excel中的表格数据区域，然后单击工具栏中的“复制”按钮（或按Ctrl+C键）。（2）单击Word文档中要插入Excel表格的位置，然后单击Word工具栏中的“粘贴”按钮（或按Ctrl+V键）。9.3Excel与文本文件的数据转换与文本文件的数据转换1.文本文件概述文本文件是计算机中的一种通用格式的数据文件，它可以在各软件系统之间传递数据。几乎所有的软件系统都可直接操作文本文件。例如，Word、Excel、Access、FoxPro、Porer Point、Sybase、Oracle、C语言编辑器等，都能建立、修改或读入文本文件
28、。9.3Excel与文本文件的数据转换与文本文件的数据转换2将Excel工作表保存为文本文件9.3Excel与文本文件的数据转换与文本文件的数据转换2导入文本文件到Excel工作表中步骤之一：选择Excel的“文件”|“打开”菜单，并在“打开”对话框中指定“文本文件（*.prn）”类型。9.3Excel与文本文件的数据转换与文本文件的数据转换2导入文本文件到Excel工作表中步骤之二：文本文件中各列的间隔方式 9.3Excel与文本文件的数据转换与文本文件的数据转换2导入文本文件到Excel工作表中步骤之三：指定各数据之间的间隔符 9.3Excel与文本文件的数据转换与文本文件的数据转换2导入
29、文本文件到Excel工作表中步骤之四：指定各列数据的类型及是否导入。9.4Excel与外部数据库与外部数据库1、概述将Excel与数据库系统结合起来，运用数据库系统管理、组织、存储数据，用Excel为数据库输入数据，对数据库系统处理的结果数据进行分析、制图或作财务分析等，将会极大地提高工作效率，达到事半功倍的效果。ODBC（OpenDatabaseConnetivity）即人们常说的“开放式数据库互连”，它是数据库系统的一种接口技术，可用于不同的应用程序，如Visual Basic、Excel、MISCROSOFT Qurey等。ODBC主要用于数据库系统与其他应用程序之间的通信，它提供应用程
30、序与数据库系统之间进行通信的一些技术细节。9.4Excel与外部数据库与外部数据库ODBC驱动程序驱动程序ODBC驱动程序是符合驱动程序是符合“开放数据库互连开放数据库互连”标标准的数据源驱动程序。它提供了多种数据库管准的数据源驱动程序。它提供了多种数据库管理系统的驱动程序，如理系统的驱动程序，如Access、SQLSever等，通过这些驱动程序，可以直接访问相关数等，通过这些驱动程序，可以直接访问相关数据库系统中的数据。据库系统中的数据。数据源数据源就是数据的来源，是要访问的数据库的一组存就是数据的来源，是要访问的数据库的一组存储信息，它包含了用于数据库连接所必需的信储信息，它包含了用于数据
31、库连接所必需的信息。另外，还包括数据库服务器的名称和位置，息。另外，还包括数据库服务器的名称和位置，用于连接数据库的驱动程序的名称，以及在登用于连接数据库的驱动程序的名称，以及在登录到数据库时所需的信息。录到数据库时所需的信息。9.4Excel与外部数据库与外部数据库2、访问外部数据库的条件 1）具有访问外部数据的权限）具有访问外部数据的权限 2）系统中安装了）系统中安装了MicrosoftQuery 3）具有访问外部数据库的）具有访问外部数据库的ODBC驱动程序驱动程序 9.5MicrosoftQuery介绍介绍1MicrosoftQuery概述概述 Microsoft Query可作为一个
32、独立的工具单独运行，用以查询各种数据库中的数据，也可以在Excel的工作表中调用它，用于将外部数据库中的数据导入到Excel的工作表中。使用 Query 从外部数据库中检索数据到Excel的工作表，可以减少数据的重复输入。此外，当外部数据库更新数据时，Miscrosoft Query还可以刷新Excel工作表中的数据，以保持与外部数据的一致性。9.5MicrosoftQuery介绍介绍MSquery用于将外部数据查询到Excel中分析9.5MicrosoftQuery介绍介绍2MiscrosoftQuery、数据源及、数据源及ODBC9.6 用MS Query查询外部数据 案例某人在Acces
33、s系统中建立了一个学生管理数据库系统，存放在“C:My Documents”目录下，名字是db4.mdb。该数据库中有学生、教师、学生选课及成绩等数据表。其中，学生档案数据表如下所示。现在要建立一个访问该数据库的数据源，并且将该数据库中的所有数据查询到Excel中进行分析处理。注意：将“XCEL资料”目录中的“学生教学管理.mdb”文件复制到C:My Document目录中，且改名为db4.mdb。9.6 用MS Query查询外部数据ACCESS学生管理数据库中的学生档案表9.6 用MS Query查询外部数据1.查询外部数据步骤之一建立数据源选择“数据”|“获取外部数据”|“新建数据库查询
34、”菜单项，系统会弹出如图示对话框。选中选中新数新数据源据源，然，然后后“确定确定”9.6 用MS Query查询外部数据设置数据源的各项参数1、在此输入数据源、在此输入数据源名字（可任取名字名字（可任取名字2、在此选择正确、在此选择正确的数据库驱动程序的数据库驱动程序3、完成、完成1、2后单后单击击“连接连接”，见下，见下面的对话框面的对话框9.6 用MS Query查询外部数据设置数据源对应的数据库单击单击“选择选择”按钮然按钮然后从弹出的后从弹出的文件选择对文件选择对话框话框中找到对应的数据中找到对应的数据库文件，选定文件后，库文件，选定文件后，单击单击“确定确定”，会见到，会见到下述对话
35、框！下述对话框！9.6 用MS Query查询外部数据建立的数据源建立的数据源建立的数据源建立的数据源建立的数据源9.6 用MS Query查询外部数据2、查询外部数据步骤之二访问数据源在上图中，选中要访问的数据源，然后单击“确定”按钮，就会见到下述对话框。1、选中要访、选中要访问的数据表问的数据表2、单单击击9.6 用MS Query查询外部数据3、查询到Excel中的数据表根据“查询向导”提示，不断单击向导对话框的“下一步”，会见到最后一步“向导对话框”选择其中的“将数据返回Miscrosft Office Excel”，会得到下页所示的工作表。9.6 用MS Query查询外部数据从外部
36、数据库查询到的数据表9.6 用MS Query查询外部数据4、建立多表查询数据库中的数据表与Excel的工作表有许多差异，数据库中的表要讲究一定的数据规范，这种规范可能导致数据库中有许多不同的数据表。比如学生库中的成绩、课程和学生档案就分散在不同的数据表中。9.6 用MS Query查询外部数据多表查询的方法指定了查询的数据源，在指定了查询的数据源，在“查询向导查询向导指定列指定列”的的对话框中，从多个数据表中选中需要的全部数据字段对话框中，从多个数据表中选中需要的全部数据字段名字。名字。在本例中，从学生表中选中学号、姓名、班级；从课在本例中，从学生表中选中学号、姓名、班级；从课程表中选中课程
37、编号、课名；从成绩表中选中成绩。程表中选中课程编号、课名；从成绩表中选中成绩。9.6 用MS Query查询外部数据按向导做完其余步骤，最后得到如下工作表9.7操作操作MSQuery1、概述Miscrosoft Query是一个非常有用的数据库查询软件，它可以在Windows系统中独立运行，也可以通过加载宏的方式在Excel中运行。在Excel中操作MS Query的方法是：在通过查询向导从外部向导的最后一步骤中，选择“在Microsoft Query中查看数据或编辑查询（Y）”9.7操作操作MSQuery2、MS Query的操作界面9.7操作操作MSQuery3、添加、删除查询结果中的列字
38、段 用鼠标将这时的用鼠标将这时的列字段拖放到下列字段拖放到下面的窗口中，即面的窗口中，即可将对应列数据可将对应列数据查询出来！查询出来！选中按选中按Delete键键即可删除对应用即可删除对应用列的查询结果列的查询结果9.7操作操作MSQuery4添加或删除表窗口中的表单击单击Query中中的的 ，可见添加，可见添加表窗口表窗口选中表名，再选中表名，再“添加添加”9.7操作操作MSQuery5、多表查询 多表连接关系 只有正确地建立只有正确地建立了多表之间的连了多表之间的连接字段后，多表接字段后，多表查询才能有正确查询才能有正确的结果的结果9.7操作操作MSQuery建立多表连接关系的方法建立多
39、表连接关系的方法方法方法1：从一个表中将连接字段直接拖放到另一个表中：从一个表中将连接字段直接拖放到另一个表中的连接字段上。的连接字段上。方法方法2：选择：选择MiscrosoftQuery“表表”|“连接连接”菜单菜单项，项，MiscrosoftQuery会弹出一个建立关系的对话框，会弹出一个建立关系的对话框，9.7操作操作MSQuery.6 条件查询 2、条件区、条件区域，在此域，在此区域输入区域输入查询条件查询条件1、若不见、若不见条件区域，条件区域，单击单击9.7操作操作MSQuery3参数条件查询在条件中输入，就表示对参数查询，在内的文字是提示信息。执行此查询时将显示下面执行此查询时
40、将显示下面的对话框，输入一个名字，的对话框，输入一个名字，将只查询对应学生的数据将只查询对应学生的数据9.8Excel与其他文件相互转换与其他文件相互转换1.概述概述Excel可以直接打开多种不同类型的文件，如可以直接打开多种不同类型的文件，如文本文件、文本文件、dBASE文件、文件、Web网页、网页、Excel低版本的文件、模板文件、低版本的文件、模板文件、MiscrosoftQuery建立的查询文件、建立的查询文件、Lotus1-2-3文件等，文件等，然后将这些文件转换成为然后将这些文件转换成为Excel工作簿；工作簿；Excel也可以将工作簿文件保存为文本文件、也可以将工作簿文件保存为文
41、本文件、以以Web页的方式发布到网上，或保存为页的方式发布到网上，或保存为Lotus1-2-3文件等。文件等。9.8Excel与其他文件相互转换与其他文件相互转换2、将Excel文件转换成其它类型的文件选择选择“文件文件”|“另存为另存为”菜单命令，从菜单命令，从保存类型的下保存类型的下拉列表中选择拉列表中选择需要的文件类需要的文件类型型9.8Excel与其他文件相互转换与其他文件相互转换由Excel工作表转换成的WEB网页9.9导入外部数据应用举例导入外部数据应用举例【例例1】某单位有一医疗管理系统是用FoxPro编写的，其中有一个汇总数据库，假设数据库的文件名为“医疗.dbf”，其中的数据
42、如图所示。将此数据表中的数据导入Excel9.9导入外部数据应用举例导入外部数据应用举例【例例2】一家拥有7000多职工的合资企业，其人事档案管理系统是用FoxPro编写的，该公司的财务部门每个月要对公司职工的各项财务数据进行汇总和对比分析，并作出各种分析报表和图表。将此VFP数据表导入Excel并作出下页的分析图表。姓名职工号类别加班时间加班工资房屋补贴其他津贴生计津贴离职津贴餐卷吴银1664STAFF1031159.5323.62129.80244.54122.09李小民0987STAFF26808.6245.99138.50161.9137.52289.38黄大能0065WORKER11
43、342.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.359.9导入外部数据应用举例导入外部数据应用举例例2需要的最近两月对比分析表9.9导入外部数据应用举例导入外部数据应用举例例2需要的分析图19.9导入外部数据
44、应用举例导入外部数据应用举例例2需要的分析图29.8Excel与其他文件相互转换与其他文件相互转换3Excel与与Access数据交换数据交换Access与与Excel或或Word同是同是Office软件系统中软件系统中的成员，所以它们之间的数据交换远没有的成员，所以它们之间的数据交换远没有Excel与与Fox系统之间进行数据交换那么复杂。系统之间进行数据交换那么复杂。在在Access系统中，可以将数据库中的数据表直系统中，可以将数据库中的数据表直接发送到接发送到Excel的工作表中进行分析；同时，的工作表中进行分析；同时，在在Excel中可以直接启动中可以直接启动Access数据库系统，数据
45、库系统，将工作表中的数据直接传递到将工作表中的数据直接传递到Access的数据表的数据表中。中。直接建立直接建立Access数据库中的数据表与数据库中的数据表与Excel工工作表的链接，使得在工作表中输入的数据可直作表的链接，使得在工作表中输入的数据可直接传递到接传递到Access数据库中。数据库中。9.8Excel与其他文件相互转换与其他文件相互转换案例某辅导员用Access开发了一个学生管理系统，名字为“学生档案.mdb”，其中的部分数据表及数据记录如图所示。9.8Excel与其他文件相互转换与其他文件相互转换4、直接将Access数据发布到EXCEL分析9.8Excel与其他文件相互转换与其他文件相互转换5建立Access数据表与Excel工作表的链接Access提供了一种与Excel进行通信的更高级的方法，那就是建立Access数据表与Excel工作表之间的链接。方法1）选择Access“文件”|“获取外部数据”|“链接”命令。2）根据链接向导提示作完其余步骤。1）选择Access“文件”|“获取外部数据”|“链接”命令。2）在“链接”对话框的“文件类型”中指定“Microsoft Excel”，在“文件名”中指定要链接的Excel工作簿名称，然后单击“链接”按钮，Access将弹出如图9-80所示的链接向导第一步对话框。The End