どうも！リョクちゃです。
今回は、Excelで困ったことについて解決策の紹介をしていこうと思います。
みなさんは、Excelで集計をするときに今あるデータ列に変更は加えず、
計算を行い指定の値と比較し結果を出力したい場合ってありませんか？
そんなときの方法を関数の説明しながら順を追って紹介していきます。
ちなみにExcelのバージョンは、2016以前のを対象にしています。
今回、使うExcel関数は下の通りです。
- INDEX関数 → 指定された行と列が交差する位置にある値またはセルの参照を返す
- MATCH関数 → 指定された照合の種類に従って
目次
INDEX関数
指定された行と列が交差する位置にある値またはセルの参照を返します。
とExcelで関数について説明がされています。
基本構文
使い方が2通りあり、それぞれ
① INDEX(配列、行番号、【列番号】)
※列番号は省略可能
② INDEX(参照、行番号、【列番号】、【領域番号】)
※列番号、領域番号は省略可能
①を配列形式、②をセル範囲形式とそれぞれ参照する形式が違います。
配列形式？
行番号と列番号のインデックスで選択された。
テーブルまたは配列内の要素の値を返します。
セル範囲形式?
指定された行と列が交差する位置にあるセルの参照を返します。
参照が隣接していない選択範囲で構成されている場合は、検索対象を選べます。
それぞれ、Excel上ではこのように説明がされています。
ではどんな時につかえるでしょうか。
たとえば、下のようなExcelがあるとします。
B列には分類、C列には産地、D列には名称そしてE列には価格がそれぞれ埋められています。
このExcelから青森のりんごの価格を取得したいとき、
私たちが手動で操作をしている場合は、”E3″と選択するだけで取得が簡単にできます。
下のような形になります。
E列の3行目の価格に破線が引いてあります。
このセルに値を”青森のりんごの価格”と記述された列に”=E3″と書くことで、
求めている価格が表示できます。
ではこれをINDEX関数を使って表記した場合どうなるか見ていきます。
先ほどの”青森のりんごの価格”が表示されている列に、
INDEXの基本構文①を参考に書いてみましょう。
配列には、A:Eを範囲として選択します。
これはA列とE列全てを選択しています。
行番号には、3行目を選択します。(青森のりんごなので)
列番号には、5列目を選択します。
これらをINDEX関数に埋めていくと、
と書くことができます。
実際にこれを実行すると、配列内の3行目と5列目で交差する値の取得ができます。
他にも、E3からE5までの合計を取得したい場合、図で表すと下のようなとき。
このようなときにもINDEX関数は力を発揮します。
＜書き方＞
= INDEX(SUM(E3:INDEX(A:E,7,5))
と書くことで、E3(250)からE5(170)までの値取得し、
SUM関数で足した合計値を取得することができます。
こういった使い方もできます。
MATCH関数
指定された照合の種類に従って検査範囲内を検索し、
検索値と一致する要素の、配列内での相対的な位置を表す数値を返します。
と説明がされています。
簡単に言うと、検索したい値と検索したい列がわかっていれば、それをMATCH関数に入れてあげることで、検索したい値が何行目にいるかを番号で教えてくれるといった関数です。
(※個人的解釈です)
基本構文
照合の種類は、
- 1 または省略 → 検査値に近い最大値の位置を返す
- 0 → 検査値との完全一致の場合に位置を返す
- －1 → 検査値に近い最小値の位置を返す(※降順で並べ替える必要あり。)
となっています。
では、先ほどの表をもとに今度はみかんが何行目にあるのか調べてみましょう。
私たちが見ているだけであれば、みかんは4行目にあるのがわかります。
これをMATCH関数を使ってやらせる場合はどうなるでしょうか？
検査値には、”みかん”
検査範囲には、みかんが表示されているD列(D:D)
照合の種類には、0(完全一致)
をそれぞれ埋め込んでいくと、
と書くことができます。
実行すると、みかんのある”4行目”が取得されます。
このようにして、MATCH関数を使うことができます。
本題：新たに列を作らず任意列の値を抽出
たとえば、下のような集計表があるとします。
これは4月と5月のそれぞれ項目1項目2の不良数を表したシートです。
不良数の隣にはその月のランクをそれぞれ示しています。
例えば4月のパソコン、キズについては、
不良数が10件で全体でみるとランクは1位になっています。
このシートの完成は、4月と5月の項目ごとの不良件数を比較し、前月と比べてどうなっているかをシートに出力するのが完成となります。
既に4月の項目ごとの不良件数におけるランクは求められているので、
後は、5月ランクと比較し、上昇しているか下降しているかを判定すればいいのですが、
条件としては、
- 新たに列を追加するのは禁止(計算用に列を作るのはダメ)
- 項目1と項目2の内容を連結して前月から一致するランクを取得する
例えば、5月の3行目のI列とJ列を連結して前月(4月)のランク取得してくる。 - 前月と比較し上昇(↑)か下降(↓)かをそれぞれ判別する。
といったのが条件として挙げられます。
新たに列を追加するのは禁止……
例えば新たに列を追加するのが禁止されていなければ、
以下のようにすることで問題は解決できると思います。
使用する関数＜VLOOKUP＞
使用方法：VLOOKUP(検索値、検索範囲、検索方法)
これを使えば、例えば、下の表のJ列の”冷えない”をキーとして
VLOOKUPを使うと、前月の”冷えない”のランクが引っ張ってくることができます。
ただしこれだと、項目2のみでしか検索ができません。
項目1と項目2を連結させて検索したい場合、下のように書いてもエラーとなってしまいます。
= VLOOKUP(I3&J3,C:C & F:F, 3, FALSE)
※ & は文字列同士の連結を表す記号です。
ここで最初に紹介した、INDEX関数とMATCH関数の登場です。
この二つの関数を組み合わせて、問題の解決に試みます。
INDEX関数とMATCH関数を組み合わせる
INDEX関数では、前月のランクを取得することを目的に、
MATCH関数では、5月の項目1と2が4月の項目1と2で一致する行を見つけることを目的
とそれぞれすることで関数を書いていくと、以下の手順の通りで行えると思います。
- MATCH関数でI3とJ3を連結した項目が、C列とD列のどの行に含まれるか表記する。
- INDEX関数で、取得したいランクの列(F列)と1で求めた値を行番号にあてる。
この流れが関数に記述できれば、前月のランクを引っ張ってくることができるはずです。
実際に流れをもとに関数を書いていきましょう。
1. MATCH関数で検査値が、どの行に含まれるか表記する
これは、このようにして書くことができます。
ここでは検査範囲の項目もそれぞれ連結させています。
これを実行すると、”冷蔵庫冷えない”に一致した行の6行目が値として返されます。
実際に前月の”冷蔵庫冷えない”を見てみると、6行目に表記されています。
2. INDEX関数で、取得したい列と1.で求めた値を行にあてる
これは、このようにして書くことができます。
では、これを実行してみましょう。
……#Value……？と表示がされてしまいます。
なぜでしょうか……？
関数の設定を見てみると値は取得できているはずなのに……。
うーん……順を追って細かく見てみると、どうやら問題は、
項目1と項目2の連結させていく方法で起きていそうです。
ここからは個人的解釈に基づく見解になります。
文字列の連結がすべての関数の処理を終わってからしているからかと推測。
また、I3&J3では一つのセル同士を指定して検索値を決めていますが、
検査範囲では、C列とD列の複数の文字連結を行ったのを範囲として決めている。
おそらくこの2点が原因だろうと推測。
これを解決するに至った考えが、配列数式です。
配列数式？
配列を対象に、1つの数式を作成する式のことを呼んでいます。
これを行うことで、あらかじめ検査範囲に当てられた、
配列内の文字列連結の処理を行ってしまう数式を作ります。
作り方は簡単で、作成した式の前後に{}を付与してあげるだけです。
※ {}これはただ、入力するだけでは効力が発揮されません。
この状態で、キーボードの“Ctrl” + “Shift” + “Enter”を同時に押します。
実行すると、
となるかと思います。
そして、#Valueではなく、結果が正しく表示されました。
これでなんとか目的達成です。
それぞれ実行した結果になります。
まとめ
| 項目 | 書き方 | 内容 |
|---|---|---|
| INDEX関数 | INDEX(配列、行番号、【列番号】) | 指定した行と列が交差する値を取得 |
| MATCH関数 | MATCH(検査値、検査範囲、照合の種類) | 検査範囲にある検査値を照合の種類に基づいて結果を取得 |
| 配列数式 | {計算式} | Ctrl + Shift + Enterそれぞれのキーを同時押し(計算式セル編集中に) |
