エクセルマクロVBAで項目(製品)別に集計｜ランキング順位も自動出力

エクセルのデータを製品別の売上を計算する作業があります。

このような作業はピボットテーブルを使っても可能です。

しかしデータ数が多かったり更新頻度が高かったりするとマクロ化する方が負担が少ないこともあります。

そこで製品別の売上を自動計算して、結果も分析するマクロを作っていきます。

またページ下部でこのページで紹介しているVBAプログラムをダウンロードすることもできますので、ぜひお仕事にご活用ください。

製品別に売上結果を分析するマクロの概要

このページでは、製品や商品別に売上データを分析するマクロを紹介します。

流れは以下のとおりです。

以下で詳しく説明します。

手順1. データを準備

今回は上記のような売上データを想定してマクロを作成します。

手順2. VBAプログラム実行

売上の分析結果として、以下のデータを出力します。

ここでは各製品の売上が総売上に対して、どの程度貢献しているかを計算しています。

そうすることで、どの製品が会社全体の売上に寄与しているかを見極めることができます。

そしてこの結果から、ヒト・モノ・カネのリソースを配分する判断材料とします。

VBAプログラムの詳細は後半部分で解説しています。

ボタンにVBAプログラムを登録

このページではボタンを設定していませんが、VBAプログラムをボタンに登録することもできます。

ボタンにVBAプログラムを登録することで、ボタンを押下しプログラムを実行することができます。

ボタンをVBAプログラムを設定したい場合は、以下で動画も交えて設定方法を紹介しているので、そちらをご覧ください。

類似のVBAプログラム｜データ分析マクロ

ここでは売上のデータ分析マクロを紹介していますが、このサイトでは他にもマクロを紹介しています。

興味があればぜひご覧ください。

1. Excelマクロで月末処理のデータ集計

2. Excelマクロで月別データ集計

3. Excelマクロで週別データ集計

4. Excelマクロで期別(Q毎)にデータ集計

5. Excelマクロで日別にデータ集計

VBA入りのエクセルファイルをダウンロード

以下で紹介しているVBAプログラムをそのまま使いたい人は、以下のフォームからダウンロードできます。

登録したメールアドレスへ「VBAプログラムが含まれたエクセル」を送信します。

本プログラムの内容をそのまま使用可能です。ぜひお仕事にお役立てください。

それでは、以下でプログラムについて詳細を説明します。

VBA作成前の事前準備

ExcelでVBAを使うためにはいくつかの準備が必要です。

具体的には以下です。

上記の2つに関して、以下で説明します。

1. ExcelでVBAを使うための事前準備

Excelで、以下の2つの準備をします。

保存ファイルの拡張子変更、Excelの基本設定変更の2つです。

2つともで難しい作業はなく、それぞれ1分もあれば設定変更可能です。

上記に関しては、以下の記事で解説をしています。

もしVBAを使うための準備段階に不安がある人は上記をご覧ください。

この内容は以下の動画で紹介しています。

入門エクセルマクロの使い方｜マクロ作成から実行までを徹底解説

文字や画像だけで分かりづらい人は上記の動画をご覧ください。

VBAのプログラムソース解説

今回紹介するプログラムの概要は以下です。

プログラム0｜変数宣言の指定

「Option Explicit」とは、変数宣言を強制するためのものです。

これを入れておくと、変数を定義していない場合、エラーが出ます。

つまり、「Option Explicit」を入力しておくことで、たとえば「Dim i」をあらかじめ入力しないと、「i」という変数を使えません。

もし「Option Explicit」を入力しているのに、「Dim i」を書かずに「i = 1」と書くと、エラーメッセージが表示されます。

実は、この機能はあくまでオプションです。「Option Explicit」を入力しなくても、プログラムは動きます。

しかし、これを入れておくことで、変数の誤記によるエラーを防止することができます。

結果的に、プログラム作成速度が上がるので、「Option Explicit」を入力することを習慣化することをオススメします。

プログラム1｜プログラム開始

「Sub GetProductData()」のプログラムを開始することを意味します。

VBAではプロシージャという単位でプログラムを呼び出します。

プロシージャの構文は下記となっています。

「Sub」で始まり「End Sub」で終わります。

「Sub XXXX」の「XXXX」の部分がプロシージャ名です。

このプロシージャ名はあらゆる文字（アルファベット、ひらがな、漢字、数字など）が使用可能です。

ただし、プロシージャ名の先頭は数字を入れるとエラーとなります。

あとで見たときに、「何のプログラムだったのか？」とならないようにするためです。

なお、「()」の中には何も記入されていません。これは引数なしという意味です。なお、プログラム2-1では引数を受け取ってプログラムを実行します。

初心者の内は、引数ということが分からなくてもVBAプログラムを書くことは可能です。

興味があれば、「VBA 引数」で検索して調べてみてください。

プログラム2｜シート設定

ws1をワークシート型で変数設定します。

「Data」シートをws1とします。

プログラム3｜ws1の最終行を取得

ws1のA列の最終行を取得します。ここではA633までデータが入力されているため、cmax1=633となります。

Debug.Printでチェックしてみます。

プログラム4｜新しいシートの挿入してws2とする

新しいシート作成し、挿入します。挿入先はDataシート(ws1)の右側(after)です。

プログラム5｜シート名取得

変数sheet_nameに「製品別売上データ」という文字列を設定します。

プログラム6｜同じシート名があれば、シート削除

「製品別売上データ」という名前のシートがあれば、そのシートを削除します。

「製品別売上データ」という名前のシートを削除する理由

VBAの処理を行うとき、たまたま「製品別売上データ」という名前でシートが存在する場合があります。

たとえば、このプログラムで実行した後に再度プログラムを走らせたときに、以前作成した「製品別売上データ」が残ってしまっていた場合です。

もし「製品別売上データ」という名前のシートが残っていると、プログラムエラーが出てしまいます。

よって「製品別売上データ」という名前のシートを削除するプログラムを組み込んでいます。

ワークブック内の全シートを処理する

上記のプログラムでワークブック(エクセルファイル)に含まれる全てのシートに対して処理を実行することができます。

シートを削除する

対象となるワークシートの名前がsheet_nameのとき、そのワークシートを削除します。

VBAプログラムを動かしているとき、警告メッセージが表示されると、VBAプログラムが停止してしまいます。

そこで「Application.DisplayAlerts」を使うことで、警告メッセージの表示をコントロールします。

ここではエクセルファイルを保存するプログラムの前後で、「Application.DisplayAlerts」のFalseとTrueをスイッチしています。

こうすることで、VBAプログラム実行中に警告メッセージの表示されないようにします。

プログラム7｜シート名設定

シート名を変更します。

プログラム8｜ws2の1行目に出力

ws2(新しく作成したシート)の1行目にヘッダー情報を出力します。

プログラム9｜ws2に製品名を出力

ws1のB列のデータをws2のA列に出力します。

なおcmax1=633なので、正確には「ws1のB2:B633」のデータを「ws2のA2:A633」に出力しています。

プログラム10｜製品名の重複を削除

ws2のA列のデータを重複削除します。

重複削除することで、製品リストをA列に出力することができます。

上記のプログラムで、重複削除の軸にしたい列番号(1:A列)をもとに、「シート名」の「範囲」の重複を削除します。

重複削除する理由

ここでは、製品データの重複削除を行っています。

この理由は、製品の「重複なしリスト」が欲しかったからです。

重複のないリストを利用して、製品ごとに売上情報を計算しやすくしています。

ここから先のプログラムで実際に製品別の売上を計算していきます。

プログラム11｜重複削除したシートの最終行を取得

ws2のシートのA列の最終行を取得します。

ここではA16までデータが入力されているため、cmax2=16となります。

Debug.Printでチェック

プログラム12｜変数設定

変数を設定します。

プログラム13｜ws2のA列を取得して繰り返し処理

ws2のA列の2行目から16(cmax2)行目までの値をseihinとして取得します。

Debug.Printでチェック

プログラム13｜売上をゼロリセット

売上を計算する変数としてuriageを設定します。

このタイミングでuriage=0でゼロリセットします。

ws2のA列の値が切り替わるタイミングでゼロリセットしないと、正しい売上の値が計算されません。

そのため、ここでuriage=0としています。

プログラム14｜製品別に売上を累算

「seihin(製品名)」が「Dataシート(ws1)のB列の製品名」と一致するかどうかをif文で調べます。

一致した場合、Dataシート(ws1)のD列の金額をuriageに加算していきます。

これにより各製品ごとの売上金額をuriageとして取得します。

for next文やif文については以下のページで事例を交えて説明しています。興味がある人はご覧ください。

事例で学ぶfornext構文

事例で学ぶif文

プログラム15｜ws2のB列に売上を出力

新しく作成したシート(ws2)のB列にuriageの値を出力します。

これで製品別の売上データを計算することができました。

プログラム16｜売上の合計値を計算

変数goukeiにuriageを加算していきます。

これにより、goukeiで全製品の総売上を取得できます。

このgoukei(総売上)をもとに各製品の売上を分析していきます。

プログラム17｜売上順に並び替え

売上順に上から並ぶように並び替えを行います。

プログラム18｜順位、売上比率、累計売上比率を出力

売上に関する分析結果(順位、売上比率、累計売上比率)をC列~E列に出力します。

順位

「i = 2 to cmax2」でFor文を実行しているため、「i-1」で順位を計算することができます。

売上比率

売上比率(製品別の総売上に対する割合)を計算します。

製品別の売上(B列)/総売上(goukei)を計算して、ratioという変数に格納します。

Round(ratio,1)で小数点第一位までの値をD列に出力します。

累計売上比率

売上比率の累算を行います。

この値を確認することで、どの製品が売上に対して貢献しているかを確認することができます。

ratio_goukeiという変数に、製品ごとの売上比率(Round(ratio,1))を加算していきます。

この値をE列に出力します。

プログラム19｜プログラム終了

プログラム1と対になるプログラムで、プログラム終了させるための記述です。

「End Sub」を読み込むと、プログラムが終了します。

プログラムの解説はここまでです。

Excel VBAについて詳しく理解したいなら

VBAを活用すると、仕事を効率化できる幅を広げることができます。

たとえば私が実際にVBAを活用して効率化してきた作業は以下の記事で紹介しています。

興味がある人は以下の記事もご覧ください。

動画でも解説しています。

エクセルマクロVBAで出来ることを15の事例で紹介｜日常業務をラクにするヒントを見つけよう！

(音声が小さいので、ボリュームを上げてご覧いただければと思います)

VBAの勉強方法

私はプログラミング初心者からVBAを勉強を始めて少しずつレベルアップしていきました。

成長の過程は以下で紹介しています。

→私のVBA習得の道のり（10ヶ月でVBA取得するまで）

学習の過程では、意識すべきポイントがあります。

特に独学の場合だと、勉強を優先してしまい、肝心な実践を疎かにしがちです。

私の経験では、実践から逆算した勉強が必要だと考えています。

目指すべきは会社でお金をもらいながら勉強することです。

要はVBAを仕事の一つとして上司に認めてもらうのです。

そうすればわざわざ高いお金を払って勉強をする必要がなくなります。

しかも作業を自動化して、会社やチームに貢献しつつ、自らのスキルアップできます。

そのために必要な考え方を以下で紹介しています。

→VBAを作成するときに覚えておきたい18のコツ

とはいえ、プログラミング初心者でVBAについて知識ゼロの人もいるはずです。

いきなり会社でVBAで使うことさえ、とてつもなくハードルが高く見えてしまうものです。

その場合は、VBAの基本について学ぶ必要があります。

たとえば車の運転も慣れてしまえば、たいしたことではありません。

しかし教習所で運転の基本を学び、免許を取得することで、公道で運転できるようになります。

VBAも同じです。VBAに免許はありませんが、まずは基本を学ばないことには会社で使えるレベルにはなりません。

実際に私もプログラミング初心者のときは、動画を見たり書籍を読んだりして勉強しました。

今はオンラインの教材で無料で学習できるものも多いです。

上記のリンクでは、私の経験から勉強にオススメの教材を紹介しています。

興味がある人はご覧ください。

VBAを自分で書けるようになる

さて、本記事で紹介したマクロを利用すれば、作業の自動化が可能になります。

しかしデメリットもあります。それはカスタムできないことです。

なぜなら、色々な要望が増えるからです。

この動画を見たとき、「もっと○○ができるのでは？」や「ここはなんとかならないのか」と感じる人は少なくないはずです。

例えば、「他の条件を付け加えたい」や「日付毎に条件を変えたい」といった要望が出るかもしれません。

このような要望を満たすには、マクロを勉強して自力でマクロを編集できるようになる必要があります。

もし、自力でマクロを編集できるようになれば、今より仕事の効率はグッと上がります。

実際、私も自力でマクロを書けるようになってからは、仕事の生産性が一気に上がりました。

他の人が30分～1時間かけて行う仕事が、ボタン一つで終わらせることができるようになったのです。

その結果、周囲からの信頼も増し、仕事で高い評価を得られるようになりました。

ただ、要望に応えるようになるためには、マクロを学ぶ必要があります。

まずは無料でマクロを勉強してみる

ウェブや書籍で勉強すれば、マクロを習得できると考えている人は少なくありません。

しかし、仕事で使えるマクロを習得したいなら、仕事で使える部分に特化した教材で学ぶことをお勧めします。

なぜなら、ウェブや書籍には仕事に関係しない部分まで提供していることが多いからです。

例えば、マクロ初心者なのに配列を学ぼうとする人がいます。実は配列なしでも仕事で使えるマクロを書くことは可能です。

しかし、マクロ初心者ほど「全ての知識が必要だ」と考えて、無駄な学習に時間を使ってしまうのです。詳しくは、こちらの記事で紹介しています。

そこで、私がお勧めするのは仕事に直結するマクロ教材です。とくにお勧めするのは、こちらの無料オンライン動画です。

なぜなら、仕事に直結する部分に絞って、エクセルマクロを学ぶことができるからです。

マクロの作り方・考え方から解説しているので、教材をしっかり学べばここで紹介したマクロをゼロから書けるようになります。

マクロ初心者が、仕事に直結したいマクロを学ぶなら、まずはこちらの無料オンライン動画を試すのがいいです。

興味がある人は、まずは無料でエクセルマクロの勉強を始めてみてください。

もっと学びたいと感じたら、さらに深く勉強をしてみることをお勧めします。