Excel2007にて期間の絞込みをしたい

質問

下記のsheet1のデータを利用して、
加入日が「2011年1月」以前の
データとステータスが「加入中及び停止」の顧客名を
sheet2に出力する関数(VlookUp?)が分からなくて悩んでいます。
sheet2のA3に期先を入れて、B3期後を入れると
下記の条件の顧客を抽出したい。
期間の絞込みをしたい。
Excel2007を使用しています。

例
加入期間が2010/9～2011/4で
ステータスが加入及び停止の
顧客を全てsheet2のA4以下に表示。

sheet1
　Ａ　Ｂ　 Ｃ　　　　　 Ｄ　　　　　 　Ｅ　　　　　　 F
１　　顧客ＩＤ　 サービス名　　　加入日　　　 ステータス　　顧客名
２　　00001 　　 ニコニサービス　2010/9/19　 退会　　　　 ＡＢＣ土木
３　　00002 　　 ニコニサービス　2010/10/20　 停止　　　　 ＡＢＣ海運
４　　00003 　　 ニコニサービス　2010/11/12　 加入　　　　 ＡＢＣ銀行
５　　00004 　　ニコニサービス　 2010/12/23　 停止　　　　 ＡＢＣ保険
６　　00005 　　ニコニサービス　 2011/1/10　 退会　　　　 ＡＢＣ食品
７　　00006 　　ニコニサービス　 2011/2/13　 加入 　　　 ＡＢＣ不動産
８　　00007 　　ニコニサービス　 2011/3/30　 加入 　　　 ＡＢＣガス
９　　00008 　　ニコニサービス　 2011/4/10　 加入 　　　 ＡＢＣシステム

sheet2
　Ａ　 Ｂ　 Ｃ　 Ｄ　 Ｅ　 F
1 期限の絞込み　　　　　　　　　　
2　期先　　　　　期後
3　2010/9　　　　2011/3　
4
5

回答No.6

KURUMITO

回答No5です。シート1のF2セルに入力する式は次のように訂正してください。他の操作はNo5と全く同じです。

=IF(AND(AND(C2>=Sheet2!$A$3,C2<=DATE(YEAR(Sheet2!$B$3),MONTH(Sheet2!$B$3)+1,0)),OR(D2="加入",D2="停止")),MAX(F$1:F1)+1,"")

上の式ではシート2で期後としての日付が2011/3となっていたとしても数式バー上では2011/3/1の表示になっている場合には3月31日までのデータを拾うことにはなりません。そこで式の上で月末の日付となるように訂正しています。

回答No.5

KURUMITO

式はできるだけ分かり易く簡単な式を使うことがデータが多いくなる場合には特に必要です。
作業列を使って対応しましょう。
シート１ではA１セルに顧客IDから始まってE１セルには顧客名までが入力されているとします。
作業列としてF１セルには次の式を入力して下方にオートフィルドラッグします。

=IF(AND(AND(C2>=Sheet2!$A$3,C2<=Sheet2!$B$3),OR(D2="加入",D2="停止")),MAX(F$1:F1)+1,"")

勿論、シート２ではA３セルに期先が、B３セルに期後のデータが入力されているとします。
お示しの表示は2010/9のようになっていますがこれは数式バー上では2010/9/1と表示されていることが必要です。セルに201/9/1と入力してからセルの書式設定の表示形式でユーザー定義からyyyy/mのようにすればセルには2010/9と表示されますね。
シート2のA4セルからE4セルまでにはシート1の項目名を入力してから、A5セルには次の式を入力してE5セルまでオートフィルドラッグしたのちに下方にもオートフィルドラッグします。

=IF(ROW(A1)>MAX(Sheet1!$F:$F),"",INDEX(Sheet1!$A:$E,MATCH(ROW(A1),Sheet1!$F:$F,0),COLUMN(A1)))

回答No.4

kagakusuki

回答番号:ANo.3です。
　使用しているExcelのバージョンの関係で、動作確認が出来ないのですが、先程の回答におけるSheet2のA4に入力する数式を、次の数式に変更すれば、Sheet3に作業列を設けずに、関数のみで抽出する事が出来ると思います。

=IF(ROWS($4:4)>COUNTIFS(Sheet1!$C:$C,">="&$A$3,Sheet1!$C:$C,"<="&$B$3,Sheet1!$D:$D,"加入")+COUNTIFS(Sheet1!$C:$C,">="&$A$3,Sheet1!$C:$C,"<="&$B$3,Sheet1!$D:$D,"停止"),"",INDEX(Sheet1!A:A,SUMPRODUCT(ROW(Sheet1!A$1:INDEX(Sheet1!A:A,MATCH(9^9,Sheet1!$C:$C)))*(Sheet1!$C$1:INDEX(Sheet1!$C:$C,MATCH(9^9,Sheet1!$C:$C))>=$A$3)*(Sheet1!$C$1:INDEX(Sheet1!$C:$C,MATCH(9^9,Sheet1!$C:$C))<=$B$3)*OR(Sheet1!$D$1:INDEX(Sheet1!$D:$D,MATCH(9^9,Sheet1!$C:$C))="加入",Sheet1!$D$1:INDEX(Sheet1!$D:$D,MATCH(9^9,Sheet1!$C:$C))="停止")*(COUNTIFS(Sheet1!$C$1:INDEX(Sheet1!$C:$C,ROW(Sheet1!A$1:INDEX(Sheet1!A:A,MATCH(9^9,Sheet1!$C:$C)))),">="&$A$3,Sheet1!$C$1:INDEX(Sheet1!$C:$C,ROW(Sheet1!A$1:INDEX(Sheet1!A:A,MATCH(9^9,Sheet1!$C:$C)))),"<="&$B$3,Sheet1!$D$1:INDEX(Sheet1!$D:$D,ROW(Sheet1!A$1:INDEX(Sheet1!A:A,MATCH(9^9,Sheet1!$C:$C)))),"加入")+COUNTIFS(Sheet1!$C$1:INDEX(Sheet1!$C:$C,ROW(Sheet1!A$1:INDEX(Sheet1!A:A,MATCH(9^9,Sheet1!$C:$C)))),">="&$A$3,Sheet1!$C$1:INDEX(Sheet1!$C:$C,ROW(Sheet1!A$1:INDEX(Sheet1!A:A,MATCH(9^9,Sheet1!$C:$C)))),"<="&$B$3,Sheet1!$D$1:INDEX(Sheet1!$D:$D,ROW(Sheet1!A$1:INDEX(Sheet1!A:A,MATCH(9^9,Sheet1!$C:$C)))),"停止")=ROWS($4:4)))))

回答No.3

kagakusuki

まず、作業列として使用する適当な列（例えばSheet3のA列）を決めて下さい。
　次に、Sheet3のA1セルに次の数式を入力して下さい。

=IF(AND(Sheet1!$C1>=Sheet2!$A$3,Sheet1!$C1<=Sheet2!$B$3,OR(Sheet1!$D1="加入",Sheet1!$D1="停止")),ROW(Sheet1!$A1),"")

　次に、Sheet3のA1セルをコピーして、Sheet3のA2以下に貼り付けて下さい。
　次に、Sheet2のA4セルに次の数式を入力して下さい。

=IF(ROWS($4:4)>COUNT(Sheet3!$A:$A),"",INDEX(Sheet1!A:A,SMALL(Sheet3!$A:$A,ROWS($4:4))))

　次に、Sheet2のA4セルをコピーして、Sheet2のB4～F4の範囲に貼り付けて下さい。
　次に、Sheet2のC4セルの書式設定を[日付]として下さい。
　次に、Sheet2のA4～F4の範囲をコピーして、同じ列の5行目以下に貼り付けて下さい。

　後は、Sheet2のA2セルに期先を、B3セルに期後を入力すると、抽出結果が自動的に表示されます。

回答No.2

keithin

シート１のABCDE列に顧客IDから顧客名まで５列のデータを置いて。

シート２：
A4:
=INDEX(Sheet1!A:A,SMALL(IF(($A$3<=Sheet1!$C$1:$C$999)*(Sheet1!$C$1:$C$999<=$B$3)*(Sheet1!$D$1:$D$999={"加入","停止"}),ROW(Sheet1!$C$1:$C$999),9999),ROW(A1)))&""
を記入して，コントロールキーとシフトキーを押しながらEnterで入力。
下にコピー。

B4:
=IF($A4="","",VLOOKUP($A4,Sheet1!$A:$E,COLUMN(),FALSE))
右にコピー，下にコピー。

回答No.1

mu2011

ご要望と１行ずれですが如何でしょうか。
（１）Sheet2のA2,B2に「加入日」を入力、C2に「ステータス」を入力、A3に>=2010/9/1を入力、B3に<=2011/3/30を入力、C3に「停止」、C4に「加入」を入力
（２）Sheet2のA5を選択→データタブの詳細設定→「指定した範囲」を選択、リスト範囲にSheet1!A:E、検索条件欄にA2:C4、抽出範囲欄にA5→ＯＫ