エクセルで住所を入力したときに、都道府県とそれ以降の住所に分けたいときはどうしていますか?

一行ずつ都道府県だけを入力して、もとの住所からは都道府県だけを削除して・・・なんて作業は手間と時間がかかってしかたありませんよね。

そんなときは、いくつかの関数を組み合わせて使えば、手間をかけずに都道府県を分けることができるのです。

手順が多くてむずかしそうにみえますが、考え方はいたって単純です。
操作内容も同じことを繰り返すので思ったよりも簡単です。
この方法は住所以外でも応用できるので、知っておいて損はありませんよ!

住所を都道府県とそれ以降の住所に分ける方法<1>

関数を使ってB列の住所から都道府県と残りの住所を分けてみましょう。
今回はまず、都道府県を表示するところまでを説明します。
(都道府県をのぞいた残りの住所を表示する方法はこちら

 

<完成図>

【考え方】

都道府県は3文字、もしくは4文字なので、LEFT関数で都道府県だけを表示することはできません。

しかし、4文字なのは「神奈川県、和歌山県、鹿児島県」だけなので、住所の頭文字から4番目の1文字目が「県」であるかどうかを判断して、左から4文字表示するか、3文字表示するかを決めます。そうすれば正しく表示することができるのです。(下図参照)

img20160330_00_1

img20160330_00_2

【関数式】

B列の住所の4番目の1文字目が”県”であるならば、
 B列の住所の左から4文字分をC列に表示する
そうでなければ左から3文字分を表示する

というIF関数の式を作ります。

=IF(MID(B2,4,1)=”県”,LEFT(B2,4),LEFT(B2,3))

MID(B2,4,1)=”県”→もし、セルB2の頭文字から4番目の1文字目が”県”である
LEFT(B2,4)→セルB2の左から4文字分を表示する
LEFT(B2,3)→セルB2の左から3文字分を表示する


1.都道府県を表示させたいセル(C2)を選択し、数式バー横の[fx]関数の挿入ボタンをクリックします。

 

2.関数の挿入ウインドウが開くので、[関数の分類(C):]→「論理」、[関数名(N):]→「IF」を選び、「OK」ボタンをクリックします。

 

3.IF関数の引数ウインドウが開きます。論理式欄には、MID関数を使った「都道府県が入った住所の頭文字から4番目の1文字目が”県”である」という式を入力していきます。

img20160330_02_2

 

名前ボックスの(A列の上部にある)▼マークをクリックして「MID」を選びます。もしも「MID」がないときは、下の「その他の関数」をクリックして関数の挿入ウインドウから「MID」を選びます。

すると、MID関数の関数の引数ウインドウが開きます。

↓ ↓ ↓ ↓

 

4.文字列の欄には、都道府県が入っている住所のセル(B2)をクリックします。

img20160330_05

↓ ↓ ↓ ↓

img20160330_05_2

 

5.開始位置欄には4番目の「4」、文字数欄には1文字分の「1」をそれぞれ半角入力します。次に、下の「OK」ボタンはクリックしないで数式バーに表示されている「IF」の文字のところをクリックします。

img20160330_06

↓ ↓ ↓ ↓

img20160330_06_2

 

6.再び、IF関数の引数ウインドウが開くので、論理式欄に「MID(B2,4,1)」と表示されていれば、そのあとに続けて「=”県”」と県以外は半角入力します。

img20160330_07

↓ ↓ ↓ ↓

img20160330_07_2

これで、IF関数の「B2(元住所)の頭文字から4番目の1文字が”県”であれば」という部分(式)ができました。

 

7.真の場合欄には、LFET関数を使った「左から4文字分を表示する」という式を入力します。

真の場合欄をクリックし、3.と同様に、名前ボックスのA列の上部にある)▼マークをクリックして「LEFT」を選びます。もしも「LEFT」がないときは、下の「その他の関数」をクリックして関数の挿入ウインドウから「LEFT」を選びます。

img20160330_08

 

8.LEFT関数の引数ウインドウが開いたら、文字列欄には都道府県が入っている住所のセル(B2)をクリックし、文字数欄には4文字分の「4」を半角入力します。次に、下の「OK」ボタンはクリックしないで数式バーに表示されている「IF」の文字のところをクリックします。

img20160330_09

 

9.再び、IF関数の引数ウインドウが開き、真の場合欄に「LFET(B2,4)」と表示されていれば、次に偽の場合欄をクリックし、7.と同様に名前ボックス▼マークから「LEFT」を選びます。

img20160330_10

↓ ↓ ↓ ↓

img20160330_11

 

10.LEFT関数の引数ウインドウが開いたら、文字列欄には都道府県が入っている住所のセル(B2)をクリックし、文字数欄には3文字分の「3」を半角入力します。これで入力が終わりなので、「OK」ボタンをクリックします。

img20160330_12

 

都道府県だけが表示されました。

img20160330_13

 

11.下のセルにも関数をコピーするために、フィルハンドル(+)をダブルクリックします。

 

下のセルにも都道府県が正しく表示されました。

 

次回は、都道府県が分けられた残りの住所の表示方法をご紹介します。

 

計算元のデータが入力されるまで、数式は入っていてもセルは空白状態にしておきたいことはありませんか?

ところが下表のように、項目が未入力の残高欄(E6、E7)に数式を入力しておくと・・・

img20151005_01

↓ ↓ ↓

img20151005_02

直前の計算結果(E5)が表示されてしまいます。

そこで今回は、データ(項目)が未入力のときはセル(残高)が空白になるように、IF関数を使って設定する方法をご紹介しましょう。

◇IF関数・・・IF(論理式、真の場合、偽の場合)
論理式を満たすときは真の場合を返し、満たさない場合は偽の場合を返す

 

数式の結果を表示しないで空白にする方法(IF関数)

今回使うIF関数の内容は以下のようになります。
・論理式…指定したセルが空白ならば
・真の場合…(論理式どおりなら)セルを空白にする
・偽の場合…(論理式と違うなら)計算式をもとめる

 

では、まずE3の計算式を削除してから、IF関数を使った数式を入力してE7まで数式をコピーしましょう。

1.E3を選択し、[Delete]キーを押してデータを削除します。

img20151005_03

 

2.関数の挿入ボタン「fx」をクリックします。

img20151005_04

 

3.関数の挿入ウインドウが開くので、[関数の分類(C)]から「論理」を選び、[関数名(N)]から「IF」を選び「OK」ボタンをクリックします。

img20151005_05

 

4.「論理式」欄をクリックし、セルB3をクリックし、続けて「=””と半角入力します。

img20151005_06

img20151005_07

※「”」間に文字列を入れないことで空白になります。

 

5.「真の場合」欄をクリックし、「””と入力します。

img20151005_08

 

6.「偽の場合」欄をクリックし、残高をもとめる計算式(E2+C3-D3)を入力します。
セル[E2]クリック→「+」入力→[C3]クリック→「」入力→[D3]クリックする。(全文を直接入力しても可)
最後に「OK」ボタンをクリックします。

img20151005_09

 

数式が入力されて残高が表示されました。

img20151005_10

 

下のセルまで数式をコピーしましょう。

7.コピーするセルの右下にマウスポインターをおいて、フィルハンドル(+)をセル[E7]までドラッグします。

img20151005_11

 

項目が未入力の残高欄が、数式は入力されてますが空白になりました。

img20151005_12

 

新規入力した場合、項目と入金(出金)を入力すると残高は自動的に計算表示されます。

img20151005_13