[發問] 公式如何精簡

各位前備，最近幫朋友製作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

中學生 Rank: 2

帖子: 129
主題: 3
精華: 0
積分: 178
點名: 0
作業系統: XP
軟體版本: 2010
閱讀權限: 20
性別: 男
註冊時間: 2010-12-28
最後登錄: 2018-11-10

2^#

發表於 2014-5-20 14:59 | 只看該作者

建議用vba

Sub ex()
Dim Ar() As String, s, a, time
Application.Range("A:D").SortSpecial key1:=Range("D:D")
'讓A:D的資料根據D的大小，由小至大排列
s = WorksheetFunction.CountIf(Range("A:A"), "V1")
'計算有幾個V1,V1超過65535就會錯誤
ReDim Ar(0 To s) As String
With Sheets(1)
s = 0
For Each a In .Range(.[D1], .[D1].End(xlDown))
If a.Offset(, -3) = "V1" Then
Ar(s) = a.Value
s = s + 1
End If
Next
.[F1].Resize(s, 1) = Application.Transpose(Ar)
'把資料儲存在F1
End With
End Sub

複製代碼

50 字節以內
不支持自定義 Discuz! 代碼

TOP

ML089

版主 Rank: 7 Rank: 7 Rank: 7

帖子: 2025
主題: 13
精華: 0
積分: 2053
點名: 0
作業系統: WIN7
軟體版本: Office2007
閱讀權限: 100
性別: 男
來自: 台北市
註冊時間: 2011-3-2
最後登錄: 2024-3-14

論壇榮譽勳章論壇贊助勳章熱烈參與勳章 2014馬年全勤獎章 2015羊年全勤獎章 2016猴年全勤獎章

3^#

發表於 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倍

{...} 表示需要用 CTRL+SHIFT+ENTER 三鍵輸入公式

TOP

phoenix1