役立つ! 総務マガジン

Excel時間集計あるある 表示形式を極める

24時間を超えても「0:00」に戻らないように設定するには?

総務・人事の業務における「勤怠管理」に欠かせない、時間の計算。しかし、Excelで時間をそのまま足していくと、24時間を超えたところで「0」に戻ってしまいます。今回は時間計算の正しい方法について学びましょう。

[2018年 4月23日公開]

時間計算のトラブルは、多くの人が陥る落とし穴

従業員の勤怠管理においては、時給で働くパートやアルバイトはもちろん、基本給のある正社員でも、時間外・残業手当などのために勤務時間の計算が必要です。しかし、1日の勤務時間は正しく計算できても、1週間や1カ月の勤務時間を計算すると結果がおかしくなった……という経験をした人も多いのではないでしょうか?

Excelで何も考えずに時間を計算すると、「24時間を超えると0に戻る」という落とし穴にはまってしまいます。なぜそうなるのかも含めて、解決策をお教えします!

勤務時間の合計は「オートSUM」で求められる

ここでは勤怠管理表を例に解説します。表には日付や曜日のほか、ある従業員の出社・退社時刻から計算した1日の勤務時間が表示されています。この表で、1カ月の勤務時間の合計を求めてみましょう。

SUM関数の構文
SUM(数値1, 数値2, …, 数値255)

(1)オートSUMでSUM関数を入力する

合計を表示したいセルをアクティブにし、[オートSUM]ボタンをクリックします。

(2)SUM関数の引数を確認する

SUM関数の引数となるセル範囲(明滅する点線で囲まれた部分)が正しいことを確認したら、[Enter]キーを押します。

(3)勤務時間の合計が表示された……?

勤務時間の合計が求められましたが、「4:23」と表示されています。1カ月間の勤務時間の合計が「4時間23分」なわけはありませんから、誤った結果です。

時間計算の基になる「シリアル値」とは?

なぜ、勤務時間の合計が「4:23」と大幅に少なく表示されてしまうのでしょうか? それはExcelを含めたパソコン内での日付や時間が、一般的な数値とは異なる「シリアル値」で管理されていることに起因します。

シリアル値とは、1900年1月1日を「1」として、以降1日ごとに「1」ずつ増えていく数値のこと。1日=「1」なので、1時間はそれを24等分、1分はさらに60等分した数値つまり「1÷24÷60=0.00069444…」となります。

先ほどの例で見てみましょう。ある1日の勤務時間と、1カ月の勤務時間の合計をほかのセルにコピーし、セルの表示形式を[標準]にします。すると、それぞれが小数点以下の数値を持つシリアル値になります。これが時間に見えていたものの正体です。

1日の勤務時間は1日を超えないため「1」より小さいシリアル値、1カ月の勤務時間の合計は1日を超えるため「1」より大きいシリアル値になります。

Excelでは、このシリアル値に日付や時間の表示形式を適用することで、私たちがふだん目にする「2018/4/25」「20:15」といった形式にしています。しかし、標準で適用される[時間]の表示形式は、シリアル値の小数点以下のみを時間として表示し、整数の部分は無視します。そのため、1日(24時間)を超える時間がおかしくなってしまうのです。上記の画像では、その値「9.182639」の小数点以下部分「0.182639」だけが計算され、1分を示すシリアル値0.00069444で割ると263.000162分→4.383336時間→4時間23分となったわけです。

1日を超える時間は「ユーザー定義」の表示形式で解決

ただ、おかしくなるといってもシリアル値としては間違っておらず、正しい書式を設定すれば解決できます。以下の手順のように「ユーザー定義」の表示形式を適用しましょう。

(1)セルの書式設定を開く

勤務時間の合計が表示されているセルをアクティブにします。続いて[ホーム]タブの[セル]グループにある[書式]をクリックし、[セルの書式設定]を選択して[セルの書式設定]ダイアログボックスを表示します。[Ctrl]+[1]キーのショートカット操作で表示することもできます。

(2)ユーザー定義の表示形式を選択する

[表示形式]タブ内の[分類]ボックスから[ユーザー定義]を選択し、[種類]リストの[h:mm]が選択されていることを確認します。

(3)表示形式を編集して適用する

[種類]ボックス内をクリックし、「h」を半角の大カッコ([])で囲みます。[サンプル]に24時間以上の時間が表示されていることを確認し、[OK]ボタンをクリックします。

(4)勤務時間の合計が正しく表示された

1カ月の勤務時間の合計が「220:23」(220時間23分)となり、正しく表示できました。

シリアル値+表示形式が時間計算のポイント

勤怠管理表に限らず、総務・人事の業務として、日付や時間に関連した計算をすることは多いでしょう。考えていたような計算結果にならない場合は、日付や時間の正体がシリアル値であることを思い出し、正しい表示形式を適用するようにしてください。

協力メディア

できるネット(https://dekiru.net)

もっと関数のスキルを磨きたい! という人はぜひ大塚商会のExcel教室へ

大塚商会では、企業の人材育成をトータルにサポートする「人材育成支援サービス」をご提供しています。ITやCAD、ビジネススキルなどさまざまなジャンルをご用意しており、もちろんExcelを学ぶコースも充実しています。

Excelを基礎から応用まで体系的に学べる「標準コース」や、関数やマクロなど目的に応じて学べる「目的別コース」をご用意しています。大塚商会の研修施設で定期開催されているオープンコースのほか、貴社の業務に合わせてカスタマイズした企業研修(ご来場または出張)も可能です。

Excel研修の詳細情報を見る

Excelをもっと便利に活用するにはOffice 365をおすすめします

マイクロソフトの「Office 365」は、ExcelやPowerPointを始めとしたOffice製品、メール、スケジュール管理などをクラウド上で使用できます。非常に利便性の高いシステムを実現することが可能です。

Office 365 について見る

おすすめの記事