フォルダ内の複数のExcel経費精算書を集計・弥生会計へインポートするマクロ

フォルダ内にある経費精算書がある場合、手でコピーしていたら大変です。
こういったときにはマクロが便利です。

EX-IT_No-17

※パワポで作成

Excelで経費精算書をつくる

Excelで経費精算書をつくるなら、次のようなフォーマットがあります。

EX-IT_No-02

工夫しているところは、経費データを部分で、テーブル機能を使っている点です。
テーブル(Ctrl+T)を使えば、最後の行まで入力し(907)、Tabキーを押せば、

EX-IT_No-07

書式を維持したまま、次の行をつくることができます。
網掛け(青、白、青・・)のパターンもそのままです。

EX-IT_No-08

テーブルをクリックして、上部の[テーブルツール]の[集計行]にチェックを入れれば、集計行が追加されます。

SUMで計算するのと比べるとミスがありません。

InkedEX-IT_No-09_LI

Excel経費精算書に繰越マクロを入れる

この経費精算書は月ごとにシートを分けています。
シート名は、「2018年2月」といった形式で、これを集計マクロで使っているので、正しく入力しなければいけません。
「2018.2」
「2018年2月」
「h30.2」
「平成30年2月」
「2月」
「2」
じゃダメなわけです。

本来は、正しく入力できるよう人が気を付けるべきなのですが、なかなかそうもいきません。
そこで、人がやらなくていいようにします。
繰り越す場合、「繰越」のボタンを押せば、EX-IT_No-13
このように、繰り越すようにマクロを組み込んでみましょう。
EX-IT_No-14

・シートをコピー
・セルA2の年月に1か月加算
・シート名を変更
・経費データをクリア
といったマクロです。

すでに繰越しているのに繰越ボタンを押してしまった時のためにエラー処理も入れています。

経費データをクリアするのは前の月のデータが残ってしまう可能性があるからです。

さらにはシートを保護してもいいでしょう。

(お客様に提供しているファイルには保護をかけています)

その場合、繰越のマクロに、パスワードをいったん解除し、再度パスワードで保護をするという処理が必要です。

フォルダ内のExcel経費精算書を集計するマクロ

この経費精算書をプリントアウトして提出し、ハンコを押して・・とやると、紙を見ながら会計ソフトに入力しなければいけません。
データのままにしておき、それを集計したほうが効率的です。

とはいえ、複数のファイル、シートにあるデータを集計するのは、Excelの苦手とすること。
ここはマクロの出番です。

あらかじめ同じフォルダに入れておき、マクロで集計します。
ついでに会計ソフトへのインポートデータをつくるのが、このマクロです。

参照ボタンを押し、経費精算書ファイルが入っているフォルダを指定します。

InkedEX-IT_No-15_LI

次に年月を指定します。

この数値を読み取って、各経費精算書の該当月のシートを読み込んでいるわけです。

InkedEX-IT_No-15_LI2

集計ボタンを押せば、

・フォルダにある経費精算書ファイルを開き

・該当月の経費データをコピーし、

・経費集計ファイルのシート「経費」に貼り付ける

といった処理をファイルの数だけ、つまり社員の分だけ繰り返します。

何人いても手間は同じです。
フォルダに入れ、ファイル名に「経費精算書」が入っていれば、粛々と処理してくれます。
こういったことを人がやっていたらキリがありません。
ただ、大事なのは、Excelに入力していただくこと。
これが手書きだとアウトですし、好き勝手なフォーマットだったらマクロもかないません。
お客様のケースでは、社長が先陣を切っていただいたので、このしくみを導入できました。
最初に導入したのは2013年です。

Excelで入力なんて・・・と思われるかもしれませんが、やってみると手書きで電卓たたくよりはるかに楽でミスもありません。

経費精算も仕事です。
手書きでささっとやったりレシートを経理に丸投げすることにより、経理担当者が苦労するということはあってはいけません。

全体の効率化を考えるべきです。

InkedEX-IT_No-15_LI3

そして、集計したデータを

EX-IT_No-04

ピボットテーブルで集計しています。
ピボットテーブルでやっているのは、人が増減したときにも対応するためです。

ピボットテーブルは[更新]をしなければいけませんので、その部分もマクロでやっています。

EX-IT_No-06

そして、集めた経費データを有意義に使いたいものです。ここで、弥生会計のデータ形式に数式で連動させ、マクロで仕上げをして、CSVデータに保存しています。

EX-IT_No-16

マクロのコードを載せています。
アレンジして使ってみていただければ。

経費精算システム・ソフトを入れればいいのでしょうが、そうもいかない場合も多いです。
高くて使い勝手の悪いものも多いですし。

Excel+マクロ+人のアイデアで、こういったものもつくれます。


【編集後記】

昨日は、朝、新宿に行き、発売されたSurface Book 2 15インチを入手。
セットアップして、午後はExcelマクロ プログラミング入門セミナーを開催しました。

次回は、5/11に開催します。

【昨日の1日1新】

※詳細は→11新」ぬる燗佐藤

Surface Book 2 15インチ

【昨日の娘日記】

新年度の最初の週、新しい先生や新入児に戸惑っていましたが、だいぶ慣れたようです。
昨日は、送っていったとき、泣かずにバイバイしてました。


『新版 そのまま使える経理&会計のためのExcel入門』キャンペーン (~12/11 8:59)
■スポンサードリンク ■井ノ上陽一のプロフィール
■スポンサードリンク


井ノ上陽一のVALU
■著書
ひとり税理士のIT仕事術―ITに強くなれば、ひとり税理士の真価を発揮できる!!
フリーランスとひとり社長のための 経理をエクセルでトコトン楽にする本
新版 ひとり社長の経理の基本
毎日定時で帰っても給料が上がる時間のつかい方をお金のプロに聞いてみた!
『ひとり税理士の仕事術』
『フリーランスのための一生仕事に困らない本』
『社長!「経理」がわからないと、あなたの会社潰れますよ』
『そのまま使える経理&会計のためのExcel入門』
【監修】十人十色の「ひとり税理士」という生き方