エクセルで参照する代表的な関数と実務で生かす方法

紬ちゃん

エクセルで参照する関数には種類があるって聞いたんですけど、どんな関数があるのか教えてください。

ライクさん

参照に関する関数はたくさんあるんだけど、ビジネスシーンで使われることが多いVLOOKUP関数、HLOOKUP関数、INDEX関数、MATCH関数、INDIRECT関数について解説するね。

エクセルで使用できる関数の種類は、400種類以上あると言われています。

参照に関する関数も複数あり、単体で使うだけではなく、他の関数と組み合わせることでより便利になるものもあります。

今回は、覚えておきたい参照に関する5つの関数と基本的な使い方を紹介しますので、ぜひマスターしておきましょう。

サンプルデータと画像を使って実務に生かせるよう形で紹介していますので、日常業務の効率アップにぜひ役立ててください。

関数以外のエクセルの参照の使い方は、以下の記事でも紹介しています。

エクセルの参照に関する代表的な関数の種類

エクセルの参照に関する代表的な関数は、以下の5つです。

関数の内容と使い方を解説していきます。

列方向に検索して値を取り出す『VLOOKUP関数』

VLOOKUP関数は、別の場所に作成した表を列方向に検索して、指定した列番号の値を返す関数です。

VLOOKUP関数の引数は、「検索値」、「範囲」、「列番号」、「検索の型」の4つです。

この画像の品名の列には、商品ナンバーを入力すると青枠の表と同じ番号の品名が表示されるように、以下のVLOOKUP関数が挿入されています。

=VLOOKUP(B18,$J$16:$K$21,2,0)

VLOOKUP関数の引数には、以下のものを入力します。

VLOOKUP関数は、あらかじめ作成した表のデータを参照して入力できるので、「商品管理」、「納品書」、「請求書」などの書類に使うと便利です。

行方向に検索して値を取り出す『HLOOKUP関数』

HLOOKUP関数は、別の場所に作成した表を行方向に検索して、指定した行番号の値を返す関数です。

HLOOKUP関数の使い方はVLOOKUP関数とほとんど同じですが、赤枠の表のように参照したい文字が行方向に入力されている表を使用する場合は、HLOOKUP関数を使います。

この画像の品名の列には、商品ナンバーを入力すると青枠の表と同じ番号の品名が表示されるように、以下のHLOOKUP関数が挿入されています。

=HLOOKUP(B18,$J$17:$N$18,2,0)

HLOOKUP関数の引数には、以下のものを入力します。

HLOOKUP関数も、VLOOKUP関数と同様にあらかじめ作成した表のデータを参照して入力できるので、「商品管理」、「納品書」、「請求書」などの書類に使うと便利です。

行番号と列番号を指定して値を取り出す『INDEX関数』

行番号と列番号を指定して、交わっている部分の値を取り出したいときはINDEX関数を使います。

簡単な例を使って、INDEX関数の使い方を確認してみましょう。

これは、商品ごとの月別の仕入価格をまとめた表です。

この中から、「3月のパパイヤの仕入価格を表示したい」といった場合にINDEX関数を使うと便利です。

「パパイヤ」は表の5行目、「3月」は3列にありますね。

これをINDEX関数の引数に当てはめてみましょう。

=INDEX(B5:E9,5,3)

「D12」に上記の関数を挿入すると、行と列が重なっている「310」が表示されました。

ただし、INDEX関数で条件に合う値を返すためには、行番号と列番号を目視で確認しなければなりません。

そのため、INDEX関数は単体で使うよりも、MATCH関数など他の関数を組み合わせたときに便利さを発揮する関数です。

詳しい手順は、INDEX関数とMATCH関数を組み合わせて条件に合うデータを一発抽出！解説します。

指定した値の位置を番号で表示する『MATCH関数』

MATCH関数は、表の中にある指定した値の位置を番号で返す関数です。

例えば、上記の表の中から「パパイヤ」の文字が何行目にあるかを調べたいときは、MATCH関数を使います。

行方向の文字を検索したいときは、赤枠の範囲を指定します。

=MATCH("パパイヤ",A5:A9,0)

と入力すると「5」という数字が返りました。

これは、「パパイヤ」は、表の中の5行目にあるという意味です。

同じようにして、3月が何列目にあるか調べてみましょう。

=MATCH("3月",B4:E4,0)

と入力すると「3」と表示され、「3月」は表の3列目にあるということがわかります。

MATCH関数は、検索する文字が表の何行目または何列目にあるかを数字で返す関数です。

そのため、MATCH関数も単体ではなくINDEX関数やVLOOKUP関数など、他の関数と一緒に使われることが多い関数です。

入力した値や数式を変えずに参照セルをずらす『INDIRECT関数』

INDIRECT関数は、入力した値や数式を変えずに参照セルをずらすことができる関数です。

INDIRECT関数は、他の関数とは異なり、引数に入力するセル番地のダブルクォーテーションの有無で返る値が変わるため、使い方には少し注意が必要です。

ライクさん

INDIRECT関数は、「=INDIRECT("A1")と「=INDIRECT(A1)」のように同じセル番地を入れても、ダブルクォーテーションの有無で返る値が変わるよ。

参照形式は省略することができるので、ここではわかりやすくするために参照形式を省略した形で解説していきます。

これは、「D1」にりんご、「A1」にINDIRECT関数を挿入したものです。

引数のセル番地をダブルクォーテーションで囲むと、指定したセル番地と同じ文字が返ります。

=INDIRECT("D1")

上記は、「A1」に挿入されている関数です。

INDIRECT関数の引数がダブルクォーテーションで囲まれているときは、指定したセル番地と同じという意味になります。

よって、この数式で返る値は、「D1」に入力されている「りんご」です。

今度は、引数のセル番地に何もつけずにINDIRECT関数を挿入してみましょう。

「D1」にりんご、「A1」にD1、「A5」にINDIRECT関数が挿入してあります。

「A5」には、下記のINDIRECT関数が挿入されています。

=INDIRECT(A1)

この数式だけを見ると、「A5」のセルの値は「A1」参照するという意味です。

しかし、「A1」に「D1」というセル番地が挿入されていますね。

このように、ダブルクォーテーションで囲まずにセル番地を指定し、さらに参照先のセルに別の場所を意味する文字が入力されていた場合は参照するセルが変わります。

このデータの場合は、「A1」ではなく「D1」を参照するという意味に変わるということです。

「=INDIRECT(A1)」はD1を参照するという意味になるので、「D1」に入力されている「りんご」が表示されます。

INDIRECT関数は少しわかりにくいと感じるかもしれませんが、入力した文字を変えずに参照先を変えることができるので、覚えておくと便利な関数です。

ビジネスに役立つエクセルの参照に関する関数の使い方

紬ちゃん

参照に使える関数の種類と使い方はわかったんですが、実際のデータでどうやって生かしたらいいかわからないので、具体的な使い方を教えてください。

ライクさん

実務で使えるサンプルデータを作成して、実際に関数を使ってみよう！

紹介した関数を実際のデータで活用するために、実務で使えるサンプルデータを使って解説していきます。

別の場所に作成した表を参照したいときはVLOOKUP関数が便利

「シート上の別の場所」や「別のシートやファイル」に作成した表を参照してデータを入力したいときは、VLOOKUP関数が便利です。

VLOOKUP関数を活用して、「事務用品の備品リスト」を作成してみましょう。

STEP

「事務用品備品リスト」のシートを作成して備品リストを作る

新しいシートに、上記のような「事務用品備品リスト」の表を作成します。

必要に応じて、「現在個数」、「使用数」、「残」、「最低個数」、「要発注」などの項目を作成しておくと在庫管理がしやすくなります。

使用数のセルには、以下のSUMIF関数を挿入してあります。
=SUMIF(事務用品使用表!$A$3:$A$19,"A0001",事務用品使用表!$E$3:$E$19)
「事務用品使用表」の管理IDと一致する持ち出し数の数字だけが合計されるようにしておくと、使用数が計算できるので参考にしてみてください。

STEP

「事務用品使用表」のシートで表を作成

別のシートに、「管理ID」、「品名」、「使用部署」、「持ち出し日」、「持ち出し数」などの見出しがある「事務用品使用表」を作成してください。

右側の表は、データを入力する際に使用します。

STEP1で作成した事務用品備品リストの「A列:B列」をコピーして貼り付けておきましょう。

右側に貼り付けた表と同じ管理IDを入力すると、品名が表示されるようになります。

STEP

VLOOKUP関数を検索

事務用品使用表のB列にVLOOKUP関数を挿入します。

• 「B3」をクリックして、「=v」と入力
• 表示された関数の候補の中から「VLOOKUP」をダブルクリックする

STEP

「検索値」を入力

• 「A3」をクリック
• 半角で「,」を入力

STEP

範囲を指定

1. 「事務用品備品リスト」のシートをクリック
2. 管理ID、品名が入力されているA列からB列の範囲を選択

STEP

表の範囲を絶対参照にして、列番号、検索の型を入力

• 事務用品備品リストのシートが選択されている状態で表の範囲に「$」をつけて絶対参照にする
• 「,2,0)」を入力したらEnterで確定

ライクさん

事務用品備品リストのシートが選択されている状態で範囲を絶対参照にすると、自動的に事務用品使用リストのシートが選択されるよ。

STEP

管理IDが入力されていないのでエラー表示になっている

この段階では上記のようなエラーの状態になりますが、管理IDを入力すると品名が表示されます。

ライクさん

エラーを表示させたくない場合は、下で紹介している「参考」を見てね！

STEP

VLOOKUP関数をコピーする

挿入した計算式をオートフィルでコピーしたら完成です。

管理IDを入力すると品名が表示されるようになります。

参考

管理IDが入力されていないときに品名のセルにエラーを表示させたくない場合は、IF関数と組み合わせておきましょう。

=IF(A3="","",VLOOKUP(A3,事務用品備品リスト!$A$2:$E$19,2,0))

挿入したVLOOKUP関数の前に「=IF(A3="","",」、関数の最後にIF関数を閉じる「)」を入力します。

上記のIF関数と組み合わせると、管理IDが入力されていないときは空白にできるため、エラーが表示されなくなります。

IF関数は数式をコピーする前に追記してください。

INDEX関数とMATCH関数を組み合わせて条件に合うデータを一発抽出！

INDEX関数は、指定した行番号と列番号が交差している値を返す関数です。

MATCH関数は、指定した値が表の何行目または何列目にあるかを数字で返す関数です。

この2つの関数を組み合わせて使うと、条件に合うデータを一発で抽出できるようになります。

これは、3月のパパイヤの仕入価格を調べるために、INDEX関数とMATCH関数をそれぞれ別の場所に挿入してあるデータです。

こちらの画像のように、INDEX関数とMATCH関数を組み合わせて、条件に合うデータを抽出してみましょう。

STEP

INDEX関数の範囲を入力

「D12」をクリックして、「=INDEX(B5:E9,」と入力します。

ライクさん

最後の半角の「,」を忘れずに入力しよう。

STEP

MATCH関数で「パパイヤ」の行番号を入力

MATCH関数を挿入して、「パパイヤ」の行番号を挿入します。

INDEX関数のあとに、「MATCH("パパイヤ",A5:A9,0),」と入力してください。

STEP

もうひとつMATCH関数を挿入して「3月」の列番号を入力

MATCH関数で「3月」の列番号を挿入します。

「MATCH("3月",B4:E4,0)」を入力したら、最後にINDEX関数を閉じる「)」を入力しましょう。

最終的に、INDEX関数とMATCH関数が以下のような形で挿入されます。

=INDEX(B5:E9,MATCH("パパイヤ",A5:A9,0),MATCH("3月",B4:E4,0))

STEP

Enterで関数を確定する

数式が正しく入力されていることを確認したら、Enterで数式を確定してください。

ライクさん

関数の間の「,」やINDEX関数を閉じる最後の「)」を忘れないように注意してね。

INDIRECT関数とVLOOKUP関数を組み合わせて参照先を変更

INDIRECT関数とVLOOKUP関数を組み合わせると、VLOOKUP関数を1つ挿入するだけで複数の表が参照できるようになります。

紬ちゃん

1つのVLOOKUP関数で複数の表が参照できるってどういう意味ですか？

ライクさん

言葉だけだとわかりにくいから、実際のデータを見てみよう！

「C2」のセルには、VLOOKUP関数とINDIRECT関数を組み合わせた以下のような数式が入力されています。

=VLOOKUP(B2,INDIRECT(A2),2,FALSE)

VLOOKUP関数の「範囲」を指定する部分に、INDIRECT関数が挿入されていますね。

「A2」を見ると「食料品」と入力されていますが、INDIRECT関数の引数が「A2」になっています。

紬ちゃん

INDIRECT関数の引数がダブルクォーテーションで囲まずにセル番地を指定しているから、セルに入力されている文字じゃなくて別の場所を参照するって意味に変わるってことですよね？

ライクさん

そのとおり！この場合は、「食料品」の文字は「食料品」の表を参照するという意味に変わるよ。

つまり、「A2」に入力する文字を変更すれば、1つのVLOOKUP関数で違う表の値が参照できるようになるということです。

実際にデータを作成して試してみましょう。

STEP

VLOOKUP関数を挿入する表と参照に使用する表をカテゴリー別に作成する

VLOOKUP関数を使って参照した内容を表示する表と、参照に使用する表をカテゴリーに分けて作成します。

STEP

カテゴリーに分けて作成した表に名前の定義を使って名前をつける

カテゴリーに分けた表に名前をつけます。

• 食料品の表の「A6:B9」の範囲を選択
• 名前ボックスに「食料品」と入力してEnterで確定

同様にして、「飲料品」、「日用品」の表の範囲にも名前をつけてください。

STEP

「C2」にVLOOKUP関数とINDIRECT関数を組み合わせた数式を挿入

「C2」に、VLOOKUP関数とINDIRECT関数を組み合わせた以下の数式を挿入します。

=VLOOKUP(B2,INDIRECT(A2),2,FALSE)

STEP

種類と品名を変えて正しい金額が表示されるか確認する

最後に、「種類」と「品名」を変更して、正しい金額が表示されるか確認してみてください。

複数の表を参照したいときは、VLOOKUP関数とINDIRECT関数を組み合わせておきましょう。

関数を組み合わせておくことで、1つのVLOOKUP関数で複数の表が参照できるようになります。

例えば、「年度ごとの仕入価格表のリスト」、「仕入れ先別の価格リスト」、「顧客別の商品売価リスト」、「カテゴリー別の部品リスト」などをこのような形で作成しておけば、単価の比較や経費の見直しなどに役立ちます。

しくみさえ理解してしまえば挿入する関数自体はそれほど難しくはないので、ぜひ挑戦してみてください。

エクセルの参照の関数に関するQ&A

VLOOKUP関数とINDEX関数+MATCH関数のどちらを使うのがいいですか？

関数は、作成した表からどの値を参照したいかによって適している関数の組み合わせが変わります。

例えば、番号が入力されている表を使って同じ番号の値を参照したい場合は、VLOOKUP関数が使えます。

しかし、指定した行番号と列番号が交差する値を取り出したいときは、INDEX関数とMATCH関数を組み合わせて使う方法が便利です。

別の場所に作成した表を参照したいときはVLOOKUP関数が便利とINDEX関数とMATCH関数を組み合わせて条件に合うデータを一発抽出！で、それぞれの使い方を解説していますので、こちらの内容を参考にしてみてください。

エクセルで関数を使って別のセルを参照するにはどうすればいいですか？

別のセルの値などを参照したいだけであれば、「=参照したいセル番地」という形で入力すると関数を使わなくても別のセルを参照することが可能です。

関数を使用する場合は、INDIRECT関数を使って「=INDIRECT("セル番地")」のように参照したいセル番地をダブルクォーテーションで囲むと、指定したセル番地と同じものを参照することができます。

INDIRECT関数とは何ですか？

INDIRECT関数は、入力されている値や数式を変えずに参照先が変えられる関数です。

本文で入力した値や数式を変えずに参照セルをずらす『INDIRECT関数』とINDIRECT関数とVLOOKUP関数を組み合わせて参照先を変更を紹介していますので、参考にしてみてください。

参照ができる関数を活用して作業効率をアップさせよう！

エクセルで参照できる関数は複数あります。

単体で使用できるものもありますが、ビジネスでは複数の関数を組み合わせて使うことが多いです。

例えば、参照に使用する代表的な関数としてVLOOKUP関数がありますが、検索値が入力されていないときにエラーを表示させない場合は、VLOOKUP関数とIF関数と組み合わせるといった使い方をします。

参照の関数の使い方だけではなく、今回紹介したように複数の関数を組み合わせて使う方法を覚えておくと、より効果的に関数を活用できるので試してみてください。

最後に、参照で使える代表的な関数をおさらいしておきましょう。

参照できる関数を挿入しておくと、データが追加されたときや修正されたときにセルの値を自動で修正することができます。

関数をうまく活用して、日常業務の効率アップに役立てましょう。

初心者におすすめなエクセルの「参照」について、詳しく解説している別の記事もあります。

こちらもぜひ参考にしてみてください。