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


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



sheet1
 A B  C      D       E       F
1  顧客ID  サービス名   加入日    ステータス  顧客名
2  00001    ニコニサービス 2010/9/19  退会     ABC土木
3  00002    ニコニサービス 2010/10/20  停止     ABC海運
4  00003    ニコニサービス 2010/11/12  加入     ABC銀行
5  00004   ニコニサービス  2010/12/23  停止     ABC保険
6  00005   ニコニサービス  2011/1/10  退会     ABC食品
7  00006   ニコニサービス  2011/2/13  加入     ABC不動産
8  00007   ニコニサービス  2011/3/30  加入     ABCガス
9  00008   ニコニサービス  2011/4/10  加入     ABCシステム


sheet2
 A  B  C  D  E  F
1 期限の絞込み          
2 期先     期後
3 2010/9    2011/3 
4
5

このQ&Aに関連する最新のQ&A

A 回答 (6件)

回答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日までのデータを拾うことにはなりません。そこで式の上で月末の日付となるように訂正しています。
    • good
    • 0

式はできるだけ分かり易く簡単な式を使うことがデータが多いくなる場合には特に必要です。


作業列を使って対応しましょう。
シート1ではA1セルに顧客IDから始まってE1セルには顧客名までが入力されているとします。
作業列としてF1セルには次の式を入力して下方にオートフィルドラッグします。

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

勿論、シート2ではA3セルに期先が、B3セルに期後のデータが入力されているとします。
お示しの表示は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)))
    • good
    • 0

 回答番号: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)))))
    • good
    • 0

 まず、作業列として使用する適当な列(例えば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セルに期後を入力すると、抽出結果が自動的に表示されます。
    • good
    • 0

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



シート2:
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))
右にコピー,下にコピー。
「Excel2007にて期間の絞込みをした」の回答画像2
    • good
    • 0

ご要望と1行ずれですが如何でしょうか。


(1)Sheet2のA2,B2に「加入日」を入力、C2に「ステータス」を入力、A3に>=2010/9/1を入力、B3に<=2011/3/30を入力、C3に「停止」、C4に「加入」を入力
(2)Sheet2のA5を選択→データタブの詳細設定→「指定した範囲」を選択、リスト範囲にSheet1!A:E、検索条件欄にA2:C4、抽出範囲欄にA5→OK
    • good
    • 0

お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!


人気Q&Aランキング

おすすめ情報