Excel VBA質問箱 IV

当質問箱は、有志のボランティア精神のおかげで成り立っています。
問題が解決したら、必ずお礼をしましょうね。
本サイトの基本方針をまとめました。こちら をご一読ください。

投稿種別の選択が必要です。ご注意ください。
迷惑投稿防止のため、URLの入力を制限しています。ご了承ください。

  新規投稿 ┃ツリー表示 ┃一覧表示 ┃トピック表示 ┃検索 ┃設定 ┃過去ログ ┃ホーム  
6105 / 13646 ﾂﾘｰ ←次へ　｜　前へ→
▼ 【47184】都道府県名を含むをソートできない うまくいかない 07/3/4(日) 9:21 質問[未読]
【47185】Re:都道府県名を含むをソートできない かみちゃん 07/3/4(日) 10:22 発言[未読]
【47186】Re:都道府県名を含むをソートできない うまくいかない 07/3/4(日) 11:05 発言[未読]
【47187】Re:都道府県名を含むをソートできない かみちゃん 07/3/4(日) 11:21 発言[未読]
【47188】Re:都道府県名を含むをソートできない うまくいかない 07/3/4(日) 11:39 発言[未読]
【47189】Re:都道府県名を含むをソートできない かみちゃん 07/3/4(日) 14:03 発言[未読]
【47191】Re:都道府県名を含むをソートできない inoue 07/3/4(日) 14:11 発言[未読]
【47190】Re:都道府県名を含むをソートできない inoue 07/3/4(日) 14:06 発言[未読]
【47192】Re:都道府県名を含むをソートできない Kein 07/3/4(日) 15:06 回答[未読]
【47196】Re:都道府県名を含むをソートできない ichinose 07/3/4(日) 17:16 発言[未読]
【47198】Re:都道府県名を含むをソートできない うまくいかない 07/3/4(日) 21:10 お礼[未読]
【47184】都道府県名を含むをソートできない
質問  うまくいかない  - 07/3/4(日) 9:21 -
引用なし
パスワード
   ＶＢＡでArray関数を利用し、Array("北海道","青森県",・・・)として
ソートをする場合、都道府県名だけならソートできるのですが、「北海道
札幌市・・・」という場合は、思い通りのソートが出来ません。
ちなみに、Array("北海道*","青森県*",・・・)としてみても同様の結果
です。どのたか、「〜を含む」または「〜始まる」で指定どおりのソート
をする方法を教えてもらえませんか。
【47185】Re:都道府県名を含むをソートできない
発言  かみちゃん  - 07/3/4(日) 10:22 -
引用なし
パスワード
   こんにちは。かみちゃん　です。

>「北海道札幌市・・・」という場合は、思い通りのソートが出来ません。

どういう状態でどのようになったら思い通りのソートになるのかを教えていただけませんか？
ちなみに、都道府県名だけなら、どのようにしたらソートできたのかも合わせて教えていただきたいと思います。
【47186】Re:都道府県名を含むをソートできない
発言  うまくいかない  - 07/3/4(日) 11:05 -
引用なし
パスワード
   ▼かみちゃん さん：
Array("北海道", "青森県", "岩手県", "宮城県", "秋田県", "山形県", "福島県", "茨城県", "栃木県", "群馬県", "埼玉県", "新潟県", "長野県", "千葉県", "東京都", "神奈川県", "山梨県", "富山県", "石川県", "福井県", "岐阜県", "静岡県", "愛知県", "三重県", "滋賀県", "京都府", "大阪府", "兵庫県", "奈良県", "和歌山県", "鳥取県", "島根県", "岡山県", "広島県", "山口県", "徳島県", "香川県", "愛媛県", "高知県", "福岡県", "佐賀県", "長崎県", "熊本県", "大分県", "宮崎県", "鹿児島県", "沖縄県")
の順でソートするようにしています。
都道府県名だけならArray関数登録通りに並びますが住所データには市町村名等
が入っているため、思うとおりに並びません。
したいことは、住所データを上記の都道府県順にソートしたいということです。
【47187】Re:都道府県名を含むをソートできない
発言  かみちゃん  - 07/3/4(日) 11:21 -
引用なし
パスワード
   こんにちは。かみちゃん　です。

>住所データには市町村名等が入っているため、思うとおりに並びません。
>住所データを上記の都道府県順にソートしたい

どういう状態でどのようになったらいいのか？
がわからないと何もアドバイスはできません。

そもそも市町村別にシートが分かれているのですか？
それは、スゴイシート数になりませんか？
あと、ひとつひとつのシート名がどこからどこまでが都道府県名でどこからが市町村名かがExcelに理解できると思いますか？

北海道札幌市
北海道苫小牧市
神奈川県横浜市
神奈川県相模原市
東京都武蔵野市
東京都日野市

上記のデータをソートすると、北海道、神奈川県、東京都は塊になると思いますが。
【47188】Re:都道府県名を含むをソートできない
発言  うまくいかない  - 07/3/4(日) 11:39 -
引用なし
パスワード
   ▼かみちゃん さん：

普通にソートすると下のようになってしまいます。
　(Ａ列)
北海道札幌市・・・
東京都武蔵野市・・・
神奈川県横浜市・・・
福岡県福岡市・・・
兵庫県神戸市・・・
大阪府大阪市・・・

ソートして下のように都道府県順(都道府県コード順)にならぶようにしたい。
　(Ａ列)
北海道札幌市・・・
東京都武蔵野市・・・
神奈川県横浜市・・・
大阪府大阪市・・・
兵庫県神戸市・・・
福岡県福岡市・・・

当初はＢ列に都道府県名を取り出し、質問のArray関数を利用していましたが
一度に行う方法はないかと思っています。
【47189】Re:都道府県名を含むをソートできない
発言  かみちゃん  - 07/3/4(日) 14:03 -
引用なし
パスワード
   こんにちは。かみちゃん　です。

まず、私、思い切り勘違いをしていました。
シート云々は一切関係ありませんでした。
申し訳ありません。

>ソートして下のように都道府県順(都道府県コード順)にならぶようにしたい。

その前に確認ですが、
> ＶＢＡでArray関数を利用し、Array("北海道","青森県",・・・)として
> ソートをする場合、都道府県名だけならソートできるのですが
とおっしゃっていて、さらに

> 順でソートするようにしています。
> 都道府県名だけならArray関数登録通りに並びます

とのことですが、どのようにして並べ替えているのか教えていただけませんか？
【47190】Re:都道府県名を含むをソートできない
発言  inoue  - 07/3/4(日) 14:06 -
引用なし
パスワード
   >普通にソートすると下のようになってしまいます。
>　(Ａ列)
>北海道札幌市・・・
>東京都武蔵野市・・・
>神奈川県横浜市・・・
>福岡県福岡市・・・
>兵庫県神戸市・・・
>大阪府大阪市・・・
このスレから貼り付けると
神奈川県横浜市・・・
大阪府大阪市・・・
東京都武蔵野市・・・
福岡県福岡市・・・
兵庫県神戸市・・・
北海道札幌市・・・
となりますね。

>当初はＢ列に都道府県名を取り出し、質問のArray関数を利用していましたが
>一度に行う方法はないかと思っています。
Array配列を持っているのなら、
各行の住所の先頭がArray配列のどれと一致するかでその「何番目」の値を
Ｂ列に放り込んでソートすれば済むのではないでしょうか。
これなら別にユーザー設定リストを作らなくても可能です。
但し、１行ずつ調べて「何番目」の値をB列にセットするループ処理を
書く必要があります。
【47191】Re:都道府県名を含むをソートできない
発言  inoue  - 07/3/4(日) 14:11 -
引用なし
パスワード
   かみちゃんさん、カブってしまいました。

> どのようにして並べ替えているのか
オプションの「ユーザー設定リスト」に都道府県名のリストを作って、
並べ替えのオプションの「並べ替え順序の指定」で
その都道府県名のリストを使っているのだと思います。

この操作を記録すると、
Application.AddCustomList ListArray:=Array("北海道", "青森県", _
　"岩手県", "宮城県", "秋田県", "山形県", "福島県", "茨城県", _
　"栃木県", "群馬県", "埼玉県", "千葉県", "東京都", "神奈川県", _
　"新潟県", "富山県", "石川県", "福井県", "山梨県", "長野県", _
　"岐阜県", "静岡県", "愛知県", "三重県", "滋賀県", "京都府", _
　"大阪府", "兵庫県", "奈良県", "和歌山県", "鳥取県", "島根県", _
　"岡山県", "広島県", "山口県", "徳島県", "香川県", "愛媛県", _
　"高知県", "福岡県", "佐賀県", "長崎県", "熊本県", "大分県", _
　"宮崎県", "鹿児島県", "沖縄県")
というコードが途中に出てきます。
【47192】Re:都道府県名を含むをソートできない
回答  Kein  - 07/3/4(日) 15:06 -
引用なし
パスワード
   inoueさんの方法と同じですが、作業列を挿入し数式を入れて一気に番号を付与し、
それをキーにして並べ替える。というコードを組んでみました。

Sub MySort()
　 Const Municipality As String = _
　 "{""北海道"",""青森県"",""岩手県"",""宮城県"",""秋田県""," & _
　 """山形県"",""福島県"",""茨城県"",""栃木県"",""群馬県"",""埼玉県""," & _
　 """新潟県"",""長野県"",""千葉県"",""東京都"",""神奈川県"",""山梨県""," & _
　 """富山県"",""石川県"",""福井県"",""岐阜県"",""静岡県"",""愛知県""," & _
　 """三重県"",""滋賀県"",""京都府"",""大阪府"",""兵庫県"",""奈良県""," & _
　 """和歌山県"",""鳥取県"",""島根県"",""岡山県"",""広島県"",""山口県""," & _
　 """徳島県"",""香川県"",""愛媛県"",""高知県"",""福岡県"",""佐賀県""," & _
　 """長崎県"",""熊本県"",""大分県"",""宮崎県"",""鹿児島県"",""沖縄県""}"
　 Application.ScreenUpdating = False
　 Range("B:B").Insert xlShiftToRight
　 With Range("A1", Range("A65536").End(xlUp))
　　　.Offset(, 1).Formula = _
　　　"=MATCH(LEFT($A1,((MID($A1,4,1)=""県"")+3))," & _
　　　Municipality & ",0)"
　　　.Resize(, 2).Sort Key1:=Columns(2), Order1:=xlAscending, _
　　　Header:=xlNo, Orientation:=xlSortColumns
　 End With
　 Range("B:B").Delete xlShiftToLeft
　 Application.ScreenUpdating = True
End Sub
【47196】Re:都道府県名を含むをソートできない
発言  ichinose  - 07/3/4(日) 17:16 -
引用なし
パスワード
   こんばんは。

>当初はＢ列に都道府県名を取り出し、質問のArray関数を利用していましたが
>一度に行う方法はないかと思っています。

Ｂ列を作業領域として、キーを配置し、これを基にソートする・・、

これが一番簡単だと思いますし、Excelを使うならこの方法を使わないと
勿体無いですよ！！

とお断りした上で・・・、この作業領域を別の場所に置く方法です。

標準モジュールに
'================================================================
Sub main()
　　Dim g0 As Long
　　Dim myarray As Variant
　　Call sort_set_list(Array("北海道", "青森県", _
　　　　　　"岩手県", "宮城県", "秋田県", "山形県", "福島県", "茨城県", _
　　　　　　"栃木県", "群馬県", "埼玉県", "千葉県", "東京都", "神奈川", _
　　　　　　"新潟県", "富山県", "石川県", "福井県", "山梨県", "長野県", _
　　　　　　"岐阜県", "静岡県", "愛知県", "三重県", "滋賀県", "京都府", _
　　　　　　"大阪府", "兵庫県", "奈良県", "和歌山", "鳥取県", "島根県", _
　　　　　　"岡山県", "広島県", "山口県", "徳島県", "香川県", "愛媛県", _
　　　　　　"高知県", "福岡県", "佐賀県", "長崎県", "熊本県", "大分県", _
　　　　　　"宮崎県", "鹿児島", "沖縄県"))
　　g0 = 1
　　Do Until Cells(g0, 1).Value = ""
　　　 Call sort_put_ele(Left(Cells(g0, 1).Value, 3), Cells(g0, 1).Value)
　　　 g0 = g0 + 1
　　　 Loop
　　g0 = 1
　　myarray = sort_get_array(True)
　　Do While TypeName(myarray) <> "Boolean"
　　　 Range(Cells(g0, 1), Cells(g0 + UBound(myarray) - 1, 1)).Value = _
　　　　　　　　　Application.Transpose(myarray)
　　　 g0 = g0 + UBound(myarray)
　　　 myarray = sort_get_array()
　　　 Loop
　　Call sort_term
End Sub


別の標準モジュールに
'===================================================================
Option Explicit
Private sdic As object
'===================================================================
Sub sort_set_list(myarray As Variant)
　　Dim sitem() As Variant
　　Dim g0 As Long
　　Set sdic = CreateObject("scripting.dictionary")
　　For g0 = LBound(myarray) To UBound(myarray)
　　　 sdic.Add myarray(g0), sitem()
　　　 Next
End Sub
'===================================================================
Sub sort_put_ele(skey As Variant, sdata As Variant)
　　Dim idx As Long
　　Dim sarray As Variant
　　If sdic.Exists(skey) Then
　　　 sarray = sdic.Item(skey)
　　　 On Error Resume Next
　　　 idx = UBound(sarray) + 1
　　　 If Err.Number <> 0 Then idx = 1
　　　 ReDim Preserve sarray(1 To idx)
　　　 sarray(idx) = sdata
　　　 sdic.Item(skey) = sarray
　　　 On Error GoTo 0
　　　 End If
End Sub
'===================================================================
Function sort_get_array(Optional stt As Boolean = False) As Variant
　　Static sidx As Long
　　Static skey As Variant
　　Dim wk As Long
　　If stt Then
　　　 skey = sdic.Keys
　　　 sidx = LBound(skey)
　　　 End If
　　On Error Resume Next
　　sort_get_array = False
　　Do While sidx <= UBound(skey)
　　　 sort_get_array = sdic.Item(skey(sidx))
　　　 sidx = sidx + 1
　　　 Err.Clear
　　　 wk = UBound(sort_get_array)
　　　 If Err.Number <> 0 Then
　　　　　sort_get_array = False
　　　 Else
　　　　　Exit Do
　　　　　End If
　　　 Loop
　　 On Error GoTo 0
End Function
'===================================================================
Sub sort_term()
　　Set sdic = Nothing
End Sub


として、対象シートをアクティブにしてmainを実行してください。

尚、データはセルＡ１から入っているとします。
【47198】Re:都道府県名を含むをソートできない
お礼  うまくいかない  - 07/3/4(日) 21:10 -
引用なし
パスワード
   返事が遅くなりました。

Keinさん
Match関数を使用するとは思いも付きませんでした。

ichinose さんは、Dictionaryを使用されていますね。
集計等に使用しているのを他のスレで見ましたが、今ひとつ
理解できません。

お二方のコードをコピーして試してみたら思っていた通りになりました。
ありがとうございました。
参考にして工夫してみます。
  新規投稿 ┃ツリー表示 ┃一覧表示 ┃トピック表示 ┃検索 ┃設定 ┃過去ログ ┃ホーム  
6105 / 13646 ﾂﾘｰ ←次へ　｜　前へ→
ページ：  ┃  記事番号：
2610219
(SS)C-BOARD v3.8 is Free