重複エラーを出したい

質問

Excelで重複エラーを出したくて、

Ａ列はリスト、Ｂ列は任意に値を入力出来るようにし、

Ｃ列で「=A3&B3」として、結合しています。

そこで、Ｃ列で同じ値となった場合、重複エラーを出したいのですが、

ＣＯＵNTIFを使用して「=COUNTIF(C:C,C3)=1」としても、重複エラーが出力されません。
「=A3&B3」の値をＣ列の数式内に引き継いでエラー出力させるには、
どのようにすればいいのでしょうか。

よろしくおねがいいたします。

回答No.13

kagakusuki

>ドロップダウンリストの元の値を非常に煩雑な関数を使って、現時点で重複のない文字だけを表示させる

という方法です。
　尚、質問者様が御使いになられているExcelのバージョンが不明ですので、Excel2007以前のバージョンでも使う事が出来る様にするために、入力規則の中で他のシートのセル範囲を指定する際にはINDIRECT関数を使用しております。(Excel2007以降であれば、扱い難いINDIRECT関数を使わずに済ませる方法もあります)

　今仮に、質問者様の添付画像に写っている様な表が゜、Sheet1上に存在するものとします。
　又、Sheet2のA列～I列を作業列として使用するものとします。

　まず、Sheet2のA2以下に、「Sheet1のB列」に何も入力されていない状態の場合において、「Sheet1のA列のセル」に入力する事が出来る選択肢を、全て入力して下さい。

　次に、Sheet2のC2セルに次の関数を入力して下さい。

=IF(INDEX(Sheet1!$B:$B,ROW())="","",Sheet1!$B2&"■"&Sheet1!$A2)

　この時、Sheet1のA列の値とB列の値の間に、何故、"■"を挟めてしまうのかと申しますと、例えばA列が「123」で、B列が「456」となっている行があった場合において、これら2つの値を単純に&を使って結合させただけでは、「123456」となってしまい、A列が「1」でB列が「23456」となる場合や、A列が「12」でB列が「3456」となる場合、A列が「1234」でB列が「56」となる場合、A列が「12345」でB列が「6」となる場合、等と区別する事が出来なくなり、A列に「1」を入力してからB列に「23456」を入力する事が出来なくなる、等の様になる事を避けるためです。
　その際、間に挟める文字を例えば「3」等の様な、A列の値やB列の値の中に含まれる可能性が高い文字を使用しますと、例えば、A列が「12」でB列が「3456」の場合と、A列が「123」でB列が「456」の場合を区別する事が出来なくなりますので、本回答の例では、A列やB列の値の中に含まれている可能性が比較的低いと思われる"■"を使用しております。
　ですから、別に"■"に限る必要は無く、A列やB列の値の中に含まれている可能性が低い文字や記号でさえあれば、別な文字に変更しても構いません。

　次に、Sheet2のD2セルに次の関数を入力して下さい。

=IF(INDEX(Sheet1!$B:$B,ROW())="","",IF(COUNTIF(Sheet1!$B$1:INDEX(Sheet1!$B:$B,ROW()-1),INDEX(Sheet1!$B:$B,ROW())),"",ROW()))

　次に、Sheet2のE2セルに次の関数を入力して下さい。

=IF(ROWS($2:2)>$F$2,"",INDEX(Sheet1!$B:$B,SMALL($D:$D,ROWS($2:2))))

　次に、Sheet2のC2～E2の範囲をコピーして、同じ列範囲の3行目以下に(Sheet1の表の行数を上回るのに十分な行数となるまで)貼り付けて下さい。
　次に、Sheet2のF1セルに次の関数を入力して下さい。

=MAX(IF(COUNT(INDEX($A:$A,ROW($A$1)+1):INDEX($A:$A,ROWS($A:$A))),MATCH(9E+307,$A:$A),0),IF(COUNTIF(INDEX($A:$A,ROW($A$1)+1):INDEX($A:$A,ROWS($A:$A)),"*?"),MATCH(CHAR(1),$A:$A,-1),0))-ROW($A$1)

　次に、Sheet2のF2セルに次の関数を入力して下さい。

=COUNT($D:$D)

　次に、Sheet2のG1セルに次の関数を入力して下さい。

=IF(ROW()>$F$1*($F$2+1),"",IF(COUNTIF($C:$C,INDEX($E:$E,MOD(INT((ROW()-1)/$F$1),$F$2+1)+ROWS($E$1)+1)&"■"&INDEX($A:$A,MOD(ROW()-1,$F$1)+ROW($A$1)+1)),"",ROW()-1))

　次に、Sheet2のH2セルに次の関数を入力して下さい。

=IF(OR(ROW()<ROW(H$2),ROWS($2:2)>COUNT($G:$G)),"",INDEX($E:$E,MOD(INT(SMALL($G:$G,ROWS($2:2))/$F$1),$F$2+1)+ROW($E$1)+1)&"■")

　次に、Sheet2のI2セルに次の関数を入力して下さい。

=IF($H2="","",INDEX($A:$A,MOD(SMALL($G:$G,ROWS($2:2)),$F$1)+ROW($A$1)+1))

　次に、Sheet2のG1セルをコピーして、Sheet2のG2セルに貼り付けて下さい。
　次に、Sheet2のG2～I2の範囲をコピーして、同じ列範囲の3行目以下に、「Sheet1のA列の選択肢の数」と「『Sheet1のB列に入力したデータの内、重複しているものを除いたデータの数』に1を加えた数」を掛け合せた数を上回るのに十分な行数となるまで貼り付けて下さい。

　次に、Sheet1のB2セルに、次の様な数式による[ユーザー定義]の入力規則を設定して下さい。

=OR($A2="",COUNTIF(INDIRECT("Sheet2!C:C"),B2&"■"&$A2)=1)

　次に、Sheet1のA2セルにおいて、入力規則を[リスト]に設定し、「元の値」欄の所に、「選択肢が入力されているセル範囲」や「カンマで区切ったリスト」などではなく、次の数式を入力して下さい。

=OFFSET(INDIRECT("Sheet2!I"&MATCH($B2&"■",INDIRECT("Sheet2!H:H"),0)),,,COUNTIF(INDIRECT("Sheet2!H:H"),$B2&"■"))

　これで、Sheet1のA列の後で、Sheet1のB列に値を入力する際に、A列の値とB列の値の組み合わせが別の行のものと同じになる様な値を入力しようとした場合には、「データの入力規則」の中の「エラーメッセージ」タブで設定するポップアップが表示されますし、A列のドロップダウンリストでは、A列とB列が共に同じ組み合わせとなる様な選択肢を除外したリストが表示されます。
　但し、B列が空欄となっている行に関しては、A列に同じ値を何回でも重複して入力する事が出来ます。

回答No.12

yaritsusozai

調べてみましたが、やはりリストとポップアップは、どちらか片方しか選べないようです。

それでもどうしても、ということであれば、マクロ併用での対応となります。あくまで、出来ないこともない、というぐらいの例ですが…。

手順:
前回ご案内した通り、A列はリスト、B列はユーザー定義によるエラーのポップアップ、C列はA3&B3、というセッティングが完了した状態で、

今使っているシートの「シート見出し」を右クリック→コードの表示→開いた画面に次のコードを記述する。

Private Sub Worksheet_Change(ByVal Target As Range)

Dim TG1 As String, TG2 As String ,TG3 As String

If Target.Column <> 1 Or Target.Row < 3 Or Target.Value ="" Then Exit Sub

TG1 = Target.Value
TG2 = Cells(Target.Row, 2).value
TG3 = TG1 + TG2

If Application.CountIf([C:C], TG3) > 1 Then

Target.Value = ""
MsgBox ("重複！")
End If

End Sub

誤転記がないか見直し確認した後、ファイル→終了してExcelに戻る。

A列B列に色々な値を入れて動作を確認してみる。

注:A列とB列のポップアップのデザインの違いに関しては、演出的な部分ですので、質問者様の方で気に入るように改良してください。下から3行目、MsgBoxの中をいじればデザインは変えられます。

回答No.11

MackyNo1

>お教え頂いた復号参照の式ですと、式を設定するにあたり、A列のリスト形式を解除しなければならないのですが、リスト形式を保ったまま、数式を入れる方法があるのでしょうか。

基本的に入力規則は、日本語入力以外は2つの条件で設定できませんので、リストを採用するか数式を使ったユーザー設定のいずれかを選択することになります。

どうしても、A列のデータをドロップダウンリストから選択したいなら、ドロップダウンリストの元の値を非常に煩雑な関数を使って、現時点で重複のない文字だけを表示させるような必要があります。

＞また、結合した値のC列の結果を、重複エラーとして判断させる方法はありますでしょうか。

手入力するなら通常のCOUNTIF関数でもOKですが、C列のように数式で表示されているセルの場合は、通常の「入力」とは違うので、入力規則の対象になりません。

回答No.10

bunjii

回答No.7の検証で説明に誤りがありました。

データの入力規則で「ユーザー設定」の数式は質問の式をそのまま使いました。
　　　　　　　↓
データの入力規則で「ユーザー設定」の数式は次のようにしました。
=COUNTIF(C:C,A3&B3)=1

補足に対する回答
>(1)A列は「リスト選択」の形式にしたい
入力規則は同時に2つ以上の要件を設定できませんので優先する要件を1つにしてください。

>(4)エラーが出るタイミングは、・・・・・・
セルに値を入力して確定の操作（Enter、カーソル移動等）したときにポップアップでエラー表示されます。
値の入力途中ではエラー条件の判定をしないようです。

回答No.9

MackyNo1

参考までに補足します。

ご希望の操作が、A列とB列の連結文字列の重複なら、提示したような数式を利用する必要がありますが（連結文字が同じ「1」「12」と「11」「2」も入力させない場合）、単純にA列とB列に入力されるデータの組み合わせが同じものがないようにしたいということなら、COUNTIFS関数を使用するほうが簡便です（Excel2007以降のバージョンを使用している場合）。

A及びB列の入力範囲を選択して（A3セルを基準）、以下の数式を入力すればよいことになります。

=COUNTIFS($A$3:$A$100,$A3,$B$3:$B$100,$B2)=1

＃Officeソフトはバージョンによって使用できる機能や操作方法が大きく異なるので、質問の際には必ずバージョンを明記するようにしましょう。

補足

確かにその通りですね、
すいません、使用バージョンは2010です。

ここで今一度整理させて頂くと、
(1)A列は「リスト選択」の形式にしたい
(2)B列は手入力の形式にしたい
(3)重複エラーはエクセルの機能を使ってポップアップ出力させたい
(4)エラーが出るタイミングは、
A列とB列の結合した値が（C列に=A2&B2などとして出しています）、他のA列B列の結合結果と同じであれば、エラー出力させたい。
つまり、

1行　Ａ列　Ｂ列　Ｃ列
2行　　 1　　 2　　12
3行　　 　　 　
とあった場合、

※３行目に同じ値が入力された時、
つまり3行目のB列に「2」が（この場合）最後に手入力され、A列とB列を結合して2行目と
同じ値となった時に、
1行　　Ａ列　　Ｂ列　　Ｃ列
2行　 　1　　　　2　　　12
3行　　 1　　　 2●　　12
ポップアップでエラーを出力する

及び、
※既に3行目B列に「2」が入力されており、
最後に3行目のA列のリスト選択（例ですと「1」）がされ、A列とB列を結合した値が2行目と同じになった時に
1行　　Ａ列　　Ｂ列　　Ｃ列
2行　 　1　　　　2　　　12
3行　　 1●　　 2　　　12
同様にポップアップでエラーを出力する

とさせたいのが元々の希望です。
お教え頂いた復号参照の式ですと、式を設定するにあたり、
A列のリスト形式を解除しなければならないのですが、リスト形式を保ったまま、
数式を入れる方法があるのでしょうか。
また、結合した値のC列の結果を、重複エラーとして判断させる方法はありますでしょうか。

回答No.8

MackyNo1

＞Ｂ列が入力されている状態で、Ａ列をリストから選択し直し、別の値にして重複した場合、エラーを出力させる事は可能でしょうか。

A列についても入力規則を設定したいなら、A3セル以下を選択し、同じ数式で対応することができます。

=COUNT(INDEX(1/(A3&B3=$A$3:$A$100&$B$3:$B$100),))=1

A3、B3セル以下にも（2列まとめて）条件付き書式を設定したいなら以下のような複合参照の数式にしてください。

=COUNT(INDEX(1/($A3&$B3=$A$3:$A$100&$B$3:$B$100),))=1

回答No.7

bunjii

質問と補足のの文言から勝手解釈で検証してみました。

貼付画像はExcel 2013で実行したものです。
データの入力規則で「ユーザー設定」の数式は質問の式をそのまま使いました。
設定範囲はB3:B10とし、「エラーメッセージ」へは"入力データは重複します"としてあります。

補足

検証ありがとうございます。
実現したいのは以下の感じです。

(1)A列は「リスト選択」の形式にしたい
(2)B列は手入力の形式にしたい
(3)重複エラーはエクセルの機能を使ってポップアップ出力させたい
(4)エラーが出るタイミングは、
A列とB列の結合した値が（C列に=A2&B2などとして出しています）、他のA列B列の結合結果と同じであれば、エラー出力させたい。
つまり、

1行　Ａ列　Ｂ列　Ｃ列
2行　　 1　　 2　　12
3行　　 　　 　
とあった場合、

※３行目に同じ値が入力された時、
つまり3行目のB列に「2」が（この場合）最後に手入力され、A列とB列を結合して2行目と
同じ値となった時に、
1行　　Ａ列　　Ｂ列　　Ｃ列
2行　 　1　　　　2　　　12
3行　　 1　　　 2●　　12
ポップアップでエラーを出力する

及び、
※既に3行目B列に「2」が入力されており、
最後に3行目のA列のリスト選択（例ですと「1」）がされ、A列とB列を結合した値が2行目と同じになった時に
1行　　Ａ列　　Ｂ列　　Ｃ列
2行　 　1　　　　2　　　12
3行　　 1●　　 2　　　12
同様にポップアップでエラーを出力する

結合された結果のC列を、重複エラーを出力する時の
要素に使用できないかなと思っております。

回答No.6

MackyNo1

質問内容は、入力規則の数式に使用したいということだったのですね。

その場合、私の回答をそのまま使うならB3セル以下を選択して、以下のような数式を設定してみてください。

=COUNT(INDEX(1/(A3&B3=$A$3:$A$100&$B$3:$B$100),))=1

補足

有難うございます。
成程、やりたい事にかなり近くなってきました。

Ａ列も参照している為、Ａ列が選択されている前提であれば、
Ｂ列を後からDeleteで消して入力し直したとしても重複チェックがされますね。

さらに一歩踏み込んで、例えば、Ｂ列が入力されている状態で、Ａ列をリストから選択し直し、別の値にして重複した場合、エラーを出力させる事は可能でしょうか。
例
1　Ａ列　Ｂ列　Ｃ列
2　　 1　　 2　　12
3　　 2　　 2　　 22
を
Ａ列のリストを変更し、
1　　Ａ列　　Ｂ列　　Ｃ列
2　　 1　　　　2　　　12
3　　●1　　　 2　　　12
リストから「1」を選択した時にエラーを出力させる
これですとやはりＣ列の値を使用しなければならないのでしょうか。

回答No.5

yaritsusozai

こちらで動作確認を行いましたが、概ね最初の式で正しく動きます。
C列にA列とB列を連結する式を入れておけば、=COUNTIF(C:C,C3)=1は、AとBの連結した結果Cについて調べますので、B列にセットしたからといってB列の重複だけを調べるわけではありません。

あと考えられる疑わしい点は、

すでに数値が入っている所に、後から規制を掛けるとうまくいかない恐れがある。

C列に、=A3&B3の式が入っていないか、後から入れた。

入力確定時、エンターでなく他セルをクリックするとうまくいかない
などでしょうか。

新たにまっさらなシートを用意し、次の手順に沿って操作してみてください。

(1)：C3に、=$A3&$B3と入力し、下へコピー
(2)：A3からA1000あたり(任意)までを範囲選択し、入力規制→リスト→1,2,3を設定
(3)：B3からB1000あたりまでを範囲選択し、入力規制→ユーザー設定→数式欄に、=COUNTIF($C:$C,$C3)=1と入力する。式の中身はC列に関する内容で、セットする場所はB列である点に注意。

(4)：A列に入れるデータをリストから選んだ後、B列を手入力し、エンターで確定する。逆にするとうまくいかないので注意。

補足

有難うございます。
確かに上手くいきました。

しかしながら本当に実現したいのは、
Ｂ列だけでなく、Ａ列と組み合わせたエラー出力なのです。

・Ａ列とＢ列を合わせた値がＣ列の他セルの値と重複する場合エラーをポップアップする
・つまりＡ列→Ｂ列またはＢ列→Ａ列の逆の順序で選択入力、入力選択しても機能する

これは実現できないでしょうか。

回答No.4

yaritsusozai

データの入力規則の中にある、ユーザー設定の数式欄ですか？それでしたら、質問者様が最初にご提示なされた式で合っていますが…？

例えば、それをC列に設定しようとすれば、正しく機能しません。

その入力規則&数式を、B列(手入力する列)に適用してみたらどうなりますか？

補足

そうです、ユーザ設定の数式欄となります。

Ｂ列に適用しますと確かにＢ列が重複した場合、エラーを出す事が出来ますが、
やりたいのは、Ａ列も組み合わせた場合で、重複する時に、エラーを出したいのです。

Ａ列はリストになっており、例えば、
Ａ列１、2、3のいずれかの数字をリストから選択し、
Ｂ列は数字を手入力する。
Ａ列＝1　Ｂ列＝2

Ａ列セルとＢ列セルを組み合わせた２桁の値（上記例ですと「12」）が他のＡ列Ｂ列の入力結果と重複した時、エラーを出力したい。というのが希望です。
なので、Ａ列の値とＢ列の値を結合した結果をＣ列に「=A3&B3」と出力させ、その値に対し他と重複するのであればエラーを出そうと思ったわけです。