Excelの値取得関数について

質問

Excelで表を作成する際、コードから値を取得する方法について教えてください。
以下のようなことをやりたいです。
画像の場合、
1. 社員コードに"A001"を入力。
2. 各マスタの領域にある値を取得し、検索結果の各セルに値を表示。
　※B5,B6 の部分に値を取得する関数を設定したいです。
社員名は、C4のセルに記載したような、VLOOKUPで値を取得できました。
出身地と所属部署を取得する場合、 VLOOKUP に VLOOKUP を入れるような形で
ないと取得できないのでしょうか。
※出身地
　社員コード"A001" をキーに社員マスタから出身地"T001"を取得し、地方マスタより、"青森県"を取得したいです。
※所属部署　
　社員コード"A001" をキーに社員マスタから所属部署"S001"を取得し、部マスタより、"総務部"を取得したいです。
アドバイスをお願いします。

回答No.6

HohoPapa · ベストアンサー

・VBAを扱える
・Excelのシートに作成した表のデータをADOを使ってテーブルとして扱う
・シート上で使うユーザ定義関数としたい
・そこそこにSQLが扱える
という条件なら

添付画像で、
以下のコードにするというのはいかがでしょうか。

なお、
社員マスタにせよ、部マスターにせよ、
実際は、別シート上にあるということだろうと思いますので
const部分を含め、実環境に合わせる必要があります。

無論、コードはユニーク（重複しない）ことが条件です。

Option Explicit

'//社員名取得関数
Function GetName(SyaNum As Range) As String
　Dim cn As Object
　Dim rs As Object
　Dim wkSQL As String
　Const SyainList = "[検索$A9:D13]"

　Set cn = CreateObject("ADODB.Connection")
　Set rs = CreateObject("ADODB.Recordset")
　cn.Provider = "Microsoft.ACE.OLEDB.12.0"
　cn.Properties("Extended Properties") = "Excel 12.0;HDR=YES;IMEX=1"
　cn.Open ThisWorkbook.FullName

　wkSQL = ""
　wkSQL = wkSQL & "SELECT " & _
　　 "A.[社員コード],A.[社員マスタ] as kotae " & vbCrLf
　wkSQL = wkSQL & "FROM " & SyainList & "　A" & vbCrLf
　wkSQL = wkSQL & "WHERE " & vbCrLf
　wkSQL = wkSQL & "[社員コード] =" & "'" & SyaNum.Value & "'" & vbCrLf

　rs.Open wkSQL, cn
　GetName = rs("Kotae")

　rs.Close　　　　　 '後処理
　Set rs = Nothing
　cn.Close
　Set cn = Nothing

End Function

'//出身地取得関数
Function GetHome(SyaNum As Range) As String
　Dim cn As Object
　Dim rs As Object
　Dim wkSQL As String
　Const SyainList = "[検索$A9:D13]"
　Const TihouList = "[検索$A15:B21]"

　Set cn = CreateObject("ADODB.Connection")
　Set rs = CreateObject("ADODB.Recordset")
　cn.Provider = "Microsoft.ACE.OLEDB.12.0"
　cn.Properties("Extended Properties") = "Excel 12.0;HDR=YES;IMEX=1"
　cn.Open ThisWorkbook.FullName

　wkSQL = ""
　wkSQL = wkSQL & "SELECT " & _
　　 "A.[社員コード],A.[出身地],B.[地方名] as Kotae" & vbCrLf

　wkSQL = wkSQL & "FROM " & SyainList & "　A" & vbCrLf
　wkSQL = wkSQL & "LEFT JOIN " & TihouList & " B on A.[出身地] = B.[地方コード]" & vbCrLf
　wkSQL = wkSQL & "WHERE " & vbCrLf
　wkSQL = wkSQL & "[社員コード] =" & "'" & SyaNum.Value & "'" & vbCrLf

　rs.Open wkSQL, cn
　GetHome = rs("Kotae")

　rs.Close　　　　　 '後処理
　Set rs = Nothing
　cn.Close
　Set cn = Nothing

End Function

'//所属部署取得関数
Function GetBusyo(SyaNum As Range) As String
　Dim cn As Object
　Dim rs As Object
　Dim wkSQL As String
　Const SyainList = "[検索$A9:D13]"
　Const BusyoList = "[検索$A23:B30]"

　Set cn = CreateObject("ADODB.Connection")
　Set rs = CreateObject("ADODB.Recordset")
　cn.Provider = "Microsoft.ACE.OLEDB.12.0"
　cn.Properties("Extended Properties") = "Excel 12.0;HDR=YES;IMEX=1"
　cn.Open ThisWorkbook.FullName

　wkSQL = ""
　wkSQL = wkSQL & "SELECT " & _
　　 "A.[社員コード],A.[所属部署],B.[部名] as Kotae" & vbCrLf

　wkSQL = wkSQL & "FROM " & SyainList & "　A" & vbCrLf
　wkSQL = wkSQL & "LEFT JOIN " & BusyoList & " B on A.[所属部署] = B.[部コード]" & vbCrLf
　wkSQL = wkSQL & "WHERE " & vbCrLf
　wkSQL = wkSQL & "[社員コード] =" & "'" & SyaNum.Value & "'" & vbCrLf

　rs.Open wkSQL, cn
　GetBusyo = rs("Kotae")

　rs.Close　　　　　 '後処理
　Set rs = Nothing
　cn.Close
　Set cn = Nothing

End Function

お礼

ありがとうございます。
今回は、こういうやり方もあるのですね。
参考にさせていただきます。

回答No.7

HohoPapa

掲示するときに、半角スペース2つを全角スペース１つに置換したため、
SQL文に全角スペースが混じってしまっているようです。
以下、訂正します。

誤
　wkSQL = wkSQL & "FROM " & SyainList & "　A" & vbCrLf

正
　wkSQL = wkSQL & "FROM " & SyainList & " A" & vbCrLf

それぞれのFunctionに上記記述がありますので
合計3か所、修正が必要です。　ゴメンナサイ。

お礼

ありがとうございます。
お礼が遅くなりすみません。
いただいた情報を参考にさせていただきました。

回答No.5

chayamati

今日は、回答No.2です。
>社員マスタはコードのみで管理した方がメンテナンスしやすかと思って
なるほど、担当者が代わって業務引継ぎのときなど
社員マスタの社員マスタ名を替えるだけで、関連する表はそのまま使えますね
しかし、地方マスタ(都道府県名)、部マスタはほぼ普遍的なものですね
社員マスタの出身地、所属部署は「入力規則」を利用してはと考えますが

蛇足

地方コードの頭1桁は地域区分でしょうが、
地域区分が必要な時は地域列で対応しては
都道府県コードはJIS規格で2桁であります。

お礼

ありがとうございます。
参考にさせていただきます。
今回はサンプルとして社員名簿のようなマスタを使用させていただきましたが、実際は、製品などプロジェクトでの使用となります。

回答No.4

Chiquilin

> 出身地と所属部署を取得する場合、 VLOOKUP に VLOOKUP
> を入れるような形でないと取得できないのでしょうか。
数式でやるならそうなりますね。 Excelの関数には 複数の配列
をひとつにまとめる手段が 基本的にはないので。

Excel2013以降なら リレーションシップが構築できるので そっ
ちで繋いでから結果を返してやればできます。もしくは Power
BIを導入するか。

後はデータベースクエリ。添付画像のように全てのマスタに項
目名が入っているとして

接続文字列を

SELECT 社員コード, 社員マスタ,
地方マスタ.出身地, 部マスタ.所属部署
FROM
[Sheet1$A9:D12] As 社員マスタ,
[Sheet1$A15:B19] As 地方マスタ,
[Sheet1$A22:B25] As 部マスタ
WHERE 社員マスタ.出身地 = 地方マスタ.地方コード
AND 社員マスタ.所属部署 = 部マスタ.部コード
AND 社員コード = ?

パラメータを B2に設定

お礼

ありがとうございます。
Excelの細かい使い方がわかっておらず、すみません。
リレーションシップ はできましたが、そこから先のやり方がわからず苦戦中です。
また、データベースクエリ　もテーブルの範囲がうまく設定できませんでした。
あきらめて、VLOOKUP に VLOOKUP で対応しようかと思っています。

回答No.3

chayamati

今晩は、回答No.の続きです。
一覧表リストのプルダウンリストはデータリボンの入力規則のリストです。
社員マスタの出身地入力を地方マスタをプルダウン入力設定
【出身地入力セルを範囲選択】⇒【データリボン】⇒【データの入力規則】
⇒【データの入力規則】⇒【リスト】⇒【出身地リストを範囲選択】⇒【OK】

お礼

ありがとうございます。
いただいた情報を参考にさせていただきます。

回答No.2

chayamati

今日は
コードに拘っているのは何か理由はありますか
コードが無くてもリストがあれば
これをプルダウンリストして選択することが出来ます

添付は
社員マスタの出身地、所属部署を参照入力したものです

B4=VLOOKUP($B$2,$A$9:$D$12,2,FALSE)
B5=VLOOKUP($B$2,$A$9:$D$12,3,FALSE)
B6=VLOOKUP($B$2,$A$9:$D$12,4,FALSE)

お礼

ありがとうございます。
いただいた情報を参考にさせていただきます。

補足

chayamati さん
情報ありがとうございます。今回、質問のためにサンプルのシートを添付しましたが、実際に使用する際はマスタの値（部マスタの部署名など）が多々変更になりそうだったので、社員マスタはコードのみで管理した方がメンテナンスしやすかと思って、今回の構成にしてみました。

回答No.1

skydaddy

手元にExcelがすぐ使えないので確認していませんが、多分下記で大丈夫かと。
B5に”=VLOOKUP(VLOOKUP(B2,A9:D12,3),A13:B19,2)”
B6に”=VLOOKUP(VLOOKUP(B2,A9:D12,4),A21:B24,2)”

お考えの内側のVLOOKUPがそれぞれの社員マスタの値なのでそれを使って、再度VLOOKPUPという感じです。

お礼

VLOOKUPのVLOOKUPが使えることを確認できました。
ありがとうございました。