エクセルを使用して仕事をするためには、あらゆる関数を覚えたり、ツールを覚えたりする必要があります。
とはいっても、ひとつひとつ覚えようとしても、実際に使うシーンがイメージできなければなかなかインプットすることは困難です。
この記事では、エクセルのテーブル内にある項目を、関数を用いて別のセル内に参照させる方法を解説。
主に使うのは、
- INDEX関数
- MATCH関数
の2つの関数です。
INDEX関数やMATCH関数のそれぞれの違いと使用するための書式、複合してどのようなことができるのかについて、主な使い方と応用を使うシーンをイメージしながら覚えていきましょう。
INDEX関数とは
INDEX関数はテーブルデータなどから指定した値、またはその値を参照する関数です。
また、INDEX関数は2つの使い方がありますから使いやすい方を選びましょう。
配列形式と書式
指定したセルやセルの配列に対する値を求める場合は【配列形式】を使用します。
配列形式とは、行と列の数値によって選んだテーブルやセルの値を返す形式です。
INDEX関数の1つめの引数が配列定数で表示されている場合は配列形式を使用します。
その場合の書式は以下のようになります。
| INDEX(配列,行数値,[列数値]) |
書式には3種類の引数を使用します。
引数1:配列
INDEX関数を使用するうえで入力必須の引数で、セルの範囲や配列の定数を指定します。記入しなければ関数を使用することができません。
引数2:行番号
行数値を求める場合は入力必須です。
配列のなかにあり、値を返す行を行の値によって指定します。
列数値のみを求める場合は省略可能です。
引数3:列数値
列数値によって値を返す場所を指定する方法です。
列数値も行番号おなじく省略することも可能です。
セル範囲形式
セル範囲形式は、指定した行と列が交差する部分にあるセルを求めます。
参照が隣接しておらず離れて作成されている場合には、参照する範囲指定することで数値を求められます。
その場合の書式は以下のようになります。
| INDEX(参照,行番号,[列番号],領域番号]) |
それぞれの引数の解説をします。
参照
1つ、もしくは複数のセルの参照を指定する際に使用するため入力は必須です。
行番号
範囲内のセルを求める場合に、返すセルの行を行番号を用いて指定するため、入力は必須です。
列番号
列番号は任意になります。セル参照を返すセルの位置を列番号を用いて数値で返す際に使用します。
領域番号
領域番号は任意になります。
INDEX関数の基本の使い方
では商品名が未記入なので、INDEX関数を使って表示させていきましょう。
まずは3つの引数を使った入力方法です。
D11のセルには以下のような関数を入力しましょう。
| =INDEX(C4:C7,1,1) |
この関数には、C4からC7のセルのなかから1行目、さらに1列目の数値を返す意味があります。
関数を入力したら【OK】を押しましょう。
INDEX関数によってカスミソウが表示されました。
ではつぎに4つの引数を使った方法を解説します。
その場合の関数は以下のようになります。
| =INDEX(B4:E7,2,2,1) |
まずはB4からC7のセルを範囲指定します。
つづいてバラは範囲指定したセルの2行目、2列目にあるため【2,2,】と入力します。
領域番号は【1】を入力します。
問題なく入力できたら【OK】を押しましょう。
バラが表示されました。
同じ要領でカーネーションも表示させました。
これで3つの引数を使用したINDEX関数の入力方法と、4つの引数を使用したINDEX関数の2種類が使えるようになりました。
関数の挿入を使ってINDEX関数を使う方法
エクセルには手入力で関数を作成していく方法と、関数の挿入を使用した関数の作成方法との2種類があります。
ここでは、関数の挿入を使ったINDEX関数の入力方法について解説します。
関数の意味や、引数の文言、引数の役割をまだ完全にマスターできていない方には、手入力よりもわかりやすく関数を作成することができるので、まずは関数の挿入を使ったやり方で実践していきましょう。
先ほど使用したテーブルデータを使って解説します。
つぎは価格の部分が未記入となっているので、INDEX関数を使って価格部分を求めてみましょう。
関数の挿入部分をクリックします。すると引数の選択ダイアログボックスが開き、
- 配列,行番号,列番号
- 参照,行番号,列番号,領域番号
のどちらを使用するのかを問われます。
どちらでも問題なく作成できますが、領域番号などは特に不要なので、今回は前者の配列,行番号,列番号を使ってINDEX関数を作成します。
配列,行番号,列番号を選んだら【OK】をクリックしましょう。
関数の引数ダイアログボックスが開いたら、上から順に、配列、行番号、列番号を記入していきます。
カスミソウの価格を参照したいセルはD4からD7であるため、D4からD7のセルをマウスでドラッグし選択します。
選択したあと、【F4】キーを押すことで【$】が付き、【絶対参照】となります。
絶対参照をしておくことで、参照範囲をずらすことなく参照できるようになるので、F4キーを押すクセを付けておくと良いでしょう。
つぎに行番号を入力します。
D4からD7の範囲指定したセルのなかに、カスミソウの価格は一行目に記入されています。
そのためこの部分は【1】を入力します。
最後に列番号です。
この部分も、範囲指定した部分は価格のみのセルであるため【1】を記入します。
このような引数が作成できたら【OK】を押しましょう。
関数が無事入力され、カスミソウの価格部分に【550】が表示されました。
逆に、今度は参照,行番号,列番号,領域番号の方を使ってみましょう。
参照部分にはB4セルからE7セルまでを範囲指定します。
その後、さきほどと同じように【F4】キーを押して【絶対参照】へと切り替えます。
つぎに行番号を入力します。
カスミソウの価格はB4からE7までの範囲のなかで1行目にあるため【1】を入力します。
つづいて列番号を入力します。
カスミソウはB4からE7までの範囲のなかで価格は3列目にあるため【3】を入力します。
最後に領域番号です。
領域番号は最初に選択、もしくは入力された領域の領域番号の場合は1、2番目に入力された領域である場合は2となるため、ここでは【1】を押します。
このようにINDEX関数の引数が入力できたら【OK】を押します。
もうひとつの方法を用いても、カーネーションの価格を表示させることができました。
MATCH関数
MATCH関数はセル範囲指定した部分から見て、指定した値が列内の何番目にあるかを検索し、数値を返すための関数を指します。
その場合の書式は以下のようになります。
| MATCH(検査値,検査範囲,[照合の型]) |
それぞれの引数の解説をします。
検査値
検査範囲の中で照合をするための値を指定する際に使用します。入力は必須になります。
検査範囲
検査範囲は検索するセルの範囲を指定する際に使用します。入力は必須になります。
照合の型
照合の型は【-1】【0】【1】のいずれかを記入し指定しましょう。照合の型はなくても問題ありません。
MATCH関数の基本的な使い方
ではMATCH関数の基本的な使い方について解説します。
カスミソウは何行目にあるのか、価格は何列目にあるのかを、MATCH関数を使ってH2、H3セルにそれぞれ表示させます。
まずはカスミソウが何行目にあるかを求めます。その際の関数は以下のようになります。
| =MATCH(G2,C4:C7,0) |
入力し終えたら【Enter】を押します。
MATCH関数を使ってカーネーションが1行目にあることを求めました。
つづいて価格は何列目に記載されているのかを求めます。
この場合の関数は以下の通りです。
| =MATCH(G3,B3:E3,0) |
価格が何列目に記載されているかを表示することができました。
関数の挿入を使ってMATCH関数を使う方法
つづいて関数の挿入を使った方法を解説します。
関数の挿入をクリックします。
すると関数の挿入ダイアログボックスが開くので【MAT】と入力し【MATCH】を検索しましょう。
MATCH関数を見つけたら【OK】を押し、関数の引数をそれぞれ入力していきます。
最初はカスミソウが何行目にあるのかを求めるので、G2の検索値を入力します。
つづいて検索範囲をマウスで選択します。カスミソウはC4からC7のセル内にあるのでマウスでC4からC7の範囲を選択します。
最後に照合の種類を記入します。ここでは【0】を記入します。
このような引数が完成したら【OK】を押しましょう。
カスミソウは1行目にあることが求められました。
では同じように価格は何列目に表示されているのかを求めます。
同じように関数の挿入をクリックし、関数の検索を行います。先ほどMATCH関数を使ったばかりなので下部に表示されていますから、そのまま【MATCH】の部分を選択した状態で【OK】を押します。
検索値であるセル値を記入します。価格を検索したいので【G3】をクリック、または記入します。
つづいて検索したい範囲を指定します。商品コードから在庫数までをマウスで選択範囲します。
最後に照合の種類を【0】と記入します。
このような引数が完成したら【OK】をクリックしましょう。
価格が何列目に表示されているかも、関数の挿入を用いて求めることができました。
INDEX関数とMATCH関数を合わせてデータを検索・抽出する方法
あらかじめ作成されたエクセルにおいて、価格表内にあるデータを違うリストに表示させたいときがあります。
その際、いちいちデータをコピーしたり商品の詳細を検索して入力したりすることなく、INDEX関数とMATCH関数を複合してデータを返す方法があります。
この方法を用いることで入力ミスなどを防ぐことができ、また関数を覚えておくことでどんなエクセルデータにも対応させることができるため、入力や商品詳細をリサーチする時間を大幅にカットでき、効率よくエクセルデータを作成することが可能になります。
では実際にどのような流れになるかを画像を用いて解説します。
1つのエクセルに、商品の価格表と受注日を記入するリストがいくつかあるとします。
INDEX関数とMATCH関数を複合した関数を使い、C11の商品コードのセル内にカスミソウの商品コードを入力させます。
カスミソウの商品コードを表示させたいC11のセル内に、以下のような書式を記入します。
| =INDEX(B4:C7,MATCH(“カスミソウ”,C4:C7,0),1) |
書式の解説
まず【=INDEX(】と記入し、範囲をドラッグします。青い線で囲われているB4からC7のセルをマウスでドラッグし、入力します。(ここでは【B4:C7】と入力されています。)
つづいてINDEX関数の行番号をMATCH関数にそれぞれ書き換えましょう。
【MATCH】と入力したあとは検索値である【カスミソウ】を【””】で囲って入力し、【,】を入力します。
つづいて検索値の範囲をマウスでドラッグして指定させます。この場合は紫の枠で囲われた部分を指します。(入力する場合は【C4:C7】と入力)
称号の種類を選択したのち【),】を入力しましょう。
さらに、検索値と完全一致した数値を返したいので【0】を押します。
INDEX関数の列番号を記入したあとは【)】を押します。ここでは商品コードがセルの一列目にあるため【1】と入力し【Enter】を押します。
【カスミソウ】の商品コードが入力されました。
【オートフィル】を使っての自動入力は検索値が【カスミソウ】となっていたり、セルの値がズレてしまうために、使用することができません。
このように、INDEX関数とMATCH関数を組み合わせた関数を、オートフィルを使用してコピーすると以下のようにエラーが発生します。
これは検索対象のセルがズレてしまい、関数が乱れてしまったことによって起きてしまいます。
このようなエラーを防ぎ、オートフィルを使用して関数を入力したい場合は、2段階の工程を行う必要があります。
範囲を指定したら【F4】を押し【絶対参照】に指定する
【=INDEX(】のあとや【(“○○”,】のあとに、ドラッグして参照したい範囲を指定しますが、このときに【F4】を押してあげることで、参照範囲を【絶対参照】に設定し変更不可能にします。
(【F4】キーを押すとセル範囲の数値が【$B$4:$C$7】や【$C$4:$C$7】など【$】で囲まれるようになります)
そうすることで、オートフィルを使用して検索値にしても、参照したセルが一段ずつ下に下がって参照されることがなくなります。
検索値の名称を変更する
絶対参照を行いオートフィルを使用すると、
カスミソウの商品コードのみがそのままコピーされてしまっています。
これはカスミソウの商品コードをオートフィルしているために起きてしまうエラーです。
この場合は関数のなかの【カスミソウ】の部分を、それぞれ【バラ】や【カーネーション】などの商品名に変えるだけでそれぞれふさわしい商品コードが入力されるようになります。
同じようにカーネーションもオートフィル後、名称のみを変更し、無事カーネーションの商品コードを表示することができました。
まとめ
2種類の関数を用いると文字だけで見るととても複雑でイメージしにくかったかもしれません。
しかし、どんなエクセルのデータに使えるのかを画像を見ながら使い方を学ぶことで、オフィス内の作業のなかで使えるシーンがふっと浮かんだのではないでしょうか。
INDEX関数とMATCH関数を複合して数値を返すことで、入力ミスを防ぐだけでなく、さまざまな作業を効率よく進められますから、ぜひこの機会にマスターしておきましょう。
