多量のSUMIF式を軽くしたい

質問

お世話になっております。
今回、7000行～10000行程度のデータを集計するEXCELシートを作成しています。

元データは
取引先CD　社名　分類CD　分類名　売上　粗利　在庫
001　　　　A社　001　　　A　　　1200　120　　1440
001　　　　A社　002　　　B　　　1000　100　　1200
001　　　　A社　003　　　C　　　2000　200　　2400
001　　　　A社　004　　　D　　　 300　 30　　 360
001　　　　A社　005　　　E　　　1500　150　　1800
001　　　　A社　006　　　F　　　 200　 20　　 240
002　　　　B社　002　　　B　　　5000　500　　6000
002　　　　B社　004　　　D　　　　10　　1　　　12
003　　　　C社　006　　　F　　　2200　220　　2640
005　　　　E社　005　　　E　　　 100 　10　　 120
のように会社別・分類別の売上・利益・在庫データです。

通常であれば、集計表を作るにはSUMIF関数で十分なのですが、
実際のデータは数千行あり、また項目ももっと多岐にわたるため、
再計算に時間がかかってしまいます。
（Celeron1.2Ghz、メモリ512MB位のノートPCで再計算に２～３分要します）

SUMIF以外の方法で処理を軽くすることはできないでしょうか。
VBAを使って軽くなるのであればVBAでもかまいません。

ちなみに元データをいじることはできません。
（定型でシステムから出力されるデータであるため）

申し訳ありませんが、ご教示いただければ助かります。

回答No.3

hallo-2007 · ベストアンサー

No1です。
もしデータが取引先CD　或いは　社名順ならば
=SUM(INDEX(E:E,MATCH("001",A:A)):INDEX(E:E,MATCH("001",A:A,FALSE)))
といった関数が使えます。
SUM関数ですが範囲をMACTH関数で指定するものです。
MATCH関数で、最後にFALSEを指定するかしないかで、最初の行と最後の行が指定できます。
昇順に並んでいる事が条件です。SUMIF関数とは違って配列を使用しないので、非常に軽くなります。

お礼

具体的な解決策をありがとうございます！
元データを貼り付ける手順をVBAで組んでいますので、
その時に取引先CD順にソートするようにしてみます。
ぜひ試してみたいと思います。
ありがとうございます！

回答No.5

Wendy02

こんばんは。

数千行程度で、そんなに遅くなるはずはないので、何か別の要因があるような気がしますが、もともと、そのようなために、データベース関数が昔作られました。データベース関数は、計算データの取得の仕方が違いますから、列をフルに使っても大丈夫なように作られています。ただ、使い方の細かな説明が、Microsoft サポートだけにしか出ていないような気がします。

なお、配列数式では、SUMIFよりは遅いはずです。

お礼

お恥ずかしい話、データベース関数は今まで使ったことがありません。
DSUMなどを試してみたいと思います。
ありがとうございます。

回答No.4

imogasi

こういう
データが多量
仕事関係（即時性が要求される）
正確が要求される課題（関数、ＶＢＡは自作すると、データが多い場合、なかなかほぼ間違いないとはいえないものだ）
などの場合は、出来るなら、専用にプログラムが組まれている
ピボットテーブルを使うべきです。
たとえれば「カンナとのみ」（関数）と木工機械（特別ツール）とぐらいの差があると思います。道具を選ばないと。
ーー
ＶＢＡなら
会社＋分類別にソートし（どれぐらい時間がかかるかな？）たら、他に、初めから終わりまで１通り読む時間で集計できます。
ロジックとしては、バッチ処理の定石なんですが、ここにコードを書くことは、課題丸投げのコードを作ってくださいに応じることになるので、省略します。

お礼

おっしゃる通り、道具は選ぶべきですが、
現状の使い方を考えると関数で組むことがベストかと思いました。

VBAで集計処理を入れることも検討してみたいと思います。
まだまだVBAは勉強中なので、今後の課題とさせていただきます。

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

回答No.2

sige1701

>会社別・分類別の売上・利益・在庫データです。

>通常であれば、集計表を作るにはSUMIF関数で十分なのですが、
>実際のデータは数千行あり、また項目ももっと多岐にわたるため
どのように集計しているのでしょう?
どのような式になっているのでしょう?
(SUMIF関数で出来ているのかな)

現状がわからないので
>SUMIF以外の方法で処理を軽くすることはできないでしょうか。
についての回答もしづらいですね

お礼

説明が悪くて申し訳ありません。

実際には元データは２シートあります。
当年と前年のデータです。

これらを合わせて複数のシートに集計したいのです。
シートは会社別となっており、
縦に分類、横に売上・粗利・在庫　がそれぞれ当年・前年と来る形です。

集計にはすべてSUMIFを使っておりますが、
１つのシートに1,000個以上のSUMIF関数が入っており、
さらにこのシートが会社別に複数あると、
すさまじい量の計算になります。

これを少しでも早くしたいのですが、
なにか良い方法はないでしょうか。

回答No.1

hallo-2007

ピボットテーブルは試して見られましたか、
データ＝＞ピボットテーブルと・・・　で新規シートに作成
楯列に　社名　横列に　分類名　内側に　売上　でひとつのピボット作成出来ます。
上記の方法で、粗利　在庫　も同様に作成します。

お礼

ご回答ありがとうございます。
ピボットテーブルも考えましたが、
今回は関数とマクロだけで実現したいと考えています。