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

Question

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

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

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

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

tsubuyuki · Accepted Answer

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

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

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

では、本編。

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

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

で・・私が作業した順に行くと、
作業列その１「社名の（重複しない）リストを作成するための作業列」として、
Ｅ列に「=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)
で、追加してやります。

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

おつかれさまでした。

tom04 · Answer

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

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

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

tom04 · Answer

こんばんは！横からお邪魔します。一案です。 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

keithin · Answer

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

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

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

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

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

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

KURUMITO · Answer

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

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

正直、こういう使い方はＥＸＣＥＬは不得意であると思うんですけどね・・

No.4です。

こんばんは！

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

この回答への補足

社名が違っても製品名が同じであるときにそれを区別するのでしたら社名を取り込んだ形で検索することが必要でしょう。

この回答への補足

関連するカテゴリからQ&Aを探す

デイリーランキングこのカテゴリの人気デイリーQ&Aランキング

マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング