こんにちはゲストさん。会員登録(無料)して質問・回答してみよう!

解決済みの質問

Excel2000使用・関数式を教えてください

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

別sheetに6桁の数字の一覧があるのですが、
(1)一覧にない数字が入力されているとエラーが表示されるようにしたい
また、
(2)6桁の数字は前半3桁と後半3桁が隣の列のセルと一致しないといけないのですが、
不一致の場合は、エラーが表示されるようにしたい
例) A2 000001 B2 001000 C2 000120 D2 120346 E2 346508 F2 508346 G2 346508・・・
   A3 123456 B3 456280 C3280002 D3 002428 E3 428508 F3 508346 G3 346213・・・
E2 346508 F2 508346 G2 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

投稿日時 - 2013-06-05 12:16:40

QNo.8120442

すぐに回答ほしいです

質問者が選んだベストアンサー

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

対象シートが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でやった場合です。

投稿日時 - 2013-06-05 14:54:14

補足

追加で質問なのですが、

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

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

投稿日時 - 2013-06-05 16:09:12

お礼

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

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

投稿日時 - 2013-06-05 15:46:23

ANo.3

このQ&Aは役に立ちましたか?

0人が「このQ&Aが役に立った」と投票しています

回答(6)

ANo.6

ANo.3です。

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

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

=(MOD(A2,10000)<>INT(B2/10000))

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

投稿日時 - 2013-06-05 17:23:23

お礼

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

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

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

投稿日時 - 2013-06-05 17:26:51

ANo.5

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

=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)))

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

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

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

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

これでご質問の(1)、(2)のケースではセルに色が付くことになります。

投稿日時 - 2013-06-05 16:42:27

お礼

ご回答有難うございます

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

投稿日時 - 2013-06-05 17:24:21

ANo.4

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

投稿日時 - 2013-06-05 14:56:09

お礼

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

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

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

投稿日時 - 2013-06-05 15:52:06

ANo.2

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

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

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

投稿日時 - 2013-06-05 13:40:06

補足

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

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

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

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

投稿日時 - 2013-06-05 14:24:38

ANo.1

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

投稿日時 - 2013-06-05 12:29:03

補足

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

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

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

投稿日時 - 2013-06-05 12:53:46

あなたにオススメの質問