ピボットテーブルをマクロで作成したい

質問

こんにちは。よろしくお願いします。
ピボットデーブルをマクロで作成したいのですが、条件がいくつかあり、それに沿ったものをどうすれば作成できるか教えていただきたいと思います。マクロは記録するものを使用する程度の初心者です。

＜元データ＞
A B C D E
1 生徒　科目　期末1　期末2　期末3・・・
2 花子　国語　　20　　 30　　50　・・・
3 花子　英語　　40　　 10　　60　・・・
4 花子　化学　　10　　 20　　50　・・・
5 太郎　国語　　10　　 20　　50　・・・
6 太郎　英語　　40　　 30　　50　・・・
7 太郎　化学　　50　　 70　　80　・・・
↓　↓
＜作成したいピボットテーブル＞
　　　　　期末1　　　　期末2　　　期末3・・・
　　　　国語　英語　国語　英語　国語　英語・・・
　花子　20　　40　　30　　 10　　50　 60　・・・
　太郎　10　　40　　20　　 30　　50　 50　・・・

(1)既存の「結果」という名前のシートにピボットを作成したいです。
(2)期末1、期末2・・・のC列からのデータの列数は毎回違います。
(3)今回は国語、英語、化学のデータから国語と英語のデータを例にとりましたが、科目の種類も科目の数も毎回違います。生徒数も変わります。
別シート（シート名「結果」）のA列に、まとめる際に必要な科目名が入力されており、そことリンクさせてピボットを作成できたら、と思っています。（例　A1に国語　A2に英語と入力されていたら、上記の作成したいピボットテーブルのような形になる、というように）

どなたかお力を貸していただけますでしょうか。
よろしくお願いします。

回答No.2

mitarashi · ベストアンサー

徒然なるままに作成してみました。シート名などご希望とは異なりますが、もし使ってみようという気になられたら、怪しげな英語で変数名をつけてありますので、参考にしていただき、ご自分で修正してください。
元データの変化への動的な対応がお望みなので、その分だけ分かりにくくなっているとは思います。
XL2000のコードですので、他の環境での動作は不明です。他の環境への対応、解説、メンテナンスはできません。
Sub test()
Dim columnIndex() As String
Dim targetRange As Range, destRange As Range, pivotRange As Range
Dim myRow As Range
Dim dataSheet As Worksheet, criteriaSheet As Worksheet
Dim tempSheet As Worksheet, pivotSheet As Worksheet
Dim i As Long, j As Long

With ThisWorkbook
'元データのシート、フィルタオプションの抽出条件のシート
Set dataSheet = .Sheets("Sheet1"): Set criteriaSheet = .Sheets("Sheet2")
'ピボット用に整形したデータのシート、ピボット出力用のシート
Set tempSheet = .Sheets("Sheet3"): Set pivotSheet = .Sheets("Sheet4")
End With
'フィルタオプションで、目的の科目のデータ以外を隠す
'抽出条件シートに　A1　科目、A2　国語、A3　英語という様にいれておく。
Set targetRange = dataSheet.Range("a1").CurrentRegion
If dataSheet.FilterMode = True Then dataSheet.ShowAllData
targetRange.AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _
criteriaSheet.Range("A1").CurrentRegion, Unique:=False
'データベースのレコード形式のデータに整形
tempSheet.Cells.Clear
Set destRange = tempSheet.Range("a1")
ReDim columnIndex(1 To targetRange.Columns.Count - 2)
For i = 1 To targetRange.Columns.Count - 2
columnIndex(i) = targetRange.Cells(1).Offset(0, i + 1).Value
Next i
For j = 1 To targetRange.Rows.Count
Set myRow = targetRange.Rows(j)
If j = 1 Then
With destRange
.Value = myRow.Cells(1)
.Offset(0, 1).Value = myRow.Cells(2)
.Offset(0, 2).Value = "試験"
.Offset(0, 3).Value = "点数"
End With
Set destRange = destRange.Offset(1, 0)
Else
'隠されたデータは整形出力しない
If myRow.EntireRow.Hidden = False Then
For i = 1 To UBound(columnIndex)
With destRange
.Value = myRow.Cells(1)
.Offset(0, 1).Value = myRow.Cells(2)
.Offset(0, 2).Value = columnIndex(i)
.Offset(0, 3).Value = myRow.Cells(2 + i)
End With
Set destRange = destRange.Offset(1, 0)
Next i
End If
End If
Next j
Set pivotRange = tempSheet.Range("a1").CurrentRegion
Call makepivot(pivotRange, pivotSheet)
End Sub

Sub makepivot(dataRange As Range, destSheet As Worksheet)
Dim dataRangeAddress As String
Dim pivotTableName As String

pivotTableName = "ピボットテーブル1"
dataRangeAddress = dataRange.Parent.Name & "!" & dataRange.Address(ReferenceStyle:=xlR1C1)
destSheet.Activate
destSheet.Cells.Clear
destSheet.Parent.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
dataRangeAddress).CreatePivotTable TableDestination:=Range("A3"), _
TableName:=pivotTableName
With destSheet.PivotTables(pivotTableName)
.SmallGrid = False
.PivotFields("生徒").Orientation = xlRowField
.PivotFields("試験").Orientation = xlColumnField
.PivotFields("科目").Orientation = xlColumnField
.PivotFields("点数").Orientation = xlDataField
.ColumnGrand = False
.RowGrand = False
.PivotFields("試験").Subtotals = Array( _
False, False, False, False, False, False, False, False, False, False, False, False)
End With
End Sub
参考URL
http://pvttbl.blog23.fc2.com/

お礼

早速の丁寧なご回答どうもありがとうございました。
一つ一つ内容を確認しながら、勉強させていただきました。
手元にある参考書で意味を確かめながらなのでかなり理解に時間はかかりましたが、コメントを加えてくださっていたので内容把握の手助けになりました。
作っていただいたものはまさにこう出来たら、と思う理想通りのものです。
実務用にほんの少し手を加えるだけで大幅に効率化が図れそうです。
本当にどうもありがとうございました。
心より感謝いたします。

回答No.3

ookami1969

マクロだ何だと言う前に もうちょっと Excelを勉強された方が良いのではないでしょうか？

ピボットで行がいくつ増えてもいいように範囲選択しておけば「更新」をクリックするだけで
増えた分も集計されるのは ご存知でしょうか？

また ピボットの各項目に▽が付いていて「科目」から「国語」と「英語」だけ抽出する方法があるという事をご存知でしょうか？
「科目」が何百もあるなら面倒なのも分かりますが解決方法をマクロに求めるのは ちょっと違うと思いますよ？

僕ならシートを３枚使い「データテーブル用」「ピボット用」「集計用」とします。
（実際に今の業務では そのようにしています）
それでも不都合が出て来たら科目毎にブックを分けるとか、方法はあると思いますが
今回のケースでは「マクロ」は違うと思います。

「ピボット」の意味をご存知ですか？
バスケの「ピボット」と同じ意味ですよ。
「何かを軸に色々な方向へ」というニュアンスです。

お礼

ご教授、ご回答ありがとうございます。
確かに勉強不足で理解していない事も多く、ご指摘の通りです。
職場の年配のパソコンに苦手意識のある方にも、ボタンひとつでデータの処理ができるものが何とかできないかと思い、このように質問させていただきました。
ピボットテーブルの使い方も参考にさせていただきます。
どうもありがとうございました。

回答No.1

popuplt

>ピボットデーブルをマクロで作成したいのですが

質問の意図がよくわからないのですが・・・。
「ピボットテーブルはリスト形式になったものを集計する」という機能です。
質問の元データはすでにテーブルデータですので、ピボットテーブルで作成したいものを作るのは、困難ではないのでしょうか。
他にも方法はあると思いますが、
ピボットテーブルを使うなら元データをリスト形式にする。
マクロを使うなら「ピボットテーブルをマクロで」とこだわらない。
など考え方を変えてはいかがでしょうか。

お礼

わかりにくい質問で申し訳ありません。
多いとは言えない知識の中で考えたもので、これ以外に選択が思い浮かびませんでした。これからさらに勉強していきたいと思います。
ありがとうございました。