パソコンスキルの教科書

パソコンスキルの教科書

東京大学大学院卒。博士課程に進学を志すも、担当教授と折が合わず、無職になる。医者を目指すも結局断念。田舎で派遣社員として働く。「スキルがなければ、仕事ももらえない」と悟り、ビジネススキルを学ぶ。プログラミング、英語を学び、一部上場企業へ転職。年間100時間以上の業務効率化を行い、社内講師に抜擢。海外の案件を担当し、数億円のプロジェクトに携わる。個人の事業でも、月売上100万を達成。現在は、自分の価値を高めるためのスキル向上支援を行う

エクセルマクロVBAでガントチャート作成して進捗管理する方法|作り方やフリーテンプレートも公開

f:id:gene320:20180501034011j:plain

仕事をしていると、業務の進捗管理はつきものです。しかし、チームで仕事をしていると、各個人の仕事の進捗はどうしても見えづらくなってしまいがちです。

なぜなら、仕事の見える化ができていないからです。

その結果、納期ギリギリになって、未完了のタスクが数多くあることに気づいたり、最悪の場合、納期を遅らせたりすることになるのです。

ただ、このようなミスは、ガントチャートで仕事を見える化することで解決できます。ガントチャートとは、各タスクのスケジュールを一覧で見えるようにするツールです。

実際、私もチームで仕事をするときは、ガントチャートを活用して仕事の進捗管理を行っていました。

そこで、エクセルマクロVBAを活用したガントチャートの作成方法を紹介します。

無料でダウンロードできるエクセルファイル(マクロ入り)も紹介しているので、このまま読み進めていってください。

エクセルで進捗管理!マクロでガントチャートを作成する方法(無料テンプレートもダウンロード可)

この記事で出来ること

この記事では、エクセルマクロVBAでガントチャートを作成する方法を紹介していきます。

まずは、以下の動画をご覧ください。この記事で解説するマクロの概要を紹介しています。


ガントチャート_Gantt chart

この記事で紹介するエクセルマクロによるガントチャートの概要

上記の動画で紹介した内容をまとめると、以下の通りです。

[1] 必要項目を入力すれば、自動でガントチャートを作成する
[2] 完了/未完了の進捗状況を数値化する
[3] 開始日と終了日を入力することで、色が変わる
[4] タスク、担当者、タスク状況は手動で入力する

これらのことを行うことで、半自動でガントチャートを作成することができます。

動画で紹介しているエクセルファイルは、この記事の後半でダウンロードできます。

そもそもガントチャートとは?

そもそもガントチャートとは何かというと、仕事を見える化するためのツールです。タスクとスケジュールが一覧で見えるようにして、仕事の進捗管理するときに使われます。

以下の画像がわかりやすい事例です。

f:id:gene320:20180501030434p:plain

左側にタスクが並び、右側に各タスクのスケジュールが色で塗られて仕事の納期や進捗を共有できるようになります。

この記事では、ガントチャートをエクセルマクロを作成します。

マクロでガントチャートを作成してスケジュール管理するメリット

マクロでガントチャートを作成するのには、いくつかのメリットがあります。

[1] チームで共有、見える化|仕事や工程の進捗管理を行いやすい
[2] 分析を自動化|マクロを使うことで、グラフ化や進捗率も自動計算できる
[3] ファイルの安全性|エクセル関数や条件付き書式を使うと、ファイルが破損のリスクがある

以下で詳しく説明していきます。

理由1|チームで共有、見える化

チームで仕事をするときは分業が基本です。そのとき、チームメンバーの仕事が終わらないと、自分に着手できないことがあります。

例えば、請求書の作成を考えます。このとき、仕事が完了していない状態で、請求書を作成することはできません。前の工程が終了している必要があるのです。

ただ、仕事の連携が悪いチームでは、誰がいつ仕事を完了させたのか分かりません。

そのため、請求書担当者が前の工程の担当者に問い合わせる必要が発生します。その結果、コミュニケーションによる仕事のロスが生じてしまうのです。

ガントチャートを利用すれば仕事を見える化しているため、このような連携ロスを防ぐことができます

理由2|分析の自動化

仕事をマネジメントするとき、どの仕事がどこまで終わっているのかを見るのは重要な作業です。

しかし、何がどこまで終わっているのかを判別するのは面倒な作業です。

なぜなら、担当者に聞かないとどこまで仕事が終わったのか分からないからです。

例えば、上司が幹部にプロジェクトの進捗するタイミングで、担当者に仕事の進捗を聞くことがあります。それをまとめて、進捗の資料をまとめたり分析したりするのです。

しかし、これは分析を自動化しておけば、担当者に入力してもらうだけで作業が終わります

ガントチャートに分析機能を付与すれば、進捗を聞くためにわざわざ時間をロスする必要がなくなるのです。

そして、エクセル上でどの仕事がどこまで終わっているのか見えるので、仕事のマネジメントはとてもラクになります。

理由3|ファイルの安全性

エクセル関数や条件付き書式を使うと、ファイルが破損のリスクがあります。

なぜなら、エクセル関数や条件付き書式は、ファイルを重くするからです。

例えば、エクセル関数を使っていると、このような関数式を見かけることがあります。

=IFERROR(VLOOKUP($I$3&"-"&$C8,在庫入出記録!$C:$Q,MATCH(E$6,在庫入出記録!$C$4:$Q$4,0),0),"")

このような式が、エクセルのセルの一つ一つに入力されている場合、エクセルファイルは重くなります。とくに200行,300行とデータが増えると、エクセルファイルへの負荷が大きくなります。

エクセルファイルの処理速度が遅くなり、エラーが出て、強制終了の頻度が増え、最悪の場合、ファイルが壊れます。せっかく作成したエクセルデータが壊れたら、復元のために無駄な仕事が増えてしまいます。

しかし、エクセルマクロVBAを使えば、ファイルへの負荷はほとんどありません。そのためファイルが壊れる危険も少ないのです。

ここではファイルの安全性の観点から、「エクセル関数よりマクロをお勧めする」とお伝えしました。

ただ、他の観点からみても、私はエクセル関数を習得するならマクロを習得する方がメリットが大きいと考えています。

他の観点とは、以下の7つです。

項目 エクセルマクロ エクセル関数
1.出来ること ・エクセルの自動計算
・パワポを自動作成
・アウトルックからメール送信
・ウェブ情報の取得
・エクセルの自動計算
2.周囲の評価 「なにこれ!?…魔法を使っているみたい。」と言われる すごい。この人はエクセル得意なんだ!
3.習得の難易度 簡単ではない(学び方次第) sum,if程度ならカンタン。関数を組み合わせると簡単ではない
4.エクセルファイルへの負荷 マクロやVBAでは、ファイルへの負荷は少ない 関数を大量に使うと、ファイルが壊れる危険がある
5.希少価値 「プログラミング?難しそう…」と敬遠しがち→希少価値高い とっつきやすい。手軽なイメージ→希少価値低い
6.削除されるリスク VBAコードの保存先を知っているのは経験者だけ 間違ってセルを削除されると、組んだ関数が消える
7.メンテナンス VBAを書ける人がいないとメンテナンスできない 関数が複雑になるとメンテナンスできない

その詳細は、こちらの記事で解説しています。ぜひ合わせて読んでみて下さい。

www.fastclassinfo.com

そのため、ガントチャートに限らず、私はエクセル関数や条件付き書式を使うことをお勧めしません。

エクセルマクロVBAでのガントチャートのプログラムソース

エクセルマクロVBAでガントチャートのプログラムソースについて紹介します。

この記事で紹介するガントチャートは、以下の5つのプログラムで構成されます。

[1] ガントチャートを作る
[2] イナズマ線を入れる
[3] 週/月で見えるようにセルの幅を調節する
[4] 進捗状況を見える化する
[5] 開始日と終了日を記載して、エクセルに自動で色を付ける

以下でプログラムのコードを紹介します。なおプログラムについては、深く解説はしません。

プログラム1|ガントチャートを作る

Sub new_project()

    Dim cmin As Long, cmax As Long
    Dim i As Long, j As Long, k As Long, l As Long
    Dim d1 As Date, d2 As Date, dx As Date
    Dim newsub As String
    Dim taskno As Long
    Dim ws1 As Worksheet, ws2 As Worksheet, ws3 As Worksheet, ws4 As Worksheet
    Dim ws As Worksheet
    
    Set ws1 = Worksheets("設定")
    Set ws2 = Worksheets("進捗管理表")

    'プロジェクト名称を入れ込む
    newsub = ws1.Range("E2").Value
    
    For Each ws In Worksheets
        If ws.Name = newsub Then
            MsgBox "シートにプロジェクト名と同じ名前があります。"
            Exit Sub
        End If
    Next
    
    '名称を入れ込む
    ws2.Copy After:=ws1
    Set ws4 = ActiveSheet
    
    
    ws4.Range("B1").Value = newsub
    ws4.Name = newsub

    'タスク数を入れ込む
    taskno = ws1.Range("F2").Value
    i = 0
    
    
    '期間を入れ込む
    d1 = ws1.Range("B2").Value
    d2 = ws1.Range("B3").Value
    ws4.Range("B3").Value = d1 & "~" & d2
    
    dx = d1

    With ws4.Range("H3")
        .NumberFormatLocal = "yyyy"
        .Value = dx
    End With

    With ws4.Range("H4")
        .NumberFormatLocal = "mm"
        .Value = dx
    End With

    With ws4.Range("H5")
        .NumberFormatLocal = "d"
        .Value = dx
    End With

    i = 1
    dx = dx + 1

    Do While dx <= d2

        If Left(ws4.Range("H3").Value, 4) <> Left(dx, 4) Then
            ws4.Range("H3").Offset(0, i).NumberFormatLocal = "yyyy"
            ws4.Range("H3").Offset(0, i).Value = dx
        End If

        
        ws4.Range("H4").Offset(0, i).NumberFormatLocal = "mm"
        ws4.Range("H4").Offset(0, i).Value = dx
    
        ws4.Range("H5").Offset(0, i).NumberFormatLocal = "d"
        ws4.Range("H5").Offset(0, i).Value = dx
        
        If Weekday(dx) = 1 Or Weekday(dx) = 7 Then
            ws4.Range("H5:H" & 5 + taskno).Offset(0, i).Interior.ColorIndex = 15
            
        End If
        
        dx = dx + 1
        i = i + 1
    Loop
    
    ws4.Range("A6:G" & taskno + 6).FillDown
    
    For j = 6 To taskno + 5
        ws4.Range("A" & j).Value = j - 5
    Next

    With Range(Cells(5, 1), Cells(5 + taskno, 7 + i))
        .Borders(xlDiagonalDown).LineStyle = xlNone
        .Borders(xlDiagonalUp).LineStyle = xlNone
        .Borders(xlEdgeLeft).LineStyle = xlContinuous
        .Borders(xlEdgeTop).LineStyle = xlContinuous
        .Borders(xlEdgeBottom).LineStyle = xlContinuous
        .Borders(xlEdgeRight).LineStyle = xlContinuous
        .Borders(xlInsideVertical).LineStyle = xlContinuous
        .Borders(xlInsideHorizontal).LineStyle = xlContinuous
    End With

    Range(Columns(8), Columns(8 + i)).ColumnWidth = 4
    Application.ScreenUpdating = True

End Sub

プログラム2|イナズマ線を入れる

Sub inazuma()
    ActiveSheet.Shapes.AddConnector(msoConnectorElbow, 200, 100, 250, 150). _
        Select
        Selection.ShapeRange.Line.EndArrowheadStyle = msoArrowheadTriangle
    Selection.ShapeRange.ShapeStyle = msoLineStylePreset1
End Sub

プログラム3|週/月で見えるようにセルの幅を調節する

Sub monthly()
    Dim cnt As Long
    cnt = Range("XFD5").End(xlToLeft).Column
    Range(Columns(8), Columns(cnt)).ColumnWidth = 2
End Sub

Sub weekly()
    Dim cnt As Long
    cnt = Range("XFD5").End(xlToLeft).Column
    Range(Columns(8), Columns(cnt)).ColumnWidth = 4
End Sub

プログラム4|進捗状況を見える化する

Sub koushin()
    Dim cmax As Long, i As Long
    Dim newsub As String
    Dim taskno As Long
    Dim ws1 As Worksheet, ws2 As Worksheet, ws3 As Worksheet, ws4 As Worksheet
    Dim n1 As Long, n2 As Long
    
    Set ws1 = Worksheets("設定")
    
    newsub = ws1.Range("E2").Value
    Set ws3 = Worksheets(newsub)
    
    
    taskno = ws1.Range("F2").Value
    
        
    cmax = ws3.Range("A65536").End(xlUp).Row
    n1 = 0
    n2 = 0
    
    For i = 6 To cmax
        If ws3.Range("G" & i).Value = "実施中" Then
            n1 = n1 + 1
        ElseIf ws3.Range("G" & i).Value = "完了" Then
            n2 = n2 + 1
        End If
    Next
    
    ws1.Range("I2").Value = n1 / taskno
    ws1.Range("I3").Value = n2 / taskno
    
End Sub

プログラム5|開始日と終了日を記載して、エクセルに自動で色を付ける

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim i, j As Long
    i = Target.Row
    j = Target.Column

    If i > 4 Then
        If j = 6 Or j = 7 Then
            Call schedule_update(i)
       End If
    End If
End Sub

このプログラムは、イベントプロシージャで作成しています。

セルの値を変更すると、自動でマクロが起動するように設定しています。

例えば、開始日と終了日を変更すると、自動でセルの色が変わります。(記事上部の動画でお見せしている通りです)

そのため、sheetの中のプログラムに設定しておきます。

以下は、標準モジュールで構いません。

Sub schedule_update(i)
    Dim d1 As Date, d2 As Date, hiduke As Date
    Dim j As Long
    Dim y As String, m As String, d As String
    Dim tantou As String

    'Application.ScreenUpdating = False
    If Range("E" & i).Value = "" Or Range("F" & i).Value = "" Then
        Exit Sub
    End If

    d1 = Range("E" & i).Value
    d2 = Range("F" & i).Value
    j = Range("H5").End(xlToRight).Column

    For j = 0 To j - 7
        
        'hidukeに格納
        hiduke = Range("H5").Offset(0, j).Value

        '一度、背景を白に戻す
        If Range("H" & i).Offset(0, j).Interior.ColorIndex <> 15 Then
            Range("H" & i).Offset(0, j).Interior.ColorIndex = xlNone
        End If
        
        '予定に反映を入れる
        If hiduke >= d1 And hiduke <= d2 Then
            If Range("H" & i).Offset(0, j).Interior.ColorIndex <> 15 Then
                Range("H" & i).Offset(0, j).Interior.Color = vbGreen
            End If
        End If

    Next
    
    Application.ScreenUpdating = True

End Sub

ここでお伝えしているイベントプロシージャや標準モジュールといった単語が分からない人は、こちらの無料オンライン動画で勉強することをお勧めします。

この動画を見れば、このサイトでお伝えしている内容をより理解できるようになります。

ガントチャートの入ったエクセル(マクロ入り)のテンプレートをフリーダウンロード

この記事でダウンロードできるマクロ入りのエクセルファイルは以下の動画で解説しています。

動画は、記事上部のものと同じです。


ガントチャート_Gantt chart

以下のフォームにメールアドレスを入力いただくと、返信メールからエクセルファイルをダウンロードできます。

ガントチャートの使い方・使用手順

使用手順は4つのパートに分かれます。

[1] ガントチャートを作る [2] ガントチャートのタスクとスケジュールを入力する [3] ガントチャートの機能を使う [4] 進捗状況を分析する

それでは、以下で詳しく説明していきます。

[1] ガントチャートを作る

●設定シートに以下の内容を入力する
[1-1] B2セルに「開始日」を入力(yyyy/mm/dd形式で入力すること)
[1-2] B3セルに「終了日」を入力(yyyy/mm/dd形式で入力すること)
[1-3] D2セル~に「担当者」を入力
添付エクセルでは5人ですが、担当者は何人でも可能です。そのままD列に記載すればOKです。
[1-4] E2セルに「プロジェクト名称」を入力
[1-5] F2セルに「タスク数」を入力(半角数字で入力すること)
[1-6] ボタン「ガントチャートを作る」をクリックする

f:id:gene320:20180501031151p:plain

[2] ガントチャートのタスクとスケジュールを入力する

●自動作成されたガントチャートのシートに以下の内容を入力する
[2-1] B列に「タスク名」を入力
[2-2] C列に「担当者」を入力
[2-3] D列に「備考」を入力(必要に応じて)
[2-4] E列に「開始日」を入力(半角数字で入力すること)
[2-5] F列に「終了日」を入力(半角数字で入力すること)
開始日と終了日の2つを入力すると、自動で色が付く(日付を変更すれば、自動で変更が反映される)
[2-6] G列に「タスク状況」を入力

f:id:gene320:20180501032034p:plain

[3] ガントチャートの機能を使う

●新しく作成したガントチャート上で操作する
[3-1] ボタン「イナズマ線」をクリックする
イナズマ線が出現するので、手動で移動して使う

[3-2] ボタン「月スケジュール」をクリックする
H列より右側の列の間隔を短くして、月単位でガントチャートを見えるようにする

[3-3] ボタン「週スケジュール」をクリックする
H列より右側の列の間隔を広くして、週単位でガントチャートを見えるようにする

f:id:gene320:20180501032139p:plain

[4] 進捗状況を分析する

●新しく作成したガントチャート上で操作する
[3-1] G列のタスク状況を変更する
「実施中」か「完了」を選択する

●設定シートを操作する
[3-2] ボタン「タスク進捗率」をクリックする
タスク進捗率が自動で集計される

f:id:gene320:20180501032329p:plain

f:id:gene320:20180501032335p:plain

さらにレベルの高いガントチャートを作成したいなら

この記事では、マクロのガントチャート作成方法を紹介しました。ここで紹介したガントチャートを利用すれば、プロジェクト全体の進捗が見えるようになります。

チームメンバーも仕事が見える化できて安心できます。何より、このガントチャートを使えば、プロジェクトをマネジメントする上司が安心するでしょう。

しかしデメリットもあります。それはメンテナンスです。

なぜなら、周囲からの要望も増えるからです。ここで紹介するガントチャートを使えば、「もっと○○ができるのでは?」や「ここはなんとかならないのか」といったことを言われるようになります。

例えば、「担当者毎に色を変えてほしい」や「ガントチャートに限らず、売上のデータ分析もしてほしい」といった依頼が来るのです。

このような依頼を満たすには、マクロを勉強して自力でマクロを編集できるようになる必要があります

もし、自力でマクロを編集できるようになれば、今より仕事の効率はグッと上がります。

実際、私も自力でマクロを書けるようになってからは、仕事の生産性が一気に上がりました。

他の人が30分~1時間かけて行う仕事が、ボタン一つで終わらせることができるようになったのです。その結果、周囲からの信頼も増し、仕事で高い評価を得られるようになりました。

ただ、要望に応えるようになるためには、マクロを学ぶ必要があります。

まずは無料でマクロを勉強してみる

ウェブや書籍で勉強すれば、マクロを習得できると考えている人は少なくありません。

しかし、仕事で使えるマクロを習得したいなら、仕事で使える部分に特化した教材で学ぶことをお勧めします。

なぜなら、ウェブや書籍には仕事に関係しない部分まで提供していることが多いからです。

例えば、マクロ初心者なのに配列を学ぼうとする人がいます。実は配列なしでも仕事で使えるマクロを書くことは可能です。

しかし、マクロ初心者ほど「全ての知識が必要だ」と考えて、無駄な学習に時間を使ってしまうのです。詳しくは、こちらの記事で紹介しています。

www.fastclassinfo.com

www.fastclassinfo.com

そこで、私がお勧めするのは仕事に直結するマクロ教材です。とくにお勧めするのは、こちらの無料オンライン動画です。

なぜなら、仕事に直結する部分に絞って、エクセルマクロを学ぶことができるからです。

マクロの作り方・考え方から解説しているので、教材をしっかり学べばここで紹介したマクロをゼロから書けるようになります。

マクロ初心者が、仕事に直結したいマクロを学ぶなら、まずはこちらの無料オンライン動画を試すのがいいです。

ただ、この無料動画だけではマクロを自由自在に書けるようになりません。

ガントチャートに新規機能をつけたりメンテナンスしたりできるようになりたいなら、有料講座で勉強してみる

もし、自力で編集して仕事を自由に自動化・効率化できるようになりたいなら、有料の講座で学ぶことをお勧めします。

なぜなら、相手の要望に応えたり他の機能と連動させたりするには、プログラムを理解した上でガントチャートを書けるようになる必要があるからです。

ガントチャートの有料講座の一部は、以下の動画で初回しています。


ガントチャートを作る-イントロダクション

これを学べば、ガンとチャートのメンテナンスから新機能まで自由に作ることができるようになります。

仕事で進捗管理をしたり、納期管理が必要だったりする場合はとてもおすすめです。

興味がある人は、こちらから受講可能です。

ぜひ、この記事の内容を実践して効率的な仕事ができるようにしてみてください。