エクセルでの在庫管理は、関数を使いこなせるかどうかで作業効率と正確性が大きく変わります。
入出庫の集計、商品情報の検索、発注アラートの判定など、在庫管理で繰り返し発生する処理は、適切な関数を組み込むことで自動化でき、転記ミスや計算ミスの削減が可能です。
在庫管理に役立つエクセル関数を業務シーン別に紹介し、運用で起きやすい課題と限界を感じたときの対策を解説します。
在庫管理にエクセル関数は使える？
エクセルでの在庫管理に関数の活用は欠かせません。
エクセル関数とは、特定の計算や処理を自動で行うためにあらかじめ用意された数式のことです。
エクセルのセルに「=SUM(A1:A10)」のように、関数名と引数を入力すると、合計や検索、条件判定などを自動で実行できます。
在庫管理では、入出庫数の合計や商品データの参照、在庫数の判定など、繰り返し発生する処理が少なくありません。
これらを手作業で行うと時間がかかるうえ、転記ミスや計算ミスの原因にもなります。
関数を使えば一度数式を組んでおくだけで、データの変動に応じて自動で再計算され、ミスを大幅に減らせるでしょう。
在庫管理に役立つエクセル関数：集計
在庫管理に役立つ具体的なエクセル関数を見ていきましょう。
在庫管理に役立つエクセル関数の中から入出庫数や在庫数の合計を集計する際に役立つ関数を紹介します。
SUM関数
SUM関数は、指定した範囲の数値を合計する最も基本的な関数です。
「=SUM(数値1, 数値2, …)」または「=SUM(範囲)」の形で記述します。
例えば「=SUM(B2:B10)」と入力すれば、B2セルからB10セルに入力された入庫数の合計を算出できます。
日々の入出庫記録から月間の総数を求めたり、倉庫全体の在庫数を把握したりする場面で欠かせない関数です。
SUMIFS関数
SUMIFS関数は、複数の条件に一致するデータだけを合計する関数です。
「=SUMIFS(合計対象範囲, 条件範囲1, 条件1, …)」という形で記述します。
例えば「=SUMIFS(C2:C100, A2:A100, “A倉庫”, B2:B100, “部品X”)」とすれば、A倉庫にある部品Xの数だけを集計できます。
拠点が複数ある場合や、商品カテゴリ別に在庫金額を算出したい場合などに、必要な数値を抽出して合計することが可能です。
SUBTOTAL関数
SUBTOTAL関数は、フィルタで絞り込んだデータだけを集計できる関数です。
「=SUBTOTAL(集計方法, 範囲)」の形で記述し、集計方法に「9」を指定すれば合計、「3」を指定すればデータ件数を求められます。
通常のSUM関数では、フィルタで非表示になっているデータも含めて合計してしまうため、絞り込み後の集計には対応できません。
SUBTOTAL関数なら、フィルタで見えているデータだけを集計できるため、特定条件で絞り込んだ在庫数を把握したいときなどに便利です。
在庫管理に役立つエクセル関数：参照・検索
在庫管理に役立つエクセル関数の中から商品情報の参照や検索を自動化する関数を紹介します。
XLOOKUP関数
XLOOKUP関数は、指定した値を別の表から検索し、対応するデータを取り出す比較的新しい関数です。
「=XLOOKUP(検索値, 検索範囲, 戻り値の範囲)」と記述します。
例えば「=XLOOKUP(A2, マスタ!A:A, マスタ!B:B)」と入力すれば、A2セルの商品コードに合致する商品名をマスタシートから探して表示します。
次に紹介するVLOOKUP関数よりも記述がシンプルでエラーが起きにくく、手入力による品名や単価の打ち間違いを防ぐのに適した関数です。
VLOOKUP関数
VLOOKUP関数は従来から広く使われている検索関数で、指定した値に一致するデータを表の左端から探し、同じ行にある別の列のデータを返す関数です。
「=VLOOKUP(検索値, 範囲, 列番号, [検索の型])」の形で記述し、完全一致で探す場合は「=VLOOKUP(A2, マスタ!A:D, 2, FALSE)」のように指定します。
XLOOKUP関数が使えない古いバージョンのエクセルや、既存の在庫管理表にすでに組み込まれている場合に活躍する関数です。
INDEX関数+MATCH関数
INDEX関数とMATCH関数を組み合わせると、複雑な表から特定の位置にあるデータを自在に抽出できます。
INDEX関数は指定した位置のセルの値を取り出し、MATCH関数は指定値が範囲内の何番目にあるかを返す関数です。
「=INDEX(配列, 行番号, 列番号)」と「=MATCH(検査値, 検査範囲, [照合の型])」を組み合わせ、「=INDEX(B2:D10, MATCH(A12, A2:A10, 0), MATCH(B12, B1:D1, 0))」のように入れ子構造で記述します。
「縦軸が商品名、横軸が倉庫名」のようなマトリクス状の表から、条件に合う在庫数を正確に引き出したい場合に有効です。
在庫管理に役立つエクセル関数：判定・整形
在庫管理に役立つエクセル関数の中から在庫数の判定やアラート表示、データの整形を自動化する関数を紹介します。
IF関数
IF関数は、設定した条件を満たしているかどうかで、表示する結果を分岐させる関数です。
「=IF(論理式, 真の場合, 偽の場合)」の形で記述します。
例えば「=IF(C2<10, "発注", "適正")」と入力すれば、C2セルの在庫数が10未満になったら「発注」という警告文字を自動表示できます。
欠品リスクを視覚的に検知し、発注漏れを防ぐためのアラート機能として有効です。
COUNTIFS関数
COUNTIFS関数は、複数の条件に合致するセルの数を数える関数です。
「=COUNTIFS(条件範囲1, 条件1, 条件範囲2, 条件2), …」と記述します。
例えば「=COUNTIFS(A2:A100, “不良品”, B2:B100, “>”&TODAY()-30)」とすれば、直近30日間に発生した不良品の件数を数えられます。
特定の条件を満たすトラブルの発生回数や、発注点割れを起こした商品の品目数を把握するなど、現場の傾向を分析するのに便利です。
CEILING関数
CEILING関数は、数値を指定した基準値の倍数に切り上げる関数です。
「=CEILING(数値, 基準値)」の形で記述します。
例えば、必要な発注量が15個で、ロット単位が12個の場合、「=CEILING(15, 12)」と入力すると、自動で「24」という発注数が算出されます。
仕入先のケース入数や最低発注ロットに合わせて発注数を自動計算し、端数による計算ミスや発注エラーを防ぐのに役立つ関数です。
在庫管理にエクセル関数を使うときのよくある課題
エクセル関数は大変便利な一方で、実際の運用では課題も少なくありません。
在庫管理の現場でよくある、エクセル関数の課題を解説します。
意図せず関数を壊してしまう
エクセル関数を使った在庫管理で多いトラブルが、関数の入ったセルを、作業者が意図せず上書きしたり削除したりして、計算式を壊してしまうことです。
セルを選択した状態で誤ってキーボードに触れたり、別のデータをコピー＆ペーストしたりするだけで関数は消えてしまいます。
関数が壊れたことに気づかないまま運用を続けると、在庫数の計算が狂い、欠品や過剰在庫につながるリスクがあります。
データ量が増えると重くなる
日々の入出庫データが蓄積していくと、データ量の増加に伴ってファイルが重くなるのも、エクセルでよくある課題です。
SUMIFS関数やXLOOKUP関数などを数千行にわたって多用していると、ファイルを開いたり保存したりするたびに再計算が走り、フリーズする原因となります。
動作が遅くなると現場の作業効率が低下し、「エクセルが重くて入力作業が後回しになる」という運用ルール崩壊の引き金になりかねません。
複雑な関数を組んで属人化する
エクセルに詳しい担当者が良かれと思って複雑な関数やマクロを組み込むと、その担当者しかメンテナンスできない「属人化」を引き起こします。
「退職した前任者が作った管理表にエラーが出たが、誰にも直せない」というようなトラブルも少なくありません。
関数を入れ子にして何重にも組み合わせた管理表は、後任者への引き継ぎを困難にし、業務の継続を難しくする要因となり得ます。
在庫管理のエクセル関数に限界を感じたときの対策
エクセル関数を使った在庫管理で課題を感じたら、運用方法の見直しやツールの切り替えを検討するタイミングです。
在庫管理のエクセル関数に限界を感じたときの対策を解説します。
保護機能で関数を守る
意図しない関数の破壊を防ぐには、エクセルのシート保護やセルのロック機能を活用しましょう。
エクセルでは、手入力が必要な入出庫数のセルだけを入力可能にし、関数が設定されているセルは編集できないようにロックをかけることが可能です。
これにより、エクセル操作に不慣れな従業員が誤って計算式を消してしまう人的ミスを未然に防げます。
マスタと入力シートを分離する
ファイルが重くなる課題には、商品マスタと日々の入出庫を記録する入力シートを分離する対策が有効です。
商品コード・商品名・単価などの基準情報をまとめた商品マスタと、日々の入出庫履歴を別シートまたは別ファイルに分けることで、関数の参照範囲が整理されファイルを軽量化できます。
マスタを独立させると保守性も高まり、商品情報の変更を1箇所で管理できるため、複数人での運用もしやすくなるでしょう。
在庫管理システムを導入する
エクセルでの在庫管理に根本的な限界を感じた場合は、専用の在庫管理システムへの移行が最も確実な対策です。
在庫管理システムなら、あらかじめ必要な計算ロジックが組み込まれており、関数が壊れる心配やファイルが重くなるストレスから解放されます。
また、スマートフォンでのバーコード読み取りなど、エクセルにはない現場向けの入力支援機能が充実しているため、作業効率の大幅な改善も期待できるでしょう。
在庫管理にエクセル関数を使うよりzaico
エクセル関数は手軽で強力なツールですが、データ量が増えたり複数人で運用したりする段階になると、属人化や計算エラーのリスクが高まります。
エクセルの管理に限界を感じたら、現場の負担を抑えて正確な管理ができる在庫管理システムの導入を検討するタイミングです。
在庫管理の効率化をお考えなら、「クラウド在庫管理システムzaico」をご検討ください。
zaicoは、複雑な関数を組まなくても、スマートフォンでバーコードやQRコードを読み取るだけで正確な在庫数が自動計算されます。
エクセルで作られたデータをCSVで簡単にインポートできるため、移行もスムーズです。
エクセル関数を使った在庫管理に限界を感じている方は、zaicoまでお気軽にご相談ください。
