Excel。データ分析23。年代別複数回答をクロス集計にする
＜COUNTIFS関数＞
分析をするにしても、元のデータベースは文字情報レベルでしかないので、集計して少しでも「見える化」する必要があるわけですね。
ところが、この集計がなかなかの曲者だったりすることが
多く現場では見受けられます。
次の表がその一例です。
A1:D11が元のデータベースで、
それを、F1:L6までの年代別の複数回答の件数がわかる表にしていきたいわけです。
やりたいことは簡単そうに見えるのですが、コレ意外と厄介なんですね。
最初に、算出するのが面倒なのが、年齢を年代別の人数を算出する。
そして、D列のアンケート結果から年代別で1がいくつあるのか？
という算出する作業があるわけですね。
A1:D11が元のデータベースを集計のことも考慮して作成していれば、
問題はないわけです。
例えば、C列の年齢だけではなくて、年代の列も設けておくとか、
D列のアンケート結果もD列１つにするのではなくて、
それぞれ列ごとにわけて、回答があれば○などの文字をいれておけば、
その文字数を数えるだけでよかったわけです。
まぁ、つべこべ言っても、A1:D11が眼前にあるわけですから、
現場では処理しなければいけないわけですね。
では、G列の年代を算出するところから始めていきましょう。
【事前準備：何度も使う範囲には”名前の定義”】
年齢から年代を求めるには様々な方法がありますが、今回の場合、F2に20と入力されていますので、C2:C11で、
20以上で、C2:C11で30より小さい件数を求めればいいわけですね。
少しアイディアなのですが、C2:C11が複数登場しますので、
名前の定義を使っておくとわかりやすく、管理しやすくなりますので、
C2:C11を範囲選択して、名前ボックスに「年齢」と入力して、
名前の定義をしておきましょう。
ついでに、D2:D11も同じように、
「アンケート結果」と名前を定義しておきます。
【年代の算出】
それでは、G2をクリックして、COUNTIFS関数ダイアログボックスを表示しましょう。
COUNTIF関数とAND関数を使うのでもいいのですが、
複数条件に対応した関数が用意されていますので、
わざわざ、関数のネストにする必要がありませんね。
検索条件範囲1には、数式タブの「数式で使用」から、
先ほど設定した、年齢をクリックして、”年齢”と入力しましょう。
検索条件1には、F2以上なので、”>=”&F2
比較演算子とセル番地を組み合わせて使う場合には、
比較演算子に””(ダブルコーテーション)で挟んで、&を使って、
セル番地と結合させる必要があります。
検索条件範囲2にも、検索条件範囲1と同じ方法で、”年齢” を設定します。
検索条件2には、”<”&F2+10
こちらの条件は、より小さいという条件ですから、”<”を使います。
そして、20年代は、20歳以上、30歳未満なわけなので、
F2+10をすれば、30という数値が作れます。
そして、OKボタンをクリックして、オートフィルで、数式をコピーしましょう。
なお、F2の数式は、
=COUNTIFS(年齢,">="&F2,年齢,"<"&F2+10)
次は、アンケート結果を算出していきましょう。
【複数回答をカウントする】
H1をクリックして、COUNTIFS関数ダイアログボックスを表示しましょう。最初は、年代を算出する関係を作っていきます。
検索条件範囲1には、年齢
検索条件1には、">="&$F2
数式を横方向、縦方向にオートフィルでコピーしますので、
複合参照でF列を固定する必要があります。
検索条件範囲2は、年齢
検索条件2には、"<"&$F2+10
検索条件範囲3は、アンケート結果
検索条件3には、"*"&H$1&"*"
ワイルドカードを使ってあげることで、
「*1*」という形にしてあげれば、
文字を含んでいるという条件で求めることができます。
OKボタンをクリックして、数式をオートフィルでコピーしましょう。
H1の数式は、
=COUNTIFS(年齢,">="&$F2,年齢,"<"&$F2+10,アンケート結果,"*"&H$1&"*")
このようにしてあげると、元データから集計しやすい表を作ることができます。
