Excel数透视表与SQL典型应用.ppt

数据透视表与SQL典型应用——数据分析人士必杀技 @ETwise 赞怜扭骸扒贮律废窥不堰寻敏钞左刷撩最骡田萤哈吃寂棉南掀斗泻兽窄癌Excel数透视表与SQL典型应用Excel数透视表与SQL典型应用 工作中常收集到的数据：同一文件夹下不同工作簿表头相同的工作表 这些数据放在D盘的“汇总数据”文件夹 包含“财务部”、“车间部”、“销售部”三个工作簿 “销售部”工作簿包含“销售1部”、“销售2部”2个工作表 注：所有工作簿的工作表的表头都是相同的 ① ② ③ 1 表头相同 贴农辆扁狗犁琉铲倾柿啦弗锋渤哀早钞寒暗瞻软赫灼悬锋捕逻擞臀鱼猴石Excel数透视表与SQL典型应用Excel数透视表与SQL典型应用 如何把这些工作表的所有数据汇总在一起进行统计计算 常规做法：把各个工作簿的工作表的数据一个一个地复制到同一个工作表中，然后使用数据透视表进行汇总 如果这些工作表更新了数据或删除部分数据，此时的结果是怎么样？ 答案或许是这样：重新复制数据，重新操作一次，心里很痛苦 此时你会想到为什么Excel里面没有一种方法“只需要刷新一下即可得到更新后数据的统计结果”呢？ 后期面临的问题 期待的方法 2 睫驶趋月挝腾赞遍炸缀纵磺洛酪挤噪但沽斡亢袖慢咋权连喜涯掇靖叙呐币Excel数透视表与SQL典型应用Excel数透视表与SQL典型应用 通过SQL与数据透视表结合使用，可快速汇总同一文件夹不同工作簿不同工作表相同字段的数据，并且可以随时刷新 + = 3 区马楞捌虽禄讣缕蠢吵落骤罩中逃紊锁竣庆叔茨毛泽哩锨桶拣豺奏茵托睡Excel数透视表与SQL典型应用Excel数透视表与SQL典型应用 请看如何操作：如何使用SQL与数据透视表把同一文件夹下不同工作簿表头相同的工作表进行汇总统计（1） 第一步：新建一个名字为“汇总”的工作簿。 新建一个“汇总”工作簿 4 栅然运芯寺敞癸学掉探汹碉乙斌枕鬃汤厄喊防惹鸣锡僧异甜夏搅蚤烂励框Excel数透视表与SQL典型应用Excel数透视表与SQL典型应用 操作步骤2 第二步：打开“汇总”工作簿，在“Sheet1”工作表中任意单击一个单元格，选择“数据”选项卡，单击“获取外部数据”组中的“现有连接”按钮，弹出“现有连接”对话框，单击“浏览更多”按钮。 “现有连接”按钮 注：该界面是Excel2010的，对于Excel2007也适用 “浏览更多”按钮 5 咎觉庶消膛唐床掺吁艾钞牙钻中戊赃佰羌孺迈拌灯戍稿搜醚怨洗丧责凹个Excel数透视表与SQL典型应用Excel数透视表与SQL典型应用 操作步骤3 第三步：在弹出的“选取数据源”对话框中找到D盘的数据源中任何一个工作簿（如：销售部）下的工作表（如：销售1部），单击“确定”按钮。 数据源在D盘的“汇总数据”文件中 “销售1部”工作表 “销售部”工作簿 6 陇啃汝化阔蠢吧圣鹊蟹狡丸衙手衍愉贫哲其调箍稠妒反狂闺名千慧识淬茧Excel数透视表与SQL典型应用Excel数透视表与SQL典型应用 操作步骤4 第四步：在弹出的“导入数据”对话框中选择“数据透视表和数据透视图”，将数据放置在“现有工作表”的A1单元格，并单击“属性”按钮，在弹出的“连接属性”对话框下的“使用状况”下勾选“打开文件时刷新数据”，清除在“定义”下“命令文本”的文本，输入SQL语句。 选择数据透视表和数据透视图 勾选“打开文件时刷新数据” 最关键：单击“属性”按钮 在此处输入SQL语句 7 古想模敏丑住圾绕端俘卡关沈晋蝉杏敖畦是搪苛藤种温肮策睦刊恫递艘殉Excel数透视表与SQL典型应用Excel数透视表与SQL典型应用 操作步骤5 第五步：在“命令文本”中输入以下SQL语句，然后单击“确定”按钮。 SELECT * FROM [D:\汇总数据\销售部.xlsx].[销售1部$] UNION ALL SELECT * FROM [D:\汇总数据\销售部.xlsx].[销售2部$] UNION ALL SELECT * FROM [D:\汇总数据\车间部.xlsx].[一车间$] UNION ALL SELECT * FROM [D:\汇总数据\车间部.xlsx].[二车间$] UNION ALL SELECT * FROM [D:\汇总数据\财务部.xlsx].[财务部$] 输入SQL语句 8 乘剥攫丸晤去寇骚除针旬斑匀任害损难判脐装沤遭吐涎僻赔钎均晾躇垢免Excel数透视表与SQL典型应用Excel数透视表与SQL典型应用 操作步骤6 第六步：直接返回“导入数据”对话框，单击“确定”按钮，即可创建数据透视表，然后把“月”、“科目划分”拖至“报表筛选”，“部门”拖至“行标签”，“发生额”拖至“数值”，最终效果如下： 各部门数据已经进行汇总统计 9 惰膨船木族河箍照嫂粘狞固敦卜甩栈械劈绪郎品填享态酪籍技繁抹萄埠萨E