[[20110528080650]]　『再計算によるパフォーマンス低下のための改善方法』（せいやん）　ページの最後に飛ぶ

『再計算によるパフォーマンス低下のための改善方法について』（せいやん）

初めて質問いたします。

こちらのサイトを参考に２つの条件に一致する値の合計値を計算するのに

「SUMPRODUCT(($I$2:$I$21=B$2)*($J$2:$J$21=$A4),K$2:$K$21)」

を使ったのですが、これを２０００行×７０列にセット
したためか、データーを入力するたびに再計算が実行され、
パフォーマンスが低下しています。

これを回避するために自動計算を手動計算に変える以外で
パフォーマンス向上の方法はあるのでしょうか？

一部セル（２００行×７０列）にSUMPRODUCT＋SUMPRODUCTと
２つの関数の足し算を入れているのですが、このことが
影響しているのでしょうか？

Excel2000,WindowsXPを使用しています。
よろしくお願い致します。

 質問の主旨が理解できません。

 範囲、I2:K21　はどのようになっているのでしょう
 I列、J列に重複データがあるのでしょうか?

 >データーを入力するたびに再計算が実行され、
 再計算されると言うことは、範囲、I2:K21　のデータをたびたび変更すると言うことですか?

 他に原因がありそうな気がしますが・・・

 >これを２０００行×７０列にセット
 どの範囲に入力しているのでしょう
 提示された式をコピーしているとは思えませんが・・・

 まず、原因を調べた方がいいかな(提示された式が原因と思われませんので)

 編集　10:25
 >データーを入力するたびに再計算が実行され 
 揮発性の関数が使用されていると思われます

 表全体について説明した方がいいと思います

 考え方だけアドバイス

 リアルタイムに集計結果が必要で、且つ、速さを求めるのであれば、
 作業列に各行個別の計算値を出しておいて、その作業列を単純にSUMする
 という仕組みの方が、
 普通に考えて再計算に必要なコストは最小で済みます。

 セルの消費を抑えるのか、
 計算コストを抑えるのか、
 優先すべきはどっちでしょうか?
 ということ。(DB管理でINDEXを貼るか悩むのに似ている・・・かな)

 リアルタイムに結果が必要なければ、
 私は、
 1000行単位のデータを相手にするときは、
 配列関数や配列数式を使用する場合でなかったとしても、
 １行目だけ数式を残して、後は数値化し、
 最新の計算結果が欲しくなったらフィルダウンして再度数値化。
 というやり方をしています。

 都度フィルダウンが困難な場合(レイアウトが複雑とか)なら、

 例えば、
 SUMIFを大量に配置しており、その元データが定期的に変更される場合などは、
 一つのセルをSUMIF発動スイッチとして定義して、
 =IF(発動スイッチ,SUMIF(...),)
 という数式にしておけば、
 発動スイッチがFALSEの間は、SUMIF再計算にコストは掛りませんので、
 元データの編集がスムーズに行えます。

 (白茶)

早速のご回答ありがとうございます。
また、質問が抽象的ですみませんでした。

①今やりたいこと

下記のような複数のＤＢ（品名、日付の項目はすべて
共通で持っていますが、数量の部分がＤＢ①、ＤＢ②では一致していません）
から下記のような品名、日付の一致するもののマトリックスを
つくりたいということです。
＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
　　　　　　　　　　　　　　5/1　5/2（←日付です）
　－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－
　品名　　条件　　

 　a　　ＤＢ①より　　　　　　 3　　4（←DB①の中で一致するものの合計量です）
 　a    ＤＢ②より　　 　　　　5    6（←DB②の中で一致するものの合計量です）
 　a    ＤＢ②＋ＤＢ③より　　 6    5（←DB②とDB③の中で一致するものの合計量です）
　 b    ＤＢ①より
 　b    ＤＢ②より
　 ・      ・
　 ・      ・
＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
１つのDBですとピポットテーブルを使えばいいと思うのですが、
複数のDBから共通である品名、日付の部分を使って、１つの
ピポットテーブルをつくりたいといったイメージです。

②実際やったこと
　ＤＢは実際はすべてで４つありますが、上記表、以下の説明では
　簡略化のためＤＢ３つで説明します。
　
　まず、Ａ列に品名（品名数：２０００種）、１行目に
　日付（とりあえず5/1～7/10）のマトリックス表を作り、
　品名：a、条件：ＤＢ①の部分は前述のSUMPRODUCT関数を使って
　日付と品名の一致する時の数式をセットしました。
　また品名：a、条件：ＤＢ②＋ＤＢ③の部分は
　（SUMPRODUCT関数＋SUMPRODUCT関数）の数式をセットしました。
　なお、ＤＢ①、②、③ともに日々更新していきます。

③結果
　処理自体はうまくいったのですが、自動計算の設定にしておくと
　作業ごとになぜか再計算の処理が発生し、砂時計の待ちの状態に
　なってしまいます。
　
この処理の時間をなんとか早くしたいというのが今回
ご相談したいことです。

今まで複雑な関数を使うことがなく、色々調べながら作業をしており、
ご相談の趣旨が本当にわかりにくくてすみませんでした。
（マトリックス表の表現がうまくいかないかもしれませんが、すみません
　日付のあとの－－－は罫線のつもりです。）

よろしくお願い致します。

 それは確かに重いですね・・・

 「ピボット使えるのならその方が良い」とお考えでしたら、
 4っつのDBをテーブルID付きの1テーブルに縦継ぎし、
 そいつに対してピボットかける。

 という方向ではダメでしょうか?

 現状、SUMPRODUCT使ってるってことは、
 各DBには集計上のデータ数上限があるってことなので、
 4っつのDBを縦に継ぎ足したテーブルは、容易に作成可能と思いますが?
 (テーブルID列作って、元データから「=」で参照するだけ)

 「DBを編集する度に再計算待ちになるのがイヤなだけで、
 最終的な結果を計算する時はいくらでも待ちますよ」
 という事であれば、
 先程申しました発動スイッチで逃げれると思います。

 (白茶)

ご回答ありがとうございます。４つのテーブルを
縦つぎする方法は思いつきませんでした。
なんとかうまくいきそうです。
ありがとうございました。

コメント返信：

[ 一覧(最新更新順) ]

[[20110528080650]] 『再計算によるパフォーマンス低下のための改善方法』（せいやん） ページの最後に飛ぶ

[[20110528080650]]　『再計算によるパフォーマンス低下のための改善方法』（せいやん）　ページの最後に飛ぶ