エクセル(Excel)/ 複数回答のアンケート集約 ~ COUNTIF 関数 ー 検索条件に「ワイルドカード文字」を使って

今回は、「表計算ソフト」エクセル(Excel)で、「COUNTIF 関数を使って、複数回答のアンケートを集約する方法」について紹介します。

COUNTIF(カウントイフ)関数は、1 つの検索条件に一致するセルの個数をカウントする関数です。

この COUNTIF 関数 の検索条件に 「ワイルドカード文字」を使うことで、任意の文字列を含むセルの個数をカウントすることができます。

※ 操作は、Microsoft 365 エクセル(Excel)で行っています。

条件が一致するセルを検索

エクセルで集計

アンケート回答の入力

(1)<図1>は、「2学期にむけて ~ 成績を伸ばしたいと思う教科」を、いわゆる5教科 「a,国語 b,社会 c,数学 d,理科 e,英語」の中から選択したアンケート調査(複数回答可)の回答である。
<図1>

 エクセルのワークシートに、アンケートの回答を入力しました。なお、人数については、この記事用に10人までにしています。

 

COUNTIF 関数の使用

=(イコール)を入力する

(2)国語と回答した人数の集計を表示するセルとして、「E2」セル をクリックして選択した後<図2ー1>、数式バーをクリックし、数式の入力が始まることを意味する =(イコール)を数式バーに直接入力する。<図2ー2>
<図2ー1>

 

<図2ー2>

 

「関数名のリスト」を表示する

(3)=(イコール)に続いて、cou と入力すると、cou で始まる関数名(ここでは「関数名のリスト」とよびます)が表示される。
<図3ー1>

 

※ 「関数名のリスト」が表示されない場合は、①「Excelのオプション」→ ②「数式」→ ③「数式オートコンプリート」をクリックし、チェックマークをオン(チェックマークあり)にした後、④「Excelのオプション」右下にある[OK]ボタンをクリックしてください。
<図3ー2>

 

COUNTIF をダブルクリックする

(4)「関数名のリスト」の中から COUNTIFダブルクリックすると<図4ー1>、数式バーに COUNTIF( と表示される。<図4ー2>
<図4ー1>

 

<図4ー2>

 

カウントする範囲を指定する

(5)「B2」セル から 「B11」セル までドラッグ(マウス左ボタンを押したまま移動)して選択し、カウントするセルの範囲を指定する。数式バーに B2:B11 と表示される。<図5>
<図5>

 

範囲を絶対参照に変える

(6)オートフィル機能を使ったとき、参照先のセルが移動しないように、[F4]キーを1回押し、範囲を絶対参照に変える。数式バーの範囲が $B$2:$B$11 に変わる。
<図6>

 

カンマを入力する

(7)範囲指定が終わったので、 ,(カンマ)を入力する。
<図7>

 

検索条件を入力する

(8)目的は、「国語」と回答した生徒の人数をカウントすることなので、検索条件の「D2」セル(「国語」と入力してある)をクリックし<図8ー1>、最後に数式を ) で閉じる。<図8ー2>
<図8ー1>

 

<図8ー2>

 

エンターキーを押す

(9ー1)[Enter](エンター)キーを押し、数式を確定する。「E2」セル に 1 と表示される。
<図9-1>

 

 この数式では、「D2」セル の「国語」と、セルの内容が全く同じ「B8」セル の「国語」のみ、カウント(表示)されます。

 

(9ー2)ちなみに、「B8」セル内 の「国語」の後ろに全角スペースを入力し<図9-2ー1>、「B8」セル を「国語 」とすると、「D2」セル の「国語」とは一致しないので、 0 とカウントされます。<図9-2-2>半角スペースや記号などがセル内に入っていても、「国語」と「一致しない」ので、0 とカウントされます。
<図9-2ー1>

 

<図9-2-2>

 

(9ー3)なお、数式バーに、=COUNTIF($B$2:$B$11,"国語") と教科名を直接入力しても、やはり結果は同じ 1 となります。
<図9-3>

※ この "(ダブルクォーテーションマーク)で囲んだ部分は、文字列を表します。

 

※ 以下、今回、数式で使用するキーを紹介します。数式の記号は、半角/英数モードで入力します。

 

検索条件を変える

ワイルドカード文字の挿入

検索条件を変える

(10)数式バー内の D2 の前にカーソルを置く。
<図10>

 

「国語を含む」を条件とする

(11)D2(国語)の前に "*"& と入力<図11ー1>、D2(国語)の後ろに &"*" と入力する。<図11ー2>
<図11ー1>

 

<図11ー2>

 

※ "(ダブルクォーテーションマーク)で囲んだ部分は、文字列を表します。

※ *(アスタリスク) は、ワイルドカード文字で、任意の文字列を表します。

※ &(アンパサンド)は、文字列を結合する演算子です。(セルは "" で囲みません)

 

※ "*"&D2&"*" と入力することで、「D2」セル(D2セル内の「国語」という文字列)を含むセルをすべてカウントします。

つまり、セル内の文字列が、「国語」(国語単独)、「・・・国語」(最後に国語)、「国語・・・」(最初に国語)、「・・・国語・・・」(途中に国語)となっているセルをすべてカウントします。

 

エンターキーを押す

(12)[Enter]キーを押し、数式を確定する。「E2」セル に 4 と表示される。
<図12ー1>

 

 なお、D2 の代わりに(つまり、セル参照にしないで)ワイルドカード文字のアスタリスクを前後に使って、 "*国語*" (国語を含むすべての文字列という意味)と入力しても同様の結果になります。
<図12ー2>

 

回答欄を見る

(13)回答欄を見ると、「国語」という回答者が4人いることがわかる。
<図13>

 

数式のコピー

 集計欄「E2」セル に入力されている数式を、「オートフィル機能」を使って、同じE列の連続している「E6」セル までコピーします。

 

オートフィル機能を使う

(14)「E2」セル をクリックして選択、「緑色の太枠」右下の「四角形のフィルハンドル」にマウスポインター(以下、ポインターとよびます)を合わせると、ポインターが「黒い十字」(ここでは、「十字ポインター」とよびます)に変わる。
<図14>

 

フィルハンドルをドラッグする

(15)「十字ポインター」を「E6」セル までドラッグし、マウス左ボタンを離すと、数式がコピーされ、各セルに数値が表示される。
<図15>

 

※ ここで、「オートフィル機能」を使ってコピーした後の数式が、どのように変わっているか調べることにします。

数式を表示する

(16)①「数式」タブ → ②「ワークシート分析」グループ → ③[数式の表示]ボタンをクリックし<図16ー1>、数式バーの数式をセル内に表示させて確認する。<図16ー2>
<図16ー1>

 

<図16ー2>

 

(検索条件として使用した)コピー元の「D2」セル が相対参照なので、コピー後の参照先のセルが、自動的に変更された数式(検索文字列の行番号が D3 D4 D5 D6 と1行ずつ自動的にずれた数式)になっています。「オートフィル機能」の便利さがわかります。

 

※ なお、数式のセル内表示を解除するときは、[数式の表示]ボタンを再度クリックします。

 

今回は、「表計算ソフト」エクセル(Excel)で、「COUNTIF 関数を使って、複数回答のアンケートを集約する方法」について紹介しました。

 

 

※ セル内に同じ回答が複数あるアンケートの集約については、下記リンクをどうぞ。