月の合計残業と平日の休みの個数

質問

セルC4:AF4に９月の曜日があり休日（土,日,祭）の全時間及びその他平日の８h以上の残業時間を合計しAG5:AG7に入力したい、またAH5:AH7に平日の休みの日数を入力したいのですがどなたか関数が解る方宜しくお願いします。
環境はExcel2013です。

回答No.1

kkkkkm · ベストアンサー

AG5に
=SUM(C5:AF5)-COUNT(C5:AF5)*8+COUNTIFS($C$4:$AF$4,"土",C5:AF5,">0")*8+COUNTIFS($C$4:$AF$4,"日",C5:AF5,">0")*8+COUNTIFS($C$4:$AF$4,"祭",C5:AF5,">0")*8
AH5に
=COUNTIFS($C$4:$AF$4,"<>土",$C$4:$AF$4,"<>日",$C$4:$AF$4,"<>祭",C5:AF5,"")

で下にコピーでいかがでしょう。

お礼

ありがとうございます。応用して何とか解決しました。

回答No.8

msMike

[No.7]に適用した別表の「祝日表」を添付図に示しておきます。

お礼

ありがとうございます。

回答No.7

msMike

添付図参照(小さくて判読不可、または、下記の説明が理解困難の場合は、潔く諦めませう、チャンチャン)
左から Sheet1、2、…、5 とします。

Sheet1 において、
１．文字列に書式設定したセル C2 に 2019/9 を入力し、範囲 C2:AG2 の
￣￣“セルを結合”
２．範囲 C3:D3 を d に書式設定
３．セル C3 に式 =(C2&"/1")*1 を入力
４．式 =C3+1 を入力したセル D3 を右方にズズーッと(AG列まで)オート
￣￣フィル
５．aaa に書式設定し、かつ、式 =C3 を入力したセル C4 を右方に
￣￣ズズーッと(AG列まで)オートフィル
６．範囲 C3: AG7 に下記の[条件付き書式]を設定
￣￣￣￣ルールの適用先: 条件1のみ =$C$3:$AG$4、他は =$C$3:$AG$7
￣￣￣￣ルールの種類: “数式を…を決定”
￣￣￣￣ルールの内容↓
￣￣￣￣￣条件1
￣￣￣￣￣￣数式→ =MONTH($C3)<>MONTH(C3) 書式→白のフォント色
￣￣￣￣￣条件2
￣￣￣￣￣￣数式→ =AND(MOD(C$3,7)=0,MONTH($C$3)=MONTH(C$3))
￣￣￣￣￣￣書式→ 青の塗りつぶし色
￣￣￣￣￣条件3
￣￣￣￣￣￣数式→ =AND(MOD(C$3,7)=1,MONTH($C$3)=MONTH(C$3))
￣￣￣￣￣￣書式→ 赤の塗りつぶし色
￣￣￣￣￣条件4
￣￣￣￣￣￣数式→ =AND(COUNTIF(Nholiday,C$3),MONTH($C$3)=MONTH(C$3))
￣￣￣￣￣￣書式→ オレンジの塗りつぶし色
￣￣￣￣【お断り】Nholiday は別表の祝日表に付けた名前

Sheet2 において、
７．0;;;
￣￣に書式設定し、かつ、次式を入力したセル C5 を右30列および下方
￣￣にオートフィル
￣￣=(MONTH(Sheet1!C$3)=MONTH(Sheet1!$C$3))*(MOD(Sheet1!C$3,7)>1)*(COUNTIF(Nholiday,Sheet1!C$3)=0)*(Sheet1!C5="")
￣￣式 =SUM(C5:AG5) を入力したセル AI5 を下方にオートフィル

Sheet3 において、
８．0;;;
￣￣に書式設定し、かつ、次式を入力したセル C5 を右30列および
￣￣下方にオートフィル
￣￣=(MONTH(Sheet1!C$3)=MONTH(Sheet1!$C$3))*(MOD(Sheet1!C$3,7)>1)*(COUNTIF(Nholiday,Sheet1!C$3)=0)*MAX(Sheet1!C5-8,0)
￣￣式 =SUM(C5:AG5) を入力したセル AH5 を下方にオートフィル

Sheet4 において、
９．0;;;
￣￣に書式設定し、かつ、次式を入力したセル C5 を右30列および
￣￣下方にオートフィル
￣￣=(MONTH(Sheet1!C$3)=MONTH(Sheet1!$C$3))*(MOD(Sheet1!C$3,7)<2)*(Sheet1!C5)
￣￣式 =SUM(C5:AG5) を入力したセル AH5 を下方にオートフィル

Sheet5 において、
10．0;;;
￣￣に書式設定し、かつ、次式を入力したセル C5 を右30列および
￣￣下方にオートフィル
￣￣=(MONTH(Sheet1!C$3)=MONTH(Sheet1!$C$3))*(COUNTIF(Nholiday,Sheet1!C$3))*(Sheet1!C5)
￣￣式 =SUM(C5:AG5) を入力したセル AH5 を下方にオートフィル

Sheet1 において、
11．セル AH5、AI5 にそれぞれ式
￣￣=Sheet3!AH5+Sheet4!AH5+Sheet5!AH5、=Sheet2!AI5
￣￣を入力した後で、範囲 AH5:AI5 を下方にオートフィル

お礼

ありがとうございます。勉強になりました。

回答No.6

imogasi

#4です。
最後の部分に追加。
＞平日の休みの日数
平日の休み（＝「計数」セルがブランクセルの数）は私の例では
=COUNTIFS(B3:Q3,"<>土",B3:Q3,"<>日",B3:Q3,"<>祭",B5:Q5,"")
といった形になります。
「曜日」行が土、日、祭のどれでもなくて、「計数」セル行が空白のものの件数。

お礼

ありがとうございます。

回答No.5

bunjii

質問の文言に不適切な用語が含まれていますが時間外勤務の合計時間数と平日に休んだ日数を個人別に集計したいのですよね？
AG5には次の数式を設定してください。
=SUMPRODUCT((C$4:AF$4={"月";"火";"水";"木";"金"})*(C5:AF5>8)*(C5:AF5-8))+SUMPRODUCT((C$4:AF$4={"土";"日";"祭"})*(C5:AF5))
AH5セルには次の数式を設定します。
=SUMPRODUCT((C$4:AF$4={"月";"火";"水";"木";"金"})*(C5:AF5=""))
AG5とAH5セルを下へ必要数コピーすれば目的通りになるでしょう。

お礼

ありがとうございます。

回答No.4

imogasi

課題の前半は、Googleで、「sumif or条件」で照会すればよいのだ。
http://excel-magic.com/post-1282/　など。
SUMIFSでOR条件を使う方法　（条件範囲の中で複数の条件を合計する）
ーー
小生の例データ
A-K列（下記では、Ｋ-Q列と２分割してそれぞれ掲示）
社員・日付 2019/9/1 2019/9/2 2019/9/3 2019/9/4 2019/9/5 2019/9/6 2019/9/7 2019/9/8 2019/9/9 2019/9/10
曜日 日 月 火 水 木 金 土 日 月 火

Aさん 9 10 8 8 8 8 7 5 8 8
ーー
K-Q列
2019/9/11 2019/9/12 2019/9/13 2019/9/14 2019/9/15 2019/9/16
水 木 金 土 日 祭

8 8 8 10
実際の日とその曜日の対応は間違っているので、お許しを（手抜き）。
式　B8セルに　（空白セルなら、どこでもよいが）
=SUM(SUMIFS($B$5:$Q$5,$B$3:$Q$3,{"土","日","祭"}))
ーー
結果
上の例では　３１
土日祭日列のセルを塗りつぶして、目視で合計と一致確認。
ーー
後半の課題は、ＣＯＵＮＴＩＦＳででるのではないか。略。

お礼

ありがとうございます。

回答No.3

Mathmi

考え方はNo1の方と被りますが、自分なら別に休日か否かを判別する行を作ります。
ここでは仮に土日祝の場合、その列の8行目に「休」と入力したものとします。

AG5セルに
=SUMPRODUCT(($C$8:$AF$8="休")*($C5:$AF5))+SUMPRODUCT(($C$8:$AF$8<>"休")*($C5:$AF5>=8)*($C5:$AF5))-COUNTIFS($C$8:$AF$8,"<>休",$C5:$AF5,">=8")*8
AH5セルに
=COUNTIFS($C$8:$AF$8,"<>休",$C5:$AF5,"")

これで、例えば平日に7時間出勤した場合、残業時間が-1時間ではなく0時間として集計されます。

お礼

ありがとうございます。

回答No.2

msMike

》 平日の８h以上の残業時間
ソレ、 Excel的に正しくない表現でしょ？！
平日の８hを超える分の残業時間とするのがホントじゃないッ！えッ？
チコちゃんに叱られまっせぇ～！

お礼

アドバイスありがとうございます。