ExcelでLEFT関数、RIGHT関数、MID関数を使うときには単純に文字列の一部を抽出することもありますが、取り出した文字列を条件としてIF関数で判定したり、検索値としてVLOOKUP関数と組み合わせて使うこともあります。
また、IF関数で取り出す文字数を変えたり、開始位置を変えたりすることもあります。さまざまな入れ子のパターンがありますので、応用問題を解くことによって慣れたほうが良いです。
そこで、今回は、LEFT・RIGHT・MIDをIF・VLOOKUPの中で使う方法などの応用事例と注意点について出題します。
目次
- 0.LEFT、RIGHT、MID関数の基本
- 1.IF関数の条件式で使う
- 2.先頭の文字を削除する
- 3.都道府県名の取得
- 4.数値を取り出したときの注意点
- 5.VLOOKUP関数を使う
- 6.LEFT、RIGHT、IF、IFERROR、VLOOKUP複合問題
- 7.並べ替え
- 8.発展演習:k=1000、M=100万、G=10億の換算
0.LEFT、RIGHT、MID関数の基本
LEFT関数、RIGHT関数、MID関数の基本的な使い方と練習問題はこちらの記事をご覧ください。
1.IF関数の条件式で使う
問題
A列のうち部長に〇印をつけなさい。また、次長課長に〇印をつけなさい。
解説
A列の右側の2文字は「RIGHT(A2,2)」です。これが「部長」であれば丸印をつけるので、IF関数の条件式を「RIGHT(A2,2)="部長"」とします。
- =IF(RIGHT(A2,2)="部長","〇","")
部長だけ丸印が付きます。
次長課長も同じようにするのであれば、複合参照のほうが早いです。
- =IF(RIGHT($A2,2)=B$1,"〇","")
2.先頭の文字を削除する
問題
A列のうち課長について、最初の3文字を削除しなさい。
解説
最初の3文字を削除するということは、4文字目以降を取得するということです。つまり「MID(A1,4,10)」です。文字数は多めにしておけばよいです。
IF関数を用いて課長とそれ以外で処理を分けます。
- =IF(RIGHT(A1,2)="課長",MID(A1,4,10),A1)
別解
課長は4文字目以降を取得しますが、それ以外は1文字目から取得すると考えることもできます。MID関数の開始位置を4にするか1にするかということになります。IF関数では「IF(RIGHT(A1,2)="課長",4,1)」となります。
これをMID関数の中に入れます。
- =MID(A1,IF(RIGHT(A1,2)="課長",4,1),10)
3.都道府県名の取得
問題
A列に都道府県庁所在地を入力した。都道府県名と都市名に分けなさい。
解説
先頭に都道府県名が付いている住所の中から、都道府県名だけを取り出すには、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))
住所の途中から抜き出すには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))
別解
都道府県名は取り出す文字数が異なるだけです。住所の4文字目が「県」であれば4文字、それ以外は3文字です。
- IF(MID(A2,4,1)="県",4,3)
したがって、LEFT関数の中にこれを入れるという方法もあります。
- =LEFT(A2,IF(MID(A2,4,1)="県",4,3))
また、都市名についても開始位置が異なるだけです。住所の4文字目が「県」であれば5文字目、それ以外は4文字目です。
- IF(MID(A2,4,1)="県",5,4)
したがって、これをMID関数の第2引数にするという方法もあります。
- =MID(A2,IF(MID(A2,4,1)="県",5,4),10)
4.数値を取り出したときの注意点
問題
A列の5文字目が「2」のとき先頭の4文字を取り出し、それ以外は5文字を取りだしなさい。
誤答例
A列の5文字目はMID関数を用いて、「MID(A1,5,1)」です。
取り出す文字数が異なるだけなので、LEFT関数の中にIF関数を入れて求めることができます。「=LEFT(A1,IF(MID(A1,5,1)=2,4,5))」とします。これは間違いです。
解説
MID関数で取り出される文字は「文字列」であって数値ではありません。数値化する前の文字列と、数値とは異なるものと扱われます。したがって、MID(A1,5,1)=2という条件式は間違いです。
ダブルクォーテーションで囲みます。
- =LEFT(A1,IF(MID(A1,5,1)="2",4,5))
ちなみに、MID関数で取り出された文字列に1をかけて数値化するのは間違いです。エラーになります。数値でないものは数値化できないからです。
5.VLOOKUP関数を使う
(1)VLOOKUP関数の検索値
問題
A列のコードのうち1番右のアルファベットは、B=部長、J=次長、K=課長を表している。VLOOKUP関数を用いて、部長、次長、課長を表示しなさい。
解説
RIGHT関数を用いてアルファベットだけを取り出します(文字数省略可能)。
これを検索値として、VLOOKUP関数で検索します。このように、文字列の一部を検索値として、別の文字列に変換することができます。
- =VLOOKUP(RIGHT(A2),$D$2:$E$4,2,0)
(2)VLOOKUPがエラーになる理由
問題
A列のコードのうち先頭の数字は、部を表している。それぞれ部を求めなさい。
解説
LEFT関数を用いて先頭の数字を取り出します(文字数省略可能)。1~6の整数になります。
さきほどの設問と同じように、これを検索値として、VLOOKUP関数で検索します。エラーになります。
- =VLOOKUP(LEFT(A2),$F$2:$G$7,2,0)
LEFT関数で取り出される値は「文字列」であり、数値でありません。いっぽうコード表に入力されている整数は数値なので一致しません。
この場合は、LEFT関数で取り出される値が1~6であることが分かっているので、1をかけて数値化をします(参考:【Excel】数値と文字列の違い、数値と文字列の変換方法の総まとめ)。
- =VLOOKUP(LEFT(A2)*1,$F$2:$G$7,2,0)
別解
先頭にシングルクォーテーションを入れると文字列としての数値を入力することができます。そのほか数値を文字列に変換するには主に次のような方法があります。
- 先頭にシングルクォーテーションを入れる「'1」
- 空白文字列の連結「1&""」
- 表示形式を「文字列」にしてから入力
コード表のコードが文字列かされている場合は、VLOOKUP関数の検索値も文字列にしなければなりません。この場合は、LEFT関数を数値化する必要はありません。コード表のコードが「数値」か「文字列」かによって数式が異なりますので注意が必要です。
6.LEFT、RIGHT、IF、IFERROR、VLOOKUP複合問題
問題
A列のコードの左側2文字が2桁の整数である場合、その2桁の整数は課を表している。部長は「~部長」、次長は「~部次長」、課長は「~部~課長」と表示しなさい。ただし、部署のコード番号は数値とする。
解説
取得するためのコード表が3つあるので3つに分けて考えます。3列挿入します。
まず、部を求めます。
- =VLOOKUP(LEFT(A2)*1,$I$2:$J$7,2,0)
次に、課を求めます。左の2文字を使って求めます。LEFT関数で取得する値は1を掛けて数値化します。
- =VLOOKUP(LEFT(A2,2)*1,$K$2:$L$9,2,0)
このとき、最初の2文字が整数でない場合エラーになります。数値でない場合数値化できないからです。IFERROR関数で空白にします。
- =IFERROR(VLOOKUP(LEFT(A2,2)*1,$K$2:$L$9,2,0),"")
コードの右が「J」であれば次長です。次長だけ「次」と表示します。
- =IF(RIGHT(A2)="J","次","")
最後にこれらを連結すれば完成です。
- =B2&C2&D2&"長"
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","次","")&"長"
7.並べ替え
問題
A列のファイル名の拡張子は最後の3文字である。拡張子の昇順で並べ替えなさい。ただし、同じ拡張子の場合はファイル名の昇順にしなさい。
解説
Excelの並べ替えの機能は、原則として文字列の一部分をキーとして並べ替えをすることはできません。したがって、その部分だけを取り出す必要があります。
ファイル名のドットの後にある文字列を拡張子といい、文字数に決まりはありません。しかし、今回はすべて3文字であることが事前に分かっているので、RIGHT関数で取り出すことができます。
優先キーを拡張子(昇順)、第2優先キーをファイル名(昇順)にします(参考:【Excel】並べ替えの昇順と降順、優先順位、グループ化、一部分だけの並べ替え)。
これで拡張子ごとでグループ化することができます。
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億と表示しなさい。
解説
桁数を変えるには常用対数の関数である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)」となります。
LOGは整数部分の桁数を表すので、3で割って切り捨てると0001112223・・・となります。
- =INT(LOG(A1)/3)
これをMID関数で変換します。
- =MID("kMG",INT(LOG(A1)/3),1)
MID関数で開始位置を0にするとエラーになりますから、IFERROR関数で空白にします。これで完成です。
- =IFERROR(MID("kMG",INT(LOG(A1)/3),1),"")
解説は以上です。