「棚卸表をエクセルで自動計算したいけれど、どの関数を使えばよいかわからない」「毎回の棚卸で在庫数や金額を手計算していて手間がかかる」とお悩みではありませんか。

エクセルは、関数を活用することで在庫数や棚卸差異、在庫金額などを自動計算できるため、棚卸業務の効率化に役立ちます。一度フォーマットを作成すれば、毎月・毎年の棚卸でも使い回せるほか、入力ミスや計算ミスの防止にもつながります。

本記事では、Excel業務改善の専門家であるセルネッツが、棚卸表を自動計算できるエクセルの作り方を、初心者にもわかりやすく解説します。

コピペで使える関数や、自動計算を設定する手順、作成時のポイントまで紹介します。また、エクセルで棚卸管理を行う限界や、より効率的に管理する方法も解説するので、棚卸業務を効率化したい方はぜひ参考にしてください。

EXCEL開発・VBA開発・EXCELマクロ開発ならセルネッツ

棚卸表の自動計算をエクセルで実現する仕組みとは

エクセルの棚卸表は、あらかじめ関数を設定しておくことで、入力した数量や単価をもとに自動で計算できる仕組みです。

たとえば、在庫金額の列に「数量×単価」の数式を設定しておけば、数量や単価を入力・変更するたびに金額が自動で更新されます。

同様に、合計金額もSUM関数を設定しておけば、各商品の金額を集計し直す必要はありません。

このように、一度数式を設定してしまえば、担当者は必要な数値を入力するだけで済むため、作業時間の短縮や計算ミスの防止につながります。

エクセルで自動計算できるのは在庫金額・合計・棚卸差異など

エクセルの棚卸表では、主に次のような計算を自動化できます。

• 在庫金額の計算:数量×単価から商品ごとの在庫金額を自動算出
• 合計金額の集計:全商品の在庫金額を自動で合算
• 棚卸差異の計算:帳簿在庫と実在庫の差を自動表示
• 差異金額の算出:棚卸差異に単価を掛けて金額ベースでも確認

これらを関数で設定しておけば、入力内容が変わるたびに計算結果も自動で更新されます。品目数が多い棚卸でも、毎回電卓で計算し直す必要がありません。

【差異確認シート】

エクセルによる棚卸表の自動計算は中小規模に向いている

エクセルによる棚卸表の自動計算は、品目数が比較的少ない企業や店舗に適した方法です。

エクセルは多くのパソコンに標準搭載されており、追加費用をかけずに利用できるほか、自社の運用に合わせてレイアウトや計算式を自由にカスタマイズできます。そのため、小売店や飲食店、製造業など、多くの中小企業で活用されています。

一方で、数万点規模の商品を管理する場合や複数拠点で同時に在庫管理を行う場合は、入力ミスやファイル管理の負担が大きくなりやすい点に注意が必要です。

こうしたケースでは、在庫管理システムの導入を検討したほうが効率的です。

棚卸表を自動計算できるエクセルの作り方4ステップ

ここからは、実際にエクセルで自動計算できる棚卸表を作る方法を紹介します。

エクセルに詳しくない方でも、手順どおりに進めれば設定できる内容です。

コピー&ペーストですぐに使える関数も紹介しますので、ぜひ読み進めながら作成に挑戦してみてください。

STEP1:棚卸表の項目(列)を決める

まずは、棚卸表の土台となる項目を作成します。

最低限あると便利な項目は、次のとおりです。

• 商品コード:JANコードや社内の商品番号
• 品名:商品の名称
• 数量:実際に数えた在庫数
• 単価:1個あたりの仕入単価
• 金額:数量×単価で算出する在庫金額
• 備考:保管場所やロット番号など

食品であれば「賞味期限」、アパレルなら「サイズ」「カラー」など、業種に応じた項目を追加しても構いません。ただし、項目が増えすぎると入力負担も大きくなるため、必要最小限にするのがおすすめです。

なお、本記事では次のようなレイアウトを例に解説します。

【棚卸表シート】

なお、1行目を見出し、2行目から商品データを入力する前提で説明します。

STEP2:金額を自動計算する数式を入れる

次に、在庫金額を自動計算する数式を設定します。

金額を表示するE列の最初のデータ行であるE2セルに、次の数式を入力してください。

=C2*D2

この数式は、「C2セル(数量)×D2セル(単価)」を計算するという意味です。

設定後は、E2セルをコピーして下の行に貼り付ければ、3行目以降にも同じ数式をまとめて反映できます。

これで数量や単価を入力するたびに、在庫金額が自動で計算されます。

なお、未入力の行に「0」が表示されるのを避けたい場合は、次のIF関数がおすすめです。

=IF(OR(C2=””,D2=””),””,C2*D2) 

この式は「数量もしくは単価が空欄なら在庫金額も空欄、入力されたら計算する」という意味です。印刷時や確認時に表が見やすくなります。

STEP3:合計・小計を自動集計する

商品ごとの金額を計算したら、棚卸高の合計を集計します。

たとえば、商品データが2行目から101行目まである場合は、合計を表示したいセル(例:E102)に次の数式を入力します。

=SUM(E2:E101)

これでE列の金額をすべて合計できます。

さらに、「食品だけ」「店舗Aだけ」など条件を指定して集計したい場合は、SUMIF関数を使います。

たとえば、F列に「分類」を追加し、「食品」の金額だけを集計する場合は次のようになります。

=SUMIF(F2:F101,”食品”,E2:E101)

これは、「F列が『食品』になっている行だけ、E列の金額を合計する」という意味です。

分類別や保管場所別の棚卸金額を確認したい場合に役立ちます。

STEP4:単価の自動表示と差異判定を加える

さらに入力作業を減らしたい場合は、VLOOKUP関数を使って単価を自動表示させましょう。

事前に以下のような「商品マスタ」という別シートを作成し、商品コード・品名・単価を登録しておきます。

【商品マスタシート】

そのうえで、棚卸表のD2セル(単価)に次の数式を入力します。 

=VLOOKUP(A2,商品マスタ!$A:$C,3,FALSE)

この数式は、「A2セルに入力された商品コードを商品マスタから検索し、3列目(単価)の値を表示する」という意味です。

これにより、担当者は商品コードと数量を入力するだけで、単価と在庫金額が自動で表示されるようになります。

なお、Microsoft 365やExcel 2021以降を利用している場合は、XLOOKUP関数を利用する方法もあります。

ただし、古いバージョンとの互換性を重視する場合は、VLOOKUPを使用するとよいでしょう。

【棚卸表完成イメージ】

棚卸表の自動計算で使うエクセル関数まとめ

ここまで紹介した関数を、用途ごとにまとめました。どの関数を使えばよいか迷ったときは、この一覧を参考にしてください。

関数・数式	用途	数式のイメージ
*(掛け算)	数量と単価から在庫金額を自動計算する	=数量セル*単価セル
IF	空欄の場合は何も表示しないなど、条件に応じて表示内容を切り替える	=IF(数量セル=””,””,数量セル*単価セル)
SUM	在庫金額の合計を自動集計する	=SUM(金額列の範囲)
SUMIF	分類や保管場所など、条件を指定して小計を集計する	=SUMIF(条件範囲,条件,集計範囲)
VLOOKUP	商品コードをもとに商品マスタから単価を自動取得する	=VLOOKUP(商品コード,商品マスタ範囲,取得列,FALSE)

これらの関数を組み合わせることで、商品コードや数量を入力するだけで、単価や在庫金額、棚卸高の合計まで自動計算できる棚卸表を作成できます。

初めて棚卸表を作る場合は、まずは掛け算(*)とSUMで基本的な自動計算を設定しましょう。その後、入力作業を効率化したい場合はVLOOKUP、分類ごとの集計が必要になったらSUMIFを追加すると、より実用的な棚卸表になります。

棚卸表を自動計算するときは「単価の設定方法」に注意する

エクセルで棚卸表を自動計算する場合、数式だけでなく、入力する単価が正しいことも重要です。単価が誤っていると、在庫金額や棚卸高も正しく計算されません。

中小企業で広く採用されている評価方法は「最終仕入原価法」です。これは、その事業年度で最後に仕入れた価格を単価として、期末に残っている在庫を評価する方法です。計算方法がシンプルで管理しやすいため、多くの中小企業で利用されています。

また、最終仕入原価法は税法上の法定評価方法とされており、棚卸資産の評価方法を税務署へ届け出ていない場合は、この方法が適用されます(国税庁「A1-17 所得税の棚卸資産の評価方法の届出手続」)。

そのため、VLOOKUPなどで商品マスタから単価を自動取得する場合は、商品マスタの単価を最新の仕入価格へ定期的に更新することが大切です。

なお、業種や会計方針によっては、総平均法や移動平均法など、最終仕入原価法以外の評価方法を採用しているケースもあります。自社でどの評価方法を適用しているかわからない場合は、事前に顧問税理士へ確認しておくと安心です。

エクセル棚卸表でよくある課題

ここまで、エクセルで棚卸表を自動計算する方法を紹介してきました。

ただし、自作の棚卸表は便利な反面、運用を続けるなかでさまざまな課題が生じることがあります。特に、複数人で利用する場合や管理する在庫が増えてきた場合は、入力ミスや運用トラブルが発生しやすくなるため注意が必要です。

ここでは、自作のエクセル棚卸表で起こりやすい代表的な3つの課題と、その対策を紹介します。

数式が上書きされて誤った集計になる

複数人で棚卸表を利用していると、誤って数式が入力されたセルを上書きしてしまうことがあります。

たとえば、在庫金額を自動計算するセルに数値を直接入力してしまうと、その行だけ自動計算が機能しなくなります。見た目では気付きにくいため、誤った棚卸高のまま決算資料を作成してしまうリスクもあります。

こうしたミスを防ぐには、エクセルの「シートの保護」機能を活用するのがおすすめです。数式や商品マスタが入力されたセルを編集不可にし、数量など入力が必要なセルだけ編集できるよう設定すれば、誤操作による数式の破損を防ぎやすくなります。

作成者しか扱えない「属人化」が起こる

自動計算を充実させようとすると、関数が増え、ファイルの仕組みが複雑になりがちです。

その結果、「作成した担当者しか修正方法がわからない」という属人化が起こるケースは少なくありません。担当者が異動・退職すると、エラーが発生しても原因を特定できず、棚卸表を作り直さなければならないこともあります。

こうした事態を防ぐには、誰が見ても理解しやすいファイル構成を意識することが大切です。たとえば、数式の意味をコメントとして残す、入力欄と商品マスタを分ける、シート名や項目名のルールを統一するなど、引き継ぎしやすい運用を心がけましょう。

在庫や拠点が増えると管理が難しくなる

エクセルは柔軟にカスタマイズできる一方で、大規模な在庫管理には限界があります。

品目数や取扱商品が増えると、入力や検索に時間がかかりやすくなるからです。また、複数の店舗や倉庫で在庫を管理する場合は、ファイルの共有やデータの統合作業も煩雑になり、入力漏れや集計ミスが発生する原因になります。

さらに、複数人が同時に編集する運用や、リアルタイムで在庫数を更新する運用には対応しにくい点も課題です。

「ファイルが重くなった」「集計作業に時間がかかる」「複数拠点の在庫をまとめて管理できない」といった状況が増えてきた場合は、エクセルだけで運用するには限界を迎えているサインといえるでしょう。

エクセルで作るか、ツール化・外注するかの判断軸

棚卸業務を効率化したい場合、「エクセルのままで十分なのか」「自動化ツールやシステムを導入すべきなのか」と悩む方も多いでしょう。

重要なのは、現在の業務規模や課題に合った方法を選ぶことです。必ずしも高額なシステムを導入する必要はなく、エクセルの改善だけで十分に効率化できるケースも少なくありません。

一般的な判断の目安は、次のとおりです。

現在の状況	おすすめの対応
品目数が少なく、担当者が運用・メンテナンスできる	エクセルでの運用を継続
関数やマクロが複雑で、修正できる人が限られている	エクセルファイルの改修・保守を検討
入力や集計を手作業で行っており、工数が多い	VBAなどを活用した自動化を検討
品目数や拠点数が多く、エクセルでは管理しきれない	在庫管理システムなどの導入を検討

業務改善では、「システムを導入すること」が目的ではありません。入力作業や集計作業を減らし、業務を効率化することが本来の目的です。

そのため、既存のエクセルを活用して課題を解決できるのであれば、無理に新しいシステムを導入する必要はないでしょう。

実際、セルネッツでは他社から「100万円以上かかる」と言われた業務効率化支援について、エクセルの仕組みを活用して低コストで解決できたケースが数多くあります。

当社の見積りは8割以上が100万円(税別)以下なので、コストを抑えながら効率化を実現できるのが強みです。

▶ 改善事例を動画で見る:【管理部門】仕様変更なのに「改修コスト0円」のワケ(YouTube)

とはいえ、品目数や利用人数が増え、ファイル管理やデータ共有に限界を感じるようであれば、在庫管理システムなどへの移行を検討するタイミングといえます。

このように、自社の業務規模や将来的な運用も踏まえながら、エクセル・自動化ツール・システム化の中から最適な方法を選んでみましょう。

棚卸表の自動計算エクセルに関するよくある質問

最後に棚卸表の自動計算エクセルに関するよくある質問を紹介します。

棚卸表は何年保存すればよいですか?

棚卸表は決算関係書類にあたり、法人の場合は確定申告書の提出期限の翌日から原則7年間の保存が必要です。なお、青色申告書を提出した事業年度で欠損金が生じた場合などは、10年間の保存が必要です。

エクセルとGoogleスプレッドシートはどちらがよいですか?

数式の考え方は両者でほぼ共通しており、本記事で紹介した関数の多くはスプレッドシートでも使えます。複数人で同時編集したい場合はスプレッドシート、既存の社内資産や印刷レイアウトを重視する場合はエクセル、という選び方が現実的です。

関数が苦手でも自動計算の棚卸表は作れますか?

はい。まずは金額欄の「=数量×単価」と、合計欄の「=SUM(…)」の2つだけでも、集計はぐっと楽になります。VLOOKUPやSUMIFは慣れてきてから足せば十分です。それでも難しい場合は、無料テンプレートの活用や、専門会社への相談も選択肢になります。

まとめ|棚卸表の自動計算化でお困りならセルネッツへご相談を

エクセルの棚卸表は、あらかじめ関数を設定しておくことで、数量や商品コードを入力するだけで在庫金額や棚卸高を自動計算できます。基本となる「掛け算(*)」と「SUM」に加え、VLOOKUPやSUMIF、IFなどの関数を活用すれば、入力や集計の手間を大幅に削減できるでしょう。

一方で、自作の棚卸表は、数式の上書きや属人化、品目数・拠点数の増加による運用負荷など、長く使うほど課題が生じやすくなります。シートの保護やファイル構成の見直しなど、壊れにくく・引き継ぎやすい運用を意識することも欠かせません。

「今使っている棚卸表をもっと効率化したい」「前任者が作成したエクセルの仕組みが複雑で修正できない」といったお悩みがあれば、セルネッツへお気軽にご相談ください。

セルネッツは、Excel業務改善を専門とする会社です。エクセルの改修・自動化はもちろん、「本当にシステム導入が必要なのか」という視点も踏まえ、費用対効果を重視した改善方法をご提案します。まずは無料で、現在の課題をお聞かせください。

無料お見積もりシミュレーターを試す