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

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

【Excel関数】LEFT・RIGHT・MIDとIF・VLOOKUPを組み合わせた応用事例と注意点


ExcelでLEFT関数、RIGHT関数、MID関数を使うときには単純に文字列の一部を抽出することもありますが、取り出した文字列を条件としてIF関数で判定したり、検索値としてVLOOKUP関数と組み合わせて使うこともあります。

また、IF関数で取り出す文字数を変えたり、開始位置を変えたりすることもあります。さまざまな入れ子のパターンがありますので、応用問題を解くことによって慣れたほうが良いです。

そこで、今回は、LEFT・RIGHT・MIDをIF・VLOOKUPの中で使う方法などの応用事例と注意点について出題します。 

 

目次

0.LEFT、RIGHT、MID関数の基本

LEFT関数、RIGHT関数、MID関数の基本的な使い方と練習問題はこちらの記事をご覧ください。

 

1.IF関数の条件式で使う

問題

A列のうち部長に〇印をつけなさい。また、次長課長に〇印をつけなさい。

f:id:waenavi:20191024224527j:plain

 

解説

A列の右側の2文字は「RIGHT(A2,2)」です。これが「部長」であれば丸印をつけるので、IF関数の条件式を「RIGHT(A2,2)="部長"」とします。

  • =IF(RIGHT(A2,2)="部長","〇","")

f:id:waenavi:20191024224855j:plain

 

部長だけ丸印が付きます。

f:id:waenavi:20191024224923j:plain

 

次長課長も同じようにするのであれば、複合参照のほうが早いです。

  • =IF(RIGHT($A2,2)=B$1,"〇","")

f:id:waenavi:20191024225041j:plain

 

2.先頭の文字を削除する

問題

A列のうち課長について、最初の3文字を削除しなさい。

f:id:waenavi:20191024225329j:plain

 

解説

最初の3文字を削除するということは、4文字目以降を取得するということです。つまり「MID(A1,4,10)」です。文字数は多めにしておけばよいです。

f:id:waenavi:20191024225435j:plain

 

IF関数を用いて課長とそれ以外で処理を分けます。

  • =IF(RIGHT(A1,2)="課長",MID(A1,4,10),A1)

f:id:waenavi:20191024225631j:plain

 

別解

課長は4文字目以降を取得しますが、それ以外は1文字目から取得すると考えることもできます。MID関数の開始位置を4にするか1にするかということになります。IF関数では「IF(RIGHT(A1,2)="課長",4,1)」となります。

f:id:waenavi:20191024225945j:plain

 

これをMID関数の中に入れます。

  • =MID(A1,IF(RIGHT(A1,2)="課長",4,1),10)

f:id:waenavi:20191024230035j:plain

 

3.都道府県名の取得

問題

A列に都道府県庁所在地を入力した。都道府県名と都市名に分けなさい。

f:id:waenavi:20191024230216j:plain

 

解説

先頭に都道府県名が付いている住所の中から、都道府県名だけを取り出すには、LEFT関数を使います。47都道府県のうち44の都道府県は3文字ですが、神奈川県、和歌山県、鹿児島県は4文字です。住所の4文字目が「県」であれば4文字の県、それ以外は3文字の県と考えます。それぞれMID関数とLEFT関数で表すことができます。

  • 4文字目が「県」・・・MID(A2,4,1)="県"
  • 4文字の県名・・・LEFT(A2,4)
  • 3文字の県名・・・LEFT(A2,3)

IF関数を用いて都道府県名を取得することができます。

  • =IF(MID(A2,4,1)="県",LEFT(A2,4),LEFT(A2,3))

f:id:waenavi:20191024230405j:plain

 

住所の途中から抜き出すにはMID関数を使います。この場合も住所の4文字目が「県」であれば5文字目以降、それ以外は4文字目以降と考えます。それぞれMID関数で表すことができます。

  • 4文字目が「県」・・・MID(A2,4,1)="県"
  • 5文字目以降・・・MID(A2,5,10)
  • 4文字目以降・・・MID(A2,4,10)

IF関数を用いて都道府県名を取得することができます。

  • =IF(MID(A2,4,1)="県",MID(A2,5,10),MID(A2,4,10))

f:id:waenavi:20191024231004j:plain

 

別解

都道府県名は取り出す文字数が異なるだけです。住所の4文字目が「県」であれば4文字、それ以外は3文字です。

  • IF(MID(A2,4,1)="県",4,3)

f:id:waenavi:20191024231121j:plain

 

したがって、LEFT関数の中にこれを入れるという方法もあります。

  • =LEFT(A2,IF(MID(A2,4,1)="県",4,3))

f:id:waenavi:20191024231205j:plain

 

また、都市名についても開始位置が異なるだけです。住所の4文字目が「県」であれば5文字目、それ以外は4文字目です。

  • IF(MID(A2,4,1)="県",5,4)

したがって、これをMID関数の第2引数にするという方法もあります。

  • =MID(A2,IF(MID(A2,4,1)="県",5,4),10)

f:id:waenavi:20191024231305j:plain

 

4.数値を取り出したときの注意点

問題

A列の5文字目が「2」のとき先頭の4文字を取り出し、それ以外は5文字を取りだしなさい。

f:id:waenavi:20191024231957j:plain

 

誤答例

A列の5文字目はMID関数を用いて、「MID(A1,5,1)」です。

f:id:waenavi:20191024232809j:plain

 

取り出す文字数が異なるだけなので、LEFT関数の中にIF関数を入れて求めることができます。「=LEFT(A1,IF(MID(A1,5,1)=2,4,5))」とします。これは間違いです。

f:id:waenavi:20191024233911j:plain

 

解説

MID関数で取り出される文字は「文字列」であって数値ではありません。数値化する前の文字列と、数値とは異なるものと扱われます。したがって、MID(A1,5,1)=2という条件式は間違いです。

ダブルクォーテーションで囲みます。

  • =LEFT(A1,IF(MID(A1,5,1)="2",4,5))

f:id:waenavi:20191024234103j:plain

 

ちなみに、MID関数で取り出された文字列に1をかけて数値化するのは間違いです。エラーになります。数値でないものは数値化できないからです。

f:id:waenavi:20191024234733j:plain

 

5.VLOOKUP関数を使う

(1)VLOOKUP関数の検索値

問題

A列のコードのうち1番右のアルファベットは、B=部長、J=次長、K=課長を表している。VLOOKUP関数を用いて、部長、次長、課長を表示しなさい。

f:id:waenavi:20191024235146j:plain

 

解説

RIGHT関数を用いてアルファベットだけを取り出します(文字数省略可能)。

f:id:waenavi:20191024235219j:plain

 

これを検索値として、VLOOKUP関数で検索します。このように、文字列の一部を検索値として、別の文字列に変換することができます。

  • =VLOOKUP(RIGHT(A2),$D$2:$E$4,2,0)

f:id:waenavi:20191024235127j:plain

 

(2)VLOOKUPがエラーになる理由

問題

A列のコードのうち先頭の数字は、部を表している。それぞれ部を求めなさい。

f:id:waenavi:20191024235146j:plain

 

解説

LEFT関数を用いて先頭の数字を取り出します(文字数省略可能)。1~6の整数になります。

f:id:waenavi:20191024235337j:plain

 

さきほどの設問と同じように、これを検索値として、VLOOKUP関数で検索します。エラーになります

  • =VLOOKUP(LEFT(A2),$F$2:$G$7,2,0)

f:id:waenavi:20191024235427j:plain

 

LEFT関数で取り出される値は「文字列」であり、数値でありません。いっぽうコード表に入力されている整数は数値なので一致しません。

f:id:waenavi:20191024235627j:plain

 

この場合は、LEFT関数で取り出される値が1~6であることが分かっているので、1をかけて数値化をします(参考:【Excel】数値と文字列の違い、数値と文字列の変換方法の総まとめ)。

  • =VLOOKUP(LEFT(A2)*1,$F$2:$G$7,2,0)

f:id:waenavi:20191024235758j:plain

 

別解

先頭にシングルクォーテーションを入れると文字列としての数値を入力することができます。そのほか数値を文字列に変換するには主に次のような方法があります。

  • 先頭にシングルクォーテーションを入れる「'1
  • 空白文字列の連結「1&""
  • 表示形式を「文字列」にしてから入力

f:id:waenavi:20191024235922j:plain

 

コード表のコードが文字列かされている場合は、VLOOKUP関数の検索値も文字列にしなければなりません。この場合は、LEFT関数を数値化する必要はありません。コード表のコードが「数値」か「文字列」かによって数式が異なりますので注意が必要です。

f:id:waenavi:20191025000030j:plain

 

6.LEFT、RIGHT、IF、IFERROR、VLOOKUP複合問題

問題

A列のコードの左側2文字が2桁の整数である場合、その2桁の整数は課を表している。部長は「~部長」、次長は「~部次長」、課長は「~部~課長」と表示しなさい。ただし、部署のコード番号は数値とする。

f:id:waenavi:20191025000153j:plain

 

解説

取得するためのコード表が3つあるので3つに分けて考えます。3列挿入します。

f:id:waenavi:20191025000424j:plain

 

まず、部を求めます。

  • =VLOOKUP(LEFT(A2)*1,$I$2:$J$7,2,0)

f:id:waenavi:20191025000556j:plain

 

次に、課を求めます。左の2文字を使って求めます。LEFT関数で取得する値は1を掛けて数値化します。

  • =VLOOKUP(LEFT(A2,2)*1,$K$2:$L$9,2,0)

f:id:waenavi:20191025000634j:plain

 

このとき、最初の2文字が整数でない場合エラーになります。数値でない場合数値化できないからです。IFERROR関数で空白にします。

  • =IFERROR(VLOOKUP(LEFT(A2,2)*1,$K$2:$L$9,2,0),"")

f:id:waenavi:20191025000725j:plain

 

コードの右が「J」であれば次長です。次長だけ「次」と表示します。

  • =IF(RIGHT(A2)="J","次","")

f:id:waenavi:20191025000813j:plain

 

最後にこれらを連結すれば完成です。

  • =B2&C2&D2&"長"

f:id:waenavi:20191025000840j:plain

 

1つの式にすると次のようになります。

  • =VLOOKUP(LEFT(A2)*1,$J$2:$K$7,2,0)&IFERROR(VLOOKUP(LEFT(A2,2)*1,$L$2:$M$9,2,0),"")&IF(RIGHT(A2)="J","次","")&"長"

f:id:waenavi:20191025000936j:plain

 

7.並べ替え

問題

A列のファイル名の拡張子は最後の3文字である。拡張子の昇順で並べ替えなさい。ただし、同じ拡張子の場合はファイル名の昇順にしなさい。

f:id:waenavi:20191025001142j:plain

 

解説

Excelの並べ替えの機能は、原則として文字列の一部分をキーとして並べ替えをすることはできません。したがって、その部分だけを取り出す必要があります。

ファイル名のドットの後にある文字列を拡張子といい、文字数に決まりはありません。しかし、今回はすべて3文字であることが事前に分かっているので、RIGHT関数で取り出すことができます。

f:id:waenavi:20191025001352j:plain

 

優先キーを拡張子(昇順)、第2優先キーをファイル名(昇順)にします(参考:【Excel】並べ替えの昇順と降順、優先順位、グループ化、一部分だけの並べ替え)。

f:id:waenavi:20191025001422j:plain

 

これで拡張子ごとでグループ化することができます。

f:id:waenavi:20191025001459j:plain

 

8.発展演習:k=1000、M=100万、G=10億の換算

問題

A列に1以上100億未満の数値を入力した。1000以上の数値は1000で、100万以上の数値は100万で、10億以上の数値は10億で割り、有効数字3桁(1以上1000未満)になるように四捨五入しなさい。k=1000、M=100万、G=10億と表示しなさい。

f:id:waenavi:20191025204443j:plain

 

解説

桁数を変えるには常用対数の関数であるLOG関数を使います。LOG関数(LOG10関数)については別の記事で詳しく解説しています。

 

有効数字3桁で四捨五入をするときのROUND関数の桁数は「2-LOG」であり、1000、100万、10億の単位にするには「10^FLOOR(LOG,3)」で割ります。このことについては上記の記事で解説しており、ここでは説明を割愛します。

したがって、B列は「=ROUND(A1,2-LOG(A1))/10^FLOOR(LOG(A1),3)」となります。

f:id:waenavi:20191025204804j:plain

 

LOGは整数部分の桁数を表すので、3で割って切り捨てると0001112223・・・となります。

  • =INT(LOG(A1)/3)

f:id:waenavi:20191025204904j:plain

 

これをMID関数で変換します。

  • =MID("kMG",INT(LOG(A1)/3),1)

f:id:waenavi:20191025204950j:plain

 

MID関数で開始位置を0にするとエラーになりますから、IFERROR関数で空白にします。これで完成です。

  • =IFERROR(MID("kMG",INT(LOG(A1)/3),1),"")

f:id:waenavi:20191025205018j:plain

 


解説は以上です。


 

 


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