EXCEL リストから条件を満たしたデータを抽出s

いつもエクセルテクニックを学ぶために活用させていただいております。
今回はみなさまの過去の質問を見ても解決できなかったので、質問させていただきました。

　取引先へ出す発注書を作成しようと思っています。
　Sheet1には、取引先名、工種、内容という一覧を作りました。
　↑を使ってSheet2で、「取引先名」にリストで選択すると、下の「工種」・「内容」欄に
　Sheet1の｛取引先名」→「工種」→「内容」と入れていきたいのです。

Sheet1
　　　A　　　　　　　　B　　　　　　　　　C
　　　取引先名　　　工種 　　　　　　内容
　1　T設計　　　　　設計費　　　　　設計
　2　(株)B　　　　　　仮設工事　　　　仮設
　3　(株)J工業　　外装工事　　　　外装
　4　(株)B　　　　　　内装工事　　　　建材
　5　H建材(株)　　内装工事　　　　建材
　6　(株)H商販　　内装工事　　　　建材



Sheet2

　　　A　　　　　　　　B　　　　　　　　　　C　　　　　　　　　　　　D
　1　取引先名
　2
　3　工　　種　　　　　内　　容　　　　　規　　　格　　　　　　　単　位
　4


　
　
　例1：「取引先名」（Sheet2：A2）に【T設計】とリストで選択
　　　　「工種」（Sheet2：A4）には【設計費】としかリスト選択出来ないようにする
　　　　「内容」（Sheet2：B4）には【設計】としかリスト選択出来ないようにする


　例2：：「取引先名」（Sheet2：A2）に【(株)B】とリストで選択
　　　　「工種」（Sheet2：A4）には【仮設工事】と【内装工事】と2項目リストを選択出来るようにする
　　　　（【内装工事】と選択した場合↓）
　　　　「内容」（Sheet2：B4）には【建材】としかリスト選択出来ないようにする
　
　例1のように、1業者1項目となるなら何とかなりそうだったのですが、例2のように
　1業者複数項目となると、どのようにしたらよいのか全くわかりませんでした。
　どなたかご教授いただければ幸いです。

No.1 ベストアンサー 回答者： tom04 回答日時： 2013/05/08 23:12

こんばんは！

一例です。
画像を拝見するとExcel2010をお使いのようですね！
Excel2010では別Sheetをそのままリストの範囲に指定できますので直接リストの「元の値」にSheet1の範囲を指定しています。
（Excel2007までの場合は「名前定義」する必要があったと思います）

↓の画像（小さくて見づらいかもしれませんが、画面を拡大してみてください）のように
Sheet3を作業用のSheetとして使用します。

「取引先」と「工種」を選択すれば「内容」が1種類しかないのであれば簡単ですが、
画像を拝見すると複数あるようなので少し厄介になります。
以下の手順で操作してみてください。

（1）Sheet1に作業用の列を設けます。
Sheet1の作業列D2セルに
=IF(COUNTIF(A$2:A2,A2)=1,ROW(),"")
という数式を入れオートフィルでしっかり下へコピーしておきます。
（データがなくても構いませんのでこれ以上データはない！というくらいに！）
その後このD列が目障りであれば非表示にします。

（2）Sheet3のA1セルに
=IF(COUNT(Sheet1!$D:$D)<COLUMN(A1),"",INDEX(Sheet1!$A:$A,SMALL(Sheet1!$D:$D,COLUMN(A1))))
という数式を入れ列方向（右方向）にオートフィルでコピー！

（3）Sheet3のA2セルに
=IF(A$1="","",IFERROR(INDEX(Sheet1!$B$1:$B$1000,SMALL(IF(Sheet1!$A$1:$A$1000=A$1,ROW($A$1:$A$1000)),ROW(A1))),""))

このA2セルは配列数式になってしまいますので、Shift+Ctrl+Enterで確定！
この画面からコピー＆ペーストする場合は
A2セルを選択　→　数式バー内に上記数式を貼り付けます。
この状態で編集可能になっていますので、Shift+Ctrlキーを押しながらEnterキーで確定！
数式の前後に｛　｝マークが入り配列数式になります。
このA2セルを列・行方向にオートフィルでコピーしておきます。

そしてSheet2のA2セルを入力規則のリスト設定します。
元の値の欄には
=Sheet3!$A$1:$E$1
としてOK
（とりあえずSheet3の1行目のデータがあるだけ範囲指定します）

次にSheet2のA4セル以降を範囲指定　→　リストの「元の値」の欄に
=OFFSET(Sheet3!$A$2:$A$11,,MATCH($A$2,Sheet3!$1:$1,0)-1,,1)
という数式を入れOK
（個の数式をそのままコピー＆ペーストしても大丈夫です）
これでA4以降にSheet1のB列が表示されると思います。

さて、最後に問題の「内容」のリスト設定です。
おそらくSheet2のA4以降は1行だけではないと思いますので、
VBAになってしまいますが・・・
画面左下のSheet2のSheet見出し上で右クリック　→　コードの表示　→　VBE画面に
↓のコードをコピー＆ペーストしておいてください。

Private Sub Worksheet_Change(ByVal Target As Range) 'この行から
Dim i As Long, wS As Worksheet
Set wS = Worksheets("Sheet1")
On Error Resume Next
Application.ScreenUpdating = False
If Application.Intersect(Target, Range("A:A")) Is Nothing Or Target.Row < 4 Then Exit Sub
With wS.Range("A1")
.AutoFilter Field:=1, Criteria1:=Range("A2")
.AutoFilter Field:=2, Criteria1:=Target
End With
wS.Columns(3).Copy Worksheets("Sheet3").Range("H1") '←H列はデータがない列にする！
wS.AutoFilterMode = False
Application.ScreenUpdating = True
End Sub 'この行まで

これでSheet2のA2とA4セル以降のデータが決まれば、それに基づく「内容」だけがSheet3のH列に表示されると思います。
このH列は実状のデータに合わせて他の列（データがない列）にします。

最後にSheet2のB4セル以降を範囲指定　→　リストの元の値の欄に
=Sheet3!$H$2:$H$11
としてOK

※　とりあえず10行分だけリスト表示するようにしていますが、これも実状に合わせてください。

尚、上記操作において
A2　→　A列　→　B列　の順にリスト表示させるという前提です。

以上、長々と書きましたがお役に立ちますかね？m(_ _)m

この回答へのお礼

tom04さん　　ご回答ありがとうございました。
配列数式を含め式の意味を理解しようと朝から格闘してました。
結果、理解しながらという点はダメでしたが完成させることが出来ました。

ただ、一番最後の「内容」の部分なのですが、リストを作成しましたが、Ａ列から反映されず
絞り込みが出来ません。

tom04さんが作成してくれた図では、リスト選択欄には「仮設」としか出ていなかったように
見えているのですが、私の入力手順が悪いのでしょうか、どうしてなのかわかりません。

Sheet3（tom04さんの図）にあったようにＨ列にも「仮設」とありますが、私が作成したSheet3には、
「内容」と表示され、下に空白のセルがあるだけでした。
何か手順を間違ってますよね？

因みに業者数が100社ほどあるため、Ｈ列をＥＬ列として、ＶＥＢ画面でも、Sheet2のB4セルのリスト作成でも、ＨをＥＬに直して作りました。

とりあえずは「内容」欄には、全ての項目のリストを作成し、活用できるようにしました。

兎にも角にも非常に参考になりました。ありがとうございました。

またお時間のある時、私のミスをご指摘願います。

お礼日時：2013/05/09 15:23