Excel VBA 集計方法

Question

1つのファイルに2つのシートがあります。集計結果を反映するシートと元データのシートで別れています。 <元データシート> 品番品名　　　バージョン　数量 11ABC Soft　　　　　2000 4 22XYS Beta 2003 3 23HU22 hyoukaban 2000 4 45298 Software 1998 7 22XYS Beta 200311 25XYS Beta20083 <集計結果シート> 品名バージョン数量 XYS Beta 2008 3 XYS Beta 200314 298 Software 1998 7 ABC Soft 2000 4 HU22 hyoukaban 2000 4 ※表がずれていると思います。バージョンは4桁の数字です。マクロを実行して、自動的に元データの情報を集計して集計結果シートに反映したいと思います。 (1)品番は一意の番号です (2)同じ品名ごとに並べて、同じ品名が見つかった場合はバージョンの新しいものが上になるようにしたいです。 (3)品番は集計結果シートには反映していません。集計結果シートのような結果にするには、どのようなマクロを書けばいいのか悩んでいます。サンプルコード等参考になるものがございましたら、お教えください。

ka_na_de · Accepted Answer

＃１です。

最終版です。

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

ka_na_de · Answer

＃１です。

もう一つ訂正です。

元データには見出し行が無いので、
　　　　.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

随分と変更だらけになってしまいました。

見出し行の自動入力の必要性についての返答を待って、
最終版を再投稿したいと思います。

ka_na_de · Answer

＃１です。

(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
となります。

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

ka_na_de · Answer

＃１です。

作り直しました。

作業用のシートを用いて「並べ替え」、「集計」の作業を行い、
＜集計結果シート＞の２行目以降に反映させます。
作った作業用シートは、削除して終了します。

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

ka_na_de · Answer

＃１です。

補足に対する回答です。

<元データシート>の１行目のA列　B列　C列　D列に
それぞれ、以下の見出しを付けておいてください。

品番　品名　バージョン　数量


＜集計結果シート＞に見出しはあっても無くても構いません。
＜集計結果シート＞は一度すべてクリアしてから、
＜元データシート＞の見出しをコピーして利用するようにしています。

前回のコードのままで動作します。

もし、＜元データシート＞に見出し行を付けることができない
のであれば、別案を考えます。

keithin · Answer

[Step1]
元データを列:列選択し，データメニューの(2007ではデータタブの)ピボットテーブルレポートを開始する
品名およびバージョンを行に配置，数量をデータに配置して作成する
「データの個数/数量」をＷクリックして集計の方法を合計に変更する
品名▼およびバージョン▼をそれぞれプルダウンし，(空白)を表示除外する
品名▼をＷクリックして集計を無しにする
バージョン▼をＷクリックしてから詳細で自動並べ替えオプションをバージョンの降順に設定する。

[Step2]
＞マクロを実行して
の代わりにピボットテーブルツールバーの「更新！」ボタンをクリックして最新のデータに更新する。

[Step3]
作成済みピボットテーブルレポートを「更新！」するマクロを自動記録マクロでマクロにして使っても，勿論構いません
しかし最初からピボットテーブルレポートをいちいち作り直すマクロを用意する必要は，通常はありません。

[Step4]
状況によって「リスト形式」の出力が必要な場合，マクロを利用してピボットテーブルレポートの結果を第三のシートに転記し，行フィールド由来の空白セルを「一つ上の内容で埋め」させても良いでしょう。

ka_na_de · Answer

こんばんは。
一例です。

なお、数量の合計は、同じ品番の数量を合計しています。

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

Excel VBA 集計方法

＃１です。

＃１です。

この回答への補足

＃１です。

＃１です。

この回答への補足

＃１です。

この回答への補足

[Step1]

こんばんは。

この回答への補足

似たような質問が見つかりました

関連するカテゴリからQ&Aを探す

デイリーランキングこのカテゴリの人気デイリーQ&Aランキング

マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング