一次较大规模数据处理的经历 EXCEL版

前一段时间写了一篇文章《一次较大规模数据处理的经历 PYTHON版》,尝试使用PYTHON和PANDAS解决较大规模数据的计算问题,近百万条数据如果使用EXCEL处理,速度比较慢,而且对于复杂的计算,还需要借助VBA程序,因此上次使用了PYTHON。但是EXCEL的优点不容忽视,比如简单直观,能够快速筛选、汇总等等,最终生成报表还得使用EXCEL,此次打算用EXCEL重新处理一遍数据,并寻找合适的方法避免EXCEL的缺点,使得EXCEL能够快速地处理较大规模的数据。

接之前的问题,数据规模在百万条,CSV格式,需要对数据进行计算,根据前三列数据的不同,公式分成四组,均类似于电费的阶梯电价计算方案,只不过具体标准不一样,【问题一】需要计算最终值;【问题二】后面还提出了一种探索的算法,即计算公式增加“次数”的条件,每一次的计算公式又不一样。

【问题一】针对该问题,最初使用if嵌套公式,发现嵌套起来复杂,而且复制公式时,机器非常卡,即使关闭了自动计算亦是如此。于是决定使用VBA编写程序解决,分为两部分:一是分段计算函数;二是调用的主函数。解决问题的思路是通过VBA计算数据,此时表中的数据结果是静态的,没有任何公式,因此避免了卡顿的问题。

分段计算函数:

'参数分别对应表中的四列数据
Function calc(col1 As String, _
              col2 As String, _
              col3 As String, _
              col4 As Double)

    '定义一个变量保存第四列的值
    Dim value As Double
    '保存结果
    Dim result As Double
    value = col4
    
    '根据组合条件计算结果
    '此处只列举一种组合的情况
    If col1 = "***" And col2 = "****" Then
        If value < 200 Then
            result = 0
        ElseIf value < 1000 Then
            result = (value-200)*0.6
        ElseIf value < 10000 Then
            result = (1000-200)*0.6 _
                     +(value-1000)*0.7
        Else
            result = (1000-200)*0.6 _
                     +(10000-1000)*0.7 _
                     +(value-10000) * 0.8
        End If
    End If
    '返回结果
    calc = result
End Function

调用的主函数:

Sub main()
    Dim iRow As Integer
    '定义变量,分别对应四列数据
    Dim col1 As String
    Dim col2 As String
    Dim col3 As String
    Dim col4 As Double
    
    '将每一行数据调用计算函数计算结果
    For iRow = 2 To 800000 Step 1
        col1 = Cells(iRow, 1)
        col2 = Cells(iRow, 2)
        col3 = Cells(iRow, 3)
        col4 = Cells(iRow, 4)
        '将结果保存在第5列     
        Cells(iRow, 5) = calc(col1, col2, col3, col4)
    Next
End Sub

【问题二】增加公式计算的条件“次数”,原始数据中,每个人可能有多条数据,时间不一样,每一次在使用公式计算时,有差别。前面已经写好了计算的函数,如果次数统计出来后,可以直接修改计算的函数了,因此主要的问题在于如何按照时间,统计好每个人的次数,即按照时间,对每个人的信息补充1、2、3、4,……,N。假设col3是人员编号,col6时间,col7是次数,现在有一个土办法解决该问题,就是用Excel手动排序,关键字是col3,次关键字是col6,这样每个人的信息都按照时间排好序了,下面就是补充次数列的数据,仍然是是使用VBA。

Sub calcCount()

    Dim iRow As Integer
    Dim count As Integer
    
    '对应人员编号
    Dim col3 As String
    
    '记录上一条的人员编码
    Dim lastId As String
    
    '次数的初始值设置为1
    count = 1
    
    '将每一行数据调用计算函数计算结果
    For iRow = 2 To 800000 Step 1
        '将人员ID赋值给col3
        col3 = Cells(iRow, 3)
             
        If iRow = 2 Then
        '第一条数据,次数设置为1,将id赋值给上一条人员编码
            Cells(iRow, 7) = 1
            lastId = col3
        Else
            '跟上一条属于一个人时,次数加1
            If col3 = lastId Then
                count = count + 1
            Else
            '跟上一条不属于一个人时,本条次数赋值1
            '将本条人员ID赋值给lastId
                count = 1
                lastId = col3
            End If
            '结果保存在第七列
            Cells(iRow, 7) = count
        End If
    Next
End Sub

一次较大规模数据处理的经历 EXCEL版》有2个想法

    1. admin 文章作者

      你要有大量公式,肯定卡得没法用,但是如果使用VBA,直接计算结果,数据都是静态的,就不会出现问题。

      回复

发表评论

您的电子邮箱地址不会被公开。