エクセルの関数についてです。

質問

初歩的な質問ですが。。。

あるセルに住所を入力すると、その隣のセルに支店担当者表に対応する支店と担当者の名前が入力される様にする方法を探しています。

具体的には、、、
支店担当者表は"福岡県""九州""山田"という様に、"住所（都道府県）""支店名""担当者"の順に並んでいます。
この表を利用して、"福岡県福岡市～"と入力すると自動的に隣とその隣のセルに"九州""山田"と入力すようにしたいのです。

VLOOKUP関数を使って見たのですが、"福岡県"など都道府県だけの入力ですとうまくいくのですが、"福岡県福岡市～"と県の後を入力すると#N/Aエラーが出てしまいます。
友人が言うにはLEFT関数を併用すればうまくいくとの事ですが、調べて見ても良くわかりません。

又、可能ならば住所を未入力のセルには#N/Aエラーの文字が表示されない様にしたいです。

わかり難い説明かもしれませんが、どなたか回答お願い致します。

回答No.4

arukamun · ベストアンサー

支店担当者表というシートの
Ａ列に都道府県名、Ｂ列に支店名、Ｃ列に担当者名だとして、
同ファイルの別シート（便宜上検索用シートとします）で、
Ａ列に住所を入力すると、Ｂ列に都道府県名、Ｂ列に支店名、Ｃ列に担当者名と表示される様に考えてみます。

検索用シートのＢ列は、Ａ列から都道府県名を抜き出す関数を考えます。

Ｂ１セルに
=LEFT(A1,IF(AND(LEN(A1)>2,ISNUMBER(FIND(MID(A1,3,1),"都道府県"))),3,IF(AND(LEN(A1)>3,ISNUMBER(FIND(MID(A1,4,1),"県"))),4,0)))

これでコピペしてください。
これで都道府県名を取り出せると思いますので、これを使ってVLOOKUPしましょう。

Ｃ１セルは
=IF(ISERROR(VLOOKUP(B1,支店担当者表!A:C,2,FALSE)),"",VLOOKUP(B1,支店担当者表!A:C,2,FALSE))

Ｄ１セルは
=IF(ISERROR(VLOOKUP(B1,支店担当者表!A:C,3,FALSE)),"",VLOOKUP(B1,支店担当者表!A:C,3,FALSE))

で、１行は出来ましたね。
必要に応じて、この行を下方向にコピーしてください。

お礼

素早い回答ありがとうございました。

皆様の回答を参考に
IF、ISERROR、VLOOKUP、LEFT関数を使用したところ
うまくできました。

又、今回使用しなかった関数も勉強になりました。
ありがとうございました！！

回答No.6

KenKen_SP

本当に、申し訳ないです。（TT)
参考URL記載もれでした。

参考URL：

http://www.x-media.co.jp/tyoufaq/faq3_2.shtml

お礼

ご丁寧にありがとうございます。
URLお気に入りに登録いたしました！！

回答No.5

KenKen_SP

こんにちは。
マクロ使用の是非はともかく、解決方法のひとつとしてご参考になれば、と思います。

1.　都道府県の抽出にはユーザー定義関数を用いる
2.　VLOOKUP関数のエラー値の非表示について
　　--＞条件付書式にISERROR関数を用いて、表面上見えなくする

＜前提＞
下にある、ユーザー定義関数 GetPrefName を標準モジュールに貼付け
シート[データ]　A列：住所 B列：都道府県 C列：支店名 D列：担当者
シート[支店担当者表]　A列：都道府県 B列：支店名 C列：担当者

＜具体的な方法＞
シート[データ]の各セルに次の関数式を記入します。
B1セル ＝GetPrefName($A1)
C1セル ＝VLOOKUP($B1,支店担当者表!$A:$C,2,FALSE))
D1セル ＝VLOOKUP($B1,支店担当者表!$A:$C,3,FALSE))

＜エラー値の非表示＞
条件付書式の数式にISERROR関数を用いて、背景色と文字色を同じ色にすることで、表面上見えなくします。参考URLをご覧ください。
IF関数とISERROR関数の組み合わせでも非表示とすることはできますが、計算式が複雑になるのでお勧めしません。

以下、ユーザー定義関数。

'*****************************************************************
' 都道府県抽出
'*****************************************************************
Function GetPrefName(住所文字列 As String)

　　Dim strPref As String
　　Dim N As Long

　　strPref = Left(Trim(住所文字列), 3)
　　Select Case strPref
　　　　Case Is = "東京都", "北海道", "大阪府", "京都府"
　　　　　　N = 3
　　　　Case Else
　　　　　　N = InStr(Left(住所文字列, 4), "県")
　　End Select

　　'都道府県が検出されない場合データ不足エラーとして#VALUE!を返す
　　If N > 0 Then
　　　　GetPrefName = Left(住所文字列, N)
　　Else
　　　　GetPrefName = CVErr(3) '3:#VALUE!
　　End If

End Function

回答No.3

haruka1234567890

住所から都道府県名のみを取得する方法はエクセルにおいて永遠のテーマとなっております。
ほかの方たちも書かれているように一工夫必要です。
これをエクセルで自動的に行うのは並大抵のことではありません。

----------------------------
=IF(MID(A1,4,1)="県",LEFT(A1,4),LEFT(A1,3))とかね
でもこれは、4文字目に県が来るのが神奈川、和歌山、鹿児島のみの場合しか使えません。市町村名の最初に「県」がある都道府県は使えません
----------------------------

それより入力時点で都道府県を分ける方が賢明でしょう。
そうすれば、あなたの言うとおりVLOOKUPでいけます。

お礼

素早い回答ありがとうございました。

皆様の回答を参考に
IF、ISERROR、VLOOKUP、LEFT関数を使用したところ
うまくできました。

又、今回使用しなかった関数も勉強になりました。
ありがとうございました！！

回答No.2

anidesu

A1が住所として
IF関数、LEFT関数、VLOOKUP関数を使えば上手くできると思います。
例）=IF(A1=0,,VLOOKUP(LEFT(A1,3),$H$1:$J$5,2,FALSE))

（説明）
もしもA5が0ならば0を、0でなければ、VLOOKUPの結果を表示する。
VLOOKUPの検索値はA5の左から3番目までの文字にする
都道府県レベルが全国区の場合、3文字じゃだめなものもありますので、完全ではありません。

一番手っ取り早いのは郵便番号で管理するのが望ましいです。
郵便番号のデータは無いのでしょうか？

例えば…
(1)郵便番号、(2)都道府県、(3)市区名、(4)町名、(5)支店名、(6)担当者名
のテーブルを作っておけば、郵便番号さえわかれば、簡単なVLOOKUPで
検索が可能です。

未入力欄の非表示は上記ようにIF関数で解決できると思います。
上記の式では、未入力欄に「0」が表示されますが、
ツール→オプション→表示のゼロ値(Z)のチェックをはずしてやると0も表示されなくなりますよ。

補足

素早い回答ありがとうございました。

皆様の回答を参考に
IF、ISERROR、VLOOKUP、LEFT関数を使用したところ
うまくできました。

今回使用しなかった関数も勉強になりました。
ありがとうございました！！

回答No.1

mshr1962

頭から3桁目まで判定してみては？
A1が住所として
=VLOOKUP(LEFT(A1,3),範囲,列番号,検索の型)
※範囲において神奈川、和歌山、鹿児島のみ県を省いておく

>又、可能ならば住所を未入力のセルには#N/Aエラーの文字が表示されない様にしたいです。
=IF(COUNTIF(範囲の左の列,LEFT(A1,3)),"",VLOOKUP(LEFT(A1,3),範囲,列番号,検索の型))

お礼

素早い回答ありがとうございました。

皆様の回答を参考に
IF、ISERROR、VLOOKUP、LEFT関数を使用したところ
うまくできました。

又、今回使用しなかった関数も勉強になりました。
ありがとうございました！！