【Excel】HLOOKUP関数+INDIRECT関数を使って任意のBOOKからデータを抽出する方法

こんにちは、AYLBlog を運営しているRyoです。

今回はHLOOKUP関数とINDIRECT関数を使って任意のBOOKからデータを抽出する方法について解説していきます。

なぜこの内容の記事を書こうと思ったかと言うと、自分の職場で任意のBOOKからデータを抽出するExcelシートを作成して欲しいと依頼を受けたのですが、インターネットで検索しても的確に、こうすれば出来る！と指示されているものがまったく見つからず、作成するのにかなり苦戦しました。

そのため、自分と同じように任意のBOOKからデータを抽出する方法を探している方のためになればと思い、今回記事を書こうと思いました。

ただし、自分はExcelについてめちゃくちゃ詳しいということではないので、もっと簡単なやり方があるかもしれません。

この記事は下記のようなことを知りたい方におすすめの記事になっています。

LOOKUP関数の基本情報

まず初めにLOOKUP関数の基本情報について解説していきます。

表から該当するデータを抽出する関数

LOOKUP関数とは指定した表の中から該当するデータを抽出する時に使用する関数になります。

そして、このLOOKUP関数を応用したものがVLOOKUP関数とHLOOKUP関数になります。

LOOKUP関数にはベクトル方式と配列方式の2つがあるのですが、配列方式に関してはVLOOKUP関数やHLOOKUP関数を使用した方が良いため、ここではベクトル方式での書式について解説していきます。

LOOKUP関数の基本的な使い方

ここでは果物1つ1つにコードが付いており、コードを入力するとそれに対応した果物が表示されるというものを例にして説明していきます。

下記画像が具体例に使う表になっています。

A列には各果物のコード、B列には対応する果物が入力されています。

セルD2はコード入力用セルとなっており、セルE2にLOOKUP関数を使った計算式が入力されています。

セルE2に入力されている計算式は下記の通りになります。

補足すると検査値が『D2』、検査範囲が『A2:A5』、対応範囲が『B2:B5』となっています。

この計算式を入力することでセルD2にコードを入力すると、対応する果物がセルE2に表示させることが出来ます。

例えば、セルD2に『102』と入力すればセルE2には『パイナップル』と表示されます。

VLOOKUP関数とHLOOKUP関数の違い

次にVLOOKUP関数とHLOOKUP関数の違いについて解説していきます。

VLOOKUP関数は列を指定し、HLOOKUP関数は行を指定する

LOOKUP関数を応用したものにVLOOKUP関数とHLOOKUP関数があります。

VLOOKUP関数とHLOOKUP関数はともに特定の表から検査する列や行を選択し、検査値が見つかった列や行から指定数離れたセルの値を抽出するという関数になります。

VLOOKUP関数では列を指定し、HLOOKUP関数では行を選択します。

ちなみにVLOOKUPのVはVertical（垂直）を意味しており、HLOOKUPのHはHorizon（水平、横）を意味しています。

VLOOKUP関数の書式は下記のようになっています。

HLOOKUP関数の書式は下記のようになっています。

VLOOKUP関数の基本的な使い方

先ほどのLOOKUP関数の時に使用した表に『月の売上数量』を追加して、コードを入力するとそれに対応する果物が表示され、さらに月の売上数量も表示されるというものを例に解説していきます。

下記画像が具体例に使う表になっています。

A列には各果物のコード、B列には対応する果物、C列には月の売上数量が入力されています。

セルE2はコード入力用セルとなっており、セルF2、G2にはVLOOKUP関数を使った計算式が入力されています。

セルF2に入力されている計算式は下記の通りになります。

補足すると検査値が『E2』、範囲が『A2:C5』、列番号が『2』、検索の型が『FALSE』となっています。

セルG2に入力されている計算式は下記の通りになります。

補足すると検査値が『E2』、範囲が『A2:C5』、列番号が『3』、検索の型が『FALSE』となっています。

セルF2とG2の違いは列番号が『2』になっているか『3』になっているかの違いになります。

このようにVLOOKUP関数を使用することで、検査値から指定数離れたところのデータを抽出することが出来ます。

HLOOKUP関数の基本的な使い方

先ほどのVLOOKUP関数の時に使用した表の『コード』、『商品』、『月の売上数量』をA列に表示したものを例に解説していきます。

下記画像が具体例に使う表になっています。

A列にはコード、商品、月の売上数量という項目名が入力されています。

B列～E列には上からコード、対応する果物、果物の月の売上数量が記入されています。

セルB6はコード入力用セルとなっており、セルB7、B8にはHLOOKUP関数を使った計算式が入力されています。

セルB7に入力されている計算式は下記の通りになります。

補足すると検査値が『B6』、範囲が『B1:E3』、行番号が『2』、検索の型が『FALSE』となっています。

セルB8に入力されている計算式は下記の通りになります。

補足すると検査値が『B6』、範囲が『B1:E3』、行番号が『3』、検索の型が『FALSE』となっています。

セルB7とB8の違いは行番号が『2』になっているか『3』になっているかの違いになります。

このようにHLOOKUP関数を使用することで、検査値から指定数離れたところのデータを抽出することが出来ます。

スポンサーリンク

INDIRECT関数の基本情報

次にINDIRECT関数の基本情報について解説していきます。

入力されたセル番地やセル範囲を参照先の形式に変換する

INDIRECT関数とは入力されたセル番地やセル範囲を参照先の形式に変換する関数になります。

今回のようにVLOOKUP関数やHLOOKUP関数と組み合わせることで別BOOKからデータの抽出を行うことが出来ます。

INDIRECT関数の書式は下記のようになっています。

注意点として、参照文字列で別BOOKを参照している場合はそのBOOKを開いておく必要があります。

INDIRECT関数の基本的な使い方

下記表にはINDIRECT関数を使った基本的な計算式が4つ入力されているのでそれを例に解説していきます。

A列にコード、C列にINDIRECT関数を使った計算式が入力されています。

それと別シート（シート名：HLOOKUP）のB6のセルには『101』の数字が入力されています。

セルC2に入力されている計算式は下記の通りになります。

この計算式では『A2』のセル番地を参照するという意味になっています。

セルC3に入力されている計算式は下記の通りになります。

この計算式では『A』と『セルF3に入力されている文字列』を合体させたセル番地を参照するという意味になっています。

今回はセルF3に『5』が入力されているため、『A5』のセル番地を参照し、『104』と表示されています。

セルC4に入力されている計算式は下記の通りになります。

この計算式ではシート名が『HLOOKUP』の『B6』のセル番地を参照するという意味になっています。

セルC5に入力されている計算式は下記の通りになります。

この計算式では『セルE5に入力されている文字列のシート』の『セルF5に入力されているセル番地』を参照するという意味になっています。

今回はセルE5に『HLOOKUP』、セルF5に『B6』が入力されているので、シート名がHLOOKUPのセルB6に入力されている『101』が表示されています。

このようにINDIRECT関数を使うことで文字列を参照形式に変更することが出来ます。

HLOOKUP関数とINDIRECT関数を使った任意のBOOKからのデータ抽出方法

次にHLOOKUP関数とINDIRECT関数を使った別BOOKからのデータ抽出方法について解説していきます。

HLOOKUP関数+INDIRECT関数で別BOOKからデータを抽出

今回は自分が実際に使っているHLOOKUP関数とINDIRECT関数を組み合わせて、別BOOKからデータを抽出する方法を解説していきます。

商品X、Y、Zを各工程A～Hにどれぐらい投入したかを記入してあるBOOKを3カ月分（4月～6月）と各工程の良品率を確認するBOOKを使って具体的に解説していきます。

商品X、Y、Zを各工程A～Hにどれぐらい投入したかを記入したBOOKが下記になります。

ちなみにBOOK名は『製品投入履歴_4月』となっており、シート名は『Sheet1』となっています。

この製品投入履歴を4月～6月分使っていきますが、BOOK名は後半の『4月』の部分のみが対象の月に変わっているだけになります。

各工程の良品率を確認するBOOKが下記になります。

良品率の計算式については、『次の工程の投入数/前の工程の投入数』となっており、表示形式をパーセントにしています。

ただし、合格品の良品率に関しては『合格品数/工程Aの投入数』になっています。

セルB2～セルD10にはすでに計算式が入力されているのですが、まずはデータ抽出を行うBOOKが固定の場合の計算式を入力しています。

代表としてセルB2に入力されている計算式について解説していきます。

補足すると検査値が『B1』、範囲が『[製品投入履歴_4月.xlsx]Sheet1!$B$1:$D$10』、行番号が『2』、検索の型が『FALSE』となっています。

セルB3であれば行番号を『2』から『3』に変えることで対象のデータを抽出することが出来ますし、セルC2であれば検査値を『B1』から『C1』に変えることで対象のデータを抽出することが出来ます。

任意のBOOKからデータを抽出する方法

次は先ほどの計算式を変更して、『製品投入履歴_4月』以外のBOOKからもデータの抽出を出来るようにしていきます。

下記表がデータ抽出先のBOOK名を任意に変更出来るように改良したものになります。

変更点としてはデータ抽出先のBOOK名を指定するために専用の入力セル（A13）を用意したことと計算式の修正です。

代表としてセルB2の修正後の計算式について解説していきます。

修正したところとしては青文字で表示している『範囲』の部分になります。

このようにINDIRECT関数を使用することでBOOK名をセル参照に変更しています。

これで特定のBOOKを指定するのではなく、自分で任意のBOOKを選択してデータの抽出を行うことが出来ます。

スポンサーリンク

シート名がSheet1ではない時の対処方法

次にデータ抽出先のシート名がSheet1ではない時の対処方法について解説していきます。

データ抽出先のシート名をセル参照に変更する

今まで解説してきたのはデータ抽出先のシート名がSheet1の場合でしたが、CSVファイルだとシート名がBOOK名と同じになったりします。

そうなってしまうと先ほどの計算式ではデータの抽出が出来なくなってしまいます。

こちらに関しての対処方法はデータ抽出先のシート名をセル参照に変更することです。

下記表がデータ抽出先のシート名を任意に選択出来るように改良したものになります。

変更点としてはデータ抽出先のシート名を指定するために専用の入力セル（E13）を用意したことと計算式の修正です。

代表としてセルB2の修正後の計算式について解説していきます。

修正したところとしては青文字で表示している『シート名』の部分になります。

ここが今までは『Sheet1』固定だったので、セル参照出来るようにしています。

ただし、データ抽出先がCSVファイルの場合は計算式の『.xlsx』の部分を『.csv』に変更して、シート名のセル参照先を『A13』にする必要があります。

セル参照先をA13に変更する理由としては前述したとおり、CSVファイルはBOOK名＝シート名になるからです。

これでシート名がSheet1以外でも問題なくデータ抽出を行うことが可能になりました。

検査する行が変化してしまう時の対処方法

最後にHLOOKUP関数を使用している時にデータ抽出先の検査する行が変化してしまう時の対処方法について解説していきます。

範囲選択の先頭行を任意に選択出来るようにする

HLOOKUP関数を使用してデータ抽出を行うのは良いのですが、何らかの原因でデータ抽出先の行番号が変化し、本来ならば『行番号1』に検査値があるのに、『行番号2』に検査値が表示されるようになってしまうとデータの抽出がうまく出来なくなってしまいます。

こちらに関しては自分の職場で実際にあった問題になります。

この問題の対処方法は範囲選択の先頭行を任意に選択出来るようにすることです。

ついでに範囲選択の最終行も任意に選択出来るようにしておくと便利です。

下記表が範囲選択の先頭行と最終行を任意に選択出来るように改良したものになります。

変更点としては範囲選択の先頭行と最終行を指定するために専用の入力セル（I13とK13）を用意したことと計算式の修正です。

代表としてセルB2の修正後の計算式について解説していきます。

修正したところとしては青文字で表示している『範囲選択の先頭行と最終行』の部分になります。

これで検査値の行番号が変わったとしても選択している範囲を任意で変更出来るようになったので、計算式を触らなくてもすぐに修正出来るようになりました。

まとめ

今回はHLOOKUP関数とINDIRECT関数を使って任意のBOOKからデータを抽出する方法について解説してきました。

HLOOKUP関数はデータの抽出をするのに非常に便利な関数ですが、範囲の先頭行に検査値がないと上手くデータ抽出が出来なかったりとデメリットもあります。

しかし、今回は任意のBOOKからデータを抽出する方法だけでなく、シート名が変わってしまった場合の対処方法や検査値の行番号がずれた時の対処方法などについても詳しく解説してきました。

この記事を理解することでHLOOKUP関数+INDIRECT関数でのデータ抽出に関してはある程度使いこなせるのではないかなと思います。

今回の記事は過去の自分への回答の意味やインプットした内容をアウトプットする意味も含まれています。

現在自分はExcel関数だけでなくマクロの勉強も行っています。

マクロの勉強には下記書籍を活用しているのですが、内容がかなり分かりやすいためこれからマクロを勉強しようと思っている方にはおすすめの書籍となっています。

自分は普段投資やお金の知識について記事を書いたりTwitterで発信をしています。

もしお金や投資について興味があるなら自分の投資成績についても見てもらいたいと思っています。

自分の投資成績やどういった銘柄に投資しているかについては『【高配当株式投資進捗】2022.3月の投資進捗とトータルリターンについて』の方で詳しく解説しています。

みなさんの投資の参考になれば幸いです。

Twitterでブログの更新や米国ETFの情報、自分の投資の状況などについて発信しているため、そちらのフォローもよろしくお願い致します！

最後までお読みいただきありがとうございました！