Excelの在庫表が現物と合わなくなる理由｜ずれの出どころを分けると、直す順番が決まる

Excelの在庫表が棚の数と合わなくなる原因は、品目や行の多さそのものではありません。在庫が動いてから表に書き込まれるまでのどこかで、記録が遅れたり、抜けたり、二重になったりしていて、それが積み重なると表の数（帳簿在庫）と棚にある数（実在庫）がずれていきます。

ずれの出どころは、出荷してから入力するまでの時間差、注文を受けた分と出荷した分の混同、返品・不良・サンプルの戻り方、ケースと個の換算、棚卸しで合わなかった分の上書き、に分けて調べられます。換算と上書きは、表の持ち方を変えればExcelのままで直せます。返品・不良・サンプルも、倉庫の中で記録が完結するなら表で直せます。時間差と引当のずれは、出荷の記録が紙で後から回ってくる、注文の入口や受ける人が複数ある、といった会社では、表を工夫しても残ります。こちらは受注・出荷の記録と在庫をつながないと直りません。

直すときは、棚卸しの差を上書きせずに残すことから始め、Excelで直せるものを片付けてから、次の棚卸しで残った差の出どころを見ます。品目数や担当者数で「ここを超えたらシステム化」と線を引く目安も見かけますが、私たちが確かめた範囲では根拠が示されたものがなかったため、この記事では線を引かず、ずれ方で決めます。受注から在庫・出荷までをひとつながりで扱う仕組みの全体像は、受発注・在庫管理システムの開発にまとめています。

限界は行数ではなく、ずれ方で分かる

Microsoft 365 版や Excel 2016 以降のワークシートは、1枚に1,048,576行、16,384列まで持てます（Microsoft「Excel の仕様と制限」）。品目の一覧がこの上限に届くことは、まずありません。在庫表で困るのは容量ではなく、表の数と棚の数が合わなくなり、合わせ直せるのが担当者一人になることです。

ずれが出た品目をいくつか選び、差の向き（表のほうが多いか少ないか）、入力待ちの伝票を入れると消えるか、差がどんな数か、前回の棚卸しでも同じ品目がずれていたか、を書き出します。ずれ方によって、疑う出どころが絞れます。

1つの品目に複数の出どころが重なっていることもあります。入力待ちの伝票や換算のように確かめやすいものから外していくと、残った差が何から来ているかが見えてきます。

出荷してから入力するまでの時間差

商品が棚を出た時刻と、Excelの数が減る時刻は、出荷の控えを後から打つ流れでは一致しません。控えを夕方にまとめて打つ、倉庫の伝票が事務所に回ってきてから打つ、という場合、その間の表の数は棚より多いままです。入荷はその逆で、届いた商品を検品している間や伝票が回ってくるまでは、棚にあるのに表に載っていない数が生まれます。

このずれは、伝票を入力し終えれば消えます。棚卸しで差が出た品目について、その時点で入力されていなかった伝票を集めて入れてみて、差が消えれば時間差です。消えなければ、ほかの出どころを疑います。

あとで消えるずれでも、害がないわけではありません。入力待ちの間に営業が表を見て「在庫あります」と答えると、すでに出た商品をもう一度約束することになります。

Excelのまま縮めるには、次のことを決めます。

• 記録する人と時刻：出荷した人がその場で入れるか、少なくとも出荷のまとまりごとに入れる。表の上に「最後に反映した時刻」を書いておくと、見る人が数の新しさを判断できる。
• 棚卸しの締め時刻：数え始める前に、締め時刻より前の伝票をすべて入れ終える。数えている間に動く商品は止めるか、「締め後」として別に記録する。入荷して検品待ちの商品は置き場を分け、数える対象に入れるかを先に決めておく。

それでも、出荷する人が記録できず、紙の伝票を事務所で打ち直す流れが変えられないなら、時間差は残ります。縮めるには、出荷の作業の中で記録が残る形（棚の前で品番を読み取るなど）に変える必要があり、ここから先は表の工夫の外になります。

注文を受けた分と、実際に出た分が同じ列にある

「在庫数」の列が一つしかないと、その列に二つの意味が混ざります。棚にある数と、まだ売ってよい数です。営業は売りすぎを防ぐために注文を受けた時点で数を減らし、倉庫は出荷した時点でまた減らす。こうして同じ注文が二回引かれます。反対に、受けた時点で減らしたあと、取り消しや一部だけの出荷を戻し忘れることもあります。どちらも、一つの列を「棚の数」と「売ってよい数」の両方に使っていることから起きます。

このずれは表のほうが少なく出て、入力待ちの伝票を入れても消えません。差が出た品目で、受けてまだ出していない注文と、取り消した注文の数量を足してみて、差に近ければこの出どころです。足しても差が埋まらず、出荷が進むほど差が広がっているなら、同じ注文を受けた時と出した時の二回引いています。

直すには、数を三つに分けます。

• 実在庫：棚にある数。入荷・出荷・返品・廃棄・棚卸しの調整など、物が動いた記録を足し引きして出し、注文では動かさない。
• 引当：受けたがまだ出していない注文の数量。
• 使える数（有効在庫）：実在庫から引当を引いた数。注文を受ける人と、在庫の問い合わせに答える人はこれを見る。

Excelでは、注文を1行ずつ書く受注のシートを作り、状態（未出荷・出荷済み・取消）の列を持たせます。引当は、受注のシートから品番ごとに「未出荷」の数量を合計すれば出せます。複数の条件に合う行を合計するSUMIFS 関数を使います。受注のシートのB列が品番、D列が数量、E列が状態で、在庫表のA列に品番がある場合、在庫表の引当の列（2行目）には次のように書きます。

=SUMIFS(受注!D:D,受注!B:B,A2,受注!E:E,"未出荷")

実在庫は入出庫の記録から、引当は受注のシートから計算で出し、使える数はその差で出します。3つとも、誰も手で打たないようにします。kintoneで組む場合の持ち方は、kintoneで在庫管理・受注管理を組む記事の「実在庫・引当・有効在庫」の節で紹介しています。

この形が回るのは、受けた注文がすぐに受注のシートに載る場合です。FAXの注文は事務、電話は営業、Webの注文はまた別の人が受け、それぞれがメモや自分の表で持っていると、受注のシートへの記入が遅れ、引当はいつも実際より少なく出ます。また、同じ表を複数人で同時に開けても、最後の数個を二人がほぼ同時に引き当てれば、両方の注文が入ってしまうことがあります。ここが、Excelのままでは直らない部分です。

返品・不良・サンプルが戻ってくる経路

在庫は、仕入れて売る以外にも動きます。取引先からの返品、検品や保管中に見つかる不良、営業が持ち出すサンプルや貸出品、社内での使用や廃棄です。こうした動きは出荷や入荷の伝票を通らないことが多く、表の外で起きます。

ずれの向きは経路によって違います。

• 返品を記録せずに棚へ戻すと、棚のほうが多くなる。
• 不良品を別の場所によけたまま表から外さないと、表のほうが多くなる。
• サンプルを記録せずに持ち出すと表のほうが多くなり、戻ってくると差が消える。
• 傷んだ返品を良品として戻すと、数は合っていても、売れない物が在庫に混ざる。

棚卸しの日に、良品の棚だけでなく、返品置き場、検品待ちの棚、営業の手元にある物まで数え、それぞれが表に載っているかを見ると、この出どころかどうかが分かります。

Excelで直すなら、入出庫の記録を1行1件で残し、「区分」の列（入荷・出荷・返品受入・不良・サンプル持出・サンプル戻り・廃棄・棚卸調整）をデータの入力規則のドロップダウンから選ばせます。あわせて「状態」（良品・検品待ち・不良）を持たせ、使える数には良品だけを数えます。返品はいったん検品待ちで受け、誰がいつ良品か不良かを決めるかを先に決めておきます。

入力規則が止めるのは、セルに直接打ち込んだ値だけです。ほかの表からコピーして貼り付けた値や、オートフィルで入れた値にはメッセージが出ません（Microsoft「データの入力規則に関する詳細」）。伝票の一覧を貼り付けて取り込んでいるなら、締めのたびに区分の列を並べ替え、決めた区分以外の値が混ざっていないかを確かめます。

Excelで直しきれないのは、返品の受付と現物の受け入れが別々に動いている場合です。取引先とのやりとりや返金・赤伝の処理を営業や経理が、現物の受け入れを倉庫が別の表で持っていると、どの注文の返品かが結びつかず、片方にしか載っていない返品が残ります。返品を元の注文に結びつけて扱うには、受注の記録とつなぐ必要があります。

ケースと個、箱と本の換算

仕入れはケース、出荷は個という品目では、数量の列に単位が混ざりやすくなります。ケースで入荷したのに個の列にケースの数を入れる、ケースで数える列に個数を入れる、といった入力です。

たとえば入数（1ケースあたりの個数）が24の品目で、2ケース（48個）の入荷を個の列に「2」と入れると、表は棚より46個少なくなります。差が入数や、入数から1を引いた数の倍数になっていたら、換算を疑います。

見落としやすいのは、入数そのものが変わる場合です。仕入先が箱を変えて入数が24から20になったとき、品目マスタの入数を書き換えると、マスタを参照して個数に直していた過去の行まで、新しい入数で計算し直されます。誰も触っていない過去の在庫が変わってしまいます。

直し方は次のとおりです。

• 在庫は最小の単位（個・本など）だけで持ち、ケースは入力のときの単位として扱う。
• 入力では数量と単位を分けて入れ、品目マスタの入数で最小の単位に直す。
• 直した数量は式のままにせず値で残すか、その行を入れた時点の入数も同じ行に残す。
• 入数が変わったら、マスタを上書きせず、適用を始めた日を付けて行を足すか、品番を分ける。

換算のずれは、ここまでの手当てでExcelの中だけで直せます。

棚卸しで合わなかった分を上書きしていないか

棚卸しで表と棚の数が違ったとき、表の在庫数を数えた数に書き換えて終わりにしていないでしょうか。書き換えた時点で、どの品目がいくつ違っていたかという記録が消えます。次の棚卸しで同じ品目がまたずれても、前回と同じ理由なのか、前より悪くなったのかが分かりません。

書き換えには、もう一つ問題があります。数え終わってから書き換えるまでの間に動いた分が、書き換えによって消えたり、二重に引かれたりします。締め時刻を決めずに数えると、ここでも差が生まれます。

Excelにも変更をたどる機能はあります。Microsoft 365 の Excel の［変更の表示］では、誰が、どのセルを、いつ、何から何に変えたかを確かめられ、過去の変更は最大365日分さかのぼれます（Microsoft「ブックで行った変更を表示する」）。ただし、買い切り版や古いバージョンのExcelで編集した分は記録されず、変更の一覧が消えることもあります（Microsoft「Excel で変更を表示してヘルプを取得する」）。それに、残るのは書き換えたという事実で、差の理由は残りません。

直すには、表の在庫数を「入出庫の記録を足し引きした結果」だけにして、棚卸しの差は「棚卸調整」の行として記録に足します。行には日付、品目、表の数、数えた数、差、分かった理由（分からなければ「不明」）を残します。在庫数の列は式で出し、セルをロックしてシートを保護しておけば、うっかり数を打ち込むことを防げます（Microsoft「ワークシートを保護する」）。シートの保護は誤って書き換えるのを防ぐためのもので、セキュリティの機能ではない点には注意します。

調整の行がたまると、毎回ずれる品目や、決まった時期にずれる品目が見えてきます。そこから時間差・引当・返品・換算のどれに当たるかを、品目ごとにさかのぼります。

Excelのまま直せるもの、受注とつながないと直らないもの

棚卸しや出荷の入力を、スマホのカメラ読み取りで足りるか、専用のハンディターミナルが要るかは「ハンディターミナルを買う前に」で解説しています。

ここまでの出どころを、Excelのままでできることと、それでも残る条件に分けると次のようになります。

複数人で同じ表を更新しているとき

担当者ごとにコピーを持っていたり、メールで回した表を後から一つにまとめたりしていると、保存の順番しだいで誰かの入力が消えます。まずはファイルを一つにします。Microsoft 365 の Excel なら、OneDrive・OneDrive for Business・SharePoint Online に置いたブックを複数人で同時に編集でき、ほかの人の変更は数秒で画面に出ます（Microsoft「Excel ブックの共同編集を使用して同時に共同作業を行う」）。以前からある「共有ブック」は制限が多く、この共同編集に置き換えられています（Microsoft「共有ブック機能について」）。

ただし、共同編集で片付くのは「どれが最新の表か」という問題までです。使える数を超えて引き当てない、出荷したら引当を外す、といった決まりを、表が守らせてくれるわけではありません。マクロで入力を楽にしても同じで、物が動いた時刻と記録する時刻の差や、注文を受ける場所が複数あることは変わりません。作った人しか直せないマクロが増えれば、表を直せる人はさらに限られます。

直す順番と、つなぐ時期

手を付ける順番は次のとおりです。

1. 棚卸しの差を上書きせず、調整の行で残す。これがないと、ほかの手当てで差が減ったかを確かめられない。
2. 数量を最小の単位にそろえ、入数を品目マスタに持つ。
3. 返品・不良・サンプルに区分と状態を付け、検品待ちの置き場を決める。
4. 物が動いた時点で記録する人と、棚卸しの締め時刻を決める。
5. 実在庫・引当・使える数を分け、引当は受注のシートから出す。

1と2は表の中だけで済みます。3から5は倉庫や営業の動き方も変えるので、関わる人が少ないものから順に進めます。ここまで整えて次の棚卸しを迎え、調整の行に残った差を出どころ別に見ます。残った差の多くが、注文の入口が複数あることによる引当の遅れや、出荷の記録の遅れから来ているなら、表の工夫では詰めきれません。受注・出荷の記録と在庫をつなぐ時期です。残った差が小さく、理由も追えているなら、Excelのまま続けて構いません。

つなぐなら、最初に作る画面は受注一覧と引当

受注・出荷とつなぐと決めたとき、最初に在庫の画面を作りたくなりますが、先に作るのは受注の一覧と引当です。Excelで直らなかったずれは、注文の記録の遅れと、出荷の記録の遅れから来ています。出荷は受けた注文に対して行うものなので、注文が一か所に集まって引当が付いていれば、出荷はその注文を「出荷済み」にする形で記録できます。在庫の画面だけを新しくしても、注文がFAXや電話のメモのまま回っていれば、在庫の数は同じように遅れます。

受注の一覧には、少なくとも次のことを持たせます。

• FAX・電話・メール・Webのどれで受けた注文も同じ一覧に並べ、どの入口から来たかを残す。
• 品番と、最小の単位にそろえた数量。
• 状態（受付・引当済み・出荷済み・取消）と、それを変えた人と時刻。
• 登録した時点で使える数を確かめて引当を付け、足りない分は引当待ちとして残す。
• 取消や一部だけの出荷で、引当が外れる。

引当が受注の一覧で付くようになれば、在庫の数は、入荷・出荷・返品・棚卸調整の記録から計算で出せます。出荷の記録を棚の前で残すには、スマホのカメラで品番のバーコードを読み取って入れる方法があり、kintoneで組む場合は当社のバーコード・QR読み取りのプラグインのように、読み取った値をそのまま項目に入れられます。つないだ後は、同じ品目の過去の記録と比べて差が決めた割合を超えたときに知らせる仕組み（kintoneではAI差異検出のプラグインなど）を足すと、棚卸しを待たずにずれに気づけます。

移すときも、今のExcelをいきなり止める必要はありません。受注の一覧を先に動かし、棚卸しの表と入出庫の記録の形はExcelのまま残して、締めのたびに新しい仕組みの在庫数とExcelの数を突き合わせます。何回か続けて合うことを確かめてから、Excelの在庫数を更新するのをやめ、見るだけの表にします。

Aurant Technologiesでは、今の在庫表でどこまで直せるかの切り分けから、受注一覧と引当を先に作って在庫・出荷へ広げる進め方まで、既製のサービス・kintone・専用のWebアプリの中から業務に合う作り方を選んでお手伝いしています。画面の例と、並行して動かしながら切り替える進め方は、受発注・在庫管理システムの開発のページで紹介しています。