Excel VBA質問箱 IV

当質問箱は、有志のボランティア精神のおかげで成り立っています。
問題が解決したら、必ずお礼をしましょうね。
本サイトの基本方針をまとめました。こちら をご一読ください。

投稿種別の選択が必要です。ご注意ください。
迷惑投稿防止のため、URLの入力を制限しています。ご了承ください。


7792 / 13644 ツリー ←次へ | 前へ→

【36798】累積計算を高速で行うには? カド 06/4/14(金) 8:53 質問[未読]
【36801】Re:累積計算を高速で行うには? ごんぼほり 06/4/14(金) 10:05 回答[未読]
【36802】Re:累積計算を高速で行うには? ごんぼほり 06/4/14(金) 10:16 発言[未読]
【36813】Re:累積計算を高速で行うには? Ned 06/4/14(金) 15:23 発言[未読]
【36821】Re:累積計算を高速で行うには? カド 06/4/14(金) 17:29 お礼[未読]
【36824】Re:累積計算を高速で行うには? Ned 06/4/14(金) 18:09 発言[未読]
【36841】Re:累積計算を高速で行うには? Ned 06/4/15(土) 1:16 発言[未読]
【36842】Re:累積計算を高速で行うには? カド 06/4/15(土) 8:17 お礼[未読]
【36846】Re:累積計算を高速で行うには? Ned 06/4/15(土) 12:50 発言[未読]

【36798】累積計算を高速で行うには?
質問  カド  - 06/4/14(金) 8:53 -

引用なし
パスワード
   A列に品番が有り、B列にその品番の売り上げ個数があります。
A列の品番は同じ品番が何度もランダムに存在します。
D列には売り上げを集計したい品番があります。
E列に集計を書き込むマクロ作りたいのですが。

私の場合、普段offsetを使ってセルを移動し処理するため、データ数が5万個もあると数分程度の時間が掛かってしまいます。

どうすればいいかコードで示していただけると助かります。


A   B    C    D    E 
あ  1        あ   3    
う  3        い   2
い  2        う   3
あ  2

【36801】Re:累積計算を高速で行うには?
回答  ごんぼほり  - 06/4/14(金) 10:05 -

引用なし
パスワード
   おはようございます。

ワークシート関数でできると思うんですが、これじゃだめですか?

Range(Cells(1, 4), Cells(Rows.Count, 4).End(xlUp)).Offset(, 1).FormulaR1C1 = "=SUMIF(C1,RC[-1],C2)"

【36802】Re:累積計算を高速で行うには?
発言  ごんぼほり  - 06/4/14(金) 10:16 -

引用なし
パスワード
   > Range(Cells(1, 4), Cells(Rows.Count, 4).End(xlUp)).Offset(, 1).FormulaR1C1 = "=SUMIF(C1,RC[-1],C2)"
この方法はD列のデータが多いとおそいですね。
他のスレッドみてなかったので、すみません。
わすれてください。

【36813】Re:累積計算を高速で行うには?
発言  Ned  - 06/4/14(金) 15:23 -

引用なし
パスワード
   こんにちは。
 
【36764】のコードを少し修正して流用すると、下記のような感じです。

Sub sample2()
  Dim a, d, di, x
  Dim r As Range
  Dim Dic As Object
  Dim i As Long, j As Long
  Dim n As Long
  Const c As Long = -3
  Set r = Range("d1")
  With r
    n = .End(xlDown).Row
    a = .Offset(, c).Resize(.Offset(, c).End(xlDown).Row, 2).Value
    d = .Resize(n).Value
  End With
  ReDim x(1 To n, 1 To 1)
  Set Dic = CreateObject("Scripting.Dictionary")
  For i = 1 To UBound(a)
    If Dic.exists(a(i, 1)) Then
      Dic(a(i, 1)) = Dic(a(i, 1)) + a(i, 2)
    Else
      Dic(a(i, 1)) = a(i, 2)
    End If
  Next i
  For Each di In d
    j = j + 1
    If Dic.exists(di) Then x(j, 1) = Dic.Item(di)
  Next di
  r.Offset(, 1).Resize(n).Value = x
  Set Dic = Nothing
  Set r = Nothing
End Sub

また、スレッドは違いますが、検索対象列に重複の値がある場合、
【36764】のコードは重複する最下行の値を持ってきますから
重複する最上行の値を持ってくる場合、(【36749】と同じ仕様)
>Dic(a(i, 1)) = a(i, 2)
この箇所を
If Not Dic.exists(a(i, 1)) Then Dic(a(i, 1)) = a(i, 2)
と変更してください。

【36821】Re:累積計算を高速で行うには?
お礼  カド  - 06/4/14(金) 17:29 -

引用なし
パスワード
   ▼Ned さん いつも回答ありがとうございます。

質問後に昔自分で作ったコードがあることに気が付いて試してみたら、見事にフリーズしてしました。

今回の質問は累積なので全データを総当りで調べる必要があると思うのですが、やはり1秒程度で処理が終了してしまいます。すごい。

また、コードの一部を以下のように訂正しました。
これでも正しく動くと思いますが、なにか勘違いしてるでしょうか?

>  For i = 1 To UBound(a)
>    If Dic.exists(a(i, 1)) Then  ・・・不要
>      Dic(a(i, 1)) = Dic(a(i, 1)) + a(i, 2)
>    Else   ・・・不要
>      Dic(a(i, 1)) = a(i, 2)  ・・・不要
>    End If    ・・・不要
>  Next i

【36824】Re:累積計算を高速で行うには?
発言  Ned  - 06/4/14(金) 18:09 -

引用なし
パスワード
   >コードの一部を以下のように訂正しました。
あ、そうですね。その通りです。失礼しました。^ ^;

【36841】Re:累積計算を高速で行うには?
発言  Ned  - 06/4/15(土) 1:16 -

引用なし
パスワード
   あと、また蛇足ですが、今回のようなケースは、
1行目に項目行を挿入してピボットテーブルを使う。
…というのが簡単でわかり易いような気もします。
必要に応じてマクロ化すれば良いとも思いますし。
データ処理の手法として、幅が広がるので個人的にはおすすめです。^ ^

【36842】Re:累積計算を高速で行うには?
お礼  カド  - 06/4/15(土) 8:17 -

引用なし
パスワード
   ▼Ned さん こんにちは いつもお世話になります。

最近データベースなるものに手を付け始めました。
これまで扱っていたものに対し、データ量が膨大でなかなか大変だと
痛感しています。

当然、ピボットテーブルやACCESSなどにも自然に興味が湧いて来ますが、
覚えることが多くて大変です。

今回の質問は、かなり複雑な処理を行う中の、ほんの一つの処理にすぎないので
やはりマクロでやりきってしまおうと考えています。

話は変わりますが、今回の処理は結局5万*5万回=25億回の処理を
行う事になりますが、これが1秒で出来るとは本当にすごいですね。

【36846】Re:累積計算を高速で行うには?
発言  Ned  - 06/4/15(土) 12:50 -

引用なし
パスワード
   こんにちは。
>データ量が膨大
という事ですので、Excelにも限界はあると思いますよ。
早目にAccessや他のデータベースソフトに切り替えられた方がいいかもしれませんね。

>今回の処理は結局5万*5万回=25億回の処理
んー…Dictionaryの内部的にどういう処理が行われているのか知りませんが、
5万×5万ではないような気がします。
この辺り詳しい方がいらっしゃいましたら、レスがあるかもしれません。

>Dic(a(i, 1)) = Dic(a(i, 1)) + a(i, 2)
>If Dic.exists(di) Then x(j, 1) = Dic.Item(di)
この時にどういう処理がされているかですね。
少なくとも、総当りではないでしょう。
Keyを割り当てながら加算していき、
取り出す時に該当Keyを探しに行っている処理ですから。
この時は、DictionaryのKey(50000)→Key(1)の順に探しに行っているようです。
(仮にKey(1)を50000回取り出しても、それほど時間はかかりませんが)
重複値を検査する時にexistsメソッドは高速で便利なのは確かですね。

7792 / 13644 ツリー ←次へ | 前へ→
ページ:  ┃  記事番号:
2610219
(SS)C-BOARD v3.8 is Free