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

Question

初歩的な質問ですが。。。

あるセルに住所を入力すると、その隣のセルに支店担当者表に対応する支店と担当者の名前が入力される様にする方法を探しています。

具体的には、、、
支店担当者表は"福岡県""九州""山田"という様に、"住所（都道府県）""支店名""担当者"の順に並んでいます。
この表を利用して、"福岡県福岡市～"と入力すると自動的に隣とその隣のセルに"九州""山田"と入力すようにしたいのです。

VLOOKUP関数を使って見たのですが、"福岡県"など都道府県だけの入力ですとうまくいくのですが、"福岡県福岡市～"と県の後を入力すると#N/Aエラーが出てしまいます。
友人が言うにはLEFT関数を併用すればうまくいくとの事ですが、調べて見ても良くわかりません。

又、可能ならば住所を未入力のセルには#N/Aエラーの文字が表示されない様にしたいです。

わかり難い説明かもしれませんが、どなたか回答お願い致します。

arukamun · Accepted Answer

支店担当者表というシートの
Ａ列に都道府県名、Ｂ列に支店名、Ｃ列に担当者名だとして、
同ファイルの別シート（便宜上検索用シートとします）で、
Ａ列に住所を入力すると、Ｂ列に都道府県名、Ｂ列に支店名、Ｃ列に担当者名と表示される様に考えてみます。

検索用シートのＢ列は、Ａ列から都道府県名を抜き出す関数を考えます。

Ｂ１セルに
=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))

で、１行は出来ましたね。
必要に応じて、この行を下方向にコピーしてください。

KenKen_SP · Answer

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

参考URL：http://www.x-media.co.jp/tyoufaq/faq3_2.shtml

KenKen_SP · Answer

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

haruka1234567890 · Answer

住所から都道府県名のみを取得する方法はエクセルにおいて永遠のテーマとなっております。
ほかの方たちも書かれているように一工夫必要です。
これをエクセルで自動的に行うのは並大抵のことではありません。

----------------------------
=IF(MID(A1,4,1)="県",LEFT(A1,4),LEFT(A1,3))とかね
でもこれは、4文字目に県が来るのが神奈川、和歌山、鹿児島のみの場合しか使えません。市町村名の最初に「県」がある都道府県は使えません
----------------------------

それより入力時点で都道府県を分ける方が賢明でしょう。
そうすれば、あなたの言うとおりVLOOKUPでいけます。

anidesu · Answer

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も表示されなくなりますよ。

mshr1962 · Answer

頭から3桁目まで判定してみては？
A1が住所として
=VLOOKUP(LEFT(A1,3),範囲,列番号,検索の型)
※範囲において神奈川、和歌山、鹿児島のみ県を省いておく

>又、可能ならば住所を未入力のセルには#N/Aエラーの文字が表示されない様にしたいです。
=IF(COUNTIF(範囲の左の列,LEFT(A1,3)),"",VLOOKUP(LEFT(A1,3),範囲,列番号,検索の型))

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

支店担当者表というシートの

本当に、申し訳ないです。

こんにちは。

住所から都道府県名のみを取得する方法はエクセルにおいて永遠のテーマとなっております。

A1が住所として

この回答への補足

頭から3桁目まで判定してみては？

似たような質問が見つかりました

関連するカテゴリからQ&Aを探す

デイリーランキングこのカテゴリの人気デイリーQ&Aランキング

マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング