如何通过VBA输入数组公式，其中数组公式中，引用的区域为变量名。

minren118

工作上遇见的问题，需要求出某个型号的单价，用以下数组公式
=MIN(IF(($A$1028:$A$1643=A1028)*($B$1028:$B$1643=B1028),$C$1028:$C$1643))
其中A列为日期，选择当天的（不是每天增加，所以日期会多个），B列中为型号，筛选同一型号，返回C列对应的拍下价格，现需要求单价，所以尝试找同一天，同一型号，最低的价格，热销型号应该最起码有拍下一件的C列价格，求它最小值，但想这个区域$A$1028:$A$1643是用Dim 定义出的range区域，但不知道如何还原成VBA的数组公式，用宏录制没法达到，尝试过以下的的写法，但还是报错。请高手指引一下。
  j = Range("I" & Rows.Count).End(xlUp).Row
  k = Range("A" & Rows.Count).End(xlUp).Row
  Set rng1 = Range(Cells(k + 1, 1), Cells(j, 1))
  Set rng2 = rng1.Offset(0, 1)
  Set rng3 = rng1.Offset(0, 2)
    Cells(k + 1, 8).FormulaArray = _
      "=MIN(IF(("&rng1.address&"="&rng1(1).address)*("&rng2.address&"=RC[-8]),"&rng3.address&"))"

蓝桥玄霜

既然用了VBA，为何还是用公式？

蓝桥玄霜

1. Sub yy()
   
2. Dim Arr, i&, x$, r%, Arr1(), aa, j&, mn
   
3. Dim d, k, t, rng As Range
   
4. Set d = CreateObject("Scripting.Dictionary")
   
5. Sheet1.Activate
   
6. Arr = [a1].CurrentRegion
   
7. For i = 2 To UBound(Arr)
   
8.     x = Arr(i, 1) & "|" & Arr(i, 2)
   
9.     d(x) = d(x) & i & ","
   
10. Next
    
11. k = d.keys
    
12. t = d.items
    
13. For i = 0 To UBound(k)
    
14.     r = 0
    
15.     t(i) = Left(t(i), Len(t(i)) - 1)
    
16.     If InStr(t(i), ",") Then
    
17.         aa = Split(t(i), ",")
    
18.         For j = 0 To UBound(aa)
    
19.             r = r + 1
    
20.             ReDim Preserve Arr1(1 To r)
    
21.             Arr1(r) = Arr(aa(j), 3)
    
22.             If rng Is Nothing Then Set rng = Cells(aa(j), 9) Else Set rng = Union(rng, Cells(aa(j), 9))
    
23.         Next
    
24.     Else
    
25.         Cells(t(i), 9) = Cells(t(i), 3).Value
    
26.     End If
    
27.     mn = Application.Min(Arr1)
    
28.     If Not rng Is Nothing Then rng = mn
    
29.     Set rng = Nothing
    
30. Next
    
31. End Sub
    

复制代码

蓝桥玄霜

请见附件。

minren118

多谢版主关照，太多字典、数组还不太明白，下载后慢慢消化，非常感谢{:soso_e152:}

mjzxlmg

minren118 发表于 2012-6-26 16:29
经过测试，用版主那段代码比用数组公式速度差好多啊，因为我的数据已经累积了4万多条，不知道是不是数据量大 ...

只是增加行数，不改变表格，代码照样用。

minren118

经过测试，用版主那段代码比用数组公式速度差好多啊，因为我的数据已经累积了4万多条，不知道是不是数据量大，用版主的方法速度上快不起来？因为我用公式，如果确定了可以把区域转化为变量用的代码，区域就是后来添加的行数，不用调用之前的数据。请版主再次伸出援助之手{:soso_e183:}

山菊花

同意蓝版意见。数据量大时用VBA一定快过数组公式。
将原数据复制30次，总数据超过5W，经测试，计算全部单价，时间不超过1秒。
附件下载见帖子：
http://club.excelhome.net/forum. ... =881612&pid=6046050

minren118

谢谢各位热心帮助，我再测试一下看看

bl_pengc

蓝桥玄霜 发表于 2012-6-26 14:33
请见附件。

蓝桥玄霜 老师：能帮这个优化吗？Sub Macro3()
'
Range("P3").Select '
   Selection.FormulaArray = _
     "=IF(AND(RC[-5]:RC[-3]="""",RC[-2]:RC[-1]<RC[-7]/3),RC[-7]/3,IF(AND(RC[-5]=0,RC[-3]<RC[-7]/3,RC[-1]<RC[-7]/3),RC[-7]/3,IF(AND(RC[-4]:RC[-1]=""""),RC[-5]+RC[-7]/5,IF(AND(RC[-3]:RC[-1]<=RC[-5]),-RC[-4]+RC[-7]/3,IF(AND(RC[-3]:RC[-1]<=RC[-5]*1.5),-RC[-4]+RC[-7]/3,"""")))))"
   
    ActiveWindow.SmallScroll Down:=-15
    Range("P3").Select
    Selection.AutoFill Destination:=Range("P3:P238")
    Range("P3:P238").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
         
   
   
End Sub

这是写录的宏，请问老师那儿有不对
提示 FormulaArray属性不对


公式是这个{=IF(AND(K3:M3="",N3:O3<I3/3),I3/3,IF(AND(K3=0,M3<I3/3,O3<I3/3),I3/3,IF(AND(L3:O3=""),K3+I3/5,IF(AND(M3:O3<=K3),-L3+I3/3,IF(AND(M3:O3<=K3*1.5),-L3+I3/3,"")))))}