エクセルで2つの郵便番号を比較し、正しい方を7桁に処理して反映させたい

Question

エクセルで郵便番号の整理をしています。

下記のようにA列とB列を比較して選び、C列に処理したものを反映させたいです。

どのように処理させれば実現できますでしょうか。


　元の〒　　住所生成した〒　　希望する結果
　　A列　　　　　　B列　　　　　C列
1、336　　　　 336-0321　　336-0321　→Aが8文字以下なのでBを反映
2、336-　　　　336-0000　　336-0000　→Aが8文字以下なのでBを反映
3、250021　　　025-0021　　025-0021　→Aが8文字以下なのでBを反映
4、542-2210　　(空欄)　　　　542-2210　→B空欄なのでAを反映
5、452-0000　　452-0312　　452-0312　→両方8文字だがAは末尾が0000なのでBを反映
6、225-0055　　225-0000　　225-0055　→両方8文字だがBは末尾が0000なのでAを反映
7、025-　　　　(空欄)　　　　025-0000　→B空欄なのでAを反映　→「-」から後ろが
　　　　　　　　　　　　　　　　　　　　　無いので「0000」を足す
8、0250021　　(空欄)　　　 025-0021　→B空欄なのでAを反映→　
　　　　　　　　　　　　　　　　　　　「-」無しの7文字なので「-」を挿入する
9、250021　　　(空欄)　　　025-0021　→B空欄なのでAを反映→　
　　　　　　　　　　　　　　　　　　　　「-」無し4文字以上6文字以下なので
　　　　　　　　　　　　　　　　　　　　頭に0を足して7文字して「-」を挿入
10、45　　　　　(空欄)　　 045-0000　→B空欄なのでAを反映→　
　　　　　　　　　　　　　　　　　　　　3文字未満なので頭に0を足して
　　　　　　　　　　　　　　　　　　　　3文字し「-0000」を足す
11、7　　　　　(空欄)　　　007-0000　→B空欄なのでAを反映→　
　　　　　　　　　　　　　　　　　　　　3文字未満なので頭に0を足して3文字し
　　　　　　　　　　　　　　　　　　　　「-0000」を足す
12、45-　　　　(空欄)　　　045-0000　→B空欄なのでAを反映　→「-」の前が3文字
　　　　　　　　　　　　　　　　　　　　未満なので頭に0を足し3文字し「0000」を足す
13、45-0010　　(空欄)　　　045-0010　→B空欄なのでAを反映　→「-」の前が3文字
　　　　　　　　　　　　　　　　　　　　未満なので頭に0を足す
14、045-010　　(空欄)　　　045-010　→B空欄なのでAを反映　→「-」から後ろが
　　　　　　　　　　　　　　　　　　　　足りないがそのまま


※頭に0を追加するのは書式が数値になっていて0が消えている為です。
※AB両方8文字なら末尾が0000ではない方を反映し、まったく同一ならB列を反映します。
※約25万行あります。

郵便番号が3桁だった時代から追記している為、同一人物のデータでも3桁と7桁が混在している状況です。

下記のページを参考に住所列から7桁の郵便番号をB列に反映させましたが、村や市が合併しているものは(空欄)や末尾-0000になってしまいました。
http://www.wanichan.com/pc/excel/2010/2/page06.html

何卒、宜しくお願いします。

soixante · Accepted Answer

#1の者です。久しぶりに見に来ました。かなり盛り上がってますね（笑。

すみません、すべての流れは読んでいませんが、条件18が追加されたようなので、
投稿します。

＃１で回答したコードの一部分を修正します。
具体的には、「'両方とも形式が正しい場合」　のところを差し換えてください。
最終行を「'両方埋まっていて、片方が形式が正しい場合」としていますが、前回分コードと
見比べた時の区切りを分かりやすくするためのものですので気にしなくて良いです。

～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～
'両方とも形式が正しい場合
If Len(MojiA) = 8 And Mid(MojiA, 4, 1) = "-" And Len(MojiB) = 8 And Mid(MojiB, 4, 1) = "-" Then
      　　　If Right(MojiA, 4) = "0000" Then   'A列末尾が0000の場合
            　　　　　　MojiC = MojiB
      　　　ElseIf Right(MojiB, 4) = "0000" Then    'B列末尾が0000の場合
         　　　　　　 If Left(MojiA, 3) = Left(MojiB, 3) Then    '上３ケタが一致の場合
           　　　　　　　　　 MojiC = MojiA
         　　　　　　ElseIf Left(MojiA, 3) <> Left(MojiB, 3) Then  '上３ケタが不一致の場合
            　　　　　　　　　MojiC = MojiB
          　　　　　　End If
          
     　　　 Else    '条件17
            　　　　　　MojiC = MojiB
      　　　End If
'両方埋まっていて、片方が形式が正しい場合
～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～

コード全体を検証いただいたかどうかは分かりませんが、このコードの全体の方針としては、
A列のデータをMojiA、B列のデータをMojiB　とし、各種条件判定のうえ、そのどちらか採用する
ほうを　MojiC　に入れる作業をして、最後に　Cells(r,3).Value=MojiC　で吐き出す、というものです。

今回の　条件18　は、「両方とも形式が正しい場合」で、「B列末尾が0000の場合」
で、さらに下の階層に、If 分岐を埋め込んだものです。
「A列、B列のハイフン以前の3ケタが一致」か「不一致か」　です。

いちおうわたしの手元のダミーデータでは正常作動しました。
よろしくお願いします。

MarcoRossiItaly · Answer

No.2・11・14 です。No.14 にて書き漏らしていたことがありますので、ご参考まで、追記いたします。 No.11 を改造して作った No.14 の I7 セルの数式は、IF 関数が入れ子の構造になっています。この数式では、TRUE、FALSE の場合に IF が返す値（今回はセル参照）が「h7」と「g7」の 2 種類だけになっているため、2 つの IF を 1 つにまとめることが可能です。 *（論理積）は「かつ」、+（論理和）は「または」に相当します。<> は「≠」の意味です。 (1)論理式 h7/10^4<>int(h7/10^4) の否定は、h7/10^4=int(h7/10^4) です。 (2) h7*(round(n(g7),-4)<>round(h7,-4)) は、(h7<>0)*(round(n(g7),-4)<>round(h7,-4)) と書いても同じ働きをします。 (3) (h7<>0)*(round(n(g7),-4)<>round(h7,-4)) の否定は、(h7=0)+(round(n(g7),-4)=round(h7,-4)) です。 (4) h7=0 は h7/10^4=int(h7/10^4) の条件に含まれる（h7=0 は十分条件である）ため、(h7/10^4=int(h7/10^4))*(h7=0) は、単なる h7=0 という条件と同じです。以上により、IF を次のとおり 1 つ減らすことができます。どちらの数式でも同じ計算結果が得られます。 I7　=if(h7/10^4<>int(h7/10^4),h7,if(h7*(round(n(g7),-4)<>round(h7,-4)),h7,g7)) 　　あるいは　　=if((h7=0)+(h7/10^4=int(h7/10^4))*(round(n(g7),-4)=round(h7,-4)),g7,h7)

MarcoRossiItaly · Answer

No.2・11 です。下で述べるとおり、郵便番号を 3 列に分ける方式にするのもいいと思います。1 列を 3 列に分ける方法も簡単です。＞普段データを記入する者の多くが、そういったエクセルの挙動を知らず、…… 　今回の整理で「ハイフンありの文字列に統一してしまった方が良さそうだ」というのが私の判断です。それならそれで構わないと思います。No.11 で示したとおり TEXT 関数を適用すれば、いつでも数値を文字列に変換できます。また、SUBSTITUTE 関数あるいは置換のダイアログにより、いつでも文字列を数値に戻せます。 A1　12345　など B1　=text(a1,"000-0000")　　　←「001-2345」に変換 C1　=0+substitute(b1,"-",)　　←「12345」に変換 ※ C 列の処理をする代わりに、置換のダイアログで「-」を削除しても可多数の人が同じファイルを編集するため、記入される値の内容がコントロールできないので文字列にしたい、という状況でしょうか？そうであれば、お勧めなのは、「『-』より前の部分」と「-」と「『-』より後の部分」を 3 列に分けて記入させる方式です。このように細分化しておけば、異常なデータがあったとしても、オートフィルタなどですぐに抽出できます。 D1　1　など　　　　　　　　←あらかじめ書式「000」をセル範囲に設定 E1　-　　　　　　　　　　　←あらかじめ記入 F1　2345　など　　　　　　　←あらかじめ書式「0000」をセル範囲に設定 G1　=0+(d1&f1)　　　　　　　←結合　　あるいは　　　=d1*10^4+f1 H1　=text(g1,"000-0000")　　←「数値→文字列」の変換数値「12345」あるいは文字列「001-2345」を「1」と「2345」というふうに 2 列に分割するには、「区切り位置」ウィザードを次の手順で利用するか、または数式を使います。 (1)数式が除去された郵便番号の文字列の列全体などを選択した状態で、「データタブ > 区切り位置」を実行数値の場合 (2)「スペースによって…」を指定して「次へ」ボタンを押す。 (3)その次に表示された画面内にプレビューがあるので、「12345」の上でクリックして線を出現させ、ドラッグアンドドロップすることにより、その線を「1」と「2」の間の位置に持ってくる。文字列の場合 (2)「カンマやタブなどの…」を指定して「次へ」ボタンを押す。 (3)その次に表示された画面内の「その他」の右隣にあるボックスに「-」を入力する。 (4)あとは「完了」ボタンを押せば、2 列に分割されます。ただし「区切り位置」の実行時には、「郵便番号の列の右隣の列」を空けておく必要があります。空いていなければ分割時に、既存データを上書きしてよいかとの確認のメッセージが出ます。数式により数値や文字列を分割するなら、次式など。 I1　=0+substitute(g1,j1,)　　←あらかじめ書式「000」をセル範囲に設定 J1　=0+right(g1,4)　　　　　 ←あらかじめ書式「0000」をセル範囲に設定 K1　=0+left(h1,3)　　　　　　←あらかじめ書式「000」をセル範囲に設定 L1　=0+right(h1,4)　　　　　 ←あらかじめ書式「0000」をセル範囲に設定＞18、079-2300　　055-0000　　055-0000 　→両方8文字でBの末尾が0000だった時、　　前3桁の数字が一致した場合はAを、一致しなかった場合はBを選択する No.11 の I 列の数式を次式に差し替えれば、その条件にも対応できます。今回の添付図を見ると、25 行目にある 18-2 というデータにおいて、正しく計算されていることが分かります。 I7　=if(h7/10^4<>int(h7/10^4),h7,if(h7*(round(n(g7),-4)<>round(h7,-4)),h7,g7))

30246kiku · Answer

＃１０です

> 回答の受付を締め切っても、このページは何年も残り続けます。
> 各スクリプトの実行結果を記載しているのは、後から検索等で本質問ページに辿り着く人達から見て、どうゆう結果になったのか、という情報はあった方が良いからです。

私も同じ考えです。
また、おかしな動きをしたままのものが残る方が嫌です。
なので、修正したものを回答しているわけですが･･･

>　　　A　　　　　　B　　　　　　C
> 18、079-2300　　055-0000　　055-0000　→両方8文字でBの末尾が0000だった時、
> 　　　　　　　　　　　　　　　　　　　　　　　前3桁の数字が一致した場合はAを、
> 　　　　　　　　　　　　　　　　　　　　　　　一致しなかった場合はBを選択する
> を追加したいです。

実はこれ用に、既に Samp3 に盛り込んでいました。
（Samp4 は、この処理を除外してました）

　　　　sA = PostCode(vA(i, 1))
　　　　If (vA(i, 2) <> "") Then
　　　　　　sB = PostCode(vA(i, 2))
　　　　　　If (Len(sA(1)) = Len(CZR4)) Then
　　　　　　　　If ((sA(0) <> sB(0)) Or (sB(1) <> CZR4)) Then
　　　　　　　　　　sA = sB
　　　　　　　　End If
　　　　　　Else
　　　　　　　　sA = sB
　　　　　　End If
　　　　End If
　　　　vA(i, 1) = Join(sA, CSEP)

A、B 列に何かあったら、
・A 列を無条件で整形
・B 列に何かあったら
　B 列を整形
　A 列整形結果 "-" 以降が４文字でなければ（旧番号？） B 列採用
　で、３桁部分が違っているか、B の４桁部分が 0000 でなければ B 列採用

文字列は整形してから比較するので
300131　0300131　030-0131　は、同じものとして扱います。

例えば、パターン１５の場合は
sA(0)："000"
sA(1)："0000"
sB(0)："045"
sB(1)："0000"
の比較になり、B列を採用････

Samp2 の場合は、If ((sA(0) = CZR3) Or (sB(1) <> CZR4)) Then
の判別してましたが、抜けが多かったですね

※ 現状 Samp3 でうまくいきそうな気がしていますがどうでしょう

TakeShigeIwk · Answer

問合せの件（16ケース）を満たす、関数式を作成しました。
詳細は、こちらをダウンロードしてご確認下さい。

http://1drv.ms/1chQMt3

MarcoRossiItaly · Answer

No.2 です。＞頂いた関数を実行してみたところJ列に、下記の結果が出ました。　…… 　下記３つの条件が満たされていないようです。質問者さんが意図していなかった結果が出た、ということだけがコメント欄に書かれている状態です。しかし、その前に、No.2 に書いた「データを処理する方針」や各数式の意味は、理解しましたか？こちらとしては、数式を成果物として納品することよりも、データの扱い方・Excel の使い方を理解してもらう、即ちスキルアップしてもらうことが最も重要ですので。むしろ、仮に、必要な条件が不足なく、きちんと示されている状況であれば、それら全てに対応した数式を回答するのが「当たり前」です。そのため、逆に、条件が明らかに不足している時点では、こちらとしては「（データ処理結果においてまだ）条件が満たされていない（と質問者さんが感じる）」かどうかについて、関心がありません。そういう結果が出るのもまた、当たり前のことだからです。補足などにより質問の水準が上がってくれば、数式の機能も上げていくだけのことです。＞010-05　　010-1502　　　010-05 ＞3、250021　　　025-0021　　025-0021　→Aが8文字以下なのでBを反映条件「Aが8文字『未満』なのでBを反映」のほうが、条件「『-』から後ろが足りないがそのまま」よりも優先される、という隠し条件が他にあったのですね。つまり B 列にデータがあるときは、A 列の「-」以降の桁数は、実は考えなくていいという意味なのですね？それが判明すれば、対応は簡単にできます。＞15、(空欄)　　　　045-0000　　045-0000　→Aが空欄なのでBの末尾が0000でもBを反映その条件というか、準備の作業に対応することは、ごく簡単です。B 列から A 列に値を写し取る数式を書くだけです。苦労されているようですので、こちらで下のとおり用意しました。＞17、452-0300　　452-0312　　452-0312　→両方8文字で末尾0000も無い場合はBを反映こちらでは、「12 を選択する理由は、恐らく、00 より大きいためなのだろう」と判断していました。しかしそうではなくて、文字どおり「2 列がいずれも 5 桁以上で、かつ、いずれも『0000』を含まない場合は、必ず B 列を選択することにしたい」という秘密条件が本当の希望なのですね？それが判明すれば、対応は簡単にできます。＞Bの末尾が0000でない限り、Bの方が信憑性がある為、Bを選びたいのです。つまり、「B 列が 5 桁以上で、かつ、10,000 の倍数でないケースでは、必ず B 列を選択することにしたい」という秘匿条件を追加されるのですね？これも、対応は簡単です。＞Aは手入力したもので、Bは住所から自動生成したものなので、…… そういう裏条件に関する知識の有無は、データ処理の方針に大きな影響を与える可能性があります。いずれの追加条件も、マクロでなくてもできます。No.2 にて提示済みの数式は、いろいろ修正することにはなりますが。別にどんな方法でも目的が果たせれば構わないと思いますが、マクロだと、行数が多い場合には処理に少し時間がかかるとか、コードの内容によってはバックアップを取らないといけないというくらいですかね。「2 列とも空欄の場合にはエラーを出してくれる数式」がいいと思いましたが、人によって苦労されるかもしれないので、エラー値よりも、置換用の文字列「★★★」を返す数式を下のとおり提示することとしました。計算結果の値複写により数式を除去した後で、「★★★」→「」（未入力）と置換することにより（Ctrl+H）、空白に戻してください。あと、No.2 で、「郵便番号は 5 桁以上の整数として保存すればよい」と言いました。しかしそれでも、どうしても文字列にしたいという方もいらっしゃいます。言われる前に、TEXT 関数を含む数式をサービスしておきます。TEXT は、セルの書式設定のように見かけだけではなくて、実際に数値を「-」込みの文字列データに変換してくれる関数です（セルの書式に制限はありません）。今回のご質問の範疇では No.2 の説明どおり、あくまで不要な数式に過ぎないのですが、気分に応じてご利用ください。 E1　=if((b1="")*(c1<>""),c1,b1) F1　=(countif(b1,"*-?")+countif(b1,"*-??")+countif(b1,"*-???"))*(c1="")=0 G1　=if(f1,0+substitute(e1,"-",),e1) H1　=0+substitute(0&c1,"-",) I1　=if(h1/10^4<>int(h1/10^4),h1,g1) J1　=if(f1*(len(i1)<=3),i1*10^4,i1) K1　=if(j1=0,"★★★",j1) L1　=k1 M1　=text(k1,"000-0000")

30246kiku · Answer

＃８です

私の回答は、
・文字数で切り分けるのではなく
・整形後の比較でどうだ？？
でしたが

※ 少なくてもパターン１～１７は動作していたので回答しました

処理できなかったものが提示されて、それに対応すべく修正していく･･･
これがダメだという事なのでしょうか？
誰かと競っているつもりはありませんけど･･･

何を思って、名指しされたのでしょうか

WindFaller · Answer

#4の回答者です。

>A　　　　　　 B
>452-0012　　452-0001
>このパターンだった場合は、Aが選択されるという事でしょうか？

その通りです。

>Aは手入力したもので、
>Bは住所から自動生成したものなので、
>Bの末尾が0000でない限り、Bの方が信憑性がある為、Bを選びたいのです。

そういうデータの重みに関しては、人為的なものであるのかはこちらは認識なかったけれども、
それを直すことはたやすいことです。しかし、その信ぴょう性（正確さ）は、そのプロセス自体にあるように思います。それは、現在の段階では分かりません。ご質問者さんの言葉のままに、ということになります。

いずれにしても、私は、Aを選ぶとか、Bを選ぶとかという、決定事由は、もう少し論理的な組み立てが必要だったと思います。このレベルでは、不具合が出て直すことは簡単でも、マクロが分っていて、きちんと組み立てができていれば、AだのBだの判定とは、まったく違った次元が展開できるはずです。

ここに出されているマクロのコードのそれぞれは大きく違う要素がないので、こちらが、現在32046kikuさんが、されていることに、競い合うようなことは好まないです。#4で書いたように、「郵便番号ウィザードのオープンコードを加工して、その出力から判定させればよい」というのは、それ自体を、マクロの中に取り込んでしまうことを考えました。もちろん、これはやってみなければ分からないから、失敗の可能性もあります。しかし、現状は、これ以上の大きな変化もないようですから、できれば、このままROMモードにさせてもらいます。失礼しました。

30246kiku · Answer

＃７です

> マクロを実行してみたところ、下記の結果になりました。
>
> A　　　　　　B　　　　　　C
> 029-52　　　024-0000　　024-52
> 030-0131　　038-0000　　038-0131
> 079-23　　　055-0000　　055-23

＃３のお礼にあった上記パターンの場合、
Samp2 は、A を採用する様に変更しましたが、
言及されないという事は OK ？　じゃないですよね

Public Sub Samp3()
　　Dim vA As Variant
　　Dim sA() As String, sB() As String
　　Dim i As Long

　　With ActiveSheet.UsedRange.Resize(, 3)
　　　　vA = .Resize(, 2).Value
　　　　For i = 1 To UBound(vA)
　　　　　　If ((vA(i, 1) <> "") Or (vA(i, 2) <> "")) Then
　　　　　　　　sA = PostCode(vA(i, 1))
　　　　　　　　If (vA(i, 2) <> "") Then
　　　　　　　　　　sB = PostCode(vA(i, 2))
　　　　　　　　　　If (Len(sA(1)) = Len(CZR4)) Then
　　　　　　　　　　　　If ((sA(0) <> sB(0)) Or (sB(1) <> CZR4)) Then
　　　　　　　　　　　　　　sA = sB
　　　　　　　　　　　　End If
　　　　　　　　　　Else
　　　　　　　　　　　　sA = sB
　　　　　　　　　　End If
　　　　　　　　End If
　　　　　　　　vA(i, 1) = Join(sA, CSEP)
　　　　　　End If
　　　　Next
　　　　.Columns(3).Value = vA
　　End With
End Sub

整形してから比較しましょうか･･･
の方針でやってましたが、結構無理があるかも

> A　　　　　　B　　　　　　C
> 030-0131　　038-0000　　038-0000

となります。
元の文字数で比較する様にしてみると

Public Sub Samp4()
　　Dim vA As Variant
　　Dim sA() As String, sB() As String
　　Dim i As Long

　　With ActiveSheet.UsedRange.Resize(, 3)
　　　　vA = .Resize(, 2).Value
　　　　For i = 1 To UBound(vA)
　　　　　　If ((vA(i, 1) <> "") Or (vA(i, 2) <> "")) Then
　　　　　　　　sA = PostCode(vA(i, 1))
　　　　　　　　If (vA(i, 2) <> "") Then
　　　　　　　　　　sB = PostCode(vA(i, 2))
　　　　　　　　　　Select Case Len(vA(i, 1))
　　　　　　　　　　　　Case 8
　　　　　　　　　　　　　　If (sB(1) <> CZR4) Then sA = sB
　　　　　　　　　　　　Case Else
　　　　　　　　　　　　　　sA = sB
　　　　　　　　　　End Select
　　　　　　　　End If
　　　　　　　　vA(i, 1) = Join(sA, CSEP)
　　　　　　End If
　　　　Next
　　　　.Columns(3).Value = vA
　　End With
End Sub

※ 抜けていそうなパターンは以下くらい？
でも、パターン３で B 列採用ということみたいですけど･･･

> A　　　　　　B　　　　　　C
> 0300131　　038-0000　　？？？？

030-0131 なら、A 列採用と思いますが･･･現状 B 列採用

30246kiku · Answer

＃６です＃６は安易すぎましたパターン１５がおかしくなりますね Samp1 部分を以下に変更してみてどうなりますか Public Sub Samp2() 　　Dim vA As Variant 　　Dim sA() As String, sB() As String 　　Dim i As Long, j As Long 　　With ActiveSheet.UsedRange.Resize(, 3) 　　　　vA = .Resize(, 2).Value 　　　　For i = 1 To UBound(vA) 　　　　　　If ((vA(i, 1) <> "") Or (vA(i, 2) <> "")) Then 　　　　　　　　sA = PostCode(vA(i, 1)) 　　　　　　　　If (vA(i, 2) <> "") Then 　　　　　　　　　　sB = PostCode(vA(i, 2)) 　　　　　　　　　　If ((sA(0) = CZR3) Or (sB(1) <> CZR4)) Then 　　　　　　　　　　　　sA = sB 　　　　　　　　　　End If 　　　　　　　　End If 　　　　　　　　vA(i, 1) = Join(sA, CSEP) 　　　　　　End If 　　　　Next 　　　　.Columns(3).Value = vA 　　End With End Sub

エクセルで2つの郵便番号を比較し、正しい方を7桁に処理して反映させたい

No.2・11・14 です。

#1の者です。

No.2・11 です。

＃１０です

問合せの件（16ケース）を満たす、関数式を作成しました。

No.2 です。

＃８です

#4の回答者です。

＃７です

＃６です

似たような質問が見つかりました

関連するカテゴリからQ&Aを探す

デイリーランキングこのカテゴリの人気デイリーQ&Aランキング

マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング