複数の条件での平均、最大値、最小値、合計を簡単な数式で処理する

OpenOffice

下の表は、とある会社の営業部員の売上一覧です。
営業エリアが北部、中部、南部の3つに別れていてそれぞれ男女の担当者がいます。
分析をするに当たって、担当者の属性(エリアと性別)を考慮して合計、平均、最大値、最小値を求めることにしたのが、表の右側です。
ここでは、複数の条件に合った集計処理を行なう2つの方法を紹介します。IF関数と組み合わせる　DATABASE関数を使う

送信者 OpenOffice
このサンプルファイルは、SKYDRIVEからcalc135.odsをダウンロードしていただき、IFを組み合わせのタブをクリックしてください。

方法１：IF関数と組み合わせる
複数の条件のもとでの集計を行なうとき、ちょっとした工夫が必要になります。
北部のエリア担当で、女の売上を集計するとき、
もしも、(C2:C12が北部)で、(D2:D12が女)のとき、SUMで集計の対象にする。そうでないときは集計の対象にしない。
という内容の数式を作ります。

送信者 OpenOffice
条件が1つの場合と複数の場合で、条件を()で括るか括らないかの違いが生じるので、分けて書きましたが、基本的な構造は同じです。
CtrlキーとShiftキーを押しながらEnterを押すと、{}で数式が括られて条件に合うデータだけが集計の対象にされる、いわゆる配列数式です。
誤ってEnterだけを押すと、集計されません。その場合、下の画像のようにやり直してみてください。
いずれの場合も、アタマがSUM、AVERAGE、MAX、MINなどの違いがあるだけで以下は、何も違いはありません。
他に、COUNT関数でも使えます。
送信者 OpenOffice

方法２：DATABASE関数を使う

上に書いた処理は、もちろん、他の関数を用いても可能です。
例えば、DSUM関数、DCOUNT関数、DMAX関数、DMIN関数、SUMPRODUCT関数、SUMIF関数などが使える場合があります。
一例を下に表示します。

送信者 OpenOffice
このサンプルファイルは、SKYDRIVEからcalc135.odsをダウンロードしていただき、D系関数の場合のタブをクリックしてください。
関数名：DSUM関数
書式：=DSUM(リスト範囲;フィールド;検索条件範囲)
何ができる：リスト範囲から検索条件に当てはまるレコードのデータだけを合計する
平たく書くと：=DSUM(B1:D12;B1;F1:G2)　でB1:D12から、F1:G2に指定されたと条件に合うB列の行を合計する

関数名：DAVERAGE関数
書式：=DAVERAGE(リスト範囲;フィールド;検索条件範囲)
何ができる：リスト範囲から検索条件に当てはまるレコードのデータだけを平均する
平たく書くと：=DAVERAGE(B1:D12;B1;F1:G2)　でB1:D12から、F1:G2に指定されたと条件に合うB列の行を平均する

関数名：DMAX関数
書式：=DMAX(リスト範囲;フィールド;検索条件範囲)
何ができる：リスト範囲から検索条件に当てはまるレコードのデータだけを対象にして最大値を求める
平たく書くと：=DMAX(B1:D12;B1;F1:G2)　でB1:D12から、F1:G2に指定されたと条件に合うB列の行から最大値を求める

関数名：DMIN関数
書式：=DMIN(リスト範囲;フィールド;検索条件範囲)
何ができる：リスト範囲から検索条件に当てはまるレコードのデータだけを対象にして最小値を求める
平たく書くと：=DMIN(B1:D12;B1;F1:G2)　でB1:D12から、F1:G2に指定されたと条件に合うB列の行から最小値を求める


しかし、次のような場合は、DATABASE関数は使えません。というか、使いづらいです。
送信者 OpenOffice
DATABASE系の関数では検索条件の見出しの直ぐ下の行に検索条件が書かれている必要があります。しかし、上の表の場合、K列とL列において、南部の1月はその条件を満たしていますが、北部の一月以降は、見出しと行が離れています。もしもDATABASE関数を使おうと思えば、一行ごとに見出し行を挿入しなければいけません。これは、面倒であると同時に、表として見やすさを損ないます。
このサンプルファイルは、SKYDRIVEからcalc135.odsをダウンロードしていただき、D系関数が使いにくい場合のタブをクリックしてください。


この記事をクリップ！