勤怠管理表を作成しています、集計のやり方を教えてください。

この様な勤怠管理表なのですが、土曜日だけ外して各々の集計をだすにはどうすればよいでしょうか？
また、土曜日のみ集計するにはどうすれば良いでしょうか？
会社の締め日が２０日なため、２０日までの集計と、２０以降の集計で分けています。
どの様な関数が適切なのか？どうかわかる方教えてください。
宜しくお願い致します。

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

• 

  ありがとうございます！！
  現在出張なので、社に戻り次第やってみたいと思います。

    補足日時：2017/02/24 13:52

• こんにちは！大分遅くなりましてすみません！！
  B列の曜日は自動ででるようにはなってます。
  この前頂いた関数を入力しましたが、計算が何故かおかしい答えになりました。
  次のファイルに添付致します。
  どこが間違っているか見て頂けないでしょうか？

  
    補足日時：2017/03/01 14:42

• 補足の付け足しです。
  この様に答えは2.1時間になってしまいます。
  どうすればよろしいでしょうか？

  
    補足日時：2017/03/01 14:48

No.2 ベストアンサー 回答者： fujillin 回答日時： 2017/03/01 17:35

ANo1です。

回答時には、表の作成のされ方がまったく不明でしたので、計算しやすい方法を提示しましたが、補足の表を見る限り（細部はぼやけてたりでよくわかりませんが）、違う作り方（見掛けではなく内部的に）をなさっているようですので、ANo1で提示した関数では意味の違うものになってしまう可能性が高いです。
（ANo1で仮定している内容は、回答に示してあります）

どのような作り方でもかまいませんが、その作り方に合った計算方法をとらないと、求める答えが得られる保証はありません。
とは言え、通常の数値計算では見た目と計算内容はほぼ一致していますが、時間や日付計算の場合はエクセルがどのような値を保持しているかで計算内容がまったく異なった結果になる可能性があります。（見掛け上で表示されている値ではありません）

例えば、ANo1では（説明にもあるように）A列、B列とも日付型の同じ値が保持されているものと仮定しています。（同じ値なのでB列の式は「=A4」などの単純なものになっています）
例として、日付型の値「2017/3/1」がセルに入っているとして…
　・書式を「yyyy/m/d」とすると、「2017/3/1」と表示
　・書式を「標準」とすると、「42795」と表示（←シリアル値）
　・書式を「d」とすると、「1」と表示（←回答のA列＝日付）
　・書式を「aaa」とすると、「水」と表示（←回答のB列＝曜日）
となることを利用しています。
（セルの値として、計算に使用されるのは「2017/3/1」に対応するシリアル値です）

それぞれの時刻の項目に関しても同様で、60進法を気にせずに加減算ができることを利用しています。
（結果が負数にならないように注意することは必要ですが…）

ご提示の表では、時刻の部分が文字列（？）のようにも見受けられますので、そのまま計算してくれるのか不明です。
手元で簡単に試してみたところ、時刻形式の文字列に対しては、エクセルは解釈できる範囲で時刻計算をしてくれているみたいではありますが…
（どのような条件の時に変換されるのかは、私にはわかりません）

それなので、もしかすると、補足でご提示の式でも、書式を変えるだけで時刻の計算結果が得られるのかも知れません。
しかし、対象が文字列の場合には、一旦、TIMEVALUE()などを取って変換するか、あるいは時・分に分解して60進法で自分で計算するなどの方が確実と言えそうです。

ANo1で提示した表の作成方法と計算式は、比較的簡単に計算ができる方法として思いついたものですので、他の方法を採用なさるのはもちろんご自由ですが、その際には、その内容に合わせた計算式にしておくことが必要と思います。

この回答へのお礼

分かりました！ちょっとやってみます！

お礼日時：2017/03/01 19:10

No.1 回答者： fujillin 回答日時： 2017/02/23 14:54

こんにちは

回答が無いようですので…


表の内容をどのように作成なさっているのかわかりませんが、時間計算を行うならシリアル値を利用するとそのまま加減算ができるので便利です。

サンプルでひと月分作ると読めなくなってしまうので、小規模に縮小したサンプルで作成してみました。
左上（A1セル）に年月日（日付は1日）を入力すると、A列（日）とB列（曜日）は自動で表示されます。
時間表記部分の書式は全て [h]:mm として時分で表記できるようにしてあります。
（各時間入力（？）欄は 時：分 形式のみの入力と仮定）

・・・という仮定のもとに、

例えばD列の集計は
　土曜以外　=SUMPRODUCT((TEXT($B3:$B14,"aaa")<>"土")*(D3:D14))
　土曜日　　=SUMPRODUCT((TEXT($B3:$B14,"aaa")="土")*(D3:D14))
　全体合計　=SUM(D3:D14)
で求められると思います。
（SUMPRODUCTで求めましたが、SUMIFでもできるのかも）

※ 日数は縮小サイズなので、計算範囲の指定は調整が必要です。
　（上記の例では3行目から14行目までの集計になっています）
※ 20日以前、以後を分割する場合は、A列の値を条件に加えれば可能です。
　（上の例示式の曜日判定の応用でできます）

この回答へのお礼

遅くなりました、すみません。

頂いた関数でできました！

書式の中の定義を変更し忘れていたのでご指摘の定義を使用したところちゃんと計算されました。

ありがとうございます。

お礼日時：2017/03/08 16:25