こんにちはゲストさん。会員登録(無料)して質問・回答してみよう!

回答受付中の質問

Excelの値取得関数について

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

投稿日時 - 2018-07-10 13:23:17

QNo.9516971

困ってます

このQ&Aは役に立ちましたか?

0人が「このQ&Aが役に立った」と投票しています

回答(7)

ANo.7

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


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


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

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

投稿日時 - 2018-07-17 22:55:36

ANo.6

・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

投稿日時 - 2018-07-13 09:38:31

お礼

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

投稿日時 - 2018-07-18 16:05:02

ANo.5

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

蛇足

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

投稿日時 - 2018-07-12 11:45:55

お礼

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

投稿日時 - 2018-07-18 16:03:35

ANo.4

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

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

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

接続文字列を

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

パラメータを B2に設定

投稿日時 - 2018-07-11 13:54:33

お礼

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

投稿日時 - 2018-07-11 19:02:56

ANo.3

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

投稿日時 - 2018-07-10 19:50:41

ANo.2

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

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

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)

投稿日時 - 2018-07-10 18:36:52

補足

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

投稿日時 - 2018-07-10 20:05:34

ANo.1

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

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

投稿日時 - 2018-07-10 13:49:44

お礼

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

投稿日時 - 2018-07-18 16:00:30