Excel：開始日時から終了日時の表示

質問

例えば、「シート1」の「C3～C6」に日時が入力されているとします。
そして、「D3～D6」に開始日時、「E3～E6」に終了日時、「F3～F6」に「手続き可能中」などの文字を表示したいと思っています。

ただし、条件があります。

■開始日時について
「D3」に表示する日時：「C3」に入力されている日時から、3ヶ月前（例えば、「C3」に2016年4月30日と入力されているとすれば2016年2月1日となります。）からが「開始日時」となります。開始日時の開始日は必ず「1日」となります。
「D4」に表示する日時：「C4」に入力されている日時から、3ヶ月前（例えば、「C4」に2016年4月30日と入力されているとすれば2016年2月1日となります。）からが「開始日時」となります。開始日時の開始日は必ず「1日」となります。
「D5」に表示する日時：「C5」に入力されている日時から、1ヶ月前（例えば、「C5」に2016年4月30日と入力されているとすれば2016年4月1日となります。）からが「開始日時」となります。開始日時の開始日は必ず「1日」となります。
「D6」に表示する日時：有効期間満了年の誕生日の1ヶ月前。例えば、誕生日が4月15日なら月日のみ3月15日。

ただし、下記の「■表示してはいけない（取り除く）「開始日時」、「終了日時」について」参照。

■終了日時について
「E3」に表示する日時：基本的には「C3」を表示する。
「E4」に表示する日時：基本的には「C4」を表示する。
「E5」に表示する日時：基本的には「C5」を表示する。
「E6」に表示する日時：基本的には「C6」を表示するが、土曜日、日曜日、祝日（振替休日を含む）、12月29日～1月3日（年末年始）に当たるときは、これらの日の翌日までです。

ただし、下記の「■表示してはいけない（取り除く）「開始日時」、「終了日時」について」参照。

■表示してはいけない（取り除く）「開始日時」、「終了日時」について
「D3」に表示してはいけない日時：土曜日、日曜日、祝日（振替休日を含む）、12月29日～1月3日（年末年始）
「D4」に表示してはいけない日時：土曜日、日曜日、祝日（振替休日を含む）、12月29日～1月3日（年末年始）
「D5」に表示してはいけない日時：土曜日、日曜日、祝日（振替休日を含む）、12月29日～1月3日（年末年始）
「D6」に表示してはいけない日時：土曜日、祝日（振替休日を含む）、12月29日～1月3日（年末年始）

「E3」に表示してはいけない日時：土曜日、日曜日、祝日（振替休日を含む）、12月29日～1月3日（年末年始）
「E4」に表示してはいけない日時：土曜日、日曜日、祝日（振替休日を含む）、12月29日～1月3日（年末年始）
「E5」に表示してはいけない日時：土曜日、日曜日、祝日（振替休日を含む）、12月29日～1月3日（年末年始）
「E6」に表示してはいけない日時：土曜日、祝日（振替休日を含む）、12月29日～1月3日（年末年始）

■その他
01. いずれも関数のみで表示すること。
02. 祝日は、他のシートとすること。「シート2」とする。
03. 関数式の祝日の設定は、増減・変更の可能性があるためその部分の関数式を変更しなくても永久に使えるようにすること。

条件は以上です。

■「F3～F6」について
「F3～F6」については、「開始日時」より1日前まででは「手続き必要なし」、「開始日時」から「終了日時」までの期間までなら「手続き可能中」、「終了日時」より1日後以降なら「期限切れ」という文字列を表示したいです。

Excel2010です。
これらの条件を満たした関数式を教えてください。
できれば、関数式の具体的な解説もいただけるとありがたいです。

難しいとは思いますが、回答よろしくお願いします。

回答No.17

kagakusuki

>今日、Excelを開いたのですがE3が、2016年4月29日となっていました。
>昨日は、2016年4月28日だったのですが、何故でしょうか？

　Sheet2の祝日表の関数を誤って壊してしまったという事は無いでしょうか?
　或いは、C列のその他のセルに2014/1/1よりも前の日時又は41640未満の数値が入力されている事によって、Sheet2のF1セルに表示されている年が2014年よりも前の年になってはいないでしょうか?
　或いは、Sheet2のA2セルの関数をA3以下に貼り付けた際に、貼り付けたセルの数が不足していて、G列～I列で計算した結果である祝日の全てを抽出する様になってはおらず、SheetC列のその他のセルに2014年の日付を入力した際に、2016年4月28日が表示されていない状態になってはいないでしょうか?

お礼

回答ありがとうございます。

無事、2016年4月28日を表示することができました。
原因は、回答者様のおっしゃった、
「Sheet2のA2セルの関数をA3以下に貼り付けた際に、貼り付けたセルの数が不足していて、G列～I列で計算した結果である祝日の全てを抽出する様になってはおらず、SheetC列のその他のセルに2014年の日付を入力した際に、2016年4月28日が表示されていない状態になってはいない」ということでした。

ありがとうございます。
ここまで回答してくれたことに感謝いたします。
大変申し訳ありませんがもう一つ質問あるのですが、祝日が増減などの変更があった場合、どのような式を入力したらよいのでしょうか？また、どのような作業をすればよいでしょうか？
祝日の関数式を入れているのはシート名「Sheet2」でしたが、現在はシート名「祝日」としています。

回答よろしくお願いします。

回答No.16

kagakusuki

>E3：2016年4月28日（間違っています。正しくは、2016年4月30日となります。）

　どこが間違っていると仰るのでしょうか?

>「E3」に表示してはいけない日時：土曜日、日曜日、祝日（振替休日を含む）、12月29日～1月3日（年末年始）

>終了日（「E3～E6」）の表示は、「■表示してはいけない（取り除く）「開始日時」、「終了日時」について」の項目にある条件付きで、その「前日」を表示させます。

という条件の筈であるのにもかかわらず、何故、2016年4月30日(土)が正しい事になるのでしょうか?

>E6：2016年3月31日（間違っています）

　申し訳御座いません。関数をコピーする際にコピー元のセルを1行だけ間違えてE6セルの関数ではなく、E5セルの関数を貼り付けておりました。
　E6セルに入力する正しい関数は以下の通りです。

=IF(AND($C6>"1904/1/4"+0,$C6<"9999/12/31"+1),WORKDAY(INT($C6)-1,1,OFFSET(Sheet2!$A$1,1,,COUNT(Sheet2!$A:$A))),"")

>F6：空白（間違っています。D6とE6が逆転してしまっているため、空白となってしまったのだと思われます。）

　はい、

>例えばC5に入力されている日付が2016/5/1(日)だった場合、D5セルの開始日はその翌日の2016/5/2になるのに対し、E5セルの終了日は2016/4/29(昭和の日)、2016/4/30(土)、2016/5/1(日)の3連休の前日である2016/4/28となってしまい、開始日と終了日が逆転してしまいます。
>同様の事は、C5セルに1月1日～3日の日付が入力された場合や、1日が土日となっている月の初日の日付が入力された場合にも起こります。

の件がありましたので、開始日と終了日が逆転している場合には何も表示させない様にしております。
　大元の原因は回答No.12で誤ってE5セルの関数を、E6の関数であるとした事にあるのですから、

>E6：2016年3月31日（間違っています）

の件を修正して頂ければおそらく解決するのではないかと思います。

お礼

回答ありがとうございます。

>どこが間違っていると仰るのでしょうか?

2016年4月30日を平日だと勘違いしておりました。
気を害されたのではないかと思います。
大変申し訳ありませんでした。

今日、Excelを開いたのですがE3が、2016年4月29日となっていました。
昨日は、2016年4月28日だったのですが、何故でしょうか？

>申し訳御座いません。関数をコピーする際にコピー元のセルを1行だけ間違えてE6セルの関数ではなく、E5セルの関数を貼り付けておりました。
　E6セルに入力する正しい関数は以下の通りです。

=IF(AND($C6>"1904/1/4"+0,$C6<"9999/12/31"+1),WORKDAY(INT($C6)-1,1,OFFSET(Sheet2!$A$1,1,,COUNT(Sheet2!$A:$A))),"")

この関数式でE6とF6に正しい表示結果が出ました。
ありがとうございます。

後は、上記に述べたように、
「E3が2016年4月28日だったのに、2016年4月29日となってしまいました。」
これを解決するにはどうしたらよいでしょうか？

回答よろしくお願いします。

回答No.15

kagakusuki

>表示結果が全て、「#REF!」となってしまいました。

　申し訳御座いません。断言は出来かねますが、おそらく当方で確認用に作ったシート上に作成した関数を基にして、投稿文(回答文)を作成するにあたって、関数内のシート名の部分だけをSheet2に置換しようとした際に、誤ってSheet1に置換してしまった事が原因ではないかと思います。
　ですから、D3～F6の範囲を範囲選択してから、Excelの置換機能を起動([Ctrl]キー+[H]キー)させ、「検索する文字列」欄に

Shet1

と入力し、「置換後の文字列」欄に祝日の一覧表を作成した実際のシート名を入力してから、ダイアログボックスの[すべて置換]ボタンをクリックしてみて下さい。

お礼

回答ありがとうございます。

回答の通りやったところ、「D3～D6」（開始日）、「E3～E6」（終了日）に年月日、「F3～F6」（文字列）に文字列の表示が出ました。
ありがとうございます。

ところが、所々に表示結果が間違っている部分があります。
D3：2016年2月1日（合っています）
D4：2015年8月3日（合っています）
D5：2016年3月1日（合っています）
D6：2017年？月？日（合っています。あまり誕生日のこと知られたくないため具体的な月日の表示は控えさせてください。ご了承ください。）

E3：2016年4月28日（間違っています。正しくは、2016年4月30日となります。）
E4：2015年10月30日（合っています）
E5：2016年3月31日（合っています）
E6：2016年3月31日（間違っています）

F3：手続き必要なし（合っています）
F4：手続き必要なし（合っています）
F5：手続き必要なし（合っています）
F6：空白（間違っています。D6とE6が逆転してしまっているため、空白となってしまったのだと思われます。）

どこが間違っているのか分かりますでしょうか？
大変お手数ですがもう一度よろしくお願いします。

回答よろしくお願いします。

回答No.14

kagakusuki

>C5に入力されている日付が2016/5/1(日)の場合、D5の開始日は2016/5/2となり、E5の終了日は2016/5/31となります。
>「C3～C5」に入力されている日付は必ずその月の末日となっております（例えば、3月だった場合、31日、4月だった場合、30日。）。
>したがって、何も問題ありません。

　御説明内容に矛盾が御座います。

>「C3～C5」に入力されている日付は必ずその月の末日

という事は、C5セルに入力されている日付が2016/5/1(日)になる事は無い筈ですから、

>D5の開始日は2016/5/2となり、E5の終了日は2016/5/31となります。

という事にはならない筈です。
　要するに、2016/5/1(日)や年始の日付けがC5セルに入力される事はあり得ないので、開始日と終了日が逆転してしまう様な日付が入力される事まで考慮して、関数の機能としてそれを防止する機能まで盛り込んでおく必要は特に無いと考えておけば宜しいという事なのでしょうか?
　それでしたら回答No.12の関数でも良いと思うのですが、もし何か問題がある様でしたら御知らせ願います。
　それとも、もしもC列のセルに月末以外の日付が入力された場合にも対応するために、D列、E列の関数内でC列に入力された日付の月末の日に対応した開始日と終了日を表示する様にして欲しいという事なのでしょうか?

お礼

回答ありがとうございます。

申し訳ありません、記載に間違いがありました。

「C5に入力されている日付が2016/5/1(日)の場合、D5の開始日は2016/5/2となり、E5の終了日は2016/5/31となります。」

この部分なんですが、正しくは、
「C5に入力されている日付が2016/5/31の場合、D5の開始日は2016/5/2となり、E5の終了日は2016/5/31となります。」
（「C3～C5」に入力されている日付は必ずその月の末日となっています。）
これが、正しい文章となります。

したがって、何も問題はありません。

また、何度もお手数おかけいたしまして申し訳ありませんが、
回答No.8、回答No.9、回答No.10、回答No.12の順で入力したのですが、
「D3～D6」（開始日）、「E3～E6」（終了日）、「F3～F6」（文字列）の表示結果が全て、「#REF!」となってしまいました。

どこが間違っているのか分かりません。
また、どこが間違っているのか説明するために、そのセルの式と表示結果をこのサイトで記載しようとすると、15,000文字以上になってしまいます。
どうしたらいいでしょうか？

回答よろしくお願いします。

回答No.13

kagakusuki

>>次に、Sheet2のG列全体をコピーして、「Sheet1のC列に入力されている日付の中で最も古い日付の3ヶ月前の日の年～Sheet1のE列に表示される日付の中で最も新しい日付の年」の期間が「幾つの年に跨っているのかを数えた値」の数だけ、G列の右隣の列範囲に貼り付けて下さい。
>　例えばC3セルに2015/1/11、C4セルに2015/12/29とそれぞれ入力されていて、C5セルやC6セルに入力されている日付はいずれも2015/1/11～2015/12/29の期間の途中の日付である場合には、C列～E列に入力、或いは表示されている日付は2014/12/1～2016/1/4の範囲になりますから、2014年～2016年の3つの年に跨っている事になりますので、Sheet2のG列全体をコピーした後、Sheet2のH列～I列に貼り付ける事で、G列と合わせて3列に同様の関数が存在している様にします。

>この部分の文章を何度も読み直したのですが、どのようにしたらいいのかよく分かりません。

　まず、Sheet2を開いて下さい。
　そして、G1セルの上の所にある「G」と記されている箇所をクリックして下さい。
　するとG列全体を選択する事が出来ます。
　その状態でG1セルの右上の角の所に表示されている黒い点にカーソルを合わせて下さい。
　するとカーソルの形状が黒い「+」の字の形に変わりますから、カーソルがその状態となっている時にマウスの左ボタンを押しっ放しにし、ボタンを押したままの状態でカーソルをI列の所にまで移動させてから、マウスのボタンから指を離して下さい。
　それでG列の関数やセルの書式等を、H列～I列にコピーする事が出来ます。

お礼

回答ありがとうございます。

理解不足ですいません。
今回の説明で理解できました。

回答No.8、回答No.9、回答No.10、回答No.12の順で入力したのですが、
「D3～D6」（開始日）、「E3～E6」（終了日）、「F3～F6」（文字列）の表示結果が全て、「#REF!」となってしまいました。

どこが間違っているのか分かりません。
また、そのセルの式と表示結果をこのサイトで記載しようとすると、15,000文字以上になってしまいます。
どうしたらいいでしょうか？

回答よろしくお願いします。

回答No.12

kagakusuki

申し訳御座いません。
　回答No.11は色々と間違っておりました。
　ですから、Sheet1に関しては以下の様にして下さい。

　まずSheet1のD3セルに下記の関数を入力して下さい。

=IF(AND($C3>"1904/3/1"+0,$C3<"9999/12/31"+1),WORKDAY(EOMONTH($C3,-3),1,OFFSET(Sheet1!$A$1,1,,COUNT(Sheet1!$A:$A))),"")

　次に、Sheet1のD5セルに下記の関数を入力して下さい。

=IF(AND($C5>"1904/1/1"+0,$C5<"9999/12/31"+1),WORKDAY(EOMONTH($C5,-1),1,OFFSET(Sheet1!$A$1,1,,COUNT(Sheet1!$A:$A))),"")

　次に、Sheet1のD6セルに下記の関数を入力して下さい。

=IF(AND($C3>"1904/3/1"+0,$C3<"9999/12/31"+1),WORKDAY(EDATE($C6,-2),1,OFFSET(Sheet1!$A$1,1,,COUNT(Sheet1!$A:$A))),"")

　次に、Sheet1のE3セルに下記の関数を入力して下さい。

=IF(AND($C3>"1904/1/4"+0,$C3<"9999/12/31"+1),WORKDAY(INT($C3)+1,-1,OFFSET(Sheet1!$A$1,1,,COUNT(Sheet1!$A:$A))),"")

　次に、Sheet1のE6セルに下記の関数を入力して下さい。

=IF(AND($C5>"1904/1/4"+0,$C5<"9999/12/31"+1),WORKDAY(INT($C5)+1,-1,OFFSET(Sheet1!$A$1,1,,COUNT(Sheet1!$A:$A))),"")

　次に、Sheet1のF3セルに下記の関数を入力して下さい。

=IF(AND(COUNT($D3,$E3)=2,$D3<=$E3),IF(TODAY()>=INT(SUM($D3)),IF(TODAY()>INT(SUM($E3)),"期限切れ","手続き可能中"),"手続き必要なし"),"")

　次に、Sheet1のD3セルをコピーして、　Sheet1のD4セルに貼り付けて下さい。
　次に、Sheet1のE3セルをコピーして、　Sheet1のE4～E5のセル範囲に貼り付けて下さい。
　次に、Sheet1のF3セルをコピーして、　Sheet1のF4～F6のセル範囲に貼り付けて下さい。

　設定方法は以上になります。

　只、1点疑問があります。

>「D5」に表示する日時：「C5」に入力されている日時から、1ヶ月前（例えば、「C5」に2016年4月30日と入力されているとすれば2016年4月1日となります。）からが「開始日時」となります。開始日時の開始日は必ず「1日」となります。

>「D5」に表示してはいけない日時：土曜日、日曜日、祝日（振替休日を含む）、12月29日～1月3日（年末年始）

>「開始日」については「土曜日、日曜日、祝日（振替休日を含む）、12月29日～1月3日（年末年始）に該当する期間の翌日の日付を表示させる」ということです。

>「E5」に表示する日時：基本的には「C5」を表示する。

>終了日（「E3～E6」）の表示は、「■表示してはいけない（取り除く）「開始日時」、「終了日時」について」の項目にある条件付きで、その「前日」を表示させます

という条件ですと、例えばC5に入力されている日付が2016/5/1(日)だった場合、D5セルの開始日はその翌日の2016/5/2になるのに対し、E5セルの終了日は2016/4/29(昭和の日)、2016/4/30(土)、2016/5/1(日)の3連休の前日である2016/4/28となってしまい、開始日と終了日が逆転してしまいます。
　同様の事は、C5セルに1月1日～3日の日付が入力された場合や、1日が土日となっている月の初日の日付が入力された場合にも起こります。
　質問者様の条件では、この様な矛盾した期間設定とならざるを得ない場合が出て来る事が御座いますが、その様な場合にはどの様にすれば宜しいのでしょうか?

お礼

回答ありがとうございます。

>回答No.11は色々と間違っておりました。
　ですから、Sheet1に関しては以下の様にして下さい。

分かりました。
回答No.11は飛ばして入力します。

>という条件ですと、例えばC5に入力されている日付が2016/5/1(日)だった場合、D5セルの開始日はその翌日の2016/5/2になるのに対し、E5セルの終了日は2016/4/29(昭和の日)、2016/4/30(土)、2016/5/1(日)の3連休の前日である2016/4/28となってしまい、開始日と終了日が逆転してしまいます。
　同様の事は、C5セルに1月1日～3日の日付が入力された場合や、1日が土日となっている月の初日の日付が入力された場合にも起こります。
　質問者様の条件では、この様な矛盾した期間設定とならざるを得ない場合が出て来る事が御座いますが、その様な場合にはどの様にすれば宜しいのでしょうか?

また、説明不足で申し訳ありません。
C5に入力されている日付が2016/5/1(日)の場合、D5の開始日は2016/5/2となり、E5の終了日は2016/5/31となります。
「C3～C5」に入力されている日付は必ずその月の末日となっております（例えば、3月だった場合、31日、4月だった場合、30日。）。
したがって、何も問題ありません。

引き続き、よろしくお願いします。

回答No.11

kagakusuki

回答No.10の続きです。

　次に、Sheet1のD3セルに下記の関数を入力して下さい。

=IF(AND($C3>1,$C3<"9999/12/31"+1),WORKDAY(EOMONTH($C3,-3),1,OFFSET(Sheet2!$A$1,1,,COUNT(Sheet2!$A:$A))),"")

　次に、Sheet1のD4セルに下記の関数を入力して下さい。

=IF(AND($C4>1,$C4<"9999/12/31"+1),WORKDAY(EOMONTH($C4,-3),1,OFFSET(Sheet2!$A$1,1,,COUNT(Sheet2!$A:$A))),"")

　次に、Sheet1のD5セルに下記の関数を入力して下さい。

=IF(AND($C5>1,$C5<"9999/12/31"+1),WORKDAY(EOMONTH($C5,-1),1,OFFSET(Sheet2!$A$1,1,,COUNT(Sheet2!$A:$A))),"")

　次に、Sheet1のD6セルに下記の関数を入力して下さい。

=IF(AND($C3>1,$C3<"9999/12/31"+1),WORKDAY(EDATE($C6,-2),1,OFFSET(Sheet2!$A$1,1,,COUNT(Sheet2!$A:$A))),"")

　次に、Sheet1のE3セルに下記の関数を入力して下さい。

=IF(AND($C3>1,$C3<"9999/12/31"+1),WORKDAY(INT($C3)-1,1,OFFSET(Sheet2!$A$1,1,,COUNT(Sheet2!$A:$A))),"")

　次に、Sheet1のF3セルに下記の関数を入力して下さい。

=IF(AND($C3>1,$C3<"9999/12/31"+1,TODAY()>=INT(SUM($D3)),TODAY()<=INT(SUM($E3))),"手続き可能中","")

　因みに、もし例えば、現在の日時が「開始日時」当日の午前9:30以降(午前9:30丁度を含む)～「終了日時」当日の16:55直前(16:55は含まない)までの範囲内において、「手続き可能中」と表示させる様にする場合には、Sheet1のF3セルに入力する関数を下記の様なものにします。

=IF(AND($C3>1,$C3<"9999/12/31"+1,NOW()>=INT(SUM($D3))+"9:30",NOW()<INT(SUM($E3))+"16:55"),"手続き可能中","")

　次に、Sheet1のE3～F3のセル範囲をコピーして、Sheet1のE4～F6のセル範囲に貼り付けて下さい。

　これでお求めの結果が得られるかと思います。

お礼

回答ありがとうございます。

引き続きよろしくお願いします。

回答No.10

kagakusuki

回答No.9の続きです。

　次に、以下の操作を行って、条件付き書式を設定して下さい。

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

=AND(G9<>"",COUNTIF($C:$C,G9)=0)

と入力
　　↓
「新しい書式ルール」ダイアログボックスの[書式]ボタンをクリック
　　↓
現れた「セルの書式設定」ダイアログボックスの[表示形式]タブをクリック
　　↓
現れた「分類」欄の選択肢の中にある[ユーザー定義]をクリック
　　↓
現れた「種類」欄に

[赤]yyyy/m/d("要確認")

と入力
　　↓
「セルの書式設定」ダイアログボックスの[塗りつぶし]タブをクリック
　　↓
現れた背景色のサンプルの中にある黄色の四角形をクリック
　　↓
「セルの書式設定」ダイアログボックスの[OK]ボタンをクリック
　　↓
「新しい書式ルール」ダイアログボックスの[OK]ボタンをクリック
　　↓
現れた「条件付き書式ルールの管理」ダイアログボックスの「適用先」欄に

=$G$9

と入力
(或いは該当する「適用先」欄をクリックしてから、Sheet2のG9セルを2回クリック)
　　↓
「条件付き書式ルールの管理」ダイアログボックスの[新規ルール]ボタンをクリック
　　↓
現れた「新しい書式ルール」ダイアログボックスの「ルールの種類を選択して下さい」欄の中にある[数式を使用して、書式設定するセルを決定]をクリック
　　↓
現れた「次の数式を満たす場合に値を書式設定」欄の中に

=AND(G9<>"",COUNTIF($C:$C,G9)=0,TEXT(($F$1+COLUMN()-COLUMN($G:$G))*365.242374471529-693879.06308625+"1:30","h:m:s")-"3:0"<0)

と入力
　　↓
「新しい書式ルール」ダイアログボックスの[書式]ボタンをクリック
　　↓
現れた「セルの書式設定」ダイアログボックスの[表示形式]タブをクリック
　　↓
現れた「分類」欄の選択肢の中にある[ユーザー定義]をクリック
　　↓
現れた「種類」欄に

yyyy/m/d("要確認")

と入力
　　↓
「セルの書式設定」ダイアログボックスの[フォント]タブをクリック
　　↓
現れた「色」欄をクリック
　　↓
現れた色のサンプルの中にある黄色の四角形をクリック
　　↓
「セルの書式設定」ダイアログボックスの[塗りつぶし]タブをクリック
　　↓
現れた背景色のサンプルの中にある赤色の四角形をクリック
　　↓
「セルの書式設定」ダイアログボックスの[OK]ボタンをクリック
　　↓
「新しい書式ルール」ダイアログボックスの[OK]ボタンをクリック
　　↓
「条件付き書式ルールの管理」ダイアログボックスの中に複数表示されている「適用先」欄の内、最も上にある欄に

=$G$9

と入力
(或いは該当する「適用先」欄をクリックしてから、Sheet2のG9セルを2回クリック)
　　↓
「条件付き書式ルールの管理」ダイアログボックスの[新規ルール]ボタンをクリック
　　↓
現れた「新しい書式ルール」ダイアログボックスの「ルールの種類を選択して下さい」欄の中にある[数式を使用して、書式設定するセルを決定]をクリック
　　↓
現れた「次の数式を満たす場合に値を書式設定」欄の中に

=AND(G21<>"",COUNTIF($D:$D,G21)=0)

と入力
　　↓
「新しい書式ルール」ダイアログボックスの[書式]ボタンをクリック
　　↓
現れた「セルの書式設定」ダイアログボックスの[表示形式]タブをクリック
　　↓
現れた「分類」欄の選択肢の中にある[ユーザー定義]をクリック
　　↓
現れた「種類」欄に

[赤]yyyy/m/d("要確認")

と入力
　　↓
「セルの書式設定」ダイアログボックスの[塗りつぶし]タブをクリック
　　↓
現れた背景色のサンプルの中にある黄色の四角形をクリック
　　↓
「セルの書式設定」ダイアログボックスの[OK]ボタンをクリック
　　↓
「新しい書式ルール」ダイアログボックスの[OK]ボタンをクリック
　　↓
「条件付き書式ルールの管理」ダイアログボックスの中に複数表示されている「適用先」欄の内、最も上にある欄に

=$G$21

と入力
(或いは該当する「適用先」欄をクリックしてから、Sheet2のG21セルを2回クリック)
　　↓
「条件付き書式ルールの管理」ダイアログボックスの[新規ルール]ボタンをクリック
　　↓
現れた「新しい書式ルール」ダイアログボックスの「ルールの種類を選択して下さい」欄の中にある[数式を使用して、書式設定するセルを決定]をクリック
　　↓
現れた「次の数式を満たす場合に値を書式設定」欄の中に

=AND(G21<>"",COUNTIF($D:$D,G21)=0,TEXT(($F$1+COLUMN()-COLUMN($G:$G))*365.242012008987-693691.930838468+"1:30","h:m:s")-"3:0"<0)

と入力
　　↓
「新しい書式ルール」ダイアログボックスの[書式]ボタンをクリック
　　↓
現れた「セルの書式設定」ダイアログボックスの[表示形式]タブをクリック
　　↓
現れた「分類」欄の選択肢の中にある[ユーザー定義]をクリック
　　↓
現れた「種類」欄に

yyyy/m/d("要確認")

と入力
　　↓
「セルの書式設定」ダイアログボックスの[フォント]タブをクリック
　　↓
現れた「色」欄をクリック
　　↓
現れた色のサンプルの中にある黄色の四角形をクリック
　　↓
「セルの書式設定」ダイアログボックスの[塗りつぶし]タブをクリック
　　↓
現れた背景色のサンプルの中にある赤色の四角形をクリック
　　↓
「セルの書式設定」ダイアログボックスの[OK]ボタンをクリック
　　↓
「新しい書式ルール」ダイアログボックスの[OK]ボタンをクリック
　　↓
「条件付き書式ルールの管理」ダイアログボックスの中に複数表示されている「適用先」欄の内、最も上にある欄に

=$G$21

と入力
(或いは該当する「適用先」欄をクリックしてから、Sheet2のG21セルを2回クリック)
　　↓
「条件付き書式ルールの管理」ダイアログボックスの[適用]ボタンをクリック
　　↓
「条件付き書式ルールの管理」ダイアログボックスの[OK]ボタンをクリック

　次に、Sheet2のG列全体をコピーして、「Sheet1のC列に入力されている日付の中で最も古い日付の3ヶ月前の日の年～Sheet1のE列に表示される日付の中で最も新しい日付の年」の期間が「幾つの年に跨っているのかを数えた値」の数だけ、G列の右隣の列範囲に貼り付けて下さい。
　例えばC3セルに2015/1/11、C4セルに2015/12/29とそれぞれ入力されていて、C5セルやC6セルに入力されている日付はいずれも2015/1/11～2015/12/29の期間の途中の日付である場合には、C列～E列に入力、或いは表示されている日付は2014/12/1～2016/1/4の範囲になりますから、2014年～2016年の3つの年に跨っている事になりますので、Sheet2のG列全体をコピーした後、Sheet2のH列～I列に貼り付ける事で、G列と合わせて3列に同様の関数が存在している様にします。
　次に、Sheet2のA2セルに下記の関数を入力して下さい。(参照する列範囲を$G:$Iとするのは、跨っている年が3年に亘っている場合)

=IF(ROWS($2:2)>COUNT($G:$I),"",SMALL($G:$I,ROWS($2:2)))

　次に、Sheet2のA2セルをコピーして、Sheet2のA3以下に貼り付けて下さい。
　次に、Sheet2のC列に各年ごとの「春分の日」の年月日を、D列には各年ごとの「秋分の日」の年月日をそれぞれ入力して下さい。

　これで、Sheet2上に「Sheet1のD列に表示される日付の中で最も古い年の元旦から数えて3年分の祝日、振替休日、年末年始の社定休日の日付が自動的に表示されます。(但し、「春分の日」と「秋分の日」に関しては、可能な限りSheet2のC列～D列に手入力しておく事が望ましい)

※まだ途中なのですが、そろそろこの回答欄に入力可能な文字数の限度である4000文字を超えてしまいますので、残りは又後で投稿する事に致します。

お礼

回答No.9に引き続き、回答ありがとうございます。

>次に、Sheet2のG列全体をコピーして、「Sheet1のC列に入力されている日付の中で最も古い日付の3ヶ月前の日の年～Sheet1のE列に表示される日付の中で最も新しい日付の年」の期間が「幾つの年に跨っているのかを数えた値」の数だけ、G列の右隣の列範囲に貼り付けて下さい。
　例えばC3セルに2015/1/11、C4セルに2015/12/29とそれぞれ入力されていて、C5セルやC6セルに入力されている日付はいずれも2015/1/11～2015/12/29の期間の途中の日付である場合には、C列～E列に入力、或いは表示されている日付は2014/12/1～2016/1/4の範囲になりますから、2014年～2016年の3つの年に跨っている事になりますので、Sheet2のG列全体をコピーした後、Sheet2のH列～I列に貼り付ける事で、G列と合わせて3列に同様の関数が存在している様にします。

この部分の文章を何度も読み直したのですが、どのようにしたらいいのかよく分かりません。
分かりやすいように説明してくださってくれたと思うのですが、自分には理解できませんでした。

より分かりやすいように説明してしただけないでしょうか？

回答よろしくお願いします。

回答No.9

kagakusuki

回答No.8の続きです。

　次に、Sheet2のF13セルに「憲法記念日」と入力して下さい。
　次に、Sheet2のG13セルに下記の関数を入力して下さい。

=IF(AND(ISNUMBER(1/($F$1=INT($F$1))),$F$1>=1904,$F$1<9999),DATE($F$1+COLUMN()-COLUMN($G:$G),5,3),"")

　次に、Sheet2のF14セルに「みどりの日」と入力して下さい。
　次に、Sheet2のG14セルに下記の関数を入力して下さい。

=IF(AND(ISNUMBER(1/($F$1=INT($F$1))),$F$1>=1904,$F$1<9999),DATE($F$1+COLUMN()-COLUMN($G:$G),5,4),"")

　次に、Sheet2のF15セルに「こどもの日」と入力して下さい。
　次に、Sheet2のG15セルに下記の関数を入力して下さい。

=IF(AND(ISNUMBER(1/($F$1=INT($F$1))),$F$1>=1904,$F$1<9999),DATE($F$1+COLUMN()-COLUMN($G:$G),5,5),"")

　次に、Sheet2のF16セルに「振替休日」と入力して下さい。
　次に、Sheet2のG16セルに下記の関数を入力して下さい。

=IF(AND(ISNUMBER(1/(WEEKDAY(G15)<4)),ISNUMBER(G15)),G15+1,"")

　次に、Sheet2のF17セルに「海の日」と入力して下さい。
　次に、Sheet2のG17セルに下記の関数を入力して下さい。

=IF(AND(ISNUMBER(1/($F$1=INT($F$1))),$F$1>=1904,$F$1<9999),DATE($F$1+COLUMN()-COLUMN($G:$G),7,21-WEEKDAY(DATE($F$1+COLUMN()-COLUMN($G:$G),7,0),3)),"")

　次に、Sheet2のF18セルに「山の日」と入力して下さい。
　次に、Sheet2のG18セルに下記の関数を入力して下さい。

=IF(AND(ISNUMBER($F$1+COLUMN()-COLUMN($G:$G)),$F$1+COLUMN()-COLUMN($G:$G)>2015),DATE($F$1+COLUMN()-COLUMN($G:$G),8,11),"")

　次に、Sheet2のF19セルに「敬老の日」と入力して下さい。
　次に、Sheet2のG19セルに下記の関数を入力して下さい。

=IF(AND(ISNUMBER(1/($F$1=INT($F$1))),$F$1>=1904,$F$1<9999),DATE($F$1+COLUMN()-COLUMN($G:$G),9,21-WEEKDAY(DATE($F$1+COLUMN()-COLUMN($G:$G),9,0),3)),"")

　次に、Sheet2のF20セルに「国民の休日」と入力して下さい。
　次に、Sheet2のG20セルに下記の関数を入力して下さい。

=IF(ISNUMBER(1/(G21=G19+2)),G19+1,"")

　次に、Sheet2のF21セルに「秋分の日」と入力して下さい。
　次に、Sheet2のG21セルに下記の関数を入力して下さい。

=IF(AND(ISNUMBER(1/($F$1=INT($F$1))),$F$1>=1904,$F$1<9999),IF(COUNTIFS($D:$D,">="&$F$1+COLUMN()-COLUMN($G:$G)&"/1/1",$D:$D,"<"&$F$1+COLUMN()-COLUMN($G:$G)+1&"/1/1"),SMALL($D:$D,COUNTIF($D:$D,"<"&$F$1+COLUMN()-COLUMN($G:$G)&"/1/1")+1),INT(($F$1+COLUMN()-COLUMN($G:$G))*365.242012008987-693691.930838468)),"")

　次に、Sheet2のF22セルに「振替休日」と入力して下さい。
　次に、Sheet2のG22セルに下記の関数を入力して下さい。

=IF(ISNUMBER(1/(WEEKDAY(G21)=1)),G21+1,"")

　次に、Sheet2のF23セルに「体育の日」と入力して下さい。
　次に、Sheet2のG23セルに下記の関数を入力して下さい。

=IF(AND(ISNUMBER(1/($F$1=INT($F$1))),$F$1>=1904,$F$1<9999),DATE($F$1+COLUMN()-COLUMN($G:$G),10,14-WEEKDAY(DATE($F$1+COLUMN()-COLUMN($G:$G),10,0),3)),"")

　次に、Sheet2のF24セルに「文化の日」と入力して下さい。
　次に、Sheet2のG24セルに下記の関数を入力して下さい。

=IF(AND(ISNUMBER(1/($F$1=INT($F$1))),$F$1>=1904,$F$1<9999),DATE($F$1+COLUMN()-COLUMN($G:$G),11,3),"")

　次に、Sheet2のF25セルに「振替休日」と入力して下さい。
　次に、Sheet2のG25セルに下記の関数を入力して下さい。

=IF(ISNUMBER(1/(WEEKDAY(G24)=1)),G24+1,"")

　次に、Sheet2のF26セルに「勤労感謝の日」と入力して下さい。
　次に、Sheet2のG26セルに下記の関数を入力して下さい。

=IF(AND(ISNUMBER(1/($F$1=INT($F$1))),$F$1>=1904,$F$1<9999),DATE($F$1+COLUMN()-COLUMN($G:$G),11,23),"")

　次に、Sheet2のF27セルに「天皇誕生日」と入力して下さい。
　次に、Sheet2のG27セルに下記の関数を入力して下さい。

=IF(AND(ISNUMBER(1/($F$1=INT($F$1))),$F$1>=1904,$F$1<9999),DATE($F$1+COLUMN()-COLUMN($G:$G),12,23),"")

　次に、Sheet2のF28セルに「社定休日」と入力して下さい。
　次に、Sheet2のG28セルに下記の関数を入力して下さい。

=IF(AND(ISNUMBER(1/($F$1=INT($F$1))),$F$1>=1904,$F$1<9999),DATE($F$1+COLUMN()-COLUMN($G:$G),1,1),"")

　次に、Sheet2のF29セルに「社定休日」と入力して下さい。
　次に、Sheet2のG29セルに下記の関数を入力して下さい。

=IF(AND(ISNUMBER(1/($F$1=INT($F$1))),$F$1>=1904,$F$1<9999),DATE($F$1+COLUMN()-COLUMN($G:$G),1,2),"")

　次に、Sheet2のF30セルに「社定休日」と入力して下さい。
　次に、Sheet2のG30セルに下記の関数を入力して下さい。

=IF(AND(ISNUMBER(1/($F$1=INT($F$1))),$F$1>=1904,$F$1<9999),DATE($F$1+COLUMN()-COLUMN($G:$G),1,3),"")

　次に、Sheet2のF31セルに「社定休日」と入力して下さい。
　次に、Sheet2のG31セルに下記の関数を入力して下さい。

=IF(AND(ISNUMBER(1/($F$1=INT($F$1))),$F$1>=1904,$F$1<9999),DATE($F$1+COLUMN()-COLUMN($G:$G),12,29),"")

　次に、Sheet2のF32セルに「社定休日」と入力して下さい。
　次に、Sheet2のG32セルに下記の関数を入力して下さい。

=IF(AND(ISNUMBER(1/($F$1=INT($F$1))),$F$1>=1904,$F$1<9999),DATE($F$1+COLUMN()-COLUMN($G:$G),12,30),"")

　次に、Sheet2のF33セルに「社定休日」と入力して下さい。
　次に、Sheet2のG33セルに下記の関数を入力して下さい。

=IF(AND(ISNUMBER(1/($F$1=INT($F$1))),$F$1>=1904,$F$1<9999),DATE($F$1+COLUMN()-COLUMN($G:$G),12,31),"")

※まだ途中なのですが、そろそろこの回答欄に入力可能な文字数の限度である4000文字を超えてしまいますので、残りは又後で投稿する事に致します。

お礼

回答No.8に引き続き、回答ありがとうございます。

引き続きよろしくお願いします。

回答No.8

kagakusuki

>誕生日が1月29日の方ですと、C6には2月29日（うるう年の場合。うるう年でない場合、2月28日）が入っています。

という事ですと、閏年以外の年におけるC6セルに入力される日付は、誕生日が1月28日の方と1月29日の方が共に2月28日となって区別がつきませんので、どちらの方の場合もD6セルに表示させる日付を前年の12月28日にせざるを得ない事になります。
　又、誕生日が8月30日の方と8月31日の方の場合も、同様にD6セルに表示させる日付をどちらの方も7月30日にせざるを得ない事になります。
　この問題は、元データであるC6セルの値が同一で区別を付ける術が無い以上、誕生日を示す正しいデータを新たに別個に用意して頂くといった事でもない限りは、解決方法が存在しない事になりますので、許容して頂くより他には御座いませんが、宜しいでしょうか?

　それで、条件を確認するだけではいつまで経っても話が進みませんので、取り敢えず、判る部分から回答して行く事に致します。
　まず、休日のリストを作成するSheet2に関してですが、回答No.1に対する補足内容において、C列に入力される日時が

>現在日（2015年5月9日）からExcel2010の仕様の最終日（9999年12月31日）としておすきます。

とされておりましたので、祝日の決め方に関して既に廃止されたものは考慮に入れず、現在の施行されている祝日の決まりを基にして、「山の日」に関しては2016年から有効になる様にした決め方で、祝日や振替休日の日付を求める事に致します。
　尚、回答No.2で述べました様に、「春分の日」と「秋分の日」に関しては単純計算では正しい日付を確実に求める事は出来ませんので、基本的には下記の参考URLのページ

【参考URL】
　国立天文台 > 暦計算室/日・月出入り情報 > 暦要項
　　http://eco.mtk.nao.ac.jp/koyomi/yoko/
　国立天文台 > 暦計算室/日・月出入り情報 > 暦要項 > 平成27年（2015）
　　http://eco.mtk.nao.ac.jp/koyomi/yoko/2016/rekiyou161.html

で国立天文台が発表している情報などで「春分の日」と「秋分の日」を確認して、Sheet2のC列、D列に手入力して頂き、そこに入力されている日付を祝日の一覧表に(関数を使って)反映させるという方法で対応する事にし、未だ日付が正式に決まってはいない故にSheet2にデータを入力出来ない再来年以降の春分・秋分の日に関しましては、仕方がありませんので概算値を表示させる様にする事に致します。
　今回使用する「春分の日」や「秋分の日」を求める関数は、Sheet2のC列、D列に該当する年の日付が存在している場合にはその日付を表示し、該当する日付が存在していなかった場合には、概算値を計算によって求める様にしております。
　その概算値を求めるやり方は、「春分や秋分が起きてから、次の春分や秋分が起きるまでに要する時間(時間的間隔、周期)は、年々一定の割合で変化して行く」という仮定の下に、各年ごとの西暦年と春分や秋分が起きる時間間隔をグラフ上にプロットした際に、各プロット点が一直線に並んでいるものとした場合における線の傾きと切片を、前もって私が計算で求めておきましたので、その傾きと切片の値を使った関数(セルに入力されている関数)で、該当年における春分や秋分が起きる瞬間の日時(日付+時刻)を求め、その日時が何年何月何日の日付になるのかという事を求めて表示させております。
　尚、傾きと切片の値は、国立天文台の暦計算室が発表している1955年～2014年の春分・秋分が起きる日時(何何年何月何日の何時何分に起きるのかという情報)を、以前、個人的に調べた事があったため、今回そのデータに2015年と2016年のデータも追加した上で、Excelを使って最小二乗法で求めたものです。
　但し、回答No.2で述べました様に、春分や秋分が起きる瞬間の時刻は、地球以外の天体の影響により計算通りの時刻になるとは限らず、計算結果には常に誤差が含まれる事になりますので、実際の日付とは異なってしまう恐れが常に存在します。
　実際、前述の計算方法で求めた春分・秋分が起きる日時も、暦計算室が発表した1955年～2016年の春分・秋分が起きる日時と比べて、最大で15分弱の違いが生じています。
　そのため、計算によって求めた春分・秋分の日付をそのまま信頼する訳には参りませんので、Sheet2の祝日の一覧表上に表示される春分・秋分の日が計算によって求めた日時である場合には、条件付き書式を使用して、文字色を赤とした上で日付の後ろに「(要確認)」と表示させる様にしております。
　そして更に計算で求めた春分・秋分が起きる時刻が、日付の変わり目である午前0時(真夜中の24時)の前後1時間半以内であった場合には、その日付が信用出来ないものである事を示すために、条件付き書式を使用してセルを赤く塗りつぶした上で、文字色が黄色になる様にしております。

※日付が信頼出来ないものである事は重要な問題ですので、この条件付き書式の件は忘れない様にして下さい。
そして、もし他の方に引き継ぎ等を行う様な事がある場合にも、この事を忘れずに伝える様にして下さい。

　説明が長過ぎましたが、関数や条件付き書式の設定方法は以下の様なものになります。

　　まず、Sheet2のF1セルに下記の関数を入力して下さい。

=IF(COUNTIFS(Sheet1!$C:$C,">=1",Sheet1!$C:$C,"<=9999/12/31"),YEAR(MIN(Sheet1!$C:$C)),"")

　次に、Sheet2のF4セルに「元旦」と入力して下さい。
　次に、Sheet2のG4セルに下記の関数を入力して下さい。

=IF(AND(ISNUMBER(1/($F$1=INT($F$1))),$F$1>=1904,$F$1<9999),DATE($F$1+COLUMN()-COLUMN($G:$G),1,1),"")

　次に、Sheet2のF5セルに「振替休日」と入力して下さい。
　次に、Sheet2のG5セルに下記の関数を入力して下さい。

=IF(ISNUMBER(1/(WEEKDAY(G4)=1)),G4+1,"")

　次に、Sheet2のF6セルに「成人の日」と入力して下さい。
　次に、Sheet2のG6セルに下記の関数を入力して下さい。

=IF(AND(ISNUMBER(1/($F$1=INT($F$1))),$F$1>=1904,$F$1<9999),DATE($F$1+COLUMN()-COLUMN($G:$G),1,14-WEEKDAY(DATE($F$1+COLUMN()-COLUMN($G:$G),1,0),3)),"")

　次に、Sheet2のF7セルに「建国記念の日」と入力して下さい。
　次に、Sheet2のG7セルに下記の関数を入力して下さい。

=IF(AND(ISNUMBER(1/($F$1=INT($F$1))),$F$1>=1904,$F$1<9999),DATE($F$1+COLUMN()-COLUMN($G:$G),2,11),"")

　次に、Sheet2のF8セルに「振替休日」と入力して下さい。
　次に、Sheet2のG8セルに下記の関数を入力して下さい。

=IF(ISNUMBER(1/(WEEKDAY(G7)=1)),G7+1,"")

　次に、Sheet2のF9セルに「春分の日」と入力して下さい。
　次に、Sheet2のG9セルに下記の関数を入力して下さい。

=IF(AND(ISNUMBER(1/($F$1=INT($F$1))),$F$1>=1904,$F$1<9999),IF(COUNTIFS($C:$C,">="&$F$1+COLUMN()-COLUMN($G:$G)&"/1/1",$C:$C,"<"&$F$1+COLUMN()-COLUMN($G:$G)+1&"/1/1"),SMALL($C:$C,COUNTIF($C:$C,"<"&$F$1+COLUMN()-COLUMN($G:$G)&"/1/1")+1),INT(($F$1+COLUMN()-COLUMN($G:$G))*365.242374471529-693879.06308625)),"")

　次に、Sheet2のF10セルに「振替休日」と入力して下さい。
　次に、Sheet2のG10セルに下記の関数を入力して下さい。

=IF(ISNUMBER(1/(WEEKDAY(G9)=1)),G9+1,"")

　次に、Sheet2のF11セルに「昭和の日」と入力して下さい。
　次に、Sheet2のG11セルに下記の関数を入力して下さい。

=IF(AND(ISNUMBER(1/($F$1=INT($F$1))),$F$1>=1904,$F$1<9999),DATE($F$1+COLUMN()-COLUMN($G:$G),4,29),"")

　次に、Sheet2のF12セルに「振替休日」と入力して下さい。
　次に、Sheet2のG12セルに下記の関数を入力して下さい。

=IF(ISNUMBER(1/(WEEKDAY(G11)=1)),G11+1,"")

※まだ途中なのですが、そろそろこの回答欄に入力可能な文字数の限度である4000文字を超えてしまいますので、残りは又後で投稿する事に致します。

お礼

回答ありがとうございます。

>という事ですと、閏年以外の年におけるC6セルに入力される日付は、誕生日が1月28日の方と1月29日の方が共に2月28日となって区別がつきませんので、どちらの方の場合もD6セルに表示させる日付を前年の12月28日にせざるを得ない事になります。
　又、誕生日が8月30日の方と8月31日の方の場合も、同様にD6セルに表示させる日付をどちらの方も7月30日にせざるを得ない事になります。
　この問題は、元データであるC6セルの値が同一で区別を付ける術が無い以上、誕生日を示す正しいデータを新たに別個に用意して頂くといった事でもない限りは、解決方法が存在しない事になりますので、許容して頂くより他には御座いませんが、宜しいでしょうか?

はい、構わないです。

式の意味が分からないため、そのまま入力しています。
引き続きよろしくお願いします。