進捗管理を自動化するエクセルの作り方【設定の方法や注意点を解説】

疑問がある人

業務進捗状況をエクセルを使って効率よく管理したい。いい方法ってないの？

こんな疑問にお答えします。

結論からいうと、別シートや別ブックから情報を集約するマクロで進捗管理表を作成する！です。

エクセルで進捗管理をしようとした場合、以下のような問題が生じます。

「進捗状況を確認して管理表に入力する」という面倒な入力作業が生じる
管理表に入力した内容にミスがないかチェックしなければならない
案件数やチームの人数が多ければ多いほど管理表の作成に時間がかかる

エクセルマクロを設定すれば、効率の悪い手入力作業を完全自動化することができます。

以前勤めていた会社では、社内監査対策のために手作業で何時間もかけて進捗管理表を作成していました。

拒否する人

円滑に業務を進めるための管理表を残業して作成するなんてありえない

管理表の作成内容としては、担当者や期日などの情報を確認しながら一覧表に入力するという単純作業がほとんどだと思います。

ガッツポーズの人

単純作業はマクロを使って一瞬で処理可能です

この記事では、進捗管理の簡単なサンプル事例を使って管理表の作成を自動化するエクセルマクロの作り方についてご紹介しています。

1 そもそも進捗管理とは？
2 進捗管理エクセルのサンプル事例
3 進捗管理表の作成を自動化することで仕事の効率アップ
4 業務内容に合わせたVBAを開発するためには基礎を学ぶ必要がある

そもそも進捗管理とは？

進捗管理とは、予定通り業務が進んでいるのか、忘れられている業務はないのかなど、業務全体の進捗状況の把握・管理を行うことです。

進捗状況の把握を効率よく行うために、業務ごとの要点をまとめた一覧表が必要になります。

例えばこんな感じ

担当者や契約状況など、最低限の情報をまとめた一覧表が進捗管理表です。

企業ごとに「担当者」「契約状況」「連絡先」がまとめられているので、一目で「各企業との仕事の進み具合」を確認することができます。

私の会社では「進捗管理の自動化」に力を入れるようになってから、効率よく仕事を進められているというのを実感することができました。

業務の進め方に問題があると素早く改善
余力がある社員を忙しい案件の助っ人に配置する
やり残し、やり忘れ業務の激減

正解の人

案件数や従業員が多くなればなるほど進捗管理の重要度が上がっていきます

進捗状況管理の悪い例がこちら

・各担当者が編集している進捗管理表エクセルを一つ一つ確認する

→　チーム全体の進捗状況を一目で確認できない

悩んでる人

めちゃくちゃ効率が悪くチェック漏れが多発

・進捗管理用のブックを作成し、各担当者が自ら編集する

→　進捗状況の入力作業が負担になり更新漏れが発生。結局は進捗管理用ブックを使わなくなる

拒否する人

各担当者に無駄な作業を追加しただけ

これらの方法はあまり効率のいい手段ではありません。

情報を集約するVBAを使えば最新の情報を一瞬で一つの一覧表にまとめることができるので、

手作業で行うデメリットを全て解消することができます。

進捗管理エクセルのサンプル事例

これから紹介するサンプル事例は、勤めていた会社で実際に行っていた進捗管理の仕組みを用いています。

サンプル事例

担当者ごとにシートを作成し、各シートの重要な項目だけを「案件進捗シート」に集約する

イメージとしてはこんな感じ

設定したマクロを実行するだけで下記の一覧表を一瞬で作成することが可能

担当者は、仕事の進捗に応じて自分のシートを編集するだけ。

管理者は、マクロを実行して各担当者が編集した最新の情報を集約した一覧表を確認するだけ。

正解の人

使い方もかなりシンプルでわかりやすいと思います

VBAを使って情報を集約することで、転記ミスを防止し作業時間を短縮することができます。業務に合わせた様式に設定することで、どんな様式にも対応することができます。

進捗管理自動化マクロの導入方法

上記で紹介した「サンプル事例に設定されているマクロの導入方法」を画像を使って具体的にご紹介していきます。

1.様式設定

最初に設定するのが、「様式設定」です。

様式設定はめちゃくちゃ重要な作業なので、やり直しがないようしっかり確定してしまいましょう。

※マクロを書き始めてから様式の変更をしようと思ったらかなりめんどうです。

2.VBAを起動

様式を確定したら、VBAを起動させプログラムを書いていきます。

上記画面ではすでにプログラムが表示されていますが、最初にVBAを起動させたときは空白になっているはずです。

3.標準モジュールを作成

プログラムは、「Module」という場所に書いていきます。

上記のように「Module」を作成してください。

4.ModuleにVBAを記入

作成した「Module」にプログラムを書いていきます。

プログラムの意味を1行1行説明するとかなり時間がかかってしまうので、省略させてもらいます。

今回は下記に掲載している「サンプルVBA」をコピペして使ってください

5.Moduleに記入するサンプルVBA

Sub 案件進捗()
''''①シートの名前を指定
    Dim Ash As Worksheet
    Dim Bsh As Worksheet
    Dim Csh As Worksheet
    Dim Dsh As Worksheet
    Dim Esh As Worksheet
    Dim Fsh As Worksheet
    Set Ash = ThisWorkbook.Worksheets("案件進捗")
    Set Bsh = ThisWorkbook.Worksheets("Aさん")
    Set Csh = ThisWorkbook.Worksheets("Bさん")
    Set Dsh = ThisWorkbook.Worksheets("Cさん")
    Set Esh = ThisWorkbook.Worksheets("Dさん")
    Set Fsh = ThisWorkbook.Worksheets("Eさん")
 '''「案件進捗」シートのB列目の最終行を取得
    ga = Ash.Cells(Rows.Count, 2).End(xlUp).Row
 '''BシートからNシートのBT列目の最終行を取得
    gb = Bsh.Cells(Rows.Count, 2).End(xlUp).Row
    gc = Csh.Cells(Rows.Count, 2).End(xlUp).Row
    gd = Dsh.Cells(Rows.Count, 2).End(xlUp).Row
    ge = Esh.Cells(Rows.Count, 2).End(xlUp).Row
    gf = Fsh.Cells(Rows.Count, 2).End(xlUp).Row
'''②「案件進捗」シートのリセット
    Ash.Range(Ash.Cells(5, 2), Ash.Cells(ga, 21)).Clear
    Ash.Range(Ash.Cells(5, 2), Ash.Cells(ga, 21)).ClearFormats
'''AシートB4セルに１を入力
    Ash.Cells(4, 2) = "1"
'''③「Aさん」シートから「案件進捗」シートに情報を転記
    For i = 6 To gb
    ga = Ash.Cells(Rows.Count, 2).End(xlUp).Row
    For j = 2 To 18
    Ash.Cells(ga + 1, j) = Bsh.Cells(i, j)
    Next j
    Next i
'''④「Bさん」シートから「案件進捗」シートに情報を転記
    For i = 6 To gc
    ga = Ash.Cells(Rows.Count, 2).End(xlUp).Row
    For j = 2 To 18
    Ash.Cells(ga + 1, j) = Csh.Cells(i, j)
    Next j
    Next i
'''⑤「Cさん」シートから「案件進捗」シートに情報を転記
    For i = 6 To gd
    ga = Ash.Cells(Rows.Count, 2).End(xlUp).Row
    For j = 2 To 18
    Ash.Cells(ga + 1, j) = Dsh.Cells(i, j)
    Next j
    Next i
'''⑥「Dさん」シートから「案件進捗」シートに情報を転記
    For i = 6 To ge
    ga = Ash.Cells(Rows.Count, 2).End(xlUp).Row
    For j = 2 To 18
    Ash.Cells(ga + 1, j) = Esh.Cells(i, j)
    Next j
    Next i
'''⑦「Eさん」シートから「案件進捗」シートに情報を転記
    For i = 6 To gf
    ga = Ash.Cells(Rows.Count, 2).End(xlUp).Row
    For j = 2 To 18
    Ash.Cells(ga + 1, j) = Fsh.Cells(i, j)
    Next j
    Next i
'''⑫「案件進捗」シートに罫線を引く
    ga = Ash.Cells(Rows.Count, 2).End(xlUp).Row
    Ash.Range(Ash.Cells(5, 2), Ash.Cells(ga, 18)).Borders.LineStyle = xlContinuous
    Ash.Range(Ash.Cells(5, 2), Ash.Cells(ga, 18)).Borders(xlInsideVertical).LineStyle = xlContinuous
    Ash.Range(Ash.Cells(5, 2), Ash.Cells(ga, 18)).Borders(xlInsideHorizontal).LineStyle = xlContinuous
    Ash.Cells(4, 2).ClearContents
'''⑬E列からK列のテキストを中央揃えにする
    Ash.Range(Ash.Cells(5, 5), Ash.Cells(ga, 9)).HorizontalAlignment = xlCenter
'''⑭M列からQ列のテキストを中央揃えにする
    Ash.Range(Ash.Cells(5, 11), Ash.Cells(ga, 14)).HorizontalAlignment = xlCenter
'''⑮B列からU列の列幅を自動調整する
    Ash.Range("B:R").EntireColumn.AutoFit
'''⑯E列からG列の列幅を8に調整する
    Ash.Range("E:I").EntireColumn.ColumnWidth = 8
'''⑰M列からQ列の列幅を10に調整する
    Ash.Range("K:N").EntireColumn.ColumnWidth = 10
End Sub

このサンプルVBAを「Module」にコピペしてください。

プログラムは、

各担当者が編集する「個人シート」の情報を「案件進捗シート」に集約する

という内容になっています。

6.シートにVBAを記入

上記でコピペしたVBAを実行させるための仕組みを設定していきます。

今のままではVBAを書いただけでエクセルの編集画面でマクロを実行させることはできません。

今回は、「進捗管理」シートのA1セルをクリックすると標準モジュールに記入したVBAを実行するという仕組みにしていきます。

7.シートに記入するサンプルVBA

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    On Error Resume Next
    If Target.Row = 1 And Target.Column = 1 Then
    Call 案件進捗
    If Err <> 0 Then
    Err.Clear
    End If
    End If
End Sub

引数Targetを使って、「セルをクリックするとVBAを実行する」という処理を行っています。

他にも、「案件進捗シートに切り替えたタイミングでVBAを実行する」といった処理にすることも可能です。

エクセルファイルの運用方法に合わせてVBAの実行方法を確定していきましょう。

下記の記事では、VBA実行方法の設定について詳しく解説されています。