いまさら聞けないExcelの使い方講座

【Excel】重複データや無効なデータを直す時間がムダすぎる……！修正が必要なデータをエクセルに入力させないワザ

トップスタジオ

2019年12月2日 06:55

データの重複や無効なデータを後から見つけてげんなり……

　Excelで表の作成や管理をしていて、同じデータが複数回入力されていることに気づいたことはないでしょうか。最初から自分で作っている表ならまだしも、長年使い続けられていて、何人もの人が引き継いで使ってきた表だと、過去に入力されているデータに気づかずに、うっかり同じデータを入力してしまうということはありそうですよね。また、内容が不足したり誤っていたりするデータが表に入力されていて、データそのものが無効であるという場合もあります。

　このような場合、重複したデータを削除するのは単純に面倒ですし、表のデータを別の場所で参照していたりすると予想以上に大きな影響を与える場合があります。データが重複するべきではない場所では最初から重複データが入力できないようにしておくことができれば、このような事態を未然に防ぐことができますよね。また、データ入力時にセルをクリックした際に、データを入力する上での注意点をメッセージとして表示できれば、無効な内容の入力を防ぐことができます。

　今回は、Excelの［データの入力規則］の機能を使って、重複データを入力できないように設定する方法と入力時メッセージを表示させる方法を解説します。

❶重複データの入力を禁止し、同じデータが入力されたら警告を表示する

　ある職場では、作業に使ういろいろな部品に商品コードを付け、そのコードに備品庫内での位置や部品の品名などの情報を紐づけて管理しており、それを「備品庫　取扱製品一覧表」（①）というExcel表にまとめているとします。したがって、A列に入力される商品コードが重複すると、管理に支障が出るおそれがありますよね。

　この表で、A列には同じデータを重複して入力できないように設定します。A列の列見出しをクリックしてA列全体を選択（②）し、［データ］タブ（③）→［データの入力規則］（④）をクリックします。

　［データの入力規則］ダイアログボックスが表示されます。［設定］タブの［入力値の種類］欄で［ユーザー設定］（⑤）を選択し、［数式］欄に「=COUNTIF(A:A,A1)=1」（⑥）と入力します。

　先ほど［数式］欄に入力した数式の意味を解説します。この例の場合は、A列に重複データが入力できないようにする条件を設定する必要があります。そのため、ここではCOUNTIF関数を使って、そのセルに入力されている内容がA列内で1つだけ（すなわち、同じ内容のセルが2つ以上存在しない）という条件を表現します。

　COUNTIF関数は、「=COUNTIF(範囲,検索条件)」という書式で使います。この数式により、「範囲」の中に「検索条件」と同じ内容のセルがいくつ存在するかを求めることができます。したがって、この例の場合は、「範囲」はA列、「検索条件」はA列の各セルということになります。ここまでを数式で表現すると「=COUNTIF(A:A,A1)」となります。ここで検索条件のセルを相対参照にするのは、A列のどのセルに対しても「そのセルと同じ内容のものの数を数える」という処理を行うことができるようにするためです。

　さらに、COUNTIF関数で求めた同じ内容のセルの数が1つ（すなわち、そのセル以外に同じ内容のセルがない）の場合のみ入力可能とする、という意味にするため、先ほどの数式に続けて「=1」を入力することにより、条件としての数式「=COUNTIF(A:A,A1)=1」が完成します。

　これで、入力を可能にする条件を設定できたので、次は重複データが入力された時に表示されるエラーメッセージを設定しましょう。［データの入力規則］ダイアログボックスの［エラーメッセージ］タブ（⑦）をクリックし、［無効なデータが入力されたらエラーメッセージを表示する］のチェックマークがONになっていることを確認したら、［スタイル］欄で［停止］（⑧）を選択します。［タイトル］と［エラーメッセージ］欄に、エラーメッセージとして表示させたい内容（⑨）を入力し、［OK］（⑩）をクリックします。

　これで、すでに入力されているデータと同じデータが入力された時にエラーメッセージを表示させて、重複データの入力ができないようにする設定ができました。

　実際にエラーメッセージが表示されるかどうか試してみましょう。セルA8に、セルA3にすでに入力されている商品コードと同じ「A01011」（⑪）を入力して［Enter］キーを押します。

　すると、先ほどダイアログボックスで設定した内容のエラーメッセージ（⑫）が表示されます。［再試行］をクリックすれば、セルA8に入力された内容が削除され、別の内容を入力できます。

　こうすれば、確実に商品コードの重複入力を防ぐことができますね。

❷「入力時メッセージ」を使って入力時の注意事項をスマートに伝える

　表などの管理を行う上で、［データの入力規則］ダイアログボックスから設定できるもう1つの便利な機能が「入力時メッセージ」です。前項で設定したように厳格に禁止や制限を行う必要はなくても、データを入力する際の注意点があることもありますよね。このような場面では、表の欄外に注意点をまとめておいても良いですが、入力するセルをクリックした時に、「入力時メッセージ」としてその注意点が表示されるようにしておくと、入力者にいっそう効果的に注意を促すことができます。

　先ほどの「備品庫　取扱製品一覧表」の例で、C列に製品名を入力する際の注意点を入力時メッセージとして表示させてみましょう。まず、C列の列見出しをクリックしてC列全体を選択（①）し、［データ］タブ（②）→［データの入力規則］（③）をクリックします。

　［データの入力規則］ダイアログボックスが表示されます。［入力時メッセージ］タブ（④）をクリックし、［セルを選択したときに入力時メッセージを表示する］のチェックマークがONになっていることを確認します。入力時メッセージ（⑤）を入力して、［OK］（⑥）をクリックします。ここでは「タイトル」を入力していません。「タイトル」に入力された内容は、表示されるメッセージ内の最初に太字で表示されます。必要に応じて入力しましょう。