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

～ 複数回答の度数と構成比の集計 ～

2020年9月6日

［はじめに］

　今回は複数回答の集計を取り上げます。

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

　たとえば、アルコールの種類(日本酒, ビール, ワイン, ウイスキー)を掲げて
どれを好むかに「○」を付けてもらうようなケースです。

　最も好きなもの一つだけを選択してもらうなら単一回答ですが、
二つ以上に「○」を付けることができるのが複数回答です。

　複数回答の集計をExcelのピボットテーブルだけで行うことはできませんが、
ピボットテーブルを利用する形で工夫します。

［目次へ］

1.　素材データ

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

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

　100人のアンケート回答が記録されており、
「ID, 年齢層, 主な情報源(新聞, 雑誌, テレビ, ラジオ)の項目があります。

　主な情報源が複数回答です。

　「新聞, 雑誌, テレビ, ラジオ」は各々別の列になっており、
選択された場合は数値1が記載され、
選択されなかったときは空欄になっています。

　ソースデータの最初の方を掲げると次のとおり。

ID 年齢層 新聞 雑誌 テレビ ラジオ
1 30代 1 □ □ □
2 40代 1 1 □ □
3 40代 □ 1 □ □
4 20代 □ □ 1 1

　空欄のところは数値0にしておいてもかまいません。

　なお、データは乱数により生成したものです。

［目次へ］

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

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

　それに対して、構成比は 0％～100％のパーセンテージの値。

　まずは度数の集計を行います。

(1)　集計表のできあがりイメージ

　できあがりの表は下のようになります。

年齢層 新聞 雑誌 テレビ ラジオ 全体
20代 13 18 14 14 32
30代 24 17 20 16 34
40代 15 22 18 17 34
全体 52 57 52 47 100

　一番右側の「全体」(縦に並ぶ数字)は
横方向に足し算したものの合計ではなく、
該当の回答者の総数を示します。

　一方、一番下の「全体」の行(横に並ぶ数字)は、
縦方向に足し算したものの合計になっています。

(2)　スクリプト

　前掲の集計表を作成するためのスクリプト
multi01.wsfの中核となる部分を掲げると次のとおり。

[ワークブックを開く] "data.xlsx"
Set sourceSheet = [ワークブック].Worksheets("SourceSheet")
Set newSheet = [空のワークシート]
items = "新聞 雑誌 テレビ ラジオ"  ' 複数回答の選択肢
[複数回答の度数を集計] sourceSheet, newSheet, items, "年齢層", Null
[ワークブックを別名で保存] "multi01.xlsx"
[エクセルを終了]

　[複数回答の度数を集計] が集計表作成のサブルーチンです。

　その引数については次項で説明しますが、
複数回答を集計するためのサブルーチンは下の四つです。

　どれも使い方は同じです(引数の指定方法が同じ)。

(3)　「複数回答の度数を集計」の引数

　[複数回答の度数を集計] の引数は五つです。

[複数回答の度数を集計] sourceSheet, newSheet, "新聞 雑誌 テレビ ラジオ", _
    "年齢層", Null
  1. ソースデータが書かれたワークシートオブジェクト
  2. 集計表を書き出すためのワークシートオブジェクト
  3. 複数回答の選択肢の列名を列記した文字列または配列
  4. 単一回答の列名
  5. 単一回答の内訳(20代, 30代, 40代)の順番を示す文字列または配列
    　Nullだと指定なし(おまかせ)になる。

　引数の与え方についてはいろいろ説明を加える必要がありますが、
以降でおいおい述べていきます。

［目次へ］

3.　度数表と構成比表を同一シートに作成(第2引数関連)

　まず 度数表を作成し、その下の方に構成比表を設けます。

　ポイントは、第2引数として
集計表を書き出すためのワークシートオブジェクトを与えるだけでなく
集計表の開始位置を示すセルオブジェクトを与えることもできる点です。

Set cell = newSheet.Range("A10")
[複数回答の構成比を集計] sourceSheet, cell, "新聞 雑誌 テレビ ラジオ", _
    "年齢層", Null

　上のようにすると、構成比表が10行目以降に作成されます。

　以下、度数表と構成比表を作成するスクリプト
multi02.wsf の主要部分を掲げます。

[ワークブックを開く] "data.xlsx"
Set sourceSheet = [ワークブック].Worksheets("SourceSheet")
Set newSheet = [空のワークシート]
items = "新聞 雑誌 テレビ ラジオ"  ' 複数回答の選択肢
[複数回答の度数を集計] sourceSheet, newSheet, items, "年齢層", Null
Set cell = [新先頭のセルon](newSheet).Offset(2)
[複数回答の構成比を集計] sourceSheet, cell, items, "年齢層", Null
[ワークブックを別名で保存] "multi02.xlsx"
[エクセルを終了]

　上に出てくる

Set cell = [新先頭のセルon](newSheet).Offset(2)

というのは、度数表がおわった次の行【[新先頭のセルon](newSheet)】の
更に2行下【Offset(2)】に位置するセルをセットするものです。

　つまり、度数表の下に空白行が2行置かれて、
それ以降に構成比表が書き出されることになります。

［目次へ］

4.　複数回答にだけ着目した集計(第4引数関連)

　これまで、複数回答(主な情報源)×単一回答(年齢層)の
クロス集計を取り上げました。

　でも、シンプルに複数回答にだけ着目した集計がほしいこともあります。

　次のような表です。

新聞 雑誌 テレビ ラジオ 全体
度数 52 57 52 47 100
構成比 52.0% 57.0% 52.0% 47.0% 100.0%

　このような表をつくるには第4引数を "年齢層" ではなく Null にします。

　以下にスクリプト multi03.wsf の主要部分を掲げます。

[ワークブックを開く] "data.xlsx"
Set sourceSheet = [ワークブック].Worksheets("SourceSheet")
Set newSheet = [空のワークシート]
items = "新聞 雑誌 テレビ ラジオ"  ' 複数回答の選択肢
[複数回答の度数と構成比を集計] sourceSheet, newSheet, items, Null, Null
[ワークブックを別名で保存] "multi03.xlsx"
[エクセルを終了]

　ここではサブルーチンとして [複数回答の度数と構成比を集計] を使っています。

　このサブルーチンは、ほんとなら度数と構成比を横並びに配置しますが、
単一回答の指定がないときは、無駄に横に広げるのもなんなので
度数と構成比を縦に並べます。

［目次へ］

5.　引数に関するその他の事項

　引数について付け加えます。

(1)　単一回答の内訳の順序を変更(第5引数関連)

　単一回答(年齢層)の内訳「20代, 30代, 40代」の順序は
第5引数で指定できます。

　これまでは指定なしの Null にしてきました。

　その場合は Excelにおまかせで
結果的に「20代, 30代, 40代」の順序になっていました。

　これを逆順にするには第5引数を "40代 30代 20代" にします。

(2)　複数回答の選択肢の順序は変更可能(第3引数関連)

　第3引数は複数回答の選択肢の名前を列記したものです。

　これまでは "新聞 雑誌 テレビ ラジオ" としてきましたが、
順序は好きな並べ方でかまいません。

"ラジオ テレビ 雑誌 新聞"

　上のように順序を逆転させれば集計表の順序もそれに従います。

［目次へ］

6.　選択個数と無回答などの扱い

　複数回答において、どの選択肢にも「○」がついていないケースを
無回答とするかどうか悩ましいところがあります。

　でも、選択の傾向や特徴をみる材料にならないのは確かです。

　「みる材料にならない」という意味では
全部に「○」がついているケースも同じです。

　ということで、選択個数が 0 または 4 の人を覗いて集計してみます。

　素材データでは、選択されていれば数値1、されていなければ空欄になっています。

　なので =SUM(C2:F2) のような関数記述で選択個数を得ることができます。

　「選択個数」の列を追加しておいて、それを手掛かりに AutoFilter を使って
データの絞り込みを行います。

　絞り込んだ結果を別のワークシートにコピーすれば
それが集計の素材データになります。

　以上の手順を具体的なスクリプトにすると下のとおり。

[ワークブックを開く] "data.xlsx"
Set ws = [ワークブック].Worksheets(1)
Set rng = ws.UsedRange
Set cell = rng.Find("新聞")
headerRow = cell.Row  ' 見出しの行番号
col1 = cell.Column  ' 複数回答の最左列の番号
col2 = rng.Find("ラジオ").Column  ' 複数回答の最右列の番号
Set cell = [末尾のセルon](ws)
newCol = cell.Column + 1  ' 選択個数用の新しい列の番号
row1 = headerRow + 1  ' データの最初の行の番号
row2 = cell.Row  ' データの最後の行の番号
fml = "=SUM(RC[" & col1-newCol & "]:RC[" & col2-newCol & "])"
ws.Cells(headerRow, newCol).Value = "選択個数"
For i = row1 To row2
    ws.Cells(i, newCol).Formula = fml
Next
    ' ↓ 選択個数による絞り込み
Set startCell = rng.Range("A1")  ' 素材表領域の先頭セル
startCell.AutoFilter newCol, ">0", 1, "<4"  ' 絞り込み
Set sourceSheet = [空のワークシート]
startCell.CurrentRegion.Copy sourceSheet.Range("A1")
startCell.AutoFilter  ' オートフィルタを解除
    ' ↓ 複数回答の集計
Set newSheet = [空のワークシート]
items = "新聞 雑誌 テレビ ラジオ"  ' 複数回答の選択肢
[複数回答の度数と構成比を集計] sourceSheet, newSheet, items, "年齢層", Null
[ワークブックを別名で保存] "multi04.xlsx"
[エクセルを終了]

　選択個数別の状況を知りたい場合は、
AutoFilterをかける前に、選択個数に着目した単一回答の集計を行えばOKです。

　ちなみに、選択個数 0 の人は4人、全部に「○」をつけた人は5人です。

［目次へ］

7.　どのようにピボットテーブルを利用しているか

　今回の複数回答の集計は、途中でピボットテーブルを利用してはいるものの、
最終的な集計結果はピボットテーブルに紐づけられていません。

　第7回で取り上げた単一回答の集計の結果は、
ピボットテーブルを利用した結果そのままなので
ワークブックやワークシートにピボットテーブルの関連情報が記録されています。

　でも、今回はピボットテーブル関連情報は残っていません。

　ではありますが、どのようにピボットテーブルを利用しているかについて
簡単に記しておきます。

　複数回答の一つの選択肢に着目すると、それを選択したか
選択しなかったかの二択ととらえることができます。

　そこで、「新聞」の列にある空欄を「×」に変換し、
数値1を「新聞」という文字に変換します。

　そして、「新聞」と「年齢層」のクロス集計(単一回答の集計)を行うと
下のような表が得られます。

年齢層 × 新聞 総計
20代 19 13 32
30代 10 24 34
40代 19 15 34
総計 48 52 100

　同じようにして「雑誌, テレビ, ラジオ」についてもそれぞれ表を作成し、
横っちょにくっつけていきます。

　その上で「×」の列および重複する「年齢層」と「総計」の列を削除すると
複数回答の集計表ができあがります。

　以上の手順で複数回答の集計表を生成するようにしました。

　ピボットテーブルが記録されているワークシートは、使い道がなさそうなのて
シートまるごと削除しています。

～　以上　～

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