[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『複数シートのデータの集計』（ミッチェル）
※仕様変更があったので最初の質問を書き直して投稿します
物流関係の会社でデータの集計をしています。
取引先から「このアイテムをこういう組み合わせで出して」という指示が来たら、商品、パンフ、 サンプル、情報誌などをその内容に応じて組み合わせて出荷しています。
「指示内容」（出荷番号ごとのファイル）
A B C D E F 1 発送品ID 印字記号 在庫ID 商品名 数量 種別 2 A001 ア01 AAA あいう 200 商品 3 A001 イ22 ABB かきく 50 資料 4 B001 ウ01 ABB かきく 20 資料 5 C001 CCC たちつ 150 サンプル
出荷番号は毎日ほぼ決まっていて（XXは定期コース購入者向け、YYはサンプル希望者などの規則性がある） これから出荷番号ごとにどの資材をどれだけ出荷しているかを日毎に集計して、月平均などを出そうとしています。
日毎の集計表は出荷番号ごとのシートがあり（30〜40シート）、各シートにその出荷番号で使用した資材の内訳が入っています。
例1：出荷番号「XX」のシート
A B C D E F G 1 番号 出荷番号 発送品ID 印字記号 在庫ID 商品名 数量 2 1 XX A001 ア01 AAA あいう 200 3 2 XX A001 イ22 ABB かきく 50 4 3 XX B001 ウ01 BBB たちつ 20 5 4 XX C001 CCC さしす 150
例2：出荷番号「YY」のシート
A B C D E F G 1 番号 出荷番号 発送品ID 印字記号 在庫ID 商品名 数量 2 1 YY A001 ア01 AAA あいう 150 3 2 YY A002 BBB たちつ 550 4 3 YY B001 ウ01 BBB たちつ 120 5 4 YY C001 CCC さしす 10
A列は連番で「=ROW()-1」の式が入っています。 同じ発送品IDで違う商品が出ることもあり、違う発送品IDで同じ商品が出ることもあります。 D列の「印字記号」は空白のこともあります。 また「発送品ID」は違う出荷番号で同じ発送品IDが使われます。
シート名は「出荷XX」のようになっています。 その日の出荷が全て終わった時点で、「集計」シートに「発送品ID」をキーにしてそれぞれの発送品IDで どの商品がどれだけ使われたかの集計を取りたいです。
例：「集計」シート
A B C D E F 1 番号 発送品ID 印字記号 在庫ID 商品名 数量 2 1 A001 ア01 AAA あいう 250 3 2 A001 イ22 ABB かきく 50 4 3 A002 BBB たちつ 550 5 4 B001 ウ01 BBB たちつ 140 6 5 C001 CCC さしす 160
基本は「発送品ID」での合計個数、同じ発送品IDで複数の商品があったらその内訳の合計を出したいです。
「データ」-「統合」を試してみましたが「発送品ID」で集約した数量の合計しか出ず、在庫IDや商品名が空欄になってしまいました。 また「統合」では30ほどのシートを一つずつ追加しなければならず、かなり面倒です。
SUMIFS関数で何とかできるかと思いましたがどのように条件を指定していいか分からず…
どなたか何かお知恵をお貸しください。
Excelは2007です。
※追記
各「出荷○○」のシートのデータを「集計」シートにまとめるのは下記のマクロでできました。
Sub sh_check()
Dim newSh As String
Dim Sh As Worksheet, myFlag As Boolean
newSh = "当日一覧"
myFlag = False
For Each Sh In ThisWorkbook.Worksheets
If Sh.Name = newSh Then
myFlag = True
Worksheets(newSh).Cells.ClearContents
Worksheets(newSh).Move before:=Sheets(1)
Exit For
End If
Next Sh
If myFlag = False Then
ActiveWorkbook.Worksheets.Add(before:=Worksheets(1)).Name = newSh
End If
End Sub
Sub matome()
Dim i As Integer
Dim lRow As Long, lCol As Long, lRow2 As Long
Application.ScreenUpdating = False
sh_check
Worksheets(2).Range("1:1").Copy Worksheets(1).Range("A1")
For i = 2 To Worksheets.Count - 9
'「出荷○○」シートの後に9シート別のシートが存在するのでそれを差し引き
With Worksheets(i)
lRow = .Cells(Rows.Count, 1).End(xlUp).Row
lCol = .Cells(1, Columns.Count).End(xlToLeft).Column
If lRow >= 2 Then
lRow2 = Worksheets(1).Cells(Rows.Count, 1).End(xlUp).Row + 1
.Activate
.Range(Cells(2, 1), Cells(lRow, lCol)).Copy Worksheets(1).Cells(lRow2, 1)
End If
End With
Next i
Worksheets(1).Activate
Range("A1").Select
Application.ScreenUpdating = True
End Sub
これで「出荷○○」シートと同じレイアウトで「当日一覧」というシートにデータをまとめ、 「出荷番号」列を削除、「発送品ID」をキーに昇順で並べ替えまで出来ました。
この後各発送品IDごとの集計がうまくいきません。
出荷番号ごとのファイルというデータを出荷番号に応じて 出荷番号「XX」のシートとか出荷番号「YY」のシートに振り分けて入力された データを今度は、統合したデータ集計をしたい ということですね！！
VBAを知らない方なら、この出荷番号別にシートを作るという方法も頷けますし、 実際良く見かけます。
が、VBAを知っているなら、 データは、同じレイアウトなのですから、 同一シートに貯めて(すなわち、これデータベース）、ほしい集計（出荷番号別元帳を はじめとする各種集計）は、このデータのあるシートからVBAを使って抽出する というのが基本的な手法なんです。データは、シンプルに・・・。 もし、検討ができるなら、一考ください。
さて、・・・・。例にあげられたデータも多く、出来ているコードも提示されていて 私は、非常によい記述だと思います。
一点だけ >例：「集計」シート >2 1 A001 ア01 AAA あいう 250
これ、合計は、入力データからすると、250ではなく、350ですか？
合計値を集計するには、何をキーに集計すればよいのか？ が、わからないと行えませんが・・・、
例としてのデータだけを見ると、
発送品IDと在庫IDが同じであれば、集計できる
ように見えますが、いかがですか？ 違うなら、このキーを特定することです。 集計するキーは一つの列だけでなくても良いのです。
いづれにせよ、Excelの集計を使っても結果は出ます。
が、よく見かけるのが Dictionary というオブジェクトです。 これを使うと、割と簡単にこの手の集計が行えます。
例 Ａ Ｂ Ｃ Ｄ １ 日付 姓 名 売上 ２ 2012/1/5 田中 真紀子 15000 ３ 2012/1/5 藤 圭子 2000 ４ 2012/1/5 伊藤 咲 5000 ５ 2012/1/5 田中 秀子 2000 ６ 2012/1/5 鈴木 美穂 6000 ７ 2012/1/5 鈴木 良子 2000 ８ 2012/1/5 藤巻 江 3000 ９ 2012/1/5 藤 巻江 3000 １０ 2012/1/6 田中 真紀子 1000 １１ 2012/1/6 藤 圭子 20000 １２ 2012/1/6 伊藤 咲 2000 １３ 2012/1/6 田中 秀子 1500 １４ 2012/1/6 鈴木 美穂 4000 １５ 2012/1/6 鈴木 良子 3000 １６ 2012/1/6 藤巻 江 2000 １７ 2012/1/6 藤 巻江 1000
ある化粧品販売レディの個人の二日間（1/5と1/6）の売上集計を行うことを考えます。 但し、この上記の表、個人名の 姓と名前がB列とC列に分かれています。 ですから、B列の姓だけで集計すると、正確な情報が出ませんよね？
では、B列とC列を連結したデータをキーに集計しよう ということになります。
でも、ただ単に連結すると・・・、
田中真紀子 藤圭子 伊藤咲 田中秀子 鈴木美穂 鈴木良子 藤巻江 藤巻江
あれれえ（コナン風）、 ふじまき ごう さんと ふじ まきえ さんが同じキーになってしまう。
ということで、間に有り得ない文字を区切り文字として挟みます。例えば _ アンダーバー
Sub test()
Dim rng As Range
Dim dic As Object
Dim crng As Range
Dim kk As Variant
Dim myarray As Variant
Set rng = Range("a2", Cells(Rows.Count, "a").End(xlUp))
Set dic = CreateObject("scripting.dictionary")
For Each crng In rng
kk = crng.Offset(0, 1).Value & "_" & crng.Offset(0, 2).Value
If dic.exists(kk) Then
myarray = dic(kk)
myarray(3) = myarray(3) + crng.Offset(0, 3).Value
dic(kk) = myarray
Else
With Application
dic(kk) = .Transpose(.Transpose(crng.Offset(0, 1).Resize(1, 3).Value))
End With
End If
Next
Range("F1:H1").Value = Array("姓", "名", "合計")
With Application
Range("f2:h" & dic.Count + 1).Value = .Transpose(.Transpose(dic.items))
End With
Set dic = Nothing
Set crng = Nothing
Set rng = Nothing
Erase myarray
End Sub
こんなコードで上記表のあるF列から、集計表が作成されます。
同じような方法で考えてみてください。
ichinose
>例：「集計」シート >2 1 A001 ア01 AAA あいう 250 すみません！おっしゃる通り、合計は350です…
> 同一シートに貯めて(すなわち、これデータベース）、ほしい集計（出荷番号別元帳を >はじめとする各種集計）は、このデータのあるシートからVBAを使って抽出する >というのが基本的な手法なんです。 ええと、つまり最初に「集計」シートを作って、それから各データのシートを作るということでしょうか。
VBAは超簡単なものしか扱えないのですが、そちらの方がよいのでしょうか。
>発送品IDと在庫IDが同じであれば、集計できる はい、その通りです。
教えていただいたコードを参考に下記のコードを書いたところ、同じシートのI列以降に結果を出すことができました
Sub test2()
Dim rng As Range
Dim dic As Object
Dim crng As Range
Dim kk As Variant
Dim myarray As Variant
Set rng = Range("a2", Cells(Rows.Count, "a").End(xlUp))
Set dic = CreateObject("scripting.dictionary")
For Each crng In rng
kk = crng.Offset(0, 1).Value & "_" & crng.Offset(0, 3).Value
If dic.exists(kk) Then
myarray = dic(kk)
myarray(5) = myarray(5) + crng.Offset(0, 5).Value
dic(kk) = myarray
Else
With Application
dic(kk) = .Transpose(.Transpose(crng.Offset(0, 1).Resize(1, 5).Value))
End With
End If
Next
Range("I1:M1").Value = Array("発送品ID", "印字記号", "在庫ID", "商品名", "数量")
With Application
Range("I2:M" & dic.Count + 1).Value = .Transpose(.Transpose(dic.items))
End With
Set dic = Nothing
Set crng = Nothing
Set rng = Nothing
Erase myarray
End Sub
それで、今度はデータの一覧を載せた「当日一覧」シートから合計した結果を「当日集計」シートに転記しようと思い、 コードを下記のように書き換えましたが、「当日集計」シートに結果が出ません。 （もし集計データから各データシートへの転記が必要なら集計データを残した方がいいと思ったので） シートの指定の仕方が悪いのでしょうか。
Sub test3()
Dim rng As Range
Dim dic As Object
Dim crng As Range
Dim kk As Variant
Dim myarray As Variant
With Sheets("当日一覧")
Set rng = Range("a2", Cells(Rows.Count, "a").End(xlUp))
Set dic = CreateObject("scripting.dictionary")
For Each crng In rng
kk = crng.Offset(0, 1).Value & "_" & crng.Offset(0, 3).Value
If dic.exists(kk) Then
myarray = dic(kk)
myarray(5) = myarray(5) + crng.Offset(0, 5).Value
dic(kk) = myarray
Else
With Application
dic(kk) = .Transpose(.Transpose(crng.Offset(0, 1).Resize(1, 5).Value))
End With
End If
Next
End With
With Sheets("当日集計")
Range("B1:F1").Value = Array("発送品ID", "印字記号", "在庫ID", "商品名", "数量")
With Application
Range("B2:F" & dic.Count + 1).Value = .Transpose(.Transpose(dic.items))
End With
Set dic = Nothing
Set crng = Nothing
Set rng = Nothing
Erase myarray
End With
End Sub
(ミッチェル)
>つまり最初に「集計」シートを作って、それから各データのシートを作るということでしょうか。
いえ、もっと生データです。
データには、出荷番号や日付と言う情報も入ったような・・・、 抽出したい情報を思い浮かべてください。その情報を取得するなら、どんなキー情報が あればよいか？ それら全部の情報がふくまれたデータを一つのシートに蓄積するのです。
決められた出荷番号のデータだけ取得するには、出荷番号情報がどこかの列になければ なりませんし、ある期間の集計を行いたければ、日付の情報が必要ですよね！！
私の会社の仕事をしてもらっている新人プログラマに最初に販売管理のプログラムの 概略設計をさせると、そのほとんどが納品書を印刷したタイミングで この納品書データ（売上データ）を得意先別に作成した売上ファイルの該当得意先ファイルに 書き込むという設計書を持ってきます。 でも、そんな設計になっている販売管理はないと思いますよ！！
まっ、これは、ゆっくり考えてみてください。
さて、本題です。
test3というプロシジャー、拝見しました。
問題は、Withステートメントの使い方です。
> With Sheets("当日一覧")
これで当日一覧というSheetオブジェクトは、アドレッシングされています。 でも、せっかく、アドレッシングしてもプロシジャーの中でどこにも 当日一覧というシートを参照している記述がありません。
本来は、
>Set rng = Range("a2", Cells(Rows.Count, "a").End(xlUp))
ここで参照しなくてはならないのですが・・・・。
With Sheets("当日一覧")
Set rng = .Range("a2", .Cells(.Rows.Count, "a").End(xlUp))
End With
違いがわかりますか？ 参照しているか否かは、「.」これです。
Rangeの前で、Cellsの前で、Rows.Countの前で .が付いていて参照しています。
当日一覧の参照は、上記の箇所だけでよいのです。rngにセル範囲が登録されますので ほかでは参照の必要がありません。
もう一箇所は、
>With Sheets("当日集計")
このWithステートメントも Withでアドレッシングされたシートを参照している コードが見当たりません。 ここよく考えてください。
どうすれば、アドレッシングされたSheets("当日集計")を参照できるのか？
更に
>With Application
とここでは、Applicationをアドレッシングしています。
With ステートメントで二つ同時にアドレッシングは、出来ませんから どうすればよいか？
どちらかは、Withではなく、オブジェクト変数を使いアドレッシングしなければ なりません。
Withの意味から、調べてみてください。
このWithステートメントやオブジェクトへの参照がきちんと理解できると、 どのシートがアクティブであっても、シートを対象シートに切り替えることなく、 この処理を遂行することが出来ます。 又、今後のプログラミングの処理速度向上やバグの少ないプログラミングに 大いに役立つと思いますよ！！
ichinose
ichinose様
丁寧なご説明ありがとうございます。
下記のコードで思うような処理ができました。
Sub 集計()
Dim rng As Range
Dim dic As Object
Dim crng As Range
Dim kk As Variant
Dim myarray As Variant
Dim syukei As Range
Set syukei = Worksheets("当日集計").Range("A1")
With Sheets("当日一覧")
Set rng = .Range("a2", .Cells(.Rows.Count, "a").End(xlUp))
End With
Set dic = CreateObject("scripting.dictionary")
For Each crng In rng
kk = crng.Offset(0, 1).Value & "_" & crng.Offset(0, 3).Value
If dic.exists(kk) Then
myarray = dic(kk)
myarray(5) = myarray(5) + crng.Offset(0, 5).Value
dic(kk) = myarray
Else
With Application
dic(kk) = .Transpose(.Transpose(crng.Offset(0, 1).Resize(1, 5).Value))
End With
End If
Next
syukei.Range("B1:F1").Value = Array("発送品ID", "印字記号", "在庫ID", "商品名", "数量")
With Application
syukei.Range("B2:F" & dic.Count + 1).Value = .Transpose(.Transpose(dic.items))
syukei.Range("A2:A" & dic.Count + 1).Formula = "=Row()-1"
End With
Set dic = Nothing
Set crng = Nothing
Set rng = Nothing
Erase myarray
End Sub
あの、お世話になりついで（？）に教えていただきたいのですが、
1)「データベース」の件ですが、こちらには一番上に書いた「指示内容」のようなファイルが出荷番号ごとのファイルで届きます。 そしてデータが必要なのはやはり一番上に書いたような
例1：出荷番号「XX」のシート
A B C D E F G 1 番号 出荷番号 発送品ID 印字記号 在庫ID 商品名 数量
このような情報です。
出荷番号は規則性はありますが、その日によってある番号とない番号があります。
おっしゃられる >それら全部の情報がふくまれたデータを一つのシートに蓄積するのです。 というのは、上記のA〜Gの情報を一つのシートにまとめ、それぞれの出荷番号ごとのシートはそこからマクロで 抽出するようなイメージでしょうか？ （理解が悪くてすみません） その日にいくつの出荷番号があっていくつのシートができるかは分かりませんので、出荷番号ごとのシートの雛形を 作っておいてそれをコピーしてその中に抽出したデータを書き込む、という感じですか？
2）>Applicationをアドレッシングしています 検索して Applicationオブジェクトというものがあるのは分かりましたが具体的にどのようなものか分かりません。 上記のコードのApplicationオブジェクトはどのような働きをしているのでしょうか？
3)ピリオドの有無ですが、Withステートメントの中で範囲の指定などをする時は先頭にピリオドを付ける、という内容で合ってますか？
すみません、よろしくお願いします。
（ミッチェル）
>下記のコードで思うような処理ができました。 一箇所だけ、
> Dim syukei As Range
> Set syukei = Worksheets("当日集計").Range("A1")
この箇所これでも確かに間違いではありません。作動しているのですから・・・。
が、通常は、
Dim syukei As worksheet
Set syukei = Worksheets("当日集計")
このようにWorksheetオブジェクトとして、変数を定義し、 変数は、ワークシートオブジェクトを参照するようにします。
> syukei.Range("B1:F1").Value = Array("発送品ID", "印字記号", "在庫ID", "商品名", "数量")
> With Application
> syukei.Range("B2:F" & dic.Count + 1).Value = .Transpose(.Transpose(dic.items))
> syukei.Range("A2:A" & dic.Count + 1).Formula = "=Row()-1"
> End With
> Set dic = Nothing
> Set crng = Nothing
> Set rng = Nothing
set syukei=nothing
> Erase myarray
後半のsyukei変数を利用している箇所は、そのままでよいです。
１について
>上記のA〜Gの情報を一つのシートにまとめ、それぞれの出荷番号ごとのシートはそこからマクロで 抽出するようなイメージでしょうか？
↑これです。前回も申し上げましたが、一日だけでは、ないなら、日付列も必要です。 (ここは、運用の精細がわからないのでなんともいえませんが・・）
出荷番号別のデータは、必要に応じてオンデマンドで情報をみることができたらよいのですよね？（勿論、これはVBAで行います） 何もブック上の別シートにデータを残すことはない様に思えます。 元情報があれば、いつだって集計できるのですから・・・。
２ Applicationオブジェクトついて
Applicationオブジェクトとは、
Workbookオブジェクトがブックを操作する時に使うオブジェクト
Worksheetオブジェクトは、ワークシートを操作する時に使うオブジェクト
Rangeオブジェクトは、セルを操作する時に使うオブジェクト
これと同様に Applicationは、Excel本体を操作する時に使うオブジェクトです。
一例ですが、Application.QuitのQuitメソッドでExcelが終了します。
今回は、ワークシート関数のTranspose関数をVBAで利用するために Applicationオブジェクトを使っています。 他にもApplicationオブジェクトには、へえと思うような機能がありますから、 HelpでApplicationオブジェクトのプロパティやメソッドを眺めてみてください。 漢字のフリガナを連想する なんてこともできるんですよ！！
３ ピリオドの有無
Withステートメントについては、以前こんな投稿をしたことがありました。
http://www.vbalab.net/vbaqa/c-board.cgi?cmd=ntr;tree=45342;id=excel
参考にしてください
ichinose
オブジェクトというのは、プロパティとメソッドと呼ばれるインターフェースを使って 操作します。
追伸 >オブジェクトというのは、プロパティとメソッドと呼ばれるインターフェースを使って 操作します。
昨日は、この記述をしておきながら、私、うとうとしてしまって・・・。
続きです。
これを使ってオブジェクトを操作するのに
range("a1").value=1
range("a1").NumberFormatLocal = "#,##0.00_ "
等とオブジェクトから、記述すると、そに度にVBAは、オブジェクトを探さなければ なりません。シートやブックなら、探すだけでよいですが、
Rangeオブジェクトなどは、最初からオブジェクトがあるわけではないので、
range("a1") という記述を実行するたびにオブジェクトを作成しているので
何度も使うなら、無駄と言うか、処理時間がかかってしまいます
（これがリンク先の実験でわかることです）。
これを withステートメントやオブジェクト変数でアドレッシングすることで
コードの記述では、 オブジェクトの記述が省略でき、更に処理速度も向上する。
with range("a1")
.value=1
.NumberFormatLocal = "#,##0.00_ "
end with
という一石二鳥なんです。
以上ですが、 私の記述から、
range("A1").value=1 '1
range("A1").NumberFormatLocal = "#,##0.00_ " '2
1と2のRange("A1")で表してるオブジェクト、同じオブジェクトではないのです！！
同じセルを扱っているので確かにプロパティやメソッドは、同じ値を返すでしょうが、
オブジェクトとしては、別物です。
sub test()
msgbox worksheets("sheet1") is worksheets("sheet1")
msgbox range("a1") is range("a1")
end sub
この結果の違いを考えて見てください。
本当に以上です。
ichinose
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.