SUBSTITUTE関数・TRIM関数の使い方としくみ｜草野球成績管理表で入力ゆれを補正する例で解説

フォーム・シート作成

スプレッドシートで地味に効いてくる関数のひとつ、SUBSTITUTE関数とTRIM関数について、使い方と内部のしくみを解説します。草野球の成績管理表で対戦相手名などの入力ゆれ(全角/半角、余分なスペース)を補正する場面を例に、初心者向けに丁寧に説明します。

SUBSTITUTE関数・TRIM関数とは

📝 SUBSTITUTE・TRIMの役割(一言でいうと)

指定した文字列の中から特定の文字列を探して別の文字列に置き換えるのがSUBSTITUTE関数、文字列の前後にある余分なスペースを取り除くのがTRIM関数です。成績管理表では、対戦相手名やメモ欄を人が手入力する場面で、全角スペースが混じったり、コピー&ペーストの際に余分なスペースが入り込んだりすることがよくあります。この2つの関数を組み合わせることで、「見た目はほぼ同じでも中身が微妙に違う」データを揃えることができます。

基本の書式はこうです。

=SUBSTITUTE(文字列, 検索文字列, 置換文字列, [対象])
=TRIM(文字列)

SUBSTITUTEの「対象」は省略可能で、省略すると文字列内に複数ある一致箇所がすべて置き換えられます。

成績管理表での実例:入力ゆれを補正する

大阪グーニーズの個人成績シートでは、対戦相手名を試合ごとに手入力しています。入力する人によってスペースの入れ方にクセがあり、C列(対戦相手)に次のようなゆれが起きることがあります。

試合日 対戦相手(入力データ) 入力ゆれの内容
4/6 　○○ファイターズ 先頭に全角スペースが入っている
4/13 △△ベアーズ 末尾に半角スペースが2つ入っている
4/20 □□イーグルス 前後に半角スペースが入っている

このC列を、全角スペースは半角に統一したうえで前後の余分なスペースも取り除いて揃えたいとき、式はこうなります。

✅ 対戦相手名の入力ゆれを補正する式
=TRIM(SUBSTITUTE(C2,"　"," "))

内側のSUBSTITUTEで全角スペース「　」を半角スペース「 」に置き換え、外側のTRIMで前後の余分なスペースと文字間の連続スペースを整理しています。この式を1つ用意しておけば、C列がどんな入力のされ方をしていても同じ見た目の文字列に揃います。

STEP 1
補正結果を表示する列を用意する

対戦相手名が入っているC列とは別に、補正後の文字列を表示するための列(例:D列)を用意します。

STEP 2
「=TRIM(SUBSTITUTE(」と入力し、対象セルと置換パターンを指定する

対象セル(C2)、検索文字列(全角スペース「　」)、置換文字列(半角スペース「 」)の順に指定します。全角スペースは変換候補に出てこないことが多いので、コピー&ペーストで入力すると確実です。

STEP 3
「)」を2つ重ねて閉じ、Enterキーで確定してから全行にコピーする

SUBSTITUTEとTRIMの2つの関数を閉じる必要があるため、括弧の数に注意してください。確定後は他の試合行にも同じ式をコピーします。

しくみを理解する:SUBSTITUTEとTRIMは何をしているのか

📝 SUBSTITUTE・TRIMの内部動作

2つの関数は、それぞれ違う仕組みで文字列を整えています。

  • SUBSTITUTEは、指定した検索文字列と完全に一致する部分だけを文字列の中から探し出し、そこだけを置換文字列に差し替える。文字列全体を書き換えるのではなく、あくまで「一致した箇所だけ」を狙い撃ちする点がポイント
  • SUBSTITUTEの第4引数(対象)を省略すると、文字列内に複数の一致箇所があってもすべて置換される。逆に対象を数値で指定すると、その順番の一致箇所だけが置換される
  • TRIMは、文字列の先頭と末尾にある半角スペースを取り除き、さらに単語と単語の間に連続した半角スペースがあれば1つだけ残して残りを削除する
  • ただしTRIMが対象にするのは半角スペースのみで、全角スペースはTRIM単体では除去されない

よくあるミス:補正が効かない原因

⚠️ 原因1:TRIMだけでは全角スペースが消えない

「TRIMをかけたのに空白が残っている」というときは、そのスペースが全角である可能性が高いです。見た目には空白がないように見えても、セルの中身としては別の文字列として扱われるため、VLOOKUPなどで一致しない原因になります。全角スペースが混じる可能性がある列には、TRIM単体ではなくSUBSTITUTEとの組み合わせを基本にしておくと安心です。

⚠️ 原因2:SUBSTITUTEの「対象」を指定してしまっている

第4引数(対象)に数値を入れると、その順番の一致箇所しか置換されません。「1箇所しか直らない」というときは、この引数を誤って指定していないか確認してください。全体を揃えたい場合は、対象の引数自体を省略するのが基本です。

✅ この記事のまとめ
  • SUBSTITUTE関数は「指定した文字列だけを検索して置き換える」関数
  • TRIM関数は「前後の余分な半角スペースを除去し、連続スペースを1つにまとめる」関数
  • 全角スペースはTRIM単体では消えないため、SUBSTITUTEと組み合わせるのが定番の対処法

次回予告

次回のテーマは「TODAY関数・NOW関数」です。実行した時点の日時を返す仕組みと、再計算のタイミングについて、試合日からの経過日数を自動計算する例で解説していきます。

▶︎ 次回：TODAY関数・NOW関数の使い方としくみ

コメント

タイトルとURLをコピーしました