EXCELのドロップダウンリスト

質問

EXCELで困っています。以下の例です。

Sheet1にリスト表を作成し、Sheet2の表に入力します。
社名及び製品名を名前の定義でドロップダウンできるようにします。
A列に　　　社名：　　A社・Ｂ社・Ｃ社・・・・
Ｂ列に　　製品名：　ねじ・ナット・スプリング・・・・・
C列に　　製品番号：　ＧＨ1・ＨＫ2・ＫＳ4・・・・・・
尚、製品名および製品番号は社名により内容が異なります。

Sheet2の表の入力でB列の社名クリックで社名がドロップダウン選択でき、C列の製品名クリックで製品名がドロップダウン選択できます。

D列で製品名に準じた製品番号を自動で入力できるように
VLOOKUPを用いて表示できたのですが、
問題は製品名は同じですが製品番号が違う時、同一の製品名の
どれを選択しても同一製品名の中の最上位の製品番号しか
選択されません。
どうぞこの問題の解消していただける方のご回答をお願いします。

回答No.3

tsubuyuki · ベストアンサー

正直、こういう使い方はＥＸＣＥＬは不得意であると思うんですけどね・・
やるならユーザーフォームで入力画面を作ることをオススメしたいところです。
なので、直接の回答とは言いづらいので、アレだったらスルーなさってください。

要するに、段階的に絞り込みたい、ということなのかなぁと勝手に解釈しまして、
それを基軸に考えてみたんですが・・・
何分、無能なもので作業列を駆使する方法しか思い浮かばなかったのですが、
とりあえず、こんな考え方もあるよ、という参考までに。
（ちょぃと長いです。）

ただし、これは「入力部分が１か所の場合のみ有効」ですから、
厳密には「やりたいこと」とは合致しないでしょう。
長いですし、不要であればなおさらスルーなさってくださいませ。

では、本編。

まず・・添付図のように（主に面倒だったので）１枚のシートで完結していますので、
シートを分ける必要があるのであれば、適宜変更ください。

さて。
Ａ：Ｃ列に社名・製品名・製品番号の「元リスト」を作っておきます。
（たぶん、ここまでは一緒と思われます。）

で・・私が作業した順に行くと、
作業列その１「社名の（重複しない）リストを作成するための作業列」として、
Ｅ列に「=IF(COUNTIF(A$2:A2,A2)=1,ROW(),"")」という式を入れます。
式の内容はお察しいただけると幸いです。

Ｊ列に「社名リスト」を作成します。
Ｊ２セルに「=IF(COUNT(E:E)<ROW(A1),"",INDEX(A:A,SMALL(E:E,ROW(A1))))」という式を入れ、
行方向に必要（と思われる）行数、フィルしてやります。
これで、「重複しない社名リスト」が完成しました。

これを基に範囲名をつけます。
「名前の定義」より、
　　名前：社名リスト
　　参照範囲：=OFFSET(Sheet1!$J$2,0,0,COUNTIF(Sheet1!$J$2:$J$65535,"?*"),1)
として、名前を定義してやります。
つまり「Ｊ２セルから下、空白以外のセルが続く範囲」を参照してやっています。

で、この範囲名を使って、Ｏ１セルに入力規則を
　　入力値の種類：リスト
　　元の値：=社名リスト
と設定します。

ここまでで「社名」のドロップダウン完成です。

さて、続いてＦ列。
ここは「社名がＯ１セルと同じだったら製品名を引っ張ってくる」作業列です。
Ｆ２セルに「=IF(A2=$O$1,B2,"")」という簡単な式を入れて、必要分フィルです。
引き続き、Ｇ列に「重複しない製品名リストを作るための作業列」を作ります。
Ｇ２セル「=IF(AND(F2<>"",COUNTIF(F$2:F2,F2)=1),ROW(),"")」という式でフィルしておきましょう。

Ｋ列に「製品名リスト」を作成します。
Ｋ２セル「=IF(COUNT(G:G)<ROW(A1),"",INDEX(F:F,SMALL(G:G,ROW(A1))))」で、
これも行方向に必要（と思われる）行数、フィルしてやります。
これで「重複しない製品名リスト」が出来ました。

同様に名前を定義します。
「名前の定義」より、
　　名前：社名リスト
　　参照範囲：=OFFSET(Sheet1!$K$2,0,0,COUNTIF(Sheet1!$K$2:$K$65535,"?*"),1)
として、名前を追加してやります。

Ｏ２セルに入力規則を追加しましょう。
　　入力値の種類：リスト
　　元の値：=製品名リスト
ですね。

これで、製品名のドロップダウンが完成です。
（念のため、テストしてくださいね。）

ラスト、製品番号もがんばりましょう。
まずは「重複しない製品番号リストを作るための作業列」をＨ列に作ります。
Ｈ２セル「=IF(AND(A2=$O$1,B2=$O$2),ROW(),"")」で、行方向にフィルします。
なんとなく、意味を掴んでいただければ幸いです。

Ｌ列に「重複しない製品番号リスト」を作成しましょう。
Ｌ２セル「=IF(COUNT(H:H)<ROW(A1),"",INDEX(C:C,SMALL(H:H,ROW(A1))))」とし、
これも必要な分、行方向にフィルします。
で「重複しない製品番号リスト」完成です。あと一歩です。

これも名前を定義してやりましょう。
「名前の定義」より、
　　名前：製品番号リスト
　　参照範囲：=OFFSET(Sheet1!$L$2,0,0,COUNTIF(Sheet1!$L$2:$L$65535,"?*"),1)
で、追加してやります。

Ｏ３セルに入力規則をかけます。
　　入力値の種類：リスト
　　元の値：=製品番号リスト
と設定しましょう。
これで、最後の「製品番号ドロップダウン」も完成しました。

おつかれさまでした。

お礼

長文でご丁寧なご回答ありがとうございました。
しかしながら、中途半端な知識しかない私には、
いまひとつ理解できず、かつ要領を得ず
断念せざるを得ませんでした。
こちらこそ、折角のご回答に応えられずすいません。

回答No.5

tom04

No.4です。
何度もごめんなさい。

すでにお気づきだと思いますが、前回の投稿の中で入力ミスがあります。

「名前提議」→「名前定義」です。
どうも失礼しました。m（_ _）m

回答No.4

tom04

こんばんは！
横からお邪魔します。

一案です。
Sheet3を作業用のSheetとして使用しますので、Sheet3は全く使用していない！という前提です。
↓の画像で説明します。
上側がSheet1で下側がリスト表示させたいSheet2だとします。
「製品番号」に重複がない！というコトですので、Sheet1の表を変えてみます。
行方向に「社名」・列方向に「製品名」を入力し、
その表に「製品番号」を埋めておきます。

ご希望としてはSheet2のB列をリストで選択　→　C列をリストで選択　→　D列に当てはまる製品番号を！
というコトだと思いますので、Sheet2のB列を選択するたびにC列のリスト表示データを変更する必要があると思います。

↓の画像でSheet1のA2～A7セルを
「社名」と名前提議しています。名前提議はこれだけです。
この名前提議したものをSheet2のB列リストの「元の値」にします。

そしてSheet2のB列データの変更があるたびにSheet2のC列にリスト表示させたいデータを
Sheet3のA列に表示させます。
ココだけはVBAになってしまいますので、
画面左下のSheet2のSheet見出し上で右クリック　→　コードの表示　→　VBE画面に
↓のコードをコピー＆ペーストしておいてください。

Private Sub Worksheet_Change(ByVal Target As Range) 'この行から
Dim j As Long, myRow As Long, endCol As Long, cnt As Long, c As Range, wS1 As Worksheet, wS3 As Worksheet
Set wS1 = Worksheets("Sheet1")
Set wS3 = Worksheets("Sheet3")
endCol = wS1.Cells(1, Columns.Count).End(xlToLeft).Column
If Application.Intersect(Target, Range("B:B")) Is Nothing Or Target.Count <> 1 Then Exit Sub
wS3.Range("A:A").ClearContents
With Target
Set c = wS1.Range("A:A").Find(what:=.Value, LookIn:=xlValues, lookat:=xlWhole)
myRow = c.Row
For j = 2 To endCol
If wS1.Cells(myRow, j) <> "" Then
cnt = cnt + 1
wS3.Cells(cnt, "A") = wS1.Cells(1, j)
End If
Next j
End With
End Sub 'この行まで

そして、Sheet2のC列のリストの「元の値」の欄に
=Sheet3!$A$1:$A$10
という数式を入れておきます。

最後にD2セルに
=IF(COUNTBLANK(B2:C2),"",INDEX(Sheet1!$B$2:$F$7,MATCH(B2,社名,0),MATCH(C2,Sheet1!$B$1:$F$1,0)))
という数式を入れオートフィルで下へコピー！
これで画像のような感じになります。

※　Excel2007以降での方法になりますので、もしExcel2003までのバージョンの場合は
Sheet3のA1～A10セル（実際は製品番号の数の最大数程度の行まで）を範囲指定　→　好みの名前提議を行い
Sheet2のD列のリストの「元の値」に入力します。

※　Sheet2のリストで不必要なものを表示したいためにこのような方法にしてみました。m（_ _）m

お礼

ご丁寧な説明ありがとうございました。
参考になりました。

回答No.2

keithin

「Ａ社のねじ」と「Ｂ社のねじ」で製品番号が異なるのを見分けたい、というお話に限定して。

必ずしもお勧めというワケではありませんが、昨日のご相談の
http://okwave.jp/qa/q8152741.html
でご質問者さんが最初使っていた式、また状況によっては回答の式が使えるかもしれません。

＃参考
なんでも一緒くたにせず
　Ａ社の製品並びに番号一覧
　Ｂ社の製品並びに番号一覧
　Ｃ社の製品並びに番号一覧
をそれぞれ別個に作成し、

１．会社を選んだら、その会社の製品だけが入力規則で選べるように仕込む
http://www.geocities.jp/chiquilin_site/data/100930_screening2.html

２．会社（と製品）が選ばれたら、その製品名でVLOOKUPする範囲として会社から適正範囲を計算して、番号を計算させる

といった具合に、もう少し丁寧に作成したほうが良いかも？しれません。
上手くいかないときは、具体的な状況を添えて改めて別途ご相談を投稿してみて下さい。

補足

早々のご回答有難うございました。
私の説明不足がありすいません

ご指摘の通りリストとしては、なんでも一緒くたにはせず以下のように個別にしたつもりです。
社名のリストとして名前の定義でその部分を範囲指定し、入力セルでＢセルにドロップダウン入力します
製品名も同様に社名別に製品名のリストとして名前の定義でその部分を範囲指定し、入力セルでＣセルにその社の製品名だけがドロップダウン入力できるようにします
製品番号はＣセルに入力された製品名に準じたリスト内のとなりのセル（製品番号）を入力セルＤにVLOOKUPで表示させるという内容です。なお製品番号に同じものはありません。
その際に、同一製品名のなかで最上位の製品番号だけしか表示されません。
（例えば、製品名にネジが5個ありそれぞれ製品番号A1.A2.A3.A4.A5と違うときどのネジをクリックしてもA5しか表示しないといった状態です）
知識が乏しいなりにいろいろ考えていたのですが、やはりVLOOKUPでは無理みたいで・・・
他の方法があるのでしょうか？（出来れば、私にも理解できる簡単な方法であれば幸いです）
又、1.．会社を選んだら、その会社の製品だけが入力規則で選べるように仕込む
http://www.geocities.jp/chiquilin_site/data/100930_screening2.html
を参考にしようと思ったのですがページが開けませんでした。

回答No.1

KURUMITO

社名が違っても製品名が同じであるときにそれを区別するのでしたら社名を取り込んだ形で検索することが必要でしょう。その場合でも例えばA社のねじで製品番号がいくつかある場合にはVLOOKUPなどでは対応できませんね。A社のねじについては製品番号は一つだけなのでしょうか？

補足

早々のご回答有難うございました。
私の説明不足がありすいません
重複するかもしれませんが以下の通りです。

社名のリストとして名前の定義でその部分を範囲指定し、入力セルでＢセルにドロップダウン入力します
製品名も同様に社名別に製品名のリストとして名前の定義でその部分を範囲指定し、入力セルでＣセルにその社の製品名だけがドロップダウン入力できるようにします
製品番号はＣセルに入力された製品名に準じたリスト内のとなりのセル（製品番号）を入力セルＤにVLOOKUPで表示させるという内容です。なお製品番号に同じものはありません。
知識が乏しいなりにいろいろ考えていたのですが、やはりVLOOKUPでは無理みたいで・・・
他の方法があるのでしょうか？（出来れば、私にも理解できる簡単な方法であれば幸いです）