わえなび ワード&エクセル問題集 waenavi

当サイトはWordとExcelの実力を鍛える最高水準の特訓問題集です。リンクを張るのは自由です。みんなで勉強しましょう!

【Excel】LEN関数は文字数、FIND関数は左から何文字目にあるかを数える関数である


LEN関数は文字数を数える関数なので、一定の文字数を超えていないか、スペースなどの余分な文字が無いかなど、文字列のチェックに使われることがあります。また、FIND関数は特定の文字(文字列)を探してその位置を求める関数です。

LEN関数、FIND関数は単独で使われることもありますが、IF、IFERROR、LEFT、RIGHT、MIDなどの他の関数と入れ子にして使用することが多いです。文字列操作関数の中で重要な関数であることは言うまでもありません。

そこで、今回は、LEN関数、FIND関数の仕様と注意点、IFERROR関数と併用する例について出題します。

f:id:waenavi:20191026191405j:plain

 

 

目次

1.LEN関数の使い方

問題

A列の役職には必ず「部」という文字が含まれている。また、B列のアカウントはアルファベット+数字4文字である。A列の文字数を求めなさい。また、B列の文字列のうちアルファベットの部分の文字数を求めなさい。

f:id:waenavi:20191026184338j:plain

 

解説

文字数を数える関数は、LEN関数です。LENGTH=レングス(長さ)のことです。LEN関数は引数が1つなので簡単です。

  • LEN(文字列)

「=LEN(a2)」と入力します。

f:id:waenavi:20191026184438j:plain

 

LEN関数挿入の画面を用いても構いません。

f:id:waenavi:20191026184539j:plain

 

これで文字数が求められます。LEN関数の計算結果は「数値」です(文字列ではない)。全角も半角も1文字としてカウントします。

f:id:waenavi:20191026184619j:plain

 

B列の文字列のうち右側の4文字が数字であることが分かっています。したがって、アルファベットの部分は全体の文字数から4を引けばよいです。

f:id:waenavi:20191026184944j:plain

 

「=LEN(B2)-4」と入力します。

f:id:waenavi:20191026184751j:plain

 

2.FIND関数の使い方

(1)左から何文字目か

問題

A列の文字列のうち「部」は左から何文字目にあるかを求めなさい。また、「課」は左から何文字目にあるかを求めなさい。

f:id:waenavi:20191026185130j:plain

 

解説

特定の文字列を探して、左から何文字目にあるかを求める関数は、FIND関数(ファインド)です。検索文字列と文字列を指定します。開始位置は検索を開始する場所を指定するものですが、文字列の先頭から検索する場合は省略してもかまいません。

  • FIND(検索文字列,文字列,[開始位置])

「=FIND("部",A2)」と入力します。引数の順番は、探す文字列が先で、対象となる文字列が後です。

f:id:waenavi:20191026185200j:plain

 

FIND関数挿入の画面を用いても構いません。

f:id:waenavi:20191026185410j:plain

 

FIND関数の計算結果は「数値」です(文字列ではない)。

f:id:waenavi:20191026185435j:plain

 

「5」というのは、先頭の文字を1とした時に左から5文字目にあるという意味です。

f:id:waenavi:20191026185814j:plain

 

「=FIND("課",A2)」と入力します。「課」という文字が無いためエラーになります。

f:id:waenavi:20191026190009j:plain

 

「課」を含む場合はその位置が整数値で求められ、含まない場合はエラーになります。

f:id:waenavi:20191026190030j:plain

 

(2)複数文字の場合

問題

「開発」という文字が含まれる場合は左から何文字目かを求め、含まれない場合は0にしなさい。

f:id:waenavi:20191026190142j:plain

 

解説

「=FIND("開発",A2)」と入力します。「開発」という文字が無いためエラーになります。

f:id:waenavi:20191026190438j:plain

 

「開発」という文字列が含まれるものだけ答えが求められます。

f:id:waenavi:20191026190511j:plain

 

複数文字列でも検索は可能です。この場合、先頭の文字の位置を返します。「開」が3文字目で「発」が4文字目であれば、FIND=3となります。

f:id:waenavi:20191026190732j:plain

 

また、「技術開発部開発企画課長」のように、「開発」が2つ以上ある場合は最初に見つかったほうを返します。

f:id:waenavi:20191026190916j:plain

 

エラーになっているものを別の値するにはIFERROR関数を使います。

  • =IFERROR(数式,エラーの場合の値)

IFERROR関数で囲み、第2引数を0にします。これでエラーになっているものは強制的に0になります。

  • =IFERROR(FIND("開発",A2),0)

f:id:waenavi:20191026191259j:plain

 

3.都道府県名の文字数

問題

A列に都道府県庁所在地を入力した。「県」という文字が含まれていれば左から何文字目にあるかを求め、含まれていなければ3にしなさい。また、都市名の文字数を求めなさい。

f:id:waenavi:20191026191405j:plain

 

解説

「県」という文字を探します。「=FIND("県",A2)」と入力します。

f:id:waenavi:20191026191525j:plain

 

北海道、東京都、大阪府、京都府はエラーになりますがこれらはすべて3文字です。IFERROR関数で強制的に3にします。これで都道府県の文字数になります。

  • =IFERROR(FIND("県",A2),3)

f:id:waenavi:20191026191559j:plain

 

都市名は全体の文字数から都道府県名の文字数を引けばよいです。

  • =LEN(A2)-B2

f:id:waenavi:20191026191642j:plain

 

4.LEN、FINDと空白の関係

(1)LENはすべての文字をカウントする

問題

空白のセルの場合、LEN関数の計算結果はいくつか。また、スペースやセル内改行を入力した場合はどうか。 

解説

「=len(B1)」と入力します。

f:id:waenavi:20191026192039j:plain

 

0になります。LEN関数で空白で何も入力されていないセルを指定すると0になります。

f:id:waenavi:20191026192042j:plain

 

セルB1に半角スペースを3個入れてみます。

f:id:waenavi:20191026192111j:plain

 

3になりました。全角スペース、半角スペースは1文字とカウントされます。スペースが入力されていても見た目では分かりませんが、LEN関数を使うことによって空白スペースが入力されているかを見分けることができます。

f:id:waenavi:20191026192203j:plain

 

セル内改行を入力します。Altキー+Enterキーです。

f:id:waenavi:20191026192304j:plain

 

4になります。セル内改行はラインフィード(LF)と呼ばれる制御文字の一種であり、1文字としてカウントされます。

f:id:waenavi:20191026192328j:plain

 

(2)空白を探す

問題

空白セルを対象としてFIND関数を用いた場合、計算結果はいくつか。また、検索文字列を空白文字列(長さ0の文字列)とした場合はどうか。 

解説

「=FIND("a",B1)」と入力します。見つからないのでエラーになります。

f:id:waenavi:20191026193002j:plain

 

「=FIND("",B1)」と入力します。今度は1になります。

f:id:waenavi:20191026193050j:plain

 

セルB1に何らかの文字列を入力した場合も同じです。1になります。検索文字列(第1引数)を空白文字列とした場合、FIND関数の値は常に1になります。

f:id:waenavi:20191026193148j:plain

 

5.FIND関数の開始位置(第3引数)の仕様

(1)2番目、3番目を求める

問題

A列の文字列は15字以内で、ドットが3つ含まれることが分かっている。2つ目、3つ目のドットの位置をFIND関数を使って求めなさい。

f:id:waenavi:20191026193407j:plain

 

解説

「=FIND(".",A1)」と入力します。すべて4になります。FIND関数は第3引数(開始位置)を指定しなければ最初に見つかった位置を返します。2つ目以降は無視されます。

f:id:waenavi:20191026193525j:plain

 

2つ目を見つけるためには、開始位置を5とします。「=FIND(".",A1,5)」と入力します。

f:id:waenavi:20191026193702j:plain

 

開始位置を5とすると、1~4文字目を無視して5文字目以降だけを探します。したがって8となります。

f:id:waenavi:20191026194447j:plain

 

開始位置の5というのは、はじめの「FIND(".",A1)」に1を加えたものです。

f:id:waenavi:20191026194418j:plain

 

したがって、「=FIND(".",A1,FIND(".",A1)+1)」とすれば、2番目のドットを探す式になります。

f:id:waenavi:20191026194519j:plain

 

同様に、式の初めに「FIND(".",A1,」、最後に「+1)」をつければ、3番目のドットを探す式になります。

  • 1番目:=FIND(".",A1)
  • 2番目:=FIND(".",A1,FIND(".",A1)+1)
  • 3番目:=FIND(".",A1,FIND(".",A1,FIND(".",A1)+1)+1)

f:id:waenavi:20191026194614j:plain

 

*補足*3番目を見つけるにはSUBSTITUTE関数を使う方法もあります。

  • =FIND("★",SUBSTITUTE(A1,".","★",3))

 

(2)開始位置が小数の場合、元の文字数より大きい場合

問題

FIND関数の開始位置(第3引数)を「4.5」にするとどうなるか。また、16以上にするとどうなるか。

f:id:waenavi:20191026193407j:plain

 

解説

「=FIND(".",A1,4.5)」と入力します。すべて4になります。FIND関数の第3引数である開始位置は通常、整数値を指定しますが、小数にした場合は自動的に小数点以下切り捨ての整数値となります。開始位置「4.5」は、「4」を指定したのと同じです。

f:id:waenavi:20191026194858j:plain

 

A列の文字列は15字以内です。「=FIND(".",A1,16)」と入力します。開始位置を16にするとエラーになります。

f:id:waenavi:20191026194931j:plain

 

しかし、MID関数の場合はエラーになりません。例えば「=MID(A1,16,1)」とすると空白になるだけです(参考:【Excel】LEFT関数、RIGHT関数、MID関数で文字列を抽出する方法の基本例題)。

f:id:waenavi:20191026195026j:plain

 

MID関数の開始位置(第2引数)が元の文字数より大きい場合は空白となり、FIND関数の開始位置(第3引数)が元の文字数より大きい場合はエラーとなることに注意します。

f:id:waenavi:20191026195529j:plain

 

(3)検索文字列の長さが0の場合

問題

FIND関数の検索文字列の長さが0の場合、開始位置を指定するとどうなるか。

f:id:waenavi:20191026193407j:plain

 

解説

FIND関数で検索文字を空白文字列(長さが0)の場合は、エラーにはならず、常に1となります。

f:id:waenavi:20191026195802j:plain

 

これは、第3引数で1を指定した場合も同じです。

f:id:waenavi:20191026195833j:plain

 

開始位置を2以上にすると開始位置となります。ただし、開始位置が元の文字数を超えるとエラーになります。

f:id:waenavi:20191026200055j:plain

 

6.文字数を利用して文字列を取り出す

LEN、FINDは、LEFT、RIGHT、MIDと組み合わせることによって、文字列の一部を抽出することができます。

 


解説は以上です。


 

 


Copyright(C)2018-2021 waenavi, All rights reserved. [www.waenavi.com 定礎 平成三十年八月]
Youtube | 公式サイト | twitter | 著作権 | 運営者情報 | お問い合わせ