【スプレッドシート】集計出来ない・入力漏れ・管理出来ない原因は？ダメなシートの特徴

事業が拡大し、関わるメンバーが増えることは素晴らしいことです。

しかし、その反面で管理することの負担とストレスは限界に達していませんか？

もし今現在そんなGoogleスプレッドシートを使っているなら、今後さらにチームを拡大した時に管理の手間がさらに増えるかもしれません。

メンバーのミスが多いのは、メンバーの注意不足やスキル不足ではありません。

シートの作り方がそもそも間違っているから起きている可能性が高いです。

まずは、以下のリストで現状を診断してみてください。

以上の項目の中で、3つ以上当てはまったらシートの作り替えを検討した方がいいです。

そのまま使い続けると、メンバーのミスは減らない一方で、無駄な管理コストがどんどん増えていきます。

そう思った方のために、なぜこれらが「集計できない」「メンバーの入力漏れ」「データの破損」を引き起こすのか、その理由を解説します。

なぜこれらが集計出来ない・入力漏れ・管理出来ないシートになるのか

先ほどのチェックリストを見て、

と感じた方もいるかもしれません。

しかし、これらの見た目優先の工夫が、実はチーム全体の生産性を下げ、あなたの管理工数を増やす最大の原因になっています。

スプレッドシートは正しいルールで作れば、集計や分析は一瞬かつ、自動で終わります。

しかし、上記のリストに当てはまるシートは、その自動化の恩恵をすべて捨ててしまっています。

なぜそれぞれの項目が、チームの生産性を下げるのか具体的な理由を解説します。

毎月、右側に新しい月の列を追加し続けている（横にどんどん長くなる）

「4月」「5月」「6月」…

と、月が変わるたびに右側に新しい列を追加していませんか？

カレンダーのような見た目で、人間にとっては直感的で分かりやすい形式です。

しかし、この作り方は、後ほどデータの集計作業をしたいと思った時に、地獄を見ます。

なぜなら、スプレッドシート最強の武器であるピボットテーブルが一切使えなくなるからです。

  ピボットテーブルとは？
数万行あるデータからでも、「商品別」「月別」などの集計を、マウス操作だけで簡単に完了できる自動集計機能

この機能が使えないと、「昨年の売上と比較したい」「商品ごとの推移を知りたい」 と思った時、自動で集計できないため、セル一つ一つに複雑な計算式（SUMIFなど）を手動で組み直すことになります。

そして、その複雑すぎる数式が、「作った本人しか直せない」という最悪の結果につながります。

この結果、本来なら3秒で終わる作業に、毎回30分以上の時間をかけることになります。

【正解】データは横ではなく縦に積み上げる

正しいやり方は、A列に「日付」、B列に「商品名」…というように、縦に追加していくことです。

この形なら、後でデータを集計するのも楽になりますし、作った本人しか直せない関数が作られることも防げます。

見やすくするためにセルの結合を多用している

「日付」や「担当者名」など、同じ項目が続くときに、見た目をすっきりさせるためにセルを結合していませんか？

確かに、人間が見る分には、結合されていた方が情報が整理されていて綺麗に見えます。

しかし、データを蓄積して管理するという目的において、セルの結合は絶対やるべきではない一手です。

例えば、3行分がセル結合された佐藤さんのデータがあるとします。

人間が見れば、「この3行分がすべて佐藤さんの売上だ」と一瞬で理解できますよね。

しかし、コンピュータは1つのセルに1つのデータというルールでしか認識できません。

結果として、SUMIF関数を使って「佐藤さんの売上を合計して」と命令しても、コンピュータは、「佐藤」と書いてある一番上の1行しかカウントしません。

残りの行はスルーされ、結果として大きな集計漏れが発生してしまいます。

【正解】入力用シート（データベース）では結合は絶対禁止

正しいやり方は、同じ値であっても、すべての行に入力することです。

「同じ文字が並んでいて見にくい」

と感じるかもしれませんが、コンピュータにとってはこれが最も情報を扱いやすい形です。

1枚のシートの上下左右に、別の表（売上・経費など）が散らばっている

こうやって1枚のシートの中に「売上表」「経費表」「担当者リスト」など、複数の表を配置していませんか？

このシートの問題点は、ちょっとした修正をするだけで膨大な時間が奪われてしまうことです。

例えば、以下のようなケースがあります。

• 1行足したいだけなのに横の表がズレる
• 1列広げたいだけなのに、下の表のレイアウトが崩れる。
• 1つの表を絞り込みたいだけなのに、下の表まで道連れに消えてしまう。

こうなると、本来やりたかったデータの入力よりも、崩れた表の修復に時間を取られるようになり、シートを触ること自体がストレスになってしまいます。

【正解】1つのデータ、1つのシート

原則として1シートにつき、1つのテーブルにすることです。

• 売上のシート
• 経費のシート
• 担当者のシート

というように、トピックごとにシートをきっちり分けましょう。

こうすることで、後からデータを集計する際もスムーズに行うことができます。

「未完了の行は赤」「完了の行はグレー」など、ステータスを色だけで区別している

このように、進捗管理を色だけでおこなっていませんか？

見た目はパッと分かって便利な気がしますが、実はこれ、管理においてはあまり推奨されません。

なぜなら、進捗管理のすべてを人間の目に頼ることになるからです。

例えば、

のように、チームメンバーの進捗状況を数字で可視化したいとします。

スプレッドシートを正しい設計にしていれば、スプレッドシートの関数を使って一発で集計が完了します。

しかし進捗状況を色だけで管理しているとこれが出来なくなります。

あなたは画面を上から下までスクロールしながら、「1、2、3……」と画面を指差しして数えることになります。

管理者がこの作業に数十分も費やすのは、時間も労力も非常にもったいないです。

【正解】ステータス列を最終列に作る

正しいやり方は、色で進捗状況を管理するのではなく、「ステータス」という専用の列を作ることです。

この中で「完了」「未完了」のようにプルダウンで選べるようにします。

このように、色ではなく、文字としてデータを持たせることで、COUNTIF関数を使って一発で集計したり、特定のステータスだけをフィルタで絞り込むなどが出来るようになります。

 それでも色を付けたい場合は？
ステータスを変更したら自動で色が変わる「条件付き書式」を使いましょう。
これなら、ステータスを変更した瞬間に色も付ける事が出来るので、集計もできる上に、人間が見ても分かりやすいシートになります。

1つのセルに「佐藤（リーダー）」や「商品A 1,000円」など情報をまとめて入力している

という親切心から、1つのセルに複数の情報を詰め込んでいませんか？

もちろん人間が読めば意味は分かりますが、スプレッドシートの運用においては最もやってはいけないことの一つです。

なぜならスプレッドシートを使って自動計算や分析が一切できなくなるからです。

上記の例の様にデータを詰め込み過ぎると、将来データを集計したくなった時、すべてのデータを一つずつ手作業で切り分けるという、気の遠くなるような修正作業に追われることになります。

本来ならSUM関数一つで終わるはずの売上集計も、文字が混ざっているせいでシートは「0」としか表示してくれません。

結局、数千行あるデータの「円」や「商品名」を消して回るという、もっとも生産性の低い事に時間を捧げることになってしまいます。

【正解】1つのセルには、1つの情報だけ

正しいやり方は、情報の最小単位で列を分けることです。

このように「1セル＝1情報」のルールを徹底すれば、後からデータを集計したくなった時もワンクリックで集計が完了します。

1つの列に「(株)」「株式会社」など、表記が統一されずバラバラで混ざっている

複数人でデータを入力していると、同じ会社名や商品名のはずなのに、微妙に違う書き方が混ざり込んでいませんか？

例えば同じ「トヨタ」という会社を入力しているつもりでも

• (株)トヨタ
• 株式会社トヨタ
• トヨタ自動車

のように、伝えたい事は同じだけど、人によって書き方が違うことは無いでしょうか？

これも先ほどと同様に、データ集計をする際に、うまくデータを集められないというトラブルに発展します。

その結果、正しい集計ができないだけでなく、検索やVLOOKUP関数でデータを探そうとしても、エラーを出す事になってしまいます。

【正解】プルダウンで入力方法を縛る

正しいやり方は、プルダウンを設置してメンバーの自由な書き方で入力させないことです。

これにより以下のメリットがあります。

• 入力者はリストから選ぶだけなので楽になり作業スピードが上がる
• 管理者は100%統一されたデータだけを受け取れる

これにより、誰が集計してもズレのない、完璧な結果が出るようになります。

=A1*1.1 のように、計算式の中に数字を直接埋め込んでいる

消費税や手数料の計算をする際、手っ取り早いからといって、数式の中に直接数字を書き込んでいませんか？

この書き方は、その場しのぎの計算には便利ですが、管理シートにおいては最も危険な書き方です。

なぜなら計算式内の条件が変わった瞬間にシートが崩壊するからです。

例えば、消費税が10%から将来的に変更されたりしたらどうなるでしょうか？

数千行あるデータの中から、この数式が入っているセルをすべて探し出し、一つ一つ手作業で数字を書き換えなければなりません。

もし、1箇所でも修正漏れがあれば、そのシートの合計金額は合わなくなり、「どこが間違っているのか分からない」という迷宮入り状態になります。

【正解】変動する数字は外に出して管理する

正しいやり方は、計算に使う数字を数式の中に埋め込むのではなく、別のセルに入力し、そこを参照することです。

例えば、E1セルに「10%」と入力しておいて、数式は以下のように作ります。

こうすることで万が一修正する時にも、このE2セルを変更するだけで大丈夫です。

なお、この時重要なのが、数式内で $E$2 のように「$」をつけることです。

これをつけておけば、数式を下の行にコピーしても参照先がズレることはありません。

IF関数が何重にも重なっていて、作った本人以外は誰も読めない

条件によって結果を変えたいとき、ついついIF関数を何個も繋げていませんか？

例えば、80点以上なら「A」70点以上なら「B」の様な、テストの点数によってランクを決めるシートを想定します。

このように、IF関数の中にさらにIF関数を入れているなら要注意です。

これが3つ4つと重なると、もう誰も解読不能な呪文になります。

こうなってしまったIF関数は、属人化の極みであり、作った担当者がいないと中身が誰も修正出来ないブラックボックスになります。

例えば、

「カッコを1つ消してしまっただけで数式が壊れ、どこにエラーがあるのか探すのに30分かかる。」

などザラにあります。

また、引き継ぎが出来なくなるので、作成者がチームを抜けた瞬間、みんなパニックになります。

【正解】設定専用のシートを別に作り、VLOOKUP関数で呼び出す

正しいやり方は、数式の中に条件を直接書き込むのではなく、設定専用シートをもう1枚追加し、そこに判定ルールを作ることです。

例えば、「設定シート」という名前のシートを追加して、そこにルールを書きます。

そして、メインのシートからは以下のように呼び出します。

こうすれば、数式がシンプルになるだけでなく、ルール変更は「設定シート」を書き換えるだけで終わります。

※VLOOKUPの「TRUE」を使う際は、設定シートの表を必ず数字の小さい順に並べておくのがポイントです。

また、数式自体をいじる必要がなくなるため、ミスが大幅に削減され、誰でもメンテナンスができるシートになります。

行を追加するたびに、上の行から数式を手動でコピーしている

データが増えるたびに、上のセルの右下にある「■」を下に引っ張って、数式をコピーしていませんか？

これの問題点は、コピー漏れがないかの確認作業に、あなたの貴重な時間が奪われ続けることです。

人間は必ずミスをする生き物です。

数行のデータであれば苦にならないかもしれませんが、数十数百以上のデータの確認作業に時間を取られるのは、さすがに時間の無駄です。

また、仮に計算のルールを変えたい時も、また数千行分をドラッグして上書きし直さなければならないのも手間です。

【正解】ARRAYFORMULA関数で列全体を自動化する

正しいやり方は、「ARRAYFORMULA関数」を使って列全体を1つの数式で管理することです。

ARRAYFORMULA関数を使う事は、以下のメリットがあります。

• 誰が入力しても100%正しいデータが出来上がる
• コピー漏れがないかの確認時間が0になる
• 数式のルール変更がある時もARRAYFORMULA関数を修正するだけ

安心してメンバーに仕事を任せられる様になるのが、ARRAYFORMULA関数の大きなメリットです。

まとめ

ここまで、集計出来ない・入力漏れ・管理出来ないシートの特徴について解説しました。

メンバーのミスが多いシートの原因は、メンバー自身のスキルではなく、シートの設計自体に問題がある場合が多いです。

逆に言えば、シートの設計自体を見直せばチームメンバーのミスも大きく減らし、管理の手間を大きく下げる事が出来ます。

今回紹介した方法を取り入れれば、スプレッドシートはあなたの代わりにミスを監視し、正確な計算をし続けてくれる心強いパートナーになります。

もし今のシートの何が原因でメンバーのミスが減らないか分からないとお悩みでしたら一度ご相談ください。

プロ目線で、今のシートの何が問題点なのかアドバイスさせていただきます。