EXCELの日付の計算と表示

質問

質問させて下さい。

EXELEで作表しているのですが、下記についてわからず教えて頂けると助かります。

私の会社は客商売をしており、お客様には貸しスペースを提供しております。その際に、使用日に対して予約を入れて頂くのですが、実際に使用して頂く(決定)のと、使用しない(キャンセル)と、その日が先約がいてこちらからお断り(残念)という扱いになるのですが、作表しているデータはキャンセルと残念になります。

セルＡ　　　　　セルＢ　　　セルＣ　　　　　　セルＤ
予約対象日　　予約日　　キャンセル日　　残念

何をしたいかといいますと、予約対象日に対してお客様より何日前に返事を出して頂いたかを調べております。もしもできるなら、セルＣかセルＤに日付を入力した時にセルＡに色付けされるとベストです。色付けの定義としては、予約対象日に対しての返事が(日数は仮定です)・・・(1)一週間以上前(2)５日以内(3)３日以内の３色を付けられたらと思います。入力に際しては、セルＣとセルＤは(当たり前ですが)必ずどちらかにしか日付は入りません。
この内容が無理なようでしたら、単純にセルを追加してそこに日数の差し引きをする計算式があれば教えて頂けると助かります。

見当違いであったり、説明不足があるかと思いますが
宜しくお願い致します。

回答No.6

hallo-2007 · ベストアンサー

No2,No5です。
回答していて、そちらの仕事の状況が良くわからなくなりました。
対象日　確定日　　分類
6/22　　　6/11　　予約
6/22　　　6/12　　キャンセル
6/22　　　6/13　　予約
6/22　　　6/14　　決定
とかなると思いますが、残念はどこに入るのでしょうか。
よくわかりませんが
受付日　予約者氏名　品名(対象日）　分類
というシートにすれば　いつ、だれが、何を、どうする　になるので
一般的で良くなると思います。
先に、紹介した作業列ですが、上シートの場合ですが　
=IF(AND(COUNTIF(C2:C$2,C2)=COUNTIF(C:C,C2),D2="決定"),C2,"")
で、予約が決定していて、重複予約が出来ない日
=IF(AND(COUNTIF(C2:C$2,C2)=COUNTIF(C:C,C2),D2="予約"),C2,"")
で、予約が入っているが、キャンセル待ちで予約が可能な日
のような気がしてきました。
関数が長くなりましたが。
同じ対象日が　予約、キャンセル、予約、決定　と何度も出てくるので
最後のデータの行を参照するために
COUNTIF(C2:C$2,C2)=COUNTIF(C:C,C2)　を加えました。
難しければ、E列に　=COUNTIF(C2:C$2,C2)
　　　　　　F列に　=COUNTIF(C:C,C2)
　　　　　　G列に　=IF(AND(E2=F2,D2="決定"),C2,"")　と分けて見て下さい。
式を理解していただいたら、使い安いように別シートにカレンダーや
入力規則、条件付書式などに利用してください。
より便利にするための希望がありましたらまた質問してください。

回答No.5

hallo-2007

こんばんは、No2です。
＞もう一つ伺いたいのですが・・・
＞>例えば、D列に　=IF(C2="予約",A2,"") 下フィルしていけば、

＞これはどういう事でしょうか？理解力がなくてすみません。
エクセルのテクニック以外に、ご自身の仕事全体を把握することも
シートを作成するには、必須です。
今回、私は　対象日（A2)が既に　予約（C2)確定していれば、再度予約を受けてはいけない　日　ではないかと思ったしだいです。
上記の式をD列に入れておくことで、予約済みの一覧が出来ます。
入力規則を利用するとエクセルに判断させて重複入力が出来なくすることも出来ます。
その他
この日付を参照することで重複予約の防止が出来ます。
例えば、別シートに
　　A　　B
　　1/1
　　1/2
　　1/3
・・・・
とA列に一年分のカレンダーを作成して
B列に　=IF(COUNTIF(データシート!D:D,A2)=1,"予約済み","予約なし"))
入れて下フィルしておくと、予約の有無のカレンダーも作成できます。
こんな風に何かと便利ではないでしょうか。

回答No.4

imogasi

質問文は結構無長いのですが、良くわからない。
前半など書く必要は無いと思う。＞私の会社は客商売をし・・
ーー
それより
場合を分けて数例を挙げて、ここの日付差日数はこうだから、こうしたい。
という場合分けの書き方は出来ないのですか。内容的には出来ると思う。
＞予約対象日に対してお客様より何日前に返事を出して頂いたかを・・
具体的に日数差はシート上に出せないのか、確定返事を頂いた（断った）本日日付はセルにCTRL+；で簡単に入力できますから、予約対象日と引き算の式を隣列に入れておくだけです。
そうすれば、条件付き書式の式も考え方も、簡単になります。
＞日以内の３色を付けられたらと
ここに「条件付き書式」という言葉が、質問文に出ないのは、知らないのですか。勉強が必要です。
２００３までは、３色までは色分けが可能なことも、極く初心者は別として、常識化してます。
２００７では
http://www.eurus.dti.ne.jp/~yoneyama/Excel2007/excel2007-jyosyo.html
エクセルのバージョンが質問に書いてない。大切な事項です。

お礼

>質問文は結構無長いのですが、良くわからない。
仰るとおりでした。申し訳御座いません。

回答No.3

mshr1962

色を付けるということだと条件付書式で可能です。
ところで残念に入るのは対象日を過ぎても取りに来ない場合（当日の日付）でしょうか？
例
条件1「数式が」「=OR(AND(C2>0,A2-C2>5),AND(D2>0,D2>A2,D2-A2>5))」で書式ボタンでパターンから一週間以上前に設定する色を設定
追加ボタンを押して
条件2「数式が」「=OR(AND(C2>0,A2-C2>2),AND(D2>0,D2>A2,D2-A2>2))」で書式ボタンでパターンから4日～6日の間に設定する色を設定
追加ボタンを押して
条件3「数式が」「=OR(C2>0,D2>0,A2>D2)」で書式ボタンでパターンから3日以内に設定する色を設定

日数の差し引きの数式
=IF(C2>0,A2-C2,IF(AND(D2>0,D2>A2),D2-A2,""))

お礼

有難う御座います。
参考にさせて頂きました。

回答No.2

hallo-2007

A　　　　B　　　　　C　　　　　D
対象日　制約日　キャンセル日　残連連絡日
6/21　　　6/10
6/21　　　　　　　　　　　　　　　6/11
6/22　　　　　　　　6/12
6/22　　　6/13
とかになるのでしょうか。
＞セルを追加してそこに日数の差し引きをする計算式
でよければ　そのまま　=A2-B2 で書式を数値にするとよいでしょう
B~D列のいずれに日付が入る、入る場所は一箇所だけであれば
=A2-B2-C2-D2　　でも同じ結果が出ます。
色を付けるのであれば、A～D列選択して、「書式」「条件付書式」
左側の　値が　を　数式が　にかえて　=$A2-$B2-$C2-$D2>7
とかいれて　書式でパターン　希望する色で3色まで設定できます。

アドバイスですが
　　 A　　　　B　　　　C　　　　　
対象日　確定日　　分類
6/21　　　6/10　　予約
6/21　　　6/11　　残念
6/22　　　6/12　　キャンセル
6/22　　　6/13　　予約
とシートを作成した方が集計は便利だと思います。
例えば、D列に　=IF(C2="予約",A2,"") 下フィルしていけば、
確定済みの対象日の一覧が出来ますし
A列、或いはC列に　入力規則掛ければ　制約済みの入力が出来なくすることも出来ます。
例　
C列に　入力規則　ユーザー設定　=OR(E2<>"予約",COUNTIF(G:G,A2)<1)
にしておけば、確定済みの対象日で、C列に　予約の文字が入力できなくなります。

お礼

有難う御座います。
アドバイス頂いた通りに分類という項目に置き換えて作成し直しました。

もう一つ伺いたいのですが・・・
>例えば、D列に　=IF(C2="予約",A2,"") 下フィルしていけば、

これはどういう事でしょうか？理解力がなくてすみません。

回答No.1

Masa2072

Excelでは1900/1/1を１として経過日数を日付シリアルとして管理しています。
ですから2008/6/21という日付に１を加算すると2008/6/22になります。
（なにか言い回しが変ですが・・）

予約対象日（A1)、予約日（B1)、キャンセル日（C1)、残念日？（D1)とします
C1とD1のどちらかに不成立の日付が入るということですから、逆にいえば日付が入っていない方は０になるということです。
つまりC1とD1どちらかのみに日付が入っていればC1+D1も同じ日付と言うことになり、=A1-(C1+D1)でキャンセル日（もしくは残念日）が何日前かを求めることができます。

色付けは条件付書式で設定します。
たとえば１週間前なら
数式　=A1-(C1+D1)>=7
と条件を指定します。

お礼

有難う御座います。
お陰様で必要最低限のやりたい事はできました。