ikkhksvu 发表于 2024-10-1 08:54:39

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><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/4d5121d4ab824a918c89c2490c17a918~noop.image?_iz=58558&amp;from=article.pc_detail&amp;lk3s=953192f4&amp;x-expires=1727953834&amp;x-signature=snMYi%2BjYIrK%2BU4xgJORbDTIkRV4%3D" style="width: 50%; margin-bottom: 20px;"></div>
    <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>点击<span style="color: black;">插进</span>，<span style="color: black;">选取</span>数据透视表，放在G3单元格，如下所示：</p>
    <div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/tos-cn-i-qvj2lq49k0/44665b54135846edae6f90cc7ccdea5e~noop.image?_iz=58558&amp;from=article.pc_detail&amp;lk3s=953192f4&amp;x-expires=1727953834&amp;x-signature=FaGVz%2FIfSJ12xfOX5eeHC2RQ4e4%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/209f9814cd6c49ce80c8d1c56a1d7c3e~noop.image?_iz=58558&amp;from=article.pc_detail&amp;lk3s=953192f4&amp;x-expires=1727953834&amp;x-signature=ZkJEJppmcxQaIvQoRKjQErXl%2Bfc%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>不符合<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/046db39586f749df8ccc6c0f5e48a01f~noop.image?_iz=58558&amp;from=article.pc_detail&amp;lk3s=953192f4&amp;x-expires=1727953834&amp;x-signature=WAvZHh5LR62Fg3XyWNjVanhKU2w%3D" style="width: 50%; margin-bottom: 20px;"></div>
    <h1 style="color: black; text-align: left; margin-bottom: 10px;">2、更新汇总方式</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>值汇总方式，<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/4e952d2db35b4a0b8d43f791e2d88d1f~noop.image?_iz=58558&amp;from=article.pc_detail&amp;lk3s=953192f4&amp;x-expires=1727953834&amp;x-signature=oLFXbI2Pw46%2B9rvzQoD%2FvpTjfzE%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>的<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>说：吕布一次是30均价<span style="color: black;">营销</span>的，一次是40均价<span style="color: black;">营销</span>的，<span style="color: black;">那样</span><span style="color: black;">倘若</span>只是求平均值，Excel只会简单的把所有均价进行求和再除以数量，<span style="color: black;">因此</span>Excel是用（30+40）/2=35得到的均价；</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>的均价应该是总金额除以总数量，结果应该是(1920+1600)/(65+40)=3520/104=33.85</p>
    <div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/tos-cn-i-qvj2lq49k0/644f81ee11054e71b136ea7ddb9cdb50~noop.image?_iz=58558&amp;from=article.pc_detail&amp;lk3s=953192f4&amp;x-expires=1727953834&amp;x-signature=idOoH17JNBTDyt31XzPrwYENcLs%3D" style="width: 50%; margin-bottom: 20px;"></div>
    <h1 style="color: black; text-align: left; margin-bottom: 10px;">3，<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>
    <div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/tos-cn-i-qvj2lq49k0/f70d42c08aa6444e981593eb3b1a88ce~noop.image?_iz=58558&amp;from=article.pc_detail&amp;lk3s=953192f4&amp;x-expires=1727953834&amp;x-signature=zJtoEIyrD4eIAEkd1uNX3%2F0IhPI%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>金额/<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/47427c9e580e429caf61dc35f943cb49~noop.image?_iz=58558&amp;from=article.pc_detail&amp;lk3s=953192f4&amp;x-expires=1727953834&amp;x-signature=tDoYb8pUBi%2F1r9EsFYelMDDLldg%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>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">关于这个小技巧，你学会了么？动手试试吧！</p>




m5k1umn 发表于 2024-10-16 17:41:27

你的见解独到，让我受益匪浅，非常感谢。
页: [1]
查看完整版本: Excel数据透视表，不可忽略的细节，你会么？