# pivot01.rb.txt: ruby script (encoding: Windows-31J)
  # ExcelのPivotTable操作  data01.xls を素材にして z_pivot01.xls を出力
  # 「ID, 性別, 職業, 身長, 体重」の100人分のデータを素材にして
  # クロス集計：男女別・職業別の人数、および構成比(％)
  # 数値の集計：男女別に身長と体重の平均値を算出
  # 男女別・身長区分別の人数(数値のグループ化)、身長を逆順にした表も作る
  # 「__END__」以降に、関連のプロパティにセットできる値を列記
require "exlap"

data_file = "data01.xls"  # 素材データのワークブック
filename = "z_pivot01.xls"  # 新たに作るワークブック
if test(?e, filename)  # z_pivot01.xlsが既に存在するなら削除
  File.unlink(filename)
end

xl = Exlap.new
xl.opens_once(data_file, filename) do |wb1, wb2|  # ２つを同時に開く
      # 素材データがある領域を変数 src_rng に記録
  ss = wb1.ss(1)  # 第１ワークシート(素材データがある)を選択
  src_rng = "[#{wb1.Name}]#{ss.Name}!" +
        ss.UsedRange.Address(true, true, XlR1C1)

  ## クロス集計：男女別・職業別の人数
  ss = wb2.fes  # 空のワークシートを選択
  ss.Name = "男女別・職業別の集計"
  ss.Activate  # ピボットテーブルを設けるシートに焦点を当てる
  pvc = wb2.PivotCaches.Add('SourceType'=>XlDatabase, 'SourceData'=>src_rng)
  ptname = "男女別・職業別の人数"  # ピボットテーブルの名前
  pvc.CreatePivotTable(
    'TableDestination'=>ss.Range("A1"),  # ピボットテーブルの始点
    'TableName'=>ptname)
  pt = ss.PivotTables(ptname)  # ピボットテーブルオブジェクトをptに
  pt.SmallGrid = false  # レイアウト枠の調整
      # ↓ 項目分類の注目欄として性別を指定。横方向(列)に広げる形に設定
  ptf1 = pt.PivotFields("性別")
  ptf1.Orientation = XlColumnField  # 「列」方向に並べるフィールドとする
  ptf1.PivotItems("男性").Position = 1  # 男性を１番目に
  ptf1.PivotItems("女性").Position = 2  # 女性を２番目に
      # ↓ 項目分類の注目欄として職業を指定。縦方向(行)に広げる形に設定
  ptf2 = pt.PivotFields("職業")
  ptf2.Orientation = XlRowField  # 「行」方向に並べるフィールドとする
      # ↓ どの項目に注目して人数をカウントするかを設定
  ptf3 = pt.PivotFields("職業")
  ptf3.Orientation = XlDataField  # 計算・集計の材料にするとの指定
  ptf3.Function = XlCount  # 個数(人数)のカウント
  ptf3.Caption = "男女別・職業別の人数"

  ## 同じシートに構成比（％）の表を書き出す
      # ↓ 第２ピボットテーブル作成の準備
  rng = pt.TableRange2  # 第１ピボットテーブルの領域をrngに代入
  y,x = ss.range_last(rng)  # 第１ピボットテーブルの最終番地を得る
  start_cell = ss.cell(y+3,1)  # 第２ピボットテーブルの始点を２行空け後に
      # ↓ 第２ピボットテーブルの作成
  ptname = "男女別・職業別の構成比"  # ピボットテーブルの名前
  pvc.CreatePivotTable(
    'TableDestination'=>start_cell,  # ピボットテーブルの始点
    'TableName'=>ptname)
  pt = ss.PivotTables(ptname)  # ピボットテーブルオブジェクトをptに
  pt.SmallGrid = false  # レイアウト枠の調整
      # ↓ 項目分類の注目欄として性別を指定。横方向(列)に広げる形に設定
  ptf1 = pt.PivotFields("性別")
  ptf1.Orientation = XlColumnField  # 「列」方向に並べるフィールドとする
  ptf1.PivotItems("男性").Position = 1  # 男性を１番目に
  ptf1.PivotItems("女性").Position = 2  # 女性を２番目に
      # ↓ 項目分類の注目欄として職業を指定。縦方向(行)に広げる形に設定
  ptf2 = pt.PivotFields("職業")
  ptf2.Orientation = XlRowField  # 「行」方向に並べるフィールドとする
      # ↓ どの項目に注目して人数をカウントするかを設定
  ptf3 = pt.PivotFields("職業")
  ptf3.Orientation = XlDataField  # 計算・集計の材料にするとの指定
  ptf3.Function = XlCount  # 個数(人数)のカウント
  ptf3.Calculation = XlPercentOfRow  # 「行」における構成比
  ptf3.NumberFormat = "##0.0%"  # 小数点以下１位まで表示
  ptf3.Caption = "男女別・職業別の構成比(％)"

  ## 数値の集計：男女別に身長と体重の平均値を算出
  ss = wb2.fes  # 空のワークシートを選択
  ss.Name = "身長と体重の値"
  ss.Activate
  pvc = wb2.PivotCaches.Add('SourceType'=>XlDatabase, 'SourceData'=>src_rng)
  ptname = "男女別の身長と体重の平均値"  # ピボットテーブルの名前
  pt = pvc.CreatePivotTable(
    'TableDestination'=>ss.Range("A1"),  # ピボットテーブルの始点
    'TableName'=>ptname)
  pt.SmallGrid = false
      # ↓ 項目分類の注目欄として性別を指定。横方向(列)に広げる形に設定
  ptf1 = pt.PivotFields("性別")
  ptf1.Orientation = XlColumnField  # 「列」方向に並べるフィールドとする
  ptf1.PivotItems("男性").Position = 1  # 男性を１番目に
  ptf1.PivotItems("女性").Position = 2  # 女性を２番目に
      # ↓ 計算用の注目欄として「身長」を指定。平均を採ることも指定。
  ptf2 = pt.PivotFields("身長")
  ptf2.Orientation = XlDataField  # 計算の材料にするとの指定
  ptf2.Function = XlAverage  # 計算方法を「平均」に設定
  ptf2.NumberFormat = "0.0"  # 小数点以下１位まで表示
      # ↓ 「体重」に関する設定(「身長」と同じ)
  ptf3 = pt.PivotFields("体重")
  ptf3.Orientation = XlDataField
  ptf3.Function = XlAverage
  ptf3.NumberFormat = "0.0"

  ## 男女別・身長区分別の人数(数値のグループ化)
  ss = wb2.fes  # 空のシートを選択
  ss.Name = "男女別・身長区分別の集計"
  ss.Activate
  pvc = wb2.PivotCaches.Add('SourceType'=>XlDatabase, 'SourceData'=>src_rng)
  ptname = "男女別・身長区分別の人数"  # ピボットテーブルの名前
  pt = pvc.CreatePivotTable(
    'TableDestination'=>ss.Range("A1"),  # ピボットテーブルの始点
    'TableName'=>ptname)
  pt.SmallGrid = false  # レイアウト枠の調整
      # ↓ 項目分類の注目欄として性別を指定。横方向(列)に広げる形に設定
  ptf1 = pt.PivotFields("性別")
  ptf1.Orientation = XlColumnField  # 「列」方向に並べるフィールドとする
  ptf1.PivotItems("男性").Position = 1  # 男性を１番目に
  ptf1.PivotItems("女性").Position = 2  # 女性を２番目に
      # ↓ 分類の注目欄として「身長」を指定
  ptf2 = pt.PivotFields("身長")
  ptf2.Orientation = XlRowField  # 縦方向(行)に列べるとの指定
      # ↓ どの項目に注目して人数をカウントするかを設定
  ptf3 = pt.PivotFields("性別")
  ptf3.Orientation = XlDataField  # 計算の材料にするとの指定
  ptf3.Function = XlCount  # 個数(人数)のカウント
  ptf3.Caption = "男女別・身長区分別の人数"
      # ↓ 「身長」を手がかりにしてグループ化
  ptf2.DataRange.Cells(1).Group('Start'=>155.0, 'End'=>185.0, 'By'=>10.0)

  ## 同じシートに、身長区分の逆順の表を作る
      # ↓ 逆順ピボットテーブル作成の準備
  rng = pt.TableRange2  # 先のピボットテーブルの領域をrngに代入
  y,x = ss.range_last(rng)  # 先のピボットテーブルの最終番地を得る
  start_cell = ss.cell(y+3,1)  # これから作るピボットテーブルの始点
      # ↓ 新ピボットテーブルの作成
  ptname = "男女別・身長区分別(逆順)の人数"  # ピボットテーブルの名前
  pt = pvc.CreatePivotTable(
    'TableDestination'=>start_cell,  # ピボットテーブルの始点
    'TableName'=>ptname)
  pt.SmallGrid = false  # レイアウト枠の調整
      # ↓ 項目分類の注目欄として性別を指定。横方向(列)に広げる形に設定
  ptf1 = pt.PivotFields("性別")
  ptf1.Orientation = XlColumnField  # 「列」方向に並べるフィールドとする
  ptf1.PivotItems("男性").Position = 1  # 男性を１番目に
  ptf1.PivotItems("女性").Position = 2  # 女性を２番目に
      # ↓ 分類の注目欄として「身長」を指定
  ptf2 = pt.PivotFields("身長")
  ptf2.Orientation = XlRowField  # 縦方向(行)に列べるとの指定
      # ↓ どの項目に注目して人数をカウントするかを設定
  ptf3 = pt.PivotFields("性別")
  ptf3.Orientation = XlDataField  # 計算の材料にするとの指定
  ptf3.Function = XlCount  # 個数(人数)のカウント
  ptf3.Caption = "男女別・身長区分別(逆順)の人数"
      # ↓ 「身長」を手がかりにしてグループ化
  ptf2.DataRange.Cells(1).Group('Start'=>155.0, 'End'=>185.0, 'By'=>10.0)
      # ↓ 「身長」の区分を逆順にする
  count = ptf2.PivotItems.Count
  names = [""]
  for i in 1..count  # ピボットアイテムの名前を記録
    names << ptf2.PivotItems(i).Name
  end
  for i in 1..count  # ピボットアイテムの順番を入れ換える
    ptf2.PivotItems(names[i]).Position = count - i + 1
  end

  ss = wb2.ss(1)  # 第１ワークシートを選択
  ss.Activate
  wb2.save  # ワークブックの保存
end
xl.quit

__END__
＊ピボットテーブルの関連プロパティにセットできる値
○ Orientation
  XlPageField　ページ
  XlColumnField　列
  XlRowField　行
  XlDataField　データ

○ Function
  XlAverage　平均
  XlCount　データの個数
  XlCountNums　数値の個数
  XlMax　最大値
  XlMin　最小値
  XlProduct　積
  XlStDev　標本標準偏差
  XlStDevP　標準偏差
  XlSum　合計
  XlVar　標本分散
  XlVarP　分散

○ Calculation
・XlPercentOfRow　「行」における構成比
　横方向に足し算を行った時に得られる総計に対して各欄が占める構成比を得る。
・XlPercentOfColumn　「列」における構成比
　縦方向に足し算を行った時に得られる総計に対して各欄が占める構成比を得る。
・XlPercentOf　他の欄との比較比率
　例えば、「対前年比」などを得る時に用いる。

○ その他のプロパティ
　変数ptにピボットテーブルのオブジェクトが代入されているとき、
      pt.SmallGrid = false
　上のように指定できる「SmallGrid」のようなプロパティがいくつかある。
・SmallGrid　ピボットテーブルの青いレイアウト枠(ステンシルアウトライン)の調整
　これをtrue(デフォルト値)にすると、小さなレイアウト枠になる。
・RowGrand　「行」の総計欄設定の有無
　これをtrue(デフォルト値)にすると、「行」の総計欄が設けられる。
・ColumnGrand　「列」の総計欄設定の有無
　これをtrue(デフォルト値)にすると、「列」の総計欄が設けられる。
・GrandTotalName　総計欄の名称
　デフォルト値の "総計" を変更したい時に用いる。
・SaveData　データ保存の有無
　true(デフォルト値)を設定すると、ピボットテーブル集計表のデータがワークブックと一緒に保存される。falseなら表の定義のみが保存される。
・RefreshDate　ピボットテーブルが最後に更新された日付
　ピボットテーブルが最後に更新された日付を "2011/02/27 10:03:49" などの文字列で返す。値をセットすることはできない。