[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『期間の重複を数えたい』（だめもんど）
条件付き書式にて処理したいのですが、どう設定すればいいのかわかりません 教えてください
シートに以下の表があります
A B C 1 件名 開始日 終了日 2 北海道 2017/5/1 2017/5/31 3 長野 2017/4/11 2017/4/30 4 岐阜 2017/5/15 2017/5/31 5 福井 2017/6/12 2017/6/30 6 広島 2017/7/14 2017/8/31 ← 7 佐賀 2017/7/16 2017/7/31 8 島根 2017/8/14 2017/8/31 ← 9 沖縄 2017/8/1 2017/8/31 ←
開始日から終了日までを期間とし、その期間が３件以上重複する場合、その重複する行を塗りつぶしたいです [1日]だけでも３件の重複があればぬりつぶしたいのですが、さっぱりわかりません 上記なら、6行目と8行目と9行目の期間が重複しますので、その行を塗りつぶしたいです
よろしくお願いいたします
＜ 使用 Excel：Excel2010、使用 OS：Windows7 ＞
過去検索にて [[20080602154954]] 。。。Ms.Rin〜♪♪さんの式を参考に解決いたしました
=SUMPRODUCT(($C$2:$C$9>=$B2)*($B$2:$B$9<=$C2))>2
SUMPRODUCTがやっぱりいまいちピンとこないので、そこを重点的に練習したいと思います ありがとうございました （だめもんど） 2017/08/04(金) 17:53
念のため確認。
例えば 2017/5/1 2017/5/31 2017/5/1 2017/5/10 2017/5/11 2017/5/21 のような場合、「2017/5/1 2017/5/31」だけ色がつくことになるがそれで構わないだろうか？
（ねむねむ） 2017/08/07(月) 10:24
⇒ねむねむさん⇒ 妥協した結果、日付だけ赤くしました……
本当は行全体が変わるのが理想なんですが、方法はございますでしょうか？？？ （だめもんど） 2017/08/07(月) 14:25
私の質問に対する回答はないのでしょうか？
（ねむねむ） 2017/08/07(月) 15:56
⇒ねむねむさん⇒ 大変失礼いたしました。 読み間違えてとんちんかんな返信をしてしまい申し訳ございません
3件以上重複の場合、一番最初に出てきた行のみ塗りつぶされるということでしょうか？ どれが重複しているかも判断したいので、重複している行すべてが塗りつぶされてほしいです
（だめもんど） 2017/08/07(月) 17:03
そうではなく「2017/5/1 2017/5/31」は他の２件と重複しているが「2017/5/1 2017/5/10」と「2017/5/11 2017/5/21」は 「2017/5/1 2017/5/31」としか重複していなく、「2017/5/1 2017/5/10」と「2017/5/11 2017/5/21」は重複していない。
この場合はどうするのか？
（ねむねむ） 2017/08/07(月) 17:12
⇒ねむねむさん⇒ 理解力がとぼしいうえ、きちんと読んでいなくて、更に説明不足で再々申し訳ございません
ご提示の場合、２件重複とカウントして、塗りつぶしなしとしたいです
北海道 5/1 5/10 東京 5/1 5/4 愛知 5/5 5/10 大阪 5/4 5/6 福岡 5/6 5/10
上記を図にすると、
5/1 5/2 5/3 5/4 5/5 5/6 5/7 5/8 5/9 5/10
北海道 ←――――――――――――――――――――――→
東京 ←―――――――→
愛知 ←――――――――――――→
大阪 ←―――――→
福岡 ←――――――――――→
となり、日付の列で３つ以上重なっているかどうかを判断したいのです
上記の場合カウントの仕方は、 1.北海道・東京・大阪 が 5/4で 3件重複 2.北海道・愛知・大阪 が 5/5で 3件重複 3.北海道・愛知・大阪・福岡 が 5/6で 4件重複 4.北海道・愛知・福岡 が 5/7〜5/10で 3件重複 となり、すべてぬりつぶしたいです
どれがどれと重複しているかはわからなくていいのですが、 重複が３件以上ある件の行はすべてぬりつぶしたいです
伝わりますでしょうか？？
（だめもんど） 2017/08/07(月) 17:33
質問ですまないが、重複は最大で何重複になるか（上記の場合であれば5/6の4件が最大）、また期間の幅はどれくらいになるだろうか？ （ねむねむ） 2017/08/08(火) 09:09
件数が最大で何件になるかも。 （ねむねむ） 2017/08/08(火) 09:23
⇒ねむねむさん⇒ ありがとうございます
>重複は最大で何重複になるか 最大で10重複の可能性はありますが、めったになく、だいたい5重複になります
>期間の幅はどれくらいになるだろうか 最大で半年ですが、だいたい１か月になります 半年の定義は上半期・下半期(4-9月・10-3月)で、そこで一度リセットします １か月の定義は厳密に何日と区切るものではなく、２月なら２月、８月なら８月となります しかし、必ずしも１日〜末日と決まっているわけでもなく、最短で１週間からとなります
>件数が最大で何件になるか 30件を想定してシートを作成しています それ以上になることはまずないです
こんな説明で伝わりますでしょうか？
（だめもんど） 2017/08/08(火) 14:19
確認し忘れがあった。 件名はすべて異なるものになるだろうか？ 同じ件名が複数出てくることはあるだろうか？
あと、今考えている物は1枚のシートを作業用に使うことを考えているがそれは構わないだろうか？ （ねむねむ） 2017/08/08(火) 14:50
⇒ねむねむさん⇒ 何度もありがとうございます 返答にタイムラグがあり申し訳ございません
>件名はすべて異なるものになるだろうか？ すべて異なり、おなじものはありえません
>1枚のシートを作業用に使うことを考えているがそれは構わないだろうか？ 全く問題ありません
（だめもんど） 2017/08/08(火) 15:53
こちらの都合で回答がぶつ切りになるがまとめてみてくれ、 まず、元のシートがSheet1で作業用のシートがSheet2だとする。
Sheet2のA2セルに =IF(SUM(C2:AF2)>2,B2,"") と入力してA200セルまでフィルコピー。 (これは期間の最大が半年、約30*6＝180から） 次にB2セルに =MIN(Sheet1!B2:C40) (件数が最大30件から）
（ねむねむ） 2017/08/08(火) 16:01
次にSheet2のB3セルに =IF(B2="","",IF(B2+1>MAX(Sheet1!B$2:C$40),"",B2+1)) と入力してB200セルまでフィルコピー。 C1セルに =IF(COUNTA(Sheet1!$A2:$A40)>=COLUMN(A1),INDEX(Sheet1!$A2:$A40,COLUMN(A1)),"") と入力してAP1セルまでフィルコピー。
（ねむねむ） 2017/08/08(火) 16:06
Sheet2のほうの最後にC2セルに =IFERROR(IF(AND($B2>=VLOOKUP(C$1,Sheet1!$A:$B,2,FALSE),$B2<=VLOOKUP(C$1,Sheet1!$A:$C,3,FALSE)),1,""),"") と入力して下および右へフィルコピー。 (行は200行まで、列はAP列まで） これで作業シートの準備は完了。 （ねむねむ） 2017/08/08(火) 16:07
ここまでできたらSheet1でA列からC列を選択して条件付き書式で =SUMPRODUCT((Sheet2!$A$1:$A$200>=$B1)*(Sheet2!$A$1:$A$200<=$C1)*(A1<>"")) の条件で色を付けてみてくれ。
（ねむねむ） 2017/08/08(火) 16:11
すまない。 Sheet2のA2セルの式は =IF(SUM(C2:AP2)>2,B2,"") としてくれ。 （ねむねむ） 2017/08/08(火) 16:13
⇒ねむねむさん⇒ すごいです 素晴らしいです 入力した件名、期間しか表示させない箇所が目からウロコで、もう感激です
できあがりを見て、何かもったいなく思いましたので、Sheet2も活用させていただきます Sheet2を縦横入れ替えて表示させ、条件付き書式で塗りつぶしに設定させていただきました (わざわざ縦横入れ替えたのは、日付を横に見たいという要望の強い部署なので……すみません 表として見たり、数式を入れたりするのは逆の方がはるかに便利なんですけど)
変更が即座に反映される点がとても汎用性が高く、これは他の日程表にも活用させていただこうと思います どうもありがとうございました！！！ （だめもんど） 2017/08/09(水) 10:17
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.