No.4ベストアンサー
- 回答日時:
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)
添付画像も上記のように読み替え願います。
絶対番地表記への訂正もありがとうございます。
相対参照・絶対参照と今は言わないんですか?
細かな所まで訂正していただき助かりました。
No.3での詳しく説明でベストアンサーと思いましたが、細かな訂正までしていただいたので、こちらにベストアンサーとさせていただきます。
本当にありがとうございました。
No.5
- 回答日時:
》 やりたい事は
》 E37の平日の出勤日数
》 E38の休日の出勤日数
》 H37の平日の実働時間合計
》 H38の休日の実働時間合計
貴方の添附圖ではH37は既に記入濟みの「79:40」だけど、OKですか?
間違へて居るのなら、訂正して下さい。
何れにしても、考へる數式の檢證の爲に、求める E37、38、H37、38 の正解値を先に教へて措いて下さい。
記入して頂いている間に、他の方へのお礼を記入していて、解決済みにしてしまいました。申し訳ございません。
そして、mile_g様の書き込みがちょっと文字化けしていてちょっと分からない部分があります。
H37は記入済みで総合計になってます。
それをH37に平日計、H38に休日計にしたかったのです。
No.3
- 回答日時:
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)
となります。
添付画像を参照してください。
ご回答ありがとうございました。
説明もものすごく分かりやすくエクセルの表まで添付していただき、本当にありがとうございます。
SUMPRODUCTを検索してみたら、簡単な例から色々と出てきました。
単純な横計縦計の合計を出してくれるのですね。
今まで同じような計算は普通にSUMだけで縦横計を合わせてしてました。
このような復数計算をすると微妙に1,2程変わってくる場合が出るんですよね。
とても便利だと思いました。
が、しっかり把握し使いこなせるようになるまで、自分でしっかり勉強したいと思います。
代休などE列に「代休」とか書かれそうですが、そうすると合計されてしまうので、備考欄に書くように伝えます。
本当に関数等は奥が深い。ハマればものすごく楽しいんでしょうけれど、色々他にも勉強したいことが山程で…。
助かりました。
ありがとうございます。
No.2
- 回答日時:
No.1です。
> 一応、曜日はTEXT(C4,"aaa")でやってます。
そういえば曜日列がありましたね。
なお、祝日列は「休日」にした方が良いのではないでしょうか。
或いは、「祝日名」とそれ以外は「休日」と記せば、
記入文字の有無で、区分け集計が簡単です(No.1の前方提案です)。
> 土日以外の祝日はSheet2に祝日一覧を作ってあります。
であれば、
休日集計は、「土」「日」「祝日一覧」のどれかに該当すること、
平日集計は、「土」「日」「祝日一覧」のどれにも該当しないこと、
で良いです。
使える関数は、SUMIFS、SUMPRODUCT になります。
合計の対象に、複数の条件を指定できます。
> 平日に代休を取って休んだ場合とかはどうなるんだろう??
休日出勤で平日に代休を取った、という事になると思います。
この場合、給料的には、次の処理で良いでしょう。
・ 休日出勤に対して、平日給+休日割り増しで処理する。
代休出勤は0時間とする。但し、欠勤ではない。
法的には、代休処理した場合は、休日出勤扱いが消えます。
この件は、会社(上司)に確認してください。
なお、代休か有給休暇かの判断列が必要になってきます。
代休処理や有給休暇取得管理、給与計算に影響するからです。
使える関数を教えてくださりありがとうございます。
やりたい事があっても、使える関数が何なのか分からない為
とても助かりました。勉強していきます。
代休の件もご丁寧にお教えくださり、有難うございます。
No.1
- 回答日時:
一番簡単なのは、出勤日/休日を示す列を新たに作り、
それを参照して合計する方法です。
但し、その判断の間違いをなくすには、
次(それ以外では、に示す)2点の方法により、
作成したほうが安全です。
それ以外では、
・ 日付から土日を判断する …関数があります。
・ 土日以外の祝日を判断する …祝日一覧表が必要
これを利用して、その条件に従い合計する方法があります。
祝日は年毎に違い、また会社固有の休日もあるので、
祝日一覧表は必要になります。
ご参考、
https://tonari-it.com/excel-workday/
> さっぱり分かりません。
お勉強しなければ、先には進めません…
ご回答ありがとうございます。
一応、曜日はTEXT(C4,"aaa")でやってます。
土日以外の祝日はSheet2に祝日一覧を作ってあります。
今まではE37にCOUNTで時間が入る=出勤日数って事で表示していましたが、土日祝と平日と分けてくれと言われてしまいました。
新たに列を作って平日なら出勤日・土日祝なら休日と表示しそれを元に日数を合計する?でいいんですかね?
平日に代休を取って休んだ場合とかはどうなるんだろう??
さっぱり分からないのも、発想が浮かばないので(知らないからですが(汗))どの関数を勉強すればいいのかすら分からないです(泣)
ご参考URLを勉強してみます。
ありがとうございました。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- 求人情報・採用情報 月単位、週40時間の変形労働時間制で、 日曜祝日土曜半日の固定休+シフト制(平日一日)の企業の場合、 1 2023/08/10 23:43
- Excel(エクセル) 年間仕事用シフトカレンダーに、日勤 夜勤 休み に色分けをした。 4 2022/12/09 04:12
- 転職 転職活動中で2社内定があり迷っています。皆様ならAとBどちらの方が良いと思いますか?理由もお願いしま 1 2023/02/06 12:20
- その他(Microsoft Office) 従業員増減対応で当番種類の増減対応な当番表 21 2022/07/19 07:30
- 会社・職場 有給休暇について教えてください。 雇用から6ヶ月継続勤務している労働者で、かつ全労働日の8割以上出勤 4 2022/07/08 06:58
- 求人情報・採用情報 気になる求人があったのですが、、、 年間休日:105日 休日:日、祝日 土曜日の出勤が月に2~3日 1 2022/12/03 10:55
- 求人情報・採用情報 年間休日について 転職活動中です 質問内容 求人内容の中に年間休日数がしっかり明記してある、してない 1 2023/01/21 08:01
- Excel(エクセル) セルに特定の色が出た時だけ、式を発動させたい 4 2022/06/17 10:32
- 中途・キャリア 転職するか悩んでます。皆様ならどちらを選びますか? A社→基本給18万+固定残業代5万円 営業職 残 3 2022/04/03 18:21
- ヒーター・こたつ・ホットカーペット 教えて!gooに10分おきに質問をしていれば給料がもらえる仕事があれば、やりたいですか? 毎週金土日 4 2023/07/04 21:17
このQ&Aを見た人はこんなQ&Aも見ています
-
プロが教えるわが家の防犯対策術!
ホームセキュリティのプロが、家庭の防犯対策を真剣に考える 2組のご夫婦へ実際の防犯対策術をご紹介!どうすれば家と家族を守れるのかを教えます!
-
エクセル 平日と土日祝の時間計算表示
その他(OS)
-
エクセルで勤務時間を出したい。 土日、祝日と18時以降、朝9時までを取り除きたい
Excel(エクセル)
-
エクセルで表示形式の時刻の「0:00」を表示しないようにするには?
Excel(エクセル)
-
-
4
土日祝を空白にする関数を教えてください
Excel(エクセル)
-
5
【Excel】での計算式教えて下さい。土日祝は時給が30円アップするので画像を例にしますと、9時間×
Excel(エクセル)
-
6
Excel関数で休日出勤日数を計算
Excel(エクセル)
-
7
EXCELで、平日は平日、休日は休日で集計したい
Excel(エクセル)
-
8
Excelにて残業時間、休日出勤時間の求め方
Excel(エクセル)
-
9
EXCELで休日出勤を計算する
Excel(エクセル)
-
10
月間シフト表中で、祝日の出勤数をSUMIF関数を使ったが計算ができない
その他(Microsoft Office)
-
11
Excelにて勤務表の作成で早出・残業、所定労働時間の計算式を教えて下さい
Excel(エクセル)
-
12
エクセルで曜日によって表示と非表示をわけたい
その他(OS)
-
13
28日~31日が必要ない所を空白表示にする方法
Excel(エクセル)
-
14
平日と土日祝を区別して売上実績を出したい。
その他(Microsoft Office)
-
15
エクセルで勤怠管理表で休憩時間を計算させるには
Excel(エクセル)
-
16
エクセルで、時間の計算をした場合、マイナス表示を0:00にする方法を教えてください。
Excel(エクセル)
-
17
エクセルの時間外勤務時間と深夜時間を自動割り振りできる計算式を教えてください
Excel(エクセル)
-
18
Excelによる土日と祝祭日の算出関数
Excel(エクセル)
-
19
IF関数で、時間を条件にしたい場合の式について
Access(アクセス)
-
20
エクセルで「500時間」を「何日と何時間」で表示したい
その他(パソコン・スマホ・電化製品)
関連するカテゴリからQ&Aを探す
おすすめ情報
このQ&Aを見た人がよく見るQ&A
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
マクロの有効化するダイヤログ...
-
文字列になっている時間をVBAで...
-
excel で二つのどちらかを選ぶ
-
【マクロ】フォルダからエクセ...
-
Excelでの時間帯の入力
-
Excel 2019 のピボットテーブル...
-
VBA Private Sub Worksheet_Cha...
-
行数が不規則な一週間ごとの合...
-
エクセルで特定の範囲内から小...
-
エクセル 価格表から単価を呼び...
-
シートAで横に並んだ項目→シー...
-
ある列、或いは、ある行のセル...
-
【関数】スペースがいくつ入っ...
-
エクセルで複数のシートにまた...
-
Excelが固まってしまった。
-
Excelはなんで先頭の0を消すん...
-
西暦や和暦の表示をyyyymmdd表...
-
excelの不要な行の削除ができな...
-
エクセルの順位別一覧表の自動...
-
Excel2013のF6キー操作について
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
Excel 2019 のピボットテーブル...
-
[関数得意な方]教えて下さい・...
-
Excelにてある膨大なデータを管...
-
[関数について]わかる方教えて...
-
Excel初心者です。 詳しい方、...
-
excelの不要な行の削除ができな...
-
エクセル関数に詳しい方教えて...
-
INDIRECTを使わず excelで複数...
-
[オートフィルタ]で抽出された...
-
エクセルの神よ、ご回答を! エ...
-
エクセル関数に詳しい方、教え...
-
各ページの1番上の表示について
-
Excelで写真のような表を作った...
-
エクセルで不等号記号(≠)が上に...
-
数学 Tan(θ)-1/Cos(θ)について...
-
Excel 2019 は、SPILL機能があ...
-
Excelで全角を半角にしたいので...
-
条件付き書式を教えてください
-
Excel フィルターを掛けた状態...
-
[オートフィルタ]の適用範囲の...
おすすめ情報