業務効率化の道具箱（11）VBAでグラフ描画が可能な自動集計アプリを作ってみよう：山浦恒央の“くみこみ”な話（164）（3/3 ページ）

ソフトウェア開発にとどまらない、PCを使う全ての人が対象となるシリーズ「業務効率化の道具箱」。第11回は、VBAによる自動集計アプリのさらなる進化版として、データを転記し、グラフ描画を行うツールを作ってみる。

[山浦恒央 東海大学 大学院 組込み技術研究科 非常勤講師（工学博士），MONOist]

※本記事はアフィリエイトプログラムによる収益を得ています

4.5 プログラム

　作成したシートを使用し、自動集計アプリのプログラムを作成します。このプログラムは、図8の「アプリ実行」を押すことで動作します。プログラムの作成例はリスト1の通りです。

Option Explicit
Sub ボタン1_Click()
    Const GRAPH_SHEET_NAME As String = "グラフシート"
    Const INPUT_SHEET_NAME As String = "入力ファイルシート"
    'ワークブック変数
    Dim wb As Workbook
    
    'コピー用のワークブック変数
    Dim copyWb As Workbook
    
    'グラフ表示用のシート変数
    Dim wsGraph As Worksheet
    
    'データ保持用のシート変数
    Dim wsData() As Worksheet
    
    '設定用のシート変数
    Dim wsInput As Worksheet
    
    '入力ファイル名保持用変数
    Dim inputFileNames() As String
    
    'ループ変数、グループ番号、ファイル数
    Dim i, j As Long
    Dim grNum As Long
    Dim fileNum  As Long
    
    'シート情報を代入
    Set wb = ThisWorkbook
    Set wsGraph = wb.Sheets(GRAPH_SHEET_NAME)
    Set wsInput = wb.Sheets(INPUT_SHEET_NAME)
    
    '入力するグループ数とファイル数を代入
    grNum = wsInput.Range("E2").Value
    fileNum = wsInput.Range("E3").Value
    
    '入力するデータに合わせて変数を再定義
    ReDim wsData(1 To grNum)
    ReDim inputFileNames(1 To grNum, 1 To fileNum)
    
    '入力したデータ数分のファイル名を代入する
    For i = 1 To grNum
        For j = 1 To fileNum
            inputFileNames(i, j) = wsInput.Cells(j + 1, i).Value
        Next j
    Next i
    '各種変数の宣言
    Dim grIdx As Long
    Dim rowMax As Long
    Dim columnOffset As Long
    grIdx = 1
    columnOffset = 1
    ReDim chartObjs(grNum) As ChartObject
    ReDim Charts(grNum) As Chart
    Dim pos1, pos2, pos3, pos4 As String
    
    'グループ数分繰り返す
    For i = 1 To grNum
        '散布図を作成する
        Set chartObjs(i) = wsGraph.ChartObjects.Add(Left:=100 + 500 * (i - 1), Width:=400, Top:=100, Height:=300)
        Set Charts(i) = chartObjs(i).Chart
        
        'データ用のシートを追加する
        Set wsData(i) = wb.Sheets.Add
        wsData(i).Name = wsInput.Cells(1, i).Value
        
        'ファイル数分繰り返す
        For j = 1 To fileNum
            Dim filePath As String
            
            'ファイルを開き、列数を取得する
            filePath = wb.Path & "\" & inputFileNames(i, j)
            Set copyWb = Workbooks.Open(Filename:=filePath)
            rowMax = copyWb.Worksheets(1).Cells(Rows.Count, 1).End(xlUp).Row
            
            'データをコピーする
            copyWb.Worksheets(1).Range("A1:B" & rowMax).Copy
            
            'データ保持用のシートに貼り付ける
            wsData(i).Cells(1, columnOffset).PasteSpecial xlPasteValues
            
            'ファイルを閉じる
            copyWb.Close False
            'グラフのX軸とY軸の位置を取得する
            pos1 = wsData(i).Cells(2, columnOffset).Address
            pos2 = wsData(i).Cells(2, columnOffset + 1).Address
            pos3 = wsData(i).Cells(rowMax, columnOffset).Address
            pos4 = wsData(i).Cells(rowMax, columnOffset + 1).Address
            
            'グラフが参照するデータ位置を指定する
            With Charts(i)
                .HasTitle = True
                .ChartType = xlXYScatter
                .SeriesCollection.NewSeries.XValues = "=" & wsData(i).Name & "!" & pos1 & ":" & pos3
                .SeriesCollection(grIdx).Values = "=" & wsData(i).Name & "!" & pos2 & ":" & pos4
                .SeriesCollection(grIdx).Name = inputFileNames(i, j)
                .ChartTitle.Text = "グループ " & i
            End With
            
            columnOffset = columnOffset + 2
            grIdx = grIdx + 1
        Next j
        'オフセットをリセット
        columnOffset = 1
        grIdx = 1
    Next i
End Sub

リスト1　自動集計アプリのプログラム作成例

4.6 動作の確認

　最後に動作確認をしてみましょう。

4.6.1 ボタンをクリック

　図9に示す「アプリ実行」ボタンを選択し、プログラムを実行します。

図9　アプリ実行ボタン

　アプリ実行ボタンを押すと、自動集計アプリが動作します。

4.6.2 実行結果

　プログラムの実行結果を以下の観点で確認します。

4.6.2.1 シートの作成

　このプログラムでは、グループに合わせてシートを作成し、その中にデータをコピーします。本記事の例では、グループ数2で実行することを想定しているので、下記のシートが追加されます（図10）。

図10　追加されたシート

　各シートのデータは、図11となります。

図11　グループごとのデータ例

　図11は各シートのデータの例です。このように、グループごとに指定したファイルが読み込まれていればOKです。

4.6.2.2 グラフの描画

　実行結果として図12に示すグラフを描画します。

図12　出力したグラフ［クリックで拡大］

　図12に示す通り、2つの散布図が表示されます。このようなグラフが出てくればOKです。なお、ファイル名がデータの系列に出るようになっています。

5．終わりに

　今回も、前回と前々回に引き続き、VBAを使った自作ツールの作成例を紹介しました。特に今回は、筆者が実際に依頼を受けた業務を題材としています。データの集計作業は単純作業ですが、こんな作業で残業してまで頑張ることはもったいないと考えます。

　手作業で頑張るのもいいですが、自分でツールを作ることも選択肢の一つとして考えていただければと思います。VBAを使って自作ツールを作ることで、作業の効率化や生産性の向上を図れます。ぜひ、この記事を自分の道具箱に入れて置き、必要な時に活用してみてください。

山浦先生の書籍が発売中です！

　前々シリーズ「ソフトウェア技術者のためのバグ百科事典」を大幅に加筆、修正した山浦恒央先生の書籍「ソフトウェア技術者のためのバグ検出テキスト」が日科技連出版から好評発売中です。連載でも取り上げた、「要求仕様書のバグ」「実装抜けのバグ」「テスト業務のバグ」など、バグを36種類に分類して解説しています。囲碁や将棋であれば、「相掛かり」「矢倉」「四間飛車」「藤井システム」のような戦法を網羅した内容になっています。

　前著「ソフトウェア技術者のためのバグ検出ドリル」（2019年11月刊行）も好評発売中です。実際にバグを含む要求仕様書、設計書、コーディング、デバッグ、保守を具体的に取り上げ、練習問題として31問を出題しました。同書は、囲碁や将棋における「次の一手」的な問題であり、ピンポイントの場面を取り上げ、実践力を鍛えることを目的としています。

　両書とも興味のある方は、Amazon.comや書店でチェックしてください！

【 筆者紹介 】

山浦 恒央（やまうら つねお）

東海大学 大学院 組込み技術研究科 非常勤講師（工学博士）

1977年、日立ソフトウェアエンジニアリングに入社、2006年より、東海大学情報理工学部ソフトウェア開発工学科助教授、2007年より、同大学大学院組込み技術研究科准教授、2016年より非常勤講師。

主な著書・訳書は、「Advances in Computers」 （Academic Press社、共著）、「ピープルウエア 第2版」「ソフトウェアテスト技法」「実践的プログラムテスト入門」「デスマーチ 第2版」「ソフトウエア開発プロフェッショナル」（以上、日経BP社、共訳）、「ソフトウエア開発 55の真実と10のウソ」「初めて学ぶソフトウエアメトリクス」（以上、日経BP社、翻訳）。