我有一本工作簿可以对我的支出进行“智能”绘制。它已经运行了一年,现在有很多图表和费用。现在,每当我进行任何更改或打开工作簿时,Excel都会引发资源不足错误。问题是,我有很多资源,几乎没有使用它们。
Win8 64bit w/ 8 core CPU and 32GB of ram
Office 2013 64bit
我有2张纸,第一张称为Expenses的纸有3列[Date,Description,Amount]和大约1500行数据。第二张纸上有很多公式(500左右),它们都是相同的,其目的是“将日期与描述匹配的X和Y之间的所有费用相加-一些针头-”。我的公式是这样的:
=
ABS(
SUMPRODUCT(
--(Expenses!A:A >= DATE(2011,12,1)),
--(Expenses!A:A < DATE(2012,1,1)),
--(ISNUMBER(FIND(C50,Expenses!B:B))),
Expenses!C:C
)
)
我可以给Excel更多资源吗? (我很高兴能使用我所有的ram,并且将我的CPU调动了几分钟)。
有没有更有效的方法可以执行此公式?
我知道此公式正在创建一个大型网格并用它掩盖我的费用 list ,并且必须为每个公式创建此网格。我应该创建一个宏来更有效地执行此操作吗?如果我有一个宏,我想以某种方式从一个单元格中调用它
=sumExpenses(<startDate>, <endDate>, <needle>)
那可能吗?
谢谢。
最佳答案
我尝试创建一个函数,希望该函数可以复制您当前的等式在VBA中的功能,但有一些区别。由于我不知道第二张工作表的详细信息,因此缓存可能根本无济于事。
如果您的第二张纸对sumExpenses
的所有调用都使用相同的日期范围,则它应该会更快一些,因为它会在第一遍通过时将所有内容假定为前提。如果您的日期范围在整个过程中都发生变化,那么它只会做很多事而无所作为。
Public Cache As Object
Public CacheKey As String
Public Function sumExpenses(ByVal dS As Date, ByVal dE As Date, ByVal sN As String) As Variant
Dim Key As String
Key = Day(dS) & "-" & Month(dS) & "-" & Year(dS) & "_" & Day(dE) & "-" & Month(dE) & "-" & Year(dE)
If CacheKey = Key Then
If Not Cache Is Nothing Then
If Cache.Exists(sN) Then
sumExpenses = Cache(sN)
Exit Function
End If
Set Cache = Nothing
End If
End If
CacheKey = Key
Set Cache = CreateObject("Scripting.Dictionary")
Dim Expenses As Worksheet
Dim Row As Integer
Dim Item As String
Set Expenses = ThisWorkbook.Worksheets("Expenses")
Row = 1
While (Not Expenses.Cells(Row, 1) = "")
If Expenses.Cells(Row, 1).Value > dS And Expenses.Cells(Row, 1).Value < dE Then
Item = Expenses.Cells(Row, 2).Value
If Cache.Exists(Item) Then
Cache(Item) = Cache(Item) + Expenses.Cells(Row, 3).Value
Else
Cache.Add Item, Expenses.Cells(Row, 3).Value
End If
End If
Row = Row + 1
Wend
If Cache.Exists(sN) Then
sumExpenses = Cache(sN)
Else
sumExpenses = CVErr(xlErrNA)
End If
End Function
Public Sub resetCache()
Set Cache = Nothing
CacheKey = ""
End Sub