エクセルの入力規則について

質問

初投稿です。
現在仕事で商品を管理するためにエクセルで管理表を作成しているのですが
データ入力が不特定多数の人で行われるためセルに入力規則をしたいと考えております。
規制かけたい項目は
○入力文字数７桁、半角英数字固定
○入力可能文字列　0000001から9999999の間で重複不可
○全角、半角スペースの入力不可

セル範囲は
B8からI41　です。
自分なりに調べてはみたのですが、この項目すべてをセルに適用させることができずに困っています。
どなたか詳しい人教えてくださると助かります。
よろしくお願いします。

使用環境
OS : win7
ソフト : Microsoft office　excel 2013

回答No.2

keithin · ベストアンサー

はて？

○７桁の半角の数字。ただし０００００００は除く
○１セルの中の重複数字は別にＯＫで、Ｂ８からＩ４１の範囲で重複したらダメ
○スペースに限らず英字記号も不可、全角文字も不可
と言いたかったのでしょうか。

手順：
Ｂ８セルを選択
セルの書式設定の表示形式を文字列に設定
入力規則を開始
種類をユーザー設定
数式欄に
=ISNUMBER(B8*1)*(B8-0)*(LENB(B8)=7)*(COUNTIF($B$8:$I$41,B8)=1)
と記入してＯＫ

Ｂ８をコピー、Ｂ８：Ｉ４１に貼り付けて完成。

＃操作に慣れたらＢ８：Ｉ４１を最初から選択して操作しても勿論ＯＫ。

＃参考
「７文字の数字」の制約の必要性についてよく考えて、たとえばセルの書式設定の表示形式のユーザー定義で
0000000
を設定しておき、最大７桁までの「数値」（たとえば１や１０）を記入するような仕込みをする事もできます
実際に手を動かして操作してみて、必要性についてご自分のホントにやらなきゃならない事に照らして判断してください。

補足

迅速な回答ありがとうございます。
とても解りやすかったです。
教えて頂いた式を設定したら解決しました。ありがとうございました。

ついでと言っては何ですが、この入力規則を生かしたまま左上に自動で文字が列移動されるような設定も可能なのでしょうか。

例えば、下記のような図1データがあるとします。そこでA1のデータを消すと図2のような状態に自動で詰めて常に空白セルを最後尾に表示させることは出来るのでしょうか。

　A　 B　 C
1 01 02 03
2 04 05 06
3 07 08 09

　　↑図1

　 A 　 B　 C
1 02 03 04
2 05 06 07
3 08 09

　　↑図2

設定セル範囲はB8からE41とF8からI41の二か所を考えています。
説明下手で伝わりにくいとは思いますが、回答いただけると幸いです。
よろしくお願いします。

回答No.1

tsubuyuki

一気に行くとわかりづらいので、一つ一つやっつけていきましょう。

まず、簡単なところから

> 重複不可
　　=COUNTIF($B$2:$I$41,B2)<=1
これで、範囲内にその値が「１以下」つまり、１か０しか認めない＝重複しないとできます。

> 全角、半角スペースの入力不可
これもちょっと捻りますが難しくないです。
　　=ISERROR(FIND(" ",ASC(B2)))
Ｂ２をＡＳＣ関数で半角にし、ＦＩＮＤ関数でスペースを探してやり、
ＩＳＥＲＲＯＲ関数でエラー値が返ってくるかどうか調べています。
ちなみに、ＦＩＮＤ関数は第２引数（検索値）が含まれていないをとエラーで返ります。
なので、ＩＳＥＲＲＯＲ関数に渡して、ＦＡＬＳＥが返ってきたら
「スペース（全/半問わず）」を含んでいる、と言うことです。

> 入力文字数７桁
これも難しくないですね。
　　=LEN(B2)=7
つまり「文字列長＝７文字固定」の意味です。

と、ここまでは良いのですが。
問題は、質問文中のちょっとした矛盾点。
> 半角英数字固定
> 0000001から9999999の間で
これです。

前者を活かすなら、アルファベットＯＫ。
後者を活かすなら、アルファベットＮＧで数字のみ。

後者なら、
　　=AND(B2>=1,B2<=9999999)
でいけるんですが、前者ならもう少し力任せにやってやる必要がありますね。

ちなみに全角が混ざっていないかどうか？は
　　=LEN(B2)=LENB(B2)
で調べられます。

ま、踏まえて、あとは全てをＡＮＤ関数で括ってやればＯＫ。
つまり、「アルファベット込」なら
　　=AND(COUNTIF($B$2:$I$41,B2)<=1,ISERROR(FIND(" ",ASC(B2))),LEN(B2)=7,LEN(B2)=LENB(B2))
なのですが、「数字だけ」ならちょっと端折ることも可能です。
　　=AND(COUNTIF($B$2:$I$41,B2)<=1,AND(B2>=1,B2<=9999999))
これだけで、たぶん出来ます。
この場合は、表示形式を「0000000」に設定してやるのをお忘れなく。

お礼

回答ありがとうございます！
とても参考になりました。

本当はお二方ともベストアンサーに選ばせてもらいたいのですが、今回はNo2さんを選ばせて頂きました。
自分ももっとエクセルについて再度勉強してみたいと思います。
本当にありがとうございました。