第八课
- 认识OLEDB接口
- 连接数据源
- 数据的更新
- 创建多表合并数据源
- 合并字段相同的表格
- 合并字段不同的表格
- 合并结构不同的表
- 构建数据源
- 处理空列
- 实操
认识OLEDB接口
连接数据源
步骤:
【数据】→【现有连接】→【浏览更多】→打开要的数据文件→【选择表格】
→【导入数据】→【确定】→选择输出类型,数据or透视表or透视图→【属性】→【定义】
数据的更新
位置:【连接属性】
- 允许后台刷新
- 刷新频率
- 打开文件时刷新
创建多表合并数据源
使用SQL语句:
- 位置:【数据】→【现有连接】→浏览导入要的数据→确定,左下角【属性】→右侧【定义】→
- 只连接一个表时:
基础语句:select * from [sheet1$]
含义:从表1 里选取所有数据。其中星号表示(所有数据),最后是表格名称,需要加 $ 和方括号。要空格 - 连接多个表:用union all 去连接每个数据源
合并字段相同的表格
- 直接关联多张表
- 例:
select 星号 from [苏州$] union all select * from [无锡 $] union all select 星号 from [昆山 $] - 关联表时加入标识字段
select “苏州” as 城市, * from [苏州 $] union all select “无锡” as 城市,星号 from [无锡 $] union all select “昆山” as 城市,星号from [昆山 $]
如果只选取部分字段下的数据,将星号改成 字段名,用半角逗号隔开。
select “苏州” as 城市,日期,产品类别,金额 from [苏州$]
合并字段不同的表格
- 选取交集,分别选取共有的字段,连接在一起
- 选取并集,以字段多的那个表为准
合并结构不同的表
- 给每个表加一个标识字段,下分不同项目(类似筛选页字段的作用)。用以区分。
- 语句这样写:select “项目名” as 字段名,bala,bala …from [sheet1$] union all …
- 例:分别有这两张表,图1 为实际的销售数据,图2 为销售计划。根据它们做一个可实时查看的透视表分析差异,样式如图3:
构建数据源
在合并之前呢,可以先构想一下合并之后的 理想的数据源应该是什么结构。
便于写语句,可以另开一个表格,分别截取一段,来构建一个完整的表,例:
上面的两张表合并的样子应该如下图:
处理空列
- 空着的字段用 null 代替。那么上面一共有9个空列,按顺序应该是:null,null,null,null,null,null,所属区域,null,null,金额,null
- 再加入标识字段分别是:select “实际” as 类型 和 select “计划” as 类型 放在前面
- 把他们都连接起来是:select “实际” as 类型,* from [销售数据 $] union all select “计划” as 类型,null,null,null,null,null,null,所属区域,null,null,金额,null from [销售计划 $]
- 最后做好布局:把不需要的缩起来,比如“计划完成”的明细。给数据加上【条件格式】
实操