Chiquilin Site■06.10.24_Excel：データベース関数で合計・カウントする
ChiquilinSite
★その１：データベース関数の基本
まず合計ではなく検索をしたい場合は
データベース関数で検索する をご参照下さい。あとデータベース関数以外での方法については
複数条件での合計・カウント に掲載しています。
デーデータベース関数で合計を求める場合は DSUM関数。カウントする場合は DCOUNT関数か DCOUNTA関数を使います。データベース関数の使い方は
フィルタオプションの設定と似ていて 検索条件式(Criteria)を 別のセル範囲に用意することで計算させます。下準備が必要ですから 単純に条件がひとつの計算をする時には
SUMIF関数や COUNTIF関数を使ってもいいかもしれません。
■Ｈ３セル
=DSUM(B2:D12,H2,F2:G4)
※件数を数える時は DCOUNT関数を使います。
この場合の条件(F2:G4セル)が何を表しているかというと
「日付が『10月1日』」且つ「果物が『りんご』」
または
「日付が『10月4日』」且つ「果物が『みかん』」
つまりデータベース関数の条件式は「
縦方向に OR条件」「
横方向に ＡＮＤ条件」となります。
この時 注意して欲しいのは「
Excel2002以外は 検索文字列は前方一致になる」ということです。Ｇ３セルに「りんご」と入力した場合 「りんご」は勿論「りんご酒」や「りんごあめ」も一致していると判断されます。ただし「青りんご」は一致しません。あくまで前方からみて部分一致する文字列だけが引っかかります。
これを完全一致にしたい時は「="=りんご"」と「=」記号が入った状態で表示させなければなりません。逆に「青りんご」も検索対象にしたい時は「*りんご*」と前後にワイルドカード文字「*」を挟む必要があります。「=」以外の演算子の場合は
普通に前に付けるだけでそのまま使えます。例えば「>=10/2」とすれば「『10月2日』以上」が対象になります。
★その２：条件式を数式で参照する方法
条件式(Criteria)は数式でも対応できます。見た目がすっきりするので結構便利です。
■Ｆ１６セル
=AND(B16>="10/3"*1,OR(C16={"りんご","みかん"}))
■Ｇ１６セル
=DSUM(B15:D25,G15,F15:F16)
条件は「日付が10月3日以上」で「果物が『りんご』か『みかん』」になります。数式で対応する場合の注意点は「比較するセルは タイトル行の下の先頭セル」「日付を数値化する」ということです。この場合
検索範囲は Ｂ１５:Ｄ２５セルですから １６行目が先頭セルということになります。
ここまでくると「データベース関数ってすごく便利」と思われるかもしれません。実際 SUMIF関数や COUNTIF関数と比較しても非常に計算が速いので
大量のデータでも処理に時間が掛かりません。ところがそんなデータベース関数には以外と使い勝手の悪いところがあります。
その訳は 「複数条件での合計」を 複数のパターンに使いづらいところです。「その１」で書きましたが データベース関数の条件範囲は 下方向に書かれたデータは
ＯＲ条件と判断されます。
■Ｈ３セル
=DSUM(B2:D12,H2,F2:G3)
■Ｈ５セル
=DSUM(B2:D12,H4,F4:G5)
つまり 複数のパターンで合計を求めたい場合は タイトル行を毎回挟む必要があるのです。そうすると複数条件で検索したい品目が大量にあると 非常に間延びした表になってしまいます。
これは数式で対応させた場合にも同じことが云えます。
本当は Ｆ１７セルの数式が「=ＡＮＤ（B 16>=G$17,C16=H$17)」にならなければいけないのですが 参照形式の都合上 どうやっても検査範囲がずれてしまいます。この点が
データベース関数が いまいち普及していない理由のひとつだと思います。これを回避する為にはいくつかの方法があります。
★その３：複数条件の合計１(検査値の組み合わせに重複がない場合)
一つ目は ＯＲ条件を逆手に取る方法です。
■Ｈ３セル
=DSUM(B$2:D$12,H$2,F$2:G3)-SUM(H$2:H2)
下方向にオートフィルコピー
つまり下方向にコピーするにつれ 延々と条件範囲を広げてしまう方法です。あくまで ＯＲ条件ですから 条件が増えれば増えるだけ 計算結果の数量も増えていきます。しかし
前行までに計算した Ｈ列の合計をそこから引いてやれば 最終行の条件範囲の結果だけが計算されるという仕組みです。この方法なら タイトル行を付けなくても下方向にオートフィルコピーして計算させることができます。ただし……
・条件にダブりが出てくる(『10月1日』の『りんご』が 何度か出てくるなど)
・ＯＲ条件の計算(検査範囲が複数行に渡る計算)
・DSUM・DCOUNT・DCOUNTA関数以外のデータベース関数
だと計算できません。
★その４：複数条件の合計２(検査値の組み合わせに重複がある場合)
画像はクリックすると初期状態に戻せます
二つ目の方法です。こっちは若干下準備が必要になります。
■Ｅ２セル
="=(B3=F$"&ROW()&")*(C3=G$"&ROW()&")"
Ｅ４セルまでオートフィルコピー
Ｅ２セルをはずして Ｅ３セルから 最終行まで選択
そのまま 右クリックして[形式を選択して貼り付け]→[値]を選択して[OK]
そのまま [データ]→[区切り位置]→[完了]
→変更を画像で確認(クリックすると上の画像が切り替わります)
■Ｈ３セル
=DSUM(B$2:D$12,H$2,E2:E3)
下方向にオートフィルコピー
これだと DSUM関数に限らず 他のデータベース関数でも使えます。
★その５：複数条件の合計３(検査値の組み合わせに重複がある場合)
三つ目の方法です(10.05.20 追加)。随分前に思いついたのに アップしてませんでした＾＾； 作業用シートを作成しておけば ややこしいこと考えなくても 列の数だけ条件式を 縦方向にコピーする方法があります。
■条件シートの Ａ１
=COLUMN()
■条件シートの Ａ２セル
=AND(
OR(INDEX(重複あり２!$F:$F,2+A$1)="",
INDEX(重複あり２!$F:$F,2+A$1)=重複あり２!$B3),
OR(INDEX(重複あり２!$G:$G,2+A$1)="",
INDEX(重複あり２!$G:$G,2+A$1)=重複あり２!$C3))
で Ａ１:Ａ２セルを選択して 右方向にオートフィルコピー
とこのように条件式を 別シートに作成しておくと
■Ｈ３セル
=DSUM(B$2:D$12,D$2,INDEX(条件シート!$1:$2,,ROW(H1)))
下方向にオートフィルコピー
とできてしまいます。若干面倒なので お勧めする訳ではありませんが 一度準備さえしておけば 後は快適に利用できると思います。
→参考ファイル(026.xls)
