[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『複数の日付から条件を抽出し、抽出された日付から該当する範囲を色付けしその時間を足して表示する。』（＿|￣|○）
エクセルの知識がなく困ってます。。
どなたかご教授下さい。
A B C D E F G H 1佐 1/1 2/1 3/1 4/1 5/1 2藤 3:00 3:00 1:00 0:30 1:00 3さ 6/1 7/1 8/1 9/1 10/1 4ん 2:00 1:00 0:10 0:30 0:10 5
B1の1/1〜F3の10/1の入力された日付のうち、直近で180日（6ヶ月）の期間のうち日付の下に入力された日付を足して6:00以上となる範囲を抽出し、該当する範囲を色付けして表示。さらに、それらの時間を足してH1に表示させるにはどうしたら良いでしょうか？
例）4/1〜10/1が1番直近だが、180日（6ヶ月）で時間を足しても6時間未満（5:20）なので非該当。
3/1〜9/1だと2番目に直近で180日（6ヶ月）で時間を足すと6時間以上（6:10）なので、この範囲を抽出し該当する期間を色付け、さらにこの範囲の時間を足してH1へ表示したい。
どうか、よろしくお願いいたします。
＜ 使用 Excel：Excel2013、使用 OS：Windows7 ＞
1.実際の表の規模は？(ご提示は5列×4行ですが、本当はもっと大きい or どんどん大きくなっていくのでは？)
2.「直近」の基準日は？(例えば投稿日が基準日なら、直近は10/1ではなく2/1なのですが。なんの日かを基準日として、しかも範囲を遡るということですよね？ それがはっきり読み取れるようにしてください)
3.「6ヶ月」ではなく「180日」で考えるべきものですか？
など。
通勤の片手間ですので、上記確認だけで完全に掴めるか分かりませんが、差し当たってこれくらいはないと、少なくとも私には回答のしようが(考えようが)なさそうです。
（電話番子） 2018/02/21(水) 07:40
余計に掘り起こしてしまっただけかもしれません。
であればお返事いただかなくて構いませんので、無視しておいてください。
（電話番子） 2018/02/21(水) 08:24
＞4/1〜10/1が1番直近だが、180日（6ヶ月）で
１．前回は、単に180日前でしたけど、今回は6ヶ月前ですか？ でも、それって実質7ヶ月間だと思うんですけど、それでいいんですか？
２．データの構成についてですが、 日付は、各月の1日が入っており、月々は連続していると決め打ちしていいですか？
（半平太） 2018/02/21(水) 09:40
2:直近の基準日は？
これは、表の10/1を（2017.10.1）とし、1/1は（2017.1.1）とします。
10/1が直近の日付となりますが、例であげたように条件に合うところを抽出したいと思ってます。
3:6ヶ月or180日？
これは、180日です。（上の表だと例にあげた計算が合わなくなるので、下に新しく表を修正します）
（＿|￣|○） 2018/02/21(水) 15:59
A B C D E F G H 1佐 1/1 2/1 3/30 4/1 5/1 2藤 3:00 3:00 1:00 0:30 1:00 3さ 6/1 7/1 8/1 9/1 10/1 4ん 2:00 1:00 0:10 0:30 0:10 5 （＿|￣|○） 2018/02/21(水) 16:02
2:日付は月々は連続しないこともあります。
（＿|￣|○） 2018/02/21(水) 16:05
＞これについては、1人（11列×4行）で考えています。
2行分が日付とすると、22箇月分ですか？ それとも、12箇月分だが、各月は初日とも限らないし、各月複数日になることがあると言うことですか？
＞4/1〜10/1が1番直近だが、180日（6ヶ月）で時間を足しても6時間未満（5:20）なので非該当。 ＞180日となります。
10/1より180日前だと、4/4ですから、4/1は含まれないですよね？
なんか、条件が曖昧に感じるんですけど・・
（半平太） 2018/02/21(水) 17:36
枠に入らなくなった場合は古い日付を消して、新しい日付を更新して使う予定です。
2:申し訳ありません。修正したのを下に貼り付けたいと思います。
（＿|￣|○） 2018/02/23(金) 10:03
A B C D E F G H 1佐 3/8 3/10 8/2 8/10 9/13 2藤 0:20 0:10 1:00 3:00 2:00 3さ 11/25 12/11 1/30 2/12 2/16 4ん 0:40 0:20 0:40 0:20 0:20 5
2/16=2018.2.16とし、最新の日付とします。
例）2/16から180日遡った日付は、8/20（2017.8.20）となり、表の中で該当する期間は2/16（2018.2.16）〜9/13（2017.9.13）となります。その期間に該当する日付の下に入力された時間を足すと4:20となります。しかし、抽出したいのは180日間で6時間以上となる範囲なので、この期間は抽出しません。
この表の中で、もっとも日付が新しく、180日の期間で6時間以上となるのが、1/30（2018.1.30）から8/10（2017.8.10）の期間が180日6時間以上（6:40）となります。この期間の日付を色付けして、足した時間をH1に表示する。
あくまで、日付が最も新しい期間となる範囲で6時間以上となる箇所を抽出します。
（＿|￣|○） 2018/02/23(金) 10:55
B列から11列だと、実際はL列までですね？
O1(オー1）セルに所定の直近日を出すとして、N1セルに合計時間を出す場合
O1セル =SMALL(B1:L3,LOOKUP(1,0/(SUMIFS(B2:L4,B1:L3,"<="&SMALL(B1:L3,ROW($A$1:$A$21)),B1:L3,">="&SMALL(B1:L3,ROW($A$1:$A$21))-180,B1:L3,">"&100*ROW($A$1:$A$21))>"5:59:30"*1),ROW($A$1:$A$21)))
N1セル =SUMIFS(B2:L4,B1:L3,"<="&O1,B1:L3,">="&O1-180) ↑ ↑ O1(オー1） O1(オー1）
条件付き書式を B1以下、対象範囲全体に設定する。 条件式は ↓ =ABS(OFFSET($O1,-MOD(ROW()-1,4),0)-90-B1)<=90
（半平太） 2018/02/23(金) 17:48
＞o1セルに入力しましたが、#N/Aと表示されてしまいました
はれ？ 済みません。
6時間以上のケースが無いとそうなりますね。
「超過なし」とでも表示することにします。
O1セル =IFERROR(SMALL(B1:L3,LOOKUP(1,0/(SUMIFS(B2:L4,B1:L3,"<="&SMALL(B1:L3,ROW($A$1:$A$21)),B1:L3,">="&SMALL(B1:L3,ROW($A$1:$A$21))-180,B1:L3,">"&100*ROW($A$1:$A$21))>"5:59:30"*1),ROW($A$1:$A$21))),"超過なし")
（半平太） 2018/02/24(土) 00:08
ご提示のサンプルデータだと、当方ではこんな結果になっているんですけどねぇ。そちらではそうならないですか？
<結果図> 行 _A_ _____B_____ _____C_____ ____D____ ____E____ ____F____ _G_ _H_ _I_ _J_ _K_ _L_ _M_ __N__ ____O____ 1 1佐 2017/3/8 2017/3/10 2017/8/2 2017/8/10 2017/8/16 6:40 2018/1/30 2 2藤 0:20 0:10 1:00 3:00 2:00 3 3さ 2017/11/25 2017/12/11 2018/1/30 2018/2/12 2018/2/16 4 4ん 0:40 0:20 0:40 0:20 0:20
あと、このトラブルとは関係ないですが、何かの拍子に行削除をやったらしくて、22になるべき所、21になっていましたので 21行目の上にでも1行挿入して、数式を自動修正してください。 ↓ ＞ O1セル =IFERROR(SMALL(B1:L3,LOOKUP(1,0/(SUMIFS(B2:L4,B1:L3,"<="&SMALL(B1:L3,ROW($A$1:$A$21)),・・・
（半平太） 2018/02/24(土) 09:28
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.