エクセル 書式設定・入力規則・関数等教えて下さい。

質問

先日は、わたくしの質問に回答を下さった皆さま、誠にありがとうございました。
日本人の優しさに感謝しております。

また、
エクセルで書式や関数等をご教授いただきたくお願いいたします。
全くの初心者でありますため、お恥ずかしい質問になりますが、どうかお助け下さい<(_ _)>
質問内容は、下記の２つです。

【質問内容です】
(1)　Ｂ列にドロップダウンリストで「050」を強制的に選ばせるようにしました。
　　（選択肢は１つなので、わざわざと思われそうですが、０５の後にハイフンを入れる
　　ような方もいらっしゃいますので・・・・）
　　セルの書式設定で050の後にハイフンが表示するようにしています。

　　ここから先がどうしたら良いのか分かりません。
　　Ｃ列に７桁の数字だけを入力してもらい、「５〇〇〇ー〇〇〇〇」としたいのです。
　　セルの書式設定のユーザ定義「５000"-"0000」とし、データの入力規則で「文字列（長さ
　　指定）」長さを「７」としてみたのですが、
　　初めに「０」（ゼロ）を入力するとエラーになってしまいます。
　　Ｃ列をセルの書式設定で「文字列」に設定してないからなのかな？と思いますが、強制的に
　　先頭に５を表示させたいのです。
　　関数は、さっぱり分かりません。
　　先日、回答をいただきながらも、私の知識が乏しいため、出来ないものもありましたため、
　　可能な限り簡素な方法をご教示いただきたく、お願いいたします。

(2)　Ｂ列にＩＰアドレスを入力してもらうため、数字と.（ドット）以外入力出来ないように
　　入力規制をかけたいのです。
　　（ＩＰアドレスの先頭に０「ゼロ」がある場合もあるのでしょうか？その辺のところも
　　加味していただけますと幸いです）

　　以上、２つの質問になりますが、何卒よろしくお願いいたします。

回答No.3

kagakusuki · ベストアンサー

回答No.1です。

>質問内容の
>(1)０５０電話番号と
>(2)のＩＰアドレスは、全くの別もの（別ファイル）の質問でございます。

>(1)と(2)を全く分けていただくことは可能でしょうか？

　早とちりをしてしまい申し訳御座いません。
　しかしそうしますと、御質問文を良く読んだ限りでは、(1)に関しましても、単に

>Ｃ列をセルの書式設定で「文字列」に設定してないからなのかな？と思いますが、強制的に先頭に５を表示させたいのです。

という点を実現出来さえすれば良いという意味に受け取れる様に思えます。
　それにも関わらず、何故、

>Ｂ列にドロップダウンリストで「050」を強制的に選ばせるようにしました。
>（選択肢は１つなので、わざわざと思われそうですが、０５の後にハイフンを入れる9ような方もいらっしゃいますので・・・・）
>セルの書式設定で050の後にハイフンが表示するようにしています。

という話を述べておられるのかが良く解りません。

>Ｂ列にドロップダウンリストで「050」を強制的に選ばせるようにしました。
>セルの書式設定で050の後にハイフンが表示するようにしています。

という話が、本件にどう関係して来るのかという事を御説明願えませんでしょうか？

　後それから、入力した7桁の数値をセルの書式設定で「5〇〇〇-〇〇〇〇」としたとしましても、それはあくまでモニターで見た時や印刷した時に「5〇〇〇-〇〇〇〇」に"見える"だけであり、セルに入っているデーターそのものは入力した時の7桁の数値のままで、「5〇〇〇-〇〇〇〇」になっている訳ではありませんので、例えば「050」とC列の値を組み合わせても「050-5〇〇〇-〇〇〇〇」にはなりませんが、その点は御承知の事なのでしょうか？

　上記の様に不明な点が若干残ってはおりますが、取りあえずB列の値とは関係なしに

>Ｃ列をセルの書式設定で「文字列」に設定してないからなのかな？と思いますが、強制的に先頭に５を表示させたい

という御質問に対する直接的な回答としましては以下の様なものになります。

C列のセルの中で、書式と入力規則を設定したいセルを選択
　　↓
Excelウインドウの上の方にある[データ]タブをクリック
　　↓
現れた「データツール」グループ内の[データの入力規則]ボタンをクリック
　　↓
現れた選択肢の中にある[データの入力規則]をクリック
　　↓
現れた「データの入力規則」ダイアログボックスの中にある[設定]タブをクリック
　　↓
現れた「入力値の種類」欄をクリック
　　↓
現れた選択肢の中にある[整数]をクリック
　　↓
現れた「最小値」欄に

0

と入力
　　↓
「最大値」欄に

9999999

と入力
　　↓
「データの入力規則」ダイアログボックスの[入力時メッセージ]タブをクリック
　　↓
現れた「セルの選択時にメッセージを表示する」と記されている箇所にチェックを入れる
　　↓
「メッセージ」欄に

電話番号の末尾の7桁の数字のみを入力して下さい。
「-」(ハイフォン)等の数字以外の文字や記号は入力しないで下さい。

と入力
　　↓
「データの入力規則」ダイアログボックスの[エラーメッセージ]タブをクリック
　　↓
現れた「無効なデータが入力されたらエラーメッセージを表示する」と記されている箇所にチェックを入れる
　　↓
「スタイル」欄をクリック
　　↓
現れた選択肢の中にある[停止]を選択してクリック
　　↓
「タイトル」欄に

無効な値が入力されました

と入力
　　↓
「エラーメッセージ」欄に

電話番号の末尾の7桁の数字のみを入力して下さい。
入力出来るのは
0 1 2 3 4 5 6 7 8 9
の10個の半角数字のみからなる7桁以下の数値のみです。
「-」(ハイフォン)等の数字以外の文字や記号は入力できません。

と入力
　　↓
「データの入力規則」ダイアログボックスの[OK]ボタンをクリック
　　↓
選択範囲を変更せずに、選択範囲を示す黒い太枠の内側を右クリックして、現れた選択肢の中にある[セルの書式設定]をクリック
或いは
[Ctrl]キーを押しながら[1 ! ぬ]キーを押す
　　↓
現れた「分類」欄の中にある[ユーザー定義]をクリック
　　↓
現れた「種類」欄に

"5"000-0000

と入力
　　↓
「セルの書式設定」ダイアログボックスの[OK]ボタンをクリック

　上記の様に設定しますと、例えば

1234567

を入力しますと

5123-4567

と表示されます。(セルに入力されているデータは1234567のままです)
　又、例えば

1

を入力しますと

5000-0001

と表示されます。(セルに入力されているデータは1のままです)
　又、例えば

0000001

と入力しますと

5000-0001

と表示されますが、セルに入力されているデータは1になります。

　(1)に関しては以上です。

　一方、(2)に関してですが、入力するのがIPアドレスという事ですと、

>数字と.（ドット）以外入力出来ないように

するだけでは、例えば
.999.256......1234567890.99999999999999.9876543210.
などの様なIPアドレスではない値が入力されてしまう事を防ぐ事が出来ません。
　IPアドレスは「.」(ドット)で区切られた4個の整数で構成されていて、その4個の数字は0～255の範囲の整数であり、負の数や255を超える数字が設定される事はありません。

【参考URL】
　IPアドレス - Wikipedia > 4.1 グローバルIPアドレス
　　https://ja.wikipedia.org/wiki/IP%E3%82%A2%E3%83%89%E3%83%AC%E3%82%B9#.E3.82.B0.E3.83.AD.E3.83.BC.E3.83.90.E3.83.ABIP.E3.82.A2.E3.83.89.E3.83.AC.E3.82.B9

　＠IT > 自分戦略研究所 > スキル創造研究室 > CCENT／CCNA 試験対策 2014年版（5）：究極の入門 プライベートIPアドレスとパブリックIPアドレス (1/2)
　　http://www.atmarkit.co.jp/ait/articles/1412/25/news029.html

　ですから、もしIPアドレス以外は入力出来ない様にするのでしたら、「3個の『.』(ドット)で区切られた4個の『0～255』の整数の組み合わせを表す文字列」以外は入力出来ない様にする入力規則が必要になります。
　入力規則に設定可能な数式の文字数には制限があり、その制限文字数はセルの制限文字数よりも少ない様で、セルを使って最初に作った判定用の数式が、入力規則の「数式」欄に入力する事が出来ず、数式をダイエットするのに時間が掛かりましたが、例えばB2セルに設定する場合の例は以下の様なものになります。

B2セルを選択
　　↓
Excelウインドウの上の方にある[データ]タブをクリック
　　↓
現れた「データツール」グループ内の[データの入力規則]ボタンをクリック
　　↓
現れた選択肢の中にある[データの入力規則]をクリック
　　↓
現れた「データの入力規則」ダイアログボックスの中にある[設定]タブをクリック
　　↓
現れた「入力値の種類」欄をクリック
　　↓
現れた選択肢の中にある[ユーザー設定]をクリック
　　↓
現れた「数式」欄に

=COUNTIFS($B2,"*?.*?.*?.*?",$B2,"<>*-*",$B2,"<>*.*.*.*.*",$B2,"<>*????.*.*.*",$B2,"<>*.*????.*.*",$B2,"<>*.*.*????.*",$B2,"<>*.*.*.*????")*ISNUMBER(SUBSTITUTE($B2,".",)+0)*(SUMPRODUCT(ISNUMBER(FIND(ROW(INDIRECT("Z256:Z999")),$B2))*1)=0)

という数式を入力
　　↓
「データの入力規則」ダイアログボックスの[入力時メッセージ]タブをクリック
　　↓
現れた「セルの選択時にメッセージを表示する」と記されている箇所にチェックを入れる
　　↓
「メッセージ」欄に

IPアドレスを入力して下さい。

と入力
　　↓
「データの入力規則」ダイアログボックスの[エラーメッセージ]タブをクリック
　　↓
現れた「無効なデータが入力されたらエラーメッセージを表示する」と記されている箇所にチェックを入れる
　　↓
「スタイル」欄をクリック
　　↓
現れた選択肢の中にある[停止]を選択してクリック
　　↓
「タイトル」欄に

無効な値が入力されました

と入力
　　↓
「エラーメッセージ」欄に

IPアドレスを入力して下さい。
IPアドレス以外の値は入力できません。
入力出来るのは

数字.数字.数字.数字

の型式で表される4個の整数の組み合わせからなる値で、各[数字]の部分に入れる事が出来るのは0～255の範囲の整数のみです。
「&」を始めとする「.」以外の記号やアルファベット等の文字の類は入力できません。
　又、全角文字は入力できませんので、入力は半角文字で行って下さい。

と入力
　　↓
「データの入力規則」ダイアログボックスの[OK]ボタンをクリック

　以上です。

補足

kagakusuki さま

大変お世話になっております。
ここまで丁寧に解説付きで返信いただえると逆に恐縮してしまいますが、正直、大変助かります。誠にありがとうございます。
kagakusuki さまがどんなお方か存じ上げませんが、こんなに親切にしてくださる方がいるなんて、世の中捨てたものではありませんね。もう少し頑張って生きてみようかと思わせれた所存です。

まずは、ＩＰアドレスの件ですが、ご教授いただきました通りにやってみたところ、1発で出来ました。わざわざエラーメッセージ等まで考えていただき、ありがとうございます<(_ _)>
しかし、凄い関数ですね。何がなんだかさっぱり分かりません。1~2週間勉強したところで出来るようなものでは到底ありませんので今はもがいても仕方がないようです・・・

050の方がどうやっても上手くいきません・・・
昨日、おとといと会社から帰って来てご教授いただいた通りに何度もやってみたのですが・・・
kagakusuki さまは非常に頭が良いので、何故、私がＢ列にわざわざ050一種類しかないのにドロップダウンリストから選ばせるのか？とかＣ列の頭に５を表示させたいのか？などなど、疑問に思っていらっしゃられると存じます。おそらく、そんなのＢ，Ｃ列と分けないで関数を作れば良いと思われているでしょう。そうなんです。それが出来れば分けることもないのですが・・・さんざん考えた結果、関数が作れない（分らない）ので、関数を使わずに、でも電話番号を間違えずに入力してもらうにはどうしたら良いか考えていたらこうなってしまいました。初めからkagakusukiさまに助けていただけると知っていたら、こんな恥ずかしい選択はしませんでした。
実は、作りたい050の決まった番号帯があります。
関数が出来ないので四苦八苦して変な形になってしまいました。
下記の数字は仮です。
(1)「050-5〇〇〇ー〇〇〇〇」
(2)「050-1000~1002-〇〇〇〇」３つ。
(3)「050-2000~2001-〇〇〇〇」２つ。
の３種類が出来れば良いのです。何度も申し上げますが、関数が作れないので・・・
同じシート中に表を３つ作って分かりやすいように分けた次第です。
Ｂ列に「050-5」としようかと思いましたが、あまりにも体裁が悪いので。
(2)と(3)はＢ列に「050-1000~2」までをドロップダウンリストで選んでもらい、Ｃ列に下4桁を持ってこようと考えていました。(1)でご教授いただいたものを少し考えたら(2)も(3)も出来るんじゃないかと簡単に思っていましたが、そういうわけにもいかないようですね・・・
ご教授いただいた関数はいう間でもなくさっぱり分かりませんが、１つ気になったものがございました。
それは、「５＠」の「＠」です。Ｗｅｂで調べたら文字列を意味するそうですね。初めて知りました。そうすると「ー」ハイフォンはやはり途中で入れられないのでしょうか？
050の後ですが、Ｃ列の先頭に5を入力されないで、自動で持ってきて、次に0「ゼロ」が来た場合の「○○○ー○○○○」は、関数でも無理なのでしょうか？？そうなると(2)も(3)も出来なくなってしまいお手上げです。
出来れば、上記のように分かりやすく3つに分けたいのですが、ご教授いただけないでしょうか？

すみません。もう、この際なのでご存じでしたら、メールアドレスの入力規則も別ファイルで考えないといけないので、ついでにご教授いただけましたら幸いです。
あまりにもあつかましいかと存じますが、お助け願います<(_ _)>
何卒よろしくお願い申し上げます。

回答No.5

kagakusuki

>例えば「012012」と6桁の数字を入力すると「5001-2012」と0「ゼロ」が重なりエラーが掛からず、すり抜けてしまいます。

　失礼しました。
　その問題に関しては気付いてはいたのですが、その問題を解決しようとすると、

>Ｃ列に７桁の数字だけを入力してもらい、「５〇〇〇ー〇〇〇〇」としたいのです。

が実現出来なくなると、"最初の内は"思っていたため敢えて目を瞑っておりました。
　しかしながら、先程解決方法が見つかりましたので再度回答する事に致します。

C2セルを選択
　　↓
Excelウインドウの上の方にある[データ]タブをクリック
　　↓
現れた「データツール」グループ内の[データの入力規則]ボタンをクリック
　　↓
現れた選択肢の中にある[データの入力規則]をクリック
　　↓
現れた「データの入力規則」ダイアログボックスの中にある[設定]タブをクリック
　　↓
現れた「入力値の種類」欄をクリック
　　↓
現れた選択肢の中にある[ユーザー設定]をクリック
　　↓
現れた「数式」欄に

=AND(ISNUMBER(C2+0),LEN(C2)=7,C2=ASC(C2),COUNTIFS(C2,"<>*-*",C2,"<>*.*",C2,"<>*/*",C2,"<>*月*",C2,"<>*日*"))

という数式を入力
　　↓
「データの入力規則」ダイアログボックスの[入力時メッセージ]タブをクリック
　　↓
現れた「セルの選択時にメッセージを表示する」と記されている箇所にチェックを入れる
　　↓
「メッセージ」欄に

電話番号の末尾の7桁の数字のみを入力して下さい。
「-」(ハイフォン)等の数字以外の文字や記号は入力しないで下さい。

と入力
　　↓
「データの入力規則」ダイアログボックスの[エラーメッセージ]タブをクリック
　　↓
現れた「無効なデータが入力されたらエラーメッセージを表示する」と記されている箇所にチェックを入れる
　　↓
「スタイル」欄をクリック
　　↓
現れた選択肢の中にある[停止]を選択してクリック
　　↓
「タイトル」欄に

無効な値が入力されました

と入力
　　↓
「エラーメッセージ」欄に

電話番号の末尾の7桁の数字のみを入力して下さい。
入力出来るのは
0 1 2 3 4 5 6 7 8 9
の10個の半角数字のみからなる7桁以下の数値のみです。
「-」(ハイフォン)等の数字以外の文字や記号は入力できません。

と入力
　　↓
「データの入力規則」ダイアログボックスの[OK]ボタンをクリック
　　↓
C2セルを右クリックして、現れた選択肢の中にある[セルの書式設定]をクリック
或いは
[Ctrl]キーを押しながら[1 ! ぬ]キーを押す
　　↓
現れた「分類」欄の中にある[ユーザー定義]をクリック
　　↓
現れた「種類」欄に

"5"@

と入力
　　↓
「セルの書式設定」ダイアログボックスの[OK]ボタンをクリック
　　↓
C2セルを右クリックして、現れた選択肢の中にある[コピー]をクリック
或いは
C2セルを選択してから[Ctrl]キーを押しながら[C そ]キーを押す
　　↓
C列のセルの中で、書式と入力規則を設定したいセル範囲を選択
　　↓
選択範囲を示す黒い太枠の内側を右クリックして、現れた選択肢の中にある[形式を選択して貼り付け]をクリック
　　↓
現れた「形式を選択して貼り付け」ダイアログボックスの中にある「入力規則」と記されている箇所をクリックしてチェックを入れる
　　↓
「形式を選択して貼り付け」ダイアログボックスの[OK]ボタンをクリック
　　↓
選択範囲を示す黒い太枠の内側を右クリックして、現れた選択肢の中にある[形式を選択して貼り付け]をクリック
　　↓
現れた「形式を選択して貼り付け」ダイアログボックスの中にある「値と数値の書式」と記されている箇所をクリックしてチェックを入れる
　　↓
「形式を選択して貼り付け」ダイアログボックスの[OK]ボタンをクリック

　以上です。

回答No.4

kagakusuki

>ＩＰアドレスの先頭に０「ゼロ」がある場合もあるのでしょうか？

　その様なIPアドレスも存在しない訳ではない様ですが、特殊な用途向け専用のアドレスとして使用するためのものとして使用が制限されており、一般的に使う事が出来るアドレスではない様です。
　ですから、0から始まるIPアドレスが入力される様な事は考えなくても良いと思われます。
　尚、回答No.3で回答致しました(2)用の入力規則では、0から始まるIPアドレスが入力出来ない様にしている訳では御座いません。

【参考URL】
　グローバルIPアドレスについて教えて下さい。０．で始まるグローバルI... - Yahoo!知恵袋
　　http://detail.chiebukuro.yahoo.co.jp/qa/question_detail/q1423106354

回答No.2

bunjii

>セルの書式設定で050の後にハイフンが表示するようにしています。
B列の書式は「000-」として数値の50を入力すると「050-」と表示されますが、あなたはそのようにされたのでしょうか？

>Ｃ列に７桁の数字だけを入力してもらい、「５〇〇〇ー〇〇〇〇」としたいのです。
>Ｃ列をセルの書式設定で「文字列」に設定してないからなのかな？と思いますが、強制に先頭に５を表示させたいのです。
データとして先頭の5と入力した7桁を合わせて8桁の数値にしたいのであればデータの入力規則では対応できないと思います。
データは入力した7桁以下の数値として表示だけを「5012-3456」のようにしたいのであればデータの入力規則と表示形式で可能になります。
C列の書式を文字列にすると入力した7桁の数字（文字列の長さ7文字の数字）は先頭の0を除いた有効な数値を文字列に変換してセルに格納されます。
数値の表示形式を次のようにします。
「5000-0000」
従って、先頭の0を強制的に文字として認識させるには'（シングルクォーティション）を先頭に付加しなければなりません。
しかし、文字列の表示形式では文字列の中間に記号や文字を追加することができませんので「5023-4567」のように表示することができません。

>Ｂ列にＩＰアドレスを入力してもらうため、数字と.（ドット）以外入力出来ないように入力規制をかけたいのです。
電話番号とは別次元（別のSheet）話でしょうか？
IPアドレスは3桁の数値の4組を.（ドット）で区切った形になりますのですべての桁を入力すれば強制的に文字列として認識します。
また、000.001.000.001のような第1オクテットが0のIPアドレスは存在しませんので、先頭の0については心配ないでしょう。
尚、入力規則の文字列長の指定では入力する文字種を制限できません。
従って、別のチェック方法（マクロ等）で行う必要があるでしょう。

回答No.1

kagakusuki

B列のセルの入力規則は、「入力値の種類」欄を[リスト]にした上で、「元の値」欄に

050

と入力すれば良いと思います。
　又、B列のセルの書式設定の表示形式は[ユーザー定義]の

000

に設定されると良いと思います。
　一方、C列のセルに関しては以下の様にされると良いと思います。
　まず、C列のセルを選択して下さい。
　次に、[データ]タブをクリックし、現れた「データツール」グループ内の[データの入力規則]ボタンをクリックし、現れた選択肢の中にある[データの入力規則]をクリックして下さい。
　すると「データの入力規則」ダイアログボックスが現れますので、その中の[設定]タブをクリックして下さい。
　次に、現れた「入力値の種類」欄をクリックし、現れた選択肢の中から[整数]を選択してクリックして下さい。
　次に、現れた「最小値」欄に0を、「最大値」欄に999999を入力して下さい。
　次に、「データの入力規則」ダイアログボックスの[入力時メッセージ]タブをクリックして下さい。
　次に、現れた「セルの選択時にメッセージを表示する」と記されている箇所にチェックを入れて下さい。
　次に、「メッセージ」欄に「ＩＰアドレスの末尾の7桁の数字のみを入力して下さい」と入力して下さい。
　次に、「データの入力規則」ダイアログボックスの[エラーメッセージ]タブをクリックして下さい。
　次に、現れた「無効なデータが入力されたらエラーメッセージを表示する」と記されている箇所にチェックを入れて下さい。
　次に、「スタイル」欄をクリックし、現れた選択肢の中にある[停止]を選択してクリックして下さい。
　次に、「タイトル」欄に「無効な値が入力されました」とと入力して下さい。
　次に、「エラーメッセージ」欄に

ＩＰアドレスの末尾の7桁の数字のみを入力して下さい。
入力出来るのは
0 1 2 3 4 5 6 7 8 9
の10個の半角数字のみからなる7桁以下の数値のみです。
「-」(ハイフォン)等の数字以外の文字や記号は入力できません。

と入力して下さい。
　次に、「データの入力規則」ダイアログボックスの[OK]ボタンをクリックして下さい。
　次に、C列のセルを選択してから、選択範囲を示している黒い太枠の内側にカーソルを合わせて、マウスの右ボタンをクリックして下さい。
　次に、現れた選択肢の中にある[セルの書式設定]をクリックして下さい。
　すると「セルの書式設定」ダイアログボックスが現れますので、その中の[表示形式]タブをクリックして下さい。
　次に、現れた「分類」欄の中にある[ユーザー定義]をクリックして下さい。
　次に、現れた「種類」欄に

"5"000-0000

と入力して下さい。
　次に、「セルの書式設定」ダイアログボックスの[OK]ボタンをクリックして下さい。

　以上です。

お礼

kagakusuki さま

お忙しいところ、誠にありがとうございました。
ご教授いただいた通りやってみました。
050の後のＣ列ですが、確かに「０」は表示されるようになりました。
しかし、例えば「012012」と6桁の数字を入力すると「5001-2012」と
0「ゼロ」が重なりエラーが掛からず、すり抜けてしまいます。

ＩＰアドレスの方は、初心者からなのか良く分かりませんでした。

今回は、まだ思うような結果が出ていませんが、親切にしていただきました
ことに感謝申し上げます。

ありがとうございました。

補足

kagakusuki さま

早速の回答誠にありがとうございます。
前回に続き、詳しいご説明感謝いたします。

質問内容の
(1)０５０電話番号と
(2)のＩＰアドレスは、全くの別もの（別ファイル）の質問でございます。
分かりにくい表現大変失礼いたしました。
「関数は全く分かりません」としましたが、前回のように（今回もですが）、１から全て操作方法まで記載いただけるようでしたら、問題ございません。
お忙しい中、見ず知らずの者にここまで親切にしていただき大変恐縮しております。大変失礼なお願いになりますが、(1)と(2)を全く分けていただくことは可能でしょうか？
何卒よろしくお願い申し上げます。