エクセルで第2,3,4土曜日に色、時間を日数に

質問

色々調べてみたのですが、エクセルで応用が効かなくて困っています。
１つは条件付き書式です。
条件１には日曜日を設定し、これは大丈夫でした。
Ｑ：１　次に、条件２には第2,3,4土曜日に色を付けるための関数がわかりません。
Ｑ：２　それと、条件３に祝祭日を設定したいのですが、これもわかりません。
祝祭日の表は見付けましたので、Ａ列に2011/1/1,2011/1/10等の表の準備はできています。（Ｂ列には次の年で５年分ほど準備しています。）
Ｑ３：　最後に、条件付き書式とは別で、１２：３０（１２時間３０分）勤務した場合、１日８時間労働とした場合1.45日とするための関数がわかりません。
年のせいか、頭が固く困っています。
どなたか、ご教示の程宜しくお願い申し上げます。

回答No.3

kagakusuki

回答番号ANo.2です。

>別のシートから参照する場合(1)　=COUNTIF(INDIRECT("A:B"),D1)>0ではうまくなりません。

　申し訳御座いません、シートを指定するのを忘れておりました。

>シート名は　祝祭日　としています。

という事なので、

=COUNTIF(INDIRECT("祝祭日!A:B"),D1)>0

という様な形にするべきでした。
　但し、これは、

>Ｂ列には次の年で５年分ほど準備しています。

という記述が正しかった場合の話で、実際にはB列には次の年の祝祭日は記載されておられない訳ですから、このままでは正しく動作する事はありません。
　ですから、実際の再来年（2013年）以降の祝祭日が、どこに記載されているかに関しても判らなければ、正しく動作する数式になるという保証は致しかねます。
　実際には、再来年以降の祝祭日の日付は、2012年と同じC列に存在しているのでしょうか、それともE列から右方向の列に存在しているのでしょうか？
　C列に存在しているとした場合の数式は、次の様になります。

=COUNTIF(INDIRECT("祝祭日!$A:$C"),D1)>0

　又、E列から右方向の列に存在しているとした場合では、日本の祝祭日は一年間に15～16日ですから、それ以外に1月2日、1月3日、12月25日、盆休み、創立記念日や夏冬休み等の会社独自の休日を含めない場合には、祝祭日シートに日付が存在している行番号は、1行目～16行目の範囲内に全て収まるため、その場合の数式は、、次の様になります。

=COUNTIF(INDIRECT("祝祭日!$1:$16"),D1)>0

　もし、日付が17行目以下にも存在する可能性がある場合には、指定する範囲内に全ての日付が確実に収まる様に、実際の行数を上回るのに充分な行番号となる様に、数式中の「$16」の部分の数字を、適時増やして下さい。

>Ｑ３：に関してですが
>１２時間３０分は1.5625日でした。
>それで、１日１時間×３１日（＝３１時間）の合計（ＳＵＭ）をしたところ、７：００となりました。
>多分、３１時間－２４時間＝７時間の答えだと思います。合計時間を３１時間と表示するにはどうしたらよいのでしょうか？

　すみませんが、何の話をされておられるのか、さっぱり解りません。
　一般的な1日8時間労働では、出勤時刻から退勤時刻までが9時間前後で、間に1時間前後の休憩時間を入れる事で、実働時間が 9時間－1時間＝8時間 となる様にしているのであり、 8時間－1時間＝7時間 だと、一日7時間労働になってしまいますが、質問者様の御勤め先では一日7時間労働なのでしょうか？
　それに、

>１日１時間×３１日

という計算では、休日にも何か1時間ずつ加算されるという事ですから、休憩時間や残業時間ではないようですが、この1時間は一体何の時間なのでしょうか？

>合計（ＳＵＭ）をしたところ、７：００となりました。

　こちらも意味不明です。
　１時間/日×３１日＝31時間ですら、７：００とはなりません。

>多分、３１時間－２４時間＝７時間の答えだと思います。

との事ですが、1ヶ月間の合計時間から1日を引く目的もわかりません。
　もしかすると、この２４時間とは、1日の事ではなく、偶々24時間となっているだけで、何かの規律で定められている値なのでしょうか？
　混乱されたまま行った計算結果だけを示されても、何を質問されているのか解る人間はいないと思いますから、状況と条件を整理して、何をなさりたいのかを御知らせ願います。

回答No.2

kagakusuki

条件付き書式は、複数の条件を同時に満たしている場合には、条件の番号が若い順に、優先して書式が現れます。
　例えば、2012年2月11日は第2土曜日であると同時に、建国記念の日でもありますが、質問者様の条件の設定では、条件3よりも上位にある条件2で設定されている、第2,3,4土曜日の場合の書式が優先して現れるため、最も下位の条件である条件3で設定した祝祭日の書式は現れません。
　ですから、祝祭日の場合の設定は、条件1に設定し、日曜日の場合と、第2,3,4土曜日の場合に関しては、条件2と条件3で設定した方が良いと思います。（日曜日の場合と、第2,3,4土曜日の場合に関しては、同時に満たす日は存在しないため、どちらが優先されても関係ありませんから、それぞれどちらを条件2と条件3のどちらに入れても構いません）

　例えば、D1セルに日付が入力されていて、その日付を基に条件付き書式を設定する場合には、次の様な関数となります。

Ｑ：１

=AND(WEEKDAY(D1)=7,DAY(D1)>7,DAY(D1)<29)

Ｑ：２
　同じシート内のA列とB列に祝祭日の表が存在する場合には、

=COUNTIF($A:$B,D1)>0

　別のシート（例えばSheet）のA列とB列に祝祭日の表が存在する場合には、

=COUNTIF(INDIRECT("A:B"),D1)>0

Ｑ３：に関してですが、何故

>１２：３０（１２時間３０分）勤務した場合、１日８時間労働とした場合1.45日とする

としているのかが解りません。
　８時間の1.45倍は11時間36分であり、12時間30分の1.45分の1は8時間37分14.48275862・・・秒ですから、休憩時間が1時間入るとしても辻褄が合いません。
　それとも、休憩時間が54分という中途半端な時間なのでしょうか？
　どの様な理由から、勤務時間が12時間30分が1.45日となっているのかを、補足欄等を使用して、御教え願います。

お礼

大変、ありがとうございます。
条件２に第2,3,4土曜日と条件３に日曜日の件…お陰様でなりました。
条件１の祝祭日の件ですが、同じシート内に　=COUNTIF($A:$B,D1)>0　とすると大丈夫ですが、
別のシートから参照する場合(1)　=COUNTIF(INDIRECT("A:B"),D1)>0ではうまくなりません。
シート名は　祝祭日　としています。いくつもシートがありますので
(1)の関数の一部にシート名をかまさなくて良いのでしょうか？
ちなみに　シート名の祝祭日は下記の様に入力しています

ちょっと、下の表は見づらいと思いますが
Ａ列は2011年の日、Ｂ列はその名称　・　Ｃ列は2012年の日、Ｄ列はその名称です。

Ａ列　　　　　　Ｂ列 　　 　　 Ｃ列　　　　Ｄ列　　　
2011/1/1　　 元日 　　 　　 2012/1/1 元日
2011/1/10　　 成人の日 　　 2012/1/2 振替休日
2011/2/11　　 建国記念の日 　　 2012/1/9 成人の日
2011/3/21 　　 春分の日 　　 2012/2/11 建国記念の日
2011/4/29　　 昭和の日 　　 2012/3/20 春分の日
2011/5/3 　　 憲法記念日 　　 2012/4/29 昭和の日
2011/5/4 　　 みどりの日 　　 2012/4/30 振替休日

Ｑ３：に関してですが
１２時間３０分は1.5625日でした。
それで、１日１時間×３１日（＝３１時間）の合計（ＳＵＭ）をしたところ、７：００となりました。
多分、３１時間－２４時間＝７時間の答えだと思います。合計時間を３１時間と表示するにはどうしたらよいのでしょうか？すみません、宜しくお願い申し上げます。

回答No.1

aokii

第2,3,4土曜日に色を付けるための関数は無いと思いますので、第2,3,4土曜日と祝祭日のリストを作って、VLOOKUPでその日が存在したら色を付けるようにしてはいかがでしょう。

また、１２：３０（１２時間３０分）勤務した場合、１日８時間労働とした場合に1.45日（1.5625日になると思いますが）とするには、

１２：３０のセルの表示形式を、標準書式で表示(0.520833と表示されます)してから、そのセルを２４倍して日単位に直し(12.5になります)、それを8時間で割って日数(1.5625日になります)にします。

ちなみに、エクセルでは時間を標準書式で表示すると、24時間を１とした割合で表示されますので、１２：３０は0.520833と表示されます。

お礼

早速のご回答ありがとうございます。
説明不足の点もあり申し訳ありません。
インターネットで調べたところ、条件付きの書式～数式が～で、第2,4土曜日に色を付ける場合は、
=AND(WEEKDAY(B6)=7,MOD(INT((DAY(B6)-1)/7),2))との関数がありました。これに第３土曜日を追加したいのですが、解説の理屈が判らず悩んでいます。・・・未解決です。
それと、１２時間３０分は1.5625日でした。失礼しました。ご教示のように１２：３０は0.520833と表示されました。
それで、１日１時間×３１日（＝３１時間）の合計（ＳＵＭ）をしたところ、７：００となりました。
多分、３１時間－２４時間＝７時間の答えだと思います。合計時間を３１時間と表示するにはどうしたらよいのでしょうか？すみません、宜しくお願い申し上げます。