Excelで住所を通り名や番号で並べ替えるにはどうすればよいですか？
Excelで住所リストを管理する場合、通り名または通り番号でデータを整理または分析する必要があります。例えば、同じ通りに住むクライアントをグループ化する必要がある場合や、家番号の順に配送を処理する必要がある場合、これらの要素でソートすることが不可欠です。しかし、典型的な住所形式は通り名と番号が一つのセルに混在しているため、単純な並べ替えでは期待される結果を得られません。この記事では、Excelで住所を通り名または通り番号で並べ替えるための実用的な方法について説明し、それぞれの利点や適用場面を分析し、さまざまなユーザーのニーズに対応するトラブルシューティングや代替案を提供します。
VBAを使用して通り名または番号を自動的に抽出し、並べ替える
Power Queryを使用して補助列なしで住所を通り名または番号で並べ替える
Excelで補助列を使用して住所を通り名で並べ替える
Excelで住所を通り名で並べ替えるには、まず通り名だけを補助列に抽出する必要があります。この方法は簡単で、"123 Apple St"のような住所形式が一貫している場合にうまく機能します。これは短期間のプロジェクトや簡単な住所リストに適しています。
1. 住所リストの隣にある空の列を選択します。補助列の最初のセルに以下の数式を入力して、通り名を抽出します：
=MID(A1,FIND(" ",A1)+1,255)（ここでA1はあなたの住所データの一番上のセルを指します—データが別の場所から始まる場合は調整してください。）
数式を入力した後、 Enter キーを押して、フィルハンドルをドラッグして住所範囲内のすべての行に数式を適用します。この数式は各住所の最初のスペースを見つけて、その後ろにあるもの—通り名とその後の接尾辞—を返します。住所が同じ構造であることを確認してください。そうでない場合、数式が期待通りに分割されない可能性があります。
2. 抽出した通り名のある補助列全体を選択し、データタブに行き、「昇順」をクリックします。これにより、通り名がアルファベット順に並び替えられます。
3. 表示される「並べ替えの警告」ダイアログボックスで、「選択範囲を拡張する」を選択して、並べ替え時に完全な住所情報が一緒に残るようにします。
4. 「並べ替え」をクリックします。これで、住所リストが通り名に基づいて再配置され、類似の通りが一緒に表示されます。
注意: この方法は標準化された住所形式に最適です。住所セルに不規則なパターンや通り名の前にもう複数のスペースがある場合、数式を調整する必要があります。数式を使用した後、必ずいくつかの結果を正確さのために確認してください。
利点： シンプルで、追加のツールは不要。
欠点： 住所フォーマットの一貫性に依存しており、フォーマットが異なる場合は余分な作業が必要。
Excelで補助列を使用して住所を通り番号で並べ替える
通り番号で住所リストを並べ替える必要がある場合—例えば、配達順を割り当てるときや、隣接する住所を特定するときなど—番号を抽出してそれを並べ替えに使用するのは簡単です。これは、住所が異なる通りにあっても効果的です。
1. 住所リストの隣にある空白セルに、次の数式を入力して通り番号を取り出します：
=VALUE(LEFT(A1,FIND(" ",A1)-1))（A1はリスト内の最初の住所です—必要に応じて調整してください。）これを入力した後、Enterキーを押します。この数式は最初のスペースを見つけ、その前の文字を数値に変換して返します。住所に通り番号として先頭の数字がある場合、この数式は正しく動作します。次に、フィルハンドルをドラッグしてリストの残りに数式を適用します。
2. 先ほど作成した補助列を選択し、データタブに行き、「昇順」（または新しいExcelバージョンでは「最小から最大へ」）をクリックします。
3. 並べ替えの警告ダイアログで、「選択範囲を拡張する」を選択して全行を並べ替えます。
4. 並べ替えを適用するために「並べ替え」をクリックします。これで、住所は抽出された通り番号に基づいて並べ替えられます。
ヒント: 通り番号をテキストとして保持したい場合、または数値による並べ替えが必要ない場合は、以下を使用することもできます：
=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 Sub2コードを実行するには、住所リストがアクティブな状態で、 
ボタンをクリックするか、 F5キーを押します。これで、列Aの住所リストが通り名のアルファベット順に並び替えられます。
このバージョンは最初のスペースの前の数字のみを抽出し、数値順に並び替えます。
トラブルシューティング：
- 住所が列Aにあることを確認するか、データの位置に応じてコードを更新してください。
- データにヘッダーが含まれている場合、 Header = xlYes ヘッダ行を並べ替えないように調整する必要があります。
- 大量のVBAコードを実行する前に常にバックアップを作成してください。
利点： 補助列は不要；大規模なデータセットや繰り返しの並べ替えに有効。
欠点： 初期設定にはマクロの許可とVBAの基本的な理解が必要。
その他の組み込みExcelメソッド - Power Queryを使用して住所列を分割し、補助列なしで直接Power Query内で並べ替える
現代のExcelバージョン（Excel 2016以降およびMicrosoft 365）で利用可能なPower Queryは、住所を通り番号や通り名などのコンポーネントに分割する際の柔軟で数式不要の方法を提供します。この解決策は、数式や補助列を使いたくない場合や、基本的な数式では効率的に処理できない多様な形式の住所がある場合に理想的です。Power Queryはステップを保存できるため、データが増えても更新することができます。
1住所データを選択し、「 データ 」タブに行き、 表/範囲から （求められた場合は表を作成します）。
2Power Queryウィンドウで、住所列を選択し、「 列の分割 > 区切り文字で分割」をクリックします。「 スペース 」を区切り文字として選択し、, 左端の最初の区切り文字を選択します。 最初の左端の区切り文字 で「 分割位置 」タイプを選択します。
3これにより、住所は通り番号と残りの通り名/住所に分割され、新しい列に名前を付けることができます。
4並べ替えるには、通り名または通り番号列の見出しにある矢印をクリックし、「 昇順で並べ替え または 降順で並べ替え.
5」を選択します。クリックして「 閉じて読み込む 」で整列された結果をワークシートに戻します。
追加のヒント：
- 住所パターンが一貫していない場合は、Power Queryでカスタム分割や変換を使用してさらに列を操作できます。
- Power Queryのステップは自動的に記録され、データソースが変更された場合でも簡単に更新できます。
- この方法は元のデータを変更しないため、オリジナルのレコードに対する安全性が向上します。
利点： シートに恒久的な変更がない；複雑な住所パターンに強力；管理する数式がない。
欠点： Excel 2016以降が必要；新規ユーザーにはインターフェースが馴染みにくい可能性がある。
まとめとトラブルシューティングの提案：
- 数式やVBAを適用する前に、住所形式の一貫性を確認してください。
- 補助列やコードを使用した後は、特に並べ替え結果をプレビューして正確さを確認してください。
- 番号や通り名が最後にないなど、予期しない構造のデータがある場合は、数式を調整するか、より強力な分割のためにPower Queryを検討してください。
- VBAや高度なデータツールを使用する前に、定期的にバックアップを作成して偶発的なデータ損失を防いでください。
- データ量、Excelバージョン、ツールの使いやすさに最も適した解決策（数式、VBA、Power Query）を選択してください。
- 最適な方法がわからない場合は、Power Queryが最も柔軟性があり、破壊的な編集が安全であることが多いです。
関連記事：
最高のオフィス業務効率化ツール
| 🤖 | Kutools AI Aide：データ分析を革新します。主な機能：Intelligent Execution｜コード生成｜カスタム数式の作成｜データの分析とグラフの生成｜Kutools Functionsの呼び出し…… |
| 人気の機能：重複の検索・ハイライト・重複をマーキング｜空白行を削除｜データを失わずに列またはセルを統合｜丸める…… | |
| スーパーLOOKUP：複数条件でのVLookup｜複数値でのVLookup｜複数シートの検索｜ファジーマッチ…… | |
| 高度なドロップダウンリスト：ドロップダウンリストを素早く作成｜連動ドロップダウンリスト｜複数選択ドロップダウンリスト…… | |
| 列マネージャー：指定した数の列を追加 ｜列の移動 ｜非表示列の表示/非表示の切替｜ 範囲＆列の比較…… | |
| 注目の機能：グリッドフォーカス｜デザインビュー｜強化された数式バー｜ワークブック＆ワークシートの管理｜オートテキスト ライブラリ｜日付ピッカー｜データの統合 ｜セルの暗号化/復号化｜リストで電子メールを送信｜スーパーフィルター｜特殊フィルタ（太字/斜体/取り消し線などをフィルター）…… | |
| トップ15ツールセット：12 種類のテキストツール（テキストの追加、特定の文字を削除など）｜50種類以上のグラフ（ガントチャートなど）｜40種類以上の便利な数式（誕生日に基づいて年齢を計算するなど）｜19 種類の挿入ツール（QRコードの挿入、パスから画像の挿入など）｜12 種類の変換ツール（単語に変換する、通貨変換など）｜7種の統合＆分割ツール（高度な行のマージ、セルの分割など）｜… その他多数 |
Kutools for ExcelでExcelスキルを強化し、これまでにない効率を体感しましょう。 Kutools for Excelは300以上の高度な機能で生産性向上と保存時間を実現します。最も必要な機能はこちらをクリック...
Office TabでOfficeにタブインターフェースを追加し、作業をもっと簡単に
- Word、Excel、PowerPointでタブによる編集・閲覧を実現。
- 新しいウィンドウを開かず、同じウィンドウの新しいタブで複数のドキュメントを開いたり作成できます。
- 生産性が50%向上し、毎日のマウスクリック数を何百回も削減！
全てのKutoolsアドインを一つのインストーラーで
Kutools for Officeスイートは、Excel、Word、Outlook、PowerPoint用アドインとOffice Tab Proをまとめて提供。Officeアプリを横断して働くチームに最適です。
- オールインワンスイート — Excel、Word、Outlook、PowerPoint用アドインとOffice Tab Proが含まれます
- 1つのインストーラー・1つのライセンス —— 数分でセットアップ完了（MSI対応）
- 一括管理でより効率的 —— Officeアプリ間で快適な生産性を発揮
- 30日間フル機能お試し —— 登録やクレジットカード不要
- コストパフォーマンス最適 —— 個別購入よりお得