前一段时间写了一篇文章《一次较大规模数据处理的经历 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
ecxel可以处理几百万的数据?不会死掉吗?
你要有大量公式,肯定卡得没法用,但是如果使用VBA,直接计算结果,数据都是静态的,就不会出现问题。