2020年8月22日
今回は Excelのピボットテーブルによる集計のうち
度数と構成比の集計を取り上げます。
JuseOffice07.zipにサンプルが入っています。
ピボットテーブルは統計的な集計を行うための仕組みです。
JuseOffice.vbs の中にピボットテーブルを利用するための
基本的な関数やサブルーチンが入っています。
その使い方に触れながらピボットテーブルの仕組みも解説したいとおもいます。
ここでは素材データとして research.xlsx を用います。
SourceSheet という名前のワークシートに素材データが書かれています。
350人のアンケート回答が記録されており、
「ID, 地域, 意見, 年齢」の4項目があります。
集計する前に、「意見」の数字は「賛成」などの文字に置き換えます。
空欄→無回答, 1→賛成, 2→保留, 3→反対の置き換え処理です。
その処理は [整数を文字に変換] というサブルーチンで行いますが、
それについては最後の方で解説します。
度数というのは人数や件数のことです。
それに対して、構成比は 0％～100％のパーセンテージの値。
まずは度数の集計を行います。
pivot01.wsfの中核となる部分を掲げると次のとおり。
[ワークブックを開く] "research.xlsx"
Set sourceSheet = [ワークブック].Worksheets("SourceSheet")
[整数を文字に変換] sourceSheet, "意見", "無回答 賛成 保留 反対"
Set pivotSheet = [空のワークシート]
Set pt = [ピボットテーブルの初期設定](sourceSheet, pivotSheet)
[度数を集計] pt, "意見", "地域"ピボットテーブルにかかわるのは最後の2行です。
Set pt = [ピボットテーブルの初期設定](sourceSheet, pivotSheet) 上は、ソースデータが書かれているワークシートと、
ピボットテーブルを書き出すためのワークシートの二つを引数としてわたして
ピボットテーブルオブジェクトを得ています。
変数ptにセットされたピボットテーブルオブジェクトは、
いろいろな集計表を作成する基板になります。
その次の行
[度数を集計] pt, "意見", "地域"これは、度数のクロス集計表を作成するための記述です。
第2引数, 第3引数はクロス集計の際に注目する「列(Column)」の名前です。
第2引数で与えた「意見」の内訳(賛成, 保留, 反対, 無回答)が
横方向に展開されます。
第3引数で与えた「地域」の内訳(海辺, 川沿い, 山際)は縦方向に展開されます。
数学のx,y座標では x座標が横方向、y座標が縦方向に拡がりますが、
それに倣って引数の順番を「横方向, 縦方向」の順番にしました。
結果として作成されるテーブルは下のようになります。
| 地域 | 賛成 | 反対 | 保留 | 無回答 | 総計 |
|---|---|---|---|---|---|
| 海辺 | 37 | 42 | 30 | 0 | 109 |
| 山際 | 43 | 33 | 36 | 5 | 117 |
| 川沿い | 42 | 38 | 44 | 0 | 124 |
| 総計 | 122 | 113 | 110 | 5 | 350 |
ちなみに、[度数を集計] を [構成比を集計] に変更すれば
度数ではなく構成比の集計結果が得られます。
また、[度数と構成比を集計] にすれば、両方が表示されるテーブルになります。
「意見」の内訳の順番が「賛成, 反対, 保留, 無回答」になっていますが
これを「賛成, 保留, 反対, 無回答」に変更します。
「地域」の方も低いところから高い順番に
「海辺, 川沿い, 山際」に変更することにしましょう。
その場合は下のように記述します。
【前略】
[度数を集計] pt, "意見", "地域"
[ピボットアイテムの順序を変更] pt.ColumnFields(1), "賛成 保留 反対 無回答"
[ピボットアイテムの順序を変更] pt.RowFields(1), "海辺 川沿い 山際"ここでピボットテーブルの構成要素について少し述べます。
ピボットテーブルはピボットフィールドから構成されています。
上記二つのフィールドは、それぞれ「賛成, 反対」とか
「海辺, 山際」などの内訳を持っています。
その内訳のことをピボットアイテムといいます。
今回、そのピボットアイテムの順番を入れ替えるようにしたわけです。
[ピボットアイテムの順序を変更] pt.ColumnFields(1), "賛成 保留 反対 無回答" 上の [ピボットアイテムの順序を変更] の引数は次の二つ。
列フィールドに pt.ColumnFields(1) のように `(1) がくっついています。
複雑な表になると列フィールドが複数あるケースがあるため
その1番目ということで (1) が付いています。
第2引数は、半角スペースで区切られた名前が列記されていますが、
これは内部で半角スペースで区切って配列に変換されます。
これを文字列でなく最初から配列で指定してもかまいません。
これまで「意見」と「地域」のクロス集計を取り上げてきました。
でも、もっとシンプルに一つだけのフィールドについて集計することもあります。
[度数を集計] pt, "意見", Null上のようにすると「意見」だけの集計表になります。
下のような表です。
| 賛成 | 保留 | 反対 | 無回答 | 総計 | |
|---|---|---|---|---|---|
| 度数 | 122 | 110 | 113 | 5 | 350 |
これを縦方向に並べる表にするには次のように書きます。
[度数を集計] pt, Null, "意見"pivot02.wsfが単一フィールドの集計表作成のサンプルになっています。
ちなみに、Null は「何も示さないもの」といった意味で、
無効な値の一つとして使われます。
[度数を集計] を [構成比を集計] にすれば構成比の表を得られますが、
ここでは度数の表に加えて構成比の表も書き出します。
そのため、ピボットキャッシュというオブジェクトを扱います。
度数も構成比も同じ元本を素材にして作成しますが、
その元本に相当するのがピボットキャッシュです。
Set pt = [ピボットテーブルの初期設定](sourceSheet, pivotSheet) 上のようにして得られたピボットテーブルオブジェクト(変数pt)には
その元本(ピボットキャッシュ)の情報も含まれており
pt.PivotCache として参照できます。
構成比の表を作成するときは、このピボットキャッシュを利用します。
ピボットテーブルオブジェクトを得るための記述
Set pt = [ピボットテーブルの初期設定](sourceSheet, pivotSheet) 上は第1引数として sourceSheet(素材データのワークシート)を与えていますが、
ピボットキャッシュを与えてもかまいません。
また、第2引数として pivotSheet を与えていますが、
ワークシート内のどの位置からピボットテーブルを書き出すか
その位置を示すセルオブジェクトを与えてもかまいません。
たとえば次のような記述ができます。
Set startCell = pivotSheet.Range("A10")
Set pt2 = [ピボットテーブルの初期設定](pt.PivotCache, startCell)こうすると、ワークシートの10行目以降にピボットテーブルが書き出されます。
同じ元本から複数のピボットテーブルを生成するときは
このようにピボットキャッシュを用います。
これまで書いてきた事柄の仕上げに当たる pivot03.wsf を掲げておきます。
[ワークブックを開く] "research.xlsx"
Set sourceSheet = [ワークブック].Worksheets("SourceSheet")
[整数を文字に変換] sourceSheet, "意見", "無回答 賛成 保留 反対"
Set pivotSheet = [空のワークシート]
Set pt = [ピボットテーブルの初期設定](sourceSheet, pivotSheet)
[度数を集計] pt, "意見", "地域"
[ピボットアイテムの順序を変更] pt.ColumnFields(1), "賛成 保留 反対 無回答"
[ピボットアイテムの順序を変更] pt.RowFields(1), "海辺 川沿い 山際"
Set startCell = [新先頭のセルon](pivotSheet).Offset(2)
Set pt = [ピボットテーブルの初期設定](pt.PivotCache, startCell)
[構成比を集計] pt, "意見", "地域"
[ピボットアイテムの順序を変更] pt.ColumnFields(1), "賛成 保留 反対 無回答"
[ピボットアイテムの順序を変更] pt.RowFields(1), "海辺 川沿い 山際"
[ワークブックを別名で保存] "pivot03.xlsx"
[エクセルを終了] 集計のための元本(Excelファイル)を作成する場合、
「賛成, 反対」などの文字を書き入れるのは手間です。
そこで、賛成の代わりに数字1を、反対の代わりに数字2を書いたりします。
当然、集計する前にそれら数字を文字に入れ替える必要があります。
その処理をするのが [整数を文字に変換] です。
このサブルーチンの引数は次の三つです。
"意見""無回答 賛成 保留 反対"ワークシート ws の中から colName を検索し、その列を置換処理の対象とします。
該当の列に空欄があれば、それを数値0に置き換えます。
そうしておいて、該当の列の各セルの整数値(0, 1, 2, ……)を文字に置き換えます。
第3引数 factorNames は、
半角スペースで区切られた文字の集まりで与えられた場合
それを配列に変換します。
すると、factorNames(0)=無回答, factorNames(1)=賛成, …… となるので
整数値と文字の対応ができて置き換えが容易に行えます。
第3引数 factorNames を配列の形で与えてもOKです。
～ 以上 ～
Copyright (C) T. Yoshiizumi, 2020 All rights reserved.