エクセルで複数条件から別シートへの結果表示

質問

早速ですがお願いいたします。
データシートに

A B C D ・・・
佐藤　りんご　　固定　 　　3月
山田　みかん 　固定　 　　4月
田中　りんご　　変動1　　　4月
佐藤　りんご　　変動2　　　4月
鈴木　みかん 　固定　 　　4月
佐藤　イチゴ　　変動2　　　5月
山田　りんご　　固定　　 　5月
田中　イチゴ　　変動1　　 6月
（実際のデータは10列以上ある為、省略しています）

上記のようなときに、別シートで
【C列が固定】のものと、固定以外で【D列が該当月（仮に4月）】に該当するものを列単位で
抽出したいと思っています。

月別でシートを作成したく、抽出したものはシートとして残したい為
フィルタではなく、関数などでこのような条件が複数のものを表示するには
どうしたらよいでしょうか？
知恵をお貸し下さい。

回答No.6

tom04

親の仇のようにお邪魔します。

数式で表示されているデータをそのまま並び替えをしても
おそらく無理のはずです。

苦肉の策ですが、
仮にデータがSheet2に表示されている場合は
表示されている列すべてを範囲指定　→　右クリック　→　コピー　→　他のSheet（仮にSheet3）のA1セルを選択
→　右クリック　→　形式を選択して貼り付け　→　「値」を選択しOK

これだと生データになりますので、並び替えが可能になります。m(_ _)m

回答No.5

tom04

No.3・4です。

前回の投稿の中で
＞（尚、前回同様単に「4月」という文字列の場合はA2セルに「4月」と入力してください）
としましたが、
その場合Sheet1の作業列A2セルの数式が変わります。
=IF(OR(D2=Sheet2!A$1,E2=Sheet2!A$2),ROW(),"")
に訂正してください。

Sheet2の数式はそのままで大丈夫です。

何度も失礼しました。m（_ _）m

お礼

何度もありがとうございました。
シリアル値入力で、無事再現しシート名などを変更したもので
バッチリいきました！

抽出が出来たので「固定」などを基準に並び替えしようとしたのですが（O列）
反応しませんでした。

すごく基本的なことなのかもしれませんが、関数の入ったセルは並び替えには
対応してないのでしょうか？関数内で並び替えも行わせないと並ばない？など
面倒でなければお返事いただければと思います。

回答No.4

tom04

No.3です！
たびたびごめんなさい。

質問内容を早とちりしていました。
確かに前回の方法では「AND」条件になってしまいました。

「日付」の列はシリアル値で入れる方が一般的ではないのかな？と思いますので、
余計なお世話かもしれませんが、日付列がシリアル値の場合の方法を投稿してみます。

今回は↓の画像のようにA列を挿入（元のデータがB列以降にずれます）し、作業用の列として使っています。
そしてSheet2のA1セルに「条件1」をA2セルに検索したい月の数値のみを入力すれば表示されるように(s)ています。
（尚、前回同様単に「4月」という文字列の場合はA2セルに「4月」と入力してください）

Sheet1の作業列A2セルに
=IF(OR(D2=Sheet2!A$1,MONTH(E2)=Sheet2!A$2),ROW(),"")
という数式を入れオートフィルでずぃ～～～！っと下へコピーしておきます。
（データ以上にコピーしておいても構いません）

そしてSheet2のA4セルに
=IF(COUNT(Sheet1!$A:$A)<ROW(A1),"",INDEX(Sheet1!B:B,SMALL(Sheet1!$A:$A,ROW(A1))))
という数式を入れ、列方向・行方向にオートフィルでコピーすると
画像のような感じになります。

※　今回は配列数式を使っていませんので、列全体を範囲指定しています）
※　Sheet1の作業列が目障りであれば非表示にしておきます。

参考になりますかね？m（_ _）m

回答No.3

tom04

こんにちは！
一例です。
↓の画像で左側がSheet1で右側のSheet2に表示するようにしています。
Sheet2のG1・G2セルに検索条件を入力するとします。
Excel2007以降のバージョンだとすると

Sheet2のA2セルに
=IFERROR(INDEX(Sheet1!A$1:A$100,SMALL(IF(Sheet1!$C$1:$C$100=$G$1,IF(Sheet1!$D$1:$D$100=$G$2,ROW($A$1:$A$100))),ROW(A1))),"")

これは配列数式になってしまいますので、Shift＋Ctrl＋Enterで確定！
この画面からコピー＆ペーストする場合は
A2セルに貼り付け後、数式バー内で一度クリック！
編集可能になりますので、Shift＋Ctrlキーを押しながらEnterキーで確定！
数式の前後に｛　｝マークが入り配列数式になります。
このA2セルを列方向・行方向にオートフィルでコピーすると
画像のような感じになります。m（_ _）m

お礼

画像まで用意いただき、わかりやすい説明ありがとうございました。
教えていただきました方法でバッチリ再現しました。

ただ、いただいたやり方ですと「条件１と条件２」の両方を満たしたものしか表示されないようで
「条件１」と「条件１以外で条件２」を満たすものは表示できないようでした。
私の説明不足で、せっかく解答いただいたのに申し訳ございません。

教えていただいたのは、条件２つを満たす検索シートを別途作成予定でしたので
そちらで活用させていただきます。

回答No.2

KURUMITO

お示しのデータがシート1のＡ列から例えばＬ列まででデータは2行目から下方にあるとして作業列を作って対応します。
初めにお求めの表をシート2に作成するとしてシート2のＡ1セルには抽出の条件とでも文字列を入力し、Ｂ1セルには固定が優先して表示したければ固定と入力します。Ｃ1セルには取り出したい該当の月を4月なら4月と入力します。
そこでシート1での作業列ですがＭ2セルには次の式を入力してＮ2セルまで横にドラッグコピーしたのちに下方にもドラッグコピーします。

=IF(COLUMN(A1)=1,IF(AND($C2=Sheet2!$B$1,$D2=Sheet2!$C$1),MAX(M$1:M1)+1,""),IF(COLUMN(A1)=2,IF(AND($C2<>Sheet2!$B$1,$D2=Sheet2!$C$1),MAX($M:$M,$N$1:$N1)+1,"")))

お求めの表のシート2の2行目はシート1の項目名を並べます。
Ａ3セルには次の式を入力してＬ3セルまで横にドラッグコピーしたのちに下方にもドラッグコピーします。

=IF(ROW(A1)>MAX(Sheet1!$M:$M,Sheet1!$N:$N),"",IF(ROW(A1)<=MAX(Sheet1!$M:$M),IF(INDEX(Sheet1!$A:$L,MATCH(ROW(A1),Sheet1!$M:$M,0),COLUMN(A1))="","",INDEX(Sheet1!$A:$L,MATCH(ROW(A1),Sheet1!$M:$M,0),COLUMN(A1))),IF(INDEX(Sheet1!$A:$L,MATCH(ROW(A1),Sheet1!$N:$N,0),COLUMN(A1))=0,"",INDEX(Sheet1!$A:$L,MATCH(ROW(A1),Sheet1!$N:$N,0),COLUMN(A1)))))

これでシート1にデータが入力されれば該当の月のデータのみが区分けされて表示されます。

お礼

細かくありがとうございます。

今実際に試しているのですが（いただきましたL列までのデータ状況などを再現し、コピペで再現するように調整してから）
私の操作がどこか悪いらしく、シート１のMセル・Nセルへのコピペ。シート２のA3セル以降へのコピペを
完了しても、空白のままでした。

ただデータシートを一行消したりすると、エラーを出すようなので
私のやりかたがどこか悪いみたいなのですが・・・

また区分けして表示されるのはすごくありがたく
そこまで質問するのは欲張りかと、あとでソートしようとしていた為
すごく助かります。
どうにか再現できるようもう少し粘ってみたいと思います。

補足

質問主です。

すいません、どうにかと思い作業していてM2セル、N2セルへいれた式の中で
M1、N1を参照しているようなのですが、この二つは空欄（１行目は項目名でL列までの為）
なのですが、そのまま空欄でよかったでしょうか？

回答No.1

aokii

ピボットテーブルを使ってみてください。

お礼

こんな機能もあるんですね。
アドバイスありがとうございます。

勉強になりました。