【Excel:INDEX関数・MATCH関数】組み合わせるとVLOOKUP関数より柔軟性UP! | どこでもパソコン教室 四日市

【Excel:INDEX関数・MATCH関数】組み合わせるとVLOOKUP関数より柔軟性UP!

【Excel:INDEX関数・MATCH関数】 💻PCの"😔困った"を"😄安心・楽しい"へ!

こんにちはどこでもパソコン教室 四日市です。

今回の授業は、INDEXインデックス関数MATCHマッチ関数を組み合わせて、VLOOKUPブイルックアップ関数より柔軟性の高い使い方を解説します!
大量のデータを高速に処理したい場合や、データ表の項目変更が必要になる
場合に効果的です。

仕事でVLOOKUP関数を使用していますけれども、情報量が多くなるにつれてデータ処理に時間がかかるようになりました。。

情報の項目(列数)が増えるたびに数式を修正しなくてはならないのも手間です。。

何か良い解決策はありませんか

解決策から申し上げますと、INDEX関数MATCH関数を使いましょう

VLOOKUP関数をご理解されているからこそ、効率的で柔軟性に富んでいるデータ処理ができることにお気づきいただけます。

実は、INDEX関数MATCH関数を使ったことがありません……。

INDEX関数MATCH関数は一つずつでみますとVLOOKUP関数より難易度は低いので、関数の意味を理解することで使いこなせるようになれますよ

新しい関数を学べて、VLOOKUP関数より柔軟性が向上するとは〝一石二鳥〟ですね

はいそれでは今回の授業は次の4項目を解説します。

  1. VLOOKUP関数のおさらい
  2. INDEX関数の解説
  3. MATCH関数の解説
  4. VLOOKUP関数 VS INDEX関数 MATCH関数の利便性と柔軟性の比較
  5. INDEX関数MATCH関数を組み合わせた使い方

意味をしっかり理解しながら学習することで、身に付いて忘れにくくなりますので、どうぞ最後までご受講ください

VLOOKUP関数が初めての方や、経験者の方の
思い出し作業
におススメの授業です!
広告

VLOOKUP関数のおさらい

VLOOKUPブイルックアップ関数について、実際の例をご覧いただきながら解説します。

下図は、都道府県別の年ごとのコンビニ数を表にしたものです。

【Excel:INDEX関数・MATCH関数】

※統計ダッシュボード (https://dashboard.e-stat.go.jp/)のデータを加工して作成


47都道府県の中からお住いの地域のコンビニ数を知りたいとき、別表都道府県名だけ入力すれば一行分のデータをすぐに取り出せるのがVLOOKUP関数です

【Excel:INDEX関数・MATCH関数】

VLOOKUP「V」は、Verticalバーティカル垂直の頭文字です。
 LOOKUPは、直訳すると「調べる」です。
同じ項目が垂直に並んでいるデータ用の表を利用するのが特徴です。


数式を見てみましょう。

【Excel:INDEX関数・MATCH関数】

VLOOKUPG4,A3:E49,,0)
VLOOKUP G4 A3:E49 2 0
関数名 検索値 範囲 列番号 検索方法
使用する関数の名前 データ表の1列目の項目の中から抽出したい情報を指定するためのセル
(今回は「都道府県名」を入力するセル)
データの範囲 抽出したい情報がデータ表の何列目にあるか
(今回は「セルに表示したい年の列番号」)
完全一致*は「0」or「FALSE」(フォールス)
近似値**は「1」or「TRUE」(トゥルー)
*完全一致とは…「検索値」と全く同じ値を検索します。
(※今回の場合「検索値」は都道府県名で、〝約北海道〟という値は無いので😅『完全一致』にします)
**近似値とは…「検索値」に近い値も含めて検索します。
(※例えば「検索値」が「100」でも、データには「90」の次は「120」しか無い場合、最も近い「90」の値を返します)
<都道府県名をリストにして効率的に入力しましょう!>
47都道府県を手入力するのはとても大変です💦
そこで、下図のようにリストにしますと、🖱クリックするだけで入力できますので、楽ですし誤入力もふせげます
 
【Excel:INDEX関数・MATCH関数】
リストを作成する詳しい手順はこちらの授業にて
解説していますので、ぜひご受講ください!

INDEX関数の解説

INDEXインデックス関数とは、データ範囲行番号列番号を指定すると、その交差するセルの値を返します。

ちなみに、「INDEX」を直訳しますと“索引”です。

1つのデータ表を使用する「配列形式」と、2つ以上のデータ表を使用する「セル範囲形式」があります。

配列形式:INDEX(配列,行番号,列番号)

INDEX関数配列形式は、データ範囲(使用する表)1つの場合に使用します。

例えば、下図のような「かけ算の九九」の表ですと、👀目視したときの「3の段」の「5行目」=『15』を、関数で見つけてくれます

数式を見てみましょう。
行番号列番号は、今回のように用意したセルに入力してもいいですし、直接番号の数字を入力してもOKです。

【Excel:INDEX関数・MATCH関数】

INDEXC4:F12,C17,D17
INDEX C4:F12 C17(※数字でもOK) D17(※数字でもOK)
関数名 配列 行番号 列番号
使用する関数の名前 データの範囲 抽出したい情報がデータ表の何行目にあるか
抽出したい情報がデータ表の何列目にあるか

セル範囲形式:INDEX(参照,行番号,列番号,領域番号)

INDEX関数セル範囲形式は、データ範囲(使用する表)複数の場合に使用します。

例えば、下図のような「かけ算の九九」の表ですと、同じ「5行目2列目」でも、左右のどちらのデータ範囲を使用するかを選択できます。

数式を見てみましょう。
行番号列番号領域番号は、今回のように用意したセルに入力してもいいですし、直接番号の数字を入力してもOKです。

【Excel:INDEX関数・MATCH関数】

INDEX(C4:F12,J4:M12),E17,F17,G17
INDEX (C4:F12,J4:M12) E17 F17 G17
関数名 参照 行番号 列番号 領域番号
使用する関数の名前 C4:F12領域番号1のデータの範囲、J4:M12領域番号2のデータ範囲 抽出したい情報がデータ表の何行目にあるか
抽出したい情報がデータ表の何列目にあるか どのデータ表を使用するか

MATCH関数の解説

MATCHマッチ関数とは、検索したいデータがデータ範囲内のどの位置にあるのかが分かります。

データ範囲一列・・選択した場合は行番号一行・・選択した場合は列番号を返します。

ちなみに、「MATCH」を直訳しますと“一致”です。

行番号を検索:検索範囲は“一列”を選択

データ範囲一列・・選択した場合は行番号を返します。
複数列選択するとエラー(#N/A)になりますので注意しましょう。

数式を見てみましょう。
検索値は、今回のように用意したセルに入力してもいいですし、直接(数値や文字列)を入力してもOKです。

【Excel:INDEX関数・MATCH関数】

MATCHD13,E4:E8,0)
MATCH D13 E4:E8 0
関数名 検査値 検査範囲 照合の種類
使用する関数の名前 データ表の何行目にあるか知りたい情報のセル番地 位置を知りたい情報のセルがあるデータ表内の一列 完全一致は「0」
検査値以下の最大値は「1」
検査値以上の最小値は「-1」

列番号を検索:検索範囲は“一行”を選択

データ範囲一行・・選択した場合は列番号を返します。
複数行選択するとエラー(#N/A)になりますので注意しましょう。

数式を見てみましょう。
検索値は、今回のように用意したセルに入力してもいいですし、直接、(数値や文字列)を入力してもOKです。

【Excel:INDEX関数・MATCH関数】

MATCHD13,B6:H6,0)
MATCH D13 B6:H6 0
関数名 検査値 検査範囲 照合の種類
使用する関数の名前 データ表の何列目にあるか知りたい情報のセル番地 位置を知りたい情報のセルがあるデータ表内の一行 完全一致は「0」
検査値以下の最大値は「1」
検査値以上の最小値は「-1」

 

【PR】🍵ちょっと一息🍡
👩‍🏫パソコンインストラクターが選ぶ〝パソコンライフお役立ちグッズ〟
仕事効率化の教科書「できる人のパソコン仕事術 なんと効率10倍!」
• 私もよく拝見していますYouTuber「パソコン博士 TAIKI」さんの書籍
• 下記の内容を豊富な図版とわかりやすい解説で、ビジネスの必須スキルが確実に身に付く!
■アプリ起動編 ■ファイル操作編 ■ブラウザー編 ■Excel編 ■Word編 ■AI活用編

VLOOKUP関数 VS INDEX関数 & MATCH関数の利便性と柔軟性の比較

VLOOKUP関数の特徴の一つ、データ表内検索値(検査値)の位置必ず一番左の列でなければなりません。

けれども、INDEX関数MATCH関数の組み合わせで一番左でなくてもOK!となります。

【Excel:INDEX関数・MATCH関数】


さらにもう一つ

下図のようにデータ表列項目を追加したとき……

【Excel:INDEX関数・MATCH関数】

VLOOKUP関数ですと列番号を手入力で修正が必要ですけれども、INDEX関数MATCH関数の組み合わせは列番号の修正が不要となります。

【Excel:INDEX関数・MATCH関数】

INDEX関数とMATCH関数を組み合わせた数式の作成手順

INDEX関数MATCH関数を組み合わせた数式の作成手順を解説します

ちなみに、今回のように関数の中に関数を入れることを「ネスティング」=通称〝ネスト〟といいます。

上項目『VLOOKUP関数のおさらい』でも使用しました、都道府県別の年ごとのコンビニ数の表を使用します。

セル番地など、ご自身の使用される表に照らし合わせて実践してみましょう

【Excel:INDEX関数・MATCH関数】

※統計ダッシュボード (https://dashboard.e-stat.go.jp/)のデータを加工して作成

  1. セルH4🖱クリックして、アクティブセル*にします。
    *アクティブセルとは…「セル」とはたくさんあるマス目のことで、「アクティブセル」とはセルの中でも入力できる状態のセルをいいます。
     
    【Excel:INDEX関数・MATCH関数】


  2. 最初に、INDEX関数を挿入しますので、次の箇所を順に操作します。
     
    【Excel:INDEX関数・MATCH関数】

    1.[関数の挿入]ボタンを🖱クリックします。
    2.「関数の挿入」ダイアログボックスが表示されますので、関数の検索「index」と入力します。
    3.[検索開始]ボタンを🖱クリックします。
    4. 関数名に表示されました「INDEX」🖱クリックします。
    5.[OK]ボタンを🖱クリックします。

  3. INDEX関数「関数の引数」ダイアログボックスが表示されますので、次の箇所を順に操作します。
     
    【Excel:INDEX関数・MATCH関数】

    1. 配列🖱クリックして|」カーソルを入れます。
    2. データ🖱ドラッグして範囲選択します。
    左上から右下に🖱ドラッグするのが〝コツ〟です)
    3. 数式を後でコピーするときに、このデータ範囲がズレてしまわないようにF4キーを押して絶対参照にします。
    $」ドルマークが、左側に付いている行と列の参照が固定されます)

  4. 行番号を取得するために、INDEX関数MATCH関数をネストしますので、次の箇所を順に操作します。
     
    【Excel:INDEX関数・MATCH関数】

    1. 行番号🖱クリックして|」カーソルを入れます。
    2.「名前ボックス」[▼]🖱クリック [その他の関数]🖱クリックします。

  5. 「関数の挿入」ダイアログボックスが表示されますので、次の箇所を順に操作します。
     
    【Excel:INDEX関数・MATCH関数】

    1. 関数の検索「ma」と入力します。
    2.[検索開始]ボタンを🖱クリックします。
    3. 関数名に表示されました「MATCH」🖱クリックします。
    4.[OK]ボタンを🖱クリックします。

  6. MATCH関数「関数の引数」ダイアログボックスが表示されますので、次の箇所を順に操作します。
     
    【Excel:INDEX関数・MATCH関数】

    1. 検査値🖱クリックして|」カーソルを入れます。
    2. 検査値となるセル(今回は情報を知りたい都道府県名を入力するセルA3)を🖱クリックします。
    3. 数式を後でコピーするときに、このセル位置がズレてしまわないようにF4キーを押して絶対参照にします。
    4. 検査範囲🖱クリックして|」カーソルを入れます。
    5. 都道府県名の範囲🖱ドラッグして範囲選択します。
    6. 数式を後でコピーするときに、この都道府県名の範囲がズレてしまわないようにF4キーを押して絶対参照にします。
    7. 照合の種類「0」を入力します。

  7. 列番号を取得するために、INDEX関数MATCH関数をネストしますので、次の箇所を順に操作します。
    【Excel:INDEX関数・MATCH関数】

    1. 数式バー「INDEX」🖱クリックします。
    2. INDEX関数「関数の引数」ダイアログボックスが表示されますので、列番号🖱クリックして|」カーソルを入れます。
    3. 名前ボックス「MATCH」🖱クリックで選択します。

  8. MATCH関数「関数の引数」ダイアログボックスが表示されますので、次の箇所を順に操作します。
    【Excel:INDEX関数・MATCH関数】

    1. 検査値🖱クリックして|」カーソルを入れます。
    2. 検査値となるセル(今回は現在数式を入力しているセルH4の上「1982年」のセルH3)を🖱クリックします。
    3. 検査範囲🖱クリックして|」カーソルを入れます。
    4. 年の範囲🖱ドラッグして範囲選択します。
    5. 数式を後でコピーするときに、この年の範囲がズレてしまわないようにF4キーを押して絶対参照にします。
    6. 照合の種類「0」を入力します。
    7.[OK]🖱クリックします。

  9. 数式が出来あがりました
     
    【Excel:INDEX関数・MATCH関数】

    INDEX($B$3:$E$49,MATCH($G$4,$A$3:$A$49,0),MATCH(H3,$B$2:$E$2,0))
    INDEX $B$3:$E$49 MATCH($G$4,$A$3:$A$49,0) MATCH(H3,$B$2:$E$2,0)
    関数名 配列 行番号 列番号
    使用する関数の名前 データの範囲 抽出したい情報がデータ表の何行目にあるか
    抽出したい情報がデータ表の何列目にあるか

    すべてのセルに数式をコピーするために、次の手順で操作しましょう。
     
    【Excel:INDEX関数・MATCH関数】

    1.」フィルハンドルを右側に、最後のセルまで🖱ドラッグします。
    2. 最初のセルの書式を引きずってしまいます。
    3. オートフィル オプション「書式なしコピー(フィル)」🖱クリックして選択します。
    4. 書式を引きずることなく、きれいに数式をコピーできます。
広告

まとめ

今回の授業は、INDEXインデックス関数MATCHマッチ関数を組み合わせて、VLOOKUPブイルックアップ関数より柔軟性の高い使い方を解説しました!
大量のデータを高速に処理したい場合や、データ表の項目変更が必要になる場合に効果的です。

ちょっと難易度高めですので、式をコピー&ペーストしてご自身のデータ範囲に置き換えてご利用いただいてもかまいません。

けれども、それぞれの関数の意味を理解されると使いやすくなりますので、ぜひ何度も学習していただければと思います

😄ご受講お疲れさまでした

コメント

タイトルとURLをコピーしました