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

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

16^# 跳轉到 »

發表於 2016-1-27 16:06 | 只看該作者

本帖最後由 c_c_lai 於 2016-1-27 16:15 編輯

回復 14# 藍天麗池
Function STSumifs(ByVal endST As Long, Optional startST As Long = 3)
1.  Optional startST As Long = 3 的用意，事先賦予預設值；
例如:
Sub Test()
      Dim WR As Long

      WR = Range("A1").End(xlDown).Row '  最後一筆資料列
      STSumifs WR
End Sub
在 STSumifs 的函式中：
      For cts = startST To endST
startST 等於 3， endST  等於 WR (4903)
此時 STSumifs WR = STSumifs WR, 3 之意，
Optional  的變數宣告，如未帶入值，則以其
設定的預設值 (3) 為參數值。
***  這是一次就處理 3 ~ 4903 完畢。

2.  假設帶入值為：
WR = Range("A1").End(xlDown).Row + 1 '  最後一筆資料列 + 1
STSumifs WR, WR
startST 等於 WR， endST  等於 WR (4094)
***  這是將資料寫入到資料錄的最後列。

TOP

c_c_lai

博士班 Rank: 6 Rank: 6

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

15^#

發表於 2016-1-27 15:30 | 只看該作者

本帖最後由 c_c_lai 於 2016-1-27 15:31 編輯

回復 12# 藍天麗池
這是你原本的定義：

S3
=IF(SUMIFS($E:$E,$D:$D,S$1,$B:$B,$R2)=0,"",SUMIFS($E:$E,$D:$D,S$1,$B:$B,$R2))
T3
=IF(SUMIFS($E:$E,$D:$D,T$1,$B:$B,$R2)=0,"",SUMIFS($E:$E,$D:$D,T$1,$B:$B,$R2))

複製代碼

這是程式碼解析的結果：

S3
=IF(SUMIFS($E3:$E4903,$D3:$D4903,S$1,$B3:$B4903,$R2)=0,"",SUMIFS($E3:$E4903,$D3:$4903,S$1,$B3:$B4903,$R2))
T3
=IF(SUMIFS($E3:$E4903,$D3:$D4903,T$1,$B3:$B4903,$R2)=0,"",SUMIFS($E3:$E4903,$D3:$D4903,T$1,$B3:$B4903,$R2))

複製代碼

然後再轉為數值表示。

TOP

藍天麗池

中學生 Rank: 2

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

14^#

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

回復 7# c_c_lai

C大我要的效果大概跟7樓大大的附件一樣你看一下

TOP

藍天麗池

中學生 Rank: 2

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

13^#

發表於 2016-1-27 14:10 | 只看該作者

回復 9# 准提部林

大大好厲害，但請教一下
1.是每分鐘都要自己按統計嗎??
2.按統計的過程中會造成EXCEL變慢嗎??
3.如果把它改成自動每分鐘統計一次可以嗎??

TOP

藍天麗池

中學生 Rank: 2

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

12^#

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

回復 9# 准提部林

大大可以說明一下用法和原理嗎??我看不太懂，抱歉
謝謝你的幫忙

TOP

藍天麗池

中學生 Rank: 2

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

11^#

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

回復 8# 准提部林
所以函數不能直接打在儲存格上面，要用VBA且執行完要馬上將公式轉成值，要不然一樣會拖慢速度

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

准提部林

版主 Rank: 7 Rank: 7 Rank: 7

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

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: 7 Rank: 7 Rank: 7

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

8^#

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

回復 6# 藍天麗池

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

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

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

[收藏此主題] [關注此主題的新回復]

[通過 QQ、MSN 分享給朋友]