【Excel,数式】数式でセルのマッチングと、指定要素を参照する

　初めての方は、はじめまして。再びご覧いただいた方はこんにちは。たろいもです。

　Excelさんは「表計算ソフト」です。「作表ソフト」でも、「方眼紙」でもありません。「表」で「計算」をするソフトなのです、「方眼紙」便利ですけど。

　なので、Excelさんはセル上で計算したり、セル同士の計算したりといった作業が得意です。他にもセルの内容を比較したり、特定のセルを探したり、セル内容の大小を判断したり、と、様々な能力を持っています。こういった様々な能力の多くは、「関数」という機能で実現できます。

　今回は、そんな「関数」の中でも、マッチング（特定の項目を探し出す）と、指定要素参照（指定位置にある要素を持ってくる）について、ご紹介しましょう。

　本日のメニューはこちらでございます！

　たろいものオススメメニューはMATCHとINDEXです。

マッチングして指定要素を持ってくる「VLOOKUP」

　「マッチングして指定要素を持ってくる」って言われても、何をどうするの？　となることでしょう。

　たとえば、こんな一覧表があったとして、

　「商品No：S1200の商品の名前は？」と聞かれれば、人間なら表から「あ、塩飴」って分かります。それをExcel上で自動的にやろうと思うと、

1. 商品No：S1200　に対応する行を探す（マッチング）
2. 探した行の商品の名前が記載されている列の情報を表示する（指定要素参照）

という、２つのステップが必要となります。「VLOOKUP」は、この「マッチング」と「指定要素参照」の２ステップを一気に行ってくれます。

　たとえばこんな感じです。

　と、これだけだと分かりづらいので、関数の解説を。

　= VLOOKUP(検索値, 範囲, 列番号, [検索方法])

　「[検索方法]」項目は、"[]"で囲われていますが、これは「任意項目」です。指定してもいいし、しなくても既定の設定で処理されます。

　この検索方法ですが、「完全一致」と「近似一致」を選べます。既定では「完全一致」です。「近似一致」だと読んで字のごとく、「似通った値」でもひっかけることができるのですが、「範囲」で選ぶ表にも少々ルールがあるため、ここでは割愛します。

一覧から一致する項目の位置を探査する「MATCH」

　「VLOOKUP」が持つ２要素、「マッチング」と「指定要素参照」の内、「マッチング」だけを行ってくれる関数です。

　たとえばこんな感じです。

　この関数は、下のような指定方法です。

　= MATCH(検査値, 検査範囲, [照合の種類])

　MATCH関数は、「検査範囲」を検査して、「検査値」がある場所を教えてくれる関数なので、ある場所を示す番号が返ってきます。上の例だと３番目に同じ物があるので「３」ですね。

　「[照合の種類]」項目は"[]"で囲われており、「任意項目」です。が、指定しない場合の既定は「1：検査値以下の最大を探す」という少々面倒な設定です。普通に使用する場合には「0：完全一致」が分かりやすいので、必ず「0」を指定しましょう。

表から指定した行と列にある要素を持ってくる「INDEX」

　「VLOOKUP」が持つ２要素、「マッチング」と「指定要素参照」の内、「指定要素参照」だけを行ってくれる関数です。

　たとえばこんな感じです。

　この関数は、下のような指定方法です。

　=INDEX(配列, 行番号, [列番号])

　INDEX関数は、「配列」で指定した領域の、「行番号」「列番号」が示す位置にある要素を表示する関数です。上の例だと、４行目の３列目である「B社」という要素が表示されています。

　「[列番号]」が任意項目になっていますが、それは下みたいに「配列」で指定する先を１列だけにして、「行番号」だけで要素を参照するという使い方もできるためです。

マッチングして指定要素を持ってくる「INDEX+MATCH」

　お待たせしました。ここまでの項目は「前菜」で、ここからが「メインディッシュ」です。マッチングして指定要素を持ってくるVLOOKUP関数に対し、マッチングだけのMATCH関数と指定要素を持ってくるだけのINDEX関数。このINDEX関数とMATCH関数を組み合わせることで、VLOOKUP関数と同じことができます。

　たとえばこんな感じです。

　中身はこんな感じになってます。

　MATCH関数で調べた行数を、INDEX関数で使用するという構成です。

　「VLOOKUP関数１つでできるなら、その方がいいでしょ」という声が聞こえてきそうです。ですが、INDEX+MATCHには、VLOOKUPには無いメリットがあります。いやむしろ、VLOOKUPにはいくつかの欠点があると表現したほうが良いでしょうか。

1.VLOOKUP関数は「範囲」で指定する表の融通性が低い

　VLOOKUP関数の「範囲」指定する表には「一番左側に検査値を探すための要素が必要」という縛りがあります。このため、表示したい値がある列は、必ず検査値を探す列より右側に無くてはならないという制約が発生してしまいます。関数で利用するだけの表ならば、列を並べ替えてしまえばいいかもしれませんが、他の使用者とも共有している表などで、おいそれと列の並び順を変えられない場合にはVLOOKUPを使えないことになります。

2.VLOOKUP関数は処理速度が遅い

　VLOOKUP関数とINDEX関数+MATCH関数を比較した場合、複数の面で処理速度に差が出ます。まず、大前提として、VLOOKUP関数は、INDEX関数+MATCH関数の場合に比べて遅いです。ただし、数十件程度の探索では差を感じられるほどではないと思います。最低でも探査先が数千件レベルになった場合の話ですが。

　さらに、VLOOKUP関数は１つの関数で「マッチング」と「指定要素参照」の両方を行ってしまうため、複数の要素を参照したい場合に、余分な処理が行われることになります。

• VLOOKUP関数の場合

• INDEX関数+MATCH関数の場合

　INDEX関数+MATCH関数の場合には、MATCH関数で調べた位置を複数要素で利用できるため、MATCH関数は１回使用するだけで良いことになりますが、VLOOKUP関数の場合は関数内部に処理が含まれてしまうため、参照したい要素全てで「マッチング」と「指定要素参照」を行ってしまいます。

　「そう言われても、わざわざINDEX関数とMATCH関数で２セルも使いたくない！」という方、こんな感じで１セルに書くこともできます。

　ちょっと複雑になりますけどね・・・・・・。

おしまい

　ここまでお読みいただいた方ならお分かりいただけるかと思いますが、たろいもはINDEX+MATCH"推し"です。というか「VLOOKUPなにそれ美味しいの？」レベルです。

　業務上でデータ整理を頻繁に行うのですが、項目増やしたり、途中処理を増やしたりで、列を指し込むことが頻繁になります。VLOOKUPはそういった表の変化にも弱い部分があります。なので、少々とっつきにくい部分もありますが、INDEXとMATCHの使い方に慣れるといろいろと便利です。

　INDEXやMATCHは更に応用的な使い方もできまして、例えば「不当定多数の人が行追加削除をやりたい放題してしまうシートをリンクしたい！」となった場合、普通のリンクでは行削除されたらリンク切れ起こします。が、INDEXとROWを用いることでリンク切れしないリンクを構築できます。MATCHは、検査範囲をOFFSETで指定することで、条件により探査先を変えるなんていう使い方もできます。

　はい、かなりニッチな使い方ですね。その辺を記事にする日がくるかどうか・・・・・・、たぶん無い気がします。