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時から恵比寿のセミナールームで開催することになりました。
ご興味のある方は以下のサイトをご覧下さい。

スポンサーリンク
自動サイズレスボンシブ
自動サイズレスボンシブ

シェアする

  • このエントリーをはてなブックマークに追加

フォローする