週の労働時間を計算するエクセル

現在、週あたりの労働時間を計算する表を作ろうとしています。

日曜日を起算日とし、1週間の労働時間を計算しているため
図のように土曜日の横の欄にその週の日曜日～土曜日までの合計の労働時間を表示するようにしています。
1週目と5週目は端数週となるので単純にその週の労働時間を合算しています。
そのため下記計算式を用いています。

V13以降のV列に入力されている式

=IF(B13="土",SUM($U$13:U13),0)


しかしこれでは月によって日数も曜日もちがってくるため、いまの計算式ですと
次の月にはめちゃくちゃになってしまいます。

OFFSET関数などを式に組み込むのではないだろうかということを
ぼんやりと認識してはいるのですが、どのようにすれば
毎月の計算を自動的に行えるようになりますでしょうか。

ご回答の程、よろしくお願いいたします。

質問者からの補足コメント

• 申し訳ありません、画像の添付ができていませんでした。
  画像を添付いたしました。

  
    補足日時：2016/11/16 09:02

No.1 回答者： tatsu99 回答日時： 2016/11/15 13:04

図のように土曜日の横の欄にその週の日曜日～土曜日までの合計の労働時間を表示するようにしています。

^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
この図を添付してください。

この回答へのお礼

返事と対応が遅くなり申し訳ありません。
補足に画像を追加いたしましたのでご確認ください。
よろしくお願いいたします。

お礼日時：2016/11/16 09:03

No.2 ベストアンサー 回答者： tatsu99 回答日時： 2016/11/16 12:41

本件、土曜日のほかに月末日も週労働時間を表示しますので、

A列の日付が何年何月のものかを知る必要があります。
その為、一か所、月初日をどこかに記述しておきます。（例 2016/8/1）
添付の例では、W12に書きましたので、関数で参照時"$W$12"で参照します。
もし、ほかの箇所に書いた場合は、適宜、変更してください。

日の列はA13へ
=IF(MONTH($W$12)=MONTH($W$12+ROW()-13),DAY($W$12+ROW()-13),"")
と設定します。
更にオートフィルでA43までコピーします。
これで、31日までの日をもたない月の場合、存在しない日付は空白になります。

曜日の列はB13へ
=IF(A13="","",TEXT($W$12+A13-1,"aaa"))
と設定し、更にオートフィルでB43までコピーします。

週労働時間はV13へ
=IF(OR(B13="土",A13=DAY(EOMONTH($W$12,0))),SUM(INDIRECT("U"&(IF(A13<7,0,A13-WEEKDAY($W$12+A13-1))+13)):U13),"")
と設定し、更にオートフィルでV43までコピーします。

これで、完了です。
W12の月初日を変えることにより、適切に変わることを確認してください。

V13の設定の考え方を簡単に補足しますと
表示対象は、土は月末日の為、OR(B13="土",A13=DAY(EOMONTH($W$12,0))）
が真の場合、表示し、偽の場合は空白　となります。
SUMをとる範囲は、XXから該当セルまでとなります。
XXは、7日未満なら1日から（後で13を加算するので0開始とします）
①土曜日なら該当セルの日-7となります。（７はWEEKDAY関数で取得可能）
②月末日なら該当セルのWEEKDAYを該当セルの日から引きます。
従って①②はともに　該当セルの日ーWEEKDAY(該当セルの日付)となります。
該当セルの日付は、$W$12+A13-1で求められます。
従って XX=A13-WEEKDAY($W$12+A13-1)) となります。
結局、集計開始のセルは
"U"＋XX+13 で与えられます。この式を直接書くと単なる文字列なので
INDIRECT()で関節参照指定を行い、セルの位置であることを明示します。

この回答へのお礼

詳しくありがとうございます。
まずは動作確認のため、適用してみました。
思ったとおりの動作になりました。

ただ、日付、曜日、労働時間を全てCSVからマクロで取り込んでいたため
日付、曜日を取り込まないようにし、ご教示いただいた式を日付、曜日欄に入力しました。
ついでにW12に月初日を取り込むようにし、無事思い通りに動作できました。

まだご教示いただいた式の内容を完全に把握できていないため
これから時間をかけて読み解こうと思います。

ご回答いただきありがとうございました。

こちらベストアンサーにさせて頂きます。

今後ともよろしくお願いいたします。

お礼日時：2016/11/16 14:30