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

締切り済みの質問

重複エラーを出したい

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

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

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

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

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


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

大変申し訳ございませんが、この投稿に添付された画像や動画などは、「BIGLOBEなんでも相談室」ではご覧いただくことができません。 OKWAVEよりご覧ください。

マルチメディア機能とは?

投稿日時 - 2014-03-16 11:16:28

QNo.8515924

すぐに回答ほしいです

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

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

回答(13)

ANo.13

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

という方法です。
 尚、質問者様が御使いになられている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列に同じ値を何回でも重複して入力する事が出来ます。

大変申し訳ございませんが、この投稿に添付された画像や動画などは、「BIGLOBEなんでも相談室」ではご覧いただくことができません。 OKWAVEよりご覧ください。

マルチメディア機能とは?

投稿日時 - 2014-03-18 02:14:12

ANo.12

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

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

手順:
前回ご案内した通り、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の中をいじればデザインは変えられます。

投稿日時 - 2014-03-17 21:38:18

ANo.11

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

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

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

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

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

投稿日時 - 2014-03-17 13:20:57

ANo.10

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

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

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

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

投稿日時 - 2014-03-17 07:10:38

ANo.9

参考までに補足します。

ご希望の操作が、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ソフトはバージョンによって使用できる機能や操作方法が大きく異なるので、質問の際には必ずバージョンを明記するようにしましょう。

投稿日時 - 2014-03-16 23:06:56

補足

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

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

1行 A列 B列 C列
2行   1   2  12
3行       
とあった場合、

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

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

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

投稿日時 - 2014-03-17 01:00:44

ANo.8

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

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

投稿日時 - 2014-03-16 22:55:13

ANo.7

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

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

大変申し訳ございませんが、この投稿に添付された画像や動画などは、「BIGLOBEなんでも相談室」ではご覧いただくことができません。 OKWAVEよりご覧ください。

マルチメディア機能とは?

投稿日時 - 2014-03-16 22:07:46

補足

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

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

1行 A列 B列 C列
2行   1   2  12
3行       
とあった場合、

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

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

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

投稿日時 - 2014-03-17 01:07:22

ANo.6

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

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

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

投稿日時 - 2014-03-16 18:04:21

補足

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

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

さらに一歩踏み込んで、例えば、B列が入力されている状態で、A列をリストから選択し直し、別の値にして重複した場合、エラーを出力させる事は可能でしょうか。

1 A列 B列 C列
2   1   2  12
3   2   2   22

A列のリストを変更し、
1  A列  B列  C列
2   1    2   12
3  ●1    2   12
リストから「1」を選択した時にエラーを出力させる
これですとやはりC列の値を使用しなければならないのでしょうか。

投稿日時 - 2014-03-16 21:36:22

ANo.5

こちらで動作確認を行いましたが、概ね最初の式で正しく動きます。
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列を手入力し、エンターで確定する。逆にするとうまくいかないので注意。

投稿日時 - 2014-03-16 16:33:32

補足

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

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

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

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

投稿日時 - 2014-03-16 21:09:39

ANo.4

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

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


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

投稿日時 - 2014-03-16 14:53:26

補足

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

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

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

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

投稿日時 - 2014-03-16 15:24:09

ANo.3

再です。

C列の中での重複している個数、という事であれば、
=COUNTIF(C:C,C3)
で求まります。

この値が1ならば、他と重複はしていない、
この値が1より大きければ、どこかに同じデータのダブりがある、
という事がわかりますので、それを判定する場合は、

=IF(COUNTIF($C:$C,$C3)>1,"重複","")

などのようにします。

投稿日時 - 2014-03-16 13:27:57

補足

余計に考えさせてしまいすいません。
また表現の仕方に関するお心添えありがとうございます。
説明が足りませんでした。

「重複した場合エラーを出したい」というのは、
「値2」に同じ値が入った場合(例えばC3セルと同じ「11」が入った場合)、
「データの入力規則」の中の「エラーメッセージ」タブで設定するExcelの機能で、重複エラーとしてポップアップさせたいという意味です。
それにあたって、上記入力規則内の、「設定」タブの「数式」欄にどの様に入力した方がいいのかが、分からない部分となります。

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

投稿日時 - 2014-03-16 14:02:37

ANo.2

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

ご希望の操作は、C列にA列とB列の連結した文字のカウントをC列にしたいということでしょうか?

その場合は、C3セルに以下の式を入力します。

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

ご質問の意味が上記の回答と違う場合は、具体的にどのようなエラー出力がしたのか補足説明してください。

大変申し訳ございませんが、この投稿に添付された画像や動画などは、「BIGLOBEなんでも相談室」ではご覧いただくことができません。 OKWAVEよりご覧ください。

マルチメディア機能とは?

投稿日時 - 2014-03-16 12:03:48

補足

そこまで深く考えて頂いて申し訳無いです。
すいません、説明が足りませんでした。

「重複した場合エラーを出したい」というのは、
「値2」に同じ値が入った場合(例えばC3セルと同じ「11」が入った場合)、
「データの入力規則」の中の「エラーメッセージ」タブで設定するExcelの機能で、エラーとしてポップアップさせたいという意味です。
それにあたって、上記入力規則内の、「設定」タブの「数式」欄にどう入力した方がいいのかが、分からない部分となります。

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

投稿日時 - 2014-03-16 13:59:21

ANo.1

C列の式を、
=IF(COUNTIF($A3:$B3,$A3)>1,"重複",$A3&$B3)

とするか、もしくは、C列はA3&B3そのままにしておいて、D列に判定を表示させる、ということにして、D3に、
=IF($A3=$B3,"重複","")

とするなどです。


ところで、
文中、エラーと書いてあるのは、利用者に注意喚起するためのメッセージ、という意味ですよね?Excel機能上のエラーと紛らわしいので、書き方に留意した方がよろしいかと思います。

投稿日時 - 2014-03-16 11:59:33

あなたにオススメの質問