【自動化Excel】在庫管理表を作成して、安全在庫数を保つ方法

【目次】
0:00 エクセルで在庫を管理して、安全在庫数を保つ方法
0:05 安全在庫とは
0:23 完成形の確認
1:53 表の土台となる項目名と仮の情報を入力する
5:57 SUMIFS関数を使って現在の在庫数を算出する
9:25 現在の在庫数から安全在庫数を引いて、発注が必要になるまでの数を算出する
10:10 表にテーブルを設定するメリット
12:09 表にテーブルを設定する
14:48 SUMIFS関数を使ってテーブル名と列名を参照(構造化参照とは)して現在の在庫数を算出する
18:26 現在の在庫数が安全在庫数より少なくなったらセルの背景色を赤色にして、発注が必要なことを知らせる(条件付き書式)
20:28 商品一覧表にある商品以外は入力できないようにする(データの入力規則・リスト)
22:57 アクションの欄に許可した選択肢以外は入力できないようにする(データの入力規則・リスト)
25:55 アクションの選択肢によって、入庫数と出庫数の欄を入力できないようにする(データの入力規則・ユーザー設定)
31:07 入力ができないセルを灰色にすることで、入力できないことがひと目で分かるようにする(条件付き書式・数式を使用して、書式設定するセルを決定)
35:59 現在、設定されている条件付き書式を閲覧・編集・削除する方法
36:44 日付データの入力欄に日付データ以外は入力できないようにする(データの入力規則・日付)
38:07 仮のデータを削除する


チャンネル登録はこちら
   / @consul-miya  

90円で入れる応援メンバーシップはこちら
   / @consul-miya  

人生の質が上がるラジオチャンネルの登録はこちらから
   / @lifehack-radio  

Twitter
  / lifehacker_miya  


【人気の再生リスト】
・ 効率を爆上げするショートカットキー&テクニック(Windows編)(   • 効率を爆上げするショートカットキー&テクニック(Windows編)  )


【使用素材】
●イラスト
いらすとや(https://www.irasutoya.com/)

●効果音
効果音ラボ(https://soundeffect-lab.info/)

●BGM
Track: Weero & Mitte - Our Dive [NCS Release]
Music provided by NoCopyrightSounds.
Watch:    • Weero & Mitte - Our Dive | Future Hou...  
Free Download / Stream: http://ncs.io/OurDiveYO

●EDテーマ
Track: Cartoon - Your Stories (feat. Koit Toome) [NCS Release]
Music provided by NoCopyrightSounds.
Watch:    • Cartoon, Jéja - Your Stories (feat. K...  
Free Download / Stream: http://ncs.io/YourStoriesYO


この動画ではエクセルで在庫を管理して、安全在庫数を保つ方法を紹介します。

安全在庫というのは、在庫管理を行う上で、欠品を防ぐ最低限の在庫量の事です。

今から紹介する方法で、在庫を管理することで、安全在庫数を保ち欠品を防ぐことができます。

現在の在庫数を一覧で表示することもできるので、棚卸しにも使うことができます。

まずは完成形を確認します。

この在庫管理表には2つの表を使います。

1つ目は商品の一覧表です。ここに、全ての商品名を一覧化し、その安全在庫数を設定します。

2つ目は入出庫履歴の管理表です。

左から、
入出庫履歴を入力した日付
入出庫した商品名
どういうアクションを行ったか
入出庫した数
を入力します。

いつどのように入出庫したかを記録することで、あとから見返すことができるので、トラブル防止に繋がります。

商品名の欄は商品の一覧表に入力した、商品名リストから選択して入力することができます。

入出庫履歴を入力していくと、商品一覧の表には、自動で現在の在庫数や、発注が必要になるまでの数が算出されるので、棚卸しなどに使うこともできます。

また、現在の在庫数が安全在庫数より少なくなった場合は、セルの色が赤色に変わり、発注が必要なことを知らせてくれるようになります。

また、現在の在庫数が安全在庫数より少なくなった商品のみ絞り込み表示することができるので、発注が必要な商品のみを一覧で表示することもできます。

更に動画の後半では、意図しない情報は入力できないように、入力制限をかけたり、見やすい表のデザイン方法も紹介します。

それでは早速作成していきましょう。

動画
商品名リストから選択して入力する
安全在庫数を割った商品のみ絞り込む
項目名と仮データ(商品一覧表)
まずは、商品一覧表から作成します。

表の土台となる、項目名の欄を入力していきます。

右へ情報を入力していく際は、Tabキーを押すと、セルの入力を確定して選択セルを右に移動することができるので、素早く入力することができます。

列幅が足りず情報が表示しきれない場合はその列名の右側の境目をダブルクリックすると、情報量に合わせて自動で、列幅を調整することができます。

次に、各挙動を確認するために、仮で情報を入力していきます。

在庫数と発注までの欄は自動で算出されるように後ほど設定するので、今は空けておきます。
項目名と仮データ(入出庫履歴)
次に、入出庫履歴の表を作成します。

左下のシートタブの横にあるプラスボタンをクリックするか、Shiftキーを押しながらF11キーを押して、新しいシートを作成します。

シート名をダブルクリックして、シート名を変更しておきます。

シートはドラッグで並び替えることができます。

次に、入出庫履歴の表の土台となる、項目名の欄を入力していきます。

次に、各挙動を確認するために、仮で情報を入れていきます。

商品名は先ほど作成した、商品一覧表からコピーします。商品一覧のシートタブをクリックして、商品一覧のシートを開きます。

商品一覧をドラッグして選択した状態で、Ctrlキーを押しながらCキーを押してコピーします。CopyのCと覚えると覚えやすいです。

入出庫履歴のシートに戻り、貼り付けたい先頭のセルを選択した状態で、Ctrlキーを押しながらVキーで貼り付けます。

日付もコピーします。Ctrlキーを押しながらCキーでコピーして、貼り付けたいセルをドラッグで選択したらCtrlキーを押しながらVキーで貼り付けます。

アクション欄や入個数欄も同じく、Ctrlキーを押しながらCキーでコピーして、貼り付けたいセルをドラッグで選択したらCtrlキーを押しながらVキーで貼り付けます。

後ほど
入庫数が正しく足されているかを確認するために、同じ商品を2回入庫したことにしておき、
出庫数が正しく引かれているかと、現在の在庫数が安全在庫数より少なくなった場合にセルが赤くなる挙動を確認するために、現在の在庫数が安全在庫数より少なくなるように出庫した商品を作っておきます。
保存
ここで一旦Ctrlキーを押しながらSキーを押して、ファイルを保存します。SaveのSと覚えると覚えやすいです。

任意のファイル名と保存場所を指定しておきます。
在庫数の算出
次に商品一覧表の在庫数の欄に、現在の在庫数が自動で算出されるように数式を作成します。
入庫数
まずは、各商品の入庫数を足します。

=SUMIFS(入出庫履歴!D$2:D$12,入出庫履歴!B$2:B$12,商品一覧!A2)

と入力したら、Enterで確定します。

SUMIFS関数は、指定した条件を満たす数字だけを全て足すことができる関数です。

1つ目の引数には、合計する数字の範囲
2つ目の引数には、条件を判定する範囲
3つ目の引数には、条件
を入力します。

ここでは、
入出庫履歴シートのB2セルからB11セルの範囲から
商品一覧シートのA2セルと同じである行の
入出庫履歴シートのD2セルからD11セルの数字を全て足す
という意味になります。

数式を入力できたら、そのセルを選択した状態で、右下の小さい四角をダブルクリックして、他の行にも適用します。

条件に設定した、A2は相対参照なので、
C2セルは、A2セルに入力されている「クリアファイル」かどうかを条件とし、
C3セルは、A3セルに入力されている「コピー用紙 A4」かどうかを条件とし、
C4セルは、A4セルに入力されている「コピー用紙 A3」かどうかを条件とする
というように続いていきます。

相対参照について詳しく知りたい方は、他の動画で紹介してるので参考にしてみてください。
出庫数
次に、今算出した入庫数から、出庫数を引いて、現在の在庫数を算出します。

数式を入力したセルの一番上のセルを選択した状態で、F2キーを押して、編集モードにしたら、

-

と入力して、先ほどと同じようにSUMIFS関数を使って、

SUMIFS(入出庫履歴!E$2:E$12,入出庫履歴!B$2:B$12,商品一覧!A2)

と入力して、Enterで確定します。

これで、現在の在庫数が自動で算出されるようになりました。

数式を入力できたら、そのセルを選択した状態で、右下の小さい四角をダブルクリックして、他の行にも適用します。
発注まで
次に、発注が必要になるまでの数が自動で算出されるように、数式を作成します。

発注が必要になるまでの数は、在庫数-安全在庫数で算出することができるので、

D2セルに=C2-B2と入力します。

Enterで確定したら、そのセルを選択した状態で、右下の小さい四角をダブルクリックして、他の行にも適用します。

これで、発注が必要になるまでの数が自動で算出されるように、数式を作成することができました。
保存
ここでCtrlキーを押しながらSキーを押して、ファイルを保存しておきます。
テーブル
次に、入出庫履歴の表にテーブルを設定します。

テーブルというのは日本語で表という意味です。表にテーブルを設定することで、いくつかのメリットがあります。

テーブルを設定するメリット
①表が縞々のデザインになって、見やすくなる
②データの並び替えや絞り込みができるようになる
③データを追加した際に数式が自動で追加される
④データを追加した際にセルの参照範囲が自動で広がる

1つ目は、表が縞々のデザインになって、見やすくなることです。デザインは豊富なテンプレートの中から選択するだけで、簡単に変更することができます。

2つ目は、データの並び替えや絞り込みができるようになることです。入出庫履歴の表であれば、期限順に並び替えたり、特定の商品のみ絞り込んだりして活用することができます。


#家にいながら学ぶ
#スキルアップ
#パソコン