DSUM関数について

質問

数式の計算がかなり重くなったので、質問させていただきます。

{=SUM(IF(("日付条件"$R$6:$R$10000>=$A$148)*("日付条件"$R$6:$R$10000<=$A$149)*("検索条件1"$Q$6:$Q$10000=A151)*("検索条件2"$M$6:$M$10000>0),"合計範囲"$O$6:$O$10000,""))}
配列数式で上記のような数式が大量にセルにあります。
日付で範囲を指定し、検索条件1で合計させています。
DSUM関数で可能だと思いましたが、数式の条件をうまく指定出来ません。
A列に検索条件1のデータが30個程入力されています。
日付範囲のデータは適当なセルに入力してあります。

上記の数式をDSUM関数にした場合、どのような数式になるでしょうか？
ちなみにDSUM関数で処理した場合、動作は軽くなるでしょうか？

どなたかご教授ください。

回答No.1

某HN クロメート(Chromate)（@CoalTar） · ベストアンサー

まず、"日付条件"、"検索条件1"、"検索条件2"、"合計範囲"
となっていますが、シート名のことでしょうか？
具体例など挙げてください

とりあえず回答してみます
=($R$6>=$A$148)*($R$6<=$A$149)*($Q$6=A151)*($M$6>0)*$O$6:$O$10000
を6行目の作業セルに貼り付け
10000行まで下へオートフィル
合計を出す。
計算速度はぜんぜん違います。

回答No.4

Wendy02

こんにちは。

>DSUMの使い勝手は思ったより悪いですね。

これらの関数が出現した理由はあるのですが、近年、あまり使われなくなってしまいました。SUMPRODUCTは、この場合は、配列を引数としますから、現在の数式と同じような問題が発生するはずです。

ここでは、SUMIF を利用するのか一番かと思います。

言い換えると、DSUM 関数のクライテリアになる数式を

=AND(R6>=$A$148,R6<=$A$149,Q6=$A$151,M6>0)

空いた列に、データの最後の行までコピーして、SUMIF の条件に、TRUE, FALSE の出ている列を代入して、計算してあげます。

=SUMIF(数式の範囲,TRUE,合計する範囲)

とすればよいと思います。VBAのユーザー定義関数を作ったとしても、配列数式よりも、10倍ぐらい計算は遅いです。

回答No.3

Wendy02

こんばんは。

ご質問の配列数式は、Excel2003でも、Excel 2007 でも、無理があります。

データがあちこちあると、数式をまとめにくくなりますが、DSUM を使うということは、フィールド行（タイトル行）が必要です。

M$6:$R$10000 これが、データの範囲なら、これに、その手前の行、つまり5行目にフィールド行を付けて、その範囲「M$5:$R$10000」を、「Database」という名前登録をしてください。

=DSUM(Database,"フィールド名",B1:B2) 'フィールド名は、任意
クライテリア部分は、1行目を空欄にするのがコツです。

クライテリア部分(B1:B2)は、

B1 は、空欄
B2 に　以下のような数式を入れます。
=AND(R6>=$A$148,R6<=$A$149,Q6=$A$151,M6>0)

A151 は、絶対参照にしてください。相対参照では、正しい答えが出ないはずです。この数式は、非常に微妙な部分がありますから、規則正しく入力しないと、うまく出せません。A151 の部分が、相対参照になるという根拠があるなら、この限りではありません。

なお、この種のものは、オートフィルタやフィルタオプションを使う方法もあるかと思います。

お礼

Wendy02さん

ご回答ありがとうございます。
ご指摘の通り作成して、動作確認できました。
DSUMの使い勝手は思ったより悪いですね。
SUMIFとSUMPRODUCTの計算スピード(使い方にもよると思いますが)
あまり変わらないものなのでしょうか？

回答No.2

某HN クロメート(Chromate)（@CoalTar）

No.1です
ちょっと修正
=($R$6>=$A$148)*($R$6<=$A$149)*($Q$6=A151)*($M$6>0)*$O$6

>上記のような数式が大量にセルにあります。
どのくらいあるのでしょう？

補足

ご回答ありがとうございます。

数式がわかりづらくてすみません。
sheet2にある数式でsheet1を参照しています。
"日付条件"、"検索条件1"、"検索条件2"、"合計範囲"
は検索条件です。

{=SUM(IF((sheet1!$R$6:$R$10000>=$A$148)*(sheet1!$R$6:$R$10000<=$A$149)*(sheet1!$Q$6:$Q$10000=A151)*(sheet1!$M$6:$M$10000>0),sheet1!$O$6:$O$10000,""))}
数式はこのような状態です。