【Excel VBA】週次集計と月間集計を作成する仕入・売上表

勤め先で業者ごとに商品の仕入額を月ごとに集計し、かつ週次集計もつくりたいという依頼がありました。

Excelでやる場合は月ごとにデータを分けたいところですが、そうすると週が途中で切れちゃったりといろいろ不都合。

そういうわけでExcel内にテーブルを作成し、指定の期間で自動的に集計ができるようなシステムをつくりました。

ぶっちゃけAccessでやれよって内容だと思うのですが、これぐらいの内容であればExcelで十分でしょうし、MacだとAccess使えませんしね。

サンプルデータ

excel-totalization-table.xlsm

サンプルのデータです。

ここからは基本的な機能の説明です。

要件としては、

  • 複数の拠点があり、それぞれ複数の取引相手がいる
  • 仕入にかかった費用を集計したい
  • 拠点・取引相手ごとの集計を、月間と週次で出したい

ってな感じです。

今回は仕入でやっていますが、売上でも機能的に問題ないはず。

個数を入力するようにつくっていますが、これは費用の計算を簡単にするためであって在庫管理は考えておりませんのであしからず。

テーブルへの登録

登録シートというのがメインのメニュー。

テーブルに登録するための入力画面、取引拠点と取引相手のマスタ、集計用のメニューがあります。

取引拠点と取引相手はサンプルということで、僕の地元・山科にある神社仏閣を並べました。

まずは日付と取引拠点、取引相手、商品名、単価、個数を入力します。商品は一度に5つまで入力できます。

入力した上で下の登録ボタンを押すと、商品名と単価と個数が消えます。これで登録完了です。

日付と取引拠点と取引相手は残るので、他にあればそのまま入力していきます。他になければ右上のリセットボタンで消えます。

登録を押した後、入力したデータは各取引拠点のシートのテーブルに登録されます。

月間の集計

各取引拠点ごとのシートがそのまま月間の集計になります。

例えば、最初は2020年12月21日のデータを登録しましたが、今度は2020年11月21日で登録してみます。

おそらくこの時点では取引拠点のシートを見ても登録したデータは表示されないと思います。

そこで、集計期間を2020年11月にして、集計ボタンを押してください。

これで各取引拠点のシートに2020年11月のデータが表示されるようになりました。

集計ボタンは、各取引拠点のシートのテーブルに指定した月でフィルタをかけるというマクロを登録しています。

つまり集計というよりも絞り込んでるだけですね。フィルタを解除したら全レコードが表示されます。

週次集計

集計の下にある週次集計の起算日を入力すると、そこから1週間分の集計が週次集計シートに表示されます。

ここはただの埋め込み関数です。マクロよりもこっちの方が良いと思います。

各取引拠点のテーブルの名前は取引拠点名で設定しています。2列目の取引相手であり、週次集計の起案日から終算日までであれば集計するというものです。

Excel形式で出力

Excelボタンを押すと、月間集計であればその月の取引拠点ごとのシートをまとめたExcelデータ、週次集計であれば起算日から1週間分のデータを表示したExcelデータを保存します。

保存先はこのマクロを動かしているブックと同じディレクトリです。

プログラムの中身的なことを言いますと、新規ブックを作成してコピペするだけです。

週次集計についてはコピペすると埋め込んでいた関数を移して元のブックとリンクになるため運用上あまりよろしくありません。コピペした後に値で貼り付け直しています。

あくまでバックアップや加工する用途なので、印刷範囲の設定はしていません。

PDF形式で出力

PDFボタンを押すとPDFで出力することも可能です。

週次集計のサイズがイマイチですが、ここは印刷範囲の設定に依存します。列の横幅とかを調整すればもう少しマシになるかな。

VBA

そういうわけでVBAのコードです。すべてのコードを1つのモジュールにまとめていますが、ここではそれぞれ関連するプロシージャごとに分けています。

テーブルへの登録

月次集計用にフィルタをかける

Excel形式で出力

PDF形式で出力

コメント

タイトルとURLをコピーしました