Excel で住所を通り名や番号順に並べ替えるには、どうすればよいですか？

著者Sun変更日2025-08-08

Excel で住所リストを管理する際、通り名や番地で並べ替えてデータを整理・分析したい場面はよくあります。たとえば、同じ通りに住む顧客をグループ化したい場合や、番地順に配達処理を行いたい場合などが該当します。しかし、一般的な住所フォーマットでは、通り名と番地が1 つのセル内に混在しているため、単純な並べ替えでは期待通りの結果が得られません。本記事では、Excel で住所を通り名または番地で並べ替える実用的な方法を紹介し、それぞれのメリットと適用シナリオを解説するとともに、多様なユーザーのニーズに対応するトラブルシューティングと代替ソリューションを提供します。

Excel でヘルパー列を使用して住所を通り名で並べ替える

Excel でヘルパー列を使用して住所を番号で並べ替える

VBA を使用して通り名または番号を自動抽出し、住所を並べ替える

Power Query を使用してヘルパー列なしで住所を通り名または番号で並べ替える

Excel でヘルパー列を使用して住所を通り名で並べ替える

素晴らしい！Excel でChrome、Edge、Firefox、Safari のような効率的な Tabs をご利用いただけます！
毎日数千回のマウスクリックを削減し、50% もの時間を節約できます！

Excel で住所を通り名で並べ替えるには、まずヘルパー列に通り名だけを抽出する必要があります。この方法は、「123 Apple St」のように住所のフォーマットが一貫している場合に効果的で、簡単なプロジェクトやシンプルなアドレスリストに最適です。

1。住所リストの隣にある空の列を選択し、ヘルパー列の先頭セルに以下の式を入力して、通り名を抽出します。

=MID(A1,FIND(" ",A1)+1,255)

（ここで A1 は住所データの上のセルを指します。データの開始位置が異なる場合は、適宜調整してください。）
式を入力したら、Enterキーを押し、その後フィルハンドルを下にドラッグして、住所範囲内のすべての行に式を適用します。この式は、各住所の最初のスペースを見つけ、その後ろにあるすべての文字（通り名と接尾辞）を返します。住所が同じ構造に従っていることをご確認ください。そうでない場合、式が期待通りに分割されない可能性があります。

数式を使用して住所を通り名で並べ替えるスクリーンショット

2。抽出した通り名が含まれるヘルパー列全体をハイライトし、データタブに移動して昇順をクリックすると、通り名がアルファベット順に並べ替えられます。

数式を使用して住所を通り名で並べ替える手順2：並べ替えのスクリーンショット

3。表示される並べ替えの警告ダイアログボックスで、選択範囲を拡張するを選択して、並べ替え時に住所情報全体がまとめて維持されるようにしましょう。

数式を使用して住所を通り名で並べ替える手順3：選択範囲を拡張するスクリーンショット

4。並べ替えをクリックすると、アドレスリストが通り名に基づいて再並べ替えされ、同じ通りの住所がまとめて表示されます。

数式を使用して住所を通り名で並べ替えた結果のスクリーンショット

注：この方法は、標準化された住所フォーマットで最も効果を発揮します。住所セルに不規則なパターンや、通り名の前に複数のスペースが含まれている場合は、式を調整する必要があります。式を使用した後は、必ずいくつかの結果を確認し、正確性を検証してください。

メリット：シンプルで、追加ツールが不要です。
デメリット：フォーマットの一貫性に依存するため、住所の形式が異なる場合は追加作業が必要です。

Excel でヘルパー列を使用して住所を番号で並べ替える

配達順の割り当てや隣接住所の特定など、住所リストを番地（数値）で並べ替える必要がある場合は、その番地から数値を抽出して並べ替えに利用するのが簡単です。この方法は、異なる通りにある住所であっても効果的です。

1。アドレスリストの隣の空白セルに、以下の式を入力して番地を抽出してください。

=VALUE(LEFT(A1,FIND(" ",A1)-1))

（A1 はリストの最初の住所を指します。必要に応じて調整してください。）入力後、Enterキーを押すと、この式が最初のスペースの位置を特定し、その前の文字を数値として返します。住所の先頭に番地が記載されている場合、この式は正しく機能します。その後、フィルハンドルを下にドラッグして、リストの残りのセルにも式を適用してください。

数式2を使用して住所を通り名で並べ替えるスクリーンショット

2。作成したヘルパー列を選択し、データタブに移動して、昇順（またはExcel の新しいバージョンでは)並べ替え最小から最大へ）をクリックします。

数式2を使用して住所を通り名で並べ替える手順2：並べ替えのスクリーンショット

3。表示される並べ替えの警告ダイアログで、選択範囲を拡張するを選択して、行全体を並べ替えましょう。

数式2を使用して住所を通り名で並べ替える手順3：選択範囲を拡張するスクリーンショット

4。並べ替えをクリックして適用します。これで、住所が抽出された番地順に並べ替えられます。

数式2を使用して住所を通り名で並べ替えた結果のスクリーンショット

ヒント：番地をテキストとして保持したい場合や、数値での並べ替えが不要な場合は、以下の式もご利用いただけます。

=LEFT(A1,FIND(" ",A1)-1)

このバージョンでは、数値がテキスト文字列として抽出されます。

注意点：「Main Street5」のように、住所が数字ではなく単語で始まる場合には、これらの式は期待通りに動作しません。式を使用する前に、必ず住所データをご確認ください。

メリット：住所フォーマットがシンプルな場合、素早く簡単に使えます。
デメリット：番地の前に建物名や接尾辞が含まれる住所や、複数の数字を含む住所には対応できません。

VBA コード – マクロで通り名/番号を自動抽出し、住所リストを自動的に並べ替える

大規模で複雑なアドレスリストや、住所の構造が可変的なデータを扱う際には、VBA を使って並べ替えプロセスを自動化するのが非常に効果的です。VBA を利用すれば、通り名や番号を素早く抽出し、アドレスリストを自動的に並べ替えて手動操作を最小限に抑えられます。このソリューションは、定期的な並べ替えが必要な場合や、ワークフローへの並べ替え機能の統合を検討している場面に最適です。

注：この VBA マクロは、A 列の各住所から最初のスペース以降の部分（通り名）を抽出し、その名前をもとにリスト全体を並べ替えます。わずかな調整で、番地を抽出して数値順に並べ替えることも可能です。

1。開発タブのVisual Basicをクリックします。表示されたウィンドウで、挿入から標準モジュールをクリックし、以下の VBA コードをモジュールウィンドウに貼り付けます。

Sub SortAddressesByStreetName()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim tempCol As Long
    Dim i As Long
    
    On Error Resume Next
    xTitleId = "KutoolsforExcel"
    
    Set ws = ActiveSheet
    lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
    tempCol = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column + 1
    
    ' Create helper column with street names
    For i = 1 To lastRow
        ws.Cells(i, tempCol).Value = Trim(Mid(ws.Cells(i, 1).Value, InStr(ws.Cells(i, 1).Value, " ") + 1))
    Next i
    
    ' Sort the whole data range by the helper column
    ws.Sort.SortFields.Clear
    ws.Sort.SortFields.Add Key:=ws.Range(ws.Cells(1, tempCol), ws.Cells(lastRow, tempCol)), _
                           SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    
    With ws.Sort
        .SetRange ws.Range(ws.Cells(1, 1), ws.Cells(lastRow, tempCol))
        .Header = xlNo
        .Apply
    End With
    
    ' Delete helper column
    ws.Columns(tempCol).Delete
End Sub

2。コードを実行するには、アドレスリストがアクティブな状態で「実行」またはF5キーを押してください。すると、A 列のアドレスリストが通り名のアルファベット順に並べ替えられます。

このバージョンでは、最初のスペースより前の数字のみを抽出し、数値の昇順で並べ替えます。

トラブルシューティング：
- 住所が A 列にあることを確認するか、データの実際の位置に合わせてコードを更新してください。
- データにヘッダーが含まれる場合は、Header = xlYes を設定して、ヘッダー行が並べ替えられないようにしてください。
- 大量の VBA コードを実行する前に、必ずバックアップを作成してください。

メリット：ヘルパー列が不要で、大規模データセットや繰り返しの並べ替えに最適です。
デメリット：初期設定にはマクロの許可と VBA の基本的な理解が必要です。

その他のExcel 組み込み方法 – Power Query を使用して住所列を分割し、ヘルパー列を使わずに Power Query 内で直接並べ替える

最新版のExcel（Excel 2016 以降および Microsoft 365）で利用可能な Power Query は、番地や通り名などの住所コンポーネントに分割する柔軟かつ数式不要な方法を提供します。このソリューションは、数式やヘルパー列を使わずに済ませたい場合や、基本的な数式では効率的に処理しきれない多様なフォーマットの住所データを扱う際に最適です。さらに、Power Query では操作手順を保存できるため、データが増えていても簡単に更新できます。

1。住所データを選択し、データタブに移動して、テーブル/範囲からを選択します（プロンプトが表示された場合は、テーブルを作成してください）。
2。Power Query ウィンドウで住所列を選択し、列の分割＞区切り文字をクリックします。区切り文字としてスペースを選択し、「分割位置」の種類として最も左側の最初の区切り文字を指定します。
3。これにより、住所が2 つの列（番地とそれ以降の住所／通り名）に分割されます。必要に応じて、新しい列の名前を変更してください。
4。並べ替えるには、通り名または番地の列ヘッダーにある矢印をクリックし、昇順に並べ替えまたは降順に並べ替えを選択します。
5。閉じて読み込むをクリックして、並べ替え後の結果をワークシートに戻します。

追加のヒント：

住所のパターンが一貫していない場合は、Power Query でカスタム分割や変換を使用して列をさらに操作できます。
Power Query の操作手順は自動的に記録されるため、データソースが変更されても簡単にデータを更新できます。
この方法では元のデータが変更されないため、元のレコードをより安全に保てます。

メリット：シートの内容が恒久的に変更されないため安心。複雑な住所パターンにもしっかり対応可能。管理すべき数式も不要。
デメリット：Excel 2016 以降が必要。新規ユーザーにとっては、インターフェースに慣れるまで少し時間がかかるかもしれません。

概要とトラブルシューティングのヒント：
・数式や VBA を適用する前に、住所フォーマットが一貫しているか確認してください。
・ヘルパー列やコードを使用した後は、特に並べ替え結果をプレビューし、正確性をしっかり確認してください。
・想定外の構造のデータ（例：番地や通り名が末尾にない場合など）には、数式を調整するか、より堅牢な分割のために Power Query をご検討ください。
・ VBA や高度なデータツールを使用する前に、定期的にバックアップを取り、誤ったデータ損失を防いでください。
・ご自身のデータ量、Excel のバージョン、およびツールへの習熟度に最も適した方法（数式、VBA、Power Query）をぜひ選んでください。
・どの方法が最適か分からない場合は、多くの場合、Power Query が最も柔軟性が高く、非破壊的な編集にもっとも安全です。

最高の Office 業務効率化ツール

🤖	KUTOOLS AI アシスタント：次に基づいてデータ分析を革新します：インテリジェント実行｜コード生成｜カスタム数式作成｜データ分析とチャート生成｜拡張機能呼び出し…
	人気の機能：検索・ハイライト、または重複をマーキング｜空白行を削除する｜データを失うことなく列の結合またはセルを｜数式を使用しない四捨五入...
	スーパー LOOKUP：複数条件 VLookup ｜複数値 VLookup ｜複数シート間 VLookup ｜ファジーマッチ....
	高度なドロップダウンリスト：ドロップダウンリストをすばやく作成｜連動型ドロップダウンリスト｜複数選択可能なドロップダウンリスト....
	列マネージャー：指定した数の列を追加｜列の移動｜非表示列の表示状態を切り替え｜範囲および列の比較...
	注目の機能：グリッドフォーカス｜デザインビュー｜強化された数式バー｜ワークブックとシートマネージャー｜リソースライブラリ（オートテキスト）｜日付ピッカー｜ワークシートの統合｜暗号化／セルの復号化｜リストからメール送信｜スーパーフィルター｜特殊フィルタ（太字のフォントを持つセルをフィルタリング／斜体／取り消し線。。。）。。。
	トップ15 ツールセット：12 テキストツール（テキストの追加、特定の文字を削除、...）｜ 50+チャートタイプ（ガントチャート、...）｜ 40+実用的関数（誕生日に基づいて年齢を計算します、...）｜ 19 挿入ツール（QR コードを挿入、パスから画像を挿入、...）｜ 12 変換ツール（単語に変換する、為替レートの変換、...）｜ 7 結合と分割ツール（高度な行のマージ、セルの分割、...）｜さらに多数

Kutools はお好みの言語でご利用いただけます。英語、スペイン語、ドイツ語、フランス語、中国語、および40+の他の言語をサポートしています！

Kutools for Excel でExcel スキルを強化し、これまでにない効率を体験しましょう。Kutools for Excel は、生産性を高め、時間を大幅に節約できる高度な機能を300 以上提供します。最も必要な機能を今すぐ入手するにはこちらをクリック。。。

Kutools for Excel の詳細はこちら。。。無料ダウンロード。。。

Office Tab は Office にタブインターフェースをもたらし、作業を大幅に簡単にします

Word、Excel、PowerPoint でタブを使った編集と閲覧を有効にします。Publisher、Access、Visio、Project でもご利用いただけます。
複数のドキュメントを、新しいウィンドウではなく、同じウィンドウ内の新しいタブで開いたり作成したりできます。
日々の生産性を50％も向上させ、毎日数百回ものマウスクリックを削減します！