日付と時刻はシリアル値で出来ている！！　上手に付き合えばExcelの真の力が見えてくる！！

《実務で使えるスキル講座　第5弾》

今回は、「シリアル値」についてのお話です。

次回の第4弾の話を読みたい場合は、下のリンクをクリックして下さい

仕事効率3倍も夢じゃない！！INDIRECT関数で作る超便利な可変リストの作り方

ところでシリアル値って何でしょう？ シリアルっていうんだから、「コーン〇〇ーク」に関するものでしょうか。

それとも「血糖値」とか「経験値」・「偏差値」のような小難しい値(あたい)のことかもしれません。

実は、この「シリアル値」今あげたものとは何も関係ありません！！

一見おいしそうですが、実はこの値。

エクセルでは、すごく重要なものなんです。

いったい何が重要なのか？？　謎の多い値ですがシリアル値の招待について見てきましょう。

それじゃあ、本編へGO！！

※この記事は、Excel2016にて解説しています。

シリアル値って何？

『シリアル値』とは、「日付や時刻を数字(値)」で管理するための「ルール」のことです。

これだけじゃピンときませんよね。

それじゃあ、実際にExcelは、日付や時間をどうやって管理しているか見てみましょう。

こんな風に日付を入力しても、Excelでは「1」とか、「43301」という数字で管理しているんです。

時間も同じように「0」とか「0.833・・・・」という具合に小数点を使って管理していますね。

こんな風にExcelは、よく分からないルールで日付や時間を管理しています。

じゃあ、どんなルールになっているのか。まず知ってみましょう。

ルール1：日付のシリアル値は、1900年1月1日を「１」とするよ。

このルールに従って日付をシリアル値にすると、1900年1月2日は「２」・1900年1月3日は「3」というように1日ごとに1を足して管理します。

ちなみに、「2018/07/24」は、「43305」です。

分かりやすくすると、1900年1月1日から「43,305日」経過した日ですよ。ということですね。

これが日付のシリアル値です。まずは、これを抑えておきましょう。

ルール2：時間のシリアル値は「0:00:00」を「0」とするよ。

時間は、1日24時間なので、0:00:00を0として、6:00　→　0.25・12:00　→　0.5・18:00　→　0.75

こんな風に24時間を管理します。

計算式で表すと、6÷24=0.25　12÷24=0.5　18÷24=0.75こんな感じで時間をシリアル値にして管理しています。

じゃあ、20時のシリアル値はいくつでしょう？

答えは、　　20時÷24時間=0.83333333333　ですね。

そして、シリアル値を時間にする場合は、0.5×60分=30分と計算すれば良いんですね。

これが、シリアル値です。

でも、不思議じゃありませんか？

数字で日付や時間を管理してるなら、入力した数字が何の数字なのか分からなくならないのでしょうか？

そう思うのは私だけ？

実は、その数字が何の数字なのかは、あなたが決めることが出来るんですよ。

そんなのどうやって決めるの？　と思うかもしれませんが、実はすっごく簡単なので見てみましょう。

こんな風に表示したいスタイルをボタンで選べばOKです。簡単でしょ。

この同じ数字でも「表示するスタイルを変えれば色々な表示に変えることができる」仕組みは、Excelではとっても重要なので、ぜひ使い慣れておきましょう。

Excelの最大のメリットは、複雑な計算でもコツさえつかめば簡単・正確・高速に処理してくれるところです。

このシリアル値と「数値の書式」は、そのメリットをめっちゃ活かせる知識なので押さえちゃいましょう。

どうしてExcelはシリアル値で日付や時間を管理するの？

シリアル値ってただの数字なので、人間にとってはすごく分かりにくいですよね。

どうしてわざわざ分かりにくいシリアル値でExcelは日付や時間を管理しているんでしょうか？

答えは、『日付や時間を計算する時に便利』だからです。

これだけだとピンときませんよね。では、こんな時はどうでしょうか。

『勤続年数や年齢を自動で計算させたい！！』

これ、実務でよくあるケースです。

会社では、社員名簿の作成やハローワークなどの公共機関・その他の各書類作成に必要になることが多々あります。

こういう場合。勤続年数や年齢は常に変わるので一人一人計算し直していては大変ですよね。

人が多くなればなるほど、作業量は増えるのでいちいち計算するなんてゾッとします。

それに、人はミスをしますよね。

そんな時に役立つのがこのシリアル値です。

シリアル値の正体は数字です。数字は計算に使えますね。

計算した結果の数字は、表示方法を変えれば「日付や時間」にすることが出来ます。

ここがポイントです！！

では、実際に日付を例にして、シリアル値のメリットを見てみましょう。

年齢も勤続年数もこんな風に計算することが出来ます。

今回は、どちらも「DATEDIF関数」で求めることが出来ます。

DATEDIF(デイト・ディフ)関数は、=DATEDIF(開始日,終了日,単位)と設定します。

この関数は、2つの日付の期間を求める関数で「単位」に求めたい「年月日」のいずれかを指定します。

今回の年齢の計算の場合は、=DATEDIF(B2,B3,”Y”)としたので、答えは「34」という数字を表示してくれます。

この式を日本語にすると、=DATEDIF(B2のセルと、B3のセルの期間の、年数を表示して)

そうすると、答えである「34(歳)」という数を表示してくれます。

あとは、この数字を「数字+歳」という表示にしてね。

と設定してあげることで、今日の日付がいつになっても自動的に計算してくれるんです。

勤続年数も同じ関数と設定を使って年数を表示しています。

では、時間の場合はどうでしょうか？

まず、簡単な出勤簿を用意しました。期間は2018年1月1日～2018年1月31日の1カ月です。

休みだった日は、空白にしています。

▼出勤簿の前半部分です。

▼出勤簿の後半部分です。

では、1カ月の出勤時間を求めてみましょう。

1カ月の出勤時間の式は、=SUM(E2:E32)です。つまり1日から31日までの労働時間を足してね。と設定してるだけです。

あとは、時間を24時間以上でもきちんと表示されるように表示の方法を工夫するだけです。

つまり、日付でも時間でも計算することを前提としているので、シリアル値で管理するというのは理にかなっていると言えるんです。

Excelが日付と時間を表示方法で切り替える理由と表示方法について知っておくことがとっても大事！！

Excelが日付と時間を「シリアル値」で管理している理由は分かってもらえましたか？

じゃあ、もしExcelが日付や時刻を文字として計算したらどうなるでしょうか？

十中八九、こうするでしょう。

例えば、年齢の計算をしてみましょう。

生年月日：1984/1/25　2018/7/1現在で何歳ですか？

【計算方法】

まず、年数だけ求めればいいので、月と日にちは無視して年数の差を計算します。

そうすると、2018ー1984なので、答えは「34」です。

一見そんなに難しそうに思えませんが、Excelにこういう考え方は出来ません。

なぜなら、「Excelに文章の内容を理解して答えを出す」ということは出来ないからです。基本的にExcelが出来ることは、数字を計算することだけです。

だから、Excelが理解できる数字を人間用に表示方法を変えてあげることで、『数字だけじゃ何の数字か分からない問題』を解決しています。これがシリアル値を使う理由ですね。

では、さっきの計算をシリアル値でやるとどうなるでしょう。

シリアル値での式計算方法：43282－30706=12576

これを関数で年数だけ表示するようにすると、12,576÷365=34.454・・・で答えは「34」ですね。

これで、Excelでも数字と計算だけで、年数を求められます。

こんな具合にExcelに合わせてあげる必要があるんですね。

でも、実はですね。衝撃の事実があります！！

それは・・・　別にここまでのことを理解する必要はありません！！！！

はい。ごめんなさい。

さんざん説明しておいてなんですが、Excelを普通に利用する人はここまで知らなくても仕事に支障はありません。

でも、シリアル値について知っておく最大のメリットは、「Excelは文字にすると計算出来ない」ということ

そして、計算結果は必要に応じて表示形式を変えることがすごく重要だということです。

シリアル値のもう一つの重要なポイントを説明します。

それは、『表示形式の設定』です。

表示形式とは、12345という数字を￥12,345と表示させる設定のことです。

実は、Excelにとって「￥」や「,」は文字として扱われます。

だから、シリアル値は「123,456,789」ではなく「123456789」と表示されるんですね。

では、実際に実務的な役立つ表示形式を手順と一緒にいくつか紹介しましょう。

基本的な手順は次の通りです。

▼各設定をするとこんな感じです。

手順は、覚えてしまえば簡単です。

どの設定も「ユーザー定義」で設定することがポイントです。

それぞれの設定をまとめるとこんな感じになります。

それぞれの意味を簡単に説明すると

「#」は、数字を表しています。そして「歳」を「””」で挟んであげると「X歳」と表示してねという意味になります。

Xの部分は入力した数字のことなので、例えば100と入力すると、100歳と表示するという意味です。

そこまで生きているかは、人によりますね。

「年」の場合も意味は同じです。

時間の場合は、[h]:mmですが、hはHour(アワー)、mはMinute(ミニット)で「時」と「分」を意味しています。

hは「[　]」で囲むことで24時間以上の時間表示にする設定です。間に「：」を挟んでmmは、08分というように2ケタで分数を表示してね。という設定です。

この表示形式を設定する最大のメリットは、元の数字を変えずに見た目だけ分かりやすいように表示してくれることです。

何度もしつこく言ってしまいますが、大切なのでもう一度言います。

Excelは文字を計算することはできません。

表示形式は、見た目を変えているだけなので実際に入力されているのは数字です。

だから、計算式にも使えるということは覚えておいて下さいね。

まとめ

今回は、「シリアル値とは何ぞや」というお話でした。

最初は、呪文のような謎の言葉だったと思いますが実はExcelにとっては、超重要な仕組みだということを分かってもらえたらうれしいです。

実務でも、色々なパターンの仕事があります。

特にデスクワークをしていると、数字を扱う事ってかなりあって、それも重要なことを担っていいたりするものです。

面倒な計算や予測。集計やグラフを作ることは仕事をしていると結構ありますよね。

そんな時は、Excelの出番です。

今あげたことは、Excelの標準機能を上手く使ってあげれば、チョチョイのチョイです。

それに加えてマクロ(VBA)を使いこなせれば、仕事が半分寝ていても出来るようになります。

ごめんなさい。言いすぎです。

でもそれくらい使い方によっては便利なツールなんですよ。

まずは、日付と時間を扱うときは「シリアル値」っていう美味しそうな値があって

シリアル値を上手に生かすには「表示形式」の設定がキモなんだということを意識して下さいね。

最後まで読んでいただきありがとうございました。

前回の記事を読む　　　最初から記事を読む