エクセルのマクロVBAでタイムトラッキングツールを作ってみよう!第2部 集計シートの作成

プログラミング

エクセルのマクロVBAでタイムトラッキングツールを作ってみる企画の第2弾です。

前回の記事で時間記録シートをつけるところまでを解説しました。

今回の記事では時間記録シートを仕事ごとに集計するための、集計シートを作っていきます。

集計方法概要

前回の記事で時間記録シートの作成方法を解説しました。

仕事ごとに開始時刻、終了時刻、仕事時間を時系列で並べることができます。

しかし、この時点では各仕事に何時間費やしたのかはパット見では分かりません。

そこで仕事ごとに集計を行います。

集計は以下の手順で行います。

  1. シートの定義
  2. 時間記録シートの仕事内容を重複しないように集計シートのリストに書き出す
  3. 集計シートに書き出した仕事内容について時間の合計を記入する
  4. 集計シートの内容を時間順に並べ替える

前準備

まず、「時間記録」シートとは別に、「集計」シートを用意します。

「集計」シート

「集計」シートはA列 仕事内容、B列 時間の表にします。

書式設定

時間(B列)

  ユーザー定義→「[h]”h” mm”m”」

今回の「集計」シートは、時間の列の「=終了時刻 – 開始時刻」の数式は不要です。

集計ボタン

「時間記録」シートに、「集計」ボタンを用意しました。今回はボタンは1つです。

「時間記録」シート

このボタンを押すことによって集計作業を行います。

ボタンにあたるVBAコードを買いていきます。

VBAコード解説

シートの定義

今回のVBAでは2つのシートを使用します。

あらかじめシート名を変数に置き換えておきます。

Setはオブジェクト型で定義したオブジェクト変数にオブジェクトを格納するときに使用します。

この記述により「時間記録」シートを「ws1」、「集計」シートを「ws2」と置くことができます。

置き換えなくても、毎回「Worksheets(“時間記録”).〜」と書くことでも記述することはできます。

変数に置き換えることによりコードが長くなることを防げるシート名が変わった際に1箇所だけ書き直せばいいという利点があります。

置き換えをしないと全ての箇所を書き直さなければなりません。

「時間記録」シートの仕事内容を重複しないように「集計」シートのリストに書き出す

具体的に書き出すと以下の手順になります。

  1. 「時間記録」シートの仕事内容を重複しないようにコレクションに追加
  2. コレクションの仕事内容を「集計」シートに書き出す

「時間記録」シートの仕事内容を重複しないようにコレクションに追加

「時間記録」シート

まず仕事内容をコレクション(Collection)に入れていきます。Dictionaryを使用することもできますが、Macで使うことができないため今回はCollectionを使用します。

まずは変数を宣言します。今回はCollectionの変数を「col」と定義します。

この記述はもっと短く1文で書くこともできます。

CollectionにはAddメソッドを使って「項目」と「キー」を追加していきます。

col.Add 項目, キー

Addメソッドを使い「時間記録」シートの仕事内容をコレクションcolに追加していきます。

単純に「時間記録」シートの仕事内容を順番にコレクションに追加していくコードは以下の通りです。(この時点ではエラーが発生し、成立しません。)

まずFor分を使って繰り返し処理を行っています。

For文による繰り返し処理

i (インデックス)がaからbまで処理を繰り返します。

For i = 1 To 3 であれば i が1から3の3回、

For i = 7 To 14 であれば i が7から14の8回処理を繰り返します。

この表現では、i が2からws1.Cells(Rows.Count, 1).End(xlUp).Rowまで繰り返すという処理になります。

「時間記録」シートには2行目から仕事内容を入れているので i= 2 To … となります。

ws1.Cells(Rows.Count, 1).End(xlUp).Rowは前回解説しました。ws1(「時間記録」シート)のA列(列インデックス1)の最終行です。

したがってこの記述は i が「時間記録」シートの仕事の行の最初(2)から、最終行(ws1.Cells(Rows.Count, 1).End(xlUp).Row)まで処理を繰り返す、となります。

col.Add ws1.Cells(i, 1), ws1.Cells(i, 1)

Addメソッドを使いコレクションcolに追加しています。今回は項目、キーを区別した使い方をしていないため、どちらも同じにしています。

ws1.Cells(i, 1)はws1 (「時間記録」シート)のA列 (列インデックス1)の仕事内容です。

For分でiが増えていくことにより、順に仕事内容を追加していくことができます。

したがってこの記述により、ws1 (「時間記録」シート)のA列 (列インデックス1)の仕事内容を順にコレクションcolに追加することができます。

しかし、仕事内容に同じ項目が複数ある場合、このまま実行すると以下のエラーが起きます。

「このキーは既にこのコレクションの要素に割り当てられています。」

コレクション内にキーは重複して追加することができません。

今回の場合「メール処理」という仕事内容が複数あります。

しかし、重複しないように別シートに並べようとしているので、重複する項目をコレクションに追加しないというのはむしろやりたいことです。

そこで重複した項目は追加しないまま処理を続行させるため、このエラーを無視させます。

On Error Resume Next と On Error GoTo 0を使います。

On Error Resume Nextと記述すると、エラーが発生しても中断せずに次のステートメントから実行を継続します。

On Error GoTo 0はエラー処理を無効にします。On Error GoTo 0以降でエラーが出ると中断されます。

したがって、On Error Resume Next と On Error GoTo 0の間の記述はエラーを無視して行われます。

このコードで「時間記録」シートの仕事内容をコレクションに追加することができます。

コレクションの仕事内容を「集計」シートに書き出す

「集計」シート

今回もFor分を使用します。

記述としては以下の通りです。

col.Countでコレクションcolの要素数です。Rows.Countと同じですね。

For i = 1 To col.Count でiが1からコレクションcolの要素数まで繰り返すという処理になります。

ws2.Cells(i + 1, 1) = col(i)

ws2 (「集計」シート)のA列 (列インデックス1)の上から順にコレクションcolの内容を書き出しています。

ws2.Cells(i + 1, 1)と行インデックスをiではなくi+1としているのはws2 (「集計」シート)の2行目から追加していくためです。

i = 1 のとき Cells(2,1)(A2セル)にコレクションの1つ目の要素を代入

i = 2 のとき Cells(3,1)(A3セル)にコレクションの2つ目の要素を代入

という感じです。変数が多く分かりづらくなった場合、具体的な数字を入れてみると理解しやすくなります。

「集計」シート

以上をまとめると以下のコードになります。

VBAコード

「時間記録」シートの仕事内容を重複しないように「集計」シートのリストに書き出す

集計シートに書き出した仕事内容について時間の合計を記入する

やりたいことを日本語で表すと以下の通りになります。

ws2 (「集計」シート)に書き出した全ての仕事内容について、ws1 (「時間記録」シート)の時間の合計を算出してws2のB列に書き出す。

人間にこのように指示したらなんとかなりそうですが、コンピューターでも理解できるよう指示する必要があります。

今回も繰り返し処理を使用しますが、今までに比べると少々複雑で慣れないうちは記述を思いつき辛いです。

そういった場合、まず具体的な1つの手順を書いてみることをおすすめします。

ws2 (「集計」シート)の仕事内容を1つにして考えてみましょう。

ws2 (「集計」シート)の仕事内容を1つ選び、ws1 (「時間記録」シート)の時間の合計を算出します。

ws2 (「集計」シート)の仕事内容をshigotoとおき、時間の合計をgoukeiとおきます。

Stringは文字列で、Variantはすべてのデータを格納できる型です。

まずgoukeiは初期では0として、ws2 (「集計」シート)の1つめの仕事をshigotoとします。

このshigotoに対してws1 (「時間記録」シート)の仕事内容を上から順に確認していき、もしws1の仕事内容とshigotoが一致すればgoukeiにws1の時間を足していきます。

ここではif文を使用しています。

if文による条件分岐

Else の部分は省くことができます。その場合は条件式に合致しない場合、何も処理が行われません。

今回のケースでは

のため、ws1 (「時間記録」シート)の仕事内容(ws1.Cells(i, 1).Value)がshigotoと一致する場合、goukeiにws1 (「時間記録」シート)の時間(ws1.Cells(i, 4).Value)を足します。

最後にシート2の仕事内容の右隣にgoukeiを入力します。

以上をまとめると以下の記述になります。

これによりws2 (「集計」シート)の1つ目の仕事(2行目の仕事)の合計は入力することができました。

あとはこれをws2 (「集計」シート)の全ての項目で行うように繰り返し処理を記述します。iはすでに使用しているため、jを使います。

これでjが2からws2 (「集計」シート)のA列(列インデックス1)の最終行まで繰り返すという記述になります。

繰り返しを行うのは上に書いた処理です。

あとは上でまとめた処理で必要な箇所をjに置き換えるだけです。

ws2 (「集計」シート)の仕事を順に処理していくため、ws2.Cells(2, 1)をws2.Cells(j, 1)とすればいいです。

「集計」シート

この時点でws1(「時間記録」シート)の内容を集計し、ws2 (「集計」シート)に書き出すことは完了しています。

しかし、ws2の並び順がws1の順そのままになっています。時間順に並べ替えたほうが見やすいため、もう少し修正を加えます。

VBAコード

集計シートに書き出した仕事内容について時間の合計を記入する

集計シートの内容を時間順に並べ替える

セルの並び替えはSortメソッドを使用し、以下の記述で行います。

Range1.Sort Key1:=Range2, Order1:=xlAscending or xlDescending

Range1が並び替えの対象範囲です。

Key1は並べ替えのキー列を指定します。

Order1で昇順(xlAscending)・降順(xlDescending)を指定します。

集計シートの内容を時間順に並べ替えるのは以下のコードになります。

1行目末の「_」は改行のときにつける記号です。それ自体に意味はありません。

VBAでは1行で書かなければならない記述は「_」無しで改行するとエラーになります。

Range(Cells(2, 1), Cells(ws2.Cells(Rows.Count, 1).End(xlUp).Row, 2))がRange1にあたります。パット見分かりづらいですが、Range(Cells(a, b),Cells(c, d))の形になっています。

Range(始点セル, 終点セル)

でセル範囲を指定します。

今回の場合A2セル(Cells(2, 1))からB6セル(Cells(6, 2))の範囲を並び替えます。

そのためセル範囲は

となります。この中で6はws2 (「集計」シート)の行数により変わる数字です。

したがって最終行に置き換えたほうがいいです。

ws2 (「集計」シート)の最終行はws2.Cells(Rows.Count, 1).End(xlUp).Rowです。(ws2.Cells(Rows.Count, 2).End(xlUp).Rowでもいいです。)

したがって並べ替えの対象範囲は

となります。

Key1:=Cells(2, 2)でB列(時間)をキーに指定、Order1:=xlDescendingで降順を指定しています。

これにより、時間をキーとして降順にリストが並び替えられます。

「集計」シート

VBAコード

集計シートの内容を時間順に並べ替える

集計ボタンのVBAコードまとめ

  1. シートの定義
  2. 時間記録シートの仕事内容を重複しないように集計シートのリストに書き出す
  3. 集計シートに書き出した仕事内容について時間の合計を記入する
  4. 集計シートの内容を時間順に並べ替える

集計シート完成

以上で集計シートは完成です。

仕事ごとにかかった時間がわかるようになりました。

これを元に時間の使い方や、仕事ごとにかかった時間を記録できるようになりました。

使ってみると「これにこんなにかかっているのか」と驚く場合も多いのではないかと思います。

今回やって驚いた方もいるかもしれませんが、マクロって以外と地道に処理を1つずつ書いているだけなんです。

コンピューターがわかるようなマニュアルを作るようなイメージです。

人間とコンピューターの違いは、コンピューターは行間を読んだりする考える融通はありませんが、正しく指示すれば、作業をミスなく超高速で繰り返すことができます。

今回のタイムトラッキングツールも、使いやすいようにこういった機能を追加しよう、などと考えればそのコード(マニュアル)を書いてあげればコンピュータはそのとおり動いてくれます。

是非使いやすいように改善してみてください。

Excel VBAの教科書 (Informatics & IDEA) | 古川 順平 |本 | 通販 | Amazon
Amazonで古川 順平のExcel VBAの教科書 (Informatics & IDEA)。アマゾンならポイント還元本が多数。古川 順平作品ほか、お急ぎ便対象商品は当日お届けも可能。またExcel VBAの教科書 (Informatics & IDEA)もアマゾン配送商品なら通常配送無料。

コメント

タイトルとURLをコピーしました