EXCELでスケジュール表を作成したい

質問

EXCELでスケジュール表を作成していたのですが、うまくセルの色付けが出来ず悩んでいます。

私のやりたい事を添付画像の例で説明します。

「予定開始」へ「2011/05/01」「AM」
「予定終了」へ「2011/05/02」「AM」

と入力します。

結果、セルの「AQ8」～「AS8」まで、自動で塗りつぶしを行いたいと考えています。

さらに、この自動動作をさせるためにいくつか制限事項があります。
以下に列挙致します。

　1．マクロを使わない
　2．条件付き書式を使う

進捗状況としては、セルの「AQ8」に条件付き書式を使用しており、
書式の設定は、「数式を使用して、書式設定するセルを決定」を選択し

「=AND(AND($X8-AQ$4=0,$AD8-AQ$4>=0),NOT($AB8="PM"))」という数式で
セルの「AQ8」を塗りつぶすことができました。

また、セルの「AR8」には、上記と同じように
「=AND(AND($X8-AQ$4=0,$AD8-AQ$4>=0),NOT($AB8="AM"))」という数式で
セルの「AR8」を塗りつぶすことができました。

ここからが、わからなくなってしまった点で、
「予定終了」の「2011/05/02」の判定はできていると思うのですが
「予定終了」の「AM/PM」の判定をどのように、数式に組み込めば良いのかわかりません。

考え方が根本的に間違っているのかすらわからない状況です。
是非、皆様のお力をご教授ください。
他に必要な情報等ございましたら、出来る限り提供致します。
よろしくお願いいたします。

回答No.4

keithin · ベストアンサー

あいだが空いてしまい，失礼しました。

[必要な知識のその１]
新しいブックのA1セルにてきとーな日付を記入し，そのセルの書式設定を標準に変えてみると，たとえば「40655」のような数字に変わります。
エクセルではこれを「シリアル値」と言いますが，日付の値はすべてこの数値で計算されています。
40655の翌日のシリアル値は，＋１した40656という数値になります。

[必要な知識のその２]
新しいブックのA1セルに　AM　あるいは　PM　と文字を記入します。
隣のB1セルに
=(A1="PM")/2
という数式を実際に入れてみます。A1をAMやPMに変えると，この式の値はゼロか0.5という計算結果になります。

この２つの事を利用して，
ある日のAM　という２つのセルのデータから　40655（40655.0)　を計算します
ある日のPM　という２つのセルのデータから　40655.5　という値を計算します

たとえば予定開始～予定終了までの期間として
ある日のAM～翌日のPMまで
が指定されていたとします。
更にカレンダーの日付として，翌日のAMを調べてみます。

ある日のAMが上述のように　40655　だとすると
翌日のPMは，上述のように　40656.5　です
カレンダー上の翌日のAMは，上述のように　40656.0　です

40655<40656<40656.5ですから，この３つの数値のMEDIANは40656です。
逆に言うと，カレンダー上の日付＋AM(0)/PM(0.5)がMEDIANと等しければ，そのカレンダーの日付+AM/PMは指定の期間の中にあるということが判ります。
カレンダーの日付+AM/PMが予定範囲外だった場合についても，演習してみてください。

＞「１日」

どんな風にその「１日」を使いたいのか説明不足ですが，AMあるいはPMの代わりに「１日」を記入するという事で良いなら。
開始予定の欄にある１日は，そのままAMと読み替えて計算すればよい事になります。
終了予定の欄にある１日は，そのままPMと読み替えて計算すればよい事になります。

すると回答した条件付き書式の式は，
=MEDIAN(AQ$4+(AQ$7="PM")/2,$X8+($AB8="PM")/2,$AD8+($AH8="PM")/2+($AH8="１日")/2)=AQ$4+(AQ$7="PM")/2
のようにすればよいことが判ります。

お礼

非常に悩みましたが、今回は、「keithin」さんの案を元にしたモノを採用させていただきましたのでベストアンサーとさせていだきます。
皆様、ありがとうございました。
また、よろしくお願いいたします。

回答No.5

ap_2

◆ ANo.1について
質問文の条件式で、開始日までは判定できていたので、
ざっくりで伝わるかと…手を抜きました＾＾；
◇ 条件＠AM/PMセル共通の説明
　以下３つの条件いずれかを満たしたら色を塗る
　1. 塗るセル＝開始日で、塗るセルがPM or 開始がPM以外
　2. 塗るセル＝終了日で、塗るセルがAM or 終了がAM以外
　3. 塗るセルが、開始日～終了日の間
◇ 式
・AQ8（5/1 AM）
=OR(AND(AQ$4=$X8,$AB8<>"PM"),AQ$4=$AD8,AND($X8<AQ$4,AQ$4<$AD8))
・AR8（5/1 PM）
=OR(AQ$4=$X8,AND(AQ$4=$AD8,$AH8<>"AM"),AND($X8<AQ$4,AQ$4<$AD8))
他のセルには、AQ8とAR8をコピー

◆ANo.3イイネ！
ANo.1は分りやすさ優先しましたが、ANo.3オススメです。
AM/PMセルで同じ条件式を使った方が、メンテナンス性が向上します。
・ANo.3＋ちょっと修正
=MEDIAN(IF(AQ$4="",AP$4,AQ$4)+(AQ$7="PM")/2,$X8+($AB8="PM")/2,$AD8+($AH8<>"AM")/2)=IF(AQ$4="",AP$4,AQ$4)+(AQ$7="PM")/2
AQ8のセルに条件を設定し、AQ8を他のセルにコピー
※日付セル参照をIF文に変更。醜くなりましたが（苦）

MEDIANは知らなかったので、ちょっとトキメキました。
ちなみに、MEDIANを使わない判定も可能です＠以下、同じ処理
　=MEDIAN(開始日,塗る日,終了日)=塗る日
　=AND(開始日<=塗る日, 塗る日<=終了日)

◆補足
・日付は、Excel内部では数値。1900/1/1から何日目か(2011/5/1なら40664)
・条件式(=やANDなど)が返却する真偽値は、True=1、False=0です。
→ 「(AQ$7="PM")/2」はカッコ内が真偽値になるので「PMなら+0.5」

回答No.3

keithin

作業を簡単にするため，ちょっと細工を追加します。

準備：
AQ1を空っぽにしておく
AR1に
=AQ1
と式を入れる
AQ1:AR1を選択してコピーする
AQ4から日付の結合セル範囲をまとめて選んで
形式を選んで貼り付けの「数式」にマーク，「空白セルを無視する」にチェックしてOKする
作業したらAR1は消して良い。

手順：
AQ8の条件付き書式は
数式が
=MEDIAN(AQ$4+(AQ$7="PM")/2,$X8+($AB8="PM")/2,$AD8+($AH8="PM")/2)=AQ$4+(AQ$7="PM")/2
と設定し，AQ8をコピーして表範囲に貼り付けておく。

お礼

すみません。しばらくいじっていたら、どうやら、1日判定は含まれていないようでした。

条件として、午前、午後、1日の判定というのは、今の状態の式では無理なのでしょうか。

補足にかけなかったのでこちらで失礼しています。

補足

4行ラインのAQ4から日付のマクロが入っており、
自動でセルの書式も変更する設定が入っている為、入れることができませんでした。
ですので、5行ラインを新たに追加し、お答えいただいた式と細工を
挿入しところ自動でセルの塗りつぶしを行う事ができました。
また、要望に書き忘れていた、AM、PM以外の1日判定も考慮したもので大変助かりました。

ただ、MEDIAN関数の知識がなかったため式の理解ができていません。
調べてみたのですが、中央値を求める関数ということはわかったのですが

日付＋（AM=PM）/2という意味が理解できずで、すっきりしていません。
今回の判定をどのように設定したものなのか、解説していただけませんでしょうか。
お忙しいなか恐縮ですが、ご教授いただければと思います。

回答No.2

KURUMITO

AQセルから色を付けたい範囲を選択してから条件付き書式の数式の窓には次の式を入力します。

=AND($X8+IF($AB8="AM",0,0.5)<=DATE(YEAR($AD$2),$AQ$3,DAY(ROUNDUP(COLUMN(A1)/2,0)))+IF(AQ$7="AM",0,0.5),$AD8+IF($AH8="AM",0,0.5)>=DATE(YEAR($AD$2),$AQ$3,DAY(ROUNDUP(COLUMN(A1)/2,0)))+IF(AQ$7="AM",0,0.5))

回答No.1

ap_2

混乱してますね。条件を整理するだけですよｗ
=OR(
　AND(日付=開始日,　OR(セルが="PM", 開始が="AM")),
　AND(日付=終了日,　OR(セルが="AM", 終了が="PM")),
　AND(開始日<日付, 日付<終了日))
)
こんな感じでどうだろう。
ちなみに…
　・日付は引き算不要、直接比較でOK！
　・不等号"<>"。NOT(A1=B1) より、A1<>B1がオススメ！
　・ORは、条件のどれかと一致するか。

補足

お返事が遅くなりもうしわけありません。

A1<>B1便利そうです！

▼回答の中の質問があります▼

式の中にある「日付」とは、どこのセルのことなのでしょうか
また、「セルが」についてもどこのセルを参照しているのでしょうか。

ご教授ください。