精华内容
下载资源
问答
  • 上一篇文章讲解了如何运用power query快速合并同一工作簿的多个工作表,不过power query合并后的数据是没有经过求和等数学运算的,这篇文章我们学习运用合并计算功能对不同工作数据进行合并求和。应用场景运用合并...

    上一篇文章讲解了如何运用power query快速合并同一工作簿的多个工作表,不过power query合并后的数据是没有经过求和等数学运算的,这篇文章我们学习运用合并计算功能对不同工作表数据进行合并求和。

    应用场景

    运用合并计算功能要求合并的各个工作表结构一致,但是首列单元格内容不需要顺序完全相同,也不需要数量相等。例如,下图为同一工作簿中的两张工作表"1月"和"2月",是我们接下来要进行合并计算求和的案例素材,两张工作表表格首列"姓名"中的姓名顺序并不完全一致,不同姓名的人数也不相等,工作表"1月"有4个人,"2月"有5个人。

    e1ac85f5d3b3ae4d6c673c28e68f4d1c.png

    操作步骤

    1、单独建立"合并计算求和"工作表,选择要放置合并计算统计结果的单元格,本例选择A1单元格,单击[数据]选项卡[数据工具]功能组中的[合并计算]按钮。

    c093a6e3fed6b226000c952666dc35d8.png

    2、在弹出的[合并计算]对话框中,保持[函数]为默认的“求和”,将光标定位在[引用位置]编辑框中,用鼠标框选"1月"工作表的$A$1:$B$5单元格区域,单击对话框中的[添加]按钮。同理,继续在[引用位置]中添加"2月"工作表的$A$1:$B$6单元格区域。在[标签位置]选项下勾选"首行""最左列"(勾选后可在合并结果中显示最左列的姓名和首行的标题字段名称),最后单击[确定]按钮。

    0c2c89cb5007e4cdb92f567fceeda1b8.png
    3f791825fd9c1336d7bf3a79f09df336.png

    3、返回"合并计算求和"工作表,可见A1:B6单元格区域已经出现了不同人员的销量汇总数据。不过,合并计算结果放置区域左上角字段(A1单元格)是空缺的,要自行添加"姓名"字段

    8ec537fd8f9e81952ba81ff4bd36c0cf.png

    小提示

    使用合并计算功能还能对不同工作表数据合并后进行计数、求平均值、求最大值最小值等运算,只需在[合并计算]对话框中选择相应的函数类型即可。

    2f0288145208ebd9941609d2e933955a.png
    展开全文
  • 我们在平时工作时常常会遇到要对一个工作簿的一个或多个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、注意事项:如果选取的数据源其中有空值,那么在创建数据透视表的时候,只会以计数项来而不是求和项,如果想变为求和项,那就需要更改数据源。另外在建立透视表时一定要把数据源工作簿关闭,以保证添加连接时可以访问。

    展开全文
  • 相信大家在进行数据汇总的时候,对一份工作簿中的多个工作表数据进行多表汇总,相信这个问题绝大多数人都碰到过。对于多工作表数据的引用,许多同学还是不知道如何操作。如上图所示,表格中有1-5月5个月的工资数据,...

    d29cc119e6d93df825ff6e209b50c557.gif

    相信大家在进行数据汇总的时候,对一份工作簿中的多个工作表数据进行多表汇总,相信这个问题绝大多数人都碰到过。对于多工作表数据的引用,许多同学还是不知道如何操作。

    cd92a4a9196c53a4ef29978218937095.gif

    如上图所示,表格中有1-5月5个月的工资数据,每个工作表里面的人数是不固定的。我们需要在汇总表中将对应人员5个月数据进行汇总求和。这里就涉及到对5个工作表的引用。下面我们就来学习Indirect函数3种不同工作表名称下的引用操作。

    案例一:Indirect函数对以数字命名的工作表进行多表引用

    1e5cf7d94d6e5ef7c0e475d173fb0d3e.png

    案例说明:对1-5月5个工作表的数据进行求和

    函数公式:

    =SUMPRODUCT(SUMIF(INDIRECT(ROW($1:$5)&"!A:A"),B6,INDIRECT(ROW($1:$5)&"!c:c")))

    函数解析:

    1、在进行多工作表数据条件求和的时候,我们需要用到Indirect函数进行多表引用,sumif函数进行条件求和,sumproduct函数进行数组求和。也就是三个函数进行嵌套运用;

    2、Indirect函数进行以数字命名的多表引用时,利用INDIRECT(ROW($1:$5)&"!A:A")可以直接调用1-5个工作表中的A列数据。

    案例二:Indirect函数对数字+文本命名的多工作表进行数据求和

    cac4cff32d5e9f5647145b47dd922f3f.png

    案例说明:工作表名称为分别为1月、2月、3月、4月、5月,以数字+文本的形式组成。

    函数公式:

    =SUMPRODUCT(SUMIF(INDIRECT((ROW($1:$5)&"月")&"!A:A"),B3,INDIRECT((ROW($1:$5)&"月")&"!c:c")))

    函数解析:

    1、在这种数字+文本形式的多工作表引用中,Indirect函数引用时需要将数字和文本分别。ROW函数只引用数字,然后用&符号进行连接对于的文字即可实现多表引用。

    现在你学会如何利用Indirect函数进行多工作表数据引用了吗?

    精彩内容推荐
    推荐文章

    两份Word文档内容差异核对,你加班2小时同事只需五秒钟

    推荐文章

    Excel三步搞定蓝底证件照,证件照颜色任意切换,不用担心不会PS了

    推荐文章

    条件判断还用IF函数就out了,Text这两组函数公式简单更易懂

    推荐文章

    Excel按文件名制作目录,你复制粘贴花两小时,同事十秒搞定

    62de13347f56317106f3360ea5aa61a1.png

    展开全文
  • 工作中常常会将不同类别的数据放在不同的工作表或工作簿中,然后需要将这些数据汇总到一个表中。怎样实现多表求和呢?历史文章中写到公式法汇总求和,本例使用技巧的方式实现,方法千万条,好用第一条,给大家提供...

    工作中常常会将不同类别的数据放在不同的工作表或工作簿中,然后需要将这些数据汇总到一个表中。怎样实现多表求和呢?

    历史文章中写到公式法汇总求和,本例使用技巧的方式实现,方法千万条,好用第一条,给大家提供不同的处理方法,也能够根据自身的情况去选择,艺多不压身!

    下面来看案例。下图是三个月的销售信息:

    571cfd2ac0b65ff252859f2fa491b329.png

    下图为合并后的状态:

    8e51cec85aec553d47fc7723b6e89c41.png

    下面将以两种方式为大家解剖操作步骤。

    方式一:合并计算

    在汇总工作表界面,鼠标选中A1单元格,再选择【数据】选项卡,【合并计算】

    6f086350bf00be6581e5f180bc047e25.png

    【合并计算】窗口选择【求和】,并点击引用按钮

    ef31f6511f04017c97645953eab4b838.png

    点击【1月】工作表,框选区域,返回窗口

    ca7aa74dab43e880d777dc151164ab06.png

    选择【添加】按钮

    fa2d4d4f46f6fb4bf588c4045c6579ff.png

    同样的方式,将2月和3月都添加进去,并勾选【首行】【最左列】

    2f55858cc5172b45aa153340ecd13a45.png

    单击确定

    65f6de13b68e84baf2382910fd75d512.png

    添加边框并美化

    69a2c4ed942bb3dcbb73566d2148471e.png

    完成!

    如果“A产品”“B产品”“C产品”等字段相同,合并计算会将三个表的数据进行求和,显示在一列。

    以上合并计算的方式,当数据源有增加,或者数值有变化时,不能动态获更新内容,需要重新操作一遍,对于临时处理,效率还是挺快的。如果需要实时更新,不想重复操作,就需要使用数据透视表的方式。

    方式二:数据透视表

    还是在汇总工作表界面,按快捷键 ALT+D不放,连续按两次 P 键,调出数据透视表向导界面,选择【多重合并计算数据区域】,并单击【下一步】

    0b18f99922b2dc977dec7dd7801b4064.png

    再单击【下一步】,出现选择区域窗口

    5e2088189dbed23e245dc7c3f5247296.png

    与合并计算选择区域的方式一样,将三个表的区域都添加一遍,行数据尽量多选择,目的是当增加数据时,会在此区域范围内。

    2142fd1491cfd29d99a812d5ccfe3de2.png

    2db2bb8e56c799c1b08c05acec903f59.png

    三个表添加完成后单击【下一步】,并选择【现有工作表】的A1单元格,点击【完成】

    af2678ac8a73bbf15ccaea789098d712.png

    点击【设计】选项卡,【报表布局】,选择【以表格形式显示】

    730a47b868d83b82f6e84c14901deafe.png

    点击【设计】选项卡,展开样式,选择第一个样式(可根据自己的喜好选择样式)

    120f131d7a5a55e297b5bc2e1fe327ae.png

    完成!

    e0a45451b4929cf01be3fd0e82f09ddc.png

    我们将1月的数据区域增加一行

    3e9045e83749a34c594db0b7e5d973a3.png

    回到汇总的数据透视表区域,在区域内点击鼠标右键,刷新

    687dc9d5d615e310df0ca93b87dc769d.png

    增加的内容自动更新了

    62e0acbdbad1c9f03aff949f9817beb8.png

    结语:

    ◆Excel的魅力不仅仅在于函数与VBA,其实技巧的作用也是非常大的,我们常常在初学Excel时,眼光被高大上的东西给吸引,忽略了其本质的东西,所以容易走弯路,如果时间非常充裕除外,不断的走弯路也可以让自己学到很多的知识点,但但现今社会时间是比较宝贵的,所以在这里建议大家学习要遵循规律,循序渐进,一楼吃不了大胖子,先将基础操作、技巧、函数学会,如果还有足够的精力,可以继续学VBA,届时你将接触的是另一片天空了,希望本篇对大家有所帮助!

    ◆学而不用则殆,希望大家学习后利用到自己的工作中,哪怕是点滴的应用,也是一种进步。给自己的工作带来非常大的方便。

    加微信群:13628686713

    展开全文
  • 最近推送的五篇文章:《偷懒的技术:打造财务Excel达人》到底有...数字格式转换方法汇总打开工作簿后,表格格式全丢失了,菩萨啊,救救我· 正 · 文 · 来 · 啦 ·大成方略纳部人俱乐部正在搞赠书活动,赠送《偷懒...
  • EXCEL 2007 宝典 附光盘文件

    热门讨论 2010-04-02 14:43:05
    conditional summing.xlsx:一个演示如何使用单个或多个条件计算进行条件求和工作簿。 cout unique.xlsx:一个演示如何计算区域内惟一(非复制的)项的工作簿。 counting text in a range.xlsx:一个演示计算...
  • Excel新增工具集

    2011-12-20 09:30:27
    3、多个工作表中相同位置的行(列)集中到同一表:本工作簿中有若干格式相同的表,为了便于观察各表相同位置数据的变化,可以首先用鼠标选定要观察的区域,然后执行本命令,则各工作表的同位置区域数据集中排列到同一...
  • 11. 一次性打开多个工作簿 11 12. 快速切换工作簿 13 13. 选定超级链接文本(微软OFFICE技巧大赛获奖作品) 13 14. 快速查找 14 15. 修改默认文件保存路径 14 16. 指定打开的文件夹 15 17. 在多个EXCEL工作簿间快速...
  • 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工作簿间快速...
  • Excel百宝箱8.0

    2011-06-07 21:32:17
    不同工作簿中同工作表的数据合并到同一工作表中。差异在于同名工作表的处理 【文本与数值互换】:将选区的数字瞬间转换成文本;将选区的文本型数字瞬间转换成数值 【复选框工具】:批量生成复选框(方框中打勾的...
  • 11. 一次性打开多个工作簿 9 12. 快速切换工作簿 9 13. 选定超级链接文本(微软OFFICE技巧大赛获奖作品) 10 14. 快速查找 10 15. 修改默认文件保存路径 10 16. 指定打开的文件夹 10 17. 在多个EXCEL工作簿间快速...
  • 不同工作簿中同工作表的数据合并到同一工作表中。差异在于同名工作表的处理 【文本与数值互换】:将选区的数字瞬间转换成文本;将选区的文本型数字瞬间转换成数值 【复选框工具】:批量生成复选框(方框中打勾的...
  • 【合并工作簿】将指文件夹中所有工作簿中所有工作表数据合并起来。有两种合并方式 【文本与数值互换】将选区的数字瞬间转换成文本;将选区的文本型数字瞬间转换成数值 【复选框工具】批量生成复选框(方框中打勾的...
  • │ │ │ 技巧24 为工作簿减肥.xls │ │ │ │ │ └─技巧22 简繁转换不求人 │ │ ├─Office 2002 简繁转换加载宏 │ │ │ tcscconv.exe │ │ │ │ │ └─Office 2003 简繁转换加载宏 │ │ ...
  • 1.6.2 创建引用其他工作簿中的数据的公式 29 1.6.3 创建对多个工作表中相同单元格区域的三维引用 30 1.6.4 更新跨工作簿引用的公式 31 1.7 审核公式 31 1.7.1 使用公式错误检查器 32 1.7.2 定位特定类型的数据...
  •  1.5.2 使用宏设置启用不在受信任位置的工作簿中的宏  1.5.3 使用设置“禁用所有宏,并发出通知”  1.6 录制和存储宏  1.7 运行宏  1.7.1 创建宏按钮  1.7.2 将宏关联到窗体控件、文本框或形状  1.8 ...
  • 006 处理跨工作簿引用 8 ◎数据计算中的互换准则与数据区分 9 007 用数值指代逻辑值的规则 9 008 用数学运算替代逻辑函数 11 009 区分空单元格与空文本 13 010 让空白单元格不显示为0 13 ◎函数屏幕提示...
  • 【工作表解密】 可以破解工作表密码和工作簿密码。在不知道密码的前提下找回密码。 【VBA工程解密】 将有密码的VBA工程代码破解,直接打开即可修改代码。相当于VBA工程免密查看。 【隐藏与显示磁盘】 ...
  • 【工作表解密】 可以破解工作表密码和工作簿密码。在不知道密码的前提下找回密码。 【VBA工程解密】 将有密码的VBA工程代码破解,直接打开即可修改代码。相当于VBA工程免密查看。 【隐藏与显示磁盘】 隐藏与显示磁盘...
  • 【工作表解密】 可以破解工作表密码和工作簿密码。在不知道密码的前提下找回密码。 【VBA工程解密】 将有密码的VBA工程代码破解,直接打开即可修改代码。相当于VBA工程免密查看。 【隐藏与显示磁盘】 隐藏与显示...
  • 安全工具 【工作表解密】 可以破解工作表密码和工作簿密码。在不知道密码的前提下找回密码。 【VBA工程解密】 将有密码的VBA工程代码破解,直接打开即可修改代码。相当于VBA工程免密查看。 【隐藏与显示磁盘】 ...
  • EXCEL集成工具箱V6.0

    2010-09-11 01:44:37
    安全工具 【工作表解密】 可以破解工作表密码和工作簿密码。在不知道密码的前提下找回密码。 【VBA工程解密】 将有密码的VBA工程代码破解,直接打开即可修改代码。相当于VBA工程免密查看。 【隐藏与显示磁盘】 ...
  • 实例111 使用重载方法实现不同类型数据的计算 5.2 结构与类 实例112 通过结构计算矩形的面积 实例113 通过类继承计算梯形面积 实例114 封装类实现一个简单的计算器 实例115 使用面向对象思想查找字符串中的...
  • 实例111 使用重载方法实现不同类型数据的计算 5.2 结构与类 实例112 通过结构计算矩形的面积 实例113 通过类继承计算梯形面积 实例114 封装类实现一个简单的计算器 实例115 使用面向对象思想查找字符串中的...
  • 实例111 使用重载方法实现不同类型数据的计算 5.2 结构与类 实例112 通过结构计算矩形的面积 实例113 通过类继承计算梯形面积 实例114 封装类实现一个简单的计算器 实例115 使用面向对象思想查找字符串中的...
  •  实例111 使用重载方法实现不同类型数据的计算 135 5.2 结构与类 136  实例112 通过结构计算矩形的面积 136  实例113 通过类继承计算梯形面积 137  实例114 封装类实现一个简单的计算器 139  实例115 使用...
  • 实例111 使用重载方法实现不同类型数据的计算 135 5.2 结构与类 136 实例112 通过结构计算矩形的面积 136 实例113 通过类继承计算梯形面积 137 实例114 封装类实现一个简单的计算器 139 实例115 使用面向对象思想...

空空如也

空空如也

1 2
收藏数 28
精华内容 11
关键字:

不同工作簿数据求和