エクセル関数で重複チェック

質問

エクセル2010です。
ワークシートのB10:B17の範囲に数値（1から200くらいの自然数）が入力されます。
この範囲が空白になることはありません。
この範囲内での重複（同じ数値の入力）をチェックしたいのです。
ただし、1は何個あろうが重複に含めません。
となりの列あたりを作業列にして、COUNTIFでもできますが、1個のセルで完結させたいと思います。

まず、1を超える数値の個数を取得し、そこから1を除く数の種類の数を引いて0になれば重複はないのではないかと思い、下記の数式を書いてみました。これでTRUEなら重複はないと思いますが、もっと簡単な方法があるのではないかと質問いたしました。
よろしく願いします。

=COUNTIF(B10:B17,">1")-(SUMPRODUCT(1/COUNTIF(B10:B17,B10:B17))-(COUNTIF(B10:B17,"=1")>0))=0

回答No.2

real beatin（@realbeatin） · ベストアンサー

色々ありますが、
=SUMPRODUCT((B10:B17>1)*(COUNTIF(B10:B17,B10:B17)>1))=0
こんな感じが標準的と思いますし、再計算処理も割と軽めです。
値が1である場合は一切無視する方向で、
「1より大きい」且つ「重複が有る」場合の件数を加算して、
0であればTRUEを返します。
尚、参照先に空白セルが含まれていても、結果に影響しませんので、
"B10:B17"はもっとルーズな指定が可能です。
ただ、行数が膨大な場合はまた違う方法を検討することもあるのかも知れません。

以上です。

お礼

ありがとうございます。
これでばっちりOKです。
式もわかりやすく助かりました。

回答No.4

real beatin（@realbeatin）

#2です。
回答する前から気になってはいたのですが、
もし、B10:B17がすべて1だった場合は、
"1は何個あろうが重複に含めません"ということですから
TRUEを返すってことでいいですよね？
それとも他の値（エラー値とか）の方が良かったりとか？
そもそも「すべて1」なんてことはあり得ないとか？
一応確かめておけばよかったかな、と。

お礼

わざわざご確認ありがとうございます。
全部1もありえます。
その場合はTRUEで大丈夫です。

補足

くわしく書きますと、デフォルトではB10:B17がすべて1なのです。
そして各セルは、ワークシート上に配置したフォームの8つのDropdownのLink先になっています。
各Dropdownは同一のリストを参照しています。リストから8つまで選択可能にしているということです。
ですから、異なったDropdownから同じ項目が選ばれてないかどうかのチェックのためにこの関数を使わせていただこうと思っています。

回答No.3

Chiquilin

> これでTRUEなら重複はないと思いますが
その式だと「重複する/しない数」以外が全部「1」だった時 上手く計算しない
と思いますけど。

=MAX(FREQUENCY(IF(B10:B17<>1,B10:B17),B10:B17))=1
Ctrl + Shift + Enterで確定

お礼

CSE関数はよくわからないのですが、ご教示の式で正しく表示されました。
ありがとうございます。

補足

> 「重複する/しない数」以外が全部「1」だった時 上手く計算しない
と思いますけど。

確かにときどき正しくない答えを返すようです。
エクセル特有の小数点以下の誤差のせいではないかと想像し以下のようにROUNDしたところ正しく答えを返してくれました。そういことですよね？
=COUNTIF(B10:B17,">1")-(ROUND(SUMPRODUCT(1/COUNTIF(B10:B17,B10:B17)),0)-(COUNTIF(B10:B17,"=1")>0))=0

回答No.1

mshr1962

数値の数(8個)=数値の種類数+1の個数-1
　↓
数値の種類数+1の個数=9
　↓
=(SUMPRODUCT(1/COUNTIF(B10:B17,B10:B17))+COUNTIF(B10:B17,1))<>9

お礼

さっそくありがとうございます。
重複しない場合に TRUEを返すためには、<>9じゃなくて =9 ですよね?

で、試してみたのですが、B10:B17の範囲に数値の 1　がある場合にはこれでうまくいきました。
しかし、たとえば、数値が 2、3、4、5、6、7、8、9 と入力された場合などは正しくありませんでした。