業務改善事例ー予算策定における「地獄のExcel」を「天国のExcel」へ

外資系の製造業、超大手のお客様です。財務関連の業務を担う部署が毎年予算策定を行っており、予算策定の提出フォーマットがExcelになっていました。経費の按分のロジックや事業部の増減などがここ2〜3年であり、その都度シートを追加して対応していたのですが、どのシートをどう編集してどの計算式を確認すれば欲しい結果が出るのか、体で覚えるしかない状態になっていました。

このぼかした画像が一番重要なシートで、ここに経費や人件費などの集計結果を元に配布を行ってから事業部に按分・・・みたいなことをやっているシートです。ただ、経費や人件費などが事業部から別Excelでもらってそれをシートの科目にコピペする運用だったので、経費の内訳に変更があったら振り出しに戻る運用でした。また、よく見ると計算されているけどどこからも参照されていない値や、数字が直打ちされているけれど、その数字がどこから来たのか全くわからないというものも多くありました。

当初はExcelをWebデータベース化しようという方向で検討していましたが、必要な数字の集計のシートが10個近くあったこと、その各々の集計ロジックが異なること、シートをまたいで数字が同じかどうか確認したいなどの特性があったことから、Webデータベース化はメリットがないと判断し、既存Excelの精査を行う方向で検討を開始しました。

Excelを使った業務が煩雑になる理由は相場が決まっています。Excelのシート間で「加工」「修正」「確認」がぐるぐる回るからです。入力データだけ！ピボットだけ！ピボット元に表を作るだけ！のように、シート間の責務が明確になっているなら追いかけるのも簡単だし、いじるのは入力データだけでよくなります。Excelは自由すぎるので、シート間の責務をキッチリ考えないと、どのデータがどこから来たのかぐるぐる回ってよーわからん、状態になります。

今回の改善の最大のポイントは、自動転記によって元データを自動更新することです。

1. 元データに「データベース」シートを作る。
2. 事業部からもらったExcelは予算フォルダに格納。
3. 予算策定のExcelにVBAを書いて、予算フォルダに格納したExcelを1件ずつ開き、データベースシートに経費申請内容を自動でコピペ。数千行でも5分あればデータベース化できました。
4. データベース化されると、事業部単位のPLを作るために必要なピボットテーブルが更新され、ピボットを参照してSUMIIFしている各シートの数字が自動で連動される。

つまり、自動転記ボタンを押すだけで予算策定が可能になる状態を作りました。

この仕組みをつくるまえは、最低でも200時間程度かかっていた(何度も変更が入るため)業務が、予算フォルダに格納されたデータの中身を変えるだけで良くなったので、確認を入れても1時間程度に短縮されました。書いたVBAはせいぜい100行程度。100行のプログラムでものすごいコスト削減が出来て、他事業部からも大いに驚かれたようです。