こんにちはゲストさん。会員登録(無料)して質問・回答してみよう!

解決済みの質問

エクセルで勤務表から実働時間を出したい。

エクセルで勤務表から実働時間を出したい。
私の会社は、365日24時間休みなく稼働しています。
68歳過ぎると勤務体系が、日勤だけ、夜勤だけ、日勤夜勤通しとわかれています。
そこで教えてほしいのですが、日勤は実働8時間、夜勤は実働11時間、日夜勤は18時間と決まっています。
今使っているエクセルの表はA1に日にち、A2に曜日、A3は空白、A4に氏名と縦に記入してあります。
横列はB1とC1を結合して1日、D1とE1を結合して2日以降横に一カ月の日にちが入ります。
B2とC2を結合して曜日がはいり横に同様に曜日を入れています。
B3に日勤の(日)、C3に夜勤の(夜)と記入しています。
A4に氏名を記入し、B4に勤務地名、C4に勤務地名りが入るようセルを開けてあります。
例えばA4のaさんがB4の1日の(日)に新宿でC4の夜勤なし。H4の4日の日勤なしでI4の(夜)に渋谷。N4の(日)に赤羽、O4の(夜)に赤羽と勤務をした場合にBN4に実働勤務時間を表したいのですが教えていただけますか。
また一人の月の勤務時間は162時間までと決まっているので、162時間を超えた場合にセルか文字の色が変わるようにできますか。
(日)の勤務地の文字を8時間と考え、(夜)の勤務地の文字を11時間と考え、(日)(夜)の勤務地を18時間と考えて計算できるのでしょうか。
説明が分かりずらく、申し訳ありませんが、教えていただけないでしょうか。

投稿日時 - 2010-04-25 13:49:40

QNo.5850299

困ってます

質問者が選んだベストアンサー

COUNTIF関数を使って、「(日)」「(夜)」「(日)(夜)」を数え、それぞれに8,11,18をかければできるでしょう。次に示す式では、セルC3からZ3の範囲にあるものを数えて勤務時間を求めます。

=COUNTIF(C3:Z3,"(日)")*8+COUNTIF(C3:Z3,"(夜)")*11+COUNTIF(C3:Z3,"(日)(夜)")*18

Z3までにした理由は特にありません。何となくです。そこは実際に計算したい範囲に書き直して使ってください。なお、上記の式では全ての勤務地を一緒くたに計算するため、もし勤務地ごとに時間を求めたい場合は、表を作り直した方が楽です。具体的には、勤務地の横にもう一つセルを用意して、ここにその日の勤務時間をしまっておくようにします。例えばセルD3に次の式を入れておきます。

=IF(B3="(日)"),8,IF(B3="(夜)"),11,IF(B3="(日)(夜)"),18,0)

このままだと、見えなくてもよい時間が見えてしまうことになるため、このセルは非表示にしておきます。そして最後に、SUMIF関数で集計します。以下の式では、赤羽の勤務時間を集計しています。

=SUMIF(C3:Z3,"赤羽",C3:Z3)

セルか文字の色を変えるのは、条件付き書式を使います。次のリンク先の説明が解りやすいと思います。このサイトの説明は良くまとまっていて、全般的に解りやすいですよ。

http://www.eurus.dti.ne.jp/~yoneyama/Excel/jyo-syo.html

この方法で、勤務時間合計を出すセル(今回はBN4)の値が162より大きければ、条件付き書式で色を変えるようにするわけです。

投稿日時 - 2010-04-25 15:19:06

お礼

siremono2496さん、ありがとうございます。
エクセルのレベルが中より下の私にも理解しやすく教えていただいて助かります。
早速試してみたのですが、まず=COUNTIF(C3:Z3,"(日)")*8+COUNTIF(C3:Z3,"(夜)")*11+COUNTIF(C3:Z3,"(日)(夜)")*18の計算式をBN4のセルに入れてみたところ、(0)と表示されました。
3の横列のB3に日、C3に夜、D3に日、E3に夜と右に31日分記入してありBJ3に日、BK3に夜と記入し終わっています。
4の横列にA4に勤務者の氏名(山本太郎)1日のB4に渋谷、C4は空白2日、3日は空白、4日のH4は空白、I4に赤羽、6日のL4に上野、M4に上野、以降空白と勤務地が入った場合を想定してBN4に上記計算式を入れてみましたが、BN4のセルには(0)と表示されます。
どこかおかしいところはありますか。
再度教えていただきたいのですが。
それと、勤務地ごとの勤務時間の出し方も教えていただきありがとうございます。
今は勤務地ごとの勤務時間は出していないのですが、大勢いるのでどうしても勤務地が偏ってしまう傾向にあります。
教えていただいた計算式を利用すれば、勤務地の偏りも改善されると思いますので、使わせていただきます。
後日、この作業を行いますので、またわからないようでしたら教えていただけないでしょうか。
よろしくお願いします。
また、セル、文字の色の変え方のサイトを教えていただいてありがとうございます。
画像付きでわかりやすく説明してあり、助かりました。

投稿日時 - 2010-04-26 20:46:38

このQ&Aは役に立ちましたか?

9人が「このQ&Aが役に立った」と投票しています

回答(3)

私はてっきり、最初に例示されたものが人の数だけ上下に並ぶのだと思っていました。次のような形で。これだと、単純に勤務形態から時間を算出するだけでよく、「どの人が」という判定が必要ないので、今の式はそこを全く考慮していません。

田中 日 夜 ・・・合計 xx H(先の式が入る)
(勤務地)

中村 夜 日 ・・・合計 xx H(先の式が入る)
(勤務地)

一つ確認ですが、No2に追記してもらった例に疑問があります。これだと、セルB3のところは日勤者だけで夜間は不在、C3のところは夜勤者だけで昼間は不在になります。また、例えば9日はAさんは赤羽で日勤でBさんは渋谷で夜勤という書き分けが出来ません。24h365日という前提とも矛盾します。そのため、上記のような勤務表になるだろうと考え、先の式を提示した次第です。この勤務表ならば、先の式でうまくいきます。

それではダメだという場合は、根本的に表の形の認識が違ってしまっているので、一度質問を締め切って、新たに質問し直していただけませんか?その際には、実際に集計したい表の例を作成し、その画像を質問に添付してください。そうすれば誤解を招かずに済むと思います。エクセル画面の画像の撮り方は、次のURLを参照ください。なお、あまり大きな画像は縮小されてしまって字が読めなくなるため、8セルx10行くらいの抜粋画像にする方が確実です。

http://www.excel.studio-kazu.jp/tips/0021/

投稿日時 - 2010-04-28 22:53:57

お礼

siremono2496さん、お手数をお掛けしまして申し訳ありません。
私の質問の仕方が悪く、大変迷惑をおかけしました。
日を改めて、表を作り、質問を再度最初からしますので、もう少しお待ち願えないでしょうか。
本当に申し訳ありませんでした。

投稿日時 - 2010-04-29 20:40:08

先の式だと、(日)(夜)といった具合に、全て括弧で囲んで記入されていることを前提としています。なので、(日)と記入しなければカウントされません。もし括弧を付けずに日と記入している場合は、先の式の中の"(日)"を"日"に書き直す必要があります。夜も同じです。

投稿日時 - 2010-04-27 21:20:56

お礼

siremono2496さんお忙しい中早々と回答していただきありがとうございます。
別に簡単な表を作り、教えていただいたように、()を消したところ、数字が現れました。
A3は空白、B3に日、C3に夜、D3に日、E3に夜、F3に日、G3に夜、H3に日、I3に夜、J3に日、K3に夜と記入しA4に山本、A5に佐藤、A6に田中と記入しました。
山本さんの勤務地をB4に上野、E4に赤羽、J4に渋谷、K4に渋谷と記入し、後は空白です。
佐藤さんの勤務地をD5に渋谷、H5に赤羽、I5に赤羽、K5に上野と記入し、後は空白です。
田中さんの勤務地をC6に赤羽、E6に上野、J6に渋谷、K6に渋谷と記入し後は空白としました。
計算機で計算すると山本、佐藤、田中さんの勤務時間は各自37時間となります。
そこで教えていただいた計算式をL4に入れ、L4のセルをL5、L6にオートフィルしました。
するとL4の答えは95、L5、L6は0と表示されました。
私の質問の仕方が悪く、ご迷惑をおかけしておりますが、再度どこが悪いか教えていただけないでしょうか。
時間がかかってもいいのでよろしくお願いします。

投稿日時 - 2010-04-27 22:46:02

あなたにオススメの質問