-
如何在多工作簿中查询_Excel多工作簿汇总求和,这个绝招你用了就戒不掉了
2020-12-03 22:57:50因为Excel天生对于处理多个工作簿的数据,是困难的是麻烦的。最新的版本增加了PowerQuery,这种难题得到了一定程度上的改观。今天我们不谈PQ,只谈怎么快速处理这样的多簿汇总。如果直接用函数去加和,比如跨簿使用...一个文件夹里有若干个工作簿文件,每个文件里数据表只有一个,结构相同。
现在要求把这些工作簿数据汇总到一个总表里,这种需求其实不简单。因为Excel天生对于处理多个工作簿的数据,是困难的是麻烦的。
最新的版本增加了PowerQuery,这种难题得到了一定程度上的改观。今天我们不谈PQ,只谈怎么快速处理这样的多簿汇总。
如果直接用函数去加和,比如跨簿使用多个SUMIF相加,或者使用什么INDIRECT等函数,你会发现好多函数跨簿都出些问题,好多都要求数据源的工作簿必须处于打开状态,函数汇总的结果才会更新,否则经常会报错。
这些都是函数设计之初的问题,或者说函数在不打开源头工作簿的情况下,就不能有效地及时地更新数据过来。那么怎么办呢?
今天提供一种方法
1、首先新建一个汇总工作簿,里面建好各个城市的工作表
2、然后通过,数据,现有连接,浏览更多,找到存放多个工作簿的文件夹,然后逐一选择需要导入的某城市工作簿文件,打开
导入后的效果
3、然后按上面步骤导入北京的数据,深圳的数据等等
有5个表,就要导入数据5次,这个过程省不了快不了
4、然后再在当前工作簿里新建一个“城市汇总”表,数据,现有连接,选择自身这个工作簿文件
select '上海' as 城市,* from [上海$] union all select '北京' as 城市,* from [北京$] union all select '深圳' as 城市,* from [深圳$] union all select '广州' as 城市,* from [广州$] union all select '杭州' as 城市,* from [杭州$]
5、然后把相关字段拖拽到透视表的筛选、行、列和值区域里,并对日期字段进行年月季度的组合。
具体细节不解释了,效果如下:
这绝对是非常高级的一种多簿汇总方法之一,期待您的尝试。
来源:Excel和PPT职场见
-
sqlsum多个字段求和_如何利用SQL语句对多个工作表做数据透视分析表
2021-01-25 14:00:32我们在平时工作时常常会遇到要对一个工作簿的一个或多个sheet(工作表)进行添加数据透视表分析(例如对一个车间不同岗位的培训信息进行统计),对于单个sheet的数据透视表分析相信大家都不陌生了。这里为大家介绍一种...我们在平时工作时常常会遇到要对一个工作簿的一个或多个sheet(工作表)进行添加数据透视表分析(例如对一个车间不同岗位的培训信息进行统计),对于单个sheet的数据透视表分析相信大家都不陌生了。这里为大家介绍一种可以利用数据透视表来对多个sheet进行分析的方法。
在我们平时使用数据透视表时,往往是直接在数据源工作表中直接点击“插入”菜单下的插入“数据透视表”,这种方法只能选择一个工作表的数据进行分析
这种方法不能满足同时分析多个数据表的内容。
当然也可以利用数据透视表向导(得先在“选项”→“自定义功能区”里面添加这个命令)进行合并数据区域后添加数据透视表,但这种办法会使需要的字段丢失。所以下面就教大家一种能够同时分析多个数据透视表数据的方法:
这种方法是把数据源工作表和数据透视表分别放在两个独立的工作簿中(这样可以提高数据源工作表的独立性,防止干扰,同时保证建立“连接”时数据源工作簿不受干扰),然后采用“连接”的方法将数据源工作表引用到数据透视表中进行分析,每次更新数据后直接在数据透视表中刷新即可。下面是具体步骤:
1、把数据源工作表(这里是“01提钒一车间安全培训记录表”)放在固定的一个文件夹目录下(方便以后更新数据),新建一个需要放置数据透视表的Excel表格,这里命名为“数据透视表”,放在指定的位置(为了方便都放置在了“示例文件”下),确保数据源工作表处于未被占用状态(未被打开,未被打开,未被打开,重要的事情说三遍)。
2、打开“数据透视表”工作簿中点击插入菜单栏下的插入数据透视表
选择“使用外部数据源”,点击“选择连接”
点击“浏览更多”,找到数据源工作表,点击打开,在要分析的工作表中选择一个,点击确定
选择透视表的位置,这里放置于本工作表的一个位置。点击确定,此时就会得到一个数据透视表(数据源外部的连接)。
3、点击数据透视表区域,菜单栏出现“数据透视表工具”,选择选项菜单栏下的“更改数据源”,“连接属性”
在出现的页面中选择“定义”,并在命令文本下输入神秘代码并点击确定:
select * from [提钒一车间$a2:o] union all
select * from [钒渣预处理工序$a2:o] union all
select * from [配料焙烧工序$a2:o] union all
select * from [浸出净化工序$a2:o] union all
select * from [沉淀洗涤工序$a2:o] union all
select * from [脱氨熔化工序$a2:o] union all
select * from [污水污泥处理工序$a2:o]
代码解释:此SQL语句为工作表连接语句,以第一句为例,“提钒一车间$”为数据源工作簿中要分析的工作表名称,“a2:o”为选择的工作表区域(选择的区域要注意不能包含合并单元格,且标题不要含有空值),这里需要注意的是连接的工作表区域格式要一致,例如,一个工作表统计的为“姓名”、“单位”和“培训老师”的资料,而连接的另一个工作表统计的为“姓名”、“单位”和“培训时间”,此时便会产生数据混乱,不能进行数据透视;其次,SQL语句格式要严格参照示例要求,一定要用英文的状态下符号,嫌麻烦的可以直接复制后再更改即可。
4、对数据透视表的字段进行组合即可产生所需的透视表。
这里可以对在设计中对透视表布局进行更改,这里选择“以表格形式显示”
然后我们需要对培训学时进行求和,在字段列表右下角的“值”字段中,点击 “计数项:培训学时”的下拉三角
进入“值字段设置”,在里面选择计算类型为“求和”即可
可以看到培训学时已经从计数项变为了求和项
到这里就完成了对于多个sheet的数据透视表分析。当数据源内容更改后,保存并关闭数据源工作簿,打开数据透视表所在的工作表刷新数据即可。
5、注意事项:如果选取的数据源其中有空值,那么在创建数据透视表的时候,只会以计数项来而不是求和项,如果想变为求和项,那就需要更改数据源。另外在建立透视表时一定要把数据源工作簿关闭,以保证添加连接时可以访问。
-
如何统计多个Excel文件中的信息,统计多个工作簿中的数据,统计多个表格中相同的条,统计多个表格的和等等
2019-12-31 16:52:22做为信息收集人员,可能经常需要统计多个Excel文件收集来的信息,对多个Excel、对多个工作簿、对多个表格进行统计,统计的维度包含按文件名、按表名进行求和、求排名、求平均值、条件统计、条件求和、文字汇总、多...做为信息收集人员,可能经常需要统计多个Excel文件收集来的信息,对多个Excel、对多个工作簿、对多个表格进行统计,统计的维度包含按文件名、按表名进行求和、求排名、求平均值、条件统计、条件求和、文字汇总、多条件统计等等,这往往比较费劲,工作量大,易出错,现在有一个方便的统计工具可以按照你需要的汉字描述完成统计。已下面为例:
例如我们有三个文件( 一个是2018年、一个是2019年、一个是2020年),文件中每个月的销售情况是一个Sheet。
工具地址:www.excelfb.com
点击:
汇总工具
一、现在我要汇总每年 、每个月,每个产品的销售情况。
操作如下图:
二、汇总每个文件的信息行数
三、汇总每个月的销售情况
四、汇总2019及以后的销售情况----多文件条件统计
-
把一个工作表按行拆分为若干张工作表,并以行首单元格命名(同一个工作簿内),会计也要写代码...
2020-12-11 08:53:38分久必合,合久必分。excel的使用中,使用频率最高而且让我们最头疼的操作不是合并就是拆分,不是拆分就是合并。...Indirect函数第三讲:多表合并但是,要论快速和省事,当然莫属使用VBA代码了,一键就可以完成...分久必合,合久必分。
excel的使用中,使用频率最高而且让我们最头疼的操作不是合并就是拆分,不是拆分就是合并。刘老师收到的频率最高的提问不是合并就是拆分,不是拆分就是合并,本公众号陆续介绍过使用函数或菜单来合并的方法,比如:
<75>利用Query进行多表合并
<122>Indirect函数第三讲:多表合并
但是,要论快速和省事,当然莫属使用VBA代码了,一键就可以完成合并和拆分,接下来几周,我打算做个拆分和合并的VBA代码集合:
拆分文件(蓝色字体为链接,直接点击可以跳转文章)
1、把一个工作簿中的多张工作表快速拆分成多个工作簿,并以工作表名命名
2、把工作表中的每一列拆分成单个工作簿,并以表头命名
3、把一个工作表按筛选结果拆分为若干张工作表(同一个工作簿内)
4、把若干年资产负债表按年拆分为若干张工作表(同一个工作簿内)
5、把一个工作表按行拆分为若干张工作表,并以行首单元格命名(同一个工作簿内)
合并文件:
1、同一个文件夹内的若干个工作簿合并到一个工作簿(放在若干个工作表内)
2、同一个工作簿内的若干张工作表合并到一个工作表内(上下结构),比如3个车间工人的3张工资表合并成1张工资表。
3、同一个工作簿内的若干张工作表合并到一个工作表内(左右结构),比如,放在若干张工作表内的若干年资产负债表合并成1张汇总表。
今天发布的视频和代码是:把一个工作表按行拆分为若干张工作表,并以行首单元格命名(同一个工作簿内)
代码如下,复制粘贴到模块里就可以了。
Sub 按行拆分工作表() a = 0 Application.ScreenUpdating = False For j = 1 To 10 Step 1 a = a + 1 Sheets.Add after:=Sheets(Sheets.Count) Sheets(Sheets.Count).Name = Sheets(1).Range("a" & j + 1) Sheets(1).Range("a" & 1 & ":f" & 1).Copy Sheets(Sheets.Count).Range("a" & 1 & ":f" & 1) Sheets(1).Range("a" & j + 1 & ":f" & j + 1).Copy Sheets(Sheets.Count).Range("a" & 2 & ":f" & 2) Application.ScreenUpdating = False Next j msgbox"拆分完成"End Sub
有任何疑问,请带着excel文件来提问。泛泛提问,可能得不到有效回答。
关于提问
请点击链接
如何有效的提问
点击蓝色字体 回顾热门文章
被教材坑过的那些年——论股票的估值(上)
被教材坑过的那些年——论股票的估值(下)
毕业季——论文排版讲座视频汇总
信用卡分期还款引发的讨论
你的房贷每月还款额是这样计算出来的(等额年金法)
你的房贷每月还款额是这样计算出来的(等额本金法)
使用数据透视表在汇总表中按要求进行筛选
买保险的,卖保险的,都要进来看一看
来,来,来,我教你怎样向会计系女生表白
你还在使用收费的抽奖平台或抽奖软件吗?那真是弱爆了!使用Excel也可以抽奖喔!
另一种思维方式——论资产的价值倒底几何
1秒钟核对两个表格的异同——使用突出显示单元格规则
开奖啦!开奖啦!开奖啦!
在公众号中发送“目录”可获取所有教学视频目录
在公众中中发送“论文”可获取毕业论文排版讲座视频
-
wps合并重复项并求和_WPS应用技巧—如何将多个工作表合并计算
2021-01-01 03:32:01我们在利用WPS表格和Excel表格进行日常办公时,经常需要添加多个工作簿,那么我们在单个工作簿当中的求和已经非常熟悉了,如果需要两个甚至多个工作簿当中的数据进行合并计算,该如何去操作呢?今天我们就来教大家... -
Excel-同个工作簿中,多个工作表中有格式一致的表格,做汇总透视合并信息
2019-05-18 11:39:0012、同个工作簿中,多个工作表中有格式一致的表格,做汇总透视合并信息 在工作簿中新建一个工作表-汇总表-》按Alt+D+P调出数据透视表和数据透视图向导-》选择“多重合并计算数据区域”,然后依次添加每个工作表想要... -
不同表_Excel办公技巧:运用合并计算功能对不同工作表数据进行合并求和
2021-01-07 15:24:58上一篇文章讲解了如何运用power query快速合并同一工作簿的多个工作表,不过power query合并后的数据是没有经过求和等数学运算的,这篇文章我们学习运用合并计算功能对不同工作表数据进行合并求和。应用场景运用合并... -
n平方的求和公式_必会的多条件求和
2020-12-15 13:11:05简单的求和几乎人人都会,然而工作中往往会遇到有条件的求和,有的是一个条件,有的是多个条件,所以要想工作做的好做的快还需要学会单条件求和、多条件求和、多工作表求和、多工作簿求和。计算 第27~30周各公司的... -
c++单链表 一元多项式求和_Excel多工作表引用,Indirect函数才是NO.1,多表操作这两组函数公式你应会...
2020-12-28 01:53:59相信大家在进行数据汇总的时候,对一份工作簿中的多个工作表数据进行多表汇总,相信这个问题绝大多数人都碰到过。对于多工作表数据的引用,许多同学还是不知道如何操作。如上图所示,表格中有1-5月5个月的工资数据,... -
数据合并设计_【操作技巧】Excel多工作表合并汇总
2021-01-14 13:55:56工作中常常会将不同类别的数据放在不同的工作表或工作簿中,然后需要将这些数据汇总到一个表中。怎样实现多表求和呢?历史文章中写到公式法汇总求和,本例使用技巧的方式实现,方法千万条,好用第一条,给大家提供... -
从一个汇总表里查找另一张表的数据_【赠书】不打开工作簿也能批量合并不同文件夹下多个表格的数据...
2021-01-27 09:49:47最近推送的五篇文章:《偷懒的技术:打造财务Excel达人》到底有...数字格式转换方法汇总打开工作簿后,表格格式全丢失了,菩萨啊,救救我· 正 · 文 · 来 · 啦 ·大成方略纳部人俱乐部正在搞赠书活动,赠送《偷懒... -
asp点击按钮sql列求和_从Excel到SQL:效率增倍,错误减半!
2020-11-18 10:58:12Excel最多可以处理100万行数据,但当你处理大型数据集或进行繁重的分析时,例如,将公式应用于一组单元格、链接多个电子表格或连接到其他工作簿时,在未超出Excel处理的最大行数之前,其操作速度... -
excel两个函数实现日报表自动统计
2020-09-09 20:23:39在同一个工作簿的话vlookup也可以用,但想实现基础表和日报表分离的话,就只能用lookup函数。 月数据和年数据用到同一个函数sumproduct,这是一个数组函数,可以多条件求和。月度汇总公式:SUMPRODUCT((时间>=... -
EXCEL 2007 宝典 附光盘文件
2010-04-02 14:43:05打开这个工作簿时,会看见有关循环引用的信息。 worksheet functions.xlsx:包含一个列举和描述了每个工作表函数的表的工作簿。 table formulas.xlsx:用于演示表内公式的使用的工作簿 Chapter 12 character set.... -
Excel新增工具集
2011-12-20 09:30:277、多个工作簿中的所有工作表或指定工作表相同位置(区域)求和:打开(复制)一个空白的汇总表,表格格式与要汇总的表相同,凡鼠标选定单元格连续或不连续区域将对所有表格做累加计算。 8、当前工作表中每行记录都... -
电子表格Excel使用技巧163种使用技巧大全
2019-03-16 10:41:3811. 一次性打开多个工作簿 11 12. 快速切换工作簿 13 13. 选定超级链接文本(微软OFFICE技巧大赛获奖作品) 13 14. 快速查找 14 15. 修改默认文件保存路径 14 16. 指定打开的文件夹 15 17. 在多个EXCEL工作簿间快速... -
Excel 缺点
2019-07-17 08:38:20在多个工作簿、工作表中的操作相互干扰。例如在a中删除操作,b中输入操作。在a中撤销必须先清除b中的操作。 2. Excle经常出现空白列。例如,从A表复制一列到一个新表B中,本来表A只有5行,可到了B表中,...惨不忍睹... -
Excel使用技巧集锦—163种使用技巧大全(超全).doc
2015-05-10 08:08:0711. 一次性打开多个工作簿 11 12. 快速切换工作簿 13 13. 选定超级链接文本(微软OFFICE技巧大赛获奖作品) 13 14. 快速查找 14 15. 修改默认文件保存路径 14 16. 指定打开的文件夹 15 17. 在多个EXCEL工作簿间快速... -
Excel技巧大全
2015-05-06 07:21:3711. 一次性打开多个工作簿 9 12. 快速切换工作簿 9 13. 选定超级链接文本(微软Office技巧大赛获奖作品) 10 14. 快速查找 10 15. 修改默认文件保存路径 10 16. 指定打开的文件夹 10 17. 在多个Excel工作簿间快速... -
excel函数与公式---第五篇 实例作证篇
2020-11-16 11:46:49技巧267 打造单工作簿多表单台账系统 技巧268 根据资源曲线分配资源数量 技巧269 制作计划与实际进度对比甘特图 技巧270 现金日记账与现金日报表的设计 技巧271-1 方格网土方简易计算表 技巧271-2 方格网土方简易... -
Excel使用技巧大全(超全).doc
2011-07-19 19:13:4011. 一次性打开多个工作簿 9 12. 快速切换工作簿 9 13. 选定超级链接文本(微软OFFICE技巧大赛获奖作品) 10 14. 快速查找 10 15. 修改默认文件保存路径 10 16. 指定打开的文件夹 10 17. 在多个EXCEL工作簿间快速... -
Excel百宝箱8.0
2011-06-07 21:32:17【工作表折分】:将当前工作表的数据按条件拆分成多个工作表,可以用任意列的数据做为拆分条件 【合并工作簿】:将指文件夹中所有工作簿中所有工作表数据合并起来。有两种合并方式:将每个工作簿中的工作表合到当前... -
通用报表合并汇总v3.0
2005-12-02 10:36:26可同时对选定的任意多个工作表进行合并和汇总,既可以对固定行数的表格进行合并,也可以对行数不固定的表格进行合并(表格的列数必须固定)。基于以上功能,你可轻松构建报表汇总系统,能达到应用程序级的效果,能极... -
Excel百宝箱9.0无限制破解版.rar
2012-09-05 09:31:51【工作表折分】:将当前工作表的数据按条件拆分成多个工作表,可以用任意列的数据做为拆分条件 【合并工作簿】:将指文件夹中所有工作簿中所有工作表数据合并起来。有两种合并方式:将每个工作簿中的工作表合到... -
Excel百宝箱 9.0 破解版 批量导入图片等200种功能
2013-05-11 22:46:24【工作表折分】将当前工作表的数据按条件拆分成多个工作表,可以用任意列的数据做为拆分条件 【合并工作簿】将指文件夹中所有工作簿中所有工作表数据合并起来。有两种合并方式 【文本与数值互换】将选区的数字瞬间... -
VBA常用技巧
2014-12-21 16:39:28技巧51 不打开工作簿取得其他工作簿数据 12 51-1 使用公式 12 51-2 使用GetObject函数 12 51-3 隐藏Application对象 12 51-4 使用ExecuteExcel4Macro方法 12 51-5 使用SQL连接 12 技巧52 返回窗口的可视区域地址 12 ... -
VBA编程技巧大全
2013-08-05 09:03:19技巧51 不打开工作簿取得其他工作簿数据 121 51-1 使用公式 121 51-2 使用GetObject函数 122 51-3 隐藏Application对象 123 51-4 使用ExecuteExcel4Macro方法 124 51-5 使用SQL连接 125 技巧52 返回窗口的可视区域... -
中文版Excel.2007公式与函数应用宝典 1/2
2012-04-06 18:29:441.3 使用工作簿 1.3.1 工作表 1.3.2 图表 1.3.3 宏表和对话表 1.4 Excel用户界面 1.4.1 新的用户界面 1.4.2 Ribbon 1.4.3 0ffice按钮菜单 1.4.4 快捷菜单和微型工具栏 1.4.5 快速访问工具栏 1.4.6 智能...