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>明细：</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-axegupay5k/9f943f02989449459456830fcb72c1db~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1725685262&x-signature=v794zBaFaayHOZhSaF1oHlSeWAk%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>
<h1 style="color: black; text-align: left; margin-bottom: 10px;">1、公式法</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>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">输入公式的是：=UNIQUE(TOCOL(B:B,3))</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">TOCOL第2参数填数字3，<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>就去除了B列的空格</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">运用</span>UNQUE公式，<span style="color: black;">能够</span>得到B列不重复值列表：</p>
<div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/tos-cn-i-6w9my0ksvp/6dbda9aca0cd4887b2a2120e38b92fba~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1725685262&x-signature=%2BPGy53viCNsXZVJ6n1WDM2Ra1Is%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>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">=SUMIFS(C:C,B:B,G2)</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">暗示</span>对C列求和，<span style="color: black;">要求</span>是B列里面对应G2的值</p>
<div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/tos-cn-i-6w9my0ksvp/1fb7c31fae53418887ee65e26e1f3356~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1725685262&x-signature=RQapz5CZdaO4B9yoXCS0Dyfl0B4%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;">=SUMIFS(D:D,B:B,G2)</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">把求和区域换<span style="color: black;">成为了</span>D列，<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-6w9my0ksvp/0579e97a66164e43a61940a2efbfe481~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1725685262&x-signature=V1sL5S6eAW%2BOfLggrh%2FZYcPk8JU%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>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">=I2/H2</p>
<div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/tos-cn-i-6w9my0ksvp/79e8548a1d454bd8922a2b7aed21a12a~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1725685262&x-signature=Z66rEo3OwBIPjPom3IVIYhaJGno%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>
<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>数据透视表</p>
<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://p26-sign.toutiaoimg.com/tos-cn-i-6w9my0ksvp/8e24c96dc3ad4b2f968606db01fe2e23~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1725685262&x-signature=vqn48SkLj57gLaHzU4p5y0Ecb14%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-6w9my0ksvp/ff84eaa297c2452a9fbc13719a4012bb~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1725685262&x-signature=ypiDKCWvIfMPEwF9jRs%2BN7a9nR8%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-6w9my0ksvp/673302d9a97746daa1bcc902bbbc8e06~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1725685262&x-signature=j%2BcdPNOebAvWp9gseWzsQKX3Pi8%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/tos-cn-i-6w9my0ksvp/05c18afdaf784510a1a8063a8f3ebd41~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1725685262&x-signature=PBK9xvtGsogOGkuCZaJhdJtH7D8%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><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-6w9my0ksvp/eda3db91f5cf4afab2f4c31ecc86c7be~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1725685262&x-signature=oAvjXS7TGGwEc2FYL9SWA9ERfsQ%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>
<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-6w9my0ksvp/3857a12456434dbd97d0d3418ef1c688~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1725685262&x-signature=5e0xa844X9YciEZm4Mk8pzMZ12I%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>
感谢楼主的分享！我学到了很多。 我完全同意你的看法，期待我们能深入探讨这个问题。 “BS”（鄙视的缩写） 回顾过去一年，是艰难的一年；展望未来，是辉煌的一年。 你的见解真是独到，让我受益匪浅。 i免费外链发布平台 http://www.fok120.com/ 我完全同意你的观点，说得太对了。
页:
[1]