|
在日常工作中我们需要将下属公司或其他部门打包提交的表格或报表汇总到一个表格，如果一个个去打开复制，是低效又痛苦的，那如何才能快速将所有表格汇总到一个表格里呢？我们以之前在《“偷懒”的技术：打造财务Excel达人》读者群给大家做的“练习题019：汇总各公司报表”为例，介绍如何快速汇总多个文件夹下的表格。 题目要求 假定各公司上交的报表名称均为“XX公司月报表”，报表内工作表名称和表格布局相同（如果下属公司报上来的工作簿或表格名称、表格格式五花八门，这需要集团公司对下属公司的信息上报制度进行规范）。集团报表汇总人员收集到各公司的报表分别放在以各公司命名的文件夹里，这些文件夹均放在名称为“3月”的文件夹内。 各公司的报表如下， 要求将各公司报表汇总成下面的表格样式
解题思路： 如果各公司每月都要准时提交报表，并且各公司报表都汇总收集在同一文件夹下，加上工作表名称以及工作表格式都相同，我们可以应用《“偷懒”的技术：打造财务Excel达人》第二章第三节“坐享其成：报表翻新的偷懒妙招”的相关技巧，进行报表翻新。但是，本题不符合那种情况，该如何办呢？ 我们可以想办法将各文件夹的工作簿转移至同一文件夹下，然后提取各工作簿的名称，使用Indirect函数引用各公司的报表，或使用查找替换批量翻新公式。因而，这个问题可以转换为下面三个问题 1、如何批量将各文件夹的文件转移至同一文件夹 2、如何提取某文件夹下各工作簿的名称 3、如何批量引用已知路径工作簿中某工作表单元格的数据 下面逐一介绍： 一、如何批量将各文件夹下的文件转移至同一文件夹 可以使用查找+剪切，将其转移至同一文件夹。详见昨天在“Excel偷懒的技术”公众号发布的文章。地址 http://mp.weixin.qq.com/s?src=3×tamp=1476625965&ver=1&signature=UmZtAL2k3z5AzYNa85b2-G7cjbcC*h9wB50*Omeg-QFheSFghuf-YlaiXHmgQEn8Ue6zXAB*GfRlm0Lo8i446u6YWqTTzItf3RdoISEYsEfxH1hTSp-rMxzUNLkOMABfWlsO8ygeIfMF8AxxEPJQvEKvlsiY6-0i-418Cp0MfyA= 二、如何提取某文件夹下各工作簿的名称 根据步骤一，已经将各公司报表转移至E盘“我的桌面”文件夹下，假设各公司的报表均为2007格式，即文件后缀名为“.xlsx”，我们在Excel公式选项卡下点击的“定义名称”按钮，在弹出的新建名称对话框，按下图新增自定义名称“文件列表” 自定义名称的公式为： =FILES('E:\我的桌面\汇总各公司报表\*.xlsx') 然后在某空白列的第一行输入下面的公式，然后下拉填充至其他行： =INDEX(文件列表,ROW())
然后使用查找替换，在查找栏输入“月报表.xlsx'，替换栏什么都不输入，点击“全部替换”即可将“月报表.xlsx'批量删除，仅保留各公司的名称。 全部替换后效果如下图： 三、如何批量引用已知路径下工作簿中某工作表单元格的数据 我们使用简单的链接公式引用已经移至3月文件下A公司和B公司的报表，其公式如下
='E:\我的桌面\3月\[A公司月报表.xlsx]费用表'!B4 C4单元格的公式： ='E:\我的桌面\3月\[B公司月报表.xlsx]费用表'!B4 二者不同之处就是公司的名称，而这正是第三行相应单元格的内容。 下面有二个方法引用各工作簿的数据。 方法一：使用Indirect函数 由于前述特点，因而可以使用Indirect函数，将工作表名称，已经引用的行号使用变量代替，B4公式如下： =INDIRECT(''E:\我的桌面\3月\['&B$3&'月报表.xlsx]费用表'!B'&ROW()) 然后往下拖动填充填充，再将B4：B13往右拖动填充。 此时公式计算结果为错误值“#REF!”，这是因为： Indirect函数引用其他工作簿时，需要其他工作簿处于打开状态，如果未打开，则会出错。 因而选定所有工作簿，将其批量打开。公式就会得出正确的计算结果。 这种方法的优点是用一个公式即可搞定所有引用。 注： 批量打开所有工作簿方法： 使用Shift或Ctrl键，选定需要打开的工作簿，敲击回车键。 批量关闭工作簿的方法： 本文经授权 尊重原创
文章来源：Excel偷懒的技术 作者：龙逸凡，图书《“偷懒”的技术：打造财务Excel达人》作者 |
|
|
来自： 许丽49tvl963l9 > 《44周》
