ドロップダウンからリストを絞り込んで選択

Excel初心者です。

ドロップダウンリストを作りそこから品目を選んで書類を作る形式を考えています。

マスタなどの別シートから参照してリストを作るのですが（原価などの情報もマスタで一元管理して、作成した書類にも自動で反映させたいので）、リストが100以上の選択肢を持ってしまうので選ぶのが大変面倒になりそうです。そこで考えていたのは、マスタの品目が英語表記なのでその頭三文字をLEFT関数で抽出た列を作っておいて、書類上ではそれを打ち込むことによってリスト上の選択肢を絞り込む（同じ頭三文字を含む品目もあります）形はどうかと思っています。(http://www.geocities.jp/chiquilin_site/data/1007 …　こちらのサイトを参考にしているのですが、いまいち自分で応用できなくて困っています。)

例えばマスタ上では
　　 A 　　　　 B　　　　　　　
1　 Por 　　 Pork belly
2　 Asp 　　Asparagus
3　 Bee 　　Beef steak
4　 Bee 　　Beef tongue
という具合です。そこで書類上にBeeと入れて、リストからステーキかタンを選べるようにしようかと思っている感じです。どのようにすればできるのでしょうか、教えていただけませんか？


もしその他に、簡単にリストの絞込みができるやりかたでおススメのがあればそれも教えていただければ嬉しいです。

No.3 回答者： tom04 回答日時： 2015/01/15 19:53

No.2です。

Excel2007をお使いというコトですと、
入力規則の「リスト」で「元の値」の欄の数式では他のSheetを参照できないはずですので、
Sheet1の使っていない列を利用してみてはどうでしょうか？
↓の画像のようにSheet2のA列2行目以降にデータを羅列しておきます。
（1行目は何らかの項目名を入れておいてください）
尚、リスト表示させる範囲はSheet1のA2～A20セルとしてみました。

仮にD列にリスト候補を表示させるとします。
（1）Alt＋F11キー　→　メニュー　→　挿入　→　標準モジュール　→　カーソルが点滅しているところに
↓のコードをコピー＆ペーストし、VBE画面を一旦閉じてください。

Sub Sample1() 'この行から
Dim lastRow As Long, str As String, wS As Worksheet
Set wS = Worksheets("Sheet2")
lastRow = wS.Cells(Rows.Count, "A").End(xlUp).Row
Application.ScreenUpdating = False
With Worksheets("Sheet1")
str = Selection
.Range("D:D").ClearContents
wS.Range("A1").AutoFilter field:=1, Criteria1:=str & "*"
If wS.Cells(Rows.Count, "A").End(xlUp).Row > 1 Then
Range(wS.Cells(2, "A"), wS.Cells(lastRow, "A")).SpecialCells(xlCellTypeVisible).Copy .Range("D1")
Else
MsgBox "該当データなし"
End If
wS.AutoFilterMode = False
End With
Application.ScreenUpdating = True
End Sub 'この行まで

（2）画面左下のSheet1のシート見出し上で右クリック　→　コードの表示　→　VBE画面のカーソルが点滅しているところに
↓のコードをコピー＆ペースト　→　VBE画面を閉じる

Private Sub Worksheet_SelectionChange(ByVal Target As Range) 'この行から
'A2～A20セルを対象としています。
If Intersect(Target, Range("A2:A20")) Is Nothing Or Target.Count > 1 Then Exit Sub
Call Sample1
End Sub 'この行まで

（3）Sheet1のA2～A20セルを範囲指定　→　データ　→　データの入力規則　→　リスト　→　「元の値」の欄に
=OFFSET(D$1,0,,COUNTA(D:D))
という数式を入れ　→　同じ画面の「エラーメッセージ」タブ　→　「無効なデータが入力されたら・・・」のチェックを外しておきます。

これでA2～A20セルに何らかの文字（1文字でも構いません）を入力　→　Enter　→　元のセルを選択
これでSheet2のA2セル以降のデータでその文字から始まるデータのみがリスト候補として表示されます。

※　D列が目障りであれば非表示にしてください。m(_ _)m

No.2 回答者： tom04 回答日時： 2015/01/13 21:45

こんばんは！

VBAになってしまいますが、一例です。

↓の画像のようにSheet1のA列を入力規則の「リスト」設定するとします。
Sheet2にA列に「品目」データを羅列しておきます。
そして、Sheet1のA列に入力されている文字列から始まるデータをSheet3のA列に表示し、それをリスト候補として表示させます。

まず「Sheet1」のSheet見出し上で右クリック　→　コードの表示　→　VBE画面のカーソルが点滅しているところに
↓のコードをコピー＆ペースト　→　Excel画面に戻る。（VBE画面を閉じる）

Private Sub Worksheet_SelectionChange(ByVal Target As Range) 'この行から
Dim i As Long, cnt As Long, wS2 As Worksheet, wS3 As Worksheet
Set wS2 = Worksheets("Sheet2")
Set wS3 = Worksheets("Sheet3")
With Target
If .Column = 1 And .Count = 1 Then
wS3.Range("A:A").ClearContents
For i = 2 To wS2.Cells(Rows.Count, "A").End(xlUp).Row
If Left(wS2.Cells(i, "A"), Len(.Value)) = .Value Then
cnt = cnt + 1
wS3.Cells(cnt, "A") = wS2.Cells(i, "A")
End If
Next i
End If
End With
End Sub 'この行まで

次にSheet1のA列を範囲指定　→　データ　→　データの入力規則　→　「リスト」を選択　→　「元の値」の欄に
=OFFSET(Sheet3!A$1,0,,COUNTA(Sheet3!A:A))
という数式を入れておきます。
次に同じ画面の「エラーメッセージ」タブ　→　「無効なデータが入力されたら・・・」のチェックを外しておきます。

これでA列に何らかの文字を入力　→　Enter　→　元のセルを選択すれば
その文字列から始まるSheet2のデータのみがリスト候補としてドロップダウンに表示されます。

※　手元のExcel2010でできましたが、Excel2007以前のバージョンで可能かどうかは
確認できていません。
VBAのコード自体は問題ないと思いますが、「リスト」の元の値の欄の数式でエラーになるかもしれません。

その場合は、Sheet3のA列を少し広めに範囲指定　→　名前定義　→　リストの元の値の欄に
仮に　範囲　と名前定義した場合は
=範囲
としておけば空白も表示されますが、
とりあえずは不要なものはリスト候補に表示されないと思います。m(_ _)m

この回答への補足

丁寧な回答ありがとうございます。自分が使っているのエクセルが2007なので（No.1の回答者様にも指摘されたとおり先に書くべきでした）、うまくいくか試してみたいと思います。VBAは使ったことがないのでうまくできるかわからないですが、頑張ってみたいと思います。

補足日時：2015/01/14 19:42

No.1 回答者： Chiquilin 回答日時： 2015/01/13 10:34

紹介されてる手前 書かない訳にもいかないか……

応用も何も まず書かれている内容を理解できているのでしょうか。

アルファベットの場合 略称リストを用意する必要はありません。
前方一致で抽出すればいいでしょう。

　　　　A　　　　B　　 C　　　D　　　　　　 E
1　　 名称　　　 2　　B　　　　　　　　選択リスト
2　Pork belly　　2　　Beef steak　　　　　　B
3　Asparagus　　 2　　Beef tongue
4　Beef steak　　1
5　Beef tongue　 0
6　　　　　　　　0
7　　　　　　　　0
8　　　　　　　　0
9　　　　　　　　0
10　　　　　　　 0

E列をリストにします。

■B1セル
=IF(C$1="",B2,IF(COUNTIF(A2,C$1&"*"),B2+1,B2))
好きなだけ下方向にオートフィルコピー

■C1セル
=INDEX(E:E,CELL("row"))&""

■C2セル
=IF(B$1<ROW(C1),"",INDEX(A:A,MATCH(B$1-ROW(C1),B$1:B$10,-1))&"")
フィルハンドルをダブルクリック

[数式]→[名前の管理]
名前を「選択リスト」として
参照範囲を
=IF(Sheet1!$B$1=0,OFFSET(Sheet1!$A$1,1,,COUNTA(Sheet1!$A:$A)-1),OFFSET(Sheet1!$C$1,1,,Sheet1!$B$1))
→[OK]

以上で準備完了。
E2:E10セルを範囲選択して [データ]→[データの入力規則]
エラー メッセージのチェックを外す
「設定」タブの[入力の種類]を「リスト」にして元の値に
「=選択リスト」と入力
→[OK]

以上です。

次回から 質問する時は OSや Excelのバージョンを最低でも書いて下さい。
Excel2007以上を前提として回答しました。

この回答への補足

回答どうもありがとうございます。まさかChiquilin様本人から回答いただけるとは思っていませんでしたのでびっくりしました。回答いただいてから何度も関数とにらめっこして意味を考えておりますが、お察しの通り全然理解ができていないので、せっかくなのでもうすこし時間をかけて考えてみたいと思います。ちなみにこちらのその3を使っているのですよね？（間違っていたらご指摘願います）http://www.geocities.jp/chiquilin_site/data/0603 …

補足日時：2015/01/14 19:38