Excel住所録の文字を統一して、別アプリで使いやすくする

住所録データを元に宛名印字などをする時、データを使いやすく加工する方法。

Registered 2022.09.09 Update 2023.05.21  技術習得:業務系アプリQ&A,独自開発

目次

住所録データの統一

送り状や宛名ラベルの作成で住所録を扱うことがあります。
基本はわかりやすいのですが、気になるのが「データが足りない」ことと「表記がバラバラ」なこと。

送り状で必要なデータ

表記がバラバラで困る事例

1件1行、1項目1列に加工するため関数を使う

Excelでは関数が使えるので、データの整形が可能です。
文字列の取り出しや置換になるので、以下の関数を知っておくと良いでしょう。

また、純粋な文字の置換や削除ではExcelの検索と置換、英数字の半角と全角の置換は以下で紹介するVBAがおすすめです。

文字列操作
LEN文字列又は対象セルの文字数を求める。
LEFT左端から指定した文字数分の文字列を取り出す。
RIGHT右端から指定した文字数分の文字列を取り出す。
MID文字列の内、指定位置から指定数分だけ文字列を取り出す。
FIND指定した文字列の位置を調べる。
REPLACE指定した文字数の文字列を置換する。
CONCATENATE（CONCAT）指定範囲の文字列を連結する。
TEXT数値を指定した表記に置換する。
論理
IF条件式。もし1ならGO、2ならEndなどが可能。
ANDすべての条件を満たすか調べる。
ORいずれかの条件を満たすか調べる。
NOT条件が満たされないことを調べる。
TRUE真であること。条件式で使う。
FALSE偽であること。条件式で使う。
検索と行列
LOOKUP1行又は1列の範囲を検索する。
VLOOKUP縦範囲（行方向）を検索する。
HLOOKUP横範囲（列方向）を検索する。
XLOOKUP縦横に検索する。Office365とExcel2021が対応
INDEX指定した行列の位置にある値を求める。
MATCH検索値の相対位置を求める。
XMATCH検索値の相対位置を求める。Office365とExcel2021が対応。
演算子
算術、比例関数と組み合わせる。
文字列「&」で文字列を繋げられる。

郵便番号の表記を統一する

郵便番号の例としてよくある4パターンが以下のものです。

  1. 000-0000を手入力。
  2. 0000000を手入力し、Excelの書式設定で000-0000と表示している。
  3. 0000000を手入力し、ハイフンは無い。
  4. 「000」と「0000」が別セルにある。

「000-0000を手入力」パターンは、ハイフンが必要な時はそのまま使え、ハイフンが不要な時は置換で削除できるので汎用性が高いです。
この入力方法は一般的なのか、人からもらうと高確率でこの入力規則が適用されています。

しかし、汎用性の低い「2.」から「4.」も存在します。
この3種は数式や置換で規則を変換する必要があり、ひと手間かかります。

「0000000」の解消方法

「0000000」で入力、又は書式設定で見た目を変えているが実際は「0000000」の場合、TEXT関数で置き換える方法が有効です。

=TEXT(A1,"000-0000")

A1が7桁の郵便番号が入っているセルです。

「000」と「0000」が別セルの解消法

こちらも関数で解消できます。

=TEXT(A1,'000')&"-"&TEXT(B1,'0000')

A1が3桁の数字、B4が4桁の数字が入っているセルです。

半角全角、ハイフンを統一するVBA

「漢字とかな、カナ」は全角、「英数字」は半角、複数あるハイフンは「-」に統一する時はマクロ（ExcelVBA）に限ります。
以下は選択範囲内の表記統一で実際に使っているものから引用しました。


'変数の宣言を強制する
Option Explicit

Sub henKanMacro()
    '動作が遅くなるため、画面の再描画/自動計算を停止
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual

    '謎の文字コードハイフンがあるので、ハイフンを半角に統一する
    '以下はhttps://www.wordvbalab.com/code/3178/より
    Selection.Replace what:=ChrW(&H2010), replacement:="-", lookat:=xlPart
    Selection.Replace what:=ChrW(&H2011), replacement:="-", lookat:=xlPart 
    Selection.Replace what:=ChrW(&H2012), replacement:="-", lookat:=xlPart
    Selection.Replace what:=ChrW(&H2013), replacement:="-", lookat:=xlPart
    Selection.Replace what:=ChrW(&H2014), replacement:="-", lookat:=xlPart
    'Selection.Replace what:=ChrW(&H2015), replacement:="-", lookat:=xlPart
    'これを有効にすると、?になる箇所が多いので封印しています。
    Selection.Replace what:=ChrW(&H2043), replacement:="-", lookat:=xlPart
    Selection.Replace what:=ChrW(&H2212), replacement:="-", lookat:=xlPart

    Dim re As Object
    Dim Cell As Range
    Dim Match As Object
    Dim Str As String

    ' 正規表現オブジェクトを作成
    Set re = CreateObject("VBScript.RegExp")
    re.Pattern = "[｡-ﾟ]+"   ' 1文字以上の半角片仮名
    re.Global = True        ' 検索範囲はグローバル

    ' 選択されたセル範囲について、以下の処理を実行
    For Each Cell In Selection

    	Str = Cell.Value
    	If Str <> "" Then

    	' 半角変換可能な文字を全て半角に変換
    	' 全角片仮名文字もいったん半角片仮名に変換される。
    	Str = StrConv(Str, vbNarrow)

    	' 1文字以上の半角カナを検索し全角に変換
    	' 半角片仮名文字の直後に濁点・半濁点があればここで合成される。
    	For Each Match In re.Execute(Str)
        	Str = Replace(Str, Match, StrConv(Match, vbWide), , 1)
    	Next
    	Cell.Value = Str

    	End If

    Next

    '画面の再描画/自動計算を再開
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True

    'メッセージで最終確認を促す
    MsgBox ("ハイフンが?に変換されていないか確認してください。")

End Sub

注意点は「ハイフンが?に置換される」可能性です。
このマクロを実行した場合、検索文字列「~?」を検索してクエスチョンマークが無いか確認してください。

再利用しやすいデータを作る気配り

住所録は見た目重視の表を作ると再利用時に面倒が生じます。
再利用が考えられるデータは「データ用シートに1件1行、1項目1列で入力」し、印刷や閲覧は専用シートを作ってデータ用シートを参照する方式がおすすめです。



関連ページ

トップへ