2015/11/18 公開

VLOOKUP関数で同一の検索値が複数ある場合のデータ抽出術!EXCEL VLOOKUP術[Vol.4]前編

Pocket

  • ????????????????????

Excelシート上の参考表から必要なデータを検索し、指定した箇所に表示させることのできるVlookup関数。大変便利な関数ですが、いくつかの弱点があります。そのうちの一つが、参照表内に同じ検索値が複数ある場合に、最初に一致した検索値を引用してしまうというもの。シリーズ4回目となる今回は、Vlookup関数COUNTIF関数を組み合わせることで、同一の検索値が複数ある場合に任意のデータを抽出する方法をご紹介します。

Vlookup関数は、検索値が複数の場合に機能しない!?COUNTIF関数で対応しよう!

例えば、以下の表で、左の参考表に従って右の表の「担当者名」を埋めていきたい場合、
1

普通にVlookup関数を使っても上手くいきません。仮にF2のセルにVookupの式を入力してみます。
2

=VLOOKUP(D2,A:B,2,0)、すなわち「D2のセルと一致する値を、A列からB列の検索範囲内から探し、検索範囲の2列目の値を返す」という式をF2に挿入し、これを7行目までコピー&ペーストします。

すると以下の表のように、F列には取引先ごとにすべて同じ担当者名が表示されてしまいます。なんとか株式会社の場合は「山田真一」「山村和彦」「青木修平」と表示したいところを、すべて「山田真一」となってしまっている訳です。
3★

これは、Vlookup関数の特性上、同じ値の検索値が複数ある場合に、検索範囲の一番最初のデータが引用されてしまうため。参照表で「なんとか株式会社」を検索値とするデータは3つ存在しており、そのうちの一番最初にヒットする値である「山田真一」が表示されてしまうのです。

この問題を解決するには、複数ある同一の検索値をそれぞれ唯一無二のものにすればOK。そのために「COUNTIF」という関数を使います。

重複データの処理に便利なCOUNTIF関数

さて、話を進める前に、一旦COUNTIF関数の使い方を学びましょう。COUNTIF関数とは、検索範囲内から条件に一致するセルの個数を数字で返してくれるというもの。Excelシート上のリストの中で、重複するデータがあるかどうか調べる時などに大変便利です。

例えば、以下の表でA列に入力された番号にダブりがあるか調べたい場合。B列にCOUNTIF関数の式を入力することで、番号の隣のセルに出現する数を表示することができます。
4

B2のセルにに挿入する式は
=COUNTIF(A:A,A2)
A2と同じセルの値のセルがA列でいくつあるのか数える式です。

定義は以下の通り。

定義

この式をB22までコピー&ペーストした結果が以下の表です。
2

「195」という値は3つ、「160」は1つ・・・というようにA列の各セルについて、A列で重複して入力されているかどうか確かめることができるのです。

COUNTIF関数を工夫すれば、重複するセルを一括して抽出できる

COUNTIF関数を使えば、いくつ重複するデータがあるのか数えることができると説明しました。しかしながら、この方法では各セルのデータについて、他にダブっている値のセルが存在することは分かっても、一目瞭然で消すべきデータを見分けることができません。

こんな場合は、以下のように関数に少し細工をします。
3

B2のセルに挿入するのは、
=COUNTIF($A$2:A2,A2)

この式を一番下までコピー&ペーストすると以下のような表が出来上がります。
B列の数字が示すのは、各セルの番号がその列で何番目に出てきたかということ。つまり、B列で2以上の数字が出たA列の値は、それより上の行ですでに出現しているということになるわけです。よって、B列で「2」以上の値を削除すれば重複データは無くなります。
4

なぜでしょう。B2に入力した式は=COUNTIF($A$2:A2,A2)
ポイントは、検索範囲を指定する「$A$2:A2」のコロン以前の部分に絶対参照をしめす「$」のマークをつけること。こうすることで、この式を他のセルにコピー&ペーストしたとしても、$マークの直後の値を不変にできます。

つまり、この式を下方向にコピーしていった場合、B3 のセルでは「$A$2:A3,A3」、B4のセルでは「$A$2:A4,A4」・・・と数式が変化するということ。式はそれぞれ「A2からA3までのセルで、A3の値はいくつかあるか」「A2からA4までのセルで、A4の値はいくつあるか」・・・・という意味を表し、検索する範囲がどんどん下へ広がっていくイメージです。

とすれば、B列の各セルに入力された関数は、自分自信より下の行のセルは参照していないことになり、よってB列に表示された数字は「そのセルの隣にある値が、A列で何回目に出てきたか」ということを表すのです。

「重複の削除」は使えない!?

COUNTIF関数を使って、重複するデータを抽出する方法をご紹介しました。この方法を活用すれば、複数の重複するデータに対応できないVlookup関数の弱点をカバーすることが可能になります。具体的なやり方を学ぶのは、次回に回しましょう。

ちなみに、Excelには「重複の削除」という機能がついておりますが、これは重複しないデータまで削除してしまうバグが確認されているそうです。

式も複雑でないCOUNTIF関数を覚えておいて損はありません!

Pocket

マネたま編集部
「現場視点から考えると、マネジメントがもっとオモシロクなる」をコンセプトに、マネジメントに関する情報を発信していきます。