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

質問

エクセルで勤務表から実働時間を出したい。
私の会社は、３６５日２４時間休みなく稼働しています。
６８歳過ぎると勤務体系が、日勤だけ、夜勤だけ、日勤夜勤通しとわかれています。
そこで教えてほしいのですが、日勤は実働８時間、夜勤は実働11時間、日夜勤は18時間と決まっています。
今使っているエクセルの表はＡ1に日にち、Ａ2に曜日、Ａ3は空白、Ａ4に氏名と縦に記入してあります。
横列はＢ1とＣ1を結合して１日、Ｄ1とＥ1を結合して２日以降横に一カ月の日にちが入ります。
Ｂ2とＣ2を結合して曜日がはいり横に同様に曜日を入れています。
Ｂ3に日勤の（日）、Ｃ3に夜勤の（夜）と記入しています。
Ａ4に氏名を記入し、Ｂ4に勤務地名、Ｃ4に勤務地名りが入るようセルを開けてあります。
例えばＡ4のａさんがＢ4の１日の（日）に新宿でＣ4の夜勤なし。Ｈ4の４日の日勤なしでＩ4の（夜）に渋谷。Ｎ4の（日）に赤羽、Ｏ4の（夜）に赤羽と勤務をした場合にＢＮ4に実働勤務時間を表したいのですが教えていただけますか。
また一人の月の勤務時間は１６２時間までと決まっているので、１６２時間を超えた場合にセルか文字の色が変わるようにできますか。
（日）の勤務地の文字を８時間と考え、（夜）の勤務地の文字を11時間と考え、（日）（夜）の勤務地を18時間と考えて計算できるのでしょうか。
説明が分かりずらく、申し訳ありませんが、教えていただけないでしょうか。

回答No.1

noname#194317 · ベストアンサー

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より大きければ、条件付き書式で色を変えるようにするわけです。

お礼

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

回答No.3

noname#194317

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

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

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

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

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

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

お礼

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

回答No.2

noname#194317

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

お礼

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に渋谷と記入し後は空白としました。
計算機で計算すると山本、佐藤、田中さんの勤務時間は各自３７時間となります。
そこで教えていただいた計算式をL4に入れ、L4のセルをL5、L6にオートフィルしました。
するとL4の答えは95、L5、L6は0と表示されました。
私の質問の仕方が悪く、ご迷惑をおかけしておりますが、再度どこが悪いか教えていただけないでしょうか。
時間がかかってもいいのでよろしくお願いします。