VBAでプロジェクト進捗管理グラフの週次ステータスを集計する

プロジェクトの進捗状況を報告するための週次棒グラフをVBAで自動集計します。

今回のサンプルは、筆者がある企業のコスト削減プロジェクトのレポート作成担当だった際、実際に利用していたプロジェクト進捗管理グラフを集計するのに利用していたVBAコードをアレンジしたものです。

本記事では架空のプロジェクト「年末までに10万円をオークションで稼ぐ」として、各週次ごとに分かれたワークシートをVBAで毎週自動集計させるサンプルコードをご紹介します。

なお次の動画は、このページでダウンロードできるExcelファイルを使って、プロジェクト管理をする方法についてご紹介しています。 動画をご覧になった上で、本記事を読み進めて頂ければより理解が深まると思いますが、5分程度の音声無し動画ですので、お時間ありましたら参考にしてみてください。

VBAでプロジェクト進捗ステータスを自動集計させるサンプルファイル

サンプルファイルの内容についてご紹介します。なおサンプルファイルは、本項最後でダウンロードできますので、よろしければご利用ください。

プロジェクト進捗グラフ表示用シート

1枚目のシートはグラフシートです。本記事では、オークションで年末までに10万円を稼ぐという架空のプロジェクトの進捗フォローをしますが、そのプロジェクトの進捗状況が視覚的に把握できるグラフがここに表示されます。

グラフの縦軸は金額、横軸は毎週のフォロー日を月曜日にした日付軸です。また、プロジェクト進捗グラフは棒グラフになりますが、内訳を次のような5段階のステータスとしています。

• L1：アイディア出し（出品商品のリストアップ）
• L2：出品準備中（出品用の記事作成・商品撮影など）
• L3：出品準備OK（出品手続きをするだけの状態）
• L4：出品中（値下げによる再出品も含む）
• L5：落札済み（取引成立！）

ちなみに「L」は、Level（レベル）の頭文字のことで、L1からL5に進むにつれてプロジェクトが進んでいることを意味します。

グラフ用データ集計用シート

棒グラフの元になるデータの集計先になります（下図）。

シート1列目にはステータスがあり、積上げ棒グラフの内訳を構成します。また横並びに日付が入力されています。日付間隔は1週間で、進捗フォローする日の毎週月曜日が記載されています。

毎週ステータスを更新する週次ステータス報告シート

プロジェクト進捗状況を、具体的な指標やステータスの更新で行うためのシートです。下図は、例として初めの3週間分の週次ステータス報告シートを示しています。サンプルファイルには、このような週次報告ステータスシートが12月16日まで、計16枚があります。

シートの項目は、「ID」、「アイテム」、「出品価格/落札価格」、「ステータス」で、ここでリスト化した出品商品の価格とステータス管理をして毎週フォローします。

サンプルファイルには、既に12月16日までのデータが入力してありますので、この後にご紹介するサンプルコードを実行することにより、グラフシートにはプロジェクト完了までの棒グラフが表示されることになります。

週次レポート用シートの具体的な使い方

プロジェクトの進捗を具体的に報告するための週次レポート用シートの使い方を簡単にお話ししますね。

項目「ID」はプロジェクトの個々のタスクやアイテムを管理するために使います。項目「アイテム」は、タスクやアイテムなど具体的な実施内容などを書くのに使います。

項目「出品価格/落札価格」は、個々のタスクやアイテムの具体的な数値による指標を入力します。この項目がグラフの縦軸として集計されます。

項目「ステータス」は、プロジェクト進捗度をレベル管理するためのものです。グラフ表示では、積上げ棒グラフの内訳を色分けで構成します。図にある通り、入力規制によるリストの中から選択することで入力の手間を省いたり、入力値のゆらぎによる集計漏れを防ぐ工夫をしています。

以上の内容を収めたサンプルファイルが下記からダウンロードできますので、よろしければご利用下さい。

VBAでプロジェクト進捗ステータスを自動集計させるサンプルコード

それでは、今回のサンプルコードのご紹介です。ぜひ真似して書いてみてくださいね。

VBAサンプルコード実行結果

それでは、書き終わりましたらVBAを実行してみてください。

VBA実行後のグラフ表示結果

まずグラフの内容が下図のように変わりましたでしょうか。

週ごとに棒グラフが表示され、内訳のステータスは青系で表示されます。青色が濃くなるほどL1からL5へステータスが進むようにしていますので、プロジェクトの進み具合が視覚的に分かるようになってます。

VBA実行後のグラフ用集計表

次に、グラフ用集計表の状態を確認しておきましょう。VBA実行後の様子は、下図のようになります。

日が進むにつれて、L1からL5の方に金額が集計されプロジェクトが進んでいく様子が分かりますよね。

VBAでプロジェクト進捗ステータスを自動集計させるアルゴリズム

変数宣言

7行目： Dim wstGrph As Worksheet, rngGrph As Range, intWRow As Integer, intWCol As Integer
「グラフ用集計表」シートで使う変数をここで宣言しています。wstGrphはWorkSheetオブジェクト用変数です。rngGrphはグラフ用集計表のすべてのデータを含むセル範囲をセットするRangeオブジェクト変数です。

intWRowおよびintWColは、グラフ用集計表上のどの行列に金額を集計するかを決める書込み行と書込み列を保持する変数です。

11行目： Dim wstSelf As Worksheet, rngSelf As Range
週次レポート用シートに使う変数をここで宣言しています。週次レポート用シートは、全部で16枚ありますがFor文で1枚ずつ繰り返して処理をしますので、ここで宣言している2つの変数で足ります。

wstSelfは、各週次レポートシート用のWorksheetオブジェクト変数です。rngSelfは、各週次レポートシート上にリストアップされているすべてのレコードを含むセル範囲用のRangeオブジェクト変数です。

8行目： Dim aryGrph As Variant
12行目： Dim arySelf As Variant
これらは、配列用変数として宣言しています。aryGrphは「グラフ用集計表」シート上のデータ保持用で、arySelfは各週次レポート用シート上のデータを保持する配列変数です。

「グラフ用集計シート」初期化

VBAコード17～25行目は、各週ごと、各ステータスごとに分けて金額データを集計する「グラフ用集計シート」の初期設定になります。

まずVBAコード17行目では、「グラフ用集計用」シートをWorksheetオブジェクトへセットしています。そして、VBAコード20行目で金額データが集計される範囲（図の赤枠）をクリアします。

つづく21行目では、Rangeオブジェクト変数「rngGrph」に配列に代入するデータ範囲（図の青枠）をセットしています。

この時、直接集計されるデータとは関係ない「日付」や「ステータス」項目も含めて、Cell(1,1)からセットしている理由は、配列要素番号とExcelシートの行列番号を一緒にしてVBAコードの可読性を良くするためです。

Tips：配列要素番号とExcelシート行列番号を合わせるテクニックを詳細に解説した記事
「VBA集計業務で二次元配列を使い処理を高速化させるサンプル」

25行目： aryGrph = rngGrph.Value
VBAコード21行目でRangeオブジェクトにセットした値をここで二次元配列に代入しています。この時、配列側の要素番号は1, 2次とも1から始まりますので、ちょうどExcelシートのCells(1,1)がaryGrph(1,1)となります。

各週次レポートシートを1枚ずつ繰り返し集計するアルゴリズム

27～60行目：For i=2 to 17 ～ Next
このFor文による繰り返し処理により、各週次レポート用シートのデータを「グラフ用集計表」シート上に、日付別・ステータス別に金額を集計させています。

ここでiが2から始まり17で終わってますよね。この理由は、サンプルファイルの2番目のワークシートが、先頭の週次レポート用シート「0902」であるからです。そして、i=17すなわちサンプルファイルの17番目のワークシートが、最後の週次レポート用シート「1216」に相当します。

サンプルファイルを見ますと、「グラフ」シート、「グラフ用集計表」シートに続き、3番目に「0902」シートがありますが、1枚目の「グラフ」シートはVBAコード上ではChartsオブジェクトですので、Worksheets(1)が「グラフ用集計表」シートになります（下図）。

各週次レポート用シートの初期設定

VBAコード28行目から35行目までは、各週次レポート用シートの初期設定部分になります。

28行目： Set wstSelf = Worksheets(i)
各週次レポート用シートをWorksheetオブジェクト変数にセットします。

今回のコードの中では、 先にVBAコード17行目で設定した 「グラフ用集計表」シート 用Worksheetオブジェクト「wstGrph」 と区別して扱う必要がありますから、ここでもう1つの Worksheetオブジェクト変数にセットしています。

VBAコード30～32行目では、各週次レポート用シートのデータ範囲（下図赤枠）をRangeオブジェクト変数にセットしています。

図で示したように、このVBAコードを使うとレコード数に合わせて範囲取得ができます。図の左側は、プロジェクト開始早々の「0902」のシートで、レコード数が2の場合です。

また、図の右側はアイディアが10件でそろった段階「0916」シートのデータ取得範囲を示しています。

なお、Resizeの詳しい使い方やCurrentRegionの詳しい解説については別の記事で詳しく解説しておりますのでご参照ください。

35行目： arySelf = rngSelf.Value
VBAコード31行目でセットしたセル範囲のデータを配列に代入しています。この時のセル範囲は、直前の図の中で示した通りID1番からレコードの数だけでしたよね。よって、配列1次要素の番号は、項目IDに一致します。

週次レポート用シートのレコードを読み取るアルゴリズム

VBAコード37～59行目までは、各週次レポート上のすべてのレコードの金額を、ステータス別に集計するコードになります(下図）。

38～58行目： For j = 1 To UBound(arySelf) ～ Next
このFor文の中で、各週次レポート用シート上のすべてのレコードの金額データをステータス別に集計しています。

For文の終わりにある「UBound(arySelf)」は、VBAコード35行目で配列に格納された1枚の週次レポート用シート上にあるレコード数に相当します。UBound関数を使うことで配列要素の最大番号が、レコード数に等しいことを利用しています。

39～57行目： Select Case arySelf(j, 4) ～ End Select
Select文の条件式に配列変数「artSelf(j, 4)」の値を使い、処理を場合分けしています。「arySelf(j, 4)」は、週次レポート用シート上のレコード項目が4列目の値「ステータス」になります。

よって、 Select文の条件式の「ステータス」値を読んで、その後の処理を分岐させています。その分岐処理の部分ですが、下図朱書きの通り5つのステータスに対応させています。

そして各分岐条件で実行されるVBAコードは、配列への加算集計であり、加算する値「arySelf(j, 3)」は、レコード項目3列目の「金額」になります。それでは加算式と集計先の表との関係を図で示しましたのでご覧ください。

図の右下にある集計先の表への書込み行は、ステータス別にExcelシート行番号2から6までに配置されてます（図中青枠）。また集計先の表への書込み列は、Excelシート列番号2以降17まで（一部見えませんが図中赤枠）になります。

ここで集計式にある「i」ですが、VBAコード27行目のFor文の中でワークシートのインデックス番号として設定しました。前出の説明で「0902」シートはi=2でしたが、実は集計先の書込み列番号と一致させています。

すべての週次レポート用シートの集計結果をグラフ用集計表に表示する

62行目： rngGrph = aryGrph
すべての週次レポート用シートの集計が終わった後、集計結果が格納された配列の値をグラフ用集計表に戻して表示させるVBAコードです。これにより、集計結果がグラフシートに棒グラフとして表示されて、視覚的にプロジェクト進捗状況が確認できます。

まとめ

プロジェクト進捗状況をExcel棒グラフでフォローするサンプルをご紹介しました。本記事のサンプルファイルとサンプルコードをアレンジすることで、実際の実務で遂行されるプロジェクトの進捗状況を視覚的にフォローすることができます。

視覚的に進捗状況を把握できる工夫としては、棒グラフの内訳をL1からL5までとし、さらにステータスが進むにつれて棒グラフの色を濃くしていることです。またグラフの横軸を時系列にすることで、プロジェクト期限までの時間を把握でき、縦軸からはプロジェクトの目標達成度が把握しやすいことです。

実務では企業活動を推進するために、さまざまなプロジェクトが日々動いており、一人が複数のプロジェクトを受け持つことも少なくありません。その上で、常に各プロジェクトの進捗状況を把握することは、とても大切なことです。

簡易的とは言え、今回ご紹介したようなプロジェクト進捗管理ツールを使うことで、Excelという誰もが親しみやすいグラフを常にチェックできますので、その結果プロジェクトチームメンバー内でのモチベーション維持やリスク管理が共有されやすくなります。