Windows TIPS

［Office Master］

→ Windows TIPS TOPへ → Windows TIPS全リストへ → 内容別分類一覧へ Excelワークシートで重複データを検出する → 解説をスキップして操作方法を読む 山田 祥寛 2003/09/20 　 対象ソフトウェア Excel 2000 Excel 2002

■ Excelで住所録や名簿などの大量のリストを作成していると、データの重複が気にかかるケースが少なくない。 ■ ExcelにはAccessのような主キーの概念は存在しないが、COUNTIF関数を使用することで同じような制約を設定することが可能になる。

　

解説

　Excelで大量のデータを含むリストを作成している場合、重複データの発生が気になるケースはないだろうか。もちろん、一意となるべき項目について並べ替えなどを行なえば、比較的用意に重複データを見つけることはできるだろう。だがデータが何百件、何千件ともなれば、目視でのチェックは非常に面倒であるし、完全に漏れなくチェックすることはほぼ不可能である。

　ExcelではAccessのような主キー（primary key。テーブル内のデータを一意に識別するための列データ）の設定はできないが、その代わり、条件付き書式と入力規則を用いることで、擬似的に重複データの発生を防ぐことが可能となる。

　本稿では、すでに用意されているリストから重複データを「検出する」方法と、これから入力するデータの重複を「防ぐ」方法と、2つの観点から紹介を行なってみたい。

操作方法

手順1―元となるリストを用意する

　リストの内容は特に問わないが、ここではサンプルとしてサイトURLリストを扱ってみることにしたい。リストは次のように「サイトタイトル」「URL」「アクセス数」から構成されるものとする。ここで主キー（重複してはならない項目）は「URL」とする。

サンプルExcelファイル

「サイトタイトル」「URL」「アクセス数」という3つの列を持つExcelファイル。このうち、「URL」の内容が重複しないようにデータを管理するものとする。

• サンプル・ファイルのダウンロード
  （注：サンプルduplicate.xlsをダウンロードするには、上のリンクを右クリックして、duplicate.xlsというファイル名で保存してください）

手順2―条件付き書式を設定する

　まずは既存のリストから重複データの有無を確認してみることにしたい。重複を検出したい項目「URL」（C列）全体を選択状態にした上で、メニュー・バーから［書式］−［条件付き書式］を選択する。すると「条件付き書式の設定」ダイアログが表示されるので、必要な条件を入力する（条件付書式を使った例については、「TIPS―n行おきにExcelのセル書式を変更する」も参照のこと）。

条件付書式の設定

これを使うと、条件に応じて変化する書式を定義することができる。

入力規則の種類を選択する。詳細は「TIPS―Excelでユーザー・カスタムの入力規則を定義する」を参照のこと。 　 条件式。ここでは「数式が」を選択しているので、数式はTrueかFalseを返すように設定しなければならない。 　 条件が合致した場合の書式を設定するには、これをクリックする。フォントやスタイル、背景色などを選択可能。ここでは、背景色を灰色にしている。

　条件式には、以下のような式を入力する。

=COUNTIF(C:C,C1)>1

　COUNTIF関数は指定された範囲内で検索条件に合致した値がいくつあるかをカウントする関数で、構文は次の通りである。

COUNTIF(範囲, 検索条件)

　つまり、上で示した条件式は、「C列（C:C）の中にセルC1で表わされる値が1より大きい（重複した値が2個以上ある）」場合にTrueを返す。

　ここで「検索条件」をC1に限定していることが奇妙に感じるかもしれないが、条件付き書式を列全体に指定する場合には、これで各セルごとに相対パスが設定される。試しに条件付き書式をいったん確定した後に、セルC3のみを選択して、条件付き書式の設定を確認してみよう。条件式は、以下のようにセットされているはずだ。

=COUNTIF(C:C,C3)>1

　さて設定が完了したら、任意の行を追加し、重複するようなデータを入力してみて欲しい。すると、重複するセルの背景色が灰色に変わるはずだ。

手順3―入力データ規則を設定する

　次は、新しくデータを追加する際に、重複データが入力できないように「入力データ規則」を設定してみよう。手順2と同様に、重複を検出したい項目である「URL」（C列）全体を選択状態にしたうえで、メニュー・バーから［データ］−［入力規則］を選択する。

　「データの入力規則」ダイアログが表示されるので、必要な条件を入力してみよう。

データの入力規則の設定

入力規則を設定すると、入力されるデータに対して制約を付けることができる。与えられた条件がTrueになる場合にのみ、データが入力される。

入力規則の種類を選択する。 　 数式。ここでは「入力値の種類」で［ユーザー設定］を選択しているので、数式はTrue／Falseを返すように設定しなければならない。条件式がTrueになる値だけが入力を認められる。

　条件式には、次のような式を入力することにする。

=COUNTIF(C:C,C1)=1

　つまり、「C列（C:C）の中にセルC1で表わされる値が1である（入力値がC列のいかなる値とも重なっていない）」場合にのみ入力を許可する。

　設定が完了したら、また任意の行を追加し、重複するようなURLを入力してみよう。すると以下のようなエラー・メッセージが表示されるはずだ。このエラー・メッセージの指定方法や、入力時にユーザーが指定したメッセージを表示させる方法については、「TIPS―Excelでユーザー・カスタムの入力規則を定義する」を参照して欲しい。

エラー・メッセージ

入力規則を満たさないデータが入力されると、このようなメッセージが表示される。

　

「Windows TIPS」

＠ITメールマガジン　新着情報やスタッフのコラムがメールで届きます（無料）