エクセル(Excel)/ セル内に同じ回答が複数あるアンケートの集約 ~ LEN 関数 と SUBSTITUTE 関数の組み合わせ ー 文字数の減少に注目して

今回は、「表計算ソフト」エクセル(Excel)で、「LEN 関数 と SUBSTITUTE 関数を組み合わせて、セル内に同じ回答が複数あるアンケートを集約する方法」について紹介します。

LEN(レン)関数は、文字列の文字数をカウントする関数です。

また、SUBSTITUTE(サブスティチュート)関数は、置換する文字列を検索し、指定した文字列に置換する関数です。

この2つの関数を組み合わせ、文字数の減少に注目して、目的の文字列をカウントします。

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

入力されている数式

エクセルで集計

アンケート回答の入力

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

 

 ワークシートの各セルに、アンケートの回答(班でそれぞれのメンバーが1人1教科ずつ回答)を全部入力しました。なお、班(4人で構成)については、この記事用に5班までにしています。

 

入力されている数式

国語と回答した人数の集計欄

(2)国語と回答した人数の集計を表示したセル、「E2」セル をクリックして選択する。
<図2>

 

数式バーを見ると

(3)数式バーに、すでに入力されている数式が表示される。
<図3ー1>

 

 SUM(サム)関数は、範囲内の数値を加算する関数です。

 LEN(レン)関数は、文字列の文字数をカウントする関数です。

 SUBSTITUTE(サブスティチュート)関数は、置換する文字列を検索し、指定した文字列に置換する関数です。

 

 この数式は、エクセルのバージョンによっては、[Ctrl](コントロール)キーと[Shift](シフト)キーを押しながら[Enter](エンター)キーを押して数式を確定し、数式の両側に 中かっこ {} が入っている場合があります。
<図3ー2>

 

※ なお、例えば、国語の集計で、D2 の代わりに(つまり、セル参照にしないで)、教科名を "(ダブルクォーテーションマーク)で囲んで、"国語"としても同じ結果になります。( 下図「F2」から「F6」セル 参照)
<図3ー3>




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

 

数式及び関数についての「Q&A」

SUBSTITUTE 関数について

Q1. SUBSTITUTE(サブスティチュート)関数のスペルが難しいのですが・・・。

SUBSTITUTE をダブルクリックする

A1. 「C2」セル をクリックして選択、② 半角英数モードで = と入力(数式の始まりを表します)、③ 続けて sub と入力します。④ sub を含む関数名(ここでは「関数名のリスト」とよびます)が表示されるので、⑤「関数名のリスト」の中から SUBSTITUTE をダブルクリックすると<図4ー1>、⑥ 数式バーに SUBSTITUTE( と表示されます。<図4ー2>
<図4ー1>

 

<図4ー2>

 

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

 

指定した文字列に置換する

Q2. SUBSTITUTE 関数を使うと、セル内の文字列は、どのように変わるのですか?

検索範囲、検索文字列、置換文字列を指定する

A2. <図4ー2>のSUBSTITUTE( に続いて、① 検索範囲として「B2」セル をクリックした後、② ,(カンマ)を入力、③ 検索文字列(置換される前の文字列)として、ここでは、"英語"と入力した後、④ ,(カンマ)を入力、⑤ 置換文字列(置換した後の文字列)として、ここでは「空白の文字列」を表す""と入力した後、⑥ 半角の ) で数式を閉じます。<図5ー1>(記号は、すべて半角英数モードで入力します)
<図5ー1>

 

[Enter](エンター)キーを押して、数式を確定し、⑧「C2」セル を見ると、(「B2」セル の英語を空白の文字列に置換した)英語のない文字列に変わっています。
<図5ー2>

 

※ 空白の文字列とは、空白(文字列がない状態)のことで、スペース とは違います。セル内から文字列がなくなり、文字数としてもカウントされません。

 

SUBSTITUTE 関数の使用例

SUBSTITUTE 関数は、例えば、「英語という表記(文字列)を検索し、すべて外国語という表記(文字列)に置換する」場合などによく使われます。膨大なデータがあるときは、とても便利です。
<図5ー3>

 

実際の文字数

Q3. 関数の使用によって、実際に文字数がどのように求められるのか教えてください。

英語にしぼって文字数を調べる

A3. 範囲を「B2」セル、教科を英語にしぼって、文字数を調べてみます。

 回答欄の「B2」セル の文字数は、11文字です。(LEN 関数を使用)
<図6ー1>

 

 ①「英語、数学、国語、英語」で、11文字となります。

 

 SUBSTITUTE 関数を使用(英語を空白の文字列に置換)して、「B2」セル の英語を除いた文字列の文字数は、7文字です。(LEN 関数と LEN 関数のネスト(入れ子)として、SUBSTITUTE 関数を使用)
<図6ー2>

 

 ②「、数学、国語、」で、7文字となります。

 

 (まわりくどいようですが)からを引くと、除かれた英語の文字数が4文字だとわかります。
<図6ー3>

 

 ①「英語、数学、国語、英語」(11文字)ー ②「、数学、国語、」(7文字)= ③「英語英語」(4文字)となります。

 

 の文字数の4文字、つまり、「英語英語」(4文字)を「英語」(2文字)でわると、英語の回答数は2となります。
<図6ー4>

 

 の「文字数を求める」数式の対象範囲を、「B2」セル ~「B6」セル に拡大し、それを SUM 関数を使って合計したのが、下記の数式です。(SUM 関数を使用・・・<図3ー1>参照、D2は国語の集計の場合)

=SUM(LEN($B$2:$B$6)-LEN(SUBSTITUTE($B$2:$B$6,D2,"")))/LEN(D2)

=SUM(LEN(検索する範囲)-LEN(SUBSTITUTE(検索する範囲,検索する文字列,置換する文字列)))/LEN(検索する文字列)

 

範囲指定の方法

Q4. $B$2:$B$6 は、どのように入力するのですか。

セルをドラッグ後、[F4]キーを1回押す

A4. キーボードから直接入力してもかまいませんが、ここでは、①「B2」セル から「B6」セル までをドラッグ(マウス左ボタンを押したまま移動)して範囲指定した後(数式バーには、B2:B6と表示される)、②[F4]キーを1回押して、③ $B$2:$B$6 に変えています。
<図7>

 

 このように、セルを「絶対参照」にすると、オートフィル機能を使ったとき、セル範囲が移動しません。

 

わり算の取り扱い

Q5. 次のようなアンケートの回答のときは、最後のわり算をしなくても集計できたのですが・・・。
<図8ー1>

 

検索する文字列が1文字のとき

A5. SUBSTITUTE 関数 で、検索する文字列(空白の文字列に置換される文字列)が1文字の場合は、最後のわり算は不要になります。
<図8ー2>

 

 この場合は、除かれた文字の文字数がそのまま回答数(例えば、除かれた文字が国国国の3文字であれば、すでに、国語の回答数は3とわかる)なので、国国国(3文字)を、最後に国(1文字)でわる、つまり1でわる必要はありません。

 

今回は、「表計算ソフト」エクセル(Excel) で、「LEN 関数 と SUBSTITUTE 関数を組み合わせて、セル内に同じ回答が複数あるアンケートを集約する方法」について紹介しました。

 

 

 COUNTIF 関数を使って、複数回答のアンケートを集約する方法については、下記リンクをどうぞ。