Excelには配列数式というものがあります。
配列数式を使う事でExcel2007から導入された関数「=SUMIFS」をユーザー定義関数を使わずにExcel2003でも計算可能になります。
SUMIFS・・・複数の条件に一致する合計を求める関数です。
「商店A」 and 「みかん」の合計を出します。
配列って・・・？
まずは配列とはどういうものなのか？という疑問が湧くと思います。プログラミングする人であれば解るとは思いますが、
配列とは同じ型のデータを連続的に並べたデータ形式。各データをその配列の要素といい、それらは添字(インデックス)で識別される。 （引用：IT 語辞典）
これではよく解らない。
では例をあげて説明してみましょう。
タンスの1段目に下着、2段目にシャツ、3段目にズボンを収納します。
ではタンスという配列は（”下着”,”シャツ”,”ズボン”)となるのです。1段目、2段目、3段目は値が入り、それをまとめてタンスという配列になります。
配列をどう使うのか
ではExcelに配列がどう関係するのか？
実はよく使うSUM関数、配列も計算出来る様になっているんです。そもそもSUM関数は（値1、値2…）という形の引数であり、範囲を指定する引数ではありません。
SUM(A1:A10) という様に範囲指定して合計しますが、
｛A1の値、A2の値、A3の値….A10の値｝という配列になっており、その合計をするから範囲内の合計が出来るんです。
以上の事を踏まえて次項
SUMIFを配列数式で再現してみる
SUMIFSにいく前にまずSUMIFを配列数式で理解した方が解り易いのでまずはSUMIFから。
上のような表があり、A列が”○”だけのB列の値を足したい場合。
普通に関数を使えば=SUMIF(A1:A10,”○”,B1:B10)ですよね。
配列を使うと
| =SUM( IF( A1:A10=”○” , B1:B10 , )) |
そしてこの計算式を配列数式として認識させる為に
Ctrl + Shift + Enter
を押す必要があります。すると数式バーを見ると
{=SUM(IF(A1:A10=”○”,B1:B10,))} 前後に{}が付きます。
これで配列数式となり、計算結果は320になります。もしそのままEnterした場合は配列数式とならずErrorになってしまいます。
ではどういう事なのか？｛｝内は配列
SUM ( {10,,,,50,,70,,90,100}）
○であった場合B列を返し、○でなければ何も値を返さないIF文でしたのでこのような配列になり、それをSUMで足すという訳です。
ではSUMIFS
上のSUMIFを理解出来れば後は簡単。
IF文にANDとかOR使えばいいんでしょ～。
それ・・・間違いです！！
なんと配列数式にはAND、ORが使えないのです。
その代わりに＋や＊を使います。
その前にTRUE=1 , FALSE=0 という事を頭に入れておいてください。
例：IF((A1=”○”)*(B1=”○”),”OK”,”NG”) という普通の計算式だった場合１が返ります。
A1が○なのでTRUE=1 ×（かける）B1が○なのでTRUE=1
１×１なので(A1=”○”)*(B1=”○”)=1 であり、TRUEとなる
TRUEの場合１を返すIF文なのでOKになります。
もし片方が×だったらFALSE=0を掛けるので掛け算の結果0になり、NGとなります。
この仕組みを使って以下の配列数式にするとSUMIFSが実現出来ます。
| =SUM(IF( (A1:A10=”○”) * (B1:B10=”○”) , C1:C10 , )) |
Ctrl + Shift + Enterで完了！
配列数式を使う場合の注意点！！
- 数式を編集する度にCtrl＋Shift＋Enterで確定させないといけない
- 他者への譲渡に不向きである（上項を意識しない為）
以上の点から他者へ渡すファイルに使うのは極力避けたほうが良いでしょう。もし渡す場合は編集不可にロックを掛けないと編集され、エラーとなってしまいます。もしくはユーザー定義関数のSUMIFSを使うと問題はありませんね。