土曜日, 7月 04, 2020

Excel_14 
表示形式とシリアル値(2)

3回目で触れた表示形式とシリアル値について実際のデータで再度整理してみました。

まず全員の勤務時間を計算します。これは退勤時間から出勤時間を引けばよいので、リストの最初にいる上杉謙信さんの勤務時間は =C4-B4 となります。処理結果をそのままフィルすれば全員の勤務時間が出ます。

次に全員の勤務私感の合計をオートSUMで出します。すると、5時間という値が出て来ました。明らかに変ですが、これは表示形式に問題が在るからです。通常24時間で時間はリセットされてしまいます。ですからデフォルトの設定で加算を行っても24時間を越える値は出て来ません。

そこで計算結果である D16 を選択し、ホームタグの[数値>その他の番号書式]からヨーザー定義で h:mm を [h]:mm に変更します。これで正しい 53:00 が表示されます。
  [h]:mm 

次に給与を計算します。給与は時間に時給を掛けます =E1*D4 しかし、そのままだととんでもなく低い金額になってしまいます。これはExcelの時刻がシリアル値で管理されており、1日を1(1900年1月1日を1としてスタート)とカウントしているからです。そのため、時刻に時刻以外の値を掛ける場合は時刻を24倍する必要があります。よって=E1*D4*24 となります。

ところがフィルをするとまたまた飛んでない値になってしまいます。これはうっかり絶対参照の設定をしていなかったことが原因です。時給は全ての人が同じ位置(D1)を参照するからです。よって正確には、
=E$1*D4*24 
となります。これで全ての計算が完了しました。Ecelで忘れてはいけないのはシリアル値とデータの表示形式です。これさえ混乱しなければ意外とスムースに理解が深まると思います。

Office365 2020