*

【Excel初心者向け】関数で重複データをチェックする方法

公開日: : 最終更新日:2014/07/09 お役立ちネタ , ,

業務でIDリストなどのデータを扱っていて、データの重複有無などのチェック作業を効率的に済ませたいと思ったことはありませんか?Excelを使うとこのような作業を早く・正確に行うことができます。ただし、そのためには多少関数の知識が必要になるため、Excelを使い慣れてない方にとっては意外とハードルが高いようです。ここではそんなExcel初心者の方のために、Excelの関数を使ったデータチェック方法を噛み砕いてご説明します。

 

 

基礎知識

Excelの関数を使う上で、知っておくべき基礎知識です。ごく基本的なところですので、「そんなの知っているよ!」という方はこちらの「実践編」へお進みください。

 

値と参照

Excelの関数とは”計算”で、その計算対象となるものには”値”と”参照”があります。

例として単純な足し算で考えてみます。「1と2を足す」を数式で表すと「1+2」。このとき1と2は”値”です。

次に「あるセルに入っている値(中身の数値は不明)を足す」場合を考えてみます。仮にA1セルとA2セルに入っている値を足すとするなら、数式で表すと「A1+A2」。このときA1とA2は”参照”です。

ここで重要なことは、A1+A2の計算結果はA1・A2それぞれのセルにどんな値が入っているかで変化することです。A1=1およびA2=2なら算出結果は3。A1・A2セルの値が変われば算出結果も変わります。

 

絶対参照と相対参照

参照には「絶対参照」と「相対参照」の2種類があります。

例えばA列とB列の値を足し算して計算結果をC列に表示したい場合。A列・B列にはそれぞれ100個の値がある場合、つまりA1+B1〜A100+B100まで100パターンの計算が必要な場合を考えます。

これを楽にやるにはどうすれば良いでしょうか?

まずはC1に「A1+B1」という数式を入れます。次にその数式(C1セル)をコピーしてC2〜C100セルに貼付けると、C2には「A2+B2」、C3には「A3+B3」、・・・という具合に、計算式中のA列・B列が1セルずつズレて設定されて一発で目的を達成できました。楽チンですね。

ところでC2〜C100へ貼り付けをしたとき、なぜ数式のA列・B列部分がズレてくれたのでしょうか?これはExcelがC1の数式「A1+B1」を「C1から見て二つ左のセルと一つ左のセルの値を足す」という意味に解釈しているからです。この関係を、「C1の数式中のA1,B1が”相対参照”扱いになっている」と言います。

もしA1の値は固定で、C2には「A1+B2」、C3には「A1+B3」、・・・という足し算をしたい場合はどうでしょうか?

まず、C1セルの計算式中のA1にカーソルを当ててキーボードのF4を押します。すると$A$1のように$マークが付きます。次に、上記同様C1セルをC2〜C100に貼付けます。すると、C2には「$A$1+B2」、C3には「$A$1+B3」、・・・という具合に、A列部分はA1に固定され、B列部分だけ数字がズレるので、思惑どおりのA1とB列の足し算ができます。

この$マークは「コピペしてもズレないですよ」という意味です。この例では”$A$1″ですので「C1セル(数式)をコピーして他のセルへ貼付けてもA1を参照する(A2、A3、・・・のようにズレない)」ということを表します。この関係はを、「C1の数式中のA1は”絶対参照”扱いになっている」と言います。

相対参照と絶対参照、イメージできたでしょうか?

 

数値と文字と参照

数字の”1″は、足したり引いたり計算ができる”数値”です。同時に、「ひとつであること」を示す”文字”でもあります。

また、”A1″は「A1セルに入っている値(参照)」という意味と、「”A1″という文字」という意味の2パターンがあります。

Excel上で関数や計算式を手で入力していくときには、式中の項目を数値として扱うのか、文字として扱うのか、それとも他セルの参照として扱うのかを意識する癖を付けておくと良いです。Excelは使う人のために色々と自動処理をしてくれるのですが、そのせいで「文字として扱いたいのに、勝手に参照扱いになって困る!」といった問題が出てくることもあります。そんなとき、数値・文字・参照の違いを把握できていれば、どこをどうすべきなのか判断しやすくなります。

 

別シートのセル参照

Excelはひとつのファイル中に複数のシートを持つことができます。別シートの特定のセルを参照したい場合はどうしたら良いでしょうか?

そんなときはセル名の前に「シート名!」を付けて、「シート名!セル名」とすることでそのシートの特定セルを参照することができます。

例えばSheet1のA1セルに入っている値を参照するなら「Sheet1!A1」とすればOKです。

 

よく使う基本的な関数

これだけは覚えておきたい、基本的な関数をピックアップしました。

別に暗記しなくても良いのですが、頻繁に使うのでその都度調べるより最初に覚えてしまった方が楽です。

 

SUM

数値を合計する関数。

ExcelのメニューバーのΣマーク を押すと複数セルの数値を合計することができますが、これはExcelがSUM関数をセットしているからです。(合計値が表示されているセルにカーソルを当てるとSUM関数の式がわかります。)

ちなみに「SUM(A1:A10)」のような式中の「:」は”範囲”を示します。SUM関数で言えば「このセルからこのセルまでが計算対象」 という意味ですね。

 

IF

条件分岐に使う関数。

指定した条件に一致するかどうかを判定して、

【 YESなら○○する(NOなら××する)】

といった具合に条件付き処理をするときに使えます。

例えばB1セルに IF(A1>0,1) というIF関数を入力すると、A1セルに入っている数値が0より大きい場合のみB1セルに”1″が表示されます。

 

COUNTIF

指定した範囲内で、あるセルの値と一致するセルの数を数える関数。

例えば COUNTIF(A1:A100,B1) は、「A1セルからA100セルの中でB1セルの値と一致するものの数」をカウントします。

 

実践編

さて、ここからが本題です。

以下それぞれのケースについて、Excelの関数でデータチェックをする方法を解説します。

 

2つのデータ(並び順同じ)が一致することを確認する

比較したい2種類のデータを、それぞれExcelのA列・B列に貼付けます。

  • A列:A1〜A100にデータ
  • B列:B1〜B100にデータ

A1〜A100(およびB1〜B100)の100個のデータは、重複しないものとします。
その上で、C列の1セル目、つまりC1に「IF(A1=B1,0,1)」という式(※)を入れます。これは「A1セルとB1セルの値が一致していたら0、そうでなければ1を表示する」という意味の条件式です。

※)A列・B列のデータが数値データなら「A1-B1」という引き算の式でも可。

次にC1をコピーし、C2〜C100に貼付けます。

最後にC列全体を選択し、Excelの右下に出てくる合計数が「合計=0」となっていることを確認します。または、C列にフィルタをかけて、0以外の数値が存在しないことを確認します。

 

 

2つのデータ(並び順バラバラ)が一致することを確認する

前段の条件で、比較したいA列・B列の数値データの並び順が一致していない場合はどうしたら良いでしょうか?

結論から言うとCOUNTIFを使います。

考え方としては、「B1と一致するものがA1〜A100の中にいくつかあるかカウントする、同じくB2と一致するものがA1〜A100の中にいくつかあるかカウントする、・・・」といった具合に、B列の1セル目から100セル目まで一個ずつ、「A1〜A100の中に合致するものがいくつあるか?」をカウントしていきます。一致するものがあればC列のセルに1が入るはず、という理屈ですね。(A1〜A100のデータは重複なしなので、一致するものは1個のはず)

具体的にはC列の先頭、C1セルに「COUNTIF($A$1:$A$100,B1)」という式を入れます。このとき範囲指定は絶対参照としていることにご注意ください。

次にC1をコピーし、C2〜C100に貼付けます。

※C1セルが相対参照になっている状態でC2以降にコピペすると、C2〜C100の式中のA列の範囲指定がズレてしまいます。

最後にC列全体を選択し、Excelの右下に出てくる合計数が「合計=100」となっていることを確認します。または、C列にフィルタをかけて、1以外の数値が存在しないことを確認します。

 

 

2つの列のデータに重複するものが無いことを確認する

さて、上記は「2つの列のデータが一致すること」を確認するものでした。

反対に「2つの列のデータが一致しないこと」、言い換えれば「2つの列の中に重複するものがないこと」を確認するにはどうしたら良いでしょうか?

この場合もCOUNTIFが使えます。

具体的には、前段の「2つのデータ(並び順バラバラ)が一致することを確認する」において、最後の部分を以下に変えればOKです。


最後にC列全体を選択し、Excelの右下に出てくる合計数が「合計=0」となっていることを確認します。または、C列にフィルタをかけて、0以外の数値が存在しないことを確認します。


 

おまけ

最後に、知っておくと便利な小ワザをご紹介します。

数式の計算結果データを値のデータに変える

「他のセルの値をコピペで貼り付けようとしたら数式が貼られて困る!」

そんなときには以下の手順でコピペしてください。

  1. セルをコピー
  2. 貼付けたい場所にカーソルを当てて右クリックorメニューから編集を選択
  3. 「形式を選択して貼り付け」を選択
  4. プルダウンメニューから「値」を選んでOKボタンを押す

これで数式ではなく値がコピペされます。

 

まとめ

Excelの関数を使ってデータの重複チェックを行う方法をご紹介しましたが、いかがでしたでしょうか?データのチェック作業は手間がかかりますし、疲れていたり焦っていたりするときに目視で確認すると見逃すリスクもありますよね。是非Excelをうまく使って、早く・正確にデータをチェックする方法を試してみてください。

また、同じチェック作業を定期的に行うのであれば、関数を設定したExcelファイルを保存しておき必要に応じて再利用するようにすると楽できますよ。Excelに任せられる部分は任せて、良い意味で楽をしましょう。

PR

関連記事

NTT-X Store

PC周辺機器が安いネットショップ『NTT-X Store』

  NTT-X Storeというサイトをご存知でしょうか? 聞いたことがない方も多

記事を読む

iftttでfeedlyでマークした記事をメールで送信

日々の情報収集を爆速化!feedlyとiftttでラクして効率的に最新情報をキャッチアップ

「日々の情報収集をもっと楽に、短時間でできるようにしたい」 あなたはそう思ったことがありません

記事を読む

macでOfficeファイルを開けるフリーソフト『LibreOffice』

【無料】ExcelもWordも!macでOfficeファイルをサクサク開けるフリーソフト『LibreOffice』

先日、macで拡張子xlsxのExcelファイルを開くのに『LibreOffice』を使ってみた

記事を読む

【2014年版】フリー素材サイトまとめ

【厳選40個】無料・商用OKのフリー素材配布サイトまとめ

ブログ記事でもWebサイト制作でも欠かせない画像やイラストなどのフリー素材。都度ネットで検索して

記事を読む

おすすめGoogle拡張リスト

【保存版】情報収集を快適に!Chromeに入れておきたいオススメ拡張(エクステンション)8個

私はChromeをメインブラウザとして使っていますが、その理由のひとつは便利な拡張機能(エクステンシ

記事を読む

PR

コメントを残す

PR

【Git】一つ前のcommitに戻す/他のブランチのcommitを取り込む

久しぶりにRailsでプログラムを書いている関係で、これまた久しぶりに

WordPressの子テーマで画像が表示されないときはstylesheet_directoryを使っているか確認する
WordPressの子テーマで画像が表示されないときはstylesheet_directoryを使っているか確認する

先日とあるお客様のWordPressサイトで「画像が表示されない」

おすすめGoogle拡張リスト
【保存版】情報収集を快適に!Chromeに入れておきたいオススメ拡張(エクステンション)8個

私はChromeをメインブラウザとして使っていますが、その理由のひとつ

Rubyで小数点第2位以下を切り捨てる方法

Rubyには標準で便利なメソッドがたくさん用意されています。数値の取り

BootstrapでWordPressサイトをレスポンシブデザイン化
【やって覚える】BootstrapでWordPressサイトをレスポンシブデザイン化する方法

  先日Googleから「4/21以降、サイトのスマホ

→もっと見る

  • feedlyでフォロー RSS登録ボタン
  • 株式会社YUKARIでは、ホームページリニューアルをメインにWeb制作事業を行っています。既存サイトへのWordPress導入・レスポンシブデザイン化からSEO強化まで何でもご相談ください。 ホームページリニューアルをお考えの方からのお問い合わせをお待ちしております。
PAGE TOP ↑