掌握要领实现Excel动态链接外部数据库

【 tulaoshi.com - excel 】

掌握要领,实现Excel动态链接外部数据库

　　我们有时需要在Excel中调取其他数据库的数据，并且希望其他数据库数据改变时，Excel中调取的数据也随之动态改变。下面介绍在Excel中通过新建数据库查询(Microsoft Query)的方法来实现动态链接数据库。

　　您在Excel中第一次使用新建数据库查询查询数据时，如果系统未安装Microsoft Query，系统会提示您安装。

　　　　一、Excel链接vfp数据库

　　vfp是常用的数据库，先以此来介绍。假设从px.dbf表中取出相应学校的数据，已知学校的后5位代码，数据所在字段为x16，数据值为03行和06行相加，放入Excel单个单元格中。

　　步骤如下:

　　1、点击菜单数据导入外部数据新建数据库查询，出现选择数据源对话框，选择Visual Foxpro Tables*，按确定。如果您对SQL语句不是很熟悉的话，请使用默认的查询向导创建。

　　2、在configure connection对话框中，按Browse按钮选择表px.dbf所在的路径，按ok键。

　　3、在查询向导选择列对话框中，选择查询结果中的列，要选取的数据在x16字段，所以将可用表px中的x16列移至查询结果中的列，按下一步。

　　4、在查询向导筛选数据对话框中，因为此例中设置的条件在字段学校代码xxdm和行号bh，非字段x16，所以点击下一步，暂时不筛选数据。

　　5、在查询向导排序顺序对话框中，因为此例中所取的是单个数据，所在点击下一步。

　　6、出现查询向导完成对话框，因还要对数据进行筛选，所以选择在Microsoft Query中查看数据或编辑查询，点击完成。

　　7、进入Microsoft Query，看到查询结果x16列出了所有行的数据。

　　接下来要通过设置条件来筛选所需的数据。

　　点击菜单条件增加条件(A)...，字段bh,运算符等于，指定值为03，按添加。

　　继续添加条件对话框，选择或，字段bh,运算符等于，指定值为06，按添加。

　　继续添加条件对话框，选择与，字段xxdm,运算符等于，指定值为11201，按添加后按关闭。

　　但是看到符合条件x16为空，这是因为学校代码的后五位为11201。

　　可以通过显示SQL来直接修改条件语句,将SQL语句中px.xxdm="11201"改成right(px.xxdm,5)="11201",按确定。

　　也可以在条件字段直接修改。

　　光标放在x16字段，点击循环总计，将查询出的两个值相加，当然也可以直接在SQL语句中修改。

　　8、关闭Microsoft Query，弹出导入数据对话框，选择数据的放置位置，选择$B$2，按确定。

　　9、但我们发现显示了标题行，真正的数据却显示到了$B$3，这时我们需要修改$B$2单元格的数据区域属性。点击$B$2单元格，按右键菜单的数据区域属性，弹出外部数据区域属性窗口，将数据格式与布局中包含字段名等前面的对勾去掉，将数据区域中的行数随刷新而更改的方式改成用新数据覆盖现有单元格，并清除没有使用的单元格内容，按确定。但还是有标题，没有关系，点击$B$2单元格，按右键菜单的刷新数据即可。

　　10、如果px.dbf数据库有所改变，那么如何在Excel中刷新，可以点击链接数据所在单元格右键菜单中的刷新手工刷新;也可以在数据区域属性刷新控件中选上打开工作簿时，自动刷新(见图14)，从而在每次打开工作簿时，在查询刷新的提示框中按启动自动刷新按钮刷新(见图15)。

　　11、如果需要编辑查询，则点击$B$2单元格，按右键菜单的查询编辑，如果出现查询向导无法编辑此查询的提示，只要按确定即可进入Microsoft Query。

　　12、$B$3单元格查询条件与$B$2相差的只是学校代码，可以将$B$2单元格复制到$B$3单位格，再用$B$3的编辑查询来修改，修改一下学校代码的值即可。

　　　　二、Excel链接SQL数据库

　　链接SQL数据库与链接vpf大致是相同的。这里介绍的是连接两表的查询。

　　点击菜单数据导入外部数据新建数据库查询，出现选择数据源对话框，可直接选取所需链接的SQL数据库名。

　　如果SQL数据库有密码的话，将提示输入密码，按确定。如果SQL SERVER服务管理器未开启的话，将出现错误提示。

　　如果不使用查询向导的话，可直接进入Microsoft Query。添加表news和表type，news表中的typeid和type表的typeid建立关联。在条件字段中选择字段名，并在值中输入条件值，这里输入的条件是type表的typeid字段值为1且news表的topic中含有"小学生"。点击选择要输出的字段名，这里选择的是type表中的typename字段和news表的topic字段。

　　当然，如果您对SQL语句熟悉，也可以直接输入SQL语句。

　　接下来的步骤请参考一、中的8、至10、。

　　三、与其他数据库(如access数据等)

　　　　 这里就不再赘述了，基本思路就是这样了。

在Excel中实现多条件求和的三种方法

　　在平时的工作中经常会遇到多条件求和的问题。如图1所示各产品的销售业绩工作表，我们希望分别求出东北区和华北区两部门各类产品的销售业绩，或者在同一部门中的不同组也要求出各产品的销售业绩。在Excel中，我们可以有三种方法实现这些要求。

　　　　一、分类汇总法

　　首先选中A1:E7全部单元格，点击菜单命令数据→排序，打开排序对话框。设置主要关键字和次要关键字分别为部门、组别，如图2所示。确定后可将表格按部门及组别进行排序。

　　然后将鼠标定位于数据区任一位置，点击菜单命令数据→分类汇总，打开分类汇总对话框。在分类字段下拉列表中选择部门，汇总方式下拉列表中选择求和，然后在选定汇总项的下拉列表中选中A产品、B产品、C产品复选项，并选中下方的汇总结果显示在数据下方复选项，如图3所示。确定后，可以看到，东北区和华北区的三种产品的销售业绩均列在了各区数据的下方。

　　再点击菜单命令数据→分类汇总，在打开的分类汇总对话框中，设置分类字段为组别，其它设置仍如图3所示。注意一定不能勾选替换当前分类汇总复选项。确定后，就可以在区汇总的结果下方得到按组别汇总的结果了。如图4所示。

　　　二、输入公式法

　　上面的方法固然简单，但需要事先排序，如果因为某种原因不能进行排序的操作的话，那么我们还可以利用Excel函数和公式直接进行多条件求和。

　　比如我们要对东北区A产品的销售业绩求和。那么可以点击C8单元格，输入如下公式:=SUMIF($A$2:$A$7,"=东北区",C$2:C$7)。回车后，即可得到汇总数据。

　　选中C8单元格后，拖动其填充句柄向右复制公式至E8单元格，可以直接得到B产品和C产品的汇总数据。

　　而如果把上面公式中的东北区替换为华北区，那么就可以得到华北区各汇总数据了。

　　如果要统计东北区中辽宁的A产品业绩汇总，那么可以在C10单元格中输入如下公式:=SUM(IF($A$2:$A$7="东北区",IF($B$2:$B$7="辽宁",Sheet1!C$2:C$7)))。然后按下Ctrl+Shift+Enter键，则可看到公式最外层加了一对大括号(不可手工输入此括号)，同时，我们所需要的东北区辽宁组的A产品业绩和也在当前单元格得到了，如图5所示。

　　拖动C10单元格的填充句柄向右复制公式至E10单元格，可以得到其它产品的业绩和。

　　把公式中的东北区、辽宁换成其它部门或组别，就可以得到相应的业绩和了。

　　　　三、分析工具法

　　在EXCEL中还可以使用多条件求和向导来方便地完成此项任务。不过，默认情况下EXCEL并没有安装此项功能。我们得点击菜单命令工具→加载宏，在打开的对话框中选择多条件求和向导复选项，如图6所示。准备好Office 2003的安装光盘，按提示进行操作，很快就可以安装完成。完成后在工具菜单中会新增添向导→条件求和命令。

　　先选取原始表格中A1:E7全部单元格，点击向导→条件求和命令，会弹出条件求和的向导对话框，在第一步中已经会自动添加了需要求和计算的区域，如图7所示。

　　点击下一步，在此步骤中添加求和的条件和求和的对象。如图8所示。在求和列下拉列表中选择要求和的数据所在列，而在 条件列中指定要求和数据应满足的条件。设置好后，点击添加条件将其添加到条件列表中。条件列可多次设置，以满足多条件求和。

　　点击下一步后设置好结果的显示方式，然后在第四步中按提示指定存放结果的单元格位置，点击完成就可以得到结果了。

　　如果要对多列数据按同tulaoshi.com一条件进行求和，那么需要重复上述操作。

用Excel电子表做数据分析之抽样分析工具

　　省教育厅派专家组进行某校检查学生考试试卷，专家组拟对总体进行抽样调查，对学校某班的全体同学随机抽取25名作为调查样本。为了保证结果的非人为性，采用Excel帮助专家组做出抽查的结果。

　　抽样分析工具以数据源区域为总体，从而为其创建一个样本。当总体太大而不能进行处理或绘制时，可以选用具有代表性的样本。如果确认数据源区域中的数据是周期性的，还可以对一个周期中特定时间段中的数值进行采样。也可以采用随机抽样，满足用户保证抽样的代表性的要求。

　　注：本功能需要使用Excel扩展功能，如果您的Excel尚未安装数据分析，请依次选择工具-加载宏，在安装光盘的支持下加载数据分析库。加载成功后，可以在工具的下拉菜单中看到数据分析选项。

　　　　操作步骤：

　　1. 打开原始数据表格，制作本实例的原始数据无特殊要求，只要满足行或列中为同一属性数值即可。实例中显示的是学生学号。

　　2. 选择工具数据分析抽样后，出现对话框，依次选择：

　　输入区域：把原始总体数据放在此区域中，数据类型不限，数值型或者文本型均可;

　　抽样方法：有间隔和随机两种。间隔抽样需要输入周期间隔，输入区域中位于间隔点处的数值以及此后每一个间隔点处的数值将被复制到输出列中。当到达输入区域的末尾时，抽样将停止。(在本例题中没有采用);随机抽样是指直接输入样本数，电脑自行进行抽样，不用受间隔的规律限制;

　　样本数：在此输入需要在输出列中显示需要抽取总体中数据的个数。每个数值是从输入区域中的随机位置上抽取出来的，请注意：任何数值都可以被多次抽取!所以抽样所得数据实际上会有可能小于所需数量。本文末尾给出了一种处理方法;

　　输出区域：在此输入对输出表左上角单元格的引用。所有数据均将写在该单元格下方的单列里。如果选择的是周期，则输出表中数值的个数等于输入区域中数值的个数除以间隔。如果选择的是随机，则输出表中数值tulaoshi.com的个数等于样本数;

　　3.然后单击确定就可以显示结果了(这是电脑自行随机抽样的结果)。

　　　　需要说明的情况：

　　由于随机抽样时总体中的每个数据都可以被多次抽取，所以在样本中的数据一般都会有重复现象，解决此问题有待于程序的完善。可以使用筛选功能对所得数据进行筛选。

　　选中样本数据列，依次执行数据-筛选-高级筛选，如下图所示。

　　最后的样本结果如下图所示，请您根据经验适当调整在数据样本选取时的数量设置，以使最终所得样本数量不少于所需数量。

　　如有问题可以发信至： wangbigbird@163.com 与我交流。