Excel:使用公式处理数据

Question

我有一项任务要使用Excel公式从表中获取和处理一些数据。

表如下所示：

  A          B      C      D
  Date       Name   Count  Job
1 11.11.2020 Adam   260    1
2 12.11.2020 Nikole 300    2
3 11.11.2020 Adam   900    1
4 11.11.2020 Mary   750    1

任务是：

1. I有带有数据和作业编号的字段。例如，在A1中我有11.11.2020，在A2中我有1。首先，我需要根据这些条件得到所有行。例如，第一行、第三行和第四行。
2. 之后，我需要将结果按名称和sum Count字段分组。在这个例子中，我将得到Adam-1160，Mary-750，

，

1. ，然后我需要检查每个结果:如果结果是<1000，则名称得到30，if >=1000 and <1500 - 60，if >=1500 - 90。在我们的例子中，亚当得到了30.
2. After和60，我必须把这些数字加起来，然后用公式写成单元格。举个例子，答案是90.

现在，我正在使用带有SQL-query的宏解决这个问题：

SELECT Name, SUM(Count) FROM MyTable WHERE Date = "A1" AND Job = "A2" GROUP BY Name

然后循环查询结果，并在数组中写入SumOfCount的结果，并将其和写入到单元格中。

但是这种方式太长了，因为MyTable有超过500.000行，而且宏中的查询运行得太慢。

我正在使用SUMIFS松散另一个更简单的问题；例如，我只需要对Count值进行求和--我将使用以下方法：

=SUMIFS(MyTable!$C:$C; MyTable!$A:$A; $A1; MyTable!$D:$D; $A2)

有没有办法用Excel公式解决我的问题？也许有什么办法来写我自己的公式--计算我的价值的函数？

Answer 1

我不确定基于公式的解决方案是否会更快(特别是如果它必须工作超过500000行)。尝试以下子程序：

Sub SubElaborateTable()
    
    'Declarations.
    Dim RngSource As Range
    Dim RngResult As Range
    Dim RngRange01 As Range
    Dim RngTarget As Range
    Dim DatDate As Date
    Dim DblJob As Double
    Dim WksPivotTableWorksheet As Worksheet
    Dim PvtPivotTable As PivotTable
    
    'Setting variables.
    Set RngSource = Sheets("MyTable").Range("A:D")
    Set RngResult = Sheets("MyTable").Range("F1")
    
    'Adding a new sheet.
    Set WksPivotTableWorksheet = Sheets.Add
    
    'Creating a pivot table out of RngSource.
    Set PvtPivotTable = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, _
                                                          SourceData:=RngSource, _
                                                          Version:=6 _
                                                         ).CreatePivotTable(TableDestination:=WksPivotTableWorksheet.Cells(1, 1), _
                                                                            TableName:=WksPivotTableWorksheet.Name & " Pivot Table", _
                                                                            DefaultVersion:=6 _
                                                                           )
    
    'Filling and formatting the pivot table.
    With PvtPivotTable
        With .PivotFields("Date")
            .Orientation = xlRowField
            .Position = 1
            .PivotItems("(blank)").Visible = False
        End With
        With .PivotFields("Job")
            .Orientation = xlRowField
            .Position = 2
        End With
        With .PivotFields("Name")
            .Orientation = xlRowField
            .Position = 3
        End With
        .AddDataField .PivotFields("Count"), "Count sum", xlSum
        .ColumnGrand = False
        .RowGrand = False
        .PivotFields("Date").Subtotals = Array(False, False, False, False, False, False, False, False, False, False, False, False)
        .PivotFields("Job").Subtotals = Array(False, False, False, False, False, False, False, False, False, False, False, False)
    End With
    
    'Setting RngRange01.
    With WksPivotTableWorksheet
        Set RngRange01 = .Range(Cells(2, 1), .Cells(.Rows.Count, 1).End(xlUp))
    End With
    
    'Filling the result headers.
    RngResult.Value = "Date"
    RngResult.Offset(0, 1).Value = "Job"
    RngResult.Offset(0, 2).Value = "Score"
    
    'Covering the whole RngRange01.
    For Each RngTarget In RngRange01
        
        'Setting DatDate or DblJob according to RngTarget value.
        Select Case True
            Case Is = IsDate(RngTarget.Value)
                DatDate = RngTarget.Value
            Case Is = IsNumeric(RngTarget.Value)
                DblJob = RngTarget.Value
        End Select
        
        'Checking if a Count value is encountered.
        If RngTarget.Offset(0, 1).Value <> "" Then
            
            'If the actual RngResult refers to a different date or job, RngResult is set on the next row.
            If RngResult.Value <> DatDate Or RngResult.Offset(0, 1).Value <> DblJob Then
                Set RngResult = RngResult.Offset(1, 0)
            End If
            
            'Filling in the results accordingly.
            RngResult.Value = DatDate
            RngResult.Offset(0, 1).Value = DblJob
            Select Case RngTarget.Offset(0, 1).Value
                Case Is < 1000
                    RngResult.Offset(0, 2).Value = RngResult.Offset(0, 2).Value + 30
                Case Is < 1500
                    RngResult.Offset(0, 2).Value = RngResult.Offset(0, 2).Value + 60
                Case Is >= 1500
                    RngResult.Offset(0, 2).Value = RngResult.Offset(0, 2).Value + 90
            End Select
            
        End If
        
    Next
    
    'Deleting WksPivotTableWorksheet.
    Application.DisplayAlerts = False
    WksPivotTableWorksheet.Delete
    Application.DisplayAlerts = True
    
End Sub