Excelでクロス集計するやり方を調べている人の多くは、アンケートや申込フォームの回答が手元に集まった状態で止まっています。全体の集計は出せたものの、それでは何も判断できないことに気づいた段階です。この記事では、データの形を整えることから始めて、ピボットテーブルでの手順、割合の出し方、関数で作る方法、そして読み解くときに間違えやすい点までを、公式の記載で確かめながら順序として整理します。
クロス集計は、全体の数字では見えないものを出す作業
クロス集計とは、2つ以上の質問を掛け合わせて数える作業です。全体で何%という数字を、年代別、地域別、職種別に分けて出し直します。
なぜこれが必要になるのかは、実際の例で見ると分かります。
悩んだAさんは、「ターゲット層である20～30代」の満足度をどのように調べるとよいのかを先輩に相談したところ、クロス集計のやり方を教えてもらいました。 クロス集計を行ったところ、「商品X」がターゲットとしている20代の満足度（満足+やや満足）は62％、30代は72％であることが分かりました（図2赤枠）。 一方、40～50代の満足度は90％以上と、20～30代の満足度よりも高いことも分かりました（図2緑枠）。 出典: asmarq.co.jp
この例で起きていることが、クロス集計の価値をそのまま示しています。全体の満足度だけを見ていれば、数字は悪くないという結論になります。年代で分けた瞬間に、狙っていた層の満足度が他の層より低いという事実が出てきます。全体の数字は、狙っていない層の高い評価に押し上げられていたということです。
受付や事務局の仕事でも、同じ構図が繰り返されます。申し込みの件数が全体で増えていても、どの経路から増えたのかが分からなければ、次にどこに力を入れるかが決まりません。問い合わせの内容を種類ごとに数えても、どの経路から来た人がどの種類の質問をするのかが分からなければ、案内のページを直す場所が決まりません。クロス集計は、次の行動を決めるために数え直す作業です。
クロス集計の前に、データの形を整える
手順に入る前に、データの形を整えます。ここを飛ばすと、後の作業が全部やり直しになります。
公式の案内では、ピボットテーブルを作るためのデータの条件として、1つのヘッダー行がある列にデータをまとめること、空の行や列がないようにすること、列のデータ型が同じであること、そしてExcelの表として書式設定することが推奨されています。
実務でつまずくのはこの4つのうち2つです。1つは、見出しの行が2段になっている場合です。質問文を1段目に書き、選択肢を2段目に書いた表は、そのままではピボットテーブルに渡せません。1段にまとめます。もう1つは、列の途中に空白が混ざっている場合です。回答が無い設問をそのまま空白にしておくと、見た目には問題が無くても、集計の対象がそこで切れることがあります。
もう1つ、公式の条件には書かれていませんが必ず問題になるのが、同じ意味の値が違う表記で入っている場合です。「20代」と「20代」で片方に空白が入っている、「男性」と「男」が混ざっている、地域名の表記が「東京」と「東京都」で揺れている。この状態でクロス集計を作ると、同じ属性が2行に分かれて並びます。集計の前に、列ごとに値の種類を確かめる作業を1度だけ入れます。列を選んで重複を除く機能を使えば、その列に何種類の値が入っているかがすぐ分かります。
自由記述の欄は、そのままではクロス集計の軸に使えません。1つずつ読んで分類の列を追加する作業が必要になります。この作業を後回しにすると、集計の途中で手が止まります。分類の列を追加するときは、分類の名前を先に決めて別のシートに書き出し、その一覧から選んで入力します。読みながら思いついた名前を打ち込んでいくと、似た分類が5つも6つも生まれます。
日付の列も整えておきます。文字列として入力された日付は、そのままでは月ごとにまとめられません。日付として認識されているかどうかは、そのセルを右揃えで表示したときに右に寄るかどうかで見分けられます。左に寄っている列は文字列です。集計の前に日付に直しておけば、月や四半期でまとめる操作がそのまま使えます。
ピボットテーブルでクロス集計する手順
データの形が整ったら、手順は短いです。公式の案内に沿って書き出します。
1つ目に、ピボットテーブルを作るセルを選択します。表の中のどこか1つのセルを選んでおけば、範囲は自動で認識されます。
2つ目に、挿入のタブからピボットテーブルを選びます。
3つ目に、新しいワークシートか既存のワークシートのどちらに作るかを選びます。最初は新しいワークシートにしておくと、元のデータを触らずに済みます。
4つ目に、OKを選びます。ここまでで空のピボットテーブルができます。
5つ目に、フィールドを配置します。公式の案内では、既定の自動配置として、数値以外のフィールドは行の領域、日付と時刻のフィールドは列の領域、数値のフィールドは値の領域に入ると説明されています。手で配置する場合は、フィールドを目的の領域にドラッグします。
クロス集計として使うときの配置は決まっています。行の領域に1つ目の軸、列の領域に2つ目の軸、値の領域に数える対象を配置します。年代別の満足度を出したいなら、行に年代、列に満足度の選択肢、値に回答者のIDのような一意の列を配置し、集計方法を個数にします。
ここで注意したい点が1つあります。値の領域に文字列の列を配置すると、既定では個数として集計されます。数値の列を配置すると合計になります。満足度を5段階の数値で入力している場合、値に配置すると点数の合計が出てしまいます。数えたいのは人数なので、集計方法を変えるか、列の領域に配置して人数を数える形にします。
数の表から、割合の表に変える
クロス集計の表は、人数のままでは比べられません。年代ごとに回答者の人数が違うためです。20代が50人で40代が200人なら、同じ10人でも意味が違います。
割合に変える手順は、値フィールドの設定から行います。公式の案内では、値の集計方法を変える手順として、フィールド名の矢印を選び、値フィールドの設定を選び、集計方法のセクションで計算方法を変えると説明されています。
割合にするときは、集計方法ではなく計算の種類を変えます。公式の案内に並んでいる選択肢は多く、総計に対する比率、列集計に対する比率、行集計に対する比率、基準値に対する比率、親行集計に対する比率、親列集計に対する比率、親集計に対する比率、基準値との差分、基準値に対する比率の差、累計、比率の累計、昇順での順位、降順での順位、インデックスが用意されています。
クロス集計で使うのは、このうち3つです。行集計に対する比率、列集計に対する比率、総計に対する比率。この3つの違いを理解しておかないと、出てきた%を読み間違えます。
行に年代、列に満足度を配置した表で考えます。行集計に対する比率を選ぶと、各年代の中で満足度がどう分かれているかが出ます。20代のうち62%が満足、といった読み方になります。列集計に対する比率を選ぶと、満足と答えた人のうち何%が20代かが出ます。総計に対する比率を選ぶと、全回答者に対する割合が出ます。
知りたいことがどれなのかを先に決めてから選びます。「20代の満足度」を知りたいなら行集計に対する比率です。多くの場合、軸として使いたい属性を行に配置し、行集計に対する比率を選ぶのが読みやすい形になります。
集計方法を変えて、別の見方を出す
人数を数えるだけでなく、金額や回数を集計する場合もあります。公式の案内では、値の集計方法として使える関数が並んでいます。合計、個数、平均、最大、最小、積、数値の個数、母集団の推定標準偏差、母集団の標準偏差、母集団の推定分散、母集団の分散が用意されています。数値データの既定は合計です。
実務でよく使うのは4つです。人数を数えるときは個数、金額を足すときは合計、1件あたりの傾向を見るときは平均、極端な値を確かめるときは最大と最小です。
平均を使うときのコツを1つ。平均だけを見ると、極端な値に引っ張られていることに気づけません。同じ表に最大と最小を並べておくと、平均が代表していない状態が見えます。値の領域には複数のフィールドを配置できるので、同じ列を2回配置して、一方を平均、もう一方を最大にする組み方ができます。
もう1つのコツは、個数と合計を同時に出すことです。金額の合計だけを見ると、件数が多いから合計が大きいのか、1件が大きいから合計が大きいのかが分かりません。個数を隣に並べれば、その場で判断できます。
関数だけでクロス集計を作る場合
ピボットテーブルを使わずに、関数で表を作る方法もあります。集計の結果を他の計算に使う場合や、毎月同じ形の表を作る場合には、関数のほうが扱いやすくなります。
使うのは、条件を複数指定して数える関数です。表の左に1つ目の軸の値を並べ、上に2つ目の軸の値を並べ、交差するセルに、その2つの条件を両方満たす行の数を数える式を書きます。
関数で作る利点は3つあります。1つは、元のデータが更新されたときに自動で数字が変わることです。ピボットテーブルは更新の操作が必要です。2つ目は、表の形を自由に決められることです。3つ目は、他のセルからその数字を参照して計算を続けられることです。
欠点も3つあります。1つは、軸の値を自分で書き並べる必要があることです。回答に無い選択肢を書き忘れると、その行が表から抜けます。2つ目は、軸を入れ替えるときに式を書き直すことになる点です。3つ目は、式の参照の範囲を間違えると、間違いに気づかないまま数字が出てくる点です。
使い分けの目安としては、探索の段階ではピボットテーブル、決まった形を毎月作る段階では関数が向きます。最初から関数で組むと、軸を変えて試すことができず、見つかる事実が少なくなります。
関数で作る場合の注意点を1つ。軸の値を書き並べるとき、元のデータに実際に存在する値と一字でも違うと、そのセルは0になります。0は「該当が無い」という意味にも「書き間違えた」という意味にもなるため、間違いに気づけません。対策は、表の右端と下端に合計の行と列を追加して、その合計が元のデータの件数と一致するかを確かめることです。一致しなければ、どこかの軸の値が拾われていません。この確認を入れるかどうかで、関数で作る表の信頼度が変わります。
同じ確認は、ピボットテーブルで作った表にも当てはまります。総計の数字が、元のデータの行数と合っているかを毎回見ます。合っていなければ、空白の行が含まれているか、集計の範囲が足りていません。
3つ以上の軸で見るときの組み方
2つの軸で差が見つかると、次に「その差はどの層で起きているのか」を知りたくなります。軸を3つ以上にする方法は2つあります。
1つは、行または列の領域に2つのフィールドを重ねる方法です。行に年代と地域を並べると、年代の中で地域ごとに分かれた階層の表になります。この形は、内側の軸の比較がしやすい利点があります。ただし行の数が掛け算で増えるため、年代が5区分で地域が8区分なら40行になり、回答者の数が少ないセルが大量に生まれます。
もう1つは、フィルターの領域を使う方法です。地域をフィルターに配置すると、地域を1つ選ぶごとに、年代と満足度の表が切り替わります。この形は、同じ形の表を条件ごとに見比べられる利点があります。報告の資料を作るときは、こちらのほうが読みやすい表になります。
どちらを選ぶかは、見たい問いの形で決まります。「地域によって年代ごとの傾向が違うか」を知りたいならフィルターで切り替えて比べ、「どの組み合わせが最も良いか」を知りたいなら階層にして一覧で見ます。
軸を増やすときに必ず起きるのが、セルごとの人数が足りなくなる問題です。回答が300件あっても、3つの軸で割れば1つのセルは数人になります。軸を増やす前に、1つのセルに何人残るかを掛け算で見積もってください。300件を5区分と8区分で割れば、平均して1セル7.5人です。この規模で%を語ることはできません。軸を増やすのではなく、区分をまとめる方向で考えます。
表を人に見せる形に整える
自分で読むための表と、人に見せる表は別のものです。報告に使うなら、次の4つを整えます。
1つ目は、区分の並び順です。既定では文字の順に並ぶため、「10代」「20代」「30代」「40代」「50代」が意図した順にならないことがあります。並び順を手で指定するか、先頭に番号を付けた区分名にしておきます。
2つ目は、%の桁数です。小数第1位まで出すと、精度がありそうに見えて誤解を招きます。回答者が数百人の規模なら、整数の%で十分です。
3つ目は、人数の併記です。%だけの表は、読み手が母数を判断できません。行の端に回答者の人数を並べた列を追加します。
4つ目は、何を数えた表なのかを表の上に1行で書くことです。「回答者数」「回答数」「件数」は別のものです。複数選択の設問では、回答数が回答者数を超えます。この区別を書いていない表は、読み手が必ず取り違えます。
見せる相手が判断をする人なら、表の下に読み取れたことを2行だけ書き添えます。表を渡して解釈を任せると、読み手ごとに違う結論になり、次の打ち合わせがやり直しになります。
毎月同じ集計をするときの段取り
1度だけの集計と、毎月続ける集計は、作り方を変えたほうが楽になります。
続ける集計では、元のデータを追記する形にします。月ごとにファイルを分けると、年間の推移を見るときに結合の作業が発生します。1つの表に月の列を追加して追記していけば、ピボットテーブルの列に月を配置するだけで推移が出ます。
追記する形にするなら、元のデータをExcelの表として書式設定しておきます。行を追加したときに集計の範囲が自動で広がるため、範囲の指定を毎月直す作業が無くなります。
もう1つ決めておくのが、区分の定義です。年代の区切り、地域のまとめ方、問い合わせの種類の分け方。これを月ごとに変えると、推移が比べられなくなります。定義を別のシートに書き残して、変えるときは変えた月を記録します。定義を変えた月をまたいだ比較は、そのままでは成立しません。
最後に、集計の手順そのものを書き残します。どのシートのどの列を軸にして、どの計算の種類を選んだか。担当が替わったときに、同じ表を作り直せるかどうかがここで決まります。手順が残っていないと、次の人は最初から軸を探し直すことになります。
読み解くときに間違えやすい3つの点
表が出来上がった後の読み方にも、注意する点があります。
1つ目は、回答者の数が少ないセルの%を、他のセルと同じように扱ってしまうことです。5人のうち3人が満足と答えれば60%ですが、1人違えば40%にも80%にもなります。%だけを並べた表は、この不安定さを隠します。%の表を作るときは、元の人数を必ず併記します。目安として、回答者が30人を下回るセルの%は、傾向の参考程度に留めるのが安全です。
2つ目は、差があることと、その差に意味があることを混同することです。20代の62%と30代の72%という差は、回答者の数が十分にあれば意味のある差ですが、それぞれ10人ずつの回答なら偶然の範囲に入ります。差を報告するときは、それぞれの人数を同じ資料に示します。
3つ目は、軸の取り方で結論が変わることを忘れることです。年代で分けたときに差が出なかった項目が、地域で分けると差が出ることがあります。1つの軸で差が出なかったことは、差が無いことの証明にはなりません。逆に、軸をいくつも試して差が出た1つだけを報告するのも、読み手を誤らせます。試した軸は全部書き残しておきます。
無料でできる手段と、ツールの選び方
Excelを持っていない場合や、複数の人で同じ表を見たい場合の手段もあります。
Googleスプレッドシートにも同じ機能があります。公式の案内では、ソースデータを含むセルを選択し、列ごとに見出しを付けることが必要で、上部のメニューから挿入とピボットテーブルを選び、サイドパネルの行または列の横にある追加をクリックして値を選ぶ手順が示されています。値の横にある追加をクリックして表示する値を選ぶところまで、Excelと考え方は同じです。無料で使えて、複数の人が同時に見られるのが利点です。
どちらを選ぶかの基準は3つです。1つ目は、データがどこにあるかです。回答が表計算のファイルに集まっているなら、そのまま同じ道具で集計するのが早いです。2つ目は、誰が見るかです。1人で作業するならExcelのほうが操作が速く、複数の人で見るならブラウザで開ける形が便利です。3つ目は、件数です。数万行を超える規模では、ブラウザ上の操作が重くなることがあります。
集計の専用の道具を使う手もありますが、件数が数千行までなら、表計算のソフトで十分に足ります。おすすめの順序としては、まず手元にある道具で1度作ってみて、そこで足りないと分かったものだけを次の道具に求めることです。足りないものが分からないまま道具を選ぶと、使わない機能に費用を払うことになります。
無料で使える範囲については、その時点の公式の案内で自分で確かめてください。使える機能や上限は変わります。複数の人が同時に編集する形にするとき、誰が編集して誰が閲覧だけなのかを分けられるかも、あわせて確かめておく点です。回答に個人を特定できる情報が含まれている場合、集計用のファイルを誰でも開ける場所に保存すると、必要のない範囲まで見られる状態になります。集計に使う列だけを別のシートに写し、氏名や連絡先を含めない形にしておくのが安全です。
個人情報の扱いについての判断は所管の窓口や専門家に確かめるべきものですが、社内で方針を決めるときの出発点としては、個人情報保護委員会が公開している資料が使えます。
よくある失敗と、その直し方
最後に、詰まりやすい場所と直し方を並べます。
集計の対象に、回答していない行が含まれている場合です。空白の行が「空白」という項目として表に並びます。元のデータから空白の行を除くか、フィルターで除きます。
複数選択の設問が、1つのセルにカンマ区切りで入っている場合です。この形はそのままでは軸に使えません。選択肢ごとに列を分けて、選んだかどうかを記録する形に直します。回答を集める段階でこの形にしておけば、集計の作業が1工程減ります。
同じ人が複数回回答している場合です。集計の前に、重複を確かめます。メールアドレスや受付の番号で数えて、2回以上出てくるものが無いかを見ます。重複に気づかないまま集計すると、熱心な1人の意見が全体の傾向として出てきます。
数字が更新されない場合です。ピボットテーブルは、元のデータを変えても自動では変わりません。更新の操作が必要です。行を追加した場合は、集計の対象の範囲そのものを広げる必要があります。元のデータをExcelの表として書式設定しておくと、行を追加したときに範囲が自動で広がるため、この事故を防げます。
選択肢を後から追加した場合です。設問の途中で選択肢を1つ追加すると、追加より前の回答者はその選択肢を選べなかったことになります。この違いを書き残していない表は、追加した選択肢が少なく見えるという読み間違いを生みます。設問を変えた日付は、必ず記録に残します。
同じ人の回答を上書きで受けている場合です。回答の修正を受け付ける作りにしていると、集計に使えるのは最後の回答だけになります。最初にどう答えたかを知りたい場合は、上書きせず履歴として残す形にしておく必要があります。どちらの形で受けているかは、集計を始める前に確かめておきます。
集める段階で、集計しやすい形にしておく
ここまでは、集まった回答を集計する話でした。実は、集計の手間の大半は集める段階の作りで決まっています。
自由記述で年代を聞けば、「20代」「20歳代」「25歳」という3種類の表記が混ざります。選択肢から選ばせれば混ざりません。複数選択を1つの欄で受ければ、後から列に分ける作業が発生します。選択肢ごとに分けて受ければ発生しません。集計の作業に何時間もかかっているなら、直す場所は表計算のソフトの使い方ではなく、受け口の作りです。
もう1つ、集計以前の問題として起きるのが、回答がどこにあるか分からなくなることです。フォームで受けた回答と、メールで届いた回答と、電話で聞いた回答が別の場所に記録されていると、集計に使えるのは一部だけになります。送信されたあとの道具がそろっている形にしておけば、届いた1件に担当と状態が付き、集計に使う一覧がそのまま日々の作業の画面になります。
いまGoogleのフォームで受けていて、回答が表に並ぶところまでで止まっている場合、送信されたあとに何が残るかの違いはGoogleフォームとの比較に整理しています。自社のサイトに自作のフォームを設置している場合の考え方はContact Form 7との比較に、社内がMicrosoft 365でそろっている場合はMicrosoft Formsとの比較にまとめています。
集計したい内容は、受け付けているものの種類で変わります。応募者の属性を集計するなら採用の応募受付に、公募の申請の内訳を数えるなら助成金・公募の受付に、問い合わせの種類を数えるなら問い合わせの受付に、講座の受講者の傾向を見るなら講座の受講申し込みに、それぞれ作業の流れを整理しています。
受け口に何を持たせられるかはできることに、実際の画面の動きは動くところを見るで確かめられます。集計や書き出しについて出やすい疑問はよくある質問にまとめています。
進める順序としては、データの形を整え、ピボットテーブルで軸を入れ替えながら試し、行集計に対する比率で読み、人数を併記して報告する。次に同じ集計をするときのために、受け口の設問の形を1つだけ直しておく。この繰り返しで、集計にかかる時間は回を追うごとに短くなります。
Q1. クロス集計と単純集計はどう違いますか？
単純集計は1つの質問について全体で何%かを出す作業で、クロス集計は2つ以上の質問を掛け合わせて数え直す作業です。全体の満足度が高く見えても、年代で分けると狙っていた層だけ低いという事実が出てくることがあります。次にどこへ力を入れるかを決めるには、掛け合わせた表が必要になります。
Q2. ピボットテーブルで割合を出すにはどうすればよいですか？
値フィールドの設定を開き、計算の種類を変えます。クロス集計で使うのは主に3つで、行集計に対する比率、列集計に対する比率、総計に対する比率です。軸として使いたい属性を行に配置し、行集計に対する比率を選ぶと、その属性ごとの内訳として読める表になります。
Q3. データを用意するときに気をつけることは何ですか？
公式の案内では、1つのヘッダー行がある列にまとめること、空の行や列がないこと、列のデータ型が同じであること、Excelの表として書式設定することが挙げられています。加えて、同じ意味の値が違う表記で混ざっていないかを列ごとに確かめます。表記が揺れていると、同じ属性が2行に分かれて並びます。
Q4. Excelがなくてもクロス集計はできますか？
できます。Googleスプレッドシートにも同じ機能があり、公式の案内では、見出しを付けたデータを選択し、挿入からピボットテーブルを選び、行と列と値にフィールドを追加する手順が示されています。無料で使えて複数の人が同時に見られる利点がありますが、数万行を超える規模では操作が重くなることがあります。