当我们需要从多个人那里收集相同格式的数据的时候,有很多小伙伴还在用比较原始的办法,比如使用共享工作簿、手工复制粘贴等,也有小伙伴比较先进一点,用VBA或SQL查询来实现。
这些方法或多或少都有一定的不便之处,比如:
共享工作簿在多人同时编辑时经常会造成Excel崩溃;
当人数较多时,手工复制粘贴太麻烦,而且当源数据有更改时要再调整汇总数据就不太方便;
VBA或SQL对很多人还是有点难度,不便于快速掌握。
今天就给大家介绍一种方法,只要大家按照格式将数据填到工作表中并放到指定的文件夹,数据收集者一刷新就可以汇总所有数据了。很神奇有木有?!下面给大家介绍一下操作方法。
总的来说,这是利用了Power Query查询文件夹的功能。
以下为示例数据,来自于多个文件的格式相同的销售预测数据。
数据格式如下。
点击【数据】选项卡下面的“获取和转换”组中的“新建查询”按钮,在弹出菜单中点击“从文件”-->“从文件夹”。
在弹出的对话框中点击“浏览”按钮,找到要合并的数据文件所在的文件夹。
Step 2、整理并获取数据
选择文件夹后,出现以下对话框,需要点击“编辑”按钮来进一步操作。
a. 点击“编辑”启动“查询编辑器”。
我们需要从Excel文件中查询数据,所以点击“Extension”列的下拉按钮,在里面选择“文本筛选器”、“包含”,设置筛选条件为包含“xls”这三个字符。
b. 接着选中第一列,也就是Content列,然后点击【开始】选项卡“管理列”组中的“删除列”下拉菜单中的“删除其他列”,这样就只保留了Content列。
如果需要保留原始文件名,则可以同时保留“Name”列。
c.点击“添加列”选项卡中的“添加自定义列”按钮。
在弹出的对话框的自定义列公式输入以下公式,然后点击“确定”按钮。
Excel.Workbook([Content],true)
这个公式一定要注意大小写!!!
d. 返回后,点击下图中的展开按钮,在弹出的对话框中点击“确定”按钮。
e. 在返回结果中点击“Custom.Data”列上的展开按钮,然后点击“确定”。这里我们可以取消勾选“使用原始列名作为前缀”,这样得到的结果的标题就是原始数据的标题了。
经过上面这些操作步骤,我们就获得了全部的数据。
如果一个工作簿中有多个工作表,那么所有工作表中的数据都会包含进来。
点击【开始】选项卡中的“关闭并上载”按钮,就可以在打开的工作簿中新建一个工作表,并返回数据。
最终结果如下。
这样做的好处是:
只要将文件添加到这个文件夹中,在汇总表上一刷新就可以将新文件的数据包含进来;
一个工作簿有多个工作表时,所有工作表的数据都会包含进来;
将文件移走时,再刷新汇总表,汇总数据也就跟着变化了;
当数据有更改时,轻松一刷新就可以得到最新的数据了。
--End--
联系客服