わえなび ワード&エクセル問題集

当サイトにリンクを張るのは自由です。WordとExcelの練習問題の動画を「Youtube」で無料公開しています。みんなで勉強しましょう!

【Excel】日付の「シリアル値」を本気で理解するには何を練習するべきか?

「シリアル値とは~~ということで5桁の数字が出てきたら日付に変換しましょうね~」「ふ~ん」

Excelで日付を入力することが多く、ネット上にも日付の説明をしているサイトがたくさんあります。Excelで日付を説明するには「シリアル値」の概念が欠かせませんが、ほとんどのサイトがシリアル値の簡単な説明で終わっていて、シリアル値のことを知ってても知らなくてもいいといったあいまいな説明をしているものまであります。しかし、Excelで日付が使えるようになるには、Excelにたくさんの日付を入力して、「なぜExcelがこのような動きをするのか」を真剣に考えることが大事です。

そこで、日付のことがよく分かっていない人が、どのような練習をすればシリアル値を理解することができるのか徹底的に解説したいと思います。

 

 

目次

1.日付入力の基本練習

(1)Excelが自動的に日付と判断する

問題

Excelに「1日」「1月1日」と入力してオートフィルをしなさい。

解説

1行目に「1日」「1月1日」と入力します。これらをそれぞれオートフィルします。

f:id:waenavi:20190830131254j:plain

 

32行目以降を比較します。A列のほうは32日、33日、・・・となっているのに対して、B列のほうは2月1日、2月2日、・・・となっています。

f:id:waenavi:20190830131336j:plain

 

Excelでは原則として数字と文字列が混ざっている場合、連続データとして扱いますから、32日、33日、・・・となります。しかし、日付とみなせる文字列を入力した時は、自動的に日付と解釈して1月32日、1月33日ではなく、2月1日、2月2日、・・・となります。この時、自動的に日付の表示形式になります。

f:id:waenavi:20190830132342j:plain

 

半角で「1/1」や「1-1」と入力すると「1月1日」も変換されます。Excelが自動的に日付を入力したものと判断して変換します。

f:id:waenavi:20190830132704j:plain

 

日付をオートフィルすると1日ずつ増えますが、単なるコピーをするには「セルのコピー」にします。

f:id:waenavi:20190830132933j:plain

 

(2)月日を入力すると自動的に年が補完される

問題

「2月1日」と入力してオートフィルをしなさい。

f:id:waenavi:20190830130841j:plain

 

解説

1行目に「2月1日」と入力します。これをオートフィルします。

f:id:waenavi:20190830133054j:plain

 

この画面では、2月28日の次が3月1日になっています。これは入力した年がうるう年でなかったためです。

f:id:waenavi:20190830133157j:plain

 

数式バーには「2019/2/28」と表示されています。

f:id:waenavi:20190830133219j:plain

 

もし、2020年のようにうるう年で同じ操作をすると2月29日まで表示されます。数式バーには「2020/2/29」と表示されています。

f:id:waenavi:20190830133328j:plain

 

「2月1日」のように月と日だけを入力した場合、入力した時点の年が補完されます。さきほどの設問で「1日」と入力しましたが、日付と解釈されませんでした。日にちだけ入力しても、年と月は補完されません。

f:id:waenavi:20190830133907j:plain

 

(3)年をまたぐ時は注意せよ

問題

年度末(3月末)の日付を入力しなさい。

解説

年度末は3月31日です。月と日だけを入力すると、入力した年が補完されますから、必ず数式バーを確認します。

f:id:waenavi:20190830134002j:plain

 

例えば、Excelを12月に使っていて、翌年3月の年度末のつもりで「3月31日」と入力しても、それは同じ年の3月31日なので年度末ではありません。

f:id:waenavi:20190830134651j:plain

 

逆に、Excelを1月に使っていて、先月のつもりで「12月31日」と入力しても、それは先月ではなく、同じ年の年末となります。

f:id:waenavi:20190830135030j:plain

 

年をまたいで日付を入力するには、年を省略せずに入力しなければなりません。月日だけを入力した場合は必ず数式バーで確認するようにします。

f:id:waenavi:20190830135157j:plain

 

(4)オートフィルをしたときは翌年になる

問題

「12月30日」と入力してオートフィルをしなさい。

f:id:waenavi:20190830130925j:plain

 

解説

1行目に「12月30日」と入力します。これをオートフィルします。

f:id:waenavi:20190830135548j:plain

 

12月31日の次が、1月1日となりますが、この1月1日は翌年の日付になります。

f:id:waenavi:20190830135623j:plain

 

月と日だけを入力したセルは、自動的に入力した年が補完されますが、オートフィルによって年をまたいだ場合は翌年になります(詳しくは後述)。

f:id:waenavi:20190830135826j:plain

 

(5)いろいろなオートフィル

問題

次のように入力しなさい。

f:id:waenavi:20190830131127j:plain

 

解説

12月21日の次に12月24日と入力します。この2つのセルを選択してオートフィルをすると3日ずつ加算されます。これを繰り返すと翌年の日付になります。

f:id:waenavi:20190830140038j:plain

f:id:waenavi:20190830140054j:plain

 

1月5日の次に1月4日と入力します。この2つのセルを選択してオートフィルをすると1日ずつさかのぼっていき、前年の日付になります。

f:id:waenavi:20190830140145j:plain

 

1月1日の次に2月1日と入力します。この2つのセルを選択してオートフィルをすると1か月ずつ増えていきます。

f:id:waenavi:20190830140233j:plain

 

2.「2020/1/25」と「43855」は同じ

(1)シリアル値と日付の表示は切り替えができる 

問題

「43855」と入力して、表示形式をyyyy/m/dの日付の形式にしなさい。また、標準に戻しなさい。

f:id:waenavi:20190830140338j:plain

 

解説

「43855」と入力します。これは単なる数値です。数式バーも43855です。

f:id:waenavi:20190830140338j:plain

 

これを日付の表示形式(短い日付形式)にします。「2020/1/25」となります。数式バーも2020/1/25です。

f:id:waenavi:20190830140549j:plain

 

Excelは1900年1月1日を基準として日付を管理しています。Excelで数値を入力して、日付の表示形式にすると1900年1月1日を1日目とする日付に変換されます。これを「シリアル値」といいます。

f:id:waenavi:20190830141848j:plain

 

1900年1月1日を1日目とすると、2020年1月25日は43855日目にあたります。2020年1月25日のシリアル値は43855です。シリアル値と言えば特別な数のように聞こえますが、単なる整数です。

f:id:waenavi:20190830142144j:plain

 

日付の表示形式が設定されているセルに1と入力すると、1900年1月1日となります。表示形式が日付となっているセルに、数値を入力しても日付の形式になります。

f:id:waenavi:20190830142405j:plain

f:id:waenavi:20190830142426j:plain

 

日付の表示形式を取り消すには、表示形式を「標準」にします。日付の表示形式を標準にすると5桁の数値になります。

f:id:waenavi:20190830142526j:plain

 

(2)日付を入力するとシリアル値が保存される

問題

「2020/1/25」と入力してオートフィルしなさい。また、表示形式を標準にしなさい。

f:id:waenavi:20190901042337j:plain

 

解説

「2020/1/25」と入力してオートフィルをします。年月日を「yyyy/m/d」の形式で入力すると、自動的に日付と判断され、表示形式が日付となります。

f:id:waenavi:20190830142637j:plain

 

ところで、Excelのセルは、データと書式の2つの情報が保存されています(詳しくは、【Excel】セルにはデータと書式の2つの情報が別々に保存されている)。日付を入力すると、データとしてシリアル値が保存されます。

f:id:waenavi:20190830143818j:plain

 

例えば、2020/1/25と入力するとセルには43855が保存されます。しかし、セルにも数式バーにも「2020/1/25」と表示されます。それはセルの表示形式やWindowsの設定によって「yyyy/m/d」の形式で表示するきまりになっているからです。Excelに「2020/1/25」と入力するのと「43855」と入力するのは、保存されるデータは全く同じです。書式(表示形式)によってセルの表示を切り替えているだけです。

f:id:waenavi:20190830143651j:plain

 

表示形式を標準にすると連番になります。

f:id:waenavi:20190830144304j:plain

 

3.シリアル値を用いて説明せよ

問題

オートフィルをしたときに「3月31日」の次が「4月1日」となる理由を述べなさい。また、「12月31日」の次が、翌年の「1月1日」となる理由を述べなさい。

f:id:waenavi:20190830144600j:plain

 

解説

3月31日という情報だけでは西暦何年かが分からないので、1900年1月1日からの経過日数(シリアル値)を計算することができません。そこで、Excelで月と日だけを入力した場合、入力した時点の年を補完します。

f:id:waenavi:20190830144818j:plain

 

これによって、シリアル値を計算することができ、セルにシリアル値が保存されます。オートフィルをすると、そのシリアル値に1が加算されるため、翌日の日付が表示されます。4月1日になるのは1900年1月1日からの経過日数に1を足しているからです。

f:id:waenavi:20190830144934j:plain

 

Excelで「1月1日」と入力しても翌年の日付にはなりません。入力した時点の年が補完されるからです。

f:id:waenavi:20190830145247j:plain

 

しかし、12月31日を入力したあとで、オートフィルをするとシリアル値に1が加算されるので翌年の日付になります。日付を入力するときには、1900年1月1日からの経過日数を入力していることを意識します。

f:id:waenavi:20190830145346j:plain

 

4.同じ月日なのにシリアル値が違う

問題

「2月28日」と入力してオートフィルをしたとき、次の日付は何月何日になるか。

f:id:waenavi:20190830145509j:plain

 

解説

例えば、Excelで入力しているのが2020年の場合、2020年2月28日とみなされます。このセルにはシリアル値43889が保存されます。オートフィルをするとシリアル値が加算されます。うるう年なのでその翌日は2月29日です。シリアル値は43890です。

f:id:waenavi:20190830145707j:plain

 

しかし、2021年に入力した場合、2021年2月28日とみなされます。このセルにはシリアル値44255が保存されます。うるう年ではないのでその翌日は3月1日です。シリアル値は44256です。

f:id:waenavi:20190830145744j:plain

 

このように、画面上、同じ日付が表示されているように見えても、保存されているシリアル値が異なることがありますので注意しなければなりません。

f:id:waenavi:20190830150051j:plain

 

5.シリアル値か文字列か

(1)日付と判定される形式

問題

「43855」「20200125」「2020年の1月25日」「平成10年10月10日」と入力して、表示形式をyyyy/m/dの日付の形式にしなさい。また、表示形式を和暦の日付にしなさい。

f:id:waenavi:20190830150249j:plain

 

解説

「43855」の表示形式を日付にすると「2020/1/25」となります。43855日目にあたる日付が表示されます。

f:id:waenavi:20190830150517j:plain

 

「20200125」の表示形式を日付にするとエラーとなり、大きすぎる日付は####となります。1900年1月1日を1日目とすると20200125日目は西暦57207年となりExcelの計算の限界である9999年を超えてしまいます。8桁の数値はシリアル値ではなく、日付でもありません。

f:id:waenavi:20190830150621j:plain

 

「2020年1月25日」「2020-1-25」「2020/01/25」など決まった形式で入力すれば、Excelが日付であると認識して自動的にシリアル値が保存されますが、それ以外の形式の場合、単なる文字列として認識されシリアル値にはなりません。したがって、日付の表示形式にしても「2020/1/25」にはなりません。

f:id:waenavi:20190830150720j:plain

 

和暦の日付を入力すると右揃えになります。和暦の日付を入力してもシリアル値が保存されます。シリアル値として保存される場合、入力した時に右揃えになります。セルの表示は和暦ですが、数式バーは西暦です。セルに保存されている値はシリアル値です。

f:id:waenavi:20190830150928j:plain

 

今度は、セルの書式設定で、和暦の表示形式にします。

f:id:waenavi:20190830151042j:plain

 

シリアル値が保存されていれば和暦の日付も計算されますが、単なる文字列の場合、和暦にはなりません。このようにセルに入力されているデータがシリアル値か、単なる文字列かによってExcelの動作が異なりますので、注意が必要です。

f:id:waenavi:20190830151203j:plain

 

(2)文字列として入力した日付

問題

表示形式を文字列にして「1/25」と入力してから、表示形式を標準にするとシリアル値として保存されるか。

f:id:waenavi:20190830151325j:plain

 

解説

表示形式が標準の場合、「1/25」と入力すると入力した年が補完され、日付の表示形式になります。このセルにはシリアル値が保存されています。

f:id:waenavi:20190830151415j:plain

 

今度は、表示形式を文字列にします。「1/25」と入力しても入力した年は補完されません。そして、表示形式を標準にしても入力した年が補完されず、日付の表示形式にもなりません。文字列として入力したものはシリアル値ではありません。

f:id:waenavi:20190830151439j:plain

 

そのあとで表示形式を標準にしてもシリアル値にはなりません。

f:id:waenavi:20190830151527j:plain

 

ダブルクリックして、確定すると日付として認識されます。

f:id:waenavi:20190830151624j:plain

 

6.年・月・日を非表示にする

問題

(1)「2020年4月10日」と入力して「yyyy年m月」の表示形式にしなさい。また、「m/d」の表示形式にしなさい。

(2)「西暦2020年4月1日」と入力して「m/d」の表示形式にすることはできるか。

解説

「2020年4月10日」と入力すると右揃えになり、数式バーに2020/4/10と表示されます。これは日付として認識されたことを意味しています。

f:id:waenavi:20190830151923j:plain

 

セルの書式設定によって、表示形式を「yyyy年m月」にします。

f:id:waenavi:20190830152013j:plain

 

このように表示形式によって日にちの部分を消すことができますが、数式バーには残っています。これはセルにシリアル値が保存されているからです。

f:id:waenavi:20190830152117j:plain

 

次に、セルの書式設定によって、表示形式を「m/d」にします。

f:id:waenavi:20190830152217j:plain

 

表示形式で年の部分を消すことができますが、数式バーには残っています。このように表示の形式をいろいろと変えられるのは、入力されているデータがシリアル値だからです。

f:id:waenavi:20190830152304j:plain

 

「西暦2020年4月1日」と入力しても右揃えにはなりません。西暦を付けてしまうと文字列として認識されるためシリアル値にはなりません。

f:id:waenavi:20190830152347j:plain

 

シリアル値ではないので、表示形式を変えてもセルの表示は変わりません。

f:id:waenavi:20190830152433j:plain

 

7.日付文字列をシリアル値にする

(1)日付文字列に1を掛ける

問題

="1/25"」という数式を入力した場合の計算結果はシリアル値と言えるか。また、シリアル値に変換して「yyyy年m月」の表示形式にしなさい。

f:id:waenavi:20190830152608j:plain

 

解説

数式として「=1/25」と入力すると割り算になってしまい、計算結果は0.04となります。これは1月25日とは無関係です。

f:id:waenavi:20190830153005j:plain

 

そこで、ダブルクォーテーションで囲みます。計算式でダブルクォーテーションで囲んだものは文字列として扱われます。文字列として入力されたものはシリアル値ではありません。したがって、この状態で表示形式を変えても、セルの表示は変わりません。

f:id:waenavi:20190830153113j:plain

 

文字列として入力された日付をシリアル値にするには、足し算・引き算・掛け算・割り算の四則演算が必要です。最も簡単な方法は1を掛けることです。

f:id:waenavi:20190830153327j:plain

 

1を掛けると5桁の数になります。年が省略されている場合は現時点での年を補ったうえでシリアル値を計算します。なお、これは数式なので再計算されます。つまり、年が変わったら補完される年も変わりシリアル値も変わります。

f:id:waenavi:20190830153457j:plain

 

これはシリアル値なので「yyyy年m月」の表示形式にすることができます。

f:id:waenavi:20190830153556j:plain

 

別解

先頭にマイナスを2つ付ける方法もあります。マイナスのマイナスで、プラスになります。他にも0を足すとか、0を引くとかいろいろありますが、要するに四則演算があればよいです。

f:id:waenavi:20190830154626j:plain

 

(2)月・日を分けて入力した場合

問題

月・日を分けて入力した日付はシリアル値と言えるか。また、シリアル値にして「yyyy年m月」の表示形式にしなさい。

f:id:waenavi:20190830152743j:plain

 

解説

人間がこの表を見ると1月25日を表していることは分かりますが、Excelは2つの数字が並んでいるだけにしか見えません。月・日を分けて入力した場合、日付として認識されません。

f:id:waenavi:20190830154740j:plain

 

そこで、スラッシュを入れて連結します。「=A2&"/"&B2」と入力します。

f:id:waenavi:20190830154853j:plain

 

連結しただけの文字列は「文字列」です。文字列はシリアル値ではありません。

f:id:waenavi:20190830155104j:plain

 

1を掛けると5桁の数になります。年が省略されている場合は現時点での年を補ったうえでシリアル値を計算します。ただし、これは数式なので再計算されます。つまり、年が変わったら補完される年も変わりシリアル値も変わります。

f:id:waenavi:20190830155230j:plain

 

「yyyy年m月」の表示形式にすることもできます。

f:id:waenavi:20190830155302j:plain

 

別解

Excelで日付として認識される文字列はスラッシュだけではありません。例えば、ハイフンでもよいです。

f:id:waenavi:20190830155353j:plain

 

この場合も単に連結しただけではシリアル値になりませんので、1を掛けます。

f:id:waenavi:20190830155413j:plain

 

(3)年・月・日を分けて入力した場合

問題

1~10の数値を和暦にすることは可能か。また、年・月・日を用いてシリアル値を求め、和暦の日付の表示にしなさい。

f:id:waenavi:20190830152923j:plain

 

解説

日にちとして1~10の数値を入力したとします。

f:id:waenavi:20190830155459j:plain

 

これを和暦に変換すると明治33年になります。西暦1900年のことです。

f:id:waenavi:20190830155531j:plain

 

表示形式を標準に戻します。

f:id:waenavi:20190830155459j:plain

 

そこで、スラッシュを入れて連結します。年と月は絶対参照です。

f:id:waenavi:20190830155805j:plain

 

連結しただけの文字列は「文字列」であり、シリアル値ではありません。文字列のままでは和暦になりません。1を掛けるとシリアル値になります。

f:id:waenavi:20190830155906j:plain

 

和暦の表示形式にすることも可能です。

f:id:waenavi:20190830155940j:plain

 

8.和暦の年月日を連結してシリアル値にする

問題

次の各設問について、平成25年2月5日のシリアル値を求め、西暦の日付にしなさい。

(1)和暦年2桁・月・日を入力した
(2)元号をT・S・H・Rで入力し、和暦年2桁と月を入力した
(3)元号を漢字で入力し、和暦年2桁・月・日を入力した

f:id:waenavi:20190830160124j:plain

 

解説

2桁の年の場合、西暦の下2桁なのか和暦の2桁なのかが分かりません。先頭に「H」を連結します。さらに1を掛けてシリアル値にします。

f:id:waenavi:20190830160221j:plain

 

イコールで参照します。

f:id:waenavi:20190830160320j:plain

 

西暦の日付にすることもできます。

f:id:waenavi:20190830160421j:plain

 

元号をセルに入力した場合はそれを参照します。日にちが無ければシリアル値を計算できないので、「/5」とします。さらに1を掛けてシリアル値にします。

f:id:waenavi:20190830160517j:plain

 

これで昭和でも令和でも計算できます。

f:id:waenavi:20190830160557j:plain

f:id:waenavi:20190830160559j:plain

 

元号が漢字の場合、「ggge年m月d日」の形式にします。

f:id:waenavi:20190830160627j:plain

 

9.ユーザー定義の書式設定

(1)ユーザー定義

問題

「2023/2/25」にセルA3~A13の表示形式を設定するとどのような表示になるか。

f:id:waenavi:20190830160831j:plain

 

解説

セルA1を絶対参照で参照します。

f:id:waenavi:20190830161018j:plain

 

表示形式を標準にするとシリアル値になります。セルに保存されている値が44982であることが分かります。

f:id:waenavi:20190830161058j:plain

 

表示形式をユーザー定義にして、「yyyy」にすると2023だけになります。2023/2/23の年の部分だけを取り出したものですが、月日の情報が消えたわけではありません。セルの表示は2023ですが、保存されている値は44982です。セルの表示と保存されている値が異なることに注意します。

f:id:waenavi:20190830161341j:plain

 

「m」にすると月だけになりますが、年と日にちを非表示にしただけで情報が消えたわけではありません。セルに保存されている値は44982です。「d」にすると日にちだけになります。

f:id:waenavi:20190830161343j:plain

f:id:waenavi:20190830161346j:plain

 

西暦年4桁と月2桁をつなげて6桁にするには「yyyymm」とします。「yyyymmdd」で8桁になります。6桁や8桁の数値のように見えますが実際に保存されている値は5桁の整数(シリアル値)です。

f:id:waenavi:20190830161729j:plain

f:id:waenavi:20190830161732j:plain

 

和暦の場合はgggが元号で、eが年です。

f:id:waenavi:20190830161735j:plain

 

「geemmdd」で7桁になります。

f:id:waenavi:20190830161738j:plain

 

曜日はaで表します。aが3つで「土」、4つで土曜日になります。セルには日本語が表示されていますが、セルには日付(シリアル値44982)が保存されていることに注意します。

f:id:waenavi:20190830161741j:plain

f:id:waenavi:20190830161745j:plain

 

これらを組み合わせて「yyyy年m月d日(aaa)」とすると2023年2月25日(土)となります。 

f:id:waenavi:20190830161749j:plain

 

(2)セルの表示と保存されている値が異なることがある

問題

「125」「土」と表示されているセルは、日付(シリアル値)ではないと判断してよいか。

f:id:waenavi:20190830160910j:plain

 

解説

125と直接入力すると、セルにも125が保存されます。

f:id:waenavi:20190830161957j:plain

 

しかし、セルに125と表示されているかと言って必ずしもその数値が保存されているとは限りません。必ず数式バーを確認します。2020/1/25という日付になっています。

f:id:waenavi:20190830162042j:plain

 

表示形式を見ると「md」となっています。

f:id:waenavi:20190830162125j:plain

 

表示形式を標準にするとシリアル値になります。このように、セルに表示されている数と実際にセルに保存されている値が異なることがありますので注意が必要です。

f:id:waenavi:20190830162158j:plain

 

また、「土」を直接入力すると、日本語としての「土」が保存されます。

f:id:waenavi:20190830162232j:plain

 

しかし、数式バーが日付になっていれば日本語としての「土」ではなく、表示形式が「aaa」になっている日付(シリアル値)です。

f:id:waenavi:20190830162339j:plain

 

10.曜日を直接入力してはいけない

問題

A列に2021年1月1日~10日を入力した。

f:id:waenavi:20190830162508j:plain

 

A列を日にちだけにして、B列に曜日を表示しなさい。

f:id:waenavi:20190830162556j:plain

 

解説

曜日を表示するには日付(シリアル値)が必要です。そこでA列をイコールで参照します。

f:id:waenavi:20190830162844j:plain

 

A列を選択します。表示形式を「d」にします。これで日にちだけになりましたが、年と月が消えたわけではなく、2021年1月はセルに保存されています。

f:id:waenavi:20190830162953j:plain

 

B列を選択します。表示形式を「aaa」にします。曜日になります。

f:id:waenavi:20190830163047j:plain

 

11.年と月が変われば曜日も自動的に変わる

問題

セルA1の表示形式を文字列にして「2021年4月」と入力した。そして、4行目以降に1~30の連番を入力した。

f:id:waenavi:20190830163426j:plain

 

2021年4月1日~30日の曜日を表示しなさい。また、2022年11月1日~30日の曜日にしなさい。

f:id:waenavi:20190830163456j:plain

 

解説

まず、セルA1の表示形式を文字列にして「2021年4月」と入力します。

f:id:waenavi:20190830163610j:plain

 

1~30の連番を入力していますが、これは単なる数値であり、日付でもシリアル値でもありません。

f:id:waenavi:20190830163708j:plain

 

そこで、2021年4月1日のシリアル値を求めます。2021年4月を絶対参照にして、日にちと「日」を連結して2021年4月1日とします。さらに1を掛けます。

f:id:waenavi:20190830163817j:plain

 

これを日付の表示形式にすれば日付になりますが、ユーザー定義でaaaにすれば曜日だけになります。

f:id:waenavi:20190830163906j:plain

 

オートフィルをします。コピーします。これで30日までの曜日になります。

f:id:waenavi:20190830163944j:plain

 

2022年11月にするとシリアル値も変わりますから、自動的に対応する曜日になります。

f:id:waenavi:20190830164042j:plain

 


解説は以上です。「日付の足し算・引き算」の記事へつづく・・・


 

 


Copyright(C)2018-2019 waenavi, All rights reserved. [www.waenavi.com 定礎 平成三十年八月]