[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『平日と土日祝日で時給単価が異なる場合の計算』（miko）
前回は、https://www.excel.studio-kazu.jp/kw/20100701125910.htmlにてお世話になりました。
タイムカードｎ時間を元に、パートの給与計算表を作っています。
「平日昼」「平日夜」「土日祝昼」「土日祝夜」「深夜」ごとに時給が異なるため、
それぞれの労働時間を算出できる表がつくりたいです。
行 __A__ __B__ __C__ __D__ ___E___ ___F___ ___G___ ____H____ ____I____ __J__
1 出勤 退勤 出勤 退勤 時間計 昼勤務 夜勤務 深夜勤務 休憩 2 16:46 22:34 17:00 22:30 5:30 5:00 0:30 3 10:53 19:04 11:00 19:00 8:00 6:00 2:00 1:00 4 16:44 23:15 16:45 23:15 6:30 0:15 5:00 1:15 0:30 5 17:30 1:00 17:30 25:00 7:30 4:30 3:00 0:45 6 - -- 7 8
・ C2セル =IF(A2="","",CEILING(A2,TIME(,15,)))
・ D2セル =IF(B2="","",FLOOR(B2,TIME(,15,))+(B2<A2))
・ E2セル =IF(COUNT(A2:B2)<2,"-",D2-C2)
・ F2セル =IF(E2="-","",ROUND(MAX(0,MIN(D2,"17:00")-C2),14)) ・ G2セル =IF(E2="-","",ROUND(MAX(0,MIN(D2,"22:00")-MAX(C2,"17:00")),14)) ・ H2セル =IF(E2="-","",ROUND(MAX(0,D2-"22:00"),14))
ここまでは良いのですが、この後曜日を自動的に読み取って、それぞれの合計労働時間を出す方法はありますか？
教えてください。
Excel2007, Windows Vista,
１．日付がどこにあるか分からないと、具体案が書けません。 考え方としては、土日祝かどうかを判断して分岐処理することになります。 1例を挙げれば、Workday()関数を使って、当該日の1日前から見て、1日後が当日と同じか？ なんてことで分岐判断ができます。
２．祝日リストが必要なので、それをどこに書くことにするか決定してください。 (どのシートのどの列か？）
３．新しい項目名を含んだレイアウトを示して頂くと進行がスムーズになると思います。
※バージョンはExcel2007だったんですねー。 条件付き書式を利用して0:00を出さない様にすれば、 Round()を噛ませる必要はなかったです。 m(__)m これも、今回改善します。
(半平太) 2010/07/19 10:50
前回はありがとうございました＾＾
今回も書き込み助かります！！
行 __A__ __B__ __C__ __D__ ___E__ __F__ __G__ ___H___ ___I___ ___J___
1 日付 曜日 出勤 退勤 出勤 退勤 時間計 昼勤務 夜勤務 深夜勤務 2 1 月 16:46 22:34 17:00 22:30 5:30 5:00 0:30 3 2 火 10:53 19:04 11:00 19:00 8:00 6:00 2:00 4 3 水 16:44 23:15 16:45 23:15 6:30 0:15 5:00 1:15 5 17:30 1:00 17:30 25:00 7:30 4:30 3:00 6 - -- 7 8 ･ ･ ･ 32 31 水 33 平日昼計 34 日祝昼計 35 平日夜計 36 日祝夜計 37 深夜計
１．日付と曜日は、Ａ，Ｂ列にあります。
２．祝日のリストは、同じシートの上記の表の右隣に作ろうと思います。
仮にＬ列とします。
３．３１日の下にそのまま合計欄をつくる予定です。
うまく説明できなくて申し訳ありません(^^;)
よろしくお願いします！！
(miko)
行 __A__ ___B___ ___C___ ___D___ ___E__ ___F___ __G___ ___H___ ___I____ ___J___
1 日付 曜日 出勤 退勤 出勤 退勤 時間計 昼勤務 夜勤務 深夜勤務 2 1 月 16:46 22:34 17:00 22:30 5:30 5:00 0:30 3 2 火 10:53 19:04 11:00 19:00 8:00 6:00 2:00 4 3 水 16:44 23:15 16:45 23:15 6:30 0:15 5:00 1:15 5 17:30 1:00 17:30 25:00 7:30 4:30 3:00 6 - -- 7 8 ･ ･ ･ 32 31 水 33 平日 : : 34 土日祝 : :
Ｈ33に、平日の昼勤務計。
Ｉ33に、平日の夜勤務計。
Ｈ34に、土日祝の昼勤務計。
Ｉ34に、土日祝の夜勤務計。が入るようにしたいです。
（miko）
１．祝日か否かを判定するには、年月情報が必要なので、A列はシリアル値で入力するものとします。 (表示形式で１，２，３，・・ とするのは自由です）
２．時間を表示させるC2:J32の範囲のセルの書式(表示形式)を [h]:mm;@ と設定。
３．0:00を非表示にするため、時間を表示させるセルの「条件付き書式」を以下の通り設定します。 「数式を使用して、書式設定するセルを決定」で 「次の数式を満たす場合に値を書式設定」のボックスに =TEXT(C2,"[h]:mm")="0:00" と入力し、
セルの書式の設定の(表示形式)タブを開いて 分類を「ユーザー設定」を選択し、種類(T)ボックスに ;;; とセミコロンを3つ入力。
４．数式 (1) E2セル =IF(C2="","",CEILING(C2,TIME(,15,))) (2) F2セル =IF(D2="","",FLOOR(D2,TIME(,15,))+(D2<C2)) (3) G2セル =IF(COUNT(C2:D2)<2,"-",F2-E2) (4) H2セル =IF(G2="-","",MAX(0,MIN(F2,"17:00")-E2)) (5) I2セル =IF(G2="-","",MAX(0,MIN(F2,"22:00")-MAX(E2,"17:00"))) (6) J2セル =IF(G2="-","",MAX(0,F2-"22:00")) (7) B33セル =SUMPRODUCT((COUNTIF(L:L,A$2:A$32)=0)*(WEEKDAY(A$2:A$32+6)<6),H$2:H$32) (8) B34セル =SUMPRODUCT(SIGN(COUNTIF(L:L,A$2:A$32)+(WEEKDAY(A$2:A$32+6)>5)),H$2:H$32) (9) B35セル =SUMPRODUCT((COUNTIF(L:L,A$2:A$32)=0)*(WEEKDAY(A$2:A$32+6)<6),I$2:I$32) (10) B36セル =SUMPRODUCT(SIGN(COUNTIF(L:L,A$2:A$32)+(WEEKDAY(A$2:A$32+6)>5)),I$2:I$32) (11) B37セル =SUM(J2:J32)
<結果図>
行 ____A____ __B__ __C__ __D__ __E__ __F__ ___G___ ___H___ ___I___ ____J____ _K_ ____L____ 1 日付 曜日 出勤 退勤 出勤 退勤 時間計 昼勤務 夜勤務 深夜勤務 2010/7/19 2 2010/7/1 木 -
15 2010/7/14 水 - 16 2010/7/15 木 - 17 2010/7/16 金 16:46 22:34 17:00 22:30 5:30 5:00 0:30 18 2010/7/17 土 10:53 19:04 11:00 19:00 8:00 6:00 2:00 19 2010/7/18 日 16:44 23:15 16:45 23:15 6:30 0:15 5:00 1:15 20 2010/7/19 月 17:30 1:00 17:30 25:00 7:30 4:30 3:00 21 2010/7/20 火 -
31 2010/7/30 金 - 32 2010/7/31 土 - 33 平日昼計 0:00 34 日祝昼計 6:15 35 平日夜計 5:00 36 日祝夜計 11:30 37 深夜計 4:45
(半平太) 2010/07/20 12:10
A列の日付がシリアル値で 祝日の場合、B列が「祝」と表示されてもよければ
B2 =IF(A2="","",IF(COUNTIF(L:L,A2),"祝",TEXT(A2,"aaa"))) B32までコピー
B33 =SUM(H2:H32)-B34
B34 =SUM(SUMIF(B2:B32,{"土","日","祝"},H2:H32))
夜計も同じ要領で
ところでA列の日付はオートフィル？ 数式？
（トーリス・ガーリ）
トーリス・ガーリさんのアイデアがいいですね ＾＾
私の出る幕は無いので、ドロップアウトします。 m(__)m
(半平太) 2010/07/21 08:49
これでうまくいきそうです＾＾
＞ところでA列の日付はオートフィル？ 数式？
Excelの用語がわからないのですが、手入力してます。。。
7/1と入れて「1」と表示するようにしてます。
翌月以降のことを考えると、
別の方法に変えた方が良いのでしょうか(^^;)
（miko）
＞ 手入力してます。。。 7/1と入れて「1」と表示するようにしてます。
へ？ 手入力？ 手入力って、一個一個全部？ もしかしてA列には出勤した日だけを入力してるってこと？ サンプルデータでは月末まで全部埋まってるように見えますが。。
よくわかりませんけど、数式ではないんですね？ ならいいです。この件は忘れてください。
（トーリス・ガーリ）
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.