道具としてのExcel活用

失敗に学ぶExcel活用:シート数の限界!? 18MBの巨大ファイル

使い続けて5年、1000シートを超えたエクセルの末路

要約

  • A社はある農産物に特化して小売・卸を行っている、地方都市に本社を持つ老舗企業。
    注文を取引先ごとのExcelシートにとりまとめて、商品ごとの在庫引き去りと月末の請求処理に活用。
  • 5年を超えたところで、1000シートを超えた!
  • 取引先は増減するが、シートを削除すると壊れるのでそのままに。商品も増減するのでそのままに。気が付いたらシートが多すぎて、めくって印刷するだけでも半日作業。自動化できないか?

対象業務の概要

A社では、毎朝FAXで来る大量の注文を、まず顧客ごとのシートを探して転記しています。シートが多いので、目次シートを作成し、そこから該当するシートにジャンプできるよう、工夫をしています。

こうして全ての注文を転記後、顧客在庫シートに商品ごとに数量を集計する関数が組まれています。元々あった在庫に対して、当日あった注文数を差し引くと、新しい理論在庫となります。

一旦「現在庫量-集計した当日注文数=新在庫数」までを関数で求めた後、新しいファイル(翌日分)の現在庫数に新在庫数を転記します。

また、翌月になると各顧客からの注文を基に、取引先ごとに集計して請求書を発行していますが、これは全て手作業(1日~末日のファイルを全て計算)となっています。

業務上の課題

取扱商品は季節ごとに変動するので、毎月在庫シートの内容を修正しています。

また顧客も毎月変動するので、必要に応じてシートを追加、目次シートも更新しています。

こうして、すごい勢いでシートが増えた結果、5年を過ぎる頃には1000シートを超え、ファイルサイズも18MBになってしまいました。

こうなると、ファイルを開くのに3分、シート間を移動するだけで十数秒待たなければなりません。

あまりにファイルが「重くなった」のと、もはや特定の担当者でなければ保守(メンテナンス)ができないことから、経営者は危機感を持ち始めました。。

当事者が考えた原因と対策(方針)

最初担当者は、シートが多いのが原因なので、改めて現在使っている分(取引先・商品)だけにリストラしたいと考えました。

ついでに、式を見直して、毎月修正しなくてもよいようにならないかとのご相談です。

真の原因と対応

式や関数を使う場合、計算対象となる数字があるシート・セルは固定となります。このため、在庫が増えたり取引先シートが増える都度修正がいるのです。
管理のしやすさを考えると、顧客ごとにファイル(Book)に分離し、例えば「注文フォルダ」内に置いておくようにすれば、数が変わっても、式を壊すなどの他への影響を排除できます。
同様に、取扱商品も切り離して別Book化すれば、式や関数の影響を受けなくなります。販売対象の商品にのみフラグ(横の列に1を設定する等)をたてておく運用とすれば、不要になった商品を削除しなくても、年間通じて取り扱う商品すべてを登録しておいてよいので、管理が楽になります。
さらに、取引先単位でファイルになっていることから、機械的にマクロ(VBA)で集計すれば、取引先ごとの集計→請求書の作成も、比較的単純なロジックで実現できます。返品・廃棄など前月の調整が必要な場合は、注文ファイルと同じファイルに赤伝をきればよいので、例外的な事象も吸収しやすくなり、柔軟な運用ができます。
こうして、顧客ごとのファイルに注文を転記(一部顧客は直接記入してくれます)後、「注文フォルダ」に放り込んでおくだけで、自動的に在庫の引き去り・請求書の集計ができるようになりました。

このケースから学べること

「Excelは式や関数で使うもの」という前提だと、このようにシートがどんどん増えてしまいがちです。

プログラム処理(≒マクロ)にすることで、「特定フォルダにあるファイル全てを処理」のようなことができるので、参照先を固定にする必要がありません。

固定にするから式や関数が壊れるのであって、ファイルとして分離すれば壊れるところが原理的になくせます。