ここから本文です

vba マクロ で3万行ある3シートから別シートに12000種類ある行へ高速でカウントす...

god_hands_ktさん

2009/7/1601:59:34

vba マクロ で3万行ある3シートから別シートに12000種類ある行へ高速でカウントする方法を教えてください。

シート写真あります。

まず写真をご覧ください。
結果1~3のシートがありそれぞれ3万行近くあります。
一番右のシートのB列(12000行)の件数欄に
A列の識別CDを結果1~3の識別CD行から同じ識別CDを
カウントしたいのです。
Countifでやるとかなり時間がかかります。
これを高速でやる方法はございますか?
変数でもなんでもかまいません。

とにかく高速に、あっという間に終わる方法を教えてください。
長いプログラムの中の一部なのですがここでかなり時間かかってしまいます(4から5分)。。(涙)
(PC自体もcerelon233とショボイですが、会社のパソコンなので文句言えません。自宅のPCコアクアッド8個のCPUだとすぐ終わるのですが・・・)

色々調べたのですがまだまだ勉強不足でいい方法が見つかりません。
VBAに精通された方、ご教授をお願いいたします。

COUNTIF,cerelon233,GETPIVOTDATA,識別CD,Sheet4,識別CD&quot,VBA

閲覧数:
2,777
回答数:
2
お礼:
50枚

違反報告

ベストアンサーに選ばれた回答

アバター

ID非公開さん

編集あり2009/7/1809:17:40

もう1枚シートを作るか、集計シートにピボットテーブルを(各結果シートの集計用に)3つ作り、GETPIVOTDATA関数で、それぞれのピボットテーブルの値を取得して、それの合計を計算することはできませんか?
10秒で終わるかどうかは分かりませんが、COUNTIFなんか使うよりは、はるかに高速だと思います。


補足

実際のパフォーマンスに興味があったので、tabun_kamone_no_goikenさんの連想配列を使う方法を含め、次のような方法で検証してみました。
1)Sheet1~Sheet4の4枚のワークシートを用意
2)Sheet1~Sheet3のC3セルに「識別CD」と入力
3)Sheet1~Sheet3のC4:C10003、C10004:C20003、C20004:C30003に、1~10000の順列を入力
4)Sheet4のA1に「識別CD」と入力
5)Sheet4のA2:A10001に1~10000の順列を入力
以上の手順で作ったファイルを基本として実験。

同じようなパソコンが用意できなかったので、Pentium4 3.00GHz 1GByte RAM
Windows XP SP3 / Excel 2003 の環境でテストしました。
Cerelon233 よりかなり速いはずなのですが、COUNTIFで3分強かかったので、対象のパソコンの2倍程度のパフォーマンスしか出ていないと考えられます。(常駐プログラムが多いせいでしょうか?いくらなんでも時間がかかりすぎている気がするんですが。)


実験1 COUNTIFによる方法

Sheet4 の B2 セルに
=COUNTIF(Sheet1!$C$4:$C$30003,A2)+COUNTIF(Sheet2!$C$4:$C$30003,A2)+COUNTIF(Sheet3!$C$4:$C$30003,A2)
という数式を入力し、これをB10001セルまでコピーして再計算を実行。

計測結果196.87757 秒(時間がかかるのでこの実験のみ1回の測定)
やはりCOUNTIF関数が低速で、非常に時間がかかります。


実験2 ピボットテーブルによる方法

Sheet1~Sheet3の表のピボットテーブルを Sheet4上に作成。
具体的には それぞれSheet4のD1,F1,H1セル以下の位置に、データの個数をカウントするピボットテーブルを作成。
Sheet4のB2セルに
=GETPIVOTDATA("識別CD",$D$1,"識別CD",A2)+GETPIVOTDATA("識別CD",$F$1,"識別CD",A2)+GETPIVOTDATA("識別CD",$H$1,"識別CD",A2)
という数式を入力(古いバージョンのExcelではたしかGETPIVOTDATA関数の使い方が違ったので注意)し、これをB10001セルまでコピーして、ピボットテーブルの更新と再計算を実行。

計測結果 14.98243~17.28934秒(5回計測。平均16.06535秒)
ピボットテーブルの更新自体は一瞬で終わるのですが、GETPIVOTDATA関数に時間がかかり、思ったほどパフォーマンスが出ません。それでも COUNTIFによる計算に比べると10倍以上高速です。


実験3 連想配列による方法(VBA)

Sub CD集計()
Dim I As Long, Y As Long
Dim key As String
Set dicT = CreateObject("Scripting.Dictionary")
For I = 1 To 3
With Sheets(I)
For Y = 4 To 30003
key = .Cells(Y, 3).Text
dicT(key) = dicT(key) + 1
Next
End With
Next
With Worksheets("Sheet4")
For I = 2 To 10001
.Cells(I, 2) = dicT(.Cells(I, 1).Text)
Next
End With
End Sub
というプログラムを用意して、これにかかる時間を計測。

計測結果 4.41569~4.68683秒(5回計測。平均 4.581302秒)
やはり高速で5秒を切る時間で計算終了。COUNTIFによる方法より40倍以上高速です。
一瞬とはいきませんが、低スペックのパソコンでも我慢できる時間で計算できるのではないかと思われます。

この質問は投票によってベストアンサーに選ばれました!

ベストアンサー以外の回答

1〜1件/1件中

2009/7/1613:31:42

連想配列を使用してみてはいかがでしょう。
http://www.niji.or.jp/home/toru/notes/17.html

私の環境でのテストでは
ワークシートにCountifを入れてコピー --> 約9.5分
配列を使用したVBAでカウント --> 約15秒

データは1万x2シート4千X2シートの4シート
処理対象は12千


参考までに。。。

みんなで作る知恵袋 悩みや疑問、なんでも気軽にきいちゃおう!

Q&Aをキーワードで検索:

Yahoo! JAPANは、回答に記載された内容の信ぴょう性、正確性を保証しておりません。
お客様自身の責任と判断で、ご利用ください。
本文はここまでです このページの先頭へ

「追加する」ボタンを押してください。

閉じる

※知恵コレクションに追加された質問や知恵ノートは選択されたID/ニックネームのMy知恵袋で確認できます。