棘手的excel運算問題，如何改善??

藍天麗池

明細變動記錄.rar (148.6 KB)

下載次數: 15

2016-1-26 13:58

下載 (155.37 KB)

2016-1-26 14:01

附檔是一個小弟平常用在紀錄的excel目前有些棘手的運算問題還麻煩版上大大幫我一下

如圖所示，左邊是我平時在紀錄的儲存格，右邊是計算用的儲存格，但是因為右邊計算的儲存格裡面我有寫一些函數，造成整個excel在跑的時候左邊無法紀錄或是整個當掉(因為所寫函數太吃CPU和記憶體)，請問一下版上大大小的這個問題應該怎麼解決才好??

我有想出一些解決方式，無奈對VBA不是太熟，在煩請版上高手幫幫忙

解決方式
1.讓左邊A-F列即時運算(A2-F2是DDE所以需要隨時更新才能接收資料)，R-T列每分鐘運算一次，更新完後寫成值而不是公式，這樣的方式可以嗎??(不知道同一個sheet可不可以不同頻率即時運算)

2.變更R-T列的函數寫法，讓整個程式跑起來不要那麼吃資源

3.將S-T列的函數寫在VBA裡面，每分鐘執行一次執行完後將公式寫成值

煩請版上的高手大大幫幫小弟

c_c_lai

回復 1# 藍天麗池
在 shtRTD (RTD) 表單內 RecordPrice()
加入 STSumifs WR, WR， 如下：

1. Option Explicit
   
2. 
   
3. Sub RecordPrice()
   
4.     Dim WR As Long
   
5.     Dim I As Byte
   
6. 
   
7.     If Range("P2") < 1 Then Exit Sub
   
8. 
   
9.     WR = Range("A1").End(xlDown).Row + 1
   
10.     '  ActiveWindow.ScrollRow = WR - 5     '  只顯示最新幾筆資料
    
11.     If (WR = 3) Or _
    
12.             (Range("F" & WR - 1) <> Range("F2")) Then   '  總量有異動時才記錄
    
13.         For I = 1 To 6
    
14.             Cells(WR, I) = Cells(2, I)
    
15.         Next 'I
    
16.         
    
17.         STSumifs WR, WR                         '  資料同步將數值寫入到 R，S，T 三欄內
    
18.     End If
    
19.     '  With ActiveWindow
    
20.         '  If Intersect(Cells(WR, "B"), .VisibleRange) Is Nothing Then .SmallScroll 5
    
21.     '  End With
    
22. End Sub
    
23. 
    
24. Private Function STSumifs(ByVal endST As Long, Optional startST As Long = 3)
    
25.     Dim cts As Long
    
26.     Dim btm As Long
    
27.    
    
28.     btm = Range("A1").End(xlDown).Row
    
29.    
    
30.     For cts = startST To endST
    
31.         Cells(cts, "R") = IIf(cts = 2, "", IIf(cts = 3, "=MAX(B3:B" & btm & ")", "=R" & (cts - 1) & "-1"))
    
32.         Cells(cts, "R") = Cells(cts, "R").Value
    
33.         Cells(cts, "S") = "=IF(SUMIFS($E3:$E" & btm & ",$D3:$D" & btm & ",S$1,$B3:$B" & btm & ",$R" & (cts - 1) & ")=0," & Chr(34) & Chr(34) & ",SUMIFS($E3:$E" & btm & ",$D3:$D" & btm & ",S$1,$B3:$B" & btm & ",$R" & (cts - 1) & "))"
    
34.         Cells(cts, "S") = Cells(cts, "S").Value
    
35.         Cells(cts, "T") = "=IF(SUMIFS($E3:$E" & btm & ",$D3:$D" & btm & ",T$1,$B3:$B" & btm & ",$R" & (cts - 1) & ")=0," & Chr(34) & Chr(34) & ",SUMIFS($E3:$E" & btm & ",$D3:$D" & btm & ",T$1,$B3:$B" & btm & ",$R" & (cts - 1) & "))"
    
36.         Cells(cts, "T") = Cells(cts, "T").Value
    
37.     Next cts
    
38. End Function
    
39. 
    
40. Sub Test()
    
41.     Dim WR As Long
    
42.    
    
43.     WR = Range("A1").End(xlDown).Row
    
44.     STSumifs WR
    
45. End Sub

複製代碼

最後面之 Test() 是可讓你一次自動執行更換 第 3 行 ~ 第 4903 行
R，S，T 三欄的資料值。如果不異動的話，就不需執行它。
其它程式部分均保留原貌。因我無券商連線無法測知狀況。

jackyq

1, 3 方法可行
R-T列的需求每分鐘才運算一次
卻變成逐筆運算
甚至還逐歷史
不慢都難

准提部林

SUMIFS , 算來就慢, 何況全欄引用！！！

加兩個輔助欄公式：
Ｇ２：=IF($D2=S$1,$E2,"-")　右拉Ｈ２，下拉到底
Ｓ３：=SUMIF($B:$B,$R2,G:G)　右拉Ｔ３，下拉到底，儲存格格式自訂為：#　（０值不顯示）

另外，時間公式改如下：
Ｄ２：=--TEXT(A2,"hhmm")
Ｓ１：=--TEXT(S2,"hhmm")
Ｔ１：=--TEXT(T2,"hhmm")

c_c_lai

回復 1# 藍天麗池
請參考：
如何把個股每 5 分鐘的成交價格記錄下來？

藍天麗池

回復 2# c_c_lai
執行後有幾個問題跟C大說一下
1.R列因為會跟著指數創新高而改變所以不能是寫死的

2.R列只需要抓最高到最低區間200個左右，不需要一直向下增加

3.執行後S跟T列沒有任何動作

另外，如果我想在S列之後呈現8:45-13:45之間的所有結果這樣的話公式要怎麼改(意思就是S寫入8:45、T寫入8:46....一直寫到13:45)
PS.我看了一下用R1C1的方式寫公式好像就不用變更
=IF(SUMIFS(C5,C4,R1C,C2,R[-1]C18)=0,"",SUMIFS(C5,C4,R1C,C2,R[-1]C18))---每一行都這樣，這樣如果要寫到13:45分就應該不用輸入太多公式了

C大我的想法是這樣你看看可不可行
R列不動，S列在8:46:01-8:46:05之內寫入公式，5秒內甚至更短時間完成計算，之後將公式寫成值，T、U、V列依此類推(就是讓執行時間不要太久，執行完立刻將公式寫成值)
這只是小弟的一個想法，還請C大看看可不可行

c_c_lai

回復 6# 藍天麗池
說真的，我不太懂 SUMIFS 的用意，只是依樣畫葫。
如你有時間的話，稍稍說明一下它的使用方式與你的想法，
如此會更明瞭其作用，謝謝。

准提部林

回復 6# 藍天麗池


8:45-13:45 = ３００分鐘　
R列只需要抓最高到最低區間200個左右　

300*200 個SUMIFS，跑得動嗎？？？

准提部林

做個副程式，固定時間去 CALL 即可，
欄位不夠，只做到 08:45 ~ 12:00 共 196 欄，自行去調整：
　
Sub 統計()
Dim R&, C&, Arr, Brr(1 To 200, 1 To 196), uMax, i&
R = Cells(Rows.Count, 1).End(xlUp).Row
If R < 2 Then Exit Sub
Arr = Range("A2:E" & R).Value
uMax = [R3] '最大成交數
For i = 1 To UBound(Arr)
　　R = uMax - Arr(i, 2) + 1  '最大成交數 - Ｂ欄成交數 + 1 = 列位
　　If R < 1 Or R > 200 Then GoTo 101
    　
　　C = Int(Arr(i, 1) * 1440) - 524 'Ａ欄時間分鐘數 - 8:44分鐘數 = 欄位
　　If C < 1 Or C > 196 Then GoTo 101
    　
　　Brr(R, C) = Brr(R, C) + Arr(i, 5)
101: Next
[S3].Resize(200, 196) = Brr
Beep
End Sub

參考檔：
Xl0000328.rar (82.19 KB)

下載次數: 10

2016-1-27 13:48

　

藍天麗池

回復 7# c_c_lai

C大我說明一下，我的主要用意是左邊紀錄量，右邊我根據時間和不同的成交點位來加總
例如：
12:58:55        7775        1        1258        -1                                             
12:58:56        7776        1        1258        -1
12:58:56        7776        1        1258        -1
12:58:58        7775        25        1258        1
12:58:58        7775        1        1258        -1
12:58:58        7775        1        1259        -1
12:59:00        7774        4        1259        -1
12:59:00        7774        1        1259        -1
12:59:00        7774        1        1259        -1
12:59:01        7775        1        1259        -1
12:59:01        7774        1        1259        -1
12:59:02        7774        1        1259        -1
12:59:02        7774        5        1259        -1
12:59:03        7774        12        1259        1

右邊是將12:58裡面的所有7775、7776、7774的量加總，但不加總12:58分以外的量，這就是我為什麼用SUMIFS的原因，如果用SUMIF則會將當天所有同價位或同時間的數字都加總，而我要的只是某個時間段裡面有出現的價位的加總
如上所示
7776  -2        12:58分裡面7776出現2次，最後面的數字加總是-2
7775  -3        12:58分裡面7775出現3次，最後面的數字加總是-3
7774   0         12:58分裡面7774出現0次，最後面的數字加總是0
大概就是這樣，C大能理解嗎??