エクセル在庫管理表の作り方：初心者でもわかる！日々の業務効率を劇的に改善する秘訣

この記事では、食品倉庫の事務員の方が抱えるエクセルでの在庫管理に関するお悩みを解決します。エクセル初心者の方でも、日々の業務を効率化し、正確な在庫管理を実現するための具体的な方法を、ステップバイステップで解説します。在庫管理表の作成を通じて、あなたの業務効率がどのように変わるのか、その具体的なメリットについても触れていきます。

食品(乾物)を扱う倉庫の事務所で事務をしています。この度在庫管理表をエクセルで作るよう頼まれました。

どのような倉庫かというと、10品目の商品を保管してある倉庫で、２０人の従業員がその１０品目の中から毎日ランダム数持ち出していきます。従業員は倉庫から持ち出した品目と数を記載した書類を提出しますので、その数をエクセルに打ち込んでいき管理する形になります。

例として、品目をくだものとすると

「田中：りんご10個、みかん３個、バナナ６個持ち出し」

「佐藤：みかん５個、キウイ１５個、パイン９個持ち出し」

といった事が書いてある紙を元に、毎日の集計をしていきます。

従業員は持ち出した商品を持って帰る事はありません(実際はありますが無しとします)ので、基本的には(総在庫数-持出し数)で、減るばかりで増える事はありません。ただ月４～５回(不定期)の入荷日がありますので、その時に総在庫数にプラスしたいと思います。

目的は、

日付ごとに「誰が」「何を」「何個」持ち出したのかがすぐわかる(日別持ち出し数)
今現在倉庫には「何が」「何個」残っているのかすぐわかる(現在の倉庫の総在庫数)
過去日の終業時には総在庫数が何個だったかがすぐわかる(過去日の終業時の在庫)

３は、事務員が多忙な時には持出し報告せずに紙だけ置いて持ち出して良い事になっているのですが、その時に数が合わなくなる事が多いからです(盗難も疑っていますが、逆に在庫数が多すぎる場合もある･･･)

という感じです。

そこで質問です。

私の考えでは

日付ごとにシートを分ける

従業員を行、品目を列に並べて、行の最後に品目ごとの「合計」行を作る。

さらに合計のしたの行に「入荷」という行を作り、入荷日のみそこに数字を入れる。

その下の行に「現在の総在庫数」という行を作り、一日前のシート(つまり左隣のシート)の「現在の総在庫数」から、「合計」行の数字を引き、「入荷」行の数を足した数を表示させる。

という感じでいけるかな？と思ったのですが、

その「一日前のシートから今日の合計行を引く」という計算式を日付分手作業で作らなければならず、かなり手間です。

オートフィル機能のように、シートをコピーした際に自動的に「一つ左のシートを参照にする」といった感じに変える方法などはないでしょうか？

そもそも根本から考え方が違いますでしょうか？

一つのシートで出来てしまうならそのほうがいいです。

職場にはPC知識のある方がおらず、今まで全部手書きで行っておりました。

よろしくお願いいたします。

上記の質問にお答えします。エクセルでの在庫管理表作成は、一見複雑に見えますが、適切な方法を使えば、初心者の方でも簡単に、そして効率的に管理できるようになります。ここでは、あなたの状況に合わせて、具体的なステップと、より効率的な方法を提案します。

1. 在庫管理表作成の基本：目的の明確化と全体像の把握

まず、在庫管理表を作成する目的を再確認しましょう。今回の目的は以下の3点です。

日付ごとの持ち出し数の把握
現在の総在庫数の把握
過去日の終業時の在庫数の把握

これらの目的を達成するために、どのような情報が必要で、どのように整理すれば良いかを考えます。この段階で、エクセルのどの機能を使うか、どのようなレイアウトにするかなど、大まかな全体像を把握することが重要です。

2. エクセル在庫管理表の設計：シート構成とデータの入力方法

質問者様の最初のアイデアである「日付ごとにシートを分ける」という方法は、確かに一つのアプローチですが、シート数が増えると管理が煩雑になる可能性があります。ここでは、より効率的な方法として、1つのシートで管理する方法を提案します。

2-1. シート構成

1つのシートで管理する場合、以下のような列と行で構成します。

日付：データの記録日
品目：管理する商品の名前（例：りんご、みかん、バナナなど）
入庫数：入荷した商品の数
出庫数：従業員が持ち出した商品の数
現在の在庫数：その時点での在庫数

行は、日付、品目、入庫数、出庫数、現在の在庫数の組み合わせで、各商品の在庫変動を記録します。

2-2. データの入力方法

データの入力は、以下の手順で行います。

日付：データの記録日を入力します。
品目：管理する商品の名前をプルダウンリストから選択します。
入庫数：入荷があった場合は、その数を入力します。
出庫数：従業員が持ち出した商品の数を入力します。
現在の在庫数：数式を用いて自動計算します。

3. エクセル関数の活用：在庫数の自動計算と効率化

エクセル関数を効果的に活用することで、在庫管理の効率を格段に向上させることができます。特に重要なのは、在庫数の自動計算です。

3-1. 現在の在庫数の計算

「現在の在庫数」を自動計算するために、以下の数式を使用します。この数式は、シート内の他のデータに基づいて、現在の在庫数を動的に計算します。

=IF(COUNTIFS(A:A,A2,B:B,B2)=1,SUMIF(B:B,B2,C:C)-SUMIF(B:B,B2,D:D),SUM(SUMIFS(C:C,B:B,B2,A:A,”<="&A2))-SUMIFS(D:D,B:B,B2,A:A,"<="&A2))

この数式は、以下の要素で構成されています。

SUMIF関数：特定の条件に合致するセルの値を合計します。ここでは、品目ごとの入庫数と出庫数を合計するために使用します。
COUNTIFS関数: データの重複をチェックし、最初のデータの場合は入庫数から出庫数を引いた値を表示し、重複している場合は、過去の在庫数を考慮した計算を行います。
IF関数：条件分岐に使用します。

この数式を「現在の在庫数」の列に入力し、各行にコピーすることで、在庫数が自動的に計算されるようになります。これにより、手作業での計算が不要になり、入力ミスも防ぐことができます。

3-2. 入荷と出庫の記録

入荷と出庫の記録は、SUMIF関数を使って行います。例えば、特定の品目の入荷数を合計するには、以下のような数式を使用します。

=SUMIF(B:B,”りんご”,C:C)

この数式は、「品目」列で「りんご」と一致する行の「入庫数」列の値を合計します。出庫数についても同様に、SUMIF関数を使って合計できます。

4. データの可視化：グラフとピボットテーブルの活用

エクセルでは、データをグラフやピボットテーブルで可視化することで、在庫状況をより直感的に把握することができます。

4-1. グラフの作成

グラフを作成することで、在庫数の推移や、品目ごとの入庫・出庫の傾向を視覚的に把握できます。例えば、在庫数の推移を折れ線グラフで表示したり、品目ごとの入庫・出庫数を棒グラフで比較したりすることができます。

グラフの作成手順は以下の通りです。

データ範囲を選択します。
「挿入」タブから、適切なグラフの種類（折れ線グラフ、棒グラフなど）を選択します。
グラフのタイトルや軸ラベルなどを設定し、見やすいグラフを作成します。

4-2. ピボットテーブルの活用

ピボットテーブルは、データの集計や分析に非常に便利な機能です。例えば、日ごとの出庫数や、品目ごとの在庫数を集計することができます。

ピボットテーブルの作成手順は以下の通りです。

データ範囲を選択します。
「挿入」タブから「ピボットテーブル」を選択します。
ピボットテーブルのレイアウトを設定します（行ラベル、列ラベル、値）。
ピボットテーブルの集計方法（合計、平均など）を設定します。

5. 実践的なステップ：在庫管理表の作成と運用

ここまでの説明を踏まえ、実際に在庫管理表を作成し、運用するステップを具体的に見ていきましょう。

5-1. エクセルファイルの準備

エクセルを開き、新しいワークブックを作成します。
シート名を「在庫管理表」など、わかりやすい名前に変更します。

5-2. 列の見出しの入力

1行目に、以下の列見出しを入力します。

A列：日付
B列：品目
C列：入庫数
D列：出庫数
E列：現在の在庫数

5-3. 品目のプルダウンリストの作成

品目を入力する列（B列）で、プルダウンリストを作成します。これにより、入力ミスを防ぎ、データの正確性を高めることができます。

品目リストを作成します。例えば、F列に品目のリスト（りんご、みかん、バナナなど）を入力します。
B列のデータ範囲を選択します。
「データ」タブから「データの入力規則」を選択します。
「入力値の種類」を「リスト」に設定し、「元の値」に品目リストの範囲（例：$F$1:$F$10）を指定します。
「OK」をクリックします。

5-4. 在庫数計算式の入力

E2セルに、前述の在庫数計算式を入力します。

=IF(COUNTIFS(A:A,A2,B:B,B2)=1,SUMIF(B:B,B2,C:C)-SUMIF(B:B,B2,D:D),SUM(SUMIFS(C:C,B:B,B2,A:A,”<="&A2))-SUMIFS(D:D,B:B,B2,A:A,"<="&A2))

E2セルの右下隅をダブルクリックし、数式を他の行にコピーします。

5-5. データの入力と確認

日付、品目、入庫数、出庫数を入力し、在庫数が正しく計算されるか確認します。グラフやピボットテーブルを作成し、データの可視化も試してみましょう。

6. トラブルシューティング：よくある問題と解決策

在庫管理表の運用中に、様々な問題に直面することがあります。ここでは、よくある問題とその解決策を紹介します。

6-1. 在庫数が合わない

在庫数が合わない場合、以下の点を確認してください。

入力ミス：日付、品目、入庫数、出庫数の入力ミスがないか確認します。
計算式の誤り：在庫数計算式が正しく入力されているか、参照範囲が間違っていないか確認します。
入荷・出庫の記録漏れ：入荷や出庫の記録が漏れていないか確認します。
過去のデータの修正：過去のデータが修正された場合、現在の在庫数に影響が出ることがあります。修正履歴を確認し、必要に応じて再計算します。

6-2. 入力の手間が多い

入力の手間を減らすために、以下の工夫をしてみましょう。

プルダウンリストの活用：品目の入力にプルダウンリストを使用することで、入力の手間を減らし、入力ミスを防ぎます。
自動入力機能の活用：日付や品目を自動的に入力する機能（オートコンプリートなど）を活用します。
マクロの活用：繰り返し行う作業をマクロで自動化します。

6-3. データの整理が難しい

データの整理が難しい場合、以下の方法を試してください。

フィルタ機能の活用：特定の品目や日付のデータを抽出するために、フィルタ機能を使用します。
並べ替え機能の活用：日付や品目ごとにデータを並べ替えることで、データの整理を容易にします。
ピボットテーブルの活用：データの集計や分析にピボットテーブルを使用します。

7. 業務効率化のヒント：さらなる改善のために

在庫管理表の作成と運用に慣れてきたら、さらに業務効率を向上させるための工夫をしてみましょう。

7-1. 入出庫管理の自動化

バーコードリーダーやハンディターミナルを導入することで、入出庫管理を自動化することができます。これにより、データの入力ミスを減らし、作業時間を大幅に短縮できます。

7-2. 在庫アラートの設定

在庫数が一定の数値を下回った場合に、アラートを表示する機能を追加することで、欠品を防ぎ、スムーズな在庫管理を実現できます。

7-3. 複数人でのデータ共有

複数の人が同時にデータを入力・参照できるように、共有フォルダやクラウドストレージを活用します。これにより、情報共有がスムーズになり、チーム全体の業務効率が向上します。

8. まとめ：エクセル在庫管理表で業務をレベルアップ！

この記事では、エクセル初心者の方でも簡単に作成できる、効率的な在庫管理表の作り方を解説しました。シート構成、関数の活用、データの可視化、そして実践的なステップを通じて、あなたの業務効率を劇的に改善するヒントを提供しました。在庫管理表の作成と運用を通じて、正確な在庫状況を把握し、無駄を省き、より効率的な業務を実現しましょう。

在庫管理表の作成は、あなたのビジネスにおける重要な一歩です。この記事で紹介した方法を参考に、ぜひ実践してみてください。もし、さらに詳しいアドバイスや、あなたの状況に合わせたカスタマイズが必要な場合は、専門家への相談も検討しましょう。

もっとパーソナルなアドバイスが必要なあなたへ

この記事では一般的な解決策を提示しましたが、あなたの悩みは唯一無二です。
AIキャリアパートナー「あかりちゃん」が、LINEであなたの悩みをリアルタイムに聞き、具体的な求人探しまでサポートします。

今すぐLINEで「あかりちゃん」に無料相談する

無理な勧誘は一切ありません。まずは話を聞いてもらうだけでも、心が軽くなるはずです。

“`

コメントするコメントをキャンセル

お役立ちコンテンツ

転職体験談

転職ノウハウ

転職コラム