<この記事は2829字です>
はじめに
エクセルを使ってデータを分析している方なら”クロス集計”をする機会が多くあるかと思います。クロス集計といえば”ピポットテーブル”です。
しかし、ピポットテーブルには欠点があります。集計はできますが、定型化した資料としてはとてもつかいにくいことです。ピポットテーブルでクロス集計した値を定型資料にコピペする作業を繰り返す必要があるのです。
そこで、定型資料にも使えるクロス集計をエクセル関数で処理する方法をお伝えしたいと思います。
この処理をするには、”配列数式”という関数を使います。
配列数式とは
一般的な関数は、対応する数値一つをインプットして１つの値をアウトプットします。一方、配列数式は、一つの配列（ｎ個の値）をインプットし演算して一つの配列（ｎ個の値）をアウトプットする関数になります。
通常、関数は”Enter”で確定しますが、配列数式では”Ctrl + Alt + Enter”で確定します。数式には外側に大かっこがつきますので、配列数式かどうかはすぐ判別つきます。
配列数式は、配列ごと計算する為大量のデータを集計するには使い勝手がとてもよくなっています。また、見た目がカッコいいじゃ有りませんか、この関数を使った資料を作る事でただ者ではない演出効果もあります（笑）
例えばこんな見え方になります。ちなみにこの式は、D３からＤ９の配列の中から３０以下の値抽出して、その合計を出す数式です。
TRUEとFALSEの性質を理解する
TRUEとFALSEの数的な意味
配列数式を使ったクロス集計では、TRUEとFALSEの性質を活用します。意外と知らない方も多いのでぜひ覚えていただけるといいかと思います。
それは、下記のとおりです。TRUEは１、FALSEは０、と認識されます。
TRUE ＝ １
FALSE ＝ ０
つまり、TRUEとFALSEは計算ができるということを示しています。
例えば、
TRUE × TRUE ＝ TRUE （１×１＝１）
TRUE × FALSE ＝ FALSE （１×０＝０）
この性質と配列関数を組み合わせると非常に便利で見やすい数式になるのです。
まずは、この性質の理解を深めるためにIF関数を使い説明します。
IF関数の入子構造
IF関数は、１つのインプットに対し二つの場合分けをしてアウトプットします。ある条件に合致している値を判定するときによく使いますね。
より多くの（３つ以上）場合分けをするときは、IF関数の中にIF関数を入れるといった入子構造にしなければなりません。（IFS関数があるのですが、ここでは説明は省きます）
下図は人の属性を表す表です。それを右側で集計してます。I列では、C列の性別に対し女性かどうかをIF関数で判断しています。
これに、年齢が”30以下”という要素を加えます。IF関数の入れ子構造になっています。
さらに、住所が”A町”かどうかの判断を加えます。これで、女性で３０才以下でA町に住んでいるかが判定されます。しかし、関数がかなり複雑になりわかりにくくなってしまいます。
これらの関数を整理しますと、下図のようになります。判断する要素をIF関数のTRUEの場合の処理部分に挿入していきます。これでは、自分以外の担当者には理解不能となってしまいます。
これと同じ処理をTRUEとFALSEの性質を使いますと、下図になります。
整理しますと、
赤字の（C3＝”女”）が成り立てば TRUEつまり１、成り立たなければ FALSEつまり0となります。青字、緑字についても同様です。それらを掛け算すれば、すべての条件を満たすときだけ１つまりTRUEとなります。
この式の場合３行目ですので”たかお”の属性を判断します、
- C3=”男”ですので、FALSE→0
- D3=25 ですので、FALSE→0
- E3=A町 ですので、TRUE→1
従って、０×０×１=０となりますので、FALSEとなります。
よって、IF関数は”No”をアウトプットします。
IF関数の入れ子構造はこのように記述する事ができます。可読性が高くメンテナンスもしやすいのではないでしょうか？これなら、判定項目を何個でも簡単に増やしていけます。
配列数式で集計してみる
３つの条件に合致する件数は？
下図は、表の”女性で３０歳以下でA町の人の人数”を配列数式を使い集計したものです。複雑な計算の割には数式がとてもシンプルになっていることが分かると思います。
配列数式では、一つのセルではなく配列を処理します。したがって、性別・年齢・住所のそれぞれ３～９行目に対し抽出条件を設定しています。
SUM関数のカッコ内には、条件を満たすかどうかの答え（TRUE、FALSE）を掛け算してすべての条件を満たすとき”１”となるように記述しています。
分かりやすいように、SUM関数のカッコ内の条件式を関数として入力してみました。IF関数で”Yes”となっているセルと対応して”１”となっていることが分かります。
上記配列数式では、このS3:S9の合計をSUM関数で計算しているのです。
条件の追加
次に、配列数式に趣味に関する条件を増やしてみます。
下図のように *（F3＝”バスケ”） を追記すればいいのです。
SUM関数のカッコ内に条件をどれだけ追加しても問題はありません。
読みやすくする
この数式の可読性を高めるために、セルに名前を付けてみます。（名前の付け方については説明を省きます。名前ボックス使用）とても見やすくなります。
この時気を付けなければならないのは、配列の数（今回の場合は３～９ですので７となります）は必ず同じ出なくてはエラーが出てしまいます。名前をつける際には気を付けましょう。
合計値を計算
件数だけではなく条件に合った数値の合計を計算してみます。
条件は”女性で３０歳以下でA町に住んでいて趣味がバスケの人の得点合計”です。
関数の最後に得点欄（G3：G９）を追記しています。抽出条件を”１”か”０”で判定し、それに得点を掛け算して合計を計算しています。
他の関数を使った応用
OR関数を使ってより細かな抽出条件を設定してみます。
下図は、趣味が”バスケ” → 趣味が”バスケ”か”サッカー” の条件と変更しています。OR関数もTRUEとFALSEを返す関数ですので、他の抽出条件と同じように計算可能です。
【ＶＢＡコード付】”選択範囲で中央”を右クリックメニューに追加しよう - ５０代企業内診断士のつぶやき
まとめ
配列数式はあまり解説している書籍もありませんし、Webでもわかりやすい解説は見つけられませんでした。
しかし、読んでいただいてわかる通り非常に便利で使い勝手のよい関数かと思います。この使い方はさらには応用が利き、いろんなアイデアでより便利になると思っております。ぜひ使ってみてください。良いアイデアがあれば教えていただけるとありがたいです。
これからは、”Enter+Shift+Ctrl”で関数入力をしましょう！
