excel vba 複数項目の集計

EXCEL2003　VBAにてマクロを作成しているのですが、作成日程が迫っているにも関わらず、
すぐ行き詰ってしまいます。どうかご指導お願い致します。

以下のような事をしたいのですが、できる限り高速で最も効率の良い方法を教えていただきたいと
思っております。よろしくお願い致します。

あるSheetに以下のようにデータが登録されていて、
A　　　　　　　B　　　　　　　　C
りんご　　　　赤　　　　　　　200
なし　　　　　黄色　　　　　　100
りんご　　　　緑　　　　　　　100
メロン　　　　緑　　　　　　　10
なし　　　　　黄色　　　　　　200
りんご　　　　緑　　　　　　　500

これを集計すると、
りんご　　赤　　200
りんご　　緑　　600
なし　　　黄色　300
メロン　　緑　　10
と、結果を返したいと考えています。

数字を合計するのはA列とB列がともに一致した時のみです。

最初は単純に何回もForNextで処理しようと思ったのですが、行数が
多くなると繰り返し回数も多くなり、時間もかかってしまうので、
別の方法でもっといい方法があればと思い、質問させていただきました。
宜しくお願いします。

No.1 回答者： kmetu 回答日時： 2011/01/10 13:39

VBAではありませんが

オートフィルターを使いA列とB列の重複を除去したデータをたとえばF列とG列に出します。

フィルタオプションの設定で(例示のデータの場合)

指定した範囲にチェック　
リスト範囲　　 $A$1:$B$6　(品名と色の範囲のセルを選択します)　
抽出範囲　　　$F$1:$G$1　(重複を除いたデータを出力したいセルの先頭を指定します)
重複するレコードは無視するにチェック


として集計を出したいセルに以下の式を入力します

=SUMPRODUCT(($A$1:$A$6=F1)*($B$1:$B$6=G1)*($C$1:$C$6))

VBAで行いたい場合にはこの動作を記録しておけばいかがでしょう。

この回答へのお礼

ご回答有難う御座いました。
自分はあまりSheet上で関数を使用したことがないので、
何でもマクロ上で処理しようとしていました。
回答してくださったアプローチも検討したいと思います。
有難う御座いました。

お礼日時：2011/01/12 19:20

No.2 回答者： yuu_x 回答日時： 2011/01/10 13:48

・ピボットテーブル

・集計機能
・D 関数群　等

この回答へのお礼

ご回答有難う御座いました。
ピボットテーブル　集計機能D 関数群　
は普段使用したことがないので、VBAをせずにSheet上で集計できる方法も検討したいと思います。
有難う御座いました。

お礼日時：2011/01/12 19:23

No.3 ベストアンサー 回答者： redfox63 回答日時： 2011/01/10 20:16

自前で検索するのをやめて Scripting.Dictionaryオブジェクトに任せるなら

Sub Test()
　　Dim dicName As New Dictionary
　　Dim r As Range
　　Dim dicCor As Dictionary
　　Dim sName, sColor, obj
　　Dim nn As Integer

　　' データの集計
　　For Each r In Range("A2", Range("A65536").End(xlUp))
　　　　' りんご、なし、メロンなどを取得
　　　　sName = r.Value
　　　　' 色を取得
　　　　sColor = r.Offset(, 1).Value
　　　　' 値段を取得
　　　　nn = r.Offset(, 2).Value
　　　　' dicNameに登録済みか検査
　　　　If dicName.Exists(sName) = False Then
　　　　　　' 未登録なら 色、値段を登録
　　　　　　Set dicCor = New Dictionary
　　　　　　dicCor.Add sColor, nn
　　　　　　dicName.Add sName, dicCor
　　　　Else
　　　　　　' 登録済みの場合 色情報を検査
　　　　　　Set dicCor = dicName(sName)
　　　　　　If dicCor.Exists(sColor) = False Then
　　　　　　　　' 色情報が未登録なら 新規登録
　　　　　　　　dicCor.Add sColor, nn
　　　　　　Else
　　　　　　　　' 色情報があるなら 値段を更新
　　　　　　　　nn = dicCor(sColor) + nn
　　　　　　　　dicCor(sColor) = nn
　　　　　　End If
　　　　　　' 色情報を更新
　　　　　　Set dicName(sName) = dicCor
　　　　End If
　　Next

　　' 出来上がったデータを表示
　　For Each sName In dicName
　　　　Set dicCor = dicName(sName)
　　　　For Each sColor In dicCor
　　　　　　n = dicCor(sColor)
　　　　　　' セルに転記するなら Rangeなどに置き換えましょう
　　　　　　Debug.Print sName, sColor, nn
　　　　Next
　　Next
End Sub
といった具合で ・・・

この回答へのお礼

ご回答有難う御座いました。
Scripting.Dictionaryオブジェクトの存在を初めて知りました！
これは使い勝手が非常に良さそうです！
他の方の、関数を使用した方法と平行して検討したいと思います。
有難う御座いました。

お礼日時：2011/01/12 19:26

No.4 回答者： imogasi 回答日時： 2011/01/10 21:43

VBAの勉強をしたいためにVBAを使うのか？

それなら気長に広く経験して、力を蓄えるほか無いよ。
＞作成日程が迫っているにも関わらず、・・
の時に仕事で使用するものじゃない。
ーー
ピボットテ－ブルやSUMPRODUCT関数があるのに、何でVBAを使うのか？
複数条件の集計は、２００７ならSUMIFS、それ以前ならSUMPRODUCTを使うのは定石。
ーー
VBAを使うなら一番速いロジックは、A+B列でソートして、A+B列（A１&B1)の値が変化するところをとらえて集計することだ。
ソートの良いところは、キーの同類がレコード的に（行的に）近隣行に集ることだ。そこを活用する。
コントロールブレークのロジックだ。
こういうことも経験せずに、急いで、自由にVBAを使うと言うのは無理だと思う。
ーーーー
SUMPRODUCTなど使うために必要な、A+B列に出てくる項目で重複無く、漏れの無い組み合わせは
出し方に工夫が要る。
例データ　A:D列
キー１キー２合成計数
xaxa1
xgxg2
ycyc3
zczc1
yaya2
zczc3
xaxa1
xbxb2
zaza3
C列は=A1&B1
第1行目に見出しはつけておくこと
データーフィルターフィルタオプション設定
指定した範囲を指定。その第1行目に見出しを入れておくこと。
「重複するレコードは無視する」にチェック
で実行。
結果　E：G列
キー１キー２合成
xaxa
xgxg
ycyc
zczc
yaya
xbxb
zaza
このA列とB列の値を使ってSUMPRODUCT関数の第1比較（）内、第2比較の（）内に使う。

キー１キー２合成計数合計
xaxa2
xgxg2
ycyc3
zczc4
yaya2
xbxb2
zaza3
式はH列のH2
=SUMPRODUCT(($A$2:$A$10=E2)*($B$2:$B$10=F2)*($D$2:$D$10))
式を下方向に複写。

この回答へのお礼

ご回答有難う御座いました。
みなさんの回答にもありますが、ピボットテ－ブルやSUMPRODUCT関数を使うのが
やはりポピュラーなのですね。
自分は使用したことがないので、調べてみたいと思います。
有難う御座いました。

お礼日時：2011/01/12 19:31

No.5 回答者： layy 回答日時： 2011/01/11 09:59

コントロールブレイク知らずして関数で、という人もいるでしょうが、これは基本的ロジックで、マスターすべきテクニック。

時間の許す限りＶＢＡで、ですね。

並べ替えしないでやると「なし」のデータをする時「なし」があるかないかはループ２回とか３回、「メロン」のときは３回、この存在チェックの繰り返しで無駄なループが起きているという意味でしょうか。

出現したものが変数１エリアなら存在チェックは楽です。
１件目の後「りんご」
２件目の後「りんごなし」３件目の後「りんごなし」INSTR関数使ってやると「りんごなし」から「りんご」があるかないか、「りんごなし」から「なし」があるかないか、容易にわかります。

また
事前に１度全部読み込みして何種類になるか、例では４つ、を明確にさせておいても違うのでは？、先頭から集計させるときに「なし」なら３番目、とできます。


やろうとしている考えも基本的ロジックで頻繁に使います。ほかの、と途中で放棄しないことですね。

この回答へのお礼

ご回答有難う御座いました。
すみません、もう少し具体的に書いていただけると非常に
助かるのですが・・・
理解力不足で申し訳ありません！

お礼日時：2011/01/12 19:39

No.6 回答者： yuu_x 回答日時： 2011/01/11 12:07

Microsoft Query が使えるなら

データ → 外部データ → ファイル選択（保存してあれば同一ファイルでも可） → クエリ編集

コマンドに以下入力
SELECT `名前` ,`色` ,SUM(`値段`) AS `小計`
FROM `Sheet1$`
GROUP BY `名前`, `色`

セル選択

編集する場合は選択したセルで、データ → 外部データ → クエリ編集

この回答へのお礼

ご回答有難う御座いました。
Microsoft Query といものを初めて知りました。
SQL文で抽出するのですね！

皆さんの回答のおかげで、様々なアプローチの仕方が
あることに気づきました。
有難う御座いました！

お礼日時：2011/01/12 19:42

No.7 回答者： layy 回答日時： 2011/01/12 21:59

前述の、具体的に、と言われると、

まずは「コントロールブレイク」
余裕があればこれをマスターしておく。

今回のは、
A,B,C,A,D,C,Eとあって
４件目のAが既にあったキーか見る場合、
INSTR(1,"ABC","A")=1とさせるか、Aと１件ループ検索して有り判明するか、
６件目のCが既にあったキーか見る場合、
INSTR(1,"ABCD","C")=3とさせるか、A,B,Cと３件ループ検索して有りと判明するか、
７件目のEが既にあったキーか見る場合、
INSTR(1,"ABCD","E")=0とさせるか、A,B,C,A,D,Cと６件ループ検索して無いと判明するか、
やっていたのは後者じゃないのか？と解釈しましたが・・・・。

どういうことすれば楽になるか、そんなこと考えながら組立てができればいいのですが・・・。
ＶＢＡプログラミングまで含めるとアプローチは人それぞれ、いろいろあります。

最初は使いこなせるもので行えばいいでしょう。

アプローチ策（引出し）をたくさん知っていれば、今後の対応も楽になります。

この回答へのお礼

返事遅れて申し訳ありませんでした！！
なぜだか、ログインする時に「既にそのユーザーはログイン中です」
みたいな返答が返ってきて、ログインできませんでした。

確かに、方法もいろいろあって、アプローチの仕方もみなさんそれぞれ違うと
いうことがよく分かりました。本当にありがとうございました。

みなさんの意見を参考に今回はDictionaryオブジェクトを使ってマクロを組むことにしました。
また何かありましたら宜しくお願いします！

お礼日時：2011/01/28 23:45