【IT小技2】Excel>IF関数でデータが重複している行を抽出

この記事で分かること

マスターテーブルのキー列(ID番号など)の中で、データが重複している行を見つける方法
ExcelのIF関数の実践的な使い方

対象読者はExcelのVlookup関数を理解しているレベルとします

Excel使用中の課題

今回は「数千個以上あるデータの中から、重複値を抽出すること」を課題にします。

課題例として、売上個数に単価を掛け算して売上高を計算する場面を考えてみます。具体的には以下(1)～(5)のような状況です。

(1) 日々の売上個数が、売上個数テーブルに蓄積されています
(2) 商品の情報が商品マスタで管理されています
(3) 売上個数テーブルと商品マスタは商品IDをキー列にしてリレーションシップを形成します
(4) 商品マスタから単価や商品名を取得し、売上個数テーブルに追加します
(5) 別テーブルから値を取得する方法はVlookup関数です

実行結果を下に示します。

＜商品マスタ＞

＜売上個数テーブル＞

売上個数テーブルの緑枠がマスタから取得した商品名と単価です。黄色枠が単価に売上個数を掛け算て売上高を計算しています。

しかしこの計算結果は誤りです。

何故なら商品マスタに同じ商品IDで違う商品が存在しているからです。下の図に誤り箇所を赤枠表示しておきます。

＜商品マスタ(誤)＞

＜売上個数テーブル(誤)＞

Vlookup関数の場合、重複したIDの中から最終行の値を戻り値にします。一見すると間違ってなさそうなので、重複に気づきにくいですね。

数行のテーブルならともかく、数百行とか数千行のテーブルなら、重複行の発見は可能でしょうか？キー列を昇順に並び替えて重複行を一行ずつ目視チェック？・・・それは時間がかかりますね。

そこで今回はExcelではお馴染みの「IF関数」を使って、データ重複行を抽出する方法をご説明します。

【答え】 ポイントはIF関数の使い方

以下(1)から(3)の流れで商品マスタの重複行を抽出します。

(1) 重複チェックしたい項目（ここでは商品ID）を昇順に並び替えます
(2) 新しい列(A列：重複行抽出)を挿入し、次のIF関数式を入力します

=IF(B3=B2,”★”,””)　

上の式は例として3行目の式を記述しています

(3) 新しい列(A列)にフィルタを掛けます。抽出条件は「★」です

結果は下の図のようになります。

答えの解説

「データを昇順に並べ替えた後、1行上のセルと値が同じかどうかを、IF関数でチェックする」がポイントです。

そのためIF関数の1番目の引数として、条件式”B3=B2”を渡しています。条件に合致すれば2番目の引数、”★”を戻り値にすることで重複行にマークを付けます。

後は”★”マークが付いた行をデータフィルタ機能で抽出するだけです。

補足　式のコピペを容易にするテクニック

ここでは前述の【答え】の(2)で3行目から最終行まで、簡単に式をコピペする方法を説明します。今回の商品マスタなら3行目から9行目までドラッグすればコピー完了です。

しかしデータが数千行あるマスタだと最終行までドラッグするのは時間が掛かりますね。

試しに3000行までドラッグすると55秒掛かりました

時短のためには次の方法をお試しください。

(1) 式を入力した後、A3列を選択した状態でCtrl+Cを押します
(2) 隣のB3列にカーソル移動してCtrl+↓を押すと、最終行まで移動します（但しB列に空白行がないという前提です）
(3) 最終行のA列にカーソル移動します
(4) Ctrl+Shift+↑を押すと3行目から最終行まで選択した状態になります
(5)この状態でCtrl+Vを押すと式のコピーが完了します

この方法だと55秒から8秒に時短できました

まとめ

以上、お馴染みのIF関数を使って、データ重複行を抽出する便利な小技をご説明しました。課題の例として、マスターテーブルのデータ重複行の有無チェックを取り上げました。

解決のポイントは3つ
①データの並び替え
②IF関数式を使って重複行にマーク付け
③マークをフィルタ抽出

式のコピペを効率化するテクニックも補足で紹介しました。

Excel実務に、ぜひ役立ててください。(終わり)