合志市栄のパソコン教室 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 WebSiteCopyright© 2017 SAKAEPC |