エクセルで条件に合うものを別シートに抽出（関数）

希望日　　　　りんご　　　ばなな　　　みかん　　　担当　　　配達チェック
6月1日　　　　　　2　　　　　　　　　　　　　1　　　　 山田
6月3日　　　　　　　　　　　　　4　　　　　　2　　　　 佐藤　　　　　　
6月1日　　　　　　3　　　　　　　　　　　　　　　　　　 山田　　　　　　済
6月2日　　　　　　　　　　　　　1　　　　　　　　　　　 佐藤　
6月2日　　　　　　　　　　　　　　　　　　　　2　　　　 山田

というデータがｓｈｅｅｔ１に入力されています。
＜条件＞
　・担当　山田のもの
　・6月2日以前（このデータでは1日と2日）
　・配達チェックが済でないもの（空白です）

この条件を満たすものをｓｈｅｅｔ２に抽出したいのです。
希望日　　　　りんご　　　ばなな　　　みかん　　　担当　　　配達チェック
6月1日　　　　　　2　　　　　　　　　　　　　 1　　　　 山田
6月2日　　　　　　　　　　　　　　　　　　　　2　　　　 山田
とｓｈｅｅｔ２に表示させるには関数でできますか？
できれば、セル参照でＡ１セルに6月2日、Ｂ１に山田と入力すると抽出されるととても便利なのですが・・・。
皆様のお知恵を拝借させてください！！

No.1 ベストアンサー 回答者： tom04 回答日時： 2011/06/07 23:28

こんばんは！

一例です。

↓の画像でSheet1に作業用の列を設けています。
作業列G2セルに
=IF(COUNTBLANK(Sheet2!$A$1:$B$1),"",IF(AND(A2<=Sheet2!$A$1,E2=Sheet2!$B$1,F2=""),ROW(),""))
という数式を入れオートフィルでずぃ～～～！っと下へコピーしておきます。

そしてSheet2のA4セルに
=IF(COUNT(Sheet1!$G:$G)<ROW(A1),"",INDEX(Sheet1!A:A,SMALL(Sheet1!$G:$G,ROW(A1))))
という数式を入れ列方向と行方向にオートフィルでコピー！

A列の表示形式は「日付」にしておきます。
ただ、このままではSheet1に空白セルがあれば「0」が表示されてしまいますので、
当方使用のExcel2003の場合ですが、
メニュー　→　ツール　→　オプション　→　「表示」タブ　→　「ゼロ値」のチェックを外すと
画像のような感じになります。

以上、参考になれば良いのですが・・・m(__)m

この回答へのお礼

ありがとうございます！！

なるほど、そういうふうに組み合わせればいいのですね！
勉強になりましたっ
画像まで添付していただきとてもわかりやすかったです(^o^)

お礼日時：2011/06/08 23:11

No.6 回答者： imogasi 回答日時： 2011/06/08 11:18

もう回答は出尽くしていますが、いつも気になることを書きます。

この種の質問は週に３度ぐらい出ます。
タイプは「条件付き抜き出し問題」です。
しかし関数でやるのは、１セル当たりでも非常に長い式で、関数の５式以上の組み合わせになる式になります。
エクセルに検索に適した関数が作られてないことが原因です。
初心者には、自分で生み出せる式でなく、式の意味もわからないと思う。コピペして出来ましたと、其れで良い人は使ったら良い。しかし質問者の場合への修正も一苦労。
ーー
解法のタイプは（（１）（２）（３）は広く言えば関数による解法でしょう）
（１）A　作業列を使う
　　　B　使わない
（２）A　配列数式を使う
　　　B 使わない（通常の式）
（３）A ユーザー定義関数を使うー　一種のVBA
　　B 使わない。
（４）フィルタ
の別になると思います。
ーーー
ですから、私は
（１）操作　フィルタ
を使うことをすすめます。
（２）VBA
（３）特別のソフト（フリーソフト）ただし見つけるのが難しい
を考える人もあるでしょう。
ーーー
私は長年「imogasi方式」というのを書いてきました。
上記で言うと（１）のAにあたります
Googleで「imogasi方式」で照会すれば、沢山の質問回答の例が出て、私以外の、今回既に出ているタイプの回答も毎回出ています。
ーー
imogasi方式では
例データ　　Sheet3
　A列ーH列（G列は飛ばし）　－－は左寄せ表示防止のために入れたもので、実際は空白セル。山田と日付（A1toB1セル）が条件を入れるセルと仮定。
山田2011/6/2
希望日りんごばななみかん担当配達チェック
6月1日2--1山田1
6月3日--42佐藤
6月1日3----山田済
6月2日--1--佐藤
6月2日------山田2
H列が作業列で、H3の式は
=IF(AND(E3=$A$1,A3<=$B$1,F3=""),MAX($H$1:H2)+1,"")
下方向に式を複写してます。
やっていることは、条件に合う行に連番を振ってます。連番にしているところがミソです。
ーー
Sheet４　　のA3で
=INDEX(Sheet2!$A$1:$F$100,MATCH(ROW()-2,Sheet2!$H$1:$H$100,0),COLUMN())
F列まで式を複写
A３-F３の式を下方向に式を複写
A列の表示形式を日付、B,C,D列の表示形式を数値に設定。
ーー
結果　Sheet3　A3から
2011/6/12 0 1 山田0
2011/6/20 0 0 山田0
ーー
残された問題点
・０を空白にする　ーー＞（表示形式で[=0]"";G/標準　の設定など）
・下の行の#N/Aを出さないようにする
は略（上記Googleで「imogasi方式」で照会して出てくる記事に）WEBには書いています）

No.5 回答者： mike_g 回答日時： 2011/06/08 02:46

[No.4]の訂正

添付図の左下図が「Sheet1」となっているのは「Sheet2」の間違いです。
なお、その上の図は「Sheet1」で正しいです。

No.4 回答者： mike_g 回答日時： 2011/06/08 02:41

[フィルタオプションの設定]による方法を案内しておきます。

添付図参照

１．Sheet2 のセル C2 に次式を入力
　　　=AND(Sheet1!A2<=A$1,Sheet1!E2=B$1,Sheet1!F2="")
　　なお、セル C1 は空白のままにしておく
２．Sheet2 をアクティブにして、[データ]→[フィルタ]→[フィルタ
　　オプションの設定]を実行して、次の設定を行なった後で[OK]を
　　クリック
　　　[抽出先]→　　　 “指定した範囲”
　　　[リスト範囲]→　 Sheet1!$A$1:$F$10
　　　[検索条件範囲]→ $C$1:$C$2
　　　[抽出範囲]→　　 $A$3:$F$3

この回答へのお礼

ありがとうございました

「フィルタオプションの設定」初めてこの機能を知りました！
勉強になりました。

お礼日時：2011/06/08 23:50

No.3 回答者： MackyNo1 回答日時： 2011/06/08 00:11

補助列なしにSheet2のA3セル以下に該当データを表示させたいなら、以下のような関数を入力して下方向にオートフィルしてください。

F3セル：
=INDEX(Sheet1!E:E,SMALL(INDEX(((Sheet1!$A$2:$A$100>$A$1)+(Sheet1!$E$2:$E$100<>$B$1)+(Sheet1!$F$2:$F$100="済"))*1000+ROW($A$2:$A$100),),ROW(E1)))&""

A3セル：（右方向にD3セルまでオートフィル）
=IF($E3="","",INDEX(Sheet1!A:A,SMALL(INDEX(((Sheet1!$A$2:$A$100>$A$1)+(Sheet1!$E$2:$E$100<>$B$1)+(Sheet1!$F$2:$F$100="済"))*1000+ROW($A$2:$A$100),),ROW(A1))))

B3：D3セルの空白部分に0を表示しないのであれば、その範囲を右クリックしてセルの書式設定の表示形式をユーザー定義にして「0;;」と入力してください。

＃エクセルのバージョンが記載されていないので、すべてのバージョンに対応できる数式を示しましたが、この数式は必ずしも最適な方法とは限りません。
Officeソフトはバージョンによって使用できる機能や操作方法が異なりますので、ご質問の際には必ずバージョンを明記するようにしましょう。

この回答へのお礼

ありがとうございます

いろいろな方法があるのですね！
勉強になりました！

お礼日時：2011/06/08 23:19

No.2 回答者： hishosaryu 回答日時： 2011/06/07 23:30

商業高校卒業してます。

２９歳主婦です。
参考までに聞いてください。

別のシートに、
条件があう情報を抜き出して一覧表示したい、
ということですよね？？

別のシートに表示させる必要がない方法を教えます。
違ったらスルーしてくださいね☆

まず、入力した『希望日』から『配達チェック』までをドラッグします。
※日付や数字を入力したところは含まず、見出しの行のみ。
上部の『データ』タブを開いて、『フィルタ』を選択して、
『オートフィルタ』を選択すると、各見出しの右端にボタンが現れます。
自分の欲しい希望日のみを表示したいときは、
希望日のボタンを押して、（例）６月２日を選ぶと６月２日のみ表示されますし、
更にその中から、担当が山田のもののみ表示させたいときは、
希望日で６月２日と操作したボタンはそのままにして、
更に担当のボタンをおして『山田』を選択してください。
希望日は関係なく、山田担当のもののみ表示させい時は、
希望日で操作したボタンを『すべて』に戻す必要があります。

長くなりましたが、オートフィルタボタンを使うと、
それらを更に昇順や降順にも並べ替えられますから、便利ですよ。
データが消えるわけではないですし、
使い勝手いいと思います。
頑張ってください！！

この回答へのお礼

ありがとうございます

説明不足で申し訳ありませんが、今現在オートフィルタで処理しているのです。
ですが担当者が非常に多くフィルタで抽出する時間と手間が非常にかかるので別シートに抽出したかったのです。

お礼日時：2011/06/08 23:16