ダブリをチェックする方法（EXCEL）

質問

あるものが３０個あるとします。
それらはもともと、Ａ～Ｚという２６のアルファベットで種別が付けられているとします。
３０個のうち、あるアルファベットが欠けていたり、または、あるアルファベットが２個以上あったりするとします。
たとえば、３０個のうち、Ａは欠けており、Ｊ、Ｋ、Ｌが２個ずつ、Ｍが３個あったり、というケースです。

それを、別の区分で５つ（第１分類～第５分類）に分け、エクセルを作成するとします。
どの区分に属するかは、人間の主観で判断するとします。
また、同じアルファベットのものは、同じ分類になるようにしなければなりません。
しかし、複数の人が作業するとき、ある人はＪを第２分類に分類し、ある人はＪを第５分類に分類した、ということが起こりえます。

どちらに分類すべきかは、協議の上決まります。さらに、２個以上あるものはダブりとして、１個のみをデータに残すことになります。
そのためにはまず、１つのアルファベットが複数の分類に分類されてしまってることに、気づかなければなりません。
これまでそのチェックを、ただ目視で行っていましたが、できればエクセルの機能で簡単な方法があれば知りたいと思い、質問させていただきます。
なお、第１分類から第５分類まで、項目は同じ列です。

非常にわかりづらい質問文で申し訳ありませんが、よろしくお願いいたします。

回答No.5

dogs_cats · ベストアンサー

NO4です。

B列内の重複をしたいのでしたら、B列全体選択後、条件付き書式の数式を下記で設定で
=COUNTIF(B:B,B1)>1

３つ以上の重複と２つだけの重複を区別したいのなら、条件付き書式をB列で２回行います。
２回重複
=COUNTIF(B:B,B1)=2

３回以上の重複
=COUNTIF(B:B,B1)=3

条件付き書式の数式を使用する方法を下記に再度添付しますので、お読みになって下さい。
http://love-guava.com/excel-conditional-format/

添付画像は２回重複をマゼンダ、３回以上を水色でB列の条件付書式をしています。

お礼

お礼が遅くなってすみませんでした。
再度のご回答ありがとうございました。

できました！
私にはなぜかどうしても
=COUNTIF(B:B,B1)>1
が作れませんでした。

しかし貼っていただいたリンクの中の、条件付き書式という方法で、

Ｂ列を選択⇒「条件つき書式」⇒「セルの強調表示ルール」⇒「重複する値」

で、重複したセルが色づく設定にできました！

何度もありがとうございました。
とても助かりました。

（これは、完全一致の場合のみに強調されるのですね。「長ねぎ」「長ネギの葉の部分」の場合には●●色、ということはできないのですね）

回答No.6

kagakusuki

>例では、長ネギが、区分２と区分３でダブっています。
>それを発見したいのです（見つけた後、責任者が、長ネギをどちらに分類するかを判断しますので、「すぐに削除」ではなく、「発見」したいのです）。
>よって、Ｂ列での重複を、見つけたいのです。

　それでしたら以下の様な方法は如何でしょうか？

　入力用の表が何というシート名のシートに入力されているのかという事や、A～Zの種別や、第1～5の区分が何行目以下に入力されているのかという事が説明されておりませんので、取りあえず仮の話として、表が存在するシートがSheet1であるものとして、そのシートのA列に区分が、B列に名称がそれぞれ入力されていて、行番号が2行目のセルには「名称」や「区分」などといった項目名が入力されていて、実際のデータは3行目以下に入力されているものとします。
　又、Sheet2のA列を作業列として使用して、Sheet1のD列に「複数行に重複して入力されている『名称』」を表示させ、E列に「『名称』が重複している行に入力されている全ての『区分』」を、F列に「『名称』が重複している行の行番号」を表示させるものとします。
　又、元の表の「名称」の列のセルには、「重複して入力されている『名称』が最初に現れたセル」をオレンジ色に、「重複して入力されている『名称』が2回目以降に現れたセル」を赤色に塗りつぶす条件付き書式を設定するものとします。

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

=IF(INDEX(Sheet1!$B:$B,ROW())="","",IF(COUNTIF(Sheet1!$B:$B,INDEX(Sheet1!$B:$B,ROW()))>1,MATCH(INDEX(Sheet1!$B:$B,ROW()),Sheet1!$B:$B,0)*10000000+ROW(),""))

　次に、Sheet2のA3セルをコピーしてSheet2のA4以下に貼り付けて下さい。

　次に、Sheet1のD2セルに「名称」、E2セルに「区分」、F2セルに「行番号」と入力して下さい。
　次に、Sheet1のF3セルに次の関数を入力して下さい。

=IF(ROWS(F$3:F3)>COUNT(Sheet2!$A:$A),"",MOD(SMALL(Sheet2!$A:$A,ROWS(F$3:F3)),10000000))

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

=IF(ISNUMBER($F3),IF(INDEX($B:$B,$F3)=INDEX($B:$B,MAX(SUM($F2),ROW($B$2))),"",INDEX($B:$B,$F3)),"")

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

=IF(ISNUMBER($F3),INDEX($A:$A,$F3),"")

　次に、Sheet1のD2～F3のセル範囲に対して縦横に罫線を設定して下さい。
　次に、以下の様な操作を行って、Sheet1のD3セルに対して条件付き書式を設定して下さい。

Sheet1のD3セルを選択
　　↓
Excelウィンドウの[ホーム]タブをクリック
　　↓
現れた「スタイル」グループの中にある[条件付き書式]ボタンをクリック
　　↓
現れた選択肢の中にある[ルールの管理]をクリック
　　↓
現れた「条件付き書式ルールの管理」ダイアログボックスの中にある[新規ルール]ボタンをクリック
　　↓
現れた「新しい書式ルール」ダイアログボックスの「ルールの種類を選択して下さい」欄の中にある[数式を使用して、書式設定するセルを決定]をクリック
　　↓
現れた「次の数式を満たす場合に値を書式設定」欄の中に

=AND(D3="",$F3<>"")

と入力
　　↓
「新しい書式ルール」ダイアログボックスの[書式]ボタンをクリック
　　↓
現れた「セルの書式設定」ダイアログボックスの[罫線]タブをクリック
　　↓
現れた「スタイル」欄の選択肢の中から[なし]を選択してクリック
　　↓
「罫線」欄のセルの上側の罫線をクリック
　　↓
「条件付き書式ルールの管理」ダイアログボックスの[適用]ボタンをクリック
　　↓
「条件付き書式ルールの管理」ダイアログボックスの[OK]ボタンをクリック

　次に、Sheet1のD3～F3のセル範囲をコピーして、Sheet1のD列～F列の4行目以下に貼り付けて下さい。
　次に、以下の様な操作を行って、Sheet1のB列のセルに対して条件付き書式を設定して下さい。

Sheet1のB3セルを選択
　　↓
Excelウィンドウの[ホーム]タブをクリック
　　↓
現れた「スタイル」グループの中にある[条件付き書式]ボタンをクリック
　　↓
現れた選択肢の中にある[ルールの管理]をクリック
　　↓
現れた「条件付き書式ルールの管理」ダイアログボックスの中にある[新規ルール]ボタンをクリック
　　↓
現れた「新しい書式ルール」ダイアログボックスの「ルールの種類を選択して下さい」欄の中にある[数式を使用して、書式設定するセルを決定]をクリック
　　↓
現れた「次の数式を満たす場合に値を書式設定」欄の中に

=AND(B3<>"",COUNTIF(B$2:B3,B3)>1)

と入力
　　↓
「新しい書式ルール」ダイアログボックスの[書式]ボタンをクリック
　　↓
現れた「セルの書式設定」ダイアログボックスの[塗りつぶし]タブをクリック
　　↓
現れた背景色のサンプルの中にある赤色の四角形をクリック
　　↓
「セルの書式設定」ダイアログボックスの[OK]ボタンをクリック
　　↓
「新しい書式ルール」ダイアログボックスの[OK]ボタンをクリック
　　↓
「条件付き書式ルールの管理」ダイアログボックスの[新規ルール]ボタンをクリック
　　↓
現れた「新しい書式ルール」ダイアログボックスの「ルールの種類を選択して下さい」欄の中にある[数式を使用して、書式設定するセルを決定]をクリック
　　↓
現れた「次の数式を満たす場合に値を書式設定」欄の中に

=AND(B3<>"",COUNTIF(B:B,B3)>1,COUNTIF(B$2:B3,B3)=1)

と入力
　　↓
「新しい書式ルール」ダイアログボックスの[書式]ボタンをクリック
　　↓
現れた「セルの書式設定」ダイアログボックスの[塗りつぶし]タブをクリック
　　↓
現れた背景色のサンプルの中にあるオレンジ色の四角形をクリック
　　↓
「セルの書式設定」ダイアログボックスの[OK]ボタンをクリック
　　↓
「新しい書式ルール」ダイアログボックスの[OK]ボタンをクリック
　　↓
「条件付き書式ルールの管理」ダイアログボックスの「適用先」欄に設定されているセル範囲の所を、2箇所ともSheet1のB3以下のセル範囲において表として使用されているセル範囲を設定（カーソルとマウスの左ボタンによる範囲選択が使えます）
　　↓
「条件付き書式ルールの管理」ダイアログボックスの[適用]ボタンをクリック
　　↓
「条件付き書式ルールの管理」ダイアログボックスの[OK]ボタンをクリック

　以上です。

お礼

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

質問文の説明が足りず申し訳ありません。
丁寧にご説明下さり、ありがとうございました。

初心者の私には難易度が高すぎて難しかったのですが、とても参考になりました。
お礼申し上げます。

回答No.4

dogs_cats

仮にA1~A3に３０データ入力

C1~C26に２６のリストを入力

E列にC列に対するA列データのカウント数を表示
E1式
=IF(C1="","",COUNTIF(A:A,C1))
下方へオートフィル

Ｆ列に２つ以上のカウントは「重複」と表示、１つだけは「有り」、無い場合は空白
F1式
=IF(COUNTIF(A:A,C1)>1,"重複",IF(COUNTIF(A:A,C1)=0,"","有り"))

視認性を高めるのにはＣ列を条件付書式でセルに色をつける事が出来ます。
添付画像のＣ列
カウントが２以上　マゼンダ
カウント１　　　　水色

条件付き書式を２回設定
Ｃ列を選択後
カウント２以上の数式
=COUNTIF(A:A,C1)>1
書式で好きなセル色を選択

カウント１の数式
=COUNTIF(A:A,C1)=1
書式で好きなセル色を選択

条件付き書式の数式を使用する方法
http://love-guava.com/excel-conditional-format/

添付画像は30行では視認出来ないので、15行としています。

お礼

お礼が大変遅くなり失礼しました。
詳細にご説明くださり、ありがとうございました。

試みてみましたが、うまくできませんでした。
私の説明文が本当によくなかったと反省しております。

Ａ～Ｚは、「種別」ではなく、「名称」でした。
そのため、「最大２６種類」ではなく、無限にありえますが、同じものが複数ある場合があります。

野菜にたとえるなら、一つの籠の中に色々な野菜があり、外側の色で区分するとして、下記のような表を作っています。

「区分１：赤」（←Ａ列に入力）
　　　　　「トマト」（←Ｂ列に入力）
　　　　　「とうがらし」（←Ｂ列に入力）　　　　　

「区分２：白」（←Ａ列に入力）　　
　　　　　「長ネギ」（←Ｂ列に入力）　
　　　　　「かぶ」（←Ｂ列に入力）

「区分３：緑」（←Ａ列に入力）　　
　　　　　「長ネギ」（←Ｂ列に入力）
　　　　　「キャベツ」（←Ｂ列に入力）

上記のような感じです。
例では、長ネギが、区分２と区分３でダブっています。
（籠の中に長ネギが複数あり、取り出した人の見方により、緑に分類されたり、白に分類されたということです。）
それを発見したいのです（見つけた後、責任者が、長ネギをどちらに分類するかを判断します）。

よって、Ｂ列での重複を、見つけたいのです。
よろしくお願いします。

回答No.3

kagakusuki

>２個以上あるものはダブりとして、１個のみをデータに残すことになります。

という事は、最初にその種別が現れた行よりも下の行に同じ種別がダブりとして現れている行があれば、その様な行を全て削除してしまい、その後から各種別についている区分を協議の上で決めれば良いのではないでしょうか？
　A～Zの種別や、第1～5の区分がどの列に入力されているのかという事が何も説明されておりませんので、取りあえず仮の話として種別が入力されている列がA列、区分が入力されている列がB列であるものとします。
　又、その他にもデータが存在していて、表は全部でA列～D列の範囲に存在していて、行番号が2行目のセルには「種別」や「区分」などといった項目名が入力されていて、実際のデータは3～32行目の範囲に入力されているものとします。
　その場合、以下の様な操作を行いますと、種別がダブっている行が全て自動的に削除されます。

A2～D32の範囲(表全体)を選択
　　↓
Excelウィンドウの上の方にある[データ]タブをクリック
　　↓
現れた「データツール」グループの中にある[重複の削除]ボタンをクリック
　　↓
現れた「重複の削除」ダイアログボックスの中にある「先頭行をデータの見出しとして使用する」と記されている箇所をクリックしてチェックを入れる
　　↓
「重複の削除」ダイアログボックスの中にある[すべて選択解除]ボタンをクリック
　　↓
「重複の削除」ダイアログボックスの「列」欄の中にある「種別」(←A～Zの種別が入力されている列の項目名)と記されている箇所をクリックしてチェックを入れる
　　↓
「重複の削除」ダイアログボックスの中にある[OK]ボタンをクリック

【参考URL】
　よねさんのWordとExcelの小部屋 > Excel2007基本講座 > フィルタオプションの設定 > 重複の削除
　　http://www.eurus.dti.ne.jp/~yoneyama/Excel2007/excel2007-filter2.html#jyufukunosakujyo

お礼

お礼が大変遅くなり失礼しました。
詳細にご説明くださり、ありがとうございました。

試みてみましたが、うまくできませんでした。
私の説明文が本当によくなかったと反省しております。

Ａ～Ｚは、「種別」ではなく、「名称」でした。
そのため、「最大２６種類」ではなく、無限にありえますが、同じものが複数ある場合があります。

野菜にたとえるなら、一つの籠の中に色々な野菜があり、外側の色で区分するとして、下記のような表を作っています。

「区分１：赤」（←Ａ列に入力）
　　　　　「トマト」（←Ｂ列に入力）
　　　　　「とうがらし」（←Ｂ列に入力）　　　　　

「区分２：白」（←Ａ列に入力）　　
　　　　　「長ネギ」（←Ｂ列に入力）　
　　　　　「かぶ」（←Ｂ列に入力）

「区分３：緑」（←Ａ列に入力）　　
　　　　　「長ネギ」（←Ｂ列に入力）
　　　　　「キャベツ」（←Ｂ列に入力）

上記のような感じです。
例では、長ネギが、区分２と区分３でダブっています。
（籠の中に長ネギが複数あり、取り出した人の見方により、緑に分類されたり、白に分類されたということです。）
それを発見したいのです（見つけた後、責任者が、長ネギをどちらに分類するかを判断しますので、「すぐに削除」ではなく、「発見」したいのです）。

よって、Ｂ列での重複を、見つけたいのです。
よろしくお願いします。

回答No.2

keithin

あるモノがＡ１からＡ３０セルに３０個あるとして
基本は
=COUNTIF(A:A,A1)
という関数が１なら、Ａ１はその中にそれ１個しかありません。２以上であれば、それは２個以上ありますので分けないように注意しなければいけません。

これをどう使うかは、あなたの工夫次第です。
例えば分ける前の３０個に対して、条件付き書式等で先に「分けちゃダメなヤツ」に色を付けておく等しておき、「人手で振り分ける」の作業に備えるでしょうね。

お礼

お礼が大変遅くなり失礼しました。
詳細にご説明くださり、ありがとうございました。

試みてみましたが、うまくできませんでした。
私の説明文が本当によくなかったと反省しております。

Ａ～Ｚは、「種別」ではなく、「名称」でした。
そのため、「最大２６種類」ではなく、無限にありえますが、同じものが複数ある場合があります。

野菜にたとえるなら、一つの籠の中に色々な野菜があり、外側の色で区分するとして、下記のような表を作っています。

「区分１：赤」（←Ａ列に入力）
　　　　　「トマト」（←Ｂ列に入力）
　　　　　「とうがらし」（←Ｂ列に入力）　　　　　

「区分２：白」（←Ａ列に入力）　　
　　　　　「長ネギ」（←Ｂ列に入力）　
　　　　　「かぶ」（←Ｂ列に入力）

「区分３：緑」（←Ａ列に入力）　　
　　　　　「長ネギ」（←Ｂ列に入力）
　　　　　「キャベツ」（←Ｂ列に入力）

上記のような感じです。
例では、長ネギが、区分２と区分３でダブっています。
（籠の中に長ネギが複数あり、取り出した人の見方により、緑に分類されたり、白に分類されたということです。）
それを発見したいのです（見つけた後、責任者が、長ネギをどちらに分類するかを判断します）。

よって、Ｂ列での重複を、見つけたいのです。

なお、「=COUNTIF(A:A,A1) という関数が１」という関数はどうやってつくるのでしょうか？試みたところ、「=COUNTIF(A:A」まではできましたが、「A1という関数が１」ができませんでした。どうしたらいいのでしょうか。

よろしくお願いします。

回答No.1

aokii

はじめに、A列にアルファベット、B列に対応する正解の分類を記載した一覧表を別シートに作る。
次に、３０個のデータ（A列にアルファベット、B列に分類）が入ったシートで、C列にVLOOKUP関数を入れて、A列のアルファベットを検索文字として、先に作っておいた一覧表シートで分類名を検出して、３０個のデータのB列と一致しているかどうかを、EXACT関数で調べてはいかがでしょう。

お礼

お礼が大変遅くなり失礼しました。
詳細にご説明くださり、ありがとうございました。

試みてみましたが、うまくできませんでした。
私の説明文が本当によくなかったと反省しております。

Ａ～Ｚは、「種別」ではなく、「名称」でした。
そのため、「最大２６種類」ではなく、無限にありえますが、同じものが複数ある場合があります。

野菜にたとえるなら、一つの籠の中に色々な野菜があり、外側の色で区分するとして、下記のような表を作っています。

「区分１：赤」（←Ａ列に入力）
　　　　　「トマト」（←Ｂ列に入力）
　　　　　「とうがらし」（←Ｂ列に入力）　　　　　

「区分２：白」（←Ａ列に入力）　　
　　　　　「長ネギ」（←Ｂ列に入力）　
　　　　　「かぶ」（←Ｂ列に入力）

「区分３：緑」（←Ａ列に入力）　　
　　　　　「長ネギ」（←Ｂ列に入力）
　　　　　「キャベツ」（←Ｂ列に入力）

上記のような感じです。
例では、長ネギが、区分２と区分３でダブっています。
（籠の中に長ネギが複数あり、取り出した人の見方により、緑に分類されたり、白に分類されたということです。）
それを発見したいのです（見つけた後、責任者が、長ネギをどちらに分類するかを判断します）。

よって、Ｂ列での重複を、見つけたいのです。
よろしくお願いします。