エクセル関数で重複チェック（条件付）

質問

表A1:B100で、
A列には1～100の数値が必ず入ります。（空白なし）
B列には数値が入っているところと何も入力されていないところがあります。

B列に0以上の数値があり、A列が1でないものだけを対象として、A列の数値が重複していないかどうかをチェックするワークシート関数の組み合わせ方法をご教示ください。（VBAではありません）

B列に0以上の数値があり、A列が1でないものだけのA列の数値を抜き出す作業列と、抜き出された数値をCOUNTIFで重複を調べる作業列の計２つの作業列を加えればわたしにもできたのですが、もっといい方法があるのではないかと思い質問いたしました。
よろしくお願いします。

回答No.8

KenKen_SP · ベストアンサー

Excel 標準の関数を幾つも組み合わせるぐらいなら、VBA でユーザー定義関数
を作ってしまうタチなので、関数の組み合わせを考えるのはどうも苦手です。
VBA による解決をオススメしたいところですが、関数のみとなると、いろいろ
考えてみましたが、どうやら私には手に負えないようです。（；-д- ）

日が経ってしまいましたので、他に回答がつかないようですし、改めて Excel
専門の他掲示板で質問してみてはどうでしょうか？

マルチポストにならぬよう気をつけて下さいね。Moug 辺りが良いと思います。

お力になれず、すみません。

お礼

「すみません。」だなんてとんでもない！
大変おせわになり、ありがとうございました。

回答No.7

KenKen_SP

> わたしが書いたSUMPRODUCTも、B列が空白のA列に重複があった場合でも
> COUNTIF($A$1:$A$100,A1)>1で拾ってしまいました。

うーーん、、気のせいでは？ Excel2002 でテストしてみたら大丈夫でしたよ。

> =IF(SUMPRODUCT((A1:A100>1)*(B1:B100>=0)*(B1:B100<>"")*(COUNTIF(A1:A100,A1:A100)>1)*1)>1,"重複","")

結果についてイメージしているものに食い違いがあるかもしれないので、何件
かサンプルデータ（うまくいかないデータ含む）と、どのような結果になれば
良いのかを示して下さい。

お礼

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

補足

A列　B列
2　　10
3　　10
4　　20
1
1
2
3
1
1
1
上記のようなAB列10行だけのサンプルです。

=IF(SUMPRODUCT((A1:A10>1)*(B1:B10>=0)*(B1:B10<>"")*(COUNTIF(A1:A10,A1:A10)>1)*1)>0,"重複","")

で重複とでてしまいます。
A列ではたしかに2と3が重複していますが、6行目と7行目の2.3はB列が空白なのでカウントしたくないのです。

回答No.6

noname#37676

度々ですが、＃１のroadhです。
＃１のお礼で
>作業列１つでチェックできました。
>出来れば作業列をなくしたいのですが・・・・。
とのことでした。
その他の方の回答でも、「作業列を作成する」ことが前提になっていると思います。
判定をするには判定列（作業列）が必要になると思うのですが？？？
そこで、数式はどのセル（行/列）に置きたいのか？どのような形の結果が欲しいのか？確認したいと思いまして。。。

お礼

ありがとうございます。

> 数式はどのセル（行/列）に置きたいのか？どのような形の結果が欲しいのか？確認したいと思いまして。。。

判定の数式はどこでもいいですが一つのセルだけで行ないたいのです。
サンプルをNO7の補足に記入しました。

回答No.5

noname#37676

こんにちは。＃１です。
＃３サンの回答で「目的を達しました」とのことでしたが、問題の解決はできましたか？
ゴメンナサイ。私には「>出来れば作業列をなくしたいのですが・・・・。」という完成形が見えませんでした。
ご質問の「>ワークシート関数の組み合わせ」はどこに入れようと考えていたのでしょうか？

お礼

ありがとうございます。
一旦は、

=IF(SUMPRODUCT((A1:A100>1)*(B1:B100>=0)*(B1:B100<>"")*(COUNTIF(A1:A100,A1:A100)>1)*1)>1,"重複","")

で目的を達したと思ったのですが、勘違いでした。

出来れば、この式のように一個のセルで判定したかったのです。
現在は、作業列２つで判定しています。

回答No.4

mshr1962

チェックだけでいいならA:B列を選択して「書式」「条件付き書式」で
「数式が」「=AND($B1>0,COUNTIF($A:$A,$A1)>1)」で書式を押し、パターンを変更する。
※上記はA1又はB1の条件になります。
これで条件に該当する行の塗りつぶしが変更されます。

お礼

計算上で使用しますので塗りつぶしではダメなんです。

なお、ご教示の式もB列が空白のA列に重複があっても、B列に数値のあるA列でそれを拾ってしまいます。（1ではなくなってしまいます）

やはり、作業列２つ必要なのでしょうか。

回答No.3

KenKen_SP

訂正します。

> B列に0以上...

=(A1<>1)*(B1>=0)*IF(COUNTIF($A$1:$A$100,A1)>1,1,0)

お礼

ありがとうございます。

=IF(SUMPRODUCT((A1:A100>1)*(B1:B100>=0)*(B1:B100<>"")*(COUNTIF(A1:A100,A1:A100)>1)*1)>1,"重複","")

で目的を達しました。

補足

解決したつもりが・・・。

だめでした。
ご教示の方法も、わたしが書いたSUMPRODUCTも、B列が空白のA列に重複があった場合でもCOUNTIF($A$1:$A$100,A1)>1で拾ってしまいました。

回答No.2

KenKen_SP

こんにちは。

これで結果が 1 となったのが該当かな...？

=(A1<>1)*(B1>0)*IF(COUNTIF($A$1:$A$100,A1)>1,1,0)

お礼

ありがとうございました。
でも、
=(A1<>1)*(B1>0)で2行目以下を判定できるのですか？

補足

あ、すみません。勘違いしました。
これは作業列方式ですね？

回答No.1

noname#37676

こんにちは。
こんなカンジですか？
ＩＦ関数内にＡＮＤを組み合わせました。
=IF((AND(A1>1,B1>0,COUNTIF(A$1:A$100,A1)=1)),A1,"")
これをフィルコピーしてください。

お礼

さっそくありがとうございました。
=IF((AND(A1>1,B1>0,COUNTIF(A$1:A$100,A1)>1)),"重複","")
とすることで、作業列１つでチェックできました。

出来れば作業列をなくしたいのですが・・・・。