6hz7vif 发表于 2024-10-1 09:30:54

数据透视表8个数据分析小技巧


    <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 style="color: black;">都数</span>Excel用户印象中，数据透视表只是一个<span style="color: black;">归类</span>汇总的工具。其实它的数据分析功能<span style="color: black;">亦</span>很强的，今天分享常用的8个数据分析小技巧。</span></span></p>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;"><strong style="color: blue;"><span style="color: black;">1、<span style="color: black;">显现</span>次数统计</span></strong></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;">【例1】统计下表中男女生的人数</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>
    <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 style="color: black;">非常多</span><span style="color: black;">朋友</span>以为<span style="color: black;">仅有</span>数字列<span style="color: black;">才可</span>放在值字段，No! 把字符列放进去是<span style="color: black;">能够</span>计数的。</span></span></p>
    <div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/tos-cn-i-qvj2lq49k0/82ca78ae188a406c945357310cdb82c9~noop.image?_iz=58558&amp;from=article.pc_detail&amp;lk3s=953192f4&amp;x-expires=1727956315&amp;x-signature=fuxfg5%2Fi1JpC2ZrEYo7eUMp4IDc%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;"><strong style="color: blue;"><span style="color: black;">2、计算占比</span></strong></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;">【例2】统计下表中男女生的人数</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 style="color: black;">表示</span>方式修改为“</span><strong style="color: blue;"><span style="color: black;">总计的百分比</span></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 style="color: black;">能够</span>添加N次的，不要以为只能添加一次</span></span></p>
    <div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/tos-cn-i-qvj2lq49k0/941a1898212647a99480f2c804d07180~noop.image?_iz=58558&amp;from=article.pc_detail&amp;lk3s=953192f4&amp;x-expires=1727956315&amp;x-signature=U5qHSRVx6ABQyNPi5JeHmsaNGYc%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;"><strong style="color: blue;"><span style="color: black;">3、生成排名（中国式）</span></strong></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;">【例3】<span style="color: black;">按照</span>下表中<span style="color: black;">营销</span>数量，生成排名</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 style="color: black;">表示</span>方式设置为“</span><strong style="color: blue;"><span style="color: black;">降序<span style="color: black;">摆列</span></span></strong><span style="color: black;">”<span style="color: black;">就可</span>生成排名</span></span></p>
    <div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/tos-cn-i-qvj2lq49k0/3f8804283348426383c38b6aacad52b1~noop.image?_iz=58558&amp;from=article.pc_detail&amp;lk3s=953192f4&amp;x-expires=1727956315&amp;x-signature=goibq8%2FGx1SHJ7FZrb6B8CirFwo%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;"><strong style="color: blue;"><span style="color: black;">4、累计计算</span></strong></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;">【例4】<span style="color: black;">按照</span>入库和出库数量，生成<span style="color: black;">每日</span>的剩余库存量</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 style="color: black;">每日</span>的进销差，<span style="color: black;">而后</span>把值<span style="color: black;">表示</span>方式设置“按某一字段汇总”，<span style="color: black;">选择</span>默认的日期。</span></span></p>
    <div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/tos-cn-i-qvj2lq49k0/1c7e7207ac0a4fb682540faa1ada2b55~noop.image?_iz=58558&amp;from=article.pc_detail&amp;lk3s=953192f4&amp;x-expires=1727956315&amp;x-signature=UvZovd1mrll%2BNpUT4MSqRREvt8Q%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;"><strong style="color: blue;"><span style="color: black;">5、和标准值对比</span></strong></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;">【例5】计算出每人和</span><strong style="color: blue;"><span style="color: black;">指标</span></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 style="color: black;">营销</span>数量到值标签中，后两列的值<span style="color: black;">表示</span>方式分别设置为“</span><strong style="color: blue;"><span style="color: black;">差异</span></strong><span style="color: black;">”和</span><strong style="color: blue;"><span style="color: black;">差异百分比</span></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/ef9bc63d621d4d6395118dc00ea81e36~noop.image?_iz=58558&amp;from=article.pc_detail&amp;lk3s=953192f4&amp;x-expires=1727956315&amp;x-signature=8ZKbaVIlqE6T22EkegYwQmx54pY%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;"><strong style="color: blue;"><span style="color: black;">6、总计百分比</span></strong></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 style="color: black;">需求</span>生成<span style="color: black;">每一个</span>区、个人占总销量的百分比</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 style="color: black;">表示</span>方式设置为“</span><strong style="color: blue;"><span style="color: black;">列汇总百分比</span></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/376c7fe0446b4ee98d82bf3520f7d681~noop.image?_iz=58558&amp;from=article.pc_detail&amp;lk3s=953192f4&amp;x-expires=1727956315&amp;x-signature=8jGQmCityQUSxAWen%2BzNpOsTNCM%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;"><strong style="color: blue;"><span style="color: black;">7、内部百分占比</span></strong></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;">【例7】下表中每人在本地区的占比分析</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 style="color: black;">表示</span>方式设置为“</span><strong style="color: blue;"><span style="color: black;">父级百分比占比”</span></strong></span></p>
    <div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/tos-cn-i-qvj2lq49k0/014a7228eb2346fbac8734efd5956366~noop.image?_iz=58558&amp;from=article.pc_detail&amp;lk3s=953192f4&amp;x-expires=1727956315&amp;x-signature=K4yWkZxIHwywSTb4muUy3fwN%2BEM%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;"><strong style="color: blue;"><span style="color: black;">8、区总计占比+区内部占比</span></strong></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;">【例8】<span style="color: black;">表示</span>各个区占总计的百分比 + 区内部占本区总计的百分比</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 style="color: black;">表示</span>方式设置为“</span><strong style="color: blue;"><span style="color: black;">父行百分比占比”</span></strong></span></p>
    <div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/tos-cn-i-qvj2lq49k0/80bce12f63864388a4aa8c1cc9935828~noop.image?_iz=58558&amp;from=article.pc_detail&amp;lk3s=953192f4&amp;x-expires=1727956315&amp;x-signature=V50Kfp9nurYzUhkpCj%2Fp%2BAU4tqk%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>




页: [1]
查看完整版本: 数据透视表8个数据分析小技巧