エクセルで郵便番号の整理をしています。
下記のように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.h …
何卒、宜しくお願いします。
No.6
- 回答日時:
#3、5です
> If ((sA(1) = CZR4) Or (sB(1) <> CZR4)) Then
> sA(1) = sB(1)
> End If
> If ((sA(0) = CZR3) Or (sB(0) <> CZR3)) Then
> sA(0) = sB(0)
> End If
↓ 上記部分を以下の1行に変更してどうなりますか
If (sB(1) <> CZR4) Then sA = sB
※
> vA(i, 1) = Join(sA, "-")
↓ "-" を Const にしていたので、以下に変更しても
vA(i, 1) = Join(sA, CSEP)
No.5
- 回答日時:
#3です
> 14、045-010 (空欄) 045-010 →B空欄なのでAを反映 →「-」から後ろが
> 足りないがそのまま
確認ですが、このパターンは変更してはいけないのですよね?
No.4
- 回答日時:
こんにちは。
本来、私自身、最初からなら、このような発想にはしないのですが、あえて、質問者さんのご要望に合わせて作ってみました。なぜなら、番号ウィザードのオープンコードを加工して、その出力から判定させればよいからです。ただ、それは、VBAを知っている人に限ります。また、これでも、VBAの裏ワザを使っていますので、わかる人は、どこで使っているかわかるはずです。
最初みた時に、気になった質問でしたので、フィードバックして作ってみました。
>17、452-0300 452-0312 452-0312 →両方8文字で末尾0000も無い場合はBを反映
端に、どちらが重いか測るだけです。逆のケースが出てくるかもしれません。
'//
Sub CheckPhonNumAr()
'No. 8973501
Dim i As Long
Dim x As Variant, y As Variant
Dim Rng As Range
Dim Ar As Variant
'ユーザー設定(最初の行の決定
'---------------------------
Set Rng = Range("A2", Cells(Rows.Count, 1).End(xlUp).Resize(, 3))
'---------------------------
Ar = Rng.Value
For i = LBound(Ar) To UBound(Ar)
x = StrConv(Ar(i, 1), vbNarrow): y = StrConv(Ar(i, 2), vbNarrow)
If x Like "###-#*" Then
x = x & String(8 - Len(x), "0")
ElseIf y Like "###-#*" Then
y = y & String(8 - Len(y), "0")
End If
x = Replace(x, "-", ""): y = Replace(y, "-", "")
If IsNumeric(x) Then
If Len(x) > 4 Then
x = Format$(x, "000-0000")
ElseIf Len(x) > 0 Then
x = Format$(x, "000") & "-0000"
End If
End If
If IsNumeric(y) Then
If Len(y) > 4 Then
y = Format$(y, "000-0000")
ElseIf Len(y) > 0 Then
y = Format$(y, "000") & "-0000"
End If
End If
If x >= y Then Ar(i, 3) = x
If y > x Then Ar(i, 3) = y
Next
Rng.Columns(3).Value = Application.Index(Ar, 0, 3)
End Sub
ご回答ありがとうございます。
>>17、452-0300 452-0312 452-0312 →両方8文字で末尾0000も無い場合はBを反映
>端に、どちらが重いか測るだけです。逆のケースが出てくるかもしれません。
これはつまり、
A B
452-0012 452-0001
このパターンだった場合は、Aが選択されるという事でしょうか?
そうだとちょっとまずいです。
Aは手入力したもので、
Bは住所から自動生成したものなので、
Bの末尾が0000でない限り、Bの方が信憑性がある為、Bを選びたいのです。
No.3
- 回答日時:
処理抜けがあるかもしれませんが、以下でどうなりますか
※ 25万行なんですよね
Option Explicit
Const CZR3 As String = "000"
Const CZR4 As String = "0000"
Const CSEP As String = "-"
Public Sub Samp1()
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))
sB = PostCode(vA(i, 2))
If ((sA(1) = CZR4) Or (sB(1) <> CZR4)) Then
sA(1) = sB(1)
End If
If ((sA(0) = CZR3) Or (sB(0) <> CZR3)) Then
sA(0) = sB(0)
End If
vA(i, 1) = Join(sA, "-")
End If
Next
.Columns(3).Value = vA
End With
End Sub
Private Function PostCode(vSrc As Variant) As String()
Dim sA() As String
sA = Split(vSrc & CSEP, CSEP)
If (Len(sA(1)) = 0) Then sA(1) = CZR4
If (Len(sA(0)) > Len(CZR3)) Then
sA(1) = Right(sA(0), Len(CZR4))
sA(0) = Left(sA(0), Len(sA(0)) - Len(CZR4))
End If
sA(0) = Right(CZR3 & sA(0), Len(CZR3))
If (UBound(sA) > 1) Then ReDim Preserve sA(1)
PostCode = sA
End Function
ご回答ありがとうございます。
マクロを実行してみたところ、下記の結果になりました。
A B C
029-52 024-0000 024-52
030-0131 038-0000 038-0131
079-23 055-0000 055-23
Bの末尾が0000だった場合の処理におかしい点があるようです。
前3桁がBのものが反映され、後ろ4桁がAのものが反映されています。
No.2
- 回答日時:
普通、Excel では、郵便番号は「-」を含む文字列データではなく、「7 桁以下の整数」である数値として保存します。
しかし「-」を表示させたくなったときは、セルの書式を設定することにより、「7 桁以下の整数」に「-」が挿入されたような見かけにすることが可能です。数値を文字列に変換する必要はありません。具体的には、例えばどこかのセルに、「12345」と記入してください。「セルの書式設定 > 表示形式タブ > その他 > 郵便番号」とセルの書式を指定するだけで、「001-2345」とセルに表示されます。数式バーには引き続き「12345」と表示されているので、書式を設定しても、数値が文字列になったわけではありません。
しかし「012-012」という文字列が記入されているセルに郵便番号の書式を設定したとしても、表示は何も変化しません。だから数値や文字列を含む広いセル範囲に、まとめて郵便番号の書式を設定すればオッケーです。
「先頭に 0 を付ける目的で文字列の書式を設定する」ということは、行いません。郵便番号の書式は数値データのための書式ですが、文字列の書式に変更しなくても、7 桁未満なら勝手に 0 が付きます。
したがってお手元のデータを処理する方針としては、次のようになります。質問文のデータのみであれば、マクロなどを使わず基本的な数式のみでできます。
(1)全データの文字列から「-」を数式により削除、ただし条件 14
(「-」を含むが「-」以降が 1 桁以上 3 桁以下)のケースのみを除外
(2) A 列が 3 桁以下の数値であれば A 列を 10,000 倍
(3) B 列が空欄であれば A 列のほうを採用し、B 列にデータがあれば
大きなほうの数値を採用(7 桁未満でも気にせずそのまま)
(4)得られた答えのデータに郵便番号の書式を設定
添付図の数式は、次のとおりです。
E1 =countif(b1,"*-?")+countif(b1,"*-??")+countif(b1,"*-???")=0
F1 =if(e1,0+substitute(b1,"-",),b1)
G1 =if(e1*(len(f1)<=3),f1*10^4,f1)
H1 =0+substitute(0&c1,"-",)
I1 =if(e1,max(g1:h1),g1)
J1 =i1
※ F1・H1 セルの「0+」という部分は、SUBSTITUTE 関数で「-」を削っただけでは
文字列のままなので、それを数値に変換するための操作です。
※ H1 の「0&」という部分は、C 列が空白の場合、空文字列にゼロを足すことにより
発生するエラーを回避するための操作です。
ご回答ありがとうございます。
頂いた関数を実行してみたところJ列に、下記の結果が出ました。
B C J
010-05 010-1502 010-05
(空欄) 311-4152 #VALUE!
001-0915 001-0910 001-0915
下記3つの条件が満たされていないようです。
A B C
3、250021 025-0021 025-0021 →Aが8文字以下なのでBを反映
15、(空欄) 045-0000 045-0000 →Aが空欄なのでBの末尾が0000でもBを反映
17、452-0300 452-0312 452-0312 →両方8文字で末尾0000も無い場合はBを反映
No.1
- 回答日時:
条件が多岐にわたっているので、マクロでの処理かと思います。
漏れもあるかもしれませんが、お書きの17パターンはいけると思います。
■マクロ処理した後のものは戻せませんので、必ずバックアップを取ってからやってください
データは1行目から始まると想定しています。A、Bにデータ、Cに最終結果を出す設定です。
マクロがお分かりにならない場合、以下の流れでお願いします。
1 .エクセル画面で、Alt+F11 でマクロの画面(Visual Basic Editor)を出す。
2. Alt + I 、M で標準モジュールを挿入する。(真っ白な画面が出ます)
3.以下のコードをコピペする。
4.エクセル画面に戻り、Alt+F8 から AAA を実行。
■マクロ処理した後のものは戻せませんので、必ずバックアップを取ってからやってください
-------------------------------------------------------------------
Sub AAA()
Dim r As Long
Dim MojiA As Variant, MojiB As Variant
Dim MojiC As Variant
Dim Lstrow As Long
Lstrow = Cells(Rows.Count, 1).End(xlUp).Row
r = 1
Do While r <= Lstrow
MojiA = Cells(r, 1).Value
MojiB = Cells(r, 2).Value
'両方とも形式が正しい場合
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
MojiC = MojiB
ElseIf Right(MojiB, 4) = "0000" Then
MojiC = MojiA
Else
MojiC = MojiB
End If
'両方埋まっていて、片方が形式が正しい場合
ElseIf Len(MojiA) = 8 And Mid(MojiA, 4, 1) = "-" And Len(MojiB) = 0 Then
MojiC = MojiA
ElseIf Len(MojiB) = 8 And Mid(MojiB, 4, 1) = "-" And Len(MojiA) = 0 Then
MojiC = MojiB
'片方埋まっていて、その形式が正しい場合
ElseIf Len(MojiA) = 8 And Mid(MojiA, 4, 1) = "-" Then
MojiC = MojiA
ElseIf Len(MojiB) = 8 And Mid(MojiB, 4, 1) = "-" Then
MojiC = MojiB
'Aが空白
ElseIf Len(MojiA) = 0 Then
If Len(MojiB) > 0 Then
MojiC = MojiB
End If
'Bが空白
ElseIf Len(MojiB) = 0 Then
If InStr(MojiA, "-") > 0 Then 'ハイフンがあるとき
If Mid(MojiA, 4, 1) = "-" And Len(MojiA) = 8 Then
MojiC = MojiA
ElseIf Right(MojiA, 1) = "-" Then
MojiC = String(4 - Len(MojiA), "0") & MojiA & "0000"
ElseIf Left(Right(MojiA, 5), 1) = "-" Then
MojiC = String(8 - Len(MojiA), "0") & MojiA
Else
MojiC = MojiA
End If
ElseIf InStr(MojiA, "-") = 0 Then 'ハイフンがないとき
If Len(MojiA) = 7 Then
MojiC = Left(MojiA, 3) & "-" & Right(MojiA, 4)
ElseIf Len(MojiA) >= 4 And Len(MojiA) <= 6 Then
MojiC = String(7 - Len(MojiA), "0") & MojiA
MojiC = Left(MojiC, 3) & "-" & Right(MojiC, 4)
ElseIf Len(MojiA) <= 3 Then
MojiC = String(3 - Len(MojiA), "0") & MojiA & "-0000"
End If
End If
Else
MojiC = "未定義"
End If
Cells(r, 3).Value = MojiC
MojiC = ""
r = r + 1
Loop
End Sub
-------------------------------------------------------------------------------
ご回答ありがとうございます。
希望の通りの結果が得られました。
スクリプトにも随所にコメントが記載されていて、どの部分がなんの処理をしているのか解かりやすく
カスタマイズして活用できそうです。
マクロの勉強にもなって非常に助かります。
ありがとうございました。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- Excel(エクセル) エクセル 3つの値の中からデータを抽出させる方法 4 2023/08/24 11:00
- Excel(エクセル) 【画像あり】A1が●+B1と同じ文字がB列にある+C1と同じ文字がC列にある場合D1に〇を付ける 3 2023/03/09 18:18
- Excel(エクセル) ある数値に対して、値を返す数式についてです 2 2022/09/13 22:06
- Excel(エクセル) エクセル テーブル機能の不明点 2 2022/04/14 14:10
- Excel(エクセル) ExcelのIF関数 2 2022/04/14 16:11
- Excel(エクセル) 【エクセル】参照セルに何も入力が無い場合の空白表示方法 1 2022/05/26 10:01
- Excel(エクセル) Excelの関数について質問です! 2 2023/06/08 13:54
- Excel(エクセル) Excelについて 4 2023/03/12 17:43
- Excel(エクセル) VBAで複数のExcelを1つのExcelにまとめる 1 2022/09/04 11:08
- Visual Basic(VBA) excelにて、特定の列に数字入力してあれば、入力してある行コピーして 別ファイルに張り付ける 2 2022/08/11 05:33
関連するカテゴリからQ&Aを探す
おすすめ情報
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
Excel2017 フィルタ昇順並びがA...
-
エクセルで行の高さ及び列幅の...
-
エクセルの時刻のカウントが出...
-
急ぎ!色のついたセルを非表示...
-
オートフィルタ後のデータから...
-
基準日以前のデータを範囲を指...
-
【Excel VBA】指定した行の最大...
-
エクセルで2つの郵便番号を比較...
-
検索条件に合うセルの個数を数...
-
【Excel】数式の参照範囲を可変...
-
エクセル 複数行ある同一商品...
-
Excel VBAでセルのクリアが出来...
-
文字列を比較し、相違するフォ...
-
Excelで並び替え後にア行...
-
EXCELで日付を比べ3か月以内の...
-
excel / ピポッド 日数を出したい
-
エクセルVBAでカレンダー作成し...
-
EXCEL 最終行のデータを他のセ...
-
タスクマネージャーについて(W...
-
エクセルで円グラフに引き出し...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
Excel2017 フィルタ昇順並びがA...
-
エクセルで行の高さ及び列幅の...
-
【Excel VBA】指定した行の最大...
-
Excelで並び替え後にア行...
-
急ぎ!色のついたセルを非表示...
-
エクセルの時刻のカウントが出...
-
オートフィルタ後のデータから...
-
基準日以前のデータを範囲を指...
-
EXCEL 最終行のデータを他のセ...
-
マクロで行の高さを設定したい
-
EXCELで日付を比べ3か月以内の...
-
エクセル関数について
-
文字列を比較し、相違するフォ...
-
エクセル VBA 行間隔を飛ばした...
-
Excel VBAでセルのクリアが出来...
-
【Excel】数式の参照範囲を可変...
-
excel / ピポッド 日数を出したい
-
時間の重複チェック
-
検索条件に合うセルの個数を数...
-
複数回答のクロス集計の方法
おすすめ情報
15、(空欄) 045-0000 045-0000 →Aが空欄なのでBの末尾が0000でもBを反映
16、(空欄) (空欄) (空欄) →飛ばす
17、452-0300 452-0312 452-0312 →両方8文字で末尾0000も無い場合はBを反映
後から追加ですみません。
ありがとうございます。
045-010
このようにハイフンがあって、後ろが4桁になっていないものは
人為的な入力ミスだと私は思っているので、そのままにしておくしかないかなと思っています。
Bが空欄という事は住所にもミスがあるか、村や市の合併で古くなっていますし。。。
何か案があるようでしたら、検討したいのでお聞かせ頂けますと幸いです。
宜しくお願い致します。
皆様、マクロ例など大変参考になるスクリプトを多数ありがとうございます。
明日、GW明け出勤しますので、早速試してみたいと思います。
結果が出たら追記致します。
皆様ご回答ありがとうございます。
下記のようにハイフンより前の3桁が同じである状況しか想定できていませんでした。
A B
055-2300 055-0000
実際は下記のように前3桁が違っていて、Bの末尾が0000のものが多数ある事が判明しました。
A B
030-0131 038-0000
現在の条件ですと、下記6番に該当し、A採用になるかと思いますが、Bを採用したいです。
A B C
6、225-0055 225-0000 225-0055 →両方8文字だがBは末尾が0000なのでAを反映
理由は、B列はつい1ヶ月前に住所列から最新の郵便番号DBを元に生成したもので、
A列は10年以上前から手入力で古くなっています。
B列の末尾が0000だった場合は住所不備で完全な郵便番号になっていませんが、
昔の郵便番号よりは近い番号になっているようです。
その為、
A B C
18、079-2300 055-0000 055-0000 →両方8文字でBの末尾が0000だった時、
前3桁の数字が一致した場合はAを、
一致しなかった場合はBを選択する
を追加したいです。
尚、No.8のご回答にて、この条件を考慮して頂いたマクロがあるとの事です。(No.13談)
No.7以降に頂いたサンプルに関しては、退勤してしまった為、まだ試す事が出来ておりません。
次回、5月12日(火)の出勤で試す事が出来ますので、その時にまた結果等をご報告させて頂きます。
>No.2 に書いた「データを処理する方針」や各数式の意味は、理解しましたか?
すみません。
勤務中は非常にバタバタしていまして、素っ気無い結果のみのお礼になってしまい失礼しました。
書式の変更で「0」と「-」が自動的に付く事は認知しておりますが、
普段データを記入する者の多くが、そういったエクセルの挙動を知らず、
長年にわたり入力やコピペを繰り返した結果、今のA列の状況を生んでいる為、
今回の整理で「ハイフンありの文字列に統一してしまった方が良さそうだ」というのが私の判断です。
No.11に記載して頂いている各条件理由の推測に関してはズレを感じます。
B列を優先している理由は、前述した通り、つい1ヶ月前に下記ページの「住所から郵便番号を入力する」を実行した最新の郵便番号だからです。
http://www.wanichan.com/pc/excel/2010/2/page06.h …
A列は10年以上前からの手入力なのでパターンが多いですが、B列は3パターンしかありません。
1、末尾が0000ではない郵便番号(完全体) ←全部これだったら良かった
2、末尾が0000の郵便番号
3、空欄
なので、この3パターンから条件を考えてみるとこうなりました。
B列が、
1、末尾が0000ではない郵便番号 → 無条件にB列採用 (整形無しでそのまま)
2、末尾が0000の郵便番号 → 整形したA列と比較して前3桁が不一致ならB列採用、一致ならA列採用
3、空欄 → A列採用し整形
となります。
最初からB列の3パターンから条件を提示した方がシンプルだったかもしれませんね。
お手数をお掛けして申し訳ありません。
10件以上の回答がつくのはなかなか珍らしく、ご注目ご注力頂き、感謝致します。
サンプルスクリプトはNo.8にあるSamp3を採用させて頂きましたが、
カスタマイズ性に優れ、スクリプトを見ただけで勉強になるのは
圧倒的にsoixante様のサンプルであった為、ベストアンサーはNo.1&No.15とさせて頂きました。
多数のご回答ありがとう御座いました。