这个数据透视表的隐匿功能，非常多人都不晓得！多列数据变一列数据-天涯论坛

nykek5i 发表于 2024-10-1 17:24:24

这个数据透视表的隐匿功能，非常多人都不晓得！多列数据变一列数据

<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;"><span style="color: black;">晓得</span><span style="color: black;">怎样</span>将Excel工作表中的多列数据转换成一列数据吗？<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>介绍一个简单的<span style="color: black;">办法</span>，只需不到一分钟就<span style="color: black;">能够</span>快速<span style="color: black;">处理</span>这个问题。</span></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">今天<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></p>
<div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/tos-cn-i-qvj2lq49k0/2c32d28b6fbc4deaac42038aad302112~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1727976847&x-signature=sSBaxg64%2FT%2Fi6TDX5k3jGLUSHOU%3D" style="width: 50%; margin-bottom: 20px;"></div>
<h1 style="color: black; text-align: left; margin-bottom: 10px;">工具</h1><span style="color: black;">本案例<span style="color: black;">运用</span>版本为Excel 2016</span>
<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;">如下图1所示，是一份<span style="color: black;">包括</span>了<span style="color: black;">分部</span>信息和姓名信息的Excel数据表，一共有5列数据，<span style="color: black;">此刻</span>想将下表快速转换成两列数据的表格，如图2所示，<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/3f5cfaad2c8c4eb9827a52a881726ab7~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1727976847&x-signature=Nik%2FtavPVorYjLtJcs0UFgCWcy4%3D" style="width: 50%; margin-bottom: 20px;">
   <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">图1</p>
</div>
<div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/tos-cn-i-qvj2lq49k0/98dc65afe8be497f88e7f71434d7ad20~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1727976847&x-signature=Twz64fACwoZkjxBhlqbwx5OakmI%3D" style="width: 50%; margin-bottom: 20px;">
   <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">图2</p>
</div>
<h1 style="color: black; text-align: left; margin-bottom: 10px;"><span style="color: black;">处理</span><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>1、<span style="color: black;">首要</span>【ALT+A】选中所有的数据，如下图所示：</p>
<div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/tos-cn-i-qvj2lq49k0/d81ffcef843f4b6bb8f67d967bce3523~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1727976847&x-signature=HeyMnVxjRcSNZ3Gybl6PhzUYV%2BA%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>2、<span style="color: black;">先按【ALT+D】<span style="color: black;">而后</span>松开，再按P键，打开【数据透视表向导】对话框，如下图所示：</span></p>
<div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/tos-cn-i-qvj2lq49k0/467532a3579145a1bc2b59726cc4d271~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1727976847&x-signature=n31Kw92D0wDVKwZUxmcTCs8mZWk%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>3、<span style="color: black;"><span style="color: black;">选取</span>【多重合并计算数据区域】，再分别点两次【下一步】，如下图所示：</span></p>
<div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/tos-cn-i-qvj2lq49k0/c74fbba989d240018c45130c144fd453~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1727976847&x-signature=0bAjv8br7Q%2Fk%2F68HuDgcp0VtJ48%3D" style="width: 50%; margin-bottom: 20px;"></div>
<div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/tos-cn-i-qvj2lq49k0/a0fb256ea59744e295d0c26c0921616f~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1727976847&x-signature=bl21LOdnYsAkojKVgMNdOTl%2FyO0%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>4、<span style="color: black;">在选定区域中，<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>数据的时候要多选一个空白列<span style="color: black;">做为</span>标题列），如下图所示：</span></p>
<div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/tos-cn-i-qvj2lq49k0/3eadae5ba38a48398268c886afb1a3c0~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1727976847&x-signature=xnWqU55vQg%2BAXHNVncu9OTT0qBo%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>5、<span style="color: black;"><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></p>
<div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/tos-cn-i-qvj2lq49k0/bc7016adb6784104a8973c4f483cd6e2~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1727976847&x-signature=riu4S4RuN65%2FPGnYF0vR9N05EcE%3D" style="width: 50%; margin-bottom: 20px;"></div>
<div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/tos-cn-i-qvj2lq49k0/58911b4f3f6a4ff29d736c0f18e25a6c~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1727976847&x-signature=KC%2FUTvLHx1o8Q0uSI4bDCjorSzI%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>6、此时，双击总计<span style="color: black;">这儿</span>的80这个数字，得到图2的结果，如下图所示：</p>
<div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/tos-cn-i-qvj2lq49k0/6843876e1f8f40518eada6474ec97396~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1727976847&x-signature=IlsIuXYyRLil%2BR0isoA668tebSI%3D" style="width: 50%; margin-bottom: 20px;"></div>
<div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/tos-cn-i-qvj2lq49k0/3dd25d531b8a49c0a6bba52be81f741f~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1727976847&x-signature=786m%2BWHpeE%2BdN%2B%2F6IOQWhs%2Bc5BY%3D" style="width: 50%; margin-bottom: 20px;">
   <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">图2</p>
</div>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">过程</span>7、将【值】这列的空白去掉，如下图所示：</p>
<div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/tos-cn-i-qvj2lq49k0/6e3d7861f8e74aa9bcb380b0ff624750~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1727976847&x-signature=zkwiYv7MyZtjY5v%2BCT3nH5U7Qss%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>8、整理后得到如下图所示的结果即为<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/f2378d07a87d45089085282b78262ddc~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1727976847&x-signature=v7Q6yGe%2F%2BUUD7fJk0u2LXe8EVDE%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>1、<span style="color: black;">首要</span>【ALT+A】选中所有的数据</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">过程</span>2、<span style="color: black;">先按【ALT+D】<span style="color: black;">而后</span>松开，再按P键，打开【数据透视表向导】对话框</span></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">过程</span>3、<span style="color: black;"><span style="color: black;">选取</span>【多重合并计算数据区域】，再分别点两次【下一步】</span></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">过程</span>4、<span style="color: black;">在选定区域中，<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><span style="color: black;">选取</span>数据的时候要多选一个空白列<span style="color: black;">做为</span>标题列）</span></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">过程</span>5、<span style="color: black;"><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></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">过程</span>6、此时，双击总计<span style="color: black;">这儿</span>的80这个数字，得到图2的结果</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">过程</span>7、将【值】这列的空白去掉</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">过程</span>8、整理后得到如下图所示的结果即为<span style="color: black;">最后</span>结果</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><strong style="color: blue;">职场里想要升职加薪，务必需要<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>excel的高端用法，<span style="color: black;">有些</span>动态的数据看板，这个专栏里有<span style="color: black;">非常多</span>很常用的excel职场商务模板的制作教程，欢迎<span style="color: black;">大众</span>选购！</strong></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="https://p1.toutiaoimg.com/large/pgc-image/3e0218d1f54a42a3b436cdd899f232ab" 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;">作者：小达人干货分享中心</div>
               <div style="color: black; text-align: left; margin-bottom: 10px;">
                     <div style="color: black; text-align: left; margin-bottom: 10px;">69.9币</div>
                     <div style="color: black; text-align: left; margin-bottom: 10px;">9人已购</div>
               </div>
               <div style="color: black; text-align: left; margin-bottom: 10px;">查看</div>
            </div>
         </div>
   </div>
</div>
<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="https://p1.toutiaoimg.com/large/pgc-image/c95cccc55ee940d1b4f55af73bc6d900" 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;">作者：小达人干货分享中心</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;">3人已购</div>
               </div>
               <div style="color: black; text-align: left; margin-bottom: 10px;">查看</div>
            </div>
         </div>
   </div>
</div>

b1gc8v 发表于 2024-10-20 22:16:47

你的话语真是温暖如春，让我心生感激。

nqkk58 发表于 2024-11-13 04:46:53

我深感你的理解与共鸣，愿对话长流。

页: [1]

天涯论坛's Archiver

这个数据透视表的隐匿功能，非常多人都不晓得！多列数据变一列数据