エクセル（Excel）/ セル内に同じ回答が複数あるアンケートの集約 ～ LEN 関数 と SUBSTITUTE 関数の組み合わせ ー 文字数の減少に注目して
今回は、「表計算ソフト」エクセル（Excel）で、「LEN 関数 と SUBSTITUTE 関数を組み合わせて、セル内に同じ回答が複数あるアンケートを集約する方法」について紹介します。
LEN（レン）関数は、文字列の文字数をカウントする関数です。
また、SUBSTITUTE（サブスティチュート）関数は、置換する文字列を検索し、指定した文字列に置換する関数です。
この２つの関数を組み合わせ、文字数の減少に注目して、目的の文字列をカウントします。
※ 操作は、Microsoft 365 エクセル（Excel）で行っています。
入力されている数式
エクセルで集計
アンケート回答の入力
（１）＜図１＞は、「２学期にむけて ～ 成績を伸ばしたいと思う教科」を、いわゆる５教科「a,国語 b,社会 c,数学 d,理科 e,英語」の中から１人１教科ずつ選択したアンケート調査の回答を、班ごとにまとめたものである。
＜図１＞
※ ワークシートの各セルに、アンケートの回答（班でそれぞれのメンバーが１人１教科ずつ回答）を全部入力しました。なお、班（４人で構成）については、この記事用に５班までにしています。
入力されている数式
国語と回答した人数の集計欄
（２）国語と回答した人数の集計を表示したセル、「E2」セル をクリックして選択する。
＜図２＞
数式バーを見ると
（３）数式バーに、すでに入力されている数式が表示される。
＜図３ー１＞
※ SUM（サム）関数は、範囲内の数値を加算する関数です。
※ LEN（レン）関数は、文字列の文字数をカウントする関数です。
※ SUBSTITUTE（サブスティチュート）関数は、置換する文字列を検索し、指定した文字列に置換する関数です。
※ この数式は、エクセルのバージョンによっては、［Ctrl］（コントロール）キーと［Shift］（シフト）キーを押しながら［Enter］（エンター）キーを押して数式を確定し、数式の両側に 中かっこ {} が入っている場合があります。
＜図３ー２＞
※ なお、例えば、国語の集計で、D2 の代わりに（つまり、セル参照にしないで）、教科名を "（ダブルクォーテーションマーク）で囲んで、"国語"としても同じ結果になります。（※ 下図「F2」から「F6」セル 参照）
＜図３ー３＞
※ "（ダブルクォーテーションマーク）で囲んだ部分は、文字列を表します。
数式及び関数についての「Q＆A」
SUBSTITUTE 関数について
Q１. SUBSTITUTE（サブスティチュート）関数のスペルが難しいのですが・・・。
SUBSTITUTE をダブルクリックする
A１. ①「C2」セル をクリックして選択、② 半角英数モードで = と入力（数式の始まりを表します）、③ 続けて sub と入力します。④ sub を含む関数名（ここでは「関数名のリスト」とよびます）が表示されるので、⑤「関数名のリスト」の中から SUBSTITUTE をダブルクリックすると＜図４ー１＞、⑥ 数式バーに SUBSTITUTE( と表示されます。＜図４ー２＞
＜図４ー１＞
＜図４ー２＞
※ 「関数名のリスト」が表示されない場合は、①「Excelのオプション」→ ②「数式」→ ③「数式オートコンプリート」のチェックマークをオン（チェックマークあり）にした後、④「Excelのオプション」右下にある［OK］ボタンをクリックしてください。
＜図４ー３＞
指定した文字列に置換する
Q２. SUBSTITUTE 関数を使うと、セル内の文字列は、どのように変わるのですか？
検索範囲、検索文字列、置換文字列を指定する
A２. ＜図４ー２＞のSUBSTITUTE( に続いて、① 検索範囲として「B2」セル をクリックした後、② ,（カンマ）を入力、③ 検索文字列（置換される前の文字列）として、ここでは、"英語"と入力した後、④ ,（カンマ）を入力、⑤ 置換文字列（置換した後の文字列）として、ここでは「空白の文字列」を表す""と入力した後、⑥ 半角の ) で数式を閉じます。＜図５ー１＞（記号は、すべて半角英数モードで入力します）
＜図５ー１＞
⑦［Enter］（エンター）キーを押して、数式を確定し、⑧「C2」セル を見ると、（「B2」セル の英語を空白の文字列に置換した）英語のない文字列に変わっています。
＜図５ー２＞
※ 空白の文字列とは、空白（文字列がない状態）のことで、スペース とは違います。セル内から文字列がなくなり、文字数としてもカウントされません。
SUBSTITUTE 関数の使用例
SUBSTITUTE 関数は、例えば、「英語という表記（文字列）を検索し、すべて外国語という表記（文字列）に置換する」場合などによく使われます。膨大なデータがあるときは、とても便利です。
＜図５ー３＞
実際の文字数
Q３. 関数の使用によって、実際に文字数がどのように求められるのか教えてください。
英語にしぼって文字数を調べる
A３. 範囲を「B2」セル、教科を英語にしぼって、文字数を調べてみます。
① 回答欄の「B2」セル の文字数は、11文字です。（LEN 関数を使用）
＜図６ー１＞
※ ①「英語、数学、国語、英語」で、11文字となります。
② SUBSTITUTE 関数を使用（英語を空白の文字列に置換）して、「B2」セル の英語を除いた文字列の文字数は、７文字です。（LEN 関数と LEN 関数のネスト（入れ子）として、SUBSTITUTE 関数を使用）
＜図６ー２＞
※ ②「、数学、国語、」で、７文字となります。
③ （まわりくどいようですが）①から②を引くと、除かれた英語の文字数が４文字だとわかります。
＜図６ー３＞
※ ①「英語、数学、国語、英語」（11文字）ー ②「、数学、国語、」（７文字）＝ ③「英語英語」（４文字）となります。
④ ③の文字数の４文字、つまり、「英語英語」（４文字）を「英語」（２文字）でわると、英語の回答数は２となります。
＜図６ー４＞
⑤ ③の「文字数を求める」数式の対象範囲を、「B2」セル ～「B6」セル に拡大し、それを SUM 関数を使って合計したのが、下記の数式です。（SUM 関数を使用・・・＜図３ー１＞参照、D2は国語の集計の場合）
=SUM(LEN($B$2:$B$6)-LEN(SUBSTITUTE($B$2:$B$6,D2,"")))/LEN(D2)
=SUM(LEN(検索する範囲)-LEN(SUBSTITUTE(検索する範囲,検索する文字列,置換する文字列)))/LEN(検索する文字列)
範囲指定の方法
Q４. $B$2:$B$6 は、どのように入力するのですか。
セルをドラッグ後、［Ｆ４］キーを１回押す
A４. キーボードから直接入力してもかまいませんが、ここでは、①「B2」セル から「B6」セル までをドラッグ（マウス左ボタンを押したまま移動）して範囲指定した後（数式バーには、B2:B6と表示される）、②［Ｆ４］キーを１回押して、③ $B$2:$B$6 に変えています。
＜図７＞
※ このように、セルを「絶対参照」にすると、オートフィル機能を使ったとき、セル範囲が移動しません。
わり算の取り扱い
Q５. 次のようなアンケートの回答のときは、最後のわり算をしなくても集計できたのですが・・・。
＜図８ー１＞
検索する文字列が１文字のとき
A５. SUBSTITUTE 関数 で、検索する文字列（空白の文字列に置換される文字列）が１文字の場合は、最後のわり算は不要になります。
＜図８ー２＞
※ この場合は、除かれた文字の文字数がそのまま回答数（例えば、除かれた文字が国国国の３文字であれば、すでに、国語の回答数は３とわかる）なので、国国国（３文字）を、最後に国（１文字）でわる、つまり１でわる必要はありません。
今回は、「表計算ソフト」エクセル（Excel） で、「LEN 関数 と SUBSTITUTE 関数を組み合わせて、セル内に同じ回答が複数あるアンケートを集約する方法」について紹介しました。
※ COUNTIF 関数を使って、複数回答のアンケートを集約する方法については、下記リンクをどうぞ。
