Excel2013で出勤簿・賃金計算書の作成

質問

Windows 8.1　Excel 2013で各月の出勤簿から賃金計算をしておりました。
これまでごOKWAVEで指導を頂きながら順調に出来ましたが、問題が発生致しました。

ここからが質問ですが、ご指導をお願い致します。

只今、作業中のExcelの表を掲載し説明を受けるのが筋ですが、Excelの表の掲載方法が判らないため長文になりますが文書にて質問させて頂きます。

Excelの表の各列・各行・セルの状況は下記の通りで、計算式(関数)が入っています。

７行目は項目が入っており、
A列は月日・B列は曜日・C列は始業時刻・D列は終業時刻・E列は休憩時間・F列は勤務時間
・G列は時間内勤務時間・H列は時間外勤務時間・I列は時間内支給額・J列は時間外支給額となっています。

８行目から5月１日～３８行目が５月３１日となっています。
B列の曜日には、関数(=TEXT(A8,"aaa")(以下関数を示します)が入っており、A列に2016/5/1と入力すれば、自動的にB列に日曜日と表示されます。
C列・D列・E列の各セルの勤務時間は、手動で入力します。
F列の勤務時間には、5:00と入力し、=IF(COUNT(C9:D9)=2,D9-C9-E9,0)
G列の時間内勤務時間には、12:00と入力し、=IF(B9="日",0,MIN($F$45,F9))保護あり
H列の時間外勤務時間には、=F9-G9保護あり
I列の時間内支給額には、=G9*24*$E$42保護あり
J列の時間外支給額には、=IF(B9="日",H9*24*$E$43,H9*24*$E$44) 保護あり

A列・B列は入力後、保護する。
C列・D列・E列は保護せず、手入力のみです。
それ以外は、関数が入っているので保護あり

４０行以降は、セルごとに関数が入っています。

C41セルに全勤務労働時間として、=SUM($F$8:$F$38)
F41セルに全勤務労働時間として、=SUM($F$8:$F$38)

C42セルに定時労働時間として、=SUM($G$8:$G$38)
G４２セルに定時労働時間として、=SUM($G$8:$G$38)
I４２セルに時間内支給額として、=SUM($I$8:$I$38)
C43セルに休日労働時間として、
H４３セルに時間外勤務時間として、=$C$43
J43セルに時間外支給額として、=SUMIF($B$8:$B$38,$D$43,$J$8:$J$38)
C４４セルに早出労働時間として、=SUM($H$8:$H$38)-$C$43
H４４セルに時間外勤務時間として、=SUM($H$8:$H$38)-H43
J44セルに時間外支給額として、=SUM($J$8:$J$38)-$J$43
C45セルに勤労日数として、=COUNT($C$8:$C$38)
F45セルに契約時間として、固定で08:00
J45セルに定時労働時間支給額として、=I42
J４６セルに休日労働時間支給額として、=J43
J４７セルに早出労働時間支給額として、=J44
J４８セルに総支給額合計として、=SUM(J45:J47)
D4３セルに休日労働時間検索のための、日

時給関係は、
　E４２セルに定時労働時間時給として、769円(変更があるので、保護せず)
　E４３セルに休日労働時間時給として、1,039円(変更があるので、保護せず)
　E４４セルに早出労働時間時給として、962円(変更があるので、保護せず)

現在、入力されている関数等は上記の通りです。

参考事項ですが、
日曜日以外は、平日の時給と同額で自動的に計算するように公式を入れていましたが、この度、祝日も日曜日と同額で計算するように関数を入れたいのですが、良い方法があればご指導をお願い致します。

勝手を申しますが、よろしくお願い致します。

説明不足で理解し難いかもしれませんが、よろしくお願い致します。

長文になりましたがご容赦下さい。

よろしくお願い致します。

回答No.9

bunjii · ベストアンサー

>G8:G38には計算式が既に入力されており、保護ありで手を加えることが出来ません。
保護を解除して数式を修正してください。
それができないと問題が解決しません。
但し、新たにExcelブックを作成するのであれば解決できるでしょう。
しかし、あなたの応用力では新たに作成することは困難でしょう。

>B9セルが"日"または"祝"の時にG9（時間内）が0時間になるようです。（目的通りです）
>この意味は、祝日の一覧表が作成出来ないと現段階では見れないのでしょうか。
「見れない」ではなく「解決しない」です。
祝日は毎年同じ月日のものと年によって変化するものがありますので、対象となる年の一覧表が必要です。

>ダウンロード先を、表の構成の関係で”L8”としました。
提示のサイトから直接L8セル以下へダウンロードできませんので、ファイルをダウンロードして目的のセル範囲をコピペで値のみをL8以下へ貼り付けます。
各祝日が連続していなくてもB列へ設定する数式で"祝"を代入できます。

>「月間スケジュール表」が表示され、シートが「予定表」と「祝日表」と「sheet1」があります。
>ここから先の操作が判りませんので、よろしくお願い致します。
祝日表のB3:B82をコピーコマンドでクリップボードに記憶させ、値の貼り付けでL8へ貼り付けコマンドを実行してください。
結果が数値（5ケタの整数）のときは表示形式を日付にすると分かり易いでしょう。
あなたの知識では問題解決に長期間が必要になるでしょう。

お礼

この度の質問に関しては何回もご指導を頂き、本当にありがとうございました。
私の知識では問題解決に長期間が必要とのこと、ここまでやってきたことを再度見て、時間はかかっても完成させたいと思います。
今回の記載されていることをよく何回も読み返し、色々チャレンジしてみます。
Excel・word関係等幅広くご指導をされておられますが、知識・能力等の違いで大変でしょうが、今後益々のご活躍をお祈り致します。
猛暑が続きますが、ご自愛ください。

回答No.8

bunjii

>・「G列の時間内勤務時間には、12:00と入力し、」とは何のことですか？
>G列の時間内勤務時間は、F45セルに契約時間の8:00が入力されており、契約時間が8:00以上の場合にそのセルの時間を見て、休憩時間の1:00を入力するためです。
補足になっていません。
G8:G38には時間内の計算式が既に入力されていると言っていますので12:00と入力すると数式が削除されてしまいます。G列の何行目に12:00と入力するかを提示しないと補足になりません。

>・G9=IF(OR(B9="日",B9="祝"),0,MIN($F$45,F9)) に直せば良いと思います。
>私の解釈が違っていてはいけませんので説明します。
>注：日曜日と祝日の勤務の場合、全てが時間外になります。
質問の中に「G列の時間内勤務時間には、12:00と入力し、=IF(B9="日",0,MIN($F$45,F9))保護あり」とありますので、この数式を修正しただけですよ。
B9セルが"日"または"祝"の時にG9（時間内）が0時間になるようです。（目的通りです）

>・C43=SUMIF(B8:B38,"日",H8:H38)+SUMIF(B8:B38,"祝",H8:H38)　は
>　　　　　↓
>C43=SUMIF($B$8:$B$38,"日",$H$8:$H$38)+SUMIF($B$8:$B$38,"祝",$H$8:$H$38)　と絶対参照は不要でしょうか。
C43セルを他のセルへコピペしなければ絶対番地にする必要がありません。（絶対番地にしてはいけない訳ではありませんので好きなようにしてください）

>・祝日の時は曜日の代わりに"祝"にする方法は次の数式で良いでしょうか。
数式が何処にも提示されていませんので、答えようがありません。

>L列に祝日の一覧表をダウンロード(シート名: 2016月間スケジュール表)したのですが、”#NUM”エラー表示されたので別シートにダウンローとしました。
新たな条件を提示されても説明不足で問題点が見つけられません。
"#NUM"エラーになる数式を提示してください。
「L列に祝日の一覧表をダウンロード」と言う操作がどのようにされたかが説明不足になっていると思います。

>・これ以降はどのようにすればよろしいのでしょうか。
理解できないことをなるべく短い文章で補足してください。

補足

何回もご指導及び補足説明になってない指摘を頂き、恥ずかしいのと恐縮をしております。
完成するまでのご協力をよろしくお願い致します。

補足させて頂きます。

G列の時間内勤務時間は、F45セルに契約時間の8:00が入力されているが(自動で休憩時間が表示される訳でない)、契約時間が8:00以上の場合にそのセルの時間を見て、休憩時間の1:00を入力するためです。
C列・D列・E列は手入力とし、勤務状況を見て8:00以上でなくても休憩時間として1:00を入力することがあります。

申し訳ありません。私の間違いです。
G8:G38には計算式が既に入力されており、保護ありで手を加えることが出来ません。

B9セルが"日"または"祝"の時にG9（時間内）が0時間になるようです。（目的通りです）
この意味は、祝日の一覧表が作成出来ないと現段階では見れないのでしょうか。

祝日の時は曜日の代わりに"祝"にする方法は、数式が何処にも提示されていない(祝日の一覧表が作成出来ない)ことでしょうか。

ダウンロードの方法は
ダウンロード先を、表の構成の関係で”L8”としました。
http://www.tschoolbank.com/excel/schedule-holiday/　で開き
10．「月間予定表テンプレートのダウンロード」　をクリック
「ファイルのダウンロード」　の中の「月間スケジュール表」　をクリック
「名前を付けて保存」　をクリック
「保存場所」　をデスクトップにあるファイル名
(今回は「OKwaveの回答に従い作業用(シート5月分使用」)を指定し、クリック
「月間スケジュール表」が表示され、
　シートが「予定表」と「祝日表」と「sheet1」があります。

ここから先の操作が判りませんので、よろしくお願い致します。
シート「祝日表」には、年・日付・曜日・祝日名があります。

回答No.7

kkkkkm

No4に追加です。

> D4３セルに休日労働時間検索のための、日

というのもあったんですね。
関連してそうなところが
> J43セルに時間外支給額として、=SUMIF($B$8:$B$38,$D$43,$J$8:$J$38)
これはわかるとしても
> J44セルに時間外支給額として、=SUM($J$8:$J$38)-$J$43
これがわからないので
たとえばD44に祝日と記載しておいて
J43に
=SUMIF($B$8:$B$38,$D$43,$J$8:$J$38)+SUMIF($B$8:$B$38,$D$44,$J$8:$J$38)
だけでいいのかどうかいまひとつわかりません。

複雑な表の全体を文章で記載しても把握し辛いので、複雑な場合はファイル自体を
https://bitsend.jp/
ここなどにアップしておいてURLを質問内容とともに記載しておくという手もあります。
マクロが入っているファイルだと開いてくれるのは難しいかもしれませんが、そうでない場合は興味があれば開いてみてくれると思いますよ。

お礼

再度、追加のご指導ありがとうございます。
https://bitsend.jp/を拝見しましたが、最初から勉強する必要があり大変そうです。
これまで頂いたものにより完成に向けて頑張ろうと思いますので、本当にありがとうございました。
今後益々のご活躍をお祈り致します。

回答No.6

bunjii

回答No.5の数式にタイプミスがありましたので訂正します。
J9=IF(OR(B9="日"B9="祝"),H9*24*$E$43,H9*24*$E$44)
　　　　　　　↓
J9=IF(OR(B9="日",B9="祝"),H9*24*$E$43,H9*24*$E$44)
IF関数の論理式でOR関数を使っていますが、OR関数の引数の区切りであるカンマが抜けていました。

お礼

今日は。

昨日は前回に引き続き目に止めて頂き、ありがとうございました。
Excelが添付出来ないので長文文書にて説明させい頂きました。

又、今朝３時過ぎまでご苦労を頂き、本当にありがとうござしました。

OKwaveの中で、Excel関係でbunjii 様の質問に対するご指導を多々拝見し、素晴らしいなぁ思いました。

今回のご指導も説明を読めば、時間がかかっても完成出来ると思います。

再度疑問の箇所がありましたら、又質問させて頂きます。

少し時間を要しますが、完成しましたら再度お礼を申し上げます。
この度の件に関しましては本当にありがとうございました。

補足

おはようございます。
引き続きお世話になります。

質問に対する回答です。

・「G列の時間内勤務時間には、12:00と入力し、」とは何のことですか？
G列の時間内勤務時間は、F45セルに契約時間の8:00が入力されており、契約時間が8:00以上の場合にそのセルの時間を見て、休憩時間の1:00を入力するためです。

・G9=IF(B9="日",0,MIN($F$45,F9)) ということですよね？
・G9=IF(OR(B9="日",B9="祝"),0,MIN($F$45,F9)) に直せば良いと思います。
私の解釈が違っていてはいけませんので説明します。
注：日曜日と祝日の勤務の場合、全てが時間外になります。

・C43セルには日曜、祝日のみの労働時間数を集計すれば良いのでしょうか？
その通りです。

質問ですが

・C43=SUMIF(B8:B38,"日",H8:H38)+SUMIF(B8:B38,"祝",H8:H38)　は
　　　　　↓
C43=SUMIF($B$8:$B$38,"日",$H$8:$H$38)+SUMIF($B$8:$B$38,"祝",$H$8:$H$
38)　と絶対参照は不要でしょうか。

・祝日の時は曜日の代わりに"祝"にする方法は次の数式で良いでしょうか。
L列に祝日の一覧表をダウンロード(シート名: 2016月間スケジュール表)したのですが、
”#NUM”エラー表示されたので別シートにダウンローとしました。

・ダウンロードしたら予定表・祝日表の２シートがあり、祝日表の中の祝日表(2016年)の必要な範囲のみを別シート(シート名: 2016月間スケジュール表)として保存してあります。

・これ以降はどのようにすればよろしいのでしょうか。

引き続き、ダウンロード以降の作業を勉強しますが、よろしくお願い致します。

取り急ぎ。

回答No.5

bunjii

前提条件が長すぎて何を知りたいのか読み取れません。

>B列の曜日には、関数(=TEXT(A8,"aaa")(以下関数を示します)が入っており、A列に2016/5/1と入力すれば、自動的にB列に日曜日と表示されます。
祝日の時は曜日の代わりに"祝"にする方法は次の数式で良いでしょう。
B8=IF(COUNTIF(L:L,A8),"祝",TEXT(A8,"aaa"))
但し、L列に祝日の一覧が入力されているものとします。

>G列の時間内勤務時間には、12:00と入力し、=IF(B9="日",0,MIN($F$45,F9))保護あり
「G列の時間内勤務時間には、12:00と入力し、」とは何のことですか？
「G列は時間内勤務時間」と言っていますので「12:00と入力」しないでしょう。
G9=IF(B9="日",0,MIN($F$45,F9)) ということですよね？
G9=IF(OR(B9="日",B9="祝"),0,MIN($F$45,F9)) に直せば良いと思います。

>J列の時間外支給額には、=IF(B9="日",H9*24*$E$43,H9*24*$E$44) 保護あり
J9=IF(B9="日",H9*24*$E$43,H9*24*$E$44)
　　　　　↓
J9=IF(OR(B9="日"B9="祝"),H9*24*$E$43,H9*24*$E$44)

>C43セルに休日労働時間として、
C43セルには日曜、祝日のみの労働時間数を集計すれば良いのでしょうか？
C43=SUMIF(B8:B38,"日",H8:H38)+SUMIF(B8:B38,"祝",H8:H38)

他の数式は今回の質問と関係がないようなので割愛させて頂きます。
あなたの思考回路が複雑すぎるだけで、簡単に解決できる内容です。
もし、他の部分で分からないことがありましたら要点を整理して疑問点を提示してください。

補足

おはようございます。
引き続きお世話になります。

質問に対する回答です。

・「G列の時間内勤務時間には、12:00と入力し、」とは何のことですか？
G列の時間内勤務時間は、F45セルに契約時間の8:00が入力されており、契約時間が8:00以上の場合にそのセルの時間を見て、休憩時間の1:00を入力するためです。

・G9=IF(B9="日",0,MIN($F$45,F9)) ということですよね？
・G9=IF(OR(B9="日",B9="祝"),0,MIN($F$45,F9)) に直せば良いと思います。
私の解釈が違っていてはいけませんので説明します。
注：日曜日と祝日の勤務の場合、全てが時間外になります。

・C43セルには日曜、祝日のみの労働時間数を集計すれば良いのでしょうか？
その通りです。

質問ですが

・C43=SUMIF(B8:B38,"日",H8:H38)+SUMIF(B8:B38,"祝",H8:H38)　は
　　　　　↓
C43=SUMIF($B$8:$B$38,"日",$H$8:$H$38)+SUMIF($B$8:$B$38,"祝",$H$8:$H$
38)　と絶対参照は不要でしょうか。

・祝日の時は曜日の代わりに"祝"にする方法は次の数式で良いでしょうか。
L列に祝日の一覧表をダウンロード(シート名: 2016月間スケジュール表)したのですが、
”#NUM”エラー表示されたので別シートにダウンローとしました。

・ダウンロードしたら予定表・祝日表の２シートがあり、祝日表の中の祝日表(2016年)の必要な範囲のみを別シート(シート名: 2016月間スケジュール表)として保存してあります。

・これ以降はどのようにすればよろしいのでしょうか。

引き続き、ダウンロード以降の作業を勉強しますが、よろしくお願い致します。

取り急ぎ。

回答No.4

kkkkkm

No2の訂正

あえて訂正しなくても分かるとは思いますが
=IFERROR(VLOOKUP(A8,Sheet2!B2:F16,6,FALSE),TEXT(A8,"aaa"))
↓
=IFERROR(VLOOKUP(A8,Sheet2!$B$2:$F$16,6,FALSE),TEXT(A8,"aaa"))

また、A8とかは適宜適切に変更していただけると思いますし、$B$2:$F$16も同様に適宜変更していただけると思いますので細かい説明はしてません。

で、正直あまりにも説明が長く全て把握する気力がありません。多分そこだけでいけるだろうと思って回答しています。

お礼

今晩は。
昨日もご指導を頂きありがとうございました。
びっくり～～しました。
本日の私の長文の説明文書に対しても、ご指導を頂き本当に嬉しく思います。
本日は全ての作業は出来ませんが、明日以降時間をかけて少しずつ目的に向かって実施していきます。
明日中には完成させたいと考えております。
完成をしましたら報告させて頂きます。
本当にありがとうございました。
お休みなさい。

回答No.3

msMike

》 B列の曜日には、関数(=TEXT(A8,"aaa")
その「関数」は「B列」8行目の式ですよね？

》 F列の勤務時間には、5:00と入力し、=IF(COUNT(C9:D9)=2,D9-C9-E9,0)
「5:00」は「F列の」8～38行目に入力？
その式はどの列の9行目に入力？なぜ先ほどと異なる行なのですか？

余りに長～い長～い寿限無説明文なので読み終わるのに疲れて数日掛りそう、取り敢えず此処までの確認をしておこうかと。(^_^)

お礼

ご指導ありがとうございました。
申し訳ございませんが、他に２件のご指導があり順次確認をしながら、完成に向けて行きたいと思います。
完成しましたら報告させて頂きます。
もし、未完成の場合は私の力不足と思ってお許しください。
今後益々のご活躍をお祈り致します。

回答No.2

kkkkkm

No1の一部訂正です。

F列ではなくG列に祭日入力で、式は
=IFERROR(VLOOKUP(A8,Sheet2!B2:F16,6,FALSE),TEXT(A8,"aaa"))
なお、紹介したページでは年ごとに下に表を追加していますが、追加せずに一年分でA2の年の数値を毎年変更するだけで対応してください。

お礼

再度、ご指導ありがとうございました。
時間がかかりますが、URLを開き該当するものを利用し完成させたいと思っております。
本当にありがとうございました。
今後、益々のご活躍をお祈り致します。

回答No.1

kkkkkm

先日回答した
祝祭日を計算する
http://www.geocities.jp/chiquilin_site/data/080307_holiday.html
こちらの方法で祭日の表を作り
上記のページと同じ列で作成したとしたらその表のF列の2行目から最終行まで祭日と入力しておき
曜日の関数(=TEXT(A8,"aaa")が入っているセル全てに
祭日の表がSheet2に上記のページと同じように作成されているとしたら
=IFERROR(VLOOKUP(A8,Sheet2!B2:F16,5,FALSE),TEXT(A8,"aaa"))
と入力すれば祭日の時は祭日、それ以外は曜日が表示されます。
あとは、祝日も日曜日と同額で計算するようにですので
=IF(B9="日",以下略)
の部分を
=IF(OR(B9="日",B9="祝日"),以下略 )
曜日の部分が日か祝日なら以下略を実行ということになるので、日でも祝日でも同じ計算になります。
このように変更してやればいかがでしょう。