役立つ! 総務マガジン

複数Excelシートのデータをまとめるマクロ技(後編)

データを自動的にコピーするマクロを実務で使えるようにカスタマイズ

社内の複数部署から総務が集めたデータを一つにまとめる作業では、Excelのマクロを活用すると効率化が図れます。前編では部署ごとのシートから集計用シートにコピーするマクロを記録しましたが、これを実務で使えるようにするには、もう一工夫が必要です。

[2018年 9月10日公開]

どのシートにも対応できるようにマクロを編集

Excelのマクロは簡単に記録できますが、記録したままの状態では「完全に同じ処理」しか繰り返すことができません。

前編「複数Excelシートのデータをまとめるマクロ技(前編)」では例として、「キャリアサポート部」の消耗品発注表からデータをコピー&貼り付けする操作をマクロとして記録しましたが、このシートの表には7行分のデータが入力されていました。よって、このマクロはそのままの状態では、7行ちょうどのデータにしか対応できません。

この後編では、記録したマクロの内容を編集し、いかなる行数のデータにも対応できるマクロに仕上げていきます。マクロの編集には「VBA」(Visual Basic for Applications)と呼ばれるプログラミング言語を使いますが、そのまま入力すれば動作するように解説しますので、心配はいりません。

(1)マクロの編集画面を表示する

前編「複数Excelシートのデータをまとめるマクロ技(前編)」で作成した集計用シート(「shuukei」シート)を表示しておきます。[表示]タブにある[マクロ]ボタンをクリックして[マクロ]ダイアログボックスを表示しておき、「DataCopy」を選択して[編集]をクリックします。

(2)マクロの編集画面が表示された

VBAの画面が表示されました。この画面でマクロを編集していきます。

(3)コピー元のセル範囲を修正する

まず、コピー元のセル範囲を示す「Range("A5:D11").Select」の部分を修正します。このままではセルA5~D11に固定されてしまうので、以下のように記述します。

Range("A5:D11").Select → Range(Cells(5,"A"),Cells(11,"D")).Select

編集後の部分「Cells(5,"A"),Cells(11,"D")」は「5行目、A列のセルから11行目、D列のセル」という意味です。選択するセル範囲は変わっていませんが、表現の仕方を変えたことで、以降の手順での修正が可能になります。

(4)行数の変化に対応させるための変数を設定する

セル範囲の表現を変えた部分を、さらに編集します。実務ではマクロによって選択されるセル範囲を、データに合わせて変化させる必要があります。ここでの例では、開始位置はセルA5で固定できますが、終了位置は各部署が記入しているデータの行数によって異なるでしょう。

よって、D列の行数を「行数」という名前の変数で表すことにします。変数とは、さまざまなデータが格納される入れ物のようなものです。

Range(Cells(5,"A"),Cells(11,"D")).Select → Range(Cells(5,"A"),Cells(行数,"D")).Select

(5)変数「行数」について定義する

「行数」という変数が何を表すのかを定義します。上図のように、VBAの画面に次の2行を追加しましょう。

Dim 行数 As Integer
行数=WorksheetFunction.CountA(Range("C:C"))+3

1行目は、「行数」は整数の変数である、という意味です。
2行目は、「行数」はC列のデータの個数を数えて、それに3を足したものである、という意味になります。これにより、消耗品発注表にあるデータの行数を数えて、コピーする行数を変化させることが可能になります。入力が終わったら、VBAの画面を閉じます。

(6)編集したマクロを実行する

前編「複数Excelシートのデータをまとめるマクロ技(前編)」でマクロを記録したときの手順と同じように、集計用シートを表示して、貼り付け先となる先頭のセルをクリックし、アクティブにしておきます。続いて、コピー元となるシート(ここでは「経理部」シート)を表示し、[表示]タブの[マクロ]ボタンから「DataCopy」のマクロを実行しましょう。

(7)マクロが実行された

マクロが実行され、データがコピーされました。「経理部」シートには9行分のデータがありますが、セル範囲が正しく選択され、意図通りに貼り付けられました。

ショートカットキーを割り当てればさらに効率アップ!

[Ctrl]+[C]キーを押すとコピーの操作を実行できるように、特定のキーの組み合わせによって、マクロを実行することもできます。[表示]タブで[マクロ]ボタンをクリックし、マクロ名を選択して……といった数回の操作を省略できるので、さらに効率がアップするでしょう。

マクロへのショートカットキーの割り当ては、[マクロ]ダイアログボックスの[オプション]から設定します。このとき、既にあるショートカットキーと同じものにならないよう、気をつけてください。

(1)マクロのオプションを表示する

[表示]タブにある[マクロ]ボタンをクリックし、[マクロ]ダイアログボックスを表示しておきます。「DataCopy」を選択し、[オプション]をクリックしましょう。

(2)マクロにショートカットキーを割り当てる

[マクロオプション]にある[ショートカットキー]で、マクロを実行するキーの組み合わせを設定します。ここでは[Ctrl]+[Shift]+[D]キーを割り当てました。完了したら[OK]をクリックします。

(3)ショートカットキーでマクロを実行する

ショートカットキーでマクロを実行してみましょう。集計用シートで貼り付け先となる先頭のセルをアクティブにした後、部署ごとのシートを表示して[Ctrl]+[Shift]+[D]キーを押します。

(4)マクロが実行された

設定したショートカットキーで、マクロが実行されました。以降は「部署ごとのシートを表示してショートカットキーを押す」という操作だけで、集計用シートにデータをまとめられるようになります。

次回以降もマクロを使うには「マクロ有効ブック」として保存

ここまでの手順で、消耗品発注表の集計を手早く行えるマクロが完成しました。しかし、このファイルを通常の方法で上書き保存すると、マクロは削除されてしまいます。次回以降にファイルを開いたときにもマクロが使えるように、「Excel マクロ有効ブック」として保存しましょう。

(1)保存時のメッセージを確認する

マクロを記録・編集したファイルを上書き保存すると、上図のようなメッセージが表示されます。ここで[はい]をクリックしてしまうと、作成したマクロは保存されません。[いいえ]をクリックし、マクロが使える状態でファイルを保存します。

(2)「マクロ有効ブック」として保存する

ファイル名を入力した後、[ファイルの種類]から[Excel マクロ有効ブック(*.xlsm)]を選択し、[保存]をクリックします。

(3)保存したファイルの拡張子や種類を確認する

Excelを閉じ、フォルダーウィンドウで保存したファイルを確認してみましょう。ここでは拡張子を表示した状態にしていますが、通常のExcelファイルの拡張子である「.xlsx」ではなく、「.xlsm」となっています。また、ファイルの種類には「マクロ有効ワークシート」と表示されており、マクロが使える状態で保存されたことが分かります。

マクロによる自動化を総務のさまざまな業務に生かそう

「シートを表示した後セル範囲を選択してコピー&貼り付け、また別のシートを表示した後セル範囲を選択して……」といった単調な作業は、できるだけ手早く済ませたいものです。操作をマクロとして記録してから少しだけカスタマイズすれば、それが実現できることを分かっていただけたと思います。

とはいえ、今回紹介した機能は、Excelのマクロでできることのほんの一部です。これを機に興味を持った方は、書籍などでマクロとVBAについて学習し、ほかの総務の業務でも自動化にチャレンジしていただければと思います。

協力メディア

できるネット(https://dekiru.net)

もっと関数のスキルを磨きたい! という人はぜひ大塚商会のExcel教室へ

大塚商会では、企業の人材育成をトータルにサポートする「人材育成支援サービス」をご提供しています。ITやCAD、ビジネススキルなどさまざまなジャンルをご用意しており、もちろんExcelを学ぶコースも充実しています。

Excelを基礎から応用まで体系的に学べる「標準コース」や、関数やマクロなど目的に応じて学べる「目的別コース」をご用意しています。大塚商会の研修施設で定期開催されているオープンコースのほか、貴社の業務に合わせてカスタマイズした企業研修(ご来場または出張)も可能です。

Excel研修の詳細情報を見る

Excelをもっと便利に活用するにはOffice 365をおすすめします

マイクロソフトの「Office 365」は、ExcelやPowerPointをはじめとしたOffice製品、メール、スケジュール管理などをクラウド上で使用できます。非常に利便性の高いシステムを実現することが可能です。

Office 365 について見る

おすすめの記事