Board logo

標題: [發問] ★(發問)一個資料欄位不對稱的資料篩選問題 [打印本頁]
作者: yagami12th    時間: 2012-1-5 12:19     標題: ★(發問)一個資料欄位不對稱的資料篩選問題

本帖最後由 yagami12th 於 2012-1-5 12:23 編輯

請教一下大家，昨天晚上在思考一個資料處理的問題，問題如下：

附檔：[attach]9072[/attach]

以下為2010/1月契約資料，以工作表1為例 ：                                            (註解：工作表1代表2010/1月，工作表2代表2010/2月，以此類推)

從a2儲存格到a37儲存格為2009/12月17日的"買權"和"賣權"各履約價資料，
從a38儲存格到a73儲存格為2009/12月18日的"買權"和"賣權"各履約價資料，
以此類推 ．．
到2010/1/20(註解：未沖銷契約數為未平倉量，買權有買權的未平倉量，賣權有賣權的未平倉量)
(檔案編排一直排不好，sorry，以觀察未沖銷契約數那欄為主)
----------------------------------------------------------------------------------
交易日期             契約        交割月份        履約價        買賣權        開盤價        最高價        最低價        收盤價        成交量        結算價        未沖銷契約數       
2009/12/17        TXO        201001        6900        買權        830        895        805        805        45                 820                                                         176       
2009/12/17        TXO        201001        6900        賣權        16        20        12.5        19.5        7294                 19.5                                                         24820       
2009/12/17        TXO        201001        7000        買權        750        775        720        720        11                 725                                                         85       
2009/12/17        TXO        201001        7000        賣權        20        28        17.5        27        4631                  27                                                         20925       
2009/12/17        TXO        201001        7100        買權        700        700        630        630        2                 630                                                         132       
2009/12/17        TXO        201001        7100        賣權        33        38        23        36.5        5276                  36.5                                                         11340
．．．
．．．
2010/1/20
-----------------------------------------------------------------------------------------------------------------------------------------------------
excel相關的涵數有max，min，large，small。找最大用max，找第二或第三大用large


因為 每一天的買權有36檔，賣權也是36檔，
所以先新增一個excel檔案為"2010最大oi"，新增12個sheet(因為是12個月)，因為有各個不同的履約價，要找出"每一天"的未平倉量最大值是落在哪一個履約價
，以上述的例子為(2010/1月契約)就將找到的資料往2010最大oi.xlsx裡的sheet1貼上(說明：因為在同個excel新增sheet會變的太多，雖然新增sheet比較好寫，)

所以會最後成果會變成：
(在12/17的各檔履約價的買權有36筆)>但對應算出的最大未沖銷契約數], "(註：最大未沖銷契約數又稱最大未平倉量)落在這36筆的哪一檔履約價"如12/17是落在8400的履約價"，未平倉量為23299(為最大)， 第二大未平倉量的履約價落在 8000的履約價，未平倉量為 21482(第二大)

另外各檔履約價的賣權也有36筆>>也會算出一個最大未沖銷契約數]"是落在哪一檔履約價，與上述的買權說明類似。
---------------------------------------------------
表格格式為：買權為例(以最大值為例)
日期         買權最大未倉量             買權最大未平倉量落在哪個履約價-
12/17       23299                              8400------
12/18       25593                              8400------
12/21      26094                             8400------
．．
．．
---------------------------------------------------
表格格式為：賣權為例(以最大值為例)
日期         賣權最大未倉量             賣權最大未平倉量落在哪個履約價-
12/17       24820                             6900------
12/18       25593                             6900------
12/21      26450                              6900------
----------------------------------------------------
完成的表格格式為 最後合併：

日期         買權最大未倉量             買權最大未平倉量落在哪個履約價-          賣權最大未倉量             賣權最大未平倉量落在哪個履約價-
12/17       23299                              8400------                                                         24820                             6900------
12/18       25593                              8400------
12/21      26094                             8400------                                                          26450                             6900------

  最後會畫成這樣的圖：http://2330.tw/Option_Master.aspx

上面例子是剛好買權最大未平倉量履約價一直在"8400"，常常會有7500跳到7800，又跳到8000的狀況。蠻有趣的一個現象
大概是這樣，不知道我說明的清不清楚．．我也還在找處理這個問題的資訊，這個範例能學會應該也能功力大增，謝謝各位。
作者: GBKEE    時間: 2012-1-5 14:43

回復 1# yagami12th
  1. Option Explicit
  2. Sub Ex()
  3.     Dim E As Worksheet, i As Date, M As Variant, AR(), C As Variant
  4.     ReDim AR(1 To 5, 1 To 1)                               '第一維 有5個元素 ,第二維 有1個元素
  5.     AR(1, 1) = "日期"
  6.     AR(2, 1) = "買權 最大未倉量"
  7.     AR(3, 1) = "買權 最大未平倉量落在哪個履約價"
  8.     AR(4, 1) = "賣權 最大未倉量"
  9.     AR(5, 1) = "賣權 最大未平倉量落在哪個履約價-"
  10.     Application.ScreenUpdating = False
  11.     For Each E In Sheets                                     '工作表1,工作表2,工作表3
  12.        If E.FilterMode Then E.AutoFilterMode = False         '有篩選時 取消[篩選]
  13.         For i = E.[A2] To E.[A2].End(xlDown)                 '日期[A2]直到最後的日期
  14.             E.AutoFilterMode = False
  15.             E.Range("A1").AutoFilter 1, i
  16.             If E.Range("A1").End(xlDown).Row <> Rows.Count Then   '因中間有沒交易日期[篩選]不到資料
  17.                 ReDim Preserve AR(1 To 5, 1 To UBound(AR, 2) + 1) '第二維 原有元素再加1元素
  18.                 AR(1, UBound(AR, 2)) = i                          '日期: i
  19.                 For Each C In Array("買權", "賣權")
  20.                     E.AutoFilterMode = False
  21.                     E.Range("A1").AutoFilter 1, i
  22.                     E.Range("A1").AutoFilter 5, C
  23.                     M = Application.Max(E.Range("L:L").SpecialCells(xlCellTypeVisible))
  24.                     AR(IIf(C = "買權", 2, 4), UBound(AR, 2)) = M              '最大未倉量
  25.                       'IIf(C = "買權", 2, 4)     C = "買權"->2  ,C <> "買權"-> 4
  26.                     Set M = E.Range("L:L").SpecialCells(xlCellTypeVisible).Find(M, LookIn:=xlValues)
  27.                     AR(IIf(C = "買權", 3, 5), UBound(AR, 2)) = M.Offset(, -8) '最大未平倉量落在哪個履約價
  28.                 Next
  29.             End If
  30.         Next
  31.     Next
  32.     With Sheets.Add(Sheets(1))    '新增工作表
  33.         .[A1].Resize(UBound(AR, 2), UBound(AR)) = Application.WorksheetFunction.Transpose(AR)
  34.         .Cells.EntireColumn.AutoFit
  35.     End With
  36.     Application.ScreenUpdating = True
  37. End Sub
複製代碼
作者: yagami12th    時間: 2012-1-5 18:26

回履 GBKEE大：謝謝，我有試了程式碼，但出現以下問題：

13．                   For i = E.[A2] To E.[A2].End(xlDown)                 '日期[A2]直到最後的日期

執行到上面這行，跳出視窗顯示：
出現執行階段錯誤 '16':
運算式太過複雜
作者: GBKEE    時間: 2012-1-5 20:36

回復 3# yagami12th
出現執行階段錯誤 '16':  運算式太過複雜     當真如此
2003正常  請將你的上傳看看
作者: yagami12th    時間: 2012-1-5 20:51

本帖最後由 yagami12th 於 2012-1-5 20:53 編輯

麻煩GBKEE大了，謝謝。我是2010版本的，我記得附檔裡的sheet只放一月，二月，三月，意思為= 工作表1到3，是沒有將工作表1到12全放上去 。

電腦配備也還可以，應該是不會有問題才對，蠻少見的感覺。

以下為附檔，code放在module1：

[attach]9085[/attach]
作者: GBKEE    時間: 2012-1-5 21:03

回復 5# yagami12th
以下為附檔，code放在module1：
附檔沒有module1 也沒code
我2003 已更新可處裡2007檔案 並沒錯誤發生
作者: yagami12th    時間: 2012-1-5 21:10

本帖最後由 yagami12th 於 2012-1-5 21:21 編輯

GBKEE大，我重新試了一下，還是一樣，附檔我再三檢查了，剛剛沒注意到，抱歉。

附檔：(excel)
[attach]9086[/attach]
作者: yagami12th    時間: 2012-1-5 21:24

因為兩張圖的容量太大，我再另外傳圖的部份比較詳細。

圖的附檔為：
[attach]9087[/attach]
[attach]9088[/attach]
作者: GBKEE    時間: 2012-1-5 21:36

本帖最後由 GBKEE 於 2012-1-5 21:42 編輯

回復 8# yagami12th
錯誤原因: 工作表3 第一列是空白列   請修正 為同其他工作表 是欄位名稱
圖片檔案太大??  按 (抓畫面鍵)  Print Screen Sys Rq   小畫家 編輯  (存 gif 檔案不會太大)
作者: yagami12th    時間: 2012-1-5 21:41

真的耶，謝謝，我真是個蠢d，因為我桌面上好幾個2011的選擇權，我剛好點到那個有空白列的去tray。我再試一次。
作者: yagami12th    時間: 2012-1-5 21:44

回GBKEE大：我剛改了sheets3的第一列格式與其他sheets一致，但還是跳一樣的錯誤。

我先把資料寄出，用小電腦開2003版本試看看，等一下我會回來編輯文章。
作者: GBKEE    時間: 2012-1-5 21:53

本帖最後由 GBKEE 於 2012-1-5 21:58 編輯

回復 11# yagami12th
For Each E In Sheets                                     '工作表1,工作表2,工作表3
       If E.FilterMode Then E.AutoFilterMode = False         '有篩選時 取消[篩選]
       Debug.Print E.Name                                '看錯誤是那一工作表
For i = E.[A2] To E.[A2].End(xlDown)                 '日期[A2]到 To E.[A2].End(xlDown) 必須是日期         
      E.AutoFilterMode = False


如圖 : 有錯誤時 打開 [ 即時運算 ] 視窗 查看是那一個工作表出錯


[attach]9089[/attach]
作者: yagami12th    時間: 2012-1-5 22:13

本帖最後由 yagami12th 於 2012-1-5 23:29 編輯

回GBKEE大：
請教GBKEE大，錯在第一個工作表，日期資料都正常。如果把新增sheets的方式，改成開全新的excel檔，分12個sheets去貼篩選好的資料，這樣會不會比較ok



剛試著把工作表刪到只剩工作表1，試好幾次跟不同電腦，跳出的錯誤還是一模一樣，不知道方不方便拜託GBKEE大的檔案上傳。

先謝謝了。

另外，小電腦有灌matlab link excel 所以要花時間開一下，才這麼久。
我用小電腦並檢查欄位都有一致，還是跳出一模一樣的錯誤，會不會是要重新複製，貼上數值格式才能順利運行。，會不會是要重新複製，貼上數值格式才能順利運行。
作者: GBKEE    時間: 2012-1-6 08:27

本帖最後由 GBKEE 於 2012-1-6 08:55 編輯

回復 13# yagami12th
7#檔案(須刪除工作表3 第一列) 測試 ok ,你試試看.  如不行試試 修改的程式碼
  1. Option Explicit
  2. Sub Ex()
  3.     Dim E As Worksheet, i As Date, M As Variant, AR(), C As Variant
  4.     ReDim AR(1 To 5, 1 To 1)                               '第一維 有5個元素 ,第二維 有1個元素
  5.     AR(1, 1) = "日期"
  6.     AR(2, 1) = "買權 最大未倉量"
  7.     AR(3, 1) = "買權 最大未平倉量落在哪個履約價"
  8.     AR(4, 1) = "賣權 最大未倉量"
  9.     AR(5, 1) = "賣權 最大未平倉量落在哪個履約價-"
  10.     Application.ScreenUpdating = False
  11.     For Each E In ActiveWorkbook.Sheets                     '作用中的活頁簿中的 工作表
  12.        If E.FilterMode Then E.AutoFilterMode = False         '有篩選時 取消[篩選]
  13.         For i = E.[a2] To E.[a2].End(xlDown)                 '日期[A2]直到最後的日期
  14.             E.AutoFilterMode = False
  15.             E.Range("A1").AutoFilter 1, i
  16.             If E.Range("A1").End(xlDown).Row <> Rows.Count Then   '因中間有沒交易日期[篩選]不到資料
  17.                 ReDim Preserve AR(1 To 5, 1 To UBound(AR, 2) + 1) '第二維 原有元素再加1元素
  18.                 AR(1, UBound(AR, 2)) = i                          '日期: i
  19.                 For Each C In Array("買權", "賣權")
  20.                     E.AutoFilterMode = False
  21.                     E.Range("A1").AutoFilter 1, i
  22.                     E.Range("A1").AutoFilter 5, C
  23.                     M = Application.Max(E.Range("L:L").SpecialCells(xlCellTypeVisible))
  24.                     AR(IIf(C = "買權", 2, 4), UBound(AR, 2)) = M              '最大未倉量
  25.                       'IIf(C = "買權", 2, 4)     C = "買權"->2  ,C <> "買權"-> 4
  26.                     Set M = E.Range("L:L").SpecialCells(xlCellTypeVisible).Find(M, LookIn:=xlValues)
  27.                     AR(IIf(C = "買權", 3, 5), UBound(AR, 2)) = M.Offset(, -8) '最大未平倉量落在哪個履約價
  28.                 Next
  29.             End If
  30.         Next
  31.     Next
  32.     Dim SaveName As String
  33.     With ActiveWorkbook            '作用中的活頁簿
  34.         SaveName = .Path & "\" & Format(.Sheets(1).[a2], "yyyy") & "年選擇權.xls" '存檔路徑名稱
  35.     End With
  36.     With Workbooks.Add(1).Sheets(1)    '新增活頁簿 [存放整理的資料]
  37.         .[A1].Resize(UBound(AR, 2), UBound(AR)) = Application.WorksheetFunction.Transpose(AR)
  38.         .Cells.EntireColumn.AutoFit
  39.         .Parent.SaveAs SaveName        '新增活頁簿存檔
  40.     End With
  41.     Application.ScreenUpdating = True

  43. End Sub
複製代碼
作者: yagami12th    時間: 2012-1-6 09:04

本帖最後由 yagami12th 於 2012-1-6 10:56 編輯

謝謝GBKEE大，剛執行程式碼出現跟前面一樣的錯誤，      

For i = E.[a2] To E.[a2].End(xlDown)                 '日期[A2]直到最後的日期

xlDown<<會不會是這裡的關係。
作者: GBKEE    時間: 2012-1-6 11:04

回復 15# yagami12th
MsgBox E.[a2]                          '加上看看是否有錯誤
MsgBox E.[a2].End(xlDown)   '加上看看是否有錯誤
For i = E.[a2] To E.[a2].End(xlDown)
作者: yagami12th    時間: 2012-1-6 11:45

回復  yagami12th
MsgBox E.[a2]                          '加上看看是否有錯誤
MsgBox E.[a2].End(xlD ...
GBKEE 發表於 2012-1-6 11:04


回GBKEE大，程式碼經測試後，跳出a2欄的日期跟最後一欄的日期為12/16跟1/19的msgbox視窗後，接著出現前面所提的運算過於複雜的錯誤。

如附檔圖中所示：
[attach]9095[/attach]
作者: GBKEE    時間: 2012-1-6 12:00

回復 17# yagami12th
真是丈二金剛!!
Dim E As Worksheet, i As Date, M As Variant, AR(), C As Variant   刪掉試試看
成  Dim E As Worksheet, i , M As Variant, AR(), C As Variant
請再上傳你測試的檔案 看看
作者: yagami12th    時間: 2012-1-6 13:40

回GBKEE大 ，真的不好意思，也非常感謝你，讓你這麼麻煩，改了剛剛第一行的程式碼之後可以運行了，但變成自動篩選排序的視窗格式，不知是否是正常。

附件如下：
[attach]9096[/attach]
[attach]9097[/attach]
作者: GBKEE    時間: 2012-1-6 14:35

回復 19# yagami12th
下載 [執行後2011年近月選擇權.xlsm] 打開一看 工作表的畫面都是篩選不到資料  這不太對 (應是可顯示最後篩選到的 資料)  
*** E.[a2].End(xlDown) 是最後一個日期 (一定有資料) ****                  搞不清楚.
我重新執行 [執行後2011年近月選擇權.xlsm] 後 工作表的畫面都是留在顯示 篩選到最後 E.[a2].End(xlDown)的資料
我2003升級 到可運行 2007檔案而已,還是2003版本,可請有 2007 版的測試嘹解.
作者: yagami12th    時間: 2012-1-6 15:10

回復 20# GBKEE大
下載 [執行後2011年近月選擇權.xlsm] 打開一看 工作表的畫面都是篩選不到資料  這不太對 (應是可顯示最後篩選到的 資料)<<我開這個檔案也是這個情形，看來只有換電腦測試一途。等一下要來試另一個程式看看，如果有成功再放上來分享。  

不過還是非常感謝GBKEE大
作者: yagami12th    時間: 2012-1-6 15:49

本帖最後由 yagami12th 於 2012-1-6 16:06 編輯

回GBKEE大，我換了AO753的小電腦，開啟"未執行的2011年近月選擇權"EXCEL檔，執行巨集成功，桌面會產生一個"2010選擇權"EXCEL檔，我將它上傳上來，圖如果畫下去容量會太大，所以傳普通的檔案。

太感謝GBKEE大了，按N個贊，我還在錄程式碼邊學。

附檔如下：
[attach]9099[/attach]

含圖版(蠻酷的)：
[attach]9101[/attach]
作者: GBKEE    時間: 2012-1-6 16:32

回復 22# yagami12th
你的檔案 2003版 不認識它啊!!!!! ( 可否存成2003版)
作者: yagami12th    時間: 2012-1-6 16:49

本帖最後由 yagami12th 於 2012-1-6 17:17 編輯

我在2010裡畫好圖，到2003不知道能不能順利顯示，GBKEE大如果不行再跟我說，我用小電腦的2007版畫。

附檔是2003年版本，剛命名命錯了，命成2010，已經改了檔名
附檔如下：
[attach]9105[/attach]
作者: yagami12th    時間: 2012-1-6 17:04

又發現另一種風格的圖，原來可以畫成這樣，有空再來試試。

附件如下(資料來源：大華期貨)：
[attach]9104[/attach]
作者: GBKEE    時間: 2012-1-6 17:14

回復 24# yagami12th
謝謝你  期貨我是門外漢  
[給你的程式沒有抓  最高價,最低價 ]
作者: yagami12th    時間: 2012-1-6 17:25

本帖最後由 yagami12th 於 2012-1-6 19:22 編輯

回復 25# GBKEE大
我才要特別感謝你，不然的話不知要摸多久，今天都在東查名查，簡單程式弄到一半有點失焦了。
因為目前處理的都是期貨跟選擇權的每日資料，所以另外再抓最高最低價貼過來也還OK。

但是之後處理到每分每秒的選擇權資料就需要程式的功力了，台股交易時間五小時，以每分鐘頻率資料為例：就是原本EXCEL檔的資料量乘以300，如果是每秒鐘的委託資料就要將原資料量乘以5000至10000(註：因為一秒鐘內常發生30筆到70筆的交易，開盤附近很常見)，另外，選擇權履約價這麼多，資料量會十分驚人，期貨的話就還好，因為期貨只有一個。

關於最高最低價等資料，不知道GBKEE大是否需要，我把他放上來。剛找這個資料找了一下，不然很快就回文了。

附件如下：             期貨與現貨日資料("1990/1/4至今，1998/7/21至今")(註：是最完整的資料)這次我有記得，2003跟2010版皆有。
[attach]9107[/attach]
作者: yagami12th    時間: 2012-1-7 20:34

本帖最後由 yagami12th 於 2012-1-7 20:36 編輯

回復 26# GBKEE

回GBKEE大，我做一個從2007年到現在的的圖做好再讓你看看，之前回覆都不知道要按回覆按紐，抱歉。

論壇的夾帶檔案好像出很大的問題，我下午弄了好久，另外我翻的書裡面都找不到GBKEE大的這幾次分享進階程式碼。
作者: GBKEE    時間: 2012-1-8 08:22

回復 28# yagami12th
可能是你還不熟悉上傳檔案吧!
文件尺寸: 小於 2MB   
可用擴展名: chm, pdf, zip, rar, tar, gz, bzip2, gif, jpg, jpeg, png
作者: yagami12th    時間: 2012-1-8 10:06

回復 29# GBKEE

不是，昨天真的怪怪的 夾帶好久，我是中學生等級，所以是1mb，2009年近月的資料八月的資料有點問題，我正在修正中，下午一點左右應該可以做好完整版的圖。
作者: yagami12th    時間: 2012-1-8 12:03

回復 29# GBKEE

給GBKEE大
經過檢查跟校正之後終於完成
這個是完成的檔案(2007到2011)：

[attach]9138[/attach]
作者: GBKEE    時間: 2012-1-8 13:20

本帖最後由 GBKEE 於 2012-1-8 13:24 編輯

回復 31# yagami12th
謝謝你
作者: usana642    時間: 2012-10-17 10:45

謝謝各位大大的熱心分享
作者: hipper68    時間: 2012-11-29 09:59

回復 22# yagami12th


    請教版大...
    無法篩選的問題，是換電腦之後就解決了嗎???
    在 excel2010 執行還是會有問題

    如附圖:[attach]13324[/attach]

   謝謝！！
作者: hipper68    時間: 2012-12-7 12:09

回復 20# GBKEE

google 一下autofilter 的用法
我好像找到一個解法：
            
       E.Range("A1").AutoFilter 1, Format(i, "yyyy/m/d")
   
改成這樣就可以跑了...
提供給大家參考...

擷取的說明：
http://www.pcreview.co.uk/forums/excel-can-use-variable-autofilter-vba-t3748818.html
======================
This is from "Excel 2002 VBA Programmer's Reference"
Written by John Green, Stephen Bullen, Rob Bovey and Robert Rosenberg

http://www.oaltd.co.uk:80/ExcelProgR...rogRefCh22.htm
Search for "Range.AutoFilter" and you'll see this note:

Range.AutoFilter

The AutoFilter method of a Range object is a very curious beast. We are forced
to pass it strings for its filter criteria and hence must be aware of its string
handling behaviour. The criteria string consists of an operator (=, >, <, >=
etc.) followed by a value.

If no operator is specified, the "=" operator is assumed. The key issue is that
when using the "=" operator, AutoFilter performs a textual match, while using
any other operator results in a match by value. This gives us problems when
trying to locate exact matches for dates and numbers.

If we use "=", Excel matches on the text that is displayed in the cell, i.e. the
formatted number. As the text displayed in a cell will change with different
regional settings and Windows language version, it is impossible for us to
create a criteria string that will locate an exact match in all locales.

There is a workaround for this problem. When using any of the other filter
criteria, Excel plays by the rules and interprets the criteria string according
to US formats. Hence, a search criterion of ">=02/01/2001" will find all dates
on or after 1st Feb, 2001, in all locales.

We can use this to match an exact date by using two AutoFilter criteria. The
following code will give an exact match on 1st Feb, 2001 and will work in any
locale:

Range("A1200").AutoFilter 2, ">=02/01/2001", xlAnd, "<=02/01/2001"



歡迎光臨 麻辣家族討論版版 (http://forum.twbts.com/)