Excelにてプルダウンの情報を参照しデータ挿入

質問

こちらではいつも大変お世話になっております。
よろしければまたご教授いただけると幸いです。
現在便利なシフト表を作成するため、VBAやマクロを勉強しながら作成しています。
実現したい機能としましては、プルダウンから店舗名を作成し、選択した店舗に所属する社員名を
セルに表示したいと考えております。
店舗名と社員名との結びつけ？を行い、参照したものを表示するという所でとても悩んでおります。
別シートに店舗情報や、社員情報を記載したものを作成したのですが、ここからどのように進めていけばよいかわからずつまずいております。
そもそも別シートに情報を起き、参照させるというような方法でこの機能は実現できるのでしょうか？
ご教授の程よろしくお願いいたします。
添付の画像は、実現したい機能のイメージになります

回答No.3

kagakusuki

失礼ながら、御質問欄に添付されている画像の例では、毎日同じ店員の名前が表示されていて、シフトは行っておられない様に見えるのですが、それでも宜しいのでしょうか？
　もし、シフトを行うのだとしますと、日によって（列によって）表示する店員の名前を変えなくてはなりませんが、その方法を回答するためには、どの様なルールで各日付ごとに表示させる店員を決めるのか、という情報が必要になります。
　ですから、シフト表とするのでしたら、その日に担当する店員を、どの様な決め方で選ぶのかを御教え願います。

　尚、もし、日が変わっても、表示する店員の名前を変えなくとも良いのでしたら、以下の様な2通りの方法が御座います。

【方法その1】VLOOKUP関数を使う方法
　今仮に、Sheet1のA1セルに、店舗名をドロップダウンリストで入力するものとします。
　又、下の添付画像の上の表の様に、Sheet2のA2～A7に店舗名を入力しておき、Sheet2のB列～G列に各店舗ごとの店員の名前を入力した表を作成しておくものとします。
　その場合、まず、Sheet1のA1セルに、ドロップダウンリストを設定する際には、「元の値」欄に

=INDIRECT("Sheet2!A2:A7")

と入力して下さい。
　尚、質問者様が御使いになられているExcelのバージョンがExcel2007以降のものである場合には、「元の値」欄で指定する際に、

=Sheet2!$A$2:$A$7

とする事も出来ます。
　次に、1人目の店員名を表示させるセルに次の関数を入力して下さい。

=IF(ISERROR(1/(VLOOKUP($A$1,Sheet2!$A:$G,ROWS($3:3)+1,FALSE)<>"")),"",VLOOKUP($A$1,Sheet2!$A:$G,ROWS($3:3)+1,FALSE))

　そして、「1人目の店員名を表示させるセル」をコピーして、2人目以降の店員名を表示させるセル範囲に貼り付けて下さい。

【方法その2】HLOOKUP関数を使う方法
　今仮に、Sheet1のA1セルに、店舗名をドロップダウンリストで入力するものとします。
　又、下の添付画像の下の表の様に、Sheet2のB1～G1に店舗名を入力しておき、Sheet2の2行目～7行目に各店舗ごとの店員の名前を入力した表を作成しておくものとします。
　その場合、まず、Sheet1のA1セルに、ドロップダウンリストを設定する際には、「元の値」欄に

=INDIRECT("Sheet2!B1:G1")

と入力して下さい。
　尚、質問者様が御使いになられているExcelのバージョンがExcel2007以降のものである場合には、「元の値」欄で指定する際に、

=Sheet2!$B$1:$G$1

とする事も出来ます。
　次に、1人目の店員名を表示させるセルに次の関数を入力して下さい。

=IF(ISERROR(1/(HLOOKUP($A$1,Sheet2!$1:$7,ROWS($3:3)+1,FALSE)<>"")),"",HLOOKUP($A$1,Sheet2!$1:$7,ROWS($3:3)+1,FALSE))

　そして、「1人目の店員名を表示させるセル」をコピーして、2人目以降の店員名を表示させるセル範囲に貼り付けて下さい。

回答No.2

KURUMITO

各店舗のシフト表といっても各店舗の名前の付いたシートについて４月なら4月のシフト表が載っているのですか？そうであればお求めのシートではA1セルにはシート名と同じ店舗名をプルダウンリストから表示させます。
A2セルには次の式を入力してG2セルまでドラッグコピーしたのちに下方にもドラッグコピーします。

=IF(INDEX(INDIRECT($A$1&"!A:G"),ROW(A1),COLUMN(A1))="","",INDEX(INDIRECT($A$1&"!A:G"),ROW(A1),COLUMN(A1)))

これでA1セルで選ばれたシートがそのまま表示されることになります。

回答No.1

keithin

例えば基本的な手順としては。

準備：
シート２の１行目に店舗名を列記
２行目以下に各店舗の社員名を列記
シート１のＡ１セルに入力規則で店舗名を選択できるように準備

手順：
シート１のＡ４セルに
=IF($A$1="","",HLOOKUP(Sheet2!$A:$Z,$A$1,ROW(A2),FALSE)&"")
と記入し，下向けに（一日分のセル数）コピー

その１日分をヒトカタマリにしてコピー，毎日の欄に貼り付けて完成。

それで。
数式じゃ困る生データを記入しておきたい，という事で特に困っているご相談なら，同じ考え方でマクロに生データを記入させます。

準備：
シート２の１行目に店舗名を列記
シート２の２行目以下に各店舗の社員名を列記（最大６名）
シート１のＡ１セルに入力規則で店舗名を選択できるように準備
シート１のＡ４：Ａ９，Ｂ４：Ｂ９…，Ａ１３：Ａ１８，Ｂ１３：Ｂ１８…に転記したい

手順：
シート１のシート名タブを右クリックしてコードの表示を開始
現れたシートに下記をコピー貼り付ける

private sub worksheet_change(byval Target as excel.range)
　dim res as range
　if target.address <> "$A$1" then exit sub
　range("A4:Z9,A13:Z18").clearcontents
　if target = "" then exit sub

　set res = worksheets("Sheet2").range("1:1").find(what:=target, lookin:=xlvalues, lookat:=xlwhole)
　if res is nothing then exit sub
　res.offset(1).resize(6,1).copy range("A4:Z9,A13:Z18")
end sub

ファイルメニューから終了してエクセルに戻り，Ａ１セルを書き換えると自動でコピーしてきます。