在庫管理をエクセルで行うメリットは?管理表作成で使える関数を紹介
Excel(エクセル)で在庫管理を行う最大のメリットは、低コストで手軽に始められる点です。すでに多くの企業で導入されているエクセルを活用することで、新たなシステム導入にかかる費用や手間を削減できます。
一方で、データ量や業務規模によっては限界があるのも事実です。在庫管理をエクセルで行う際は、業務規模やデータ量を考慮した上での比較検討が重要です。
当記事では、在庫管理をエクセルで行う方法やメリット・デメリットなどについて詳しく解説します。
DX実現に向けたデジタル化なら、KDDI まとめてオフィスにご相談ください
1. 在庫管理をエクセルで行うメリット・デメリット
Excel(エクセル)での在庫管理は、コスト、導入の容易さ、管理のしやすさという点で多くのメリットがあります。中小企業や小規模な事業所では、特に有効な手段と言えるでしょう。
以下では、在庫管理をエクセルで行うメリット・デメリットを解説します。
1-1. 在庫管理をエクセルで行うメリット
在庫管理をエクセルで行うメリットは、主に以下のとおりです。
- コストを抑えられる
- 馴染み深いツールで手軽に導入できる
- 紙よりも管理しやすい
エクセルでの在庫管理は、特別なソフトウェアを導入する必要がないため、初期費用や運用コストを大幅に抑えられます。すでにエクセルが導入されている場合、在庫管理をする上でも、追加費用なしで始めることが可能です。
エクセルは、ビジネスシーンで広く利用されている表計算ソフトです。多くの従業員がエクセルの操作に慣れているため、特別なトレーニングなしで、すぐに在庫管理の運用を開始できます。また、エクセルにはさまざまな便利な機能が搭載されており、在庫管理に必要な集計やグラフ作成も容易に行えます。
1-2. 在庫管理をエクセルで行うデメリット
在庫管理をエクセルで行うデメリットは、主に以下のとおりです。
- 拠点が複数あるなど企業規模が大きい場合は不向き
- データ容量が多いと処理に時間がかかる
- 誤ったデータが上書きされる可能性がある
商品数や顧客数が増えるにつれて、エクセルのデータやシート数が膨大になり、管理が煩雑になります。特に複数の拠点で在庫を管理する場合は、各拠点のデータを一元管理するのが難しく、データの整合性が保てなくなる可能性があるでしょう。
また、データ量が増えると、関数の計算処理などに時間がかかり、作業効率が低下します。ファイルサイズが大きくなると、保存やバックアップに時間がかかります。
他にも、手作業でのデータ入力や修正は、人為的なミスが起こりやすく、誤ったデータが上書きされるリスクが高まる点に注意しましょう。
2. エクセルを活用した在庫管理表の作成方法
一般的にエクセルで作成される在庫管理表は、大きく分けて単票タイプ・在庫移動票タイプの2つのタイプに分類されます。
どちらのタイプが適しているかは、管理する商品の数、必要な情報の詳細度、会社の規模などによって異なります。
2-1. 単票タイプ
単票タイプの在庫管理表は、1つの商品に対して1つのシートを作成し、その商品に関する入出庫履歴を詳細に記録していく形式です。商品ごとに独立したシートを持つため、商品ごとの在庫状況を細かく把握したい場合に適しています。
【単票タイプの作成手順】
1.シートの作成
新しいブックを作成し、商品ごとに新しいシートを作成します。
2.エクセルのA列に必要な情報を記入
品番、サイズ、残高などの必要情報を記載し、その下には日付を入れます。
3.行の見出しの追加
入庫、出庫、在庫、備考などの項目を追加します。
4.残高の計算
残高のセルには、入庫数から出庫数を引く計算式を入力します。
5.在庫の記載
A列に日付を記載し、在庫の変動を記録します。
単票タイプでは、1商品ごとの入出庫履歴を時系列で追跡できるため、いつどの商品が入出庫されたのかを正確に把握できます。
2-2. 在庫移動票タイプ
在庫移動票タイプとは、1つのシートにすべての商品の入出庫履歴を記録していく形式の在庫管理表です。日付を横軸、商品名を縦軸にして、各セルに入出庫数を記録していく形式です。帳簿のように、日々の入出庫を時系列で追跡できます。1つのシートで全体的な在庫の動きを把握しやすいのが特徴です。
【在庫移動票タイプの作成手順】
1.表のフォーマットの設定
エクセルシートを横長のフォーマットに設定し、商品ごとに2行(入庫と出庫用)を割り当てます。さらに、サイズやカラーなど、商品ごとに複数の情報が含まれる場合は、3行以上になるので、適宜調整してください。
2.エクセルのA列以降に必要な情報を記入
列ごとに品番、サイズ、在庫数など管理に必要な情報を記載します。基本項目以降の横の列には、日付が入ります。
3.データ入力
商品ごとに、入庫や出庫の数量を入力します。
作り方は基本的に単票と同じですが、行列が反対になるのが特徴です。
また、在庫管理の際に「引当在庫数」をあわせて把握することで、より正確な必要発注数を予測できます。業務上必要であれば、管理表に適宜情報を追加しましょう。
3. 在庫管理表をエクセルで作成する際に覚えておきたいこと
エクセルで在庫管理表を作成する際、1からシートを作り始めるのは大変な作業です。以下では、エクセルで在庫管理表を作成する工数を少しでも減らすための方法を3点紹介します。
3-1. テンプレートを使用する
エクセルのテンプレートライブラリには、在庫管理にも使用できるような、さまざまな種類のテンプレートが用意されています。また、多くのサードパーティーのテンプレートサイトにも、在庫管理専用のテンプレートが提供されているでしょう。
自社のニーズに合ったテンプレートが見つからない場合は、既存のテンプレートをカスタマイズすることも可能です。
【sample_【単票】在庫管理.xlsx】
在庫管理のサンプルはこちら
3-2. エクセル関数を活用する
エクセルの関数は、ある特定の計算や処理を行うための「式」のようなものです。関数を活用することで、複雑な計算を自動化し、作業効率を大幅に向上させられます。
在庫管理に役立つ代表的な関数としては、VLOOKUP関数やIF関数、SUMIF関数、SUMIFS関数などがあります。
3-3. エクセルマクロで作成する
エクセルのマクロとは、一連の操作を記録し、それを自動で実行させる機能のことです。例えば、「ボタンをクリックする」や「特定のセルに数値を入力する」といった操作を、マクロに記録しておけば、後でそのボタンをクリックするだけで、記録された一連の操作が自動的に実行されます。
在庫管理において、マクロでできることは多岐にわたります。
・データ入力の自動化
新しい商品を追加する際、商品コードや初期在庫数を自動で入力する。
毎日、最新の入出庫データを自動で入力する。
・計算の自動化
残高、発注点、在庫評価額などを自動計算する。
・グラフの作成
在庫の推移をグラフ化し、視覚的に分析する。
・条件分岐
在庫数が少ない場合に警告を表示する。
発注が必要な場合に、発注書を作成する。
・データの抽出
特定の条件に合致するデータを抽出する。
ただし、マクロを作成するには、Visual Basic for Applications(VBA)というプログラミング言語の基礎的な知識が必要です。
4. 在庫管理表で活用したいエクセル関数
在庫管理表で活用できるエクセル関数は、多岐にわたります。どのようなデータを管理したいか、どのような計算を行いたいかによって、使用すべき関数は異なります。
一般的な在庫管理でよく利用される関数とその活用例をいくつか紹介します。
4-1. VLOOKUP関数
VLOOKUP関数とは、エクセルの表から特定の値を検索し、その値に対応する別の値を返す関数です。いわば、エクセル版の辞書引きのようなものです。
=VLOOKUP(検索値,検索範囲,列番号,[検索の型])
在庫管理においては、商品コードから商品名や単価を自動で呼び出すといった用途で頻繁に利用されます。他にも、商品コードから、その商品の単価を取得し、金額を計算するのにも活用できます。
4-2. IF関数
IF関数とは、エクセルで特定の条件を満たす場合と満たさない場合で、表示する内容や実行する処理を切り替えるための関数です。
=IF(論理式,真の場合,偽の場合)
在庫管理における活用例としては、在庫数が発注点以下になった場合に「発注」と表示したり、在庫数が多すぎる場合は「過剰」、少なすぎる場合は「不足」、理想の在庫数を維持している場合は「適正」と表示したりするのに役立ちます。
4-3. SUMIF関数・SUMIFS関数
SUMIF関数とSUMIFS関数は、エクセルで特定の条件に合致するセルの値を合計する関数です。
SUMIF関数は、1つの条件に基づいて合計を行う関数です。SUMIFS関数は、複数の条件に基づいて合計を行う関数です。
=SUMIF(範囲,条件,合計範囲)
=SUMIFS(合計範囲,条件範囲1,条件1,[条件範囲2,条件2],...)
在庫管理における活用例としては、特定の商品や期間の合計在庫数、売上などを計算する際に便利です。
SUMIF関数の例:特定の商品の在庫数を合計する場合。たとえば、A列に商品名、B列に在庫数があるシートで、特定の商品「商品A」の在庫数を合計する際に次のように使用します。
=SUMIF(A:A,"商品A",B:B)
SUMIFS関数の例: A列に日付、B列に商品名、C列に入出庫ステータス、D列に数量を入力したシートで、特定の商品の「商品A」の入庫数や出庫数の合計を導き出す際に、次のように使用します。
=SUMIFS($D:$D, $B:$B, "商品A", $C:$C, "入庫")
=SUMIFS($D:$D, $B:$B, "商品A", $C:$C, "出庫")
4-4. ROUND関数
ROUND関数は、エクセルで数値を指定した桁数で四捨五入する関数です。桁数を正の値にすると小数点以下、負の値にすると小数点以上の桁を指定します。
=ROUND(数値,桁数)
在庫管理における活用例としては、在庫評価額の計算や、売り上げ予測、発注数の計算などで、小数点以下の端数を切り捨てたり、ある桁数に丸めて表示したりすることで、より見やすく、扱いやすいデータにすることが可能です。
4-5. MOD関数
MOD関数は、ある数を別の数で割ったときの余りを求める関数です。
=MOD(数値,除数)
在庫管理では、特定の数量でまとめたり、周期的な処理を行ったりする際に役立ちます。例えば、ある商品を10個単位で定期的に発注する場合、在庫数を10で割って余りが5になったときに発注する、といったルールを作れます。
他にも、商品ごとに異なる周期で棚卸しを行う場合、MOD関数を使って、どの月に棚卸しを行うか判定できます。
=MOD(A2,B2)
4-6. MID関数
MID関数は、エクセルで文字列の特定の位置から、指定した文字数を抜き出す関数です。
=MID(文字列,抜き出しを開始する位置,抜き出す文字数)
在庫管理における活用例としては、商品コードから品名や数字の一部を抽出したり、長い品名から必要な部分だけを表示したりする際に便利です。商品コードが「AAA12345」のように構成されている場合、品名部分(「AAA」)+最初の数字2文字など、任意のところまでを抽出できます。また、品名が長い場合、先頭から数文字だけ表示することで、見やすくすることができます。
=MID(A2,B2,C2)
4-7. LEFT関数・RIGHT関数
LEFT関数とRIGHT関数は、エクセルで文字列の左端または右端から、指定した文字数を抜き出す関数です。
=LEFT(文字列,左から抜き出す文字数)
=RIGHT(文字列,右から抜き出す文字数)
在庫管理における活用例としては、MID関数と同様、商品コードからメーカーコードを抽出したり、長い品名から先頭の数文字だけ表示したりする際に便利です。
エクセルの文字列操作関数であるLEFT関数、RIGHT関数、MID関数は、以下のように使い分けられます。
【使い分けの例】
- メーカーコードが常に先頭にある場合:LEFT関数
- メーカーコードが末尾にある場合:RIGHT関数
- メーカーコードの位置が固定でない場合:MID関数とFIND関数などを組み合わせる
4-8. PRODUCT関数
PRODUCT関数は、エクセルで複数のセル内の数値をすべて掛けあわせる関数です。
=PRODUCT(数値1,数値2,...)
在庫管理における活用例としては、単価と数量を掛けあわせて金額を計算したり、複数の割引率を掛けあわせて最終的な価格を計算したりする際に役立ちます。
=PRODUCT(B2, 1-C2, 1-D2)
4-9. CEILING関数
CEILING関数は、指定した数値を、指定した倍数に切り上げる関数です。
=CEILING(切り上げたい数値,切り上げる倍数)
在庫管理における活用例としては、商品を一定の単位で発注したり、在庫数を特定の値に揃えたりしたい場合に活用できます。例えば、商品を10個単位で発注する場合、在庫数が8個でもCEILING関数で10個に切り上げ、発注数量を決定できます。
4-10. FLOOR関数
FLOOR関数は、指定した数値を、指定した倍数に切り下げる関数です。CEILING関数とは反対に、0に近づく方向に数値を調整します。
=FLOOR(切り下げたい数値,切り下げる倍数)
在庫管理における活用例としては、商品を一定の単位で発注したい場合のほか、在庫数を特定の値以下に抑えたい場合に活用できます。例えば、最小在庫を5個に設定する場合、在庫数が3個だったとしても、FLOOR関数を使って0個に切り下げ、発注が必要であると判断できます。
=FLOOR(B2, C2)
4-11. QUOTIENT関数
QUOTIENT関数は、エクセルで2つの数値を割り算したときの商の整数部を返す関数です。小数点以下の部分は切り捨てられます。
=QUOTIENT(分子,分母)
在庫管理における活用例としては、ある数量の商品を、一定の個数ずつ箱詰めする場合に、箱の数がいくつになるか計算するといった場面で活用できます。
4-12. TRIM関数
TRIM関数は、セル内の不要なスペースを削除する関数です。特に、文字列の始めや終わり、または文字と文字の間にある連続したスペースを1つのスペースに統一したり、完全に削除したりする際に役立ちます。
=TRIM(文字列)
在庫管理における活用例としては、商品名の統一などに役立ちます。複数のデータに余分なスペースが含まれている場合、TRIM関数で統一することが可能です。例えば、「 りんご 」という商品名が「りんご」に修正されます。
また、TRIM関数でスペースを削除することで、検索の精度も向上させることができます。
5. 在庫管理表をエクセルで運用する際のポイント
エクセルで在庫管理表を作成・運用する際には、フォーマットを統一し、マニュアルを作成することで、誰が担当しても同じように運用できます。
以下では、在庫管理表をエクセルで運用する際のポイントを3点紹介します。
関連サービス | : | Microsoft 365 with KDDI |
---|
5-1. 運用ルールを決定する
エクセルで在庫管理表を作成する際には、運用ルールを明確にすることで、データの整合性が保たれ、より正確な在庫管理が可能になります。入力項目や手順が統一できれば、作業時間が短縮されミスも減るでしょう。
【在庫管理表の運用ルールの例】
項目 | 内容 | 備考 |
---|---|---|
商品名 | 全角カタカナで入力 | スペースは1つに統一 |
商品コード | 英数字10桁 | 先頭はアルファベット |
数量 | 数値 | 小数点以下は切り捨て |
入庫日 | 日付形式 | YYYY/MM/DDで入力 |
担当者 | 全角文字で入力 | 苗字と名前の間はスペース1つ |
例えば、数値は数値形式、日付は日付形式など、データ形式を統一することで、計算やグラフ作成がスムーズになります。また、いつ、誰が、どのデータを更新するかを決め、定期的な更新を徹底できるとよいでしょう。
5-2. バックアップを取っておく
エクセルで作成した在庫管理表は、企業にとって非常に重要なデータ資産です。万が一、ファイルが破損したり、誤って削除されたりした場合、業務に大きな影響が出る恐れがあります。そのため、定期的なバックアップが不可欠です。ファイルが破損した場合、最新のバックアップから復元することで、データを失うリスクを最小限に抑えられます。
バックアップの方法は、外付けハードディスク、USB、クラウドストレージ、バックアップソフトなど数多くの種類があり、それぞれメリット・デメリットがあるので、自社で運用しやすい手法を検討するとよいでしょう。
5-3. クラウド型のエクセルを使用する
クラウド型のエクセルとは、インターネットを通じて利用できるクラウド型オフィスアプリケーションの一つである「エクセル」のことです。 Microsoft 365のエクセル オンライン(Excel Onlinne/Excel for the web)や、 Google スプレッドシート などが代表的な例です。
パソコンだけでなく、スマートフォンやタブレットなど、インターネットに接続できるデバイスであれば、いつでもどこからでもデータにアクセスできます。複数のユーザーが同時に同じシートを編集でき、リアルタイムで変更内容が反映されるのもメリットです。また、更新履歴も残るので、どの時点で誰が何を更新したのか、必要に応じて後から確認することもできます。
まとめ
エクセルで在庫管理を行う最大のメリットは、特別なシステム導入の必要がなく、低コストで手軽に始められることです。また、従業員にも馴染みがあり、操作の自由度が高い点もメリットです。
ただし、エクセルで在庫管理を行う際は、データの整合性を保つために、入力ルールを明確にし、万が一のために定期的にバックアップを取るなどの対策が重要です。また、業務規模や複雑さにあわせて、より専門的な在庫管理システムへの移行も検討するとよいでしょう。
Microsoft 365 ならソフトウェアが自動更新されるため、在庫管理に最適なエクセルをはじめ、各種 Office アプリケーションの機能を、常に最新バージョンでご利用いただけます。また、高いセキュリティレベルを有しており、ビジネスでの利用も安心です。さらに、生成AI の Copilot for Microsoft 365 を活用すれば、エクセルの在庫管理データを素にした分析なども行ってくれます。
気になる方はぜひお気軽にご相談ください。セミナーも随時開催しています。
関連情報 | : | セミナー情報 |
---|---|---|
関連サービス | : | Microsoft 365 with KDDI |
関連特集 | : | あなたの会社の非効率は Microsoft Teams で解決できる! |
※ 記載された情報は、掲載日現在のものです。