【Excel】重複がある際に適切なデータを選ぶ(COUNTIF関数を使う)

Excelでの「重複データの扱い」について教える機会が良くあるので、ここに載せておきます。
(2021/3/6重複の削除でもっと簡単な方法があったので追記します)

1.重複とは

例えば、一人で重複する申請があるときに、一人1件分のみを抽出するイメージです。
具体的には、以下のようなイメージです。

一人で複数の申請がある場合は、

一番上か一番下の申請のいずれかのみを申請データとして使用する必要があります。
そこで COUNTIF関数を使用します。

2.一番上の行を取得したい時

C3のセルに 「=COUNTIF(E$3:E3,E3)」を入力する($3は、データの一番上の行の絶対指定)

3.一番下の行を取得したい時

C3のセルに 「=COUNTIF(E3:E$8,E3)」を入力する($8は、データの一番下の行の絶対指定)

4.重複だけをチェックするとき

ちなみに重複しているかどうか調べるのみであれば、「COUNTIF(E:E,E3) 」の様に列全体を選択します。

5.データのユニーク件数をカウントするには

「2.一番上の行を取得したい時」「3.一番下の行を取得したい時」で「1」をつけたもののみカウントします。
「=COUNTIF(C:C,1)」と入力
(本当に急ぎなら、フィルタで「1」が立っているものを数えてもよいですね。)

 

6.シンプルに重複データを削除するには

これ知らなかったのですが、
データ タブ/重複の削除 で重複データ消せるのですね。(2021/3/6追記)

 

(参考)急ぎの時にピボットテーブルを使う人も

他の手段として、PIVOTテーブルを作成して、行ラベルに社員IDを入れ件数を見る手もありますが、
COUNTIFを使った方がすっきりしています。

(参考:PIVOTテーブル画面)

7.このデータをVLOOKUP関数で活用する場合

さて、こういったデータは、次のステップに活用することが殆どです。
今回は、以下のようなケースを事例とします。

●別に対象者名簿がある。

●その名簿に、上記でダウンロードした、回答状況・回答内容の情報を追加する。
(ちなみにダウンロードしたデータがCSVファイルで、数字に見えて文字型のデータがある場合は
CSVデータの扱い(Excelでの取り込み方法他)をご参照ください。(頭の0を落とさずにExcelにできます)

 

上記のようなケースでは、こんな風に対応します。

 

①キー列(KEY列)を作る
ダウンロードしたExcelファイル上で、VLOOKUP関数で使用する「key部分」列を作り、
 必要な行のみ、社員番号を表示するようにする。
(つまり、「1」が立っている行のみ、社員IDを表示できるようにする。)
式は、「=IF(C3=1,E3,””)」で他の行にコピーする。

 

②対象者名簿のVLOOKUPでは、①のキー列を参照する。
対象者名簿上で、VLOOKUP関数で、ダウンロード表を参照する。
その際に、①で設定したkey列をVLOOKUP関数のキーにする。

 

(↓画像をクリックすると拡大表示されます)
※ちなみにVLOOKUP関数を使うときは、列固定($B3)と行固定(D$1)を上手く使い、
一つのセルに式を書いたら、他のセルに一気にコピーできるようにするのが便利。
見本では、D3に式を書いたら、右側・下側に全てコピーで①の回答データの参照が出来る。
(そのためには、「1行目」に参照列番号を入力している(D1に6・E1に7))

8.更に便利に。雛形戦法

 

更に効率化します。上記の発展系です。
「雛形戦法」です。(私が勝手に名付けているのですが)

 

ダウンロードを一度しかしないのであれば、
上記の方法(ダウンロード・COUNTIF関数・IF関数・VLOOKUP関数)は1回だけですが、
進捗を見るために何回もダウンロードして、名簿を更新するのであれば、
毎回式を書くのは時間の無駄です。
始めに対象者名簿と、ダウンロードリスト(回答シート)に式まで書き込んで「雛形」にしておきましょう。

 

そして、その先は以下の手順を繰り返します。

 

①ダウンロード雛形リスト(回答シート)に、ダウンロードデータを貼り付ける。
(どこに貼り付けるかコメントをつけておくとわかりやすい)
増えた行は式をコピー
(この場合で「一番下の行を取得したい時」はCOUNTIFの最後の列を多めにしておく)
↑「3.一番下の行を取得したい時」のスクリーンショットの下の赤字部分です。

 

②それだけで、名簿のデータも更新される。
 (この場合、IFERROR関数を作って、#N/A を例えば「未提出」にしておくと、よりキレイ)

 

↓こんな感じです。
雛形に貼り付ける戦法
(↓画像をクリックすると拡大表示されます)
これだと、ダウンロードして名簿の更新まで、5分で処理できます。
ここを節約することで分析に時間をかけることができます。