データベース関数を使って
複数の条件にあったデータを取り出す


　エクセルにはデータを管理する機能として、データベース機能というものがあります。データベースを利用するにはワークシート上に表データを作成する必要があり、この表データをリストといい、リストは各列に見出しをつけ、列見出しの下には同じ種類のデータを入力します。このリストの列をフィールド、行をレコード、列見出しをフィールド名といいます｡このようなデータベース機能をワークシート上で利用するための関数を、データベース関数といいます。
　データベース関数を利用すると、複数の条件をつけてリストから簡単にデータを取り出すことができるのです。たとえば7月号でご紹介したDSUMもデータベース関数の一つ。DSUMは、条件にあったデータの合計を求める関数で、SUMIFが条件を一つしか設定できないのに対し、DSUMは、データを範囲指定するだけで簡単に複数の条件にあったデータを取り出すことができます。
　このようなデータベース関数は全部で13種類。GETPIVOTDATAだけ例外ですが、それ以外は統計関数（平均値を出すAVERAGEなど）や数学/三角関数（合計を出すSUM）の前にデータベースの「D」を付けたかたちとなり、書式はすべて（Database, フィールド, Criteria）の順となります。フィールドはデータを抽出したいフィールド名を入力、Criteriaには検索条件を設定します。また検索条件の先頭行のデータはデータベースのフィールド名と一致している必要があるので注意しましょう。


今月の関数

1．DAVERAGE関数
　　書式　=DAVERAGE(Database, フィールド, Criteria)
　E17セルでは、(A)の範囲のデータから(C)の条件に合致する(B)の「定員」項目のデータを抽出、その平均を算出。ここでは引数（ひきすう）のDatabaseが(A)、フィールドが(B)、Criteriaが(C)にあたる。

2．DMAX関数
　　書式　=DMAX(Database, フィールド, Criteria)
　E23のセルでは、(A)の範囲のデータのうち、(E)の条件に合致する、(D)の「受講料」項目のデータの最大値を出している。ここでは引数のDatabaseが(A)、フィールドが(D)、Criteriaが(E)にあたる。

3．GETPIVOTDATA関数
　　書式　=DAVERAGE(ピボットテーブル,名前)
　H16のセルでは、(F)というピボットテーブル化したデータ範囲のうち、(G)の項目名のデータを表示。これにより集計方法を変更した場合でも指定した項目を参照。引数のピボットテーブルは(F)、名前が(G)にあたる。

1．2つの条件からデータを抽出

　受講料が2万円以上、5万円未満の平均人数を求める。ここでデータベース関数を利用する前に検索条件を入力。(1)検索条件の先頭行には検索するフィールドと同じフィールド名を入力。リストとまったく同じ文字を入れ、同じフィールド名に複数の条件を設定するときは、条件の数だけフィールド名の欄を設定。(2)条件に使用する演算子はすべて半角で入力。アスタリスク(*)を利用すると、MOUSで始まる文字列のすべてを表すことができる。


　　　　　　　　　　　　　　　　　　
　(3)データを抽出したいセルをクリックし、(4) [関数貼り付け] ボタンをクリック。データベース関数の関数名ボックスから [DAVERAGE関数] を選択し [OK] ボタンを押す。


　　　　　　　　　　　　　　　　　　
　(5)数式パレットの「Database」にカーソルを置き、リストを範囲選択。(6)「フィールド」には、定員の平均を求めるので定員のフィールド名であるD4をクリック。(7)「Criteria」には検索条件を入力するので、B15からD16までを範囲選択し、[OK] ボタンをクリック。


E17のセルに入った式は
=DAVERAGE(B4:E11,D4,B15,:D16)


2．OR、AND条件でデータを抽出

　9/1からのエクセル、ワードの試験でいちばん高い受講料を求める。まず検索条件を入力。アスタリスクを文字列の前につけると、文字列を含むすべてのデータを表すことができる。条件は、同じ行に入力するとAND条件、行を変えて入力するとOR条件が設定。(9)データを出したいセルを選択、[関数の貼り付け] ボタンを押し、データベース関数から [DMAX関数] を選択し、[OK] ボタンを押す。数式パレットの「Database」には、B4からE11を、「フィールド」にはC4を、「Criteria」には(8)B21からC23までを選択し、[OK] ボタンをクリック。


E23のセルに入った式は
=DMAX(B4:E11,C4,B21:C23)

3．ピボットテーブルでセルを参照するには

　ピボットテーブルでセルを参照しようとすると、元データを変更した場合、データが正しく参照されなくなってしまう。しかし [GETPIVOTDATA関数] を利用すると、元データを変更してもデータが正しく抽出される。(10)元となるリストの範囲を選択、[データ] → [ピボットテーブルとピボットグラフレポート] を選択し、(11) [レイアウト] ボタンをクリックしてピボットテーブルレイアウトを設定。


　　　　　　　　　　　　　　　　　　　　　　　


　　　　　　　　　　　　　　　　　　　　　
　(12)ピボットテーブルを作成。(13)参照したいセルをクリックし、[関数の貼り付け] ボタンをクリック。データベース関数の一覧にはないので、「関数の分類」ボックスから [すべて表示] を選択し、「関数名」の一覧から [GETPIVOTDATA] を選択し [OK] ボタンをクリック。

　　　　　　　　　　　　　　　　　　　　
　数式パレットの「ピボットテーブル」には抽出するデータの左上のセルを指定するので、G6を選択。「名前」にはデータを抽出する値のフィールド名を入力し、[OK] ボタンをクリック。


H16のセルに入った式は
=GETPIVOTDATA(G6,G17)

(14)を押し、ピボットテーブルの月名を変更して、[OK] ボタンをクリックすると、データが変更され、参照されたセルの値も同時に変更されて表示される。