今 月 の 末 日 =DATE(YEAR(TODAY()),MONTH(TODAY())+1,0) 月 を +1 し 日 を 0 にします 来 年 の 元 日 =DATE(YEAR(TODAY())+1,1,1) 生 年 月 日 など 期 日 を 指 定 して 開 始 日 や 終 了 日 を 求 め

Transcription

1 DATE 関 数 DATE 関 数 は 引 数 で 指 定 される 日 付 のシリアル 値 を 返 します =DATE( 年, 月, 日 ) 引 数 の 年 月 日 には 整 数 や YEAR 関 数 MONTH 関 数 DAY 関 数 などでシリアル 値 から 取 り 出 した 数 値 などが 使 用 できます =DATE(2016,4,1) =DATE(YEAR(TODAY()),MONTH(TODAY())+6,1) DATE 関 数 は シリアル 値 を 返 しますが 自 動 で 表 示 形 式 が 日 付 となるため 日 付 として 表 示 されます 表 示 形 式 を 標 準 にすると シリアル 値 が 表 示 されます 特 定 の 日 付 を 取 得 するための 専 用 の 関 数 も 多 数 ありますが DATE 関 数 でも 様 々な 日 付 を 取 得 することができます 今 月 の 初 日 =DATE(YEAR(TODAY()),MONTH(TODAY()),1) 翌 月 の 初 日 =DATE(YEAR(TODAY()),MONTH(TODAY())+1,1) 1 月 1 日 の 場 合 も YEAR を +1 しなくても Excel が 翌 年 の 1 月 1 日 に 調 整 します 1

2 今 月 の 末 日 =DATE(YEAR(TODAY()),MONTH(TODAY())+1,0) 月 を +1 し 日 を 0 にします 来 年 の 元 日 =DATE(YEAR(TODAY())+1,1,1) 生 年 月 日 など 期 日 を 指 定 して 開 始 日 や 終 了 日 を 求 めることがあります このような 場 合 は DATE 関 数 と IF 関 数 等 を 組 み 合 わせます 以 下 生 年 月 日 の 入 ったセルには 生 年 月 日 と 名 前 を 定 義 しています 児 童 手 当 小 学 校 修 了 前 の 開 始 日 まず 3 歳 の 誕 生 日 を 取 得 します =DATE(YEAR( 生 年 月 日 )+3,MONTH( 生 年 月 日 ),DAY( 生 年 月 日 )) 次 に 小 学 校 修 了 前 の 開 始 日 を 3 歳 に 達 した 日 の 属 する 月 の 翌 月 と 考 えます =DATE(YEAR( 生 年 月 日 )+3,MONTH( 生 年 月 日 )+1,1) が 生 年 月 日 が 月 の 初 日 の 場 合 3 歳 未 満 に 支 給 がうまくいきません そこで 生 年 月 日 が 月 の 初 日 であるかどうかを 判 断 して 支 給 開 始 月 を 正 しく 取 り 出 します =IF(DAY( 生 年 月 日 )=1,DATE(YEAR( 生 年 月 日 )+3,MONTH( 生 年 月 日 ),1),DATE(YEAR( 生 年 月 日 )+3,MONTH( 生 年 月 日 )+1,1)) 2

3 小 学 校 入 学 まず 満 6 歳 の 誕 生 日 を 取 得 します =DATE(YEAR( 生 年 月 日 )+6,MONTH( 生 年 月 日 ),DAY( 生 年 月 日 )) 次 に 満 6 歳 の 誕 生 日 以 後 における 最 初 の 学 年 の 初 め( 最 初 の 4 月 1 日 )を 取 得 します =DATE(YEAR( 生 年 月 日 )+7,4,1) が このままでは 早 生 まれ の 場 合 間 違 った 日 付 を 返 します 早 生 まれ を 調 整 します =IF(OR(MONTH( 生 年 月 日 )<4,AND(MONTH( 生 年 月 日 )=4,DAY( 生 年 月 日 )=1)),DATE(YEAR( 生 年 月 日 )+6,4,1),DATE(YEAR( 生 年 月 日 )+7,4,1)) 早 生 まれ は 月 が 4 未 満 または 月 が 4 且 つ 日 が 1 の 場 合 ということです 次 の 論 理 式 のほうがわかりやすいかも しれません (MONTH( 生 年 月 日 )<4)+(MONTH( 生 年 月 日 )=4)*(DAY( 生 年 月 日 )=1) 児 童 手 当 中 学 校 修 了 前 の 開 始 日 小 学 校 入 学 と 同 じ 考 えで +する 年 数 を 12 または 13 とします =IF(OR(MONTH( 生 年 月 日 )<4,AND(MONTH( 生 年 月 日 )=4,DAY( 生 年 月 日 )=1)),DATE(YEAR( 生 年 月 日 )+12,4,1),DATE(YEAR( 生 年 月 日 )+13,4,1)) 加 算 開 始 日 小 学 校 入 学 と 同 じ 考 えで +する 年 数 を 15 または 16 とします =IF(OR(AND(MONTH( 生 年 月 日 )=4,DAY( 生 年 月 日 )=1),(MONTH( 生 年 月 日 )<4)),DATE(YEAR( 生 年 月 日 )+15,4,1),DATE(YEAR( 生 年 月 日 )+16,4,1)) 18 歳 年 度 末 =IF(OR(AND(MONTH( 生 年 月 日 )=4,DAY( 生 年 月 日 )=1),(MONTH( 生 年 月 日 )<4)),DATE(YEAR( 生 年 月 日 )+17,3,31),DATE(YEAR( 生 年 月 日 )+18,3,31)) 22 歳 年 度 末 =IF(OR(AND(MONTH( 生 年 月 日 )=4,DAY( 生 年 月 日 )=1),(MONTH( 生 年 月 日 )<4)),DATE(YEAR( 生 年 月 日 )+22,3,31),DATE(YEAR( 生 年 月 日 )+23,3,31)) 3

4 EDATE 関 数 EDATE 関 数 は 開 始 日 から 起 算 して 指 定 された 月 数 だけ 前 または 後 の 日 付 に 対 応 するシリアル 値 を 返 します =EDATE( 開 始 日, 月 ) 今 日 のひと 月 後 は 月 を 1 とします =EDATE(TODAY(),1) 今 日 のひと 月 前 は 月 を -1 とします =EDATE(TODAY(),-1) DATE 関 数 ではそれぞれ 次 のようになります =DATE(YEAR(TODAY()),MONTH(TODAY())+1,DAY(TODAY())) =DATE(YEAR(TODAY()),MONTH(TODAY())-1,DAY(TODAY())) 応 当 日 指 定 された 月 数 だけ 前 または 後 の 日 付 に 対 応 するシリアル 値 を 返 しますということは 一 見 民 法 の 応 当 日 のようです が 次 の 式 は =EDATE("2016/10/31",1) 2016/11/30 を 返 します 次 の 式 は =EDATE("1/31",1) 2016/2/29 を 返 します 民 法 では 応 当 日 がないときは という 文 があります これは 10 月 31 日 の 応 当 日 11 月 31 日 がない 場 合 など 月 末 に 生 じるもので 応 当 日 がない 場 合 は 月 の 末 日 が 応 当 日 の 前 日 となります つまり EDATE 関 数 が 月 によっては 応 当 日 と 応 当 日 の 前 日 を 返 してしまいます そこで 開 始 日 と EDATE で 取 得 した 日 付 の 日 が 異 なる 場 合 は 応 当 日 がないという 判 断 をして 調 整 します =IF(DAY(D1)<>DAY(EDATE(D1,1)),DATE(YEAR(D1),MONTH(D1)+2,0),EDATE(D1,1)-1) 4

5 EOMONTH 関 数 EOMONTH 関 数 は 開 始 日 (シリアル 値 )から 起 算 して 指 定 された 月 数 だけ 前 または 後 の 月 の 最 終 日 のシリアル 値 を 返 します =EOMONTH( 開 始 日, 月 ) 月 が 0 はの 場 合 はその 月 の 末 日 -1 の 場 合 は 前 月 の 末 日 となります 月 の 初 日 は 次 の 式 です その 月 の 日 数 も 簡 単 に 取 得 できます =DAY(EOMONTH(D1,0)) ところで DATE 関 数 では 今 月 の 末 日 と 初 日 は 次 の 式 でした =DATE(YEAR(TODAY()),MONTH(TODAY())+1,0) =DATE(YEAR(TODAY()),MONTH(TODAY()),1) EOMONTH 関 数 は 2007 以 降 で 使 用 できる 関 数 であり 新 しい 関 数 は 常 により 簡 潔 に 書 けます が 関 数 の 引 数 や 使 い 方 動 作 等 を 覚 えておく 必 要 があります 一 方 DATE 関 数 のほう 理 解 しやすく は 長 くはなりますが どのような 動 作 をしているのかが 分 かりやすくなっています 5

6 NETWORKDAYS 関 数 NETWORKDAYS 関 数 は 開 始 日 から 終 了 日 までの 期 間 に 含 まれる 稼 動 日 の 日 数 を 返 します 引 数 はすべてシリアル 値 です NETWORKDAYS( 開 始 日, 終 了 日,[ 祭 日 ]) 稼 働 日 とは 土 曜 日 曜 および 指 定 された 休 日 を 除 く 日 祭 日 は 省 略 可 です 日 曜 日 と 土 曜 日 を 除 く 日 数 を 返 します( 育 児 休 業 手 当 金 の 給 付 日 数 ) =NETWORKDAYS( 開 始 日, 終 了 日 ) 祭 日 は 日 付 を 含 むセル 範 囲 か 日 付 を 示 すシリアル 値 の 配 列 定 数 を 指 定 します 祭 日 は 土 日 と 重 なった 場 合 は 除 きま す =NETWORKDAYS( 開 始 日, 終 了 日, 祭 日 ) 次 の 式 は 月 ごとの 稼 働 日 を 返 します $E$2:$E$18 で 祭 日 が 指 定 されているので 主 任 手 当 の 日 数 計 算 の 基 礎 として 使 えます =NETWORKDAYS(A2,B2,$F$2:$F$19) 6

7 NOW TODAY 関 数 NOW 関 数 は 現 在 の 日 付 と 時 刻 に 対 応 するシリアル 値 を 返 します =NOW() TODAY 関 数 は 現 在 の 日 付 に 対 応 するシリアル 値 を 返 します =TODAY() TODAY 関 数 が 整 数 のみのシリアル 値 であるのに 対 して NOW 関 数 が 返 すのは 少 数 部 のあるシリアル 値 であり 少 数 部 が 時 刻 です シリアル 値 Excel では 日 付 と 時 刻 は 連 続 したシリアル 値 として 扱 います シリアル 連 続 していること 日 付 は 1900/1/1 を 1 とし 1 日 ごとに 1 を 加 えた 数 値 になっています 1899/12/31 は 文 字 列 と 判 断 されます また 日 付 の 最 大 値 は 9999/12/31 です 時 刻 は 1/(24*60*60)を 1 秒 とし 0 時 00 分 から 1 秒 経 過 ごとに 同 じ 数 値 が 加 算 されます 累 計 が 1 を 超 えると 日 付 が 1 日 繰 り 上 がり 0 からの 加 算 が 繰 り 返 されます 次 の 式 は 5 時 を 表 します 式 を 挿 入 し 表 示 形 式 を 時 刻 にすると 5:00:00 と 表 示 されます =(1/(24*60*60))*5*60*60 7

8 WEEKDAY 関 数 WEEKDAY 関 数 は 日 付 (シリアル 値 )に 対 応 する 曜 日 を 1 から 7 の 整 数 で 返 します 既 定 では 1 が 日 2 が 月 7 が 土 です =WEEKDAY(シリアル 値,[ 週 の 基 準 ]) F 列 は =E1 といった 数 式 が 入 っており 表 示 形 式 (aaa)で 曜 日 名 を 表 示 しています 週 の 規 準 は 省 略 できます 2 とすると 1 を 月 として 開 始 します WEEKDAY 関 数 は 単 に 整 数 を 返 すだけなので 曜 日 を 表 示 するためには CHOOSE 関 数 などを 使 うか 表 示 形 式 で 表 示 を 切 り 替 えます =CHOOSE(WEEKDAY(A1)," 日 "," 月 "," 火 "," 水 "," 木 "," 金 "," 土 ") VLOOKUP 関 数 では 次 のとおりです =VLOOKUP(WEEKDAY(D1),$G$1:$H$7,2) ところで 日 付 のシリアル 値 を 7 で 割 ると 日 曜 日 は 剰 余 が 1 月 曜 日 2 金 曜 日 6 土 曜 日 0 となります これを 利 用 すると 曜 日 を 返 す VLOOKUP 関 数 の 式 は =VLOOKUP(MOD(D1,7),$G$1:$H$7,2,FALSE) となります この 場 合 は 4 つ 目 の 引 数 (FALSE)が 必 要 です 8

9 YEAR MONTH DAY 関 数 YEAR 関 数 は 日 付 (シリアル 値 )に 対 応 する 年 を の 範 囲 の 整 数 で 返 します =YEAR(シリアル 値 ) MONTH 関 数 は 日 付 (シリアル 値 )から 月 を 整 数 で 返 します =MONTH(シリアル 値 ) DAY 関 数 は 日 付 (シリアル 値 )から 日 を 整 数 で 返 します =DAY(シリアル 値 ) DATE 関 数 のところで シリアル 値 から 取 り 出 した 年 や 月 日 を 利 用 して 様 々な 日 付 を 取 得 しました DATE 関 数 とこの 3 つの 関 数 が 日 付 処 理 の 基 本 です 和 暦 の 年 を 取 り 出 す 関 数 はありません 西 暦 の 年 から 換 算 します 平 成 の 場 合 は 次 の 式 です =YEAR(TODAY())-1988 昭 和 は 次 の 式 です =YEAR(D1)-1925 あるいは TEXT 関 数 で 取 得 します =VALUE(TEXT(D1,"e")) TEXT 関 数 で 取 り 出 すと 文 字 列 になるので VALUE 関 数 で 数 値 に 変 換 します シリアル 値 のまま 表 示 形 式 (e)で 和 暦 の 年 にすることもできます 9

10 日 付 と 表 示 形 式 例 えば 月 予 定 表 を 作 るときに 次 のような 形 式 の 表 を 作 ることがあります ここでは A4 からの 結 合 セルに 該 当 月 の 初 日 のシリアル 値 を 入 れ 表 示 形 式 で 平 成 28 年 4 月 事 務 予 定 一 覧 と しています また 日 は 数 値 を 直 接 入 力 するのではなく A3 には=A1 A4 以 下 には=A3+1 といった 数 式 を 入 れ 表 示 形 式 (d)で シリアル 値 から 日 を 表 示 しています また B3 以 下 には =A3 といった 数 式 を 入 れ 表 示 形 式 (aaa) で 曜 日 を 表 示 しています このようにシリアル 値 をそのままにして 表 示 形 式 で 加 工 することにより A1 に 該 当 月 の 初 日 を 入 れるとその 月 に 切 り 替 わり ます 条 件 付 き 書 式 等 で 末 日 の 処 理 を 行 うと 翌 月 の 日 や 曜 日 罫 線 の 非 表 示 等 も 行 えます 日 付 はシリアル 値 として 取 り 扱 い シリアル 値 はシリアル 値 のまま 残 しておくことで 自 動 取 得 や 表 示 の 切 り 替 えが 簡 単 に 行 えます YEAR 関 数 や MONTH 関 数 と 文 字 列 を 連 結 して =" 平 成 " & YEAR(TODAY())-1988 & " 年 " & MONTH(TODAY()) & " 月 分 " など 平 成 年 月 分 などをシリアル 値 から 自 動 的 に 取 得 することがあります が このような 数 式 にすると 文 字 列 となってしまい 再 利 用 ができません 個 人 的 には 日 付 はシリアル 値 として 扱 うべし という 方 針 を 取 り 年 や 月 日 を 扱 う 場 合 は 書 式 で 処 理 をするこ とにしています 表 示 形 式 は 次 のとおりです ggge" 年 "m" 月 分 非 常 勤 講 師 の 勤 務 状 況 について( 報 告 )" 10

11 次 の 図 では A1 から 下 へ 先 月 の 初 日 本 日 のシリアル 値 を 返 す 数 式 を 設 定 し 表 示 形 式 を ggge" 年 "m" 月 分 " とし ています A1:A3 をデータの 入 力 規 則 リストの 元 の 値 にすると 月 分 を 今 月 と 前 月 から 選 択 することができます 通 常 は 実 績 月 の 翌 月 に 作 成 するが 実 績 月 に 作 成 することもある 場 合 など の 処 理 に 使 えます 日 付 シリアルに 下 記 の 表 示 形 式 文 字 を 適 用 すると 右 側 のように 表 示 されます 表 示 形 式 文 字 表 示 表 示 形 式 文 字 表 示 yyyy 2016 ge H28 yy 16 gge 平 28 m 2 ggge 平 成 28 mm 02 e 28 d 5 aaa 金 dd 05 aaaa 金 曜 日 g H 区 切 り 文 字 として 使 用 できる 半 角 記 号 以 外 の 文 字 列 は (ダブルクォーテーション) で 挟 んで ユーザー 定 義 の 表 示 形 式 に 配 置 します ggge" 年 "m" 月 "d" 日 ("aaa ") 現 在 の 児 童 生 徒 見 込 み 数 " 11

12 年 齢 計 算 年 齢 計 算 でまず 思 いつくのは 次 の 式 です =YEAR(TODAY())-YEAR( 生 年 月 日 ) が これでは 今 年 達 する 年 齢 となってしまいます そこで 誕 生 日 が 来 ているかどうかを 判 断 して -1 することが 必 要 です 判 断 は (MONTH(TODAY())>=MONTH( 生 年 月 日 ))*(DAY(TODAY())>=DAY( 生 年 月 日 )) となります 月 と 日 が 誕 生 日 の 月 と 日 を 超 えているかどうかの 判 断 です これを 加 えると 年 齢 計 算 の 式 は =IF((MONTH(TODAY())>=MONTH( 生 年 月 日 ))*(DAY(TODAY())>=DAY( 生 年 月 日 )),YEAR(TODAY())-YEAR( 生 年 月 日 ),YEAR(TODAY())-YEAR( 生 年 月 日 )-1) DATEDIF 関 数 Excel には 日 付 関 数 の 一 覧 に 出 てこない DATEDIF 関 数 というのがあります =DATEDIF( 開 始 日, 終 了 日, 単 位 ) 単 位 の 設 定 で 年 数 や 月 数 などを 返 します =DATEDIF( 開 始 日, 終 了 日,"Y") で 満 年 齢 を 返 します ただし この 関 数 は Lotus との 互 換 性 を 保 つための 関 数 ということで ウィザードもヘルプをありません ネットで 検 索 すれば 資 料 は 大 量 にあります 12