6hz7vif 发表于 2024-10-1 08:05:06

Excel数据透视表,跨多表汇总,1分钟学会


    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">举个工作中的例子,<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>数量</p>
    <div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/tos-cn-i-axegupay5k/e36866167d984640963a4f6606793654~noop.image?_iz=58558&amp;from=article.pc_detail&amp;lk3s=953192f4&amp;x-expires=1727949752&amp;x-signature=zzcVch2Z477p6M1QP72DpI5sA6I%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>再去数据透视。<span style="color: black;">然则</span><span style="color: black;">这般</span>工作量大,并且,当子表数据更新时,总表<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>有<span style="color: black;">办法</span><span style="color: black;">能够</span>一步到位,创建一个数据透视表,<span style="color: black;">而后</span>引用的数据源是各个子表,进行快速汇总更新</p>
    <h1 style="color: black; text-align: left; margin-bottom: 10px;">1、创建透视表</h1>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">首要</span>,<span style="color: black;">咱们</span>在excel里面,依次按下alt、d、p键(不是<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>会得到一个向导窗口,<span style="color: black;">咱们</span><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-6w9my0ksvp/b96b88014951431d8ea8a4b31667793a~noop.image?_iz=58558&amp;from=article.pc_detail&amp;lk3s=953192f4&amp;x-expires=1727949752&amp;x-signature=5BIKgLcEkTn5HdKkNoz1vs9FZXc%3D" style="width: 50%; margin-bottom: 20px;"></div>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">第2步,<span style="color: black;">咱们</span>直接<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-6w9my0ksvp/23298b58e97d433e9f70e602b4727e5f~noop.image?_iz=58558&amp;from=article.pc_detail&amp;lk3s=953192f4&amp;x-expires=1727949752&amp;x-signature=PCEkQuF5Rb%2BUorpg%2BI7LTzR3OAE%3D" style="width: 50%; margin-bottom: 20px;"></div>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">第3步,<span style="color: black;">咱们</span>要选定<span style="color: black;">每一个</span>子表数据区域的数据,<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>依次的添加了4个子表的a:b列数据</p>
    <div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/tos-cn-i-6w9my0ksvp/7e7490f8112c4fbfbc2279bd78948bfc~noop.image?_iz=58558&amp;from=article.pc_detail&amp;lk3s=953192f4&amp;x-expires=1727949752&amp;x-signature=VYpJ4POWEXC9BdeZjVaSmlsr4Tc%3D" style="width: 50%; margin-bottom: 20px;"></div>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">点击完成,所有的子表数据就汇总了,得到了如下的数据透视:</p>
    <div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/tos-cn-i-6w9my0ksvp/e7602cbf5e8c40e69c168b5245e75edd~noop.image?_iz=58558&amp;from=article.pc_detail&amp;lk3s=953192f4&amp;x-expires=1727949752&amp;x-signature=LgKl%2BHKp8RMZprMinpFdqb7AmgM%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>的子表里面,数据有更新时,例如,<span style="color: black;">咱们</span>将商品A的数据进行了修改</p>
    <div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/tos-cn-i-6w9my0ksvp/81bce1f2266648ce8b0b6bb0eb4134e6~noop.image?_iz=58558&amp;from=article.pc_detail&amp;lk3s=953192f4&amp;x-expires=1727949752&amp;x-signature=ohZM8iqoPdyfRPGaBeky3E1UY1k%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-6w9my0ksvp/955ace5dcb6644ad84d494cd1be84ec5~noop.image?_iz=58558&amp;from=article.pc_detail&amp;lk3s=953192f4&amp;x-expires=1727949752&amp;x-signature=Ucyxq8UuG3IV5nVpB30c4XxDyjU%3D" style="width: 50%; margin-bottom: 20px;"></div>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">关于这个小技巧,你学会了么?自己动手试试吧!</p>




4lqedz 发表于 2024-10-2 15:20:22

“板凳”(第三个回帖的人)‌
页: [1]
查看完整版本: Excel数据透视表,跨多表汇总,1分钟学会