いまさら聞けないExcelの使い方講座
【Excel】アンケート結果をクロス集計したい！ 縦横2つの条件に合致する値を表にするには
2023年1月18日 06:55
クロス集計の作成には「COUNTIFS」関数を利用する
以下のような上端と左端に見出しのある表を見たことがありますよね。「クロス集計表」や「クロス表」などと呼ばれます。アンケートの集計のほか、乗り物の料金表や施設の会員料金などのいろいろなシーンで見かけます。
縦と横の項目の交差する箇所に目的の値が入力されている構造です。例えば、年代が「20代」で、回答が「B」なら、交差する箇所の「2」が目的の値です。
このようなクロス集計表にデータをひとつずつ手入力するのは現実的ではありません。COUNTIFS関数を利用するのが定番。構文は以下の通りです。
クロス集計表の元になるデータは、隣の表に［年代］列と［回答］列に一覧でまとめられているので、ここからカウント可能。では条件は？ クロス集計表の上端と左端の見出し項目が使えそうです。これをふまえてクロス集計表を作成してみましょう。
基本の数式を作成する
まずはクロス集計表の左上のセルF3に基本となる数式を入力してみましょう。この表ではB3～B38に回答者の年代、C3～C38に回答が入力されています。［検索範囲］は［年代］列のセルB3:B38と［回答］列のセルC2:C38です。［検索条件］はセルE3とF2です。これを踏まえると数式は以下のようになります。
=COUNTIFS(B3:B38,E3,C3:C38,F2)
COUNTIFS関数の数式は、条件範囲と条件のセットで指定するだけなので簡単ですよね。引数を指定した時のセル範囲を確認して、数式を確定してください。
数式のコピーを考慮した参照方式を指定する
先ほど入力した数式をオートフィルでコピーすると、結果がおかしいことに気づくと思います。COUNTIFS関数の引数に指定したセル範囲が「相対参照」であることが原因です。相対参照で指定したセル範囲は、数式のコピーによって参照がずれます。ずらしたくないセル参照を固定するのがポイントです。
セル参照をずらしたくない［検索条件範囲］と［条件］を「絶対参照」で固定しましょう。数式の引数に指定したセル範囲を選択して［F4］キーを押すと、参照方式を簡単に切り替えられます。
変更した数式を横方向にもコピーしてみます。お気づきの人もいると思いますが、上記の修正では不完全。再び修正が必要になります。
横方向にコピーする際の参照方式を考えてみましょう。［条件1］のセル参照がずれてしまったことと、［条件2］のセル参照がずれていないことの2つの問題があります。
［条件1］がずれてしまったのは「相対参照」だから。［条件2］がずれないのは「絶対参照」に修正したためです。行と列のいずれかを固定する「複合参照」を利用して解決します。
下方向へコピーする時は行の参照をずらしたく、横方向へコピーするときは列の参照をずらしたいわけですから、［条件1］の「E3」は列を固定する「$E3」、［条件2］の「F2」は行を固定する「F$2」とします。ここがポイントです。
行や列を固定する複合参照に慣れよう
ひとつの数式を下方向と横方向にコピーする場合、行や列を固定する「複合参照」がポイントです。慣れないうちは、どちらを固定するかに悩みますが、マスターするには試行錯誤するのが近道です。参照方式を切り替えながら試してみてください。