Excel講座

日付・時間

本章で用いる実習用ファイル

excelDateTime

Excelは日付・時刻をシリアル値という数値データとして扱う。このシリアル値は1900/1/0を0とした連番である。

1日が「1」なので、時間(時:分:秒)は小数となる。

1日…「1」
1時間…「1/24」
1分…「1/24/60」
1秒…「1/24/60/60」

	A	B
1	単位	シリアル値
2	1日	1
4	1時間	0.041666667
5	1分	0.000694444
6	1秒	1.16E-05
7
8	今日の日付	シリアル値
9	2024/5/19	45431
10

これでは人間には訳が分からないので、数値の書式で人間に分かるように表示させる。

日付

表示形式

西暦年

yyyy…4桁(ex. 1995)
yy…2桁(ex. 95)

元号

ggg…漢字(ex. 平成)
gg…漢字1字(ex. 平)
g…英字1字(ex. H)

元号年

ee…必ず2桁で表示(ex. 07)
e…1桁表示あり(ex. 7)

月(数字)

mm…必ず2桁で表示(ex. 07)
m…1桁表示あり(ex. 7)

月(英字)

mmm…英字3字(ex. Jul)
mmmm…英字(ex. July)
mmmmm…英字1字(ex. J)

日

dd…2桁の日(ex. 03)
d…1桁の日(ex. 3)

曜日

ddd…英字3字(ex. Sun)
dddd…英字(ex. Sunday)
aaa…漢字1字(ex. 日)
aaaa…漢字(ex. 日曜日)

課題:表示設定(形式)

シリアル値が表示例になるように書式を設定せよ。

	A	B
1	シリアル値	36772
2
3	表示例1	2000/09/03
4	表示例2	9月3日(日)
5	表示例3	平成12年9月3日日曜日
6

日数計算

2000/9/3を基点として日数計算をする。

日数計算
	A	B	C
1	経過日数	シリアル値	日付表示
2	0	36772	2000/9/3
3	35	=$B$2 + A3	=C3
4	-365	=$B$2 + A4	=C4
5	-40000	=$B$2 + A5	=C5
6	8659	=$B$2 + A6	=C6
7

日付は高々連番の数値なので、足し算も引き算も普通にできる。計算した結果の表示書式を日付形式にすると当然月・年も跨げている。閏年の考慮もしてくれる(2000年は閏年)。

表示
	A	B	C
1	経過日数	シリアル値	日付表示
2	0	36772	2000/9/3
3	35	36807	2000/10/8
4	-365	36407	1999/9/4
5	-40000	-3228	#########
6	8659	45431	2024/5/19
7

負の値は日付とは扱えない(セルC5の表示が「########」になる)。つまりExcelでは1900/1/1以前は日付計算できない。

課題:日数計算(形式)

各メンバーの誕生日から今日(2024/5/19)までの日数を計算して、表を完成させよ。

生まれてからの日数
	A	B	C
1		2024/5/19	現在
2
3		誕生日	日数
4	小片	1998/11/5
5	山岸	1998/11/24	155405
6	新沼	1999/10/20	155075
7	谷本	1999/11/16	155048
8	岸本	2000/4/1	154911
9	浅倉	2000/9/3	154756
10	小野	2000/9/29	154730
11	小野田	2001/12/17	154286
12	秋山	2002/7/29	154062
13

曜日

日付(シリアル値)から曜日を表示するだけなら書式設定で行う。

曜日
	A	B	C	D	E
1	日付	曜日	数値	曜日表示	イベント
2	2024/5/1	=A2	=MOD(A2,7)	=MOD(A2,7)	=IF(D2=1,"休み","")
3	2024/5/2	木	5	木
4	2024/5/3	金	6	金
7	2024/5/4	土	0	土
5	2024/5/5	日	1	日	休み
6	2024/5/6	月	2	月
7	2024/5/7	火	3	火
8	2024/5/8	水	4	水
9	2024/5/9	木	5	木
10

毎日曜日に「休み」と表示したいとする。このとき、B列を参照して=IF(B2="日","休み","")と式を立てても上手くいかない。曜日固有の値を取得したいときは7で除した余り(MOD)を用いる。日曜日なら「1」となる。

日付関数

シート操作したときの日付:TODAY()

TODAY関数は現在の日付をシリアル値で返す(引数はない)。

シートに何らかの操作をしたときに再計算され、そのときの日付となる。入力した翌日にファイルを開くと、翌日の日付となる。

	A	B	C	D
1	記入日	=TODAY()
2
3	品目	単価	数量	小計
4
5
6

TODAY関数は記入した日付を記録するための関数ではない(随時更新されるから)。

実用的な使い方としては、他項目を記入完了後、そのまま印刷ないしPDF化すると出力先には記入日が記録される。またシート上で値を随時更新するのではなく、シート記入日を記録したいときには「=TODAY()」入力後、Enterキーを押す代わりにF9キーを押す。すると関数ではなく、そのときの日付のシリアル値が入力される。

TODAY関数の効果的な使い方としては、常に最新の年齢をデータとして表示する、など動的なデータ更新を行うケースである。

年・月・日を実数で返す:YEAR(シリアル値), MONTH(シリアル値), DAY(シリアル値)

YEAR関数はシリアル値から「年」を数値として返す関数である。
MONTH関数はシリアル値から「月」を数値として返す関数である。
DAY関数はシリアル値から「日」を数値として返す関数である。

	A	B
1	今日	2024/5/19
2
3	年	=YEAR(B1)
4	月	=MONTH(B1)
5	日	=DAY(B1)
6

年月日からシリアル値を返す:DATE(年,月,日)

DATE関数は年,月,日を指定すると、その日付を表すシリアル値を返す関数である。

	A	B
1	シリアル値	=DATE(B3, B4, B5)
2
3	年	2024
4	月	5
5	日	19
6

課題:貸出期間(形式)

指定された貸出期間に従って返却期日を表示する表を完成させよ。なお貸出期間の数字が変更されれば、返却期日も変化するようにせよ。

	A	B	C
1	貸出日		2024/5/19
2
3	貸出期間		返却期日
4	2	週間
5	3	ヶ月
6	1	年
7

時間

表示形式

時間(h):分(m):秒:(s)の組み合わせで設定する。

hh, mm, ssなら必ず2桁で表示(ex.01:05:06)
h, m, sなら1桁表示あり(ex.1:5:6)
[] ([hh][h][mm][m][ss][s])で括ると24時間・60分・60秒を超えた時間を表示

実用的な組み合わせ例(25時間3分9秒)

hh:mm:ss→01:03:09
hh:mm→01:03
h:mm:ss→1:03:09
[hh]:mm:ss→25:03:09

時間計算

日付と異なり、時間(時:分:秒)は全て固定値で換算できるので、四則演算だけでできる。

1日…「1」
1時間…「1/24」
1分…「1/24/60」
1秒…「1/24/60/60」

東海道新幹線の駅間所要時間
	A	B	C	D
1	駅名	時刻	所要時間	分
2	京都発	7:59
3	名古屋	8:56	=B3 - B2	=C22460
4	浜松	9:44	0:48	48
5	静岡	10:19	0:35	35
6	新横浜	11:28	1:09	69
7	品川	11:40	0:12	12
8	東京	11:47	0:07	7
9

課題:実働時間(形式)

始業から終業までの時間から休憩時間を除いた実働時間(実数)に時給を掛けて一日の支給額が決まる。

表より、実働時間と支給額の計算をせよ。

5/19(日)分バイト実績
	A	B
1	始業	9:25
2	終業	17:49
3	休憩(分)	55
4	実働
5	時給	1050
6	支給額
7

課題:年齢計算(形式)

各メンバーの誕生日から今日(5/19)現在の年齢を計算して、表を完成させよ。

誕生日
	A	B	C
1			現在
2
3		誕生日	年齢
4	小片	1998/11/5
5	山岸	1998/11/24	25
6	新沼	1999/10/20	24
7	谷本	1999/11/16	24
8	岸本	2000/4/1	24
9	浅倉	2000/9/3	23
10	小野	2000/9/29	23
11	小野田	2001/12/17	22
12	秋山	2002/7/29	21
13

年だけの引き算では、その年の誕生日前なのかあとなのかが分からない。誕生日前後で年齢を変えるために、以下の計算をする。

日付を8桁の数値に変換する。
- 2024/5/19→20240519
- 1998/11/5→19981105
二つの数値の引き算をする。
- 20240519 - 19981105
  →259414
10,000で割り、小数点以下を切り捨てる。
- 25

課題:給与計算(形式)

以下の条件にしたがって、表を完成させ、給与支払額を計算せよ。

条件

「始業時刻」から「退社時刻」までの時間をから昼食休憩時間を除いた時間を「勤務時間」とする。
「始業時刻」以前に出社しても「勤務時間」には含まれない。
終業時刻以降の勤務は「残業時間」とする。
「正規時間」とは「勤務時間」と「残業時間」の差である。
出社していない日の勤務時間欄などは空白にする。
土曜日、日曜日の支払額は平日の5割増し。
支給額の小数点以下は日ごとに切り捨て。

	A	B	C	D	E	F	G	H
1	始業時刻	9:00
2	終業時刻	17:45
3	休憩時間(分)	45
4	正規時給	2200
5	残業時給	2970
6
7	日付	曜日	出社時刻	退社時刻	勤務時間	正規時間	残業時間	支給額
8	11月1日
9	11月2日	土	8:55	16:35	6:50	6:50	0:00	22550
10	11月3日	日	9:35	19:20	9:00	7:25	1:35	31528
11	11月4日	月	9:00	17:45	8:00	8:00	0:00	17600
12	11月5日	火	9:20	18:50	8:45	7:40	1:05	20084
13	11月6日	水	8:55	19:20	9:35	8:00	1:35	22302
14	11月7日	木
15	11月8日	金	9:30	17:00	6:45	6:45	0:00	14850
16	11月9日	土	8:55	15:00	5:15	5:15	0:00	17325
17	11月10日	日
18	11月11日	月	10:00	17:30	6:45	6:45	0:00	14850
19	11月12日	火
20	11月13日	水	11:00	18:45	7:00	6:00	1:00	16170
21	11月14日	木	8:50	20:00	10:15	8:00	2:15	24282
22	11月15日	金
23	11月16日	土
24	11月17日	日	10:00	17:45	7:00	7:00	0:00	23100
25	11月18日	月	9:00	17:45	8:00	8:00	0:00	17600
26	11月19日	火	8:45	15:30	5:45	5:45	0:00	12650
27	11月20日	水
28	11月21日	木
29	11月22日	金	11:00	15:00	3:15	3:15	0:00	7150
30	11月23日	土	9:00	17:45	8:00	8:00	0:00	26400
31	11月24日	日	9:00	18:45	9:00	8:00	1:00	30855
32	11月25日	月
33	11月26日	火
34	11月27日	水	8:50	19:00	9:15	8:00	1:15	21312
35	11月28日	木	8:50	17:00	7:15	7:15	1:00	15950
36	11月29日	金
37	11月30日	土	9:00	18:50	9:05	8:00	1:05	31226
38	合計				144:45	133:55	10:50	387784
39

課題:万年カレンダー(形式)

万年カレンダーを作成せよ。

セルB1に年、セルD2に月を入力すると、指定された年月のカレンダーを生成する。

	A	B	C	D	E	F	G
1		2024	年	5	月
2
3	日	月	火	水	木	金	土
4				1	2	3	4
5	5	6	7	8	9	10	11
6	12	13	14	15	16	17	18
7	19	20	21	22	23	24	25
8	26	27	28	29	30	31
9
10

	A	B	C	D	E	F	G
1		2024	年	6	月
2
3	日	月	火	水	木	金	土
4							1
5	2	3	4	5	6	7	8
6	9	10	11	12	13	14	15
7	16	17	18	19	20	21	22
8	23	24	25	26	27	28	29
9	30
10

セルに出力するのはシリアル値

セルにはシリアル値で日付を出力する。

表示形式で「日」のみを表示する。

最初のセル(A4)

[指定年月初日のシリアル値]－[指定年月初日の前日の曜日番号(シリアル値を7で割った余り)]

以降のセル

前のセルに数字を1ずつ加えていく

当月以外のセルを空白にする(発展)

余力があれば…。

セルにブランク("")を出力するのではなく、値(シリアル値)を出力した上で表示設定で空白に見えるようにする。その際「条件付き書式」を用いる。

当月初日より前
または
当月の翌月初日以降

なら、文字は白(背景と同じ色)