当前位置:初级会计师考试>考试经验>正文

常用Excel函数公式:多工作表快速汇总,财务会计考试要看!

发布于 2018-03-29 17:05  编辑:Ywen
0

财务会计考试看!常用Excel函数公式:多工作表快速汇总


多工作表快速生成的方法有很多种,现小编为各位财务会计考试及工作相关的伙伴们汇总一下:


一、Sum求和法

如果各个分表表格格式完全相同,可以用SUM函数直接进行求和

=SUM(第一个表:最后一个表名!单元格地址)

64860499_1.jpg

二、Sumif函数法

如果格式不一样,可以用Sumif多表求和法

【例】要求:对各地区的明细表的产品销量进行汇总

定义名称:

工作表=Get.workbook(1)

blob.png

公式:

=SUMPRODUCT(SUMIF(INDIRECT(工作表&'!B:B'),C2,INDIRECT(工作表&'!C:C')))

注意:为了防止循环引,集团表中列前插入2列。


blob.png


三、数据透视表法


【例】如下图所示,在一个工作簿中有四个城市的销售表,需要根据这4个表格,用数据透视表进行汇总。

blob.png


具体步骤:

1、按alt+d组合键,松开后再快速按p键。会打开数据透视表向导,在第一步中选取“多重合并计算数据区域”。

blob.png


2、选取“自定义页字段”。

blob.png


3、添加区域和设置字段数目及名称。

blob.png


4、添加所有要合并的表,并分别设置字段数目和名称。

blob.png


5、点击完成后,会自动生成数据透视表。把页字段名子由“页1”改为“城市”、“行”改为“产品”。

更改前:

blob.png


设置OK!

接下来就可以通过调整字段位置,来完成不同模式的汇总。

以城市+产品汇总

blob.png


四、辅助表法

【例】如下图所示为各子公司销售明细表,要求生成汇总表,统计出每天每个产品的销售情况。

设置步骤:

blob.png


1 、用公式引用公司1的数据。直接用=号引用,然后复制800行(具体行数可以依据表格可能的最大行数,以便数据添加后还可以引用过来)

blob.png


2、 分别引用其他各公司的数据。也要引用各表足够的空行。

blob.png


3 、选取“辅助表”,插入 - 数据透视表。

blob.png


4 、设置数据透视表布局。

blob.png


5、调整数据透视表格式

选取数据透视表 - 报表布局 - 以数据表显示

选取数据透视表 - 设计 - 选取适合的样式

隐藏数据透视表的0值。

调整后的统计表如下图所示。

blob.png

五、合并计算法

【例】如下图所示要求把北京、上海和广州三个地市的产品销售情况进行汇总。

blob.png


操作步骤:

数据 - 合并计算 - 把三个表逐个添加到合并计算引用位置中。

注:(如果是合并多个excel文件中的表,可以点浏览按钮选取另一个文件的表)

blob.png


六、数据透视表+Sql法

【例】如下图所示工资表中,含有多个月份的数据。要求在汇总表中把所有月份的数据汇总到一起。

blob.png


操作步骤:

1、数据选项卡 - 现有连接 - 浏览更多。

blob.png


2、找到当前操作的excel文件。

blob.png


3、选取任一个表格名称。

blob.png


4、【导入数据】窗口中,显示方式选“数据透视表”,放置位置选工资表A1单元格(也可以选新工作表)。

blob.png


5、选取刚创建的数据透视表,然后点击 数据透视表工具 - 选项 - 更改数据源 - 连接属性。

blob.png


6、把下面的代码粘贴到命令文本框里。

------------------------------------

select '3月' as 月份, * from [3月$] union all select '4月' as 月份, * from [4月$] union all select '5月' as 月份, * from [5月$]

-------------------------------------


代码说明:

1) 具体的SQL语句一言两语也说不清楚,同学们只需要按下面的模式套用即可。有多少表,连接多少个。

Select * from [表1$] union all Select * from [表2$] union all Select * from [表3$]....union all Select * from [表N$]

2) '3月' as 月份: 是为三个表创建一个共同的字段(月份),其中引号内3月为自定义字符,用来区分工作表,只是不一定要和工作表名称一致。月份是数据透视表要用的字段名,也是自定义的。中间用 as 连接,放在select 之后 *号之前。如果不需要为三个表创建共同字段名,此部分可以省略。

blob.png


7、连接完成!下面就是对数据透视表的基本操作,添加行、列标标签和数据标签。

blob.png


8、调整格式和修改字段标题。最终效果如下图所示。

blob.png


推荐阅读:

2018年初级会计师考试时间

2017年初级会计师考试大纲

2018年初级会计职称考试报名需要提交的资料


延伸阅读:

更多初级会计师考试报考条件、考试时间和历年真题在这里>>>初级会计师考试

想考试拿高分? 考无忧助你考试无忧>>>专家推荐在线考题 >>>考无忧2018年初级会计师考试辅导题库软件(含历年真题)

本文网址:http://www.k51.com.cn/info/cjkjs/1803/2933833.html
选择分享到: