エクセルで時給計算をしたいと考えているのですが、関数が複雑でよくわかりません。
時間帯によって時給が違います。詳しい方教えてください。
宜しくお願いします。
時給単価A 7:00~9:00 or 17:00~20:00 1000円/h
時給単価B 9:00~17:00 850円/h
例)7:30~18:00まで勤務した場合、
時給単価A 2.5h
時給単価B 8h
A(出勤時間) B (退勤時間) C (1000円/hの時間数)D( 850円/hの時間数)E(合計勤務時間数)
A1(7:30) B1(18:00) C1(2.5) D1 (8) E1(10.5)
C1とD1に入る式をおしえて頂けますでしょうか?
7:00より前、20:00以降の勤務はありません。
宜しくお願いします。
No.3ベストアンサー
- 回答日時:
こんにちは
時間帯を1000円と850円で区別するよりも、朝(C)、昼(D)、夜(E)、と分けるとわかりやすくなると思います。
「朝」は、
出勤時刻が9:00過ぎなら何もしない。
9:00前なら「9:00」と「退勤時刻」を比較して小さい方から、「7:00」と「出勤時刻」を比較して大きい方を差し引く。
=IF(A1>="9:00"*1,"",MIN("9:00",B1)-MAX("7:00",A1))
このままでは、「出勤時刻」と「退勤時刻」が空白でも計算してしまうので、IF(COUNT(A1:B1)<2,"", )を追加。(この部分は昼と夜にも同様に追加)
結果
=IF(COUNT(A1:B1)<2,"",IF(A1>="9:00"*1,"",MIN("9:00",B1)-MAX("7:00",A1)))
「昼」は、
出勤時刻が17:00過ぎ、または退勤時刻が9:00前なら何もしない。
上記以外なら、「17:00」と「退勤時刻」を比較して小さい方から、「開始時刻」と「9:00」を比較して大きい方を差し引く。
=IF(COUNT(A1:B1)<2,"",IF(OR(A1>="17:00"*1,B1<="9:00"*1),"",MIN("17:00",B1)-MAX("9:00",A1)))
「夜」は、
退勤時刻が17:00前なら何もしない。
17:00過ぎなら「20:00」と「退勤時刻」を比較して小さい方から、「17:00」と「出勤時刻」を比較して大きい方を差し引く。
=IF(COUNT(A1:B1)<2,"",IF(B1<="17:00"*1,"",MIN("20:00",B1)-MAX("17:00",A1)))
後は、朝と夜を足して時給1000円の合計を求める。
一応、入力時刻が時間外(6:45等)になっても対応しておりますが、丸め処理(10分未満切り捨てなど)はしておりません。
関数はやはり自分には難しかったですが、「結果」だけコピペしたらうまくできました!
これで時給計算の作業が軽減できると思うとうれしくなります。
本当にありがとうございました
No.2
- 回答日時:
セルI1に9:00と入力し、名前定義で、「通常開始」と定義します。
セルJ1に17:00と入力し、名前定義で、「通常終了」と定義します。
セルG1に下記関数を入力します。
=IF(A1<=通常開始,通常開始-A1,0)
セルH1に下記関数を入力します。
=IF(B1>=通常終了,B1-通常終了,0)
結果
セルC1に以下の関数を入力します。
=G1+H1
セルD1に以下の関数を入力します。
=B1-A1-C1
この回答への補足
ご回答ありがとうございます。アドバイスの通りの式を使ったらうまくできました!
ありがとうございます。
ただ、7:00~20:00などの長い勤務の場合ではうまくいきました
が、7:00~8:00や7:00~7:30などの短い勤務の場合、G1は常に2:00
となります。
正しくはG1に1:00か0:30となるかと思うのですが、これを改善するにはどうすればよろしいでしょうか?
宜しくお願い致します。
No.1
- 回答日時:
1つの枠でやろうとすると、それだけ関数は複雑になります。
そういう時は手順を分けて考えます。時間帯が3つに分かれているので、その点はやりやすいと思います。
あと、9:00、17:00は事前にどこかのセルに入力して置いた方が参照しやすくて良いです。(内部では時間ではなく日数で管理しているため)
時給の数字も参照できるようにセルに入れておくと、そこを変更するだけで全ての計算を変更できます。
C1=if(A1<[9:00],([9:00]-a1)*24,0)+if(B1>[17:00],(B1-[17:00])*24,0)
D1=E1-C1
[9:00]と[17:00]は、参照先のセル番号です。
E1はB1-A1じゃなくて、(B1-A1)*24にして置く必要があります。書式も「時刻」ではなく「標準」にすること。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- アルバイト・パート 22年10月から社会保険適用拡大について、詳しい方いらっしゃいましたら教えてください 2 2022/09/01 20:40
- その他(ビジネス・キャリア) 日報の実務労働時間の集計について困っています。 2 2023/06/09 19:00
- 求人情報・採用情報 介護の求人についてお伺いしたいです。 現在フリーターでアルバイトを探しており、グループホームの求人を 1 2023/03/20 23:15
- 所得・給料・お小遣い 勤務時間:8時ー17時勤務(2時間休憩) 実稼働:7時間 勤務日数:週6勤務 毎週日曜日を休みとして 2 2023/07/26 18:25
- 求人情報・採用情報 1日の実働7.5h✕週6日の求人についてお尋ねします。 週の労働時間は40hまでなので、5hこえた分 2 2022/10/24 15:48
- 所得・給料・お小遣い 1日8時間、月21日稼働、最低時給1071円で18万!時給2500円だと42万!!!(^_^;) 1 2023/02/09 19:35
- 労働相談 アルバイト・パートの有給制度について 4 2023/01/07 20:58
- 所得・給料・お小遣い 週休3日制のシフト制の日割り計算を教えてください。 給与 22万(総支給) 週休3日制 10時間勤務 1 2022/05/25 07:54
- アルバイト・パート 自由シフト制と日給について教えてください。 1 2023/04/13 15:30
- 転職 主婦の転職、、、皆さんに働き方について質問します。 貴方が主婦の場合どのような働き方をしますか? 夫 3 2023/03/07 18:53
おすすめ情報
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
同一セルに日時があるものを日...
-
1年分のデータから特定の月分...
-
EXCELでCSVファイル保存すると...
-
Excelで指定した条件と一致する...
-
色なしセルの合計値の出し方に...
-
1つのセルにまとまっている情報...
-
COUNTIFの反対の関数はあるので...
-
エクセルVBAで、複数セルのデー...
-
エクセルでセルを一括で右詰に...
-
エクセルで数字、文字列混在の...
-
ドロップダウンリストで空白の...
-
Excelで連続データを行飛ばしで...
-
Excelのシートごとの日付の追加
-
EXCELで足し算できるのにSUM...
-
エクセル数式・等間隔ごとの範...
-
【エクセル】一覧から指定デー...
-
複数のシートの日付データを、...
-
Excelの連続データ作成とmatch関数
-
エクセルの関数について
-
Excelで、表紙に、住所を自動で...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
1年分のデータから特定の月分...
-
同一セルに日時があるものを日...
-
Excelで指定した条件と一致する...
-
EXCELでCSVファイル保存すると...
-
エクセルで数字、文字列混在の...
-
COUNTIFの反対の関数はあるので...
-
ドロップダウンリストで空白の...
-
1つのセルにまとまっている情報...
-
エクセルVBAで、複数セルのデー...
-
エクセルでセルを一括で右詰に...
-
EXCELで年月日の表記から日付部...
-
複数のシートの日付データを、...
-
EXCELで足し算できるのにSUM...
-
webクエリのurlの変更方法
-
OFFSET関数を使用した印刷範囲...
-
エクセルで特定の行を除いて計...
-
Excelで連続データを行飛ばしで...
-
excelで平均差を出したい
-
エクセル数式・等間隔ごとの範...
-
Excel2010 vlookup 検索値が複...
おすすめ情報