DTPでよく使うエクセル関数の使い方を丁寧に紹介【IF関数・LEN関数・RIGHT関数】

はじめに

前回は、文字列中の文字を抽出するMID関数を解説しました。

今回は、道府県名を取り出す方法を例に、条件ごとに作業を分けるIF関数、テキストの文字数を調べるLEN関数、右端からの文字を抽出するRIGHT関数、を解説します。

実は、意外に簡単です。
というのも、道府県名はパターンが非常に少ないので、この場合あの場合その場合…、と条件による取り出し方も非常にコンパクトになります。

目次


IF関数―条件ごとに作業を分ける

IF 関数を使うと、条件をテストして結果 (True または False) を返すことにより、値と予想値の間の論理的な比較を行うことができます。

=IF(条件が True であればある処理を行い、それ以外の場合は別の処理を行う)参照：IF 関数 - Office サポート
例1：
B2：千葉県
=IF(B2="千葉県","そうです","違います")
結果：そうです

LEN関数―テキストの文字数を調べる

文字列の文字数を返します。

=LEN(文字列)文字列    必ず指定。 文字数またはバイト数を調べる文字列を指定します。 スペースは文字として数えられます。
参照：LEN 関数、LENB 関数 - Office サポート
例2：
B2：千葉県
=LEN(B2)
結果：3


文字列の末尾 (右端) から指定された文字数の文字を抽出します。

=RIGHT(文字列[,文字数])文字列    必ず指定。 （A2などのセル名や"あいう"などの文字）を指定します。
文字数    省略可能。 取り出す文字数 (文字列の末尾からの文字数) を指定します。
参照：RIGHT 関数、RIGHTB 関数 - Office サポート
例3：
B2：千葉県■市▲町12-34
=RIGHT(B2,9)
結果：■市▲町12-34

道府県名の文字数のパターンを調べる

道府県名とその文字数は以下の通り。



表を見ると分かりますが、文字数が4の都道府県は3つのみでしかも「県」のみになります。

他の1道2府40県は全て文字数3。

つまり、

  • 道府県名の文字数が4の場合、しかも「県」しかない
  • 道府県名の文字数が3の場合

の2つしかパターンがありません。

意外に単純でした。

郡市区町村名の1文字目に「県」があるか

道府県名の文字数が3の場合に懸念されるのは、住所の4文字目、3文字の都道府県名を除いた住所では1文字目に「県」がある、例えば「●●県県■市」の場合、
  • 道府県名の文字数が4の場合、しかも「県」しかない
に該当するので、郡市区町村名の1文字目に「県」があるかを調べる必要があります。

調べた結果、郡市区町村名の1文字目に「県」は無いことを確認しました。

調査結果は、『都道府県郡市区町村名一覧』各シートの「県が先頭」項目をご覧ください。

住所から道府県名を取り出す

では、道府県名の文字数が3の場合の関数から考えていきます。

道府県名の文字数が3の場合
道府県名の文字数が3の場合の条件は、


  • 道府県名は、住所の最初に来る
  • 「道」「府」「県」が付いて3文字なので、道府県は関係ない
  • 「東京都」は除外する


ですので、住所の左端から3文字を取り出し、「東京都」は除外すれば良いことになります。

そうなると関数は、以前に解説したLEFT関数を使って、以下の通りとなります。

例4：
B2：千葉県■市▲町12-34
=LEFT(B2,3)
結果：千葉県

「東京都」を除外するには、IF関数を使って、以下の通りになります。
=IF(LEFT(B2,3)="東京都","",●)
これは、「住所の左端から3文字が「東京都」なら無し、他の場合は●」という意味になります。

そして、上記●に例4の関数を挿入すると、以下の通りになります。
=IF(LEFT(B2,3)="東京都","",LEFT(A3,3))

道府県名の文字数が4の場合
次に、道府県名の文字数が4の場合の条件は、

  • 道府県名は、住所の最初に来る
  • 住所の左端から4文字分が県名である

関数は同じく、以下の通りとなります。

例5：
B2：神奈川県■市▲町12-34
=LEFT(B2,4)
結果：神奈川県

IF関数を使って一つの式に
では、例4と例5を、各々条件の結果としてIF関数を使用して一つにまとめます。

例4と例5を条件分けするのは何でしょうか？

それは、

  • 「県」が住所の左端から必ず4文字目に来る

ということです。

この条件を関数で表すのに使用するのは、前回解説しましたMID関数です。

MID関数は

=MID(文字列, 開始位置, 文字数)文字列    必ず指定。 取り出す文字を含む文字列（A2などのセル名など）を指定します。
開始位置    必ず指定。 文字列から取り出す先頭文字の位置を数値で指定します。 文字列の先頭文字の位置が 1 になります。
文字数    必ず指定。 取り出す文字数を指定します。

ですので、文字列がB2セルにあるとして、開始位置は「県」の位置で4文字目なので「4」、文字数は「県」1文字なので「1」になり、関数は、

例6：
B2：神奈川県■市▲町12-34
=MID(B2, 4, 1)
結果：県

ということで、

  • 「県」が住所の左端から4文字目にある場合、→住所の左端から4文字を取り出す
  • その他の場合、→住所の左端から3文字を取り出す（東京都は除く）

となり、これを関数を使って表すと、

  • MID(B2, 4, 1)="県"→=LEFT(B2,4)
  • （その他の場合、）→IF(LEFT(B2,3)="東京都","",LEFT(B2,3))

これをIF関数を使ってまとめると、

=IF(MID(B2, 4, 1)="県",LEFT(B2,4),IF(LEFT(B2,3)="東京都","",LEFT(B2,3)))

となります。

これで、道府県名を住所から抜き出すことが出来るようになりました。

住所から郡市区町村以下を抜き出す

道府県名を別セルに取り出した後、住所から郡市区町村以下を取り出す方法は何個かあります。

住所の文字数から道府県名の文字数を引いた数の文字を右端から数えて抜き出す
この場合、RIGHT関数を使用します。

住所の文字数は、B2セルにあるとして

=LEN(B2)

道府県名の文字数は、C2セルにあるとして

=LEN(C2)

そして住所の内、郡市区町村以下の文字数は

=LEN(B2)-LEN(C2)

となります。

結果、郡市区町村以下は
=RIGHT(B2,LEN(B2)-LEN(C2))

で取り出すことが出来ます。

置換で住所から道府県名を消す
住所から道府県名を探して、道府県名を空文字に置換すると、郡市区町村以下を取り出すことが出来ます。

置換するには、以前紹介しましたSUBSTITUTE関数を使用します。

B2に住所、C2に抜き出した道府県名がある場合、
=SUBSTITUTE(B2,C2,"")

となります。






コメント

コメントを投稿

よく読まれている記事

CSSボタンでテキストを天地中央に揃えるとき、なぜボタン高と行高を一緒にするのか

イメージ
表題の件が気になり調べたものを共有した方がイイかなと思い公開してみる。 自身の中でまだよく噛み砕けていないので判りづらい箇所があるかと思うが、そこはご寛恕を。 参考 Cascading Style Sheets Level 2 Revision 1 (CSS 2.1) Specification Cascading Style Sheets Level 2 Revision 1 (CSS 2.1) Specification（日本語訳） Eric A. Meyer著. 株式会社クイープ訳. CSS完全ガイド第2版 . オライリージャパン, 2010 インライン要素とは何か 文書構造を構成するブロックレベル要素内の特定箇所にある特定の機能や論理的な意味を付加する要素をいう。それ自身の前後に改行を生成しない。例としては、a要素、em要素、span要素など。 インライン要素には非置換要素と置換要素とがある。今回は置換要素には触れない（置換要素の代表例はimg要素）。 非置換要素とは何か 要素に囲まれた内容がそのまま表示される要素。HTMLやXHTMLの要素の大半は非置換要素である。 インライン非置換要素の特徴 widthとheightは適用されない。 背景はコンテンツエリアとpaddingに適用される。 上下marginは、事実上適用されない。 左右paddingは、要素の先頭と末尾のみに適用される。 上下paddingは、要素の背景を上下に伸ばすが、行の高さに影響しないため、背景色がある場合は上下の行（実際は上の行）に重なってしまう。 line-heightは何を制御しているか 行高を指定するline-heightは単純に行間を制御しているのではなく、 leadingという、コンテンツエリアの上下にある余白を制御 している。 leadingはline-heightとfont-sizeとの差 であり、 この差を等分 して コンテンツエリアの上下に加えることによって行間が決まる （コンテンツエリア＋leadingをインラインボックスという）。 コンテンツエリアとは、インライン非置換要素では、要素内の全ての文字を繋ぎ合わせたボックスでfont-sizeの値と同じ高さになる。 図解すると以下の通り。 つまり、line-heightが設定され...
Read more »

FullCalendarの導入からカレンダー毎の色指定まで

イメージ
2012年7月の拙サイト改装2回目時に既に導入している「 FullCalendar 」について、導入からGoogleカレンダーの複数読込み、カレンダー毎の色指定までをおさらい。基本的な流れはFullCalendarサイトの解説を踏襲。 デフォルト状態 下記改修を施した後 1. ダウンロード Downloadページ から「fullcalendar-1.5.3.zip」をダウンロードして解凍後、自サーバーへアップ。 2. fullcalendarのCSSとJSを読込む <head> タグ内に以下を記述。 <link rel="stylesheet" type="text/css" href=" fullcalendar.css " /> <script type="text/javascript" src=" fullcalendar.js "></script> <script type="text/javascript" src=" fullcalendar.min.js "></script> <script type="text/javascript" src=" gcal.js "></script> 赤字部分は各自の階層に合うように適宜変更。 3. FullCalendarにGoogleカレンダーを読込むためのjQueryを <head> タグ内に記述 必ず <script> タグで囲むこと。 <script type="text/javascript"> $(document).ready(function() { $(' #calendar ').fullCalendar({ // あとでここに色々追記 }) }); </script> 4. <body> タグ内のFullCalendarを挿入したい箇所にID属性を挿入 <...
Read more »

FacebookページのフィードURLを取得しウォールを自サイトに表示

イメージ
結局未採用だけど、折角調べて試してみたので記録しておく。 参照サイトは jQueryでFacebookページのウォールを自分のサイトに表示する方法（簡易バージョン） | webOpixel FacebookページのフィードURL取得は（取得ツール付き） FacebookページのフィードURLを取得してフィードリーダーで購読する方法: 小粋空間 以上、深謝。 自分のFacebookページのフィードURLを取得 上記参照サイトでは、自FacebookページのID確認方法について書かれているが、小粋空間さんのページに「自FacebookページのフィードURL取得ツール」があるので、自FacebookページのURLを入力すれば、フィードURLを表示してくれる。但しフォーマットはAtom1.0なので、RSS2.0フォーマットは以下のように変更する。 小粋空間さんの取得ツールで取得したフィードURL https://www.facebook.com/feeds/page.php?format= atom10 &id= [取得したID] RSS2.0フォーマットに変更後 https://www.facebook.com/feeds/page.php?format= rss20 &id= [取得したID] jQueryを自サイトに貼込み webOpixelさんからjQueryをを拝借し、自サイトに貼り込む。 <script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1.8.1/jquery.min.js"></script> <script type="text/javascript"> $(function() { var formatData = function(date) { var d = new Date(date); return (d.getFullYear() + '.' + ("0"+(d.getMonth()+1)).slice(-2) + '.' ...
Read more »