EXCEL汇总公式.docVIP

一、编码 名称 规格 上期结存 本期购入 本期发出 本期结存 在外订单 计划总用量 安全库存 安全报警 在外订单：SUMIF（未入明细！B：B，A：A，未入账明细！F：F） 库存报警：IF（03N2,“Y”，“N”）{结存安全库存} 二 通过导入外部数据“编辑OLE DB 查询”创建数据透视表 第一部分 通过导入外部数据“编辑OLE DB查询”创建数据透视表 ?一、???????? OLE DB简介 ?二、???????? OLE DB查询与数据透视表的关系 ?(一)???????? 导入单张数据列表创建数据透视表 ?1.??? 创建一(放导入数据的)工作簿,打开，在工作表内单击任意单元格：数据——导入外部数据——导入数据? ? ? 2.??? 在“选取数据源”对话框内，选择要导入的数据列表，后，单击 打开 ?? ? 3.??? 在“选择表格”对话框内，单击 确定? ? ? 4.??? 在出现的“导入数据”对话框内，单击 属性，对属性设置后，单击“创建数据透视表” ?? ? ? 注意：1 当数据透视表创建好后，第一次刷新：会出现“刷新数据” 对话栏，勾选“不再显示此消息”，单击确定，以后刷新不再出现此对话框 ? ?? 2 如果先打开了数据透视表，再打开数据源工作簿，将会出现错误提示——文件正在使用。在这种状态下，打开的数据源工作簿增加的数据将不会在数据透视表中刷新中更新。解决此问题的办法是先打开数据源工作簿，再打开数据透视表。 ?二)???????? 导入多张数据列表创建数据透视表 ?1.??? 在（要创建透视表的）工作表内单击任意单元格：数据——导入外部数据——导入数据 2.??? 在“选取数据源”对话框内，选择要导入的数据列表，后，单击打开 ? ?? 3.??? 在“选择表格”对话框内，直接单击 确定 ? ?? 4.??? 在出现的“导入数据”对话框内，单击属性，对属性设置后，单击“编辑查询” ? ?? 5.??? 在编辑 OLE DB 查询 对话框内的命令文本处写入SQL语句，或把已经写好的SQL语句复制后，在此 粘贴，确定 ? ??在导入对话框 中，设置属性；单击 创建数据透视表 ?(三)???????? 汇总不同工作簿的数据表，创建透视表 ? 1.??? 创建一个汇总工作簿，打开，单击任意单元格：数据——导入外部数据——导入数据 2.??? 在“选取数据源”对话框内，选择要导入的其中一个数据列表，单击打开 ? ?? 3.??? 在“选择表格”对话框内，直接单击 确定 ? ? 4.??? 在出现的“导入数据”对话框内，单击属性，对属性设置后，单击“编辑查询” ? ? 命令文本： SELECT 滨海, * FROM `D:\汇总不同工作簿内的数据表\滨海司`.`1月$` `1月$` UNION ALL SELECT 滨海, * FROM `D:\汇总不同工作簿内的数据表\滨海司`.`2月$` `2月$` UNION ALL SELECT 丽江, * FROM `D:\汇总不同工作簿内的数据表\丽江司`.`1月$` `1月$` UNION ALL SELECT 丽江, * FROM `D:\汇总不同工作簿内的数据表\丽江司`.`2月$` `2月$` ? 5.??? 设置属性后，单击“创建数据透视表” ? ?三、???????? 数据源移动后如何修改OLE DB 查询中的连接 ?1.??? 在数据透视表工作簿中点数据------导入外部数据-----编辑查询-----在编辑OLE DB查询中修改---------在连接处输入正确的路径后点确定即可。 2.??? 用VBA代码也可以修改。 ? ? ?代码： Private Sub Workbook_Open() ??? Dim strCon As String, iPath As String, i As Integer, iFlag As String, iStr As String ??? strCon = ActiveSheet.PivotTables(1).PivotCache.Connection ??? Select Case Left(strCon, 5) ??? Case ODBC; ??????? iFlag = DBQ= ??? Case OLEDB ??????? iFlag = Source= ??? Case Else ??????? Exit Sub ??? End Select ??? iStr = Split(Split(strCon, iFlag)(1), ;)(0) ??? iPath = Left(iStr, InStrRev(iStr, \) - 1) ??? With ActiveSheet.PivotTables(1).Piv