業務課題をHackせよ! プログラミングで仕事自動化物語(5) VBAでシステム化せよ! 毎月の成績グラフを3秒で完成させろと迫られる

この物語は、入社して一年目の新人社員が上司の指南のもと、業務を自動化していく様を記したものです。物語はフィクションですが、自動化の手法は実際にパソコンやスマートフォンで実践できるものですので、物語と自動化作業の両方を楽しめます。(前回のストーリーはこちら)

前回、緊急ミッションをこなした僕に、美人上司の太田先輩が、お昼ご飯を奢ってくれることになった。もちろん、主に仕事をこなしたのは太田先輩なのだが、上司に奢ってもらえるのは部下の特権なのかも。例えそれが牛丼屋だったとしても。

太田先輩を待たせないよう、急いで机の上を片付けて、オフィスを出ると、そこには、太田先輩に加えて、同じ経理部の佐藤先輩も居た。「うーむ。さすがに二人きりではなかったか・・・」と、がっかりしつつも三人で歩き出した。ちなみに、佐藤先輩は、すらっと高身長の30才独身男だ。そう言えば、太田先輩と佐藤先輩が話しているのをよく見かけるなぁ。

そんな複雑な思いで先頭を歩いていた僕は、牛丼屋に入るべく店の前で立ち止まった。その時、太田先輩が僕を呼び止めた。

「田中君、お昼は牛丼ではなくてお寿司になったの。」

「なぜいきなり牛丼から寿司に？！」と驚いている僕に、太田先輩は笑顔のまま牛丼屋の数件隣にある寿司屋に入った。寿司屋のテーブル席にて、くつろいでいると、太田先輩が僕に質問してきた。

「田中君、プログラミングが役立つことはもう分かったでしょう？」

「はい。太田先輩、先輩からプログラミングの素晴らしさを教わることができて光栄です。」

「田中君、大げさね。それでも、いつもプログラミングをするのが正しいというわけではないの。むしろプログラムを作らない方が良い場合もあるの。」

「え？どういうことですか？」

「それは、プログラムを作らないで手作業でやった方が早い場合よ。」

「確かに。プログラムを作らなくても良いなら、手作業でやるのが普通ですよね。」

そのとき、佐藤先輩が口を挟んできた。

「しかし！！人はプログラミングを覚えると、すべてをプログラミングで片付けたくなる生き物なのだよ。」

確かに、僕は華麗な太田先輩のプログラミングの腕に心酔し、世界のあらゆる出来事をプログラミングで解決できると思い込んでいた。佐藤先輩が続ける。

「プログラミングは所詮は手段なのであって目的ではない。そう言いたいんですよね、太田先輩？」

佐藤先輩は、太田先輩に笑顔を向ける。すると、太田先輩が続ける。

「そうなの。プログラミングの実力アップのためと思っているなら、最初はプログラミングに時間を掛けることも悪いことではないけれど。その仕事をもっとも早く片付ける方法は何かを考えるべきよ。」

太田先輩はさらに続ける。

「プログラムを作るよりも手作業が早ければ、そうするべきだし、紙に印刷して赤ペンで書き込んだりハサミで切ったりするのが早いのなら、そうするべきなの。つまり、作業に取りかかる前に、あらゆる可能性を考えてみて、どの方法で問題を解決するのが最も正確かつ早く仕事を終えることができるのか検討する必要があるということなの。」

僕はそれを聞いて「うーん、早く先輩たちのように、できる大人になりたいなぁ。」と答えるのが精一杯だった。

そして、太田先輩がお手洗いに場所を外した時のこと。佐藤先輩が僕に小声で話しかけてきた。

「おい、田中。太田先輩は、仕事もできるし、気も利くし、勉強熱心で、料理も上手。とにかく、素晴らしい人なんだ。しかも、完璧すぎるわけじゃなくて、ちょっと抜けているところもあって、最高なんだ。俺の言っている意味が分かるよな？」

「え？それって・・・」僕が驚いていると、太田先輩が帰ってきた。それで、その話は終わったのだが、どうやら佐藤先輩も太田先輩に惚れているということで間違いないようだ。

そのとき、美味しそうなお寿司プレートが運ばれてきた。美しいお寿司が机の上に四人分並んだ。「あれ？四人分ですか？」僕が不思議そうな顔をしているところへ、寿司屋にもう一人入ってきた。前回緊急ミッションを持ってきた眼鏡美人の笹川さんだ。

どうやら、笹川さんがミッションクリアのお礼として奢ってくれることになり、牛丼から寿司にバージョンアップしたようだ。僕らは笹川さんから美味しいお寿司をご馳走になり大満足だった。

さて、その日の午後。僕に割り当てられた仕事は、営業部の成績グラフを作ることだった。営業部では月一回の会議のたびに、売上額の成績グラフを出して成果発表会をするのが通例となっている。契約の種類ごとに、誰がいくら契約を取ったのかというグラフだ。

僕はプログラミング初心者であるものの、Excel自体の操作には習熟している。成績グラフの作成くらいあっという間に片付けることができる。でも、これって、毎月一回やる仕事なんだよね。僕はしばし考えてから太田先輩に近づいて尋ねた。

「太田先輩、先ほど頼まれた成績グラフの作成なんですけど、これってプログラムを作るべきですか？手作業でやった方が断然早く終わるんですけど。」

すると、太田先輩は逆に質問してきた。「田中君はどう思うの？」

僕は先ほど考えた結論を述べる。

「えっと、今回はプログラムを作るべきと思います。もちろん、件数が多いとはいえ成績グラフを作る作業自体は、それほど大変ではありません。しかし、この仕事は毎月、発生する仕事です。今回、多少時間がかかったとしてもプログラムを作っておけば、来月以降、楽ができると思うのですが、どう思いますか？」

すると、太田先輩は笑顔で答えた。「田中君、よく見極めることができたわね。定期的に発生することが分かっている仕事であるなら、プログラムを作ることをオススメするわ。プログラムを作っておくことで、仕事をシステム化できるというメリットもあるしね。」

「システム化ですか？」僕が何のことだか分からないという声を上げると、太田先輩は優しく解説してくれた。

「『システム化』とは、誰がやっても同じ成果を生み出せる体制のことよ。つまり、仕事の手順をプログラムにしておくなら、田中君の部署が変わってこの現場を離れたとしても、別の人が引き継いで同じ結果を出すことができるのよ。」

「なるほど。僕が突然死んだとしても、残された人が困ることなく仕事を進めることができるということですね。」

「田中君、入社早々に死んでもらっては困るわ。でも、そういうことよ。」と太田先輩は優しくフォローしてくれた。さらに、太田先輩が僕に気合いを入れてくれた。

「田中君。毎月の成績グラフを3秒で出せるようシステム化するのよ！」

誰がやっても同じ成果を3秒で生み出せるシステム・・・

僕はできる気になって「はい！任せてください！」と言ったものの、プログラムの作り方はぜんぜん分からなかった。

それで「太田先輩、僕、作り方がぜんぜん分からないんです。教えてください。」と教えを請うと、「残念。私忙しいの。」と断られてしまった。その代わり、VBA入門の本を貸してくれた。

とは言え、本を読んでも難しかったので、恋敵である佐藤先輩の力を借りることになったのだが、なんとかプログラムを完成させることができた。

契約一覧表を集計して各種グラフを出力するプログラムを作った

「太田先輩、僕、出来ました！」とプログラムを見せると、太田先輩が満面の笑顔で「よく出来たね。」と褒めてくれたのが嬉しかった。ただし、佐藤先輩が「ほとんど俺が教えたんだけどな」と太田先輩に告げ口していたのが解せなかった。「早く一人で何でも作れるようになりたい！」と強く思ったのだった。

実際のプログラム

それでは、今回、新人の田中君がライバル（？）の佐藤先輩と力を合わせて頑張って作ったプログラムを紹介しましょう。なお、今回のサンプルデータおよびVBAマクロ入りのデータは、こちらからダウンロードすることができます。

最初に、営業部の成績データですが、以下のような形式のExcelデータになっているとしましょう。

営業成績を表すExcelデータ

そして、このデータを元に、各契約の種別ごとにグラフを作成します。その際、担当ごとの契約金額を集計してグラフを作成します。

契約種別ごとに契約金額のグラフを作成する

元データには、契約一件ごとの契約金額が記録されています。そのため、グラフを作る手順は以下のようになります。

・（1）契約種類ごとにデータを分ける
・（2）各営業担当ごとに契約金額を合計する
・（3）上記データに対してグラフを作成する

Excelに慣れている方であれば、上記の一つ一つの手順を処理するのは難しくありません。しかし、本文で田中君が気づいたように、グラフを出力するまでの手順をVBAのマクロにまとめておけば、Excelに慣れていない人でも手軽にグラフを作成することができますし、面倒な操作手順を経てグラフを作成する手間が省けます。

実際にVBAマクロを実行してみましょう。

まず、契約一覧データを用意します。（このとき、データ形式は上記の書式にしておく必要があります。）そして、[Alt] + [F11]キーを押してVBAエディタを起動します。そして、契約一覧のシートを選択して、以下のプログラムを貼り付けましょう。

Sub MakeSubGraph(GType)
    ' 集計用の辞書 --- (*1)
    Dim dic As Variant
    Set dic = CreateObject("Scripting.Dictionary")
    ' 最終行を得る --- (*2)
    lastRow = Range("A1").End(xlDown).Row
    ' 繰り返しセルを調べる
    For i = 1 To lastRow
        aname = Cells(i, 1).Value
        atype = Cells(i, 3).Value
        aprice = Cells(i, 4).Value
        If atype = GType Then
            If Not dic.Exists(aname) Then
                dic.Add aname, aprice
            Else
                dic.Item(aname) = CLng(dic.Item(aname)) + CLng(aprice)
            End If
        End If
    Next
    ' 結果を出力 --- (*3)
    Dim sheet As Worksheet
    Set sheet = Worksheets.Add
    sheet.Name = GType
    i = 1
    For Each Key In dic.Keys
        sheet.Cells(i, 1).Value = Key
        sheet.Cells(i, 2).Value = dic.Item(Key)
        i = i + 1
    Next
    ' グラフを作成 --- (*4)
    sheet.Shapes.AddChart2(286, xl3DColumnClustered).Select
    ActiveChart.SetSourceData Source:=sheet.Range("A:B")
    ActiveChart.ChartTitle.Text = GType
End Sub

' 契約の種類ごとにグラフを作成する--- (*5)
Sub MakeGraph()
    MakeSubGraph ("リフォーム")
    MakeSubGraph ("中古住宅")
    MakeSubGraph ("新築住宅")
End Sub

そして、エディタ上部の実行ボタンを押すか、メニューから[実行] > [Sub/ユーザーフォームの実行]をクリックします。マクロの選択ダイアログが表示されたら「Sheet1 > MakeGraph」を選んで実行します。

マクロを実行しましょう

すると、契約種類ごとに新規シートが作成され、そこに営業ごとの合計契約金額の表とグラフが自動作成されます。

プログラムの簡単な解説

プログラムの(＊1)では、営業後との契約金額を集計するために、Dictionary型のオブジェクトを作成します。これは、macOSのExcelでは使えないWindowsのVBAならではの技ですが、データの集計に上に便利な機能でよく使われています。このDictionaryとは、連想配列と呼ばれるもので、キーと値をセットにしてデータを操作できます。

そして、(＊2)の部分では、契約一覧表の最終行を調べ、表の一行目からデータを確認していきます。セルの1列目(契約担当)、3列目(契約種類)、4列目(契約金額)を調べ、契約担当ごとに契約金額を合計していきます。

(＊3)の部分では新規シートを作成し、(＊2)の部分で集計した契約金額の表をセルに書き込んでいます。最後に(＊4)の部分で、(＊3)で書き込んだ表を元にして、グラフを作成します。

まとめ

今回は、大量のデータを一気に片付けるのではなく、定期的に発生するタスクを間違いなく決められた手順で素早く処理する方法の大切さについて紹介しました。実際、プログラムを作っておくことで、複雑なExcel操作をすることなく、データ集計後のグラフが自動作成できるのは、とても便利ではないでしょうか。業務自動化の参考にしてみてください。