精华内容
下载资源
问答
  • 因为Excel天生对于处理多个工作簿数据,是困难的是麻烦的。最新的版本增加了PowerQuery,这种难题得到了一定程度上的改观。今天我们不谈PQ,只谈怎么快速处理这样的多簿汇总。如果直接用函数去加和,比如跨簿使用...

    一个文件夹里有若干个工作簿文件,每个文件里数据表只有一个,结构相同。

    fc0850f07b7f65692a7365b46216655c.png

    现在要求把这些工作簿数据汇总到一个总表里,这种需求其实不简单。因为Excel天生对于处理多个工作簿的数据,是困难的是麻烦的。

    最新的版本增加了PowerQuery,这种难题得到了一定程度上的改观。今天我们不谈PQ,只谈怎么快速处理这样的多簿汇总。

    916f8cfa5937f8bbf87bee1408c3e9b5.png

    如果直接用函数去加和,比如跨簿使用多个SUMIF相加,或者使用什么INDIRECT等函数,你会发现好多函数跨簿都出些问题,好多都要求数据源的工作簿必须处于打开状态,函数汇总的结果才会更新,否则经常会报错。

    这些都是函数设计之初的问题,或者说函数在不打开源头工作簿的情况下,就不能有效地及时地更新数据过来。那么怎么办呢?

    今天提供一种方法

    1、首先新建一个汇总工作簿,里面建好各个城市的工作表

    53d3d0d9f2efb2513ecde00fdae9de75.png

    2、然后通过,数据,现有连接,浏览更多,找到存放多个工作簿的文件夹,然后逐一选择需要导入的某城市工作簿文件,打开

    b0312d2652b80a7d95996e756ba57ed8.png
    43aa8e6c57c2b740558270ccb2406aaf.png
    7adeb88bc15bcf8af130f7b43ad3248c.png
    f1d6a620f397777c05b0d88b71011a96.png
    d578d850e2831a4344818e3d45c335bf.png

    导入后的效果

    3b51b3503440dedd420ccb39b7b1cd6b.png

    3、然后按上面步骤导入北京的数据,深圳的数据等等

    86ee687c696d7f91a8417aa8521c020c.png

    有5个表,就要导入数据5次,这个过程省不了快不了

    4、然后再在当前工作簿里新建一个“城市汇总”表,数据,现有连接,选择自身这个工作簿文件

    4914615e9a94bc5f7a04e2fb5b71a7d1.png
    c321527ee68dd2cc86e8c3685ef79e57.png
    2dd16892c2b9402b887a52474cea2ebc.png
    8b7c0fe7165e3ffbb005f40441aed834.png

    select '上海' as 城市,* from [上海$] union all select '北京' as 城市,* from [北京$] union all select '深圳' as 城市,* from [深圳$] union all select '广州' as 城市,* from [广州$] union all select '杭州' as 城市,* from [杭州$]

    15b97f9f74510886197feb9ff0413456.png

    5、然后把相关字段拖拽到透视表的筛选、行、列和值区域里,并对日期字段进行年月季度的组合。

    具体细节不解释了,效果如下:

    5b46e1189a1e7da14a51cb67a41fda13.png

    这绝对是非常高级的一种多簿汇总方法之一,期待您的尝试。

    来源:Excel和PPT职场见

    展开全文
  • 我们在平时工作时常常会遇到要对一个工作簿的一个或多个sheet(工作表)进行添加数据透视表分析(例如对一个车间不同岗位的培训信息进行统计),对于单个sheet的数据透视表分析相信大家都不陌生了。这里为大家介绍一种...

    我们在平时工作时常常会遇到要对一个工作簿的一个或多个sheet(工作表)进行添加数据透视表分析(例如对一个车间不同岗位的培训信息进行统计),对于单个sheet的数据透视表分析相信大家都不陌生了。这里为大家介绍一种可以利用数据透视表来对多个sheet进行分析的方法。

    4643750b16f8e824fe3bd1114783f666.png

    在我们平时使用数据透视表时,往往是直接在数据源工作表中直接点击“插入”菜单下的插入“数据透视表”,这种方法只能选择一个工作表的数据进行分析

    6586f4d83f90b60e2dcbf623e1956134.png

    这种方法不能满足同时分析多个数据表的内容。

    当然也可以利用数据透视表向导(得先在“选项”→“自定义功能区”里面添加这个命令)进行合并数据区域后添加数据透视表,但这种办法会使需要的字段丢失。所以下面就教大家一种能够同时分析多个数据透视表数据的方法:

    这种方法是把数据源工作表和数据透视表分别放在两个独立的工作簿中(这样可以提高数据源工作表的独立性,防止干扰,同时保证建立“连接”时数据源工作簿不受干扰),然后采用“连接”的方法将数据源工作表引用到数据透视表中进行分析,每次更新数据后直接在数据透视表中刷新即可。下面是具体步骤:

    1、把数据源工作表(这里是“01提钒一车间安全培训记录表”)放在固定的一个文件夹目录下(方便以后更新数据),新建一个需要放置数据透视表的Excel表格,这里命名为“数据透视表”,放在指定的位置(为了方便都放置在了“示例文件”下),确保数据源工作表处于未被占用状态(未被打开,未被打开,未被打开,重要的事情说三遍)。

    3b26915971d034e6de9a767563a9e715.png

    2、打开“数据透视表”工作簿中点击插入菜单栏下的插入数据透视表

    33da6397395bd9cfd5c38ff2e198da3d.png

    选择“使用外部数据源”,点击“选择连接”

    1b28547b262f8d9b424ccfd3a79e8e32.png

    点击“浏览更多”,找到数据源工作表,点击打开,在要分析的工作表中选择一个,点击确定

    3f2465c5afc392c0a31dd4c4fb92be8d.png
    7c80ad7b695e2fecd548bde05463238d.png

    选择透视表的位置,这里放置于本工作表的一个位置。点击确定,此时就会得到一个数据透视表(数据源外部的连接)。

    206b33ff08905cf6fc0a3e08ea0b005e.png
    27c75e4a5c58d3fab3d55843d402db4a.png

    3、点击数据透视表区域,菜单栏出现“数据透视表工具”,选择选项菜单栏下的“更改数据源”,“连接属性”

    0064ed44fe8b62de6a92b10608adbaa6.png

    在出现的页面中选择“定义”,并在命令文本下输入神秘代码并点击确定:

    bfd7a7d5daa041e6ab7ac2f5b1776915.png
    c36b8463cc2cc683141a9e5312325d54.png

    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、对数据透视表的字段进行组合即可产生所需的透视表。

    a2498d9fe5214e365888369fd5dc345e.png

    这里可以对在设计中对透视表布局进行更改,这里选择“以表格形式显示”

    6c6c7ee7ed91e8628d13ff473e06ed2e.png

    然后我们需要对培训学时进行求和,在字段列表右下角的“值”字段中,点击 “计数项:培训学时”的下拉三角

    210e68773507dec64020afaf61b41a9c.png

    进入“值字段设置”,在里面选择计算类型为“求和”即可

    b45187f212c790ae8b209964343107ca.png

    可以看到培训学时已经从计数项变为了求和项

    78ec1a3f55d16eed6de7ff63f696a564.png

    到这里就完成了对于多个sheet的数据透视表分析。当数据源内容更改后,保存并关闭数据源工作簿,打开数据透视表所在的工作表刷新数据即可。

    5、注意事项:如果选取的数据源其中有空值,那么在创建数据透视表的时候,只会以计数项来而不是求和项,如果想变为求和项,那就需要更改数据源。另外在建立透视表时一定要把数据源工作簿关闭,以保证添加连接时可以访问。

    展开全文
  • 做为信息收集人员,可能经常需要统计多个Excel文件收集来的信息,对多个Excel、对多个工作簿、对多个表格进行统计,统计的维度包含按文件名、按表名进行求和、求排名、求平均值、条件统计、条件求和、文字汇总、多...

         做为信息收集人员,可能经常需要统计多个Excel文件收集来的信息,对多个Excel、对多个工作簿、对多个表格进行统计,统计的维度包含按文件名、按表名进行求和、求排名、求平均值、条件统计、条件求和、文字汇总、多条件统计等等,这往往比较费劲,工作量大,易出错,现在有一个方便的统计工具可以按照你需要的汉字描述完成统计。已下面为例:

      例如我们有三个文件( 一个是2018年、一个是2019年、一个是2020年),文件中每个月的销售情况是一个Sheet。

     

      工具地址:www.excelfb.com

    点击:

    汇总工具

    Excel自动汇总工具(根据汉字描述的要求格式进行汇总)

     

    一、现在我要汇总每年 、每个月,每个产品的销售情况。

        操作如下图:

        

    二、汇总每个文件的信息行数

    三、汇总每个月的销售情况

    四、汇总2019及以后的销售情况----多文件条件统计

     

     

      

      

    展开全文
  • 分久必合,合久必分。excel的使用中,使用频率最高而且让我们最头疼的操作不是合并就是拆分,不是拆分就是合并。...Indirect函数第三讲:表合并但是,要论快速和省事,当然莫属使用VBA代码了,一键就可以完成...

    98253457423754883e1f9d6453240baa.gif

    分久必合,合久必分。

    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文件来提问。泛泛提问,可能得不到有效回答。

    关于提问b87720609588992c13c583009bcd38c6.png请点击链接

    如何有效的提问

    06f8fc2dc6710adc7042b553a63f9482.gif

    点击蓝色字体    回顾热门文章 

    被教材坑过的那些年——论股票的估值(上)

    被教材坑过的那些年——论股票的估值(下)

    毕业季——论文排版讲座视频汇总

    信用卡分期还款引发的讨论 

    你的房贷每月还款额是这样计算出来的(等额年金法)

    你的房贷每月还款额是这样计算出来的(等额本金法)

    使用数据透视表在汇总表中按要求进行筛选

    买保险的,卖保险的,都要进来看一看

    来,来,来,我教你怎样向会计系女生表白

    你还在使用收费的抽奖平台或抽奖软件吗?那真是弱爆了!使用Excel也可以抽奖喔!

    另一种思维方式——论资产的价值倒底几何

    1秒钟核对两个表格的异同——使用突出显示单元格规则

    开奖啦!开奖啦!开奖啦!

    在公众号中发送“目录”可获取所有教学视频目录

    在公众中中发送“论文”可获取毕业论文排版讲座视频

    6530f2629d4e324def376fa8a6db91a1.png

    6f2e35987d3cb409258f42b246a46275.png

    26ed89898aad67be1c361e1ffc9294b0.gif

    展开全文
  • 我们在利用WPS表格和Excel表格进行日常办公时,经常需要添加多个工作簿,那么我们在单个工作簿当中的求和已经非常熟悉了,如果需要两个甚至多个工作簿当中的数据进行合并计算,该如何去操作呢?今天我们就来教大家...
  • 12、同个工作簿中,多个工作表中有格式一致的表格,做汇总透视合并信息 在工作簿中新建一个工作表-汇总表-》按Alt+D+P调出数据透视表和数据透视图向导-》选择“多重合并计算数据区域”,然后依次添加每个工作表想要...
  • 上一篇文章讲解了如何运用power query快速合并同一工作簿多个工作表,不过power query合并后的数据是没有经过求和等数学运算的,这篇文章我们学习运用合并计算功能对不同工作表数据进行合并求和。应用场景运用合并...
  • 简单的求和几乎人人都会,然而工作中往往会遇到有条件的求和,有的是一个条件,有的是多个条件,所以要想工作做的好做的快还需要学会单条件求和、多条件求和、多工作表求和、多工作簿求和。计算 第27~30周各公司的...
  • 相信大家在进行数据汇总的时候,对一份工作簿中的多个工作数据进行多表汇总,相信这个问题绝大多数人都碰到过。对于多工作表数据的引用,许多同学还是不知道如何操作。如上图所示,表格中有1-5月5个月的工资数据,...
  • 工作中常常会将不同类别的数据放在不同的工作表或工作簿中,然后需要将这些数据汇总到一表中。怎样实现求和呢?历史文章中写到公式法汇总求和,本例使用技巧的方式实现,方法千万条,好用第一条,给大家提供...
  • 最近推送的五篇文章:《偷懒的技术:打造财务Excel达人》到底有...数字格式转换方法汇总打开工作簿后,表格格式全丢失了,菩萨啊,救救我· 正 · 文 · 来 · 啦 ·大成方略纳部人俱乐部正在搞赠书活动,赠送《偷懒...
  • Excel最多可以处理100万行数据,但当你处理大型数据集或进行繁重的分析时,例如,将公式应用于一组单元格、链接多个电子表格或连接到其他工作簿时,在未超出Excel处理的最大行数之前,其操作速度...
  • 在同一个工作簿的话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:27
    7、多个工作簿中的所有工作表或指定工作表相同位置(区域)求和:打开(复制)一个空白的汇总表,表格格式与要汇总的表相同,凡鼠标选定单元格连续或不连续区域将对所有表格做累加计算。 8、当前工作表中每行记录都...
  • 11. 一次性打开多个工作簿 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表中,...惨不忍睹...
  • 11. 一次性打开多个工作簿 11 12. 快速切换工作簿 13 13. 选定超级链接文本(微软OFFICE技巧大赛获奖作品) 13 14. 快速查找 14 15. 修改默认文件保存路径 14 16. 指定打开的文件夹 15 17. 在多个EXCEL工作簿间快速...
  • Excel技巧大全

    2015-05-06 07:21:37
    11. 一次性打开多个工作簿 9 12. 快速切换工作簿 9 13. 选定超级链接文本(微软Office技巧大赛获奖作品) 10 14. 快速查找 10 15. 修改默认文件保存路径 10 16. 指定打开的文件夹 10 17. 在多个Excel工作簿间快速...
  • 技巧267 打造单工作簿多表单台账系统 技巧268 根据资源曲线分配资源数量 技巧269 制作计划与实际进度对比甘特图 技巧270 现金日记账与现金日报表的设计 技巧271-1 方格网土方简易计算表 技巧271-2 方格网土方简易...
  • 11. 一次性打开多个工作簿 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
    可同时对选定的任意多个工作表进行合并和汇总,既可以对固定行数的表格进行合并,也可以对行数不固定的表格进行合并(表格的列数必须固定)。基于以上功能,你可轻松构建报表汇总系统,能达到应用程序级的效果,能极...
  • 【工作表折分】:将当前工作表的数据按条件拆分成多个工作表,可以用任意列的数据做为拆分条件 【合并工作簿】:将指文件夹中所有工作簿中所有工作表数据合并起来。有两种合并方式:将每个工作簿中的工作表合到...
  • 【工作表折分】将当前工作表的数据按条件拆分成多个工作表,可以用任意列的数据做为拆分条件 【合并工作簿】将指文件夹中所有工作簿中所有工作表数据合并起来。有两种合并方式 【文本与数值互换】将选区的数字瞬间...
  • 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 返回窗口的可视区域...
  • 1.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 智能...

空空如也

空空如也

1 2 3
收藏数 46
精华内容 18
关键字:

多个工作簿数据求和