合志市栄のパソコン教室 SAKAEPC
活用編

Excelで作るカレンダー 勤務表や工程表を作成

 日付と曜日を配置した表(カレンダー)を作り、日程を管理しましょう。
 ここでは windows 10 office 2016 で解説しています。

勤務表

 サンプルファイル[calendarA.xlsx]をダウンロードしてください。


 セル[B2]に日付「2018/2/1」が入力されています。日付を変えてみてください。
年末年始休み・月曜定休(祝日の時は翌日)に対応(2018年版)、以下にその表示方法を解説しています。

日付の表示形式をユーザー定義で決める

セルの上で右クリック→[セルの書式設定]→[表示形式]→[ユーザー定義]
「種類」に「ggge"年"m"月勤務表"」と入力
 「サンプル」に「平成30年2月勤務表」と表示されます。確認が済んだらダイアログを閉じてください(以下同じ)。


 入力した日付のシリアル値を基に、「日」と「曜」を連続して表示します。

セル[B6]は「=B2」、ユーザー定義「d」


セル[B7]は「=B6+1」、セル「B36」までコピー


セル[C6]は「=B6」、ユーザー定義「aaa」、セル「C36」までコピー

曜日の文字色を条件付き書式で変える

 土曜を青、日曜・祝日を赤にしています。
[B6]~[C36]を選択
[ホーム]→[条件付き書式]→[ルールの管理]

「条件付き書式ルールの管理」から、現在の選択範囲に適用されているルールの一つ(土曜:青)を選択→[ルールの編集]

「書式ルールの編集」から、ルールの種類を「数式を使用して、書式設定するセルを決定」→ルールの内容を編集
「次の数式を満たす場合に値を書式設定」に「=WEEKDAY($B6)=7」と入力→[書式]
 「セルの書式設定」で、フォントの色を青にしています。

 同一範囲にルール(日曜・祝日:赤)が適用されています。

「次の数式を満たす場合に値を書式設定」に「=OR(WEEKDAY($B6)=1,COUNTIF(祝日,$B6)>0)」と入力→[書式]
 「セルの書式設定」で、フォントの色を赤にしています。

条件付き書式の条件となる数式

=WEEKDAY($B6)=7

勤務表!B2=2018/2/1の場合、数式の結果=FALSE(土曜でない)
シート「条件付き書式の条件となる数式」で確認

=OR(WEEKDAY($B6)=1,COUNTIF(祝日,$B6)>0)
 当年の祝日を、シート[休館日]のセル[C2]~[C21]に「祝日」と名前を付けてまとめています。

=OR(WEEKDAY($B6)=1,COUNTIF(祝日,$B6)>0)

勤務表!B2=2018/2/1の場合、数式の結果=FALSE(日曜でも祝日でもない)
シート「条件付き書式の条件となる数式」で確認

休館日の算出

【休館日】
1) 月曜日(祝日のときは翌日)
2) 12月28日から1月4日
3) その他教育委員会が認めた日
 当年の休館日を、シート[休館日]のセル[E2]~[E60]に「休館日」と名前を付けてまとめています。

「月曜日(祝日のときは翌日)」を求める式

=WORKDAY($C$2-WEEKDAY($C$2)+7*ROW(A1),1,祝日)→前の週の土曜日から土・日・祝日を除いた1日後
 元旦の七日後から毎週、年末まで算出しています。

 「開始日」を、定休日(月)の前の週の土曜日としています。
$C$2-WEEKDAY($C$2,種類省略は1)→日付(シリアル値)-その日の曜日を示す整数→返される値の曜日は一定(種類1の場合、土曜)
+7*ROW(A1)→+7*(指定セルの行番号)→7ずつ加算→曜日変わらず

表内の行に条件付き書式を設定する

 定休日の行を黄色で塗りつぶし、月の変わり目を罫線で区切っています。
[B6]~[M36]を選択
[ホーム]→[条件付き書式]→[ルールの管理]

 シート[休館日]のセル[H2]~[H13]に、「月初め」と名前を付けて各月の1日をまとめています。

工程表

 サンプルファイル[calendarB.xls]をダウンロードしてください。

セル[D10]に日付を入力→工程表の背景となるカレンダー(H26年1月~H27年3月に対応)を自動作成
 入力した日付のシリアル値を基に、「日」と「曜」を連続して表示します。
 月の変わり目を月名と罫線で区切って、休日(日曜と祝日)の列を黄色で塗りつぶしています。
 カレンダーの表面に、テキストボックスと図形で内容を書き加えています。


 シート[月初め_休日]のセル[B3]~[B17]に「月初め」と名前を付けて、2014年の1月から翌年3月までの各月の1日をまとめています。
 同じく、セル[D3]~[D23]に「休日」と名前を付けて、期間中の休日をまとめています。

 対応期間に合わせて、枠内の日付を変えてください。

数式による月の変わり目の表示

月名
 日付を基にセル範囲「月初め」から、1日(ついたち)ごとに[E8]の行に月を表す整数、[E9]の行に“月”を表示しています。
月を表す整数

文字「月」

条件付き書式による月の変わり目と休日の表示

罫線と塗りつぶし
 表の該当範囲に、数式を使用して設定した書式を適用しています。


 当ページの説明図はExcel 2016で作成しています。

合志市栄のパソコン教室 SAKAEPC [目次へ]

HaraHara WebSite

Copyright© 2017 SAKAEPC
inserted by FC2 system