12345
返回列表 上一主題 發帖

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

c_c_lai

帖子
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

帖子
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# 藍天麗池
這是你原本的定義：
  1. S3
  2. =IF(SUMIFS($E:$E,$D:$D,S$1,$B:$B,$R2)=0,"",SUMIFS($E:$E,$D:$D,S$1,$B:$B,$R2))
  3. T3
  4. =IF(SUMIFS($E:$E,$D:$D,T$1,$B:$B,$R2)=0,"",SUMIFS($E:$E,$D:$D,T$1,$B:$B,$R2))
複製代碼
這是程式碼解析的結果：
  1. S3
  2. =IF(SUMIFS($E3:$E4903,$D3:$D4903,S$1,$B3:$B4903,$R2)=0,"",SUMIFS($E3:$E4903,$D3:$4903,S$1,$B3:$B4903,$R2))
  3. T3
  4. =IF(SUMIFS($E3:$E4903,$D3:$D4903,T$1,$B3:$B4903,$R2)=0,"",SUMIFS($E3:$E4903,$D3:$D4903,T$1,$B3:$B4903,$R2))
複製代碼
然後再轉為數值表示。

TOP

藍天麗池

帖子
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

藍天麗池

帖子
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

藍天麗池

帖子
365 
主題
40 
精華
0 
積分
406 
點名
0  
作業系統
Win 7 
軟體版本
OFFICE 2003 
閱讀權限
20 
註冊時間
2012-12-11 
最後登錄
2024-8-24 
12#
發表於 2016-1-27 13:59 | 只看該作者
回復 9# 准提部林

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

TOP

藍天麗池

帖子
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

藍天麗池

帖子
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

准提部林

帖子
2825 
主題
10 
精華
0 
積分
2881 
點名
0  
作業系統
〔略〕 
軟體版本
〔略〕 
閱讀權限
100 
性別
男 
來自
〔略〕 
註冊時間
2013-5-13 
最後登錄
2024-9-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

准提部林

帖子
2825 
主題
10 
精華
0 
積分
2881 
點名
0  
作業系統
〔略〕 
軟體版本
〔略〕 
閱讀權限
100 
性別
男 
來自
〔略〕 
註冊時間
2013-5-13 
最後登錄
2024-9-15 
8#
發表於 2016-1-27 12:43 | 只看該作者
回復 6# 藍天麗池


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

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

TOP

c_c_lai

帖子
2035 
主題
24 
精華
0 
積分
2031 
點名
0  
作業系統
Win7 
軟體版本
Office2010 
閱讀權限
100 
性別
男 
註冊時間
2012-3-22 
最後登錄
2024-2-1 
7#
發表於 2016-1-27 11:51 | 只看該作者
回復 6# 藍天麗池
說真的，我不太懂 SUMIFS 的用意，只是依樣畫葫。
如你有時間的話，稍稍說明一下它的使用方式與你的想法，
如此會更明瞭其作用，謝謝。

TOP

        靜思自在 : 時時好心就是時時好日。
12345
返回列表 上一主題