[[20090716232143]]　『電話番号の表示』（brain）　ページの最後に飛ぶ

『電話番号の表示』（brain）

 はじめましてbrainと申します♪
 下記内容についてご教授いただきたいので
 何卒、回答の程よろしくお願いいたします。

 [内容]
 A列に電話番号を入れたいのですが、番号を入力する時に
 0661601654というようにベタ打ちしたく思います。
 ベタ打ちされた番号は自動的に06-6160-1654というように
 ‐が入るようにしたいです。
 どのようにすれば出来ますか？

 市外局番等は、場所によって桁数が変わります。
 携帯電話やIP電話等の番号でも、「-」の位置が変わります。
 （最初の区切りだけでも、2桁から5桁まで）
 入力する番号は、必ず
 　二桁-四桁-四桁
 なのでしょうか？

 そうでないなら、市外局番を振り分けるのが
 大変になると思いますが。。。

 また
 ＞ 0661601654というようにベタ打ち
 して、表示だけ変えると
 電話番号に見える9桁の値
 （本来数字を数えると　2+4+4=10桁　ですが）
 になってしまいます。

 そのあたりの扱いも、どうなれば良いのか
 先に考えておくのが良いと思いますが。。。

 (HANA)

 ・・・そんな面倒なことはどうでも良いですかね。（笑）

 セルの書式設定を「00-0000-0000」

 後で問題が出てくるかもしれませんが。

 (HANA)

HANA様

 ありがとうございます。
 ご察しのとおり市外局番の桁数が違う事が原因で困っています。
 下記のようになるようにしたいのです。
 一般電話、携帯電話に対応したいです。

 [入力]           　　[結果]

 0661601654           (06)6160-1654   
 0759629698           (075)962-9698
 0726345967           (07)2634-5967
 09085264561          (090)8526-4561

 (brain)

 ＞ご察しのとおり市外局番の桁数が違う事が原因で困っています。
 でしたら、「後で一気に」なんて考えないのが良いと思いますよ。

 誰かがエクセルに「これは3桁」「これは2桁」って
 一行ずつ教えていく羽目になります。

 「それでも良い」なら、隣の列へ数式を使って判定をしながら・・・
 なんて方法は　割と簡単なところでとれると思います。

 例えば、先頭に市外局番の桁数を入れながら入力するとか
  20661601654
  30759629698
  20726345967
  309085264561

 あとは、Tabキーも駆使しながら　列を分けておくと
 後で「-」を加えながら結合は　簡単にできると思います。

 「番号だけ入力したら、勝手に自動で市外局番を認識してくれて・・・」
 とか考えておられるなら、素直に入力する方が早いと思います。

 (HANA)

HANA様

 ありがとうございます。
 実は入力するデータそのものがベタ打ちの0661601654というようなデータで市外局番が
 判らないような感じなので入力担当者が困ってるのが現状なのです（＾＾；）

 別のシートに市外局番のリストを作っておいて、前3桁がリスト内の市外局番に
 該当するものならば○○桁で区切る・・・
 というような式でもB列に入れたら出来ますかね？
 すいません何とか良い方法を見出したいので、ご教授願えますでしょうか。

 (brain)

 ＞別のシートに市外局番のリストを作っておいて
 これが可能なら、ある程度は出来るとは思いますが・・・
 それでも難しいと思います。

 お話としては
	[A]		[B]	[C]	[D]
[1]	0661601654	2		06	
[2]	0759629698	3		075	
[3]	0726345967	2		07	
[4]	09085264561	3		090	
[5]	0422874215	4		0422	
 D列が対応表。
 B1セルに
=IF(COUNTIF($D$1:$D$5,LEFT(A1,4)),4,IF(COUNTIF($D$1:$D$5,LEFT(A1,3)),3,IF(COUNTIF($D$1:$D$5,LEFT(A1,2)),2,"なし")))
 としてフィルドラッグ。

 (HANA)

 もしかしたら何らかのフリーソフトがあるのかもしれませんが

 市外局番がわからない
 ベタ打ちの数字しか存在しないデータということなら
 難しいと思います。

 > 前3桁がリスト内の市外局番に 該当するものならば○○桁で区切る・・・

 たとえば北海道ですと
 市外局番が 015 だったり 0154 だったり 01547 だったりします。
 0154723333 ← 数字だけでは市外局番を判断できませんよね？

 ですのでまずは そのもとのデータを
 市外局番－市内局番－電話番号 と
 調べて分けるしかないように思います。

 （ていおう）2009/7/17 0:52

HANA様、ていおう様

 ご丁寧に応対頂き、ありがとうございます。
 まずは市外局番のリストを入手し、何かキーワードになるものを探してみます♪
 そのキーワードを基に市外局番－市内局番－電話番号を振分けれる方法を探ろうと
 思います。
 飽くなき探求を僕はしていきたいと思うので何か方法を見出していこうと思います。
 あと、HANAさんが教えてくれた式を試してみますね♪
 また結果報告します。

 （brain)

 ＞たとえば北海道ですと
 ＞市外局番が 015 だったり 0154 だったり 01547 だったりします。
 確かにそうなんですが。。。。
 01547で始まっているのが　実は
 0154-7・・・だったり、015-47・・・だったり
 ってのはあまり考えにくいと思うんですよね。。。

 実際はどうか分かりませんが、
 市外局番が0154の物の中には次の桁が「7」で始まる物は無い。
 （7が続く物は、市外局番が01547である）
 と言う考え方で良いのなら、桁数の多い方からマッチングしていけば
 良さそうに思います。

 しかも、おそらく「-」を入れたい理由は
 　数字ばかりが並んでいると見にくい
 　かといって全く関係なく「-」を入れるのもどうかと思うので
 　なるべく　正規の区切りで入れたい
 程度の事だと思います。

 本当に、市外局番を省略して　電話を出来る地域の人なら
 市外局番が何番なのか知っているはずですから。。。。

 まず、現在分かっている物に関しては　面倒でも「-」を入れながら
 入力するのが良いと思います。

 そうでないものは、先ほどのサンプルでは　数字を全て文字列扱いにして
 先頭の「0」から入れていますが、面倒なので　省略して入力するのが良いと思います。

 先頭の0を省略して入力した場合（A列）
 対応表の先頭の0も省略して入力し（E列）
 B列に市外局番の桁数を
 C列に　先頭に0と所定の位置に「-」が入った番号を表示するには
 B1に
=IF(COUNTIF($E$1:$E$5,LEFT(A1,4)),4,IF(COUNTIF($E$1:$E$5,LEFT(A1,3)),3,IF(COUNTIF($E$1:$E$5,LEFT(A1,2)),2,IF(COUNTIF($E$1:$E$5,LEFT(A1,1)),1,"なし"))))
 C1に
="0"&REPLACE(REPLACE(A1,LEN(A1)-3,,"-"),B1+1,,"-")

        [A]     [B]      [C]       [D]   [E]
[1]   661601654  1  06-6160-1654            6
[2]   759629698  2  075-962-9698           75
[3]   726345967  1  07-2634-5967            7
[4]  9085264561  2  090-8526-4561          90
[5]   422874215  3  0422-87-4215          422

 これで、有る程度それらしき物には成ると思います。

 (HANA)

HANA様

 ありがとうございます。
 入力する担当者はあくまで電話番号のベタ打ちのみを希望しているので
 Ｂ列に桁数を入れる等が出来ないんですよ（＾＾；）

 今現在、別シートに市外局番のリストを取り込みました。
 そして、下記の段階までなんとか四苦八苦しながらきています。

 〔現在の状況〕

 シート1
    入力番号                       市外局番　　　　　中番　　　　　　後4桁
 　 　A                 B              C               D               E
 1 0661601654                          06                             1654
 2 0759629698                          075                            9698
 3 09085201782                         090                            1782
 4     :                                :                               :
 5     :                                :                               :

 シート2

　市外局番の一覧

                 ※Ｂ列もA列と同じ番号です
         A         B
 1    00011　　　00011
 2　　00166　　　00166
 3　　00155　　　00155
 4      :          :
 5　　　:          :

 ※シート1のC列に強引かもしれませんが下記式をいれて市外局番を割り出しました。
    =(IF((ISERROR(VLOOKUP(LEFT(A1,5),Sheet2!$A$2:$B$200,2,FALSE))),0,
        VLOOKUP(LEFT(A1,5),Sheet2!$A$2:$B$200,2,FALSE)))
      +(IF((ISERROR(VLOOKUP(LEFT(A1,4),Sheet2!$A$2:$B$200,2,FALSE))),0,
        VLOOKUP(LEFT(A1,4),Sheet2!$A$2:$B$200,2,FALSE)))
      +(IF((ISERROR(VLOOKUP(LEFT(A1,3),Sheet2!$A$2:$B$200,2,FALSE))),0,
        VLOOKUP(LEFT(A1,3),Sheet2!$A$2:$B$200,2,FALSE)))
      +(IF((ISERROR(VLOOKUP(LEFT(A1,2),Sheet2!$A$2:$B$200,2,FALSE))),0,
        VLOOKUP(LEFT(A1,2),Sheet2!$A$2:$B$200,2,FALSE)))
      +(IF((ISERROR(VLOOKUP(LEFT(A1,1),Sheet2!$A$2:$B$200,2,FALSE))),0,
        VLOOKUP(LEFT(A1,1),Sheet2!$A$2:$B$200,2,FALSE)))

 今、僕の描いている思惑はベタ打ち番号を一度、市外局番と中番号と後4桁に
 バラして＆関数でくっつけようかと思っています。
 中番号をどうやって抽出しようか今悩んでいるところです。

 (brain)

 対応表が必要なのは、お分かりですよね？

 でしたら、私の書き込みをもう一度お読み下さい。

 私は数式でB列を求める方法をかいているつもりですが？

 (HANA)

HANA様

 申し訳ございません。
 僕の読み違いでした。

 ＞対応表の先頭の0も省略して入力し（E列）
 というのは・・・
 もう一度読み直すとA列にベタ打ちして、C列が結果で、C列の結果を求める為にB列が必要で
 B列を求めるためにE列の対応表が必要になる。という理解で良いでしょうか？
 E列は都度入力が必要になるのでしょうか？

 (brain)

 ごめんなさい・・・

 Ａ列に打ち込んだベタ打ちの電話番号をＡ列で希望の形に変換したいとの事で
 Ｂ列に結果が出てもダメとの事（＞＜；）
 やり直しします。
 ＶＢＡでないと無理ですよね？

 (brain)

 逆に聞きますがべたうちした電話番号でどんな不都合があるのですか？
 入力セルと集計（結果）セルは分けるというのがエクセルの基本的な考え方です。
 べたうちで-も入れられないほど忙しいのでしたら結構打ち間違えも頻繁にあるような気が致します。
 回答でなくて申し訳ないですが、あまりにもなので。。。
 （とおりすがっ太）

 元データは市外局番が分かれていなくて、
 分けながら入れるのが面倒だし
 実際の所どう分ければ良いのか分からないから問題だ

 と言う事だと思っていますが？

 (HANA)

とおりすがっ太様

 ①表を見る方（チェックする人と利用する人）・・・『ベタ打ち状態のままだと見づらい！』
 ②入力する方・・・『いちいち()や-をいれてられない』
 　                『そもそもどこまでが市外局番か判らん！』

 ①は大変上層部のおえらいさんなので『我慢しろ』とは言えなくて・・・

 ②を説得するにしても『市外局番の判別が難しい』と言われれば確かに判別しながらの
   入力は時間も労力も大きく違ってきますので今回皆様にお力を借りようと思った次第です。

 ＨＡＮＡ様には昨日より、ご助言と御協力をいただき大変感謝いたしております。
 何卒、あたたかいご支援を賜りますようよろしくお願いいたします。

 （brain)

 ①についてですが、市街局番がつく＝視認性が良くなるではないと思いますので別アプローチとして
 　数字に一定の規則を設けたらどうでしょう？
 　具体的には000-0000-0000　場合によっては　000-0000-000や000-0000-00となりますが表になると
 　000-0000-0000
 　000-0000-00
 　000-0000-000
 　となり、頭がそろっているので視認性はよくなるのではないでしょうか？
 　検討していただけないか確認してみてください。

 ②についても上記の解決案で決着がつくと思います。
 マクロを使用して入力と同時に区切ることはたやすいと思います。

 いかがでしょう？
 （とおりすがっ太）

とおりすがっ太様

  ありがとうございます！
　確認に時間が少しかかりますが本日中に確認してみます♪

 (brain)

とおりすがっ太様

 ダメでした・・・
 （市外局番）○○○○-○○○○にしなさいと指定されました（；；）

 さて、どうしたものか・・・。

 (brain)

 まさに
 ＞①表を見る方（チェックする人と利用する人）・・・『ベタ打ち状態のままだと見づらい！』
 ＞②入力する方・・・『いちいち()や-をいれてられない』
 ＞　                『そもそもどこまでが市外局番か判らん！』
 この様な時によく言われるのが、とおりすがっ太さんが書いておられる
 ◆入力セルと集計（結果）セルは分けるというのがエクセルの基本的な考え方です。
 です。

 入力は別のセルへして（IV列とか。笑）
 A列に数式で表示すれば良いのでは？

 　って言っても、大抵却下されますけどね。
 　そちらからも、○○は××で駄目だ。という情報を
 　先に載せて頂けると良いのですが。
 　詳しい情報（手順や、データの構成）等も。

 いずれにしても、ロジックは必要だと思いますけどね。

 (HANA)

 いろいろ電話番号のあれこれについてみてきましたが…
 なんとなく光明が見えてきました。

 0　…　一般家庭向けの頭番号
 1～4ケタ　…　市外局番
 1～4ケタ　…　市内局番
 4ケタ　…　加入者番号

http://www.benri.com/areacode/

 ここで例えば検索で0123と入力すると、0123　01235　01237　01238が検索されます。
 検索文字列を0123-5　0123-7　0123-8と検索してもでてきません。

 同様に042で検索すると0422　0424　0428　0429が検索されます。
 検索文字列を042-2　042-4　042-8　042-9で検索してもでてきません。

 上記のことから連続する市外局番が存在する場合、市内局番はかぶらないように設定されている
 と推察できますので、市外局番を4ケタから検索することで判別が付くと思います。

 それで、マクロはOKですか？（）笑

 （とおりすがっ太）

 使ったことはないのですが
 ↓でハイフン編集ができるようです。

 kt電話郵便アドイン
http://www.h3.dion.ne.jp/~sakatsu/TelPost.htm
http://www.h3.dion.ne.jp/~sakatsu/TelPost_Saport.htm

 （R）

 ごめんなさい
 さっき提示したところは「市外局番」しか調べられなかったとです…。

http://www.ntt-east.co.jp/info-st/mutial/suburbs/numlist/index.html

 こちらを見る限り番号領域によって分けられているそうなのですが、
http://www.ntt-east.co.jp/info-st/mutial/suburbs/numlist/pdf/ma_area1.pdf
 最初のこのページから015　の次に　015410～6　とか書かれちゃっているのでもうどうしようかとorz

 市外局番だけを拾うのは無理とわかりましたので、この番号領域のデーターベースを作成して、そこから割り出す方法が妥当かもしれません。
 Rさんが提示されている方法もよいと思いますが、おそらく作業は会社のパソコンでしょうから、勝手にアドインを入れてもよいものなのか。
 入力者のパソコンも複数あるようだったら総てに入れなくてはならない等弊害があると思います。

 （とおりすがっ太）

R様、とおりすがった様

 ありがとうございます。
 とおりすがっ太様の言うようにアドインを入れる許可もらえませんでした。

 一応、僕が考案したのは下記内容です。
 A列にベタで入力するだけでB列の結果が返せるんですが、それじゃダメでA列を
 B列のような形に、そのまま自動変換する事を望んでいます。

  シート1
    入力番号                       市外局番　　　　　中番　　　　　　後4桁
 　 　A                 B         C     D    E          F     G        H
 1 0661601654     (06)6160-1654   （   06　　）       6160    -       1654
 2 0759629698     (075)962-9698   （   075   )         962    -       9698
 3 09085201782    (090)8520-1782  （   090   )        8520    -       1782
 4     :                                :                               :
 5     :                                :                               :

 シート2
　市外局番の一覧

                 ※Ｂ列もA列と同じ番号です
         A         B
 1    00011　　　00011
 2　　00166　　　00166
 3　　00155　　　00155
 4      :          :
 5　　　:          :

 ※シート1のD列に強引かもしれませんが下記式をいれて市外局番を割り出しました。
    =(IF((ISERROR(VLOOKUP(LEFT(A1,5),Sheet2!$A$2:$B$200,2,FALSE))),0,
        VLOOKUP(LEFT(A1,5),Sheet2!$A$2:$B$200,2,FALSE)))
      +(IF((ISERROR(VLOOKUP(LEFT(A1,4),Sheet2!$A$2:$B$200,2,FALSE))),0,
        VLOOKUP(LEFT(A1,4),Sheet2!$A$2:$B$200,2,FALSE)))
      +(IF((ISERROR(VLOOKUP(LEFT(A1,3),Sheet2!$A$2:$B$200,2,FALSE))),0,
        VLOOKUP(LEFT(A1,3),Sheet2!$A$2:$B$200,2,FALSE)))
      +(IF((ISERROR(VLOOKUP(LEFT(A1,2),Sheet2!$A$2:$B$200,2,FALSE))),0,
        VLOOKUP(LEFT(A1,2),Sheet2!$A$2:$B$200,2,FALSE)))
      +(IF((ISERROR(VLOOKUP(LEFT(A1,1),Sheet2!$A$2:$B$200,2,FALSE))),0,
        VLOOKUP(LEFT(A1,1),Sheet2!$A$2:$B$200,2,FALSE)))

 (brain)

 Aにベタ打ちされたらBの数字をAに置き換えるというマクロかVBAが出来ないですかね？
 僕にマクロやVBAの知識が無いので、無茶を言ってたらごめんなさい（＾＾；

 (brain)

 ◆途中からなんですが、こんな方法はいかがでしょう
	A	  B	         C	D
1   0661601654	(06)6160-1654		0725
2   0759629698	(075)962-9698		0721
3   09085201782	(090)8520-1782		090
4   0726665555	(072)666-5555		080
5   0725411234	(0725)41-1234		075
6   0721234567	(0721)23-4567		072
7				         06

 ★D列に関係する市外局番を、桁数の多い順に入力します
 B1=IF(A1="","","("&REPLACE(REPLACE(A1,LEN(A1)-3,,"-"),LEN(INDEX($D$1:$D$7,MATCH(1,INDEX(COUNTIF(A1,$D$1:$D$7&"*"),),0)))+1,,")"))
 ★下にコピー

 (Maron)

 素朴な疑問

 入力のもとになる
「分割されていない数字だけの電話番号」のデータは
 どれくれいあるのでしょうか？

 もとのデータの「市外局番」はすべて調べたのでしょうか？

 もし調べていないなら
 Excelではなく違った方法で「市外局番」を調べるのが先のように思います。

 （えくせる青年）2009/7/19 12:05

 市外局番の一覧
http://www.soumu.go.jp/main_sosiki/joho_tsusin/top/tel_number/shigai_list.html

 こちらから、市外局番部分を重複なく抜き取ると389件見つかりました。
 （それに、090を入れると390件になりますね。）
 ご提示の数式では$A$2:$B$200と、199件の様ですので
 少ないのではないかと思いますが。。。？

 実際に入力される地域を考えて、199件になっているのでしょうか？

 また、念のためにお伺いしてみますが
 ＞もう一度読み直すとA列にベタ打ちして、C列が結果で、C列の結果を求める為にB列が必要で
 ＞B列を求めるためにE列の対応表が必要になる。という理解で良いでしょうか？
 ＞E列は都度入力が必要になるのでしょうか？
 この質問は現行でしょうか？
 それとも、その下の

 ＞やり直しします。
 ＞ＶＢＡでないと無理ですよね？
 の書き込みによって、「質問としては残っていない」
 と考えて良いのでしょうか？

 それから
 ＞Aにベタ打ちされたらBの数字をAに置き換えるというマクロかVBAが出来ないですかね？
 出来ると思いますよ。
 でも、使用人数（必要なブック数）は何人（何個）なんでしょう？

 また、市外局番は普遍的なものではありませんので
 結局は「ある程度」のものしか出来ないと思いますが
 その点は大丈夫なんですよね？

 (HANA)

コメント返信：

[ 一覧(最新更新順) ]

[[20090716232143]] 『電話番号の表示』（brain） ページの最後に飛ぶ

[[20090716232143]]　『電話番号の表示』（brain）　ページの最後に飛ぶ