Excelデータベース関数と条件統計関数

はじめに

SUM関数、COUNT関数、AVERAGE関数などの統計関数は、あるセル範囲のデータすべてを対象として、それぞれ合計、個数、平均値を求めますが、セル範囲のデータ全てではなく、ある条件のものについてのみ計算対象としたい場合に、DSUM関数やDCOUNT関数などのデータベース関数、SUMIF関数やSUMIFS関数などの条件統計関数があります。

それぞれの関数の違い

  • DSUM関数
    • 複数の条件(AND/OR)に対応
    • 計算対象範囲がリストの1フィールドになっていなければならない
    • 条件範囲の領域(項目名と条件のセルの組)を別途設ける必要がある
  • SUMIF関数
    • 単一の条件に対応
    • Office 2003以降のバージョンで対応
  • SUMIFS関数
    • 複数の条件(AND/OR)に対応
    • Office 2007以降のバージョンで対応

DCOUNT関数、COUNTIF関数、COUNTIFS関数や、DAVERAGE関数、AVERAGEIF関数、AVERAGEIFS関数なども同様です。

[本テキスト用データダウンロード]
ex_db_cross1.xlsx


データベース関数

ここでは、DCOUNTA関数、DAVERAGE関数を例に説明します。

構文

DCOUNTA(リスト範囲, 計算対象フィールド, 条件範囲)
DAVERAGE(リスト範囲, 計算対象フィールド, 条件範囲)

DSUM関数、DCOUNT関数、DMAX関数、DMIN関数も同様

下図の例は、いずれもリスト範囲がB5~D18で、条件範囲がそれぞれ異なります。リスト範囲なので、項目名も含まれていることに注意しましょう。
DAVERAGE関数の場合は、平均値を計算するため計算対象フィールドが数値である必要があるので、3列目を指定していますが、DCOUNTA関数の場合は、行数のカウントなので、計算対象フィールドは何列目でも構いません。

ex-db01


条件統計関数

ここでは、COUNTIF関数、AVERAGEIF関数及びCOUNTIFS関数を例に説明します。

構文

COUNTIF(カウントしたい範囲, 検索条件)
AVERAGEIF(範囲, 検索条件 [, 平均計算対象範囲])
COUNTIFS(条件範囲1, 検索条件1 [, 条件範囲2, 検索条件2, ...])

AVERAGEIF関数の第3引数

AVERAGEIF関数にて、第3引数(平均計算対象範囲)を省略した場合は、第1引数(範囲)が平均計算の対象となります。

  • =AVERAGEIF(D6:D18,">=80")
    • 点数リスト(D6:D18)の中で「80点以上の点数」のみで平均する
    • =AVERAGEIF(D6:D18,">=80",D6:D18)」と指定したのと同じ
  • =AVERAGEIF(C6:C18,"法文学部",D6:D18)
    • 点数リスト(D6:D18)から、学部リスト(C6:C18)で「法文学部」の行だけを絞って平均する

上記のように、条件範囲と計算対象範囲が異なる場合は注意が必要です。これは、SUMIF関数、MAXIF関数、MINIF関数も同様です。

SUMIF関数とSUMIFS関数の引数指定の違い

SUMIFS関数は、条件を複数指定できるということで、SUMIF関数の拡張版のようですが、引数の指定方法が異なります。

  • =SUMIF(範囲, 検索条件 [, 合計対象範囲])
  • =SUMIFS(合計対象範囲, 条件範囲1, 検索条件1, [条件範囲2, 検索条件2, ...])

上記のように、SUMIF関数の場合は、計算対象範囲が第3引数ですが、SUMIFS関数の場合は、条件が複数指定できることから第1引数が計算対象範囲となっています。これは、AVERAGEIF関数とAVERAGEIFS関数、MAXIF関数とMAXIFS関数、MINFS関数とMINIFS関数もそれぞれ同様です。

ex-db02

データベース関数と違って、条件範囲(項目名と条件)を設ける必要がないので、上記のような複数行に対して計算させたい場合は有効です。


配列数式

配列数式とは、複数の異なる数式を1つの数式で表現できるもので、1つの配列数式で複数の結果を生成することも可能です。計算結果が配列となる数式は、配列数式として入力する必要があります。

配列数式は、式の入力確定の際に[Enter]キーだけでなく、[Ctrl]+[Shift]+[Enter]キーを押すことで配列数式として入力できます。ただし、配列数式に利用できる関数は統計関数等に限られています。

例えば下図の例(Q1)で、数式「=SUM(IF(C6:C18=F6,1,0))」は、「=SUM(IF(C6=F6,1,0),IF(C7=F6,1,0),IF(C8=F6,1,0),...,IF(C18=F6,1,0))」のように、C列(学部)を1行1行見ていき、F6(”法文学部”)と等しい場合は1を返し、そうでない場合は0を返し、それを最終的に合計するというSUM関数の中身を「IF(C6:C18=F6,1,0)」という1つの数式で表現しています。

Q2の例では、まず計算式を入れたいセル範囲(I13~I15)を選択状態にしてから、I13に数式を入力を試み、最後確定(入力終了)する際に[Ctrl]+[Shift]+[Enter]キーを押して入力します。前のシートの例では、COUNTIFS関数の範囲してにおいて絶対セル参照を用いてましたが、配列数式においては、相対セル参照にて入力します。

ex-db03

配列数式に入力されている数式を削除する際は、まず配列数式が入力されているセル範囲を選択してから[Delete]キーを押して削除します。1つのセルを選んで削除することはできません。

また、Q3の例のように、配列数式に特化した関数(FREQUENCY関数)があります。

構文

区間内のデータの頻度分布(ヒストグラム)を計算します。

FREQUENCY(データ配列, 区間配列)

Excelデータベース関数と条件統計関数」への3件のフィードバック

コメントを残す

以下に詳細を記入するか、アイコンをクリックしてログインしてください。

WordPress.com ロゴ

WordPress.com アカウントを使ってコメントしています。 ログアウト /  変更 )

Google フォト

Google アカウントを使ってコメントしています。 ログアウト /  変更 )

Twitter 画像

Twitter アカウントを使ってコメントしています。 ログアウト /  変更 )

Facebook の写真

Facebook アカウントを使ってコメントしています。 ログアウト /  変更 )

%s と連携中