在庫管理表の作り方｜エクセルで回す最小の形

在庫管理表は、作り込むほど使われなくなる書類です。列を増やして関数を入れ、誰も更新しなくなって、結局は現物を数えに行く。続く在庫管理表には共通の形があって、それは「列が少ない」ことです。

この記事では、在庫管理表を、最小の列、入出庫との関係、更新のタイミング、表計算での作り方、続かなくなる理由の順に整理します。扱う品目数と人数で最適な形は変わるため、自社の規模に置き換えて読んでください。

在庫管理表は何のための表か

在庫管理表は、いま何がいくつあるかを、現物を見ずに分かるようにする表です。目的はこれだけです。ここがぶれると列が増えます。

在庫管理表があると、次のことができます。

「金額を出す」「原価を管理する」は、別の表の仕事です。在庫管理表に金額の列を足した瞬間に、経理の承認が要る表になり、現場が触れなくなります。

最小の列

まず、この5列だけで始めます。足すのは、無くて困ったときだけにします。

この5列があれば、取りに行けて、数が答えられて、棚卸に使えます。

よく足す列

困ったときに足す候補です。全部を最初から入れないのが要点です。

最終出庫日は、費用をかけずに効きます。この列があるだけで、半年動いていない品目を並べ替えで見つけられます。

入出庫の記録と分ける

在庫管理表と入出庫の記録は、別の表にします。1枚にまとめると、行が増え続けて重くなります。

在庫数は、入出庫の記録から計算で出すか、動くたびに書き換えるかのどちらかです。品目数が少なければ書き換えで足り、多ければ記録から集計する形になります。入出庫の記録の付け方は入出庫管理のやり方にまとめています。

どちらにするかの目安

直接書き換える形は、2人が同時に開くと片方の入力が消えます。人数が増えたら、記録を足していく形に切り替えます。

いつ更新するか

その日のうちに入れる。これが守れるかどうかで、表の値打ちが決まります。

4つ目が抜けやすいところです。割れた、濡れた、期限が切れた。その場で捨ててしまうと、帳簿だけが残ります。「捨てる前に1行書く」を作業に入れると、原因不明の差が減ります。

翌日に回すと合わなくなる

記録が1日遅れると、朝の時点の数が現物と違う状態になります。受注の返事も、棚卸の突き合わせも、そこからずれます。入力の時間を1日の終わりに固定して、10分取るだけで変わります。

表計算での作り方

特別な関数は要りません。並べ替えとフィルタが効く形にしておくのが要点です。

発注点を下回った行に色を付けるのがいちばん効きます。数字を見比べる作業が消えて、色の付いた行だけ見れば済みます。

セルを結合しない

見た目をそろえるためにセルを結合すると、並べ替えもフィルタも効かなくなります。表計算の表は、1行1件、1セル1値にしておきます。印刷のときだけ体裁を整えます。

続かなくなる理由

途中で更新が止まる表には、理由があります。

「現物と合わないので誰も見ない」が、いちばん多い終わり方です。一度ずれると、確かめるために現物を見に行くようになり、表を見る理由が無くなります。合わせ直す方法は循環棚卸のやり方にまとめています。

システムに移すかどうか

表計算で回らなくなる境目は、品目数ではなく、同時に触る人数で来ます。

表計算を悪者にしないのが要点です。品目が少なく、触る人が限られているなら、表計算のほうが速く、費用もかかりません。切り替えるのは、上の表に当てはまってからで間に合います。

現場が見る形にする

事務所の表計算だけにあると、現場は結局現物を見に行きます。現場が見られる形を1つ用意します。

棚に線を引くやり方がいちばん強いです。「ここまで減ったら知らせる」の位置に札を挟んでおけば、表を開かずに発注の合図が出せます。安全在庫と発注点の出し方は安全在庫の計算にまとめています。

品目を絞ってから始める

いまゼロから作るなら、全品目をそろえてから始めようとしないことです。

1. 動きの多い品目を20だけ選ぶ
2. 5列で表を作る
3. 1か月、その20品目だけ運用する
4. 困ったところだけ列を足す
5. 品目を広げる

3の1か月が判断の材料になります。更新が続いたなら形が合っています。止まったなら、列か入力の場所に無理があります。全品目で始めると、どこに無理があったのかが分からないまま止まります。

品番の付け方でつまずかない

在庫管理表が使われなくなる原因のうち、意外に多いのが品番です。仕入先の品番をそのまま使っている品目と、社内で付けた品番の品目が混ざっていると、同じ物が2行に分かれます。片方に在庫があるのに、もう片方を見て「在庫なし」と答えてしまう形が起きます。

社内で1つの品番に決めて、仕入先の品番は別の列に持つのが扱いやすい形です。仕入先が変わっても社内の品番は変わらないので、過去の記録がつながります。現品の表示、棚のラベル、在庫管理表の3か所で同じ書き方にそろえるところまでが1組です。ハイフンの有無や英字の大小が混ざるだけで、照合のときに人の目に頼ることになります。

品番に意味を持たせすぎるのも、あとで効いてきます。仕入先や置き場を品番に埋め込むと、仕入先が変わったときに品番を変えることになり、過去の記録と切れてしまいます。意味は列で持ち、品番は変わらない番号にしておくほうが長く使えます。棚番の付け方はロケーション管理のやり方にまとめています。

数が合わなくなったときに戻る場所

在庫管理表の数が現物と合わなくなったら、表を直す前に、どこでずれたのかを見ます。入荷の計上漏れ、出荷の記録漏れ、棚を移したときの書き換え漏れ、破損や廃棄の記録漏れ。この4つで大半が説明できます。順番に見ていって見つからなければ、原因不明として記録に残したうえで、現物に合わせます。

大事なのは、合わせた事実と、そのとき分かっていたことを残しておくことです。合わせるだけで何も残さないと、翌月に同じ品目でまたずれたときに、前回と比べられません。品目と棚番と差の向きだけでも1行残しておけば、偏りが見えてきます。調べ方は棚卸差異の原因と対策にまとめています。

現物合わせを繰り返す品目は、置き方を疑う

毎月のように合わなくなる品目があるなら、その品目だけ運用に無理があります。同じ品番が2か所の棚に分かれている、ケースとバラが混ざっている、期限で抜き取りが起きている、といった形です。表の使い方ではなく、置き方を直すほうが早く収まります。

受注のときに即答できる状態をつくる

在庫管理表がいちばん役に立つのは、営業や受注の担当から「これ、いくつ出せますか」と聞かれたときです。ここで即答できないと、折り返しの電話が増え、その間に他社に決まってしまうこともあります。数字の精度より、その場で答えられることのほうが商売には効きます。

即答するために要るのは、在庫数そのものではなく、引き当ての考え方です。棚にある100個のうち、すでに受注が入っていて出ていくぶんが40個あるなら、いま新しく約束できるのは60個になります。この「約束できる数」を持っていないと、在庫はあるのに二重に約束してしまう形が起きます。品目が少ないうちは、受注済みの数を1列足すだけで足ります。

さらに、入荷の予定が分かっていれば、いま無くても「来週なら出せます」と答えられます。発注して入ってくる数と入荷の予定日を持っておくと、答えられる幅が広がります。発注したものが届いていない状態の管理は、発注残の管理にまとめています。

棚卸の前に表を整える

年に1回の棚卸の直前は、在庫管理表を見直すのに向いた時期です。使っていない品目の行、もう扱わなくなった品番、名前だけ残って在庫がゼロのまま何年も動いていない行。こうしたものが溜まっていると、数える対象が増え、棚卸そのものが重くなります。

棚卸の1か月前に、最終出庫日で並べ替えて、1年以上動いていない品目を一覧にします。処分するか、残すか、別の場所へ移すかを決めておけば、当日に数える行数が減ります。表を軽くすることが、そのまま当日の人数と時間の削減になります。棚卸の進め方は棚卸のやり方にまとめています。

よくある質問

在庫管理表と棚卸表は同じものですか

別のものです。在庫管理表は日々の数を持つ表、棚卸表は数えるための用紙です。ただし、在庫管理表を棚番の順に並べ替えれば、そのまま棚卸表として印刷できます。同じデータから2つの用途を出す形にしておくと、二重に管理せずに済みます。

在庫数がマイナスになります

出庫の記録が入荷より先に入っているか、入荷の計上が漏れています。マイナスが出た時点で止めて、その品目の直近の記録をたどります。マイナスのまま放置すると、その品目の数はもう信用できません。入力の順番を「入荷を先に入れる」と決めておくと減ります。

複数の倉庫がある場合はどう持ちますか

倉庫ごとに行を分けるのが扱いやすい形です。同じ品番が2行になりますが、どこに何があるかが分かります。合計だけを見たいときは、集計で足せます。1行にまとめて合計だけを持つと、取りに行く場所が分からなくなります。

単位が混ざる品目はどう書きますか

ケースとバラを別の列に分けます。「12入りケース3箱＋バラ5個」を1つの数字にまとめると、数えるときに戻せません。入数の列を作って、合計は計算で出す形にします。

履歴はどのくらい残しますか

入出庫の記録は、棚卸の差異を調べるときに1年前まで見ることがあります。月ごとにシートを分けて1年分を1ファイルにし、年が変わったら別ファイルにする形がよく使われます。在庫管理表そのものは最新だけで足ります。

在庫管理表を紙で運用していますが、変えるべきですか

品目が少なく、動きも1日数件なら、紙のままでも実務は回ります。変えるかどうかは、探す時間と、聞かれてから答えるまでの時間で決めます。紙を見ながら電話で即答できているなら、無理に変える必要はありません。並べ替えや絞り込みをしたくなった時点が、表計算へ移る合図になります。

表計算のファイルが重くなってきました

入出庫の履歴を同じファイルに溜めていることがほとんどです。履歴は年ごとに別ファイルへ移し、在庫管理表には最新の数だけを残します。画像やセルの結合、使っていない範囲の書式も重さの原因になります。行を足すたびに重くなる場合は、テーブルの範囲が余分に広がっていないかを見ます。

誰が更新しますか

1人決めます。全員が触る形にすると、書き方が揃わず、上書きの事故も起きます。入力する人を決めて、現場からは紙かメモで渡す形にすると、精度が保てます。人数が増えたら、記録を足す形に変えます。

まとめ

在庫管理表は、いま何がいくつあるかを現物を見ずに分かるようにする表です。金額や原価は別の表の仕事なので、ここには入れません。

始めるときは、品番・品名・棚番・在庫数・単位の5列だけにします。足すのは、無くて困ったときだけです。最終出庫日の列は費用をかけずに効くので、早めに足す候補になります。

そして、その日のうちに入れることです。1日遅れると現物とずれ、ずれた表は見られなくなります。動きの多い20品目から始めて、1か月続くかを見てから広げます。