Spreadsheet Best Practices: Stop Making These Mistakes — csv-x.com

私は今でも、スプレッドシートのセルに「ピリオド」の代わりに「カンマ」を入力したために230万ドルが消えた日のことを覚えています。私は中堅の投資会社でファイナンシャルアナリストとしてキャリアを始めて3年目で、私たちのチームは鉄壁の買収提案を提出したばかりでした。キャッシュフロー予測の小数点のエラーは、私たちに取引を失わせただけでなく、クライアントとの信頼を損ない、3人の仕事を危うくしました。

それが2009年のことでした。それ以来、私は15年間データオペレーションのコンサルタントとして、フォーチュン500企業から急成長中のスタートアップに至るまで、さまざまな企業と仕事をしてきました。そして、私が想像できるほぼすべてのスプレッドシートの災害を目の当たりにしてきました。在庫の誤カウントで80万ドルの過剰在庫を持ったこと、IRSの監査を引き起こす給与エラー、全注文の規模でずれたマーケティング予算などです。共通の糸は、スプレッドシートを気軽なメモ帳として扱うことから生じる防げるミスです。

ほとんどの人が気づいていないことは、ハワイ大学のレイモンド・パンクによる研究によれば、すべてのスプレッドシートの88％にエラーが含まれているということです。ラベルのタイプミスや軽微なフォーマットの問題ではなく、ビジネスの意思決定に影響を与える実際の計算エラーです。ヨーロッパの経済学研究者が実際の企業の運用スプレッドシートを調べたところ、セルの数式あたりのエラー率は0.8％から1.8％に達しました。それは小さく聞こえるかもしれませんが、通常の財務モデルには500〜1,000の数式が含まれています。計算してみてください：平均でスプレッドシートあたり4〜18のエラーがあることになります。

私はあなたをスプレッドシートから逃げさせるためにここにいるわけではありません。スプレッドシートは強力で柔軟であり、正しく使用されれば非常に不可欠です。しかし、200以上の組織と相談し、数千のスプレッドシートを個人的に監査した結果、私はアマチュアのスプレッドシートユーザーと、信頼性が高くスケーラブルなデータシステムを構築する専門家を分けるパターンを特定しました。私が学んだことを共有しましょう。

基盤の問題：スプレッドシートをデータベースではなく文書として扱うこと

私が見る最大の間違い、つまり私が遭遇する深刻なエラーの約40％を占めるのは、人々がスプレッドシートをワードプロセッサの文書のように扱うことです。彼らは美観のためにセルを結合し、視覚的な間隔のために空白行を挿入し、意味を伝えるために色を主な方法として使用し、関連データを一貫した構造もなく複数のタブに散在させます。

実際の例を挙げましょう。昨年、私は生産データを「マスター スプレッドシート」と呼ばれるものに追跡している製造会社と仕事をしました。それは7年にわたって同じオペレーションマネージャーによって維持されており、彼女が引退すると混乱が起こりました。スプレッドシートには23のタブがあり、それぞれが異なる製品ラインを表していました。整理されているように聞こえますよね？間違っています。各タブにはまったく異なる構造がありました。あるタブではA列に日付がリストされ、別のタブではC列にリストされていました。あるタブは「製品ID」をヘッダーとして使用し、他のタブは「SKU」や「アイテムコード」または単に「ID」を使用していました。視覚的なヘッダーがあるセルがあちこちに統合されていて、データを適切にソートまたはフィルターすることが不可能になっていました。

彼らが私にこのシステムを統合する手伝いを求めたとき、私は「2022年第3四半期の総生産量は何でしたか？」のような簡単な質問に対して、異なる日付形式と列構造を持つ23の異なるタブを手作業でチェックする必要があることを発見しました。30秒でできるはずのクエリが、45分の手作業を要しました。また、構造が一貫していなかったため、自動化する方法はありませんでした。

この修正は、基本原則に立ち返る必要がありました。私は彼らに単一のフラットデータテーブルで追跡システムを再構築させました。各行は1つの生産イベントを表し、各列は1つの属性を表します：日付、製品ID、数量、ライン番号、シフト、品質グレード。結合されたセルなし。間隔のための空白行なし。主要なデータインジケーターとしての色分けなし。単にフィルタリング、ソート、ピボット、及び分析が可能なクリーンで構造化されたデータです。

結果は？月次報告時間が12時間から45分に短縮されました。彼らは突然、これまで答えられなかった質問に答えられるようになりました。そして、2年後に適切なデータベースシステムに移行したとき、データがすでに適切に構造化されていたため、移行はスムーズに行われました。

原則は次の通りです：分析、クエリ、または報告に必要なデータをスプレッドシートに保存する場合は、それを文書ではなくデータベーステーブルとして扱います。1行に1レコード。1列に1属性。一貫したヘッダー。データ範囲内に結合されたセルはなし。美しいフォーマットはプレゼンテーション層に取っておいて、クリーンデータテーブルから引っ張ってくる別の要約シートやレポートを作成してください。

手動データ入力とコピー＆ペースト操作の隠れた危険

私はかつて、ある医療機関の患者スケジューリングシステムを監査し、職員が予約ソフトウェアからExcelに予約データを手動でコピーし、その後請求システムにも再度コピーしていることを発見しました。これは、週5日、1日に40〜60回発生していました。エラー率を計算したところ（200件のランダムなエントリをソースレコードとスポットチェックすることによって）、3.2％というエラー率を見つけました。これは、1日に約6〜10件、年間で1,500〜2,500件のエラーに相当します。

"最も高価なスプレッドシートのエラーは、クラッシュするものではなく、正しい数字が入っているために完全に動作するものです。"

各エラーには下流の影響がありました。誤った予約時間は、医者が空いていないときに患者が到着することを意味しました。誤った請求コードは、保険の拒否や支払いの遅延につながりました。誤った患者IDは、HIPAA違反や法的責任の可能性をもたらしました。組織は手動データ移行から発生したエラーを修正するのに週に約15時間を費やしていました。

手動データ入力の根本的な問題は、人間がミスをするだけでなく（実際、私たちは予測可能な割合でミスをします）、手動プロセスはスケールしなく、効果的に監査できず、単一の障害点を生み出すことです。一人の人間だけがスプレッドシートを更新するための「プロセス」を知っている場合、病気、休暇、または会社を退社したときに何が起こるのでしょうか？

私はこのパターンを何百回も見てきました：誰かがスプレッドシートシステムを構築し、彼らだけが使用しているときには完璧に動作します。彼らはすべてのクセを知っていて、特別なケースをすべて覚えており、制約を回避することができます。そして会社が成長し、より多くの人がアクセスを必要とし、突然、一人の専門家には有効だったシステムが負担になります。データが一貫して入力されなくなり、人々が互いの作業を上書きし、誰が最新のバージョンを持っているのか分からなくなります。

解決策は、手動入力を完全に排除することではありません—現実的ではない場合もあります。しかし、次の実践を実行することでエラーを大幅に減少させることができます。まず、データ検証を徹底的に使用します。列に日付だけが含まれている必要がある場合は、他のものは拒否するように検証を設定してください。製品コードが特定の形式に従う場合は、それを強制する検証ルールを作成します。一般的に、私はデータエントリ用スプレッドシートの60〜80％の列に検証ルールを設定します。

次に、有効な値のセットが限られているフィールドにはドロップダウンリストを作成します。「ニューヨーク」、「NY」、「ニュー・ヨーク」、「N.Y.」のように、州フィールドに手で入力させないでください。ニューヨークのための1つのオプションだけを持つドロップダウンを提供してください。私の経験では、これだけで入力エラーを40〜50％削減できます。

第三に、可能な限り、データを再入力するのではなく、インポートします。ほとんどの現代のソフトウェアはCSV形式にエクスポートできます。データ型とフォーマットを保持しながらスプレッドシートにCSVファイルを適切にインポートする方法を学んでください。最初は設定に10分かかることはありますが、何時間も作業を節約し、誤りのカテゴリ全体を排除します。

数式エラー：スプレッドシートの信頼性を脅かす見えない殺人者

これは私のコンサルティングキャリアで少なくとも30回は遭遇したシナリオです：誰かが200の数式を使って財務モデルを構築します。彼らはそれを慎重にテストし、結果を確認し、すべてが完璧に見えます。6か月後、誰かがデータ範囲の真ん中に新しい行を挿入します。半分の数式は新しい行を含めるように正しく更新されますが、もう半分は更新されません。誰も気づきません、なぜなら合計はまだ合理的に見えるからです。そのモデルは現在不正確な結果を生成しており、誰かが問題を発見するまでには数ヶ月または数年かかることがあります。