platonのブログ

思考の整理とアウトプット、たまにグラブル

トップ > コンピュータ > ExcelのVBAを使ってモーニングルーティンの手順書を書いてみる

ExcelのVBAを使ってモーニングルーティンの手順書を書いてみる

コンピュータ

f:id:Platon:20201022131920p:plain

完成図

はじめに

いつも読んでいただきありがとうございます。

私事ですが、11月からちょっと環境が変化するため、今までのようにブログを書くことができるか不透明な状況となりました。

 

とはいえ、毎朝社説を読んで自分で調べ、読んだ本の内容等も織り交ぜながら文章を書いていくことはそれだけで自分の勉強になり、さらにブログとして表現することで理解が深まるのを実感しており、今後も継続したいと考えています。

 

そこで、ちょっと古いですがブログを書く時間を捻出するために効率的な「モーニングルーティン」を実践し、さらにそれを手順書として明文化することで余計な思考の手間を省き、さらにさらに手順書をExcelのVBAを用いて動的な形に落とし込み、使いやすくしようと思います。

 

まだ開発段階ですが、試行錯誤の記録としてブログに書いておこうと思い筆を執りました。

しばしお付き合いいただけますと幸いです。

 

手順書に何を書くか

そもそも手順書とは

まず、「手順書」とは何ぞやというところからですが、これはマニュアルのようなものです。

「やらなければいけないこと」を書きだし、順番をつけてリスト化すれば、それはもう立派な手順書になります。

そこから「誰がやるか」「締め切りはいつか」などを書き加え、誰が読んでも理解できる体裁にすれば、仕事のマニュアルになるでしょう。

行動の書き出し

今回作成する手順書は「自分のためのモーニングルーティン」についてなので、自分がわかればそれで十分です。

まずは起床から家を出るまでの手順を書きだします。

  1. 朝目を覚ます
  2. ベッドから出る
  3. 窓を開けて空気を入れ替える
  4. ・・・

といった感じにやっていきます。

自分の場合は28項目ありました。

 

Excelで手順書をつくる

次に、それらをExcelに書いていきます。

もちろん、手順を書きだす段階からExcelに書き込んでいてもOKです。

ちなみに表形式で書いた項目を選択して「テーブルとして書式設定」を押すと、簡単にきれいなデザインの表が出来上がるのでおすすめです。

チェックボックスを入れる

Excelに書いたら、どこまでやったかを確認できるようにチェックボックスを付けます。

上部に「開発」タブがあればそのまま進んでOKですが、おそらく初期設定では表示されていないと思うので、とりあえず表示させるところから始めましょう。

 

まずはExcel左上の「ファイル」から「オプション」を開きます。

f:id:Platon:20201022102639p:plain

「ファイル」→「オプション」→「リボンのユーザー設定」を開く

そして上記画像のように「リボンのユーザー設定」を開き、「開発」のチェックボックスにチェックを入れてOKを押します。

これで、「開発」タブが上部に表示されるようになりました。

 

次に、「開発」タブをクリックし、「挿入」から「フォームコントロール」の「チェックボックス」を選択します。

f:id:Platon:20201022131650p:plain

上段左から3番目のアイコンがチェックボックス

するとマウスが範囲指定モードになるので、チェックボックスを設置した場所（上記画像の場合はB3セル）に適当に四角を書きましょう。

最初はボックス右にテキストが書かれているので、それは削除しておきましょう。

これをコピーしていくのですが、その前にVBAの設定をします。

VBAでチェックを入れた時点の時刻を表示・記録する

VBAの正式名称はVisual Basic for Applicationsであり、Excel上で動くプログラミング言語です。

プログラミングと聞くと複雑そうですが、実際はコードのコピペを駆使することで大抵のことはできてしまいます。

手順書レベルであれば複雑な処理は不要なので、十分VBAで記述できます。

 

今回VBAで実装したいのは以下の機能です。

  1. ボックスにチェックを入れたらその時点の時刻を記録する
  2. 全てのボックスのチェックを外すボタンを設置する

まず1を実装するため、チェックボックスを右クリックして「マクロの登録」から適当な名前をつけ、編集していきます。

具体的なコードは以下の通りです。

  1. Sub チェック1_Click()
  2. XOffset = 3
  3. YOffset = 0
  4. On Error Resume Next
  5. If ActiveSheet.CheckBoxes(Application.Caller).Value = Checked Then
  6. Range(ActiveSheet.Shapes(Application.Caller).TopLeftCell.Address).Offset(YOffset, XOffset).Value = Now
  7. End If
  8. End Sub

このコードにより、ボックスにチェックが入った時にそのボックスが設置されているセルから右に3つ移動したセルに「Now（=その時点の時刻）」を記録できるようになります。

 

この記述の良いところは、基準となるセルと時刻を記録するセルをそれぞれチェックボックスの位置から相対的に指定している点です。

つまり、チェックボックスがどこにあっても、同じ動きをしてくれるということです。

だから、コピペする前にVBAを書く必要があったんですね。

 

ここまで書いたら、あとはボックスをコピペです。

セル右下をドラッグしてオートフィルを使ってもいいですし、一番下の項目のセルからCtrl+Shift+↑を押してチェックボックスのあるセルまで選択し、Ctrl+Dを押して一括コピペしてもOKです。

もちろん手作りの温もりを求め、丹精込めて1つずつ作成しても構いません。

「2. 全てのボックスのチェックを外すボタンを設置する」についてはまた後ほど。

 

チェックを入れたら目立つようにする

次に、ボックスにチェックを入れた際にわかりやすくしてみましょう。

ボックスを右クリックして「コントロールの書式設定」を選択します。

f:id:Platon:20201022110631p:plain

コントロールの書式設定

その後、「リンクするセル」にボックスの左のセル（下の図ではA3）を記入します。

f:id:Platon:20201022110833p:plain

リンクするセルを記入

これでどうなるかと言うと、ボックスにチェックを入れた際に、「リンクするセル」で指定したセルに「TRUE」が表示されるようになります。

チェックを外すと、「FALSE」と表示されます。

これでチェックの有無を条件式に簡単に代入できるようになりました。

 

そしてこれは自分でも良いやり方が見つかっていないのですが、どうも「リンクするセル」の設定はチェックボックスをコピペしても自動で変わってくれないようです。

上の画像の例で「$A3」と相対参照を使ってみた跡がありますが、これをコピペすると「$A4, $A5, $A6, ・・・」とならずすべて「$A3」になってしまいます。

 

ゆえに非常に不本意なのですが、ボックス1つ1つを選択してセルを指定してあげなければなりません。

何か良い方法はないものでしょうか・・・。

 

気を取り直して、すべてのボックスにリンクするセルを設定し終わったら、次に条件付き書式の設定をしていきます。

ボックス左のセルが「TRUE」であれば、手順の背景色が変わるようにしてみましょう。

f:id:Platon:20201022132159p:plain

条件付き書式の設定

ここの数式「=$A3=TRUE」はコピペすれば自動で変わるので、一番上のセルで設定したらあとは書式の設定をコピペすればOKです。

もしくは「条件付き書式の設定」から「ルールの管理」を開き、適用範囲を拡張してもOKです。

f:id:Platon:20201022112816p:plain

条件付き書式ルール適用範囲の拡張

これで完了した行動がすぐにわかるようになりましたが、TRUEとFALSEが表示されるのが邪魔かと思います。

ですので、文字色を白にして見えなくしてしまいましょう。

全体チェック/全体チェック外しの設定

次にすべてのボックスからチェックを外す（並びにチェックを付ける）機能を実装しましょう。

一番下にボックスを作り、これまで同様リンクするセルを設定しておきます。

そしてマクロの登録（VBAの記述）です。コードは以下の通りになります。（セルの個数や位置によって変わります）

  1. Sub check_all()
  2. If Range("A32").Value = True Then
  3. Range("A3").Value = True
  4. Range("A4").Value = True
  5. Range("A5").Value = True
  6. Range("A6").Value = True
  7. Range("A7").Value = True
  8. Range("A8").Value = True
  9. Range("A9").Value = True
  10. Range("A10").Value = True
  11. Range("A11").Value = True
  12. Range("A12").Value = True
  13. Range("A13").Value = True
  14. Range("A14").Value = True
  15. Range("A15").Value = True
  16. Range("A16").Value = True
  17. Range("A17").Value = True
  18. Range("A18").Value = True
  19. Range("A19").Value = True
  20. Range("A20").Value = True
  21. Range("A21").Value = True
  22. Range("A22").Value = True
  23. Range("A23").Value = True
  24. Range("A24").Value = True
  25. Range("A25").Value = True
  26. Range("A26").Value = True
  27. Range("A27").Value = True
  28. Range("A28").Value = True
  29. Range("A29").Value = True
  30. Range("A30").Value = True
  31. ElseIf Range("A32").Value = False Then
  32. Range("A3").Value = False
  33. Range("A4").Value = False
  34. Range("A5").Value = False
  35. Range("A6").Value = False
  36. Range("A7").Value = False
  37. Range("A8").Value = False
  38. Range("A9").Value = False
  39. Range("A10").Value = False
  40. Range("A11").Value = False
  41. Range("A12").Value = False
  42. Range("A13").Value = False
  43. Range("A14").Value = False
  44. Range("A15").Value = False
  45. Range("A16").Value = False
  46. Range("A17").Value = False
  47. Range("A18").Value = False
  48. Range("A19").Value = False
  49. Range("A20").Value = False
  50. Range("A21").Value = False
  51. Range("A22").Value = False
  52. Range("A23").Value = False
  53. Range("A24").Value = False
  54. Range("A25").Value = False
  55. Range("A26").Value = False
  56. Range("A27").Value = False
  57. Range("A28").Value = False
  58. Range("A29").Value = False
  59. Range("A30").Value = False
  60. Range("E3:E30").ClearContents
  61. End If
  62. End Sub

長いのでFor文でループ処理をさせた方がいいかもしれませんね。

これが何を意味しているかというと、「A32がTRUE（＝ボックスにチェックが入っている）になったら、A3からA30のすべてのセルもTRUEにする（＝行動の背景色が変わり、リンクしているボックスにチェックが入る）」及び「A32がFALSE（＝ボックスからチェックが外れる）になったら、A3からA30のすべてのセルもFALSEにする（＝行動の背景色が戻り、リンクしているボックスのチェックが外れる）」、「時刻が記入されたE3からE30までのセルを空白にする」という動きです。

 

途中経過時間（ラップタイム）を計測する

ここは好みの部分かと思いますが、自分は行動に要する時間を見積もるのが苦手でいつの間にか時間が過ぎてしまい遅刻する、ということがよくあるため、「どの動作にどれくらいの時間がかかるか」を知りたいと思い、ラップタイムを計測できる機能も実装しました。

やり方は関数のみで、ラップを知りたい行動の完了時刻から直前の行動の完了時刻を引けばOKです。

f:id:Platon:20201022132327p:plain

ラップタイムの数式

[@Time]はE4セルを選択したらいつの間にか変わっていました。

ちなみに「Minute」列はおおよその所要時間見積もりです。

散歩や食事、ブログといった時間がかかる行動以外のほぼすべての動作を1分としています。

「Minute」列と「Lap」列を比較することで、どの動作にどれだけ時間がかかっているかを目視し、見積もった所要時間をより正確な値にすることができます。

 

またこのままでは「Lap」列に「0:00:00」がズラッと並んでしまい気になるため、条件付き書式設定でセルの値がゼロの時、背景と文字が白になるようにします。

f:id:Platon:20201022132541p:plain

条件付き書式の設定でセルを空白にする

最初は文字のみを白にしていましたが、表が縞模様のデザインであるためうっすら「0:00:00」が表示されるのが気になり、背景も白にすることとしました。

条件を付けたセルだけ最初から白くなってしまうので、表のデザイン的には改善の余地がありそうです。

例えば、「0:00:00」以外の時に文字を表示するようなVBAを記述するとかでしょうか。

 

完成

お疲れさまでした。

ここまでできれば、とりあえず使えるレベルにはなったかと思います。

 

あとは実際に使ってみて、改善点を見つけたらその都度修正していくという感じです。

自作なので、自分が欲しいと思う機能だけを実装して、自分好みに作りかえていくことが可能なところがいいですね。

以前iPad用の電卓を作成した時にも感じましたが、やはり自分で作ったものが動くのを見るのは、それだけで楽しい気分になります。

platon.hatenablog.jp

 

おわりに

昨日思いつきで作って今朝初めて使用し、項目を追加するなど試行錯誤しています。

将来的には、チェックボックスにチェックを入れたら好きなキャラクターの画像が表示されるとともに、キャラクターが褒めてくれる音声を再生するような機能を実装したいと思っています。

その際はインセンティブとモチベーションの研究も参考にしつつ、どのような画像を表示するのがいいのか、動画の方がいいのか、ランダム性は必要か、音声はどの程度の長さがいいのかといったことも自分で実験してみたいと考えています。