EXCEL2003 VBAにてマクロを作成しているのですが、作成日程が迫っているにも関わらず、
すぐ行き詰ってしまいます。どうかご指導お願い致します。
以下のような事をしたいのですが、できる限り高速で最も効率の良い方法を教えていただきたいと
思っております。よろしくお願い致します。
あるSheetに以下のようにデータが登録されていて、
A B C
りんご 赤 200
なし 黄色 100
りんご 緑 100
メロン 緑 10
なし 黄色 200
りんご 緑 500
これを集計すると、
りんご 赤 200
りんご 緑 600
なし 黄色 300
メロン 緑 10
と、結果を返したいと考えています。
数字を合計するのはA列とB列がともに一致した時のみです。
最初は単純に何回もForNextで処理しようと思ったのですが、行数が
多くなると繰り返し回数も多くなり、時間もかかってしまうので、
別の方法でもっといい方法があればと思い、質問させていただきました。
宜しくお願いします。
No.3ベストアンサー
- 回答日時:
自前で検索するのをやめて 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オブジェクトの存在を初めて知りました!
これは使い勝手が非常に良さそうです!
他の方の、関数を使用した方法と平行して検討したいと思います。
有難う御座いました。
No.7
- 回答日時:
前述の、具体的に、と言われると、
まずは「コントロールブレイク」
余裕があればこれをマスターしておく。
今回のは、
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プログラミングまで含めるとアプローチは人それぞれ、いろいろあります。
最初は使いこなせるもので行えばいいでしょう。
アプローチ策(引出し)をたくさん知っていれば、今後の対応も楽になります。
返事遅れて申し訳ありませんでした!!
なぜだか、ログインする時に「既にそのユーザーはログイン中です」
みたいな返答が返ってきて、ログインできませんでした。
確かに、方法もいろいろあって、アプローチの仕方もみなさんそれぞれ違うと
いうことがよく分かりました。本当にありがとうございました。
みなさんの意見を参考に今回はDictionaryオブジェクトを使ってマクロを組むことにしました。
また何かありましたら宜しくお願いします!
No.6
- 回答日時:
Microsoft Query が使えるなら
データ → 外部データ → ファイル選択(保存してあれば同一ファイルでも可) → クエリ編集
コマンドに以下入力
SELECT `名前` ,`色` ,SUM(`値段`) AS `小計`
FROM `Sheet1$`
GROUP BY `名前`, `色`
セル選択
編集する場合は選択したセルで、データ → 外部データ → クエリ編集
ご回答有難う御座いました。
Microsoft Query といものを初めて知りました。
SQL文で抽出するのですね!
皆さんの回答のおかげで、様々なアプローチの仕方が
あることに気づきました。
有難う御座いました!
No.5
- 回答日時:
コントロールブレイク知らずして関数で、という人もいるでしょうが、これは基本的ロジックで、マスターすべきテクニック。
時間の許す限りVBAで、ですね。並べ替えしないでやると「なし」のデータをする時「なし」があるかないかはループ2回とか3回、「メロン」のときは3回、この存在チェックの繰り返しで無駄なループが起きているという意味でしょうか。
出現したものが変数1エリアなら存在チェックは楽です。
1件目の後「りんご」
2件目の後「りんごなし」3件目の後「りんごなし」INSTR関数使ってやると「りんごなし」から「りんご」があるかないか、「りんごなし」から「なし」があるかないか、容易にわかります。
また
事前に1度全部読み込みして何種類になるか、例では4つ、を明確にさせておいても違うのでは?、先頭から集計させるときに「なし」なら3番目、とできます。
やろうとしている考えも基本的ロジックで頻繁に使います。ほかの、と途中で放棄しないことですね。
ご回答有難う御座いました。
すみません、もう少し具体的に書いていただけると非常に
助かるのですが・・・
理解力不足で申し訳ありません!
No.4
- 回答日時:
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))
式を下方向に複写。
ご回答有難う御座いました。
みなさんの回答にもありますが、ピボットテ-ブルやSUMPRODUCT関数を使うのが
やはりポピュラーなのですね。
自分は使用したことがないので、調べてみたいと思います。
有難う御座いました。
No.1
- 回答日時:
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上で関数を使用したことがないので、
何でもマクロ上で処理しようとしていました。
回答してくださったアプローチも検討したいと思います。
有難う御座いました。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- Excel(エクセル) Excelの関数詳しい方お願いします。 13 2023/01/18 21:11
- Excel(エクセル) excel 2013 色付きセルだけ合計する。 2 2023/02/20 07:28
- Windows 7 エクセルで重複データから抽出したい 2 2022/05/18 23:31
- 高校 数学A組み合わせの考え方 3 2022/04/19 09:05
- Excel(エクセル) 指定した数字まで累計する方法や文字例の抽出について教えてください 4 2022/10/05 21:19
- Excel(エクセル) 条件付き書式の色付きセルのカウント方法について 2 2022/10/21 14:51
- 会計ソフト・業務用ソフト Excelで売上げデータの中の任意の商品の合計を出したい 3 2023/01/18 18:19
- Excel(エクセル) エクセルでA1に黄色のハイライトをつけたらB1に1,000という数値を返したいです。 4 2023/03/17 00:24
- Excel(エクセル) vba 同じブック内での転記について 4 2023/01/15 14:42
- Visual Basic(VBA) 列と行の名前(重複あり)が交差するセルに、データを入力したい 3 2022/06/12 11:17
このQ&Aを見た人はこんなQ&Aも見ています
-
あなたの「必」の書き順を教えてください
ふだん、どういう書き順で「必」を書いていますか? みなさんの色んな書き順を知りたいです。 画像のA~Eを使って教えてください。
-
人生最悪の忘れ物
今までの人生での「最悪の忘れ物」を教えてください。 私の「最悪の忘れ物」は「財布」です。
-
とっておきの手土産を教えて
お呼ばれの時や、ちょっとした頂き物のお礼にと何かと必要なのに 自分のセレクトだとついマンネリ化してしまう手土産。 ¥5,000以内で手土産を用意するとしたらあなたは何を用意しますか??
-
いけず言葉しりとり
はんなりと心にダメージを与える「いけず言葉」でしりとりをしましょう。 「あ」あら〜しゃれた服着てはりますな 遠くからでもわかりましたわ
-
好きな和訳タイトルを教えてください
洋書・洋画の素敵な和訳タイトルをたくさん知りたいです!【例】 『Wuthering Heights』→『嵐が丘』
-
VBAで重複データを合算したい
Excel(エクセル)
-
VBAで重複する項目を1つにまとめて金額を合計したい
Excel(エクセル)
関連するカテゴリからQ&Aを探す
おすすめ情報
- ・漫画をレンタルでお得に読める!
- ・【大喜利】【投稿~11/22】このサンタクロースは偽物だと気付いた理由とは?
- ・お風呂の温度、何℃にしてますか?
- ・とっておきの「まかない飯」を教えて下さい!
- ・2024年のうちにやっておきたいこと、ここで宣言しませんか?
- ・いけず言葉しりとり
- ・土曜の昼、学校帰りの昼メシの思い出
- ・忘れられない激○○料理
- ・あなたにとってのゴールデンタイムはいつですか?
- ・とっておきの「夜食」教えて下さい
- ・これまでで一番「情けなかったとき」はいつですか?
- ・プリン+醤油=ウニみたいな組み合わせメニューを教えて!
- ・タイムマシーンがあったら、過去と未来どちらに行く?
- ・遅刻の「言い訳」選手権
- ・好きな和訳タイトルを教えてください
- ・うちのカレーにはこれが入ってる!って食材ありますか?
- ・おすすめのモーニング・朝食メニューを教えて!
- ・「覚え間違い」を教えてください!
- ・とっておきの手土産を教えて
- ・「平成」を感じるもの
- ・秘密基地、どこに作った?
- ・【お題】NEW演歌
- ・カンパ〜イ!←最初の1杯目、なに頼む?
- ・一回も披露したことのない豆知識
- ・これ何て呼びますか
- ・初めて自分の家と他人の家が違う、と意識した時
- ・「これはヤバかったな」という遅刻エピソード
- ・これ何て呼びますか Part2
- ・許せない心理テスト
- ・この人頭いいなと思ったエピソード
- ・牛、豚、鶏、どれか一つ食べられなくなるとしたら?
- ・好きなおでんの具材ドラフト会議しましょう
- ・餃子を食べるとき、何をつけますか?
- ・あなたの「必」の書き順を教えてください
- ・ギリギリ行けるお一人様のライン
- ・10代と話して驚いたこと
- ・大人になっても苦手な食べ物、ありますか?
- ・14歳の自分に衝撃の事実を告げてください
- ・家・車以外で、人生で一番奮発した買い物
- ・人生最悪の忘れ物
- ・あなたの習慣について教えてください!!
- ・都道府県穴埋めゲーム
このQ&Aを見た人がよく見るQ&A
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
Excelで隣のセルと同じ内容に列...
-
Excel関数:「0」を除いた標準...
-
Excel関数で、範囲内の最後のセ...
-
値の入っているセルのうち、一...
-
【エクセル】区切り位置で分割...
-
SUMIFで数値が入力されているセ...
-
ピボットテーブル 0個の行を...
-
更新前と更新後の差分をVBAを使...
-
エクセルに入力された日付「S40...
-
エクセルVBA AdvancedFilterの...
-
エクセルの日付書式を一発置換...
-
エクセルで何種類のデータがあ...
-
correl関数の範囲指定
-
エクセルのSUMPRODUCT関数につ...
-
エクセル2007の表で自動的に点...
-
《エクセル2000》重複している...
-
【Excel】歯抜けデータの集約
-
エクセル、正数のみの集計[(負...
-
エクセルで別のシートに数行お...
-
excel vba 複数項目の集計
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
Excelで隣のセルと同じ内容に列...
-
Excel関数:「0」を除いた標準...
-
Excel関数で、範囲内の最後のセ...
-
値の入っているセルのうち、一...
-
【エクセル】区切り位置で分割...
-
更新前と更新後の差分をVBAを使...
-
SUMIFで数値が入力されているセ...
-
ピボットテーブル 0個の行を...
-
エクセルに入力された日付「S40...
-
SUMPRODUCT関数 行が増えても...
-
correl関数の範囲指定
-
エクセル 8ケタの数字から日数...
-
【Excel】歯抜けデータの集約
-
エクセル、正数のみの集計[(負...
-
Excelのマクロでソートがうまく...
-
Excelで複数列かつ複数行分の一...
-
エクセルで何種類のデータがあ...
-
エクセルで別のシートに数行お...
-
Excelのマクロで行を間引きたい
-
エクセル2000で〇×の並び替えを...
おすすめ情報