このブログのはてなブックマーク数 このエントリーをはてなブックマークに追加

知らなきゃ絶対損するPCマル秘ワザ

知らなくて損したPC情報とかを分かりやすくメモする個人ブログ。
『月,水,金』の週3回更新!(予定)

エクセル:開始,終了日から求めた期間を「時間(hour)」で表示

このエントリーをはてなブックマークに追加
操作画面


こんにちは、さち です。

先日、エクセルを使って
開始日時 と 終了日時 から期間を算出していました。

その期間の長さを「◯◯時間」と表示しようとしたのですが
書式設定から表示形式を変更するだけでは無理らしい。

本当に時間計算ってややこしいですよね…。




期間の長さを「◯◯時間」で表示したい

  1. 「開始」と「終了」から「期間」を求めてみます。
    操作画面

  2. 「終了」から「開始」を引き算します。
    操作画面

  3. 計算結果は「10.04166667」となりました(表示形式は「標準」)。
    操作画面

  4. 期間の長さを「時間」で表示したいので
    表示形式を「h"時間"」に変更してみました。
    しかし、結果は「1時間」となってしまいました。
    1時間なわけがないのでおかしいですね。
    操作画面




日付は「1900年1月1日」を起点にしている

期間の長さを「時間」で表示するには
計算結果である「10.04166667」が何を意味するのか
理解する必要がありそうです。
(方法だけ分かればいい人は次項まで読み飛ばして下さい)

エクセルでは、日付データを
「1900年1月1日」から何日目という形で記録しています。

例えば、起点の「1900/1/1」は1日目なので「1」。
1日後の「1900/1/2」は2日目なので「2」
同様に、1年後の「1901/1/1」は「367」なわけです。
図解

実際、セルに「1」「2」「367」などと入力し
「セルの書式設定」から「表示形式」を「日付」にすると
上図と同じ日付に変換されることからも分かります。

ここで、先ほどの「2014/2/10 3:00」について考えてみましょう。
エクセルの形式に従うと、2014/2/10 は「41680」です。
さらに、今回は時間「3:00」があります。
1日は24時間なので 1÷24×3=0.125 を足します。
つまり、「2014/2/10 3:00」は「41680.125」と表せるわけです。
図解

同様に、「2014/2/20 4:00」は「41690.16666667」となります。
(1÷24×4 は 0.16666666… と割り切れないなのでこうなります)

だから 2014/2/10 3:00 から 2014/2/20 4:00 までの期間の長さは
41690.16666667-41680.125=10.04166667 と計算されたわけです。
操作画面




「時間」で表示するには「24」をかければいい

エクセルの日付データは
1900年1月1日を起点にした経過日数で記録されていましたね。

つまり、先ほどの計算結果「10.04166667」は
期間の長さを「日数」で表示したものということ。
「時間」で表示するには「24」をかければよいわけです。
操作画面

計算結果は「241時間」となりました。
期間の長さは 10日と1時間 なので正解ですね。
操作画面

同様に、「分」で表示したい場合は
「1440(=24×60)」をかければよいですよね。
操作画面

ね、簡単でしょ?
操作画面




どうでもいいような、よくないような事

最後に少し脱線して
エクセルの日付に関する無駄な話をしましょう。

1901/1/1 は「367」と記録されているという話をしましたが
この「367」のような日付を表す値を「シリアル値」と言います。

このシリアル値、実は「0」からスタートしています。
そのため「0」と入力したセルの表示形式を日付に変更すると
「1900年1月0日」という謎の日付になります。

また、エクセルでの日付のカウントが始まる「1900年」ですが
厳密にはうるう年ではないので2月29日は存在しません。

前項で、あたかも「1901/1/1」は367日目だから
シリアル値は「367」のように書きましたが実は違います。
本来、1901/1/1 は366日目なので「366」となるべきですが
エクセルには幻の 1900/2/29 が存在するため「367」になります。
つまり、1900/3/1 以降はシリアル値=経過日数とはなりません。
図解

「こまけぇこたぁいいんだよ!」にも思えますが
1900/3/1 から 1900/2/28 を引いた結果は「2」となり時空が歪みます。
つまり、時間計算で 1900/2/29 をまたぐと計算が狂い実害が出ます。
気をつけましょう。



エクセルで時間計算をすると
表示形式が思いどおりにならなかったり
訳の分からない数値になったりすることがあるので
苦手に思っている人もいるかもしれません。

日時がどのように記録,計算されるかが分かれば
苦手意識もかなり克服できるはずですよ。



このエントリーをはてなブックマークに追加




管理人だけが読めるコメントにする(返信はしません)
【お知らせ】
  • コメントは承認制です。反映に時間がかかります。
  • スパムコメント防止のため一部ホストを規制中です。
  • URLを含む投稿はできません。必要な場合は最初のhを削って下さい。
  • 記事に直接関係ない個人のトラブルにはお答えできません。
  • Android は使用経験がないので質問を頂いても答えられません。
  • その他詳細はこちら


書式を [h]"時間" にするだけでよかったはずです。
ttp://support.microsoft.com/kb/879837/ja
名無し | 2014/02/15/Sat [EDIT]
おぉ!やっぱり書式でやる方法あったんですね。
(5.8時間みたいな)小数点以下が不要な場合ならその方法が簡単ですね。
勉強になりました、ありがとうございました。
うみの さち | 2014/02/16/Sun [EDIT]



記事別の週間アクセス数ランキングです。こちらの記事もぜひ読んでみて下さい。

2008-2019 知らなきゃ絶対損するPCマル秘ワザ  無断転載禁止

ブログパーツ