あけましておめでとうございます。デザイナーの真琴です。今年もみなさまのお役にたてる情報を伝えれるようにがんばりますので、よろしくお願いします。
今年はWeb解析に注力した年にするのが個人的な目標なのでそのことについても記事にできればと思っています。でも今回はExcelについてです。
以前紹介した【VLOOKUP関数】なのですが、使い慣れてくると弱点に気づいてくると思います。
その弱点は、検索列(キー列)が必ず左側にないと検索できない点です。
そこで、今回はその弱点を克服できる【INDEX関数】&【MATCH関数】の紹介です。
VLOOKUP関数は、指定した範囲内で検索条件に一致した数値や文字列を縦方向に検索し表示させる関数です。
=VLOOKUP(検索値,範囲,列番号,検索型)
検索値 | 検索する値を指定。全角と半角の区別はされるが、大文字と小文字の区別はされない |
---|---|
範囲 | 検索したいセル範囲 |
列番号 | 範囲の先頭の列から数えた列数を指定 |
検索型 | 『TRUE-近似一致-』と『FALSE-完全一致-』 |
この関数に関しては、過去に詳しく記事を書いているのでそちらをご覧ください。
業務で使えるExcel関数『VLOOKUP関数』編
この関数は【VLOOKUP関数】の様に配列の中の文字列や数列の値を求める関数です。
=INDEX(配列,行番号,列番号)
配列 | 値を求めたい範囲を配列定数で指定 |
---|---|
行番号 | 【配列】のなかで何行目に当たるかを指定 |
列番号 | 【配列】のなかで何列目に当たるかを指定 |
例えば、3行目にあるバナナの個数をD1に表示したい場合は、D1に【=INDEX($A$1:$B$8,3,2)】と入力すると
A | B | C | D | E | |
---|---|---|---|---|---|
1 | くだもの | 個数 | =INDEX($A$1:$B$8,3,2) | ||
2 | りんご | 7 | |||
3 | バナナ | 3 | |||
4 | いちじく | 17 | |||
5 | りんご | 9 | |||
6 | りんご | 2 | |||
7 | マンゴー | 12 | |||
8 | マスカット | 4 |
B3の値の【3】が表示されます。
正直なところこの関数は単体ではあまり使うことがなく、他の関数と組み合わせて使う場合が多いです。
※INDEX関数には【配列形式】と【セル参照形式】の2種類がありますが、ここでは【配列形式】だけの解説です。
この関数は【検査値】が【検査範囲】のなかの何番目のセルに存在するかを求める関数です。
=MATCH(検査値, 検査範囲, 照合の種類)
検査値 | 検索する値を指定 |
---|---|
検査範囲 | 【検査値】を検索する範囲を指定 |
照合の種類 | 1または省略・・・【検査値】以下の最大値を検索。 0・・・【検査値】に一致する値のみを検索 -1・・・【検査値】以上の最小値を検索 |
マスカットが指定する範囲内の上から何番目にいるか表示させる場合は、E1に【=MATCH(D1,$A$1:$A$8,0)】と入力すると
A | B | C | D | E | |
---|---|---|---|---|---|
1 | くだもの | 個数 | マスカット | =MATCH(D1,$A$1:$A$8,0) | |
2 | りんご | 7 | |||
3 | バナナ | 3 | |||
4 | いちじく | 17 | |||
5 | りんご | 9 | |||
6 | りんご | 2 | |||
7 | マンゴー | 12 | |||
8 | マスカット | 4 |
指定範囲がA1からA8なので、E1には【8】と表示されます。
※この関数も【INDEX関数】と同様に単体ではあまり使うことがなく、他の関数と組み合わせて使う場合が多いです。
「指定範囲の中で○行目、○列目の値」を返してくれる【INDEX関数】と、「指定した値は指定した範囲の○番目にある」を返してくれる【MATCH関数】を組み合わせることで【VLOOKUP関数】と同じ動きをさせると、以下のようになります。
この状態だと、わざわざ難しそうな関数に変えただけで「オレ、エクセルめっちゃ使えるぜ!( -`ω-)ドヤッ!」ってしてる感じだけになりますが、ここからが【VLOOKUP関数】にはできないことです。
冒頭でもお話した、検索列(キー列)が必ず左側にないと検索できない弱点の克服です。
以下の画像のF4:G10の表のデータをもとにC列の価格を表示したい場合、A列の商品がキー列になりG列を参照して、F列の価格を表示させることになります。
C5に【=INDEX($F$5:$G$10,MATCH(A5,$F$5:$F$10,0),2)】
キー列が値を表示させたい列より常に左側にあるとは限らない場合がよくあります。そんなとき、【VLOOKUP関数】は使えないですよね。
その他にも、行列両方のマッチングが可能になります。
列数の値にもMATCH関数を入れてやると、行列両方のマッチングが可能です。
【VLOOKUP関数】と比べると少し理解して使いこなすには難しいとは思いますが、【VLOOKUP関数】で行えることは全て【INDEX関数】&【MATCH関数】パターンでも再現でき、逆に【INDEX関数】&【MATCH関数】パターンでできることのうち一部しか【VLOOKUP関数】には再現できません。
【INDEX関数】&【MATCH関数】は完全に上位互換で、【VLOOKUP関数】の必要性がなくなってしまいました。みなさんもこの際に【INDEX関数】&【MATCH関数】を覚えて快適なExcelライフを送ってください。