予定表をエクセルで作る方法【背景色と表示範囲を自動調整するマクロを導入】
豆父ちゃん
こんな疑問にお答えします。
結論から言うと、エクセルマクロを使って使いやすい予定表にカスタマイズする！です。
予定表を作成するときにありがちな落とし穴として、情報が多くなりすぎて見づらくなってしまう！ということです。このような問題は、背景色と表示範囲を自動調整するマクロを使えば一瞬で解決することができます。
他にも罫線を自動で表示するマクロを設定すれば、さらに使いやすく見やすい予定表を自動作成できるようになります。
これから予定表を作成していこうと考えいる方は参考にしてみてください。
目次
エクセルで作る使いやすい予定表とは
使いやすい予定表とは、罫線の操作や背景色を変える操作のような、スケジュール管理に関係のない操作を極力やらないで済む予定表！のことです。スケジュール管理に関係のない操作はマクロを使って全て自動化することができます。
この記事で紹介するエクセル予定表は、電気設備の検査をする仕事で実際に使用している予定表で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初心者が効率化マクロを導入する方法
VBAなんて扱ったことないけどエクセル作業を自動化するマクロを導入したい！
このような問題を抱えながら日々のエクセル業務をこなしている方がおおくいらっしゃるのではないでしょうか。
こんな問題を解決する方法がこちら
- マクロの開発を外注に依頼する
- マクロが設定されたエクセルファイルをダウンロードする
- 参考書やネットでVBAスキルを身に付ける
VBAの勉強を始める前は上記の方法で効率化マクロを導入していました。
完成されたマクロを使ってエクセル業務を処理する
↓
作業効率が２倍以上アップ！！
↓
便利すぎて自分でもマクロを設定したいと考え始める
↓
参考書やネットの情報でVBAスキルを身に付ける
↓
マクロ開発を代行できる程のスキルを習得
VBAは
他のプログラミングに比べて参考書やネットで紹介されている情報量が多いので初心者でも導入しやすい
という特徴があります。
毎日のルーティーン作業にうんざりしている、だれでもできる単純作業に時間を費やしている、という方はあなたに合った方法でVBAを導入しちゃいましょう。
マクロの開発を外注に依頼する
マクロの開発を外注に依頼する導入方法は
VBAを全く扱えない方でもエクセル作業を自動化できる最も簡単な方法
です。
「こんなエクセル作業を自動化したい」「作った資料を自動的にPDFで保存したい」「決まった様式に写真を自動で貼り付けたい」
こんな感じで実現したい内容を依頼するだけで業務内容に合ったマクロを開発してくれます。
ガッツポーズの人
デメリットは、費用がかかることだけ。
開発内容、依頼先によってかかる費用は大きく異なります。
私が会社員の頃利用していた業者さんは１マクロあたり2万円～3万円の費用がかかりました。
疑問がある人
ちなみに、マメBlogでもエクセルマクロ開発代行サービスを承っています。
開発内容の確認、VBAコードの設定、動作確認後の調整、など全ての工程を私（マメ父ちゃん）が行っているので、
費用が安い！スピード納期！！
でやらせてもらっています。
ほとんど独学で身に付けたVBAスキルなので、内容によっては開発できないこともあるのでご了承ください。
ご相談、見積もり依頼は完全無料ですので気になる方は下記のリンクよりお問合せ下さい。
マクロが設定されたエクセルファイルをダウンロードする
VBA初心者でも『マクロが設定されたエクセルファイルをダウンロード』ですぐにエクセル作業を自動化することができます。
たとえば、
A４用紙に写真を貼り付けてコメントを入力する作業を自動化したい！という場合、
『写真を貼り付けるマクロ』が設定されたエクセルファイルをダウンロードすれば問題解決です。
実際に、『写真を貼り付けるマクロ』をダウンロードして使っている様子がこちら
ガッツポーズの人
『マクロが設定されたエクセルファイルをダウンロード』する導入方法は、
VBA初心者でもマクロを導入できる、費用が安い、
というメリットがあります。
しかし
VBAに関する情報が多く公開されているとはいえ、ネット上で想定するマクロは見つけることが難しい
というデメリットもあります。
ガッツポーズの人
個人ブログやファイルを販売できるサイト「note」や「Tips」ではいろんなマクロの情報が掲載されているので是非参考にしてみてください。
今までマメBlogで受注した効率化マクロを下記のnoteで掲載していますので併せてチェックしてみてください。
参考書やネットでVBAスキルを身に付ける
先程も紹介しましたが、
VBAは他のプログラミングに比べて参考書やネットで紹介されている情報量が多いので初心者でも導入しやすい
プログラミングです。
言い換えると、
情報量が豊富なので自分で勉強できちゃう
ということです。
私のVBAスキルはほとんど独学で身に付けたもので、
考えた処理のほとんどをVBAで表現できるレベルにまで到達した！
と思っています。
私の作ったマクロをプロのプログラマーが見れば、
お前の書くコードはインチキだ！「変数の宣言」とか「引数」を正しく使えよ！！
と感じるはずです。
でも、
私からすればどうでもいい！！！想定した内容を正しく処理できればよくないですか！？
独学であっても『写真を貼り付けるマクロ』『全てのシートをPDF出力するマクロ』『連番を振るマクロ』など、
業務内容に合ったマクロを開発できるようになれます。
独学でVBAスキルを身に付けるコツは、
エラーが発生してもいいからひたすらコードを書く、なんでもいいからエクセル作業をマクロ化してみる
です。
とは言っても、全くの知識0の方は何をすればいいかわからないですよね。
VBA知識0の初心者の方は、参考書『たった1秒で仕事が片づくExcel自動化の教科書【増強完全版】』を読んでみてください。
VBAの知識0の方でもすいすい読み進められる内容になっています。
やさしい内容にも関わらず「え！！VBAを使えばこんなことできるの！？」と感動すると思います。
実際に私はこの参考書を読んでからVBAの勉強を始めました。
ひらめく人
VBAの魅力を発見した後は、
ひたすら自動化したい処理を実現するコードを書くだけ
です。
【VBA 写真を貼る】、【VBA PDF出力】、のようにネット検索すれば知りたいコードをすぐにゲットできます。
実現したい処理に向かって、「調べる→書いてみる→エラーを改善する→調べる」を繰り返していると
自然といろんなマクロが作れるようになっているはずです。
VBAに興味が湧いてきたという方は今からVBA学習をスタートさせちゃいましょう。