怎样提取EXCEL表中多个SHEET中某列最后一个数据？

来源：蜘蛛抓取(WebSpider) 时间：2022-07-10 07:12 标签： 如何从数据表中取一部分数据
格式：=某工作表名+!+单元格名称。

2、一次性提取各工作表同一位置单元格数字。
格式：=第一个工作表名+：+最后一个工作表名+！+单元格名称。

注：使用该方法的前提是各工作表名称有规律且连续。

1、引用本工作表单元格内容：直接就是等号后面，紧跟要引用的单元格。
例：a1单元格要引用b1 的单元格，就是在a1里面输入=b1，即可。

2、引用本工作薄里面其它工作表里面的单元格内容：只要在单元格前加上工作表名，再加一个感叹号“!”。

3、跨工作薄引用：需要在以上两点的基础上，再增加工作薄的名称，即工作薄的名称!工作表的名称!单元格的名称。


大家好，我是李锐，今天在Excel课程群里看到有同学提问如何从多个Excel工作簿中提取不重复值列表，再制作动态下拉菜单。

由于数据源分散在多个不同的Excel文件中，实际工作要求无论哪个文件中的数据源变动，结果中的合并报表和动态下拉菜单都可以自动更新，而且排除重复仅显示不重复值。

这类问题的处理方案较多，可以用power query或者sql查询，也可以用vba编程，虽然之前的图文教程或视频课程里面有包含此类知识点的案例，但今天这个问题要求多技术综合运用，所以专门用这篇教程给出完整操作过程。

除了本文内容，还想全面、系统、快速提升Excel技能，少走弯路的同学，请搜索微信公众号“LiRuiExcel”点击底部菜单，或下方二维码进知识店铺。

更多不同内容、不同方向的Excel视频课程

首先我把需要用到的Excel文件都放在一个文件夹中，方便数据提取和后期更新。

Excel名称及文件夹位置如下图所示。


北京、上海、广州每个文件中都存放着员工的编号和姓名记录，要求从多个文件中提取不重复的员工记录。

看完文件夹位置和文件名称，下面再来看下每张Excel工作簿文件打开以后的样子。

先来看北京（为了方便大家查看仅给出少量数据，即使有大量数据也同样适用）




下面我们开始操作从多个Excel工作簿文件中提取不重复值。

首先介绍一种仅仅点几下鼠标，不需输入任何代码就可以搞定的方法：Power Query法，后面再介绍sql代码法，以及遇到其他要求的扩展应对方法。

首先打开要放置结果的Excel工作表，操作步骤如下图所示。


这里输入Excel文件所在的路径位置，操作步骤如下图所示。


pq会自动扫描指定位置的所有文件，如下图所示。


将数据源位置加载到pq编辑器，如下图所示：


筛选你需要的Excel工作簿文件，如下图所示：


然后单击Content字段右侧的扩展按钮，如下图所示：


再选择你要提取的数据所在的工作表，如下图所示：


然后整理数据源，设置标题行，如下图所示：


再清除多余的标题行记录，可以借助筛选批量去除勾选，如下图所示：


然后按照你所需要的规则选中字段，按照这个字段删除重复值，即提取这个字段下的不重复值数据，如果需要多字段同时考虑重复，可以同时选中多字段再执行操作，如下图所示：


完成多工作簿不重复值的提取后，可以按某一个规则将记录排序，比如这里是需要按照编号排序，如下图所示：


然后关闭并上载，如下图所示：


仅创建连接即可，方便后续将结果加载到任何位置，如下图所示：


最后打开你想展示结果的位置，加载之前在power query编辑器中整理完毕的数据，如下图所示：


注意这个结果是支持后期自动更新的，无论哪个Excel工作簿中的数据源变动，你都可以一键更新结果，操作如下图所示：


整个操作过程都无需输入任何代码，仅仅点击几下鼠标即可，虽然看起来上面的各种操作截图很多，但是整个操作过程不会超过2分钟，不必害怕麻烦。

虽然power query的方法很简单，但也并不是没有限制，它需要至少Excel 2010版本以上才可以使用，在Excel 2010和2013版本中可以安装power query插件，在Excel 2016及以上版本中power query就是内置功能了，可以直接调用。

需要新版安装包的同学可以进入公众号“LiRuiExcel”后台发送如下图红色数字，获取对应版本的安装包

可能还有同学会问，我用的是Excel 2007版本，甚至更低版本的Excel，难道就没有办法解决这类问题了吗？

当然不会，方法很多，我们可以根据情况灵活选择。

下面继续介绍一种任何Excel版本都支持的操作方法，由于之前写过的SQL教程极少人阅读和点赞，所以后来没有再写，今天重新提起来这种扩展性很强的方法：SQL查询法。

这次我们稍微变更一下业务目的，即要求从多个Excel工作簿文件中的B列提取出不重复的员工编号，支持后期的自动更新。

首先我们打开要放置结果的工作表，比如sheet1，调用数据选项卡的现有连接功能，操作如下图所示。


点击浏览更多按钮以后，选择当前文件所在位置，操作如下图所示。


选择表格后，单击确定，如下图所示。


注意此时不要直接导入数据，要点开属性按钮，操作如下图所示。


在连接属性设置中，点击定义按钮，在命令文本中输入sql代码，操作如下图所示。


这时需要根据实际排除重复的规则写sql代码，如果你仅需要每个工作簿中的B列提取不重复值，不删除多个工作簿合并以后的B列重复值，命令文本中的sql代码如下图所示。

从多文件多工作簿中动态提取出不重复值，一键更新结果\广州.xlsx].[员工表$]

为了方便同学们总结，这里给出通用代码结构：

[路径\工作簿3名称].[工作表名称$]

当然实际情况下按你的路径、文件名、工作表名填写即可，如果有更多文件需要合并，继续在后面连接即可，结构同前所示。

单击确定按钮以后，即可根据你的需要，从多个Excel工作簿文件中提取B列的不重复编号，如下图所示。


扩展需求说明及处理方案

上面的代码是应对多个工作簿内各自删除重复值的，如果工作簿1和工作簿2中包含同样的重复值，不会被删除，这点需要注意。

如果你需要的是无论多个Excel工作簿文件内，还是多文件之间出现重复一律删除，并且按编号排序，看下面的sql代码。

从多文件多工作簿中动态提取出不重复值，一键更新结果\广州.xlsx].[员工表$]order by 编号


和之前代码有两点区别：

1、将union all换成union，作用是合并后也提取不重复值；

2、增加order by 编号，作用是按编号升序排列。

总结一下这种通用代码结构：

这样即可保证最终结果中不会出现任何重复值，即使不同工作簿文件之间的不重复值也会被删除。

这种sql查询法得到的结果，也是同样支持后期自动更新的，一键刷新的位置同样是在数据选项卡下的点击全部刷新。

因为结果报表是根据sql查询代码生成的，所以当工作要求变更时，如何快速更新代码呢？是不是要从头选择数据连接位置、选择文件位置、重写代码呢？

当然不必那么麻烦了，只要第一次写好了代码，后续仅需在结果表中编辑查询就可以修改sql代码了，操作如下图所示。


所以这种方法也算是一劳永逸的帮我们解决问题，而且方便后期的更新，而且还没有Excel版本的任何限制，适用于所有情况。

当然，我们也不好武断的断言，某某方法就是最好的，无论什么时候，建议大家都要根据实际情况，考虑需要处理的问题场景、数据源结构和后期更新要求，以及结合自己所掌握的技术和自身习惯等因素，选择最适合的方法。

其实到哪都是这句话，永远不变的就是一直在变，万能的永远不会是表格，而是操作表格的人，希望每个人都根据需要汲取到适合自己的营养。

更多经典的实战技能，已整理成超清视频的系统课程，方便你系统提升。


（点击图片可放大查看）

今天就先到这里吧，希望这篇文章能帮到你！更多干货文章加下方小助手查看。

欢迎点个在看，分享转发到朋友圈

干货教程 · 信息分享

欢迎扫码↓添加小助手进朋友圈查看

（点击蓝字可直接跳转）

进知识店铺>>更多精品课程

更多的Excel实战技术，我已经整理到Excel特训营中以超清视频演示并同步讲解，不但有具体场景，还讲解思路和方法，更有配套的课件下载和社群互动。

想系统学习的同学长按下图识别二维码。

按上图↑识别二维码，查看详情

请把这个公众号推荐给你的朋友：）

关注微信公众号（ExcelLiRui），每天有干货

关注后置顶公众号或设为星标

再也不用担心收不到干货文章了

关注后每天都可以收到Excel干货教程

请把这个公众号推荐给你的朋友

↓↓↓点击“阅读原文”进知识店铺

特别声明：以上内容(如有图片或视频亦包括在内)为自媒体平台“网易号”用户上传并发布，本平台仅提供信息存储服务。

数据-排序，不就行了？
由于题目中没有表格的数据截图，只好按我这边的举例设定了，如不符合要求的请：
我把文件传上来，请点击这回答右下角绿色的“点击下载”按钮，下载文件看看效果。
建议在添加辅助列，标注商品属于哪类，这样生鲜类的就比较好找出，计算总金额的时候，剔除生鲜类不算应该就可以了吧，不知道理解的对不对
假设这3列分别为A列，B列，C列，A1，A2，A3对应的行数分别为1,2,3
输入后生成C1对应的数字1，在将D1单元格的公式复制到D2，D3
2 在对应普瑞合格的单元格做这个公式=COUNTIF(E:E,普瑞合格)
3在对应普瑞不合格的单元格做这个公式=COUNTIF(E:E,普瑞不合格)
就这样，其他供应商的也这样做就行了。

> 如何提取excel 表格中包括某一特定供应商所对应的每个数据

我要回帖

更多关于 如何从数据表中取一部分数据 的文章

 

随机推荐