エクセルでリストから抽出並び替えについて

質問

Sheet1
得意先： あああ商事　 条件： 50%

品番　　 　　品名　　　　　 　上代　 発注数　 単価　　
1001 　　　タオル　A　　 　　980 　　　　5 　　　　2450
1002 　　　タオル　B　　 　　980
1003 　　　タオル　C　　 　　980
2001 　　　ハンカチ　A　 　　680 　　　10 　　　3400
2002 　　　ハンカチ　B　 　　680
2003 　　　ハンカチ　C　 　　680 　　　10 　　　3400

Sheet2
得意先　　　 品番　　 品名　　　　　 上代 　発注数 　単価
あああ商事　 1001 　タオル　A　　 980 　　5 　　　　2450
あああ商事　 2001 　ハンカチ　A　 680 　　10 　　　3400
あああ商事　 2003 　ハンカチ　C　 680 　　10 　　　3400

お世話になります。
エクセルでSheet1の商品リストに発注数を入力したものだけを
Sheet2にフィルターコピペではなく、数式で並べていきたいのですが
方法はございますでしょうか。

回答No.3

tom04 · ベストアンサー

こんばんは！

他の回答者様とほとんど同じやり方ですが・・・
Sheet1の　50％　が何を意味しているのか分からないので、無視しています。

↓の画像でSheet2のA2セルに
=IF(B2="","",Sheet1!B$1)

B2セルに
=IFERROR(INDEX(Sheet1!A$1:A$1000,SMALL(IF(ISNUMBER(Sheet1!$D$1:$D$1000),ROW(A$1:A$1000)),ROW(A1))),"")
これは他の方の回答と一緒で配列数式ですので、Ctrl＋Shift＋Enterで確定！
B2セルをF2セルまでオートフィルでコピー！

最後にA2～F2セルを範囲指定　→　F2セルのフィルハンドルで下へコピーすると
画像のような感じになります。m(_ _)m

お礼

ご回答ありがとうございます。
意図していたとおりになりました。

回答No.4

kagakusuki

各データがどの列の何行目に入力されているのかが不明なため、今仮に、「あああ商事」等の得意先名がSheet1のB1セルに入力されていて、Sheet1のA3セルに「品番」、Sheet1のB3セルに「品名」、Sheet1のC3セルに「上代」、Sheet1のD3セルに「発注数」、Sheet1のE3セルに「単価」の様に、Sheet1の3行目には項目名が入力されていて、実際のデータはSheet1の4行目以下に入力されているものとします。
　そして、Sheet1のE1セルに「50%」等の条件を表示させ、Sheet2のA列～F列の2行目以下に、抽出結果を表示させるものとします。
　又、Sheet1のA列に入力されている品番には、同じ番号が重複して入力されている様な事は無いものとします。

　まず、Sheet1のE1セルに次の関数を入力して下さい。

=IF(COUNTIF($A:$A,"*?")-COUNTIF($A$1:$A$3,"*?")+COUNT($A:$A),COUNT($D:$D)/(COUNTIF($A:$A,"*?")-COUNTIF($A$1:$A$3,"*?")+COUNT($A:$A)),"")

　次に、Sheet1のE1セルの書式設定の表示形式を[パーセンテージ]として下さい。
　尚、上記のパーセンテージを表示させる関数を、Sheet1のD列に入力する事だけは避けて下さい。

　次に、Sheet2のB1セルに次の関数を入力して下さい。

=Sheet1!$A$3

　次に、Sheet2のC1セルに次の関数を入力して下さい。

=Sheet1!$B$3

　次に、Sheet2のD1セルに次の関数を入力して下さい。

=Sheet1!$C$3

　次に、Sheet2のE1セルに次の関数を入力して下さい。

=Sheet1!$D$3

　次に、Sheet2のF1セルに次の関数を入力して下さい。

=Sheet1!$E$3

　次に、Sheet2のA2セルに次の関数を入力して下さい。

=IF($B3="","",Sheet1!$B$1&"")

　次に、Sheet2のB2セルに次の関数を入力して下さい。

=IF(ROWS($2:2)>COUNT(Sheet1!$D:$D)-COUNT(Sheet1!$D$1:$D$3),"",INDEX(Sheet1!$A:$A,MATCH(9E+307,INDEX(Sheet1!$D:$D,1):INDEX(Sheet1!$D:$D,IF(B3="",ROWS(Sheet1!$D:$D),MATCH(B3,Sheet1!$A:$A,0)-1)))))

　次に、Sheet2のC2セルに次の関数を入力して下さい。

=IF(OR($B2="",COUNTIF(Sheet1!$A$3:$E$3,C$1)=0),"",IF(VLOOKUP($B2,Sheet1!$A:$E,MATCH(C$1,Sheet1!$A$3:$E$3,0),FALSE)="","",VLOOKUP($B2,Sheet1!$A:$E,MATCH(C$1,Sheet1!$A$3:$E$3,0),FALSE)))

　次に、Sheet2のC2セルをコピーして、Sheet2のD2～F2の範囲に貼り付けて下さい。
　次に、Sheet2のA2～F2の範囲をコピーして、同じ列範囲の3行目以下に貼り付けて下さい。

　これで、Sheet2に抽出結果が自動的に表示されます。

お礼

ご回答ありがとうございます。
こちらの数式でも意図していたとおりになりました。

回答No.2

bunjii

Sheet2の各セルは次の関数式で良いと思います。

A2=IF(C2="","",Sheet1!$B$1)
下へ必要数をオートフィルでコピーする。

B1=IFERROR(INDEX(Sheet1!A$3:A$1000,SMALL(IFERROR(MATCH(Sheet1!$D$3:$D$1000,Sheet1!$D$3:$D$1000,0),""),ROW())),"")
数式バーに入力後、Ctrl+Shift+Enterで確定してください。
B1セルを右にF1までオートフィルでコピーし、そのまま下へオートフィルコピーします。

貼付画像はExcel 2013で検証したものですがExcel 2007以降のバージョンでも同様な結果が得られるはずです。
Excel 2003以前の場合はIFEROOR関数が組み込まれていないので使えません。

お礼

ご回答ありがとうございます。
発注数が同じですと引っ張ってくる内容が同じものになってしまうようです。

回答No.1

MackyNo1

Sheet2のデータが「あああ商事」という前提なら、以下の式を入力して右方向及び下方向にオートフィルしてください。

=INDEX(Sheet2!A:A,SMALL(INDEX((Sheet2!$D$2:$D$100="")*1000+ROW($2:$100),),ROW(1:1)))

該当データがない部分は「0」と表示されますので、セルの書式設定で表示形式をユーザー定義にして「0;;;@」と入力してください。

お礼

ご回答ありがとうございます。
こちらはSheet2の内容を1に持ってくる式のような？