エクセル中級 仮番3-2

Excelの基礎■

■数式を使った検索条件の作り方

検索条件は、数式を使って設定することができます。その方法は、ワークシートへ検索対象のリスト（データベース）との間で条件式（論理式）を作ることです。作成するには、以下のようなポイントがあります。

・条件式は、検索対象のリスト（データベース）の先頭レコードを参照させる
　（他に列見出しを入力する方法もある）

下図は、DSUM関数を使い「=DSUM(A2:D11,D2,A13:A14)」で渋谷の売上数の合計を求めたものです。

条件式は、図のとおり数式 「=B3="渋谷"」を使い、リストの販売区の先頭レコードを参照しています。 通常条件式は、このようにリストの先頭レコードを参照させます。参照しているのは、先頭レコードですが、先頭レコードが列を代表するため、列全体から渋谷を検索します。

数式の結果は、「TRUE」が返されていますが、これはたまたまB3が渋谷のためです。仮に別のデータが入力されていて、「FALSE」であっても問題ありません。
ただし、条件式の結果は「TRUE」か「FALSE」が返される論理式になっている必要があります。

この条件式は、他に「=販売区="渋谷"」と列見出しを直接書き込んで作ることも可能です。この方法では、式の結果にエラー値が返されますが、計算結果には影響しません。詳細は省略します。

･セル参照の条件式は、相対参照、絶対参照を基本とする

条件式の”渋谷”は、直接入力していますが、セル参照で入力することもできます。仮に「G28」に渋谷が入力されていてセル参照とする場合は、「B3=$G$28」とします。このように通常は、先頭レコードの参照を相対参照、リスト外の参照を絶対参照にします。
ただし、この原則は絶対的なものではありません。条件式をコピーして使うような場合は、「$B3=G$28」のようにそれぞれ複合参照にすることも可能です。ただし、先頭レコードへの参照は「$B3」のように行は必ず相対参照なっている必要があります。また、リスト外への参照は、「G&28」のように行は必ず絶対参照になっている必要があります。（理由は省略）

･条件式の列見出しは、空白とするかリストの列見出しと異なるものにする

上の解説図のDSUM関数の式は、「=DSUM(A2:D11,D2,A13:A14)」ですが、Criteria（クライテリア）は、条件式の列見出し部分も含め範囲選択（A13：A14）をしています。Criteria（クライテリア）は、このように必ず検索条件の上の行（列見出し）を含め範囲選択します。
数式を使わない場合この列見出しは、リストと同じ列見出しを設定するところですが、数式の場合、リストと同じ列見出しをつけてはいけません。ここは、空白のままとするか、例のようにリストの列見出しと全く異なる見出しにします。（例では、条件式としています）

･AND条件、OR条件は、複数列や複数行、あるいはAND関数やOR関数などで設定する

検索条件を数式で作る場合も、AND条件、OR条件は複数列や複数行で作ることができます。

左図のように複数列に条件式を設定すれば、AND条件となります。

例は、AND条件の例ですが、OR条件の場合も数式を使わない場合と同じく、複数行で設定できます。

AND関数やOR関数を使っても条件式を作ることができます。
下図はDSUM関数で、=DSUM(A2:D11,D2,A13:A14)として計算結果「125」を求めたものです。

※数式を使った条件式は、かなり複雑な条件を設定することができます。ただ欠点もあります。条件設定がデータベース関数の式の中で確認できませんので、設定条件が確認しづらく、メンテナンス性が悪くなります。従って、一般的には、数式を使わないで条件表で作ることが多いようです。

■検索条件の書き込み位置

Criteria（クライテリア）として指定する検索条件は、数式を使わない条件表で作る場合も、数式の条件式（論理式）で作る場合も、ワークシート上に書き込みます。

検索条件は、検索対象のリスト（データベース）の上に作ったり、解説図のように下に作ったりしますが、実務上では、リストと別のシートやブックへ検索結果を求めることが多いものです。このような場合は、必ず検索結果を求めるシートと同じシート上へ、検索条件を書き込みます。検索条件と検索結果を別シートにするとエラーになる場合があります。

■DSUM関数

DSUM関数は、これまでの解説で取り上げていますが、簡単にまとめておきます。DSUM関数は、見てのとおりSUM関数に「D」が付いたもので、リストの指定した列から検索条件を満たす値の合計を求める関数です。SUMIF関数と似ています。

書式で表すと、DSUM(Database, フィールド, Criteria)となります。
それぞれの引数の意味や指定方法は、上段や前ページで解説したとおりです。

◆使用例

左図は、「4/1」の「パソコン」の売上金額を求めた例です。 検索条件に「パソコン*」としてワイルドカードを使っています。 引数のDatabaseは、「A7:F15」、フィールドは、金額を求める式なので「F7」とし、Criteriaは、「A1:F2」としています。

なお、Criteriaは「A1:F2」とし、空白列を含め範囲選択していますが、列の空白は検索結果に影響しません。仮にこの条件範囲を空白にすると、売上金額の総計が求まります。

※例では、「パソコン*」とワイルドカードを使っていますが、このような場合、エクセル97、2000、2003では単に「パソコン」でも同じ結果を求めることができます。ただし、要注意です。
関連情報： 中級仮番3-2-1 を参照してください。

■DAVERAGE関数

DAVERAGE関数は、AVERAGE関数に「D」が付いたもので、リストの指定した列から検索条件を満たす値の平均値を求める関数です。

書式で表すと、DAVERAGE(Database, フィールド, Criteria)となります。
それぞれの引数の意味や指定方法は、上段や前ページで解説したとおりです。

◆使用例

左図は、「プリンター」の売上数量の平均を求めたものです。 検索条件に「プリンター*」としてワイルドカードを使っています。 引数のDatabaseは、「A7:F15」、フィールドは、数量を求める式なので「E7」とし、Criteriaは、「A1:F2」としています。

※例では、「パソコン*」とワイルドカードを使っていますが、このような場合、エクセル97、2000、2003では単に「パソコン」でも同じ結果を求めることができます。ただし、要注意です。
関連情報： 中級仮番3-2-1 を参照してください。

「中級・特別編トップへ」