エクセルで勤怠表土日祝と平日と分けてそれぞれの合計を出したい

解決済

質問者：huurenka
質問日時：2020/11/26 16:01
回答数：5件

色々と似たようなのを拝見しましたが、関数が詳しくないため、さっぱり分かりません。
どうか助けてください。
エクセルで勤怠表を作っており、営業さんが土日祝にも出勤がある場合があります。
やりたい事は
E37の平日の出勤日数
E38の休日の出勤日数（土日祝)この表では祝日は別シートで祝日表がありそこから引っ張ってます)
H37の平日の実働時間合計
H38の休日の実働時間合計
どうかよろしくお願いします。
関数ほぼ分からずなので、すみませんが分かりやすくお願い致します。

通報する

この質問への回答は締め切られました。

質問の本文を隠す

回答 (5件)

ベストアンサー優先
最新から表示
回答順に表示

No.4ベストアンサー

回答者： goomania
回答日時：2020/11/27 10:20

No.3です。

結果に影響はないのですが、数式を若干修正したいと思います。
前回の投稿の以下の①②です。

平日の出勤日数の数式
=SUMPRODUCT((E4:E34<>"")*((C4:C34="日")+(C4:C34="土")+($D$4:$D$34<>"")))･･････････････①

休日の実労働時間の数式
=SUMPRODUCT((E4:E34<>"")*((C4:C34="日")+(C4:C34="土")+($D$4:$D$34<>"")),$H$4:$H$34)･･･②

コピペして複数セルで使う式ではないので、間違いではないのですが、①②の数式の一部に相対番地表記になっている部分があります。
あくまでも、「出勤時間」「曜日」「祝日」のセルの内容が判断するという主旨からすると好ましくないと思いますので、以下のとおり絶対番地表記に修正してください。他の数式は絶対番地表記になっています。

①は
=SUMPRODUCT(($E$4:$E$34<>"")*(($C$4:$C$34="日")+($C$4:$C$34="土")+($D$4:$D$34<>"")))

②は
=SUMPRODUCT(($E$4:$E$34<>"")*(($C$4:$C$34="日")+($C$4:$C$34="土")+($D$4:$D$34<>"")),$H$4:$H$34)

添付画像も上記のように読み替え願います。

- 1
- 件

通報する

この回答へのお礼

絶対番地表記への訂正もありがとうございます。
相対参照・絶対参照と今は言わないんですか？

細かな所まで訂正していただき助かりました。
No.３での詳しく説明でベストアンサーと思いましたが、細かな訂正までしていただいたので、こちらにベストアンサーとさせていただきます。
本当にありがとうございました。

通報する

お礼日時：2020/11/27 16:18

No.5

回答者： mike_g
回答日時：2020/11/27 15:04

》やりたい事は

》 E37の平日の出勤日数
》 E38の休日の出勤日数
》 H37の平日の実働時間合計
》 H38の休日の実働時間合計

貴方の添附圖ではH37は既に記入濟みの「79:40」だけど、ＯＫですか？
間違へて居るのなら、訂正して下さい。
何れにしても、考へる數式の檢證の爲に、求める E37、38、H37、38 の正解値を先に教へて措いて下さい。

- 1
- 件

通報する

この回答へのお礼

記入して頂いている間に、他の方へのお礼を記入していて、解決済みにしてしまいました。申し訳ございません。

そして、mile_g様の書き込みがちょっと文字化けしていてちょっと分からない部分があります。
H37は記入済みで総合計になってます。
それをH37に平日計、H38に休日計にしたかったのです。

通報する

お礼日時：2020/11/27 16:25

No.3

回答者： goomania
回答日時：2020/11/27 01:10

angkor_hさんのご指摘のとおり、本来であれば、祝日ではなく休業日or休日として、法定休日以外の会社の休日に出勤した場合(例えば年末の12/31など)を休日出勤とするかどうかという問題もあるのではないかと思います。

しかし、投稿された表の情報だけで、ご質問者の課題を解決するとすれば、以下のようになるのではないかと思います。
まず、E37の平日の出勤日数は
出勤時刻が入力されていて、かつ土・日でなく、かつ祝日欄に何も表示されていない(空欄である)日を数えることにします。その数式は
=SUMPRODUCT(($E$4:$E$34<>"")*($C$4:$C$34<>"土")*($C$4:$C$34<>"日")*($D$4:$D$34=""))
となります。
次に、E38の休日の出勤日数は、
出勤時刻が入力されていて、かつ土・日であるか祝日欄に何かが表示されてる(空欄でない)日を数えることにします。その数式は
=SUMPRODUCT((E4:E34<>"")*((C4:C34="日")+(C4:C34="土")+($D$4:$D$34<>"")))
となります。
さらに、ご質問者は
H37に平日の実働時間合計、H38に休日の実働時間合計を表示したいとのことですが、H37には既に、実勤務時間総合計が表示されていますので、
平日の実働時間合計をG37、休日の実働時間合計をG38に表示することにします。
そこで、G37の平日の実働時間合計の数式は、
=SUMPRODUCT(($E$4:$E$34<>"")*($C$4:$C$34<>"土")*($C$4:$C$34<>"日")*($D$4:$D$34=""),$H$4:$H$34)
となります。
最後に、G38の休日の実働時間合計の数式は、
=SUMPRODUCT((E4:E34<>"")*((C4:C34="日")+(C4:C34="土")+($D$4:$D$34<>"")),$H$4:$H$34)
となります。
添付画像を参照してください。

- 2
- 件

通報する

この回答へのお礼

ご回答ありがとうございました。
説明もものすごく分かりやすくエクセルの表まで添付していただき、本当にありがとうございます。

SUMPRODUCTを検索してみたら、簡単な例から色々と出てきました。
単純な横計縦計の合計を出してくれるのですね。
今まで同じような計算は普通にSUMだけで縦横計を合わせてしてました。
このような復数計算をすると微妙に１，２程変わってくる場合が出るんですよね。
とても便利だと思いました。

が、しっかり把握し使いこなせるようになるまで、自分でしっかり勉強したいと思います。

代休などE列に「代休」とか書かれそうですが、そうすると合計されてしまうので、備考欄に書くように伝えます。

本当に関数等は奥が深い。ハマればものすごく楽しいんでしょうけれど、色々他にも勉強したいことが山程で…。
助かりました。
ありがとうございます。

通報する

お礼日時：2020/11/27 16:15

No.2

回答者： angkor_h
回答日時：2020/11/26 18:49

No.1です。

> 一応、曜日はTEXT(C4,"aaa")でやってます。
そういえば曜日列がありましたね。
なお、祝日列は「休日」にした方が良いのではないでしょうか。
或いは、「祝日名」とそれ以外は「休日」と記せば、
記入文字の有無で、区分け集計が簡単です(No.1の前方提案です)。

> 土日以外の祝日はSheet２に祝日一覧を作ってあります。
であれば、
休日集計は、「土」「日」「祝日一覧」のどれかに該当すること、
平日集計は、「土」「日」「祝日一覧」のどれにも該当しないこと、
で良いです。
使える関数は、SUMIFS、SUMPRODUCT　になります。
合計の対象に、複数の条件を指定できます。

> 平日に代休を取って休んだ場合とかはどうなるんだろう？？
休日出勤で平日に代休を取った、という事になると思います。
この場合、給料的には、次の処理で良いでしょう。
・　休日出勤に対して、平日給+休日割り増しで処理する。
　代休出勤は0時間とする。但し、欠勤ではない。
法的には、代休処理した場合は、休日出勤扱いが消えます。
この件は、会社(上司)に確認してください。

なお、代休か有給休暇かの判断列が必要になってきます。
代休処理や有給休暇取得管理、給与計算に影響するからです。

- 1
- 件

通報する

この回答へのお礼

使える関数を教えてくださりありがとうございます。
やりたい事があっても、使える関数が何なのか分からない為
とても助かりました。勉強していきます。
代休の件もご丁寧にお教えくださり、有難うございます。

通報する

お礼日時：2020/11/27 15:10

No.1

回答者： angkor_h
回答日時：2020/11/26 17:09

一番簡単なのは、出勤日/休日を示す列を新たに作り、

それを参照して合計する方法です。
但し、その判断の間違いをなくすには、
次(それ以外では、に示す)2点の方法により、
作成したほうが安全です。

それ以外では、
・　日付から土日を判断する　…関数があります。
・　土日以外の祝日を判断する　…祝日一覧表が必要
これを利用して、その条件に従い合計する方法があります。

祝日は年毎に違い、また会社固有の休日もあるので、
祝日一覧表は必要になります。

ご参考、
https://tonari-it.com/excel-workday/

> さっぱり分かりません。
お勉強しなければ、先には進めません…

- 2
- 件

通報する

この回答へのお礼

ご回答ありがとうございます。
一応、曜日はTEXT(C4,"aaa")でやってます。
土日以外の祝日はSheet２に祝日一覧を作ってあります。

今まではE37にCOUNTで時間が入る＝出勤日数って事で表示していましたが、土日祝と平日と分けてくれと言われてしまいました。

新たに列を作って平日なら出勤日・土日祝なら休日と表示しそれを元に日数を合計する？でいいんですかね？
平日に代休を取って休んだ場合とかはどうなるんだろう？？

さっぱり分からないのも、発想が浮かばないので(知らないからですが(汗))どの関数を勉強すればいいのかすら分からないです(泣)

ご参考URLを勉強してみます。
ありがとうございました。

通報する

お礼日時：2020/11/26 18:00

お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう！