パソコンスキル教科書

パソコンスキルの教科書

東京大学大学院卒。博士課程に進学を志すも、担当教授と折が合わず、無職になる。医者を目指すも結局断念。田舎で派遣社員として働く。「スキルがなければ、仕事ももらえない」と悟り、ビジネススキルを学ぶ。プログラミング、英語を学び、一部上場企業へ転職。年間100時間以上の業務効率化を行い、社内講師に抜擢。海外の案件を担当し、数億円のプロジェクトに携わる。個人の事業でも、月売上100万を達成。現在は、自分の価値を高めるためのスキル向上支援を行う

エクセルマクロVBAで月末日処理(月別集計)を自動化|データ計算作業を行う方法(テンプレート付)

f:id:gene320:20170618181211p:plain

月末が近づくたびに、

・大量のデータ処理しないと…
・残業しなければ…
・またエクセルに入力…

こんなことを感じていませんか?

実は、エクセルマクロを使えば、月別集計のデータ計算を一瞬で終わらすことができます。しかも一度仕組みを作ってしまえば、あとは面倒な入力も一切不要。設定によっては、ボタンを押すだけで仕事が終わります。

この記事では、企業でエクセルマクロを教えた経験もある私が、データ計算を自動化して、月末日処理を一瞬で終わらせる方法を紹介します。

すぐに使えるサンプルも記載していますので、ぜひこのまま読みすすめていってください。

エクセルマクロVBAで月末日処理(月別集計)を自動化(テンプレートあり)

この記事で紹介するマクロとは?

この記事で紹介するマクロは、

左の表に、右のデータベースに格納してあるデータに入れ込む

f:id:gene320:20170618180549p:plain

左の分析結果の表には、
①で指定した日付の期間内で
②各担当者が受け持った案件の
③取引先別の
④取引金額の合計
が表に格納されます。

ボタンを押すと、2,3秒で、以下の結果が出力されます。

もう少し具体的に言うと、

データベースに格納されているデータを読み込んで f:id:gene320:20170618175219p:plain

あらかじめ作っておいた表に「指定した日付の範囲内で、各担当者が受け持った案件の取引先別の引金額の合計」の結果を f:id:gene320:20170618175255p:plain

ボタンを押すだけで、入れ込む f:id:gene320:20170618175354p:plain

そんなマクロです。

月末日処理(月別集計)を自動化するマクロの作り方

ここからは、紹介したマクロの作り方をステップバイステップで紹介していきます。ぜひこのまま読みすすめていってください。

ステップ1|シートを3つ作成

まず、エクセルファイルの中に、3つのシートを作成します。ここでは、「Data」,「Result」,「設定」の3つを用意します。

f:id:gene320:20170618162123p:plain

ステップ2|データベースを作成する

「Data」シートに四角で穴のないデータ一覧を作成します。以下の画像のように四角で、穴がない(中が詰まった)データ一覧にします。

ここで紹介するマクロは、データが1000行でも10000行でも、自動で範囲を読み込んで、処理を行うので、データがどれだけあろうと問題ありません。

f:id:gene320:20170618162700p:plain

ただし、四角で穴のないデータリストでないと、マクロ、ピボットテーブル、関数での自動計がうまくいきません。

たとえば、以下の4つはデータリストとしては、不具合が生じやすいので、注意しましょう。

不具合が生じやすいデータリストの例1
データは四角だが、横に複数個並んでいる f:id:gene320:20170618162821p:plain

不具合が生じやすいデータリストの例2
データは四角だが、途中で穴があいている f:id:gene320:20170618162834p:plain

不具合が生じやすいデータリストの例3
データはバラバラで、しかも途中で穴があいている(しかも空欄が多く、セルが結合されている) f:id:gene320:20170618162842p:plain

不具合が生じやすいデータリストの例4
データは四角のように見えるが、ところどころ穴があいている f:id:gene320:20170618162854p:plain

四角で穴のないデータ一覧が良い理由は、こちらの無料動画講座でDPRというフレームワークとして詳しく解説しています。

興味がある人は、ぜひ勉強してみてください。

ステップ2|データ分析の日付を設定するドロップダウンリストを作成

「Result」シートに、データの集計開始と集計終了の日を入力するときのドロップダウンリストを作成します。ここもマクロにしたい!と思われるかもしれませんが、ドロップダウンリストで選択したほうが便利です。

たとえば、「データ集計の範囲を変えたい」という場合、範囲を選択しなおす必要があります。その変更のたびに、マクロのコードを直すのはメンドウなので、あえて手入力を採用します。

f:id:gene320:20170618163610p:plain

もちろん月末日を指定するマクロを設定することもできます。(Datediffやweekdayなどの関数を利用します。)

しかし、月末日といっても、それぞれの企業で定義が異なりますし、コードを覚えるのも大変なので、コードは省略します。

なお、ドロップダウンリストの設定方法は、こちらの記事で詳しく紹介していますので、興味があれば、あわせて読んでみてください。

www.fastclassinfo.com

ステップ3|データ分析結果を出力する表を作成

データ集計の結果を表として出力するために、表を作成しておきます。

この記事では、
・横軸に担当者
・縦軸に取引先

f:id:gene320:20170618163832p:plain

としています。ここは、必要であれば、書き換えても問題ありません。ただし10行目から始まるようにしてください。そうしないと、マクロが正しく作動しなくなります。

補足ですが、、合計の部分には、エクセルワークシートのSum関数を入れ込んでいます。

f:id:gene320:20170618164314p:plain

必要であれば、あなたの事例にあわせて、入れ替えてみてください。

ステップ4|マクロを導入する

月末処理のマクロのコードを紹介していきます。

Option Explicit
Sub keisan()

    '---変数を宣言---
    Dim cmax0, cmax1, cnt, yoko, i, j, k As Long
    Dim ws0, ws1, ws2 As Worksheet
    Dim torihiki, tanto As String
    Dim goukei As Double
    Dim d1, d2, d3 As Date
    
    '---ワークシートを宣言---
    Set ws0 = Worksheets("Data")
    Set ws1 = Worksheets("Result")
    Set ws2 = Worksheets("設定")
    
    Application.Calculation = xlManual
    
    '---集計開始日と集計終了日を指定---
    d1 = ws1.Range("B2").Value & "/" & ws1.Range("C2").Value & "/" & ws1.Range("D2").Value
    d2 = ws1.Range("B3").Value & "/" & ws1.Range("C3").Value & "/" & ws1.Range("D3").Value
    
    '---Dataシートを検索---
    cmax0 = ws0.Range("A65536").End(xlUp).Row
    cmax1 = ws1.Range("A65536").End(xlUp).Row
    cnt = ws1.Range("XFD10").End(xlToLeft).Column
    
    For k = 11 To cmax1

        torihiki = ws1.Range("A" & k).Value
        
        '---照合したい条件でデータベースを検索---
        For j = 0 To cnt - 3
            goukei = 0
            tanto = ws1.Range("B10").Offset(0, j + 1).Value
            
            For i = 2 To cmax0
            
                d3 = ws0.Range("D" & i).Value
  
                If d3 >= d1 And d3 <= d2 Then
                
                    If ws0.Range("B" & i).Value = torihiki Then
                        If ws0.Range("C" & i).Value = tanto Then
                            goukei = goukei + ws0.Range("H" & i).Value
                        End If
                    End If
                End If
            
            Next
            
            '---Resultシートの表に出力---
            ws1.Range("B" & k).Offset(0, j + 1).Value = goukei
        
        Next
        
    Next
    
    Application.Calculation = xlAutomatic
    
End Sub

コードの説明は、省略しますが、必要であれば解説します。

もし、コードをコピペして使う方法が分からない方は、コチラの手順を参考にしてください

手順

[1] 開発→Visual Basicをクリック
[2] 標準モジュールを追加する
[3] VBEのコード領域に上記のコードをコピペする
[4] VBEを閉じる
補足|もし「開発」が見当たらないとき

一つずつ解説します

[1] 開発→Visual Basicをクリック f:id:gene320:20170618171520p:plain

[2] 標準モジュールを追加する f:id:gene320:20161203212218j:plain

[3] VBEのコード領域に上記のコードをコピペする
コードをコピーしたら、黒で囲った部分にペースト(貼り付け)する
f:id:gene320:20161203212220j:plain

[4] VBEを閉じる f:id:gene320:20161203223204j:plain

補足|もし「開発」が見当たらないとき

「ファイル」→「オプション」→「リボンのユーザー設定」まで進んで、コマンドの選択©で「メイン タブ」から「開発」を入れ込みましょう。

「開発」をタブに入れ込むことで、とくに不具合は生じませんので、ぜひ設定してみください

ステップ5|ボタンを設置してカンタン集計

ボタンを押して計算ができるようにしたい!

ここでは、そんなあなたに、ボタンを設置して、マクロが動くようにする方法を紹介します。手順は以下のとおりです。

手順

[1] 開発→挿入→ボタン を選択
[2] ボタンを設置したい場所をマウスでドラッグ
[3] 登録したマクロを選択し、OKをクリック
[4] ボタンの名称を変更するなら、ボタンを選択して右クリック
[5] 名称を変更

一つずつ解説します

[1] 開発→挿入→ボタン を選択
f:id:gene320:20170618164616p:plain

[2] ボタンを設置したい場所をマウスでドラッグ
f:id:gene320:20170618164629p:plain

[3] 登録したマクロを選択し、OKをクリック
f:id:gene320:20170618164636p:plain

[4] ボタンの名称を変更するなら、ボタンを選択して右クリック
f:id:gene320:20170618164645p:plain

[5] 名称を変更
f:id:gene320:20170618164655p:plain

これで、ボタン一つでデータを自動集計できるようになります。

月末処理マクロを使うときの注意事項

このマクロを使用する上で、いくつか注意事項がありますので、紹介しておきます。

注意1|データは上書きされるので、必要なら名前を変えて保存する

ボタンを押すたびに、データ分析結果が表に出力されます。単純に上書きされ、保存されません。

もし、あなたがデータ分析結果を残しておきたい場合は、別ファイルとして名前を変えて保存しておいておきましょう。

注意2|縦軸や横軸への追加はいくつでもOK

Resultのシートの縦軸や横軸に追加する場合は、いくつ追加しても、問題なくマクロは作動します。このコードを使えば、自動で縦軸や横軸の数が増えても、自動で読み込んでくれます。

f:id:gene320:20170618165326p:plain

注意3|行追加、列追加すると、マクロが作動しない

Resultのシートは、行追加や列追加すると、マクロが作動しません。コードを理解できれば、その理由もわかるかと思います。ぜひ、コードを理解して、自力で編集できるようになりましょう。

注意4|シートの名前は変更しないこと

もし、この記事で紹介したマクロのコードをそのまま転用するなら、「Data」,「Result」,「設定」の3つのシート名を変更してはいけません。このシート名称でないと、マクロが動きません。

注意5|Resultシートは10行目から開始すること

もし、この記事で紹介したマクロのコードをそのまま転用するなら、「Result」シートの表の位置は10行目から変更してはいけません。この位置を変更するには、マクロのコードを編集する必要があります。

これらの制約を乗り越えるには、コードを自力で編集する力を身に付けるのが、もっともてっとり早い方法です。

ぜひ勉強して、自力で編集できるようになって自由自在にデータ処理できるようになりましょう!

ファイルをダウンロードしたい方はコチラから

今回紹介したエクセルファイルは、一から作るのは大変なので、今回作ったファイルは無料でダウンロードできるようにします。以下のフォームにメールアドレスを入力いただくと、返信メールからエクセルファイルをダウンロードできます。

ぜひご活用ください。

このエクセルマクロのコードを自力で編集できるレベルになりたいなら

このコードを利用すれば、月末の処理が一瞬で終わるようになります。あなたの仕事がラクになるだけでなく、ほかの人の仕事も一気に終わらせることができるようになります。

しかし、このコードをこのままコピペして使うことができるケースはほとんどないでしょう。

なぜなら、このコードを使うためには、以下のどちらかを設定しないといけないからです。
・あなたが使っているエクセル表を手直しする
・あなたのエクセル表に合わせて、上記のコードを変更する必要がある

さらに、この記事で紹介したコードを転用すると、注意事項で記載とおり、数々の制約を受けます。

ですので、もしあなたが仕事を効率化したいのであれば、ぜひこのマクロのコードを参考にエクセルを編集することをオススメします。

もし、あなたが自分で編集してみたい!と思うのであれば、こちらの無料オンライン動画講座を受講することをオススメします。

本よりも手軽に始めることができて、費用もゼロなので、オススメの勉強方法です。

もし、本から始めたいという場合は、こちらの記事で、本を紹介していますので、参考にしてみてください

www.fastclassinfo.com

エクセルマクロVBAについてもっと詳しく知りたいあなたへ

エクセルマクロVBAというのは、なんとなく知っているけれど、もう少し詳しく知りたい!というなら、こちらの記事で詳しく紹介しているので、ぜひ参考にしてみてください。

www.fastclassinfo.com