Googleカレンダーの記録を活用してスプレッドシートに業務別のコスト計算

★気に入ったらシェアをお願いします!

cost

photo credit: Office Desk via photopin (license)

みなさん、こんにちは!
タカハシ(@ntakahashi0505)です。

引き続きGoogleカレンダーの記録を活用して過去の仕事の生産性を測定する方法を進めていきます。

前回はこちらの記事です。

Google Apps ScriptでGoogleカレンダーの記録をスプレッドシートに出力する
Googleカレンダーの記録を活用して仕事の生産性を測定する方法を進めています。Googleカレンダーの特定の月のイベントをスプレッドシートに出力するGoogle Apps Scriptを紹介します。

Google Apps Scriptを使ってGoogleカレンダーから先月のイベントをスプレッドシートに出力するという内容でした。

今回は出力したイベントデータに対して、スプレッドシートの各種関数FIND、LEFT、SUBSTITUTE、UNIQUE、SUMIFSを駆使して、業務カテゴリごとの所要時間そしてコストを算出をしていきたいと思います。

今回のお題:業務カテゴリごとにコスト計算をする

前回作成したスクリプトでカレンダーのイベントを出力した状態がこちらです。

日付・時刻の表示形式を変更したスプレッドシート

タイトルに墨付き括弧【】でカテゴリを入れてます。ブログとか、勉強とか、または取引先コードなど、業務別に振っています。

今回はこの業務カテゴリごとに所要時間を集計、時間あたりのコストを掛け算することでコスト計算をしていきたいと思います。

文字列から墨付き括弧内のテキストを抜き出す

まず各イベントのデータについて、B列のタイトルから業務カテゴリを抜き出したものをF列に出力したいと思います。

業務カテゴリは墨付き括弧内にあります。

タイトルが「【勉強】jQuery」であれば「勉強」と出力したいわけです。

この場合、スプレッドシートの3つの関数を駆使します。

FIND関数で特定の文字の位置を調べる

最初の手順として”閉じる方の”墨付き括弧、つまり「】」の位置を調べて、その手前までを切り取って抜き出すということをします。

特定の文字の位置を調べるにはFIND関数を使います。

=FIND(検索文字列, 検索対象のテキスト)

と書きます。

とすれば、閉じる方の墨付き括弧は4文字目ですから「4」という値が返ります。

LEFT関数で文字列を指定位置まで切り取る

FINDのおかげで、閉じる法の墨付き括弧の位置がわかりましたので、文字列をそこまでで切り取ってしまいます。

文字列を左から切り取るにはLEFT関数を使います。

=LEFT(文字列, 文字数)

と書きます。

としますと、「【勉強】jQuery」の左から3文字を抜き取るので「【勉強」が返ります。

SUBSTITUTE関数で文字を置き換える

続いて”始まる方の”墨付き括弧が残っているので、これを削除します。

特定の文字を削除するにはSUBSTITUTE関数を使います。

=SUBSTITUTE(検索対象のテキスト, 検索文字列, 置換文字列)

と書きます。

今回の場合は検索文字列は「【」、置換文字列はNULLにすればOKですから

とします、これで「勉強」が返りますね。

以上の関数を駆使して、F列に各イベントの業務カテゴリを抜き出してみると

スプレッドシートのイベントリストにカテゴリを抽出

このようになります。

UNIQUE関数で重複削除してリスト

次にH列から右側に、業務カテゴリごとに所要時間を計算していきたいと思います。

まず業務カテゴリをユニークにリストアップしたいですね。

エクセルだとリボンから「データの重複削除」の機能を選ぶことができるのですが、スプレッドシートはメニューではなくて関数を使います。

特定の範囲から重複削除したものをリストするにはUNIQUE関数を使います。

=UNIQUE(範囲)

と書きます。

今回の場合は、H1にカーソルを置いた状態にでF列全体を範囲として

とします。すると

スプレッドシートのUNIQUE関数

バッチリですね!スプレッドシートおそるべし。

ちなみにH列を並び替えしたい場合は、H列を並び替えをしてもウンともスンとも言いません。

その元であるF列を並び替えする必要がありますので、覚えておいて下さいね。

業務カテゴリごとの所要時間を算出する

続いてI列に各業務カテゴリごとの所要時間の総和を算出します。

2つの関数を使って算出していきます。

SUMIFS関数で条件付き総和を算出

まず、条件による総和を出す場合はSUMIFS関数を使えばよいですね。おなじみです。

=SUMIFS(合計範囲, 条件範囲1, 条件1)

と書きます。条件範囲と条件が複数ある場合は、この後にカンマで続けて設定できますよ。

とすれば業務カテゴリがブログであるイベントの所要時間の総和を算出できます。

これをI列にすべての業務カテゴリについてコピーしますと

スプレッドシートでSUMIFS関数

となります。

表示形式を数字の経過時間として下さいね。

時給を掛け算をしてコスト換算する

J列に時給1000円として(安!)コスト換算をしてみたのですが…

スプレッドシートでコスト算出

なんかおかしいですね。時給が安いにしても、あまりにも安すぎますね。

というのも、時刻に乗算をすると「日」をベースに計算してしまうのです。ですから日給1000円での計算になっちゃっているわけですね。

ですから、さらに24を掛ける必要があります。

スプレッドシートでコストを時給で算出

これでOKです。

まとめ

さて、今回はスプレッドシートのたくさんの関数を駆使して、業務カテゴリごとの所要時間、そしてコストを算出しました。

  • FIND
  • LEFT
  • SUBSTITUTE
  • UNIQUE
  • SUMIFS

UNIQUE以外はエクセルと同様ですね。覚えちゃいましょう。

ここまで何回かにわけて、Googleカレンダーの記録を使った生産性の評価をする方法についてお伝えしてきました。

これを使って、割に合っている業務、そうでない業務などを振り返って今後のアクションにつなげていきたいですね。

また、プロジェクト管理や給与計算などにも使えるのではないかと思いますので、ぜひご活用頂ければと思います。

次回はGoogleカレンダーの場所、説明を取得する方法についてお伝えします。

Google Apps ScriptでGoogleカレンダーの場所・説明を取得する方法
Googleカレンダーの記録をスプレッドシートに書き出して活用する方法についてお伝えしています。今回はGoogle Apps ScriptでGoogleカレンダーのイベントの場所と説明を取得する方法です。

どうぞお楽しみに!

連載目次:Googleカレンダーの記録を活用して過去の仕事を振り返る

企業ではスプレッドシートを勤怠管理やプロジェクト進捗管理などに使っている場合も多いと思います。このシリーズでは、カレンダーとスプレッドシートを連携をさせて仕事の生産性を簡単に測定する方法についてお伝えしています。
  1. 仕事の生産性を測定して振り返るためのGoogleカレンダーの使い方
  2. Google Apps Scriptで特定月のカレンダーのイベント情報を取得する
  3. Google Apps ScriptでGoogleカレンダーの記録をスプレッドシートに出力する
  4. Googleカレンダーの記録を活用してスプレッドシートに業務別のコスト計算
  5. Google Apps ScriptでGoogleカレンダーの場所・説明を取得する方法