関数での在庫管理表を作成しようとしているのですが見当がつかず困ってます

質問

現在、エクセルで在庫リストを作っています。
複数条件の表示に悩んでいます。分かりにくい文章だと思いますが教えていただけたら幸いです。

Sheet2に

A4セル　　B4セル　　 C4セル　　　D4セル　　E4セル　　　F4セル　　　
　No　　製品名　　材料1　　材料2　　数値1　　数値2
0001　　XXX　　　 PPP　　　VVV　　　0.25　　　12.3
0002　　XXX　　　 MMM　　　VVV　　　0.15　　　12.3
0003　　XZY　　　 PPP　　　ZZY　　　0.10　　　10.5
0004　　XZY　　　 PPP ZZY 0.23　　　11.2

という表を作り（実際はY列：500行まであります^^;）
挿入→名前→定義
で「在庫リスト」としました。

問題はここからで、上の条件をSheet1でいろいろなパターンで検索するものを作りたいと思っています。

Sheet1には
C3：No 、D3：製品名 、C5：材料1 、D5：材料2 、C7：数値1 、D7：数値2
と書きその下に検索したい物を入れたいと思っています。
例C4セル：0001

で、ここからが本題なのですが、複数条件
例えば
製品名：XXX
と入れたら、14行の所にXXXに該当するもので
　No　　製品名　　材料1　　材料2　　数値1　　数値2
0001　　XXX　　　 PPP　　　VVV　　　0.152　　12.3
0002　　XXX　　　 MMM　　　VVV　　　0.152　　12.3

を表示させたり

材料2：ZZY 、数値2：11.2
と入れたら
　No　　製品名　　材料1　　材料2　　数値1　　数値2
0004　　XZY　　　 PPP ZZY 0.102　　11.2

と表示させたいと思っています。

また数値1で0.1～0.19の間を検索して表示とかもできるのでしょうか？
数値1：10
　No　　製品名　　材料1　　材料2　　数値1　　数値2
0002　　XXX　　　 MMM　　　VVV　　　0.15　　　12.3
0003　　XZY　　　 PPP　　　ZZY　　　0.10　　　10.5

というようなものを求めています。
どうか、よろしくお願いします。

回答No.2

KURUMITO · ベストアンサー

お示しのデータが次のようになっているとします。
シート1ではＣ3にNo、Ｄ３に製品名、Ｃ５に材料1、Ｄ5に材料2、Ｃ7に数値1、Ｄ7に数値2の文字があり検索に使用するデータはそれぞれの項目の1行下の行に入力するものとします。
Ａ14セルからＦ14セルにはＮｏから数値2までの項目名が並んでいるとして、検索の結果はそれ以下の行に表示されるとします。
シート2ではＡ4セルからＦ4セルまでに項目名が並び、それ以降の行にデータが入力されているとします。
なお、検索に使用する数値1のデータおよび数値2のデータについては、すなわち、Ｃ8セルやＤ8セルに入力するデータについては、ある範囲を指定して検索する場合には0.9-0.19などと半角英数文字で入力することとします。
非常に操作が複雑になりますので作業列や作業のセルを用意して対応することとします。
シート1のＦ3セルには次の式を入力します。
=IF(Sheet1!D4="","",D4&"/")&IF(Sheet1!C6="","",C6&"/")&IF(Sheet1!D6="","",D6&"/")&IF(Sheet1!C8="","",IF(ISERROR(FIND("-",C8)),C8&"/","OK/"))&IF(Sheet1!D8="","",IF(ISERROR(FIND("-",D8)),D8,"OK"))
Ａ15セルには次の式を入力し、Ｆ15セルまでオートフィルドラッグしたのちに下方にもオートフィルドラッグします。
=IF(OR(COUNTIF(Sheet2!$J$5:$J$1000,ROW(A1))=0,A$14=""),"",INDEX(Sheet2!$A$5:$F$1000,MATCH(ROW(A1),Sheet2!$J$5:$J$1000,0),COLUMN(A1)))
シート２についてはＧ５セルに次の式を入力しＨ５セルまでオートフィルドラッグしたのちに、下方にもオートフィルドラッグします。
=IF(OR($A5="",Sheet1!C$8=""),"",IF(ISERROR(FIND("-",Sheet1!C$8)),"",IF(AND(E5>=VALUE(LEFT(Sheet1!C$8,FIND("-",Sheet1!C$8)-1)),E5<=VALUE(MID(Sheet1!C$8,FIND("-",Sheet1!C$8)
+1,10))),"OK","NG")))
これらの列は数値1や数値2のデータについて範囲指定の検索があった時に使用するものです。
Ｉ5セルには次の式を入力し下方にオートフィルドラッグします。
=IF(A5="","",IF(Sheet1!$D$4="","",$B5&"/")&IF(Sheet1!$C$6="","",$C5&"/")&IF(Sheet1!$D$6="","",$D5&"/")&IF(Sheet1!$C$8="","",IF(G5="",$E5&"/",G5&"/"))&IF(Sheet1!$D$8="","",IF(H5="",$F5,H5)))
これらの操作が終わった後で検索のデータを入力すれば、それらのデータを満足するデータが15行目以降に表示されます。
式が複雑ですので、必ずこちらが指定した条件で表を作成し、一度検証してから実際のデータに適応してください。

お礼

詳細な説明ありがとうございます。
現在は帰宅しているので、明日朝からやってみたいと思います。
助かりました。

回答No.6

KURUMITO

大変失礼いたしました。
シート2のＪ2セルに入力する式が間違っていました。次の式をＪ2セルに入力し下方にオートフィルドラッグしてください。
=IF(Sheet1!$F$3="","",IF(I5=Sheet1!$F$3,MAX(J$4:J4)+1,""))

お礼

できました。
これから応用して、本データを入れていこうと思います。
いろいろありがとうございました。

回答No.5

KURUMITO

回答2では
Ａ15セルには次の式を入力し、Ｆ15セルまでオートフィルドラッグしたのちに下方にもオートフィルドラッグします。
=IF(OR(COUNTIF(Sheet2!$J$5:$J$1000,ROW(A1))=0,A$14=""),"",INDEX(Sheet2!$A$5:$F$1000,MATCH(ROW(A1),Sheet2!$J$5:$J$1000,0),COLUMN(A1)))
とのことでＦ15までオートフィルドラッグしたのちにＡ15からＦ15セルまでを選択してＦ15セルの右下はじの■をクリックして下方の行にドラッグするのです。そのことによって100行までの好きな行だけ式がコピーし貼り付けられますので、該当するデータがたくさんあれば下の行に表示されることになりますね。
Ａ14からＦ14セルまでの間には項目名を入れるのですよ。

補足

すいません、解答4のところで15行以降への表示がされないという答えがあったにも関わらず、変な補足の書き方をしてしまいました。
解答4で仰られたとおりに、Sheet2のJ5セルに入力後下方にオートフィルを行いました。Sheet1のA15セルのオートフィルは行ってます。
その状態で検索を行うと、複数の条件の答えのもの「製品名：XXX」を入れると J5セル、J6セル には 1 と帰ってきます。
ここでチェックされたものがその後Sheet1の15以降に行くということですよね、しかし、表示される結果は15行の1行のみなのです。
他の検索も同様です。いい忘れましたが当方Office2003です。
バージョンによってできない可能性もあるのでしょうか。

回答No.4

KURUMITO

回答2で重要なことが抜けていました。
シート2のＪ5セルには次の式を入力し下方にオートフィルドラッグします。
=IF(Sheet1!$F$3="","",IF(I5=Sheet1!$F$3,MAX(I$4:I4)+1,""))
この式が入力されていないとシート1で15行以降への該当行の表示ができません。

補足

すべて同様のものを作成し、検証を行ってみましたが、
製品名：XXX
と入れたら、14行の所にXXXに該当するもので
　No　　製品名　　材料1　　材料2　　数値1　　数値2
0001　　XXX　　　 PPP　　　VVV　　　0.152　　12.3
0002　　XXX　　　 MMM　　　VVV　　　0.152　　12.3
を複数表示というものがうまくできませんでした。
で、よく考えてみるとこの表記では、15行のみに表示させる形の書き方ですね。
15行と16行に渡って複数表示させたいのですができるのでしょうか？
たとえば「製品名」では15、16行に2つ「材料1」で「PPP」と検索したら15,16、17行の3つに出てくるような形です。

回答No.3

KURUMITO

回答Ｎｏ２で数値の範囲を入力する例で0.9-0.19のように書きましたが0.1-0.19の誤りです。－の前にある数値はーの後にある数値よりも小さいことが必要です。

お礼

補足ありがとうございます。

回答No.1

ksys2009

質問の回答とは異なりますが、オートフィルタ機能を使用してはどうでしょうか。

参考URL：

http://www.eurus.dti.ne.jp/~yoneyama/Excel/filter.htm

お礼

それも視野に入れましたが、今回のデータの応用で他の作業にも使えそうな事が有りましたので、あえて関数でと投稿しました。
回答ありがとうございます。