Excel の行列関数と配列数式
ここ数日、会社の仕事で Excel のシートを作成しています。今さらながら MATCH 関数や INDEX 関数、配列数式の使い方を少し覚えました。これらの関数や配列数式の使い方はマイクロソフト社のページに書かれています。この記事の内容はこれらの実例です。
ワークシート関数一覧 (機能別) - Excel - Office.com
MATCH 関数 - Excel - Office.com
INDEX 関数 - Excel - Office.com
配列数式のガイドラインと例 - Excel - Office.com
MATCH 関数は、検査範囲の中で検査値に一致する項目を探し、その相対位置を返すものです。検査値との一致を調べるだけならこれでよいのですが、もう少し一般に、検査範囲の中で条件に合致する項目を探したいことがあります。
たとえば「日経平均株価について前日比の上げ幅が最大となった日付を求める」とかです。もちろん、これは仕事とは関係のない例題です。
株価データは Yahoo ファイナンスあたりで拾えます。デイリーの四本値が表になっていますので、これをエクセルにコピペすればよいです。デフォルトでは 20 日分しか表示されませんが表示範囲を選択すれば過去のデータも表示できるようです。
日経平均株価【998407】：国内指数 - Yahoo!ファイナンス
Excel に貼り付けるとこんな風になりました。2013 年 1 月以降のデータを取得して、Excel の機能で日付順にソートしたところの図です。1 月 4 日から 4 月 11 日まで全部で 67 行のデータで、最終行は 68 行目になっています。
ちなみに、これを範囲選択してそのままグラフにするとローソク足チャートを描くことができます。こんな感じ。
それではこのデータから、前日比が最大になる日付を計算します。まずは MATCH 関数をシンプルに使って求めてみます。F3 セルに =E3-E2 と数式を入力して前日比を計算させ、これを最終行までコピペします。前日比の最大値は MAX(F3:F68) なので、=MATCH(MAX(F3:F68),F3:F68,0) という数式で前日比が最大になる項目の位置が求まります。下図では H3 セルに数式を入力しました。I3 セルでは、この値 (22) を使って該当の日付を求めています。この計算の数式は =INDEX(A3:A68,H3) です。
配列数式を使うと、この計算を F 列を使わずに実現できます。H3 セルに =MATCH(MAX(E3:E68-E2:E67),E3:E68-E2:E67,0) と数式を入力して Ctrl+Shift+Enter で決定します。F 列を使う計算式と同じように 22 が表示されます。この数式の E3:E68-E2:E67 の部分が配列数式ですね。このことを確認するため、G 列の G3:G68 セルを選択して数式 =E3:E68-E2:E67 を入力します。配列数式なので Ctrl+Shift+Enter で決定します。すると F3:F68 セルと同じ値が表示されました。
