いまさら聞けないExcelの使い方講座

【Excel】質問の回答を年齢別に集計したい！エクセルでアンケートをクロス集計するときのコツ

トップスタジオ

2020年3月6日 06:55

アンケートの集計作業は時間がかかるので大変……

　普段の生活の中では、いろいろな場面でアンケートに答える機会があります。アンケートに答える側の場合は気軽に回答することが多いものですが、仕事などで自分がアンケートを回収して集計する側になると、集計が大変でつい気が重くなってしまう……ということはありませんか？

　Excelには、関数でアンケートを効率よく集計できるテクニックがあります。今回は、ExcelのCOUNTIFS関数をアンケート集計に活用する方法を解説します。

複数の条件で回答を集計する時に便利なCOUNTIFS関数

　読者アンケート調査を実施し、Excelを使って回答を集計することになったとしましょう。このアンケートの回答者は、自分の年齢（年代）と、ある質問に対する回答を「A」～「E」または「その他」の6種類から選択して回答します。「読者アンケート結果」の表（①）には、各回答者の回答した内容が入力されています。この表の右側にある「集計」欄（②）を使って、年代別に各回答の数を数えてみます。

　今回は、「年代別」「アンケートの回答別」という2つの条件で集計します。このように、複数の条件でデータを集計したい場合には、COUNTIFS関数を使うと便利です。COUNTIFS関数は、複数の条件に該当するデータの数を集計する関数で、次のような書式で使います。

　「条件範囲1」には、1つ目の条件を検索する対象となるセル範囲を指定し、「検索条件1」には、1つ目の条件そのものや条件が入力されたセルを指定します。同様に、「条件範囲2」「検索条件2」には、それぞれ2つ目の条件を検索する対象となるセル範囲と、2つ目の条件を指定します。この書式例では条件範囲・検索条件ともに2つまでしか記載していませんが、最大で127組の条件範囲・検索条件を指定することができます。また、条件範囲と検索条件が1つのみの場合もCOUNTIFS関数で集計できます（この場合、COUNTIF関数と結果は同じになります）。

　例として、年代が「20代」で「A」と回答した人の数を集計してみましょう。B列の「年代」欄の中で年代が「20代」にあたり、かつC列の「回答」欄で「A」に該当するデータの数を数えればよいということになります。

　COUNTIFS関数を使って数式を作成していきましょう。まず、1つ目の条件範囲と条件を指定します。1つ目の条件範囲はB列で「年代」が入力されているセル範囲（ここではセル範囲B3:B27）、条件はセルE3に入力されている「20代」となります。したがって、数式の前半は「=COUNTIFS(B3:B27,E3,」（③）となります。ここでは、この数式を他の場所にコピーしたりすることは想定していないので、絶対参照にしなくても問題ありません。

　続けて、2つ目の条件範囲と条件を入力します。2つ目の条件範囲はC列で「回答」が入力されているセル範囲（ここではセル範囲C3:C27）、条件はセルF3に入力されている「A」となります。したがって、数式の後半は「C3:C27,F3)」（④）となります。

　［Enter］キーを押すと、20代で「A」と回答した人の数がセルG3に「3」（⑤）と表示されます。

　このように、COUNTIFS関数を使った数式をセルG3に入力しておくと、セルE3やセルF3に入力する条件を変更するだけで該当の回答数が自動で再計算されます。例えば、「40代」で「B」と回答した人の数は「1」となります（⑥）。

　一度数式を作成すれば、いろいろな条件で簡単に集計ができるので便利ですよね。

応用：セル範囲を正しく参照させればクロス集計も簡単に

　前項では、調べたい条件をその都度入力することでいろいろな条件で集計を行うことができる集計表を作成しましたが、今回の例では条件の数が多いため、手作業で条件を入力し直しながら集計するのは少し面倒ですよね。すべての年代で、すべての回答を一気に集計する方法はないでしょうか。

　皆さんは、今回の例のように2つの条件で何かを集計する時、片方の条件を表の縦方向に、別の条件を表の横方向に並べて集計したことはありませんか？今回のアンケート結果の集計の例であれば、回答を縦に、年代を横に並べて集計できますよね。このような集計表を「クロス集計表」といいます。言葉で説明するとわかりにくいかもしれませんが、実際に集計に使う表を見ればピンとくると思います。先ほど作成した集計表の下に、「集計（全体）」というクロス集計表の枠（①）を作成してみました。皆さんもこのような表にきっと見覚えがありますよね。

　このようなクロス集計も、COUNTIFS関数を使って簡単に行うことができます。ただし、先ほどの例とは違って数式を他のセルにコピーする必要があるため、絶対参照と相対参照を正しく指定する必要があります。どの部分を絶対参照にするかを考えながら、少しずつこの表に数式を入力していってみましょう。「条件範囲」と「検索条件」となる項目は前項と同じですが、集計表の形式が変わっているため、「検索条件」として指定するセルの場所が異なっていることに注意が必要です。

　「10代」で「A」と回答した人の数を集計するセルF7に数式を入力していきます。「条件範囲1」は、前項と同様にセル範囲B3:B27になりますが、数式をどのセルにコピーしても参照したいセル範囲は変わらないので、絶対参照にする必要があります。したがって、数式の最初は「=COUNTIFS($B$3:$B$27,」（②）となります。実際に数式にセル範囲を入力する時には、条件範囲とするセル範囲B3:B27をドラッグした後で［F4］キーを押すと、簡単に絶対参照にすることができます。

　「検索条件1」は、ここではセルF6に入力されている「10代」となります。この「F6」を数式内で指定する時に、少し注意が必要です。この数式を横方向にコピーする時、列の参照はコピーする方向に応じて変化してほしいので、列は固定しません。一方、縦にコピーする時は、行の参照はコピーする方向に応じて変化してほしくないので、行は固定します。したがって、先ほどの数式の続きは「F$6,」（③）となります。

　次に、「条件範囲2」のセル範囲を指定します。指定するセル範囲はC3:C27で、ここでも数式をどのセルにコピーしても参照範囲が変わらないようにする必要があるため、絶対参照で指定します。数式の続きは「$C$3:$C$27,」（④）となります。

　最後に、「検索条件2」を指定します。ここで検索条件となるのはセルE7に入力されている「A」ですが、ここでもセルを数式内で指定する時に注意が必要です。この数式を横方向にコピーする時、列の参照はコピーする方向に応じて変化してほしくないので、列は固定します。一方、縦にコピーする時は、行の参照はコピーする方向に応じて変化してほしいので、行は固定します。したがって、数式の最後は「$E7)」（⑤）となります。

　これで数式は完成です。［Enter］キーを押すと、それぞれの条件に該当するデータの数がセルに表示されます。ここでは「10代」で「A」と回答した人はいなかったため、セルF7には「0」（⑥）と表示されます。

　セルF7に入力した数式を表内の他のセルにコピーしてみましょう。まず、フィルハンドルを右方向にセルK7までドラッグします（⑦）。この表の一番右のL列は、後ほど年代の「合計」を求める別の数式を入力するため、先ほどの数式をコピーするのはK列までになります。

　数式がコピーされ、セル範囲G7:K7に計算結果が表示されます。そのまま、フィルハンドルを下方向にセルK12までドラッグ（⑧）します。

　数式がセル範囲F7:K12にコピーされ、年代別に回答者数が表示されました（⑨）。数式を作成するのは少し大変かもしれませんが、数式が完成してしまえばあとはコピーするだけで集計が完了しますよ。

　最後に、先ほど空欄にした「合計」のL列に数式を入力します。ここでは、年代は関係なく各選択肢の回答者数を合計すればよいので、条件は1つです。そのため、使う関数はCOUNTIFS関数でもCOUNTIF関数でもOKです。ここではCOUNTIF関数を使って、セルL7に「=COUNTIF($C$3:$C$27,$E7)」（⑩）と入力します（もちろん、シンプルにSUM関数を使ってF列からK列の値を合計しても構わないのですが、今回はCOUNTIF関数の使い方の練習として、COUNTIF関数を使ってみます）。

　これは先ほどの数式の2つ目の条件を入力した時と同じで、セル範囲C3:C27の中で「A」と入力されているセルを数える数式です。L列の他のセルにコピーすることを考えて、絶対参照や複合参照を指定しています。

　［Enter］キーを押して数式を確定し、セルL12まで数式をコピーすれば、各回答の合計数がセル範囲L7:L12に表示されます（⑪）。

　これだけ項目数の多いクロス集計表も、数式を使えば簡単に作成できましたね。