Excel数据透视表技巧：分析营销流水数据！-天涯论坛

nqkk58 发表于 2024-8-31 22:50:33

Excel数据透视表技巧：分析营销流水数据！

<div style="color: black; text-align: left; margin-bottom: 10px;">
   <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;">下面A：D列是<span style="color: black;">营销</span>流水数据明细，有地区，商品，数量，金额</p>
   <div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/pgc-image/a10b7d2896bd4027a37853b3c533a5f4~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1725685248&x-signature=tbkKViUkFZl1ZFEYQ76olJ77gu4%3D" style="width: 50%; margin-bottom: 20px;"></div>
   <h1 style="color: black; text-align: left; margin-bottom: 10px;">汇总各地区各商品的数量、金额、均价</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>将数据透视表的位置放在当前工作表的F1单元格，如下所示：</p>
   <div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/pgc-image/d0e0684dab2941edb7099534f000590e~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1725685248&x-signature=uNJ1yhZc3kv9IxJPGY9Hk6%2Bra8Y%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/pgc-image/01366d59e9754f6bb41c2527e28297ec~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1725685248&x-signature=EM07oaY5dqgUBDAIymTyBphgv%2FA%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>字段、项目和集，<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/pgc-image/b9f9b413cd6846cf83555ff2120cabde~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1725685248&x-signature=nKO%2BQybw6x%2BuezDvNtjzOVN%2BKuA%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>=金额/数量，操作的动图如下所示：</p>
   <div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/pgc-image/b19fa0cc220a4053b1501c1266ca757d~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1725685248&x-signature=Vixd%2FSxP1ICM%2BU5yyGR%2FmNI7dKs%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/pgc-image/8f9453bfac2c4e2a965370130f605fd1~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1725685248&x-signature=rIDNReInqzGFNc%2FDcpnvHSQm0BM%3D" style="width: 50%; margin-bottom: 20px;"></div>
   <h1 style="color: black; text-align: left; margin-bottom: 10px;">计算<span style="color: black;">各样</span>商品的在各地区的占比<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>再将金额字段拖入至数据透视表内【第2次拖入进来了】</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>
   <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/pgc-image/d518c55ee84c4fe6b35e6fe792125392~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1725685248&x-signature=LZR%2FvA4NBV5AeHfX9069diN8yPo%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/pgc-image/d6fe99d845b84849be41ead9ff616619~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1725685248&x-signature=%2BAjK%2Bi%2BRV%2FuZDIU7PjEWcep%2B%2BBM%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;">上篇：<a style="color: black;">Excel中Vlookup函数又出错了！</a></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="http://p1.toutiaoimg.com/large/pgc-image/15566074939218e441b3ae5" 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;">414人已购</div>
                     </div>
                     <div style="color: black; text-align: left; margin-bottom: 10px;">查看</div>
               </div>
            </div>
         </div>
   </div>
</div>

4lqedz 发表于 2024-10-6 22:24:48

软文发布平台 http://www.fok120.com/

7wu1wm0 发表于 2024-10-16 15:31:18

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

4zhvml8 发表于 2024-10-21 17:25:30

楼主发的这篇帖子，我觉得非常有道理。

页: [1]

天涯论坛's Archiver

Excel数据透视表技巧：分析营销流水数据！