Excel VBA質問箱 IV

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

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


6104 / 13645 ツリー ←次へ | 前へ→

【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 -

引用なし
パスワード
   VBAで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 -

引用なし
パスワード
   ▼かみちゃん さん:

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

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

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

【47189】Re:都道府県名を含むをソートできない
発言  かみちゃん  - 07/3/4(日) 14:03 -

引用なし
パスワード
   こんにちは。かみちゃん です。

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

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

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

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

とのことですが、どのようにして並べ替えているのか教えていただけませんか?

【47190】Re:都道府県名を含むをソートできない
発言  inoue  - 07/3/4(日) 14:06 -

引用なし
パスワード
   >普通にソートすると下のようになってしまいます。
> (A列)
>北海道札幌市・・・
>東京都武蔵野市・・・
>神奈川県横浜市・・・
>福岡県福岡市・・・
>兵庫県神戸市・・・
>大阪府大阪市・・・
このスレから貼り付けると
神奈川県横浜市・・・
大阪府大阪市・・・
東京都武蔵野市・・・
福岡県福岡市・・・
兵庫県神戸市・・・
北海道札幌市・・・
となりますね。

>当初はB列に都道府県名を取り出し、質問のArray関数を利用していましたが
>一度に行う方法はないかと思っています。
Array配列を持っているのなら、
各行の住所の先頭がArray配列のどれと一致するかでその「何番目」の値を
B列に放り込んでソートすれば済むのではないでしょうか。
これなら別にユーザー設定リストを作らなくても可能です。
但し、1行ずつ調べて「何番目」の値を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 -

引用なし
パスワード
   こんばんは。

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

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

これが一番簡単だと思いますし、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を実行してください。

尚、データはセルA1から入っているとします。

【47198】Re:都道府県名を含むをソートできない
お礼  うまくいかない  - 07/3/4(日) 21:10 -

引用なし
パスワード
   返事が遅くなりました。

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

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

お二方のコードをコピーして試してみたら思っていた通りになりました。
ありがとうございました。
参考にして工夫してみます。

6104 / 13645 ツリー ←次へ | 前へ→
ページ:  ┃  記事番号:
2610219
(SS)C-BOARD v3.8 is Free