Excel数据透视表，数据不规范，同事流泪加班-外链论坛

j8typz 发表于 2024-10-1 09:08:14

Excel数据透视表，数据不规范，同事流泪加班

<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>，今天分享2个数据透视表不规范的场景</p>
<h1 style="color: black; text-align: left; margin-bottom: 10px;">1、数据透视表<span style="color: black;">不可</span>正常汇总数据</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>需要汇总<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>数据透视表</p>
<div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/tos-cn-i-qvj2lq49k0/7bcfd740b83b43259345967a90df8885~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1727954481&x-signature=I4XhKcMrYOe%2Fk6vexya94iJCnW0%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>，金额数据并不会自动计算</p>
<div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/tos-cn-i-qvj2lq49k0/8bcbf89f332246409a97ae693af37edf~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1727954481&x-signature=BF5Vv6XA3bnGFR8D9JsO6lWbs%2BE%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>点击计算项，更改计算方式，把它修改成求和，<span style="color: black;">最后</span>它得到的结果仍然是0</p>
<div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/tos-cn-i-qvj2lq49k0/d193d4689a8f4f019f69dad3f5fc4955~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1727954481&x-signature=JyAVMmERjGSCanvmIQvNAwDfPxg%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>需要选中原始数据中的金额字段，在数据选项卡，点击分列，直接点击完成</p>
<div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/tos-cn-i-qvj2lq49k0/6f725282fa4c4cb49d793491e4275a5e~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1727954481&x-signature=ukH2kTYSroglTLo1QqDBF7USMWE%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/145ecd4b70b14415b0c4cdfc461832d3~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1727954481&x-signature=TG81UQxrOY39TiES8LXwN4g60KA%3D" style="width: 50%; margin-bottom: 20px;"></div>
<h1 style="color: black; text-align: left; margin-bottom: 10px;">2、汇总结果，<span style="color: black;">显现</span>重复数据</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>需要汇总数量和金额，<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>值，并进行汇总的</p>
<div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/tos-cn-i-qvj2lq49k0/ce30510c78d645d2a9ccb513c70f80ea~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1727954481&x-signature=J8gGBSNEKHBC7sJgr9vgseBjDm8%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><span style="color: black;">能够</span>在数据源中，按CTRL+H<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-qvj2lq49k0/49c21401f54b43f995a6751898843616~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1727954481&x-signature=xCS%2FirVZs%2BPYLcf22SSA9pW5fIk%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/4bb545ee28c24e9eba09f45a50471c19~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1727954481&x-signature=W1irR7csJ9Vyc4C792I7J6ETdU8%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个小技巧，你学会了么？动手试试吧！</p>
<div style="color: black; text-align: left; margin-bottom: 10px;">
   <div style="color: black; text-align: left; margin-bottom: 10px;">
         <div style="color: black; text-align: left; margin-bottom: 10px;">
            <div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/pgc-image/15566074939218e441b3ae5~noop.image?x-expires=2005045933&x-signature=M9vxTsKQwcO%2Ffs7OHeTfsTb8HOk%3D" style="width: 50%; margin-bottom: 20px;">
               <div style="color: black; text-align: left; margin-bottom: 10px;"><span style="color: black;">专栏</span></div>
            </div>
            <div style="color: black; text-align: left; margin-bottom: 10px;">
               <div style="color: black; text-align: left; margin-bottom: 10px;">Excel数据透视表视频教程</div>
               <div style="color: black; text-align: left; margin-bottom: 10px;">作者：Excel自学成才</div>
               <div style="color: black; text-align: left; margin-bottom: 10px;">
                     <div style="color: black; text-align: left; margin-bottom: 10px;">29.9币</div>
                     <div style="color: black; text-align: left; margin-bottom: 10px;">1,004人已购</div>
               </div>
               <div style="color: black; text-align: left; margin-bottom: 10px;">查看</div>
            </div>
         </div>
   </div>
</div>

4lqedz 发表于 2024-10-3 09:26:25

谷歌网站排名优化 http://www.fok120.com/

j8typz 发表于 2024-10-28 06:51:43

感谢你的精彩评论，带给我新的思考角度。

j8typz 发表于 2024-10-31 04:41:31

同意、说得对、没错、我也是这么想的等。

页: [1]

外链论坛's Archiver

Excel数据透视表，数据不规范，同事流泪加班