EXCEL VBA:如何计算方案? [英] EXCEL VBA: how to count scenarios?

问题描述

我正在完成一个项目，似乎最后一部分是最困难的.

I am finishing a project and seems like the last part is the most difficult.

我有7个(6 +1个可选)列具有这种排序数据(其中一些仅具有更早/更高/不存在，而另一些则具有更早/更高/等于/不存在).例如三行:

I have 7 (6 + 1 optional) columns that have this sort data (some of them have only earlier/later/na, and some of them earlier/later/equals/na). For example three rows:

OK OK       No  Yes Earlier Earlier N/A
OK OK       No  Yes Earlier Earlier Earlier
OK Missed   Yes Yes Later   Later   Earlier

这些可以在13种不同的情况下结束(如果可以，可以，可以，可以，更早些，不适用"例如为"a = a + 1").我需要实际计算每种情况发生了多少(从"a"到"m").另外例如，如果前三列为"OK OK OK"，则无需考虑以下条件，直接将其添加到f.e.中. b = b + 1并转到下一行.

These can end in 13 different scenarios (if it's "ok ok no yes earlier earlier n/a" would come to for example "a = a + 1"). What I need is to actually count how many of each scenario happened (from "a" to "m"). Also for example if the first three columns are "OK OK OK" I dont need to consider the following conditions and straight add it to f.e. b = b + 1 and go to the next row.

我的问题是，考虑到我将拥有超过50,000行，我可以如何高效地做到这一点?我知道我可以使用IF来做到这一点，但是我会在所有的if中迷失了方向，我相信宏要花很多时间才能在所有情况下运行.

My question here is how efficiently I can do that having in mind that I will have more than 50,000 rows? I understand that I could do that with IF, but I would just get lost in all the if's and I believe this will take a lot of time for macro to run through all the scenarios.

感谢您的所有帮助和支持.

I appreciate all your help and support.

推荐答案

好的，这是在VBA中使用Excel SubTotal函数的启动器

OK this is a starter using the Excel SubTotal function within VBA

它具有您可能要更改的代码中内置的假设，包括解决方案"当前与数据位于同一表中(当前位于名为场景"的表中，从A列和第C行开始) 7).这仅适用于有限数量的数据，但价值不超过5万行！您可以根据需要添加代码以汇总统计信息，并删除小计.保留原始数据不变.

It has assumptions built-in to the code which you may want to change, including that the 'solution' is currently in the same sheet as the data (currently in a sheet called "Scenarios", starting in col A and row 7). This works with a limited amount of data but to 50k rows worth! You can add code to summarise the statistics as you wish and remove the subtotals. It leaves the original data intact.

Sub scenarios()
Dim ws As Worksheet
Dim strow As Long, endrow As Long, stcol As Long, endcol As Long
Dim r As Long, c As Long
Dim newstr As String
Dim cl As Range, rng As Range, drng As Range
Dim strArr() As String

strow = 7
stcol = 1  'Col A
endcol = 7 '7 variables

Set ws = Sheets("Scenarios")

    With ws
        'find last data row
        endrow = Cells(Rows.Count, stcol).End(xlUp).Row
            'for each data row
            For r = strow To endrow
                newstr = ""
                'produce concatenated string of that row
                For c = stcol To endcol
                    newstr = newstr & .Cells(r, c)
                Next c
                'put string into array
                ReDim Preserve strArr(r - strow)
                strArr(r - strow) = newstr
            Next r
        'put array to worksheet
        Set drng = .Range(.Cells(strow, endcol + 4), .Cells(endrow, endcol + 4))
        drng = Application.Transpose(strArr)
        'sort newly copied range
        drng.Sort Key1:=.Cells(strow, endcol + 4), Order1:=xlAscending, Header:=xlNo

        'provide a header row for SubTotal
        .Cells(strow - 1, endcol + 4) = "Header"
        'resize range to include header
        drng.Offset(-1, 0).Resize(drng.Rows.Count + 1, drng.Columns.Count).Select
        'apply Excel SubTotal function
        Selection.Subtotal GroupBy:=1, Function:=xlCount, Totallist:=Array(1)

    End With

End Sub

这篇关于EXCEL VBA:如何计算方案?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！