エクセル 抽出データのリストのテンプレート作成

質問

初心者です。
上段のデータをもとに、下段のようなリストのテンプレートを作成したいです。
できれば、別のシートに作成したいです。（上段データが大きいので）
下段の出荷日を設定すると、自動的に該当データのみリストアップしたいです。

よろしくお願いします。

回答No.6

tom04 · ベストアンサー

こんばんは！
横からお邪魔します。

ほとんどNo.3さんと同じ方法になるので、参考程度で・・・

↓の画像でSheet1のE2セルに
=IF(COUNTIF(F2:K2,$E$1),INDEX(F2:K2,,MATCH($E$1,F2:K2,0)+1),"")
という数式を入れオートフィルですぃ～～！っと下へコピーしておきます。

Sheet2のE1セルには
=Sheet1!E1

A2セルに
=IF(COUNTIF(Sheet1!$F:$K,$C$1)<ROW(A1),"",INDEX(Sheet1!A$1:A$1000,SMALL(IF(Sheet1!$F$1:$K$1000=$C$1,ROW($A$1:$A$1000)),ROW(A1))))
これは配列数式になってしまいますので、Shift+Ctrl+Enterで確定！
この画面からコピー＆ペーストする場合はA2セルに貼り付け後、数式バー内で一度クリック！
編集可能になりますので、Shift+Ctrlキーを押しながらEnterキーで確定！
数式の前後に｛　｝マークが入り配列数式になります。

このA2セルを隣のB2セルまでオートフィルでコピー！
C2セルは数式を変えています。

C2セル（これも配列数式）は
=IF(COUNT(Sheet1!$E$2:$E$100)<ROW(A1),"",INDEX(Sheet1!$E$2:$E$1000,SMALL(IF(Sheet1!$E$2:$E$1000<>"",ROW($A$1:$A$999)),ROW(A1))))

※　C2セルの数式の範囲指定の行番号が違っているのに注意してください。
（INDEX関数で範囲指定した行数とROW($A$1:$A$999)の行数を合わせる必要があります）

最後にA2～C2セルを範囲指定　→　C2セルのフィルハンドルで下へコピー！
これで画像のような感じになります。

お邪魔しました。m(_ _)m

お礼

この場をお借りしまして申し訳ないですが、みなさま本当にご回答ありがとうございます。
初心者の私に、大変わかりやすいご説明をありがとうございます。
便利さと楽しさに気づかせていただきました。
みなさまの知識にはまだまだ近づけませんが、勉強したい気持ちになりました。

いろいろなやり方がある中で、改良を考えてくださり、言葉の足りない私の希望にあうものを教えていただき、ありがとうございます。
休み明けの出社が大変待ち遠しくなりました。

本当にありがとうございます。

回答No.5

keithin

他の人に寄せられた追加質問を横取りするのも大変マナーの悪いことですが。

シート２のＡ２に
=IFERROR(INDEX(Sheet1!A:A,SMALL(IF(Sheet1!$E:$E>0,ROW(Sheet1!$E:$E)),ROW(A2))),"")
と記入して必ずコントロールキーとシフトキーを押しながらEnterで入力。下向けにコピー、Ｂ列にコピー。

シート２のＣ２に
=SUMIFS(Sheet1!E:E,Sheet1!A:A,A2,Sheet1!B:B,B2)
と記入して下向けにコピー。

回答No.4

KURUMITO

回答Ｎｏ3です。ごめんなさい。シート2のＣ1セルに日付を入力することでシート1のデータをシート2に表示させるのでしたね。
その場合にはすでに回答した内容のうちでシート2のＣ1セルにはお望みの日付を入力します。シート1のＥ1セルには次の式を入力します。

=Sheet2!C1

これでほかのすべては回答3と同じになります。

補足

みなさま、ありがとうございます。

私の質問の説明不足があります。

上段データ（シート１）は、もとになります。
シート１のE1に、検索したい出荷日（2012/3/1)を入力すると、
シート２に、2012/3/1に出荷のあったデータのみの
A1 オーダーNo.
B1 商品
C1　検索したい出荷日（2012/3/1)
という体裁のリストが自動的にほしいのです。

なので、シート２のC1には、＝Sheet1!E1 がくるかと思われます。

基本的に、No.3さんの式を入力しましたが、
シート２のA2に
=IF(ROW(A1)>COUNT(Sheet1!$E$2:$E$500),"",INDEX(Sheet1!$A:$E,SMALL(IF(Sheet1!$E$2:$E$500>0,ROW(Sheet1!$E$2:$E$500)),ROW(A1)),IF(COLUMN(A1)<=2,COLUMN(A1),IF(COLUMN(A1)=3,5,100))))
を入力しましたが、
なぜかオーダー１だけのデータしか、シート2に反映されません。

私のCtrl＋Shift＋Enter が、まちがっているのか
｛=IF(ROW(A1)>COUNT(Sheet1!$E$2:$E$500),"",INDEX(Sheet1!$A:$E,SMALL(IF(Sheet1!$E$2:$E$500>0,ROW(Sheet1!$E$2:$E$500)),ROW(A1)),IF(COLUMN(A1)<=2,COLUMN(A1),IF(COLUMN(A1)=3,5,100))))
となり、オーダー1のデータのみ反映されます。

重ねて、初心者の上、不慣れなのでことばの足りなさや、もしかしたら知らずにマナー違反をしていましたら、なにとぞお許しください。
よろしくお願いします。

回答No.3

KURUMITO

シート1にお示しの表が有るとしてＥ1セルに日付を入力したときの値はＥ2セルに次の式を入力して下方にドラッグコピーします。

=IF(COUNTIF(F2:AX2,E$1),INDEX(F2:AX2,MATCH(E$1,F2:AX2,0)+1),"")

そこでシート2にデータを表示させるのでしたらＡ1セルにオーダーＮｏ、Ｂ1セルに品名、Ｃ1セルに数量を表示させるとしてＡ2セルには次の式を入力して式を確定する段階でＣｔｒｌキーとＳｈｆｔキーを押しながらＥｎｔｅｒキーを押します。これで式の両側には｛　｝が表示されます。この式をＣ2セルまでドラッグコピーしたのちに下方にもドラッグコピーします。

=IF(ROW(A1)>COUNT(Sheet1!$E$2:$E$500),"",INDEX(Sheet1!$A:$E,SMALL(IF(Sheet1!$E$2:$E$500>0,ROW(Sheet1!$E$2:$E$500)),ROW(A1)),IF(COLUMN(A1)<=2,COLUMN(A1),IF(COLUMN(A1)=3,5,100))))

これでシート1のＥ1セルの日付を変えることで該当の表がシート2に表示されます。

お礼

ご回答ありがとうございます。
まとめてお礼を申し上げてしまい、大変失礼いたしました。
Ctrl+Shift+Enterがわかりました。
自分でも考えるきっかけがつかめました。
本当にありがとうございます。

回答No.2

keithin

シート２のＡ１からテンプレートを作成し、２行目から実際のデータを記入します。
表の体裁はあなたが作成した通り、Ａ列にオーダーNo、Ｂ列に商品、Ｃ１に出荷日とします

ご質問では説明がヌケていますが、出荷１，２，３をまとめて集計することにします。

Ｃ２には
=SUMIFS(Sheet1!G:G,Sheet1!A:A,A2,Sheet1!B:B,B2,Sheet1!F:F,$C$1) +SUMIFS(Sheet1!I:I,Sheet1!A:A,A2,Sheet1!B:B,B2,Sheet1!H:H,$C$1) +SUMIFS(Sheet1!K:K,Sheet1!A:A,A2,Sheet1!B:B,B2,Sheet1!J:J,$C$1)
のように集計します。

あぁ、それとも。
ご質問で全然書かれていませんが、「テンプレート」というのは実はこういう事を聞きたかったのですか？

シート１のＬ２に
=IF(OR(F2=Sheet2!$C$1,H2=Sheet2!$C$1,J2=Sheet2!$C$1),ROW(),"")
と記入し、リストの下端までコピー貼り付けておきます

シート２のＡ２には
=IFERROR(INDEX(Sheet1!A:A,SMALL(Sheet1!L:L,ROW(A1))),"")
シート２のＢ２には
=IFERROR(INDEX(Sheet1!B:B,SMALL(Sheet1!L:L,ROW(A1))),"")
とそれぞれ記入し、下向けにコピー貼り付けておきます

お礼

ご回答ありがとうございます。
まとめてお礼を申し上げてしまい、大変失礼いたしました。
言葉違いや言葉足らずで、申し訳ありませんでした。
みなさまからいろいろな方法を教えていただき、それぞれ試してみて、便利さと楽しさに気づかせていただきました。
じっくり関数の内容の理解の仕方を勉強したくなりました。
本当にありがとうございます。

回答No.1

aokii

出荷１，２，３のデータを一つの列に自動でまとめてから、出荷日でフィルタリングすることをお勧めします。

お礼

ご回答ありがとうございます。
まとめてお礼を申し上げてしまい、大変失礼いたしました。
前回のご回答から、さらにこうできたらいいなあ、と思いつくヒントをいただきました。
初心者ですが、エクセルって便利で楽しいなあと思い、もっと勉強したくなりました。
本当にありがとうございます。