『ピボットテーブル作成時のフィールドの設定』（あまちゃん） [ピボットテーブル作成時のフィールドの設定のVBAについて] いつもお世話になり、誠にありがとうございます。 ピボットテーブルをＶＢＡで自動作成していますが、Exel2000とExel2003では コードがほんの少し違うところがあります、2003で作成したコードを2000で使うと 実行時エラー'1004': 「PivotTableクラスのPivotFieldsプロパティを取得出来ません」となり実行でき ません。良い解決策はないでしょうかどなたかご指導下さい。 表は年間の支払金額を縦軸に項目、横軸に月を取って、項目別月別の集計を ピボットテーブルを用いＶＢＡで作っています。ピボットテーブルでは データのフィルド設定が最初は「データの合計金額」が表示されずに、「データの 個数」が表示されます。そのため、フィールドの設定で合計金額に変更しています。 その時のコードが 　2003の場合: ActiveSheet.PivotTables("ﾋﾟﾎﾞｯﾄﾃｰﾌﾞﾙ1").PivotFields ("デーの個数 / 支払金額").Function = xlSum 2000の場合:ActiveSheet.PivotTables("ﾋﾟﾎﾞｯﾄﾃｰﾌﾞﾙ1").PivotFields("データの個 数 : 支払金額").Function = xlSum で / と：のところだけ違うためと思われます。 ピボットテーブル作成時に 「データの 合計」が優先で表示されるようにすことができないでしょうか、 そうすれば、 上記のコードは無しで行けます。さもなくば上記のコード を2003と2000とどちらでも使えるようにすることができないでしょうか？ よろしくお願いいたします。 ---- Application.Version でヴァージョン情報は取得できるので、それぞれに応じた処理を 分岐させることは出来ると思います。（みやほりん） ---- みやほりん さん 早速ご指導ありがとうございます。 ＶＢＡはまだ初歩的なことしかできませんので、分岐させるコードを ご教授ください。これは簡単な小遣い帳で、年間集計を自動作成するもので ６０歳台の知人に提供しようと思いますが、Ｅxｃｅlのヴァージョンを聞かなくても 使用できれるようコード上で自動で分岐できれば幸いです。 よろしくお願いします。(あまちゃん) ---- 記録をとったところ、私の場合は、Excel2000でも2003でも ActiveSheet.PivotTables("ﾋﾟﾎﾞｯﾄﾃｰﾌﾞﾙ1").PivotFields("合計 : 金額").Function = xlSum になりました。 2000も2003も MicrosoftVisualBasic 6.0 バージョンでした。 ご参考まで。 (代奈) ---- でも普通、金額だったら自動的に「合計」が選択されるはずですよね〜。。。 弥太郎さん、出番ですよ！ (代奈) ---- えっ！、コホン、あのぅ・・・、モジモジ・・・ いや、ちょっと野暮用が・・・ ほとぼりが冷めた頃帰って来ます〜。（笑 代奈マイトさんにかかったらかなわんなぁ。（笑 　　　　　　（弥太郎） ---- 代奈さん、弥太郎さん お世話になりありがとうございます。 バージョンはExcel2003(11.5612.----)でコード("デーの個数 / 支払金額")と なり、： ではありません。（2005年1月発売のパソコン　プリインストール） また、パソコンは、新旧3台あり、すべてデータが金額でも最初に「個数」が 表示されます。（ヴァージョンは3台とも違います）「合計」が最初に表示され る方法があるでしょうか？よろしくお願いします。（あまちゃん） ---- まず、集計フィールドでデフォルトで合計にならない原因として、 元となるデータの書式設定の表示形式が文字列になっている可能性 を指摘します。数値化する必要があるようです。 （１）もとのデータベースの数値として計算されるべきデータ範囲 　　　の書式設定の表示形式を「標準」または「数値」に変更。 （２）未使用の入力のないセルをコピー。 （３）集計対象のデータ範囲を範囲選択、右クリックメニューから 　　　「形式を選択して貼り付け」＞「値」「加算」にチェックし 　　　て、OK。 とした上でピボットテーブルを作成してください。 　 フィールド名についてですが、XL2003SP1で確認したところ ActiveSheet.PivotTables("ﾋﾟﾎﾞｯﾄﾃｰﾌﾞﾙ1").PivotFields("データの個数 / bbb").Function = xlSum と記録されるのを確認しました。2000をインストールしたマシンが 今手元にないので2000での確認はできていません。同じ2003でもSP ヴァージョンでの違いがあるかもしれません。 　 ヴァージョンの違いによりフィールド名が異なるとすれば、分岐せ ざるを得ないでしょう。分岐のステートメントサンプルは以下。 Select Case Application.Version Case Is = "9.0" Rem XL2000の時の処理 Case Is = "10.0" Rem XL2002の時の処理 Case Is = "11.0" Rem XL2003の時の処理 End Select 　 なお、9.0、10.0・・・等のヴァージョン番号は念のため MsgBox Application.Version などを別個に実行するなどして実機で確認してください。 （みやほりん） ---- みやほりんさん、私のExcel2003のバージョンは　11.6xxx.xxxx です。 プリインストではないからでしょうけど、このような場合、 > Case Is = "11.0" > Rem XL2003の時の処理 だと逆にひっかかっちゃうんですよね？ 本題から外れてごめんなさい。 (代奈) ---- みやほりんさん ご丁寧なご教授誠にありがとうございます。 急用で出かけますので、帰ってからトライします。 ノロマで時間がかかりそうですから 代奈さんもありがとうございます。（あまちゃん） ---- メニューのヘルプのヴァージョン情報ダイアログで表示される 11.xxxx.xxxx と Application.Version　で取得できる情報は 同一ではないと認識しています。ちなみに私の使用機も 11.6xxx.xxxxとなっていますが、 Application.Version　では 11.0が取得されます。 どんなヴァージョン番号が返るかはっきり把握していないため、 　 ＞なお、9.0、10.0・・・等のヴァージョン番号は念のため ＞MsgBox Application.Version ＞などを別個に実行するなどして実機で確認してください。 　 と、実際に確かめた上でコード作成してほしい、と言うことです。 一応ネット上で調べた限りでは9.4とか10.1とかってのはないよう ですが、自信はありません。 万が一想定外のヴァージョンでの処理を回避するなら 　 Select Case Application.Version Case Is = "9.0" Rem XL2000の時の処理 Case Is = "10.0" Rem XL2002の時の処理 Case Is = "11.0" Rem XL2003の時の処理 Case Else MsgBox "想定外のExcelバージョンのため処理を中止します" Exit Sub End Select などのように回避ロジックを作成しておくと良いでしょう。 （みやほりん） ---- なるほど、取得してみたら 11.0 でした。 それでも ActiveSheet.PivotTables("ﾋﾟﾎﾞｯﾄﾃｰﾌﾞﾙ1").PivotFields("合計 : 金額").Function = xlSum となるケースもあり →想定外　なのね。 了解。 (代奈) ---- ああ、そうか、代奈さんのでは2003でもフィールド名が　「"合計 : 金額"」 になるんでしたね。もしかしたら汎用にしようと思うとヴァージョンだけ調 べてもダメなのか。 　 でもまあ、とりあえず、表示に関係なく、元のデータフィールドが "金額"であるPivotFieldの計算方法を変更するには以下が適。 手動で変更されていても計算方法の変更ができます。 （前掲のヴァージョンによる分岐云々は忘れてください。） 　 Dim PvFld As PivotField Dim Flag As Boolean Dim PvName As String Flag = False With ActiveSheet.PivotTables("ﾋﾟﾎﾞｯﾄﾃｰﾌﾞﾙ1") For Each PvFld In .PivotFields If PvFld.Name = "金額" Then PvName = PvFld.Parent.DataLabelRange.Formula .PivotFields(PvName).Function = xlSum Flag = True End If Next PvFld End With If Flag = False Then MsgBox "有効なフィールドがありませんでした" Exit Sub End If　 　 ここまで書いていて今更ですが・・・・ 「集計フィールドでデフォルトで合計にならない原因」が解決すれば、 この相談の主題では、あまり問題ないのでは・・・。 （そもそもがSUMにしたいところがCOUNTになる、と言う問題なので、 SUMできていればフィールドの計算方法の変更は必要ない･･･？） （みやほりん） ---- な、なんかむずいです。 手持ちのデータで確認すると、 PvName = PvFld.Parent.DataLabelRange.Formula が黄色に反転。。。 こっ、これは何だ？ むーん、先は長い(ーー;) ＞（そもそもがSUMにしたいところがCOUNTになる、と言う問題なので、 ＞SUMできていればフィールドの計算方法の変更は必要ない･･･？） それはそうなんですけどね(・・;) (代奈)　弥太郎さ〜ん、ほとぼり（？）冷めてますから出てきてくださ〜い ---- データフィールドが１アイテムのピボットテーブルでは問題なくできてました。 が・・・複数データフィールドアイテム表示状態ではご指摘のとおり（汗）。 ピボテの女王様のサンプルデータは抜かりがないらしい・・・。 なんだか泥縄の第4弾。 データフィールドの全アイテムを一旦非表示、必要なフィールドアイテム の計算方法とラベル表示を再設定。 　 Dim PvFld As PivotField Dim Flag As Boolean Flag = False 　 With ActiveSheet.PivotTables("ﾋﾟﾎﾞｯﾄﾃｰﾌﾞﾙ1") .DataPivotField.Orientation = xlHidden For Each PvFld In .PivotFields Select Case PvFld.Name Rem ↓この辺でフィールドアイテム選択、ラベル、計算方法設定 Rem サンプルは"金額", "売価"というフィールドを合計にする。 Case Is = "金額", "売価" .AddDataField .PivotFields(PvFld.Name), "合計 / " & PvFld.Name, xlSum Flag = True Case Else End Select Next PvFld If Flag = False Then MsgBox "有効なフィールドがありませんでした" Exit Sub End If End With 　 （みやほりん）たぶんこれで打ち止め〜 ---- 明日は将棋大会がありますもんで、それに備えてトレーニングに励んでましたワ（笑 優勝したら結果を報告します。（でける訳無いやろバーカ！） だもんで只今から脳無い、いや、脳内エステに入ります。 ところで、この問題はなんでしたっけ？ キョロキョロ・・・解決マークも付いとらんみたいやし・・・。 あまちゃんの代わりに解決マーク打っときまひょか？（笑 念のため申し上げときますけど私の得意分野は関数ですさかい、マクロもピボットなんとかいうんもからっきしあきまへんねん。（笑 　　　　　　（弥太郎）　　　 ---- みやほりんさん 返事が遅くなり誠に申し訳ございません。本当にご丁寧にご教授いただきありがとう ございます。 Select Case Application.Version Case Is = "9.0" Rem XL2000の時の処理　・・・の場合が２００３では上手く行きました。 データの書式設定の表示形式・・・・・の場合は１，２，３を実施しましたが、 なぜか私のパソコンでは、数値、標準でも個数が先に表示されます。データを通貨 にしても同じく個数が表示されます。 最後の データフィールドの全アイテムを一旦非表示、必要なフィールドアイテム の計算方法とラベル表示を再設定。 この場合 .DataPivotField.Orientation = xlHiddenのところで次のエラー表示があり 中断してしまいました。 実行時エラー'1004': 「PivotFieldsクラスのOrientationプロパティを設定出来ません」 Select Case ・・・・２００３で上手く出来たましたが、２０００にコピーして 実施しましたら、 ActiveSheet.PivotTables("ﾋﾟﾎﾞｯﾄﾃｰﾌﾞﾙ1").PivotFields("合計 : 金額").Function = xlSum　のところで中断し 「オブジェクトは、このプロパティまたはメソッドをサポートしていません」 となりました。 頭が混乱してわからなくなりました。せっかくご教授を頂ながら、申し訳け ございません。明日頭をすっきりさせて頑張ります。 みやほりんさん、代奈さん、弥太郎さんありがとうございました。 （あまちゃん） ---- 元データの「支払金額」列を選択し、「データ」＞「区切り位置」＞ そのまま「完了」としてみてください。 書式が「標準」でも「数値」でも文字列認識の場合、これでうまくいくことが多いのです。 ご参考 ↓↓ [[20051122121128]]『""』（こう） (代奈) ---- 対象データに文字列（目に見えないスペース文字、シングルクォーテー ション[']など）が紛れ込んでいないでしょうか。 これさえ解決できれば問題なさそうな感じですが（みやほりん） ---- みやほりんさん、代奈さん 最後まで懇切丁寧にご指導いただきありがとうございました。 お蔭様で、原因がわかり解決いたしました。 本当に感謝感激です。 上手く行かなかった原因は空白行でした。小遣い帳のため「収入」と「支払」が 混在しますから、先に「収入」のピボットを次に「支払」のピボットを作り、新しい シートに両方をコピーして、集計表を完成するマクロにしていました。そのために 収入のピボットの時は、支払の行が空白になり、何度挑戦しても個数が先に表示され 合計に設定しなおす必要が生じました。 質問する場合は、すべての内容を公開しなければ、いけないことを痛感いたしました。 年間にまとめた小遣い帳のシートを収入と支払に並び替えをし収入分だけと支払分 だけにを分けてピボットを作ろうと思います。 みやほりんさん「たぶんこれで打ち止め〜」といわれながら、ご指導ありがとう ございました。代奈さんにも適切なご指示ありがとうございました。 弥太郎さんにも以前お世話になったと思いますが、 「あまちゃんの代わりに解決マーク打ってあげよか」待っていただきありがとう ございました。 解決できました（＾○＾）　解決マークこれでよかったでしょうか？（あまちゃん）