Ｅｘｃｅｌシートでの集計

質問

エクセルを使って、２～４万件のデータ集計作業で困っています。
概要は下記のとおりなのですが、これらを集計する方法はありますでしょうか？
If関数を使っていましたが、ファイル容量が大きくなりすぎて使えなくなりました。

・Excel2002を使っています。（会社の仕様なので、2003は使えません）
・シートは１枚を使っています。
・２～４万のデータが50のファイル分あります。（合計180万データ）
・構成要素は、３つです。（年式、型番、台数）
・同じ型番で、違う年式のものが１～数十台発生します。
・年式は１８種類（1988以前～2005）あります。
・型番は数百種類あります。

　これらのデータを、同じ型番の台数が何台になるかを集計したいのですが、うまく出来ません。
　同じような型番が多いので、ソートをかけても正しく集計されているか自分で判断できません。（抜けが多くて分からなくなる）
　同じ情報のセルを探して、任意のセルの数字を合計するような命令はありますでしょうか？
　やはりマクロの世界に入らないと解決しないのでしょうか。

（データ例）
1990年式　KE-PIO202SGW　3（台）
1991　　　KE-PIO202SGW　1
1992　　　KE-PIO202SGW　11
1989　　　KE-PIO202SGV　1
1991　　　KE-PIO202SGV　5
1993　　　GG-WER2033　　3
1994　　　GG-WER2033　　4
1994　　　GG-WER2034　　15
1994　　　PPD-12DEFG　　4
というようなデータです。

回答No.5

Wendy02 · ベストアンサー

こんにちは。

＞　試してみましたが、集計できるのが1シート中のセルだけだったのです。
＞　何か方法があるのでしょうか？

もちろん、私の今回のマクロは、複数シート、ブック全体は、「想定内（^^;」です。そうでなければ、私の作ったものは何の意味もありません。

ただ、ピボットテーブル側のマクロになりますと、今の私の設計方針からすると、ダミーシート（１つ余計なシートを設ける）を考えています。ただ、ピボットテーブルのほうですが、結局のところ、人間のする作業をマクロに置き換えていくということになりますね。

ともかく、「ItemSort_Summary」という私の作ったマクロが動いているようでしたら、以下のように、ブック全体を行うようなものに換えました。違うブックでも、ワークシートから、Alt + F8 で、マクロの実行は可能です。

前回のものを、すべて上書きしてしまってください。

なお、
「年式　　型番　　台数」という３列のみの形式になっていないものは、処理できません。二度処理する場合は、３列にしてしまってください。

'<標準モジュール>
------------------------------------------------------------------
Sub ItemSort_Summary()
　 Dim sh As Worksheet
　 Dim r As Range
　 Dim myWord As String
　 Dim RowCount As Long
　 Dim i As Long
　 Application.ScreenUpdating = False
　 For Each sh In ActiveWorkbook.Sheets
　 With sh
　 Set r = .Range("A1").CurrentRegion
　 If r.Columns.Count = 3 And r.Count > 2 Then
　　'エラー処理（３列のみで、２行以上でないと処理は進みません）
　'並べ替え
　 r.Sort Key1:=.Range("B2"), Header:=xlYes
　 r.Rows(1).Resize(, r.Rows.Count - 1).Offset(, 1).Copy r.Cells(1, 4)
　 With r.Offset(1).Resize(r.Rows.Count - 1).Columns(2)
　　　i = 1
　　　Do
　　　　 myWord = .Cells(i, 1).Value
　　　　 If myWord = "" Then Exit Do
　　　　 .Cells(i, 3).Value = myWord
　　　　 RowCount = WorksheetFunction.CountIf(.Columns(1), myWord)
　　　　 .Cells(i, 4).Value = WorksheetFunction.Sum(.Cells(i, 2).Resize(RowCount))
　　　　 i = i + RowCount
　　　Loop
　 End With
　 r.Columns(4).AutoFit
　 Set r = Nothing
　 ''集計したものが必要な場合は、以下のコメントブロックを外す
　 Call EmptyRowDelete(sh)
　 Else
　　 sh.Select
　　 MsgBox "このブックの処理は、集計形式と違うので出来ません。", 16
　 End If
　 End With
　 Next sh
　 Application.ScreenUpdating = True
　 MsgBox "このブックは終了しました。", 64
End Sub
Sub EmptyRowDelete(sh As Worksheet)
'サブルーチン
Dim r As Range
　 Set r = sh.Range("A1").CurrentRegion
　 With r.Cells(1, 4).Resize(r.Count, 2)
　　　.AutoFilter Field:=1, Criteria1:="<>"
　　　'集計のみをコピー　F1 に集計
　　　.Copy sh.Range("F1")
　　　sh.Range("F1").EntireColumn.AutoFit
　　　.AutoFilter
　　End With
　 Set r = Nothing
End Sub

お礼

何度もありがとうございます。
大変助かりました。
本当にありがとうございました。

回答No.4

Wendy02

追伸：

#3のコードの中で

''集計したものが必要な場合は、以下のコメントブロックを外す
　 Call EmptyRowDelete

既に外れていました。

　' Call EmptyRowDelete

これが、コメントブロックが入った状態です。

お礼

ありがとうございます。
　続きの質問で申し訳ないのですが、ピボットテーブルで複数シートの集計はできないのでしょうか？
　試してみましたが、集計できるのが1シート中のセルだけだったのです。
　何か方法があるのでしょうか？

　もちろん、列毎の項目は同じならびになっています。
　よろしくお願いします。

回答No.3

Wendy02

こんにちは。

＞D列以降に型式を表示して、E列（以降）に合計値を表示するような方法はできないでしょうか。

ということは、すでに「並べ替え」はお済みという意味を含んでいるのですね。

これだけは、ピボットが好きでない私でも、ピボットが最適だと思います。

ピボットでは、以下のように出力されます。

合計　:　台数
型番　　　▼
GG-WER2033　　　 7
GG-WER2034　　　15
KE-PIO202SGV　　 6
KE-PIO202SGW　　15
PPD-12DEFG　　　 4
-------------------
総計　　　　　　47

そうでない場合は、

D2:
=IF(B1<>B2,B2,"")

E2:
=IF(LEN(D2)>0,SUM(OFFSET(B2,,1,COUNTIF(B:B,B2))),"")

ということになりますね。それを、フィルダウンコピーしていきます。

かなりシートが重くなりますので、できあがったら、コピーして、値貼り付けして定数化してしまったほうがよいです。また、ピボットと同じようにまとめるには、オートフィルタで、出てきたデータの部分を「空白以外」を選択して、コピーして、もし式でしたら、値貼り付け、そうでなかったら、一般の貼り付けをします。

ピボットテーブルは、ある意味で、パッケージ化されたマクロと考えてもよいかもしれませんが、もし必要でしたら、以下をお使いください。考え方、方法は、上記の手作業とまったく同じ方式です。マクロで出力した場合は、メモリ負担がひじょうに減ります。

'標準モジュール
'---------------------------------------
Sub ItemSort_Summary()
　 Dim r As Range
　 Dim myWord As String
　 Dim RowCount As Long
　 Dim i As Long
　 Application.ScreenUpdating = False
　 Set r = Range("A1").CurrentRegion
　''必ずしも、A1 からデータが始まっていない場合
　''下に切り替える
　'Set r = ActiveCell.CurrentRegion
　'並べ替え
　 r.Sort Key1:=Range("B2"), Header:=xlYes
　 r.Rows(1).Resize(, r.Rows.Count - 1).Offset(, 1).Copy r.Cells(1, 4)
　 With r.Offset(1).Resize(r.Rows.Count - 1).Columns(2)
　　　i = 1
　　　Do
　　　　 myWord = .Cells(i, 1).Value
　　　　 If myWord = "" Then Exit Do
　　　　 .Cells(i, 3).Value = myWord
　　　　 RowCount = WorksheetFunction.CountIf(.Columns(1), myWord)
　　　　 .Cells(i, 4).Value = WorksheetFunction.Sum(.Cells(i, 2).Resize(RowCount))
　　　　 i = i + RowCount
　　　Loop
　 End With
　 r.Columns(4).AutoFit
　 Set r = Nothing
　 ''集計したものが必要な場合は、以下のコメントブロックを外す
　 Call EmptyRowDelete
　 Application.ScreenUpdating = True
End Sub

'---------------------------------------
Sub EmptyRowDelete()
'出力したものをまとめるためのマクロ
Dim r As Range
　 Set r = Range("A1").CurrentRegion
　 With r.Cells(1, 4).Resize(r.Count, 2)
　　　.AutoFilter Field:=1, Criteria1:="<>"
　　　'集計のみをコピー　F1 に集計
　　　.Copy Range("F1")
　　　Range("F1").EntireColumn.AutoFit
　　　.AutoFilter
　　End With
　 Set r = Nothing
End Sub

お礼

マクロまで作成いただき、ありがとうございます。
＃４の回答とあわせて、非常に助かりました。

回答No.2

kamejiro

Ａ　　　　　　Ｂ　　　　　　　　Ｃ
１　　年式　　　　型番　　　　　　台数
２　　1990　　　KE-PIO202SGW　3
３　　1991　　　KE-PIO202SGW　1
４　　1992　　　KE-PIO202SGW　11
５　　1989　　　KE-PIO202SGV　1
６　　1991　　　KE-PIO202SGV　5
７　　1993　　　GG-WER2033　　3
８　　1994　　　GG-WER2033　　4
９　　1994　　　GG-WER2034　　15
１０　1994　　　PPD-12DEFG　　4

というデータだと仮定します。
A1:C:10を領域選択します。

データ→ピボットテーブル…を処理します。
［次へ］を続けて処理するか［完了］を処理します。

ピボットテーブルのウィンドウが表示され
新しいシートが追加されます。

ここに行のフィールド…　　←　「型番」をドラッグ
ここにデータアイテム…　　←　「台数」をドラッグ

で出来ませんか？。私はエクセル２０００のため少し違うかもしれませんが…。

お礼

回答ありがとうございます。
　ピボットテーブルを使いましたが、型式毎の合計が表示出来ません。
　また、合計値が各型式の間に表示されるので、再集計しにくいのですが、別シートもしくはD列以降に型式を表示して、E列（以降）に合計値を表示するような方法はできないでしょうか。
　（ピボットテーブルの使い方に慣れていないので、分かっていないだけかもしれませんが）

回答No.1

damejan3988

ピボットテーブルで集計かければ良いのでは？
ファイル全部の集計結果を合計すればどうでしょう？