Skip to content

Excel で管理台帳を作るときの2つの重要なコツ

Featured image of post Excel で台帳を作るときの2つの重要なコツ

 

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

Excel で台帳を作成することはとても多いですが、どうせ作るなら使いやすく、正確で、管理しやすい台帳のほうが良いですよね。使いやすい台帳を作るには、Excel の使い方、台帳の作り方のコツが分かっているかどうかがカギになっています。

Excel 台帳あるある

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

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

前任者の前任者が作った台帳が今もそのまま使われ、設定されている関数の意味やシートの役割などが秘伝のタレのようになっていて、便利に作る変えようにも変えれないということもよくあると思います。

今回は2回に分けて台帳を作るコツと、作成した台帳をもとにテレワークでも使えるようにするにはどうしたらよいかを書いていきたいと思います。

 

第2回の記事はこちらです。

kintone で Excel 台帳をシステム化しよう

 

台帳を作るその前に

秘伝のタレと化した Excel 台帳にいきなり手を入れるのは、当然のことながらなかなかリスクがあります。まずは次のステップでこの Excel 台帳で何を管理しているか、どんな作業をしているかを整理しましょう。

 

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

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

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

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

2. 手順を起こす

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

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

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

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

作業内容

情報の種類

備品の購入

作成

備品の払い出し

変更

備品の返却

変更

備品の廃棄

削除

棚卸し

棚卸し

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

作業手順を起こすコツはこちらの記事も参考にしてみてください。

事務作業を効率化すべき3つの理由と6つのコツ

 

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

この2つを意識して書くだけで手順全体の見通しがよくなります。

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

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

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

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

台帳を作るコツ

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

 

一つのシートですべての作業が完結できそうで、一見これでもいいように見えますが、次のような課題が見えてきます。

 

  1. 長く使い続けると、購入履歴の行だけがどんどん増えて見づらくなる
  2. 在庫を求める関数が何をやっているのかわかりにくい
    • 購入履歴から備品の文字列をカウントして購入数を算出(COUNTIF(B:B,$H3))
    • そこから、払い出しの『返却済み』が空欄(未返却状態)の備品の数を引く(-COUNTIFS(L:L,$H3,N:N,""))
  3. 備品の名前が統一されていない
    • 購入履歴のB10セル『プロジェクター』、在庫のH2セル『プロジェクター』、払い出しのL3セル『プロジェクタ』
  4. 手入力が多いので入力間違いが起こりそう
    • No の欄の連番が正しくない
    • 利用者名や購入元を間違えない?金額を全角で入力すると計算できない、日付は正しい日付になっている?
  5. 現在の在庫数が正しくない
    • 関数で求めているが、備品の名前がきちんと統一されていないので、在庫数の計算が正しくない場合がある
    • プロジェクターの在庫は本来2台。1台貸出中なので現在の在庫台数は正確。だが、返却(払い出しのN5セルに○を記載する)されても1台のままになってしまう

台帳を作るコツとしては次の2つを意識してみましょう。

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

手作業、手入力させない

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

在庫を求める関数を見てみると、購入履歴の中からH4セルの『プロジェクター』という文字列を検索条件にして台数をカウントしています。
そして、払い出しの中で「『プロジェクター』という文字列かつ『返却済み』が空欄の数」を引いた台数が現状の在庫数となります。

ちょっとややこしいですよね。整理するとこのようになります。

  1. 『在庫』H列の備品名『プロジェクター』の台数を管理。
  2. 『在庫』の備品の台数は、「購入数から払い出した台数」を引いた数。
  3. 『購入履歴』B列には『プロジェクター』という文字列は1つ。(『プロジェクター』も加えなければいけないので、本来は購入数が2個)。
  4. 『払い出し』L列には『プロジェクター』という文字列は存在しない(0個)。(『プロジェクタ』ならば1個ある)
  5. そのため、備品『プロジェクター』の在庫は1個

数字上、在庫の数は合っているように見えます。
しかし、本来プロジェクタの購入数は2台で、うち1台が貸出中なので、現在の在庫は1台が正解となります。

『払い出し』には『プロジェクター』の文字列がないので、『返却済み』に○を記入しても『在庫』H列の備品名『プロジェクター』の台数は増えません。

 

手作業が多い台帳の特徴として、こうした集計間違いや正しくない情報が紛れ込みやすくなります。
台帳に1つでも正しくない情報が紛れ込んでいると、その台帳に登録されている情報が本当に正しいのか疑わしくなります。

結果的に、正しい情報に直す手間が発生したり、間違った情報を元に作業を進めてしまうことで手戻りが発生したり、管理している情報によっては取引先に損害を与えることに繋がりかねません。

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

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

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

too_long_list

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

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

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

=row()-x

という関数は、行番号(row())からXの数を引いた値を導き出す関数です。そのため、この台帳では3行目(行番号3)から No1 が始まるので、=row()-2としています。

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

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

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

例えば、しばらくこの台帳を運用していると、在庫の管理部分は管理している備品の製品が増えない限りは行が増えませんが、購入履歴や払い出しの履歴が長くなる一方です。その結果、台帳が使いづらくなることが予想されます。また、各管理項目で列を追加した時に、関数が崩れたりする場合があります。

 

 

また、在庫の数を求める関数も、ひと目では何をしているのか、どんな計算式なのかわかりにくく、修正を加える際にどんな動きをしているのか追いかけながら修正が必要になります。

この関数自体はまだ簡単な方ですが、関数が長くなればなるほど修正が難しくなります。

 

 

そのため、まずは用途ごとにシートを分けることをおすすめします。

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

まとめ

これらのコツを踏まえて改善した台帳がこちらです。
まずはシートを管理項目ごとに分けて、そのシートで何を管理しているのか明確にしました。

after_improvement_ledger

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

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

function_calculate_stock

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

今回作成した台帳のサンプルはこちらにアップロードしています。
自由に変更してご利用いただいても問題ありませんので、ぜひダウンロードしてみてください。


第2回の記事では、作成したこの台帳をもとに、テレワークでも業務ができるように改善を行います。
この台帳が社内のサーバーに保管されている場合、テレワークをしている社員の方は、台帳を使用した業務ができなくなってしまいます。

そこで、クラウドの業務改善ツール kintone を使って、テレワークにも対応した台帳に改善する例をご紹介します。

kintone で Excel 台帳をシステム化しよう