帖子: 365
主題: 40
精華: 0
積分: 406
點名: 0
作業系統: Win 7
軟體版本: OFFICE 2003
閱讀權限: 20
註冊時間: 2012-12-11
最後登錄: 2024-8-24

1^# 跳轉到 » 倒序看帖

打印

字體大小: tT

發表於 2016-1-26 14:15 | 只看該作者

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

本帖最後由藍天麗池於 2016-1-26 14:18 編輯

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

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

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

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

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

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

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

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

樓主熱帖

c_c_lai

博士班 Rank: 6 Rank: 6

帖子: 2035
主題: 24
精華: 0
積分: 2031
點名: 0
作業系統: Win7
軟體版本: Office2010
閱讀權限: 100
性別: 男
註冊時間: 2012-3-22
最後登錄: 2024-2-1

2^#

發表於 2016-1-26 19:45 | 只看該作者

本帖最後由 c_c_lai 於 2016-1-26 20:00 編輯

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

Option Explicit
Sub RecordPrice()
Dim WR As Long
Dim I As Byte
If Range("P2") < 1 Then Exit Sub
WR = Range("A1").End(xlDown).Row + 1
' ActiveWindow.ScrollRow = WR - 5 ' 只顯示最新幾筆資料
If (WR = 3) Or _
(Range("F" & WR - 1) <> Range("F2")) Then ' 總量有異動時才記錄
For I = 1 To 6
Cells(WR, I) = Cells(2, I)
Next 'I
STSumifs WR, WR ' 資料同步將數值寫入到 R，S，T 三欄內
End If
' With ActiveWindow
' If Intersect(Cells(WR, "B"), .VisibleRange) Is Nothing Then .SmallScroll 5
' End With
End Sub
Private Function STSumifs(ByVal endST As Long, Optional startST As Long = 3)
Dim cts As Long
Dim btm As Long
btm = Range("A1").End(xlDown).Row
For cts = startST To endST
Cells(cts, "R") = IIf(cts = 2, "", IIf(cts = 3, "=MAX(B3:B" & btm & ")", "=R" & (cts - 1) & "-1"))
Cells(cts, "R") = Cells(cts, "R").Value
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) & "))"
Cells(cts, "S") = Cells(cts, "S").Value
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) & "))"
Cells(cts, "T") = Cells(cts, "T").Value
Next cts
End Function
Sub Test()
Dim WR As Long
WR = Range("A1").End(xlDown).Row
STSumifs WR
End Sub

複製代碼

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

TOP

jackyq

中學生 Rank: 2

帖子: 319
主題: 6
精華: 0
積分: 309
點名: 0
作業系統: xp
軟體版本: 2k
閱讀權限: 20
性別: 男
註冊時間: 2015-6-24
最後登錄: 2024-4-27

3^#

發表於 2016-1-26 19:51 | 只看該作者

本帖最後由 jackyq 於 2016-1-26 19:55 編輯

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

TOP

准提部林

版主 Rank: 7 Rank: 7 Rank: 7

帖子: 2834
主題: 10
精華: 0
積分: 2890
點名: 0
作業系統: 〔略〕
軟體版本: 〔略〕
閱讀權限: 100
性別: 男
來自: 〔略〕
註冊時間: 2013-5-13
最後登錄: 2024-11-27

4^#

發表於 2016-1-26 21:29 | 只看該作者

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

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

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

TOP

c_c_lai

博士班 Rank: 6 Rank: 6

帖子: 2035
主題: 24
精華: 0
積分: 2031
點名: 0
作業系統: Win7
軟體版本: Office2010
閱讀權限: 100
性別: 男
註冊時間: 2012-3-22
最後登錄: 2024-2-1

5^#

發表於 2016-1-27 09:29 | 只看該作者

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

TOP

藍天麗池

中學生 Rank: 2

帖子: 365
主題: 40
精華: 0
積分: 406
點名: 0
作業系統: Win 7
軟體版本: OFFICE 2003
閱讀權限: 20
註冊時間: 2012-12-11
最後登錄: 2024-8-24

6^#

發表於 2016-1-27 09:38 | 只看該作者

回復 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大看看可不可行

TOP

c_c_lai

博士班 Rank: 6 Rank: 6

帖子: 2035
主題: 24
精華: 0
積分: 2031
點名: 0
作業系統: Win7
軟體版本: Office2010
閱讀權限: 100
性別: 男
註冊時間: 2012-3-22
最後登錄: 2024-2-1

7^#

發表於 2016-1-27 11:51 | 只看該作者

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

TOP

准提部林

版主 Rank: 7 Rank: 7 Rank: 7

帖子: 2834
主題: 10
精華: 0
積分: 2890
點名: 0
作業系統: 〔略〕
軟體版本: 〔略〕
閱讀權限: 100
性別: 男
來自: 〔略〕
註冊時間: 2013-5-13
最後登錄: 2024-11-27

8^#

發表於 2016-1-27 12:43 | 只看該作者

回復 6# 藍天麗池

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

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

TOP

准提部林

版主 Rank: 7 Rank: 7 Rank: 7

帖子: 2834
主題: 10
精華: 0
積分: 2890
點名: 0
作業系統: 〔略〕
軟體版本: 〔略〕
閱讀權限: 100
性別: 男
來自: 〔略〕
註冊時間: 2013-5-13
最後登錄: 2024-11-27

9^#

發表於 2016-1-27 13:49 | 只看該作者

做個副程式，固定時間去 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)
　

TOP

藍天麗池

中學生 Rank: 2

帖子: 365
主題: 40
精華: 0
積分: 406
點名: 0
作業系統: Win 7
軟體版本: OFFICE 2003
閱讀權限: 20
註冊時間: 2012-12-11
最後登錄: 2024-8-24

10^#

發表於 2016-1-27 13:55 | 只看該作者

本帖最後由藍天麗池於 2016-1-27 14:01 編輯

回復 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大能理解嗎??

TOP