戻る HOME

Excel の配列数式

　Excel には通常の関数・数式とはひと味違う「配列数式」と呼ばれる数式があります。これは、表の中の複数のセルを含む範囲をひとつのかたまり（これを配列といいます）として式の中で取り扱うものです。
　普通、複雑な計算をさせたいときには、if 関数でいくつも条件分岐させたり、たくさんの関数をごちゃごちゃ組み合わせたり、一時的に数値を格納するセル（テンポラリーセル／仮計算セル）を用意したりすることになります。しかし、配列関数を使うと、そんなことをせずにもっと簡単に済むことがあるんです。
　たとえば、下の表を見てください。

総合計

合宿中の食費の総額を計算するのに、普通の関数・数式を使うのであれば、各列ごとに小計を出し、その和を求めることになるはずです。しかし、配列数式を使えば、

　=SUM(B4:B6*C4:C6*D4:D6)

という式で済んでしまうのです!!

　B4:B6 と C4:C6 と D4:D6 というセル範囲（配列）を引数にしている点に注意してください。この数式では、それぞれの列の４行目から６行目をそれぞれ掛け合わせ、その和を求めています。配列数式では、引数に配列を使うので、普通の関数とは構文がやや異なってきます。また、式の入力後、確定するのに [Enter] キーだけを押しても配列数式にはなりません。[Ctrl] キーと [Shift] キーを押しながら [Enter] キーを押して確定してください。そうすると、この例でいえば数式バーに、

　{=SUM(B4:B6*C4:C6*D4:D6)}

というように、{　} で囲まれて表示されます。
　確定の仕方さえ覚えれば、配列数式で難しいのは配列というものの考え方・使い方になります。そこで、もう少し具体例を紹介しましょう。


　まずは、「２つ以上の条件に合うデータの個数をカウント」する方法です。通常データ数のカウントには COUNTIF 関数を使いますが、残念ながら COUNTIF 関数では、条件がひとつしか設定できません。これが配列数式なら一発でできます。下の表を見てください。

２つ以上の条件を満たすものの和

　ここで「１年生で、かつ準２級を受検する生徒」の数を求めたいとします。この時、配列数式を使って、

　=SUM(IF(A4:A10=1,IF(D4:D10="準２級",1)))

と入力し、[Ctrl] + [Shift] + [Enter] を押してください。これだけでＯＫです！ 　この式の意味は、「A 列が１（１年生）で、B 列が "準２級" である場合を１、それ以外を０として、その合計値を求める」ということになります。この式では少し省略してあるので、省略を補うと、

　=SUM(IF(A4:A10=1,IF(D4:D10="準２級",1,0),0))

ということになります。どちらの式でもＯＫです。
　また、上の式で２つ目の if 関数の第２引数を１にしていますが、ここをセル範囲にすれば、その範囲の値のうち条件に合うものの和が求められます。SUMIF 関数では条件が１つしか設定できませんが、配列数式を使えば、２つ以上の条件を満たすものの和も簡単に求められるのです。
　どうでしょうか？ちょっと難しいですが、便利ですよね！


　もうひとつ例を挙げましょう。今度は「ある条件を満たすデータの中での順位」を求める方法です。順位を求めるには RANK 関数を使いますが、これでは条件を付けられません。RANKIF のようなものがあればいいのですが、そのような関数はありません。そこで配列数式を使います。例として、学年全体の集計表の中で、クラス順位をつけたいとしましょう。

条件内での順位

　ここで、クラス順位の欄 D4 に、

　=SUM(IF($A$4:$A$12=A4,IF($C$4:$C$12>C4,1)))+1

と入力して、[Ctrl] + [Shift] + [Enter] を押します。そして、このセルを下にコピー＆ペーストすれば完成です。ここで、セル番地を絶対番地にしているのは、コピー＆ペーストするためです。
　順位というのは、自分より大きな（小さな）数値の数をかぞえて、それに１足せばいいはずです。そこで、この式では「A 列（クラス）が自分と同じで、かつ、C 列（点数）が自分より多きものを１、それ以外を０として、その合計値を求め、さらにその合計値に１を足す」ことによって条件付き順位を割り出しています。省略を補えば、

　=SUM(IF($A$4:$A$12=A4,IF($C$4:$C$12>C4,1,0),0))+1

となります。


　いかがでしたか？

　配列数式については、Excel のヘルプを見てもたいしたことは書かれていませんし、市販の参考図書でも詳しく書かれているものはなかなか無いと思います。試行錯誤しながら自分で考えて作っていくしかないようですが、非常に便利なので、研究の価値はあると思います。Ｍｒ．Ｂｉｇの『教務手帳』の中でも使用しています。このページではじめて知ったという方も、今後ぜひ活用してみてください。
　ただし、配列数式内で指定するセル範囲があまりに広いと、計算に時間がかかります。セル範囲が広い場合は配列数式を避けて、何段階かに分けて（テンポラリーセルを使って）計算させた方がいいでしょう。



戻る