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>你转换成右边的格式</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">例如：赵云在两个城市有<span style="color: black;">营销</span>记录，对应转成右边的2条记录，依些类推</p>
<div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/tos-cn-i-axegupay5k/94e78cc462e3486f9229788569a5877c~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1727958540&x-signature=pkfypEHgKtejFffwhie2%2FqCsSAQ%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>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">网上<span style="color: black;">亦</span>有教程是用Powerquery来实现的逆透视，<span style="color: black;">然则</span>它受制于版本限制，必须是office版本，且<span style="color: black;">需求</span>是2016版本以上才<span style="color: black;">能够</span>实现，<span style="color: black;">然则</span><span style="color: black;">非常多</span>小伙伴用的是WPS软件</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;"><span style="color: black;">第1</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>有2列不需要逆透视表，<span style="color: black;">咱们</span>只能<span style="color: black;">保存</span>1列，把它放到需要逆透视字段的前面</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>
<div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/tos-cn-i-6w9my0ksvp/4de746cf36ac485a936d7cc5c1d8e95d~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1727958540&x-signature=xoOOqM2pUoRUGDPQUlV6%2BD4qJNY%3D" style="width: 50%; margin-bottom: 20px;"></div>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">PS：<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://p26-sign.toutiaoimg.com/tos-cn-i-6w9my0ksvp/cb90ec57af9945bbb36d5f9785362f4b~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1727958540&x-signature=SRX6PAPZUucsKaeAbbLspsdOez4%3D" style="width: 50%; margin-bottom: 20px;"></div>
<h1 style="color: black; text-align: left; margin-bottom: 10px;">第二步：<span style="color: black;">插进</span>逆透视表</h1>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">这一步WPS和EXCEL进入的路径有点<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>是WPS用户，<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>
<div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/tos-cn-i-6w9my0ksvp/9528946bf51c4103b8ce533a0c25b480~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1727958540&x-signature=e6MwxWKj01jKRhGzC2YgEVF10rk%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>是OFFICE用户，<span style="color: black;">咱们</span>需要依次按下ALT、D、P、P键（<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/361608dfa3f649b9bbc6c80d059a5543~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1727958540&x-signature=huFekMN0WyV3QL%2BD%2BZmVGfIvzT8%3D" style="width: 50%; margin-bottom: 20px;"></div>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">按下来的操作WPS和OFFICE是<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/1a616f27e1e14494b765f0ac79c3a397~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1727958540&x-signature=qB5ofXRdQtD%2FNTb5qp0WosU0pUs%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>从辅助C列<span style="color: black;">起始</span>选数据，<span style="color: black;">不可</span>从A列<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/dfa93b771feb4945b30afbc5535bff15~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1727958540&x-signature=SvjkErFOmnWZ46YWA2Ujx9ectog%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/ddf96dcecb764e0a836abc0c9a44b8dc~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1727958540&x-signature=efyVm%2BZg2Iw4Zn1s7ly6OhJBrjo%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/e9977ce65aa1434cbeecac8ee0fc092c~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1727958540&x-signature=quHahoru1qyB0iwCQyYd1gfAXjE%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>将它删除掉，把没用的D列页1给删除掉</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>辅助列的数字匹配过来</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">这儿</span>用=XLOOKUP(K2,C:C,A:B)</p>
<div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/tos-cn-i-6w9my0ksvp/51b645aab8bc44ca8a810c3999a7fd42~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1727958540&x-signature=9gTPmRZHGEWVDNCpc1MJUzf07Tk%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>
“NB”（牛×的缩写，表示叹为观止） 可以发布外链的网站 http://www.fok120.com/ 网站建设seio论坛http://www.fok120.com/ 谢谢、感谢、感恩、辛苦了、有你真好等。
页:
[1]