予定表をエクセルで作る方法【背景色と表示範囲を自動調整するマクロを導入】
豆父ちゃん
こんな疑問にお答えします。
結論から言うと、エクセルマクロを使って使いやすい予定表にカスタマイズする！です。
予定表を作成するときにありがちな落とし穴として、情報が多くなりすぎて見づらくなってしまう！ということです。このような問題は、背景色と表示範囲を自動調整するマクロを使えば一瞬で解決することができます。
他にも罫線を自動で表示するマクロを設定すれば、さらに使いやすく見やすい予定表を自動作成できるようになります。
これから予定表を作成していこうと考えいる方は参考にしてみてください。
目次
エクセルで作る使いやすい予定表とは
使いやすい予定表とは、罫線の操作や背景色を変える操作のような、スケジュール管理に関係のない操作を極力やらないで済む予定表！のことです。スケジュール管理に関係のない操作はマクロを使って全て自動化することができます。
この記事で紹介するエクセル予定表は、電気設備の検査をする仕事で実際に使用している予定表で4つのマクロが設定されています。
- 条件によって背景色を変更するマクロ
- 表示範囲を変更するマクロ
- 罫線を表示するマクロ
この３つのマクロを設定すれば、スケジュール管理に関係のない操作を手動で行うことはほとんどありません。
エクセルで予定表を作るコツ
予定表を作るにはいくつかのコツがあります。コツを知らないでがむしゃらに作業を進めていくと、やり直し作業の繰り返しで挫折してしまいます。
今回紹介する予定表を作るコツは、私が実際に何回も失敗と挫折を繰り返した経験から得たものです。いちから予定表を作成しようとする方にとって、役に立つ情報なので参考にしてみてください。
予定表に必要な項目を全て記入
情報が多くなると、スクロールしなければ表の全体が見えないし、印刷範囲の調整が難しくなってしまう。
このように考えて、予定表の項目を無理矢理減らそうとする方がいますが、減らす必要はありません。
このあと紹介する、表示範囲を変更するマクロを使えば項目がいくつあっても見やすい予定表に調整することができます。
予定表(全体表示)
予定表(点検表表示)
予定表(物件情報表示)
マクロを使えば表示範囲の変更を一瞬で行うことができるので、スクロールして表を移動させる必要がありません。
セル結合は極力使わない
セルの結合を多用してしまうと、テキストの長さに対する列幅の自動調整ができなくなる可能性があります。
例えば、
セル結合を使って住所項目の列幅を憶測で調整。しかし、入力するテキストが長すぎてセルの長さが足りなかった。
結論から言うと、列幅の調整で対応してください。
列を挿入し列を増やしてセル結合で調整していくこともできますが、どれだけの列を挿入するべきなのか検討がつきません。感覚を頼りに行う作業のためマクロで自動化することができません。
列幅の調整はセル結合を使うのではなく、一つのセルにテキストを入力し、列幅をAutofitで調整する方法がおすすめです。
Autofitは入力されるテキストがどんなに長くなっても対応することができます。住所のように長いテキスト、短いテキストが入力される項目では、長いテキストを認識し自動調整してくれます。
上記の記事は、列幅を自動調整する方法について詳しく紹介しています。
行の最初のヘッダー情報を計画的に作成
予定表作成ではヘッダー情報（一番上の項目）を計画的に作成することが重要です。
ヘッダー情報の作成方法は、重要な項目を左側にする！ことがおすすめです。
表示範囲を変更するマクロでは表示させたい項目を選ぶことができますが、項目の表示する順番を変えることはできません。なので、どのように予定表を表示させたいかをしっかりと考えて作成しましょう。
実際に使っている予定表は、左から「物件名」「住所」「担当者」「連絡先」・・・という順番にしています。
「住所」は重要な情報ではあるものの日常的に確認する情報ではありません。このようなものは表示範囲を変更するマクロで非表示にしています。
予定表作成に必要なマクロの設定
スケジュール管理に関係のない操作を自動化する4つのマクロを詳しく紹介します。コピーしてすぐに使えるサンプルも掲載しているので、これから予定表にマクロを設定しよう！と考えている方は是非使ってみてください。
条件によって背景色を変更するマクロ
条件によって背景色を変更するマクロはいろんな場面で利用することができます。
今回の予定表では3つの条件で背景色を変化させています。
- 物件毎に背景色を交互に変える
- 検査が完了していないセルはピンク
- 請求、領収が完了してないセルは黄色
背景色を変化させる条件は関数IFを使って表現しています。
テキストに「○」やがあるとき背景色を変化させ、それ以外はなにもしない
というマクロにしています。
サンプルマクロ
| 1 | Sub 背景色変更() |
| 2 | gyo = Cells(Rows.Count, 9).End(xlUp).Row |
| 3 | For i = 4 To gyo Step 6 |
| 4 | Range(Cells(i, 1), Cells(i + 2, 26)).Interior.Color = RGB(234, 234, 234) |
| 5 | Next |
| 6 | For i = 7 To gyo Step 6 |
| Range(Cells(i, 1), Cells(i + 2, 26)).Interior.Color = xlNone | |
| 8 | Next |
| 9 | For i = 4 To gyo |
| 10 | For j = 10 To 21 |
| 11 | If Cells(i, j) = “〇" Then |
| 12 | Cells(i, j).Interior.Color = RGB(255, 204, 255) |
| 13 | ElseIf Right(Cells(i, j), 2) = “持込" Then |
| 14 | Cells(i, j).Interior.Color = RGB(255, 204, 255) |
| 15 | ElseIf Right(Cells(i, j), 2) = “入金" Then |
| 16 | Cells(i, j).Interior.Color = RGB(255, 255, 204) |
| 17 | ElseIf Right(Cells(i, j), 2) = “郵送" Then |
| 18 | Cells(i, j).Interior.Color = RGB(255, 255, 204) |
| 19 | Else |
| 20 | End If |
| 21 | Next |
| 22 | Next |
| 23 | For i = 4 To gyo Step 3 |
| 24 | If Right(Cells(i, 22), 2) = “予定" Then |
| 25 | Range(Cells(i, 22), Cells(i + 2, 22)).Interior.Color = RGB(255, 204, 255) |
| 26 | Else |
| 27 | End If |
| 28 | Next |
| 29 | End Sub |
表示範囲を変更するマクロ
表示範囲を変更するマクロは、必要な情報が多すぎて全てを表示させると見づらくなるようなエクセルファイルで使うことができます。
検査や請求の処理状況を把握するための一覧表と物件の詳細情報を確認するための一覧表とで、表示範囲を区別しています。
検査や請求の処理状況を確認するための一覧表
サンプルマクロ
| 1 | Sub 点検表表示() |
| 2 | For i = 1 To 26 |
| 3 | Columns(i).AutoFit |
| 4 | Next |
| 5 | For i = 3 To 8 |
| 6 | Columns(i).ColumnWidth = 0 |
| 7 | Next |
| 8 | For i = 23 To 25 |
| 9 | Columns(i).ColumnWidth = 0 |
| 10 | Next |
| 11 | End Sub |
物件の情報情報を確認するための一覧表
サンプルマクロ
| 1 | Sub 一覧表表示() |
| 2 | For i = 1 To 26 |
| 3 | Columns(i).AutoFit |
| 4 | Next |
| 5 | For i = 6 To 25 |
| 6 | Columns(i).ColumnWidth = 0 |
| 7 | Next |
| 8 | End Sub |
表示範囲を変更するマクロを使えば、一覧表の項目が増えると、スクロールが手間で使いにくい！というストレスがなくなります。
初めから一覧表の項目が多いとわかっている場合、2つの一覧表を別シートや別ファイルで並行して管理しようとする方がいますが、得策ではありません。
- シートやファイルを見比べながら作業しなければならない
- どちらかのデータを変更したとき、もう一方のデータを更新しなければならない
- 2つのデータに食い違いがあるとき、どちらが間違っているか調べなければならない
データを分けてしまうと、上記のようなデメリットがあります。一覧表の項目が多くて使いにくい場合でも、1つのシートで管理することをおすすめします。
罫線を表示するマクロ
予定表の項目が増減したとき、罫線の引き直しを自動化するマクロです。
予定表では、外枠が太線、他は細線の罫線を引き直す！というマクロが設定されています。
サンプルマクロ
| 1 | Sub 罫線() |
| 2 | retu = Cells(2, Columns.Count).End(xlToLeft).Column |
| 3 | gyo = Cells(Rows.Count, 1).End(xlUp).Row |
| 4 | Range(Cells(2, 1), Cells(gyo + 2, retu)).Borders(xlDiagonalDown).LineStyle = xlNone |
| 5 | Range(Cells(2, 1), Cells(gyo + 2, retu)).Borders(xlDiagonalUp).LineStyle = xlNone |
| 6 | Range(Cells(2, 1), Cells(gyo + 2, retu)).Borders(xlEdgeLeft).LineStyle = xlNone |
| 7 | Range(Cells(2, 1), Cells(gyo + 2, retu)).Borders(xlEdgeTop).LineStyle = xlNone |
| 8 | Range(Cells(2, 1), Cells(gyo + 2, retu)).Borders(xlEdgeBottom).LineStyle = xlNone |
| 9 | Range(Cells(2, 1), Cells(gyo + 2, retu)).Borders(xlEdgeRight).LineStyle = xlNone |
| 10 | Range(Cells(2, 1), Cells(gyo + 2, retu)).Borders(xlInsideVertical).LineStyle = xlNone |
| 11 | Range(Cells(2, 1), Cells(gyo + 2, retu)).Borders(xlInsideHorizontal).LineStyle = xlNone |
| 12 | Range(Cells(2, 1), Cells(gyo + 2, retu)).Borders(xlEdgeTop).LineStyle = xlContinuous |
| 13 | Range(Cells(2, 1), Cells(gyo + 2, retu)).Borders(xlEdgeTop).Weight = xlMedium |
| 14 | Range(Cells(2, 1), Cells(gyo + 2, retu)).Borders(xlEdgeLeft).LineStyle = xlContinuous |
| 15 | Range(Cells(2, 1), Cells(gyo + 2, retu)).Borders(xlEdgeLeft).Weight = xlMedium |
| 16 | Range(Cells(2, 1), Cells(gyo + 2, retu)).Borders(xlEdgeRight).LineStyle = xlContinuous |
| 17 | Range(Cells(2, 1), Cells(gyo + 2, retu)).Borders(xlEdgeRight).Weight = xlMedium |
| 18 | Range(Cells(2, 1), Cells(gyo + 2, retu)).Borders(xlEdgeBottom).LineStyle = xlContinuous |
| 19 | Range(Cells(2, 1), Cells(gyo + 2, retu)).Borders(xlEdgeBottom).Weight = xlMedium |
| 20 | Range(Cells(2, 1), Cells(gyo + 2, retu)).Borders(xlInsideVertical).LineStyle = xlContinuous |
| 21 | Range(Cells(2, 1), Cells(gyo + 2, retu)).Borders(xlInsideVertical).Weight = xlThin |
| 22 | Range(Cells(2, 1), Cells(gyo + 2, retu)).Borders(xlInsideHorizontal).LineStyle = xlContinuous |
| 23 | Range(Cells(2, 1), Cells(gyo + 2, retu)).Borders(xlInsideHorizontal).Weight = xlThin |
| 24 | End Sub |
ヘッダー情報が記載されている行の一番右の列番号を取得、物件の番号が記載されている列の一番下の行番号を取得して、罫線を引く！というマクロです。なので、物件の数やヘッダー情報の項目がいくら増えても自動的に罫線が修正されます。
罫線を一旦全て消して、セルを選択し直して罫線を引き直す！という手間がなくなります。
キレイで見やすい予定表を作成するためには、罫線をしっかり引くことが重要です。
エクセルマクロを使って作業効率アップ
エクセルマクロを使えば、エクセル業務の効率を格段にアップさせることができます。
会社では、いくつもの作業効率が上がるエクセルマクロを導入した実績があります。最初は自分が使うエクセルファイルにだけマクロを設定していたのですが、働き方改革の一環として、職場のみんなが使うエクセルファイルにマクロを設定することになりました。
マクロの導入には、以下のようなメリットとデメリットがあります。
マクロ導入のメリット
- 機械的なルーティーン作業を自動化することができる
- ミス（ヒューマンエラー）がなくなる
- エクセル操作に費やしてした時間が削減される
マクロ導入のデメリット
- マクロを扱える人間が少ない
- 作成者しかプログラムの改修ができない
- 正常に動作するマクロかどうか判断が難しい
メリットとデメリットの両方をふまえてマクロの導入していくことをおすすめします。私の職場では、さほど重要ではないけどエクセル操作に時間がかかる業務にマクロを設定する！ことになりました。マクロの処理だけに頼ってしまうと、もし設定したプログラムに不具合があったとき大変なことになってしまいます。
職場に導入したマクロには、写真データの貼り付け作業を自動化するマクロや決められた様式に必要な項目を記入する転記マクロがあります。
写真データの貼り付け作業を自動化するマクロとは
エクセルに写真を貼り付ける作業は手間と時間がかかります。
- 写真の大きさを調整しなければならない
- 写真のきれいに並べなければならない
- 写真が保存されているファルダを探さなければならない
毎回、上記のような処理をしていると写真の貼り付け作業がめんどうになってしまいますよね。そんなとき動画で紹介している写真を自動で貼り付けるマクロを使えば、めんどうな写真張り付け作業をすぐに処置することができます。
このマクロは下記のサイトでダウンロードしてお使いいただけます。写真の貼り付け作業の効率を少しでもアップさせたいと考えている方は是非使ってみてください。
決められた様式に必要な項目を記入する転記マクロとは
転記マクロは、請求書や領収書のような決まられた様式に必要な項目を記入する！というものです。
氏名、住所、所属などが記載された一覧表から契約書や領収書を一瞬で作成することができます。転記マクロにはいろんなメリットがありエクセル作業の効率アップに繋がります。
- 編集ミスが減る
- 過去の情報をすぐに検索できる
- 誰でも簡単に書類作成ができる
基本的な転記VBAが設定されたエクセルファイルを下記のサイトからダウンロードしてお使いいただけます。書類作成業務の効率を少しでもアップさせたいと考えている方は是非使ってみてください。