职场人士看过来,汇总大全各工作表数据不求人

原创 kaixinit  2018-06-04 10:47  阅读 1,821 次

文章一:职场人士看过来,汇总各工作表数据不求人

Excel版本:2013

在职场中,我们经常会碰到分项目存储到各工作表中,但汇总各工作表数据时就比较麻烦,笨办法就是在汇总表中一个个引用各项目的汇总数据,如果项目工作表多了,这么操作还是费时费力的。其实我们用INDIRECT函数可以实现对各工作表数据的快速汇总。

具体看GIF演示

文章二:Excel多工作表汇总,这个功能让你快速达成

Excel多工作表如何汇总,这是职场中经常碰到的问题之一。有多种方法可以达到这个效果,今天给大家带来一个简单的操作——合并计算。

合并计算在哪?

在【数据】选项卡,【数据工具】功能区。

1、 标题一致的多表汇总

分表如下:

各工作表标题一致的情况下,首先将光标定位在汇总表格的左上角单元格,然后再进行操作。光标定位在“引用位置”的文本框中选择各工作表的表格范围,然后点击“添加”。“首行”或“首列”根据情况选择,本例中即要首行的标题,也要首列的员工编号,所以都选择。

最终完成效果如下:

2、标题不一致的多表汇总

如我们要将4个月的明细汇总到一个工作表中,各工作表除员工编号标题外,其它标题不同。

操作方法一致,最终效果如下:

文章三:用VLOOKUP函数从多个工作表查询数据

有群友在群里问如何在多个工作表中查询数据,当时我太忙,没仔细考虑,只是建议用VLOOKUP+INDIRECT应该能解决。等忙完后自己仔细考虑了一下,这两个函数结合其它函数是可以解决这个问题的,现在将我解决的思路写出来供大家思考。

=VLOOKUP(A2,LOOKUP(1,0/COUNTIF(INDIRECT({"河北区";"北京区";"天津区"}&"!A:A"),A2),INDIRECT({"河北区";"北京区";"天津区"}&"!A:B")),2,0)

模拟了一些数据,查询表中的姓名在三个工作表中的任意一个,需要得到每位员工对应的销售额。

VLOOKUP函数的用法不再解释,它由四个参数组成:

VLOOKUP(要查找的数据,在哪个区域查找,返回区域第几列,精确匹配或模糊匹配)

这四个参数中,我们可以解决三个,公式是在查询表的B2单元格中输入的。

VLOOKUP(A2,在哪个区域查找,2,0)

在哪个区域查找,这个我们是不确定的,有可能是在“=河北区!A:B”,也有可能是在“=北京区!A:B”,还有可能是在“=天津区!A:B”。因为是在三个工作表中,我们不确定这名员工到底在哪个工作表。所以我们需要来确定他在哪个工作表中。

因为姓名都在各工作表中第一列,所以要查找的姓名在三个区域中,分别为“=河北区!A:A”、 “=北京区!A:A”、 “=天津区!A:A”。

如果查找员工有没有在某一个工作表中,我们可以用COUNTIF来查找,如果结果等于1,代表这个工作表中有这名员工,如果等于0则代表这个工作表中没有这名员工。但现在我们需要在三个工作表中查找,可以用INDIRECT函数来引用。

所以可以编辑公式:COUNTIF(INDIRECT({"河北区";"北京区";"天津区"}&"!A:A"),A2),它返回由1和0组成的数组,如{1;0;0},这个结果代表员工在河北区。

我们知道1在什么位置就是哪个工作表,但是EXCEL不知道,所以我们得让它知道1所以位置对应的工作表。

此时我们用到LOOKUP(1,0/数组或公式结果为数组,返回结果的区域或数组)这种经典的用法,公式如下:

LOOKUP(1,0/COUNTIF(INDIRECT({"河北区";"北京区";"天津区"}&"!A:A"),A2),INDIRECT({"河北区";"北京区";"天津区"}&"!A:B"))

这样就能得到姓名所在的区域,例如 “河北区!A:B”。

这样的话,就是VLOOKUP的基础用法了:VLOOKUP(A2, 河北区!A:B,2,0)

我们要注意的是公式是数组公式,要用CTRL+SHIFT+ENTER键结束输入。

本例中公式可以作为多表查询的一个通用公式,其实我们通过姓名来返回数值型数据,还可以用SUMIF+INDIRECT,本例还可以用数组公式,公式不再解释。

=SUM(SUMIF(INDIRECT({"河北区";"北京区";"天津区"}&"!A:A"),A2,INDIRECT({"河北区";"北京区";"天津区"}&"!B:B")))

文章四:学会Excel多表汇总,还能学会一个神奇的小技巧

多表汇总一直是职场人士问的最多的EXCEL问题之一,我也向大家介绍过几种多表汇总的方法,这几天在操作多表汇总时碰到一个有意思的小技巧:编辑好公式后我们可以随意指定汇总的工作表而不用重新编辑公式。

先来说一下这个多表汇总的方式,用SUM函数来求和,具体来看GIF:

这种汇总方法要求列各个工作表列标题是一致的,最左侧的员工编号排序必须也是一致的才可以,各个分表的员工编号数量到是可有多有少。

下面神奇的小技巧来了

首先我们增加两个工作表,表名分别为"开始"和"结束",分别放在各分表前面和后面,重新编辑前面演示的公式。

然后我们移动"开始"、"结束"两个工作表的位置,我们会发现汇总的数据总是在两个表中间的工作表,其它工作表是不参与计算的。

是不是一个神奇的小技巧?

经常做数据处理的表哥、表姐们都身有体会,对数据汇总是很平常的事,那在EXCEL中怎么样进行分类汇总呢?在汇总之前要先对关键字段进行一下排序,下面我们以一张工资表中的部门进行汇总来操作。

工具/原料

  • EXCEL2013
方法/步骤

  1. 打开一张工资表,选中工作表中的数据,标题就不要选了,在【数据】选项卡中的【排序】。

  2. 在排序对话框中设置【主要关键字】为【部门】,次序【升序】,升序是从小到大,降序是从大到小,汉字是以拼音的第一个字母排的,A---Z是升序,Z--A是降序。

  3. 排序完成后,就可以进行汇总了,【数据】-【分类汇总】。

  4. 在弹出的对话框中【分类字段】选(部门),汇总方式这里选择求和,【汇总项】就可以根据自己的需要去选择了。

  5. 确定之后各部门就汇总出来了。

  6. 如果只显示汇总后的,只要点击一下左上方的数字2,就可以了。这样就完成了汇总项。

展开全部

我们在日常工作中往往都会面临多表汇总的问题,这段时间也有不少小伙伴在后台问我这个问题。今天我就抽时间给大家一个解决多表汇总,你不需要写繁杂的公式,只需要给我5分钟,动动鼠标就可以解决这个困扰你已久的问题了。

如下图所示,我们该如何快速得到每个人的年薪以及公司的总工资成本支出呢?

此方法适用于Excel2016或者office365,Excel2013及以下需要安装Power Query软件才能使用。

一、导入要汇总的工作簿至查询编辑窗口

依次单击数据选项卡---新建查询---从文件--从工作簿;选择要汇总的Excel文件;在弹出的窗口中选择“多表汇总实例文件”---编辑。

二、在“查询编辑器”中编辑

在打开的“查询编辑器”中按住Ctrl键依次单击最右侧的3列将其选中,然后单击开始--删除列---删除列将其删除;

单击Data列标右侧的双箭头符号,然后在弹出的对话框中单击“确定”;

选中第1行,单击开始选项卡---将第一行作为标题即可;

右键单击ABC Name,选择重命名,将其命名为“月份”。然后单击“工号”右侧的筛选按钮,将“工号”前面的勾去掉,单击确定。

三、将编辑好的数据导入到“数据透视表”中

单击“关闭并上载”---关闭并上载至……

选择“仅创建连接”---加载

回到Excel表格中单击“数据”---“现有连接”---选择“查询--多表汇总实例文件 xlsx(3)”--打开

选择数据透视表--新工作表--单击确定。

四、利用数据透视表汇总结果

拖拽“姓名”字段至行字段,拖拽“工资”字段至值字段即可。

多表汇总就顺利完成了。

本文地址:http://www.kaixinit.com/info/office/393.html
版权声明:本文为原创文章,版权归 kaixinit 所有,欢迎分享本文,转载请保留出处!

发表评论


表情