Excel VBA 集計方法

質問

1つのファイルに2つのシートがあります。
集計結果を反映するシートと元データのシートで別れています。

<元データシート>
品番 品名 　　　バージョン　 数量
11 ABC Soft 　　　　　2000 4
22 XYS Beta 2003 3
23 HU22 hyoukaban 2000 4
45 298 Software 1998 7
22 XYS Beta 2003 11
25 XYS Beta 2008 3

<集計結果シート>
品名 バージョン 数量
XYS Beta 2008 3
XYS Beta 2003 14
298 Software 1998 7
ABC Soft 2000 4
HU22 hyoukaban 2000 4

※表がずれていると思います。
バージョンは4桁の数字です。

マクロを実行して、自動的に元データの情報を集計して
集計結果シートに反映したいと思います。
(1)品番は一意の番号です
(2)同じ品名ごとに並べて、同じ品名が見つかった場合はバージョンの新しいものが上になるようにしたいです。
(3)品番は集計結果シートには反映していません。

集計結果シートのような結果にするには、どのようなマクロを書けば
いいのか悩んでいます。

サンプルコード等参考になるものがございましたら、お教えください。

回答No.7

ka_na_de · ベストアンサー

＃１です。

最終版です。

Sub test3()
　　Dim Ws1 As Worksheet
　　Dim Ws2 As Worksheet
　　Dim Ws3 As Worksheet
　　Dim mySt As Worksheet
　　Dim myLastRow As Long
　　Dim i As Long
　　Dim myStName As String
　　Dim flg As Boolean

　　Application.ScreenUpdating = False

　　Set Ws1 = Worksheets("元データ")
　　Set Ws2 = Worksheets("集計結果")

　　myStName = "作業シート"
　　For Each mySt In Worksheets
　　　　If mySt.Name = myStName Then flg = True
　　Next mySt
　　If flg = False Then
　　　　ActiveWorkbook.Worksheets.Add.Name = myStName
　　Else
　　　　Worksheets(myStName).Cells.Clear
　　End If
　　Set Ws3 = Worksheets(myStName)

　　With Ws3
　　　　Ws1.Range("A1").CurrentRegion.Copy Destination:=.Range("A1")

　　　　.Range("A1").CurrentRegion.Sort _
　　　　　　　　Key1:=.Range("B2"), Order1:=xlAscending, _
　　　　　　　　Key2:=.Range("C2"), Order2:=xlDescending

　　　　myLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

　　　　For i = myLastRow To 2 Step -1
　　　　　　If .Cells(i, "A").Value = .Cells(i - 1, "A").Value Then
　　　　　　　　 .Cells(i - 1, "D").Value = .Cells(i - 1, "D").Value _
　　　　　　　　　　　　　　　　　　　　　　+ .Cells(i, "D").Value
　　　　　　　　.Rows(i).Delete
　　　　　　End If
　　　　Next i

　　　　.Columns(1).Delete
　　　　.Range("A1").CurrentRegion.Copy Destination:=Ws2.Range("A2")
　　　　Application.DisplayAlerts = False
　　　　.Delete
　　　　Application.DisplayAlerts = True
　　End With

　　Application.ScreenUpdating = True

　　Set Ws1 = Nothing
　　Set Ws2 = Nothing
　　Set Ws3 = Nothing
End Sub

Excel VBAの入門者がどのように勉強を進めていけばよいのか？
ということですが、

私自身、初心者で勉強中の身ですからアドバイスできる立場ではありませんが、
私が参考にした書籍やページを紹介します。

＜書籍＞
１）「世界でいちばん簡単なExcelVBAのe本 最新版
　　　　　―ExcelVBAの基本と考え方がわかる本　」
　　　道用 大介 (著) 　　出版社: 秀和システム

　　「カウンタ変数」や「代入」といった言葉をはじめて聞く方には、
　この本から入ることを勧めます。
　　他の言語でプログラムを書いたことのある方には向きません。

２）「かんたんプログラミング Excel2002 VBA
　　　　基礎編、応用編、関数コントロール編」
　　大村 あつし (著) 　　出版社: 技術評論社

　　　他の本では解説されないような事が細かく解説されています。
　　　私は、2002年版を読みましたが、現在2007年版が出てます。

３）「Excel VBA実践技&上級技大全―アッと驚く達人の技」
　　　　C&R研究所 (著) 　出版社:ナツメ社

　　　本当に実践的な内容が満載です。
　　　上級というと敷居が高く感じますが、そんなことはありません。
　　　簡潔で理解しやすいコードばかりです。
　　　最初に手にする本ではありませんが、１）や２）で基本が理解
　　　できるようになれば、もっておいても損は無いと思います。
　　　残念ながら、2007年版は出てません。

ネットで参考になるページとしては、例えば以下など
１）http://www.moug.net/
２）http://www.eurus.dti.ne.jp/~yoneyama/Excel/vba/index.html
３）http://www.asahi-net.or.jp/~ef2o-inue/menu/menu04.html

お礼

最終版の投稿ありがとうございました。ご提供いただきました内容をもとに自分でアレンジしたいと思います。また、今後勉強をするうえでの参考情報をご紹介頂き助かります。

回答No.6

ka_na_de

＃１です。

もう一つ訂正です。

元データには見出し行が無いので、
　　　　.Range("A1").CurrentRegion.Sort _
　　　　　　　　Key1:=.Range("B2"), Order1:=xlAscending, _
　　　　　　　　Key2:=.Range("C2"), Order2:=xlDescending, _
　　　　　　　　Header:=xlYes
は誤りです。

以下に修正してください。
　　　　.Range("A1").CurrentRegion.Sort _
　　　　　　　　Key1:=.Range("B2"), Order1:=xlAscending, _
　　　　　　　　Key2:=.Range("C2"), Order2:=xlDescending

随分と変更だらけになってしまいました。

見出し行の自動入力の必要性についての返答を待って、
最終版を再投稿したいと思います。

補足

修正分と質問の回答を頂きましてありがとうございました。
分りやすい回答に感謝いたします。
<集計結果シート>の見出し行の自動入力は不要でございます。
1行目に手動で入力します。
2行目から集計結果が自動で反映される内容でお願いします。
最終版の投稿をお待ちしております。

最終版の投稿時に、
Excel VBAの入門者がどのように勉強を進めていけばよいのかアドバイスを頂ければ助かります。
本でもWebサイトでも何でもよいです。

よろしくお願いいたします。

回答No.5

ka_na_de

＃１です。

(1)
作業が完了したら削除するのは「作業シート」という名前のシートです。

<集計結果シート>の１行目は既に入力済みということでしたので、
１行目はいじらずに、２行目以降に集計結果を貼り付けています。

もし、１行目の見出し行も自動で入力したいのであれば、可能ですよ。

(2)
For i = myLastRow To 3 Step -1の意味ですが、

まず、myLastRowという変数は、
myLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
で計算しているのですが、これは、Ａ列の最終行です。
手作業で言うと、
エクセルのＡ列の最終セル上で「Ctrl」＋「↑」
を行って移動するセルの行番号を取得しています。

次に、本題の　For i = myLastRow To 3 Step -1の意味ですが、
上記の最終行番号から３まで一つずつ数を減じながら繰り返す
という意味です。

ここまで書いて今気づいたのですが、
元データのシートには見出し行が無いので、
For i = myLastRow To 3 Step -1
は間違いで、正しくは
For i = myLastRow To 2 Step -1
となります。

もし、<集計結果シート>の見出し行も自動で入力したければ
連絡ください。

回答No.4

ka_na_de

＃１です。

作り直しました。

作業用のシートを用いて「並べ替え」、「集計」の作業を行い、
＜集計結果シート＞の２行目以降に反映させます。
作った作業用シートは、削除して終了します。

Sub test2()
　　Dim Ws1 As Worksheet
　　Dim Ws2 As Worksheet
　　Dim Ws3 As Worksheet
　　Dim mySt As Worksheet
　　Dim myLastRow As Long
　　Dim i As Long
　　Dim myStName As String
　　Dim flg As Boolean

　　Application.ScreenUpdating = False

　　Set Ws1 = Worksheets("元データ")
　　Set Ws2 = Worksheets("集計結果")

　　myStName = "作業シート"
　　For Each mySt In Worksheets
　　　　If mySt.Name = myStName Then flg = True
　　Next mySt
　　If flg = False Then
　　　　ActiveWorkbook.Worksheets.Add.Name = myStName
　　Else
　　　　Worksheets(myStName).Cells.Clear
　　End If
　　Set Ws3 = Worksheets(myStName)

　　With Ws3
　　　　Ws1.Range("A1").CurrentRegion.Copy Destination:=.Range("A1")

　　　　.Range("A1").CurrentRegion.Sort _
　　　　　　　　Key1:=.Range("B2"), Order1:=xlAscending, _
　　　　　　　　Key2:=.Range("C2"), Order2:=xlDescending, _
　　　　　　　　Header:=xlYes

　　　　myLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

　　　　For i = myLastRow To 3 Step -1
　　　　　　If .Cells(i, "A").Value = .Cells(i - 1, "A").Value Then
　　　　　　　　 .Cells(i - 1, "D").Value = .Cells(i - 1, "D").Value _
　　　　　　　　　　　　　　　　　　　　　　+ .Cells(i, "D").Value
　　　　　　　　.Rows(i).Delete
　　　　　　End If
　　　　Next i

　　　　.Columns(1).Delete
　　　　.Range("A1").CurrentRegion.Copy Destination:=Ws2.Range("A2")
　　　　Application.DisplayAlerts = False
　　　　.Delete
　　　　Application.DisplayAlerts = True
　　End With

　　Application.ScreenUpdating = True

　　Set Ws1 = Nothing
　　Set Ws2 = Nothing
　　Set Ws3 = Nothing
End Sub

補足

毎回すばやい回答ありがとうございます。
2点確認させてください。
(1)＜集計結果シート＞の２行目以降に反映させます。
2行目に下記項目が反映されるため、作業が完了したら
削除する意味でよろしいでしょうか。
品名 バージョン 数量

この作業は避けることは不可能なのでしょうか。

(2)下記の構文が本やWebを見ても、理解できません。
どのような処理をされているのでしょうか。
For i = myLastRow To 3 Step -1

以上

何度も質問させて頂き申し訳ございません。お教えください。

回答No.3

ka_na_de

＃１です。

補足に対する回答です。

<元データシート>の１行目のA列　B列　C列　D列に
それぞれ、以下の見出しを付けておいてください。

品番　品名　バージョン　数量

＜集計結果シート＞に見出しはあっても無くても構いません。
＜集計結果シート＞は一度すべてクリアしてから、
＜元データシート＞の見出しをコピーして利用するようにしています。

前回のコードのままで動作します。

もし、＜元データシート＞に見出し行を付けることができない
のであれば、別案を考えます。

補足

早速のご回答ありがとうございます。
可能であれば、下記の流れを希望します。
＜元データシート＞に見出し行はつけません。
＜集計結果シート＞は一度すべてクリアしません。

集計結果シートで下記3項目はすでに記載しています。
A列 B列 C列
品名 バージョン 数量

例えばこの3項目が1行目に記載されているとすると
集計結果を2行目から反映するようにしたいと思います。

何度も申し訳ございません。
別案がございましたら、お教えください。

回答No.2

keithin

[Step1]
元データを列:列選択し，データメニューの(2007ではデータタブの)ピボットテーブルレポートを開始する
品名およびバージョンを行に配置，数量をデータに配置して作成する
「データの個数/数量」をＷクリックして集計の方法を合計に変更する
品名▼およびバージョン▼をそれぞれプルダウンし，(空白)を表示除外する
品名▼をＷクリックして集計を無しにする
バージョン▼をＷクリックしてから詳細で自動並べ替えオプションをバージョンの降順に設定する。

[Step2]
＞マクロを実行して
の代わりにピボットテーブルツールバーの「更新！」ボタンをクリックして最新のデータに更新する。

[Step3]
作成済みピボットテーブルレポートを「更新！」するマクロを自動記録マクロでマクロにして使っても，勿論構いません
しかし最初からピボットテーブルレポートをいちいち作り直すマクロを用意する必要は，通常はありません。

[Step4]
状況によって「リスト形式」の出力が必要な場合，マクロを利用してピボットテーブルレポートの結果を第三のシートに転記し，行フィールド由来の空白セルを「一つ上の内容で埋め」させても良いでしょう。

お礼

ピボットテーブルでの手順を詳細にお教え頂きましてありがとうございます。ピボットテーブルも活用できるようにしたいと思います。

回答No.1

ka_na_de

こんばんは。
一例です。

なお、数量の合計は、同じ品番の数量を合計しています。

Sub test1()
　　Dim Ws1 As Worksheet
　　Dim Ws2 As Worksheet
　　Dim myLastRow As Long
　　Dim i As Long

　　Set Ws1 = Worksheets("元データ")
　　Set Ws2 = Worksheets("集計結果")

　　With Ws2
　　　　.Cells.Clear

　　　　Ws1.Range("A1").CurrentRegion.Copy Destination:=.Range("A1")

　　　　.Range("A1").CurrentRegion.Sort _
　　　　　　　　Key1:=.Range("B2"), Order1:=xlAscending, _
　　　　　　　　Key2:=.Range("C2"), Order2:=xlDescending, _
　　　　　　　　Header:=xlYes

　　　　myLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

　　　　For i = myLastRow To 3 Step -1
　　　　　　If .Cells(i, "A").Value = .Cells(i - 1, "A").Value Then
　　　　　　　　 .Cells(i - 1, "D").Value = .Cells(i - 1, "D").Value _
　　　　　　　　　　　　　　　　　　　　　　+ .Cells(i, "D").Value
　　　　　　　　.Rows(i).Delete
　　　　　　End If
　　　　Next i

　　　　.Columns(1).Delete
　　End With

　　Set Ws1 = Nothing
　　Set Ws2 = Nothing
End Sub

補足

適切なサンプルありがとうございます。
大変助かりました。

1点追加で教えてください。
集計結果シートで下記3項目はすでに記載しています。
A列 B列 C列
品名 バージョン 数量

例えばこの3項目が1行目に記載されているとすると
集計結果を2行目から反映するようにしたいと思います。

何度も申し訳ございません。お教えください。