以多个工作表为数据源，运用数据透视表向导，创建数据透视表
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">Hello，<span style="color: black;">大众</span>好！今天和<span style="color: black;">大众</span>分享，<span style="color: black;">运用</span>【数据透视表和数据透视图向导】以多个工作表为数据源，创建数据透视表。</span></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;"><span style="color: black;">一般</span>创建数据透视表的方式是单击【<span style="color: black;">插进</span>】-【数据透视表】。<span style="color: black;">然则</span>这种方式<span style="color: black;">不可</span>以多个工作表<span style="color: black;">做为</span>数据源。【数据透视表和数据透视图向导】<span style="color: black;">能够</span><span style="color: black;">运用</span>多个工作表的数据源创建数据透视表，但这个功能<span style="color: black;">不可</span>在功能区中找到。依次按下<strong style="color: blue;"><Alt>、<D>、<P></strong>键<span style="color: black;">能够</span>调出【数据透视表和数据透视图向导】对话框创建数据透视表。</span></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;"><span style="color: black;">运用</span>数据透视表向导创建数据透视表，数据源<span style="color: black;">能够</span>是同一个工作簿的多张工作表，<span style="color: black;">亦</span><span style="color: black;">能够</span>是其他工作簿的多张工作表。待合并的数据源工作表结构必须完全一致。</span></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><strong style="color: blue;"><span style="color: black;"><span style="color: black;">1</span></span></strong></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><strong style="color: blue;"><span style="color: black;">案例描述</span></strong></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">如下图所示，为北京、广州、深圳3家分<span style="color: black;">机构</span><span style="color: black;">营销</span>明细数据，3张工作表结构完全相同。<span style="color: black;">需求</span>将这3张工作表合并汇总创建数据透视表，计算每种款型的销量和<span style="color: black;">营销</span>额。</span></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">北京分<span style="color: black;">机构</span>：</span></p>
<div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/tos-cn-i-qvj2lq49k0/7cfd55fd3e5d4309b8c93dac07bfab1e~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1727976249&x-signature=FNYns%2FqFn%2BQi1Ycs2AWbD9SxPj0%3D" style="width: 50%; margin-bottom: 20px;"></div>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">广州分<span style="color: black;">机构</span>：</span></p>
<div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/tos-cn-i-qvj2lq49k0/e6758a288f014b1a81d03fd31dfa3ba6~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1727976249&x-signature=EWc8R4SC2QVzF9qrs2GdJ8%2FKjAg%3D" style="width: 50%; margin-bottom: 20px;"></div>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">深圳分<span style="color: black;">机构</span>：</span></p>
<div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/tos-cn-i-qvj2lq49k0/3346430abdc2400ca852cf6d228c32fa~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1727976249&x-signature=%2FzMpi1OPdEg8%2BYvzgKRk%2FihqK1s%3D" style="width: 50%; margin-bottom: 20px;"></div>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">创建的数据透视表效果如下图所示：</span></p>
<div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/tos-cn-i-qvj2lq49k0/4bd320f5d01743fcba79a998cf8044d2~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1727976249&x-signature=PlNO%2BBXov7NuS0dHsL9lIM9pOv8%3D" style="width: 50%; margin-bottom: 20px;"></div>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><strong style="color: blue;"><span style="color: black;"><span style="color: black;">2</span></span></strong></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><strong style="color: blue;"><span style="color: black;">操作<span style="color: black;">过程</span></span></strong></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">1、依次按下<Alt>、<D>、<P>键调出【数据透视表和数据透视图向导】对话框，<span style="color: black;">选取</span>数据源类型为【<strong style="color: blue;">多重合并计算数据区域</strong>】，创建报表类型为【数据透视表】，<span style="color: black;">而后</span>单击【下一步】。</span></p>
<div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/tos-cn-i-qvj2lq49k0/2d79faf067f241d584ef13d8436ea087~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1727976249&x-signature=iWvVtS9eFzQNHmo2EK4lFkaul8Y%3D" style="width: 50%; margin-bottom: 20px;"></div>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">2、<span style="color: black;">选取</span>【自定义页字段】，<span style="color: black;">亦</span><span style="color: black;">能够</span><span style="color: black;">选取</span>【创建单页字段】。页字段<span style="color: black;">能够</span>用于筛选<span style="color: black;">区别</span>工作表的汇总数据。<span style="color: black;">而后</span>单击【下一步】。</span></p>
<div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/tos-cn-i-qvj2lq49k0/e0f6372a92bd458289d74e6dfe033fec~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1727976249&x-signature=HhCL3RvHnPH6%2BR7xGG6mRcpNx1c%3D" style="width: 50%; margin-bottom: 20px;"></div>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">3、【选定区域】中<span style="color: black;">选取</span><span style="color: black;">第1</span>个工作表的数据源，单击【添加】。<span style="color: black;">创立</span>页字段数目<span style="color: black;">选取</span>“1”。【字段1】下方的框中输入“北京分<span style="color: black;">机构</span>”。</span></p>
<div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/tos-cn-i-qvj2lq49k0/ce71a81f85f54403a164b0986279fa90~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1727976249&x-signature=ZM8H3kNjIIrtbmVG60FsuViryYQ%3D" style="width: 50%; margin-bottom: 20px;"></div>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">依次添加其他工作表的数据源。</span></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">添加广州分<span style="color: black;">机构</span>数据源：</span></p>
<div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/tos-cn-i-qvj2lq49k0/405d162db8054606ae321c4db123f95f~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1727976249&x-signature=2fNo6O%2FjTfQCVgwPWYU62hBTBCM%3D" style="width: 50%; margin-bottom: 20px;"></div>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">添加深圳分<span style="color: black;">机构</span>数据源：</span></p>
<div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/tos-cn-i-qvj2lq49k0/6a2baf2204c04d15981b3cee855ace7d~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1727976249&x-signature=pEHz9WvYkmiskPnaMmmNjgq5ahY%3D" style="width: 50%; margin-bottom: 20px;"></div>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">添加完成所有数据源后，单击【下一步】。</span></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">4、<span style="color: black;">选取</span>生成的数据透视表<span style="color: black;">保留</span>位置，<span style="color: black;">能够</span>放在一个新的工作表，<span style="color: black;">亦</span><span style="color: black;">能够</span>放在现有工作表。</span></p>
<div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/tos-cn-i-qvj2lq49k0/e51f6f8c6732411aa8c165d3bbb3738c~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1727976249&x-signature=porlj2Xi1mIJvWX11ft6u%2BU6w40%3D" style="width: 50%; margin-bottom: 20px;"></div>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">单击完成。</span></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">5、生成的数据透视表效果如下图所示：</span></p>
<div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/tos-cn-i-qvj2lq49k0/1e7f563e897c43f4b9f7a5789fc9f412~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1727976249&x-signature=dmkBNo%2BAbCjRAzy0aeKvrDBJ74U%3D" style="width: 50%; margin-bottom: 20px;"></div>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">在数据透视表向导中<span style="color: black;">运用</span>多重合并计算数据区域为数据源创建数据透视表时，自动将数据源第1列（即本例中的“款型”）放在行区域，其他字段放在列区域，统计方式默认为计数。</span></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">6、单击数据透视表值区域任意单元格，右键单击，<span style="color: black;">选取</span>【值字段设置】，打开【值字段设置】对话框，如下图所示。【值汇总方式】修改为【求和】。</span></p>
<div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/tos-cn-i-qvj2lq49k0/d5e633c4d1934beeb36b25d35288823b~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1727976249&x-signature=TSIAJ%2Bs%2Bx0YoP7lfT9TwhZbHOlA%3D" style="width: 50%; margin-bottom: 20px;"></div>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">单击确定后，得到的效果如下图所示：</span></p>
<div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/tos-cn-i-qvj2lq49k0/6cf99fdb2dc24cf188a3b68fbfb254fd~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1727976249&x-signature=3VB1SfS5tYXKql11R9sQwsbWB5I%3D" style="width: 50%; margin-bottom: 20px;"></div>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;"><span style="color: black;">因为</span>“颜色”字段为文本，<span style="color: black;">因此呢</span>求和结果为0。</span></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">单击【列标签】下拉按钮，只勾选【销量】和【<span style="color: black;">营销</span>额】字段，如下图所示：</span></p>
<div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/tos-cn-i-qvj2lq49k0/2c566c9d4e9447f89d4adc0707939ba5~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1727976249&x-signature=dd7YFA3tv7l%2Fy%2BqrWggyPdJzTA8%3D" style="width: 50%; margin-bottom: 20px;"></div>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">销量和<span style="color: black;">营销</span>额之和得到的数据<span style="color: black;">无</span><span style="color: black;">道理</span>，<span style="color: black;">因此呢</span>需要将行方向的总计取消。单击【数据透视表工具】-【设计】-【总计】-【仅对列启用】，取消行方向的总计。结果如下图所示：</span></p>
<div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/tos-cn-i-qvj2lq49k0/7c2915d3ea5f4bb09d26db1f512492f5~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1727976249&x-signature=nyArEP%2FI3eDDBbIUcJI5A09x5nI%3D" style="width: 50%; margin-bottom: 20px;"></div>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">为了便于阅读数据透视表，<span style="color: black;">能够</span>将页字段修改为“分<span style="color: black;">机构</span>”，行标签修改为“款型”。如下图所示：</span></p>
<div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/tos-cn-i-qvj2lq49k0/af769c000c9a4b439629ff452163a90f~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1727976249&x-signature=1Vph%2BnVm%2Bk8SV%2FhYI0igbZi58Nk%3D" style="width: 50%; margin-bottom: 20px;"></div>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;"><span style="color: black;">能够</span>在页字段中筛选“北京分<span style="color: black;">机构</span>”，<span style="color: black;">那样</span>数据透视表就只统计北京分<span style="color: black;">机构</span>数据源的销量和<span style="color: black;">营销</span>额合计，如下图所示：</span></p>
<div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/tos-cn-i-qvj2lq49k0/293ff06cf6e24dc384aa9c7ee51b1734~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1727976249&x-signature=CwntcnaiH9g0oqmw2OuKDxkm2WQ%3D" style="width: 50%; margin-bottom: 20px;"></div>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">还<span style="color: black;">能够</span>将页字段“分<span style="color: black;">机构</span>”由筛选区域移动到行区域，效果如下图所示：</span></p>
<div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p26-sign.toutiaoimg.com/tos-cn-i-qvj2lq49k0/4d2938a179a44e199c99e03a77aa3f00~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1727976249&x-signature=BM1uFbxlOyOW2AC0%2BVlupDcz%2BLI%3D" style="width: 50%; margin-bottom: 20px;"></div>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;"><span style="color: black;">运用</span>【数据透视表和数据透视图向导】虽然能从多个工作表数据源创建数据透视表，但<span style="color: black;">亦</span>存在<span style="color: black;">许多</span>限制：</span></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">（1）数据源的第1列被添加到数据透视表的行区域。<span style="color: black;">倘若</span><span style="color: black;">期盼</span>将其他列字段放在行区域，需要在数据源中将该列字段移动到第1列。</span></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">（2）其他列自动<span style="color: black;">所有</span>被添加到数据透视表的值区域。<span style="color: black;">倘若</span>只<span style="color: black;">期盼</span>数据透视表对部分字段进行统计，需要在数据透视表中筛选对应字段。</span></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">（3）数据透视表值区域中只能<span style="color: black;">运用</span>一种统计方式。本例中，数据透视表中，对销量和<span style="color: black;">营销</span>额均求和，<span style="color: black;">没法</span>实现对<span style="color: black;">区别</span>字段<span style="color: black;">运用</span><span style="color: black;">区别</span>的统计方式，例如<span style="color: black;">没法</span>对销量求和并对<span style="color: black;">营销</span>额求平均值。</span></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;"><span style="color: black;">倘若</span><span style="color: black;">期盼</span>突破这些限制，<span style="color: black;">能够</span><span style="color: black;">运用</span>power query将多个数据源合并，<span style="color: black;">而后</span>再创建数据透视表。关于<span style="color: black;">运用</span>power query合并数据，<span style="color: black;">能够</span>阅读<span style="color: black;">文案</span>：</span></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><a style="color: black;"><span style="color: black;">运用</span>power query合并多个工作簿</a> </p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><a style="color: black;"><span style="color: black;">运用</span>power query合并同一工作</a>簿的多个工作表 </p>
感谢你的精彩评论，带给我新的思考角度。
页:
[1]