Ｅｘｃｅｌ２０００使用・関数式を教えてください

質問

A列～AV列まで6桁の数字が入力されています。
行によって、入っている件数(F列までデータが入っているとか、
AC列まで入っているとか)はマチマチです。
入っている数字もマチマチです。
数字の中には、000001、001000など、0から始まる数字もあり、
セルの書式設定はユーザー定義で種類を000000にしています。

別sheetに6桁の数字の一覧があるのですが、
(1)一覧にない数字が入力されているとエラーが表示されるようにしたい
また、
(2)6桁の数字は前半3桁と後半3桁が隣の列のセルと一致しないといけないのですが、
不一致の場合は、エラーが表示されるようにしたい
例) A２ 00000１ B２ 001000　C２ 000120 D２ 120346　E２ 346508　Ｆ２ 508346　G２ 346508・・・
　　 A３ 123456 B３ 456280　C３280002　D３ 002428　E３ 428508　Ｆ３ 508346　G３ 346213・・・
E２ 346508　Ｆ２ 508346　G２ 346508　のように数字が繰り返すこともあります。

Light関数を使うと(2)は解決できたのですが(1)が解決しません
=IF(RIGHT(TEXT(入力Sheet1!A2,"000000"),3)=LEFT(TEXT(入力Sheet1!B2,"000000"),3),"","ERR")

MATCH関数を使うと
=MATCH(入力Sheet1!A2,一覧sheet!$A$2:$A$500,0)
(1)は解決したのですが、(2)が解決しません。

(1)(2)とも解決する式、或いは方法を教えて頂けたら助かります。
上記の関数式は、以前、この場で教えていただいたものです。
入力用のシートと別に、検証用のシートを作成しています。

又、もし、もし可能でしたら、別シートにある数字が4桁と5桁と6桁が混在している場合でも流用できるものであるとなお有り難いです。
その場合は前後の3桁が一致するときと、前後の2桁が一致するときがあるという
イレギュラーなケースになります。入力した数字は以下のようになります
12345　4512　12650　65034　3458　58260　260013　013134　・・・

よろしくお願い致しますm(__)m

回答No.3

mt2008 · ベストアンサー

条件付き書式でやっては？

対象シートがSheet1,Sheet2のA列に数字の一覧があるとします。
(1).Sheet2のA列に名前定義で名前を付けます（今回の例では：ChkData）
(2).Sheet1に戻って、A2セルを選択して書式→条件付き書式→数式が…で、以下の式を入れます。
　=(COUNTIF(ChkData,A2)=0)
　書式は適当に（例：赤く塗りつぶす）
(3).B2セルにも同様に、以下の様に設定します。
　　条件1
　　　式：　=(B2="")
　　　書式：白く塗りつぶす

　　条件2
　　　式：　=(COUNTIF(ChkData,B2)=0)
　　　書式：赤く塗りつぶす

　　条件3
　　　式：　=(MOD(A2,1000)<>INT(B2/1000))
　　　書式：ピンクに塗りつぶす

(4).B2セルをコピーしてAV2まで書式をコピペ
(5).A2:AV2をコピーして下方向に必要なだけ書式をコピペ

これで、一覧にない数字の場合、そのセルが赤く。左隣のセルの後半3ケタと、自セル前半3ケタが不一致な場合セルがピンクになります。

添付の画像はExcel2010でやった場合です。

お礼

ご回答有難うございます。

教えて頂いた方法で、6桁の場合の(1)(2)とも解決することが出来ました!
まずは、御礼申し上げますm(__)m

補足

追加で質問なのですが、

もし、８桁になった場合、(前後4桁でチェックする場合)
教えて頂いた方法の、どの部分を変更すれば対応できるのでしょうか?

ご回答頂けましたら有り難いです。宜しくお願い致します。

回答No.6

mt2008

ANo.3です。

> もし、８桁になった場合、(前後4桁でチェックする場合)
> 教えて頂いた方法の、どの部分を変更すれば対応できるのでしょうか?

条件3の式を以下の様に変えて下さい。
=(MOD(A2,1000)<>INT(B2/1000))
↓
=(MOD(A2,10000)<>INT(B2/10000))

MOD(A2,10000)が、A2の値を10000で割った余り＝下４ケタの値
INT(B2/10000)が、B2の値を10000で割った整数部＝上４ケタの値
です。

お礼

何度も、有難うございます。

とても簡単で、私のような無知な者でも理解できました。

本当に有難うございました。

回答No.5

KURUMITO

シート１に入力シート１が有るとして、シート２のＡ列には一覧シートが有るとします。
条件に合わない数値に色を付けるとしてシート１のＡ２セルから例えばＡＶ１００セルの範囲を選択します。その上で条件付き書式を設定します。
条件付き書式の設定では数式が・・で選んで数式の窓には次の式を入力します。

=AND(COLUMN(A1)>1,B2<>"",OFFSET(A2,0,-1)<>"",OR(RIGHT(TEXT(A2,"000000"),3)<>LEFT(TEXT(B2,"000000"),3),RIGHT(TEXT(OFFSET(A2,0,-1),"000000"),3)<>LEFT(TEXT(A2,"000000"),3)))

書式をクリックして塗りつぶしのタブで例えば赤色を指定してＯＫします。
これでお示しのルールに従わないデータには赤色が付きます。

また、一覧の数値に無いデータにもセルに色を付けるために例えば上と同じように範囲を選択した上で、条件付き書式では数式の窓に次の式を入力します。

=AND(A2<>"",COUNTIF(Sheet2!$A:$A,A2)=0)

書式をクリックして塗りつぶしのタブから黄色を設定してＯＫします。

これでご質問の（１）、（２）のケースではセルに色が付くことになります。

お礼

ご回答有難うございます

条件付書式設定でうまく出来ることが分かりました。
有難うございました。
お手数をお掛け致しました。

回答No.4

chonami

MATCHの括弧を閉じてないからかな、と思うのですがその前に2000だとIFERROR使えませんがいいんでしょうか？

お礼

ご回答頂きまして有難うございます。

Excel2000では、IFERRORは使えないんですね。
調べてみたら、どうも駄目みたいです。

折角教えて頂いたのに残念です。

回答No.2

kuma310min

IF文は多重構造に出来るので、

=IF(RIGHT(・・・）<>LEFT(・・・),"ERR",IFERROR(MATCH(・・・),"ERR"))
※「・・・」の部分は、質問文と同じ

ただし、”ERR"または、MATCH関数の結果（数字）が表示されると思うので、
見にくくなるかもしれません。

補足

ご回答有難うございます。

式の作成が間違えているのだと思うのですが、
引数が多すぎるとかえります。

=IF(RIGHT(TEXT(入力sheet1!A2,"000000"),3)<>LEFT(TEXT(入力sheet1!B2,"000000"),3),"","ERR",IFERROR(MATCH(入力sheet1!A2,一覧sheet!$A$1:$A$500,"ERR"))
で宜しいでしょうか。

お手数ですが、よろしくお願い致します。

回答No.1

aokii

まずは、全てのセルのデータを数値にするために、=VALUE(A1)にすることをお勧めします。

補足

ご回答ありがとうございます。

間違えていたらすみません。

=VALUE(A1)にしますと、0から始まる数字は0が消えてしまいました。
013456と13456は違うデータ(数字の組合せ?と表現したら良いでしょうか・・)になるため、
頭の0も表示したいです。