日付・時間
- 本章で用いる実習用ファイル
Excelは日付・時刻をシリアル値という数値データとして扱う。このシリアル値は1900/1/0を0とした連番である。
1日が「1」なので、時間(時:分:秒)は小数となる。
- 1日…「1」
- 1時間…「1/24」
- 1分…「1/24/60」
- 1秒…「1/24/60/60」
A | B | C | |
---|---|---|---|
1 | 単位 | シリアル値 | |
2 | 1日 | 1 | |
4 | 1時間 | 0.041666667 | |
5 | 1分 | 0.000694444 | |
6 | 1秒 | 1.16E-05 | |
7 | |||
8 | 今日の日付 | シリアル値 | |
9 | 2024/10/6 | 45571 | |
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 | C | |
---|---|---|---|
1 | シリアル値 | 36772 | |
2 | |||
3 | 表示例1 | 2000/09/03 | |
4 | 表示例2 | 9月3日(日) | |
5 | 表示例3 | 平成12年9月3日 日曜日 | |
6 |
日数計算
2000/9/3を基点として日数計算をする。
A | B | C | D | |
---|---|---|---|---|
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 | 8799 | =$B$2 + A6 | =C6 | |
7 |
日付は高々連番の数値なので、足し算も引き算も普通にできる。計算した結果の表示書式を日付形式にすると当然月・年も跨げている。閏年の考慮もしてくれる(2000年は閏年)。
A | B | C | D | |
---|---|---|---|---|
1 | 経過日数 | シリアル値 | 日付表示 | |
2 | 0 | 36772 | 2000/9/3 | |
3 | 35 | 36807 | 2000/10/8 | |
4 | -365 | 36407 | 1999/9/4 | |
5 | -40000 | -3228 | ######### | |
6 | 8799 | 45571 | 2024/10/6 | |
7 |
負の値は日付とは扱えない(セルC5の表示が「########」になる)。つまりExcelでは1900/1/1以前は日付計算できない。
課題:日数計算(形式)
各メンバーの誕生日から今日(2024/10/6)までの日数を計算して、表を完成させよ。
A | B | C | D | |
---|---|---|---|---|
1 | 2024/10/6 | 現在 | ||
2 | ||||
3 | 誕生日 | 日数 | ||
4 | 小片 | 1998/11/5 | ||
5 | 山岸 | 1998/11/24 | 155545 | |
6 | 新沼 | 1999/10/20 | 155215 | |
7 | 谷本 | 1999/11/16 | 155188 | |
8 | 岸本 | 2000/4/1 | 155051 | |
9 | 浅倉 | 2000/9/3 | 154896 | |
10 | 小野 | 2000/9/29 | 154870 | |
11 | 小野田 | 2001/12/17 | 154426 | |
12 | 秋山 | 2002/7/29 | 154202 | |
13 |
曜日
日付(シリアル値)から曜日を表示するだけなら書式設定で行う。
A | B | C | D | E | F | |
---|---|---|---|---|---|---|
1 | 日付 | 曜日 | 数値 | 曜日表示 | イベント | |
2 | 2024/10/1 | =A2 | =MOD(A2,7) | =MOD(A2,7) | =IF(D2=1,"休み","") | |
3 | 2024/10/2 | 水 | 4 | 水 | ||
4 | 2024/10/3 | 木 | 5 | 木 | ||
7 | 2024/10/4 | 金 | 6 | 金 | ||
5 | 2024/10/5 | 土 | 0 | 土 | ||
6 | 2024/10/6 | 日 | 1 | 日 | 休み | |
7 | 2024/10/7 | 月 | 2 | 月 | ||
8 | 2024/10/8 | 火 | 3 | 火 | ||
9 | 2024/10/9 | 水 | 4 | 水 | ||
10 |
毎日曜日に「休み」と表示したいとする。このとき、B列を参照して=IF(B2="日","休み","")と式を立てても上手くいかない。曜日固有の値を取得したいときは7で除した余り(MOD)を用いる。日曜日なら「1」となる。
日付関数
シート操作したときの日付:TODAY()
TODAY関数は現在の日付をシリアル値で返す(引数はない)。
シートに何らかの操作をしたときに再計算され、そのときの日付となる。入力した翌日にファイルを開くと、翌日の日付となる。
A | B | C | D | E | |
---|---|---|---|---|---|
1 | 記入日 | =TODAY() | |||
2 | |||||
3 | 品目 | 単価 | 数量 | 小計 | |
4 | |||||
5 | |||||
6 |
TODAY関数は記入した日付を記録するための関数ではない(随時更新されるから)。
実用的な使い方としては、他項目を記入完了後、そのまま印刷ないしPDF化すると出力先には記入日が記録される。またシート上で値を随時更新するのではなく、シート記入日を記録したいときには「=TODAY()」入力後、Enterキーを押す代わりにF9キーを押す。すると関数ではなく、そのときの日付のシリアル値が入力される。
TODAY関数の効果的な使い方としては、常に最新の年齢をデータとして表示する、など動的なデータ更新を行うケースである。
年・月・日を実数で返す:YEAR(シリアル値), MONTH(シリアル値), DAY(シリアル値)
- YEAR関数はシリアル値から「年」を数値として返す関数である。
- MONTH関数はシリアル値から「月」を数値として返す関数である。
- DAY関数はシリアル値から「日」を数値として返す関数である。
A | B | C | |
---|---|---|---|
1 | 今日 | 2024/10/6 | |
2 | |||
3 | 年 | =YEAR(B1) | |
4 | 月 | =MONTH(B1) | |
5 | 日 | =DAY(B1) | |
6 |
年月日からシリアル値を返す:DATE(年,月,日)
DATE関数は年,月,日を指定すると、その日付を表すシリアル値を返す関数である。
A | B | C | |
---|---|---|---|
1 | シリアル値 | =DATE(B3, B4, B5) | |
2 | |||
3 | 年 | 2024 | |
4 | 月 | 10 | |
5 | 日 | 6 | |
6 |
課題:貸出期間(形式)
指定された貸出期間に従って返却期日を表示する表を完成させよ。なお貸出期間の数字が変更されれば、返却期日も変化するようにせよ。
A | B | C | D | |
---|---|---|---|---|
1 | 貸出日 | 2024/10/6 | ||
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 | E | |
---|---|---|---|---|---|
1 | 駅名 | 時刻 | 所要時間 | 分 | |
2 | 京都発 | 7:59 | |||
3 | 名古屋 | 8:56 | =B3 - B2 | =C2*24*60 | |
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 |
課題:実働時間(形式)
始業から終業までの時間から休憩時間を除いた実働時間(実数)に時給を掛けて一日の支給額が決まる。
表より、実働時間と支給額の計算をせよ。
A | B | C | |
---|---|---|---|
1 | 始業 | 9:25 | |
2 | 終業 | 17:49 | |
3 | 休憩(分) | 55 | |
4 | 実働 | ||
5 | 時給 | 1050 | |
6 | 支給額 | ||
7 |
課題:年齢計算(形式)
各メンバーの誕生日から今日(10/6)現在の年齢を計算して、表を完成させよ。
A | B | C | D | |
---|---|---|---|---|
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 | 24 | |
10 | 小野 | 2000/9/29 | 24 | |
11 | 小野田 | 2001/12/17 | 22 | |
12 | 秋山 | 2002/7/29 | 22 | |
13 |
年だけの引き算では、その年の誕生日前なのかあとなのかが分からない。誕生日前後で年齢を変えるために、以下の計算をする。
- 日付を8桁の数値に変換する。
- 2024/10/6→20241006
- 1998/11/5→19981105
- 二つの数値の引き算をする。
- 20241006 - 19981105
→259901
- 20241006 - 19981105
- 10,000で割り、小数点以下を切り捨てる。
- 25
課題:給与計算(形式)
以下の条件にしたがって、表を完成させ、給与支払額を計算せよ。
- 条件
-
- 「始業時刻」から「退社時刻」までの時間をから昼食休憩時間を除いた時間を「勤務時間」とする。
- 「始業時刻」以前に出社しても「勤務時間」には含まれない。
- 終業時刻以降の勤務は「残業時間」とする。
- 「正規時間」とは「勤務時間」と「残業時間」の差である。
- 出社していない日の勤務時間欄などは空白にする。
- 土曜日、日曜日の支払額は平日の5割増し。
- 支給額の小数点以下は日ごとに切り捨て。
A | B | C | D | E | F | G | H | I | |
---|---|---|---|---|---|---|---|---|---|
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 | H | |
---|---|---|---|---|---|---|---|---|
1 | 2024 | 年 | 10 | 月 | ||||
2 | ||||||||
3 | 日 | 月 | 火 | 水 | 木 | 金 | 土 | |
4 | 1 | 2 | 3 | 4 | 5 | |||
5 | 6 | 7 | 8 | 9 | 10 | 11 | 12 | |
6 | 13 | 14 | 15 | 16 | 17 | 18 | 19 | |
7 | 20 | 21 | 22 | 23 | 24 | 25 | 26 | |
8 | 27 | 28 | 29 | 30 | 31 | |||
9 | ||||||||
10 |
A | B | C | D | E | F | G | H | |
---|---|---|---|---|---|---|---|---|
1 | 2024 | 年 | 11 | 月 | ||||
2 | ||||||||
3 | 日 | 月 | 火 | 水 | 木 | 金 | 土 | |
4 | 1 | 2 | ||||||
5 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | |
6 | 10 | 11 | 12 | 13 | 14 | 15 | 16 | |
7 | 17 | 18 | 19 | 20 | 21 | 22 | 23 | |
8 | 24 | 25 | 26 | 27 | 28 | 29 | 30 | |
9 | ||||||||
10 |
- セルに出力するのはシリアル値
-
セルにはシリアル値で日付を出力する。
表示形式で「日」のみを表示する。
- 最初のセル(A4)
- [指定年月初日のシリアル値]-[指定年月初日の前日の曜日番号(シリアル値を7で割った余り)]
- 以降のセル
- 前のセルに数字を1ずつ加えていく
- 当月以外のセルを空白にする(発展)
-
余力があれば…。
セルにブランク("")を出力するのではなく、値(シリアル値)を出力した上で表示設定で空白に見えるようにする。その際「条件付き書式」を用いる。
- 当月初日より前
または - 当月の翌月初日以降
なら、文字は白(背景と同じ色)
- 当月初日より前