[[20130510092122]]　『VBAで並べ替えの順序を任意に指定したい』（シマリス）　ページの最後に飛ぶ

『VBAで並べ替えの順序を任意に指定したい』（シマリス）

 VBAで出荷した商品の月別一覧表を作っています。

 商品は「製品-AB」「製品-LL」「商品-AB」などの分類があり、その分類（K列）をキーに並べ替えをしています。

 今、昇順で並べ替えるコードを書いたのですが、これを他の色々な資料で使う際に、社内で
 暗黙の了解で決まっている順序で並べたいのです。

 例えば、「製品-AB」「製品-BB」「商品-AC」「商品-BB」という分類があったとして、普通に昇順でソートすると

 「商品-AC」
 「商品-BB」
 「製品-AB」
 「製品-BB」

 という順序になります。

 しかし社内で使う資料では

 「製品-BB」
 「製品-AB」
 「商品-AC」
 「商品-BB」

 このような順番で並んでいます。

 分類は現在15ほどありますが、今後も増える可能性があります。
 また、月別の集計表で、必ずしも全部の分類が存在するとは限りません。

 例えばどこかのシートに並べ替えの基準を記載しておいて、集計の際にそこを参照して並べ替えるということはできますか？

 Exccel2010、Windows7です。

 お願いします

 2007、2010 なら、シートに何かデータリストを作って、適当に並び替え、
 そのときに並び替え順序をクリックしてユーザー設定リストを選び
 これも、適当なものをリストから選んで（たとえば日曜日、月曜日、・・・とか）実行。

 これをマクロ記録して出来上がるコードを見れば、そこにヒントがあるとおもう。

 2003 なら、並び替え前に、テンポラリーにユーザー設定リストを登録し、
　並び替えで、そのリストインデックス番号を指定して実行。
 並び替えが終われば、テンポラリーに設定したリストを削除。

 こんな感じかな。

 (ぶらっと)

 ぶらっと様

 ありがとうございます。
 並び替えのユーザー設定リストに「製品-○○」「商品-○○」のリストを作って、マクロの記録でそのリスト順に並び替えてコードを見てみました。

 すると、コードにユーザー設定リストに設定した「製品-○○,製品-××,・・・」がそのまま羅列されていました。

 当面はこれでも問題はないのですが、分類が増えた時にまたコード自体をいじることになるのでしょうか…

 （試しに、既に登録してある「1月,2月,・・・」というリストで記録していましたが、やはりリストの項目自体がコードに羅列されました）

 （シマリス）

 >分類が増えた時にまたコード自体をいじることになるのでしょうか

 いやいや、これは【ヒント】

 >例えばどこかのシートに並べ替えの基準

 こうしようね。　で、生成されたコードは　CustomOrder:= "〇〇,■■,△△,・・・・"　こんなコードだったよね。
 つまり、どこかのシートのセルに　〇〇,■■,△△,・・・・　といった文字列をいれておいて
 CustomOrder:= どこかのシート.Range(どこかのセル).Value とあたえてみよう。

 リストがかわれば、このセルの値を任意に変更。

 (ぶらっと)

 ↑　もし、並び替えリストのメンテナンス上、１つのセル内に　〇〇,■■,△△,・・・・　といれるより
 　　どこかのシートの、たとえばA1,A2,A3,・・・・に必要な項目を入れておくほうがベターなら
 　　これも、【ヒントコード】だけど、以下。

 Sub Sample()
    Dim lst As String

    With Sheets("どこかのシート")
        lst = Join(WorksheetFunction.Transpose(.Range("A1", .Range("A" & .Rows.Count).End(xlUp))), ",")
    End With

    MsgBox lst

 End Sub

 (ぶらっと)

 >> 例えばどこかのシートに並べ替えの基準を記載しておいて、集計の際に参照して並べ替
横入り失礼します。既出ならスキップして下さい。
作業列で match+Index か　Vlookupで並び順を取得してしまえば楽ではないでしょうか。
もちろんこの部分をマクロでも良いと思いますが。
追記）私的にはユーザー設定の並び変えも結構トライしましたが、
　　　マクロでの登録削除が、いまいちしっくりこないです。
　　　この辺りは私のスキルレベルの問題でもあるのでしょうが。

（Cod）

 ぶらっと様すみません、そういうことだったんですね。
 ありがとうございます。

 Cod様、一覧表の「分類」ごとに小計行が入ったり、他にもソートキーがあったりするものでマクロで一気にしようかと…

 ありがとうございました。

 （シマリス）

 To Codさん

 >私的にはユーザー設定の並び変えも結構トライしましたが、マクロでの登録削除が、いまいちしっくりこないです。

 確かに！
 2003は、もう手元にないので記憶が忘却の彼方ですけど、メンテナンスを行うときのユーザー設定リストを指定するインデックス番号と
 並び替えで指定する番号が、１つずれているということだったかと記憶。

 ただ、2007以降は、並び替えコードの中で、パラメータとして"〇〇,■■,・・" と与えることも
 できるようになっているということで、これは、便利になったというべきでしょうかね？

 (ぶらっと)

 ぶらっと様すみません、マクロの記録でできたコードに、並べ替えのリストの変数を組み込んだりしてコードを書いたのですが「型が一致しません」のエラーがどうしても出てしまいます。

 集計の際は、作業用のシートのAV列～BL列を使用し、「分類」はBF列にあります。
 更に第二のキーで、AV列（日付）を昇順でソートしています。

     lRow = ws2.Range("AV" & ws2.Rows.Count).End(xlUp).Row　　　'作業列の最終行

    With Sheets("参照用")　　　　'J列に「分類」を2行目から入力（1行目は見出し）
        lst = Join(WorksheetFunction.Transpose(.Range("J2", .Range("J" & .Rows.Count).End(xlUp))), ",")
    End With

    ws2.Sort.SortFields.Clear
    ws2.Sort.SortFields.Add Key:=ws2.Range("BF2:BF" & lRow), _　　　　　　'★ここが黄色く反転
        SortOn:=xlSortOnValues, Order:=xlAscending, CustomOrder:= _
        lst, DataOption:= _
        xlSortNormal
    ws2.Sort.SortFields.Add Key:=ws2.Range("AV2:AV" & lRow), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ws2.Sort
        .SetRange Range("AV1:BL" & lRow)
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

 反転したコードで変数 lst にカーソルをあてると、「分類」は "製品-BB,製品-AB・・・" と表示されます。

 あれこれ試したのですがどうしてもエラーが回避できないのでお教えください。

 （シマリス)

 >>ただ、2007以降は、並び替えコードの中で、パラメータとして"〇〇,■■,・・" と与えることも

おいしい情報ありがとうございます。
何度か試したはずなんですが２００７での新機能今気が付きました。ありがとうございました。

確かにこれなら使える。

（Cod）

 CustomOrder　は　"〇〇,■■,△△" と与える必要がある。つまり、最初に " 、最後にも " 。
 一方、変数 lst は　〇〇,■■,△△　なので、Addメソッド上は　前後を " で囲む必要があるようだね。

 """" & lst & """",

 (ぶらっと)

 Key:=ws2.Range("BF2:BF" & lRow),  ????

 ぶらっと様

 ありがとうございます。別のサイトで　"" が必要、と書いてあるところがあったのでそれも試していたのですが、
 "" の括り方が間違っていたようです。
 （ """ & lst & """ としていました）

 ところで、それでエラーは出なくなったのですが、一つ疑問点が…

 並べ替えの基準になる「参照用」のシートには

 　　J
 1　分類
 2　製品-BB
 3  製品-AB
 4　商品-AC
 5　商品-BB
 　　　：

 このように並んでいるので、変数 lst は「J2セルから最終行まで」ということで

    With Sheets("参照用")　　　　'J列に「分類」を2行目から入力（1行目は見出し）
        lst = Join(WorksheetFunction.Transpose(.Range("J2", .Range("J" & .Rows.Count).End(xlUp))), ",")
    End With

 こうして、前述のように lst のポップアップには2行目の　「製品-BB」からがちゃんと表示されたのですが、
 なぜかこのままでは2行目の「製品-BB」が並び替えの先頭になりませんでした。
 （並び替えの規則に入らないということなのか、最後の部分に集計されました）

 そこで、ダメ元で

    With Sheets("参照用")　　　　'J列に「分類」を2行目から入力（1行目は見出し）
        lst = Join(WorksheetFunction.Transpose(.Range("J1", .Range("J" & .Rows.Count).End(xlUp))), ",")
    End With

 このように、変数 lst をJ1セル（見出し）からにしたところ、「製品-BB」が先頭になりました。

 上記の lst に値を入れる部分にブレークポイントを置いてコードを見ると、lst には見出しの「分類」という文字列も入っています。

 一応この状態で思い通りの並び順にはなっているのですが、どうにも「見出し」まで含めなければいけないというのが腑に落ちなくて…

 何故このようになるかお分かりになりましたらお教えください

 （シマリス）

 確かに！！

 今まで、あぁ、こんなことができるようになったんだと知識として持っていただけで実際には
 ためしたことがなかった。（ずいぶん、いい加減な回答だった。ペコリ！）

 とにかく、最初の項目が無視されるようだね。これは、絶対にバグのような気がするけど、でも、そうなっているので・・

 こちらで、下手な鉄砲をいくつか打ってみたら、以下でうまくいきそうなので、試してみてくれる？

 """" & lst & """",

 これを

 """," & lst & """",

 (ぶらっと)

 ぶらっと様

 ありがとうございます。

  """," & lst & """",

 これで最初の項目もちゃんと反映されました！

 ありがとうございました

 （シマリス）

（シマリス）さん(ぶらっと) さんありがとうございます

この板はとても参考になりました

実は私の業務の中で「車両管理SYS」から出力される車両運行結果データは入力順で、

車両ごと、運行月日順に整列するため

WinXP+EXCEL2003の時は

　　MxR = .Range("R" & Rows.Count).End(xlUp).Row 　　' 主マスター車両名

    SyuMst = .Range("R6:R" & MxR).Value             　　' 並替順配列へ
     　　　　
　　Application.AddCustomList ListArray:=SyuMst         ' 並替アレイ

    LsitNum = Application.GetCustomListNum(SyuMst)

として並べ替える主キーを別シートに記載し、それを配列へ入れマクロ記録した

ソート本体へ組み込み希望通りの整列結果を得ておりました。

しかし、Win7+EXCEL2010で動作させると、結果はこれまで通り出るのに何と、

保存しようとすると暴走しEXCEL再起動になるのです、悩んだ末、結局古典的な

ソート基準列に希望ソート語を書き込み、その列+月日順で結果を得ておりました。

今回のお二人のQ&A　結果を組み込み、

別シートに希望する「車両リスト」

２シートに車両単位+月日順　に整列した車両走行管理表を作成

それを元に、車両ごと走行距離、給油管理、高速道路利用、

などEXCEL2010用車両運行分析が出来上がりました。

やはり結果は同じでもスマートなVBAにしないと気になってしょうがないものですね。

お二人に感謝！　感謝！　です

(asami)

（asami) さんと同じく、以下のVBAで任意の並び替えをしていましたが、
Win7+EXCEL2010に変更になり、マクロ実行後に保存ができなくなりました。
（シマリス）さん(ぶらっと) さんの投稿で解決できると思い、
VBAの変更をしましたが、うまく動作しません。
以下のWinXP+EXCEL2003時のVBAで変更を教えていただけると助かります。

　・「参照用シート」の「セルA5～A15」に任意の並び替えを記入
　・「累計シート」の「セルA1～C100」を任意の設定で並び替える

　Application.AddCustomList ListArray:=Sheets("参照用").Range("A5:A15")
　
　X = Application.CustomListCount

  Worksheets("累計").Activate
  Worksheets("累計").Range("A1:C100) _
           .Sort Key1:=Range("A1"), _
                 Key2:=Range("B1"), Header:=xlYes, OrderCustom:=X + 1

　Application.DeleteCustomList (Application.CustomListCount)

（三毛猫） 2014/03/02(日) 15:19

 どう変更したかを提示した方が今後の為だとは思いますが、上の内容を参照してみたサンプルです。

 Sub Sample()
    With Worksheets("累計")
        .Sort.SortFields.Clear
        .Sort.SortFields.Add Key:=.Range("A2"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        .Sort.SortFields.Add Key:=.Range("B2"), SortOn:=xlSortOnValues, Order:=xlAscending, _
            CustomOrder:="""," & Join(Application.Transpose(Worksheets("参照用").Range("A5:A15")), ",") & """", DataOption:=xlSortNormal
        With .Sort
            .SetRange Worksheets("累計").Range("A1:C100")
            .Header = xlYes
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
    End With
 End Sub
（Mook） 2014/03/02(日) 20:54

（Mook）さん、投稿ありがとうございます。
正直、このVBAでは任意の並び替えができませんでしたが、
（Mook）さんのVBAの記述で（シマリス）さん(ぶらっと) さんのVBAが理解できました。
Win7+EXCEL2010で無事にマクロ実行⇒保存ができました。
ありがとうございます。

（三毛猫） 2014/03/04(火) 01:08

コメント返信：

[ 一覧(最新更新順) ]

[[20130510092122]] 『VBAで並べ替えの順序を任意に指定したい』（シマリス） ページの最後に飛ぶ

[[20130510092122]]　『VBAで並べ替えの順序を任意に指定したい』（シマリス）　ページの最後に飛ぶ