219mze 发表于 2024-10-1 10:24:46

用 数据透视表 完成 Excel多表合并


    <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;"><strong style="color: blue;">单</strong></span>列内容进行数据汇总，而<strong style="color: blue;">多列</strong>却<span style="color: black;">没法</span>实现。前段时间<span style="color: black;">亦</span>推送过<strong style="color: blue;">Power Query</strong>的<span style="color: black;">办法</span>，但它有版本限制。而今天兰色推送的是一个<strong style="color: blue;"><span style="color: black;">通用</span></strong>的多表汇总<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>在汇总表中把所有月份的数据汇总到<span style="color: black;">一块</span>。</p>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><img src="https://mmbiz.qpic.cn/mmbiz/4jUToJBtQvRPE48WZbibVGcD2eLLFFCM74pmaQxaaicyNsax4ib7QMc646pYlohnQeKZHERDibPAUP3nQFwj7RLrwA/640?wx_fmt=jpeg&amp;tp=webp&amp;wxfrom=5&amp;wx_lazy=1&amp;wx_co=1" style="width: 50%; margin-bottom: 20px;"><strong style="color: blue;">操作<span style="color: black;">过程</span>：</strong></p>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><strong style="color: blue;">1、</strong>数据选项卡 - 现有连接 - 浏览<span style="color: black;">更加多</span>。(excel2003版数据 - 导入<span style="color: black;">外边</span>数据)</p>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><img src="https://mmbiz.qpic.cn/mmbiz/4jUToJBtQvRPE48WZbibVGcD2eLLFFCM7xWmE8MKibggicp0oUBQlyAMLvv4JPndBPyX6pIzQu4Ka9FicDHiaG7ibcuA/640?wx_fmt=jpeg&amp;tp=webp&amp;wxfrom=5&amp;wx_lazy=1&amp;wx_co=1" style="width: 50%; margin-bottom: 20px;"></p>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">2、找到当前操作的excel文件。</p>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><img src="https://mmbiz.qpic.cn/mmbiz/4jUToJBtQvRPE48WZbibVGcD2eLLFFCM726QhAWzTBM5gby5qMXODA1ibBjCQT90xRt6VGsJXnicWs6tZcdeSAdVw/640?wx_fmt=jpeg&amp;tp=webp&amp;wxfrom=5&amp;wx_lazy=1&amp;wx_co=1" style="width: 50%; margin-bottom: 20px;"></p>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">3、<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;"><img src="https://mmbiz.qpic.cn/mmbiz/4jUToJBtQvRPE48WZbibVGcD2eLLFFCM79fXyStu72zDIluAkpeONwAeIIOoO2W0cmX1z1yuR6eVhEImpmkic83Q/640?wx_fmt=jpeg&amp;tp=webp&amp;wxfrom=5&amp;wx_lazy=1&amp;wx_co=1" style="width: 50%; margin-bottom: 20px;"></p>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">4、【导入数据】窗口中，<span style="color: black;">表示</span>方式选“<strong style="color: blue;">数据透视表</strong>”，<span style="color: black;">安置</span>位置选<strong style="color: blue;">工资表A1单元格</strong>（<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;"><img src="https://mmbiz.qpic.cn/mmbiz/4jUToJBtQvRPE48WZbibVGcD2eLLFFCM7vVNpZUt4t96lABiaa0dGgjaXhdRIbIwjxCc2WnhunRc2QHwRZfmOcDA/640?wx_fmt=jpeg&amp;tp=webp&amp;wxfrom=5&amp;wx_lazy=1&amp;wx_co=1" style="width: 50%; margin-bottom: 20px;">5、<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;"><img src="https://mmbiz.qpic.cn/mmbiz/4jUToJBtQvRPE48WZbibVGcD2eLLFFCM71NdvnhJ1266oxqfsTgMEGKZS2HlTpbscvgUWdp0V24mkAb7Z9MIEzQ/640?wx_fmt=jpeg&amp;tp=webp&amp;wxfrom=5&amp;wx_lazy=1&amp;wx_co=1" style="width: 50%; margin-bottom: 20px;"></p>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">6、把下面的代码粘贴到命令文本框里。</p>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">------------------------------------</p>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">select &nbsp;3月 as 月份, * from <span style="color: black;">union all</span>select 4月 as 月份, * from <span style="color: black;">union all</span> select 5月 as 月份, * from </p>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">-------------------------------------</p>
    <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;"><strong style="color: blue;"> 1) </strong></span><span style="color: black;">详细</span>的SQL语句一言两语<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;">Select * from [表1$] <span style="color: black;">union all</span> Select * from [表2$] <span style="color: black;">union all</span> Select * from [表3$]....<span style="color: black;">union all</span> Select * from [表N$]</p>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;"> 2) </span><span style="color: black;">3月</span> <span style="color: black;">as</span> <span style="color: black;">月份</span>: 是为三个表创建一个<span style="color: black;">一起</span>的字段（<span style="color: black;">月份</span>），其中引号内<span style="color: black;">3月</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;">as </span>连接，放在select 之后 *号之前。<span style="color: black;">倘若</span><span style="color: black;">不</span><span style="color: black;">需要为三个表创建<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;"><img src="https://mmbiz.qpic.cn/mmbiz/4jUToJBtQvRPE48WZbibVGcD2eLLFFCM7AFNTh22jtaiabbrlDV8AuCVmKHZ67NMkXibFu3JrW6cxM0FzibBSRpMXA/640?wx_fmt=jpeg&amp;tp=webp&amp;wxfrom=5&amp;wx_lazy=1&amp;wx_co=1" style="width: 50%; margin-bottom: 20px;"></p>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><strong style="color: blue;">7、</strong>连接完成！下面<span style="color: black;">便是</span>对数据透视表的基本操作，添加行、列标标签和数据标签。</p>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><img src="https://mmbiz.qpic.cn/mmbiz/4jUToJBtQvRPE48WZbibVGcD2eLLFFCM76jOWPXubNOUKKahGqt8bnHlCA0Yj8lyBgECNKmqnfiaMexgGF0Vraibw/640?wx_fmt=jpeg&amp;tp=webp&amp;wxfrom=5&amp;wx_lazy=1&amp;wx_co=1" style="width: 50%; margin-bottom: 20px;"></p>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><strong style="color: blue;">8、</strong><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;"><img src="https://mmbiz.qpic.cn/mmbiz/4jUToJBtQvRPE48WZbibVGcD2eLLFFCM7gWiaSpUuPHEIwiaH7Yf8lb3Ex8weagzrOvcwdPxcoXJGlaTNp3Hagkug/640?wx_fmt=jpeg&amp;tp=webp&amp;wxfrom=5&amp;wx_lazy=1&amp;wx_co=1" style="width: 50%; margin-bottom: 20px;"></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 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><span style="color: black;">下面二维码<span style="color: black;">照片</span>，点上面”</span><span style="color: black;">识别图中二维码</span><span style="color: black;">“<span style="color: black;">而后</span>再点关注，<span style="color: black;">每日</span><span style="color: black;">能够</span>收到一篇兰色最新写的excel教程。</span></p>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><img src="https://mmbiz.qpic.cn/mmbiz/4jUToJBtQvSebUj6fyjNHbIktD1ls6xopyj5VIDU9waiaHa55s7G4Ric1cM2fAZxneEFsPLguWneYQQdbqZ2iawHg/640?wx_fmt=jpeg&amp;tp=webp&amp;wxfrom=5&amp;wx_lazy=1&amp;wx_co=1" style="width: 50%; margin-bottom: 20px;"></p>




b1gc8v 发表于 2024-10-2 12:49:24

你的见解真是独到，让我受益良多。

nqkk58 发表于 2024-11-12 23:05:16

同意、说得对、没错、我也是这么想的等。
页: [1]
查看完整版本: 用 数据透视表 完成 Excel多表合并