ページを更新しました。
fastclassinfo.com
上記のページで、最新情報を記載しています。
本ページの情報は古いので、ぜひ上記のページの情報をご覧ください。
月末が近づくたびに、
・大量のデータ処理しないと…
・残業しなければ…
・またエクセルに入力…
こんなことを感じていませんか？
実は、エクセルマクロを使えば、月別集計のデータ計算を一瞬で終わらすことができます。しかも一度仕組みを作ってしまえば、あとは面倒な入力も一切不要。設定によっては、ボタンを押すだけで仕事が終わります。
この記事では、企業でエクセルマクロを教えた経験もある私が、データ計算を自動化して、月末日処理を一瞬で終わらせる方法を紹介します。
すぐに使えるサンプルも記載していますので、ぜひこのまま読みすすめていってください。
ページを更新しました。
fastclassinfo.com
上記のページで、最新情報を記載しています。
本ページの情報は古いので、ぜひ上記のページの情報をご覧ください。
- この記事で紹介するマクロとは？
- 月末日処理(月別集計)を自動化するマクロの作り方
- 月末処理マクロを使うときの注意事項
- ファイルをダウンロードしたい方はコチラから
- このエクセルマクロのコードを自力で編集できるレベルになりたいなら
この記事で紹介するマクロとは？
この記事で紹介するマクロは、
左の表に、右のデータベースに格納してあるデータに入れ込む
左の分析結果の表には、
①で指定した日付の期間内で
②各担当者が受け持った案件の
③取引先別の
④取引金額の合計
が表に格納されます。
ボタンを押すと、2,3秒で、以下の結果が出力されます。
もう少し具体的に言うと、
データベースに格納されているデータを読み込んで
あらかじめ作っておいた表に「指定した日付の範囲内で、各担当者が受け持った案件の取引先別の引金額の合計」の結果を
ボタンを押すだけで、入れ込む
そんなマクロです。
月末日処理(月別集計)を自動化するマクロの作り方
ここからは、紹介したマクロの作り方をステップバイステップで紹介していきます。ぜひこのまま読みすすめていってください。
ステップ1｜シートを3つ作成
まず、エクセルファイルの中に、3つのシートを作成します。ここでは、「Data」,「Result」,「設定」の3つを用意します。
ステップ2｜データベースを作成する
「Data」シートに四角で穴のないデータ一覧を作成します。以下の画像のように四角で、穴がない(中が詰まった)データ一覧にします。
ここで紹介するマクロは、データが1000行でも10000行でも、自動で範囲を読み込んで、処理を行うので、データがどれだけあろうと問題ありません。
ただし、四角で穴のないデータリストでないと、マクロ、ピボットテーブル、関数での自動計がうまくいきません。
たとえば、以下の4つはデータリストとしては、不具合が生じやすいので、注意しましょう。
不具合が生じやすいデータリストの例1
データは四角だが、横に複数個並んでいる
不具合が生じやすいデータリストの例2
データは四角だが、途中で穴があいている
不具合が生じやすいデータリストの例3
データはバラバラで、しかも途中で穴があいている(しかも空欄が多く、セルが結合されている)
不具合が生じやすいデータリストの例4
データは四角のように見えるが、ところどころ穴があいている
四角で穴のないデータ一覧が良い理由は、こちらの無料動画講座でDPRというフレームワークとして詳しく解説しています。
興味がある人は、ぜひ勉強してみてください。
ステップ2｜データ分析の日付を設定するドロップダウンリストを作成
「Result」シートに、データの集計開始と集計終了の日を入力するときのドロップダウンリストを作成します。ここもマクロにしたい！と思われるかもしれませんが、ドロップダウンリストで選択したほうが便利です。
たとえば、「データ集計の範囲を変えたい」という場合、範囲を選択しなおす必要があります。その変更のたびに、マクロのコードを直すのはメンドウなので、あえて手入力を採用します。
もちろん月末日を指定するマクロを設定することもできます。(Datediffやweekdayなどの関数を利用します。)
しかし、月末日といっても、それぞれの企業で定義が異なりますし、コードを覚えるのも大変なので、コードは省略します。
なお、ドロップダウンリストの設定方法は、こちらの記事で詳しく紹介していますので、興味があれば、あわせて読んでみてください。
ステップ3｜データ分析結果を出力する表を作成
データ集計の結果を表として出力するために、表を作成しておきます。
この記事では、
・横軸に担当者
・縦軸に取引先
としています。ここは、必要であれば、書き換えても問題ありません。ただし10行目から始まるようにしてください。そうしないと、マクロが正しく作動しなくなります。
補足ですが、、合計の部分には、エクセルワークシートのSum関数を入れ込んでいます。
必要であれば、あなたの事例にあわせて、入れ替えてみてください。
ステップ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をクリック
[2] 標準モジュールを追加する
[3] VBEのコード領域に上記のコードをコピペする
コードをコピーしたら、黒で囲った部分にペースト(貼り付け)する
[4] VBEを閉じる
補足｜もし「開発」が見当たらないとき
「ファイル」→「オプション」→「リボンのユーザー設定」まで進んで、コマンドの選択©で「メイン タブ」から「開発」を入れ込みましょう。
「開発」をタブに入れ込むことで、とくに不具合は生じませんので、ぜひ設定してみください
ステップ5｜ボタンを設置してカンタン集計
ボタンを押して計算ができるようにしたい！
ここでは、そんなあなたに、ボタンを設置して、マクロが動くようにする方法を紹介します。手順は以下のとおりです。
[1] 開発→挿入→ボタン を選択
[2] ボタンを設置したい場所をマウスでドラッグ
[3] 登録したマクロを選択し、OKをクリック
[4] ボタンの名称を変更するなら、ボタンを選択して右クリック
[5] 名称を変更
一つずつ解説します
[1] 開発→挿入→ボタン を選択
[2] ボタンを設置したい場所をマウスでドラッグ
[3] 登録したマクロを選択し、OKをクリック
[4] ボタンの名称を変更するなら、ボタンを選択して右クリック
[5] 名称を変更
これで、ボタン一つでデータを自動集計できるようになります。
月末処理マクロを使うときの注意事項
このマクロを使用する上で、いくつか注意事項がありますので、紹介しておきます。
注意1｜データは上書きされるので、必要なら名前を変えて保存する
ボタンを押すたびに、データ分析結果が表に出力されます。単純に上書きされ、保存されません。
もし、あなたがデータ分析結果を残しておきたい場合は、別ファイルとして名前を変えて保存しておいておきましょう。
注意2｜縦軸や横軸への追加はいくつでもOK
Resultのシートの縦軸や横軸に追加する場合は、いくつ追加しても、問題なくマクロは作動します。このコードを使えば、自動で縦軸や横軸の数が増えても、自動で読み込んでくれます。
注意3｜行追加、列追加すると、マクロが作動しない
Resultのシートは、行追加や列追加すると、マクロが作動しません。コードを理解できれば、その理由もわかるかと思います。ぜひ、コードを理解して、自力で編集できるようになりましょう。
注意4｜シートの名前は変更しないこと
もし、この記事で紹介したマクロのコードをそのまま転用するなら、「Data」,「Result」,「設定」の3つのシート名を変更してはいけません。このシート名称でないと、マクロが動きません。
注意5｜Resultシートは10行目から開始すること
もし、この記事で紹介したマクロのコードをそのまま転用するなら、「Result」シートの表の位置は10行目から変更してはいけません。この位置を変更するには、マクロのコードを編集する必要があります。
これらの制約を乗り越えるには、コードを自力で編集する力を身に付けるのが、もっともてっとり早い方法です。
ぜひ勉強して、自力で編集できるようになって自由自在にデータ処理できるようになりましょう！
ファイルをダウンロードしたい方はコチラから
今回紹介したエクセルファイルは、一から作るのは大変なので、今回作ったファイルは無料でダウンロードできるようにします。以下のフォームにメールアドレスを入力いただくと、返信メールからエクセルファイルをダウンロードできます。
ぜひご活用ください。
このエクセルマクロのコードを自力で編集できるレベルになりたいなら
このコードを利用すれば、月末の処理が一瞬で終わるようになります。あなたの仕事がラクになるだけでなく、ほかの人の仕事も一気に終わらせることができるようになります。
しかし、このコードをこのままコピペして使うことができるケースはほとんどないでしょう。
なぜなら、このコードを使うためには、以下のどちらかを設定しないといけないからです。
・あなたが使っているエクセル表を手直しする
・あなたのエクセル表に合わせて、上記のコードを変更する必要がある
さらに、この記事で紹介したコードを転用すると、注意事項で記載とおり、数々の制約を受けます。
ですので、もしあなたが仕事を効率化したいのであれば、ぜひこのマクロのコードを参考にエクセルを編集することをオススメします。
もし、あなたが自分で編集してみたい！と思うのであれば、こちらの無料オンライン動画講座を受講することをオススメします。
本よりも手軽に始めることができて、費用もゼロなので、オススメの勉強方法です。
もし、本から始めたいという場合は、こちらの記事で、本を紹介していますので、参考にしてみてください
この記事を読んだ方は、こちらもオススメです。ぜひ合わせて読んでみてください。
「マクロの力を実感したい」と感じているなら、こちらの記事がオススメです。マクロが入ったエクセルファイルをダウンロードできるようにしています。もし詳細が知りたいなら、以下の記事で紹介していますので、合わせて読んでみてください。
サンプル1｜2つのデータを比較照合するマクロ
サンプル2｜エクセルで在庫管理するマクロ
サンプル3｜outlookのメールを自動送信する
サンプル4｜outlookの受信メールをエクセルに一覧にして、添付ファイルも保管する
サンプル5｜インターネットに自動アクセスする
サンプル6｜ヤフオクの情報を自動取得して、エクセルに出力する
サンプル7｜エクセルの情報をワードに差し込み、印刷まで行う
ぜひご活用ください。
次ページ 無職・派遣の男がたった1年で、仕事で年収100万アップし、海外プロジェクトリーダーに抜擢された「たった1つ」の方法とは？
