こんにちは!どこでもパソコン教室 四日市です。
今回の授業は、INDEX関数とMATCH関数を組み合わせて、VLOOKUP関数より柔軟性の高い使い方を解説します!
大量のデータを高速に処理したい場合や、データ表の項目変更が必要になる場合に効果的です。
![](https://i0.wp.com/www.dokodemo-pc.com/wp-content/uploads/2024/06/jukousei2_2-200px.jpg?resize=200%2C200&ssl=1)
仕事でVLOOKUP関数を使用していますけれども、情報量が多くなるにつれてデータ処理に時間がかかるようになりました。。
情報の項目(列数)が増えるたびに数式を修正しなくてはならないのも手間です。。
何か良い解決策はありませんか?
![](https://i0.wp.com/www.dokodemo-pc.com/wp-content/uploads/2020/07/mika-200px.jpg?resize=200%2C200&ssl=1)
解決策から申し上げますと、INDEX関数とMATCH関数を使いましょう!
VLOOKUP関数をご理解されているからこそ、効率的で柔軟性に富んでいるデータ処理ができることにお気づきいただけます。
![](https://i0.wp.com/www.dokodemo-pc.com/wp-content/uploads/2024/06/jukousei2_2-200px.jpg?resize=200%2C200&ssl=1)
実は、INDEX関数とMATCH関数を使ったことがありません……。
![](https://i0.wp.com/www.dokodemo-pc.com/wp-content/uploads/2020/07/mika-200px.jpg?resize=200%2C200&ssl=1)
INDEX関数とMATCH関数は一つずつでみますとVLOOKUP関数より難易度は低いので、関数の意味を理解することで使いこなせるようになれますよ♪
![](https://i0.wp.com/www.dokodemo-pc.com/wp-content/uploads/2024/06/jukousei2_1-200px.jpg?resize=200%2C200&ssl=1)
新しい関数を学べて、VLOOKUP関数より柔軟性が向上するとは〝一石二鳥〟ですね♪
![](https://i0.wp.com/www.dokodemo-pc.com/wp-content/uploads/2020/07/mika-200px.jpg?resize=200%2C200&ssl=1)
はい!それでは今回の授業は次の4項目を解説します。
- VLOOKUP関数のおさらい
- INDEX関数の解説
- MATCH関数の解説
- VLOOKUP関数 VS INDEX関数 & MATCH関数の利便性と柔軟性の比較
- INDEX関数とMATCH関数を組み合わせた使い方
意味をしっかり理解しながら学習することで、身に付いて忘れにくくなりますので、どうぞ最後までご受講ください!
思い出し作業におススメの授業です!
VLOOKUP関数のおさらい
VLOOKUP関数について、実際の例をご覧いただきながら解説します。
下図は、都道府県別の年ごとのコンビニ数を表にしたものです。
※統計ダッシュボード (https://dashboard.e-stat.go.jp/)のデータを加工して作成
47都道府県の中からお住いの地域のコンビニ数を知りたいとき、別表に都道府県名だけ入力すれば一行分のデータをすぐに取り出せるのがVLOOKUP関数です!
• VLOOKUPの「V」は、“Vertical”= 垂直の頭文字です。
• LOOKUPは、直訳すると「調べる」です。
同じ項目が垂直に並んでいるデータ用の表を利用するのが特徴です。
数式を見てみましょう。
=VLOOKUP(G4,A3:E49,2,0) | ||||
---|---|---|---|---|
VLOOKUP | G4 | A3:E49 | 2 | 0 |
関数名 | 検索値 | 範囲 | 列番号 | 検索方法 |
使用する関数の名前 | データ表の1列目の項目の中から抽出したい情報を指定するためのセル (今回は「都道府県名」を入力するセル) |
データの範囲 | 抽出したい情報がデータ表の何列目にあるか (今回は「セルに表示したい年の列番号」) |
完全一致*は「0」or「FALSE」(フォールス) 近似値**は「1」or「TRUE」(トゥルー) |
(※今回の場合「検索値」は都道府県名で、〝約北海道〟という値は無いので😅『完全一致』にします)
**近似値とは…「検索値」に近い値も含めて検索します。
(※例えば「検索値」が「100」でも、データには「90」の次は「120」しか無い場合、最も近い「90」の値を返します)
47都道府県を手入力するのはとても大変です💦
そこで、下図のようにリストにしますと、🖱クリックするだけで入力できますので、楽ですし誤入力もふせげます。
![【Excel:INDEX関数・MATCH関数】](https://i0.wp.com/www.dokodemo-pc.com/wp-content/uploads/2024/07/kansu-index-match4.webp?resize=600%2C250&ssl=1)
解説していますので、ぜひご受講ください!
INDEX関数の解説
INDEX関数とは、データ範囲の行番号と列番号を指定すると、その交差するセルの値を返します。
ちなみに、「INDEX」を直訳しますと“索引”です。
1つのデータ表を使用する「配列形式」と、2つ以上のデータ表を使用する「セル範囲形式」があります。
配列形式:INDEX(配列,行番号,列番号)
INDEX関数の配列形式は、データ範囲(使用する表)が1つの場合に使用します。
例えば、下図のような「かけ算の九九」の表ですと、👀目視したときの「3の段」の「5行目」=『15』を、関数で見つけてくれます。
数式を見てみましょう。
行番号と列番号は、今回のように用意したセルに入力してもいいですし、直接番号の数字を入力してもOKです。
=INDEX(C4:F12,C17,D17) | ||||
---|---|---|---|---|
INDEX | C4:F12 | C17(※数字でもOK) | D17(※数字でもOK) | |
関数名 | 配列 | 行番号 | 列番号 | |
使用する関数の名前 | データの範囲 | 抽出したい情報がデータ表の何行目にあるか |
抽出したい情報がデータ表の何列目にあるか |
セル範囲形式:INDEX(参照,行番号,列番号,領域番号)
INDEX関数のセル範囲形式は、データ範囲(使用する表)が複数の場合に使用します。
例えば、下図のような「かけ算の九九」の表ですと、同じ「5行目2列目」でも、左右のどちらのデータ範囲を使用するかを選択できます。
数式を見てみましょう。
行番号と列番号と領域番号は、今回のように用意したセルに入力してもいいですし、直接番号の数字を入力してもOKです。
=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です。
=MATCH(D13,E4:E8,0) | ||||
---|---|---|---|---|
MATCH | D13 | E4:E8 | 0 | |
関数名 | 検査値 | 検査範囲 | 照合の種類 | |
使用する関数の名前 | データ表の何行目にあるか知りたい情報のセル番地 | 位置を知りたい情報のセルがあるデータ表内の一列 | 完全一致は「0」 検査値以下の最大値は「1」 検査値以上の最小値は「-1」 |
列番号を検索:検索範囲は“一行”を選択
データ範囲を一行選択した場合は列番号を返します。
複数行選択するとエラー(#N/A)になりますので注意しましょう。
数式を見てみましょう。
検索値は、今回のように用意したセルに入力してもいいですし、直接、値(数値や文字列)を入力してもOKです。
=MATCH(D13,B6:H6,0) | ||||
---|---|---|---|---|
MATCH | D13 | B6:H6 | 0 | |
関数名 | 検査値 | 検査範囲 | 照合の種類 | |
使用する関数の名前 | データ表の何列目にあるか知りたい情報のセル番地 | 位置を知りたい情報のセルがあるデータ表内の一行 | 完全一致は「0」 検査値以下の最大値は「1」 検査値以上の最小値は「-1」 |
👩🏫パソコンインストラクターが選ぶ!〝パソコンライフお役立ちグッズ〟
仕事効率化の教科書「できる人のパソコン仕事術 なんと効率10倍!」
• 私もよく拝見していますYouTuber「パソコン博士 TAIKI」さんの書籍
• 下記の内容を豊富な図版とわかりやすい解説で、ビジネスの必須スキルが確実に身に付く!
VLOOKUP関数 VS INDEX関数 & MATCH関数の利便性と柔軟性の比較
VLOOKUP関数の特徴の一つ、データ表内の検索値(検査値)の位置が必ず一番左の列でなければなりません。
けれども、INDEX関数 & MATCH関数の組み合わせで一番左でなくてもOK!となります。
さらにもう一つ!
下図のようにデータ表の列項目を追加したとき……
VLOOKUP関数ですと列番号を手入力で修正が必要ですけれども、INDEX関数 & MATCH関数の組み合わせは列番号の修正が不要!となります。
INDEX関数とMATCH関数を組み合わせた数式の作成手順
INDEX関数とMATCH関数を組み合わせた数式の作成手順を解説します!
ちなみに、今回のように関数の中に関数を入れることを「ネスティング」=通称〝ネスト〟といいます。
上項目『VLOOKUP関数のおさらい』でも使用しました、都道府県別の年ごとのコンビニ数の表を使用します。
セル番地など、ご自身の使用される表に照らし合わせて実践してみましょう!
※統計ダッシュボード (https://dashboard.e-stat.go.jp/)のデータを加工して作成
- セルH4を🖱クリックして、アクティブセル*にします。
*アクティブセルとは…「セル」とはたくさんあるマス目のことで、「アクティブセル」とはセルの中でも入力できる状態のセルをいいます。
- 最初に、INDEX関数を挿入しますので、次の箇所を順に操作します。
1.[関数の挿入]ボタンを🖱クリックします。
2.「関数の挿入」ダイアログボックスが表示されますので、関数の検索に「index」と入力します。
3.[検索開始]ボタンを🖱クリックします。
4. 関数名に表示されました「INDEX」を🖱クリックします。
5.[OK]ボタンを🖱クリックします。
- INDEX関数の「関数の引数」ダイアログボックスが表示されますので、次の箇所を順に操作します。
1. 配列に🖱クリックして「|」カーソルを入れます。
2. データを🖱ドラッグして範囲選択します。
(左上から右下に🖱ドラッグするのが〝コツ〟です)
3. 数式を後でコピーするときに、このデータ範囲がズレてしまわないように、F4キーを押して絶対参照にします。
(「$」ドルマークが、左側に付いている行と列の参照が固定されます)
- 行番号を取得するために、INDEX関数にMATCH関数をネストしますので、次の箇所を順に操作します。
1. 行番号に🖱クリックして「|」カーソルを入れます。
2.「名前ボックス」の[▼]を🖱クリック ➡ [その他の関数]を🖱クリックします。
- 「関数の挿入」ダイアログボックスが表示されますので、次の箇所を順に操作します。
1. 関数の検索に「ma」と入力します。
2.[検索開始]ボタンを🖱クリックします。
3. 関数名に表示されました「MATCH」を🖱クリックします。
4.[OK]ボタンを🖱クリックします。
- MATCH関数の「関数の引数」ダイアログボックスが表示されますので、次の箇所を順に操作します。
1. 検査値に🖱クリックして「|」カーソルを入れます。
2. 検査値となるセル(今回は情報を知りたい都道府県名を入力するセルA3)を🖱クリックします。
3. 数式を後でコピーするときに、このセル位置がズレてしまわないように、F4キーを押して絶対参照にします。
4. 検査範囲に🖱クリックして「|」カーソルを入れます。
5. 都道府県名の範囲を🖱ドラッグして範囲選択します。
6. 数式を後でコピーするときに、この都道府県名の範囲がズレてしまわないように、F4キーを押して絶対参照にします。
7. 照合の種類に「0」を入力します。
- 列番号を取得するために、INDEX関数にMATCH関数をネストしますので、次の箇所を順に操作します。
1. 数式バーの「INDEX」を🖱クリックします。
2. INDEX関数の「関数の引数」ダイアログボックスが表示されますので、列番号に🖱クリックして「|」カーソルを入れます。
3. 名前ボックスで「MATCH」を🖱クリックで選択します。
- MATCH関数の「関数の引数」ダイアログボックスが表示されますので、次の箇所を順に操作します。
1. 検査値に🖱クリックして「|」カーソルを入れます。
2. 検査値となるセル(今回は現在数式を入力しているセルH4の上「1982年」のセルH3)を🖱クリックします。
3. 検査範囲に🖱クリックして「|」カーソルを入れます。
4. 年の範囲を🖱ドラッグして範囲選択します。
5. 数式を後でコピーするときに、この年の範囲がズレてしまわないように、F4キーを押して絶対参照にします。
6. 照合の種類に「0」を入力します。
7.[OK]を🖱クリックします。
- 数式が出来あがりました!
=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) 関数名 配列 行番号 列番号 使用する関数の名前 データの範囲 抽出したい情報がデータ表の何行目にあるか
抽出したい情報がデータ表の何列目にあるか すべてのセルに数式をコピーするために、次の手順で操作しましょう。
1.「■」フィルハンドルを右側に、最後のセルまで🖱ドラッグします。
2. 最初のセルの書式を引きずってしまいます。
3. オートフィル オプションで「書式なしコピー(フィル)」を🖱クリックして選択します。
4. 書式を引きずることなく、きれいに数式をコピーできます。
まとめ
今回の授業は、INDEX関数とMATCH関数を組み合わせて、VLOOKUP関数より柔軟性の高い使い方を解説しました!
大量のデータを高速に処理したい場合や、データ表の項目変更が必要になる場合に効果的です。
ちょっと難易度高めですので、数式をコピー&ペーストしてご自身のデータ範囲に置き換えてご利用いただいてもかまいません。
けれども、それぞれの関数の意味を理解されると使いやすくなりますので、ぜひ何度も学習していただければと思います♪
文字列を抽出する関数もオススメです!
😄ご受講お疲れさまでした!
コメント