忘れられない激○○料理

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

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

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

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

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

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

A 回答 (7件)

自前で検索するのをやめて 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
といった具合で ・・・
    • good
    • 5
この回答へのお礼

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

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

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


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

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

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

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

アプローチ策(引出し)をたくさん知っていれば、今後の対応も楽になります。
    • good
    • 1
この回答へのお礼

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

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

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

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

Microsoft Query が使えるなら


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

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

セル選択

編集する場合は選択したセルで、データ → 外部データ → クエリ編集
    • good
    • 0
この回答へのお礼

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

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

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

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

時間の許す限りVBAで、ですね。

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

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

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


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

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

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

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


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

キー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))
式を下方向に複写。
    • good
    • 1
この回答へのお礼

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

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

・ピボットテーブル


・集計機能
・D 関数群 等
    • good
    • 0
この回答へのお礼

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

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

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で行いたい場合にはこの動作を記録しておけばいかがでしょう。
    • good
    • 0
この回答へのお礼

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

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

お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!

このQ&Aを見た人はこんなQ&Aも見ています


おすすめ情報