Excelタスク管理で使っているExcelテクニック＋マクロ

Excelでのタスク管理で使っているテクニック、マクロについてまとめてみました。

※ヒルトン台場にて　GoPro HERO5 BLACK

Excelタスク管理で、Excelのテクニックを使っている部分

私がここ5年ほど使っているタスク管理の手法。
Excelでやっています。

ポイントは、
・時間を意識
・ショートカットキー主体のシンプルな操作
・厳しさとゆるさのバランス
です。

時間を意識しないタスク管理は意味がありませんので、随所に盛り込んでいます。
操作もシンプルにし、メッセージや確認もでません。
厳しく管理するものですが、概要をつかめればいいところは簡略化しています。

Excelのテクニックをふんだんに組み込み、熟成させ続けてきました。

グレーの部分は数式が入っている部分です。

①残り時間

生まれてからの日数
45歳までの日数
60歳までの日数
80歳までの日数
を表示させています。
人生の残り時間を意識しないタスク管理は意味がないからです。

別シート、シート［設定］に誕生日（セルB1）を入れておくと、

生まれてからの日数は、この数式で

80歳までの日数は、この数式で表示できます。

=DATE(年、月、日）で日付を表示
=YEAR（日付）で年を表示
=MONTH(日付）で月を表示
=DAY（日付）で日を表示
なので、誕生日（シート［設定］のB1）の年、月、日をとってきて、YEAR（年）には、セルC4の値（80）を足します。
80歳の誕生日を表示させるわけです。

その80歳の誕生日から、今日の日付（TODAY（））を引くと、80歳までの日数が表示されます。
この「80歳」というのは、変更可能です。
「80」と入力すれば、「80歳まで」と表示するように設定しています。

セルの書式設定（Ctrl＋1）で、［表示形式］の［ユーザー設定］で次のように設定すれば、「80」と入力して「80歳まで」と表示できるしくみです。
「80歳まで」と入力すると、数式で使えずエラーが出ます（文字は足したり引いたりできないため）

②今日の日付

今日の日付を入れます。
自動表示もできますが、都合が悪い場合もあるので、手入力です。

③タスクリスト

タスクのリストです。
Excelのテーブル機能を使っています。
テーブルを使えば、
・デザインを変更しやすい
・新しいデータを追加したときに、数式をコピーしてくれる
というメリットがあるからです。

タスクリストにカーソルを置き、［テーブルツール］→［デザイン］から好きなものを選べば、色を変更でき、

新規にデータを入れると、

デザイン、数式を整えてくれます。

・記号　並べ替え用の記号です。数式で表示しています。

=IF(AND(B10=$B$1,K10=""),"２",IF(AND(B10=$B$1,K10<>""),"１",IF(B10>$B$1,"３","4")))

ここの処理は、
・今日のタスクで完了済みなら、1
・今日のタスクで未完了なら、2
・明日以降のタスクで未完了なら、3
・昨日以前のタスクで完了済みなら、4
というものです。

このルールで並べ替えれば、終わったタスクはリストの下になります。

・月日
タスクを実行する日を入れる場所です。
みやすくするために条件付き書式で、
今日→黄色
明日→オレンジ
という設定にしています。

設定するのは［ホーム］→［条件付き書式］→［新しいルール］です。

・r（リピート）
繰り返すタスクの場合は、次のように文字を入れれば、繰り返します。

d（小文字）→毎日
w→毎週
m→毎月
h→平日

たとえば、毎日やるタスクなら、dと入れておき、実行すると、翌日（1/25）にコピーされるというしくみです。

この処理には後述するマクロを使っています。

・時間
タスクをやる時間帯です。1時間ごとに設定します。
入力すると右上ののグラフに反映され、1時間（オレンジ）を越えると、タスクを詰め込みすぎということになるので調整が必要です。
60分を超えるタスクや予定もあるので、あくまで目安としてみています。

・チェック
タスクを完了し、［終了］を入力すると「done！」を表示されます。
タスク完了後の達成感のためです。

・タスク
タスクを入力します。
できる限り細かく入れるのがポイントです。
私の場合、移動時間は前後の予定やタスクに含めています。

・見積（分）
見積もり時間を分単位で入力します。
どんなタスクでも見積もるのが、このタスク管理の肝です。

・実績（分）
タスクにかかった時間。
自動的に計算されます。

=IF(OR(J10="",K10=""),"",(K10-J10)/TIMEVALUE("1:00")*60)

やっている処理は、「開始時刻（J10）または終了時刻（K10）が空欄なら空欄のままにして、そうでなかったら、終了時刻から開始時刻の差を計算し、分で表示する」というものです。

時刻の計算はややこしくそのままでは計算できないので、TIMEVALUE関数を使っています。

・差異
見積と実績の差です。
見積と比べて実績がどうだったか、見積が甘かったか、見積どおりだったかがわかります。
なお、タスクが見積時間以内に終われば、タスク終了時にタスクの色が青になり、そうでなかったら、赤になるようなしくみです。

タスク管理で大事なのは、自分の見積精度を上げること。
自分がどのくらいの時間で何ができるかを知っておくことが欠かせません。

・開始
通常は［終了］へ終了時刻を入れれば、直前に完了させたタスクの終了時刻が入ります。
この開始時刻を入れる処理にも、マクロを使っています。

タスクを終える
↓
終了時刻を入れる
↓
実績が計算される
↓
見積と実績の差異がわかる
↓
リピートするタスクの場合、タスクがコピーされる
という流れです。

・終了
タスク終了時刻を、直接入力又はショートカットキー（Ctrl＋：）で入力します。
ショートカットキー（Ctrl＋：）なら、今の時刻を入れることができるので楽です。

④現在時刻、終了時刻

現在時刻は、　=NOW()で表示させ、
終了時刻は、現在時刻＋未完了のタスクの見積時間の合計で計算します。

=SUMIF(A:A,2,G:G)/60*TIMEVALUE("1:00")+B3

SUMIF関数で、A列が「2」だった場合、G列の［見積］を合計するという数式です。

A列で「2」と表示されているのは、今日のタスクでまだ終わっていないものですので、これを現在時刻に足せば、

「予定しているすべてのタスクが終われば、15:02に終わる」ということがわかります。

この時刻が、夜だったり、ときには翌日朝だったりすると、タスクを詰め込みすぎということです。
あれもできるこれもできるといろいろと詰め込みますが、実は物理的に不可能ということもあります。
もし毎日そんな状態なら、仕事をとりすぎている、単価が低くて仕事量が多すぎるということにもなるのです。

この終了時刻は、余裕を持って決めておくといいでしょう。
多くの場合、見積よりも時間がかかるからです。

⑤1週間グラフ

上部には、今後1週間で、どのくらいの時間が埋まっているかをグラフで表示しています。

スケジュールとタスクを入れ、おおまかに時間の埋まり具合をみるのが目的です。
（厳密には、ルーティンタスク、繰り返すタスクが加味されませんが、概要を把握するのが目的なので気にしません）

SUMIF関数で、タスクリストの月日ごとに集計し、

［条件付き書式］の［データバー］でグラフ表示しています。

⑥時間帯グラフ

今日の、時間帯別のグラフを表示しています。

別シートに、時間帯ごとの集計をし、それをグラフにするというしくみです。
当日のタスクのみを時間帯ごとに集計するので、SUMIFS関数というものをつかっています。

また、タスクリストには、［入力規則］という機能を使い、日付は半角、タスクは全角・・と入れるような設定です。
全角、半角をいちいち切り替えなくて済みます。

Excelタスク管理のタスク実行マクロ

ここまでは、Excelの機能だけを使ってタスク管理のシステムを作りました。

「執筆」というタスク（実際はもっと細かく何を執筆するかを入れます）を10:00にスタートして、11:21に終了した場合、それぞれの時刻を入れれば、実績時間が出て、差異もわかります。

ただ、並べ替えたり、繰り返しのタスクをコピーしたりということもその後にやらなければいけません。
この処理を自動化するためにはマクロが必要です。

Alt＋F11でVBE（マクロを書くソフト）を開き、［Sheet1（task)］にプログラムを書いていきます。
通常は、［標準フォーム］に書くのですが、この場合は、「［終了］に時刻を入れれば実行する」という処理にしたいため、この場所に書くのです。

1行目の「Private Sub Worksheet_Change(ByVal Target As Range)」は、「シートが変更されたとき」ということを示します。

Private Sub Worksheet_Change(ByVal Target As Range)

    
'■終了時刻を入れたときの処理
    
    '変更が10行目より上ならプログラム終了
    If Target.Row < 10 Then Exit Sub
    
    '変更が11列目じゃなかったらプログラム終了
    If Target.Column <> 11 Then Exit Sub
    
     
    'E列に「done!」と入れる
    Range("e" & Target.Row).Value = "done!"
    

    
If Range("c" & Target.Row).Value <> "" Then

    '行を挿入
    Range("a" & Target.Row + 1).EntireRow.Insert
    
    'セルをコピー
    Range("c" & Target.Row, "d" & Target.Row).COPY Range("c" & Target.Row + 1, "d" & Target.Row + 1)
    Range("f" & Target.Row, "g" & Target.Row).COPY Range("f" & Target.Row + 1, "g" & Target.Row + 1)
    
    'フォントを黒に戻す
  '  Range("e" & Target.Row + 1, "g" & Target.Row + 1).Font.Color = vbBlack
    
    '日付処理
    Select Case Range("c" & Target.Row).Value
        
        
        '毎日 日付に1を足す
        Case "d"
            Range("b" & Target.Row + 1) = Range("b" & Target.Row) + 1
        
        '毎週　日付に7を足す
        Case "w"
            Range("b" & Target.Row + 1) = Range("b" & Target.Row) + 7
        
        '毎月　日付の月に1を足す
        Case "m"
            Range("b" & Target.Row + 1) = DateAdd("m", 1, Range("b" & Target.Row))
    
        '毎年　日付の年に1を足す
        Case "y"
            Range("b" & Target.Row + 1) = DateAdd("y", 1, Range("b" & Target.Row))
    
        '平日　日付が金曜日だったら、3を足す　そうでなかったら1を足す
        Case "h"
            If Weekday(Range("b" & Target.Row)) = 6 Then
                Range("b" & Target.Row + 1) = Range("b" & Target.Row) + 3
            Else
                Range("b" & Target.Row + 1) = Range("b" & Target.Row) + 1
            End If
      
    End Select
End If

    
'■タスクのソート

    'ソート条件クリア
    Worksheets("task").ListObjects(1).sort.SortFields.Clear
    
    'ソート条件設定
    Worksheets("task").ListObjects(1).sort.SortFields.Add Key:=Range("a9")  '記号
    Worksheets("task").ListObjects(1).sort.SortFields.Add Key:=Range("b9")  '日付
    Worksheets("task").ListObjects(1).sort.SortFields.Add Key:=Range("k9")  '終了時刻
    Worksheets("task").ListObjects(1).sort.SortFields.Add Key:=Range("d9")  '時間
    Worksheets("task").ListObjects(1).sort.SortFields.Add Key:=Range("f9")  'タスク
    'ソート実行
    Worksheets("task").ListObjects(1).sort.Header = xlYes
    Worksheets("task").ListObjects(1).sort.Apply
    

'■開始時刻を入れる
 
    '直前に実行したタスクを見つける
    Dim Last_task
    Last_task = Range("k9").End(xlDown).Row
    
    'タスクの［開始］が空欄だったら、その上のタスクの終了時刻を入力
    If Range("j" & Last_task) = "" Then
        Range("j" & Last_task) = Range("j" & Last_task).Offset(-1, 1)
    End If
    
'■フォントの色を変える
    
    If Range("i" & Last_task) > 0 Then
        'E列とF列のフォントを青に
        Range("e" & Last_task, "f" & Last_task).Font.Color = vbBlue
    Else
        'E列とF列のフォントを赤に
        Range("e" & Last_task, "f" & Last_task).Font.Color = vbRed
    End If
    
    '次のタスクを選択
    Range("k" & Last_task + 1).Select

    
    
End Sub

それぞれ解説していきます。

マクロが動く範囲を設定

このマクロは、「シートのどこかに変更があったら」という条件で動きます。
といっても、タスクリストの［終了］へ終了時刻を入れたときだけ動けば十分です。
その変更が10行目より上、または、11列めじゃない場合はプログラムをそこで終了させるようにしています。

'■終了時刻を入れたときの処理
    
    '変更が10行目より上ならプログラム終了
    If Target.Row < 10 Then Exit Sub
    
    '変更が11列目じゃなかったらプログラム終了
    If Target.Column <> 11 Then Exit Sub

「10行目より下、11列目に変更があった場合」つまり、［終了］に入力したときにプログラムが動くしくみです。

タスク実行時にdone!と入力

タスク実行時には、E列のセルに「done!」と入れるプログラムを書きます。

‘E列に「done!」と入れる
Range(“e” & Target.Row).Value = “done!”

繰り返しタスクの場合の処理

タスクを終了した（［終了］に時刻を入力）ときに、［r］に、繰り返しの記号（d＝毎日、w＝毎週、m＝毎月、y＝毎年、h＝平日）があれば、それぞれの応じて処理をします。
もし、［r］に何も入っていない場合、つまり繰り返さない、通常のタスクの場合は、そのままです。

'■c列［r］が空欄でなかったら＝何か入力されていたら
If Range("c" & Target.Row).Value <> "" Then

    '行を挿入
    Range("a" & Target.Row + 1).EntireRow.Insert
    
    'セルをコピー
    Range("c" & Target.Row, "d" & Target.Row).COPY Range("c" & Target.Row + 1, "d" & Target.Row + 1)
    Range("f" & Target.Row, "g" & Target.Row).COPY Range("f" & Target.Row + 1, "g" & Target.Row + 1)
    
   
    '日付処理
    Select Case Range("c" & Target.Row).Value
        
        
        '毎日 日付に1を足す
        Case "d"
            Range("b" & Target.Row + 1) = Range("b" & Target.Row) + 1
        
        '毎週　日付に7を足す
        Case "w"
            Range("b" & Target.Row + 1) = Range("b" & Target.Row) + 7
        
        '毎月　日付の月に1を足す
        Case "m"
            Range("b" & Target.Row + 1) = DateAdd("m", 1, Range("b" & Target.Row))
    
        '毎年　日付の年に1を足す
        Case "y"
            Range("b" & Target.Row + 1) = DateAdd("y", 1, Range("b" & Target.Row))
    
        '平日　日付が金曜日だったら、3を足す　そうでなかったら1を足す
        Case "h"
            If Weekday(Range("b" & Target.Row)) = 6 Then
                Range("b" & Target.Row + 1) = Range("b" & Target.Row) + 3
            Else
                Range("b" & Target.Row + 1) = Range("b" & Target.Row) + 1
            End If
      
    End Select
End If

1/24（火）のタスクが毎日（d）だったら、そのタスクをコピーし、日付に1を足せば、1/25になります。
7を足せば、1/31（火）です。

1/27（金）のタスクで平日（h）だったら、日付に3を足し、1/30（月）になり、平日のみ繰り返す処理ができます。
ただし、祝日には対応していません。
（祝日リストを作り対応することはできます）

タスクをソート（並べ替え）

タスクをソートする処理です。
通常の方法だと、このように設定してソートするので、それをマクロで表現しています。

いったん、ソートの条件をクリアして、
テーブルをソートします。
Worksheets(“task”).ListObjects(1) がテーブルを意味するものです。

シート［Task］の1つ目のテーブルという指定にしています。
（シート名がTaskじゃないとエラーが出ます）
テーブル名を設定したほうがいい場合もありますが、テーブルを設定し直したときに一手間かかるからです。

記号、日付、終了時刻、実行時間、タスク名の順にソートの条件を設定して、実行しています。
この条件だと、
・今日のタスクで実行済みのもの
・今日のタスクで未実行のもの
・明日以降のタスクで未実行のもの
・昨日以前のタスクで実行済みのもの
と並び、整理することができるしくみです。

'■タスクのソート

    'ソート条件クリア
    Worksheets("task").ListObjects(1).sort.SortFields.Clear
    
    'ソート条件設定
    Worksheets("task").ListObjects(1).sort.SortFields.Add Key:=Range("a9")  '記号
    Worksheets("task").ListObjects(1).sort.SortFields.Add Key:=Range("b9")  '日付
    Worksheets("task").ListObjects(1).sort.SortFields.Add Key:=Range("k9")  '終了時刻
    Worksheets("task").ListObjects(1).sort.SortFields.Add Key:=Range("d9")  '時間
    Worksheets("task").ListObjects(1).sort.SortFields.Add Key:=Range("f9")  'タスク
    'ソート実行
    Worksheets("task").ListObjects(1).sort.Header = xlYes
    Worksheets("task").ListObjects(1).sort.Apply

   

タスク開始時刻を入れる

タスクを実行して、［終了］へ終了時刻を入れると、こうなります。

［開始］へ開始時刻を入れる必要があるのですが、そもそも開始時刻は、その直前のタスクの終了時刻のはずです。

実行したタスクを見つけ、そこから1行上、1列右の時刻（直前タスクの終了時刻）を入れています。

'■開始時刻を入れる
 
    '実行したタスクを見つける
    Dim Last_task
    Last_task = Range("k9").End(xlDown).Row
    
    'タスクの［開始］が空欄だったら、その上のタスクの終了時刻を入力
    If Range("j" & Last_task) = "" Then
        Range("j" & Last_task) = Range("j" & Last_task).Offset(-1, 1)
    End If

タスクの色を変える

実行したタスクの色を変更して、実行できたかどうかがわかりやすくしています。

・［差異］（見積時間と実績の差異）がプラス、つまり見積よりも早く終われば、青

・［差異］（見積時間と実績の差異）がマイナス、つまり見積よりも時間がかかれば、赤
になるしくみです。

その後、次のタスクの［終了］列のセルにカーソルを置き、この一連の処理は終わります。

'■フォントの色を変える
    
    If Range("i" & Last_task) > 0 Then
        'E列とF列のフォントを青に
        Range("e" & Last_task, "f" & Last_task).Font.Color = vbBlue
    Else
        'E列とF列のフォントを赤に
        Range("e" & Last_task, "f" & Last_task).Font.Color = vbRed
    End If
    
    '次のタスクを選択
    Range("k" & Last_task + 1).Select

Excelタスク管理で使っているその他のマクロ

上記のマクロで、一連の処理はできますが、さらに使いやすくするには、
・新規タスクの追加
・タスクのコピー
・タスクの削除
・タスクのソート
・タスクの開始時刻手動入力
などのマクロがあります。

それぞれのマクロには、Alt＋8で表示したこのボックスで、［オプション］をクリックすれば、ショートカットキーを設定できるので便利です。
タスク管理自体の効率化も欠かせません。

次のようなマクロを、標準モジュールに入れます。

Sub Task_Add() '新規タスク追加
    
    '行を挿入
    ActiveCell.EntireRow.Insert
    
    'フォントの色を黒に
    Selection.Font.Color = vbBlack
    
    '上のタスクの時間と日付をコピー
    Range("b" & ActiveCell.Row - 1).COPY Range("b" & ActiveCell.Row)
    Range("d" & ActiveCell.Row - 1).COPY Range("d" & ActiveCell.Row)


End Sub


Sub Task_Sort() 'タスクをソート
    

    'ソート条件クリア
    Worksheets("task").ListObjects(1).sort.SortFields.Clear
    
    'ソート条件設定
    Worksheets("task").ListObjects(1).sort.SortFields.Add Key:=Range("a9")  '記号
    Worksheets("task").ListObjects(1).sort.SortFields.Add Key:=Range("b9")  '日付
    Worksheets("task").ListObjects(1).sort.SortFields.Add Key:=Range("k9")  '終了時刻
    Worksheets("task").ListObjects(1).sort.SortFields.Add Key:=Range("d9")  '時間
    Worksheets("task").ListObjects(1).sort.SortFields.Add Key:=Range("f9")  'タスク

    'ソート実行
    Worksheets("task").ListObjects(1).sort.Header = xlYes
    Worksheets("task").ListObjects(1).sort.Apply
    
    '次のタスク
    If Range("k10") = "" Then
       Range("k10").Select
    Else
        Range("k" & Range("e9").End(xlDown).Row).Select
    End If
    
    ThisWorkbook.Save
    
    
End Sub
 


Sub Task_Delete() 'タスク削除

    ActiveCell.EntireRow.delete
  
    
End Sub



Sub Task_StartTime() '開始時刻を入れる

  ActiveCell.Value = ActiveCell.Offset(-1, 1).Value

End Sub


Sub Task_Copy() 'タスクのコピー
    
    '行を挿入
    ActiveCell.EntireRow.Insert
       
    '上のタスクをコピー
    Range("b" & ActiveCell.Row - 1, "d" & ActiveCell.Row - 1).COPY Range("b" & ActiveCell.Row)
    Range("f" & ActiveCell.Row - 1).COPY Range("f" & ActiveCell.Row)

    'フォントの色を黒に
    ActiveCell.EntireRow.Font.Color = vbBlack
End Sub


Sub yellow() 'セルを黄色にする

    Selection.Interior.Color = vbYellow
    
End Sub

未完了タスクの繰越は、あえて自動化していません。
（未完了はないのが理想なので）
翌日に日付を変える必要があります。

Excel、マクロの勉強にもなりますので、挑戦してみていただければ。

なお、タスクの収集は、Evenoteのしくみを使っています。

昨日は、ランで芝浦へ行きセミナーに参加して、豊洲に行って、有楽町で仕事して、夜は千駄ヶ谷。
私にしては移動の多い日でした。

夜はトライアスロンチームポセイ丼の決起集会。
6/11のバラモンキング（スイム3.8km バイク180.2km　ラン42.2km）に参加する全メンバー8人がそろいました。

【昨日の1日1新】
※詳細は→「1日1新」

ポセイ丼8人で決起集会
とあるセミナー