仕事効率化ツール：Excelの集計作業を効率化する方法

こんにちは、管理人のtsenです。
私は10年以上人事業務を担当しています。
人事業務では単純な繰り返し作業が多く、仕事の業務効率化のために自分が開発したExcelマクロ（VBA）を公開したところ

「うちの部署の業務でも使いたい！」
「是非Excelマクロを教えてほしい！」
と、多くの需要があることを知り、より多くの人の役に立てるのではないかとこのサイトを立ち上げました。

現在では、社内で様々な仕事・業務効率化ツールの開発を手掛けています。
今回はその中からExcelの集計作業を効率化する方法について解説していきます。

仕事の効率化「Excelデータ集計作業」を効率化しよう
このページを読むと効率化できる仕事内容
集計業務を効率化するための事前準備
Excelの集計作業を効率化するマクロコード解説～人事業務効率化～
自動集計マクロを使う時のポイント
人事業務効率化に役立つお仕事ツールを無料公開中

仕事の効率化「Excelデータ集計作業」を効率化しよう

人事業務をしている皆さんは普段どんな仕事に時間がかかっていますか？

・労務管理などの情報を集計する仕事
・交通費精算のために申請書を集めて集計する仕事
・社内の業務申請のために申請書を提出してもらって集計する仕事

といった
Excelの「集計業務」が多いと感じている方が少なくないと思います。

１つ１つの作業は難しくなくても、社員全員分をこなそうと思うと膨大な量となってしまい、
毎日仕事が終わらない・・
一日中、Excelを転記していてたら終わってしまった・・なんてことはありませんか？

Aさん

１人１人の申請書の内容を１つのExcelにまとめたいんだけど・・
何か効率的ないい方法はないかな？

tsen

申請書をExcelにすれば、ボタン１つで簡単に集計できますよ！

このページを読むと効率化できる仕事内容

このページでは、例えば以下のようなExcel業務を効率化していきます。

作業手順のイメージとしては
①各社員から集めたExcel申請書を１つのフォルダにすべて格納します
②集計表を開きます
③マクロボタンを１回押すとフォルダ内のExcel申請書の内容を集計表に転記してくれます

集計業務を効率化するための事前準備

申請書をExcelにする

古い体質の会社だと、申請書がWord形式だったり項目選択のために□マークが準備されていて、
選択をするのに、「□」と「■」というように色を塗りつぶすような形の申請書を使っていることもあります。

このような印刷して記入することを前提に作られた書式になっている場合は要注意です。

その申請書が作成依頼、書式を変更していない場合によくありますね。
これからもしあなたが新しく「申請書」というものを作るのであればExcel一択です。
まずは、集計したい申請書をExcelにしましょう。
そして、項目選択にはプルダウン形式を使ってくださいね。

Excelに「集計表」を作成する

単純にExcelを新規に立ち上げ「集計表」という名前のExcelブックを作るだけです。
その際に、１行目集計項目を入れておきましょう。

Excelマクロの挿入

では、いよいよExcelマクロをコピペして保存していきます。
初めてExcelマクロを立ち上げる方はまず、開発タブを表示させる必要があります。
ファイル＞オプション＞リボンユーザーの設定＞開発にチェック

これで、開発タブが表示されます。

開発タブ＞Visual Basic＞挿入＞標準モジュール
でExcelマクロを挿入する場所が開きますので、そこに下記のコードを貼り付けてください。
そして、Excel自体を.xlsm形式で保存してください。

Sub 自動集計()
  Dim folder As String
  Dim file As String
  Dim book As Workbook
  Dim i As Integer
  i = 2

  With Application.FileDialog(msoFileDialogFolderPicker)
     If .Show = True Then
          folder = .SelectedItems(1)
     End If
  End With

  file = Dir(folder & "¥*.xlsx")

  Do While file <> ""
   
    Set book = Workbooks.Open(folder & "¥" & file)
    
    ThisWorkbook.Worksheets("Sheet1").Range("A" & i).Value = book.Worksheets("申請書").Range("B3").Value
    ThisWorkbook.Worksheets("Sheet1").Range("B" & i).Value = book.Worksheets("申請書").Range("B4").Value
    ThisWorkbook.Worksheets("Sheet1").Range("C" & i).Value = book.Worksheets("申請書").Range("B5").Value
    
    file = Dir()
    i = i + 1
    
    book.Close
    
  Loop
  
End Sub

難しい方は、超初心者向け「初めてのマクロ」を参考にしてください。

Excelの集計作業を効率化するマクロコード解説～人事業務効率化～

Excelマクロは、人事の仕事を劇的に効率化してくれる可能性を秘めていますが
その意味を理解していないと、応用が出来ません。

以下で、コード内容を初心者でもわかるように解説していきます。

Excelマクロ：第１ブロック解説

Sub 自動集計()
  Dim folder As String
  Dim file As String
  Dim book As Workbook
  Dim i As Integer
  i = 2

Sub 自動集計()　＝　単純にマクロのタイトル（名前）です。
Dim folder As Stringとは、いったい何を意味しているんでしょうか？

マクロというものは最初に
「これからこういう処理をするよ」と宣言をする必要があります。

Dim folder As String＝「これから出てくるfolderっていうのはString（文字列）だよ」
とコンピュータに教えてあげているんです。
宣言をしないでいきなりコンピュータに「folderを開いて！」と指示しても

folderって何ですか？数字ですか？そんな数字はありません！

と困ってしまうからです。
なので、最初にfolderっていうのはString（文字列）だよと教えてあげるんです。
同様に、
Dim file As String＝「file」は「String（文字列）」だよ
Dim book As Workbook＝「book」は「エクセルのブック（Workbook）」だよ
Dim i As Integer＝「i」っては「数字（Integer）」のことね
i = 2　「i」は２だよ
と、最初に教えてあげているんですね。

Excelマクロ：第２ブロック解説

With Application.FileDialog(msoFileDialogFolderPicker)
     If .Show = True Then
          folder = .SelectedItems(1)
     End If
  End With

このブロックは何をしているかザックリというと
「ファイル選択する画面を開き、任意のフォルダを選択してもらう」
「その選んだフォルダをfolderという名前にする」
ということをしています。

Aさん

つまり、「folder」っていうのは「申請書が格納されているフォルダ」ってことだね

tSen

そのとおり！
ここでは深く考えず、その理解があれば問題ありません。

Excelマクロ：第３ブロック解説

file = Dir(folder & "¥*.xlsx")

Aさん

さっき「folder」は「申請書置き場」のことだったよね？
じゃあ、この「file」っていうのは、
そのフォルダに入っているExcelっていう意味かな？

tSen

そのとおり！
申請書フォルダに入っているExcelのことを「file」っていう名前にするよ！
という意味なんです。

「folder」＝申請書置き場のこと。
「file」＝そのフォルダに履いている申請書（Excelファイル）のこと。

Excelマクロ：第４ブロック解説

さて、ではいよいよ、業務効率化の肝である「集計作業」のマクロコードに入ります。
ここが、このExcelマクロの本編です。

Do While file <> ""
   
    Set book = Workbooks.Open(folder & "¥" & file)
    
   ThisWorkbook.Worksheets("Sheet1").Range("A" & i).Value = book.Worksheets("申請書").Range("B3").Value
    ThisWorkbook.Worksheets("Sheet1").Range("B" & i).Value = book.Worksheets("申請書").Range("B4").Value
    ThisWorkbook.Worksheets("Sheet1").Range("C" & i).Value = book.Worksheets("申請書").Range("B5").Value

Do While file <> “”　は「file」がなくなるまで繰り返してね。
という意味なんですが
後で詳しく解説しますのでとりあえず、飛ばしましょう。
Workbooks.Openは、そのままエクセルを開くなので
申請書フォルダに入っているエクセルを開いて、
Set book　は　『book」という名前で呼びますね。
という意味です。

「book」＝申請書フォルダに入っているエクセルを開いたもの

少し難しくなってきましたね。
1つ1つ理解していけば大丈夫です。
ゆっくり読み進めていきましょう。
続いてのコードを見てみましょう。

ThisWorkbook.Worksheets("Sheet1").Range("A" & i).Value = book.Worksheets("申請書").Range("B3").Value

は何を意図しているのか解説する前に、
ここでポイントです。

マクロで「＝」が出てくる場合、
右側の値を左側へ代入するというのが原理原則です。

つまり、
「A=B」の場合、Bの値をAへ代入する
という意味になります。

これを踏まえたうえで、
このコードを右側を見てみてください。
book.Worksheets(“申請書”).Range(“B3”).Value

と書かれていますね。

book.　開いたエクセルの
Worksheets(“申請書”)　申請書というワークシートの
Range(“B3”)　B3セルの
Value　値

と書いてあります。

対して左側のコードは

ThisWorkbook.Worksheets(“Sheet1”).Range(“A” & i).Value

なので、

ThisWorkbook.　このワークブック（つまり集計表）の
Worksheets(“Sheet1”)　Sheet1というワークシートの
Range(“A” & i)　A2セルの
Value　値

Aさん

ちょっと待って！
なんでRange(“A” & i)がA2セルってことになるの？

tSen

一番最初の宣言を思い出してください。
i = 2　「i」は２だよ
とコンピュータに教えましたよね。

申請書置き場フォルダに入っている開いたエクセルの
申請書というワークシートのB3セルの値を
集計表のSheet1のA2セルに代入する

ここが理解できてしまえば、次の部分も同じです。

    ThisWorkbook.Worksheets("Sheet1").Range("B" & i).Value = book.Worksheets("申請書").Range("B4").Value
    ThisWorkbook.Worksheets("Sheet1").Range("C" & i).Value = book.Worksheets("申請書").Range("B5").Value

申請書というワークシートのB4セルの値を集計表のSheet1のB2セルに代入する
申請書というワークシートのB5セルの値を集計表のSheet1のC2セルに代入する

となります。

続いてこんなコードが出てきます。

file = Dir()
    i = i + 1

ここでも重要なポイントが２つあります。

先ほど

Do While file <> “”　は「file」がなくなるまで繰り返してね。

という意味なんですが後で詳しく解説しますのでとりあえず、飛ばしましょう
という部分がありましたね。

Do While file <> “”
　　～処理内容～
file = Dir()
　i = i + 1

Do While file <> “”　から　file = Dir()までに入っている処理内容をファイルがなくなるまで繰り返す。

という繰り返しの処理をしています。

この繰り返しはExcelマクロではよく使いますので覚えておきましょう。
そして、i = i + 1は何を意味しているのでしょうか？
i は 2のことでしたよね？そのまま入れると
2　＝　2　+　1　？？？？

おかしな式になってしまいます。

正解は、i = 2 + 1なんです。

このような「i」のことを変数と言います。

次の、「i」は3になるよということなんです。

この変数の考え方は慣れるまで少し難しく感じるかもしれませんが、

実際のコードをみて具体的にイメージしてみましょう。

Do While file <> ""
   
    Set book = Workbooks.Open(folder & "¥" & file)
    
   ThisWorkbook.Worksheets("Sheet1").Range("A" & i).Value = book.Worksheets("申請書").Range("B3").Value
    ThisWorkbook.Worksheets("Sheet1").Range("B" & i).Value = book.Worksheets("申請書").Range("B4").Value
    ThisWorkbook.Worksheets("Sheet1").Range("C" & i).Value = book.Worksheets("申請書").Range("B5").Value

   file = Dir()
    i = i + 1

先ほどまでの解説で

申請書のB3セルの値を集計表のSheet1のA2セルに代入する
申請書のB4セルの値を集計表のSheet1のB2セルに代入する
申請書のB5セルの値を集計表のSheet1のC2セルに代入する

まで出来ましたよね？

ここまででAさんの申請書内容は転記されています。

そしたら、

file = Dir()　ファイルがなくなるまで繰りかえすよ。
i = i + 1　次の、「i」は3になるよ。

というコードなので、

次のBさんの「file」を開いて処理を実行します。
次のファイル処理では、iは3とします。

と判断され、

次はBさんの申請書内容を転記していきます。

申請書というワークシートのB3セルの値を集計表のSheet1のA3セルに代入する
申請書というワークシートのB4セルの値を集計表のSheet1のB3セルに代入する
申請書というワークシートのB5セルの値を集計表のSheet1のC3セルに代入する

Bさんの転記終了後、

まだCさんのファイルが残っているので処理を繰り返します。

次はCさんの「file」を開いて処理を実行します。
次のファイル処理では、iは4とします

と判断され、次はCさんの申請書内容を集計表に転記していきます。

tSen

Cさんの申請書内容は、どこに転記されるか
もうわかりますね？

Aさん

iは4なので、それぞれ
A4セル・B4セル・C4セルですね！

こんな感じで集計されたと思います。

自動集計マクロを使う時のポイント

さて、ここまで読んできて、勘のいい方はお気づきかもしれませんが、
このマクロの肝の部分で
申請書というワークシートのB4セルの値を集計表のSheet1のB2セルに代入する
という部分が出てきましたよね？

つまり、提出される申請書は、ファイル名は何でもいいんですが
ワークシート名は「申請書」である必要があります。

ワークシート名が申請書 — ワークシート名が「申請書」というシート名である必要がある

よって、このシート名を変えられないようにしておくことがポイントです。
校閲＞ブックの保護＞パスワード設定＞OKで簡単に変更禁止にできます。

人事業務効率化に役立つお仕事ツールを無料公開中

今回は、人事業務効率化お役立ちツールとして自動集計マクロをご紹介してきました。
この集計作業が効率化できるだけで、人事業務の多くの仕事が効率化できると思います。
これを基本にしていけばさまざまな業務に応用することもできます。

例えば、
・集計処理が終わったら、申請書自体を通し番号を付けた名前で保存させる
・集計をA2セルからスタートじゃなくてB3セルからにする
なんかもできるようになります。
まずは、この基本コードをしっかり理解してみてください。

このサイトでは、人事業務のお仕事に役立つツールをたくさん紹介しています。
主にExcelマクロを使った無料で導入できるツールになりますので是非取り入れてみてください。