T. Yoshiizumi - exlap_pt02 Diff

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

最終更新日： 2011/03/20

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

第１版の続編として、「5.　グループ化」について書きます。

サンプルスクリプトの実行環境は、第１版の時と同じです。

[[pt_test02.zip|http://cup.sakura.ne.jp/pt_test02.zip]]
には、関連のサンプルスクリプトなどが含まれています。

----

{{toc_here}}

----

!5.　グループ化

　グループ化は、複数の項目を１つのグループにまとめた上で集計結果を出すための手法です。

　例えば、慎重区分 「150以上・160未満」　「160以上・170未満」などの区分ごとに該当の人数を集計するといった時にグループ化の手法を用います(数値によるグループ化)。

　都道府県別の人口データがある時に、東北とか関東などの地方ブロック単位で人口を集計するケースもそうです(文字列によるグループ化)。

　あるいは、ピボットテーブルとの関係でグループ化の話題が最も取り上げられやすいのは、日付によるグループ化でしょうか。日々の会計記録を基に、四半期単位とか月単位の集計を出す場合です。

　以下、それぞれのケースについて簡単な事例で説明を記してみます。

!!(1) 数値によるグループ化(Group, LabelRange, DataRange)

　ここでも、最初に取り上げた元データ　氏名・性別・身長・体重の４項目 10人分のデータを材料にします。

　身長に着目して「150以上・160未満」　「160以上・170未満」などの区分ごとに該当の人数を集計します。

　グループ化を一種の「加工処理」と捕らえるなら、まずは未加工の集計表の設計を考えます。そして、その後で加工処理(グループ化)を施します。

　この「未加工集計表の設計→加工処理」の手順は、グループ化を分かりやすくしてくれると同時に、エラーが起きにくいスクリプトを書く上でも役に立つと思います。

　では、第１ステップとして未加工集計表を考えます。

　「性別」だけに着目して男女別の人数を算出する例は、既に取り上げました(pt02.rb)。

　この pt02.rb の「性別」を「身長」に変更すれば、身長ごとの人数が得られます。元データには同じ身長の人がいないので、１つの身長の値に該当する人数は、いずれも１人です。

　余談ですが、仮に身長160.0の人が５人、170.0の人が５人だったとすれば、わざわざグループ化するまでもなく、２分類された結果が出てくるはずです。「性別」に着目した時に「男」と「女」の２分類の結果が得られたのと同じです。しかし、実際にはそのようになっていないので、グループ化する必要が出てきます。

　未加工の表を設計する部分を下に示します。ピボットキャッシュやピボットテーブルの基本的設定のところまでは省略し、集計表の設計の箇所のみです。

    −−−−　ここから
        # 「身長」を項目分類の手がかりに。
    ptf1 = pt.PivotFields("身長")
    ptf1.Orientation = XlColumnField  # 列方向(横方向)に広げる
        # 「身長」を集計(計算)の手がかりにもする
    ptf2 = pt.PivotFields("身長")
    ptf2.Orientation = XlDataField
    ptf2.Function = XlCount
    −−−−　ここまで

　上記スクリプトを実行してみると、身長の値が昇順で左から右に並びます。具体的には

　151　151.6　158.9　161.8　164　167.9　173.9　174.6　178.4　179.6　総計

という１行ができます。そして、その下の行には該当の人数(どれも１)が並び、総計のところだけ10になります。

　上のようにずらずら並んだ身長の値は、「性別」の時の「女」　「男」に該当します。つまり項目分類の名前です。

　この項目分類の名前を数値としてグループ化するわけですが、スクリプトとしては次の１行を追加するだけで行うことができます。

    ptf1.LabelRange.Group({'Start'=>150.0, 'By'=>10.0})

あるいは、次の１行でも同じ結果が得られます。

    ptf1.DataRange.Cells(1).Group({'Start'=>150.0, 'By'=>10.0})

　このグループ化のための１行は、ptf2に関する記述が終わった後に置く必要があります。ptf1に関する記述だからといって、ptf2の記述の前に置くとエラーになります。

　要するに、未加工集計表の設計が一通り済んでから加工処理(グループ化)を行う必要があるわけです。

　グループ化するためのサンプルを pt05.rb
[[pt05.rb|http://cup.sakura.ne.jp/exlap/pivot.htm#pt05]]
として同梱しているので参考にして下さい。結果は次のような表になって出てきます。

||データの個数／身長||身長
||　||150-160||160-170||170-180||総計
||集計||3||3||4||10

　上に出てくる「150-160」というのは、「150以上・160未満」の意味です。160ちょうどの人は、このグループに入りません。

　以下、グループ化のための１行について簡単に記します。

{{fonts(''○　Groupメソッド, '+3')}}''

　xxx.Group() は、xxxというRangeについてグループ化を行うメソッドです。

　Rangeがピボットテーブルフィールドのデータ範囲内の単一セルを表す時に使うことができます。複数セルでなく単一セルであることに注意。

　また、Rangeがピボットテーブルフィールドのラベル(見出し)の単一セルを表す時にも使うことができます。

　Groupには次の引数をHash形式で与えることができます。

*Start　グループ化する最初の値{{br}}
　省略すると、該当Rangeの最小値が採用される。pt05.rbの例では151.0
*End　グループ化する最後の値{{br}}
　省略すると、該当Rangeの最大値が採用される。pt05.rbの例では179.6
*By　各グループのサイズ{{br}}
　フィールドが数値の場合に指定するもので、各グループのサイズ(身長の例では10センチ単位など)を指定。
*Periods　日付のグループ化に使用(配列){{br}}
　７つの要素からなる配列で、各要素の値は true または false をとる。{{br}}
　７つの要素の意味は次のとおり。{{br}}
　　　1　秒{{br}}
　　　2　分{{br}}
　　　3　時{{br}}
　　　4　日{{br}}
　　　5　月{{br}}
　　　6　四半期{{br}}
　　　7　年{{br}}
　例えば、６番目の要素だけ true にして、他は false にすると、四半期単位のグループ化を行うことになる。{{br}}
　フィールドが日付フィールドでなければ、この引数は無視される。

{{fonts(''○　LabelRangeプロパティ, '+3')}}''

　フィールドのラベル(見出し)が含まれているRangeオブジェクトを取得します。

　pt05.rbにおける  ptf1.LabelRange は、ss2.Range("B1") と等価です。「身長」と書かれたセル１つを表します。

{{fonts(''○　DataRangeプロパティ, '+3')}}''

　フィールドのデータが含まれているRangeオブジェクトを取得します。

　pt05.rbにおける  ptf1.DataRange は、ss2.Range("B2:K2") と等価です。

　このRangeの値(Value)は、[[151.0, ［［151.0, 151.6, …… 179.6]] 179.6］］ です。

　この DataRange にGroupメソッドを適用する場合は、単一セルのRangeでないと適用できないことから Cells(1) を挿入します。

　以上、LabelRange と DataRange について記しましたが、スクリプトを書く立場からすると、これらとGroupメソッドの関係がどうも腑に落ちません。

　なぜ複数のセルを含むRangeに対してGroupを適用できず、単一セルの場合に適用するようになっているのか、不思議です。普通に考えるなら、複数だからこそグループ化するのだと思います。

　また、なぜ LabelRange に対してGroupメソッドを適用できるのか、これも納得できない感じが残ります。

　ただし、スクリプトを書く立場でなく、ＧＵＩ操作する立場からすると、わざわざ複数のセルを範囲指定してからでないとグループ化できないというよりは、１つのセルに焦点を当てるだけでグループ化できる方が便利です。

　また、複数のデータのうちの１つに焦点を当ててグループ化する以外に、ラベル(見出し)に焦点を当ててグループ化できるというのも、直感的に分かりやすいといえます。そういう観点で考えるなら、LabelRangeに対してGroupを適用できる仕様というのも分からなくはありません。

{{fonts(''［補足１］　グループ化した後の PivotItems, '+3')}}PivotItems''

　ptf1(「身長」を項目分類の手がかりにしたもの)の PivotItems は、グループ化する前であれば、各々10人分の身長の値がセットされています。

      p ptf1.PivotItems(1).Name  # => 151.0
      p ptf1.PivotItems(2).Name  # => 151.6

などのようになります。

　一方、グループ化した後にどうなるかは、集計表の結果から推測できますが、ただし、集計表に示されていない範囲外の情報も PivotItems には保持されています。

      p ptf1.PivotItems(1).Name  # => "<150"
      p ptf1.PivotItems(2).Name  # => "150-160"
      …………
      p ptf1.PivotItems(5).Name  # => ">180"

　PivotItemsのPositionプロパティで配置順を変更するような場合は、上の「範囲外」があることに留意する必要があります。もっとも、こういう数値に関係する場合の並べ替えは、Positionプロパティを使うよりも、AutoSortを使う方が便利です(別の機会に取り上げたいと思います)。

{{fonts(''［補足２］　LabelRangeにGroupを適用する時の注意点, '+3')}}''

　１つのピボットフィールドに着目したとき、そのLabelRangeが複数のセルからなるRangeになることがあるのかどうか分かりませんが、マイクロソフト社のサイトを読むと、いつも単一セルであるとは限らないようです。複数のセルを含む場合は、そのままではGroupメソッドを適用できないことになります。

　その辺が心配な時は、DataRange.Cells(1).Group(……) を使う方が無難なのかもしれません。

{{fonts(''［補足３］　範囲外が表示されるケース, '+3')}}''

　サンプルでは、Startを150.0に設定しました。それ未満の人がいないため、集計結果には範囲外が盛り込まれませんでしたが、例えば、Startを155.0、Endを175.0にすると、"<155"が2人、">175"が2人であることが分かる集計表になります。つまり、範囲外が表示されます。

----

!!(2) 文字列によるグループ化(Union, ParentField)

　ここでも同じ元データ「氏名、性別、身長、体重」の10人分のデータを使うことにします。「氏名」に着目したグループ化を試みます。

　氏名は、「ア行の人々」と「サ行の人々」に分けることができます。安部さんや伊藤さんは「ア行の人々」、佐藤さんや篠原さんは「サ行の人々」です。

　元データは、あいうえお順に並んでいますが、ランダムな並びの場合でも使える方法を採ります。

　文字列によるグループ化の要点は、私の見るところでは次の２点です。

{{fonts(1) ''1) グループ化には、ApplicationのUnionメソッドを使う, '+3')}}''

　ここでいうApplicationは、Excelを扱うための大本のオブジェクトで、VBAでよく Application と表記されます。

　rubyスクリプトの記述でいえば

    xl = WIN32OLE.new('Excel.Application')

として得られた xl のことです。

　ptがピボットテーブルオブジェクトである場合は

    xl = pt.Application

としてそれを得ることができます。

　文字列によるグループ化は、うんと単純化して書くと次のようになります。

　Application.Union("安部", "伊藤", ……).Group を実行すると、「グループ1」が設けられ、続いて同じようにUnion＋Groupを実行すれば、「グループ2」が設けられる、という具合です。

{{fonts(2) ''2) グループ化すると、該当のピボットフィールドに親フィールドが設けられる, '+3')}}''

　ptf1が「氏名」に関するピボットフィールドを表している場合、前述のUnion＋Groupでそれにグループ化を施すと、親フィールドが設けられます。ptf1そのものは変化せず、各人の氏名に関する情報が保持されたままです。

　グループ化を２回行った場合、親フィールドには２つのピボットアイテム(いわば下位項目)ができることになります。

　その親フィールドを操作することで、グループ化したもの(より大きなカテゴリー)についてその表示の仕方などを調整できます。

　数値によるグループ化の場合は ptf1 自身が変化しましたが、文字列によるグループ化の場合は、ptf1 は変化せず、グループ化した結果を保持する親フィールドが設けられます。

　以下、「ア行の人々」と「サ行の人々」各々について平均身長を算出する例を取り上げます。

　まず未加工の集計表を設計します。一人づつについて平均身長を示す表が未加工の表です。平均身長といっても、一つの枠に一人しかいないので、要するに身長の値そのものです。

　10人分の氏名が横方向にずらずら並び、その下の行に各人の身長の値が並ぶ形が未加工の表です。

　次に、Union＋Groupを用いてグループ化を施します。

　同梱の pt05_2.rb にその辺の一連の処理が含まれていますので、関連箇所を抜粋します。

    −−−−　ここから (pt05_2.rb 抜粋)
        # まず未加工の集計表を設計
    ptf1 = pt.PivotFields("氏名")
        ptf1.Orientation = XlColumnField
    ptf2 = pt.PivotFields("身長")
        ptf2.Orientation = XlDataField
        ptf2.Function = XlAverage
    
        # 以下がグループ化の処理
    xl = pt.Application
    xl.Union(ptf1.PivotItems("安部").LabelRange,
        ptf1.PivotItems("伊藤").LabelRange,
        ptf1.PivotItems("上村").LabelRange,
        ptf1.PivotItems("榎本").LabelRange,
        ptf1.PivotItems("小田").LabelRange).Group
    xl.Union(ptf1.PivotItems("佐藤").LabelRange,
        ptf1.PivotItems("篠原").LabelRange,
        ptf1.PivotItems("杉山").LabelRange,
        ptf1.PivotItems("千田").LabelRange,
        ptf1.PivotItems("相馬").LabelRange).Group
    ptf1p = ptf1.ParentField  # 親フィールド(大きなカテゴリー)のオブジェクト
    ptf1p.PivotItems(1).Name = "ア行の人々"
    ptf1p.PivotItems(2).Name = "サ行の人々"
    ptf1.Orientation = XlHidden
    wb.save
    −−−−　ここまで

　未加工の表を設計する部分については、説明を省略します。

　その後、グループ化するに当たって、まず Excel.Application のオブジェクトをxlにセットします。xl.Union().Group を実行するためです。

　Unionの引数は、ずらずらと長く書いてありますが、要するに

    ptf1.PivotItems("安部").LabelRange

という記述の氏名を変えて必要な数だけ並べたものです。

　そして、xl.Union(……).Group のように、Unionの結果にGroupを適用します。Groupメソッドに引数を渡す必要はありません。

　２つのグループを設定するので、上の処理を２回行っています。

　これでグループ化の第１段階が終了です。

　続いて、グループ化した大きなカテゴリーを操作するため

    ptf1p = ptf1.ParentField

として、親フィールドのオブジェクトをptf1pにセットします。

　この親フィールドには、グループ化を施した結果として、２つのピボットアイテム「グループ1」と「グループ2」が設けられています(「グループ」は半角カタカナ)。

　この「グループ1」などの名前を変更するには、フィールドのPivotItemsを使って次のようにします。

    ptf1p.PivotItems(1).Name = "ア行の人々"
    ptf1p.PivotItems(2).Name = "サ行の人々"

　以上でグループ化の処理が終了です。

　ただ、このままだと各人の氏名が10人分ずらずら表示されてしまいます。「安部」の真上のセルに「ア行の人々」というグループ名が置かれ、「佐藤」の真上に「サ行の人々」が配置されるだけです。グループ化した意味があまりありません。

　そこで、「氏名」に関するピボットフィールド ptf1 を非表示にします。そのためには

    ptf1.Orientation = XlHidden

という１行を書きます。これで、10人分の氏名がずらずら並ぶ行が表示されなくなり、２つのグループの平均身長が表示されます。次のような表です。

||平均／身長||氏名2
||　||ア行の人々||サ行の人々||総計
||集計||162.82||169.52||166.17

　上の集計表をみると分かりますが、親フィールドの名前は、「氏名2」となっています。「氏名」のピボットフィールドをグループ化して設けたピボットフィールドなので、そういう名前になっているのだと思います。

　したがって、ptf1.ParentFieldを用いなくても、次のようにして親フィールドのオブジェクトを得ることができます。

    ptf1p = pt.PivotFields("氏名2")

　もし「氏名2」の名称を変更したいのであれば、次のようにします。

    ptf1p.Name = "姓名グループ"

----

!!(3) 文字列によるグループ化・その２(Subtotals, SubtotalName)

　前述の pt05_2.rb では、10人分の氏名を非表示にしましたが、場合によっては、それを残しつつ、グループ単位の平均身長も表示したいことがあります。

　しかし、「氏名」のピボットフィールドである ptf1 を非表示に設定しない場合は、残念ながらグループ単位の平均身長が表に盛り込まれません。

　それを表に盛り込むためには、親フィールド ptf1p の Subtotals プロパティに適当な値をセットしてやる必要があります。

　この Subtotals は、12個の要素から構成される配列を値に持つもので、各要素は true か false のどちらか１つです。

　12個も要素がありますが、実際には最初の１つの要素しか使わないようです。その最初の要素を true にすると、グループ単位の集計値が表に盛り込まれ、false だと盛り込まれません。デフォルトは false です。

　rubyでは [true]+[false]*11 と書けば、最初のみtrueで、あとの11個はfalseの配列になります。pt05_3.rb
[[pt05_3.rb|http://cup.sakura.ne.jp/exlap/pivot.htm#pt05_3]]
では、これを Subtotals プロパティにセットしています。

　ちなみに、Subtotalsプロパティの12個の要素の意味などについて、マイクロソフトの解説では次のように書かれています。

""インデックス　意味
""　　　　　1　 自動
""　　　　　2　 合計
""　　　　　3　 データの個数
""　　　　　4　 平均
""　　　　　5　 最大値
""　　　　　6　 最小値
""　　　　　7　 積
""　　　　　8　 数値の個数
""　　　　　9　 標本標準偏差
""　　　　　10　標準偏差
""　　　　　11　標本分散
""　　　　　12　分散
""
""インデックスがTrueの場合、フィールドにその集計が表示されます。インデックス1(自動)がTrueの場合は、他のすべての値にFalseが設定されます。
""OLAPデータソースの場合、インデックスに設定できるのは1(自動)のみです。取得した配列の先頭要素には必ずTrueまたはFalseが含まれ、他のすべての要素にはFalseが含まれます。配列の要素がすべてFalseであるということは、集計がないことを意味します。

　あと、pt05_3.rb では、ptf1p.SubtotalName にも値をセットしています。これは、グループ単位の集計に付ける項目名です。

　値をセットしない時は、「ア行の人々 集計」　「サ行の人々 集計」といった名前になります。グループ名の後に半角スペースが置かれ、続いて「集計」がくる形です。

　以上が、個々人の氏名とグループ名・グループ単位の集計値を総て表示させるための方法です。

　その他、pt05_3.rbでは、最後に、氏名の並び順を調整しています。Excelにお任せだと、「安部　伊藤　榎本　小田　上村」の順になってしまいます。そこで、上村さんを榎本さんよりも前に持ってくるため、PivotItemsを使って調整します。

　不都合な箇所だけを修正するというよりは、ともかく「あいうえお順」になるように、総てのPivotItemsの要素のPositionプロパティに値をセットしています。

　それから、グループ化とは関係ありませんが、rubyに慣れている人であれば pt05_2.rb の書き方に少しいらいらするだろうと思います。Unionメソッドの引数をだらだら並べているところです。

　rubyでは、fooメソッドに引数を渡す時に、foo(a,b) と書く以外に

    param = [a,b]
    foo(*param)

と記述できます。これを使えば、引数の個数が多い時に整理した形を採ることができます。

　pt05_3.rbは、その辺も修正して書いてみました。

----

!!(4) 日付によるグループ化

　ここでは、「日付」と「売上げ」の２つの項目からなる元データを使います。

　左側の「日付」は、上から下に向かって 2010/01/01〜2010/12/31 の365日分の日付が縦に並びます。

　その右側の「売上げ」は、10〜99の数値(乱数)が、やはり縦方向に365個分並びます。

　見出しを含めると、２×366の行列になっているのが元データです。これをExcelの第１ワークシートに記録した後で、第２ワークシートに日付によりグループ化した集計表を作成します。

　四半期単位の売上げ合計、月単位の売上げ合計の集計を考えます。

!!!a.　四半期単位の集計

　日付によるグループ化は、基本的に数値によるグループ化と同じです。Groupメソッドに渡す引数が違うだけです。

　ptf1が「日付」のピボットフィールドである場合、日付のグループ化のエッセンスは、次の２行です。

    ary = [false]*5 + [true, false]
    ptf1.LabelRange.Group({'Periods'=>ary})

　上の ary は、７つの要素からなる配列ですが、６番目のみtrueで、残りはfalseです。これは、四半期単位のグループ化を行う時のもので、月単位にしたい時は５番目のみをtrueにします。

　各要素の意味は、１番目から順に次のとおりです。

　1　秒、2　分、3　時、4　日、5　月、6　四半期、7　年。

　説明が後先になってしまいましたが、未加工の集計表のイメージは、実は、元データと同じです。

　縦方向に365日分の「日付」が並び、その右隣に365日分の「売上げ」が同じく縦方向に並んでいる形です。

　これまでのサンプルでは項目分類を横方向に並べてきましたが、365個のデータを扱うため、Excel2003までだと処理できません(256個が限度)。なので、やむなく縦方向にしました。Excel2007以降であれば横方向に並べる形でも大丈夫です。

　ただし、いったん集計表を生成した後で、

    ptf1.Orientation = XlColumnField

を記述すれば、Excel2003などでも項目分類を横方向に並べる形にできます。

　四半期単位でグループ化した結果(縦方向に広がるもの)を下に示します。

||合計／売上げ
||日付||集計
||第1四半期||5186
||第2四半期||5095
||第3四半期||5504
||第4四半期||4654
||総計||20439

　上の集計表を作成するためのサンプルスクリプトが pt05_4.rb
[[pt05_4.rb|http://cup.sakura.ne.jp/exlap/pivot.htm#pt05_4]]
ですが、ここに掲げて説明を加えるほどのことはないと思います。

　なお、売上げ欄の数値は、乱数なので実行する度に変わります。

{{fonts(''［補足］　日付によるグループ化の時のピボットフィールドの変化, '+3')}}''

　ptf1が「日付」のピボットフィールドであるとき、それをグループ化する前は、365日分の日付が記録・保持されています。つまり、ピボットアイテムが365個あります。

　一方、四半期単位でグループ化した後は、４つの四半期に関する情報と、その前後の２つの範囲外の情報とで、計６つが記録・保持される状態に変化します。

　ptf1.PivotItems(1).Name, ptf1.PivotItems(2).Name, ……　の値は次のとおり。

　"<2010/1/1", "Qtr1", "Qtr2", "Qtr3", "Qtr4", ">2011/1/1"

　数値によるグループ化の時と同じように、ptf1そのものが変化します。

!!!b.　月単位と四半期単位の両方を含む集計

　次に、四半期単位と月単位の両方を盛り込むことを考えます。

　その場合は、Groupメソッドに渡す引数のPeriodsに対応する ary の５番目と６番目の２つをtrueにして、残りをfalseにします。

　ただ、それだけだと四半期単位の合計が表に盛り込まれません。それを入れるためには、文字列によるグループ化の時に用いた Subtotalsプロパティを使います。

　具体的には下のようにします。pt05_5.rb からグループ化に関連する箇所を抜粋します。

    −−−−　ここから (pt05_5.rb 抜粋)
    ary = [false]*4 + [true, true, false]
    ptf1.LabelRange.Group({'Periods'=>ary})
    ptf1q = pt.PivotFields("四半期")  # 「四半期」のピボットフィールド
    ptf1q.Subtotals = [true] + [false]*11  # 四半期合計を表示
    −−−−　ここまで

　今回のように月単位・四半期単位の両方でグループ化すると、「日付」のピボットフィールドptf1は、より小さな「月単位」の方の情報を記録・保持するものに変化します。

　そこで、「四半期」のピボットフィールドを別途取得しなければならないわけですが、それを行うのが「ptf1q = pt.PivotFields("四半期")」です。

　「四半期」のピボットフィールド ptf1q が得られた後は、その Subtotals を設定します。

　これで月単位の合計と四半期単位の合計の両方が盛り込まれた表を作成できます。

　両方を盛り込んだ集計表を下に掲げておきます。

||合計／売上げ
||四半期||日付||集計
||第1四半期||1月||1947
||　||2月||1422
||　||3月||1744
||第1四半期 集計||　||5113
||第2四半期||4月||1709
||　||5月||1763
||　||6月||1865
||第2四半期 集計||　||5337
||第3四半期||7月||1501
||　||8月||1319
||　||9月||1630
||第3四半期 集計||　||4450
||第4四半期||10月||1510
||　||11月||1843
||　||12月||1723
||第4四半期 集計||　||5076
||総計||　||19976

　上の集計表を横方向に広げる形にしたい時は、最後の方(wb.saveの直前)に

    ptf1.Orientation = ptf1q.Orientation = XlColumnField

を記述します。

　この場合、ptf1.Orientation と ptf1q.Orientation の２つとも変更しないと、意図したような結果にならないので注意が必要です。

　どちらか一方だけを変更してもエラーにはなりませんが、所々に空欄ができ、ちょっと妙な集計表になります。でも、そうした表が必要なこともあるかもしれません。Orientationプロパティの設定次第で、いろいろな形の集計表を作れることが実感できます。

----

!!(5) 細かな条件に基づくグループ化(分類用の項目を新設する方法)

　最後に、蛇足かもしれませんが少々。

　Excelのピボットテーブルに用意されているグループ化の機能では、簡単にグループ化できないケースがあります。

　例えば、身長によるグループ化で「155〜165」, 「165〜175」, 「その他」の３分類にすることを考えたとします。「その他」には155未満と175以上の両方が入ります。こうしたケースは、これまで紹介した方法を単純に適用するだけではグループ化できません。

　柔軟にグループ化を行う最も容易な方法は、おそらく、分類用の項目を新たに設けて、その新項目によって集計することだろうと思います。ピボットテーブルに用意されている機能をいろいろ組み合わせて対応するのも一つのやり方ですが、現実には、分類用の項目を新設した方が簡単にいくケースが多いと感じます。

　先の身長の３分類でいえば、元データをチェックして、各人の身長が３分類のどれに属するかを調べ、その分類名を「身長区分」(新設の欄)に書き込みます。その上で集計表を生成すれば目的達成です。ピボットテーブルのグループ化の機能は使わずに済みます。

　元データのところを変更したくないのであれば、別のワークシートやワークブックにそれをコピーした上で新設の欄を追加してもいいと思います。

　参考まで、身長区分「155〜165」, 「165〜175」, 「その他」の３分類にするためのサンプル pt05_6.rb
[[pt05_6.rb|http://cup.sakura.ne.jp/exlap/pivot.htm#pt05_6]]
を同梱しておきます。

　元データをいったん別のワークシートにコピーした上で処理しています。

　データをチェックして新たな欄を設ける処理は、rubyで行っています。Excelに拘りのある人には不満なやり方だろうと思いますが、私にとってはExcelに働いてもらうよりrubyに処理してもらう方がずっと簡単なので。

　ここで pt05_6.rb に関する逐一の説明は掲げませんが、よかったら参考にして下さい。

----

　以上、グループ化の説明はここまでにします。

　他にも、ピボットフィールドがグループ化されている時に、全部で何階層あるのか、そのフィールドが何階層目に属するのかを知るための TotalLevels, GroupLevel など説明できていない事柄がいろいろあります。

　それらについては、他の人が作ったピボットテーブルを受け取った時に、それをどう把握するか、再利用するのにどうするか、といった事項の中で説明できればと思います。

　なかなか基本編を抜け出せませんが、とりあえず。

−　ピボットテーブルの利用・第２版　おわり　−

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