【ExcelVBA】VBAで分析ツールを作ってみよう【ジョインの基礎】

みなさん、こんにちはケンケンです。

今回から、Excelを使った分析ツールを作っていきます。

実を言うと僕はいままで分析の仕事はほとんどしたことがありません。

なので、分析がどういうものかを基礎から学んでいるところです。

勉強の過程をみなさんにシェアしながら一緒に学んでいけたらと思っていますので、

お付き合いいただけるとうれしいです。

なお、分析に関しては基礎中の基礎からお伝えするのですが、

ツール作成についてはVBAを駆使していきますので、VBAの基礎に不安があるかたは

以下の記事などを参考にして勉強していただければと思います。

関連記事

 みなさん、こんにちはケンケンです。   今回はVBA入門編の6回目となります。突然ですが、本編を進める前にこの記事にたどり着いてくれた方へプレゼントです。以下でForNext構文の書き方を動画(音声なし)でご紹介しているので[…]

VBAと分析をミックスさせて業務の幅を広げたい方におすすめの記事となっていますので、

興味を持たれた方は最後までお読みください。

それでは、いってみましょう。




 

元データを用意しよう

まず、分析するための資料(データ)を作ります。

なんでも良いのですが、商品売買を前提とした資料を作ってみます。

今回は、4つのデータを各ファイルに格納している前提でお話を進めます。

資料構成は以下の通りとします。

item.xlsx 商品一覧データ(商品名、値段など)
kokyaku.xlsx 顧客データ(氏名、メールアドレスなど)
transaction.xlsm 購入明細データ
集計.xlsm 各種データをまとめて集計するファイル

以上4つのファイルをひとつのフォルダに格納しておきます。

シートの中身を見てみよう

4つのファイルの形式をザッとみていきましょう。

以下のようになります。

capture-20191208-144252.png

capture-20191208-145205.png

capture-20191208-150836.png

capture-20191208-150605.png

capture-20191208-150737.png

集計.xlsmのSheet2にはあらかじめグラフを用意しておきます。

VBAで集計.xlsmのSheet1に各種数値を集計することで自動的にグラフが作成されるように仕組みを作って行きます

では、具体的にコードを書いていきましょう。

各種ファイルをひとつのファイルへ集約しよう

ファイルが4つに分かれているので、それをひとつに統合しなくてはなりません。

実務では、まとめたいデータが都合よくひとつのファイルに格納されているとは限らないので、

まずは統合することから考えましょう。

最初に考える必要があるのは、データを集めて何をしたいか、です。

今回は、購入データを集計して各月の売上をグラフにまとめることを目的とします。

その場合、もっとも核となるデータは購入明細データが格納されているtransaction.xlsxとなります。

このようなデータは粒度が高いと表現します。

では、最初に「transaction.xlsx」を開いて「集計.xlsm」に転記するコードを書いてみます。

Sub transaction_open()
    If Dir(ThisWorkbook.Path & "\transaction.xlsx") <> "" Then
        Workbooks.Open ThisWorkbook.Path & "\transaction.xlsx"
    Else
        MsgBox "ファイルが存在しません。", vbExclamation
    Exit sub
    End If
    
    Dim Lrow
    Lrow = Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row
    ActiveWorkbook.Sheets(1).Range("A1:E" & Lrow).Copy
    Workbooks("集計.xlsm").Activate
    Range("A1").PasteSpecial
    
    Rows("1:1").AutoFilter
    ActiveWorkbook.Worksheets("Sheet1").AutoFilter.Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Sheet1").AutoFilter.Sort.SortFields.Add2 Key:= _
        Range("B1"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("Sheet1").AutoFilter.Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Selection.AutoFilter
    
    Application.DisplayAlerts = False
    Workbooks("transaction.xlsx").Close
    Application.DisplayAlerts = True

End Sub

コードの解説

1~5行目:「transaction.xlsx」というファイルを検索し、見つかったらファイルを開きます。

見つからない場合は、警告を発してプログラムを終了します。

ファイル操作参考記事

みなさん、こんにちはケンケンです。 今回は、ファイルやフォルダを操作して、ファイル名一覧を作成したりファイルに記述されているデータを取得してシートに転記する方法をご紹介します。   実務で無数のファイルと格闘して[…]

6~10行目:開いた「transaction.xlsx」に格納されているデータをコピーして「集計.xlsm」に転記しています。

コピー&貼り付け参考記事

 みなさん、こんにちはケンケンです。   今回はVBA入門編の6回目となります。突然ですが、本編を進める前にこの記事にたどり着いてくれた方へプレゼントです。以下でForNext構文の書き方を動画(音声なし)でご紹介しているので[…]

11~23行目:転記したデータをB列、つまりcustomer_idを昇順に並び替えています。

データの見栄えを良くするために行ったものなので省略しても問題ありません。

フィルターの使い方参考記事

みなさん、こんにちはケンケンです。   今回は、エクセルに備わっているフィルター機能について深堀していきます。 みなさんもフィルター機能はよく使うと思います。 フィルター機能の基本からVBAを使った応用まで[…]

24~26行目:開いた「transaction.xlsx」を警告なしで閉じています。

使えるテクニック参考記事

みなさん、こんにちはケンケンです。   エクセルVBA入門編では、請求書作成を自動化してみました。 今回は、入門編でご紹介しきれなかったテクニックについて解説していきます。 入門編で作ったツールをカスタマイ[…]

さて、これを実行して以下のように転記できていれば成功です。

capture-20191208-154534.png

データを結合(ジョイン)してみよう

元となるデータを転記することができました。

次に考えるべきことは、必要なデータを付け加えることです。

現時点では、顧客名や商品の金額が分からないのでデータとしては不十分です。

そこで、「kokyaku.xlsx」と「item.xlsx」を合体して使えるデータにパワーアップしてみましょう。

このようにデータ同士を結合することをジョインと言うので覚えておくと良いでしょう。

では、ジョインするためのコードを書いていきます。

基礎をマスターしている方にとっては特に難しいことはないと思います。

Sub customer_join()
    'kokyaku.xlsxを開いて集計.xlsmへ転記
    If Dir(ThisWorkbook.Path & "\kokyaku.xlsx") <> "" Then
        Workbooks.Open ThisWorkbook.Path & "\kokyaku.xlsx"
    Else
        MsgBox "ファイルが存在しません。", vbExclamation
        Exit Sub
    End If
    
    Dim Ccnt
    Dim CLrow
    CLrow = Workbooks("kokyaku.xlsx").Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row
    Dim Ccnt2
    Dim CLrow2
    CLrow2 = Workbooks("集計.xlsm").Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row
    Dim Cidseach
    For Ccnt2 = 2 To CLrow2
        Cidseach = Workbooks("集計.xlsm").Sheets("Sheet1").Range("B" & Ccnt2).Value
        For Ccnt = 2 To CLrow
            If Workbooks("kokyaku.xlsx").Sheets("Sheet1").Range("A" & Ccnt).Value = Cidseach Then
                Workbooks("集計.xlsm").Sheets("Sheet1").Range("F" & Ccnt2 & ":" & "J" & Ccnt2).Value = Workbooks("kokyaku.xlsx").Sheets("Sheet1").Range("B" & Ccnt & ":" & "F" & Ccnt).Value
            End If
        Next
    Next
    Application.DisplayAlerts = False
    Workbooks("kokyaku.xlsx").Close
    Application.DisplayAlerts = True
End Sub

コードの解説

ファイルを開く部分と閉じる部分は同じなので説明は省略します。

その他の箇所で「集計.xlsm」に転記していますが、これもForNextの基礎を理解していれば難しくはありません。

「集計.xlsm」のB列を起点として「kokyaku.xlsx」からデータを抽出しています。

例えば、B2は「00001」となっていますね。

この値と同じデータを「kokyaku.xlsx」から探して、該当する顧客(キハラヤスオ)の情報を「集計.xlsm」へ転記しているのです。

Excel関数のVlookupと同様のものと考えてください。

ループを2回使っていることと、ファイル間の転記を行っているので少し複雑に見えるかもしれませんが、

そこまで難しくはありません。

ステップインモードを使って1行ずつコードを解析すれば、意味が分かるはずなのでチャレンジして解読してください。

以下のitem_joinプロシージャも同様の構造になっているので確認しておいてくださいね。

Sub item_join()
    'item.xlsxを開いて集計.xlsmへ転記
    If Dir(ThisWorkbook.Path & "\item.xlsx") <> "" Then
        Workbooks.Open ThisWorkbook.Path & "\item.xlsx"
    Else
        MsgBox "ファイルが存在しません。", vbExclamation
        Exit Sub
    End If
    
    Dim Icnt
    Dim ILrow
    ILrow = Workbooks("item.xlsx").Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row
    Dim Icnt2
    Dim ILrow2
    ILrow2 = Workbooks("集計.xlsm").Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row
    Dim Iidseach
    For Icnt2 = 2 To ILrow2
        Iidseach = Workbooks("集計.xlsm").Sheets("Sheet1").Range("D" & Icnt2).Value
            For Icnt = 2 To ILrow
                If Workbooks("item.xlsx").Sheets("Sheet1").Range("A" & Icnt).Value = Iidseach Then
                    Workbooks("集計.xlsm").Sheets("Sheet1").Range("K" & Icnt2 & ":" & "L" & Icnt2).Value = Workbooks("item.xlsx").Sheets("Sheet1").Range("B" & Icnt & ":" & "F" & Icnt).Value
                End If
            Next
    Next
    Application.DisplayAlerts = False
    Workbooks("item.xlsx").Close
    Application.DisplayAlerts = True
End Sub

事前に「集計.xlsm」に仕組みを用意しておこう

「集計.xlsm(Sheet1)」のM列N列と「集計.xlsm(Sheet2)」に仕組みを作っておくことで自動でグラフ作成ができるようにしましょう。

M列には商品の値段と数を掛け算して合計額を算出しておきます。

capture-20191208-162127.png

N列には取引があった月を抽出しておきます。

capture-20191208-162355.png

Sheet2のB列にSumif関数を使うことで月ごとの集計をします。

ちなみにグラフは縦棒グラフを選択しています。

capture-20191208-162751.png

さあ、ここまでできたら後はプロシージャをまとめてコードを実行するだけです。

Sub analysis_start()
    Call transaction_open
    Call customer_join
    Call item_join
End Sub

以下のように集計できグラフの作成までできればOKです。

capture-20191208-163754.png

capture-20191208-163833.png

まとめ

いかがでしたか。

今回は、分析するための集計までを自動化する方法をご紹介しました。

複数のファイルから必要事項を自在に抽出できると分析の手間がかなり簡略化できますね。

現状使っているデータを良く見て、何をどうすればやりたいことができるかを考えることが重要です。

是非、いろいろなやり方や集計方法を試してみてください。

「こんな方法あったよ」とか「もっとこうした方がいいよ」など僕に言いたいことがあったらお気軽に連絡ください。

それでは、また次回お会いしましょう。

————————————————————————————-

○もし、よろしかったら僕宛にメールください。

内容は質問・相談・共有など何でもけっこうです。

すべて読んで返信させていただきますのでお気軽にどうぞ!

kenken061856@gmail.com

※Gmail以外をお使いの方はアドレスをコピーして送信してください。

————————————————————————————–