Excelの入力規則にいれる関数について

1つのセルに、数字8桁 または アルファベット3文字+数字4桁 の入力のみを許容するように設定をしたいのですが、どのような関数を入れたらいいでしょうか。


数字8桁の入力規則は、C6のセルに以下のように設定してあるのですが
=AND(ISNUMBER(C6*1),LENB(C6)=8,C6*1>=0,B6<>"")
上記に追加して「アルファベット3文字+数字4桁」の入力規則を入れることは可能でしょうか？

どなたか分かる方いらっしゃいましたら、教えてください。

No.7 回答者： Belltreeriver 回答日時： 2022/12/28 09:47

＜ご参考＞です。

goomaniaさんのご指摘を踏まえて、条件を追加してみました。
分析し、知識向上に繋げてください。
=IFERROR(AND(MOD(--C6,1)=0,LEN(ABS(SUBSTITUTE(C6,"0","1")))=8),AND(SUMPRODUCT(1*NOT(EXACT(UPPER(MIDB(C6,ROW($1:$3),1)),LOWER(MIDB(C6,ROW($1:$3),1)))))=3,MOD(--MID(C6,4,100),1)=0,LEN(ABS(SUBSTITUTE(MID(C6,4,100),"0","1")))=4))

No.6 回答者： goomania 回答日時： 2022/12/25 19:48

大変失礼とは存じますが、Belltreeriverさんの回答にある数式は、数字部分について「整数」と「バイト数」をチェックしているだけのように思います。

このため、「整数」で「8バイト文字」である「-1234567」と入力するとエラーにならず入力できてしまいます。

また、アルファベットとハイフンと数字を組み合わせた「ABC-789」という入力も後ろの「-789」が整数で「全体で７バイト文字」のためエラーにならず入力できてしまいます。

さらに、上記の例で符号を変えて「ABC+789」としても入力できてしまいます。

このような事象を改善する数式を考えてみました。

アルファベットの大文字と小文字の違いまで含めて、完全に一致しているかどうかを判定したいときは、Belltreeriverさんの回答にあるようにEXACT関数を使用しなくてはいけません。

何故なら、等しいかどうかを比較する「=」演算子はアルファベットの大文字小文字を区別しないからです。

しかし、これを逆手にとって、CHAR関数を使ってアルファベットと入力値を比較すれば、大文字も小文字も許容した数式になります。

つまり、全て半角英数字という前提ですが、「数字8桁 または アルファベット3文字+数字4桁 の入力のみを許容する」入力規則は

=((SUMPRODUCT((MID(C6,COLUMN($A:$C),1)=CHAR(64+ROW($1:$26)))*1)=3)*(SUMPRODUCT((MID(C6,3+COLUMN($A:$D),1)=CHAR(47+ROW($1:$10)))*1)=4)*(LEN(C6)=7))+(SUMPRODUCT((MID(C6,COLUMN($A:$H),1)=CHAR(47+ROW($1:$10)))*1)=8)*(LEN(C6)=8)

という数式でよいのではないでしょうか?

No.5 回答者： Belltreeriver 回答日時： 2022/12/24 08:49

再考しました。

次の数式で試してみてください。

=IFERROR(AND(MOD(--C6,1)=0,LENB(C6)=8),AND(SUMPRODUCT(1*NOT(EXACT(UPPER(MIDB(C6,ROW($1:$3),1)),LOWER(MIDB(C6,ROW($1:$3),1)))))=3,MOD(--RIGHTB(C6,4),1)=0,LENB(C6)=7))

No.4 回答者： Belltreeriver 回答日時： 2022/12/24 00:01

NO.3です。

一部訂正です。
最初の回答では大文字の英字3文字パターンに対応できていませんでした。

今回の回答も小文字の英字3文字パターンに対応できませんのでご参考です。
=AND(SUMPRODUCT(--ISNUMBER(--MID(C6,ROW($1:$8),1)))=8,LENB(C6)=8)+AND(NOT(EXACT(LEFTB(C6,3),LOWER(LEFTB(C6,3)))),SUMPRODUCT(1*ISERROR(FIND(ROW($1:$10)-1,LEFTB(C6,3))))=10,SUMPRODUCT(1*ISNUMBER(--MID(C6,ROW($4:$7),1)))=4,LENB(C6)=7)
長い数式だと入力規則の数式文字数の制限にかかり、諦めました。

No.3 回答者： Belltreeriver 回答日時： 2022/12/23 22:52

半角文字、数字、小数点にも対応します。

=AND(SUMPRODUCT(--ISNUMBER(--MID(C6,ROW($1:$8),1)))=8,LENB(C6)=8)+AND(NOT(EXACT(LEFTB(C6,3),UPPER(LEFTB(C6,3)))),SUMPRODUCT(1*ISERROR(FIND(ROW($1:$10)-1,LEFTB(C6,3))))=10,SUMPRODUCT(1*ISNUMBER(--MID(C6,ROW($4:$7),1)))=4,LENB(C6)=7)

No.2 回答者： fujillin 回答日時： 2022/12/22 15:12

こんにちは

文字列をまとめてISNUMBERで判定すると小数点なども通ってしまいますが、OKなのでしょうか？
ご提示の判定式であれば、文字列で
　"+1.2E-34"
のようなものも許容されます。

「全部で7文字、かつ、アルファベット3文字+数字4文字」
として、1文字ずつチェックする方式で考えてみました。
多少簡略な判定ですが、
=(LEN(C6)=7)*(SUMPRODUCT((MID(C6,ROW(A$1:A$3),1)>="A")*(MID(C6,ROW(A$1:A$3),1)<="Z"))=3)*(SUMPRODUCT(ISNUMBER(MID(C6,ROW(A$4:A$7),1)*1)*1)=4)
ではいかがでしょうか？
（TRUEは1、FALSEは0として返されます）

※ 上式では全角半角の区別ができないようです。
　正確に判定したい場合は、CODE関数でコード化して判定すれば可能です。
　コードで判定する場合は、大文字、小文字も区別されますので、判定式は
　相当に長いものになってしまいます。
　（ですので、とりあえず簡略式を提示してあります）

※ 正規表現を利用できれば簡単なのですが、入力規則ではユーザ定義関数を認識してくれないようなので諦めました。
条件付き書式で判定外の入力に対して背景色を変えるなどでも良ければ、ユーザ定義関数を利用することが可能です。

No.1 回答者： mike_g 回答日時： 2022/12/22 11:44

確認させてください。

数字もアルファベットも半角文字ですか？
数字の始まりに 0 もあり得ますか？