【ExcelVBA】経費精算書からデータを自動収集する【経理必見】

みなさん、こんにちはケンケンです。

 

今回は、経費精算書を使ってデータを自動収集する仕組みを構築します。

 

けっこう難易度が高いですが、ひとつずつ解説していきますので頑張っていきましょう。

ここを乗り越えるとファイル間のやりとりもスムーズにできるようになります。

 

 

準備をしよう

 

それでは早速、もろもろ資料を準備していきます。

 

経費精算書を用意しよう

まずは、経費精算書を用意します。

といっても、適当な書式をネットからダウンロードして加工すれば良いでしょう。

みなさんもご自分の好みや会社で使っている書式を使っても問題ありませんが、以後の説明は以下の書式を使います。

この書式は、こちらからダウンロードして少し加工しました。

加工した箇所は、備考欄を勘定科目欄に変更したのと押印欄を追加しました。

 

勘定科目リストを作成しよう

経理処理を最後まで考えると、精算書に記入する段階で勘定科目が確定していると後々の処理が楽になります。

そこで、経費精算書ファイルに勘定科目リストシートを追加して精算書ではプルダウンで選択できるようにカスタマイズします。

勘定科目リストシートの中身は以下のようにしました。リスト内容はご自分の環境に合わせて設定してください。

 

 

科目リストができたら精算書の勘定科目欄を入力規則を使ってドロップダウンリストにします

入力規則に自信のない方はこちらの記事をどうぞ。

 

ここまでできたら、名前を付けて保存します。名前は「経費精算書(元)」とします。

 

社員名簿を作ろう

経費精算書を集計するのに直接関係するものではありませんが、書類の提出状況を確認する際などに必要になるので、実務を見越して用意しておきます。

上記のような感じで作成し、「社員名簿」として名前を付けて保存します。

 

VBA記述用ファイルを用意しよう

VBAを記述しデータを収集するファイルを作ります

以下のようなシート構成にして「集計.xlsm」という名前を付けてファイルを作っておきます。

社員ごとの経費精算書を作成しよう

次に社員分の精算書を作ります。

実務では当然、社員それぞれが作成したものを指定されたフォルダへ格納してもらう運用にするべきですが、今回はこちらで作ります。

上記のように作成しました。そして、名前を付けて保存します。名前は「申請日+名前」にします。

具体的には、「20190630_横溝結菜」とします。

同様に、社員名簿に記載した5名全員分を作成します。

 

最終フォルダ内構成

最終的に以下のような構成になります。

ちなみに、前提条件として一回の申請に提出できるのは精算書1枚としています。

複数枚にすると複雑になるので今回は1枚にします。今後複数でも対応可能なようにカスタマイズする方法もご紹介できたらします。

さて、下準備は整いましたので実際に集計していきます。

 

ファイル一覧を作成しよう

前回の復習も交えて進めていきます。

前回の記事をご覧いただいてからの方が理解が速いと思います。

 

それでは、データの集計をする一歩目としてフォルダ内にある経費精算書の一覧を作りましょう。

まず、集計ファイルを開いてください。

そして、VBEを起動し標準モジュールに以下のように記述してください。

コードの実行は「ファイル名一覧」シート上で行ってください

ほとんど前回の復習ですが、ひとつ工夫をしています。

それは、変数bookNにファイル名を格納するコードの内「Sheets(“ファイル名一覧”).Range(“B1”).Value」の部分です。

B1セルに入力した値によって探すファイル名を限定します。

 

具体的にはB1セルに「20190630」と入力し、実行します。すると、20190630から始まるファイルのみ検索し表示してくれます。このようにすると直接コードを修正しなくても検索したいファイル名を変更することができるので便利です。

結果はこうなります。

 

ちなみに、入力フォームを活用できるとさらに利便性が増すので、興味のある方はチャレンジしてください。

 

データ収集のコツ

 

ファイル一覧を作成できたので、次はファイルを開いてデータを収集してみましょう。

 

詳細転記シートの準備

集計ファイル内の詳細転記シートの書式を整えるコードから書いていきましょう。

新しいプロシージャsyuukei_tenkiを作成して、上記のとおり記載します。

まず、Application.ScreenUpdating = Falseで画面更新をストップさせます。テンプレです。

次に、Sheets(“詳細転記”).Range(“A:G”).ClearContentsで初期化して、それ以下で詳細転記シートの体裁を整えています。

 

 

所属~勘定科目が転記されていればOKです。

 

Dim fileLrow

fileLrow = Workbooks(“集計.xlsm”).Sheets(“ファイル名一覧”).Range(“A” & Rows.Count).End(xlUp).Row

ファイル名一覧シートの最終行を変数fileLrowに格納します。

 

ファイルを開いて転記する

まず、ひとつファイルを開いて詳細転記シートに転記してみましょう。

syuukei_tenkiプロシージャに以下のコードを追加してください。

 

コードを実行すると以下のように詳細転記シートに転記されます。

コードの解説をします。

 

Workbooks.Open ThisWorkbook.Path & “\” & Range(“A2”).Value

ファイル一覧シートのA2セルに記述されたファイル名を開いています。

開いたファイルは上記のようになっています。

 

Workbooks(“集計.xlsm”).Sheets(“詳細転記”).Range(“C2:C8”).Value = Sheets(“経費精算書”).Range(“A11:A17”).Value Workbooks(“集計.xlsm”).Sheets(“詳細転記”).Range(“D2:D8”).Value = Sheets(“経費精算書”).Range(“E11:E17”).Value Workbooks(“集計.xlsm”).Sheets(“詳細転記”).Range(“E2:E8”).Value = Sheets(“経費精算書”).Range(“M11:M17”).Value Workbooks(“集計.xlsm”).Sheets(“詳細転記”).Range(“F2:F8”).Value = Sheets(“経費精算書”).Range(“T11:T17”).Value Workbooks(“集計.xlsm”).Sheets(“詳細転記”).Range(“G2:G8”).Value = Sheets(“経費精算書”).Range(“X11:X17”).Value

経費精算書の該当箇所を詳細転記シートに転記するコードです。

例えば、経費精算書のA列には日付の情報が入っているので詳細転記シートのC列に転記することになります。以下それぞれのシートに書かれている情報をそれぞれの該当列に転記するようコードを記述しています。行数もそれぞれのデータの数に応じて転記しています。

経費精算書の該当列が飛び飛びになっていることに気付いたかと思います。

これは、ダウンロードした資料がそのような仕様になっていたらかに他ならないのですが、本来ならデータが飛び飛びになるのは良くありません。

今回は、敢えて多少不便な作りになっている資料でも強引にコードを書く手法をとっています。

実務上、書式変更することが良いという場面でも、諸事情でその書式を使い続けなくてはいけない時もあることを考慮しています。

理想は、書式から変えてしまってコードはなるべくスマートに書くことです。

 

話が反れましたが、最後にActiveWorkbook.Closeで開いたファイルを閉じます。

これで、一人分のデータが転記できましたが、これでは不完全です。

まず、開いたファイルの行数が11から17になっていますが、17は固定ではありませんね。

そこで、開いたファイルの最終行を取得する変数を用意します。

先ほどのコードを上記のように修正してください。

openFlrowという変数を用意し、そこに開いたファイルのA列の最終行を取得して格納します。

 

Workbooks(“集計.xlsm”).Sheets(“詳細転記”).Range(“C2:C8”).Value = Sheets(“経費精算書”).Range(“A11:A” & openFlrow).Value

A17だった部分をAと変数openFlrowに分けます。

これで開いたファイルの行数に関係なくデータを取得できます。

しかし、まだ不完全です。さらに修正します。

 

開いたファイルのデータ数は取得できましたが、転記先のデータ数はRange(“C2:C8”)のように固定されています。

転記先の最初の行(C2)と最後の行(C8)を変数で表現しなくてはなりません。

まず、修正後のコードをご覧ください。

詳細転記シートの最終行を取得するためのsyousaiLrow変数を用意し、詳細転記シートのA列最終行を取得していますが、最後に「+1」しています。理由は転記する行が「最終行+1行目」だからです。

続いて、詳細転記に転記する分の行を取得するtenkiRow変数を用意します。

これは、開いた経費精算書の最終行(openFlrow)から10を引いた値になります。図で確認してください。

 

転記する際にtenkiRow + 1としているのは、詳細転記シートの記述の始まりが1行目からではなく2行目からだからです。

さて、この状態でコードを実行しても先ほどと同じ結果になったら成功です。

 

出来ましたか。しかし良く見るとまだA列とB列が空欄のままです。

そこで以下のように追記します。

 

これで、所属と氏名も埋まるはずです。

 

これで、一人目のデータを転記することが出来ました。

 

繰り返し転記してみよう

一人目の転記が完成したので、次は繰り返し処理を使って全員分を転記しましょう。

 

カウント変数fileTenkiを宣言し、ファイル名一覧に書き出したファイルを順番に開き、転記処理を実行します。

つまり、開くファイルのセル番地を指定していた Range(“A2”).ValueをRange(“A” & fileTenki).Valueに変更すればOKです

 

続いて、

Range(“A” & syousaiLrow & “:A” & tenkiRow + 1)

の「tenkiRow+1」の部分を変更しなくてはなりません。

「tenkiRow+1」のままだと、各人ごとの転記する行数のみ反映された形になってしまい、転記する際にデータが重複してしまいます。

例えば、syousaiLrowに10が格納されていて、tenkiRowに7が格納されている場合、Range(“A10:A7”)という奇妙な値になってしまいますね。

これを解消するために「tenkiRow+1」を「A列の最終行(syousaiLrow)+転記する行数(tenkiRow)-1」とする必要があります。

 

こうすれと、同じくsyousaiLrowに10が格納されていて、tenkiRowに7が格納されている場合、Range(“A10:A16”)という適正な値を持ってこれます。

これで完成です。コードを実行して下記のようになっていれば成功です。

ボタンを作る

いつものようにプロシージャを統合して、ボタンを作りましょう。

まず、プロシージャを上記のようにまとめてください。

 

そしてボタンを作ります。

ボタンは、ファイル名一覧シート上で以下のように作成してください。

 

収集したデータを加工しよう

 

ここまでで一通りデータの収集が出来るようになりました。

せっかくなので収集したデータを加工して簡単な表を作ってみます。

具体的には、

  • 部署集計
  • 個人別集計
  • 科目別集計

を作って行きます。

今回はSumif関数を使います。

まず、以下のように各シートを作成します。

A列にはそれぞれ必要な項目を記述しておきます。

 

項目ができたらSumif関数で集計します。

部署集計を例にすると、B2セルに以下のように関数を記述します。図も参考にしてください。

「=SUMIF(詳細転記!$A:$A,部署集計!A2,詳細転記!$F:$F)」

記述出来たら数式をB5までコピーします。

 

関数の使い方に不安がある方はこちらを参考にしてください。

 

 

 

 

これで部署ごとの集計ができました。合計値など必要な要素は適宜追加してください。

この状態まで作成出来れば、グラフにまとめて会議資料を作ることも簡単ですね

 

最後に、個人別集計と科目別集計も同じようにSumif関数でまとめてみましょう。

上図のように集計できました。

 

これらのシートを事前に用意しておけば、後は集計ボタンを押してデータが転記されると同時に部署集計等が集計されます。

 

なお、他の集計方法としてはピボットテーブルもありますので、お好みで使い分けてください。

ちなみにピボットテーブルだと集計する度にデータ更新しないと最新のデータが反映されないので注意しましょう。

 

まとめ

 

いかがでしたか。

だいぶ難易度が高いと感じたかもしれませんが、

ここまで出来れば相当なことができるようになりますので、何度も復習してご自分の血肉にし、どんどん活用していってください。

 

ご存知の方も多いと思いますが、経費精算に限りませんが便利な機能が安価に手に入る時代になっています。

複雑かつ煩雑な業務はシステムを導入してしまうのも有効な手段です。

そんなときは、エクセルに固執せず臨機応変に使い分けると良いでしょう。

 

しかし、エクセル以外のシステムを使うことになっても、今回ご紹介した知識は持っていて損はないです。

知識を応用する思考力はむしろこれからの時代に求められる大事なスキルです。

今回得た知識を土台にして新たなステップを踏み出していただければ幸いです。

 

それでは、また次回お会いしましょう。