Excel2013対応

前回の続きです。
前回は、日付を入れれば曜日も埋まる、ってトコまで進んだんですよね※１。 今回は、いよいよ勤務時間の計算をしていきたいと思います。

では、前回同様、仮データを入れてイメージをつかんでみましょう。
「出社時刻」欄と「退社時刻」欄に適当な値を入れてみます。 試しに、「９時出社、18時退社」ってデータを入れてみてください。 9:00と、「：（コロン）」 を使って入れてやればＯＫですので※２。

あと５コくらい入れてみましょうか※３。

５月27日のところのデータは、深夜勤務を想定してみました。 夜の21:49入りで、翌朝の8:07上がり。 仕事上がりは翌日なので、ホントは28日なんだけど、 27日の行に入れています。
ハイ、では、５件データを埋めたところで、問題です。 今、出社時刻と退社時刻を入力しました。 さて、勤務時間はどうやって割り出しますか？
…うーん、とりあえず、上がり時刻から入り時刻を引いてみましょうか。 新しい列を作って、そこで計算をしてみましょう※４。

できましたら、新しく作った「勤務時間」列に、式を入れていきます※５　※６。
休憩時間のことは、いろいろな理由があって（…こちらにも、解説の都合というものがありまして…すみません…）、 今のところはとりあえず無視してください。

式を入れました。ちゃんと勤務時間、割り出せてる感じですね。
では、この式をおしりまでコピーしてあげましょう※７！どうなるかな？

アレ？深夜勤務のところで、変な表示が出ました。
結論から言うと、これは、計算結果がマイナスになってます、というエラーです。 時間がマイナス、という計算結果に対して、Excelが困ってしまったんですな。
では、どうすれば良いのでしょうか？
　・
　・
実は、日付と同様、時刻もシリアル値をもっています。 例えば、01:00のシリアル値は、0.41666…です。これは、１を24で割った値のことです。 前回の話を思い出してください。 日付のシリアル値は、１日ごとに１増えるんでしたね。 だから、１時間経過するときには、それをさらに細分化すれば良い。 １日は24時間だから、１時間は1/24になりますよね。 同様に、06:00のシリアル値は、6/24（６を24で割る）、イコール1/4、小数にすると0.25になります。 12:00は0.5です。
時刻のシリアル値を体験してもらうために、日付のときと同様、 数値にして確認してみましょうか。 先ほど入力した時刻を空いてる列にコピって、数値に変換してください※８　※９。

…おっと失敗。書式を「数値」にするときに、 小数点以下を表示しない設定にしてしまったため、無理矢理整数表記になってしまってるんです。
お手数ですが、もう一回設定してやりましょう。 書式を「数値」にするときに、「小数点以下の桁数」ってところを調節してやります。 小数点以下は、５ケタくらい表示されれば良いかな…※10　※11。

ハイ、ようやく出ました、シリアル値。 9:00のところは、「0.375」になってますね。これは、９を24で割った値に等しいです。 また、9:07や18:31も、少数で表示されてますね。かなり細かい数字です。 シリアル値で言うと、１分は0.0006944…。 これは、１を24で割って、それをまた60で割った値です。 こんなふうに、Excel内部では、時刻の情報は小数で管理されているんです。

さて、深夜勤務対策です。
ここの表示がおかしくなるのは、計算結果がマイナスになるからです。 だから、対策としては、マイナスにならないようにしてやれば良い。
例えば、TVの深夜番組の予告で、「水曜25:30スタート！」なんて表現、ありますよね。 実際は木曜の01:30のことなんだけど、前日とのつながりで24時間を超えて表現しているわけです。
同様に、５月27日の退社時刻の8:07（実際は５月28日）も、 24時間オーバーで表現してやれば良いのでは？※12　※13

エラーは出なくなりました。
32:07も、一回転して通常表記に戻っています。 これで解決、ってコトで良いんじゃないですか？ね？

…ウン、でも、このやり方にひとつだけ難癖をつけると、 このやり方だと、入力者が工夫しないといけないんです。 すべての入力者に、「日をまたぐときは、24時間オーバーのかたちで入力してね♪」 とお願い・徹底しないといけない。 だけど、一人でもそれを忘れてふつうに8:07と打っちゃうと、エラーになっちゃうワケですよ。
もちろん、入力ルールを徹底するために、マニュアル化することもできます※14。 でも、ここまでやってもやっぱり忘れちゃう人って、いますよね。

なので、こちらでひと手間加えてやりましょう。
時刻のシリアル値は、00:00が0（ゼロ）、24:00が1です。 だから、「もし計算結果がマイナスになるなら、 退社時刻に24:00に該当する値、つまり1を足す」とすれば、マイナスにならないのでは。 なので、式をこんなふうに修正してみました※15。

式の真ん中辺り、IF関数を使うところがキモですね。 これは、「もし単純に引き算をして結果がマイナスになってしまうようだったら、 1を足してください。そうでなければ、そのまま（ゼロを足す）にしてください」 という命令です。 ちょっと難しいですが、読み取ってみてください。
合点いただけましたら、この式を全セルにコピーしていきましょう。

では、出社時刻と退社時刻、適当で良いですので、最後まで埋めてみてください。 イメージ作りですよ、イメージイメージ。

　・
　・
入力を進めていて、気になる箇所を見つけてしまいました。 「出社時刻」だけを入力した段階で、勤務時間に値が表示されてしまうんですよ※16。

これは、退社時刻に何も入っていないのを、Excelが0:00、すなわち24:00と理解して、 そこまでの勤務時間を計算してしまっているんです。 細かいところなんですが、…少し気になってしまいました。
これを解決するには、前回曜日のところでもやったように、 IF関数の空白処理を使えば良い※17。 ちょっとやってみましょう。

こんなふうに、式を改良してみました。 「もし出社時刻または退社時刻が空白なら、空白のままにしてください」という命令を付け加えています。
…そろそろ読んでいるみなさんも、「コイツ面倒くさいなー。ちまちま式をいじりやがって」と思ってらっしゃると思うんですが、 設計というのは得てして、こんなふうに蛇行しながら進むものなんです。 もちろん、いきなり正しい答えが導き出せれば良いんですけど、わたしたちは神様ではないですから。 なので、もう少し右往左往にお付き合いください。
さて、全部入力、できました？※18

サンプルデータなので、こんな感じで良いでしょう。
さて、このタイムシートにおいて、考慮しなければならない条件があと２つあります。 ひとつは、休憩時間の処理。もうひとつは、15分刻みにすることです。
まず、休憩時間。「休憩時間」の欄に、適当な値を入力しましょう。
今回は、全部１時間で良いかな。 で、あとは、これを引けば良い、ですよね。 新しい列を作って、そこで計算してあげましょう※19。

15分単位で切り下げるには、floor関数っていうのを使います。 floor関数って、あまりメジャーじゃないんですが、 「指定した基準値の倍数に 最も近い値に、数値を切り下げる」という機能をもっています。 式のかたちはこんな↓感じ。

=FLOOR(元となる値,基準値)

で、この「基準値」のところに"0:15"を指定してあげると、 8:16は切り下げられて8:15に、 8:33は8:30になる、というワケ。 ちょっとやってみましょう※20。

空白処理のところは、もう説明しませんよ。 いえいえ、いじわるしてるわけじゃないですからね。
あと、「基準値」の"0:15"は、「""（チョンチョン）」で囲ってあげてくださいね。 式を入れられたら、全セルにコピーです※21。

この式を使えば、例えば「５分刻みにしたい！」ってときにも、応用できそうですよね。

さて。いよいよラストミッション、今月の勤務時間を計算するときがやってまいりました。 今月分の合計を割り出したいので、sum関数の出番ですよね。 ちょっとやってみましょう。
合計値を入力するセルを作って…※22。

そこにsum関数を入れる。範囲は、一番右の列でＯＫですよね。すると…※23　※24。

え？オレ今月３時間15分しか働いてないの？
　・
　・
そんなわけありません（笑） かと言って、Excelが悪いわけでもありません。
これは、時間の値が24時間で一周して、ぐるぐる廻ってしまっているから起こる現象です。 だから、このままだと合計値を出せないんです。

でね、結論から言うと、この問題に対処するには、 「00:00」表記をやめて、「数値」にしないといけないんです。
では、数値にするにはどうするかというと…、
…「00:00」表記の背後にはシリアル値がある、という話を上でしましたよね。 01:00のシリアル値は1/24（24分の１）でした。02:00のシリアル値は2/24（24分の２）です。 これを、01:00は数値の「1」、02:00は「2」になるようにしたい。 であれば、24倍すれば求める数値になるのでは？※25

ということで、24倍すると…※26。

ん？いや、めげないめげない。
列を新しく作るときに、僕は「列の挿入」を使ったんですが、 そのときに隣の書式もコピーしちゃったみたいです。 なので、書式を数値に変えてあげましょう。そうすれば。※27　※28

小数表記にできました。 もう、8:00じゃないですよね、 小数の8.00になってますね。
で、できましたら、コイツを合計してください※29　※30　※31。

合計、出せました！ 数値に変換してから合算する、というのがポイントですね。

さて、計算はこれで終わりなんですが…、
作業列がたくさんありますね。 計算のため必要だったとはいえ、「勤務時間」の他に「休憩を引いた値」「15分刻みに」「数値に」 とあって、 できれば１列で済ませた方がスマートです、が、 １列で計算を済ませる仕方については、みなさんの方で考えてみてください。 余力のある方だけで良いですよ。 回答などは特に用意する予定はないので、 時間に余裕のある方はチャレンジしてみてください。