Board logo

標題: [發問] 公式如何精簡 [打印本頁]
作者: phoenix1    時間: 2014-5-19 16:09     標題: 公式如何精簡

各位前備，最近幫朋友製作excel表格以便公司統計及列印表格使用，但最近發現了一些問題，
像是本來寫的公式如下：
{=IFERROR(INDEX(執行中!D:D,SMALL(IF(執行中!$A$1:$A$9999="V1",ROW($A$1:$A$9999),""),ROW(1:1))),"")}
本來以為朋友的資料應該不會超過一萬，只寫到1~9999；結果朋友通知我，目前統計資料已經超過一萬二千筆，持續擴大中
於是我改了一下程式
{=IFERROR(INDEX(執行中!D:D,SMALL(IF(執行中!$A$1:$A$20000="V1",ROW($A$1:$A$20000),""),ROW(1:1))),"")}
結果造成輸入數據時計算量過大，因為以上的程式整個excel差不多有5000個左右，
就算把自動計算關掉，存檔還是要花約5分鐘重新計算一次才成存檔，
不知是否有前輩可以指導一下怎麼把公式簡化，我看了一堆資料，頭快暈了:dizzy:
作者: softsadwind    時間: 2014-5-20 14:59

建議用vba
  1. Sub ex()
  2. Dim Ar() As String, s, a, time
  3. Application.Range("A:D").SortSpecial key1:=Range("D:D")
  4. '讓A:D的資料根據D的大小，由小至大排列
  5. s = WorksheetFunction.CountIf(Range("A:A"), "V1")
  6. '計算有幾個V1,V1超過65535就會錯誤
  7. ReDim Ar(0 To s) As String
  8. With Sheets(1)
  9.    s = 0
  10.    For Each a In .Range(.[D1], .[D1].End(xlDown))
  11.       If a.Offset(, -3) = "V1" Then
  12.       
  13.       Ar(s) = a.Value
  14.       s = s + 1
  15.       End If
  16.          
  17.    Next
  18.    .[F1].Resize(s, 1) = Application.Transpose(Ar)
  19.    '把資料儲存在F1
  20. End With

  22. End Sub
複製代碼
作者: ML089    時間: 2014-5-20 22:43

各位前備，最近幫朋友製作excel表格以便公司統計及列印表格使用，但最近發現了一些問題，
像是本來寫的公式 ...
phoenix1 發表於 2014-5-19 16:09



20000格公式，每格公式都要排列一次(20000格)，當然很慢
可以採用區域陣列方式加速

1. 選擇要輸入公式的區域(儲存格範圍)
2. 以陣列公式方式輸入下方公式
{=IFERROR(INDEX(D:D,SMALL(IF($A$1:$A$20000="V1",ROW($A$1:$A$20000),""),ROW(1:20000))),"")}
3. 以我的電腦，約可以加快5倍
作者: phoenix1    時間: 2014-5-26 14:33

回復 3# ML089


    大大你好，我發現你跟我的公式只差在最後的row(1:20000)，請問一下這跟我寫的row(1:1)有什麼差別嗎?
作者: phoenix1    時間: 2014-5-26 14:35

建議用vba
softsadwind 發表於 2014-5-20 14:59



    大大你好，因為VBA小弟我實在不太會用，這個VBA會讓原來數據的順序跑掉嗎?
因為我只是要再另外一個分頁顯示有V1記號的數據，並沒有要更換原數據的順序，
抱歉我的問題有點多.XD
作者: softsadwind    時間: 2014-5-26 15:39

回復 5# phoenix1
  1. Sub ex()
  2. Dim Ar() As String, s, a, time

  4. s = WorksheetFunction.CountIf(Range("A:A"), "V1")
  5. '計算有幾個V1,V1超過65535就會錯誤
  6. .Range("F:F").Clear
  7. '清空F:F的資料
  8. ReDim Ar(0 To s) As String
  9. With Sheets(1)
  10.    s = 0
  11.    For Each a In .Range(.[D1], .[D1].End(xlDown))
  12.       If a.Offset(, -3) = "V1" Then
  13.       
  14.       Ar(s) = a.Value
  15.       s = s + 1
  16.       End If
  17.          
  18.    Next
  19.    .[F1].Resize(s, 1) = Application.Transpose(Ar)
  20.    '把資料儲存在F1
  21. End With

  23. End Sub
複製代碼
放在同一頁的F1, 順序不變動
作者: ML089    時間: 2014-5-26 17:31

回復  ML089


    大大你好，我發現你跟我的公式只差在最後的row(1:20000)，請問一下這跟我寫的row(1: ...
phoenix1 發表於 2014-5-26 14:33



    請參考3樓說明，試用後才知道有無差別?



歡迎光臨 麻辣家族討論版版 (http://forum.twbts.com/)