エクセルの郵便番号検索

質問

こんにちは。

いろいろ探したのですが皆様のお知恵をお借りしたく
お願いいたします。

エクセルのA列に郵便番号ハイフン（-）付で7ケタの番号があるとします。
そこで同一の5ケタが何件あるかB列に５ケタの番号とC列に件数を出したい場合は
どうすればよろしいでしょうか？

よろしくお願いいたします。

回答No.3

shorun · ベストアンサー

図のようなイメージよろしければ次の式で表示できます。
B1セルに式　=LEFT(A,7)　を入力して、B1をB5(最終行)までコピーする。
C1セルに式 =IF(B1=B2,"",COUNTIF(B:B,B1))を入力してB1をB5(最終行)までコピーする。

ただし図の場合は、A列の番号は順序よく並んでいます。
もし、元データA列のデータ番号順が不揃いの場合は、
昇順(降順でもよい)に「データを並べ替え」てから上の式を入力してください。

ここで、並べ替えると元データの順番が乱れてしまいます。
元データの順番が乱れて困る場合は、「対策」が必要です。

「データを並べ替え」「対策」の方法がわからなければ補足で要求してください。

お礼

まさしくできました！
ありがとうございました！

非常に助かりました。

回答No.5

kagakusuki

確認したいのですが、

>同一の5ケタが何件あるか

とは、7桁の内で、最初の5桁のみを比べた場合の話なのでしょうか？
　それとも、末尾の5桁のみを比べた場合の話なのでしょうか？
　或いは、「112-3451」と「991-2345」の様に、途中に同じ並び方の数字が連続して５回現れている場合等も、同一の５桁が含まれいると見做すのでしょうか？

お礼

お目にとめていただきありがとうございます。
NO3の方が私の聞きたいことでした。

回答No.4

shorun

NO3です
B1セルに式　=LEFT(A,7)は間違っていました
=LEFT(A1,6) 正しいです。
失礼しました。

回答No.2

keithin

Ａ１から最大Ａ１００までに７桁の郵便番号（XXX-XXXX）が記入されているとして
Ｂ１に
=IF(ROW(B1)>SUMPRODUCT(($A$1:$A$100<>"")/COUNTIF($A$1:$A$100,LEFT($A$1:$A$100,6)&"*")),"",LEFT(INDEX(A:A,SMALL(IF(FREQUENCY(MATCH(LEFT($A$1:$A$100,6)&"*",A:A,0),MATCH(LEFT($A$1:$A$100,6)&"*",A:A,0)),ROW($A$1:$A$100)),ROW(B1))),6))
と記入して下向けにつるつるっとコピー貼り付ける

Ｃ１に
=IF(B1="","",COUNTIF(A:A,B1&"*"))
と記入して下向けにつるつるっとコピー貼り付ける
以上です。

＃参考
Ｂ列に=LEFT(A1,6)のようにして５桁の番号を切り出す
コピーして形式を選んで貼り付けの値のみ貼り付けて値化する
データタブの「重複の削除」を使ってダブり番号を消去する
（Excel2003以前を使っているときはデータメニューの「フィルタオプションの設定」を使って重複を削除する）
Ｃ列に前述のCOUNTIF関数を並べて個数を調べる

といった段取りで手を動かして作業すれば，こんなイミフメイの関数で苦労する必要は全くありません。

お礼

ありがとうございます。
結構みなさん詳しいんですね。

非常に助かりました。

回答No.1

aokii

B列に５ケタの番号を表示する式は、
=LEFT(A1,3)&MID(A1,5,2)

C列に件数を表示する式は、
=COUNTIF(B:B,B1)

B列とC列で下にドラッグコピー。

お礼

早速のご投稿ありがとうございます。
参考にさせていただきました。

非常に助かります。