棚卸は、企業の資産状況を把握し、正確な在庫管理や健全な経営を実現するための重要な業務です。
棚卸作業には、棚卸表が欠かせません。
「コストをかけずに、まずは身近なエクセルで棚卸表を作れないだろうか」と考えている担当者の方も多いのではないでしょうか。
棚卸表のエクセルでの作り方として項目やステップ、役立つ便利機能、注意点をわかりやすく解説します。
棚卸表はエクセルで作れる？
棚卸表は、エクセルで十分作ることが可能です。
実際、中小企業や管理する品目数が限られる店舗などの多くで、棚卸表作成の主要なツールとして活用されています。
その理由に、多くのパソコンに標準でインストールされており追加コストがかからないこと、そして自社の運用に合わせて自由にカスタマイズできる柔軟性の高さなどが挙げられます。
まずはエクセルで自社に最適な棚卸表を作成し、在庫管理の基本を固めることは有効な選択肢といえるでしょう。
棚卸表のエクセルでの作り方：必要な項目
効果的な棚卸表を作成するための第一歩は、必要な項目を正確に洗い出すことです。
項目が不十分だと、せっかく棚卸をしても正確な在庫状況や資産価値を把握できません。
棚卸表のエクセルでの作り方として必要な基本項目を確認していきましょう。
商品情報を特定する項目
商品を一意に識別するための情報です。
具体的な項目に以下のようなものがあります。
- 商品コード：JANコードや社内管理コードなど各商品に割り振られた固有の識別番号
- 商品名：商品の正式名称
- 規格・型番：商品のサイズや色、モデルなどの仕様
商品の識別項目が曖昧だと、どの商品の在庫を数えているのか分からなくなってしまいます。
在庫数を把握する項目
実際の在庫数を記録し、データ上の在庫数との差異を確認するための項目です。
具体的な項目に以下のようなものがあります。
- 理論在庫数量（帳簿在庫数量）：帳簿やシステムで管理されている在庫数
- 実在庫数量（実地棚卸数量）：実地棚卸でカウントした実際の在庫数
- 差異数量：理論在庫数量と実在庫数量の差
棚卸作業でもっとも中心的な役割を果たします。
在庫資産を評価する項目
棚卸は在庫の数を数えるだけでなく、会計処理のために資産価値を評価する目的もあります。
具体的な項目に以下のようなものがあります。
- 単価：商品の仕入単価や原価
- 在庫金額：単価 × 実在庫数量で計算される商品の在庫資産の総額
決算時の棚卸資産の確定や、ロス金額の把握に不可欠な情報です。
管理を効率させる補助項目
棚卸作業そのものや、後の分析を効率化するための補助的な項目です。
自社の運用に合わせて追加すると良いでしょう。
具体的な項目に以下のようなものがあります。
- 棚卸日：在庫をカウントした日付
- 担当者名：在庫をカウントした責任者の名前
- 保管場所/ロケーション：在庫が保管されている棚番やエリア
- 備考：特記事項を自由に記入するための欄
棚卸表のエクセルでの作り方：エクセルで作るステップ
次に、実際にエクセルで棚卸表を作成する手順をステップに分けて解説していきます。
棚卸表のエクセルでの作り方のステップを確認していきましょう。
必要な項目の洗い出し
まず、自社の棚卸作業に必要な項目を明確にします。
基本項目をベースに、業種や管理方法に応じてカスタマイズしましょう。
例えば、食品を扱う企業であれば「賞味期限」や「ロット番号」、アパレル業界なら「サイズ」や「カラー」などの項目が必要かもしれません。
ただし、項目が多すぎると入力負担が増えるため、必要最小限に絞ることも重要なポイントです。
新規シートの作成と基本レイアウトの設定
次に、洗い出した項目に基づいて、エクセルで新規シートを作成し、見やすく入力しやすい基本レイアウトを構築していきましょう。
ヘッダー行は背景色を付けて見やすくし、フォントを太字にすると識別しやすくなります。
また、列幅を調整して項目名が見切れないようにしましょう。
さらに、「ウィンドウ枠の固定」機能を使って1行目を固定すると、データが増えてスクロールしても常に項目名が表示され、入力ミスを防げます。
関数や便利機能の活用
手入力だけに頼ると、計算ミスや入力漏れが発生しやすくなります。
エクセルの強みである関数や便利機能を活用して自動化、効率化を図りましょう。
例えば、「在庫金額」の列には「=（単価のセル）*（実在庫数量のセル）」という数式をあらかじめ入力しておけば、単価と実在庫数量を入力するだけで自動的に在庫金額が計算されます。
これにより、数百行におよぶデータでも、瞬時に計算結果が反映される仕組みの完成です。
その他の便利機能の内容や設定方法は、次の章で解説します。
シートの保護や運用ルールの策定
棚卸表を複数人で使用する場合、誤った操作で数式や重要なデータが消えてしまうリスクがあります。
これを防ぐために、「校閲」タブの「シートの保護」機能を使いましょう。
数式が入っているセルや、変更されたくないマスターデータ部分をロックし、在庫数を入力するセルだけを編集可能に設定することで、意図しないファイルの破損を防げます。
また、誰がいつ更新するのかといった運用ルールを定めて、エクセルファイルにわかりやすく記載しておくことも重要です。
棚卸表のエクセルでの作り方：エクセルの便利機能
基本的な棚卸表が完成したら、次はエクセルの便利機能を活用して、より高機能で使いやすい表へと進化させましょう。
棚卸表のエクセルでの作り方としてエクセルの便利機能を確認していきましょう。
入力ミスを防ぐ「入力規則」
「データ」タブにある「データの入力規則」は、特定のセルに入力できる値を制限する機能です。
例えば、在庫数のセルには「整数のみ」を許可する設定をすれば、誤って文字列を入力してしまうミスを防げます。
また、日付のセルに日付形式以外のデータが入らないようにするなど、データ全体の整合性を保つために有効な機能です。
入力効率を格段に上げる「ドロップダウンリスト」
入力規則の応用として、「ドロップダウンリスト」の作成があります。
セルをクリックするとあらかじめ設定した選択肢（リスト）が表示され、その中から選んで入力できるようにする機能です。
例えば、「保管場所」や「担当者」の項目でドロップダウンリストを使えば、毎回手入力する手間が省け、表記の揺れも防げます。
「データの入力規則」の「許可」で「リスト」を選び、選択肢を入力するとドロップダウンリストを設定できます。
商品情報を瞬時に参照する「VLOOKUP関数」
VLOOKUP関数は、別の表から特定のデータを探し出して、自動で表示させる機能です。
例えば、商品コードを入力するだけで、別シートに作成した商品マスタから商品名や単価などを自動で引っ張ってくるという使い方ができます。
「=VLOOKUP(検索値,範囲,列番号,検索方法)」で記述しますが、使いこなすには多少の慣れが必要です。
VLOOKUP関数により、都度入力する手間が削減でき、商品情報や単価の入力ミスをなくすことにつながります。
データを視覚的に把握する「条件付き書式」
「ホーム」タブにある「条件付き書式」は、指定した条件に合致するセルの色やフォントを自動で変更する機能です。
例えば、「差異のセルが0でない場合に赤色でハイライトする」という設定をすれば、在庫差異がある商品を一目で発見できます。
また、在庫金額が一定額以上の高額商品には青色を付ければ、優先的に管理すべき商品の視覚的な識別が可能です。
このように、条件付き書式を使うことで、膨大なデータの中から注意すべき項目を瞬時に見つけられます。
棚卸結果を多角的に集計・分析する「ピボットテーブル」
棚卸が終わった後のデータ分析に効果を発揮するのが「ピボットテーブル」です。
「挿入」タブの「ピボットテーブル」から利用でき、大量のデータをドラッグ＆ドロップ操作だけで、さまざまな角度から集計・分析できます。
例えば、「保管場所ごとの在庫金額合計」や「商品カテゴリ別の差異発生数」などを瞬時に集計可能です。
経営判断や業務改善のための情報を得るのに役立ちます。
棚卸表のエクセルでの作り方：作成の注意点
エクセルは非常に便利ですが、万能ではありません。
特に、事業が成長し、扱う在庫の量や種類が増えるにつれてエクセルの限界も見えてきます。
棚卸表のエクセルでの作り方として注意点を確認していきましょう。
複数人での同時編集や情報共有がしにくい
棚卸作業は、多くの人員が関わるチーム作業になることがほとんどです。
しかしエクセルは、基本的に1人のユーザーが排他的に編集するように設計されているため、複数の担当者が同時に同じファイルを編集すると、「上書きミス」や「編集中」のロックがかかるなどの問題が発生します。
また、ファイルをメール添付や共有サーバーでやり取りしていると、「どれが最新版のファイルか分からない」というバージョン管理の問題も頻発し、古い情報で作業してしまうミスにもつながるでしょう。
データ量の増加による動作遅延やファイル破損のリスクがある
管理する品目数が多くなったり、複雑な関数を多用したりすると、エクセルファイルの動作が遅くなることがあります。
セルの入力や再計算に数秒から数分かかるようになると、ただでさえ時間がかかる棚卸作業の効率が著しく低下する原因です。
さらに、保存中にエラーが発生したり、突然ファイルが開けなくなったりして、重要な棚卸データが失われる可能性もあります。
こうしたリスクを軽減するには、定期的にファイルを分割したり、不要な数式を値に変換したりする運用上の工夫が必要です。
ファイルのメンテナンスが属人化しやすい
VLOOKUP関数やピボットテーブル、マクロなどを駆使して高機能な棚卸表を作成した場合、仕組みを理解しているのが作成者だけという「属人化」に陥りがちです。
もしその担当者が退職や異動をすると、誰もメンテナンスできなくなり、エラーが発生しても対処できない「ブラックボックス」と化したファイルが残されることになります。
こうした属人化を防ぐには、ドキュメント作成や定期的な引き継ぎが不可欠ですが、実際には形骸化しやすいのが現実です。
エクセルの棚卸表の課題を解消するならzaico
エクセルは手軽に始められ、基本的な棚卸業務には十分対応できる優れたツールです。
しかし、事業規模の拡大や在庫管理の高度化にともない、エクセルでの棚卸表の運用に限界が見えてくることは避けられません。
棚卸表のエクセル管理で発生している課題を解消したいとお考えなら、「クラウド在庫管理システムzaico」への移行をご検討ください。
zaicoは、インターネット環境さえあれば、パソコンやスマートフォン、タブレットからいつでもどこでも最新の棚卸情報にアクセスし、複数人で同時編集も可能です。
スマートフォンのカメラでバーコードをスキャンして棚卸を行えば、入力ミスなく、圧倒的なスピードで作業が完了します。
データはクラウド上で安全に管理されるため、ファイル破損の心配もありません。
エクセルでの棚卸表に限界や移行の必要性を感じ始めたら、お気軽にzaicoまでご相談ください。
