在庫管理で使うExcelの関数を紹介！表作成の方法やポイントは？

現在、エクセルには400以上の関数があり、用途に合わせて使い分けることでさまざまな表計算が行えます。関数は設定することで自動計算を行うため、在庫管理表などの表作成にも便利な機能です。しかし、豊富な関数の中からどれを選べば良いかを理解するには、関数の種類、内容などを把握しておく必要があります。

そこで本記事では、これから在庫管理に必要な表作成を検討している方向けに、在庫管理表を作成するうえで使用するExcelの関数、さらに表計算を作成する上でのステップや注意すべきポイントなどを紹介していきます。

▼更に在庫管理について詳しく知るには？
【保存版】在庫管理とは？取り組むメリットや具体的な方法を分かりやすく解説

▼社内のデータをAI化するには？
ノーコードAIツールUMWELT紹介ページ（活用事例あり）

在庫管理表の作成で利用するExcel関数

在庫管理表を作成する時には、便利なExcel関数を利用することで、より効率的に表作成ができるようになります。まずは、在庫管理表作成時に便利な関数をそれぞれ紹介していきます。

VLOOKUP関数

VLOOKUP関数は、検索条件に一致したデータを抽出するものです。用いることで入力ミスが減り、効率が上がります。使用例は、在庫管理表、一覧表、データ入力、集計表の作成時に使用できます。

IF関数

IF関数は、設定した条件により処理を変える関数のことです。たとえば、価格1,000円以上の商品を｢高額品｣と表示するように設定すれば、その金額より安い商品の欄には｢低額品｣と表示させることができます。このように、IF関数を使いこなせるようになると、エクセルによって操作できる幅が広がります。

SUMIF関数・SUMIFS関数

SUMIF関数とSUMIFS関数は足し算機能をもつSUM関数の発展系となり、条件に合ったものだけを合計できる関数です。計算したいものを関数内に指定させることで、必要な計算のみ行えるようになります。

また条件が1つのときはSUMIF関数、複数条件のときはSUMIFS関数を使います。それぞれの関数は、以下の計算式で算出できます。

・SUMIFS関数　
SUMIF(範囲, 検索条件, [合計範囲])

・SUMIFS関数(合計対象範囲, 条件範囲 1, 条件 1, [条件範囲 2, 条件 2], …)

このように、SUMIFS関数は設定できる条件が１つのみなのに対し、SUMIFS関数では複数の条件を設定できます。SUMIF関数とSUMIFS関数は特定の商品の売れ行きを確認したい時に役立つものであり、在庫管理に欠かせない関数の一つとなります。

PRODUCT関数

PRODUCT関数は、複数の数値をまとめて掛け合わせることができます。たとえば｢単価×卸率×数量｣、｢定価×卸率×数量｣など、複数の計算を合わせて行いたい時に使用されます。

CEILING関数

CEILING関数は、基準値の倍数のうち、絶対値に換算して最も近い値に切り上げられた数値を返します。表計算で利用する時は、箱単位でしか発注できない商品在庫数を管理する時などに役立ちます。

FLOOR関数

FLOOR関数は、指定された基準値の倍数のうち、最も近い値かつ 0 に近い値に数値を切り捨てます。表計算や在庫管理で用いる場合では、ケース単位での購入をする際に、端数分を単品で購入するという場合の発注ケース数を求めるのに便利です。

QUOTIENT関数

QUOTIENT関数には、除算の商の整数部を返します。 商の余り (小数部) を切り捨てる場合に、この関数を使用します。QUOTIENT関数を在庫管理に用いる場合は、日ごとの売り上げ単価を算出したいときに便利です。

ROUND関数

表計算を作成する時には、計算結果で小数が出た時に整数に直すと計算しやすくなります。そこで便利なのが、ROUND関数です。ROUND関数は数値を四捨五入して指定された桁数にします。ROUND関数は大きく分けて、以下の2つの種類に分けられます。

・ROUNDUP関数
・ROUNDDOWN関数

指定した桁数に対し切り上げ計算をする関数をROUNDUP関数、指定した桁数で切り捨てる関数をROUNDDOWN関数と呼びます。ROUNDDOWN関数とROUNDUP関数は任意の桁で切捨てあるいは切上げできることから、主にセール価格を算出する時などに役立ちます。

MOD関数

MOD関数は、割り算の余りを計算することができます。たとえば｢3の倍数の日はキャンペーン｣、｢5の倍数日は30％割引｣など、複数のセール日、もしくはキャンペーンの開催日にMOD関数を設定すると自動でキャンペーン日などを割り出してくれるようになるため、表計算の管理が楽になります。

MID関数

MID関数は、指定の文字列から任意の位置より、設定した文字数を抜き出す関数です。MID関数は、部品番号、商品コードなどのコードの一部を取り出し、分析などに使う時に利用されます。MID関数と類似しているものに、LEFT関数とRIGHT関数があります。

LEFT関数・RIGHT関数

LEFT関数は、セルに入力された文字列の先頭(左側)から指定した文字数を抽出します。RIGHT関数は、LEFT関数と逆方向へ働きを行う性質を持ち、セルに入力した文字列の最後(右側)から指定した文字数を抽出します。

LEFT関数とRIGHT関数は、開始位置を指定することなく、左や右から指定した文字数のみ取り出すことが可能です。

TRIM関数

TRIM関数は、各単語間のスペースは 1 つ残し、不要なスペースをすべて削除します。

Excelで在庫管理表を作成する方法

Excel関数を用いて在庫管理表を作成するには、どのようなステップを踏むと良いのでしょうか。在庫管理表の基本的な作り方について、順番に解説していきます。

表の項目を決定する

在庫管理表を作る前に、まずは表で管理したい項目をExcelの縦軸・横軸にそれぞれ定めておく必要があります。たとえば、品目ごとに入出庫数と在庫数を管理する場合は、品番、品目、入庫数、出庫数、在庫数などの項目、さらにそれらの項目を縦軸や横軸などどこの位置に表示するかを決めていきます。設定項目の具体例は、以下の通りです。

・設定項目
縦軸1行目：品番、品目
縦軸2行目：入庫数、出庫数、在庫数
横軸：日付

横軸と縦軸の設定をする

表の項目を定めたら、次に見やすい表にするために、横軸と縦軸の設定をします。まずは、1行目に横軸を設定します。項目のなかでも在庫は前月分が繰り越されることから、日付軸の最初の列を｢前月繰越｣に設定しておきます。

前月繰越を設定したら、さらに横軸へ管理開始日を入力欄に記入します。その後は、入力内容の規則性を読み取って連続したセルを自動で埋める機能｢フィルハンドル｣を使って日付を自動入力させると便利です。横軸が設定できたら、縦軸を準備して必要な項目を埋めていきます。

テーブルを作成する

横軸、縦軸の設定がおわったら、次にテーブルを作成します。テーブル作成は、横軸縦軸で設定した表の範囲を全て選択し、挿入タブからテーブルをクリックします。ウィンドウが表示されたら、選択範囲に間違いがないことを確認しOKをクリックすればテーブルが作成できます。

テーブルを作成しておくと、Excelのワークシート内にある表がひとまとまりのデータとして扱いやすくなります。

必要な関数を入力する

テーブルが作成できたら、最後に在庫数を自動算出をするために、必要な関数を入力します。関数をあらかじめ表入力しておくと、入庫数、出庫数、在庫数などの設定した項目が自動で計算され、表示されるようになります。

Excelの表で在庫管理をするポイント

Excelの表で在庫管理をする場合、どのような点に注意したらよいのでしょうか。Excelの表で在庫管理をするポイントについて、それぞれ紹介していきます。

管理のルールを明確化する

Excelで商品や備品の在庫管理をする場合、事前に管理ルールを明確化しておくことが大切です。商品の保管場所など管理ルールを設けるなど管理体制を整えておくことで、的確な指示出しができるようになります。

たとえば、在庫の保管場所の棚、列、段に番号を振って｢〇〇は棚Aの2列1段目」というように、商品保管場所を具体的に決めておくと管理しやすくなります。

運用マニュアルの作成と共有をする

たとえ適切な設定項目を設けた管理表が作成できたとしても、表の管理や運用に不備があれば意味がありません。表の管理や運用に不備がないように、まずは管理表の運用マニュアルを作成し、管理に携わる関係各所全てに共有することが大切です。

手軽にデータ収集や分析が行える在庫管理システムが便利！

在庫管理を表計算で作成する場合、関数を用いると便利です。しかし、関数を用途に合わせて使い分けるには数学の知識が必要です。さらにExcel表は人の手によって作成することから、ヒューマンエラーも発生しやすくなります。そのため、在庫管理システムを導入する企業も多いですが、システムを社内に1から構築する場合には、開発コストも時間もかかります。

そこで便利なツールが、TRYETINGの開発するノーコードAIクラウドUMWELTです。

表計算ではできない複雑な統計やデータ分析が、統計学やプログラミングの知識不要で実現可能。クラウド型で提供するため、開発コストもかからずに現場へ即時導入可能です。在庫管理の事例では、ビューティブランドのオルビス様において、AI需要予測で全国に約100拠点以上ある店舗の在庫量の適正化をすることができました。

更に、Excelファイルで作成した表計算ファイルをそのまま取り込んで需要予測・在庫管理ができるため、現場担当者の方の手間を煩わせることなくAI在庫管理が可能となっています。

まとめ

在庫管理表はExcelでも作成可能ですが、表計算に必要な関数などの知識が求められます。そもそもExcelはあくまで表計算ソフトであるため、データ量が増えると維持管理が難しくなります。在庫管理ツールを使いこなし、業務効率化を図ることをおすすめします。

TRYETINGがサービスを提供している「UMWELT」であれば、複数のアルゴリズムを組み合わせるだけで、誰でも簡単に必要な在庫管理システムを構築できます。プログラミングや、数学の難しい知識は一切必要ありません。料金も業界最低水準、さらに導入後はコンサルサポートもあるので安心して利用できます。在庫管理ツールは、ぜひUMWELTをご利用ください。

参照文献

Excelによる生産管理―需要予測、在庫管理からJITまで｜六角 明雄、安曇出版（2012）

ホワイトペーパーダウンロードページ

ダウンロード可能な資料一覧