はじめに結論です。
Excel で管理台帳を作成するときの2つのコツは、
- 手入力をできる限り減らす
- 1つのシートに複数の役割を持たせない
細かな Excel のテクニックはありますが、基本的にこの2つを意識して台帳を作ることで、変更も容易で、入力間違いも少ない使い勝手のいい台帳になります。
このブログではクラウドや業務改善についての記事をよく書いているのですが、その中でも特に Excel での台帳管理 について検索した結果、このブログの記事を見ていただいている方が多いようです。
Excel で台帳を作成することはとても多いですが、どうせ作るなら使いやすく、正確で、管理しやすい台帳のほうが良いですよね。
使いやすい台帳を作るには、Excel の使い方、台帳の作り方のコツが分かっているかどうかがカギになっています。
Excel 台帳あるある
次の課題は Excel の台帳あるあるではないでしょうか。
- 入力間違いが含まれている
- 情報が古い
- 関数が複雑で何をしているかわからない
- 関数が複雑なので、変更や修正、機能追加が簡単にできない
前任者の前任者が作った台帳が今もそのまま使われていることも多いのではないでしょうか？
台帳で設定されている関数の意味やシートの役割などが秘伝のタレのようになっていて、台帳を修正したり、より便利に改善しようと思っても、変更したときの影響が大きく、改善したくてもできないということはよくあると思います。
今回は2回に分けて台帳を作るコツと、作成した台帳をもとにテレワークでも使えるようにするにはどうしたらよいかを書いていきたいと思います。
第2回の記事はこちらです。
kintone で Excel 台帳をシステム化しよう
台帳を作るその前に
秘伝のタレと化してしまった Excel 台帳にいきなり手を入れるのは、当然のことながらなかなかリスクがあります。
大きな変更をせずとも、列や行を追加したらうまく動かなくなってしまったり、関数を見直そうとしてもどのような値を参照しているのかを把握することが大変だということはよくあります。
まずは次のステップでこの Excel 台帳で何を管理しているか、どんな作業をしているかを整理しましょう。
1. 台帳で管理する目的を再確認する
前任者の前任者から引き継いだような長年続いている業務の場合、その業務の目的が曖昧だったり、別の仕組みに置き換わっていることがあります。そもそもなぜこの台帳が必要なのか、なければ何が困るのかを改めて確認してみましょう。
同じような台帳を作っていたり、別のシステムで似たような情報を管理している場合もあるので、自分が担当している業務以外にも目を向けてみるのも良いと思います。
台帳を作り直す以前に、その業務がそもそも今は必要なかった、なんてこともあります。
2. 作業の手順を起こす
台帳の目的や必要性を再確認できたら、台帳に関する作業の手順を起こします。
1つの台帳を使った作業は思ったよりも多いはずです。例えば備品管理台帳を例にとると、台帳を使用する業務、作業としては次のようなものがあると思います。
- 備品の購入
- 備品の貸出
- 備品の返却
- 備品の廃棄
- 備品の棚卸し
多くの場合、台帳で管理する情報の作成、変更、削除の3種類にまつわる作業に加え、情報の棚卸しの計4種類となる事が多いです。上記のそれぞれの作業の種類を当てはめると次のようになります。
| 作業内容 |
情報の種類 |
| 備品の購入 |
作成 |
|
備品の払い出し |
変更 |
|
備品の返却 |
変更 |
|
備品の廃棄 |
削除 |
|
棚卸し |
棚卸し |
台帳を使う作業を整理したら、それぞれの作業の手順を起こします。
作業手順を起こすコツはこちらの記事も参考にしてみてください。
作業の手順を起こすコツは、手順を箇条書きで書くことと、その手順を行うために必要なもの(書類やデータ、日時など、何らかのきっかけ) と、その手順を実施した結果が何か(別の台帳への記入、伝票、報告メールなど) を明確に書くことです。
例えば、購入した備品を台帳に登録する際、その業務を行う前にどのような業務が発生しているかを考えてみます。
- (作業の前提になる業務)備品の納品書を受け取る
- (誰から？)総務部から
- (いつ？)毎月第2月曜日
そして、その作業を行った結果を整理します。
- (購入した結果)倉庫、キャビネットなどに保管する
- (誰が？)備品を購入した部署
- (アウトプット)購入した備品を部署全員に周知する
- (何で？)メール
また、次のような例外パターンも漏れなく書いてください。
文房具をA社から購入しているが、コピー用紙だけはB社から購入する。その際、FAXで発注する。
台帳の作成そのものには大きく影響はしないかも知れませんが、ここで起こした手順を元に、システム化(第2回で書きます)をする際の重要な情報となります。
なお、例外だらけの作業だとすると、それをできる限り統一してシンプルな手順にできないかを検討してみましょう。
台帳を作るコツ
前置きが長くなりましたが、ここからは、備品管理をしている台帳を例に解説したいと思います。
一つのシートですべての作業が完結できそうで、一見これでもいいように見えますが、次のような課題が見えてきます。
- 長く使い続けると、購入履歴(一番左の表)の行だけが増えて見づらくなる
- 在庫を求める関数が何をやっているのかわかりにくい
- 購入履歴から備品の文字列をカウントして購入数を算出(COUNTIF(B:B,$H3))
- そこから、払い出しの『返却済み』が空欄(未返却状態)の備品の数を引く(-COUNTIFS(L:L,$H3,N:N,""))
- 備品の名前が統一されていない
- 購入履歴のB10セル『ﾌﾟﾛｼﾞｪｸﾀー』、在庫のH2セル『プロジェクター』、払い出しのL3セル『プロジェクタ』
- 手入力が多いので入力間違いが起こりそう
- No の欄の連番が正しくない
- 利用者名や購入元を間違えていないか？
- 金額を全角入力していないか？全角で数値を入力すると計算できない。
- 日付は正しい日付になっているか？
- 現在の在庫数が正しくない
- 関数で求めているが、備品の名前がきちんと統一されていないので、在庫の数をカウントする計算が正しくない
台帳を作るコツとしては、先に上げた通り、次の2つを意識してみましょう。
- 手作業、手入力させない
- シートの役割を明確にしてシンプルにする
手作業、手入力させない
1つ目のコツを見てきましょう。
人が入力する以上、入力間違いや漏れはほぼ確実に発生します。不確かな情報が含まれてしまうと、その台帳で管理されている情報の信ぴょう性が疑わしくなってしまいます。
台帳で管理されている情報の裏付けを取ったりしていては、当然その分余計な作業が発生してしまいます。台帳で管理されている情報は正確であることが大前提なのです。
それ以外にも備品の名前や利用者名、ID など入力間違いや、表記のゆらぎなどがあると、集計をした際に正しくない結果になる場合があります。
表記の揺らぎによる影響
表記のゆらぎが与える影響について見てみましょう。
在庫を求める関数を見てみると、購入履歴の表中からH4セルの『プロジェクター』という文字列を検索条件にして台数をカウントしています。
そして、払い出しの表中で「『プロジェクター』という文字列かつ『返却済み』が空欄の数」を引いた台数が現状の在庫数となります。
ちょっとややこしいですよね。整理するとこのようになります。
- 『在庫』の表で、備品ごとの在庫の数を管理している。
- 『プロジェクター』の現在の在庫数は1台になっている。
- 『在庫』の表で備品の台数は次のように求めている。
- 『在庫』の表の備品名(この場合、「プロジェクター」)で、購入履歴を検索しヒットした件数を求める。
- 『払い出し』の表から、『在庫』の表の備品名かつ『返却済み』に「◯」がついていない件数を求める。
- A で求めた件数から、Bで求めた件数を引く。
- 『購入履歴』の表B列には『プロジェクター』という文字列は1つ。(『ﾌﾟﾛｼﾞｪｸﾀｰ』も加えなければいけないので、本来は購入数が2個にならなければならない)。
- 『払い出し』L列には『プロジェクター』という文字列は存在しない(0個)。(『プロジェクタ』ならば1個ある)
- そのため、備品『プロジェクター』の在庫は1個
数字上、在庫の数は合っているように見えます。
しかし、本来『プロジェクター』の購入数は2台とならなければなりませんが、『ﾌﾟﾛｼﾞｪｸﾀｰ』と『プロジェクター』は関数の処理上、別物として扱われてしまうため、『プロジェクター』で検索した結果は1件となります。
『払い出し』の表で、『返却済み』に◯がつくと該当する備品の在庫数が1つ増えます。貸し出した備品が在庫に戻ってくることを意味しています。この『払い出し』の表では『プロジェクター』ではなく『プロジェクタ』として登録されています。
そのため、『返却済み』に○を記入しても『在庫』の表で記載されている『プロジェクター』としてはカウントされないため、台数は増えません。
在庫が2台のプロジェクターを1台払い出し、その1台が返却されたとしても『在庫』の表の『プロジェクター』は1台のままになってしまいます。
手入力が多い台帳では、こうした集計間違いや正しくない情報が紛れ込みやすくなります。
台帳に1つでも正しくない情報が紛れ込んでいると、その台帳に登録されている他の情報が本当に正しいのか疑わしくなります。
結果的に、正しい情報に直す手間(台帳の修正だけでなく、情報の再確認が必要)が増えたり、間違った情報を元に作業を進めてしまうことで手戻りが発生したり、管理している情報によっては取引先に損害を与えることに繋がりかねません。
対策
入力する項目がある程度決まっているようであれば、入力規則 を使いましょう。
入力できる項目をリストの中から選べるようにして、リストにない項目は入力できないようにすることができます。
これによって、用語を統一することができますし、入力間違いを防止することができます。
しかし、リストの選択項目が多すぎると視認性が悪くなり、かえって使いづらくなります。個人的な感覚ですが、10個〜20個くらいまでが限界ではないでしょうか。
※都道府県を選ぶようなリストの場合は、ある程度並びや選択できる項目がわかっているので例外的と言えます。
その場合は、次の章でも紹介しますが、役割を明確にしてシンプルにすることが重要になりますので、入力規則の項目をより細分化したり、カテゴリ分けできないか検討してみましょう。
また、ID や No のように、連番をふる場合は、関数を使うことをおすすめします。
途中に行を追加しても自動的に連番をふり直してくれますし、番号のふり間違いもなくなります。
=row()-x
という関数は、行番号(row())からXの数を引いた値を導き出す関数です。
この台帳では3行目(行番号3)から No1 が始まるので、=row()-2としています。
連番の先頭や後ろに、文字列を追加することもできます。その場合は="文字列"&ROW()-Xや=ROW()-X&"文字列"とします。
役割を明確にしてシンプルにする
よくありがちなのが、一つのセルや一つのシートで、多くのことをやりすぎているということです。
例で作成した備品の管理台帳では、1つのシートで購入履歴、在庫、払い出しの履歴の3つを管理している状態です。
例えば、しばらくこの台帳を運用していると、『在庫』の表は管理している備品の種類が増えない限りは行が増えません。
しかし、購入履歴や払い出しの履歴は長くなる一方です。
その結果、台帳が使いづらくなることが予想されます。また、各管理項目で列を追加した時に、関数が崩れたりする場合があります。
また、在庫の数を求める関数も、ひと目では何をしているのか、どんな計算式なのかわかりにくくなっています。
修正を加える際にどんな動きをしているのか追いかけながら修正が必要になります。この関数自体はまだ簡単な方ですが、関数が長くなればなるほど修正が難しくなります。
そのため、まずは用途ごとにシートを分けることをおすすめします。
例として、購入履歴と、在庫の管理、払い出しの管理の3つくらいに分けます。
それ以外の分け方としては入力用シートと出力用シート、集計用シートといった分け方をする場合もあります。
まとめ
これらのコツを踏まえて改善した台帳がこちらです。
まずはシートを購入履歴と、在庫の管理、払い出しの管理の3つに分けて、そのシートで何を管理しているのか明確にしました。
そして、入力規則を活用して、製品の種類や取引先などをリストから選べるようにしました。また購入額や購入日など、入力する形式が決まっている項目は数字や日付以外は入力できないようにしました。
これによって、計算を行うセルに全角の数字が入力されたり、正しくない日付が入力されてしまうことがなくなります。
ただ、現在の在庫数を求める関数はあまりシンプルにできなかったのが課題でしょうか。もう少しセルの役割を分割しても良いかもしれません。
入力規則や関数などを使って手入力がほとんど必要なくなったので、自動的に在庫数が変動したり、間違いのない入力ができるようになりました。
今回作成した台帳のサンプルはこちらにアップロードしています。
自由に変更してご利用いただいても問題ありませんので、ぜひダウンロードしてみてください。
第2回の記事では、作成したこの台帳をもとにテレワークでも業務ができるように改善を行います。
例えば、この台帳が社内のサーバーやPCに保管されている場合、テレワークをしている社員の方は、台帳を使用した業務ができなくなってしまいます。
そこで、クラウドの業務改善ツール kintone を使って、テレワークにも対応した台帳に改善する例をご紹介します。
kintone で Excel 台帳をシステム化しよう
Linkup では業務改善の伴走型の支援も行っています。業務の流れの整理から始まり、ｋintone を使った業務改善、システム化まで対応しています。
詳細についてはこちらよりお問い合わせください。
