[[20051121212931]]　『ピボットテーブル作成時のフィールドの設定』（あまちゃん）　ページの最後に飛ぶ

『ピボットテーブル作成時のフィールドの設定』（あまちゃん）

[ピボットテーブル作成時のフィールドの設定の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]]『""』（こう）
  (代奈)

 対象データに文字列（目に見えないスペース文字、シングルクォーテー
ション[']など）が紛れ込んでいないでしょうか。
これさえ解決できれば問題なさそうな感じですが（みやほりん）

  みやほりんさん、代奈さん
 最後まで懇切丁寧にご指導いただきありがとうございました。
 お蔭様で、原因がわかり解決いたしました。
 本当に感謝感激です。
 上手く行かなかった原因は空白行でした。小遣い帳のため「収入」と「支払」が
 混在しますから、先に「収入」のピボットを次に「支払」のピボットを作り、新しい
 シートに両方をコピーして、集計表を完成するマクロにしていました。そのために
 収入のピボットの時は、支払の行が空白になり、何度挑戦しても個数が先に表示され
 合計に設定しなおす必要が生じました。
 質問する場合は、すべての内容を公開しなければ、いけないことを痛感いたしました。
 年間にまとめた小遣い帳のシートを収入と支払に並び替えをし収入分だけと支払分
 だけにを分けてピボットを作ろうと思います。
 みやほりんさん「たぶんこれで打ち止め～」といわれながら、ご指導ありがとう
 ございました。代奈さんにも適切なご指示ありがとうございました。
 弥太郎さんにも以前お世話になったと思いますが、
 「あまちゃんの代わりに解決マーク打ってあげよか」待っていただきありがとう
 ございました。		
 解決できました（＾○＾）　解決マークこれでよかったでしょうか？（あまちゃん）

コメント返信：

[ 一覧(最新更新順) ]

[[20051121212931]] 『ピボットテーブル作成時のフィールドの設定』（あまちゃん） ページの最後に飛ぶ

[[20051121212931]]　『ピボットテーブル作成時のフィールドの設定』（あまちゃん）　ページの最後に飛ぶ