下記の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
A 回答 (6件)
- 最新から表示
- 回答順に表示
No.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日までのデータを拾うことにはなりません。そこで式の上で月末の日付となるように訂正しています。
No.5
- 回答日時:
式はできるだけ分かり易く簡単な式を使うことがデータが多いくなる場合には特に必要です。
作業列を使って対応しましょう。
シート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)))
No.4
- 回答日時:
回答番号: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
- 回答日時:
まず、作業列として使用する適当な列(例えば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
- 回答日時:
シート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))
右にコピー,下にコピー。
No.1
- 回答日時:
ご要望と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
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- Excel(エクセル) 至急です><Excelの関数を教えてください。 2 2022/03/22 17:56
- Excel(エクセル) エクセルの数式で教えてください。 2 2023/03/10 08:51
- 消費者問題・詐欺 お金を取り返すことは可能でしょうか? 4 2023/01/07 13:17
- その他(開発・運用・管理) マイクロソフト製品のライセンス販売で困ってます 1 2022/06/30 19:52
- 雇用保険 失業保険給付の条件について 1 2022/05/27 14:15
- Access(アクセス) 対象月の2桁表示について 1 2023/01/07 05:08
- その他(データベース) accessについて 2 2022/05/31 16:58
- Visual Basic(VBA) 指定月分の顧客データファイルを統合して並べ替え、所定の場所に貼り付ける 3 2022/09/10 07:55
- その他(プログラミング・Web制作) google formsを使ったタスク依頼フォーム作成におけるご相談 1 2023/06/22 15:55
- Visual Basic(VBA) ユーザーフォーム「frm_基本❶」を立ち上げると新規で入力する行数を右下のNoとして表示しています。 1 2023/03/16 19:02
関連するカテゴリからQ&Aを探す
おすすめ情報
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
【スプレドシート】IMPORTRANGE...
-
VLOOKUP関数について
-
英数字のみ全角から半角に変換
-
Microsoft Formsで「応答」から...
-
1つのPCに「Excel 2010」「Exc...
-
マイクロソフト オフィスについて
-
【スプレッドシート】指定の日...
-
Office 2021 Professional Plus...
-
【Microsoft Forms】回答を削除...
-
会社PCのメールが更新されない
-
vb.net オブジェクト指向につい...
-
outlookのメールが固まってしま...
-
Office2021を別のPCにインスト...
-
Microsoft Formsの「個人情報や...
-
【スプレッドシート】白色のセ...
-
エクセルマクロ(超初心者)
-
Excel VBA 日程表からスケジュ...
-
エクセルでXLOOKUP関数...
-
マイクロソフト 一時使用コード...
-
teams設定教えて下さい。 ①ビデ...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
【スプレドシート】IMPORTRANGE...
-
マイクロソフト 一時使用コード...
-
英数字のみ全角から半角に変換
-
Office2021を別のPCにインスト...
-
Microsoft Formsの「個人情報や...
-
officeビジネス型のワードやエ...
-
会社PCのメールが更新されない
-
【スプレッドシート】指定の日...
-
Microsoft Officeを2台目のPCに...
-
何このステータスバー
-
2つのシートの一致する行のセ...
-
会社のTeamsのことで相談です。...
-
エクセルにリンクされるのをし...
-
Windows 11で、IME言語バー(IM...
-
office2010とoffice365の共存で...
-
Microsoftのパソコンです。 エ...
-
エクセルでXLOOKUP関数...
-
Excel関数について質問ですm(__)m
-
VBA
-
自分の専門分野の仕事。初見で...
おすすめ情報