道具としてのExcel活用
失敗に学ぶExcel活用：シート数の限界!? 18MBの巨大ファイル
使い続けて5年、1000シートを超えたエクセルの末路
要約
- A社はある農産物に特化して小売・卸を行っている、地方都市に本社を持つ老舗企業。
注文を取引先ごとのExcelシートにとりまとめて、商品ごとの在庫引き去りと月末の請求処理に活用。 - ５年を超えたところで、1000シートを超えた！
- 取引先は増減するが、シートを削除すると壊れるのでそのままに。商品も増減するのでそのままに。気が付いたらシートが多すぎて、めくって印刷するだけでも半日作業。自動化できないか？
対象業務の概要
A社では、毎朝FAXで来る大量の注文を、まず顧客ごとのシートを探して転記しています。シートが多いので、目次シートを作成し、そこから該当するシートにジャンプできるよう、工夫をしています。
こうして全ての注文を転記後、顧客在庫シートに商品ごとに数量を集計する関数が組まれています。元々あった在庫に対して、当日あった注文数を差し引くと、新しい理論在庫となります。
一旦「現在庫量－集計した当日注文数＝新在庫数」までを関数で求めた後、新しいファイル（翌日分）の現在庫数に新在庫数を転記します。
また、翌月になると各顧客からの注文を基に、取引先ごとに集計して請求書を発行していますが、これは全て手作業（1日～末日のファイルを全て計算）となっています。
業務上の課題
取扱商品は季節ごとに変動するので、毎月在庫シートの内容を修正しています。
また顧客も毎月変動するので、必要に応じてシートを追加、目次シートも更新しています。
こうして、すごい勢いでシートが増えた結果、5年を過ぎる頃には1000シートを超え、ファイルサイズも18MBになってしまいました。
こうなると、ファイルを開くのに3分、シート間を移動するだけで十数秒待たなければなりません。
あまりにファイルが「重くなった」のと、もはや特定の担当者でなければ保守（メンテナンス）ができないことから、経営者は危機感を持ち始めました。。
当事者が考えた原因と対策（方針）
最初担当者は、シートが多いのが原因なので、改めて現在使っている分（取引先・商品）だけにリストラしたいと考えました。
ついでに、式を見直して、毎月修正しなくてもよいようにならないかとのご相談です。
真の原因と対応
式や関数を使う場合、計算対象となる数字があるシート・セルは固定となります。このため、在庫が増えたり取引先シートが増える都度修正がいるのです。
管理のしやすさを考えると、顧客ごとにファイル（Book）に分離し、例えば「注文フォルダ」内に置いておくようにすれば、数が変わっても、式を壊すなどの他への影響を排除できます。
同様に、取扱商品も切り離して別Book化すれば、式や関数の影響を受けなくなります。販売対象の商品にのみフラグ（横の列に1を設定する等）をたてておく運用とすれば、不要になった商品を削除しなくても、年間通じて取り扱う商品すべてを登録しておいてよいので、管理が楽になります。
さらに、取引先単位でファイルになっていることから、機械的にマクロ（VBA）で集計すれば、取引先ごとの集計→請求書の作成も、比較的単純なロジックで実現できます。返品・廃棄など前月の調整が必要な場合は、注文ファイルと同じファイルに赤伝をきればよいので、例外的な事象も吸収しやすくなり、柔軟な運用ができます。
こうして、顧客ごとのファイルに注文を転記（一部顧客は直接記入してくれます）後、「注文フォルダ」に放り込んでおくだけで、自動的に在庫の引き去り・請求書の集計ができるようになりました。
このケースから学べること
「Excelは式や関数で使うもの」という前提だと、このようにシートがどんどん増えてしまいがちです。
プログラム処理（≒マクロ）にすることで、「特定フォルダにあるファイル全てを処理」のようなことができるので、参照先を固定にする必要がありません。
固定にするから式や関数が壊れるのであって、ファイルとして分離すれば壊れるところが原理的になくせます。
いよいよ自分では手に負えなくなったら、Excel専門の当社にご相談下さい。匿名歓迎です！
また、既存のExcelファイルの修繕のご相談承っています。