数据透视表，2次计算，非常多人都用错了！
<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;">下面是店铺的业务数据，<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/b948f1da1d424d3d9d7b080187618792~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1727960919&x-signature=R1H%2FkcJTbrd7xGq43BRVcjxvk7c%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>是，汇总所有日期，计算各商品的总流量，总订单，总的转化率数据。</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">咱们</span>用数据透视表来进行计算</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><span style="color: black;">插进</span>一个透视表</p>
<div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p26-sign.toutiaoimg.com/tos-cn-i-twdt4qpehh/86767155479042ff9e1df6c74cb8caa5~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1727960919&x-signature=iDigxH1a0%2Ftx%2Bauh4UAOm3O8xxw%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>
<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>将商品字段放在行标签，将客流量，订单，转化率全放在值里面</p>
<div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/tos-cn-i-twdt4qpehh/b84ad79a290746ff88f0bd076c7047cf~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1727960919&x-signature=YR%2FshyEMjCTbJgDNc3v41xI9eF8%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-twdt4qpehh/0de20f550ba34b6ba574dd5ce2a49bb2~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1727960919&x-signature=k4rW0h%2BRnASLSwplmP1qoNWuQjY%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>的转化率是100%</p>
<div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/tos-cn-i-twdt4qpehh/93a110d5de6341f5be1a47354459a9aa~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1727960919&x-signature=TYULaJTVHXNo%2BZPpsojll58Seug%3D" style="width: 50%; margin-bottom: 20px;"></div>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">它透视表的结果，会变成 21%，而<span style="color: black;">实质</span>上，总订单/总流量，<span style="color: black;">仅有</span>6%</p>
<div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/tos-cn-i-twdt4qpehh/1c91186bcd954c01afba77213b67dfc1~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1727960919&x-signature=BFLQ%2BD%2FZ4hGVa7lr6m%2FOalOzWlw%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>到2次计算的指标，<span style="color: black;">咱们</span><span style="color: black;">不可</span>用原始数据中的值</p>
<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>
<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>，<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-twdt4qpehh/55c8c4baa4cf4d318d4281f02f982253~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1727960919&x-signature=SEE1%2FMh5%2BZptRGJOkDEcH33QTCk%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-twdt4qpehh/ecc4765552e944219a4b47ada072f36a~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1727960919&x-signature=grj0CFYgjcUEqkoYbnPk%2Fzvsarw%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>
页:
[1]