Excel VBA 7.41多个工作表横向类别纵向求和 任数据随意变,方法核心不会变

一起学习,一起进步~~

之前我们分享过一个方法,就是多个工作表汇总按照类别进行求和的,当时我们选择的类是姓名,并且是纵向的,不过有小伙伴在自己对着代码尝试练习的过程中,发现了另外一个场景,如果类名刚好出现在行上面,我们之前的方法就不行了,没错,因为之前的方法是按照纵向来操作的,如果碰到横向的话,我们就需要稍微调整下代码了

不过可能因为小伙伴们平时比较少用到数组和字典这两个概念,空间概念还没有形成,虽然有方法,但是一直都没有尝试成功,那么今天我就在这里分享下横向的具体操作方法吧。

场景简介

既然类别是在行上面,我们这里就稍微更改下数据

我们现在要实现和之前一样的效果,按照姓名列来进行求和汇总,得到所有人的分数的总和,方法和思路还是一样,依然是通过字典判断姓名是否存在,数组进行计算

还没有这个概念的小伙伴们,可以利用本节课和之前的Excel VBA 7.36跨工作表对数据按类求和,你还在敲计算器?对比着学习

代码区

Sub TEST()Dim zd As Object, rng As Range, arr()Set zd = CreateObject("scripting.dictionary")Set rng = Application.InputBox("请选择类名所在的行", "类名行数的确定", , , , , , 8)TatgetRow = rng.RowTatgetCol = rng.ColumnCountCol = Cells(TatgetRow, Columns.Count).End(xlToLeft).ColumnCountRow = Cells(Rows.Count, TatgetCol).End(xlUp).Rowj = 0t = 0For Each sth In Worksheets t = t + 1 sth.Activate If t = 1 Then For i = TatgetCol To CountCol k = sth.Cells(TatgetRow, i) If zd.Exists(k) Then n = zd(k) For i2 = 2 To CountRow - TatgetRow arr(i2, n) = sth.Cells(i2 + TatgetRow - 1, i) + arr(i2, n) Next i2 Else j = j + 1 zd(k) = j ReDim Preserve arr(1 To CountRow - TatgetRow + 1, 1 To j) For i1 = 1 To CountRow - TatgetRow + 1 arr(i1, j) = sth.Cells(i1 + TatgetRow - 1, i) Next i1 End If Next i Else For i = TatgetCol + 1 To CountCol k = sth.Cells(TatgetRow, i) If zd.Exists(k) Then n = zd(k) For i2 = 2 To CountRow - TatgetRow arr(i2, n) = sth.Cells(i2 + TatgetRow - 1, i) + arr(i2, n) Next i2 Else j = j + 1 zd(k) = j ReDim Preserve arr(1 To CountRow - TatgetRow + 1, 1 To j) For i1 = 1 To CountRow - TatgetRow + 1 arr(i1, j) = sth.Cells(i1 + TatgetRow - 1, i) Next i1 End If Next i End IfNext sthWorksheets.Add after:=Worksheets(Worksheets.Count)Set sthn = ActiveSheetsthn.Name = "纵向求和"sthn.Cells(1, 1).Resize(UBound(arr), UBound(arr, 2)) = arrEnd Sub

其实方法和思路是基本差不多的,不过将之前的列转化为行而已

首先程序会让我们先确定表头,也就是我们的类别所在的行

之后,静待程序运作,程序也是立刻就完成了计算

这样我们的数据就按照我们的要求,实现了横向类别纵向求和的效果了。

代码分析

今天的方法其实并不算难,不过可能是因为小伙伴们还没有形成一个空间逻辑的概念,所以一会横向,一会纵向就比较的糊涂,我这里在说明下程序运行的过程中

大家会留意到这里的数据源和之前有一点不同,因为我们并不是从第一列开始的,这里我是为了增加数据的通用性,所以我们这里还需要增加几个判断。

TatgetRow = rng.Row'选择的类别所在的行TatgetCol = rng.Column'类别是从那一列开始’CountCol = Cells(TatgetRow, Columns.Count).End(xlToLeft).Column'总共多少行CountRow = Cells(Rows.Count, TatgetCol).End(xlUp).Row'总共多少列

我们选择的类别所在的行,以及类别是从那一列开始的,同时要得到整个工作表的usedrange的最大行和最大列

然后就可以开始我们的循环了

首先第一个工作表就不用说了,遍历整个工作表,将所有的数据都装入字典和数组组成的组合中。

一个不多一个不少,全部装入了组合中,然后我们来到了第二个工作表,

这个时候就需要多一些判断了,因为整个数据表第一列是学科,也就是无用的列,我们不能再后续的计算中将他们放去循环中,所以这里要做一个判断

If t = 1 Then

如果T>1 ,那么我们的循环就从有效数据的第二列开始,那这个有效数据的第二列如何表示呢?

看上面的图,大家应该就很清楚了,因为我们最开始已经确认过类别是从第几列开始的了。

后面就比较简单了,如果不存在就直接循环编列,装入数组中,如果存在的话,就需要相加了,这里还要一个地方,大家要注意

第一行是表头,不参与计算的,所以这里要从第二行开始

这里就非常的简单了。

后面的工作表都是这样的工作原理,全部数据汇总完之后,就可以写入工作表中

相对于之前的代码,这里也有一个不同的地方。

因为我们本身就是纵向求和,所以数据本身就是纵向的,所以后面写入的过程中,我们并不需要转置,

sthn.Cells(1, 1).Resize(UBound(arr), UBound(arr, 2)) = arr

不需要再加上worksheetfunction.transpose这样的代码了,大家要留意两次代码的不同。

=========================================

(0)

相关推荐