VBA练习:单条件和多条件汇总(更新数据表,挑战0.07秒)

ldy

<div class="msgheader">QUOTE:</div><div class="msgborder"><b>以下是引用<i>不看不看</i>在2007-10-3 19:14:14的发言：</b><br/>UBound(ar2)<br/>这个就是D2的月份 对吗?</div><p></p><p>在此题中&nbsp; UBound(ar2)&nbsp; =&nbsp;&nbsp;UBound(ar2,1) = 5&nbsp;&nbsp; ar2 第一维的下标</p><p>相当于&nbsp; [a65536].end(xlup).row</p><p>&nbsp;</p><p>汗 网速很慢</p><p>&nbsp;</p>

liyh67

<p>学习了</p>

不看不看

<p>ar2 = Sheet3.Range("a1:b5")<br/></p><p>UBound(ar2)这里并没有指定是第一维呀 难道可以省略的<br/></p><p></p>

不看不看

<div class="msgheader">QUOTE:</div><div class="msgborder"><b>以下是引用<i>ldy</i>在2007-10-3 19:24:21的发言：</b><br/><div class="msgheader">QUOTE:</div><div class="msgborder"><b>以下是引用<i>不看不看</i>在2007-10-3 19:14:14的发言：</b><br/>UBound(ar2)<br/>这个就是D2的月份 对吗?</div><p></p><p>在此题中&nbsp; UBound(ar2)&nbsp; =&nbsp;&nbsp;UBound(ar2,1) = 5&nbsp;&nbsp; ar2 第一维的下标</p><p>相当于&nbsp; [a65536].end(xlup).row</p><p>&nbsp;</p><p>汗 网速很慢</p><p>&nbsp;</p></div><p>=&nbsp;&nbsp;UBound(ar2,1)哦 明白了</p><p>(我还以为是最后一维的最后那)</p>

[此贴子已经被作者于2007-10-3 19:34:06编辑过]

V20

<p>Sub ldy()<br/>'65000行数据<br/>&nbsp;&nbsp;&nbsp; endl = Sheet1.Range("a1").End(xlDown).Row<br/>&nbsp;&nbsp;&nbsp; ar1 = Sheet1.Range("a1:c" &amp; endl)<br/>&nbsp;&nbsp;&nbsp; Sheet3.Range("b2:b5") = ""<br/>&nbsp;&nbsp;&nbsp; ar2 = Sheet3.Range("a1:b5")<br/>&nbsp;&nbsp;&nbsp; yf = "-" &amp; Sheet3.[d2] &amp; "-"&nbsp;&nbsp;&nbsp; ' 改变判断方式提高速度&nbsp; -1-<br/>&nbsp;&nbsp;&nbsp; t = Timer<br/>&nbsp;&nbsp;&nbsp; For i = 2 To UBound(ar2)<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; abcd = ar2(i, 1)<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; k = 0<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; For j = 2 To UBound(ar1)<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <strong><font color="#f70909">If ar1(j, 2) = abcd Then</font></strong>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ' 月份合适<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<font color="#ff0000"> </font><strong><font color="#ff0000">If InStr(ar1(j, 1), yf)</font> </strong>Then&nbsp;&nbsp;&nbsp; ' ABCD 合适<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; k = k + ar1(j, 3)&nbsp; ' 这个k 并不多余，单变量读写比数组快<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; End If<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; End If<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ar2(i, 2) = k<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Next<br/>&nbsp;&nbsp;&nbsp; Next<br/>&nbsp;&nbsp;&nbsp; MsgBox "TIMER= " &amp; Timer - t<br/>&nbsp;&nbsp;&nbsp; Sheet3.Range("a1:b5") = ar2<br/>End Sub</p><p>红字的两句换一下先后，估计判断两个常量是否相等总比判断一个字符是否在另一个字符串中要快吧。</p>

青城山苦丁茶

<p>学习了，谢谢LDY及兰版！原来以前的代码太慢原因在于没遵循“循环中尽量不要引用对象（如 sheet&nbsp; cells）&nbsp;和使用函数，在循环之前 把它们负值给一个变量”“循环中 的 if 判断 尽量不要用 if a =&nbsp;b and&nbsp; c= d&nbsp; then&nbsp; 这样的句式 这样要判断两次”</p>

ldy

V20 正确 蓝色可以发奖了 嘎嘎[em01]

laosanjie

<div class="msgheader">QUOTE:</div><div class="msgborder"><b>以下是引用<i>飞雨飘</i>在2007-10-3 12:23:57的发言：</b><br/><p>发现此题居然没有人用常规双循环做，很是不解。我发一个吧。</p><p>Option Explicit</p><p>Sub 单条件()<br/>Dim i, j As Integer<br/>For i = 2 To Sheet2.Range("a65536").End(xlUp).Row<br/>Sheet2.Cells(i, 2).Clear<br/>&nbsp;&nbsp;&nbsp; For j = 2 To Sheet1.Range("a65536").End(xlUp).Row<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; If Sheet1.Cells(j, 2) = Sheet2.Cells(i, 1) Then&nbsp;&nbsp;&nbsp; '多条件加个AND<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ' Sheet2.Cells(i, 2) = Sheet2.Cells(i, 2) + Sheet1.Cells(j, 3) '此为数量<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Sheet2.Cells(i, 2) = Sheet2.Cells(i, 2) + <font color="#ff0000"><strong>1</strong>&nbsp;</font>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; '此为个数<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; End If<br/>&nbsp;&nbsp;&nbsp; Next<br/>Next<br/>End Sub</p></div><p>我运行了上面的程序结果不对！因跟我的程序类似，所以上面的这句 Sheet2.Cells(i, 2) = Sheet2.Cells(i, 2) + <font color="#ff0000"><strong>1</strong>&nbsp;（红色的）<strong>1</strong></font><font color="#000000">要改成 Sheet1.Cells(j, 3) 就可以了。</font> </p><p><font color="#000000">下面是我的“作业”：</font> </p><p>ublic Sub 单条件汇总1()<br/>Dim i As Integer, m As Integer, s As Integer, p As Integer&nbsp; <font color="#55aa66">'声明变量</font><br/>&nbsp; Application.ScreenUpdating = False<br/>&nbsp; Sheet1.Select&nbsp; <font color="#4db361">'选取Sheet1工作表</font><br/>&nbsp;&nbsp;&nbsp; s = [a65536].End(xlUp).Row&nbsp; <font color="#55aa77">'s为A列最的一个非空单元格的行数</font><br/>&nbsp; Sheet2.Select&nbsp; <font color="#55aa55">'选取Sheet2工作表</font><br/>&nbsp;&nbsp;&nbsp; m = [a65536].End(xlUp).Row&nbsp; <font color="#5ea25e">'m为（Sheet1工作表）A列最后一非空单元格的行数</font><br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; For p = 2 To m <font color="#5ea26b">'设置p从2到m的循环<br/></font>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; For i = 2 To s&nbsp; <font color="#55aa66">'设置i从2到s之间进行循环。</font><br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; If Cells(p, 1) = Sheet1.Cells(i, 2) Then&nbsp; <font color="#669966">'如果单元格Cells(p, 1)和Sheet1.Cells(i, 2)的值相等，</font><br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Cells(p, 2) = Sheet1.Cells(i, 3) + Cells(p, 2) <font color="#669971">'则 Cells(p, 2)的值+Sheet1.Cells(i, 3)的值<br/></font>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; End If<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Next i<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Next p<br/>&nbsp; Application.ScreenUpdating = True<br/>End Sub </p><p><font color="#ff0000"></font>&nbsp;</p>

兰色幻想

<div class="msgheader">QUOTE:</div><div class="msgborder"><b>以下是引用<i>ldy</i>在2007-10-3 22:56:48的发言：</b><br/>V20 正确 蓝色可以发奖了 嘎嘎[em01]</div><p>ldy测试了没有，在数据量大时，V20的代码运行速度比001的方法慢了20倍，001的代码用时节0.20秒,V20的是4.3秒 </p><p>&nbsp;</p>

[此贴子已经被作者于2007-10-4 0:42:17编辑过]

杨副官

<div class="msgheader">QUOTE:</div><div class="msgborder"><b>以下是引用<i>ldy</i>在2007-10-4 4:36:10的发言：</b><br/><p>目前已知最快的方法是 双数组加字典的方法</p><p>001 的代码 再优化一下 处理65000条数据 最快可达到 0.1秒（我的机子）</p><p>比双数组双循环快 1--2倍</p><p></p></div><p>什么是字典呀？</p>