如何使用VBA宏在excel表中查找条件累积和 [英] How to find conditional cumulative sums in an excel table using VBA macro
问题描述
3.5463 11
4.5592 12
1.6993 111
0.92521 112
1.7331 121
2.1407 122
1.4082 1111
2.0698 1112
2.3973 1121
2.4518 1122
1.1719 1211
1.153 1212
0.67139 1221
0.64744 1222
1.3705 11111
0.9557 11112
0.64868 11121
0.7325 11211
0.58874 11212
0.86673 11221
0.17075 11222
0.64026 12111
0.80229 12112
0.43422 12122
1.0405 12211
0.63376 12212
0.56491 12221
0.34626 12222
0.81631 111111
0.91837 111112
0.70013 111121
0.87384 111122
1.1474 111211
0.47411 111221
0.12249 111222
0.56728 112111
0.88169 112112
0.14509 112121
0.68655 112211
0.36274 112212
1.1652 121111
0.99314 121112
0.42024 121121
0.23937 121122
1.0346 122111
0.64642 122112
0.15632 122121
0.41725 122122
0。 40793 122211
在第一列中有一个数字。在第二列中，每个数字都是相关联的ID。现在，有一些空白行中不包含任何数字。
如果第一个数字的ID与第二个数字的ID相同，则将其中一个数字定义为另一个数字的女儿，并附加一个数字最后例如，ID 11211和11212都是1121的女儿，因为1121的ID具有额外的数字，一个或一个2，添加到结尾以形成其女儿的ID。因此，1121是11211和11212的父级。
这是我想要的宏。它必须输出第三列，对于每一行，其中包含该行中第一列的编号的累积和加上该数字的父数，以及父数的父数等等直到11或12为止。它将首先简单地输出第1列中的第11列和第12列中的数字。然后，在以111开始的循环中，只有当该行有一个数字和一个id时，它将累加每行（该行中的数字加上父级的第三列输出）的累积和，而只有当父级存在并且在第3列中有一个输出。所以例如，ID为11222的行的第3列中的数字应该是该行的第1列中的数字，加上1122的加号那是112，加上11.所以，0.17075 + 2.4518 + 0.92521 + 3.5463，或7.09406。但是，如果您尝试为ID 111221执行此操作，您将注意到，父11122应该是空的行。因此，父级不存在，111221的第3列中不会输出任何值。
如果有人在手上有一段时间，我将非常感谢这个VBA宏给我换了一个被接受的解决方案。
谢谢
所以例如，ID为11222的行的第3列中的数字应该是该行的第1列中的数字，加上1122的数字，加上112的数字，加上11的数字。所以，0.17075 + 2.4518 + 0.92521 +3.5463或7.09406。
但是，如果您尝试为ID 111221执行此操作，您将注意到父11122应该是空的行。因此，父级不存在，在111221的第3列中不会输出任何值。
作为本机在D1中的工作表数组公式¹
= IF（LEN（B1），SUM（SUMIFS（A $ 1：INDEX（A：A ，MATCH（1E + 99，A：A）），
B $ 1：INDEX（B：B，MATCH（1E + 99，A：A）），LEFT（B1，ROW（INDIRECT（2： & LEN（B1）））））），TEXT（，））
补偿缺失的父母（空字符串）。
作为E1中的VBAUDF²，
函数conditionalCumulativeSum（nums As Range，_
ids As Range，sib As Range，_
可选nullOnBlank As Boolean = True）
Dim i As Integer
'截断UsedRange的任何完整列引用
Set nums = Intersect（nums，nums.Parent.UsedRange）
'匹配nums和ids范围
设置ids = ids.Resize（nums.Rows.Count，nums.Columns.Count）
对于i = Len（sib.Value2）到2步骤-1
如果nullOnBlank和IsError（Application.Match（ - Left（sib，i），ids，0））然后
conditionalCumulativeSum = vbNullString
退出
结束If
conditionalCumulativeSum = conditionalCumulativeSum + _
Application.SumIfs（nums，ids，Left（sib，i））
Next i
如果i = 0 Then conditionalCumulativeSum = vbN ullString
结束函数
上述默认值在遇到任何缺少的父项时返回一个空字符串通过遗传链。这可以通过添加FALSE作为可选的第四个参数关闭，然后UDF将与本地公式相同。
¹<需要使用 Ctrl + Shift + 输入来确定数组公式。如果输入正确，Excel将括号中的公式（例如 {和} ）。你不要在自己的大括号中输入。一旦正确输入第一个单元格，就可以像其他任何公式一样填充或复制。尝试并减少您的全列引用范围更加密切地表示实际数据的范围。阵列公式对数计算循环，所以将参考范围缩小到最小值是一个很好的做法。请参阅
²用户定义函数（又名UDF）被放入标准模块代码表。点击 Alt + F11 ，当VBE打开时，立即使用下拉菜单插入►模块（ Alt + 我，中号）。将功能代码粘贴到新的模块代码表中，其名称类似于 Book1 - Module1（Code）。点击 Alt + Q 返回到您的工作表。
Let's say I have two columns.
3.5463 11
4.5592 12
1.6993 111
0.92521 112
1.7331 121
2.1407 122
1.4082 1111
2.0698 1112
2.3973 1121
2.4518 1122
1.1719 1211
1.153 1212
0.67139 1221
0.64744 1222
1.3705 11111
0.9557 11112
0.64868 11121
0.7325 11211
0.58874 11212
0.86673 11221
0.17075 11222
0.64026 12111
0.80229 12112
0.43422 12122
1.0405 12211
0.63376 12212
0.56491 12221
0.34626 12222
0.81631 111111
0.91837 111112
0.70013 111121
0.87384 111122
1.1474 111211
0.47411 111221
0.12249 111222
0.56728 112111
0.88169 112112
0.14509 112121
0.68655 112211
0.36274 112212
1.1652 121111
0.99314 121112
0.42024 121121
0.23937 121122
1.0346 122111
0.64642 122112
0.15632 122121
0.41725 122122
0.40793 122211
In the first column, there is a number. With every one of those numbers, in the second column, is an associated ID. Now, there are some blank rows that do not contain any numbers in them.
Define one of these numbers to be a "daughter" of another number if the ID of the first number is the same as the ID of the second, with an extra digit on the end. For example, both IDs 11211 and 11212 are daughters of 1121, because the ID of 1121 has an extra digit, either a 1 or a 2, added onto the end to form the ID of its daughters. Thus, 1121 is the parent of both 11211 and 11212.
Here is what I want the macro to do. It must output a third column which contains, for every row, a cumulative sum of the number of the first column in that row, plus the parent number of that number, and the parent number of the parent number, etc. all the way up until it reachers either 11 or 12. It will begin by simply outputting the numbers in column 1 for 11 and 12 in the third column. Then, in a loop beginning with 111, it will add up the cumulative sum of every row (the number in that row plus the third column output of the parent), only if that row has a number and an id, and only if the parent exists and has an output in column 3. So for example, the number in the 3rd column of the row with ID 11222 should be the number in column 1 of that row, plus that of 1122, plus that of 112, plus that of 11. So, 0.17075+2.4518+0.92521+3.5463, or 7.09406. However, if you try to do this for ID 111221, you will notice that the row where the parent 11122 should be is empty. Thus, the parent does not exist, and no value will be outputted in column 3 for 111221.
I would greatly appreciate it if someone has some time on their hands to code up this VBA macro for me in exchange for an accepted solution.
Thanks
So for example, the number in the 3rd column of the row with ID 11222 should be the number in column 1 of that row, plus that of 1122, plus that of 112, plus that of 11. So, 0.17075+2.4518+0.92521+3.5463, or 7.09406.
However, if you try to do this for ID 111221, you will notice that the row where the parent 11122 should be is empty. Thus, the parent does not exist, and no value will be outputted in column 3 for 111221.
As a native worksheet array formula¹ in D1,
=IF(LEN(B1), SUM(SUMIFS(A$1:INDEX(A:A, MATCH(1E+99, A:A)),
B$1:INDEX(B:B, MATCH(1E+99, A:A)), LEFT(B1, ROW(INDIRECT("2:"&LEN(B1)))))), TEXT(,))
The above does not compensate for missing parents (null string). It totals everything it can find and uses zero for missing parents.
As a VBA UDF² in E1,
Function conditionalCumulativeSum(nums As Range, _
ids As Range, sib As Range, _
Optional nullOnBlank As Boolean = True)
Dim i As Integer
'truncate any full column reference to the UsedRange
Set nums = Intersect(nums, nums.Parent.UsedRange)
'match the nums and ids ranges
Set ids = ids.Resize(nums.Rows.Count, nums.Columns.Count)
For i = Len(sib.Value2) To 2 Step -1
If nullOnBlank And IsError(Application.Match(--Left(sib, i), ids, 0)) Then
conditionalCumulativeSum = vbNullString
Exit For
End If
conditionalCumulativeSum = conditionalCumulativeSum + _
Application.SumIfs(nums, ids, Left(sib, i))
Next i
If i = 0 Then conditionalCumulativeSum = vbNullString
End Function
The above defaults to return a null string when it encounters any missing parent through the hereditary chain. This can be turned off by adding FALSE as the optional fourth parameter and then the UDF will behave identically to the native formula.
Results from sample data
¹ Array formulas need to be finalized with Ctrl+Shift+Enter↵. If entered correctly, Excel with wrap the formula in braces (e.g. { and }). You do not type the braces in yourself. Once entered into the first cell correctly, they can be filled or copied down or right just like any other formula. Try and reduce your full-column references to ranges more closely representing the extents of your actual data. Array formulas chew up calculation cycles logarithmically so it is good practise to narrow the referenced ranges to a minimum. See Guidelines and examples of array formulas for more information.
² A User Defined Function (aka UDF) is placed into a standard module code sheet. Tap Alt+F11 and when the VBE opens, immediately use the pull-down menus to Insert ► Module (Alt+I,M). Paste the function code into the new module code sheet titled something like Book1 - Module1 (Code). Tap Alt+Q to return to your worksheet(s).
这篇关于如何使用VBA宏在excel表中查找条件累积和的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
