２−５

データベース処理

　ここでは、データベース関数の使い方を解説します。データベース関数の引数は「データ範囲」「フィールド」「検索条件」の３つがあり、それぞれの関数での設定内容や使用目的は同じになっています。なお、データベース関数の応用例および詳細は「関数ガイド」「活用ガイド」で解説しているので合わせてご覧ください。

 

１◆最大・最小・平均値

　データの最大値や最小値、平均値、データの個数、などを求める以下の関数があります。各関数は、データベース関数として機能するので、データの入力されているセル範囲とどのデータを対象に集計などの計算を行うのかを指定することが必要になります。集計など計算の対象とするデータの集まりのことを「フィールド」といい、どのデータを対象にするかの指定が「検索条件」になります。

 

DMAX関数         指定したデータの中で最大値を返す

DMIN関数         指定したデータの中で最小値を返す

DAVERAGE関数             指定したデータの平均値を返す

DSUM関数         指定したデータの合計値を返す

DCOUNT関数               指定したデータの中で数値が入力されているセル数を返す

DCOUNTA関数              指定したデータの中で空白でないセル数を返す

 

２◆最大値と最小値

　データベースとしての数値の最大値はＤＭＡＸ関数、最小値はＤＭＩＮ関数を使います。

 

●ＤＭＡＸ関数／ＤＭＩＮ関数

　データ範囲のフィールドで指定する列の最大値（ＤＭＡＸ関数）あるいは最小値（ＤＭＩＮ関数）を返します。

 

書式             DMAX(データ範囲,フィールド,検索条件)

                DMIN(データ範囲,フィールド,検索条件)

データ範囲       データベースのセル範囲を指定する

フィールド       データベース内の使用する列を指定する

検索条件 条件が設定されているセル範囲を指定する

 

●使用例

　次の例は、商品販売の履歴簿をデータベースのデータ範囲として、担当者が取り扱った販売品の数量から最大値と最小値を抽出しています。検索条件は、図ＡのセルＨ３：Ｈ４のようにフィールド名（担当者）と検索データ（桑鶴）を縦に並べて入力しておき、データベース関数の引数としてセル範囲（Ｈ３：Ｈ４）で指定します。各数式は以下のようになります。

 

        ＝ＤＭＡＸ（Ｂ４：Ｆ１６，”数量”，Ｈ３：Ｈ４）       ・・・　最大値

        ＝ＤＭＩＮ（Ｂ４：Ｆ１６，”数量”，Ｈ３：Ｈ４）       ・・・　最小値

 

�r＞操作

�@セルＩ６に最大値を求める数式を入力する

［Ａ］結果が求められる

 

求められた結果の２０は、データ範囲のフィールド「数量」内の検索条件「桑鶴」が担当した中の最大値になります。

 

　EX4A988Z

 

　EX4A989Z

 

�r＞操作

�@セルＩ７に最小値を求める数式を入力する

［Ａ］結果が求められる

 

求められた結果の５は、データ範囲のフィールド「数量」内の検索条件「桑鶴」が担当した中の最小値になります。

 

　EX4A990Z

 

 

　EX4A991Z

 

３◆平均値

　データベースとしてのデータの平均値はＤＡＶＥＲＡＧＥ関数を使います。

 

●ＤＡＶＥＲＡＧＥ関数

データ範囲のフィールドで指定する列の平均値を返します。

 

書式             DAVERAGE(データ範囲,フィールド,検索条件)

データ範囲       データベースのセル範囲を指定する

フィールド       データベース内の使用する列を指定する

検索条件 条件が設定されているセル範囲を指定する

 

●使用例

　次の例は、商品販売の履歴簿をデータベースのデータ範囲として、担当者が取り扱った販売品の数量の平均値を求めています。検索条件は、図ＡのセルＨ３：Ｈ４のようにフィールド名（担当者）と検索データ（桑鶴）を縦に並べて入力しておき、データベース関数の引数としてセル範囲（Ｈ３：Ｈ４）で指定します。数式は以下のようになります。

 

        ＝ＤＡＶＥＲＡＧＥ（Ｂ４：Ｆ１６，”数量”，Ｈ３：Ｈ４）

 

�r＞操作

�@セルＩ８に平均値を求める数式を入力する

［Ａ］結果が求められる

 

　担当者「桑鶴」が担当した販売数量の平均値が求められます。

 

　EX4B597Z

 

　EX4B598Z

 

４◆条件に合うデータの合計

           　データベースとしてのデータの合計値はＤＳＵＭ関数を使います。

 

●ＤＳＵＭ関数

　データ範囲のフィールドで指定する列の合計を返します。

 

書式             DSUM(データ範囲,フィールド,検索条件)

データ範囲       データベースのセル範囲を指定する

フィールド       データベース内の合計計算に使用する列を指定する

検索条件 条件が設定されているセル範囲を指定する

 

●ＤＳＵＭ関数の使用例

　次の例は、商品販売の履歴簿をデータベースのデータ範囲として、担当者が取り扱った販売品の数量の合計を求めています。検索条件は、図ＡのセルＨ３：Ｈ４のようにフィールド名（担当者）と検索データ（桑鶴）を縦に並べて入力しておき、データベース関数の引数としてセル範囲（Ｈ３：Ｈ４）で指定します。数式は以下のようになります。

        ＝ＤＳＵＭ（Ｂ４：Ｆ１６，”数量”，Ｈ３：Ｈ４）

 

　なお、フィールドの指定はセル番地でもよい。上の数式をセル番地で記述すると以下のようになる。

        ＝ＤＳＵＭ（Ｂ４：Ｆ１６，Ｅ４，Ｈ３：Ｈ４）

 

�r操作

�@セルＩ１０に平均値を求める数式を入力する

［Ａ］結果が求められる

 

　EX4B599Z

 

　EX4B600Z

５◆データの個数

　データベース範囲内のデータが入力されているセルの個数を求めるには、ＤＣＯＵＮＴ関数またはＤＣＯＵＮＴＡ関数を使います。ＤＣＯＵＮＴ関数は数値が入力されているセル数、ＤＣＯＵＮＴＡ関数はデータが入力されているセル数（空白でないセル数）の個数を求めます。

 

●ＤＣＯＵＮＴ関数／ＤＣＯＵＮＴＡ関数

データ範囲のフィールドで指定する列の数値が入力されているセル数（DCOUNT関数）を返します。DCOUNTA

関数は数値や文字などが入力されている（空白でない）セル数を返します。

 

書式             DCOUNT(データ範囲, フィールド, 検索条件)

                DCOUNTA(データ範囲, フィールド, 検索条件)

データ範囲       データベースのセル範囲を指定する

フィールド       データベース内の使用する列を指定する

検索条件 条件が設定されているセル範囲を指定する

 

●使用例

次の例は、商品販売の履歴簿をデータベースのデータ範囲として、担当者が取り引きした回数を求めています。検索条件は、図ＡのセルＨ３：Ｈ４のようにフィールド名（担当者）と検索データ（桑鶴）を縦に並べて入力しておき、データベース関数の引数としてセル範囲（Ｈ３：Ｈ４）で指定します。

セルに数量が入力されていれば、取り引きあり、として数量の入力されているセル数、つまり数値が入力されているセル数を取り引きした回数に見立てています。数式は以下のようになります。

 

        ＝ＤＣＯＵＮＴ（Ｂ４：Ｆ１６，”数量”，Ｈ３：Ｈ４）

 

�r＞操作

�@セルＩ１２に数式を入力する

［Ａ］結果が求められる

 

　EX4B601Z

 

　EX4B602Z

 

●使用例２

　次の例は、商品販売の履歴簿をデータベースのデータ範囲として、１つの商品に対して何人の担当者が取り引きしたかを求めています。検索条件は、図ＡのセルＨ３：Ｈ４のようにフィールド名（品番）と検索データ（Ａ−２０）を縦に並べて入力しておき、データベース関数の引数としてセル範囲（Ｈ３：Ｈ４）で指定します。

　セルに名前（文字列）が入力されていれば、担当した者があり、として延べ担当者数、つまり文字が入力されているセル数を求めています。数式は以下のようになります。

 

        ＝ＤＣＯＵＮＴＡ（Ｂ４：Ｆ１６，”担当者”，Ｈ３：Ｈ４）

 

�r＞操作

�@セルＩ１２に数式を入力する

［Ａ］結果が求められる

 

　商品「Ａ−２０」の取り引き回数は４件ですが、セルＣ１１には担当者名が入力されていないので、３が求められています。ここでは文字列だけをカウントの例にしましたが、入力内容が数値であってもカウントされます。ＤＣＯＵＮＴＡ関数がカウントしないのは空白セルだけです。

 

　EX4B603Z

 

　EX4B604Z

 

 

【まとめ】

●データベース処理の検索条件は項目名（フィールド名ともいう）と検索値を縦に連続するセルに入力して、そのセル範囲を指定する

●データベースのデータ範囲（セル範囲）は、列をフィールドといい、行をレコードという

 

 

 

 

■データの抽出と積算

データベース（データの検索を含む処理）処理にも一覧表内の任意のデータを抜き出す（抽出する）関数があります。また、抜き出したデータを別のデータと積算する関数もあります。

 

DGET関数         データの抽出

DPRODUCT関数             データの抽出と積算

 

１．データの抽出

データベースのデータ範囲内から特定のデータを抜き出すには、ＤＧＥＴ関数を使います。

 

●ＤＧＥＴ関数

リストまたはデータベースの列から指定された条件を満たす１つの値を抽出します。列の位置を項目名で指定して、行位置を検索で求めて、交点のデータを抽出するときに利用します。

 

書式     DGET(データ範囲, フィールド, 検索条件)

 

データ範囲       データベースのセル範囲を指定する

フィールド       データベース内の使用する列を指定する

検索条件 条件が設定されているセル範囲を指定する

 

検索条件を満たすレコードがない場合、エラー値#VALUE!、レコードが複数存在する場合、エラー値#NUM！が返されます。

 

使用例

次の例は、データの一覧表をデータベースのデータ範囲として、氏名を検索条件に指定するフィールド「データＣ」の値を求めています。検索条件は、図ＡのセルＢ１１：Ｂ１２のようにフィールド名（氏名）と検索データ（幸山）を縦に並べて入力しておき、データベース関数の引数としてセル範囲（Ｂ１１：Ｂ１２）で指定します。数式は以下のようになります。

 

        ＝ＤＧＥＴ（Ｂ３：Ｆ８，”データＣ”，Ｂ１１：Ｂ１２）

 

�r＞操作

�@セルＥ２０に数式を入力する

［Ａ］結果が求められる

 

　

EX4B005Z　　　　　　　　　　　　　　　　　　　　　　EX4B006Z

 

この例では、指定した列［データＣ］内で、行位置を氏名［幸山］を指定したので、縦と横の交点にあたる３２０が抽出されています。

 

２．データの抽出と積算

データベースのデータ範囲内から２つの特定のデータを抜き出して、積算を行うＤＰＲＯＤＵＣＴ関数があります。

 

●ＤＰＲＯＤＵＣＴ関数

リストまたはデータベースの指定された列を検索し、条件を満たすレコードの積を返します。

 

書式     DPRODUCT(データ範囲, フィールド, 検索条件)

 

データ範囲       データベースのセル範囲を指定する

フィールド       データベース内の使用する列を指定する

検索条件 条件が設定されているセル範囲を指定する

 

使用例

次の例は、商品の在庫数と販売数量を一覧にした集計表をデータベースのデータ範囲として、在庫価格や月語との販売総額を求めています。項目を検索条件に指定するフィールド「椅子」列の該当するレコード（行、項目名の行）を２つ（例では「単価」と「在庫数」）の積を求めています。検索条件は、図ＡのセルＨ３：Ｈ５のようにフィールド名（項目）と検索データ「単価」と「在庫数」の２つを縦に並べて入力しておき、データベース関数の引数としてセル範囲（Ｈ３：Ｈ５）で指定します。数式は以下のようになります。

 

        ＝ＤＰＲＯＤＵＣＴ（Ｂ３：Ｆ９，”椅子”，Ｈ３：Ｈ５）

        　　　　　　　　　　　　↑　　　　↑　　　　↑

        　　　　　　　　　データ範囲　フィールド　検索条件　

 

�r＞操作

�@セルＨ８に数式を入力する

［Ａ］結果が求められる

 

 

　EX4B607Z

 

　EX4B608Z

 

 

検索条件に指定した「単価」（セルＨ４）と「在庫数」（セルＨ５）のそれぞれをフィールド（数式内に記述した「椅子」）の列から抜き出して、積算した値が求められています。２つ目の検索条件（セルＨ５の「在庫数」）を他の項目名（「１月販売数」など）に書き替えれば、その値が計算されます。

 

【まとめ】

●一覧表から縦と横の位置指定と検索で値を抽出するにはＤＧＥＴ関数を使います。

●一覧表から縦と横の位置指定と検索で２つの値を抽出して積算した値を求めるとき、ＤＰＲＯＤＵＣＴ関数を使います。

 

 

 

 

 

 

 

 

作成手順

　さて、どこから書き始めればいいのでしょう。