「時間を計算するときのポイント 1」で、Excel での時刻の入力や表示形式、基本的な計算について
ご紹介をしました。今回は「時間を計算するときのポイント 1」で求めた合計の時間を元に、
簡単なお給料計算をしてみたいと思います。
そのためには、もう少し、Excel で時刻や時間を管理するときの基本を押さえておく必要があります。

前回書いたように、合計の時間が求められたからといって、これに時給となる金額をかけたところで
正しいお給料の額は算出できないのです。
 

下図を見ていただくとわかるのですが、セル D17 の表示形式を「通貨」に変更し、
セル F10 *セル D15、という計算式を作成すると「\1,653」と表示されます。
時給 900 円で 44 時間も働いたのに1600円？？ ということになります。

これはなぜでしょう？

少し遠回りをしますが、今回は「シリアル値」というキーワードについて確認してから先に進みます。

 

Excel では、シリアル値という日付や時刻を表す数値を使って、日数や時間の計算を
可能にしています。

(Windows 版の) Excel では、1900年1月1日をシリアル値「1」として、経過日数をプラスして
いきます。1900年1月2日は 1日経過しているので、シリアル値で表すと「2」です。

それからずーーっと、何万日も経過して 2010年6月1日までいくと、シリアル値は「40300」と
なります。

Excel では、このシリアル値に「見た目」を設定することで「2010年6月1日」とか
「2010/6/1」とか「6月1日」などのように表わしているのです。

試しに、セルに「40330」と入力し、[セルの書式設定] ダイアログ ボックスを表示して、
[表示形式] タブの [分類] で [日付] を選択して [種類] で形式を選択してみてください。
「サンプル」という部分に、「40330」に選択した形式を適用するとどう見えるのかが確認できますね。

しかし、私たちは、日付を入力するときにシリアル値で入力したりしません。
「6/1」とか「6-1」とかというように入力しますね。

セルに「6/1」とか「6-1」のように入力をすると、Excel が自動的に日付だと認識し、
シリアル値を取得し、さらに「2010年6月1日」などのような見栄え (表示形式) を
設定してくれるのです。

シリアル値を使うと経過日数を求めることができます。
たとえば、40339 (2010年6月10日) から 40300 (2010年6月1日) を引くと、
「9」なので「9日が経過した」という答えが求められます。

同じように、時刻もシリアル値を使って表し、これを元に計算することができます。

シリアル値は、1 や 2 などの整数部分が日付を、0.5 や 0.25 などといった小数部分が時間を
表わしています。
24 時間経過するということは「1」日経過するということであり、1 日 (24 時間) に満たない部分は
時間であるため小数点以下で表わす、ということです。

「6:00」は 1 日の 4 分の 1 なので 0.25、「12:00」は 1 日の 2 分の 1 なので「0.5」と考えると
わかりやすいでしょうか。

「40330.25」は「2010年6月1日 の 6時」で、「40330.5」は「2010年6月1日 の 12時」です。
「40330.5」から「40330.25」を引くと、「0.25」でこれに時刻を表す表示形式を設定すると「6:00」と
表示されます。
ということで、6時間が経過しているということがわかります。

 

 

ここまでのシリアル値の考え方を元に、前回の合計時間のシリアル値を見てみましょう。

緑色のセル (合計時間のセル) のシリアル値を [セルの書式設定] ダイアログ ボックスで確認すると、
「1.836111111」となっています。

 

日の部分を整数で、時間を少数で表したとき、
44 時間は、24 時間 (1 日：シリアル値 1) と、20 時間 (シリアル値：0.83) となりますね。

時間の合計が表示されているセル F10 のシリアル値「1.836111111」に、
時給である「900円」をかけると「1,653円」になるのです。

なぜ、時給 900 円で 44 時間働いたのに 1600 円ほどと表示されてしまうのかが、
わかりましたでしょうか。

ここまでが、前提となるお話。
次の Step からが、時間と時給を元にお給料を算出するという本題です。

 

シリアル値ではない、計算のできる作業時間を用意しなければなりません。

「44:00」の中に「何日」あるかを DAY 関数で求め、(日を除いて) 何時間あるのかを
HOUR 関数で求めてセルに表示します。

DAY 関数は、シリアル値に対応する 1 ～ 31 の「日」を求める関数です。
HOUR 関数は、シリアル値に対応する 0 ～ 23 の「時刻」を求める関数です。

DAY 関数と HOUR 関数を組み合わせて、トータルの時間を求める計算式は、

=DAY(シリアル値) * 24 + HOUR(シリアル値)

となります。

今回の例を計算式に当てはめると、

=DAY(F10)*24 ＋ HOUR(F10)

となり、

=24 ＋ 20

 

なので、「44」時間という答えが出ますね。
(セル D13 の表示形式は、「標準」にすると下図と同じ結果が表示されます。)

 

あとは、算出した作業時間*時給という計算式を作ればよいです。

だいぶ遠回りをしましたが、「シリアル値」と「表示形式」、「時刻」と「時間」など、
データとそのデータの意味や見栄えについてご紹介をしました。
複雑に感じて、急には覚えられないという方もいらっしゃると思いますが、
「普通には計算できないっていうのを見たことがある気がする」という程度に、
記憶に残してもらえればそれでよいです。
あとは必要になったときにまたインターネットなどで調べてみてください。

そして、15分単位で計算しなくちゃとか、お休みの日と平日で時給が違うんだけどなど、
いろいろ応用しなければならないとは思います。
それも計算式次第。詳細なパターンについてはまたリクエストがあれば書きたいと思います。

石田 かのこ