こんにちは。
先日、「Excelで2つの表から1つの表にデータを抽出する方法(VLOOKUP、IFERROR、IF関数を使用）」という記事を書きました。

こちらの方法は知っている関数を使用して、2つの表を1つにする方法をお伝えしました。
3つ以上もできなくはないのですが、式が複雑になってしまい、間違えてしまう可能性があるので、3つ以上のテーブルがある場合はおすすめしませんでした。

今回お伝えする方法は、表が3つ以上あった場合でもVLOOKUP関数を使用し、抽出する方法をお伝えします。
例題では中間試験や期末試験を使用しています。
学校の先生達必見のExcelテクニックです。

今回使用する関数はタイトルにも記載していますが、VLOOKUP関数とINDIRECT関数を使用し、複数のテーブル（表）からデータを抽出します。

 

INDIRECT関数とは…

RIGHT関数やMID関数などと比べると関数の認知度としては低いのではないかと思います。
私自身もINDIRECT関数単体で使用することはありません。
今回、VLOOKUP関数とみ合わせて使用しますが、とても便利な関数であると知ることができると思います。

まず、INDIRECT関数の使い方ですが、下記はMicrosoftのヘルプです。

指定される文字列への参照を返します。 セル参照はすぐに計算され、結果としてセルの内容が表示されます。 INDIRECT 関数を使うと、数式自体を変更しないで、数式内で使用しているセル参照を変更することができます。

INDIRECT関数は実際使用してみるとよりわかりやすいかと思います。

INDIRECT関数で必要な情報は「参照文字列」と「参照形式」の2つになります。
※参照形式は省略可能です。

例えばA1のセルに「10」というデータがあったとします。
セルB1で=INDIRECT(“A1”)と入力しますと、「10」を抽出することができます。
この時点ではINDIRECT関数の便利さはわからないと思います。
むしろ使う必要があるのか？「=A1」でいいのでは？と思う人もいるかと思います。
しかし、INDIRECT関数はセル指定ではできない優れた能力がありますので、それをお伝えします。

INDIRECT関数で必要となる「参照文字列」で使用できるデータの参照方法は3種類あります。

• A1 形式、R1C1 形式の参照
• 参照として定義されている名前が入力されているセルへの参照
• 文字列としてのセルへの参照

今回の方法では２番目の「参照として定義されている名前が入力されているセルへの参照」を使用します。

それではINDIRECT関数とVLOOKUP関数を使用した抽出方法をお伝えいたします。

下図はとある生徒の1学期中間テストから3学期の期末試験までの結果を作成しました。

点数は、ご覧いただくとわかるかと思いますが、点数の末尾が1学期中間は「2」1学期の期末は「4」となっています。
また、国語は10点台、数学は20点台としています。

それでは緑のテーブル（Q3:S8）に1学期中間から3学期の期末の国語の点数を抽出します。

 

手順①：各テーブルに名前の定義をする

名前の定義って何？
と思う人もいるかもしれませんが、表や特定の範囲選択などをした時に、そのテーブルが何のテーブルなのか定義をすることができます。
（テーブル１などの名前が付いています）

今回の方法ではとても重要な手順となります。

名前の定義のつけ方は下記の通りです。
①1学期中間のテーブルを教科と点数を範囲指定します。
②「テーブル１」となっている箇所の名前を「中間１学期」に変更します。

１学期中間にあるテーブルを選択し、テーブル名称が変更されていることを確認します。

「1学期中間」という名前に定義したいところですが、名前の定義にはルールがあり、１文字目に数字を入れることができません。
そのため名前の定義「１学期中間」とせず、「中間１学期」としています。

このあとは下図のように１学期期末試験から３学期期末試験にそれぞれ名前の定義をします。

 

手順②：VLOOKUP関数とINDIRECT関数を組み合わせてデータを抽出する。

各試験のテーブルの名前の定義が終わりましたら、抽出する先のテーブル（緑色）に試験と教科にデータを入力しておきます。
試験の項目には手順①で定義した名前を入力します。
教科に関しましては、国語を指定していますが数学でも英語でも９教科から選択していただければOKです。

最後に点数の項目ですが、点数を抽出するには下記の数式を入力します。

=VLOOKUP(R4,INDIRECT(Q4),2,FALSE)

※数式はコピーできます。セルの位置などは調整してご利用ください。

式を実行すると下図のようになります。

それぞれの試験結果から点数を抽出していることがわかります。

それぞれの試験から国語の得点を抽出できていますが、INDIRECT関数がとても重要な働きをしています。
次はINDIRECT関数が行っていることをお伝えします。

 

INDIRECTは何をやっている？

VLOOKUP関数で必要な値は「検索値」、「範囲」、「列番号」、「検索方法」の4つです。

検索値には教科の点数が知りたいので、知りたい教科の値が入ります。

次に範囲ですが、ここでINDIRECTを使用しています。
Q4のセルにある「中間1学期」のテーブルを指定しています。

B4：F20などセルの範囲を直接指定しますが、INDIRECTを使用しますと、直接テーブル名を指定することができます。
そして、試験名称を切り替えることで取得したいデータを抽出することができます。

VLOOKUPの列名には、どの試験も得点の項目は2列目にありますので、「２」を指定します。

最後の検索方法はFALSE＝完全一致を指定します。

VLOOKUPとINDIRECTを使用することで複数の表からデータを抽出することが簡単にできます。
注意事項としては、抽出したいデータの列番号などが同じでないと、違う情報を抽出しますので、注意が必要です。
年や月など別々に管理されているものだとこの方法は使用しやすいと思います。

 

おわりに

いかがでしたでしょうか。
INDIRECT関数を知らなかった人も便利な関数であるということがわかったのではないでしょうか。
INDIRECT関数も大切ですが、もう一つテーブルに名前の定義をしてあげることも大切です。

「INDIRECT関数」と「名前の定義」はセットで覚えておくと良いです。

【PR】相模原市産業会館パソコン教室

相模原市産業会館パソコン教室では「アットホーム」な雰囲気でパソコンを楽しく分かるまで学ぶことができます。