備品や機器等をExcelの表にまとめて台帳管理している人は多いのではないでしょうか。
その台帳から、「何月にどれだけ購入したか」「今はどこに保管しているか」などの集計表や棚卸表をまとめたい場合もあるかと思います。
今回は機器管理台帳から、
月別の購入額集計表
機器の棚卸確認表
の２つの表の作成を自動化するVBAを実装していきます。
この記事で学べる事
この記事で学べるVBAの知識は以下です。
・コンボボックスの設置方法
・ユーザーフォームの作成方法
・イベントについて
・With～End Withについて
・If文について
・変数について
・For文について
・最下行の取得方法
・セルの指定方法
・Excelの別ブックを開く方法
などを学びながら作成していきます。
集計表の作成を自動化するメリット
毎月月末に手作業でこれらの集計作業を行う場合、以下のようなデメリットがあります。
・作業時間が取られ他の業務に手が回らない
・人によってやり方が違う場合があり手順の統一化に時間がかかる
・新しい人への引継ぎやマニュアル作成作業が必要となる
これをExcel VBAで自動化すると、以下のようなメリットにつながります。
・人による作業ミスがなくなり確認と修正作業が不要になる
・作業時間や労力が不要となり他の業務に集中できる
・手順が自動化されるため誰が作業を行ってもやり方が統一される
・新しい人への引継ぎが容易
例えば、
台帳やコード表のExcelファイルを開き、必要な情報をコピーして作成する棚卸表の作成業務あるとします。
その棚卸表作成の作業に、1人で最低30分の時間を毎月費やしているとします。
手作業のため、ミスがあった場合にはその修正や確認作業にさらに時間や人を費やす可能性もあります。
棚卸表の作成を自動化すると、ボタンのワンクリックのみで作業が完了します。
今まで作成作業をしていた人は、時間に余裕が生まれ、残業が減ったり、他の業務を進めることができるのです。
集計表の作成で使用する台帳について
[機器管理台帳]は以下のようになります。この台帳をもとに、各表を自動作成していきます。業務での機器の購入や破棄の履歴は、この台帳で管理しているものとします。
台帳をダウンロードしたら、ファイルは［.xlsm］を拡張子として保存しなおしてください。
［機器管理台帳］を更新する際に入力する列は、以下の通りです。
[購入日]、[購入金額]、[機器区分]、[機器名]、[利用者]
・機器の利用者を変更したときに更新する列
[更新区分]（「変更」と入力）、[更新日]、[備考] ・機器を廃棄するときに入力する列
[更新区分]（「削除」と入力）、[更新日]、[備考]
だいたい機器の台帳管理は、似たようなフォーマットや使い方になると思います。
この台帳を使用して別シートに［月別購入額集計表］と［機器棚卸確認表］を自動作成していきます。まずは自動作成するきっかけとなるボタンを配置します。
表自動作成ボタンの設置
［月別購入額集計表］と［機器棚卸確認表］を作成するためのボタンを設置します。
[開発]メニューの[挿入]から、ActiveXのボタンを選択します。
［機器管理台帳］とは別のシートにボタンを設置します。
ボタンの表示テキストを変更します。ボタンを右クリックし[プロパティ]をクリックします。
このプロパティウィンドウでは、ボタンのテキストや書式、サイズ等を変更することができます。
プロパティ画面の１番上のオブジェクト名には、ボタン名が入ります。
ここで設定したボタン名はVBAで使うので、他のボタンと区別できるようわかりやすい名前に設定します。
※日本語を入力して文字化けする場合は、メモ帳などに入力したものをコピーして、ここに張り付けてください。
Captionはボタンの表示テキストが変更できます。こちらもユーザーにとってわかりやすいテキストに変更します。
購入額集計ボタンのプロパティ
棚卸表作成ボタンのプロパティ
以上でボタンの設置は完了です。
出力データのフォーマット作成
シートを追加して「月別購入額集計表」と「機器棚卸確認表」の表を作成します。
これはVBAで集計、抽出したデータを格納していくための表になります。
月別購入額集計表シート
機器棚卸表シート
各シートを追加し、列名だけ入力します。
セルの罫線は列名を書いた1行目にだけ引いておきます。他のセルの罫線は、データがセルに入力された箇所だけ自動で引くように条件書式から設定していきます。
セルの値入力に応じて罫線を引く
[月別購入額集計表]シートのA2からD100くらいまで選択します。
[ホーム]メニューの[条件付き書式]から[新しいルール]をクリックします。
[数式を使用して、書式設定するセルを決定]を選択し、ルールの内容に、
を入力します。右下の[書式]をクリックします。
[罫線]タブで[外枠(O)]を選択し、[OK]をクリックします。
前画面に戻ったら、[OK]をクリックすれば設定完了です。
は、A2～A100（$A2）のセルの値が、空白（“”）意外（＜＞）なら、選択範囲に罫線を引くという内容です。
試しに、セル（A2）に値を入れてみます。
2行目にだけ罫線が引かれました。
[機器棚卸管理表]シートも同じように罫線の設定を行ってください。[機器管理台帳]シートもこの設定をしておけば、データを追記する際に自動で罫線が引かれるので便利です。
月別購入額集計表の作成自動化
まずは、月別購入金額集計表の作成自動化をVBAで実装していきます。
VBA作成の流れとしては、おおまかに以下のようになります。
- [購入額集計]ボタンをクリックしたら、集計する月を選択するためのフォームを表示する
- 選択した集計月のデータを抽出し、[月別購入額集計表]シートに入力する
- 最終行に購入金額合計を入力する
Visual Basic Editorを開いて、VBAを入力していきます。
Visual Basic Editorについて詳しくは以下にあります↓
［購入額集計］ボタンをクリックしたら、集計する月を選択するためのフォームを表示する
購入額はユーザーが指定した月の集計をしたいので、まずは月を選択させるためのユーザーフォームを先に作成していきましょう。
ユーザーフォームの作成
Visual Basic Editorの[挿入]メニューから、[ユーザーフォーム]をクリックします。
デザインフォームが表示されます。
購入額を集計したい月を選択するためのフォームを作成していきます。
ツールボックスの[ラベル]をクリックし、フォーム上の設置したい箇所をクリックして設置します。
この状態でもう一度ラベル上をクリックすると、テキストが編集できます。「Label1」を「集計月」に変更します。
このままでは文字が小さく見にくいので、文字サイズを変更します。ラベルを右クリックし、プロパティをクリックします。
Font項目の右端の「…」ボタンをクリックします。
フォントサイズを12に変更します。
次にコンボボックスを設置します。
プロパティ画面で、オブジェクト名を「月選択コンボボックス」に変更します。
フォントサイズを先ほど設置したラベルに合わせて12に設定、Height(コンボボックスの高さ)を20に変更します。
集計表出力ボタンの設置
月を選択後の決定ボタンを設置します。
プロジェクトウィンドウのUserForm1を右クリックし、[オブジェクトの表示]をクリックすると、ユーザーフォームが表示されます。
ボタンを設置して、プロパティ画面でオブジェクト名とCaptionを変更しておきます。
VBAの実装
[購入額集計]ボタンをクリックしたら実行される、VBAコードの作成をします。
以下のように、ボタンを右クリックし「コードの表示」をクリックします。
するとVBAが自動生成されます。
この生成された「Private Sub ~ End Sub」内に、ボタンクリック時の処理を書いていきます。
この、「ボタンクリック時の処理」を「クリックイベント」と呼んだりします。
VBAでは、「何かをきっかけに実行される」ことを「イベント」といいます。
イベントについて詳しくは以下にあります↓
先ほど自動生成したボタンクリックイベント内には、
集計する月を選択させるフォーム
を表示させるコードを書いていく必要があります。
コードは以下のようになります。
Private Sub 購入額集計ボタン_Click()
'ユーザーフォーム表示
UserForm1.Show
End SubShowでユーザーフォームの表示ができます。
次に、設置したコンボボックスに、１～12月のリストを登録します。
プロジェクトウィンドウのUserForm1を右クリックし、[オブジェクトの表示]をクリックしてデザインフォームを表示します。
登録するタイミングは、ユーザーフォームの起動時にします。
フォームを右クリックし、[コードの表示]をクリックします。
上部左側のコンボボックスで「UserForm」が選択されている状態で、右側のリストから「initialize」をクリックします。
「initialize」は、ユーザーフォームが表示される際に発生するイベントです。
イベントについて詳しくは以下にあります↓
自動生成されたinitializeイベント内に、コンボボックスに登録するリストを書いていきます。
以下はリストを登録するための構文です。
コンボボックスのリストの登録には、AddItemを使用します。
コンボボックス.AddItem (“登録したいテキスト”)（）内に入る値を「引数」といいます。
1月～12月を選択させたいので、引数には1～12月までの値を入れます。
月選択コンボボックス.AddItem ("1月")
月選択コンボボックス.AddItem ("2月")
月選択コンボボックス.AddItem ("3月")
月選択コンボボックス.AddItem ("4月")
月選択コンボボックス.AddItem ("5月")
月選択コンボボックス.AddItem ("6月")
月選択コンボボックス.AddItem ("7月")
月選択コンボボックス.AddItem ("8月")
月選択コンボボックス.AddItem ("9月")
月選択コンボボックス.AddItem ("10月")
月選択コンボボックス.AddItem ("11月")
月選択コンボボックス.AddItem ("12月")コードをすっきりさせたいときに使用する［With］
上記のコードでは、「月選択コンボボックス」が何度もでてきます。
「With～End With」を使うことで、コードをすっきり見やすくすることができます。
With オブジェクト
’省略可能エリア
End With
Withで指定した月選択コンボボックスは、以降End With まで省略することができます。
デザインモードを解除し、購入額集計ボタンをクリックしてみます。
ユーザーフォームが表示されました。
月選択コンボボックスの▼をクリックし、月リストが表示されるか確認します。
ちゃんと月の選択ができるようになりました。
選択した集計月のデータを抽出し[月別購入額集計表]シートに入力する
集計表出力ボタンをクリックした時に、
購入機器データを台帳からデータ抽出し、以下のように[月別購入額集計]シートに転記するVBAを書いていきます。
Visual Basic Editorを開き、プロジェクトウィンドウのUserForm1を右クリックし、[オブジェクトの表示]をクリックしてデザインフォームを表示します。
集計表出力ボタンをクリックしたときのイベントを追加するには、デザインフォームの集計表出力ボタンを右クリックし、[コードの表示]をクリックします。
先ほど追加したinitializeイベントの下に集計表出力ボタンのクリックイベントが自動生成されました。
コンボボックスで選択した集計月と、機器管理台帳のNo.1～No.20の購入日を1つずつ判定する処理をこのイベント内に書いていきます。
まずは[機器管理台帳]シートの購入機器データNo.1の[購入日]が、コンボボックスで選択した集計月と同じかどうかを判定します。
If Month(ThisWorkbook.Worksheets("機器管理台帳").Cells(2, 2).Value) & "月" = 月選択コンボボックス.Value Then
‘購入日と集計月が同じだった場合の処理
End ifこれと同じような判定処理が、あと19個必要になるということです。
判定するにはif文を使います。if文の基本構文は以下です。
If ○ ○ Then
● ●
ElseIf □ □ Then
■ ■
Else
▲ ▲
End if訳すと、
□□の場合は■■を実行する。
それ以外の場合は▲▲を実行する。
という意味になります。
ElseIf は条件の数により複数記述することができます。以下が例です。
If ○ ○ Then
● ●
ElseIf □ □ Then
■ ■
ElseIf △△ Then
▲ ▲
Else
★★
End ifElseは省略可能なので、それ以外が実行すべき処理がない場合は、End Ifで終わらせてもいいです。
If文について詳しくは以下にあります↓
If内の
Month(ThisWorkbook.Worksheets("機器管理台帳").Cells(2, 2).Value) & "月" = 月選択コンボボックス.Valueの部分が条件になります。
イコール（＝）は比較演算子（=，<>，<，<=，>，>=）の一つで、左右２つの式を比較します。
右の式はフォームのコンボボックスで選んだ[集計月]です。
月選択コンボボックス.Value.Valueで値を取得できます。「○月」という値が取得されます。
左の式は機器管理台帳のNo.1の購入機器データの[購入日]の月です。
Month(ThisWorkbook.Worksheets("機器管理台帳").Cells(2, 2).Value) & "月"Monthは、日付から月のみを抽出する関数です。日付は［機器管理台帳］のNo.1の購入日のセルから取得します。
購入日のセルを指定するには、
ThisWorkbook.Worksheets(シート名).Cells(セルの行, セルの列).Valueと入力することで、「○○シートのセル〇行目の〇列目」と指定できます。
この左の式では、[機器管理台帳シート]のセル2行目と2列目の値、つまりNo.1の [購入日]から月のみを抽出します。
コンボボックスのリストでは「○月」のように、「月」が付いています。そのため、Month()で取得した月の数字に「月」を付け足します。
Month(ThisWorkbook.Worksheets("機器管理台帳").Cells(2, 2).Value) & "月"文字を付け足す場合には、文字と文字の間に「＆」を入れます。付け足す文字はダブルクオーテーション（“”）で囲います。
条件式の説明は以上です。
左の式と右の式を比較して、同じであればIf文内の処理を実行します。
If文内の処理は以下のようになります。
If Month(ThisWorkbook.Worksheets("機器管理台帳").Cells(2, 2).Value) & "月" = 月選択コンボボックス.Value Then
'年月に転記
ThisWorkbook.Worksheets("月別購入額集計表").Cells(2, 1) = ThisWorkbook.Worksheets("機器管理台帳").Cells(2, 2)
'機器区分に転記
ThisWorkbook.Worksheets("月別購入額集計表").Cells(2, 2) = ThisWorkbook.Worksheets("機器管理台帳").Cells(2, 4)
'機器名に転記
ThisWorkbook.Worksheets("月別購入額集計表").Cells(2, 3) = ThisWorkbook.Worksheets("機器管理台帳").Cells(2, 5)
'購入金額に転記
ThisWorkbook.Worksheets("月別購入額集計表").Cells(2, 4) = ThisWorkbook.Worksheets("機器管理台帳").Cells(2, 3)
End If
[月別購入額集計表]シートの指定したセルに、[機器管理台帳]シートのNo.1のデータを入れています。ここでのイコール（＝）は比較演算子ではなく、代入するという意味です。
'年月に転記
ThisWorkbook.Worksheets("月別購入額集計表").Cells(2, 1) = ThisWorkbook.Worksheets("機器管理台帳").Cells(2, 2)上のコードでは、[月別購入額集計表]のセルA2に、［機器管理台帳］のセルB2の購入日データを代入しています。
機器区分や機器名、購入金額も同じようにセルを指定して代入しています。
ボタンクリックイベントの最下行の直前でユーザーフォームを閉じるコード追加してます。
Unload Me自身のフォームを閉じるという意味のコードです。Meの部分をユーザーフォーム名にすると、開いている他のユーザーフォームを閉じることができます。
ここまで作成したところで、一旦テストします。
[機器管理台帳]のNo.1のデータの[購入日]は2月なので、コンボボックスで2月を選択して、[集計表出力]ボタンをクリックします。
目的のセルにNo.1のデータが転記されているのが確認できました。
何度も登場するシート名を変数に格納
ThisWorkbook.Worksheets()は操作したいシートの指定に使います。
何度も登場しているので１行のコードが長くなり、見にくくなっていますね。
これを短縮名にしてコードを見やすくしていきます。
先ほど使用した「With～End With」でもいいのですが、短縮したいシート名は2か所あるため、今回は[変数]を使用します。
［変数］は値を入れるための箱です。何度も出てくる文字や数字に関しては1つの箱に入れて、名前を付けて使いまわすことができます。
データを変数に格納する場合
Dim 変数名
変数名 = 値
オブジェクト(シートやセルなど)を変数に格納する場合
Dim 変数名
Set 変数名= ThisWorkbook.Worksheets(“Sheet1”)
変数について詳しくは以下にあります↓
「With～End With」は一時的に省略したいオブジェクトがある場合、変数は今後何度も使用する機会がある場合に使用するものと思ってください。
今回のコードでは、シート名省略の為に変数を使いたいので、以下のように書き換えることができます。
実際使用するコード箇所の直前に変数を宣言し、シートはオブジェクト（モノ）であるため、[Set]を付けてシート名を格納しています。
Dim 台帳
Set 台帳 = ThisWorkbook.Worksheets("機器管理台帳")
Dim 集計表
Set 集計表 = ThisWorkbook.Worksheets("月別購入額集計表")if文の条件式の前に書いている理由は、これからNo.2以降も同じ変数を使いまわすためです。
これでだいぶすっきりと見やすいコードになりました。
常に最終行にデータを転記するよう書き換える
あとは作成したIf文の処理を台帳No.2～No.20の分だけ作成します。
コードが長くなるので、以下はひとまず台帳のNo.4まで比較して転記するif文を書きました。
Private Sub 集計表出力ボタン_Click()
Dim 台帳
Set 台帳 = ThisWorkbook.Worksheets("機器管理台帳")
Dim 集計表
Set 集計表 = ThisWorkbook.Worksheets("月別購入額集計表")
'台帳No.1と比較し集計表２行目に転記する
If Month(台帳.Cells(2, 2).Value) & "月" = 月選択コンボボック ス.Value Then
'年月に転記
集計表.Cells(2, 1) = 台帳.Cells(2, 2)
'機器区分に転記
集計表.Cells(2, 2) = 台帳.Cells(2, 4)
'機器名に転記
集計表.Cells(2, 3) = 台帳.Cells(2, 5)
'購入金額に転記
集計表.Cells(2, 4) = 台帳.Cells(2, 3)
End If
'台帳No.2と比較し集計表３行目に転記する
If Month(台帳.Cells(3, 2).Value) & "月" = 月選択コンボボックス.Value Then
'年月に転記
集計表.Cells(3, 1) = 台帳.Cells(3, 2)
'機器区分に転記
集計表.Cells(3, 2) = 台帳.Cells(3, 4)
'機器名に転記
集計表.Cells(3, 3) = 台帳.Cells(3, 5)
'購入金額に転記
集計表.Cells(3, 4) = 台帳.Cells(3, 3)
End If
'台帳No.3と比較し集計表４行目に転記する
If Month(台帳.Cells(4, 2).Value) & "月" = 月選択コンボボックス.Value Then
'年月に転記
集計表.Cells(4, 1) = 台帳.Cells(4, 2)
'機器区分に転記
集計表.Cells(4, 2) = 台帳.Cells(4, 4)
'機器名に転記
集計表.Cells(4, 3) = 台帳.Cells(4, 5)
'購入金額に転記
集計表.Cells(4, 4) = 台帳.Cells(4, 3)
End If
'台帳No.4と比較し集計表5行目に転記する
If Month(台帳.Cells(5, 2).Value) & "月" = 月選択コンボボックス.Value Then
'年月に転記
集計表.Cells(5, 1) = 台帳.Cells(5, 2)
'機器区分に転記
集計表.Cells(5, 2) = 台帳.Cells(5, 4)
'機器名に転記
集計表.Cells(5, 3) = 台帳.Cells(5, 5)
'購入金額に転記
集計表.Cells(5, 4) = 台帳.Cells(5, 3)
End If
Unload Me
End Subしかし、このコードには問題があります。
確認の為にまず、上記のコードでテストしてみます。コンボボックスで「4月」を選択してテストしてみてください。
ちなみに、［機器管理台帳］のNo.1～No.4のデータは以下のようになってます。購入日が４月なのは、No.4のみです。
この状態で、コンボボックスで４月を選択してテストした場合、[月別購入額集計]シートの結果は以下の通りです。
数行あけてデータが転記されてしまいました。
これは、No.4との購入日比較if文で、[月別購入額集計]シートの5行目セルにデータを転記しているからです。
'台帳No.4と比較し集計表5行目に転記する
If Month(台帳.Cells(5, 2).Value) & "月" = 月選択コンボボックス.Value Then
'年月に転記
集計表.Cells(5, 1) = 台帳.Cells(5, 2)
'機器区分に転記
集計表.Cells(5, 2) = 台帳.Cells(5, 4)
'機器名に転記
集計表.Cells(5, 3) = 台帳.Cells(5, 5)
'購入金額に転記
集計表.Cells(5, 4) = 台帳.Cells(5, 3)
End IfNo.1～No.3の購入日比較if文では、コンボボックスで選択した「４月」に当てはまらなかったため、if文内の処理がスルーされました。
そのため、[月別購入額集計]シートの２～４行目のセルには値が入らず、5行目のセルにのみ値が入る結果になってしまいました。
この場合、行は詰めて最終行にデータを入れるようにした方が良いです。結果としては以下となるのが理想です。
この、「最下行」を指定するには、以下のように書きます。
集計表の1列目[年月]の最下行に転記する場合のコードです。
'年月に転記
集計表.Cells(集計表.Cells(集計表.Rows.Count, 1).End(xlUp).Row + 1, 1) = 台帳.Cells(5, 2)
まずデータが入力されているかされていないかにかかわらず、シートのセルの最大行数を取得します。以下の式で取得できます。
集計表.Rows.Count
セルの最大行数から、データが入力されているセルまで上にジャンプします。構文は以下のようになります。
Cells(セルの最大行数, セルの列数).End(xlUp)Excelシートだと、最下行からCtrl + ↑を押すと最後のデータが入っているセルに飛ぶことができます。この場合、最下行からA1に飛びます。
集計表.Cells(集計表.Rows.Count, 1).End(xlUp)データがあるセルの1つ下の空白のセルに転記するため、1加算します。.Rowはセル位置から行数を取得できます。
集計表.Cells(集計表.Rows.Count, 1).End(xlUp).Row + 1最後に列数を指定して、転記するセルの指定が完了です。
集計表.Cells(集計表.Cells(集計表.Rows.Count, 1).End(xlUp).Row + 1, 1)これをif文内に組み込んでいきます。ひとまず今回も台帳のNo.4までのif文です。
Private Sub 集計表出力ボタン_Click()
Dim 台帳
Set 台帳 = ThisWorkbook.Worksheets("機器管理台帳")
Dim 集計表
Set 集計表 = ThisWorkbook.Worksheets("月別購入額集計表")
'台帳No.1と比較し集計表２行目に転記する
If Month(台帳.Cells(2, 2).Value) & "月" = 月選択コンボボックス.Value Then
'年月に転記
集計表.Cells(集計表.Cells(集計表.Rows.Count, 1).End(xlUp).Row + 1, 1) = 台帳.Cells(2, 2)
'機器区分に転記
集計表.Cells(集計表.Cells(集計表.Rows.Count, 2).End(xlUp).Row + 1, 2) = 台帳.Cells(2, 4)
'機器名に転記
集計表.Cells(集計表.Cells(集計表.Rows.Count, 3).End(xlUp).Row + 1, 3) = 台帳.Cells(2, 5)
'購入金額に転記
集計表.Cells(集計表.Cells(集計表.Rows.Count, 4).End(xlUp).Row + 1, 4) = 台帳.Cells(2, 3)
End If
'台帳No.2と比較し集計表３行目に転記する
If Month(台帳.Cells(3, 2).Value) & "月" = 月選択コンボボックス.Value Then
'年月に転記
集計表.Cells(集計表.Cells(集計表.Rows.Count, 1).End(xlUp).Row + 1, 1) = 台帳.Cells(3, 2)
'機器区分に転記
集計表.Cells(集計表.Cells(集計表.Rows.Count, 2).End(xlUp).Row + 1, 2) = 台帳.Cells(3, 4)
'機器名に転記
集計表.Cells(集計表.Cells(集計表.Rows.Count, 3).End(xlUp).Row + 1, 3) = 台帳.Cells(3, 5)
'購入金額に転記
集計表.Cells(集計表.Cells(集計表.Rows.Count, 4).End(xlUp).Row + 1, 4) = 台帳.Cells(3, 3)
End If
'台帳No.3と比較し集計表４行目に転記する
If Month(台帳.Cells(4, 2).Value) & "月" = 月選択コンボボックス.Value Then
'年月に転記
集計表.Cells(集計表.Cells(集計表.Rows.Count, 1).End(xlUp).Row + 1, 1) = 台帳.Cells(4, 2)
'機器区分に転記
集計表.Cells(集計表.Cells(集計表.Rows.Count, 2).End(xlUp).Row + 1, 2) = 台帳.Cells(4, 4)
'機器名に転記
集計表.Cells(集計表.Cells(集計表.Rows.Count, 3).End(xlUp).Row + 1, 3) = 台帳.Cells(4, 5)
'購入金額に転記
集計表.Cells(集計表.Cells(集計表.Rows.Count, 4).End(xlUp).Row + 1, 4) = 台帳.Cells(4, 3)
End If
'台帳No.4と比較し集計表5行目に転記する
If Month(台帳.Cells(5, 2).Value) & "月" = 月選択コンボボックス.Value Then
'年月に転記
集計表.Cells(集計表.Cells(集計表.Rows.Count, 1).End(xlUp).Row + 1, 1) = 台帳.Cells(5, 2)
'機器区分に転記
集計表.Cells(集計表.Cells(集計表.Rows.Count, 2).End(xlUp).Row + 1, 2) = 台帳.Cells(5, 4)
'機器名に転記
集計表.Cells(集計表.Cells(集計表.Rows.Count, 3).End(xlUp).Row + 1, 3) = 台帳.Cells(5, 5)
'購入金額に転記
集計表.Cells(集計表.Cells(集計表.Rows.Count, 4).End(xlUp).Row + 1, 4) = 台帳.Cells(5, 3)
End If
Unload Me
End Sub再びテストします。
テストの際は[月別購入額集計表]シートの値は以下のように削除しておいてください。
集計月に4月を選んでテストします。
4月のデータが「データが入る最終行のすぐ下のセル」に出力されることが確認できます。
これで行を詰めて出力することはできました。
あとは、長すぎるコードを短く簡潔にできればもっと見やすくなります。
For文の繰り返し処理を使う
For文を使うことで、
・一連の処理内容を繰り返し行うことができる
・プログラムのコード冗長になることを防ぐ
ことができます。
For文の構文は以下のようになります。
For カウンター変数 To 終了値
‘繰り返し処理
Nextカウンター変数とは、for文を繰り返すごとにカウントアップしていく変数です。
Nextまで来たらカウンター変数を＋１して、For文に戻って処理を繰り返します。終了値の回数分、処理を繰り返した終了します。
繰り返し処理のFor文について詳しくは以下にあります↓
For文を使って修正したコードは以下になります。
だいぶすっきりしました。以下コードの詳細です。
Dim 台帳の行数カウンター変数の変数名は「台帳の行数」です。そのまま、[機器管理台帳]シートの行数を意味しています。
For 台帳の行数 = 2 To 台帳.Cells(台帳.Rows.Count, 1).End(xlUp).Row「台帳の行数」は[機器管理台帳]シートのNo.1のデータ、つまり2行目から始まります。
台帳.Cells(台帳.Rows.Count, 1).End(xlUp).Row最終値には台帳の「データが入る最終行」を指定しています。このコードを最終値に設定しておくと、台帳のデータが追記されても終了値を書き換える必要がありません。
集計表.Cells(集計表.Cells(集計表.Rows.Count, 1).End(xlUp).Row + 1, 1) = 台帳.Cells(台帳の行数, 2)カウンター変数は、カウンターの役割だけではありません。
カウンター変数は条件判定しているデータの行数であるため、転記でCells()を使う際に行数として指定することができます。
データを転記する[月別購入額集計表]の「データが入る最終行の1つ下の行」も変数化できます。以下が修正後コードです。
[集計表の最終行]という変数が、「データが入る最終行の1つ下の行」を格納する変数です。
if文で条件一致した際に、値を格納しています。
Private Sub 集計表出力ボタン_Click()
Dim 台帳
Set 台帳 = ThisWorkbook.Worksheets("機器管理台帳")
Dim 集計表
Set 集計表 = ThisWorkbook.Worksheets("月別購入額集計表")
Dim 集計表の最終行
Dim 台帳の行数
For 台帳の行数 = 2 To 台帳.Cells(台帳.Rows.Count, 1).End(xlUp).Row
'台帳との比較
If Month(台帳.Cells(台帳の行数, 2).Value) & "月" = 月選択コンボボックス.Value Then
集計表の最終行 = 集計表.Cells(集計表.Rows.Count, 1).End(xlUp).Row + 1
'年月に転記
集計表.Cells(集計表の最終行, 1) = 台帳.Cells(台帳の行数, 2)
'機器区分に転記
集計表.Cells(集計表の最終行, 2) = 台帳.Cells(台帳の行数, 4)
'機器名に転記
集計表.Cells(集計表の最終行, 3) = 台帳.Cells(台帳の行数, 5)
'購入金額に転記
集計表.Cells(集計表の最終行, 4) = 台帳.Cells(台帳の行数, 3)
End If
Next
Unload Me
End Subではちゃんと動作するか確認してみましょう。
何月でも良いので選んでテストします。
9月を選んだ結果は以下のようになりました。成功です。
最終行に購入金額合計を入力する
「データが入る最終行の1つ下の行」に、購入金額の合計が入力されるようVBAを追記します。
[合計]という変数を作成します。For文の外で宣言します。０で初期化しておきます。
Dim 合計
合計 = 0
購入金額を加算する処理は、For文のIf文内で行います。
[合計]に加算して再び[合計]に格納します。
'購入金額を加算
合計 = 合計 + 台帳.Cells(台帳の行数, 3)For文を抜けたら、最終行に合計を出力します。
集計表.Cells(集計表の最終行 + 1, 1) = "合計"
'最終行に合計を追加
集計表.Cells(集計表の最終行 + 1, 4) = 合計最終行に合計を追加している行に罫線を引きたいので、A列にも値を入れています。（最初の方で自動で罫線を引くよう設定済）
［購入額集計］ボタンを押して、テストしてみましょう。
合計も表示されるようになりました。
試しに［機器管理台帳］のNo.21に新しく９月の購入データを追記してみます。
［購入額集計］ボタンを押して、集計月に9月を選択し、追加したNo.21が[月別購入額集計表]に出力されるか見てみます。
ちゃんとFor文でデータが入る最終行まで繰り返して、No.21が出力されています。
このようにExcelVBAでは、可変行をFor文で繰り返す処理が多いため、[データが入る最終行]を取得するコードは頻繁に使用する機会があります。
機器棚卸確認表の作成自動化
次に、[棚卸表作成]ボタンをクリックした際に、[機器棚卸確認表]シートに台帳のデータを転記し棚卸表の作成を自動化していきます。
先に作成した［機器棚卸確認表］のフォーマットには、［機器管理台帳］にない列[機器コード]が存在します。
この[機器コード]のデータについては、[コード表]という別のExcelファイルからデータを取得します。[物品コード]シートの物品コードは以下のような表が存在します。
VBA作成の流れとしては、おおまかに以下のようになります。
２．[コード表]ファイルを開く
３．[コード表]ファイルから機器別のコードを取得して[機器棚卸確認表]転記する
今回は転記がメインとなり前回よりも単純な流れになりますが、別のExcelファイルを参照してデータを取得する処理が入ります。
Visual Basic Editorを開いて、VBAを入力していきます。
[機器管理台帳]の1～No.20までのデータを[機器棚卸確認表]に転記する
まずは[ボタン]シートに設置した[棚卸表作成]ボタンを右クリックし、[コードの表示]をクリックしてボタンクリックイベントを自動生成します。（ボタンをダブルクリックでも同じことができます）
最初の[購入額集計]ボタンのクリックイベント下に、新しくコードが自動生成されたので、このイベント内に処理を書いていきます。
まずFor文を使って、台帳から棚卸表に転記するVBAを書いていきます。
For文は前回[月別購入金額集計表]で作成したFor文を参考にして作成します。
変数名やシート指定箇所は、[月別購入金額集計表]から、[機器棚卸確認表]に変更しています。
これで１は実装完了です。
[コード表]ファイルを開く
コード表は繰り返し処理を行うごとに参照するので、For文の実行前にファイルを開いておきます。
別ファイルを開くには、Workbooks.Openを使用します。
Workbooks.Open 別ファイルのパス別ファイルのパスに[コード表]ファイルのパスを指定します。
'別ブックのコード表ファイルを開く
Workbooks.Open ThisWorkbook.Path & "\コード表.xlsx"ThisWorkbook.Pathで現在のフォルダパスを取得できます。[コード表]ファイルが同じフォルダ階層に置いてある場合に使用できます。
別階層にコード表が置いてある場合は、フルパスで指定します。
Workbooks.Open “C:\コード表フォルダ\コード表.xlsx"
[コード表]ファイルから機器別のコードを取得して[機器棚卸確認表]転記する
[コード表]ファイルから機器別のコードを取得する処理は、For文内に追記します。
コード表ファイルから機器コードを取得するために、Excel関数のVLookupを使っていきます。
VBAの中でExcel関数を使うには、WorksheetFunctionからExcel関数を指定することで使用できます。
'コード表ファイルからVLookup関数で参照し機器コードに転記
棚卸表.Cells(棚卸表最終行, 4) = WorksheetFunction.VLookup(台帳.Cells(台帳の行数, 4), Workbooks("コード表.xlsx").Worksheets("物品コード").Range("A2:B10"), 2, False)
試しに、適当な場所に「WorksheetFunction.」と入力してみます。ドット（.）を入力すると、Excel関数の候補が表示されると思います。
この候補の中の関数を使用することができます。
VLookupもあるので今回はそれを選択します。直接「VLookup」と入力してもいいです。
「（」かっこまで入力すると、かっこ内の入力のヒントが表示されます。
これはExcel関数のときと似ています。同じように検索値、範囲、列番号、検索方法を入力します。
VLookupの中身は以下の通りです。
WorksheetFunction.VLookup(台帳.Cells(台帳の行数, 4), Workbooks("コード表.xlsx").Worksheets("物品コード").Range("A2:B10"), 2, False)
VBAでセル範囲を指定するには、Range()を使用します。同じく、セル指定するときに使用するCells()と違い、Range()はセルの範囲を指定できます。
Range(セル範囲)ここでは、コード表のデータA2～B10を指定します。
.Range("A2:B10")
For文を抜けて、不要になった[コード表]ファイルを閉じる処理を最後に入れます。
'コード表ファイルを閉じる
Workbooks(コード表ファイル).Close
※「Workbooks(コード表ファイル)」は複数出てきているので、For文前に変数に格納しても良いです。
これでコードは全て作成したので、最後にテストをします。
[棚卸表作成]ボタンをクリックします。
エラーが出てしまいました・・・[デバッグ]をクリックすると、どこでエラーとなっているか確認できます。
黄色いラインがエラー発生した場所です。VLookupのところでエラーになっています。
上メニューの停止ボタンを押して実行を終了します。
棚卸表はこうなっています。
No.8の行の[機器コード]が入力されていませんね。ここが怪しいです。
[コード表]ファイルを確認してみます。
あ・・・原因がわかりましたね。No.9の機器名「Wi-Fi」がコード表にありません。
Wi-Fiを追加して、 [コード表]ファイルは閉じておきます。
もう一か所修正が必要です。VLookupのコード検索範囲の部分。範囲が増えたので、
Range(“A2:B10”)から、
Range(“A2:B11”)に変更しておきます。
もしくは、頻繁にコード表が更新されるようであれば、最下行を取得するのも良いです。
以下はFor文前に[コード表]ファイルの[物品コード]シートを変数化して書き換えたRangeです。
'コード表ファイルからVLookup関数で参照し機器コードに転記
棚卸表.Cells(棚卸表最終行, 4) = WorksheetFunction.VLookup(台帳.Cells(台帳の行数, 4), _
コード表ファイル.Range("A2:B" & コード表ファイル.Cells(コード表ファイル.Rows.Count, 1).End(xlUp).Row), 2, False)変数化しても１行が長いので、改行しています。
1行のコードが長い場合は、改行したい箇所にスペース＋ハイフン（ _）を入れることで、改行できます。
コード全体
保存して再度テストします。
今度は成功しました。
これで2つの表の自動作成機能は完成です。
まとめ
普段機器管理で使用している台帳から、毎月手作業で作成している表の作成自動化をVBAで実装していきました。
初めての自動化だった方は、専門用語やコード入力が少々難しかったのではないでしょうか？
今回の表作成の自動化はいろいろな場面で転用できると思いますので、ぜひ繰り返し参考にしていただければと思います。
プログラムを学習していると、「この記述っているの？」とか、「この作業いらないんじゃない？」とか思うこともあります。私も初めはそう思っていました。
でもVBAをたくさん書いていくうちに、「これはここで必要だったんだ」とか「こう書いておかないと後で修正のときに苦労するな」など必要性が見えてきます。
このブログでは、なるべくVBAで不要な部分は省いて、最低限の知識だけで書けるようにしています。
もしかしたら無駄だと思う箇所もあるかと思いますが、後で必ず役に立つと思って学習していただければと思います。
