Excelのプルダウンリストを連動＆自動反映させた

質問

Excelで、会社名をリストから入力した際に、隣りにある担当者欄に自動で担当者名が入り、複数の担当者がいる場合はリストから選択できるようにしたいのです。

それぞれ、連動させる方法や自動で反映させる方法はあるのですが、効率よくまとめたいです。
また、社名が長い所も多いので、参照元となるリストは、A列に社名B、C、D列（増減の可能性あり）に担当者名を入力する形が望ましいです。

私はExcel初級者ですので、わかりやすくお教えいただけるとありがたいです。
ちなみに、Excel2019です。

よろしくお願いします。

回答No.5

HohoPapa

添付画像のように企業と担当者の一覧があり、
Sheet1のような担当者の埋まるセルを
会社の選択に呼応して制御したいということと思います。

添付画像のように、
Sheet1のシートモジュールに

Private Sub Worksheet_Change(ByVal Target As Range)
　If Target = Range("E3") Then　　'企業名の埋まるセル
　　Call staffSet
　End If
End Sub

を配置し、

標準モジュールに、後記コードを配置する対応はいかがでしょうか？

なお、社員が1名の時は、コンボボックスとはしていません。
また、複数名の時は、コンボボックスとし、
先頭の社員を初期値としてセットしています。
これはセットしないほうがいいですか？

むろん、社員が横方向に増えても
会社の選択をし直せば、反映します。

Option Explicit

Sub staffSet()

　Dim r As Long
　Dim c As Long
　Dim wsTbl As Worksheet
　Dim wsMain As Worksheet
　Dim HitCnt As Long　'担当者の数
　Dim SelList As String
　Dim tgCell As Range

　With ThisWorkbook
　　Set wsTbl = .Sheets("企業一覧")
　　Set wsMain = .Sheets("Sheet1")
　　Set tgCell = wsMain.Cells(4, 5) '社員名をセットするセル
　End With

　r = 2 '企業一覧のデータ開始行
　c = 2 '企業一覧の社員名開始列
　HitCnt = 0
　SelList = ""

　tgCell.Validation.Delete
　tgCell.Value = ""
　Do
　　If wsTbl.Cells(r, 1).Value = "" Then Exit Do
　　If wsTbl.Cells(r, 1).Value = wsMain.Cells(3, 5).Value Then
　　　Do
　　　　If wsTbl.Cells(r, c).Value = "" Then Exit Do
　　　　HitCnt = HitCnt + 1
　　　　If HitCnt = 1 Then
　　　　　 tgCell.Value = wsTbl.Cells(r, c).Value　'社員名をセットするセル
　　　　End If
　　　　SelList = SelList & wsTbl.Cells(r, c).Value & ","
　　　　c = c + 1
　　　Loop
　　End If
　　r = r + 1
　Loop

　If HitCnt > 1 Then
　　With tgCell.Validation
　　　　.Delete
　　　　.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
　　　　xlBetween, Formula1:=SelList
　　　　.IgnoreBlank = True
　　　　.InCellDropdown = True
　　　　.InputTitle = ""
　　　　.ErrorTitle = ""
　　　　.InputMessage = ""
　　　　.ErrorMessage = ""
　　　　.IMEMode = xlIMEModeNoControl
　　　　.ShowInput = True
　　　　.ShowError = True
　　End With
　End If

End Sub

回答No.4

kkkkkm

添付画像の
A6が社名選択の入力規則のリスト。
B6が担当者選択の入力規則のリスト。
A1からD4までが参照元のリストとします。

A1からA4まで選択した状態で
数式タブの「名前の管理」の所の「選択範囲から作成」で「以下に含まれる値から名前を作成」が出たら上端行にチェックでOKします。
A社の
A2からD2まで選択した状態で
数式タブの「名前の管理」の所の「選択範囲から作成」で「以下に含まれる値から名前を作成」が出たら左端列チェックでOKします。あとはB社C社同じように各行のA列からD列まで選択して名前を作成します。

名前の管理でそれぞれ社名,A社,B社,C社ができているのを確認してください。

A6のリストで元の値を
=社名
B6のリストで元の値を
=INDIRECT(A6)

とします。
これで、リスト選択ができますが

> 担当者欄に自動で担当者名が入り、

を実現するにはVBAが必要だと思います。担当者が一人の場合は名前が表示され、複数の場合は「選択してください」と表示されます。

該当シートのシートタブを右クリックしてコードの表示で出てきた画面で
以下のコードを記載します。具体的なシート名などが不明なので添付画像の状態でのVBAです。

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address <> Range("A6").Address Then
Exit Sub
End If
Application.EnableEvents = False
Dim n As Long: n = 0

On Error Resume Next
n = WorksheetFunction.Match(Range("A6").Value, Range("A1:A4"), 0)
On Error GoTo 0

If n <> 0 Then
If Cells(n, "C").Value = "" Then
Range("B6").Value = Cells(n, "B").Value
Else
Range("B6").Value = "選択してください"
End If
End If
Range("B6").Select
Application.EnableEvents = True
End Sub

回答No.3

iyonohanamuko

以下ページの要領で対処してみてはいかがでしょうか？
https://dekiru.net/article/21850/

回答No.2

imogasi

#1です。補足していただいた、内容がよく判らない。
＞担当者が１人の会社もあるので、その会社名が入力されたときだけ自動で担当者が入力されるようにしたい…ということ
＞担当者が１人の会社もあるので
なぜこの表現になるのかわからない。
１人の場合が、むしろ簡単で、担当者がX,Yの２人いるとき、その会社をA列に、ドロップダウンで選択入力したとき、担当者が２人いるとして、B列＝ｘ,C列＝Ｙに分けて出すほうが難しい。選択できる、リストの値（の設定）は１つでしょう。
小生では、直ちには答えられない。
とりあえず文字列「”Ｘ，Ｙ“」をB列に返して、その後操作で、データー区切り位置で２列（ｂ、Ｃ列にXと、Yを）に分けるのはどうか。
関数でもB列　=MID(A1,1,FIND(",",A1)-1)と、C列　=MID(A1,FIND(",",A1)+1,LEN(A1)-FIND(",",A1))
に分けれられる、が。
全般に質問のしたいことが正確に伝わらない。
小数のデータ例で、方法は別（飛ばして）にして、結果をが、こうなるようにしたい、という書き方をせにゃ、文章だけでは紛らわしい。
色んなケース、れう外的なケース、難しそうなケース（データ例）は、別分けて、注釈で記述するとよい。

補足

いや、複数の担当者がいる会社と、一人しかいない会社があるってだけのことなんですがね・・・

伝わらないですかね。。。
残念です。

回答No.1

imogasi

初級者、というなら、仕様で欲張らないこと。
社名が（フル名では、文字が多いのでが理由か？）３列に別れる、などの対処は、聞いたことがない。非力なら、問題を複雑化しないこと。
２段の連動を、入力規則で、リストで、エクセル関数でやるのが、限度だろう。この経験はあるのかな？よく質問がでる課題だ。「エクセル　入力規則　リスト　多段」「エクセル　連動ドロップダウンリスト」などで、WEB照会したか。そこにもフル社名が、長い場合、列を前後で２列に分けるなんて記事は調べたか？ないだろう。
ーー
別途、アクセスVBAなどデータベース的にデータを扱えるなら、３段、４段連動も、そういうデータベースを作れれば、可能かなと思う。でもツリー状の親ー子ー孫ー玄孫などの編成データを、随所の段階から索引出来るようにするのは大変だ。
エクセルの勉強することの中には、具体的な方法の習得も大切だが、どの点がどうなると、「複雑化し」、難しくなる（普通の人では出来ない）のか、の判断能力を習得することが、本当は大切だと思う。具体的にやったことがなく、やれなくても、難しい課題だというのは、同部門の他の勉強をすると、わかるものだ（限界がわかるというのかな）。延長・類推が可能なのはどこまでか、と言うこと。この訓練をしないと、無駄な試行時間を費やし、仕事などではやってられないと思う。
その他の手段として、VBAが使えれば、出来ることが、広がる部分は相当あるが。
ーー
下記の表現がわかりにくい。
＞社名が長い所も多いので、参照元となるリストは、A列に社名B、C、D列（増減の可能性あり）に担当者名を入力する形が望ましいです
社名と担当者名の２列で良いのでは？社名が長い場合は、続き（残り）を何かの方法で補えればよいのかな？
その仕掛けは、良いものが思いつかないが。エクセルの仕掛けを使う、良い方法のアイデアは質問者にあるか？
宛先などで、略称は失礼と思うなら、まず略称だけで、入力規則の入力時は、取り敢えず、我慢して、別途入力完了時に、別列にフル名を引っ張ってくることを考えてはどうか。
関数ならVLOOKUPを使うとして、両者の対応表を別途作る面倒さが発生するが。
ーーー
小生の勝手なアイデア。
G1:G5　スペースより右はH列のデータとする。
A社 東京証券取引者所データ管理課有志一同代表山田課長
東京科学工業
日野運搬 株式会社　日野運搬産業労働組合労使担当課
佐藤メリヤス工場
東京都船舶安全振興協同組合
Sheet1のA1:A１０までは、データーデータの入力規則ーリストーセル範囲　G1:G5
で設定。
VBAで、Sheet1のダブルクリックイベントで
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
MsgBox Range("G1:G100").Find(Target.Value).Offset(0, 1)
End Sub
と設定。
ーー
A社を入力した、（A列の）セルをダブルクリック。
東京証券取引者所データ管理課有志一同代表山田課長
が表示されるだろう。
このフル名を表示でなく、C列にセットするのは簡単。

補足

早速のご返答ありがとうございます。
すいません、書き方が悪かったようですが、単純にA列に会社名をリストから表示させたら、B列でその会社の担当者だけをリスト表示させたいということなんです。ただ、担当者が１人の会社もあるので、その会社名が入力されたときだけ自動で担当者が入力されるようにしたい…ということなのです。
そしてその際の、リストの参照元を縦ではなく、横にしたいのです。

わかりにくくて申し訳ないですが、よろしくお願いします。