[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『ハイフンなしで電話番号入力したデータをハイフン付きで表示する方法』（secretary）
お世話になります。 シート１のセルに入力したハイフンなしの電話番号を シート２のセルにVLOOKUPで連動させ、更にはハイフン付きで表示をさせたいのですが、 どのようにすれば宜しいでしょうか？ 尚、この電話番号は東京23区のみならず全国に及んでます。 書式→セル→表示形式→ユーザー定義→[<=999]000;[<=99999]000-00;000-0000と 設定してみましたが、うまくいきません。ご教示願います。
電話番号を数値として入力してしまうと、先頭の 0 が落ちてしまうと思いますが、 現状、その点は問題ないのでしょうか。
データを文字列として入力しているとすると、ユーザ定義での数式が機能しない気がします。 どううまくいかないか、書式設定がどうなっているかを説明いただけますか。 （Mook）
書式設定はどこで見ればよいのでしょうか？ また、文字列として入力しているか否かも、どこで見れば良いのでしょうか？ （書式→セル→表示形式→ユーザ定義でユーザー定義設定してみたのですが、 ハイフンがついた状態で表示されません。ただ10桁の数字だけが表示されてしまいます） (Secretary)
>シート１のセルに入力したハイフンなしの電話番号 これは、文字列になってますか？ 何個かセルを選択して、右下のステータスバーに計算結果が表示されれば「数値」になると思います
表示形式の「[<=999]000;[<=99999]000-00;000-0000」は【郵便番号】で【電話番号】では無いと思います…
電話番号(東京)→「[<=99999999]####-####;00-####-####」
こんなのもあるようです↓ kt電話郵便アドイン http://www.h3.dion.ne.jp/~sakatsu/TelPost.htm#Ver220
(Ohagi)
>シート１のセルに入力したハイフンなしの電話番号 → 数値設定いたしました。 >表示形式の「[<=999]000;[<=99999]000-00;000-0000」は【郵便番号】で【電話番号】では 無いと思います… → そうとは知らず、ユーザー定義に設定しておりました。【電話番号】はどう設定すれば良いのでしょうか？ その他の項目に電話番号（東京）というのが入っておりましたが、今回は全国の番号ですので、その設定方法をご教示頂きたく。よろしくお願い致します。（Secretary)
>数値設定いたしました 数値にすると先頭の「0」がぬけますので、「文字列」で無いと…(^_^;) 「[<=999]000;[<=99999]000-00;000-0000」を使うには「数値」でないと機能しないという意味です
電話番号は、表示形式だけでの設定は無理だと思います
(Ohagi)
電話番号（東京)は、最初の2桁を市外局番、次の4桁を局番、最後の4桁を番号、 と読んでいるだけのようです。 全国の場合は、市外局番、局番の桁数がばらばらなので、 単純に関数や表示形式では無理なように思います。 (sato)
Ohagi さんの紹介された kt電話郵便アドイン は秀逸だと思うのですがどうでしょう か。
既存のデータも変換してくれるので、今回の目的にも使用できると思います。 一度試してみてはいかがでしょうか。 (Mook)
Ohagi様、sato様、Mook様、色々なアドバイスありがとうございます。 今、kt電話郵便アドインを調べているところです。 経緯を詳しく申し上げますと、実は今回課題となっているデータはピボットテーブルを組んでいるエクセルのブックで、 シート１のセルA～セルXまで電話番号を始め住所や氏名等の企業情報データが800行（800件分）程入力されております。 一方同じブック内、シート10はA4サイズ設定の企業一覧フォームになっており、ある一箇所のセルにシート1の電話番号を手入力すると、 そのフォームにシート1に入力されている企業情報がVLOOKUP連動で表示されます。 ちなみに、このある一箇所のセルには「条件付入力」等の設定は一切ありません。 このシート10であるA4サイズ設定の企業一覧フォームの電話番号が表示されるセルのみ、 関数が壊れてしまい、壊れた状態で上書きしてしまい、バックアップもなく現在に至っております。 この関数が壊れてしまうまでは、東京23区のみならずそれ以外の全国も全て、電話番号がハイフン付きで表示されており、その状態に直したく今回質問ボードに書き込みをさせて頂きました。 このデータを作成された方は定年されて現在は連絡が取れない状態にあるため、 皆様のお力をお借りし、何とか復旧したい気持ちでおります。
ご存知の方いらっしゃいましたら、どうぞご教示賜りたく宜しくお願い致します。 ご返信下さった皆様、ありがとうございました。 (Secretary)
シート10の手入力する電話番号はハイホンなし、別のセルにハイホンつきで表示される・・・ ということでしょうか? 企業情報の電話番号はハイホンつきで入力されているのですか?
私なら、企業情報の電話番号はハイホン付きの文字列で入力しておき、 検索するときの入力セルにハイホンなしで入力したいなら、 企業情報の中に、SUBSTITUTE関数などでハイホンのない電話番号の列を設けておき、 それを元にVLOOKUPで検索・・・なんてことを考えるのですが、 実際のデータはどうなっているのでしょうね。
その辺のところが分かれば、解決の道もあるかもしれませんよ。 (sato)
>シート１のセルに入力したハイフンなしの電話番号 シート1の「ハイフンなしの電話番号」が検索されて、 企業情報が表示されていると思うのですが シート1に、「ハイフンつきの電話番号」列が非表示になっている… 何てこと、ありませんか？
(Ohagi)
引き続きのご教示本当にありがとうございます。
まず、 >シート1に、「ハイフンつきの電話番号」列が非表示になっている… についてですが、これは無いようです。
実際のデータを詳しく申し上げますと、 シート１のA受付日、ｾﾙB受付時間、ｾﾙC電話番号(ﾊｲﾌﾝなし)、ｾﾙD企業名、ｾﾙE住所･･･のように、 企業情報が現在約900列（900件分）入力されており、またシート10で表示させる項目に関しては 企業名のｾﾙには=VLOOKUP($C5&" ",シート10,2,FALSE)、 郵便番号のｾﾙには=LEFT(VLOOKUP($C5&" ",シート10,16,FALSE),3)&"-"&MID(VLOOKUP($C5&" ",シート10,16,FALSE),4,4) 住所のｾﾙには=VLOOKUP($C4&" ",シート10,18,FALSE)等の関数が設定されております。 電話番号(ﾊｲﾌﾝなし)のｾﾙには何も関数の設定はされておりません。
ｾﾙCに入力されているﾊｲﾌﾝなしの電話番号は 企業コードとして使用しており、検索・管理する際には全てこの番号で管理しておリます。
シート2にはピボットテーブルフィールドのデータ、シート3以降も関連データ類が続きシート10に企業情報のフォームがあります。
このシート10のあるｾﾙに、管理コードとして使用しているシート１のｾﾙCに入力した電話番号(ﾊｲﾌﾝなし）を手入力すると、 シート10企業情報のフォームのそれぞれの項目には、シート１で入力したｾﾙD企業名やらｾﾙE住所等、必要なデータが表示される仕組みになっているのです。 このシート10のそれぞれの項目を表示させるセルにも以下のような関数が設定されてます。 企業名を表示するｾﾙ=VLOOKUP($G$1,シート1!$C$2:$Z$998,2,FALSE) 住所を表示するｾﾙ=VLOOKUP($G$1,シート1!$C$2:$Z$998,5,FALSE) 電話番号を表示するｾﾙ=VLOOKUP($G$1,シート1!$C$2:$Z$998,1,FALSE)
現在、シート10には、シート1に入力されている情報がそっくりそのまま表示されており、電話番号以外はこれでOKです。 電話番号だけ、そっくりそのまま表示させるのではなく、ﾊｲﾌﾝ付で表示させたいのです。 （関数が壊れてしまうまでは、出来ていた）
データの状況はこんな感じです。 昨晩、エクセルの本を色々と見てみましたが、ダイレクトに答えとなるようなものは 載っておらず、解決出来ずにおります。
宜しくお願い致します。
(Secretary)
シート名「シート１」にVLOOKUP関数が使用されているのですよね？ C5セルに入力されている値(多分電話番号)を検索値として、 シート10と言う名前の範囲から各データを抽出しているようです… 数式バーの左隣に「名前ボックス」があるとおもいますので 「▼」を押して表示させ、シート10と言う名前の範囲がどこか確認してみて下さい （住所だけ検索値が「$C4」ですが…）
シート名「シート10」は、G1セルを検索値としてシート名「シート1」の$C$2:$Z$998から抽出しているみたいです >=VLOOKUP($G$1,シート1!$C$2:$Z$998,１,FALSE) で、電話番号を表示となってますが、検索列(一番左)と抽出する列(1列目)が一緒？？ 参照列の数が間違っているとしたら、シート名「シート1」の$C$2:$Z$998のどこかの列に 【ハイフンつきの電話番号が存在する】はずですよ？
(Ohagi)
Ohagi様 ご教示ありがとうございます。 Ohagi様のご指摘通り、シート１で使用されているVLOOKの関数は「シート10」ではなく 「シート3」から持ってきているようです。失礼致しました。
ちなみにシート3にはﾊｲﾌﾝ付の電話番号が入力されているｾﾙもございます。 シート１でVLOOKPU関数が使用されているのは、ｾﾙD（企業名）ｾﾙE（郵便番号）ｾﾙG（住所）で、 この3項目は全てシート3から持ってきてます。 ただ、管理コードとしても使用している電話番号(ﾊｲﾌﾝなし）はｾﾙCに入力されており、 何の関数も設定されておりません。 シート3のﾊｲﾌﾝ付電話番号をシート1を経由せずダイレクトにシート10に？と一瞬考えましたが、 そうするとどうしても納得のいかない箇所がございます。
それは、シート3とシート1の情報が全く同じではないということです。 シート1は（900件の企業情報)、シート3の情報(600件の企業情報）に新たに新規の300件を加えて作成したものであり、 シート3には入力されていない（シート1にしか入力されていない)300件の企業情報も、 これまでシート10のｾﾙGにﾊｲﾌﾝなしの電話番号を手入力すると、ﾊｲﾌﾝ付で表示させることができていたのです。 シート1内にﾊｲﾌﾝ付の電話番号が存在しないか、C2セルからZ998セルまで探してはみましたが、 どこにも見当たりません。Aセルから横に探しましたが、アルファベット順にセルも並んでおり、 どこも隠れている部分も見当たりませんでした。シート10の電話番号を表示させる項目のセルには、 一体どんな関数が設定されていたのでしょうか。。。。バックアップを取っていなかった事をただ悔やむばかりです。
Secretary
おそらくですが…シート3を探しても無いもの(新規追加の300件)は エラーとなるとおもいます なので、エラーとなるものは「別のシートから抽出する」などの回避がされていたのでは？
シート1のデータでVLOOKUPで抽出していない企業が、新規追加分となりますよね？ この分の「ハイフン無の電話番号」がそのブックに存在していないか調べてみてはいかがでしょうか Shiftを押しながらシートすべてを選択して、編集→検索→検索する文字列「新規追加のハイフン無の電話番号」→「すべて選択」 その電話番号が存在するすべてのシートが分かるとおもいます そのシート内に「ハイフン付きの電話番号」が無ければ、他の方法でつけているのではないでしょうか？
シート3にある「ハイフン付き電話番号」をシート1に反映させて シートを整理することをお奨めします… あと「シート名＝名前」をすると今回みたいにややこしくなりますので この際に修正してみてはいかがでしょうか？
(Ohagi)
※出来ましたら、文章の先頭に「半角スペース」を入れて下さい(改行できます) また、半角カナはあまり歓迎されないのでご使用は控えた方が良いですよ？
Ohagi様 ご教示ありがとうございます。私の説明力不足でうまく伝わらなかったようです、すみません。 シート10のデータは、全てシート1からVLOOKUPで飛んできており、そのシート1についてご説明申し上げますと、 データの600件分は更にシート3から飛んできており、新規の300件は手入力されたものです。 ですから、2行目から601行目までのセルＤ、セルＥ、セルＧはそれぞれシート3からのVLOOKUP関数が設定されており、 602行目から901行目までのセルＤ、セルＥ、セルＧは全て手入力によるもの、すなわち関数の設定が何もございません。 そして、シート10についてはセルＧ1にハイフンのない電話番号を手入力すると、シート10の全ての項目は、 シート1から飛んできた内容で埋まる仕組みになっております。 （電話番号を除く全ての項目がシート1から飛んで来る。シート1で手入力した新規300件分に関してもきちんと反応する） こんな状況にございます。 それと、ご教示いただいた方法で、シート全てを選択した状態で新規300件のうち幾つかランダムに選んで ハイフンなしの電話番号を検索してみましたが、結局はシート1内の電話番号を入力したセルＣのみで、他にはどこも見つかりませんでした。
Secretary
シート1に一列追加(ハイフン付電話番号)して、シート３より抽出 602行目から901行目は「kt電話郵便アドイン」を使用する… シート10への抽出はシート1から行えるようになります
>シート10のデータは、全てシート1からVLOOKUPで飛んできており シート3からシート1へ「ハイフン付の電話番号」が参照されていた列を削除したような気がしますが？ その列は多分602行目から901行目の新規追加企業分は、ハイフン付き電話番号を抽出ではなく、 直接入力されていたような気がします だから、検索しても「新規追加分のハイフン付電話番号」は見つからず、 現在、「ハイフン付きの電話番号」をシート10で抽出しようとしても表示されなくなっているのではないでしょうか？
(Ohagi)
【追加】 先にも回答しましたが、シート10の「ハイフン付き電話番号」の式はおかしいです >>=VLOOKUP($G$1,シート1!$C$2:$Z$998,１,FALSE) >で、電話番号を表示となってますが、検索列(一番左)と抽出する列(1列目)が一緒？？ 「シート10のハイフン無電話番号」をキーに「シート1のハイフン無電話番号」を抽出しようとしてませんか？
色々とアドバイス頂きましてありがとうございます。 やはり、ハイフンなしの電話番号をダイレクトにハイフン付きで表示するような関数などないですよね。 しかも全国区対応などなおさら。 これまで出来ていたとなると、やはりブック内のどこかのシートにハイフン付きの電話番号が潜んでいるとしか 考えられませんよね。 Ohagi様のアドバイス通り、kt電話郵便アドインを使用してみます。 それと、【追加】の件につきまして、仰る通りです。本来ここには「ハイフン付の電話番号を表示させるある関数」 が入っていたのですが、その関数が壊れてしまい、 今回こちらの質問ボードのお世話になっている次第でございます。 現在入っているおかしい関数は、緊急措置としてハイフンなしの電話番号をシート１から抽出している状態です。 では、今からkt電話郵便アドインをダウンロードしてみます。 この度は色々とご助力頂きまして、ありがとうございました。 今後共、何卒宜しくお願い致します。 Secretary
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.