Excel管理台帳の作り方｜見やすく・ミスを防ぐ2つの基本設計のコツ

はじめに結論です。

Excel で管理台帳を作成するときの2つのコツは、

1. 手入力をできる限り減らす
2. 1つのシートに複数の役割を持たせない

細かな Excel のテクニックはありますが、基本的にこの2つを意識して台帳を作ることで、変更も容易で、入力間違いも少ない使い勝手のいい台帳になります。

このブログではクラウドや業務改善についての記事をよく書いているのですが、その中でも特に Excel での台帳管理 について検索した結果、このブログの記事を見ていただいている方が多いようです。

Excel で台帳を作成することはとても多いですが、どうせ作るなら使いやすく、正確で、管理しやすい台帳のほうが良いですよね。

使いやすい台帳を作るには、Excel の使い方、台帳の作り方のコツが分かっているかどうかがカギになっています。

Excel 台帳あるある

次の課題は Excel の台帳あるあるではないでしょうか。

• 入力間違いが含まれている
• 情報が古い
• 関数が複雑で何をしているかわからない
• 台帳に変更や修正、機能追加が簡単にできない

前任者の前任者が作った台帳が、今もそのまま使われていることも多いのではないでしょうか？

台帳で設定されている関数の意味やシートの役割などが秘伝のタレのようになっていて、台帳を修正したり、より便利に改善しようと思っても、変更したときの影響が大きく、改善したくてもできないということはよくあると思います。

台帳を作るその前に

秘伝のタレと化してしまった Excel 台帳にいきなり手を入れるのは、当然のことながらなかなかリスクがあります。

大きな変更をせずとも、列や行を追加したらうまく動かなくなってしまったり、関数を見直そうとしてもどのような値を参照しているのかを把握することが大変だということはよくあります。

まずは次の順で、この Excel 台帳で何を管理しているか、どんな作業をしているかを整理しましょう。

1. 台帳で管理する目的を再確認する

前任者の前任者から引き継いだような長年続いている業務の場合、その業務の目的が曖昧だったり、別の仕組みに置き換わっていることがあります。そもそもなぜこの台帳が必要なのか、なければ何が困るのかを改めて確認してみましょう。

同じような台帳を作っていたり、別のシステムで似たような情報を管理している場合もあるので、自分が担当している業務以外にも目を向けてみるのも良いと思います。

台帳を作り直す以前に、その業務がそもそも今は必要なかった、なんてこともあります。 

2. 作業の手順を起こす

台帳の目的や必要性を再確認できたら、台帳に関する作業の手順を起こします。

1つの台帳を使った作業は思ったよりも多いはずです。例えば備品管理台帳を例にとると、台帳を使用する業務、作業としては次のようなものがあると思います。

• 備品の購入
• 備品の貸出
• 備品の返却
• 備品の廃棄
• 備品の棚卸し

多くの場合、台帳で管理する情報の作成、変更、削除の3種類にまつわる作業に加え、情報の棚卸しの計4種類となる事が多いです。上記のそれぞれの作業の種類を当てはめると次のようになります。

作業内容	情報の種類
備品の購入	作成
備品の払い出し	変更
備品の返却	変更
備品の廃棄	削除
棚卸し	棚卸し

台帳を使う作業を整理したら、それぞれの作業の手順を起こします。

作業の手順を起こすコツは、手順を箇条書きで書くことと、その手順を行うために必要なもの(書類やデータ、日時など、何らかのきっかけ) と、その手順を実施した結果が何か(別の台帳への記入、伝票、報告メールなど) を明確に書くことです。

例えば、購入した備品を台帳に登録する際、その業務を行う前にどのような業務が発生しているかを考えてみます。

• (作業の前提になる業務)備品の納品書を受け取る
• (誰から？)総務部から
• (いつ？)毎月第2月曜日

そして、その作業を行った結果を整理します。

• (購入した結果)倉庫、キャビネットなどに保管する
• (誰が？)備品を購入した部署
• (アウトプット)購入した備品を部署全員に周知する
• (何を使って？)メール

その業務を行う前に必要なことと、その業務を行った結果、の2つを意識して書くだけで手順全体の見通しがよくなります。

また、次のような例外パターンも漏れなく書いてください。

文房具をA社から購入しているが、コピー用紙だけはB社から購入する。その際、FAXで発注する。

台帳の作成そのものには大きく影響はしないかも知れませんが、ここで起こした手順を元に、システム化をする際の重要な情報となります。

なお、例外だらけの作業だとすると、それをできる限り統一してシンプルな手順にできないかを検討してみましょう。

台帳を作るコツ

前置きが長くなりましたが、ここからは、備品管理をしている画像のような台帳を例に解説したいと思います。

この備品管理台帳では、1つのシートに3つの表があり、左から備品の購入履歴、在庫の一覧、払い出し（貸出）を管理する表が並んでいます。

一つのシートを使って備品の管理にまつわる作業が完結できそうで、一見これでもいいように見えますが、次のような課題が見えてきます。

台帳を作るコツとしては、先に上げた通り、次の2つを意識してみましょう。

1. 手作業、手入力させない
2. シートの役割を明確にしてシンプルにする

手作業、手入力させない

1つ目のコツを見てきましょう。

人が入力する以上、入力間違いや漏れはほぼ確実に発生します。不正確な情報が含まれてしまうと、その台帳で管理されている情報の信ぴょう性が疑わしくなってしまいます。

台帳で管理されている情報の裏付けを取ったりしていては、当然その分余計な作業が発生してしまいます。台帳で管理されている情報は正確であることが大前提なのです。

それ以外にも備品の名前や利用者名、ID など入力間違いや、表記のゆらぎなどがあると、集計をした際に正しくない結果になる場合があります。

対策

入力する項目がある程度決まっているようであれば、入力規則 を使いましょう。

入力できる項目をリストの中から選べるようにして、リストにない項目は入力できないようにすることができます。これによって、用語を統一することができますし、入力間違いを防止することができます。

しかし、リストの選択項目が多すぎると視認性が悪くなり、かえって使いづらくなります。個人的な感覚ですが、10個〜20個くらいまでが限界ではないでしょうか。

なお、都道府県を選ぶようなリストの場合は、項目の並びや選択できる項目がある程度わかっているので例外的と言えます。

選択できる項目が多い場合は、次の章でも紹介しますが、シートの役割を明確にしてシンプルにすることが重要になりますので、入力規則の項目をより細分化したり、カテゴリ分けできないか検討してみましょう。

また、ID や No のように、連番をふる場合は、関数を使うことをおすすめします。途中に行を追加したり、削除をしても自動的に連番をふり直してくれます。

=row()-x

という関数は、行番号を求める関数row()からXの数を引いた値を求めています。この台帳では3行目から No.1 が始まるので row() で求められる 3 から 2 を引いて 1 が出力されるようにしています。

連番の先頭や後ろに、文字列を追加することもできます。その場合は="文字列"&row()-xや=row()-x&"文字列"とします。

役割を明確にしてシンプルにする

よくありがちなのが、一つのセルや一つのシートで、多くのことをやりすぎているということです。例で作成した備品管理台帳では、1つのシートで購入履歴、在庫、払い出しの履歴の3つを管理している状態です。

例えば、しばらくこの台帳を運用していると、『在庫』の表は管理している備品の種類が増えない限りは行が増えませんが、購入履歴や払い出しの履歴は長くなる一方です。

その結果、台帳が使いづらくなることが予想されます。また、各管理項目で列を追加した時に、関数が崩れたりする場合があります。

また、在庫の数を求める関数も、ひと目では何をしているのか、どんな計算式なのかわかりにくくなっています。修正を加える際に、どんな動きをしているのか確認しながら修正が必要になります。この関数自体はまだ簡単な方ですが、関数が長くなればなるほど修正が難しくなります。

そのため、まずは用途ごとにシートを分けることをおすすめします。例として、購入履歴と、在庫の管理、払い出しの管理の3つくらいに分けます。それ以外の分け方としては入力用シートと出力用シート、集計用シートといった分け方をする場合もあります。

まとめ

今回紹介したコツを踏まえて改善した備品管理台帳がこちらです。

1つのシートに購入履歴と、在庫の管理、払い出しの表がありましたが、それぞれの表を3つのシートに分けて、何を管理しているのか明確にしました。

そして、入力規則を活用して、製品の種類や取引先などをリストから選べるようにしました。また購入額や購入日など、入力する形式が決まっている項目は数字や日付以外は入力できないようにしました。これによって、計算を行うセルに全角の数字が入力されたり、正しくない日付が入力されてしまうことがなくなりました。

ただ、現在の在庫数を求める関数はあまりシンプルにできなかったのが課題でしょうか。もう少しセルの役割を分割しても良いかもしれません。

入力規則や関数などを使って手入力がほとんど必要なくなったので、自動的に在庫数が変動したり、間違いのない入力ができるようになりました。

今回作成した台帳のサンプルはこちらからダウンロードできます。

自由に変更して利用いただいても問題ありませんので、ぜひダウンロードしてみてください。