使える関数～DSUM関数

いくつかあるデータベース関数のなかから今回はDSUM関数を紹介します。

DSUM関数は任意のデータ範囲（データベース）から、指定された条件の指定されたフィールドのデータを集計する関数です。

フィールドとは、例えば、「顧客№」「顧客名」「商品名」などの項目列と理解しておけばいいと思います。

このような、データ、表、集計表を用意しました。

りんごの入荷データ


仕入価格表


等級別集計表


等級別集計表の数量欄に入荷データの数量をDSUM関数により集計します。

そして仕入価格表から該当する価格をVLOOKUP関数により表示し、等級別集計表を完成させます。



まずはDSUM関数の考え方から解説します。

データが入力された下に氏名と品種名を入力すると入荷数が集計されるエリアを作りました。



DSUM関数の引数です。

DSUM（①データベースの範囲,②集計するフィールド,③条件範囲）

今回は「八戸二郎」さんの「つがる」という品種の集計を行います。氏名と品種にそれぞれ入力します。

まずは階級「20玉」から集計します。セル「C12」を選択し、「＝DSUM（」まで入力します。

①データベースの範囲、セル「A2」から「E9」までを選択します。コピーすることも考えて絶対参照にします（F4キーを押します）。「,」で区切ります。



②集計するフィールドは「20玉」ですから、20玉と入力されたセル「C11」を選択します。「,」で区切ります。



③条件範囲は氏名と品種ですから、セル「A11」から「B12」を選択します。フィールド名も含めて選択します（絶対参照にしましょう）。条件のフィールド名はデータベースのフィールド名と同じでなければなりません。



①選択したデータ範囲の②フィールド（20玉）のデータを③条件範囲（氏名が八戸二郎で品種がつがる）と一致するデータのみ集計する。という意味になります。

Enterキーを押します。



「八戸二郎」さんの「つがる」の「20玉」のデータが集計されました。

となりの「40玉」にもコピーしましょう。



正常に集計されてますね。

条件範囲である氏名の「八戸二郎」を消すと、品種「つがる」のデータすべてを集計してきます。



ということは「品種」を消して氏名の「八戸二郎」だけにすると、「八戸二郎」のすべての入荷数が集計されます。



正しく集計されてますね。

今回条件範囲を「氏名」と「品種」のみにしましたが、データベースのフィールド分作ることができます。入荷日に制限をしたいなど、いろんな条件が想定されますね。

このDSUM関数を使い、等級別集計表を作成します。

DSUM関数で集計～条件設定へつづく

DSUM関数で集計表①～条件設定

DSUM関数を使い集計表を作りたいと思います。

データと集計するシートは別々に作り、データが入力されているシートは「データ」、集計表は「集計」というシート名にしました。

１．「データ」シート


２．「集計」シート　仕入価格表


３．「集計」シート　等級別集計表（数量、価格、金額欄は二つのセルを結合をしています。）


上図の「氏名」と「品種」の欄にそれぞれ入力すると、条件を満たす数量を「データ」シートからDSUM関数で集計し、価格をVLOOKUP関数で表示し、さらに数量および金額の計算もします。

数量の集計から行います。

DSUM関数の引数です。

DSUM（①データベース範囲,②フィールド,③条件範囲）

まずは等級別集計表の等級「優」の20玉から集計します。



ここで考えなければならないのは、③の条件範囲です。

条件は「氏名」「品種」「等級」になります。

「氏名」と「品種」は固定されますので1行の条件で済みます。しかし、等級は「優」「秀」「良」と３つに変化します。

変化する条件を含む場合は変化分の条件を用意する必要があります。

したがって、下図のように「優」「秀」「良」の３つ分の条件範囲を集計表の下に用意しました。



それぞれの等級に合わせて、条件範囲を変えればいいわけです。

ただ、「氏名」と「品種」が変わるたび３つとも修正するのを避けるため、集計表の上「氏名」と「品種」に入力したら条件の「氏名」「品種」が自動で変化するようにしましょう。

条件1の「氏名」のセル「A26」を選択し、「＝」まで入力し、図のように集計表の「氏名」のセル「A9」をクリックします。（絶対参照にしましょう。）



同じく品種の欄も設定します。



残りの条件２と条件３にコピーします。



それでは今回集計に使う「青森太郎」さんの「つがる」をセル「A9」と「B9」にそれぞれ入力してみましょう。



条件の欄すべてに反映されました。

複雑な条件でも一気に解決しようとせず、何段階かに分けて考えると大抵解決できます。

DSUM関数で集計表～数量集計へつづく

DSUM関数で集計表②～数量集計

DSUM関数で集計表～条件設定からのつづき

条件範囲の設定ができましたので、「データ」シートのりんご入荷データより「氏名」「品種」「等級」別の数量を集計します。

以下の３つの表を使います。

１．「データ」シート～りんごの入荷データ


２．「集計」シート　等級別集計表（数量、価格、金額欄は二つのセルを結合をしています。）


３．「集計」シート　条件範囲用エリア


「青森太郎」さんの「つがる」を集計します。集計表の氏名欄、品種欄にそれぞれ入力しておきます。

DSUM関数の引数です。

DSUM（①データベース範囲,②フィールド,③条件範囲）

等級「優」の「20玉」のセル「C11」を選択し、「＝DSUM(」まで入力します。



①データベースの範囲は、「データ」シートですから、シートを切り替えてデータ範囲をドラックします。選択した範囲が点滅します。絶対参照にしましょう。（F4キーを押します。）「,」で区切ります。



「=DSUM(データ!$A$2:$F$10,」のようになります。シートが別々の場合は、選択範囲の前にシート名が表示されます。

次は②フィールドです。「20玉」を集計したいので、シートを「集計」に切り替えて、集計表の「20玉」と入力されたセル「C10」を選択します。
後で「40玉」の欄にコピーすることを考えて、行のみの固定にします。（F4キーを2回押します。※複合参照といいます。）図のように「C$10」となり、行番号の前だけに「＄」マークが付いて行が固定されます。列方向（左右）にコピーした時だけ列名が変化します。「,」で区切ります。



最後に③条件範囲です。

条件設定で作成した、条件エリアから、等級が「優」の条件１の範囲、セル「A25」から「D26」までを選択し、絶対参照にします。



①データ範囲の②フィールド「20玉」を③条件範囲、条件１（氏名「青森太郎」品種「つがる」等級「優」）を満たすものだけ集計するという意味になります。

「）」で閉じてEnterを押します。





「青森太郎」さんの「つがる」等級「優」の「20玉」が集計されました。

どうやら正しいようです。

残りの等級、「20玉」の欄にコピーします。



そして、条件範囲をそれぞれ、等級「秀」は条件２、「良」は条件３の範囲に修正します。（「40玉」欄へのコピーを考えて絶対参照を忘れずに。）



最後に「40玉」の欄にそれぞれの等級ごとにコピーします。





等級別数量集計が完成しました。

１次元のデータを2次元の表に集計することができました。

このような集計に関してはピボットテーブルという機能を使っても出来ます。ただし、この章で行う、別な表から価格などの値を取得するなどの処理はできないので、データをある程度作りこまなければなりません。ピボットテーブルに関しても解説する予定です。

次は価格の欄を埋めて、集計表を完成させます。

DSUM関数で集計表③～集計表を完成させるへつづく・・・・・。



※②フィールドで設定したセルはコピーしたとき列名だけが変化しています。



この表のように条件などの範囲が、行の位置が変化せず、列だけが変化するとか、またはその逆など、行列いずれか一方だけを固定する参照方法を複合参照といいます。

行を固定する場合は「F4」キーを2回、列は3回押します。練習してみてください。

DSUM関数で集計表③～集計表を完成させる

DSUM関数で集計表～数量集計からのつづき

DSUM関数により、1次元の入荷データから等級別の２次元の表に数量を集計しました。

最後は仕入れ価格表から、VLOOKUP関数（※使える関数～VLOOKUP関数を参照）により、品種ごとに価格を表示させる数式を入力し、一気に集計表を完成させたいと思います。

使う表は以下の２つです。

「集計」シート～仕入れ価格表


「集計」シート～等級別集計表（数量、価格、金額欄は二つのセルを結合をしています。）


VLOOKUP関数の引数です。

VLOOKUP（①検索値,②検索に使うデータ範囲,③列番号,④検索方法）

価格を表示させるセル「C12」を選択し、「＝VLOOKUP(」まで入力します。

①検索値は品種になりますので、品種が入力されているセル「B9」をクリックし、絶対参照（F4キーを押す）にします。　「,」で区切ります。



②の検索に使うデータ範囲は、もちろん仕入れ価格表です。ただ、ここでは等級を意識する必要がないので図のように等級の欄を選択せず、ドラックします。（絶対参照にします。）「,」で区切ります。



③列番号は「優」の「20玉」ですから、品種のとなり2列目です。「2」と直接入力します。「,」で区切ります。

後に別の価格セルにコピーしたとき、この列番号のみ修正します。



④検索方法は、完全一致の「FALSE」と入力し「）」で閉じます。



Enterキーを押します。



「つがる」の「20玉」の価格が表示されました。

それでは、他の等級の欄にもコピーし、③列番号をそれぞれの等級及び階級（玉数）の列番号に修正しましょう。（図は秀の「20玉」の欄を修正しています。4列目なので「4」と直接修正します。）



価格の表示が完成しました。



「金額」（数量＊価格）、「計」、「合計」の欄に数式を入れて等級別集計表の完成です。



ちなみに「弘前花子」さんの「ふじ」で確認してみましょう。








入荷データ、価格表を確認して・・・・・。

どうやらOKみたいですね。

果物の精算なんかはこのようなデータや表を使うことが多いと思います。すべてシステム化している企業もあると思いますが、販売方法や精算方法が変わったりすることでなかなかシステム化できない面もありますね。

でも入荷データや販売データが整っていればExcelでも十分対応（ちょっとVBAなんかも使いますが）できます。（私が経験済み）

精算データをExcelで作成し、Wordの差込印刷で精算書を作成し、1000人近いお客さまへ配布しました。

Excel2007なんかは行数100万行まで入力できます。（すごいですね～）

かなりのデータ量も取り扱えるので、創意工夫であまりお金をかけずにがんばりましょう。

使える関数～エラー回避関数①

関数を使い、検索や計算などすると必ずエラーに遭遇します。

関数の使い方に問題あるときは、入力した内容を見直し、修正する必要があります。

困ったのは、正しく使われているのにエラー表示される場合です。

この問題を回避する関数を解説します。

下図のように顧客の表から、顧客№をセル「D2」に入力すると顧客名を表示するVLOOKUP関数（※VLOOKUP関数の回参照）を入力します。





このようにセル「D2」にまだなにも入力されていない状態では関数の式はまったく問題ないのですが、エラーが表示されてしまいます。

見栄えもよくないですし、資料などで提出する際も気になりますよね。

関数はセルに入力した値を元に検索や計算などを行うものが多いので、元になるセルになにも入力されていない場合はなにも表示しないという対策を講じておく必要があります。

そこで使えるのがISBLANK関数です。

これは、対象となるセルにデータが入力されてるか否かを判定する関数です。なにも入力されていなければ（TRUE）、入力されていれば（FALSE）という値を返します。

引数です。

ISBLANK（対象）

この引数の「対象」に検索や計算の元になるセルを入力します。

IF関数と組み合わせて使います。

IF関数の引数は
IF（①条件②条件を満たしたとき「TRUE」,③それ以外の時「FALSE」）です。

①条件の部分にISBLANK関数を当てはめます。

※図を繰クリックすると大きく表示されます。


意味はこうなります。①条件、セル「D2」が空白（BLANK）ならば、②なにも表示しない（「""」は空白の意味です。）、③入力されていればVLOOKUP関数の実行となります。

Enterキーを押します。



エラーが消えましたね。

これを使うと関数が入ったセルを複数行にコピーしても、入力前にエラー表示されることはなくなります。

エラー回避関数②へつづく