T. Yoshiizumi - exlap_pt03 Diff
- Added parts are displayed like this.
- Deleted parts are displayed
like this.
最終更新日：
Excelのピボットテーブル利用について記します。
第１版・第２版の続編として、「6. ページフィールド」および「7. 複数の元データから集計表を作成する」について書きます。
サンプルスクリプトの実行環境は、第１版の時と同じです。
[[pt_test03.zip|http://cup.sakura.ne.jp/pt_test03.zip]]
には、関連のサンプルスクリプトなどが含まれています。
----
{{toc_here}}
----
!6. ページフィールド
ここではページフィールドを取り上げます。
これまでのサンプルでは、ピボットフィールドを列方向(横方向)に広げるか、行方向(縦方向)に広げるか、あるいは、集計(計算)の手がかりにするかのどれかに指定してきました。
列方向に指定したものが列フィールド(ColumnField)、行方向に指定したものが行フィールド(RowField)、集計の手がかりにしたものがデータフィールド(DataField)です。
その他に、ページフィールド(PageField)があります。ピボットフィールドをページフィールドに指定すると、該当の項目分類ごとにワークシートを別にして集計表を作成できます。
例えば、支店・日付・売上げの３項目からなる元データがある場合、支店別にワークシートを替えて集計表を作ることができます。あるいは、日付に着目して、第１四半期〜第４四半期の４つのワークシートからなる集計表を作ることもできます。以下で、実際にその処理を取り上げます。
元データがそれなりの大きさになるので、source06.xls という別ファイルを用意しました。これを読み込んでピボットテーブルを作成します。
元データの第１行目は、支店、日付、売上げの３項目の名前が横に並んでいます。
２行目以降がデータですが、支店には東京、名古屋、大阪の３種類があり、その順番で出てきます。つまり、第２行目には東京、その下の３行目には名古屋、４行目には大阪がきて、それが縦方向に繰り返されます。
支店の右隣の日付は、2010年1月1日から同年の12月31日までです。これが縦に並んでいますが、３つの支店について同じ日付のものがあるので、１月１日が３つ、１月２日も３つ、という具合に並んでいます。365×3=1095行あることになります。
売上げは、各支店の各日付について、10〜99の範囲の数値が縦に並んでいます。乱数発生させた数値です。
以下のサンプルでは、日付を四半期単位でグループ化して集計します。
売上げは、支店ごと、四半期ごとの「合計」を集計します。
''［参考］ source06.xlsの扱い方''
この項では、元データのあるワークブック source06.xls およびピボットテーブル保存用の test06.xls などの２つを両方とも開いた状態にして処理を行います。こうすることによって、source06.xlsにodbcやadoで接続しなくても、元データを読み込むことができます。
この方式を採る時の要点は、ピボットキャッシュ設定時の SourceData にセットする値をどうするかです。SourceDataには、元データの所在(どのワークシートのどの領域にあるのか)を示す文字列をセットします。例えば、"Sheet!R1C1:R4C4" などです。
実は、この文字列には、現在開いているワークブックの名前を盛り込んで
"[Book1.xls]Sheet1!R1C1:R4C4"
のように記述することができます。
ワークブックの名前は、フルパスでないものを用います。VBAふうに書くなら ActiveWorkbook.Name です(ActiveWorkbook.FullNameではありません)。
この項に関連するサンプルスクリプト pt06.rb などでのワークブックの開き方がこれまでのサンプルと違いますが、２つのワークブックを同時に開くため異なる形になっています。
!!(1) ページフィールド指定の前に、クロス集計表
先ずは単純なクロス集計表を作ってみます。ページフィールドは、とりあえず指定しません。
集計表のイメージは、列方向(横方向)に３つの支店(東京、名古屋、大阪)が並びます。
行方向(縦方向)には第１四半期〜第４四半期の４つが並びます。
つまり、３×４(「総計」を含めると４×５)のクロス集計表を作成します。できあがりのイメージは次のとおり。
||合計／売上げ||支店
||日付||東京||名古屋||大阪||総計
||第1四半期||4828||4866||4640||14334
||第2四半期||5276||4878||4866||15020
||第3四半期||5255||5102||5021||15378
||第4四半期||4801||4752||4948||14501
||総計||20160||19598||19475||59233
参考まで、上の集計表を作るためのスクリプト pt06.rb を下に掲げておきます。ピボットキャッシュやピボットテーブルを設けるところまでは省略し、ピボットフィールドを設定するところのみを掲げます。
これまで解説したことだけで書かれているスクリプトです。追加説明するような新しいノウハウは使っていません。
−−−− ここから (pt06.rb 抜粋)
# 集計表の設定
ptf1 = pt.PivotFields("支店")
ptf1.Orientation = XlColumnField
ptf2 = pt.PivotFields("日付")
ptf2.Orientation = XlRowField
ptf3 = pt.PivotFields("売上げ")
ptf3.Orientation = XlDataField
ptf3.Function = XlSum
ary = [false]*5 + [true, false] # 四半期のみでのグループ化
ptf2.LabelRange.Group({'Periods'=>ary})
%w(東京 名古屋 大阪).each_with_index {|name, i|
ptf1.PivotItems(name).Position = i+1
}
−−−− ここまで
最後の方でピボットアイテムの Position を設定し直しているのは、Excelにお任せだと「大阪、東京、名古屋」の順になってしまうので、それを「東京、名古屋、大阪」に並べ替えているものです。
!!(2) ページフィールドを用いて支店ごとにワークシートを替える(ShowPages)
前述のような簡単なクロス集計表であれば、わざわざ支店ごとにワークシートを替えて集計表を作るほどのことはありませんが、あえてページフィールドを用いてみます。
といっても、前述のスクリプトの XlColumnField を XlPageField に変更し、最後の方に
pt.ShowPages
を記述するだけです。
この２箇所の変更で、ワークシートが４つ作られます。３つの支店に関するシート、それと、総計を示すシートの計４つです。
ワークシートの名前は、「東京」 「名古屋」 「大阪」 「基盤となるシート」です。
最後の「基盤となるシート」は、スクリプトでピボットテーブルを定義する時に、該当のワークシートに付けた名前です。いわば意図的に付けた名前です。それ以外は、Excelが自動的に割り当てた名前です。
同梱の pt06_2.rb からピボットフィールド設定の箇所を抜粋しておきます。
−−−− ここから (pt06_2.rb 抜粋)
# 集計表の設定
ptf1 = pt.PivotFields("支店")
ptf1.Orientation = XlPageField
ptf2 = pt.PivotFields("日付")
ptf2.Orientation = XlRowField
ptf3 = pt.PivotFields("売上げ")
ptf3.Orientation = XlDataField
ptf3.Function = XlSum
ary = [false]*5 + [true, false] # 四半期のみでのグループ化
ptf2.LabelRange.Group({'Periods'=>ary})
%w(東京 名古屋 大阪).each_with_index {|name, i|
ptf1.PivotItems(name).Position = i+1
}
pt.ShowPages
−−−− ここまで
この pt06_2.rb を実行して得られる集計表のうち、「東京」に関するものは次のような形になります。
||支店||東京
||合計／売上げ||
||日付||合計
||第1四半期||4828
||第2四半期||5276
||第3四半期||5255
||第4四半期||4801
||総計||20160
スクリプト中の「ptf1.Orientation = XlPageField」は、「支店」に関連づけられたピボットフィールドをページフィールドに指定するものです。このように指定することで、１つの支店に関する集計表が１つのワークシートに書き出されることになります。
「pt.ShowPages」は、Excel操作時の「ページの表示」の実行に相当します。これを記述しないと、総計に当たる「(すべて)」のワークシートだけが作成され、支店ごとのシートは作られません。
ShowPagesは、オプションを指定して
pt.ShowPages({'PageField'=>"支店"})
あるいは
pt.ShowPages({'PageField'=>ptf1.Name})
のように記述することができます。
pt06_2.rbでは、ページフィールドとして指定するピボットフィールドが１つだけなので、オプション記述を省略してますが、丁寧に書くなら上のようにオプションを記述します。
!!(3) 特定のピボットアイテムを非表示にする(Visible, RecordCount)
前述の pt06_2.rb では、３つの支店を総てワークシートに書き出しました。しかし、例えば、東京支店を書き出さなくてもいいというようなケースがあります。
このような場合は、ピボットアイテムの Visible プロパティを false にします。具体的には
ptf1.PivotItems("東京").Visible = false
という記述を「pt.ShowPages」の直前に置くと、東京支店に関するワークシートが作られません。
四半期に着目してワークシートを分けた時に、この非表示の必要性がもっと高まるので、そちらに話を移します。
pt06_2.rbでは、支店に関するピボットフィールドをページフィールドに指定しました。
同じように、日付のピボットフィールドを四半期単位でグループ化した後でページフィールドに指定すれば、第１四半期〜第４四半期のワークシートが作られるはずです。
ところが、pt06_2.rbを単純に書き換えただけだと、意図しない結果になってしまいます。
四半期単位でグループ化した場合、"<2010/1/1" および ">2011/1/1" という２つの「範囲外」も書き出されてしまいます(もちろん集計の中身は空です)。
pt06.rbで単純なクロス集計表を作った時は、この「範囲外」が出ませんでしたが、なぜかページフィールド指定すると出てしまいます。そこで、スクリプトを書く側がそれを抑制してやる必要があります。
日付を四半期単位でグループ化した場合は、ピボットアイテムの１番目と６番目が範囲外であるというのが確実なので、次の２行を書き加えれば目的を達成できます。
ptf2.PivotItems(1).Visible = false
ptf2.PivotItems(6).Visible = false
上の２行を「pt.ShowPages」の直前に置けば、「範囲外」が書き出されることはなくなります。
今回は四半期に着目したので１番目と６番目というように番号で指定できますが、しかし、中身が空の集計の番号が何番になるのか、予め分からないケースもあります。
そのような時に使える一つの手段がピボットアイテムの RecordCount プロパティです。
RecordCountは、(私の解釈では)そのアイテムの集計の材料となった元データのレコードの数(Excelにおいては行の数)を返すものです。
例えば、第１四半期に関するRecordCount
count = ptf2.PivotItems(2).RecordCount
の値は 270 です。第１四半期の１〜３月の日数は、31+28+31=90、これが３支店分あるので 90*3=270 です。
四半期の「範囲外」については、当然ながら集計の材料になるレコードがないので
count = ptf2.PivotItems(1).RecordCount
の値は 0 となります。
これを利用すれば、多くの場合について、集計の中身が空のものを非表示に設定できます。ptfがページフィールドに指定されたピボットフィールドのオブジェクトであるとき、次のように記述すれば実現できます。
ptf.PivotItems.each {|ptfi|
if ptfi.RecordCount < 1
ptfi.Visible = false
end
}
pt.ShowPages
以上のことを踏まえて書いてみたのが同梱の
[[pt06_3.rb|http://cup.sakura.ne.jp/exlap/pivot.htm#pt06_3]]
です。ここに掲げるのは省略しますが、よかったら参考にして下さい。
なお、今回はピボットアイテムの Visible, RecordCount をページフィールドに関連して述べましたが、これらは、列フィールドや行フィールドのピボットアイテムの場合も同じように利用できます。
''［補足１］ 「(すべて)」は非表示にできない？''
クロス集計表の「総計」に当たる「(すべて)」は、ピボットアイテムの中には含まれていません。つまり、ピボットアイテムではありません。そのため、ピボットアイテムのVisibleプロパティで非表示にすることができません。
列フィールドや行フィールドに関連する「総計」欄は、pt.RowGrand または pt.ColumnGrand に false をセットすることによって非表示にできますが、ページフィールド指定時の「(すべて)」については非表示にできないようです。少なくとも、私があれこれ試したところでは行えませんでした。
どうしても「(すべて)」を残したくない時は、いったんワークブックを保存した後で、それを開き直して該当のワークシートを削除する、ということになるのではないかと思います。
''［補足２］ ページフィールドとグループ化''
pt06_2.rbでは、支店に関するピボットフィールドptf1を最初からページフィールドに指定しました。
しかし、pt06_3.rbでは日付に関するピボットフィールドをいきなりページフィールドにすることはせず、いったん行フィールドにしてグループ化を施し、その後でページフィールドにしています。
これは、ページフィールドに指定したものに対してグループ化を施すことができないためです。
ページフィールドにしたままだと処理を加えられないケースは、他にもあるようです。そのような時は、とりあえず行フィールドや列フィールドにしておいて処理し、その後でページフィールドに切り替える、そんなふうにするのが常套手段のようです。
''［補足３］ 単純なクロス集計表で範囲外を表示させるためには(ShowAllItems)''
pt06.rbで単純なクロス集計表を作った時は、四半期の範囲外 "<2010/1/1" および ">2011/1/1" が表示されません。しかし、あえて表示させたいケースもあるかもしれません。その時は、
ptf2.ShowAllItems = true
という１行をグループ化した後に書いておくと、表示されるようになります。
ShowAllItemsは、ピボットフィールドのプロパティの一つで、これを true にすると、集計表に集計データが含まれていなくてもすべてのアイテムが表示されます。デフォルトは false です。そのため「範囲外」が表示されません。
ただし、ピボットフィールドをページフィールドに指定した場合は、この ShowAllItems が false であっても「範囲外」が出てしまいます。そこで先に述べたような処理が必要になるわけです。
!!(4) 複数のワークシートにおけるピボットテーブルの存在確認・更新
pt06_2.rbでは、ピボットテーブルを１つ定義しただけで複数のワークシートが作られました(pt06_3.rbも同様です。)。
このような場合、作成された test06_2.xls においてピボットテーブルの存在がどうなっているのか気になります。このワークブックを開いてピボットテーブルの存在をチェックしたとき、ピボットテーブルが一つしかみつからないのか、それとも、各ワークシートにそれぞれピボットテーブルがあるのか。
調べてみると、結果は後者でした。ピボットテーブルが複数存在する形になっています。
ピボットテーブルの名前は、スクリプト中で定義したものが「基盤となるピボット」です。ちなみに、それが置かれているワークシートの名前は「基盤となるシート」です。
test06_2.xlsを調べてみると、「東京」のワークシートにあるピボットテーブルの名前が「ピボットテーブル3」、「名古屋」が「ピボットテーブル2」、「大阪」が「ピボットテーブル1」です(これら３つの名前の「ピボットテーブル」はいずれも半角カタカナ)。そして、総計に当たる「(すべて)」が「基盤となるピボット」となっています。
ピボットテーブルを更新したり削除したりする場合、関連のワークシートとピボットテーブルが４つ存在することを念頭に置く必要があります。
ピボットテーブルの削除は、pt.TableRange2.Clearで行います。
TableRange2がページフィールドを含むRange、TableRange1がページフィールドを含まないRangeです。今回の例はページフィールドがあるので、TableRange2 の方を用いることになります。
ただ、個々のピボットテーブルを削除するよりも、実務ではワークシートごと削除した方がいいケースが多いように思います。exlap.rbを使っている場合は、
wb.delete_sheet("東京")
とすれば、ワークシート名が「東京」のシートを削除できます。
次に、ピボットテーブルの更新ですが、こちらは少々ややこしいです。
そもそもは、ピボットテーブルを１つしか定義しませんでした。しかし、それにより作られたワークブックには４つのピボットテーブルが含まれています。
それでは、元データが書き換えられた場合、それを取り込むためにどうすればいいか、ちょっと迷います。
一つには、４つのピボットテーブルを個別に更新する方法が考えられます(この場合は ShowPages を実行しない)。しかし、いつもそれで対応できるとは限りません。
例えば、東京・名古屋・大阪の３つの支店しかなかった元データに、新たに福岡支店が追加されたとしたら、その更新はどうしたらいいか。既に設けられている４つのピボットテーブルを個別に更新するだけでは対応できないことが容易に想像できます。
test06_2.xlsについて私が試した結果からいうと、次のようにするのが無難なのでは、と思います。
#３つのワークシート「東京」 「名古屋」 「大阪」を削除。
#「基盤となるシート」の「基盤となるピボット」を更新。
#ShowPagesを実行。
上を実行するスクリプトを下に掲げてみます。
−−−− ここから
#! ruby -Ks
require "exlap"
filename = "test06_2.xls"
Exlap.new(filename) {|wb|
wb.delete_sheet("東京")
wb.delete_sheet("名古屋")
wb.delete_sheet("大阪")
ss = wb.ss("基盤となるシート")
pt = ss.PivotTables("基盤となるピボット")
pt.RefreshTable
pt.ShowPages
wb.save
}
−−−− ここまで
上のスクリプトを実行すると、元データに新たな支店が追加されたような場合でも、それが集計表にちゃんと反映されます。
仮に「東京」などのワークシートを削除しないまま pt.RefreshTable を実行すると、更新された test06_2.xls には「東京」の他に「東京 (2)」の名前のシートができます。「名古屋」 「大阪」についても同様です。実害はないとしても少々煩わしい形になってしまいます。
それから、もし「東京」にあるピボットテーブルを更新して ShowPages を実行した場合はどうなるか。やってみると、「基盤となるピボット」を更新した時とほぼ同じ結果を得ることができます。
「ほぼ同じ」というのは、今回は「東京 (2)」が設けられない点が違うためです。といっても、「名古屋 (2)」と「大阪 (2)」が作られる点は変わりません。
この辺の細かな仕様は別として、要するに、最初の元祖ピボットテーブルから派生した複数のピボットテーブルは、そのどれをRefreshTableしても、ShowPagesを実行することで一連の複数の集計表を更新できます。１つしか更新できないわけではありません。
とはいっても、やはり最初に設けた「基盤となるピボット」をRefreshTableして ShowPagesを実行するのが基本だろうと思います。
----
!7. 複数の元データから集計表を作成する
ここでは、複数の元データから１つの集計表を作成する方法を取り上げます。
前項では、三つの支店のデータが一つの「元データ」になっている例を上げました。しかし、実際には支店ごとに別々のワークシート(あるいは別々のワークブック)になっているケースが自然だと思います。
そこで、各支店の「元データ」が別々のワークシートに記録されている場合を取り上げます。東京支店のデータが「東京のシート」、名古屋は「名古屋のシート」、大阪が「大阪のシート」に記録されている source07.xls を材料にします。
各ワークシートには「日付」と「売上げ」の２項目が横に並び、縦方向には365日分の日付と売上げが並んでいます。
このsource07.xlsを材料にして、一つのクロス集計表を作成します。クロス集計表は、横方向に「東京、名古屋、大阪、総計」が並び、縦方向に第１四半期〜第４四半期と総計が並ぶ形です。売上げの合計を算出します。
!!(1) 複数の元データを統合して取り込む
複数の「元データ」を統合して取り込む場合は、ピボットキャッシュを設定する時に次のようにします。
pivot_cache = wb.obj.PivotCaches.Add({
'SourceType'=>XlConsolidation,
'SourceData'=>ary})
まず、SourceTypeの値として XlConsolidation を指定します。既に取り上げたサンプルでは、XlDatabase(Excel上の１つの元データ)、XlExternal(外部アプリケーションのデータ)のものがありました。
SourceDataの方は、配列 ary をセットしますが、aryの内容を単純化して書くと次のようになります。
［[領域1, 名前1], [領域2, 名前2], [領域3, 名前3], ……］
ここでいう「領域」は、元データの所在を示すものです。どのワークブックの、どのワークシートの、どの番地かを記した文字列です。
source07.xlsの３つのワークシートを取り込む場合は、SourceDataにセットするaryを次のように書きます。
［["[source07.xls]東京のシート!R1C1:R366C2", "東京"],
["[source07.xls]名古屋のシート!R1C1:R366C2", "名古屋"],
["[source07.xls]大阪のシート!R1C1:R366C2", "大阪"]］
要するに、１つの元データについて、その「領域」と「名前」の組を必要な数だけ記述するわけですが、その時の「名前」は、好きなものを割り当ててかまいません。といっても、それが後でピボットアイテムの名前になるので、集計表の項目分類名として使えるものにしておくのがいいと思います。
文章で説明しているだけではイメージしにくいと思うので、pt07.rbのピボットキャッシュを設定するところまでを抜粋します。
−−−− ここから (pt07.rb 抜粋)
#! ruby -Ks
# 複数の元データを統合して取り込んだ時の初期状態を確認
require "exlap"
# 元データのファイルを開く
xl = Exlap.new
wbs = xl.book_open "source07.xls"
source_data = []
wbs.each {|ss|
if ss.Name =~ /^(.+)のシート$/
branch = $1
area = "[#{wbs.obj.Name}]#{ss.Name}!" +
ss.UsedRange.Address(true, true, XlR1C1)
source_data << [area, branch]
end
}
# ピボットテーブル保存用のワークブックを開く
wb = xl.book_open "test07.xls"
ss = wb.fes
ss.Name = "ピボットテーブルのシート"
ss.Activate # ピボットテーブルを設けるシートに焦点を当てる
pivot_cache = wb.obj.PivotCaches.Add({
'SourceType'=>XlConsolidation,
'SourceData'=>source_data})
−−−− ここまで
source07.xlsの３つのワークシートを取り込むに当たって、まず、そのシート名が「東京のシート」などのように「○○のシート」というパターンになっていることを利用します。この「○○」の部分を正規表現で抽出し、支店名として変数 branch に代入します。そして、そのbranchを「領域、名前」の「名前」として用います。
「領域」の取得方法に関する詳細は省略しますが、
ss.UsedRange.Address(true, true, XlR1C1)
というのは、ワークシート中で実際にデータがある領域の番地を "R1C1:R366C2" といったパターンで得るためのものです。
「領域」(ワークブック名・ワークシート名・番地)の方は、変数areaに代入します。
このようにして、１つのワークシートについて branch, area を得たら、配列 [area, branch] をより大きな配列である source_data に入れます。
あとは、test07.xlsを開き、空のワークシートを選んで、そこにピボットキャッシュを設定します。この辺については説明を繰り返す必要はないだろうと思います。
!!(2) 複数の元データを取り込んだ時にExcelが自動的に生成する集計表
ピボットキャッシュを設けた後のピボットテーブルの設定
pivot_cache.CreatePivotTable({……})
の記述は、これまでのサンプルと同じです。大きく違ってくるのは、その後です。
pt07.rbでは、CreatePivotTableが実行された直後、Excelが自動的に集計表を生成してくれます。
今回は、これまでのサンプルのように ptf.Orientation とか ptf.Function などのフィールドに関する設定を何も行っていません。pt.ShowPagesを実行しただけで、あとはワークブックを保存しています。
にもかかわらず、そうして作られた test07.xls を改めて開くと、集計表ができています。元データの３つのシートに似たものと、それらを総計したものの４つのワークシートがあります。
これまでのサンプルでは、Excelが自動的に集計表を生成することはありませんでした。スクリプトを書く人がピボットフィールドなどを手がかりにしてあれこれ設定してやると、そこで初めて集計表が生成されます。しかし、XlConsolidationを指定した場合は、自動的に集計表が生成されます。
スクリプトを書く人は、この自動生成された集計表の構成、つまり、各種フィールドやそれに属するピボットアイテムがどのようにセットされているのかを把握する必要があります。それを自分の目的に合う形に変更してやることで、新たな集計表を作成します。
ピボットテーブルを構成するフィールドには、ページフィールド(PageField)、行フィールド(RowField)、列フィールド(ColumnField)、データフィールド(DataField)があります。
ptがピボットテーブルオブジェクトであるとき、それぞれ pt.PageFields, pt.RowFields, pt.ColumnFields, pt.DataFields によって該当のフィールドを得ることができます。
各々が複数ある可能性があるので、実際に参照する時は
ptf = pt.ColumnFields(1)
のようにしてフィールドオブジェクトを取得します。
pt07_2.rbは、source07.xlsを取り込んだ時に自動生成される集計表について、その各フィールドの情報を出力します。フィールドの名前およびそれに属するピボットアイテムの名前を列記します。
行フィールドには365日分の日付が収納されており、情報量が多いので
ruby pt07_2.rb > output.txt ↓
のように実行して、結果をファイルに保存した方が確認しやすいと思います。
参考まで、４種類のうちの１つであるページフィールドに関する情報を出力する箇所を下に掲げてみます。
−−−− ここから (pt07_2.rb 抜粋)
output = ["* PageField"]
i = 0
pt.PageFields.each {|ptf|
output << sprintf("\t"+"field#%d %s", i+=1, ptf.Name)
j = 0
ptf.PivotItems.each {|ptfi|
output << sprintf("\t\t"+"item#%d %s", j+=1, ptfi.Name)
}
}
puts output.join("\n")
−−−− ここまで
上の記述パターンを４種類のフィールドについて行っています。PageをRowに変更すれば行フィールドについての出力になります。
同じパターンのスクリプト記述をだらだら書かないよう工夫したのが
[[pt07_3.rb|http://cup.sakura.ne.jp/exlap/pivot.htm#pt07_3]]
です。ここでの主題と関係ないので説明は省略しますが、evalを利用して繰り返しの記述を避けています。よかったら参考にして下さい。
さて、本題です。
source07.xlsを取り込んだ時に生成される集計表には、ページフィールドなどの４種類のフィールドが一つづつ設けられます。
各フィールドの名前は PageField:ページ1(半角カタカナ)、RowField:行、ColumnField:列、DataField:合計 / 値、となっています。
各フィールドのピボットアイテムについては、pt07_2.rbの出力結果が参考になります。一応 掲げておきます。
−−−−−−−−
* PageField
field#1 ページ1
item#1 大阪
item#2 東京
item#3 名古屋
* RowField
field#1 行
item#1 2010/1/1
item#2 2010/1/2
（中略）
item#365 2010/12/31
* ColumnField
field#1 列
item#1 売上げ
* DataField
field#1 合計 / 値
−−−−−−−−
「元データ」の３つのワークシート(つまり支店)がページフィールドに置かれ、各シートの最も左側の項目「日付」が行フィールド、その右隣の「売上げ」が列フィールドに置かれた形です。
データフィールドは分かりにくいですが、売上げの合計を集計するための設定がなされています。
ピボットアイテムに注目すると、ページフィールドのアイテムには３つの支店の名前、行フィールドのアイテムには365日分の日付、列フィールドには「売上げ」という名前がセットされ、データフィールドにはアイテムなしという状況です。
Excelが自動的に生成する集計表は、いつもこれと同じパターンといっていいと思います。慣れてくれば pt07_2.rb のようなスクリプトを実行して、各フィールドの情報を確認しなくても予想できます。
ということで、ここまで確認して、この集計表を一つのクロス集計表に変換する作業に移ります。
!!(3) 自動生成された集計表の変換
変換といっても、ページフィールドによって別々のワークシートになっているものを一つにまとめるのが要点です。「6. ページフィールド」で行った処理(支店ごとに別ワークシートにする)の逆を行うだけです。
作成するクロス集計表は、横方向に「東京、名古屋、大阪、総計」が並び、縦方向に第１四半期〜第４四半期と総計が並ぶ形です。売上げの合計を算出します。
まず、ページフィールドには３つの支店が置かれていますが、これを列フィールドに移します。
次に、365日分のアイテムがある行フィールドを四半期単位にグループ化します。
変換の基本は、この２つです。あとは、クロス集計表として分かりやすくなるように、従来の列フィールド(売上げ)を非表示にしたり、見出しを変更したりする程度です。
変換しているうちに整理がつかなくなると困るので、とりあえず、自動生成された直後の４種類のフィールドを４つの変数に代入します。その上で、それぞれの変数の Orientationプロパティなどを調整します。
pt07_4.rbにその一連の手続きが書かれています。以下、該当箇所を抜粋します。
−−−− ここから (pt07_4.rb 抜粋)
# とりあえず各フィールドを変数に代入
ptfp = pt.PageFields(1)
ptfr = pt.RowFields(1)
ptfc = pt.ColumnFields(1)
ptfd = pt.DataFields(1)
ptfp.Orientation = XlColumnField # ページフィールドを列フィールドに変更
ary = [false]*5 + [true, false] # 四半期グループ化用の配列
ptfr.LabelRange.Group({'Periods'=>ary}) # 行フィールドを四半期グループ化
ptfc.Orientation = XlHidden # 自動設定された列フィールドを非表示に
ptfp.Caption = "支店"
ptfr.Caption = "期間"
ptfd.Caption = "合計/売上げ"
%w(東京 名古屋 大阪).each_with_index {|name, i|
ptfp.PivotItems(name).Position = i+1
}
−−−− ここまで
従来の列フィールド(売上げ)を非表示にして大丈夫なのか？と思われるかもしれませんが、これを非表示にしないと、作成される集計表の「東京」の真上のセルに「売上げ」という表示が残ってしまい、めざわりです。
「売上げ」の数値は、集計(計算)の手がかりとしてデータフィールドに既に集約されているので、非表示にしてかまいません。
Orientation, Caption の再設定およびグループ化については、改めて説明を加える必要はないと思います。
なお、売上げの「合計」でなく「平均」を集計したい場合は、データフィールドを代入した ptfdに関して
ptfd.Function = XlAverage
とすれば、「平均」の集計になります。
Excelが集計表を自動生成する場合、数値データは「合計」、それ以外は「個数」でデータフィールドを設定するようです。
''［補足］ 自動生成集計表の素材となるピボットフィールドとの関係''
以前のサンプル pt02.rb では、「性別」に関するピボットフィールドを素材にして、列フィールドとデータフィールドの２つを設定しました。具体的スクリプトの形で示すと次のとおりです。
ptf1 = pt.PivotFields("性別")
ptf1.Orientation = XlColumnField
ptf2 = pt.PivotFields("性別")
ptf2.Orientation = XlDataField
ptf2.Function = XlCount
要するに、先の「(3)」で取り上げたページフィールド・行フィールド・列フィールド・データフィールドの４種類のフィールドは、素材となるピボットフィールドから創出したものです。つまり、素材ピボットフィールドは、それらとは「別物」です。
それなら、今回のケースにおいて素材ピボットフィールドにはどんなものがあるのか気になります。この素材ピボットフィールドを確認するためのスクリプトが
[[pt07_5.rb|http://cup.sakura.ne.jp/exlap/pivot.htm#pt07_5]]
です。スクリプトの中身は簡単なものなので説明を省略し、出力結果を示します。
−−−− ここから
素材となるピボットフィールドの一覧
field#1 行
item#1 2010/1/1
item#2 2010/1/2
（中略）
item#365 2010/12/31
field#2 列
item#1 売上げ
field#3 値
item#1 10
item#2 11
（中略）
item#90 99
field#4 ページ1
item#1 大阪
item#2 東京
item#3 名古屋
−−−− ここまで
この結果をみると、４つの素材ピボットフィールドがあり、その名前は「行、列、値、ページ1(半角カタカナ)」です。
「値」を別にすれば、先の４種類のフィールド(ページフィールドや行フィールドなど)と同じ名前です。それぞれに属するピボットアイテムも基本的に同じです。
「値」の素材ピボットフィールドからデータフィールドが創出されたことが推測されます。
今回の例では、元データに「日付」と「売上げ」の２項目しかありませんでした。最左端の項目である「日付」が「行」のピボットフィールドになり、その右隣の「売上げ」は「列」になっています。
もし「売上げ」の他に「仕入れ」とか「その他の経費」など複数の項目があったとすれば、それらは「列」に収納されます。各項目が「列」のピボットアイテムとして記録されます。
項目が複数になると、「列」と「値」との関係を的確に把握するのは厄介ですが、ページフィールドや行フィールドなどの４種類のフィールドと、素材ピボットフィールドの両方を確認すれば、最終的な集計表にどうつなげていったらいいか推測できると思います。
といっても、無理にピボットテーブルの統合機能を使うより、複数の元データを一つに集約してから集計表を作る方が簡単になるケースも多いので、やりやすい方法を採るのがいいと思います。
!!(4) sqlによって複数の元データを統合しつつ集計表を作成する
ちょっと話は逸れますが、sqlによって複数の元データを統合しつつ集計表を作成する方法を記しておきます。
SourceTypeにXlConsolidationをセットして複数の元データを統合するのでなく、XlExternalをセットして外部データを取り込みつつ統合も実現する方法です(前掲のpt04.rbと基本的には同じ)。sql命令で２つのテーブルを結合します。
元データとして次の２つのワークシートからなる source07_2.xls を材料にします。
*性別のシート: 「氏名」と「性別」の２項からなる表。10人分が記録されている。
*喫煙のシート: 「氏名」と「喫煙」(あり｜なし)の２項からなる表。
どちらのシートも10人分が記録されており、「氏名」は共通です。
この２つのシートから、性別と喫煙のクロス集計表を作成します。できあがりのイメージは次のとおり。
||性別／喫煙||性別
||喫煙||男||女||総計
||あり||3||1||4
||なし||2||4||6
||総計||5||5||10
このように各々の元データの項目が異なる場合は、XlConsolidationで処理しようとすると厄介です。それに対し、sql命令で２つのテーブル(Excelのワークシート)を結合するのは容易なので、XlExternalの外部データ取込みを応用すると簡単に済みます。
このケースの要点は、２つのワークシートを結合して氏名、性別、喫煙の３項目からなる元データを生成するところにあります。具体的には次のsql命令で実現できます。
select 性別のシート.氏名, 性別のシート.性別, 喫煙のシート.喫煙
into 新しいシート
from 性別のシート, 喫煙のシート
where 性別のシート.氏名 = 喫煙のシート.氏名;
上のsql命令を実行すると、「新しいシート」に氏名、性別、喫煙の３項目からなる10人分のデータが書き出されます。
実際にスクリプトを書く時は、Excelの特性から、「性別のシート」を「[性別のシート$]」と記述します。
それから、実は「新しいシート」を書き出す必要はありません。つまり「into 新しいシート」はなくてもかまいません。「selectコマンドの実行結果をExcelが内部的に保持してくれるはず」と期待して大丈夫です。氏名、性別、喫煙の３項目からなる統合表は、Excelが内的に記録・保持します。
この集計表作成のスクリプト pt07_6.rb を下に掲げておきます。
−−−− ここから (pt07_6.rb)
#! ruby -Ks
# 複数の元データを統合して集計(sql命令文の活用)
require "exlap"
source_name = "source07_2.xls"
cnn = "ODBC;DSN=Excel Files;DBQ=#{Exl::getAbsolutePath(source_name)}"
tbl1 = "[性別のシート$]"
tbl2 = "[喫煙のシート$]"
sql = <<EOS
select #{tbl1}.氏名, #{tbl1}.性別, #{tbl2}.喫煙
from #{tbl1}, #{tbl2}
where #{tbl1}.氏名 = #{tbl2}.氏名;
EOS
filename = "test07_6.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
ptname = "性別と喫煙" # ピボットテーブルの名前
pt = pivot_cache.CreatePivotTable({
'TableDestination'=>ss.Range("A1"), # ピボットテーブルの始点
'TableName'=>ptname})
pt.SmallGrid = false
ptf1 = pt.PivotFields("性別")
ptf1.Orientation = XlColumnField
ptf2 = pt.PivotFields("喫煙")
ptf2.Orientation = XlRowField
ptf3 = pt.PivotFields("性別")
ptf3.Orientation = XlDataField
ptf3.Function = XlCount
ptf1.PivotItems("男").Position = 1
ptf1.PivotItems("女").Position = 2
ptf3.Caption = "性別/喫煙"
wb.save
}
−−−− ここまで
上のサンプルでは、「性別」のピボットフィールドからデータフィールドを創出してますが、当然ながら「喫煙」を素材しても同じ結果が得られます。
sqlで２つのテーブルを結合する方法には、他にも「inner join …… on」などがあります。
なお、pt07_6.rbではodbc接続方式を採用していますが、ado接続方式のものを
[[pt07_7.rb|http://cup.sakura.ne.jp/exlap/pivot.htm#pt07_7]]
として同梱しておきます。
以上、少々脱線気味の話題でしたが、複数の元データから集計表を作成する方法として、応用の可能性が高いと思ったので記してみました。参考になるようでしたら幸いです。
− ピボットテーブルの利用・第３版 おわり −
Copyright (C) T. Yoshiizumi, 2011 All rights reserved.