エクセル初心者
在庫管理をエクセルでテンプレート化したい！今まで手書きだったものからどうやって変えればいい？
棚卸表など確定申告の書類に応用できるから、在庫管理は早めにテンプレート化できるといいね！
Dr.オフィス
今回は、今まで手書きで行ってきた在庫管理について、エクセルのテンプレート化する方法をご紹介します。
エクセルで管理することで集計ミスや発注忘れを防ぎ、時短や効率化につながります！
- 品名リストと在庫管理表のシートを分けることで単価の変更などに柔軟に対応できる
- VLOOKUP関数や数式をあらかじめ挿入し、参照ミスや集計ミスを防ぐ
- 在庫数が減ったらセルの色が変わる「条件付き書式」を設定し、発注忘れを防ぐ
MOSエクセルエキスパートの資格を持つ私が詳しく解説するよ！
Dr.オフィス
一度テンプレートを作ることで、同じ形式のフォーマットを繰り返し使えるので便利です。
ファイルをコピーしたり、以前に入力したデータを手作業で削除する手間も省けます。
また、在庫管理以外にも様々な帳票をテンプレート化しておくと、事務負担の軽減につながります。
≫テンプレートを使いこなすヒントをまとめましたのでぜひご参照ください。
※本記事は『OS：Windows11』画像は『Excelのバージョン：Microsoft365』を使用しています。
目次
在庫管理に便利なテンプレートはエクセルのVLOOKUP関数で作ろう！
在庫管理のテンプレートをエクセルで作る場合、あらかじめ数式や関数を挿入しておくと集計が簡単です。
なかでも、在庫の種類が多く品番で管理する場合は、品番を入力するだけで商品名などが表示できる「VLOOKUP関数」を挿入しておくと便利です。
それでは、在庫管理のテンプレートを作成してみましょう。
作成するポイントは次のとおりです。
- 紙からデータへの移行期間を想定して、紙フォーマットとしても使いやすいようにシンプルな作りにする
- 確定申告の棚卸表に応用しやすいように、棚卸表に記載する項目を一元化しておく
- 発注タイミングが分かりやすいように、あらかじめ発注点を入力しておく
在庫管理表のフォーマットを作成する
まずは基本となるフォーマットを作成しましょう。
品名リストと在庫管理表はそれぞれ別のシートに作成してください。
シートを分けることで、単価の変更などに柔軟に対応することができます。
- STEP
品名リストを作る
発注点を入れておくのがポイント エクセルシートに品番・品名・単位・単価・発注点を入力します。
この項目のうち、品名・単位・単価は棚卸表で使用する項目です。
在庫がどれくらいになったら発注するのか、「発注点」をあらかじめ決めておくと毎回迷わずに済むのでお勧めです。
- STEP
品名リストとは異なるシートに在庫管理表を作る
A～G列はそれぞれ2行ずつ結合する - 項目名として「品番・品名・単位・単価・発注点・前月末・在庫数・月の合計・1日～31日の日付」を入力
- A～G列はそれぞれ2行ずつ結合する
- セルH4に「出庫」H5に「入庫」とそれぞれ入力する
在庫管理表にVLOOKUP関数を挿入する
次に、品番を入力すると品名が表示されるよう、VLOOKUP関数を活用しましょう。
併せて、在庫数を計算するための数式も挿入します。
- STEP
品番を入力すると品名が表示されるよう関数を挿入する
品番を仮入力すると作業しやすい - セルA4に任意の品番を入力
- セルB4「品名」を表示させるセルを選択
- 「関数の挿入」をクリック
- STEP
VLOOKUP関数を検索する
vlookupの途中までの入力でもOK - 「関数の検索」欄に「vlookup」と入力
- 「検索開始」ボタンを押す
- STEP
VLOOKUP関数を選択
候補からVLOOKUPを選ぶ - 候補の中からVLOOKUP関数を選択
- 「OK」をクリック
- STEP
検索値を選ぶ
検索値を設定する - 検索値欄に「A4」品番のセルを入力
- 「範囲」欄の矢印をクリック
- STEP
検索範囲を選ぶ
品名リストのシートを選ぶ - 「品名リスト」が入力されたシートを選択
- 「品名リスト」を範囲選択
- 矢印をクリック
- STEP
列番号、検索方法を設定
列番号などを設定する - 列番号に「2」を入力
- 検索方法に「FALSE」を入力
FALSEは完全に一致する値を検索する場合に設定するよ！
「FALSE」の代わりに「0」と入力してもOK！
Dr.オフィス
- STEP
絶対参照を設定
- STEP
品名が表示された
VLOOKUP関数で品名を表示できた VLOOKUP関数で品名リストから品名を参照させることができました。
B4に入力された数式をフィルハンドルでE4までコピーします。
- STEP
VLOOKUP関数の列番号を手修正
数式を手修正 VLOOKUP関数をコピーした場合、参照する列番号は2列目のままコピーされるため、すべてのセルに品名（えんぴつ）が表示されてしまいます。
セルごとに参照する列を変えるために、以下のとおり数式を手修正します。
- C列（単位）：3列目を参照
- D列（単価）：4列目を参照
- E列（発注点）：5列目を参照
- STEP
前月末の在庫数を入力
任意の数字を入力 セルF4に前月末の在庫数を入力します。
数式の挿入を作業しやすくするためのものなので、任意の数字で構いません。
- STEP
在庫数を計算する数式を入力
在庫数を計算 セルG4「在庫数」を表示するセルに「=F4+I5-I4」と入力し、在庫数を計算します。
- STEP
出庫合計を計算する
出庫数の合計を計算 - セルI4「出庫数の月合計」を表示するセルを選択
- 「Σ合計」をクリック
- セルJ4からAN4「1日～31日」を範囲指定しEnterを押す
- STEP
入庫合計に数式をコピーする
フィルハンドルで数式をコピー セルI4の数式をI5にコピーして完成です。
条件付き書式を活用して発注タイミングを把握しやすいようにする
一定数より数が小さい場合にセルの書式を変更する「条件付き書式」を設定し、発注タイミングが分かりやすいように工夫しましょう。
- STEP
条件付き書式を選択する
条件付き書式をクリック - セルG4「在庫数」を表示するセルを選択
- 「条件付き書式」をクリック
- 「セルの強調表示ルール」を押す
- 「指定の値より小さい」をクリック
- STEP
発注点を設定する
- STEP
発注点以下の在庫数の場合に条件付き書式が表示されることを確認
条件付き書式が表示されるか確認 セルF4の数字を変更し、条件付き書式が表示されるか確認します。
併せて、数式や条件付き書式を6行目以降にコピーします。
行の高さを大きめにすることで、手書き用のフォーマットとしても活用できるよ！
Dr.オフィス
エクセルファイルの種類をテンプレートとして保存
最後に、F12で「名前を付けて保存」ダイアログボックスを呼び出し、在庫管理表をテンプレートとして保存しましょう。
- STEP
テンプレートとして保存
ファイル形式をテンプレートに変更して保存 - ファイル名を変更
- ファイルの種類を「Excelテンプレート」に変更
- 「保存」ボタンを押す
エクセルの在庫管理テンプレートを無料入手するにはマイクロソフトクリエイトを活用
エクセルの在庫管理テンプレートは、≫マイクロソフトクリエイトのwebサイトで無料でダウンロードできます。
テンプレートを自作するのが難しい場合に活用し、時短につなげましょう。
- STEP
マイクロソフトクリエイトのwebサイトで任意のテンプレートをクリック
マイクロソフトクリエイトのwebサイトには在庫管理テンプレートが多数 マイクロソフトクリエイトのwebサイトにアクセスし、任意の在庫管理テンプレートをクリックします。
- STEP
在庫管理テンプレートをダウンロードする
テンプレートをダウンロードする 「ダウンロード」ボタンをクリックし、在庫管理テンプレートをダウンロードします。
- STEP
ダウンロードしたファイルをテンプレート形式で保存
ファイル形式をテンプレートに変更して保存 ダウンロードした在庫管理テンプレートは、いったんエクセルでファイルを開きます。
適宜レイアウトなどを修正したうえで、ファイルの種類を「Excelテンプレート」に変更して保存しましょう。
入出庫や備品管理、返品伝票も応用でテンプレート化できる
在庫管理テンプレートを応用して、入出庫や備品管理、返品伝票もテンプレート化することができます。
いずれも、加工したのちは≫エクセルファイルの種類をテンプレートにして保存しましょう。
入出庫表のテンプレートは1シート1品名だと管理しやすい
入出庫表は品名ごとに1シート作成することで管理がしやすく、備考欄にメモを残すことができるのでお勧めです。
ポイントは次のとおりです。
- 品名はVLOOKUP関数を使って別シートから参照させる
操作方法は≫在庫管理表にVLOOKUP関数を挿入するを確認 - 「条件付き書式」を使って在庫が一定数以下の際に書式を変更させる
設定方法は≫条件付き書式を活用して発注タイミングを把握しやすいようにするを確認 - 在庫数の数式は「=前の行の在庫数＋入庫数-出庫数」で求める
備品管理はマイクロソフトクリエイトのテンプレート活用がお勧め
備品管理のテンプレートは、特にこだわりがなければ≫マイクロソフトクリエイトのwebサイトからテンプレートをダウンロードして活用するのがお勧めです。
ダウンロード方法からテンプレートとしての保存方法まで≫エクセルの在庫管理テンプレートを無料入手するにはマイクロソフトクリエイトを活用をご確認ください。
返品伝票のテンプレート化はVLOOKUP関数を活用
返品伝票についても、VLOOKUP関数を活用することで効率の良いテンプレートが作れます。
ポイントは次のとおりです。
- 品名・単価はVLOOKUP関数を使って別シートから参照させる
操作方法は≫在庫管理表にVLOOKUP関数を挿入するを確認 - 金額は「=単価＊数量」で求める
エクセルで在庫管理をテンプレート化する場合のQ&A
Q
手書きでも見やすい在庫管理表を作成するコツは？
A
紙とデータが混在する場合は、可能な限りシンプルなレイアウトにしたうえで、行の高さを高めに設定しましょう。
字が潰れて読めなくなることが防げます。
ただ、ファイルの共有などにより、PC上で在庫管理表を共有することをお勧めします。
Q
在庫管理表の項目は何を設定すればいい？
A
会社の実情などにより異なりますが、確定申告に使用する「棚卸表」の項目を一元化しておくと申告の際に調べ直す必要がなくて便利です。
品名・単位・単価は棚卸表で使用します。
Q
商品数が多い場合の在庫管理はエクセルでできますか？
A
品名リストと在庫管理のシートを分けることにより、ある程度の商品数でも対応可能です。
≫在庫管理表のフォーマットを作成する方法をご確認ください。
商品数が多すぎてVLOOKUP関数が遅くなる場合は、品名リストの参照方法を≫INDEX関数とMATCH関数のクロス抽出に変更しましょう。
在庫管理はエクセルのテンプレート活用で手書きから卒業しよう！
在庫管理をエクセルでテンプレート化する際の手順や、無料でテンプレートをダウンロードできる方法について解説しました。
エクセルの関数や条件付き書式を設定することで、集計ミスや発注ミスが防げますので試してみて下さい。
それではテンプレートを作成するポイントをおさらいしましょう。
- 品名リストと在庫管理表のシートを分けることで単価の変更などに柔軟に対応できる
- VLOOKUP関数や数式をあらかじめ挿入し、参照ミスや集計ミスを防ぐ
- 在庫数が減ったらセルの色が変わる「条件付き書式」を設定し、発注忘れを防ぐ
手書きの在庫管理の状態からエクセルでテンプレート化するには、最初は大変かもしれません。
ですが、集計の自動化など、テンプレート化でかなりの時短につながりますので、ぜひ、チャレンジしてくださいね！
また、在庫管理以外にも≫使いこなすと便利なテンプレートを多数まとめました。
事務負担軽減の参考にしていただけたら幸いです。