rubyによるExcel操縦あれこれ〜ピボットテーブルの利用（第１版）

rubyによるExcel操縦あれこれ　〜　ピボットテーブルの利用(第１版)

最終更新日： 2011/03/07

　Excel関連で時々用いるノウハウをメモにしておこうと思い立って、記しました。

　Excelのピボットテーブル利用について記します。

　「ピボットテーブルについて書くなら、最低限ここまでは」という地点まで達していませんが、私の力量では書き進めるのがなかなか骨なので、とりあえず第１版として掲げます。

pt_test.zip には、以下で掲げるサンプルスクリプトのほか、exlap.rbも含まれています。別途ダウンロードする必要はありません。

　　　　

なお、rubyがインストールされていない環境でサンプルスクリプトを実行したい方は、exl.exeをお試し下さい。詳しくは exl.exeの使い方 を参照して下さい。

---

• ＜はじめに＞
• 1.　まずはピボットテーブルを作ってみる
• (1) ピボットキャッシュの設定
• (2) ピボットテーブルの新規作成
• (3) ピボットテーブルの項目等の設定
• (4) ピボットテーブルの項目等の設定・その２ AddFields, AddDataField
• (5) ピボットフィールドに関する調整(見出しや配置順の変更)
• a. 見出し(ラベル)の名前を変更する Caption
• b. ピボットフィールドの数値表示形式 NumberFormat
• c. ピボットアイテムに関する設定(項目の配置替え) PivotItems
• (6) ピボットテーブルのプロパティ設定(総計欄の省略など)
• (7) ピボットテーブルの更新
• 2.　注目する項目が１つだけの場合
• (1) 「性別」(男女別)の人数を集計
• (2) 性別の構成比(パーセンテージ)の算出 Calculation
• 3.　複数のピボットテーブルの作成、確認、削除
• (1) １つのワークシートに複数のピボットテーブルを作成する
• (2) ワークブック内のピボットテーブルの存在有無等を確認する
• (3) ピボットテーブルの削除
• 4.　外部データを材料にしてピボットテーブルを作成
• (1) Excelワークブックにodbc接続
• (2) Accessデータベースにodbc接続
• (3) Excelワークブックにado接続
• (4) Accessデータベースにado接続
• (5) odbc接続で外部データと結びつけられたピボットテーブルの更新

---

　Excelのピボットテーブルは、集計表を簡単に作るためのものです。

　例えば、「性別、身長、体重」が人数分記録されているワークシートを元にして、男女別に平均身長と平均体重を求めて表にする、といったことができます。

　以下に掲げるサンプルスクリプトを私が実行した環境は次のとおり。

• MS-Windows xp | vista
• Excel2002(Office xp版) | Excel2007
• ruby ver 1.8.7
• 使用ライブラリ: 拙作exlap.rb v1.1
• Accessデータベースにアクセスするサンプルスクリプトもあるが、Accessがインストールされていなくても大丈夫。

---

　氏名、性別、身長、体重の４項目からなる10人分のデータを処理する例を考えます。

　下に掲げるrubyスクリプト pt01.rb は、順番に、次のような処理を行っています。

• Excelの第１ワークシートに10人分のデータを書き込む。
• その10人分のデータが書き込まれた領域に名前を付ける(名前付き範囲の定義)。
• 第２ワークシートにピボットテーブルを設ける。

　ピボットテーブルは、性別(女性・男性別)の平均身長と平均体重を示すものにしてみます。

　先に、ピボットテーブルで作られる表の出力結果を示すと、次のとおり。

	性別
データ	女	男	総計
平均／身長	161.68	170.66	166.17
平均／体重	57.24	65.18	61.21

　ピボットテーブルを設定する時の要点は次のとおりです。

1. 項目分類の注目欄として「性別」を指定し、それを列方向(横方向)に広げるよう指定。
   

　このとき、「性別」の内容(男と女の２分類)には触れない。項目分類をどうするかは、Excelに任せる。もし「不明」と書かれているものがあれば３分類にしてくれるはず。

1. 集計の注目欄として「身長」を指定し、集計方法は「平均」を設定。
   

　このとき、平均をどのように求めるかの計算式を設定したりはしない。所定のプロパティに XlAverage という定数を代入するだけ。

1. 「体重」についても「身長」と同じように、平均を集計する旨を設定する。
   

　プログラミングで細かな指定をやり慣れている人は、上のような指定方法に違和感を覚えるかもしれません。ですが、「簡単に設定できてＧＵＩ方式でも容易に扱える」ということにはなると思います。

　以下、サンプルを掲げます。

   −−−−　ここから (pt01.rb)
   #! ruby -Ks
   require "exlap"

   data_list = [  # 10人分の氏名、性別、身長、体重
     %w(氏名 性別 身長 体重),
     %w(安部 男 158.9 50.6),
         （中略）
     %w(相馬 女 151.6 51.3)]

   filename = "test01.xls"
   Exlap.new(filename) {|wb|
         # 元になるデータをワークシートに記録
     ss = wb.fes  # 最初の空のシートを選択
     ss.Name = "元データのシート"
     data_list.each_with_index {|row, i|
       rn = i + 1
       ss.rr(rn,1, rn,row.size).Value = row
     }
     y1,x1, y2,x2 = ss.range_address  # データ記録領域の番地を取得

         # 上記データの領域を「名前付き範囲」に指定
     range_name = "性別と身長と体重"  # 範囲に付ける名前
     range_area = sprintf("R%dC%d:R%dC%d", y1, x1, y2, x2)
     wb.obj.Names.Add({
       'Name'=>range_name,
       'RefersTo'=>"=#{ss.Name}!#{range_area}"})

         # 次シートにピボットテーブルを設定
     ss2 = wb.fes
     ss2.Name = "ピボットテーブルのシート"
     ss2.Activate  # ピボットテーブルを設けるシートに焦点を当てる
     pivot_cache = wb.obj.PivotCaches.Add({
       'SourceType'=>XlDatabase,
       'SourceData'=>range_name})
     ptname = "ピボット01"  # ピボットテーブルの名前
     pivot_cache.CreatePivotTable({
       'TableDestination'=>ss2.Range("A1"),  # ピボットテーブルの始点
       'TableName'=>ptname})
     pt = ss2.PivotTables(ptname)  # ピボットテーブルオブジェクトをptに
     pt.SmallGrid = false

         # 項目分類の注目欄として性別を指定。横方向(列)に広げる形に設定
     ptf = pt.PivotFields("性別")
     ptf.Orientation = XlColumnField  # 「列」方向に並べるフィールドとする

         # 集計用の注目欄として「身長」を指定。平均を採ることも指定。
     ptf = pt.PivotFields("身長")
     ptf.Orientation = XlDataField  # 集計して新たに設ける欄であるとの指定
     ptf.Function = XlAverage  # 集計方法を「平均を求める」に設定

         # 「体重」に関する設定(「身長」と同じ)
     ptf = pt.PivotFields("体重")
     ptf.Orientation = XlDataField
     ptf.Function = XlAverage
     wb.save
   }
   −−−−　ここまで

　上のスクリプトについて、10人分のデータをワークシートに書き込む部分と、名前付き範囲の定義の箇所に関しては、説明を省略します。

　ピボットテーブルの設定について、多少の補足を交じえて書いてみます。

(1) ピボットキャッシュの設定

　サンプルでは、まずワークブックにピボットキャッシュを追加し、そのピボットキャッシュを基にピボットテーブルを作成しています。

　ピボットキャッシュを設けるための記述は次のとおり。

   pivot_cache = wb.obj.PivotCaches.Add({
     'SourceType'=>XlDatabase,
     'SourceData'=>range_name})

　「wb.obj」は、ワークブックオブジェクトです。VBAの ActiveWorkbook に該当します。

　wb.obj.PivotCaches.Add によって、ワークブックにピボットキャッシュを追加するわけですが、その引数は Hash で、次のものを渡しています。

• SourceType　元データの種類を指定。値として次のものを採れる。
  • XlDatabase　Excelのリストまたはデータベース
  • XlConsolidation　複数のワークシート範囲
  • XlExternal　外部のアプリケーションのデータ
  • XlPivotTable　既存のピボットテーブル
  • XlScenario　「シナリオの登録と管理」で作成したシナリオに基づくデータ
• SourceData　元データの領域
  

　元データが書き込まれている領域を、「名前付き範囲」の名前や番地で指定する。
　サンプルでは、「名前付き範囲」の名前によって指定している。
　番地で指定するなら 'SourceData'=>"元データのシート!R1C1:R11C4" となる。

　サンプルにおいて、わざわざ「名前付き範囲」を定義し、その名前で SourceData の値をセットしているのは、後で「元データ」を追加してその領域が拡大したような場合に対応しやすくなるからです。番地で指定していると、元データの領域が拡大／縮小した時に、それに応じてピボットキャッシュを設定し直さなければならなくなります。

(2) ピボットテーブルの新規作成

　ピボットテーブルの新規作成は、次の記述で行っています。

   ptname = "ピボット01"  # ピボットテーブルの名前
   pivot_cache.CreatePivotTable({
     'TableDestination'=>ss2.Range("A1"),  # ピボットテーブルの始点
     'TableName'=>ptname})

　pivot_cache.CreatePivotTable の引数は Hash で、TableDestination, TableName の２つを渡していますが、他にもあるので下に列記しておきます。

• TableDestination　ピボットテーブルの始点
  

　ワークシート内のどの位置からピボットテーブルの集計表を始めるかの指定。
　サンプルでは ss2.Range("A1") つまりワークシートのA1欄(最も左上端)を指定。

• TableName　ピボットテーブルの名前 (省略可)
  

　サンプルでは ptname("ピボット01")をピボットテーブルの名前としてセットしている。

• ReadData　ピボットテーブルのキャッシュ作成の有無 (省略可)
  

　trueにすると、外部データベースのすべてのレコードを含むピボットテーブルのキャッシュを作成(キャッシュサイズが非常に大きくなる可能性あり)
　falseにすると、データが実際に読み込まれる前に、一部のフィールドをサーバーベースのページフィールドとして設定できる。

• DefaultVersion　ピボットテーブル集計表の既定のバージョン (省略可)
  

　ピボットテーブル集計表の既定のバージョンを指定。
　XlPivotTableVersion10 などの定数をセットできるようだが、詳しいことは未確認。

　サンプルでは、ピボットテーブルを作成した後で、

   pt = ss2.PivotTables(ptname)

のように、ピボットテーブルの名前を手がかりにして、変数 pt にピボットテーブルオブジェクトを代入していますが、実は、pivot_cache.CreatePivotTable() は、ピボットテーブルオブジェクトを返します。なので

   pt = pivot_cache.CreatePivotTable({……})

と書くことができます。

(3) ピボットテーブルの項目等の設定

　集計表にどのような項目を組み入れるかの設定は、PivotFieldsを用いて行います。サンプルに即して簡単な説明を記します。

   ptf = pt.PivotFields("性別")

　上は、「元データ」の「性別」の欄を注目フィールドとして取り上げるという意味です。その注目フィールドを変数ptfにセットしています。

   ptf.Orientation = XlColumnField

　上は、注目フィールドの「性別」を項目分類の手がかりにするとの指定です。

　元データの性別欄をみると分かりますが、「男」と「女」の２種類があるので、Excelは、「男については……」　「女については……」という集計を行うことになります。

　そして、項目分類の配置を列方向(つまり横方向)に並べることも意味しています。「女、男、総計」の３つが横方向に広がって並びます。

　XlColumnField でなく XlRowField だと、行方向(縦方向)に並べるとの指定になります。

　また、XlDataField をセットすると、項目分類の手がかりにするのでなく、集計(計算)の手がかりにするとの指定になります。この後に出てくる「身長」と「体重」は、このXlDataFieldをセットしています。

   ptf = pt.PivotFields("身長")
   ptf.Orientation = XlDataField  # 集計して新たに設ける欄であるとの指定
   ptf.Function = XlAverage

　上の最初の２行は、元データの「身長」を注目欄にして、それを集計(計算)の材料にするとの指定です。

　３行目は、集計方法を「平均を求める」に設定するとの意味です。つまり、男女別に身長の平均値を算出して、集計表に盛り込むことになります。

　XlAverage の代わりに XlMax をセットすれば、平均でなく最大値を求めることになります。XlMinなら最小値です。

　以上でピボットテーブルの設定は完了。

　下に、Orientation と Function プロパティにセットできる値を列記しておきます。

• Orientation
  • XlPageField　ページ
  • XlColumnField　列
  • XlRowField　行
  • XlDataField　データ
• Function
  • XlAverage　平均
  • XlCount　データの個数
  • XlCountNums　数値の個数
  • XlMax　最大値
  • XlMin　最小値
  • XlProduct　積
  • XlStDev　標本標準偏差
  • XlStDevP　標準偏差
  • XlSum　合計
  • XlVar　標本分散
  • XlVarP　分散

(4) ピボットテーブルの項目等の設定・その２ AddFields, AddDataField

　ピボットフィールドオブジェクトの設定をもう少し簡略に記述することができます。

　項目分類の手がかりにするフィールドについては AddFields、集計の手がかりにするフィールドについては AddDataField というメソッドを使います。

   ptf = pt.PivotFields("性別")
   ptf.Orientation = XlColumnField

という２行は、次の１行に置き換えることができます。

   pt.AddFields({'ColumnFields'=>"性別"})

　また、「身長」に関する次の３行

   ptf = pt.PivotFields("身長")
   ptf.Orientation = XlDataField
   ptf.Function = XlAverage

　上の３行は、下の１行に置き換え可能です。

   pt.AddDataField({'Field'=>pt.PivotFields("身長"), 'Function'=>XlAverage})

　参考まで、AddFields, AddDataField を用いたサンプル pt01_2.rb をzip圧縮ファイルに同梱してあります。

(5) ピボットフィールドに関する調整(見出しや配置順の変更)

　ピボットテーブルを設けた段階、つまり pivot_cache.CreatePivotTable({……}) が実行された直後、ピボットフィールドが４種類用意されます。元データの４つの欄(氏名、性別、身長、体重)の各々についてピボットフィールドが用意されます。

　各々のピボットフィールドを呼び出す時は、その名前か番号で呼び出します。

   ptf = pt.PivotFields(1)

とすれば、１番つまり「氏名」のフィールドをptfにセットすることになります。これは

   ptf = pt.PivotFields("氏名")

としても同じことです。

　なお、サンプルでは「氏名」には注目せず、「性別」　「身長」　「体重」の３つを取り上げています。

a. 見出し(ラベル)の名前を変更する Caption

　ピボットフィールドには多くのプロパティがあります。Orientation, Function については既に述べました。

　その他、例えば Caption というのがあります。集計表の中で表示される見出し(ラベル)の名前をこれで設定できます。

　集計表の中で、身長の平均を示す見出しには「平均/身長」が用いられていますが、これは、Excelが自動的に割り当てた名前です。「身長の平均」に変更したければ次のようにします。

   ptf = pt.PivotFields("身長")
   ptf.Caption = "身長の平均"

b. ピボットフィールドの数値表示形式 NumberFormat

　集計結果をみると、平均値が小数点２桁まで表示されていますが、１桁まででいいという時は、NumberFormatプロパティを用います。

   ptf = pt.PivotFields("身長")
   ptf.NumberFormat = "0.0"

c. ピボットアイテムに関する設定(項目の配置替え) PivotItems

　集計結果では、性別のところが「女、男、総計」の順になっていますが、「男、女、総計」に変更したい場合は、ピボットアイテム(いわば下位項目)を呼び出すための PivotItems を使います。

　まず PivotItems の基本的な呼び出し方を示してみます。

   ptf = pt.PivotFields("性別")
   p ptf.PivotItems.Count  # => 2
   p ptf.PivotItems(1).Name  # => "女"
   p ptf.PivotItems(2).Name  # => "男"

　上は番号を用いていますが、ptf.PivotItems("女") のように名前を用いることもできます。

　で、ピボットアイテムの配置順は、Positionプロパティで指定できるので、「男、女、総計」の順にしたいのであれば次のように設定します。

   ptf.PivotItems("男").Position = 1
   ptf.PivotItems("女").Position = 2

　以上、見出しの名前、ピボットフィールドの数値表示形式、ピボットアイテムのそれぞれに関する設定を取り上げましたが、これらを盛り込んだサンプルを pt01_3.rb として同梱してあります。

(6) ピボットテーブルのプロパティ設定(総計欄の省略など)

　「pt = pivot_cache.CreatePivotTable({……})」として新たにピボットテーブルを作成し、それを変数 pt に代入した場合、例えば、

   pt.SmallGrid = false

のように、ピボットテーブルのプロパティを設定できます。プロパティにはいろいろあるようですが、私が知っているものを下に掲げます。

• SmallGrid　ピボットテーブルの青いレイアウト枠(ステンシルアウトライン)の調整
  

　これをtrue(デフォルト値)にすると、小さなレイアウト枠になる。

• RowGrand　「行」の総計欄設定の有無
  

　これをtrue(デフォルト値)にすると、「行」の総計欄が設けられる。
　pt01.rbで作られる表では、「女、男、総計」が示されるが、総計欄を出したくなければ、適当なところに
　　　pt.RowGrand = false
を記述する。

• ColumnGrand　「列」の総計欄設定の有無
  

　これをtrue(デフォルト値)にすると、「列」の総計欄が設けられる。
　今のところサンプルでは列の総計欄が出てきてないが、省略したい時はこのプロパティをfalseにする。

• GrandTotalName　総計欄の名称
  

　デフォルト値の "総計" を変更したい時に用いる。

• SaveData　データ保存の有無
  

　true(デフォルト値)を設定すると、ピボットテーブル集計表のデータがワークブックと一緒に保存される。falseなら表の定義のみが保存される。

• RefreshDate　ピボットテーブルが最後に更新された日付
  

　ピボットテーブルが最後に更新された日付を "2011/02/27 10:03:49" などの文字列で返す。値をセットすることはできない。

(7) ピボットテーブルの更新

　元データにデータが追加されたような場合、ピボットテーブルも更新する必要が出てきます。

　pt01.rbにスクリプトを追加する形で、その方法を示してみます(同梱のpt01_4.rb)。

　pt01.rbでは、10人分のデータから、性別の平均身長と平均体重を集計していますが、その後、２人分のデータを追加して、ピボットテーブルを更新してみます。

　更新は、ピボットテーブルオブジェクトの RefreshTable で行います。

　該当箇所を抜粋します。

   −−−−　ここから (pt01_4.rb 抜粋)
       # ２人分を元データに追加
   ary = [
     %w(田中 男 167.3 66.4),
     %w(須藤 女 160.5 48.9)]
   ss = wb.ss("元データのシート")
   y,x = ss.range_last
   ary.each {|row|
     y += 1
     ss.rr(y,1, y,row.size).Value = row
   }
       # 名前付き範囲の再設定
   ss.Range(range_name).CurrentRegion.Name = range_name
       # ピボットテーブルの更新
   ss2 = wb.ss("ピボットテーブルのシート")
   ss2.Activate
   pt = ss2.PivotTables(ptname)
   pt.RefreshTable
   −−−−　ここまで

　pt.RefreshTable は、Excelを実際に操作している場面でいうと、ピボットテーブルの「更新ボタンを押す」ことに相当します。

　ワークブック内にピボットテーブルが複数あって、「すべて更新のボタンを押す」相当を実現するには、ワークブックオブジェクトのRefreshAllを使います。exlap.rbを利用している場合でいうと、

   wb.obj.RefreshAll

と記述します。

　なお、名前付き範囲の再設定のところで出てくる CurrentRegion は、対象セルが含まれるアクティブ領域を示すものです。

　アクティブ領域は、空白列と空白行で囲まれた範囲です。つまり、空白行も空白列も含まない一まとまりの領域がアクティブ領域です。

　「ss.Range("A1").CurrentRegion」は、A1欄を含むアクティブ領域ということになります。

　サンプル中の「ss.Range(range_name).CurrentRegion.Name = range_name」は、従来の名前付き範囲を含む一まとまりの領域に、改めて名前を付ける、という意味になります。

　めったにないとは思いますが、もし「元データ」の中に空白行や空白列が含まれる場合は、このCurrentRegionを使うことができません。その場合は、pt01.rbで名前付き範囲を定義しているように、ちゃんと番地で範囲を指定する必要があります。

---

(1) 「性別」(男女別)の人数を集計

　ここでは、最も単純なケース、男・女それぞれの人数だけを示す表を取り上げます。出来上がりイメージは次のとおり。

性別の人数	性別
	女	男	総計
集計	5	5	10

　先の pt01.rb では性別、身長、体重の３項目に注目しましたが、今度は注目するのが性別だけです。

　「女、男、総計」の順に項目を展開するための記述は、pt01.rb と同じでいいはずですが、人数の集計をどうするのかに少々戸惑いました。

　試してみると、「身長」と同じような記述で大丈夫でした。該当部分を下に掲げてみます。

   −−−−　ここから (pt02.rb 抜粋)
       # 項目分類の注目欄として性別を指定。横方向(列)に広げる形に設定
   ptf = pt.PivotFields("性別")
   ptf.Orientation = XlColumnField  # 「列」方向に並べるフィールドとする

       # 集計用の注目欄としても「性別」を指定。個数(人数)を取る
   ptf = pt.PivotFields("性別")
   ptf.Orientation = XlDataField
   ptf.Function = XlCount
   ptf.Caption = "性別の人数"  # 「個数/性別」の見出しを変更
   −−−−　ここまで

　上のように書いてみて、一応うまくいったものの、少々釈然としません。

　「ptf = pt.PivotFields("性別")」というのが２度出てきます。もし１度目も２度目も同一のオブジェクトを返すのだとすれば、前半の記述は無駄になります。

　ですが、どうやら同じオブジェクトではなく、その都度、「性別」と結びついた新たなオブジェクトを返すのでは？と思います。

　両者をはっきり区別するという意味では、前半をptf1、後半をptf2とするのがいいかもしれません。

　ptf1 と ptf2 が異なるオブジェクトであることを確認するため実験的に書いてみたスクリプトを下に掲げておきます(関係のある箇所のみ)。

   −−−−　ここから
   ptf1 = pt.PivotFields("性別")
   ptf1.Orientation = XlColumnField  # 「列」方向に並べるフィールドとする

   ptf2 = pt.PivotFields("性別")
   ptf2.Orientation = XlDataField  # 集計の材料にするとの指定
   ptf2.Function = XlCount

   p ptf1.Orientation  # => 2.0
   p ptf1.PivotItems.Count  # => 2  ("女", "男" の２要素)
   p ptf2.Orientation  # => 4.0
   p ptf2.PivotItems.Count  # => 0
   −−−−　ここまで

　PivotItemsは、ピボットフィールドの下位項目に関する情報を取り出す時に用います。「性別」というピボットフィールドのピボットアイテム「女、男」を取り出す時に用いますが、

   ptf.Orientation = XlDataField

のように Orientation プロパティに XlDataField をセットすると(つまり集計の手がかりにする旨を指定すると)、PivotItems の要素は空になるようです。「集計の手がかりにするのだから項目として扱わない。」ということなのだろうと思います。

　それはともかく、「pt.PivotFields("性別")」が返したオブジェクト ptf1 と ptf2 が、Orientationの値に応じて異なる振る舞いをする、つまり別々のものとして扱うことができる、ということが分かります。

(2) 性別の構成比(パーセンテージ)の算出 Calculation

　先のpt02.rbでは、集計結果として性別の人数を出しましたが、人数でなく構成比(パーセンテージ)を示したい場合があります。

　この時はピボットフィールドのCalculationプロパティに適当な値をセットします。例えば、次のようにします。

   −−−−　ここから (pt02_2.rb 抜粋)
       # 集計用の注目欄としても「性別」を指定。構成比(％)を取る
   ptf = pt.PivotFields("性別")
   ptf.Orientation = XlDataField
   ptf.Function = XlCount
   ptf.Caption = "性別の構成比（％）"
   ptf.Calculation = XlPercentOfRow  # 「行」における構成比
   −−−−　ここまで

　この pt02_2.rb を実行すると、人数でなくパーセンテージが示されます。男・女ともに５人づつ、つまり各々50%なので50.00が示されます。

　Calculationにセットできる値は次のとおり。

• XlPercentOfRow　「行」における構成比
  

　横方向に足し算を行った時に得られる総計に対して各欄が占める構成比を得る。

• XlPercentOfColumn　「列」における構成比
  

　縦方向に足し算を行った時に得られる総計に対して各欄が占める構成比を得る。

• XlPercentOf　他の欄との比較比率
  

　例えば、「対前年比」などを得る時に用いる。

---

　ここでは、１つのワークシートに複数のピボットテーブルを作成するやり方、ワークブック内にピボットテーブルがあるかどうかの確認、また、その削除を取り上げます。

(1) １つのワークシートに複数のピボットテーブルを作成する

　同じ「元データ」を基にして複数のピボットテーブルを作成するのであれば、ピボットキャッシュの設定は１つだけで大丈夫です。以下では１つの「元データ」から複数のピボットテーブルを作成することを考えます。

　１つのワークシートに１つのピボットテーブルを作成する例は、これまで掲げたサンプルから簡単に類推できると思います。

　一方、同一のワークシートに２つのピボットテーブルを作る時は、第１ピボットテーブル集計表の後に何行か空白行を空けて、それから第２を作成することになると思います。なので、第１ピボットテーブル集計表の最終番地を取得する必要があります。

　これは、ピボットテーブルの TableRange2 メソッドで行います。ptがピボットテーブルオプジェクトを示している場合、pt.TableRange2 は、ピボットテーブルの集計表が占める領域全体を表します。

　仮に pt が A1:D4 の４×４の領域を占めているのだとすれば、pt.TableRange2 は、ss.Range("A1:D4") と等価です(ssはワークシートオブジェクト)。

　以上のことが分かれば、１つのワークシートに複数のピボットテーブルを作成するのは容易です。

　ちなみに、TableRange2 が「ページフィールドも含めたピボットテーブルの集計表全体」を表すのに対し、TableRange1 は、ページフィールドを含まない領域を表します。なお、これまで掲げたサンプルではページフィールドを扱っていません。

　以下に pt03.rb の抜粋を掲げます。１つのワークシート内に、性別の平均身長と平均体重の表(pt01.rb)、そして、性別の人数(pt02.rb)を配置するスクリプトです。注目箇所のみ示しておきます。

   −−−−　ここから (pt03.rb 抜粋)
       （前略）
   filename = "test03.xls"
   Exlap.new(filename) {|wb|
       （中略）
         # 第１のピボットテーブルを作成
     ptname = "ピボット01"  # ピボットテーブルの名前
     pivot_cache.CreatePivotTable({
       'TableDestination'=>ss2.Range("A1"),  # ピボットテーブルの始点
       'TableName'=>ptname})
     pt = ss2.PivotTables(ptname)  # ピボットテーブルオブジェクトをptに
     pt.SmallGrid = false
       （中略）

         # 第２ピボットテーブル作成の準備
     rng = pt.TableRange2  # 第１ピボットテーブルのRangeをrngに代入
     y,x = ss2.range_last(rng)  # 第１ピボットテーブルの最終番地を得る
     start_cell = ss2.cell(y+3,1)  # 第２ピボットテーブルの始点を２行空け後に

         # 第２ピボットテーブルの作成
     ptname = "ピボット02"  # ピボットテーブルの名前
     pivot_cache.CreatePivotTable({
       'TableDestination'=>start_cell,
       'TableName'=>ptname})
     pt = ss2.PivotTables(ptname)
     pt.SmallGrid = false
       （中略）
     wb.save
   }
   −−−−　ここまで

　「range_last(rng)」は、与えられた領域rngの最終番地を y, x の座標数値で返すもので、exlap.rbの中で定義されています。

(2) ワークブック内のピボットテーブルの存在有無等を確認する

　与えられたワークブックにピボットテーブルが含まれているのかどうか、そして、含まれているなら、その名前は何かを調べるスクリプト pt03_2.rb を掲げてみます。

　pt03.rbを実行すると、２つのピボットテーブルを含む test03.xls が作成されるので、それを調べる形にします。

　短いので全部を載せます。

   −−−−　ここから (pt03_2.rb)
   #! ruby -Ks
   require "exlap"

   filename = "test03.xls"
   Exlap.new(filename) {|wb|
     wb.each {|ss|
       next  if ss.PivotTables.Count < 1  # ピボットテーブルがないのでskip
       puts "*ワークシート名:" + ss.Name
       ss.PivotTables.each {|pt|
         puts "\t" + pt.Name
       }
     }
   }
   −−−−　ここまで

(3) ピボットテーブルの削除

　ピボットテーブルを削除するための Delete とか Clear といったメソッドは、私が調べたかぎりでは見当たりませんでした。ptにピボットテーブルオブジェクトがセットされている場合、pt.Delete とか pt.Clear として削除することができないようです。

　それならどうするかというと、pt.TableRange2.Clear として削除します。TableRange2は、「ページフィールドも含めたピボットテーブルの集計表全体」を表します。なので、その領域(Range)をClearした場合、そこに書き込まれていたデータは消去されると思いますが、ピボットテーブルの定義そのものまで削除してしまうわけではないのでは？というのが私の疑問でした。

　しかし、pt.TableRange2.Clear とすれば、ピボットテーブルそのものがなくなってしまうようです。なんとも腑に落ちない感じですが、そのようなものらしいです。

　一応、ワークブック内のピボットテーブルを総て削除するサンプル pt03_3.rb を掲げておきます。短いので全部載せます。

   −−−−　ここから (pt03_3.rb)
   #! ruby -Ks
   require "exlap"

   filename = "test03.xls"
   Exlap.new(filename) {|wb|
     wb.each {|ss|
       next  if ss.PivotTables.Count < 1  # ピボットテーブルがないのでskip
       ss.Activate
       ss.PivotTables.each {|pt|
         pt.TableRange2.Clear
       }
     }
     wb.save
   }
   −−−−　ここまで

---

　同じワークブックに書き込まれているデータではなく、別のExcelワークブックやAccessデータベースを材料にしてピボットテーブルを作成することができます。

　クエリテーブルを作る場合と同じように、odbc接続、ado接続の２種類のやり方を採ることができます。

　要点は、接続用の文字列と接続方法、それから、ピボットキャッシュに関する設定の２つです。

　ピボットキャッシュを設けた後のピボットテーブルの作成方法は、これまでサンプルに出てきたやり方と同じです。

　以下、別のExcelワークブックおよびAccessデータベースについて、odbc接続とado接続のそれぞれを示します。

　サンプルとしては pt04.rb〜pt04_4.rb の４つが該当します。

　いずれも、性別の人数を示す表の作成です(pt02.rbと同じ)。「女 5人、男 5人、総計 10人」を示す簡単な表です。

　同梱の source.xls, source.mdb を元データとして用います。

　Office2007が導入されている環境であれば、Excel2007用のxlsxファイル、Access2007用のaccdbファイルを元データにすることも可能です。pt04.rb〜pt04_4.rbは、それらファイルに対応できる形で書いてあります。

（注）　ado接続でピボットテーブルを作成した場合、ピボットテーブルの更新(Excelを操作している場面における「更新ボタンの押下」に相当)が単純な形ではうまくいきません。odbc接続で問題なければ、そちらを選ぶ方がいいと思います。

(1) Excelワークブックにodbc接続

　まずはサンプル pt04.rb から、関連する記述を掲げます。

   −−−−　ここから (pt04.rb 抜粋)
   source_name = "source.xls"
   cnn = "ODBC;DSN=Excel Files;DBQ=#{Exl::getAbsolutePath(source_name)}"
   tblname = "性別の身長と体重"  # テーブル名、即ちワークシート名
   sql = "SELECT * FROM [#{tblname}$];"  # 総てのデータを得るためのsql命令

   filename = "test04.xls"
   Exlap.new(filename) {|wb|
         # シートにピボットテーブルを設定
     ss = wb.fes
     ss.Name = "ピボットテーブルのシート"
     ss.Activate  # ピボットテーブルを設けるシートに焦点を当てる
     pivot_cache = wb.obj.PivotCaches.Add({
       'SourceType'=>XlExternal})
     pivot_cache.Connection = cnn
     pivot_cache.CommandText = sql
   −−−−　ここまで

○　接続用文字列

　接続用文字列は、cnnという変数に代入しています。

   "ODBC;DSN=Excel Files;DBQ=#{Exl::getAbsolutePath(source_name)}"

　上の「Exl::getAbsolutePath(source_name)」は、source_nameのフルパスを得るものです。

○　sql命令文

　sql命令文の「SELECT ……」は、source_nameの該当テーブル(ワークシート)の総てのデータを選択する、という意味です。

　このsqlの中に「[#{tblname}$]」というのがあります。このドル記号は、tblnameがワークシートの名前であることを明示するために付けてあります。つまり、ワークシート内の全データを「元データ」として用いる意味になります。

　ドル記号がないと、tblnameがワークシート名でなく「名前付き範囲」の名前であると解釈されます。ただ、この場合は(source.xlsに名前付き範囲の定義はありませんが)ドル記号なしでも大丈夫だと思います。「名前付き範囲」の定義がない時はワークシート全体を用いる、というルールになっているのだと推測します。

　'[' と ']' で囲んであるのは、sql命令文の中で特殊な意味を持つことのあるドル記号が、ここではテーブル名の一部である、ということを明示するために付けてあるものです。ドル記号を付けないのであれば、括弧で囲む必要はありません。

○　ピボットキャッシュの設定

　ピボットキャッシュの設定のところでは

   pivot_cache = wb.obj.PivotCaches.Add({
     'SourceType'=>XlExternal})

として、まずワークブックにピボットキャッシュを追加しています。XlExternal が外部データに接続する時に指定する値です。

　このAddメソッドの記述は、次のように省略型で書くこともできます。

   pivot_cache = wb.obj.PivotCaches.Add(XlExternal)

　このあと、

   pivot_cache.Connection = cnn
   pivot_cache.CommandText = sql

として、接続用文字列とsql命令文をセットしています。

(2) Accessデータベースにodbc接続

　Accessデータベースにodbc接続する時も、基本的には先述のExcelワークブックへの接続と同じです。

　違う点は、接続用文字列の cnn、それと sql命令文です。

　以下に、pt04_2.rbから関連する箇所を抜粋します。説明の必要はないと思うので省略します。

   −−−−　ここから (pt04_2.rb 抜粋)
   source_name = "source.mdb"
   cnn = "ODBC;DSN=MS Access Database;DBQ=#{Exl::getAbsolutePath(source_name)}"
   tblname = "性別の身長と体重"
   sql = "SELECT * FROM #{tblname};"

   filename = "test04_2.xls"
   Exlap.new(filename) {|wb|
         # シートにピボットテーブルを設定
     ss = wb.fes
     ss.Name = "ピボットテーブルのシート"
     ss.Activate  # ピボットテーブルを設けるシートに焦点を当てる
     pivot_cache = wb.obj.PivotCaches.Add({
       'SourceType'=>XlExternal})
     pivot_cache.Connection = cnn
     pivot_cache.CommandText = sql
   −−−−　ここまで

(3) Excelワークブックにado接続

　ado接続の場合、接続用文字列が長くなり、接続のための手順が少々面倒になりますが、クエリテーブルを作る時と同じパターンなので、それを知っていれば容易に類推できます。

　まずはサンプル pt04_3.rb を抜粋します。

   −−−−　ここから (pt04_3.rb 抜粋)
   source_name = "source.xls"
   tblname = "性別の身長と体重"
   sql = "SELECT * FROM [#{tblname}$];"

       # ado接続の準備
   cnn_xls = "Provider=Microsoft.Jet.OLEDB.4.0;" +
       "Data Source=#{Exl::getAbsolutePath(source_name)};" +
       "Extended Properties=\"Excel 8.0;HDR=Yes;\""
   cnn_xlsx = "Provider=Microsoft.ACE.OLEDB.12.0;" +
       "Data Source=#{Exl::getAbsolutePath(source_name)};" +
       "Extended Properties=\"Excel 12.0;HDR=Yes;\""
   cnn = nil
   case File.extname(source_name).downcase
   when '.xls'
     cnn = cnn_xls
   when '.xlsx'
     cnn = cnn_xlsx
   end
   unless cnn
     exit
   end
   cn = WIN32OLE.new("ADODB.Connection")
   rs = WIN32OLE.new("ADODB.Recordset")
   rs.CursorLocation = 3  # adUseClient  クライアント側カーソルを使用

   filename = "test04_3.xls"
   Exlap.new(filename) {|wb|
         # シートにピボットテーブルを設定
     ss = wb.fes
     ss.Name = "ピボットテーブルのシート"
     ss.Activate  # ピボットテーブルを設けるシートに焦点を当てる
     cn.Open cnn;  rs.Open sql,cn  # ado接続
     pivot_cache = wb.obj.PivotCaches.Add({
       'SourceType'=>XlExternal})
     pivot_cache.Recordset = rs
     rs.Close;  cn.Close  # adoを閉じる
   −−−−　ここまで

○　接続用文字列

　接続用文字列は、xls用とxlsx用の２つを設けて、source_nameの拡張子に応じて必要な方を使うようにしています。

　xls用文字列は、「Provider=Microsoft.Jet.OLEDB.4.0;……」としてありますが、これは、ユーザーIDとかパスワードの指定を省略した形です。他にもいくつかオプションを盛り込むことができるようですが、ここでは必要最低限のものを示しています。

　最後の方にある「HDR=Yes」は、第１行目をフィールド名とみなすとの指定です。Accessデータベースなど専用のデータベースでは、フィールド名とデータ本体とは別々に管理されます。しかし、Excelはそうした仕組みになっていないので、このように第１行目をフィールド名とみなすか否かを指定できるようになっているのだと思います。

○　sql命令文

　sql命令文は、pt04.rbと同じです。

○　ado接続手順

　ado接続の手順は、サンプルに掲げたとおりです。

   rs.CursorLocation = 3

という記述は、環境によっては必要ないのかもしれませんが、私のところではこれがないとエラーになります。「クライアント側カーソルを使用」という意味のようですが、詳しくは分かりません。

○　ピボットキャッシュの設定

　ピボットキャッシュの設定のところでは

   pivot_cache.Recordset = rs

として、レコードセットオブジェクトを与えています。

　そして、その後、rs.Close と cn.Close を記述し、ado接続を閉じます。

(4) Accessデータベースにado接続

　Accessデータベースにado接続する方法については、 pt04_4.rb を参照して下さい。

　特に解説の必要はないと思うので、ここには掲げません。

　Access2003までのmdbファイルと、Access2007以降のaccdbファイルの両方に対応するため、少しごちゃごちゃしていますが、やっていることは単純です。

　以上、外部データを材料にしてピボットテーブルを作成する方法について記しました。

　ここでは触れませんが、適当なodbcドライバをインストールすれば、sqlite3など他のデータベースに接続することも可能です。

(5) odbc接続で外部データと結びつけられたピボットテーブルの更新

　pt04.rbは、外部データ source.xls を材料にして、ピボットテーブルを作成するサンプルでした。これを実行すると、test04.xlsが作成され、その第１ワークシートにピボットテーブルが設けられます。

　では、その後で source.xls にデータが追加されて、10人分のデータが12人分になった場合、それをtest04.xlsのピボットテーブルの集計表に反映させるにはどうしたらいいかですが、ピボットテーブルの RefreshTable を用います。

　今度は、source.xlsと結びつけるための odbc接続の設定は必要ありません。ワークブックを開いて、最初のワークシートに焦点を当て、該当のピボットテーブルを呼び出して RefreshTable を実行するだけです。

　具体的には次のようになります(サンプルとして同梱してはいません)。

   −−−−　ここから
   #! ruby -Ks
   require "exlap"
   Exlap.new("test04.xls") {|wb|
     ss = wb.ss("ピボットテーブルのシート")
     ss.Activate
     pt = ss.PivotTables("ピボット01")
     pt.RefreshTable
     wb.save
   }
   −−−−　ここまで

　それから、ピボットキャッシュの Refresh を使う方法もあります。

　上に出てくる次の１行

   pt.RefreshTable

　これを下の２行に置き換えても更新できます。

   pivot_cache = pt.PivotCache
   pivot_cache.Refresh

　残念ながら、ado接続方式で作成したピボットテーブルの場合は、上の２種類ともそのままでは使えないようです。私のところでは、どちらを実行してもエラーになります。

　あえていえば、改めてado接続を行った上で、そのレコードセットを再代入すれば、ピボットキャッシュのリフレッシュを行うことができます。参考まで、 pt_ado01.rb (test04_3.xlsのリフレッシュ)、 pt_ado02.rb (test04_4.xlsのリフレッシュ)を同梱しておきます。

　改めてado接続を行うために、ソースファイル名、そのテーブル名、sql命令文、ado接続用文字列を別途保持しておかなければならないところがこの方法の難点です。もっと簡便な方法があるような気がしますが、よく分かりません。

---

　以上、とても簡単な集計表だけを取り上げましたが、当然ながら実務ではもっと複雑な集計が必要になります。

　また、グループ化やグラフなど、ピボットテーブルでよく用いるノウハウに触れていません。

　いずれ書き残した部分についても記したいと思いますが、まずは第１版ということで、ここまでで終了。

Copyright (C) T. Yoshiizumi, 2011 All rights reserved.