Excel, Wordの自動操作のハードル削減計画・その7

~ Excelのピボットテーブル・度数と構成比の集計 ~

2020年8月22日

[はじめに]

 今回は Excelのピボットテーブルによる集計のうち
度数と構成比の集計を取り上げます。

 JuseOffice07.zipにサンプルが入っています。

 ピボットテーブルは統計的な集計を行うための仕組みです。

 JuseOffice.vbs の中にピボットテーブルを利用するための
基本的な関数やサブルーチンが入っています。

 その使い方に触れながらピボットテーブルの仕組みも解説したいとおもいます。


[目次へ]

1. 素材データ

 ここでは素材データとして research.xlsx を用います。

 SourceSheet という名前のワークシートに素材データが書かれています。

 350人のアンケート回答が記録されており、
「ID, 地域, 意見, 年齢」の4項目があります。

 集計する前に、「意見」の数字は「賛成」などの文字に置き換えます。

 空欄→無回答, 1→賛成, 2→保留, 3→反対の置き換え処理です。

 その処理は [整数を文字に変換] というサブルーチンで行いますが、
それについては最後の方で解説します。


[目次へ]

2. とりあえず度数の集計

 度数というのは人数や件数のことです。

 それに対して、構成比は 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

 ちなみに、[度数を集計][構成比を集計] に変更すれば
度数ではなく構成比の集計結果が得られます。

 また、[度数と構成比を集計] にすれば、両方が表示されるテーブルになります。


[目次へ]

3. 順番の入れ替え&ピボットテーブルの構成要素

 「意見」の内訳の順番が「賛成, 反対, 保留, 無回答」になっていますが
これを「賛成, 保留, 反対, 無回答」に変更します。

 「地域」の方も低いところから高い順番に
「海辺, 川沿い, 山際」に変更することにしましょう。

 その場合は下のように記述します。

【前略】
[度数を集計] pt, "意見", "地域"
[ピボットアイテムの順序を変更] pt.ColumnFields(1), "賛成 保留 反対 無回答"
[ピボットアイテムの順序を変更] pt.RowFields(1), "海辺 川沿い 山際"

 ここでピボットテーブルの構成要素について少し述べます。

 ピボットテーブルはピボットフィールドから構成されています。

 上記二つのフィールドは、それぞれ「賛成, 反対」とか
「海辺, 山際」などの内訳を持っています。

 その内訳のことをピボットアイテムといいます。

 今回、そのピボットアイテムの順番を入れ替えるようにしたわけです。

[ピボットアイテムの順序を変更] pt.ColumnFields(1), "賛成 保留 反対 無回答"

 上の [ピボットアイテムの順序を変更] の引数は次の二つ。

 列フィールドに pt.ColumnFields(1) のように `(1) がくっついています。

 複雑な表になると列フィールドが複数あるケースがあるため
その1番目ということで (1) が付いています。

 第2引数は、半角スペースで区切られた名前が列記されていますが、
これは内部で半角スペースで区切って配列に変換されます。

 これを文字列でなく最初から配列で指定してもかまいません。


[目次へ]

4. 単一フィールドの集計

 これまで「意見」と「地域」のクロス集計を取り上げてきました。

 でも、もっとシンプルに一つだけのフィールドについて集計することもあります。

[度数を集計] pt, "意見", Null

 上のようにすると「意見」だけの集計表になります。

 下のような表です。

賛成 保留 反対 無回答 総計
度数 122 110 113 5 350

 これを縦方向に並べる表にするには次のように書きます。

[度数を集計] pt, Null, "意見"

 pivot02.wsfが単一フィールドの集計表作成のサンプルになっています。

 ちなみに、Null は「何も示さないもの」といった意味で、
無効な値の一つとして使われます。


[目次へ]

5. 構成比の表を追加

 [度数を集計][構成比を集計] にすれば構成比の表を得られますが、
ここでは度数の表に加えて構成比の表も書き出します。

 そのため、ピボットキャッシュというオブジェクトを扱います。

(1) ピボットキャッシュの意味

 度数も構成比も同じ元本を素材にして作成しますが、
その元本に相当するのがピボットキャッシュです。

Set pt = [ピボットテーブルの初期設定](sourceSheet, pivotSheet)

 上のようにして得られたピボットテーブルオブジェクト(変数pt)には
その元本(ピボットキャッシュ)の情報も含まれており
pt.PivotCache として参照できます。

 構成比の表を作成するときは、このピボットキャッシュを利用します。

(2) 「ピボットテーブルの初期設定」の引数

 ピボットテーブルオブジェクトを得るための記述

Set pt = [ピボットテーブルの初期設定](sourceSheet, pivotSheet)

 上は第1引数として sourceSheet(素材データのワークシート)を与えていますが、
ピボットキャッシュを与えてもかまいません。

 また、第2引数として pivotSheet を与えていますが、
ワークシート内のどの位置からピボットテーブルを書き出すか
その位置を示すセルオブジェクトを与えてもかまいません。

 たとえば次のような記述ができます。

Set startCell = pivotSheet.Range("A10")
Set pt2 = [ピボットテーブルの初期設定](pt.PivotCache, startCell)

 こうすると、ワークシートの10行目以降にピボットテーブルが書き出されます。

 同じ元本から複数のピボットテーブルを生成するときは
このようにピボットキャッシュを用います。

(3) スクリプト

 これまで書いてきた事柄の仕上げに当たる 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"
[エクセルを終了]

[目次へ]

6. 「整数を文字に変換」の処理内容

 集計のための元本(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.