エクセル関数を使ってシフト表(日別)作成する方法を紹介します。
この記事では以下ようにビフォー（まっさらなエクセルの状態）からアフター（いろいろと情報を入れたエクセル）を作っていきます。
具体的に以下のことをお伝えしていきます。
・条件付き書式を上手く活用する方法
・エクセル関数(XLOOKUPやCOUNTIF)の使いどころ
・エクセル関数のエラー回避術
・VBAで作業をさらにラクにする方法
上記のことを動画で解説していきます。全て動画内で解説をしているため、ぜひ動画をご覧いただきたいです。
またVBAプログラムは、この記事内で公開しています。
なお、動画内で紹介しているエクセルは記事内のフォームからダウンロードできます。
目次
動画で解説｜エクセル関数でシフト表(日別)作成(VBAでさらに便利に)
まっさらな状態のエクセルから実務で使えるシフト表を作る方法を解説していきます。
1｜日付, 開始時間, 終了時間, 時間間隔, シフト表を入力
2｜No, 氏名, 役職, シフト, 開始, 終了, サンプル情報を入れこむ
3｜エクセル関数xlooku」を入れ込む
4｜エクセル関数でシフトの時間が自動で出力されるようにする
5｜条件付き書式を設定する
6｜日別でシフト表をコピーするマクロを設定する
7｜VBAのカンタンな解説
動画内で使っているエクセルをダウンロードしたい人は以下をどうぞ
上記の動画で作成したエクセルは以下のフォームからダウンロード可能です。
ダウンロードしたエクセルにはVBAプログラムも含まれておりますので、マクロもそのままご利用いただけます。
ぜひダウンロードしてご活用ください。
動画内で利用しているVBAプログラム
このプログラムの大まかな流れは以下です。
コード2｜変数設定
コード3｜エラー対策
コード4｜同じ名前のエクセルファイルがないかどうか確認
コード5｜新規エクセルを作成
コード6｜日別にシフト表のテンプレートシートをコピペ
コード7｜新規で作成したエクセルを保存
一言でいうと、「日別のシフト表が入ったエクセルファイル(新規)」を作成するマクロです。
以下がVBAプログラムです。なお、カンタンな説明はプログラム内に入れておきます。
もしVBAを設定する方法が分からない人は、本記事の下部でVBAプログラムの使い方を解説した動画をご覧ください。
以下のプログラムをどのようにエクセルにセットすればいいか分かるはずです。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 |
Option Explicit Sub SheetCopy() '---コード1｜変数定義--- Dim d1 As Date, d2 As Date, d3 As Date Dim ws0 As Worksheet, ws1 As Worksheet, ws2 As Worksheet, wsa As Worksheet Dim wb1 As Workbook, wba As Workbook Dim i As Long, k As Long, cmax As Long Dim FileName As String, SheetName As String, BookPath As String Dim Sun As Boolean, Sat As Boolean Dim str1 As String, str2 As String '---コード2｜変数設定--- 'ThisWorkbookをwb1として設定 Set wb1 = ThisWorkbook 'ws0, ws1, ws2に各シートを設定 Set ws0 = wb1.Worksheets("テンプレート") Set ws1 = wb1.Worksheets("シートコピーマクロ") Set ws2 = wb1.Worksheets("祝日") 'str1, str2に値を設定 str1 = ws1.Range("C3").Value str2 = ws1.Range("C4").Value 'i = 0 とする i = 0 'cmaxにws2(祝日シート)の最終行を設定 cmax = ws2.Range("B1048576").End(xlUp).Row '---コード3｜エラー対策--- 'シート「シートコピーマクロ」のセルF2が空欄だったらエラーメッセージ If str1 = "" Then MsgBox "セルF2に日付が入っていません。" Exit Sub End If 'シート「シートコピーマクロ」のセルF3が空欄だったらエラーメッセージ If str2 = "" Then MsgBox "セルF3に日付が入っていません。" Exit Sub End If 'シート「シートコピーマクロ」のセルF2の値が日付形式ではなかったらエラーメッセージ If IsDate(str1) = False Then MsgBox "セルF2の値は日付形式ではありません。" Exit Sub Else d1 = str1 End If 'シート「シートコピーマクロ」のセルF3の値が日付形式ではなかったらエラーメッセージ If IsDate(str2) = False Then MsgBox "セルF3の値は日付形式ではありません。" Exit Sub Else d2 = str2 End If '---コード4｜同じ名前のエクセルファイルがないかどうか確認--- '新しいファイルの名前を指定 FileName = Replace(d1, "/", "") & "_" & Replace(d2, "/", "") & "_WorkSchedule.xlsx" '新しいファイルのフルパスを設定 BookPath = wb1.Path & "¥" & FileName '指定したパスにファイルが作成済でないかを確認。 If Dir(BookPath) <> "" Then '既に同名のファイルが存在する場合はメッセージを表示 MsgBox "既に" & FileName & "というファイルは存在します。既存のエクセルファイルを削除するか名前を変えてください。" Exit Sub End If 'スクリーン更新をしないようにする Application.ScreenUpdating = False '---コード5｜新規エクセルを作成--- ' ワークブックを作成 Set wba = Workbooks.Add '---コード6｜日別にシフト表のテンプレートシートをコピペ--- 'For Next構文で繰り返し i=0,1,2,・・・,(d2-d1) For i = 0 To (d2 - d1) 'd3にd2からi日引いた日付を設定 d3 = d2 - i 'Sun, Satの変数にFalseを設定 Sun = False Sat = False 'd3が土曜日だったら、SatをTrueとする If Weekday(d3) = 7 Then Sat = True End If 'd3が日曜日だったら、SunをTrueとする If Weekday(d3) = 1 Then Sun = True End If 'd3が祝日に該当していたら、SunをTrueとする For k = 2 To cmax If d3 = ws2.Range("B" & k).Value Then Sun = True Exit For End If Next '変数SheetNameにd3の日付から「/」を抜いた値をセットする SheetName = Replace(d3, "/", "") '新規エクセルに変数SheetNameにd3の日付から「/」を抜いた値をセットする ws0.Copy before:=wba.Worksheets(1) 'コピーしたシートの名前をSheetNameに変更する Set wsa = ActiveSheet wsa.Name = SheetName 'コピーしたシートのセルC2にd3の日付を入れる wsa.Range("C2").Value = d3 'コピーしたシートのタブの色、セルC2の背景色を青色（土曜日なら）に変更する If Sat = True Then wsa.Tab.ColorIndex = 37 wsa.Range("D2").Interior.ColorIndex = 37 End If 'コピーしたシートのタブの色、セルC2の背景色をピンク色（日曜日もしくは祝日なら）に変更する If Sun = True Then wsa.Tab.ColorIndex = 38 wsa.Range("D2").Interior.ColorIndex = 38 End If Set wsa = Nothing 'Forの先頭に戻る Next '---コード7｜新規で作成したエクセルを保存--- '新規エクセルを名前を付けて保存する wba.SaveAs BookPath '新規エクセルを閉じる wba.Close 'スクリーン更新を元に戻す Application.ScreenUpdating = True End Sub |
エクセルVBAの基本を学べる動画
エクセルマクロVBAの使い方が分からない人は以下の動画で解説を入れています。
ご覧になっていただければ、上記のVBAプログラムをさらに活用できるようになるはずです。
エクセルマクロの編集方法やVBEの使い方を解説
エクセルマクロVBAの使い方や始め方を解説しています。VBAを使ってみたいけれど、操作に不安がある人はぜひご覧下さい。
マクロの自動記録の使い方1
エクセルマクロVBAの自動記録の使い方を解説しています。自動記録は初心者でもラクに始めることができるのでぜひご覧下さい。
マクロの自動記録の使い方2
エクセルマクロVBAの自動記録の限界について話をしています。
ボタンにマクロを登録する方法
ボタンを押してマクロを動作させたい人はぜひご覧ください。