こんにちは。よろしくお願いします。
ピボットデーブルをマクロで作成したいのですが、条件がいくつかあり、それに沿ったものをどうすれば作成できるか教えていただきたいと思います。マクロは記録するものを使用する程度の初心者です。
<元データ>
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ベストアンサー
- 回答日時:
徒然なるままに作成してみました。
シート名などご希望とは異なりますが、もし使ってみようという気になられたら、怪しげな英語で変数名をつけてありますので、参考にしていただき、ご自分で修正してください。元データの変化への動的な対応がお望みなので、その分だけ分かりにくくなっているとは思います。
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
- 回答日時:
マクロだ何だと言う前に もうちょっと Excelを勉強された方が良いのではないでしょうか?
ピボットで行がいくつ増えてもいいように範囲選択しておけば「更新」をクリックするだけで
増えた分も集計されるのは ご存知でしょうか?
また ピボットの各項目に▽が付いていて「科目」から「国語」と「英語」だけ抽出する方法があるという事をご存知でしょうか?
「科目」が何百もあるなら面倒なのも分かりますが解決方法をマクロに求めるのは ちょっと違うと思いますよ?
僕ならシートを3枚使い「データテーブル用」「ピボット用」「集計用」とします。
(実際に今の業務では そのようにしています)
それでも不都合が出て来たら科目毎にブックを分けるとか、方法はあると思いますが
今回のケースでは「マクロ」は違うと思います。
「ピボット」の意味をご存知ですか?
バスケの「ピボット」と同じ意味ですよ。
「何かを軸に色々な方向へ」というニュアンスです。
ご教授、ご回答ありがとうございます。
確かに勉強不足で理解していない事も多く、ご指摘の通りです。
職場の年配のパソコンに苦手意識のある方にも、ボタンひとつでデータの処理ができるものが何とかできないかと思い、このように質問させていただきました。
ピボットテーブルの使い方も参考にさせていただきます。
どうもありがとうございました。
No.1
- 回答日時:
>ピボットデーブルをマクロで作成したいのですが
質問の意図がよくわからないのですが・・・。
「ピボットテーブルはリスト形式になったものを集計する」という機能です。
質問の元データはすでにテーブルデータですので、ピボットテーブルで作成したいものを作るのは、困難ではないのでしょうか。
他にも方法はあると思いますが、
ピボットテーブルを使うなら元データをリスト形式にする。
マクロを使うなら「ピボットテーブルをマクロで」とこだわらない。
など考え方を変えてはいかがでしょうか。
わかりにくい質問で申し訳ありません。
多いとは言えない知識の中で考えたもので、これ以外に選択が思い浮かびませんでした。これからさらに勉強していきたいと思います。
ありがとうございました。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- C言語・C++・C# C言語初心者 構造体 課題について 2 2023/03/10 19:48
- MySQL 下記の問合せを行うクエリを、PhpMyAdminで作成して実行せよ。 第二回模試の3科目の各得点と合 1 2023/04/25 18:02
- C言語・C++・C# C言語 プログラミング 4 2022/05/22 11:53
- 英語 会社で英語を使うことになっての英語の勉強方法 5 2022/07/03 11:49
- 大学受験 推薦入試について教えていただきたいことがあります。 私は、この春高校三年生になります。進路について考 1 2022/04/05 02:04
- 留学・ワーキングホリデー 今高校三年生です。高校卒業後留学しようと思っています。語学学校に通ったあと大学(観光科)に行こうと思 4 2023/05/17 12:30
- 英語 世界ではアメリカ英語とイギリス英語、どちらが主流ですか? 現在、世界中で英語が学ばれています。学校の 4 2022/08/27 21:23
- 予備校・塾・家庭教師 フリーランスで英語指導をされている方、お詳しい方などにお聞きしたいです。 自室にて知人の中高生の子供 2 2022/06/22 09:37
- 大学受験 高校3年生です。大学に行きたいんですが、この前受けた進研模試では結果がよくありませんでした。 受験科 3 2022/07/07 15:50
- Excel(エクセル) マクロか関数で処理したいのですが、教えて頂けませんか。 8 2022/10/31 15:18
関連するカテゴリからQ&Aを探す
おすすめ情報
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
ゴールデンウィークの略は?GW...
-
「多くの方々」と「多くの方」...
-
メールの「様、」←様の後の句読...
-
単位の何本とかってなんて訳せ...
-
英語で「30歳の誕生日おめでと...
-
米国の病院への紹介状の表書き
-
revert
-
Creampieは何故クリームパイで...
-
スポーツ大会の「クラシック」...
-
ある会合の、「第1回」、「第2...
-
「非対象」と「対象外」の言葉...
-
外国人が箇条書きする際、先頭...
-
半角のφ
-
章立ては、部、章、節、項、だ...
-
指数関数の『exp』の読み方って...
-
『あなたがいつも幸せの中にい...
-
英語にしてほしいです! 私の好...
-
『50歳の誕生日おめでとう』を...
-
英語にしてください!5文で犬派...
-
PhDを持つ人の敬称は絶対にDr?
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
ALTの先生へのお礼の手紙
-
卒団記念を英語で書くと?
-
「多くの方々」と「多くの方」...
-
Creampieは何故クリームパイで...
-
数学に関して
-
メールの「様、」←様の後の句読...
-
ご縁を外国語で言うと?
-
『あなたがいつも幸せの中にい...
-
英語で「30歳の誕生日おめでと...
-
外国人が箇条書きする際、先頭...
-
単位の何本とかってなんて訳せ...
-
『50歳の誕生日おめでとう』を...
-
記号m/s の読み方を教えてくだ...
-
せっかく○○してくださったのに...
-
ALTの先生に手紙を書くこと...
-
赤ちゃんの百日祝い 英語で何...
-
章立ては、部、章、節、項、だ...
-
ビジネス英語メールで、 ...
-
ある会合の、「第1回」、「第2...
-
半角のφ
おすすめ情報