エクセルの住所一覧表があります。

Aには都道府県名
Bには市区町村名
Cにはその後の番地等が入っています。

下記のように都道府県の一群のあとに
空の行を挿入したいです。

北海道 ○○市○~
北海道 ○○○○市○~
北海道 ○○市○~

青森県 ○市~

宮城県 ○○市~
宮城県 ○○○市~


また都道府県の並びがバラバラで、
通常の使われる都道府県の順番に
並び替えをして、都道府県ごとに空の行を
入れることも可能でしょうか?

さらに発展させて
市区町村ごとで空の行を入れることも可能でしょうか?
どうぞよろしくお願いいたします。

回答の条件
  • 1人2回まで
  • 登録:2009/06/24 01:00:47
  • 終了:2009/06/28 13:19:49

ベストアンサー

id:Mook No.4

Mook回答回数1312ベストアンサー獲得回数3912009/06/24 21:18:42

ポイント200pt

これまでの回答で解決しているかもしれませんが、ソート、空白行の挿入を一括して処理するマクロの例です。


Option Explicit

Sub separateAddress()
    Dim dstWS As Worksheet
    Set dstWS = ActiveSheet

    Dim prefs As Variant
'★ この順番でソート
    prefs = Array("北海道", "青森県", "岩手県", "宮城県", "秋田県", "山形県", "福島県", _
        "茨城県", "栃木県", "群馬県", "埼玉県", "千葉県", "東京都", "神奈川県", _
        "新潟県", "富山県", "石川県", "福井県", "山梨県", "長野県", "岐阜県", _
        "静岡県", "愛知県", "三重県", "滋賀県", "京都府", "大阪府", "兵庫県", _
        "奈良県", "和歌山県", "鳥取県", "島根県", "岡山県", "広島県", "山口県", _
        "徳島県", "香川県", "愛媛県", "高知県", "福岡県", "佐賀県", "長崎県", _
        "熊本県", "大分県", "宮崎県", "鹿児島県", "沖縄県")
    
    Dim tmpWs As Worksheet
    Set tmpWs = Worksheets.Add(after:=Worksheets(Worksheets.Count))
    
    Dim r As Long
    For r = 1 To 47
        tmpWs.Cells(r, "A").Value = prefs(r - 1)
        tmpWs.Cells(r, "B").Value = r
    Next
        
    ActiveWorkbook.Names.Add Name:="PREFLIST", RefersTo:="=" & tmpWs.Range("A1:B47").Address
    
'★ 県名順で並べ替え
    dstWS.Columns(1).Insert
    Dim lastRow As Long
    lastRow = dstWS.Range("B" & Rows.Count).End(xlUp).Row
    dstWS.Range("A1").Resize(lastRow, 1).FormulaR1C1 = "=VLOOKUP(RC[1],PREFLIST,2,FALSE)"
    dstWS.Activate
    dstWS.Range("A1:F" & lastRow).Sort _
        key1:=Range("A1"), order1:=xlAscending, _
        key2:=Range("C1"), order2:=xlAscending, _
        Header:=xlGuess
    dstWS.Columns(1).Delete
        
'★ 県で空白行を挿入
    For r = lastRow To 2 Step -1
        If Cells(r, "A").Value <> Cells(r - 1, "A").Value Then
'★ 市で空白行を挿入の場合は上の行を下のように変更
'       If Cells(r, "B").Value <> Cells(r - 1, "B").Value Then
            Rows(r).Insert
        End If
    Next
    Application.DisplayAlerts = False
'★ 次行を削除すれば、剣の順番が確認できます。
'★ その場合、再度実行する場合は最終シートを削除してください。
    tmpWs.Delete
    Application.DisplayAlerts = ture
End Sub

都道府県は作業用のシートを作成して、そこでテーブルを作り、VLOOKUPで順番をつけています。

作業用のシートは処理後に削除していますが、コメントのように最後から3行目を削除すると、

どのような順番でソートしているか確認できます。

id:ohtsu6

ソート設定までしていただき

どうもありがとうございます。

2009/06/28 13:18:55

その他の回答(3件)

id:masterindex No.1

masterindex回答回数5ベストアンサー獲得回数02009/06/24 02:48:55

ポイント100pt

エクセルと、正規表現で置換可能なテキストエディタ(ex.秀丸/Terapad等)があれば可能です。

-------

1.D列に以下を追加してD列最後の行までコピー

=if(A1=A2,"","A_Kaigyou")

2.すべての文字列を選択して、テキストエディタに貼付ける。

3.テキストエディタで以下の置換を実施

 ※エクセルでも出来るかもしれません

検索する文字列:A_Kaigyou

置換する文字列:¥n

4.エクセルに戻って貼付け

-------

市区町村ごとで空の行を入れたいというリクエストですが、

上記1の手順で下記で改行すべきところが出てきます

=if(B1=B2,"","B_Kaigyou")

あとは3の手順で

検索する文字列:B_Kaigyou

置換する文字列:¥n

とやってエクセルに張ればいけそうです。

都道府県の並びがバラバラということで、

一度並び替えておく必要がありそうですね。

id:ohtsu6

ありがとうございます。

できました。

「B1=B2」の比較も参考となりました。

ちなみにエクセル内で完結する方法もありますでしょうか?

(D列にA_Kaigyouが入っていたら

その下に行を追加するみたいな・・・)

どうぞよろしくお願いいたします。

2009/06/24 08:39:38
id:masterindex No.2

masterindex回答回数5ベストアンサー獲得回数02009/06/24 11:25:24

出来たようで、よかったです!

エクセル内で完結する方法は、

マクロを使わないと難しいかもしれません。

ちょっと手元にエクセルが無くて試せませんが、

以下のアドインを使ってエクセルで正規表現を使える様にできれば、

改行できるかもしれません。

http://srcedit.pekori.jp/tool/excelre.html

しかしこの場合、テキストで言う¥n = 改行がエクセルで言う行の挿入になるのか、

ちょっと未確認で微妙なところです。。

id:pahoo No.3

pahoo回答回数5960ベストアンサー獲得回数6332009/06/24 13:21:50

ポイント200pt

Excel VBA(マクロ)を使えば、こんなプログラムで処理できます。


'都道府県名の間に空行を入れる
Sub hoge1()
    Dim rn As Integer
    Dim a0 As String

    a0 = Cells(1, 1)
    rn = 2
    While Cells(rn, 1) <> ""
        If (Cells(rn, 1) <> a0) Then
            rr = rn & ":" & rn
            Rows(rr).Insert
            rn = rn + 1
        End If
        a0 = Cells(rn, 1)
        rn = rn + 1
    Wend
End Sub

市区町村ごとで空の行を入れるには、Cells(1,1)やCells(rn,1)をCells(1,2)やCells(rn,2)に置き換えてください。


都道府県の並びがバラバラの場合は、最初に並べ替えをする必要があります。

Sortメソッドを使えばできますが、文字コードの順に並び替えてしまいます。

指定順に並べ替える」の方が参考になるでしょう。

id:ohtsu6

出来ました。

どうもありがとうございます。

2009/06/28 13:16:05
id:Mook No.4

Mook回答回数1312ベストアンサー獲得回数3912009/06/24 21:18:42ここでベストアンサー

ポイント200pt

これまでの回答で解決しているかもしれませんが、ソート、空白行の挿入を一括して処理するマクロの例です。


Option Explicit

Sub separateAddress()
    Dim dstWS As Worksheet
    Set dstWS = ActiveSheet

    Dim prefs As Variant
'★ この順番でソート
    prefs = Array("北海道", "青森県", "岩手県", "宮城県", "秋田県", "山形県", "福島県", _
        "茨城県", "栃木県", "群馬県", "埼玉県", "千葉県", "東京都", "神奈川県", _
        "新潟県", "富山県", "石川県", "福井県", "山梨県", "長野県", "岐阜県", _
        "静岡県", "愛知県", "三重県", "滋賀県", "京都府", "大阪府", "兵庫県", _
        "奈良県", "和歌山県", "鳥取県", "島根県", "岡山県", "広島県", "山口県", _
        "徳島県", "香川県", "愛媛県", "高知県", "福岡県", "佐賀県", "長崎県", _
        "熊本県", "大分県", "宮崎県", "鹿児島県", "沖縄県")
    
    Dim tmpWs As Worksheet
    Set tmpWs = Worksheets.Add(after:=Worksheets(Worksheets.Count))
    
    Dim r As Long
    For r = 1 To 47
        tmpWs.Cells(r, "A").Value = prefs(r - 1)
        tmpWs.Cells(r, "B").Value = r
    Next
        
    ActiveWorkbook.Names.Add Name:="PREFLIST", RefersTo:="=" & tmpWs.Range("A1:B47").Address
    
'★ 県名順で並べ替え
    dstWS.Columns(1).Insert
    Dim lastRow As Long
    lastRow = dstWS.Range("B" & Rows.Count).End(xlUp).Row
    dstWS.Range("A1").Resize(lastRow, 1).FormulaR1C1 = "=VLOOKUP(RC[1],PREFLIST,2,FALSE)"
    dstWS.Activate
    dstWS.Range("A1:F" & lastRow).Sort _
        key1:=Range("A1"), order1:=xlAscending, _
        key2:=Range("C1"), order2:=xlAscending, _
        Header:=xlGuess
    dstWS.Columns(1).Delete
        
'★ 県で空白行を挿入
    For r = lastRow To 2 Step -1
        If Cells(r, "A").Value <> Cells(r - 1, "A").Value Then
'★ 市で空白行を挿入の場合は上の行を下のように変更
'       If Cells(r, "B").Value <> Cells(r - 1, "B").Value Then
            Rows(r).Insert
        End If
    Next
    Application.DisplayAlerts = False
'★ 次行を削除すれば、剣の順番が確認できます。
'★ その場合、再度実行する場合は最終シートを削除してください。
    tmpWs.Delete
    Application.DisplayAlerts = ture
End Sub

都道府県は作業用のシートを作成して、そこでテーブルを作り、VLOOKUPで順番をつけています。

作業用のシートは処理後に削除していますが、コメントのように最後から3行目を削除すると、

どのような順番でソートしているか確認できます。

id:ohtsu6

ソート設定までしていただき

どうもありがとうございます。

2009/06/28 13:18:55
  • id:Mook
    お気づきかもしれませんが、最後から2行目は
      Application.DisplayAlerts = True
    です。

    土壇場でつけたしたら、Typo してしまいました。

この質問への反応(ブックマークコメント)

「あの人に答えてほしい」「この質問はあの人が答えられそう」というときに、回答リクエストを送ってみてましょう。

これ以上回答リクエストを送信することはできません。制限について

絞り込み :
はてなココの「ともだち」を表示します。
回答リクエストを送信したユーザーはいません