Excel VBA入門・応用
Excelは事務作業にとって欠かすことのできないソフトウェアとなっておりますが、Excel上で動作するプログラム(VBA)を使っている人はほとんどいません。できるわけはないと初めからあきらめているのでしょうか。
VBAを使うと、事務処理の生産性が圧倒的に高まります。ちょっとした仕事が自動化できれば、小さな改善の積み重ねで大きな効果を生むことができます。
プログラムとは何か
プログラムとは入力を加工して出力を得るものです。入力、出力には、キーボード、ファイル、メモリ、画面などいろいろあります。それを扱うプログラムの命令も多数あります。習得するのはなかなか難しいものです。
しかし、Excelで必要とされるプログラムは通常、入力、出力ともExcelの表です。普段手作業で取り扱っているExcelの表が入力であり、それを加工してExcelの表をつくればいいのです。表の体裁を整えるプログラムを作るのはなかなかやっかいですが、特に時間がかかる集計部分を自動化するのは難しくありません。
データのダウンロード
今回勉強するデータをダウンロードします。
売上集計.xlsを開きます。
Excelシートに2つのデータがあります。売上データと価格表です。このデータから月売上シートを作成します。
入力表 (売上シート)
(価格シート)
出力表 (月集計シート)
手作業での製作
あなたならどうやって集計するでしょうか。
Excelの関数や集計機能をつかいながら、手作業でも次の手順作成できます。
1)売上データに単価を表示する。
関数のVLOOKUPを使うと、価格表より単価を求めることができます。
2)単価と販売数を掛け売上金額を求める
3)月別に集計するために、月を関数のTEXTを使用して求める。
4)Excel集計機能のピボットテーブルを使い縦横集計する。
結果をコピーして値のみ貼付する。
5)商品名を求める
関数のVLOOKUPを使うと、価格表より商品名を求めることができます。
6)月別の合計を求める
以上の6手順で手作業で作成できます。
この作業をプログラムを作って自動化してみましょう。
自動集計するには、プログラムを入力して実行します。そもそもプログラムはどこに入力してどうやって実行するのでしょうか。簡単なプログラムを入力して実行してみましょう。
開発タブの表示
開発タブが表示されていない場合、次の操作をします。一回設定すれば常に表示されます。
[ファイル][オプション][リボンのユーザー設定]を押し、[開発]にチェックし、[ok]を押します。
開発タブを押し、Visual Basic を押します。
[挿入]、[標準モジュール]と操作すると、右側に入力できる場所ができます。
|
SUB テスト A=1 B=2 C=A+B RANGE(“A3”).VALUE=C END SUB
|
と入力してみましょう。
[実行][SUB/ユーザーフォームの実行][実行]と操作すると、入力したプログラムが実行され、
A3セルに 3 と記入されました。
作成したプログラムはこのブックに記憶されています。
ブックを保存すれば、プログラムも保存され、再度利用できます。
ExcelVBA自動作成
表の加工には処理のパターンがあり、このパターンを覚えれば、組み合わせて、いろいろな処理が簡単にできます。ExcelVBA自動作成というこれらのパターンのプログラムを自動的に作成するシステムがあります。40種類以上のパターンがありますが普通使用するのは10種類程度です。しかもこのパターンはExcelの手作業と同じ処理ですので、容易に理解できると思います。これを使ってプログラムを作成する方法を説明していきます。
今回のシステムではすべて自動作成され、手修正は不要です。
ExcelVBA自動作成を下のリンクからダウンロードします。
http://www.vector.co.jp/soft/winnt/business/se369444.html
ディスクトップに解凍した場合、 VBAJ5ホルダを どこかのドライブに移動します。
自動M.xlsを開きます。
注 下図のように VBAJ5.ZIPの下位に VBAJ5ホルダが作成されている場合
解凍できていません。
下記のエラーが発生します。
LHAPULSなど解凍プログラムをインストールしてください。
https://www.vector.co.jp/soft/win95/util/se169348.html
アドインタブに VBA自動作成 が表示されます。
表示されない場合は、[開発][マクロ]を操作し、
自動M.XLS!AUTO_OPEN を選択、[実行]を押してください。
[アドイン][VBA自動作成] を押すと機能を選択するフォームが表示されます。
手作業と同じく5つの手順から成り立っています。
それぞれを別のプログラムにします。
プログラムを作る上で、最も重要なのがこのように「機能」を分けることです。
何ページにも及ぶ1本のプログラムを作ったのでは、改定するときどこを直していいのかわかりません。
日本語で簡単に説明できるよう1つ1つのプログラムの機能(入力が何で出力が何)を明確にすることです。
下記の機能のプログラムを順に作っていきます。
Sub単価合成
売上シートに単価を表示する。(入力:売上シート、価格シート 出力: 売上シート)
Sub式コピー
売上金額、月を求める。(入力: 売上シート 出力: 売上シート)
Sub pivotm
ピボットテーブルで縦横集計する。(入力:売上シート 出力:月売上シート)
Sub型式合成
商品名を求める。(入力: 月売上シート、価格シート 出力: 月売上シート)
Sub合計計算式出力
月別の合計を求める。(入力: 月売上シート 出力: 月売上シート)
1つ製作したら、実行し、動作を確認していきます。
１つずつ作っていくことで、模型のプラモデルを作っていくように楽しく作業できます。
単価合成
売上シートを表示し、[アドイン][VBA自動作成]を操作します。
[合成]を選択、[作成]を押します。
下記のようなセルを選択するフォームが表示されます。
下記のように①～⑤にセルを選択入力します。
①売上シートのA3セルの値を④価格シートのA列から探し、⑤見つけた価格シートのC列の値を②売上シートのE列に記入する意味です。
[OK]を押すとプログラムが作成され、クリップボードにコピーされます。
Module1に貼り付けます。
プログラムの中にカーソルを置き、[実行][sub/ユーザーフォームの実行]を押し、プログラムを実行します。
売上シートに単価が表示されます。
売上シート
価格シート
式コピー
Excelでのプログラム作成では計算式が威力を発揮します。いろいろな計算、判断を計算式を使って実現することで、プログラムでは複雑な計算が不要になりますし、計算式はその場で結果が出ますので、正しいかどうかの判断が容易です。
先頭の式を手入力しておくことで、計算式の全行への記入が自動化できます。
[計算式記入]を選択、[作成]を押します。
下記のようなセルを選択するフォームが表示されます。
下記のように①～②にセルを選択入力します。
①売上シートのF3:G3の計算式をコピー、②データの最終行をA列で判断する意味です。
[OK]を押すとプログラムが作成され、クリップボードにコピーされます。
Module1に貼り付けます。
プログラムの中にカーソルを置き、[実行][sub/ユーザーフォームの実行]を押し、プログラムを実行します。
実行すると売上シートに売上金額、年月が表示されます。
売上シート
ピボットテーブルによる集計
ピボットテーブルの命令を使った集計です。
[ピボット作成]を選択、[作成]を押します。
下記のようなセルを選択するフォームが表示されます。
下記のように①～⑤にセルを選択入力します。
①売上シートのA3を縦項目、②G列を横項目とし、③ F列を集計する。結果を④縦項目A4セル以降、⑤横項目C3セル以降に貼り付ける意味です。
[OK]を押すとプログラムが作成され、クリップボードにコピーされます。
Module1に貼り付けます。
プログラムの中にカーソルを置き、[実行][sub/ユーザーフォームの実行]を押し、プログラムを実行します。
実行すると、月売上シートに集計されます。
売上シート
月売上シート
型式合成
月集計シートを表示し、[合成]を選択、[作成]を押します。
下記のようなセルを選択するフォームが表示されます。
下記のように①～⑤にセルを選択入力します。
①月集計シートのA4セルの値を④価格シートのA列から探し、⑤見つけた価格シートのB列の値を②月集計シートのB列に記入する意味です。
[OK]を押すとプログラムが作成され、クリップボードにコピーされます。
Module1に貼り付けます。
プログラムの中にカーソルを置き、[実行][sub/ユーザーフォームの実行]を押し、プログラムを実行します。
実行すると、月売上シートに型式が表示されます。
月売上シート
プログラム名を型式合成に修正します。
合計計算式出力
金額の合計を記入します。
まず手作業で合計の式を記入します。
この式をプログラムで記入します。データの最終行に合わせて式を自動的に変更します。
まず、入力した式の範囲を選択します。
[計算式記録]を選択、[作成]を押します。
下記のようなセルを選択するフォームが表示されます。
下記のように設定します。
全行の合計等... にチェック 最終行を判断する列をA 式も最終行に合わせて...にチェック
[OK]を押すとプログラムが作成され、クリップボードにコピーされます。
選択した範囲の式がModule1に貼り付けられます。
プログラムの中にカーソルを置き、[実行][sub/ユーザーフォームの実行]を押し、プログラムを実行します。
実行すると、合計が計算されます。
月売上シート
メインプログラムの作成
最後に、今まで製作したプログラムが順次実行されるよう、
SUB 集計としてプログラム名を並べます。
売上シートのボタンに 作成した プログラム「集計」を登録します。
月集計ボタン右クリック、[マクロの登録]をクリック。
マクロの保存先を「作業中のブック」とします。
[集計] OKを押す。
以上でプログラムが完成しました。
集計ボタンを押すと、集計され、月集計シートが作成されます。
次にもう少し難しいプログラムを作ってみます。
簡易版の会計システムとして、Excelに入力した伝票仕分けデータをもとに、元帳、試算表を作ってみます。