エクセルで2段階でリストからの選択入力したい

質問

エクセル2013で添付のようにSheet1のB列に顧客名、D列からR列まで行方向にその顧客の製品名が入力された表（顧客リスト）から、Sheet2の入力表のE列でリストから社名を選択したら、その社名に対応する製品名をF列でリストから選択入力できるようにしたいのです。
最初はINDEX関数とMACH関数の組み合わせで何とかと思ったのですが、名前の定義を使えば社名を選択したら直接製品名が選択できるように出来ると思ったのですが顧客が増えてきたら、不要の顧客名を削除したり、上書きしたらリストが崩れることに気づきました。
（顧客リストは毎月入力時に上書きしないとリストが長くなる一方で、リストから選択するより直接入力する方が簡単になってしまいます）
上記の問題を解消して、顧客リストを上書き、削除しても正しく選択できるようにならないでしょうか？
最初の思惑と違ってずいぶんハードルの高い内容になってしまい、回答いただいても当方に使えるかどうかも自信がなくなっていますがよろしくお願いします。

回答No.1

SI299792 · ベストアンサー

なんか見たことあると思ったら前の質問はこの為だったのですか。
42行目から始まっているので、顧客名　E42、 製品名 F42　とします。

E42 のデータの入力リストを
=顧客リスト!$B$5:$B$36
F42 のデータの入力リストを
=OFFSET(Sheet1!$D$4,MATCH(E42,Sheet1!$B$5:$B$36,0),0,1,15)
にします。

名前の設定はいりません。
最初からこう質問すればいいのに。

参考URL：

https://okwave.jp/qa/q9576232.html

お礼

＞名前の設定はいりません。
＞最初からこう質問すればいいのに。

これが思いつかないレベルなのが悲しいかな実力です。
OFFSETとMATCH関数で出来るとは。
知識の無さからご回答だけからだと検証方法が分からず大変失礼しました。＃３さんの説明で理解しました。

一番最初のご回答であったこととお詫びの意味でBSにさせていただきますのでご容赦！

補足

早々のご回答ありがとうございます。
ご回答だと社名が決まれば製品名が自動で一意に決定されるような気がするのですが。
A社には色んな製品（A-1、A-2、・・・）があるので、それを製品名の列（E列）で選択できるようにしたいのですが。

有るセルで社名を選択したらその横のセルでその社名に対応した複数の製品名をリストから選択できるようにするのが目的です。

もし当方の大勘違いならご容赦ください。

回答No.7

imogasi

質問者には無理でも、WEB照会などで、このテーマの疑問を持つ人用に、
名前定義による方法をVBAでやってみました。
WEBなどの記事を寄せ集めて、VBAでやってみました。
VBAでやれば、この程度の行数になるという、目安です。
シートやデータ例は私の前回答通りのものを使っています。
ーー
標準モジュールに
Sub test01()
Set sh = Worksheets("顧客リスト")
lr = sh.Range("B10000").End(xlUp).Row
'MsgBox lr
For i = 3 To lr
lc = sh.Cells(i, 1000).End(xlToLeft).Column
'MsgBox lc
sh.Range(sh.Cells(i, 4), sh.Cells(i, lc)).Name = sh.Cells(i, "B").Value
Next i
End Sub
これを実行すると、顧客リストのB列から、会社名の名称の、名前定義を自動で作れます。
ーーー
２回目以後の実行時には、名前定義の全部を一旦抹消をした後、前記Test01を実行することになるでしょう。ご破算をする。
Sub DeleteDefinedNames()
Dim n As Name
For Each n In ActiveWorkbook.Names
n.Delete
Next
End Sub
手操作でもできるでしょうが。
ーー
最後に、シートに、入力規則を、シート「入力表」のE列に設定します。
見ている会社名の列は、C列です。C列に会社名を入力後に操作すると、その会社の製品名がリストに出ます。
E2の１セルしか設定しておりませんが、＋インジケータをだし、下方向に引っ張れば（関数の式複写と同じ要領）、E10までもそれより下まででも、入力規則が設定できます。
Sub Sample1()
'入力表のE列に、入力規則ーリストを設定
'C列に会社名入力後にE列をクリックし▼をクリックして、リストから選択
Set sh = Worksheets("入力表")
With Range("E2:E10").Validation
.Delete
.Add Type:=xlValidateList, _
AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween, _
Formula1:="=IF(C2="""",C2,INDIRECT(C2))"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.IMEMode = xlIMEModeNoControl
.ShowInput = True
.ShowError = True
End With
End Sub
簡単なテスト済みです。
==
質問者に言いたいのは、難しいのは、いろんな時点（日次、月次、随時）で
、Updatingな、顧客リストのデータを、作ることです。これは多分VBAや、他のデータベースソフトの使いこなしができないと、機動的にできないでしょう。
　それらができないなら、当面はこの課題は、独力では無理でしょう。

お礼

ご指導を含め関数、マクロでの回答ありがとうございました。
思った以上にレベルの高い要求だったことが分かりました。

申し訳ありませんが、BSは１番早く、関数での回答を下さり、しかも当方の無知さから検証の仕方が分からず失礼してしまった、＃１さんに（お詫びも含めて）させていただきます。

補足

はい、独力では無理なことが良く分かりました。
今回は教えていただいた、それもなじみのある関数で出来ることが分かりましたのでご回答のマクロの検証は申し訳ありませんが遠慮させていただきます。

当方の場合、こんなに長いコードが1発で動くことは無いのと、マクロでの解決コードも前出の通り頂いていますので。

回答No.6

HohoPapa

>E列を変えるとエラーが出るので、もう少しなら今少しご指導お願いします。
もう少しと思いますし、
このVBAでのアプローチは間違っていないと思います。

まず、
>>「コンパイルエラー：定数式が必要です」と出ました
このエラーが？？？ではあるんですが、ともあれ、
シート名が
一方が「顧客リスト」、他方が「入力表」になっていること

続いて
入力表シートのＥ列が顧客名の列であること
入力表シートのＦ列が入力候補群をセットする列であること
を確認してください。

私の提示したコードを、単にコピペしているんですよね？

それでも解決できない場合は
シートの1枚目が「顧客リスト」、2枚目が「入力表」であることを確認し
以下のコードに置き換えて試してみてください。

■以下を入力表シートオブジェクトに

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
　If Target.Cells.Count > 1 Then Exit Sub
　If ((Target.Row > 11) And (Target.Column = 5)) Then　'12行目以下、5列目なら
　　SelListSet Target.Offset(0, 1), Target.Value　　　'右側のセルにセット
　End If
End Sub

■以下を標準モジュールに

Option Explicit

'//選択候補をセットするサブルーチン
Sub SelListSet(tgRange As Range, Companyname As String)

　Dim RowCount As Long
　Dim SelList As String
　Dim ColCount As Long

　SelList = ""
　RowCount = 5

　With ThisWorkbook.Sheets(1)
　　Do
　　　If .Cells(RowCount, 2).Value = "" Then Exit Do
　　　If .Cells(RowCount, 2).Value = Companyname Then
　　　　SelList = .Cells(RowCount, 4).Value　　　'D列
　　　　For ColCount = 5 To 18　　　　　　　　　'E列～R列
　　　　　SelList = SelList & "," & _
　　　　　　.Cells(RowCount, ColCount).Value
　　　　Next ColCount
　　　End If
　　　RowCount = RowCount + 1
　　Loop
　End With

　If SelList = "" Then Exit Sub
　With tgRange.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 Sub

お礼

思った以上にレベルの高い要求だったことが分かりました。

申し訳ありませんが、BSは１番早く、関数での回答を下さり、しかも当方の無知さから検証の仕方が分からず失礼してしまった、＃１さんに（お詫びも含めて）させていただきます。

補足

ホント~に何度もの対応に感謝です。

先ずは結果報告。
2番目のコードであっさり動きました。本当に最初の一発目で成功！！
ちなみに最初のコードを再度試してみましたがやはりダメでした。

最初と2番目をワードにコピペして比較してみましたが違っている個所は分かりましたが当方のどこに原因があったのかは？？？でした。（当然）
HopePapaさんには当方のシートの状況が推測できるのにビックリです。

回答No.5

bunjii

データタブのデータの入力規則でリストを選ぶ方法のことですよね？
リストの元の値に次の数式を設定すればVBAを使う必要はないと思います。
自分でコードを組めない者が安易に他人が組んだコードで運用すると仕様変更で躓きます。
組み込み関数の数式であれば理解し易いかと思います。
=OFFSET(Sheet1!$A$1,MATCH(D42,Sheet1!$B:$B,0)-1,3,1,COUNTIF(OFFSET(Sheet1!$A$1,MATCH(D42,Sheet1!$B:$B,0)-1,3,1,15),"*"))
Excel 2010で検証した結果の画像を添付します。（他のバージョンでも可）

お礼

いつも適切なアドバイスとご回答ありがとうございます。

思った以上にレベルの高い要求だったことが分かりました。

申し訳ありませんが、BSは１番早く、関数での回答を下さり、しかも当方の無知さから検証の仕方が分からず失礼してしまった、＃１さんに（お詫びも含めて）させていただきます。

補足

いつもお世話になります。
当方もできるだけ関数で対応したいのですが複数の関数を組み合わせが思うようにできないのが悲しい現実といったところ。

ご回答の関数は試して見ましたが、不思議なことに選択リストが部分にうまく出ないところ(会社名）があります。
会社名によっては選択肢が1つしか出ないものがあります。
ちゃんと表示される会社もあるので、原因は当方にあることは間違いないのですが・・・

原因はリストの製品名が数字だったところでした。
ということで数字を文字に変えればうまく動きました。

回答No.4

msMike

》 Sheet2の入力表のE列でリストから社名を選択…、…製品名をF列で…
上の記述から、「入力表」が「Sheet2」にあることは分かるけど、「社名」「製品名」がそれぞれ「E列」「F列」であることが、添付図に示されていますか？
チコちゃんに叱られないように説明してネっ！

お礼

ご指摘ありがとうございます。
ご指摘の通り、下の添付図は上部をカットしたので列名が表示されておらず、上の図と重ねると列名がずれている（誤解される）ことに気付きました。

しかし、さすがにご回答者様は皆様当方の不備に惑わされることなく（エクセル初心者になれておられ）正確に列を特定されていることに関心しました。

回答No.3

imogasi

>最初の思惑と違ってずいぶんハードルの高い内容になってしまい
そうだろうと思う。この課題は質問コーナーの課題としては、時々出てきて、有名パターンだが、市販のエクセル関数の解説書で探しても、解説がなかなかなく、あってもどこかが見つけにくいだろう。
ーー
もともとVBAや、アクセス（VBA）でフォームなどにリストボックスやコンボボックスを使って、絞った候補を出すもの。
プログラムやデータベースの知識が背景として必要。プロなら商品ソフトの中で必要とは思う。素人でも、出来ないかなと思っちゃうものだが。
エクセルの関数では、やや凝った式になる。
関数の仕組を会得してしまえば、それほどでもない。
MATCH関数で該当行を探し、列「数」を与えて、そのセル範囲をエクセルの入力規則の設定時に与えてやるもの。今回は列数は固定で説明したが、実際数で列幅を出せという要求もありうる。先般はCOUNTA関数を使って、参照するデータ数を決める回答をしたことがある。
今回は、横（列方向に）に製品名のバりエーションが与えられるが、市販本やWEB解説では、基本タイプは、行方向に、バりエーションを置くものが多いかと思う。
エクセルでは、行と列は交換可能とはいかないケースがあるので、表設計の時から注意が必要。
ーー
質問の表現によくわからないところがある。
＞顧客リストを上書き、削除しても正しく選択できるようにならないでしょうか？　など。
＞記述と画像が一致してない説明箇所があるのでは。
ーー
その前に、＃１のご回答が出てしまっているが、画像にできるだけ忠実にして、少し説明文を増やして書いておく。
シート（のシート名）は顧客リストと入力表の２枚。
顧客リスト　
データ例　画像に倣って、C列飛ばし。
B3：J8
A社 A１ A２ A３ A４ A５
B社 B1 B2 B3
C社 C1 C2 C3 C4
D社 D1 D2
E社 E1 E2 E3 E4 E5 E6 E7
F社 F1 F2 F3 F4 F5 F6 F7
入力表
データ例　A,B列はデータ入力省略。
下記はC,D列
入力規則はD列（製品名の列）に設定。
日 出勤者数 顧客名 製品名
E社 E6
A社 A２
B社 B1
A社 A３
F社 F4
D社 D1
・操作
入力表　D2:D100範囲指定　１００は適宜設定。
データー入力規則ーリストー下記式を入力ボックスに入れてーOK
・利用
D２以下のセルでセルをクリックー▼をクリック
同行C列が、B社なら、B1-B３がリストに出る。
その中からクリックして選択。D列のその行にB1とかが入る。
入力表
製品名の式
=OFFSET(顧客リスト!$C$3,MATCH($C2,顧客リスト!$B$3:$B100,0)-1,0,1,9)
ーー
＃１などのご回答に、出来るだけ早く、それでうまく行くのか、そうでないのか反応してほしい。
数日前の２段階リストに関する似た質問（別人の質問かも）などは、反応があったのか不明。
これだけの解説でも非常に時間をとる。だったら回答するな
という莫れ。

お礼

ご回答ありがとうございました。
解説の内容が50%程度しか理解できないレベルながら、テストシートを指定の通り作成してやってみたら、なんと希望通りの動作が確認できました。
上書きすればちゃんとその内容で更新されて選択肢が現れることも確認しました。

＃１さんのご回答も同じ内容であることが、imogashiさんの解説で理解できました。
＃１さんの回答では、どこをどうするのかが分からず、会社名と製品名が一対一の式の感じがしましたので、「大勘違い」でした。（#No1さん、ごめんなさい）
つまり、上記解説は有用だったので、時間をとっていただいた甲斐があったということでご容赦のほど。

回答No.2

HohoPapa

シート構成、使い方から判断して、
セル範囲の範囲名を使うよりも、
入力表のＥ列が変化したタイミングで、
その右隣りのセルの入力候補群に期待の一覧をセットするほうが
わかりやすく、使いやすいだろうと思います。
以下のコードでいかがでしょうか。

■以下を入力表シートオブジェクトに
Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
　If Target.Cells.Count > 1 Then Exit Sub
　If ((Target.Row > 11) And (Target.Column = 5)) Then　'12行目以下、5列目なら
　　SelListSet Target.Offset(0, 1), Target.Value　　　'右隣のセルにセット
　End If
End Sub

■以下を標準モジュールに
Option Explicit

Const CCTblName = "顧客リスト" '会社名、製品名一覧シート名
Const SLSetName = "入力表"　　'入力表のシート名
Const inttl = "製品の選択"
Const inmsg = "製品を選択してください"
Const erttl = "選択エラー"
Const ermsg = "選択を間違えています"

'//選択候補をセットするサブルーチン
Sub SelListSet(tgRange As Range, Companyname As String)

　Dim RowCount As Long
　Dim SelList As String
　Dim ColCount As Long

　SelList = ""
　RowCount = 5

　With ThisWorkbook.Sheets(CCTblName)
　　Do
　　　If .Cells(RowCount, 2).Value = "" Then Exit Do
　　　If .Cells(RowCount, 2).Value = Companyname Then
　　　　SelList = .Cells(RowCount, 4).Value　　　'D列
　　　　For ColCount = 5 To 18　　　　　　　　　'E列～R列
　　　　　SelList = SelList & "," & _
　　　　　　.Cells(RowCount, ColCount).Value
　　　　Next ColCount
　　　End If
　　　RowCount = RowCount + 1
　　Loop
　End With

　If SelList = "" Then Exit Sub
　With tgRange.Validation
　　.Delete
　　.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
　　xlBetween, Formula1:=SelList
　　.IgnoreBlank = True
　　.InCellDropdown = True
　　.InputTitle = inttl
　　.ErrorTitle = erttl
　　.InputMessage = inmsg
　　.ErrorMessage = ermsg
　　.IMEMode = xlIMEModeNoControl
　　.ShowInput = True
　　.ShowError = True
　End With

End Sub

お礼

HohoPapaさん
いつもお世話になっております。
今回は＃３さんの回答でうまく出来そうなので、補足への追加回答は不要です。

大変お手数をおかけし申し訳ありませんでした。

補足

何度もお手数と、使い勝手まで忖度頂き感謝です！！
残念ながら当方では全くコードの意味が分からす、とりあえずシート名をコードに合わせて変更し、社名をリストから選択してみたら、「コンパイルエラー：定数式が必要です」と出ました。

＞Sub SelListSet(tgRange As Range, Companyname As String)
が黄色になって、
＞ With ThisWorkbook.Sheets(CCTblName)　
　　の(CCTblName)がハイライトになります。

E列を変えるとエラーが出るので、もう少しなら今少しご指導お願いします。
全く当方のレベルではない（無理）なら、別の方法（品名の上の行に番号を振って、INDEX＋MATCH関数）での対応も再度検討します。
（実は最初にやってみたのですが、どうにも使い勝手が悪く直接入力の方が使いやすい感じでした。（その場合は入力ミスが心配）