Excel(エクセル)基本講座：文字列の関数(2)

• エクセルで文字列のデータを操作する時に使用するワークシート関数です。
• 文字列の置換や繰り返し(SUBSTITUTE関数,REPLACE関数,REPT関数)、文字列の結合(&,CONCATENATE関数)、
  空白や制御コードの削除(TRIM関数,CLEAN関数)、文字コードに関する関数(CHAR関数,CODE関数)
  について説明しています。

文字列の置換(SUBSTITUTE)	文字列の比較(EXACT)	文字列を結合する(＆,CONCATENATE)
文字列の置換(REPLACE)	余分な空白の削除(TRIM)	コードから文字を返す(CHAR)
数値を文字列へ変換(TEXT)	制御コードの削除(CLEAN)	文字コードを返す(CODE)
文字を繰り返し表示(REPT)	通貨スタイルの文字列(YEN、DOLLAR)

文字列から、印刷できない文字を削除します　　　topへ
　クリーン
=CLEAN(文字列）
　　　他のアプリケーションからデータを取り込んだ時など、
　　　データ ファイルの先頭や末尾に含まれている印刷できない制御コードを削除することができます。

• B1セルには「鹿児島県」と「揖宿郡」が同一セル内で改行されています。
  • ="鹿児島県"&CHAR(10)&"揖宿郡"　とします。
    または、
    「鹿児島県」 キーボードの【Alt】+【Enter】 「揖宿郡」と入力します。
    (セルの表示形式の配置タブで「折り返して全体を表示する」にチェックを入れます。）
• =CREAN(B1) で改行コードを取り除くことができます。
  

文字列から余分なスペースを取り除きます　　　topへ
　トリム
=TRIM(文字列)
　　　先頭の空白はすべて削除されます。文字間の空白は1個残します。

【問題】B2セルの文字列から余分な空白を取り除き、C2セルに表示しなさい。

• 	B	C
  2	鹿児島県　　　揖宿郡　　　　　開聞町	鹿児島県　揖宿郡　開聞町

【解答例】

• TRIM関数を使います。
  

  	B	C
  2	鹿児島県　　　揖宿郡　　　　　開聞町	=TRIM(B2)

• なお、文字間の空白も取り除きたい場合は、SUBSTITUTE関数を使います。
  

  	B	C
  2	鹿児島県　　　揖宿郡　　　　　開聞町	=SUBSTITUTE(B2," ","")
  3		鹿児島県揖宿郡開聞町

  • 空白が半角スペースと全角スペースとが混在している場合には、
    =SUBSTITUTE(SUBSTITUTE(B2,"　","")," ","")
    「=SUBSTITUTE(SUBSTITUTE(B2,"全角スペース",""),"半角スペース","")」
    と、まず全角スペースを除き、更に半角スペースを除くように式を組み立てます。

文字列を比較します　　　topへ
　イグザクト
=EXACT(文字列1,文字列2)

• B列とC列の文字列が同一かどうかを調べます。
• D2セルに　=EXACT(B2,C2)　と入力して、下方向へコピーします。
• 同じ文字列であれば「TRUE」、異なっていれば「FALSE」を返します。

  	B	C	D	E
  2	鹿児島	鹿児島	TRUE	=EXACT(B2,C2)
  3	ABC	ＡＢＣ	FALSE	=EXACT(B3,C3)
  4	ＡＢＣ	ＡＢＣ	TRUE	=EXACT(B4,C4)
  5	abc	ABC	FALSE	=EXACT(B5,C5)

  　　　　　　
• (参考) IF関数と組み合わせた例です。

  	B	C	D	E
  2	鹿児島	鹿児島	同じ	=IF(EXACT(B2,C2),"同じ","違う")
  3	ABC	ＡＢＣ	違う	=IF(EXACT(B3,C3),"同じ","違う")
  4	ＡＢＣ	ＡＢＣ	同じ	=IF(EXACT(B4,C4),"同じ","違う")
  5	abc	ABC	違う	=IF(EXACT(B5,C5),"同じ","違う")

文字列を結合します　　　topへ
　アンド
　＆　(演算子です）
　コンカティネイト
=CONCATENATE(文字列1，文字列2・・・，文字列30）

【問題】

• B2〜C2セルに入力されている文字を結合しなさい。
• F2セルには　「＆」　F3セルにはCONCATENATE関数を使いなさい。

• 	B	C	D	E	F
  2	鹿児島県	揖宿郡	開聞町	＆　を使って→	鹿児島県揖宿郡開聞町
  3				CONCATENATE　を使って→	鹿児島県揖宿郡開聞町

【解答例】

• 	B	C	D	E	F
  2	鹿児島県	揖宿郡	開聞町	＆　を使って→	=B2&C2&D2
  3				CONCATENATE　を使って→	=CONCATENATE(B2,C2,D2)

文字列中から検索文字列を探し、置換文字列に置き換えます　　　topへ
　サブスティチュート
=SUBSTITUTE(文字列，検索文字列，置換文字列，置換対象）
　　　文字列中の特定の位置にある文字をほかの文字に置き換えるときは、REPLACE 関数を使用します。
　　　置換対象：置換対象を指定した場合、文字列中の置換対象番目の検索文字列だけが置き換えられます。
　　　　　　　　省略した場合は、文字列中のすべての検索文字列が置き換えの対象となります。

【問題】

• 西鹿児島駅はH16年九州新幹線の部分開業に伴い、鹿児島中央駅に改称されました。
• 下の表の西鹿児島駅を鹿児島中央駅に変更しなさい。

• 	B	C	D	E	F
  2	出発地	到着地		出発地	到着地
  3	鹿児島駅	西鹿児島駅		鹿児島駅	鹿児島中央駅
  4	西鹿児島駅	指宿駅		鹿児島中央駅	指宿駅
  5	指宿駅	西鹿児島駅		指宿駅	鹿児島中央駅

【解答例】

• SUBSTITUTE関数を使って、文字列を置換します。

• 	B	C	D	E	F
  2	出発地	到着地		出発地	到着地
  3	鹿児島駅	西鹿児島駅		=SUBSTITUTE(B3,"西鹿児島駅","鹿児島中央駅")	=SUBSTITUTE(C3,"西鹿児島駅","鹿児島中央駅")
  4	西鹿児島駅	指宿駅		=SUBSTITUTE(B4,"西鹿児島駅","鹿児島中央駅")	=SUBSTITUTE(C4,"西鹿児島駅","鹿児島中央駅")
  5	指宿駅	西鹿児島駅		=SUBSTITUTE(B5,"西鹿児島駅","鹿児島中央駅")	=SUBSTITUTE(C5,"西鹿児島駅","鹿児島中央駅")

• 「西鹿児島」を「鹿児島中央」に置き換えても同じ結果が得られます。
• (注)　同一セルで文字列を置き換えたい場合には、【編集】→【置換】を使います。

【問題2】

• B2:B4に文字列が入力されています。
• それぞれの文字列中に「b」が何文字含まれているか数えてC列に表示しなさい。

• 	B	C
  2	abcdeB	1
  3	abbBde	2
  4	acbBbb	3

【解答例】

• 対象文字を空白に置き換えて(取り除いて)、文字列の長さを比較すれば、対象文字の数を求めることができます。
• また、検索文字列の大文字と小文字は区別されます。
  

  	B	C
  2	abcdeB	=LEN(B2)-LEN(SUBSTITUTE(B2,"b",""))
  3	abbBde	=LEN(B3)-LEN(SUBSTITUTE(B3,"b",""))
  4	acbBbb	=LEN(B4)-LEN(SUBSTITUTE(B4,"b",""))

  =LEN(B2)-LEN(SUBSTITUTE(B2,"b",))　と「""」を省略することもできます。

文字列中の指定された文字数の文字を別の文字に置き換えます　　　topへ
　リプレース
=REPLACE(文字列,開始位置,文字数,置換文字列)

• 置き換える文字の位置が決まっている場合に使用できます。

【問題】B3:B5セルにはよけいな数字が紛れ込んでいます。REPLACE関数を使って取り除きなさい。

• 	B	C
  2	置換え前	置換え後
  3	1鹿児島駅	鹿児島駅
  4	12西鹿児島駅	西鹿児島駅
  5	指23宿駅	指宿駅

【解答例】

• 	B	C
  2	置換え前	置換え後
  3	1鹿児島駅	=REPLACE(B3,1,1,"")
  4	12西鹿児島駅	=REPLACE(B4,1,2,"")
  5	指23宿駅	=REPLACE(B5,2,2,"")

• =REPLACE(B3,1,1,) と「""」は省略することもできます。

指定した数値をASCIIまたはJISコードの番号と見なし、それに対応する文字を返します　　topへ
　キャラクター
=CHAR(数値）

• VBAでは使うことがありますが、通常の数式で使うことはほとんど無いと思います。
• CODEと逆の関係になります。

【例】セル内改行の例です。

• セル内改行のAlt+EnterはCHAR(10)になりますので、C2セルに　=B2&CHAR(10)&B3　と入力します。
• セルの表示形式で、「配置」の「折り返して全体を表示する」にチェックを入れます。

• 	B	C
  2	東京都	東京都 新宿区
  3	新宿区

【参考】

• セル内改行のAlt+EnterはCHAR(10)になります。
  

  		(参考：VBAの定数)
  セル内改行（LF・ラインフィード)	=CHAR(10)	vbLf
  Space(半角空白)	=CHAR(32)
  改行（CR・キャリッジリターン)	=CHAR(13)	vbCr
  キャリッジ リターンとライン フィードの組み合わせ	=CHAR(10)+CHAR(13)	vbCrLf
  Tab	=CHAR(9)	vbTab
  Back Space	=CHAR(8)	vbBack

文字列の先頭文字に対応する ASCIIまたはJISコードを返します　　　topへ
　コード
=CODE(文字列）

• CHARと逆の関係になります。

【例】B列に入力された文字種を判別する例です。

• C2セルに　=IF(AND(CODE(B4)>=48,CODE(B4)<=57),"数字です",
• C3セルに　=IF(AND(CODE(B4)>=65,CODE(B4)<=90),"大文字の英字です",
• C4セルに　=IF(AND(CODE(B4)>=97,CODE(B4)<=122),"小文字の英字です")))

• 	B	C
  2	1	数字です
  3	a	小文字の英字です
  4	A	大文字の英字です

数値を書式設定した文字列に変換します　　　topへ
　テキスト
=TEXT(値,表示形式)

• 数値は書式設定された文字列に変換され、その計算結果は数値として計算に利用できなくなります。
• 一方、書式→セルの表示形式の場合は表示が変わるだけで、文字列には変換されません。計算に利用できます。
• 【使用例】
  • 日付のシリアル値から曜日を取り出す例。時刻のシリアル値から分数を取り出す例。

    • 	B	C	D
      2	2005/7/1	今日は金曜日です	=TEXT(B2,"今日はaaaaです;;")
      3	1:15	75分経過しました	=TEXT(B3,"[m]分経過しました")

  • 数値の四捨五入にTEXT関数を使う例
    • 表示形式で「0」とすることで、四捨五入された整数値になります。
      TEXT関数では文字列になるので、「*1」で数値にします。
      （C列は四捨五入されていることを示すために表示桁数を増やして表示しています)
      
    • 指数の表示形式で三桁を表示するようにします。
      四桁目が四捨五入された値が得られます。
      
  • 時間の計算例
    • A1に「9:00」と入力し、A63までコピーしています。
      B1に「17:00」、B2に「17:01」と入力し、B1:B2を選択後、フィルハンドルをダブルクリックして、連続値を入力します。
      C1は「=FLOOR(B1-A1,"0:30")」　D1は「=FLOOR(TEXT(B1-A1,"h:mm")*1,"0:30")」と入力して、C63:D63までコピーします。
      31行目と、61行目で「B1-A1」の計算に違いが生じているのがわかります。
      
    • フィル機能で連続値を入力した際にB列の値「17:30」と「18:00」は、手入力した値と比べると異なっているのが判ります。
      このような微小な値を、TEXT関数を使って表示形式（h:mm）で丸める(四捨五入する)ことができます。
      

文字列を指定回数繰り返し表示します　　　topへ
　リピート
=REPT(文字列,繰り返し回数)

• 指定した文字列を指定回数繰り返し表示します。
• 繰り返し回数：0を指定すると「""」空白が返されます。小数値は切り捨てて整数値と見なされます。
• 表示される文字列は32,767 文字まで。これを超える場合は、エラー値 #VALUE! が返されます。

• 	B	C	D
  2	1230	*1230	=REPT("*",5-LEN(B2))&B2
  3	230	**230	=REPT("*",5-LEN(B3))&B3

通貨スタイルの文字列を表示します　　　topへ
　エン
=YEN(数値,桁数)
　ダラー
=DOLLAR(数値,桁数)
　　桁数で四捨五入された値を返します。

• 通貨書式 ￥または $ を設定した文字列に変換します。
• 桁数を省略すると、YEN関数の場合は「0 」を、DOLLAR関数の場合は「2」を指定したと見なされます。
• 文字列で返された値は、計算の際に、Excelが自動的に数値に変換するため数式などで使用できます。

• 	B	C	D	E	F	G	H
  2	1234	\1,234	=YEN(B2)		12.34	$12.34	=DOLLAR(F2)
  3		\1,230	=YEN(B2,-1)			$12.3	=DOLLAR(F2,1)
  4		\1,200	=YEN(B2,-2)			$12	=DOLLAR(F2,0)
  5						$10	=DOLLAR(F2,-1)

PageViewCounter

Since2006/2/27