Accessクエリで文字列を分割 住所から県名を取り出す

アクセス担当のまみです。

住所から都道府県名だけを取り出したいとか、市町村名以下を取り出したいって時がありませんか?

 

データ量が多いと手作業ではとっても大変です。

そんな時には関数が活躍してくれます。

 

1つの関数では取り出せないのですが、いくつかの関数を組み合わせることで住所を都道府県名と市町村以下に分割できます。

 

住所から都道府県名を取り出す
(都道府県名が入力されていることが条件)

 

使用するテーブル

「T_住所録」

 

方法1

 

(はてな君)
住所から都道府県名を取り出すのって先頭からだからLeft関数が使えるのかなって思ったんだけど、都道府県名って3文字だったり4文字だったりして、文字数がいつも同じじゃないんだけど・・・

 

構文
Left(文字列, 文字数)

 

文字列を指定して、何番目まで取り出すかを数値で指定することで、左から文字を取り出してくれます。

 

【Access】Left関数で左から文字を抽出する

2018年12月12日

 

(あいちゃん)
都道府県名が4文字になるのは「神奈川県」「和歌山県」「鹿児島県」の3つだけであとは全部3文字なんだよ。だから、先頭から4番目の文字を取り出して「県」かどうかを調べるんだよ!
(はてな君)
なるほど~。4番目の文字を調べるといいのか~。
ということはMid関数を使うんだね。

 

構文
Mid(文字列, 開始位置[,文字数])
[ ]内は省略可能です。省略すると、開始位置より後の文字列すべてが返されます。

 

【Access】Mid関数で開始位置を指定して文字列を抽出する

2018年12月15日

 

先頭から4番目の文字は「Mid([住所],4,1)」で取り出せます。

 

(はてな君)
4番目の文字が「県」でそれ以外だったら3文字取り出したいんだから・・・
Left([住所],IIf(Mid([住所],4,1)=”県”,4,3))でどう?

 

(あいちゃん)
そうそう。IIf関数を使うと条件判断してくれるよね。

 

構文
IIf(条件式、真の場合、偽の場合)

Accessクエリの抽出条件でIIf関数を使用する方法

2018年11月20日

 

ではさっそくクエリで住所から都道府県名を取り出してみましょう。

 

リボンの①「作成」タブから②「クエリデザイン」を選択し、「T_住所録」を追加します。

 

 

フィールドに下記を入力します。

Left都道府県: Left([住所],IIf(Mid([住所],4,1)=”県”,4,3))

Mid都道府県: Mid([住所],1,IIf(Mid([住所],4,1)=”県”,4,3))

 

(はてな君)
Mid関数だけでもやってみたよ!
先頭から取り出すから開始位置は1だね!
(あいちゃん)
はてな君すごいね!Mid関数だけでもやってみたんだね。
デザインビューで確認てみてね!

 

クエリの結果

クエリには「Q_県名IIf」と名前をつけておきましょう。

 

方法2

 

(あいちゃん)
Mid([住所],4,1)=”県”であてはまる場合は「-1」あてはまらない場合は「0」を返してくれるんだ
(はてな君)
へ~そうなんだ。やってみるね。

 

フィールドに「文字数: Mid([住所],4,1)=”県”」と入力します。

クエリの結果

(はてな君)
てことは「3-Mid([住所],4,1)=”県”」で都道府県名の文字数がわかるんだね!

 

フィールドに下記を入力します。

Left都道府県: Left([住所],3-(Mid([住所],4,1)=”県”))

Mid都道府県: Mid([住所],1,3-(Mid([住所],4,1)=”県”))

 

(あいちゃん)
大正解!
デザインビューで確認してみてね!

 

クエリの結果

クエリには「Q_県名」と名前をつけておきましょう。

 

市町村以下を取り出す
(都道府県名が入力されていることが条件)

 

方法1

 

(はてな君)
じゃあ市町村以下を取り出すにはどうすればいいの?
(あいちゃん)
取り出した都道府県名を空白に置き換えればいいんだよ。
(はてな君)
あ~空白に置き換えるんだぁ。じゃあReplace関数を使うんだね!

 

構文
Replace( expression, find, replace [, start ] [, count ] [, compare ] )

最初の3つまでは必須、[ ]で囲ってある残りの3つは省略可能です。

 

【Access】Replace関数を使ってデータを置き換える方法

2018年11月26日

 

(あいちゃん)
じゃあさっそくクエリでやってみよう!
都道府県名を取り出す方法はどれでも好きなのを使ってね!

 

フィールドに下記を入力します。

都道府県: Left([住所],IIf(Mid([住所],4,1)=”県”,4,3))

市町村以下: Replace([住所],[都道府県],” “,1,1)

 

引数は省略可能なので、Replace([住所],[都道府県],” “)でも同じ結果になります。

 

クエリの結果

クエリは「Q_市町村」と名前をつけておきましょう。

 

方法2

 

都道府県名の文字数に1を足した数で住所から市町村名以下を取り出せます。

(はてな君)
てことは「3-(Mid([住所],4,1)=”県”)」に1を足して使えばいいんだよね。
(あいちゃん)
もちろんそれでもできるんだけど、都道府県名を既に取り出しているからLen関数を使って文字数を調べるんだよ。

 

構文
Len(文字列)

指定した文字列の文字数を教えてくれます。

 

(はてな君)
じゃぁ「Len([都道府県]+1)」てことだね!
既に取り出してある都道府県名を使うから文字数がわかるのかぁ。なるほど~。

 

フィールドに下記を入力します。

都道府県: Left([住所],IIf(Mid([住所],4,1)=”県”,4,3))

市町村以下: Mid([住所],Len([都道府県])+1)

 

クエリの結果

クエリには「Q_市町村Len」と名前をつけておきましょう。

 

都道府県名が入力されていない場合の分割方法

 

これまでご紹介してきた方法は、都道府県名が入力されていないと使えないのですが、都道府県名が入力されていないデータが混じっていても、住所を分割する方法があります。

(あいちゃん)
少し式が複雑になるからちょっと難しいかな。

先頭から3番目の文字が「都道府県」のどれかなら3文字取り出す、先頭から4番目の文字が「県」なら4文字取り出す、それ以外なら空白になるようにってできれば空白が混じっていても都道府県名が取り出せます。

 

(あいちゃん)
条件判断にSwitch関数を使うといいかな?IIf関数でもできるけど。

 

構文
Switch(条件1,値1[,条件2,値2,・・・・・,条件n,値n])

条件にあてはまればその条件に対応する値を返してくれます。

 

Access Switch関数で複数条件を判断する方法

2018年12月23日

 

フィールドに下記を入力します。

都道府県: Switch(Mid([住所],3,1) In (“都”,”道”,”府”,”県”),Left([住所],3),Mid([住所],4,1)=”県”,Left([住所],4))

市町村以下: Replace([住所],Nz([都道府県]),” “)

 

クエリの結果

 

(はてな君)
そっか。市町村以下は都道府県名が空欄のこともあるからNz関数を使うんだね。
構文
Nz(データ,代替値)

データがある場合はデータを返してくれて、Nullの場合は代替値を返してくれます。

【Access】 Nz関数で空白を上手に扱う方法

2018年11月22日

 

(あいちゃん)
ちなみに都道府県名をIIfで書きかえられそう?
(はてな君)
え~っと頑張てみる。

 

都道府県: IIf(Mid([住所],3,1) In (“都”,”道”,”府”,”県”),Left([住所],3),IIf(Mid([住所],4,1)=”県”,Left([住所],4)))

(あいちゃん)
ピンポーン!よく頑張りました!
(はてな君)
ちょっと難しいけど都道府県名に空白が混じっていても住所が分割できるんだね。

クエリは「Q_空白対応」と名前をつけておきましょう。

 

まとめ

住所を都道府県名と市町村以下に分割したいって結構使用頻度が高いです。

色々な方法があるので、お好きな方法で活用してくださいね!