■配列数式とは
エクセルの機能の中であまり知られていないものに、この配列数式があります。
配列数式とは、文字通り「配列」を数式の中に当てはめて結果を求める数式です。
配列は定数で指定することもできますし、セル範囲を指定することもできます。
配列数式を用いることで、本来ならば複数の作業セルを用いて求めなければならない計算結果を、一つの数式で求めることができます。
また、複数の条件に合致したデータの件数や合計値などを一つの数式で求めることも可能です。
早速、配列数式を利用した計算を見ていきましょう。
まず、このような表で配列数式を利用してみます。
ある月の社用車の燃料費の計算を行っています。
社用車はA車、B車、C車の3車でA車とB車はガソリン車です。C車はディーゼル車なので軽油を使用します。
一回で入れるリッター数や月に給油する回数もそれぞれ違います。
作業セルを用いて計算する。
作業セルを用いれば、それぞれの燃料費の小計を算出し、合計することでその月の燃料費の合計が求められます。
=SUM(E2:E4)
同じことを作業セルを用いずに算出してみましょう。
SUMPRODUCT関数を用いて計算する。
=SUMPRODUCT(B2:B4,C2:C4,D2:D4)
SUMPRODUCT関数は配列の積の合計を計算します。この場合、単価が入力されている「B2:B4」とリッター数が入力されている「C2:C4」、回数が入力されている「D2:D4」がそれぞれ配列になります。
つまり=SUMPRODUCT(単価*リッター数*回数)を各行で行い、その合計値を返しています。
答えは当然、各行の小計の合計値と等しくなります。
配列数式を用いて計算する。
=SUM(B2:B4*C2:C4*D2:D4)
まず、セルF2あたりに上記の数式を入力し、Enterを押してみてください。7500の数値が返ってきたことと思います。
なぜこうなるかというと、SUM関数が共通部分参照を行う関数だからです。
つまりこの場合、2行目のB2*C2*D2の計算を行い、その結果を返したからです。
試しに、この式をF3、F4と移動すると、各行の小計を表示するはずです。そしてF5に移動したとき、5行目には計算する数値がないため、#VALUE!のエラー値を返すはずです。
では、F5にまで移動させた後、F5セルを編集可能状態にします。（F2キー押下または数式バーをクリック）そして、Ctrl+Shift+Enterを押下して数式を確定してください。
{=SUM(B2:B4*C2:C4*D2:D4)}
このような表示で数式が確定されるはずです。そして、F5セルに燃料費の合計値が表示されているはずです。
これが配列数式です。
※ 配列数式を確定するときは、通常の数式の確定と異なりCtrl+Shift+Enter押下で確定する必要があります。Enterだけでは配列数式として確定されませんので注意してください。
このとき、B2:B4、C2:C4、D2:D4、はそれぞれ配列として数式の中にもたれます。つまり、
=SUM({125;125;105}*{30;25;35}*{2;2;1})
こうなっているわけです。あとはそれぞれの一次元配列の要素を順番に計算していきます。
つまり125*30*2と125*25*2と105*35*1を計算し、SUM関数で合計しています。
※ {125;125;105}のようなデータの塊を配列定数と呼びます。上記の説明で便宜上、一次元配列と呼びましたが、配列定数は基本的に矩形の二次元配列になります。上記の場合は3行1列の二次元配列です。配列定数の書式は、
={1,2,3;4,5,6;7,8,9}のように記述します。この中で、","は列の区切り、";"は行の区切りになります。この例の場合では、3行3列の矩形範囲に1〜9のデータが格納された二次元配列になります。
1 2 3
4 5 6
7 8 9 <=3行3列の矩形範囲にこのようにデータが格納されているイメージです
複数の条件に合致するデータの件数や合計を算出してみよう
おそらく配列数式が最も重宝されるのが、この複数条件のデータを扱う場合です。
このような表で説明します。
この表で例えば性別が男性の会員人数を算出したければ、
=COUNTIF(B2:B6,"男")
の数式で算出することができます。
また東京に住む会員が納めた会費の合計なら、
=SUMIF(C2:C6,"東京",D2:D6)
で求めることができます。
しかし、東京に住む男性会員の人数や、納めた会費の合計値は1つの数式で求めることができません。
（※ SUMPRODUCT関数を使用した論理積、論理和の数式を除く）
配列数式を使用すれば、このような場合でも1つの数式で算出することが可能です。
{=SUM((B2:B6="男")*(C2:C6="東京"))}
この配列数式では、性別を入力したB2:B6の配列で"男"のデータならTRUEを、住所を入力したC2:C6の配列で"東京"のデータならTRUEを返し、その積を求めています。
論理値のTRUEは数値の1でFALSEは0です。ですからTRUE*TRUEは数値計算の1*1になり、その結果は1になります。
つまり、会員の中で男性で東京在住の人のデータは1を返しているわけです。SUM関数でその1の数を合計しているので、結果は男性で東京在住の会員の人数を計算しているわけです。
ちなみにSUMIFで行った、男性会員の数だけを求める場合には次のようにします。
{=SUM((B2:B6="男")*1)}
ここで、1を掛けているのは、TRUE・FALSEの論理値が数値として扱われるためにはあくまで演算を行う必要があるためです。
何もしなければTRUE=1とは扱わず、TRUE=TRUEのまま無視してSUM関数は合計を計算してしまい、結果として0の値が返ります。
次に、男性で東京在住の会員が納めた会費を合計してみましょう。
{=SUM(IF((B2:B6="男")*(C2:C6="東京"),D2:D6,0))}
先ほどの例で男性で東京在住の会員、(B2:B6="男")*(C2:C6="東京")はTRUEを返すことがわかっています。
IF関数で、TRUEのときD2:D6の配列を使用し、FALSEのとき0を使用するよう定義していますので、男性で東京在住の会員ならば、納めた会費の金額がそのまま返ってきます。最後にSUM関数で金額を合計しているので、男性で東京在住の会員が納めた会費の合計額が算出されるというわけです。
前述の複数条件はAND条件でした。OR条件の場合はどうすればよいのでしょう？
人数を求める場合、
{=SUM(((B2:B6="男")+(C2:C6="東京")<>0)*1)}
会費の合計を求める場合、
{=SUM(IF((B2:B6="男")+(C2:C6="東京"),D2:D6,0))}
OR条件の場合は、論理値の和を求めます。TRUE+TRUEなら2、TRUE+FALSEなら1、FALSE+FALSEなら0がそれぞれ返ります。
会費の合計を求める場合は、IF関数を使用しているため、条件式の結果が0以外（FALSE+FALSE以外）ならそれぞれの会費を返します。つまり、東京在住でもなく男性でもない場合以外は全て会費を返すわけです。これはIF関数の場合、条件式が0（FALSE）以外ならすべてTRUEと判断するからです。1でも2でもTRUEと判定します。
人数を求める場合は工夫が必要です。
男性かつ東京在住の場合、TRUE+TRUEで2を返してしまうので、人数が二人とカウントされてしまいます。
そこで、<>0（0以外）の条件をつけ再度、TRUE・FALSEの判定をしています。最後に1を掛けているのは、先ほど述べたとおり、そのままだとTRUEはTRUEとして扱われ、数値としては扱われないため一度演算をしてやる必要があるからです。
AND条件（論理値の積）
TRUE TRUE 1
TRUE FALSE 0
FALSE TRUE 0
FALSE FALSE 0
OR条件（論理値の和）
TRUE TRUE 2
TRUE FALSE 1
FALSE TRUE 1
FALSE FALSE 0
このようになります。
最後に、配列数式を用いて数値範囲のデータ個数を求めてみましょう
数値範囲のデータ個数を求める関数に、FREQUENCY関数があります。
まずはFREQUENCY関数を用いて、年齢別の会員数を調べてみましょう。
どこでもいいので、3行1列のセル範囲を選択します。
その状態で、最初のセルに、
=FREQUENCY(D2:D6,{29,39})
を入力します。
Ctrl+Shift+Enter押下で確定すると、
{=FREQUENCY(D2:D6,{29,39})}
の配列数式が、3行1列のセル範囲に入力され、それぞれの範囲のデータ個数を返すはずです。
このとき、29歳以下のデータ個数が1行目のセルに、30〜39歳のデータ個数が2行目のセルに、40歳以上のデータ個数が3行目のセルに表示されます。
※ なぜ、こうなるのかというと"29","39"で範囲指定された「データの個数」は、3つの要素をもつ配列として返されるからです。（上述の29以下、30〜39、40以上の3つの返り値）
返される要素が3つなので当然、表示するセルも3つ必要になります。そこで、3行1列のセル範囲に配列数式として結果を表示させたというわけです。
またFREQUENCY関数を使用しなくても、先ほどの複数条件指定の配列数式を用いることで、データ個数を求めることが可能です。
G1〜G3のセルに0,30,40の数値を、H1〜H3のセルに29,39,100の数値を入力します。
I1のセルに次の配列数式を入力します。
{=SUM(($D$2:$D$6>=G1)*($D$2:$D$6<=H1))}
I1セルをオートフィルでI3までドラッグし数式をコピーします。
年齢が入力されているセル範囲、$D$2:$D$6はオートフィルをしても参照するセル範囲が変わらないように絶対参照しています。
I2のセルに、
{=SUM(($D$2:$D$6>=G2)*($D$2:$D$6<=H2))}
I3のセルに、
{=SUM(($D$2:$D$6>=G3)*($D$2:$D$6<=H3))}
の配列数式がコピーされているはずです。
この配列数式は、年齢がG列の数値以上、H列の数値以下の場合、論理値の積として1を返し、SUM関数でその数を合計しています。
つまり、FREQUENCY関数同様に、ある数値からある数値の範囲までのデータ個数を計算しているわけです。
この場合さらに、数値以外の条件を追加してデータ個数を算出することも可能です。
以上、配列数式について解説してきました。
配列数式は必ずおぼえなければならないテクニックではありません。配列数式を使用しなくても、同様の計算結果を作業セルや関数を用いて算出することは可能です。
しかし配列数式が使えれば、作業セルを使用して求めた計算結果を検証したり、表を変更することなく複雑な計算結果を算出できるなど、エクセルの使用範囲や用途が大きく広がります。
おぼえておいても、決して損なテクニックではないでしょう。
|
