[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『VBAのプログラムでピボットテーブルを
excel2003/2010, xp/7
以下のデータを基に、VBAのプログラムでピボットテーブルを
作成したいと考えています。
縦項目に変数A(氏名)、横項目に変数B(科目)
クロス表に示される値を変数C(得点)
としたいと考えています。
変数Dは過去の試験結果です。(罫線を引きたいです。)
もし可能でしたらどのようなコードになるのでしょうか。
お手数ですが、教えていただけると幸いです。
よろしくお願いいたします。
A B C D
Xさん 国 60 65
Xさん 社 90 85
Xさん 数 70 55
Xさん 理 80 75
Xさん 英 65 70
Yさん 国 85 85
Yさん 社 95 90
Yさん 数 95 100
Yさん 理 90 90
Yさん 英 85 90
Zさん 国 55 60
Zさん 社 40 35
Zさん 数 40 50
Zさん 理 60 65
Zさん 英 35 30
みほさん、こんにちは。
一般機能(手作業)で、みほさんが必要とするピボットテーブルは、 作成できますか? それができるなら、その作業の手順書を作成してください。 手順書を作成したら、Excel2003で[ツール]-[マクロ]-[マクロ の自動記録]を実行します。 記録がとられている状態で、手順書に書いたとおりにコマンド操作、等、 を行えば、みほさんが必要とするレイアウトのピボットテーブルを 作成するマクロコードが入手でき、「どのようなコードになるか」が、 わかります。
(OtenkiAme)
Range("C2:C6").Select Selection.Borders(xlDiagonalDown).LineStyle = xlNone Selection.Borders(xlDiagonalUp).LineStyle = xlNone With Selection.Borders(xlEdgeLeft) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = 1 End With With Selection.Borders(xlEdgeTop) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = 1 End With With Selection.Borders(xlEdgeBottom) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = 1 End With With Selection.Borders(xlEdgeRight) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = 1 End With
お願いします。(みほ)
With ActiveSheet.PivotTables(1) .DataBodyRange.BorderAround Weight:=xlThin 'BorderAround(周り全部です) End With
BorderAroundで行けると思います。(Cod)
Range("C2:C6").Borders.LineStyle = xlContinuous 一行でいけるみたいです。(ハモ)
codさんのコードでは変化ありませんでした。
BorderAround等の勉強します。
ハモさん回答のコードで、Range範囲変更したら(私が間違ってたみたい)
いけました。
(みほ)
codさん 今後とも宜しくお願いします。
(みほ)
みほさん、こんにちは。
何度も同じコードが記述されているのが嫌なら 次のようにまとめることはできます。
Sub Matome()
Dim myBorder As Variant
Range("C2:C6").Select Selection.Borders(xlDiagonalDown).LineStyle = xlNone Selection.Borders(xlDiagonalUp).LineStyle = xlNone For Each myBorder In Array(xlEdgeLeft, xlEdgeTop, xlEdgeBottom, xlEdgeRight) '又は For Each myBorder In Array(7, 8, 9, 10)
With Selection.Borders(myBorder) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = 1 End With
Next myBorder
End Sub
ただし、ピボットテーブルに対して書式設定をする時、 Range("C2:C6").Select をしてはいけません。それは、Excelヘルプの 「ピボットテーブルレポートの書式を設定する」の 「その他の文字とセルの書式を変更する」の手順には、 次のような記述があるからです。
1.レポートの更新またはレイアウトの変更を行った場合に、 書式が保持されるようにします。目的のレポートをクリックし、 [ピボットテーブル]ツールバーの[ピボットテーブル]を クリックします。[オプション]をクリックし、[書式の保持] チェックボックスをオンにします。 2.ピボットテーブルレポート内で、書式を変更する領域を選択します。 領域すべてに書式を適用して、現在非表示の部分がレイアウトの 変更によって表示されたときに、非表示だった部分にも書式が設 定されているようにするには、ページフィールドのドロップダウ ンリストで[(すべて)]アイテムをクリックし、マウスポインタ の形状が(太い→)または(太い↓)であることを確認してから、 選択する領域をクリックします。 3.[書式設定]ツールバーの各ボタン、および[書式]メニュー の各コマンドを使って、書式を変更します。 メモ 条件付き書式およびセルの罫線は、更新またはレイアウトを 変更すると維持されません。
つまり、ピボットテーブルに対する書式設定は、[選択]の機能を 使って領域を選択してください、ということです。 よって、(白茶)さんのフィールドのラベルとデータ領域を選択する .PivotSelect "データ2", xlDataAndLabel は、2.の作業をしているけど、(みほ)さんのセル範囲を選択した Range("C2:C6").Select は、2.の作業をしていない、と認識されます。
加えて、「条件付き書式と罫線は、維持されない」仕様になってい るので、その時は、罫線が引けたとしても、大抵は、更新で消えて しまうことになります。
私がお勧めする代替案は、[スタイル]を使った方法です。 外枠に実線を配置したスタイルや、左右だけ、上下だけ、等の罫線を 使ったスタイルをそれぞれ作成し、2.の作業で領域を選択し、作成 した[スタイル]を適用させます。 どのようなスタイルを作成するかは、ピボットテーブルのレイアウト によりますが、経験上、かなりの確率で書式(罫線)を設定(維持) することができます。
Excel2007以降のテーブルスタイルも各領域ごとのスタイルで作成され ていますから、[スタイル]と同じ方法を使って、自分用にカスタマイ ズすることも可能です。
(OtenkiAme)
所見ありがとうございます。
cell範囲の指定ではなく、選択機能を使い領域を指定する事は
なんとなく…分かりますが!?(頭が混乱…)
具体的には?どの様にコードを記述したら宜しいかと…。
また、おすすめの「スタイル」を使った方法とは…
具体的にコード等をご提示していただかないと?
私には理解不能です…?
お願いいたします。(みほ)
> 具体的には?どの様にコードを記述したら宜しいかと…。 > また、おすすめの「スタイル」を使った方法とは… > 具体的にコード等をご提示していただかないと? > 私には理解不能です…? 理解不能といわれた機能のコードを提示するつもりはありません。
Excel2003を使って一般機能だけで下記の作業をすれば、「スタイル」を使った表ができます。 マクロコードが必要なら、新しいマクロの記録を実行しながら作業をすればいいです。 作業手順と記録で得たコードを見比べれば、どんな作業にどんなコードが使用されているか理解できるようになると思います。(本人の努力次第ですが…) 後は、光が見え始めている処理を使うか、記録したコードを編集して使うかは、みほさんの自由です。 その上で、みほさんが作ったコードを提示して疑問点を質問されたら如何でしょうか?
なお、データは、“DataSht”シートに 氏名 科目 試験 得点 Xさん 国 第1学期末 60 Xさん 社 第1学期末 90 Xさん 数 第1学期末 70 Xさん 理 第1学期末 80 Xさん 英 第1学期末 65 : Xさん 国 第2学期末 65 Xさん 社 第2学期末 85 Xさん 数 第2学期末 55 Xさん 理 第2学期末 75 Xさん 英 第2学期末 70 : : のように作成したものを使っています。
【ユーザー設定リストの登録】 01.[ツール]-[オプション]-[ユーザー設定リスト]をクリックする。 02.「リストの項目」リストボックスに“国,数,理,社,英”と入力し、[OK]をクリックする。 03.[オプション]ダイアログを閉じる。 【スタイルの作成】 04.[書式]-[スタイル]をクリックする。 05.「スタイル名」リストボックスに“格子実線”と入力する。 06.「スタイルに設定されている書式」で「罫線」以外のチェックを外し、[編集]をクリックする。 07.[罫線]タブをクリックし、[格子]ボタンをクリックし、[OK]をクリックする。 08.[追加]ボタンをクリックする。 09.「スタイル名」リストボックスに“左右実線”と入力する。 10.「スタイルに設定されている書式」で「罫線」以外のチェックを外し、[編集]をクリックする。 11.[罫線]タブをクリックし、[格子]ボタンをクリックし、[上罫線]と[下罫線]ボタンをクリックして、上下の罫線を削除し、[OK]をクリックする。 12.[追加]ボタンをクリックし、[閉じる]ボタンをクリックする。 【集計用シートの作成】 13.[挿入]-[ワークシート]をクリックする。 14.作成したワークシートのシート見出しをダブルクリックし、“PvtSht”と名前を変更する。 【リスト範囲の名前定義】 15.“DataSht”をアクティブにする。 16.リストの左上セルを選択する。 17.[編集]-[ジャンプ]-[セル選択]をクリックし、[アクティブセル領域]を選択して[OK]をクリックする。 18.「名前ボックス」をクリックし、“Database”と入力して[Enter]キーを押す。 【ピボットテーブルの作成】 19.[データ]-[ピボットテーブルとピボットグラフレポート]をクリックする。 20.[ピボットテーブル… 1/3]で[次へ]をクリックする。 21.[ピボットテーブル… 2/3]で[次へ]をクリックする。 22.[ピボットテーブル… 3/3]で[オプション]をクリックする。 23.「行の総計」と「列の総計」のチェックを外し、[OK]をクリックする。 24.[ピボットテーブル… 3/3]で[レイアウト]をクリックする。 25.“氏名”を行エリアへドラッグ&ドロップする。 26.行エリアの“氏名”をダブルクリックし、「集計」リストボックスから「合計」を指定し、[OK]をクリックする。 27.“科目”を“氏名”の下へドラッグ&ドロップする。 28.“試験”を列エリアへドラッグ&ドロップする。 29.“得点”をデータエリアへドラッグ&ドロップする。 30.“合計 / 得点”をダブルクリックし、(半角スペースを追加して)名前を“得点 ”に修正する。 31.[OK]をクリックして[ピボットテーブル… 3/3]に戻る。 32.作成先シートに「既存のワークシート」を指定し、“PvtSht”シート見出しをクリックし、“A3”セルを選択する。 33.作成先を指定したら[完了]をクリックする。 34.“試験”フィールドのセルの上端をポイントし、太い↓になったらクリックする。(“試験”フィールドのデータラベル領域が選択される) 35.[書式]-[スタイル]をクリックし、「スタイル名」リストボックスから“格子罫線”を選択して[OK]をクリックする。 36.[ピボットテーブル]ツールバーの[ピボットテーブル]-[選択]-[値]をクリックする。(“試験”フィールドのデータ領域が選択される) 37.[書式]-[スタイル]をクリックし、「スタイル名」リストボックスから“左右罫線”を選択して[OK]をクリックする。 38.“氏名”フィールドの小計行の左端をポイントし、太い→になったらクリックする。(“氏名”フィールドの小計行のラベル領域とデータ領域が選択される) 39.[ピボットテーブル]ツールバーの[ピボットテーブル]-[選択]-[値]をクリックする。(“氏名”フィールドの小計行のデータ領域が選択される) 40.[書式]-[スタイル]をクリックし、「スタイル名」リストボックスから“格子罫線”を選択して[OK]をクリックする。 以上
(OtenkiAme)
OtenkiAmeさん ありがとうございます。
>理解不能といわれた機能のコードを提示するつもりはありません。 頑固なんです〜ね。(こちらが理解できるように解説付きでお願いできませんか?) >Excel2003を使って一般機能だけで 2002なんですけど〜お! >(本人の努力次第ですが…) 嫌味な言い方ですが(上から目線なんですね!)、その通りと思います。 >みほさんが作ったコードを提示して疑問点を質問されたら如何でしょうか? 私が質問できるレベルになったら、質問させていただきます。
手順書は ありがとうございます。(感謝) やさしいんですね♪勉強します。
(みほ)
みほさん、こんにちは。
> 2002なんですけど〜お! あなたが、質問の最初に > excel2003/2010, xp/7 と書いたからExcel2003の操作方法を書いたまでです。 Excel2003もExcel2002も操作方法は、同じなので マクロのコードは、自分の手で作ることができます。
> 手順書は ありがとうございます。(感謝) > やさしいんですね♪勉強します。 どういたしまして。
(OtenkiAme)
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.