【本稿は、『エクセルの達人』に8月11日付で収録したコンテンツの転載記事です。】本稿では『文字列操作を極める』シリーズの第1回として、LEFT関数、MID関数を使った初歩的な文字列操作「切り出しテクニック」を紹介したいと思う。
t凹面、毎週土日の正午過ぎには、『エクセルの達人』(エク達)というウェブサイトに掲載したコンテンツをこちらに転載するつもりです。エクセル講座をお楽しみください。
エクセルを操作する際に、是非とも覚えておきたいのが、「文字列を操作する関数」である。その基本は、LEFT関数、RIGHT関数、MIDDLE関数の3つ、そしてFIND関数とLEN関数だ。。
何事も習うより慣れろ、と言われるが、本稿ではこのうちLEFT関数とMID関数、そしてLEN関数の3つを、さっそく使ってみたい。
財務省が作成する『普通貿易統計』と呼ばれる統計資料がある。
元データは政府の統計窓口『e-stat』からダウンロードできるが、ここでは2021年8月11日時点で取得したファイルの一部をダウンロードし、公表しておきたい。
【参考】普通貿易統計(2021年1月)
※著者の側にてウィルス検査をしてあるので、安心して開いていただいて良い。
ファイルを開くと、こんなフィールドが目に入る。
- cat01_code
- 概況品目(輸出)
- cat02_code
- 概況品目表の数量・金額
- area_code
- 国
- time_code
- 時間軸(年次)
- unit
- value
図表1 今回のエクセルファイルのフォーマット
本日紹介したいのは、このうちF列、すなわち「国」から数字を取り出す方法である(次回以降はB列からデータを取り出す方法についても紹介したいと思っている)。
まず、「国」については、次のように、必ず「数字3桁」、「_(アンダーバー)」、「国名称」というフォーマットで構成されている。
- 103_大韓民国
- 105_中華人民共和国
- 106_台湾
- 107_モンゴル
- 108_香港
このように、文字コードの長さが決まっている場合は、非常に簡単だ。LEFT関数とMID関数を使えば良い。
LEFT関数の具体的な書式は、次のとおり。
LEFT(文字列,文字数)
サンプルファイルの「国」データ(F列)については、必ず国コードが3ケタと決まっている。
ここではサンプルファイルのK2セルに、こんな数式を入力してみよう(図表2)。
=LEFT(F2,3)
図表2 LEFT関数の入力(※大文字でも小文字でも良い)
すると、F2セルに含まれる「103_大韓民国」という文字列から、前半の3文字目までの「103」を取り出すことができた(図表3)。
図表3 LEFT(F2,3)の入力結果
では、次にF2セルに含まれる「103_大韓民国」という文字列から、後半の「大韓民国」を取り出してみよう。
データを眺めていて気付くのは、すべてのデータは数字3桁と「_(アンダーバー)」が付いているため、国名はすべて5文字目から始まる、という点だが、ここで問題がある。
じつは、「決まった文字数を左から(あるいは右から)切り出す」のは、大変に簡単な作業だ。
しかし、国名の場合は単純ではない。「大韓民国」の場合は4文字だが、その次の「中華人民共和国」は7文字、「台湾」は2文字、と、文字数が一定しないからだ。
このような場合にどうすればよいか。
最も簡単なやり方が、MID関数である。
MID関数の具体的な書式は、次のとおり。
MID(文字列,開始位置,文字数)
開始位置とは「文字を切り出し始めるための場所」のことであり、先ほどのケースでいえば、「_(アンダーバー)」の直後、つまり5文字目となる。
一方、文字数については「最低限、これ以上切り出してほしい」という文字数を指定する。じつは、これについてはピッタリでなくても構わない。
たとえば、開始位置に「5」、文字数に「100」と入力してやれば、「この文字列の5文字目から100文字目までを抜き出してほしい」、という指示となる。ということは、極端な話、すべてのデータの最長文字数を入れてやれば、「国名を抜き出す」という目的は達成できる、というわけだ(図表4、図表5)。
図表4 MID関数の入力
図表5 MID(F2,5,100)の入力結果
これで、いちおうは形になった。
以上で本稿を終了としても良いのだが、ただ、せっかくだからもう少しスマートなやり方も紹介しておきたい。ここで使用するのはLEN関数だ。
LEN関数の具体的な書式は、次のとおり。
LEN(文字列)
これは、文字の長さを返す関数であり、先ほどの「F2」列を指定してやれば、文字の長さである「8」が返ってくる(図表6、図表7)。
図表6 LEN関数の入力
図表7 LEN (F2)の入力結果
そして、すべてのセルにおける国名の文字の長さは、次の計算式で求められるはずだ。
(国名の文字の長さ)=(セルのデータの文字の長さ)-4
ということは、先ほどのMID関数についても、MID(F2,5,100)ではなく、MID(F2,5,LEN(F2)-4)と入力してやれば、ずばりの文字数を抜き出すことができるはずだ。
MID(F2,5,LEN(F2)-4)→F2セルの5文字目から、「LEN(F2)-4」で求まる数値の長さの文字を切り出す、という意味。
さっそくやってみよう(図表8、図表9)。
図表8 MID+LEN関数の入力
図表9 MID(F2,5,LEN(F2)-4)の入力結果
いかがだろうか。
念のため、「大韓民国」以外の国名にも対応できるかどうか、関数をコピーしてみよう(図表10)。
図表10 MID(F2,5,LEN(F2)-4)を下にコピーした結果
ちゃんと出来上がった。
それぞれシンプルな関数なのだが、これらを組み合わせることで、かなり複雑な演算ができることがおわかりいただけるだろう。
※ ※ ※ ※ ※ ※ ※
本稿を『文字列操作を極める』シリーズの第1回と位置付けたい。第2回目以降では、さらに複雑な文字列操作にチャレンジしていくつもりだ。
View Comments (5)
経営財務のモダンEXCELの難しさにやられ気味なので、理解可能なあたりでよかったです。
つぎはvlookupを使わない検索あたりでお願いいたします。
こういうの組んでるときは出力結果をsort、uniqで処理してエラーが出ていないか軽くチェックするのがいいです。
文字列操作に先立ってtrim(余分なスペースを削除)に食わせておく必要があることが多いかもですね。
left,right,mid,lenはわかりやすいんですが、長さが不揃いな場合に処理が面倒になることが多いですね。スクリプト系、Unix系になれてる人は正規表現で処理したくなるところです。
%や?がもっと柔軟に使える感じです。VBAまで行くと正規表現が使えるんですけど、今回扱う範囲からはみ出しそうですね。
FINDで"_"の位置抜き出してMIDの中に放り込むと汎用性がでてきますよね。
関数大好きなので、楽しく拝見致しました。
エクセルに限らず文字列処理のスキルは文系にも、いや文系にこそ必要ですね。
とはいえ…
cat01_code:0
概況品目(輸出):0_食料品及び動物
area_code:50106
国:106_台湾
…何でこんなデータ構造なんでしょう、財務省は。
何でひと手間かけないと名称が取り出せないのでしょう。
なんて再利用しづらいのでしょう。
まあだからこそ今回のお題に選ばれたのでしょうがw
(ひょっとして予約語には必ずコード番号を付ける、みたいな運用なのかなー)