Board logo

標題: [發問] Excel VBA條件排序問題 [打印本頁]

作者: 蒼雪    時間: 2017-12-17 16:14     標題: Excel VBA條件排序問題

想請問各位大大
若在同一儲存格內,可以利用三種條件下去排序嗎?
例如︰
L20[9/C]N
L20[7.5/B.5]S
L20[3.5/B.5]E
L20[15.5/D]N
L20[19/F]N      
L20[15.5/F.5]N  
L20[7/C]N      
L20[2/E]S      
L20[21/E]N      
     
---------------------------------------------------------
第一個條件為 L20、L10、L30,中括弧前為一個條件(紅色)
排序完成後,再來以中括弧內的數字大小排序(藍色)
最後以中括弧內的英文作排列(綠色)

L20[15.5/D]N

希望會排序成下列情況

L20[2/E]S  
L20[3.5/B.5]E
L20[7.5/B.5]S
L20[7/C]N  
L20[9/C]N
L20[15.5/D]N
L20[15.5/F.5]N  
L20[19/F]N         
L20[21/E]N      

還請各位大大幫忙想了,謝謝。
作者: hcm19522    時間: 2017-12-17 18:33

http://blog.xuite.net/hcm19522/twblog/552829288
作者: 蒼雪    時間: 2017-12-18 00:00

回復 2# hcm19522


    E2:E10{=INDEX(A:A,RIGHT(SMALL(MID(A$2:A$10,2,2)*10^9+MID(A$2:A$10,5,FIND("/",A$2:A$10)-5)*10^6+CODE(MID(A$2:A$10,FIND("/",A$2:A$10)+1,1))/1%+ROW(A$2:A$10),ROW(A1)),2))

抱歉,可以問一下
這判斷的流程是? 而紅字部份,是怎麼判斷的?
作者: hcm19522    時間: 2017-12-18 09:59

本帖最後由 hcm19522 於 2017-12-18 10:06 編輯

回復 3# 蒼雪

程式中10^9 .10^6與*1%三數的設定為第一二不超過99 (9與6次方與小數點一位差別) , 第二三用 CODE應差2次即可 ,最後2位給ROW空間 ,若數據大於99 ,空間須留3次方 ,如此往前推 ,大者*幾次方 "恆大" ,再擷取ROW空間的位置進行排序
幾次方沒固定 重點不可重疊
作者: joblyc017    時間: 2017-12-18 11:07

回復 1# 蒼雪


    輔助欄+自訂排序之方式,請參考:https://goo.gl/38zBPM
[attach]28132[/attach]
作者: 蒼雪    時間: 2017-12-18 20:28

回復 4# hcm19522


   今天實際使用了一下,結果整條的排序都只有L20[2/E]S....
    不知道是在哪一個環節出錯了

-----------------------------------------------------------------
若在這個條件可以完成下,可以也將相鄰的儲存格整列移動嗎?
L20[2/E]S  這個是在B欄,還有A、C欄的資料也可以一同移動嗎?
作者: hcm19522    時間: 2017-12-19 09:41

回復 6# 蒼雪

複製公式 不含 "=" ,貼上後加 "="   ;最前 "{" 代表 shift+ctrl+enter 三鍵一起按所產生 ,非程式裡面
作者: 蒼雪    時間: 2017-12-19 19:24

回復 7# hcm19522


    有喔,我知道Ctrl+Shift+Enter
    今天反覆的再試了幾次,都還是一樣的情況

    結果出來會是 E2:10 都是 L20[2/E]S
作者: hcm19522    時間: 2017-12-20 09:48

回復 8# 蒼雪

原稿E2:E10{=INDEX(A:A,RIGHT(SMALL(MID(A$2:A$10,2,2)*10^9+MID(A$2:A$10,5,FIND("/",A$2:A$10)-5)*10^6+CODE(MID(A$2:A$10,FIND("/",A$2:A$10)+1,1))/1%+ROW(A$2:A$10),ROW(A1)),2))
作者: 蒼雪    時間: 2017-12-20 16:13

回復 9# hcm19522


    [attach]28146[/attach]


我取下 〔INDEX(A:A,RIGHT(SMALL(MID(A$2:A$10,2,2)*10^9+MID(A$2:A$10,5,FIND("/",A$2:A$10)-5)*10^6+CODE(MID(A$2:A$10,FIND("/",A$2:A$10)+1,1))/1%+ROW(A$2:A$10),ROW(A1)),2))〕
來使用,結果得到的是 圖片的結果...
作者: hcm19522    時間: 2017-12-20 17:55

回復 10# 蒼雪

從畫面裡 常用改"公式" ,再手動改"自動"
作者: hcm19522    時間: 2017-12-20 18:00

回復 11# hcm19522
作者: 蒼雪    時間: 2017-12-23 21:01

回復 12# hcm19522


    經過嘗試,結果還是跟之前一樣...出來的結果只有一個
    不會正常的排序... ...
作者: Kubi    時間: 2017-12-24 20:08

回復 1# 蒼雪
用VBA解如下:
Sub test()
    arr = Array("L20", "L10", "L30")
    Application.AddCustomList listArray:=arr
    n = Application.CustomListCount
    For r = 1 To 9
        Cells(r, 2).Value = Split(Cells(r, 1).Value, "[")(0)
        Cells(r, 3).Value = Split(Split(Cells(r, 1).Value, "/")(0), "[")(1)
        Cells(r, 4).Value = Split(Split(Cells(r, 1).Value, "/")(1), "]")(0)
    Next r
    [A1].CurrentRegion.Sort Key1:=[B1], OrderCustom:=n + 1, key2:=[C1], key3:=[D1]
    Application.DeleteCustomList n
    Columns("B:D").Delete
End Sub

另外底下順序是否反了?

L20[7.5/B.5]S
L20[7/C]N

應該是:
L20[7/C]N
L20[7.5/B.5]S
作者: 蒼雪    時間: 2017-12-25 20:17

回復 14# Kubi


抱歉,看了一下,是我順序放錯了。
這個除了排序這個儲存格外,相同row可以一起移動嗎?
作者: Kubi    時間: 2017-12-26 10:51

回復 15# 蒼雪
可能要先了解2個問題:
1.排序索引欄是在那一欄?例如:本程式是以A欄做為排序條件。
2.各欄第1列有沒有抬頭?

本巨集是透過輔助欄來排序的,所以要先釐清欄位位置及數量,如能上傳檔案更好。
作者: 蒼雪    時間: 2017-12-26 20:17

回復 16# Kubi


抱歉,該檔案我沒辦法丟出來...公司內不可帶出

會有 A、B、C、D欄
主要排序索引欄位會在C欄
各欄第一位會有抬頭
作者: Kubi    時間: 2017-12-27 10:27

回復 17# 蒼雪
請參考
  1. Sub test()
  2.     arr = Array("L20", "L10", "L30")
  3.     Application.ScreenUpdating = False
  4.     Application.AddCustomList listArray:=arr
  5.     n = Application.CustomListCount
  6.     er = [C65536].End(3).Row
  7.     For r = 2 To er
  8.         Cells(r, 5).Value = Split(Cells(r, 3).Value, "[")(0)
  9.         Cells(r, 6).Value = Split(Split(Cells(r, 3).Value, "/")(0), "[")(1)
  10.         Cells(r, 7).Value = Split(Split(Cells(r, 3).Value, "/")(1), "]")(0)
  11.     Next r
  12.     Range("A2:G" & er).Sort Key1:=[E2], OrderCustom:=n + 1, key2:=[F2], key3:=[G2]
  13.     Application.DeleteCustomList n
  14.     Columns("E:G").Delete
  15.     Application.ScreenUpdating = True
  16. End Sub
複製代碼

作者: 蒼雪    時間: 2018-1-6 21:25

回復 18# Kubi


抱歉,我學得不是很多。
想請問你,這整個流程的說明可以嗎?

尤其是拆開之後,又是怎麼將他們組合?
作者: Kubi    時間: 2018-1-8 20:07

回復 19# 蒼雪
Q1.流程說明:
1.因你的第一順位排序(L20, L10, L30)非常規性,因此先將你的規則套入自訂排序清單中。
2.將C欄排序索引欄的資料,利用Split資料剖析方式拆解至E、F、G的輔助欄內。
3.利用E、F、G欄為索引,並依其順序分3次重新排序所有資料,請注意第1次是用自訂清單來排序。
4.排序完成後,移除剛置入的自訂排序清單,也刪除不需再使用的E、F、G欄。

Q2.拆開之後,又是怎麼將他們組合?
由上述所知,程式只有拆解並沒有再將它組合喔。
作者: 蒼雪    時間: 2018-1-8 23:33

回復 20# Kubi


所以是,以後面的排序完成後,再將資料刪除囉?
作者: Kubi    時間: 2018-1-9 20:30

回復 21# 蒼雪
是啊∼排序完成後,就不需要再留存無用的資料囉。

另外以模擬資料的方式,加寫兩支不用自訂清單的排序程式,1支是只用1個輔助欄來排序,另外1支是不用
輔助欄來排序,有興趣的話請下載參考。
還有,以上均沒有加寫防呆程式碼喔,若有Bug請自行除錯。
[attach]28217[/attach]
作者: 蒼雪    時間: 2018-1-28 20:51

回復 22# Kubi


抱歉,想再多問個問題。

若是條件排序完成後,我可以選擇哪些資料要留下嗎?

例︰ 排序後
L20[2/E]S  
L20[3.5/B.5]E
L20[7/C]N  
L20[7.5/B.5]S
L20[9/C]N
L20[15.5/D]N
L20[15.5/F.5]N  
L20[19/F]N         
L20[21/E]N

而我要的資料 第一個判別留下的為 L20,第二個為 7~16之間的數字,第三個為 A~F.5 之間的英文。
其餘的資料則Delete。

還懇請大大指教,謝謝。
作者: Kubi    時間: 2018-2-21 15:28

回復 23# 蒼雪
請參考。
Sub test()
    arr = Array("L20", "L10", "L30")
    Application.ScreenUpdating = False
    er = [C65536].End(3).Row
    For r = 2 To er
        L = Application.Match(Split(Cells(r, 3).Value, "[")(0), arr, 0)
        N = Format(Split(Split(Cells(r, 3).Value, "/")(0), "[")(1), "00.0")
        E = Left(Split(Split(Cells(r, 3).Value, "/")(1), "]")(0), 1)
        If L = 1 And N >= 7 And N <= 16 And Asc(E) >= 65 And Asc(E) <= 70 Then
            Cells(r, 5).Value = L & N & E
        End If
    Next r
    Range("E2:E" & er).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
    Range("A2:E" & er).Sort Key1:=[E2]
    Columns("E").ClearContents
    Application.ScreenUpdating = True
End Sub
作者: 蒼雪    時間: 2018-2-24 21:34

回復 24# Kubi


    太強大了,讓我學到好多。
    像是 Application不用 WorksheetFunction 可以直接使用sum ...等語法。




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