すみません、無知で。教えてください。

質問

エクセルで下の添付画のように時間を開始(BU17)・終了(BV17)に入力すれば左の表E7・F7のセルの色つけをして棒上(10分単位)で表せませんか？
ちなみに、条件付き書式で作ろうと思ったのですが、やり方が分からず関数式も解らず断念しようかとも思ったのですが…
よろしくお願いします。

回答No.2

kagakusuki · ベストアンサー

質問者様が添付された画像が粗くて見辛いため、念のために確認させて頂きますが、
左の表で塗り潰しを行う必要が生じる可能性のあるセルがあるのは、D～BKの列範囲の7行目と10行目と13行目、
右の表で「準備」欄の「開始」と入力されているセルがBU16セル、
右の表で「準備」欄の「終了」と入力されているセルがBV16セル、
で間違いないでしょうか？
　もし、上記のセルの配置で間違いが無ければ、次の様な操作を行って、条件付き書式を設定されると良いと思います。

Excelウインドウの[ホーム]タブをクリック
　　↓
「スタイル」グループの中にある[条件付き書式]ボタンをクリック
　　↓
現れた選択肢の中にある[ルールの管理]をクリック
　　↓
現れた「条件付き書式ルールの管理」ダイアログボックスの中にある[新規ルール]ボタンをクリック
　　↓
現れた「新しい書式ルール」ダイアログボックスの[数式を使用して、書式設定するセルを決定]をクリック
　　↓
「次の数式を満たす場合に値を書式設定」と記されている欄に次の数式を入力

=COUNTIFS($BU$17:$BU$25,"<="&TIME(,TEXT($D$4,"[m]")+(COLUMNS($D:D)-1)*10,)+INT((TEXT($D$4,"[m]")+(COLUMNS($D:D)-1)*10)/1440),$BV$17:$BV$25,">="&TIME(,TEXT($D$4,"[m]")+COLUMNS($D:D)*10,)+INT((TEXT($D$4,"[m]")+COLUMNS($D:D)*10)/1440))
　　↓
「新しい書式ルール」ダイアログボックスの[書式]ボタンをクリック
　　↓
現れた「セルの書式設定」ダイアログボックスの[塗りつぶし]タブをクリック
　　↓
現れた[色]欄をクリック
　　↓
現れた色のサンプルの中にある黄色の四角形を選択してクリック
　　↓
「セルの書式設定」ダイアログボックスの[OK]ボタンをクリック
　　↓
「新しい書式ルール」ダイアログボックスの[OK]ボタンをクリック
　　↓
「条件付き書式ルールの管理」ダイアログボックスの中にある「書式」欄が黄色の塗りつぶしとなっている行の「適用先」欄をクリック
　　↓
D7～BK7のセル範囲をまとめて範囲選択
　　↓
「条件付き書式ルールの管理」ダイアログボックスの中にある[新規ルール]ボタンをクリック
　　↓
現れた「新しい書式ルール」ダイアログボックスの[数式を使用して、書式設定するセルを決定]をクリック
　　↓
「次の数式を満たす場合に値を書式設定」と記されている欄に次の数式を入力

=COUNTIFS($BX$17:$BX$25,"<="&TIME(,TEXT($D$4,"[m]")+(COLUMNS($D:G)-1)*10,)+INT((TEXT($D$4,"[m]")+(COLUMNS($D:G)-1)*10)/1440),$BY$17:$BY$25,">="&TIME(,TEXT($D$4,"[m]")+COLUMNS($D:G)*10,)+INT((TEXT($D$4,"[m]")+COLUMNS($D:G)*10)/1440))
　　↓
「新しい書式ルール」ダイアログボックスの[書式]ボタンをクリック
　　↓
現れた「セルの書式設定」ダイアログボックスの[塗りつぶし]タブをクリック
　　↓
現れた[色]欄をクリック
　　↓
現れた色のサンプルの中にある赤色の四角形を選択してクリック
　　↓
「セルの書式設定」ダイアログボックスの[OK]ボタンをクリック
　　↓
「新しい書式ルール」ダイアログボックスの[OK]ボタンをクリック
　　↓
「条件付き書式ルールの管理」ダイアログボックスの中にある「書式」欄が赤色の塗りつぶしとなっている行の「適用先」欄をクリック
　　↓
D10～BK10のセル範囲をまとめて範囲選択
　　↓
「条件付き書式ルールの管理」ダイアログボックスの中にある[新規ルール]ボタンをクリック
　　↓
現れた「新しい書式ルール」ダイアログボックスの[数式を使用して、書式設定するセルを決定]をクリック
　　↓
「次の数式を満たす場合に値を書式設定」と記されている欄に次の数式を入力

=COUNTIFS($CA$17:$CA$25,"<="&TIME(,TEXT($D$4,"[m]")+(COLUMNS($D:D)-1)*10,)+INT((TEXT($D$4,"[m]")+(COLUMNS($D:D)-1)*10)/1440),$CB$17:$CB$25,">="&TIME(,TEXT($D$4,"[m]")+COLUMNS($D:D)*10,)+INT((TEXT($D$4,"[m]")+COLUMNS($D:D)*10)/1440))
　　↓
「新しい書式ルール」ダイアログボックスの[書式]ボタンをクリック
　　↓
現れた「セルの書式設定」ダイアログボックスの[塗りつぶし]タブをクリック
　　↓
現れた[色]欄をクリック
　　↓
現れた色のサンプルの中にある緑色の四角形を選択してクリック
　　↓
「セルの書式設定」ダイアログボックスの[OK]ボタンをクリック
　　↓
「新しい書式ルール」ダイアログボックスの[OK]ボタンをクリック
　　↓
「条件付き書式ルールの管理」ダイアログボックスの中にある「書式」欄が緑色の塗りつぶしとなっている行の「適用先」欄をクリック
　　↓
D13～BK13のセル範囲をまとめて範囲選択
　　↓
「条件付き書式ルールの管理」ダイアログボックスの中にある[適用]ボタンをクリック
　　↓
「条件付き書式ルールの管理」ダイアログボックスの中にある[OK]ボタンをクリック

　以上です。

お礼

非常に丁寧にありがとうございました。
やっと上手く出来そうです。
すごい。

回答No.4

bunjii

質問の貼付画像が読み取れないので勝手解釈の配置でExcel 2013により検証してみました。
下記の貼付画像ではD7～BK7が準備、D10～BK10が作業、D13～BK13が休憩の時間帯で1つのセルが10分です。
また、BN16～BS26が元データとしました。
D7=SUMPRODUCT((OFFSET($BM$16,2,MATCH($C6,$BN$16:$BS$16,0),9)<=("8:00"*1+(COLUMN()-4)*"0:10"))*1,(OFFSET($BM$16,2,MATCH($C6,$BN$16:$BS$16,0)+1,9)>("8:00"+(COLUMN()-4)*"0:10"))*1)
D7セルの数式をBK7までコピーすると、各セルの結果は0または1になります。
D7～BK7をD10～BK10とD13～BK13へコピーします。
条件付書式はセルの値が0のとき文字の色を白にし、1のとき塗りつぶしの色と文字の色を同じにします。
BL7の合計（時間数）は次の数式で算出します。
BL7=SUM(D7:BK7)*"0:10"
BL7をBL10とBL13へコピーすれば準備、作業、休憩の合計時間がシリアル値で算出されますので書式を"[h]:mm"にすれば良いでしょう。

回答No.3

kagakusuki

回答No.2です。
　申し訳御座いません。
　先程の回答において、2番目に挙げた、作業時間に該当するセルを赤く塗りつぶすための数式

=COUNTIFS($BX$17:$BX$25,"<="&TIME(,TEXT($D$4,"[m]")+(COLUMNS($D:G)-1)*10,)+INT((TEXT($D$4,"[m]")+(COLUMNS($D:G)-1)*10)/1440),$BY$17:$BY$25,">="&TIME(,TEXT($D$4,"[m]")+COLUMNS($D:G)*10,)+INT((TEXT($D$4,"[m]")+COLUMNS($D:G)*10)/1440))

の中に4箇所あるCOLUMNS関数の参照先を間違えて、正しくは

COLUMNS($D:D)

とすべきだった所が

COLUMNS($D:G)

になっておりました。
　ですから、2番目の数式を次のものと差し替えて下さい。

=COUNTIFS($BX$17:$BX$25,"<="&TIME(,TEXT($D$4,"[m]")+(COLUMNS($D:D)-1)*10,)+INT((TEXT($D$4,"[m]")+(COLUMNS($D:D)-1)*10)/1440),$BY$17:$BY$25,">="&TIME(,TEXT($D$4,"[m]")+COLUMNS($D:D)*10,)+INT((TEXT($D$4,"[m]")+COLUMNS($D:D)*10)/1440))

回答No.1

MackyNo1

＞時間を開始(BU17)・終了(BV17)に入力すれば左の表E7・F7のセルの色つけをして棒上(10分単位)で表せませんか？

画像が小さくてセル位置が判断しにくいのですが、7行目のD列から右のセルにBU17セルからBV17セルの時間範囲だけを塗りつぶしたいということなら以下のような設定をします。

準備として、表の開始時間に対応するD5セル（結合していないセル）に「8：00」のように入力します。
その右のE5セルに「=TEXT(D5+"0:10","h:m")*1」と入力して右方向にオートフィルします（通常の「8：10」とのオートフィルでは不具合が発生しますので、必ず数式を使ってください）。

D7セルから右に条件付き書式を設定するセル範囲を選択し、数式を使った条件付き書式で以下の関数を入力し、塗りつぶしを「赤」に指定します。

=AND(D$5>=$BU17,D$5<$BV17)

4行目の時間をオートフィルコピーした行は非表示などの対応することでレイアウトを整えてください。

＃上記の回答は、ひとまず例示のレイアウトを基準に、条件付き書式でご希望の表示ができることを示したものですので、実際のリストに応用する場合は、もっと効率的なレイアウトに設定し直したほうが良いと思います。