Excel関数は組み合わせて使う 「重複データ&複数条件」でもVLOOKUP関数でリストを作れます

重複データがあって、複数の条件を指定したい場合でも、VLOOKUP関数を使う事ができます。最初に、VLOOKUP関数自体についても解説していますので、「重複データ&複数条件」でVLOOKUP関数を使う方法を早く知りたい方については、「2.データの加工がカギ! 『重複データ&複数条件』でVLOOKUP関数を使う方法」からご覧下さい。 

スポンサーリンク

VLOOKUP関数の仕組みと弱点  「重複するデータ」があると、全てのデータを抽出することはできない

次のリストを見て下さい。フォーブスが発表したアスリートの年収ランキング(2013年版)です。

 

スクリーンショット 2015 01 15 12 21 17

 
「このリストから、関数を使ってフロイド・メイウェザー選手の年収を表示して下さい」と言われたら、Excelをある程度使っている人なら、解決策が思い浮かぶかもしれません。

 
VLOOKUP関数です。

 

 

VLOOKUP関数の仕組み

VLOOKUP関数は、「見つけたいデータ」を指定すると、指定した範囲1番左の列に検索して、一致するデータがあれば、指定した数だけ右に進んだセルを表示してくれる関数です。

 
言葉にすると分かりづらいですが、そんな時は”関数の動き”をイメージして理解するようにします。 
”VLOOKUP関数の動き”は、次のような動きです。

 
スクリーンショット 2015 01 15 13 31 11

 

 
VLOOKUP関数を機能させるには、次の4つの要素を入力します。 
冒頭の「データの中から、フロイド・メイウェザー選手の年収を表示する」場合を例に見ていきます。

 
=VLOOKUP(①”Floyd Mayweather”, ②$B$3:$F$103, ③4, ④0)

 
このように入力されました。

 
順番に見ていきます。

 
①見つけたいデータ
 メイウェザー選手のデータを探したいので、”Floyd Mayweather“と入力。 
 文字データなので「” ”」で囲んでいます。

 
②調査するセルの範囲
「どの範囲を対象にするか」を指定します。”どのセルを始点にするか”がポイントです。 
①のデータを調査するのは、「調査するセル範囲一番左の列」なので、
 
・始点になる列=「見つけたいデータが入力されている列
・始点になる行=「リストの先頭の行
 
になるように始点になるセルを決めます。終点になるセルは「リストの右端の列」「リストの最終行」にします。

 
このリストではB列に「名前」が入力されていて、最初の行が 3行目なので、 
始点のセルは「B3」セルです。

 
③列の番号
①と一致するデータが見つかったとき、「一致するデータのあるセルから、右にいくつ進んだセルを表示するか」を入力します。 
表示させたいのは「年収」。「年収」は、「名前」のセル(B列)から数えて””つ右に進んだセル(E列)に入力されているので、””を指定します。

 
④検索の型 
 あまり気にする必要はありません。”0”とします。これで「①見つけたいデータ」とピッタリ一致するもの以外はエラーになります。

 
その結果は、次の通り。
メイウェザー選手の年収が正しく表示されました。

 

スクリーンショット 2015 01 15 13 50 41

 
「名前」のように、リストの中に一致するデータがある場合には、VLOOKUP関数をそのまま使えば、必要なデータ(上の例では年収)を表示することができます。

 

 

VLOOKUP関数の弱点 1

大量のデータの中から該当するデータを見つけて、関連するデータを表示させる方法として、VLOOKUP関数は非常に便利です。 
ただし、VLOOKUP関数には弱点があります。

 
1つは、

 
「調査する列の中に、「①見つけたいデータ」が2つ以上存在しても、最初にヒットしたデータしか取り出すことができない

 
こと。

 
たとえば、年収30,000,000ドルを「①見つけたいデータ」にしたとき、年収30,000,000ドルのアスリートが3名いたとすると、最初にデータがヒットしたら、それ以降のデータは無視されてしまうのです。

 

スクリーンショット 2015 01 15 14 13 49 
(この場合、ハーヴィー・スペクターのデータだけがヒット。同じ30,000,000ドルでも、ジェシカ・ピアソンとマイク・ロスのデータは無視されてしまいます)

 

 

VLOOKUP関数の弱点 2

もう一つは、

 
「『①見つけたいデータ』が1つしか指定できず複数の条件を指定することができない

 
こと。

 
先ほどの例で言うと、「①見つけたいデータ」に「”Floyd Mayweather”」と入力して、これに一致するデータがないかを探していました。
ですが、たとえば、

 
「年齢が25歳以上、30歳未満で年収が20,000,000ドル以上30,000,000ドル未満」

 
のように複数の条件にデータを探したい場合、うまく入力することができないのです。

 
このように「①見つけたいデータ」を柔軟に指定できないのが、VLOOKUP関数のもう一つの弱点になります。

 

 
以上が「VLOOKUP関数」の大きな弱点です。 
ただし、この弱点は克服することができます。もちろん、別の関数を使うわけではありません。 
データの方を加工した上で、「VLOOKUP関数」を使う方法です。

 

 

 

データの加工がカギ! 『重複データ&複数条件』でVLOOKUP関数を使う方法

「重複データ」「複数条件」は、データに”独自コード”をつけることで解決します。

 
冒頭にも出てきた、フォーブスのアスリート年収ランキング(2013年版)から

 
a. 年齢が25歳以上、30歳未満 
b. 年収が20,000,000ドル以上30,000,000ドル未満

 
の複数の条件に該当するアスリートを、VLOOKUP関数で抜き出して、リストを作ることを考えてみます。 
(該当するアスリートは複数いるので、”重複データ”にも対応することになります)

 

スクリーンショット 2015 01 15 12 21 17 
(この中から条件に合うデータだけを抜き出します)

 

 

大まかな流れ

作業は3段階です。

 
1.IF関数を使って、a,b,の条件にあてはまるアスリートを判別

 
2.条件を満たすデータに”通し番号”をつけて、独自コードにする
(通し番号自体が独自コードです。該当しないデータには
番号なしです)

 
3.VLOOKUP関数を使って、独自コードを「①見つけたいデータ」にして、データを抽出
 

以上です。早速、とりかかりましょう。

 

 

1.IF関数を使って、条件にあてはまるデータを判別

該当するデータを明らかにするために、IF関数を使って判定します。

 
まず、判定結果を表示するためにリストの右端に列を追加して、この列にIF関数を入力して判定結果を表示すようにします。

 
年齢の条件式からいきましょう。
リストにある、レブロン・ジェームス選手を例にして考えます。
(レブロン・ジェームス選手のデータは7行目です)

 

スクリーンショット 2015-01-16 21.13.26

 
条件は「25歳以上、30歳未満」ですから、2つの条件の同時に満たすことを、Excel上の式で表さなければいけません。 
このような時は、「AND関数」を使います。

 
AND関数の使い方はシンプルです。

 
=AND(①式,②式,③式,…)

 
と”,(カンマ)”でどんどん式をつないでいくだけです。

 
年齢が入力されているセル(「D7」セル)のデータが「25以上、30未満」であればいいので、

 
=AND(25<=D7,D7<30)
(「以上」の記号は、等号と不等号を合わせて「<=」)

 
これで条件の式ができました。

 
この式をIF関数を使って判定していきます。
IF関数は次のような仕組みです。

 
=IF(①判定する式,
 ②①の式が正しかったときの表示内容,
 ③①の式が誤っていたときの表示内容

 
①は「AND(25<=D7,D7<30)」 
 
②は○、③は×にします。 
ただし、○、×は文字データなので””で囲むことに注意します。

 
まとめる、次のような式を入力することになります。

 
=IF(AND(25<=D7,D7<30),”○”,”×”)

 
その結果が次の画像です。

 

スクリーンショット 2015 01 15 16 15 47

 
判定のセルに「○」が表示されています。
レブロン・ジェームス選手は28歳で、25歳以上30歳未満ですから、判定結果が正しく表示されていることが分かります。

 

 
次に、年収の条件です。
「20,000,000ドル以上、30,000,000ドル以下」で、年収のデータはE列にありますから、年齢の条件と同様に、AND関数と等号、不等号の記号を使って式を作っていきます。

 

  =AND(20,000,000<=E7,E7<=30,000,000)

 
この式を先ほどのIF関数の①に入力します。

 
ただし、年齢の条件で、すでにAND関数をつかっていますから、
上の式(20,000,000<=E7,E7<=30,000,000)を追加する形にすればOKです。すると、

 
=IF(AND(25<=D7,D7,20,000,000<=E7,E7<=30,000,000)<30,”○”,”×”) 

 
式はこのようになります。

 
実際の入力結果がこちら。

 

スクリーンショット 2015 01 15 16 38 58

 
判定のセルに「×」が表示されています。
レブロン・ジェームス選手は、28歳で年齢の条件はあてはまりますが、年収5,980万ドルで、年収条件の3,000万ドルを超えていますから、条件から外れます。ですので、判定結果は”×”になり、正しく表示されていることが分かります。

 
式が正しいことが分かったので、全てのデータを判定するために、この式をコピーしていきます。

 
その結果がこちらです。
 

スクリーンショット 2015 01 15 16 41 12

 
これで、条件に当てはまるアスリートには「○」がついて、該当するアスリートが判別できました。

 

 

2.独自コードをつける

1.で○のついたアスリートに”通し番号”をつけます。
この”通し番号”を「独自コード」(重複しないように、1つ1つを別データとして区別するためのコード)とすることで、VLOOKUP関数の「①見つけたいデータ」が重複する場合に対応します。

 
今回は”○”のついたデータを全て抜き出す必要があります。 
当然、”○”のあるデータは複数あるので、重複することになりますが、”○”のあるデータについて順番に「1,2,3,…」と”通し番号”を付ければ、 
同じ”○”のついたデータでも、別データとして区別することができます。

 
VLOOKUP関数には、この”通し番号”によって区別された「独自コード」を「①見つけたいデータ」にして、探してもらうことになります。

 
まず、VLOOKUP関数の「①見つけたいデータ」は、リストの左端にこなければいけないので(VLOOKUP関数では「②調査する範囲」の一番左端の列を検索するため)
”通し番号”がリストの左端になるように、列を追加します。

 

スクリーンショット 2015 01 15 16 46 20

 
”通し番号”をつけていきましょう。
「判定」のセルがあるH列が”○”になっているアスリートに番号をつけます。

 
「上から数えて、何番目の○にあたるか」が”通し番号”になります。

 
これには、COUNTIF関数を使います。
COUNTIF関数は、指定した範囲を調査して、条件を満たすデータの数がいくつあるかを表示する関数です。

 
COUNTIF関数は次のような仕組みになっています。

 
=COUNTIF(①調査するセルの範囲、②見つけたいデータ)

 
ポイントは、「①調査するセルの範囲」の指定のしかた

 

 
(注)  
ここは少し難しいところです。 
じっくり考えながら少しずつ進めて下さい!

 

 
調査するのは条件を判定したH列ですが、調査範囲を列全体ではなく、

 
始点リストの1行目のセルH4セル)、
 
終点入力セルと同じ(入力セルがA4セルなら、H4セル。A13セルなら、H13セル)

 
になるように指定します。

 

スクリーンショット 2015 01 15 16 54 03

 
これで、COUNTIF関数を入力した行までの範囲で、何番目の”○”になるかを表示することができるのですが、全てのセルでいちいち指定範囲を変えていくのは非効率です。

 
実際の入力では、「COUNTIF関数」の式をコピー&ペーストしていきます。

 
その際、正しく範囲指定できるように、具体的には、コピー&ペーストしても 
始点のセル固定されたままで、終点のセルだけ広がっていく」 
ように指定することがポイントになります。

 
そのためには、始点のセル(H4)は、行・列ともに$マークをつけた「絶対参照」に。

 
終点は、コピー&ペーストすることによって、行が移動していく(下に広がる)ように、列にだけ$マークをつけて、行には$マークをつけない相対参照」にします。

 
最初の行の式は次のような式になります。

 
=COUNTIF(H4:$H4,”○”)

 

スクリーンショット 2015 01 15 17 17 23

 
式が正しいことが分かったので、このセルの式を全ての行にコピー&ペーストして行きましょう。

 

スクリーンショット 2015 01 15 17 28 09

 

H列に○のあるデータにだけ、”通し番号”がつきました。
「独自コード」の完成です。

 

 

3.VLOOKUP関数でデータを取り出す

ここまで準備ができると、あとは「VLOOKUP関数」で該当するデータを抜き出すだけです。
VLOOKUP関数は、次の4つの要素を入力することになります。

 
=VLOOKUP(①見つけたいデータ, ②調査するセルの範囲, ③列番号 ,④検索の型)

 
それぞれの要素を、今回のケースにあてはめてみると、

 
①見つけたいデータ
 「独自コード」=”通し番号” 
 
②調査するセルの範囲
リスト全体。 
一番左端の列は、「2.独自コードをつける」で付け加えた、”通し番号”が入力された列が来るようにする。 
リストの始まりにあたる、A3セルを始点に、リストの終点H103セルまで。 
調査するセルの範囲は変わらないので、$マークのついた絶対参照で固定。
 
③列番号
①と一致するデータが見つかったとき、「一致するデータのあるセルから、右にいくつ進んだセルを表示するか」を入力。 
全てのデータを抜き出すので、転記するリストに合わせて指定しますが、具体的にはMATCH関数を使って指定します。
 
MATCH関数は、「①見つけたいデータ」と「②範囲」を指定すると、「『①見つけたいデータ』が指定範囲の左端のセルから数えて何番目のセルにあるか」を表示してくれます。
(詳細はこちらでも説明しています。

 
④検索の型
 ”0”(見つけたいデータと一致するものだけを表示する)

 
実際に入力するのは、

 

=VLOOKUP(①$J4,②$A$3:H$103$,③MATCH(K$3,$A$3:$F$3,0),④0)

 
このような式になります。

 
その結果がつぎの画像です。

 

スクリーンショット 2015 01 15 17 49 54

 
該当するデータがあったようです。

 
このセルをコピー&ペーストしていきましょう。

 
スクリーンショット 2015-01-15 17.56.12 

 

P5セルを見てみると「バスケットボール」になっています。
名前を見ると「Carmelo Anthony」でNBAのプレイヤーですから間違いありません。
年齢、年収の条件もクリアしています。

 
式に間違いがないので、この要領でコピー&ペーストすると、

 

スクリーンショット 2015 01 15 18 00 15

 
サイズの関係で全体をお見せすることができませんが、
VLOOKUP関数、IF関数、MATCH関数、とデータの加工によって、
 
「年齢が25歳以上、30歳未満で年収が20,000,000ドル以上30,000,000ドル未満のアスリートのリスト」
 
が完成しました。

 

 

まとめ

Excelは基本的な関数をよく理解して、組み合わせることと、データを加工することで、広く効率よく使うことができます。
柔軟なアイディアがExcelを使いこなす鍵になります。

 
<おまけ>
『実務で使えるExcel入門セミナー 基礎編』を2月14日(土)の14時から恵比寿のセミナールームで開催することになりました。
ご興味のある方は以下のサイトをご覧下さい。

タイトルとURLをコピーしました