今回のテーマは在庫管理表。リアルに在庫の状況を把握し、必要に応じてタイミングよく発注業務につなげるなどなかなか悩ましいとお悩みの方に。エクセルならシンプルで機能的な在庫管理表を作ることができます。今回は、取扱商品の入出庫記録と在庫管理表をテーブル機能で作成し、SUMIFS関数やIF関数を使って求めるべく在庫管理表の作り方の一例をご紹介します。各種の設定や関数の入力など、表作成から順を追って解説します。

 

在庫管理表と入出庫記録シート

在庫管理表と入出庫記録シートの完成図はこちらです。

入出庫管理シートで入出庫の記録を入力すると、
リアルタイムで在庫状況と発注のタイミングがわかります。

在庫管理と発注業務にとても便利で役立ちます。

在庫管理表の完成図

●在庫管理表の完成図は次の通りです。

  • 5種類のカップラーメンの在庫管理表を用意しました。
  • 各商品の入出庫の記録からSUMIFS関数を使って「累計入庫」「累計出庫」を計算します。
  • 累計入庫、累計出庫から在庫数と在庫率を計算します。
  • 指定した在庫率（ここでは30%）を下回ると発注列に「要発注」と表示するようにIF関数を使って設定します。
  • 在庫の状況がわかりやすくするために、在庫率には「スケールバー」の条件付き書式を設定します。

入出庫記録シートの完成図

●入出庫記録シートの完成図は次の通りです。

  • 下図のような取扱い商品の入出庫記録表を用意します。
  • この記録のデータをもとに在庫管理表で在庫の管理を行います。

在庫管理表を作成する

在庫管理表をエクセルの「テーブル機能」を使って作成してみましょう。

在庫管理表のシート名を作成する

●在庫管理表のシートを作成します。

  • 「Sheet1」タブをWクリックしてタブ内にカーソルを入れます。
  • 「在庫管理表」と入力し「Enter」キーを押して確定します。
  • タブをマウス右クリックして「シート見出しの色」から「テーマの色」を選んでシート見出しに色を付けてより分かりやすくしておきます。

テーブル機能を使って在庫管理表を作成する

●タイトルを作成し商品名を入力します。

  1. タイトル「在庫管理表」とします。（ここではセルB2~G2を塗りつぶしタイトルを強調しています）
  2. 「商品名」の見出しと商品名を入力します。
  3. B列とC列の間をダブルクリック、入力した文字列の長さに自動的に列幅を調整します。

 

●テーブル機能を使ってテーブルを作成します。

  1. セルB4の「商品名」にカーソルを合わせます。
  2. リボンの「挿入」タブをクリック。
  3. 「テーブル」グループの「テーブル」をクリック。
  4. 「テーブルの作成」のダイアログが表示されます。
  5. 自動的にテーブルに変換する範囲が指定されます。
  6. 「ＯＫ」ボタンを押します。

 

●テーブル機能が設定され自動的に絞り込みフィルターも設定されました。

 

●見出しを入力し「Tab」キーを使ってテーブルを完成します。

  1. 続いてセルC4の見出しに「累計入庫」と入力し「Tab」キーを押します。
  2. カーソルはセルD4に移動し「累計入庫」列のテーブルが作成されます。
  3. 続いてD4~G4を同様にテーブルを作成します。

 

●列幅を調整します。

  1. C列~G列の列番号をドラッグします。
  2. マウス右クリックで「列の幅」をクリック。

 

  1. 「列幅」ダイアログが表示されます。
  2. 「列幅」にここでは「10」と入力します。
  3. 「OK」ボタンを押します。

 

●在庫管理表の元は完成です。

  • 「テーブルツールデザイン」を使って「テーブルスタイル」グループでテーブルの色を変えることができます。（後ほど詳細をご紹介します）
  • 入出庫記録シートの作成後に集計用の関数等を入力します。

入出庫記録シートを作成する

入出庫記録シートを「テーブル機能」を使って作成してみましょう。

入出庫記録シートのシート名を作成する

●在庫管理表のシートを作成します。

  • 「Sheet1」タブをWクリックしてタブ内にカーソルを入れます。
  • 「在庫管理表」と入力し「Enter」キーで確定します。
  • タブをマウス右クリックして「シート見出しの色」から「テーマの色」を選んでシート見出しに色を付けてより分かりやすくしておきます。

テーブル機能を使って入出庫記録シートを作成する

●タイトルを作成し見出しを入力します。

  1. タイトル「入出庫記録」とします。（ここではセルB2~F2まで塗りつぶしをしてタイトルを強調しています）
  2. 見出しを入力します。ここでは、日付、商品名、入出庫、数量、備考としました。
  3. 日付セルにカーソルを合わせ、リボンの「挿入」タブをクリック。
  4. 「テーブル」グループの「テーブル」をクリック。
  5. 「テーブルの作成」ダイアログが表示されます。
  6. 「テーブルに変換するデータ範囲を指定してください」に見出し範囲が自動的に範囲指定されます。
  7. 「先頭行をテーブルの見出しとして使用する」のチェックボックスにチェックを入れます。
  8. 「OK」ボタンを押します。

 

●入出庫記録シートのテーブル設定が完了しました。

 

●テーブルの色をタイトルに合わせて変更します。

  1. 表の中にカーソルを合わせ、リボンに表示の「テーブルツール・デザイン」タブをクリック。
  2. 「テーブルスタイル」の「▼」マークをクリック。
  3. 表示されたスタイルの中から選択します。

 

●入出庫記録のスタイルが変更し完成しました。

入出庫記録の商品名セルにドロップダウンリストを設定する

●次に、リスト形式で商品を選べるように商品名の各セルに「プロダウンリスト」を設定します。

  1. 「ドロップダウンリスト」を設定する商品名セルC5を選択します。
  2. リボンの「データ」タブをクリック。
  3. 「データツール」グループにある「データの入力規則▼」をクリック。
  4. 「データの入力規則」をクリック。

 

●データの入力規則ダイアログが表示されます。

  1. 「設定」タブをクリック。
  2. 「入力値の種類」の「▼」マークをクリックして「リスト」を選択。
  3. 「空白を無視する」「ドロップダウンリストから選択する」のチェックを確認。
  4. 「元の値」の枠にカーソルを入れます。
  5. 「在庫管理表」シートタブをクリック、商品名をドラッグして範囲指定します。
  6. 範囲指定が自動的に入力されます。
  7. 「OK」ボタンを押します。

 

●ドロップダウンリストが設定されました。

  • ドロップダウンリストを設定したセルにカーソルを合わせると「▼」マークが表示されます。
  • 「▼」マークをクリック。
  • 範囲指定の商品がドロップダウンリストで表示され選ぶことができます。
  • データテーブル機能では、データを追加すると自動的にドロップダウン設定も引き継がれますのでいちいち設定をする必要がなく便利です。

入出庫記録の入出庫セルに「入庫」「出庫」のドロップダウンリストを設定する

●入出庫セルD5に「入庫」「出庫」のドロップダウンリストを設定します。

  1. セルD5にカーソルを合わせます。
  2. リボンの「データ」タブをクリック。
  3. 「データツール」グループの「データの入力規則」の「▼」マークをクリック。
  4. 「データの入力規則」をクリック。

 

■「データの入力規則」ダイアログが表示されます。

  1. 「設定」タブをクリック。
  2. 「入力値の種類」の「▼」マークをクリックし「リスト」を選択。
  3. 「空白を無視する」「ドロップダウンリストから選択する」のチェックボックスのチェックを確認。
  4. 「元の値」の枠内に「入庫」「,」（カンマ）「出庫」と入力。
  5. 「OK」ボタンを押します。

 

■「入出庫」セルD5に「入庫」「出庫」のドリルダウンリストが設定されました。

入出庫記録シートの列幅を調整する

入出庫記録シートの列幅を調整する方法をご紹介します。

●日付、入出庫、数量の列幅を同じ幅で調整します。

  1. 「日付」列の列番号「B」をクリック。
  2. 「Ctrl」キーを押しながらD列の列番号「D」をクリック。
  3. 「Ctrl」キーを押しながらE列の列番号「E」をクリック。
  4. マウス右クリックで表示のメニューから「列の幅」をクリック。

 

●列幅をここでは「12」に設定します。

  • 「OK」ボタンを押して列幅を調整します。

 

●商品名列の列幅を文字列に合わせて自動調整します。

  1. C5セルの「▼」マークをクリックしてドリルダウンリストを表示、文字列の長い商品を選びます。（ここでは、日清カップヌードルを選択します）
  2. 列番号「C」と「D」の間をダブルクリック。

 

●文字の長さに列幅が自動調整されました。

 

●備考の列幅を調整します。

  • 備考列の列番号「Ｆ」をクリック。
  • マウス右クリックで「列の幅」をクリック。
  • 「列幅」をここでは30で設定、「OK」ボタンを押します。

入出庫記録シートに入力する際に日本語入力の自動切換えを設定をする

必ず必要な設定ではありませんがよりストレスなく入力を行うために、入出庫記録シートに記録を入力する際に自動的に日本語入力が切り替わるように設定します。見出しの順に「日付」は日本語入力モードがオフ、「商品名」と「入出庫」はプルダウンメニューで選択。「数量」は日本語入力モードがオフ、備考はオンに自動に切り替わるように設定をする方法をご紹介します。

 

●日付列と数量列は日本語入力モードをオフに設定する。

  1. 日付列番号の「B」をクリック。
  2. 「Ctrl」キーを押しながら数量列の列番号「Ｅ」をクリックして列幅を選択します。
  3. リボンの「データ」タブをクリック。
  4. 「データツール」グループの「データの入力規則」の「▼」マークをクリック。
  5. 「データの入力規則」を選択。
  6. 「データの入力規則」ダイアログが表示され「設定」タブをクリック。
  7. 「日本語入力」の「▼」マークをクリック、「オフ（英語モード）」を選択。
  8. 「OK」を押して設定完了です。日付セル、数量セルにカーソルが移動すると、日本語入力が自動的にオフモードに変更になり半角英数字で入力することができます。

 

●備考列の日本語入力モードを「ひらがな」に設定する。

  1. リボンの「データ」タブ、「データツール」グループの「データの入力規則」とクリックして「データの入力規則」ダイアログが表示されます。
  2. 「設定」タブをクリック。
  3. 「日本語入力」の「▼」マークをクリック、「ひらがな」を選択。
  4. 「OK」ボタンを押して設定完了です。備考のセルにカーソルが移動すると、日本語入力が自動的にひらがなモードに変更になります。

 

入出庫記録シートを実際に入力します

完成図を参考に、実際に各見出し列のセルに入力をしてみましょう。

●日付を入力する。

  • 『「Ctrl」キー + 「;」（セミコロン）』のショートカットキーを使うと「2022/01/06」と本日の日付が入力できるので便利です。
  • ここでは「1-6」とハイフンを使って入力し、「1月6日」と入力する方法を使います。
  • セルB5に、「1-6」と入力し「Tab」キーを押します。
  • セルB5は「1月6日」と入力されます。

 

●商品名を入力する。

  • 『「Alt」キー + 「↓」キー』を押すとマウスに持ち替えることなく素早くプルダウンメニューを表示することができます。
  • 「↓」キーで商品を選択し「Enter」キーを押します。
  • ここでは、「日清カップニュードル」を選択します。
  • セルC5に「日清カップヌードル」が入力されます。

 

●入出庫を入力する。

  • 『「Alt」キー + 「↓」キー』を押すと、「入庫」「出庫」が表示されます。
  • 「↓」キーで商品を選択し「Enter」キーを押します。
  • ここでは、「入庫」を選択します。
  • セルD5に「入庫」が入力されます。

 

●数量を入力する。

  • セルが数量列のセルに来ると、日本語入力は設定どおり自動的にオフに切り替わるので便利です。
  • ここでは、「50」と入力します。
  • 引き続き、完成図を参考にデータを入力し入出庫記録を完成します。

 

●テーブル機能のメリット。

  • テーブル機能を使うと、表の管理が簡単になります。
  • 標準でフィルターボタンが追加され、1行おきに色分けされます。
  • 行や列の追加に応じてテーブルの範囲が拡張されるため、書式やフィルター範囲をいちいち再設定する必要がなくなります。

在庫管理表を作成する

入出庫記録からSUMIFS関数を使って、累計入庫と累計出庫を集計します。少し複雑なので、ここでは「関数の挿入」ダイアログボックスを使ってSUMIFS関数を入力します。また、リアルな在庫数の確認と、在庫率からデータバーを使ってよりリアルで在庫の状況を確認できるように設定します。さらに、発注のタイミングをIF関数を使って見逃さない方法を実践します。

SUMIFS関数を使って累計入庫を集計する

■SUMIFS関数を使って入出庫記録から累計入庫を集計する。

  1. セルC5にカーソルを合わせます。
  2. 「関数の挿入」をクリック。
  3. 「関数の挿入」ダイアログが表示されます。
  4. 「関数の検索」に「SUMIFS」（小文字でもOK）と入力。
  5. 「検索開始」ボタンを押す。
  6. 「関数名」に「SUMIFS」が表示されクリック。
  7. 「OK」ボタンを押します。

 

●「関数の引数」ダイアログが表示されます。

  • セルC5に「=SUMIFS()」と数式が入力されます。

 

●「合計対象範囲」を指定します。

  1. 関数の引数ダイアログの「合計対象範囲」にカーソルが挿入されています。
  2. 「入出庫記録」を開き「数量」のセル「E5:E13」をドラッグ。
  3. 「合計対象範囲」に指定した範囲が入力されます。（=50,36,35‥）と表示されて指定した範囲を確認できます。

 

●「条件1」を指定します。

  1. 関数の引数ダイアログの「条件1」をクリックしてカーソルを挿入します。
  2. 在庫管理表の「商品名」、セル「B5」を指定します。
  3. 関数ダイアログの「条件1」に指定した引数[@商品名]が入力されます。（=”日清カップヌードル”）と表示されているので選択した商品名を確認できます。

 

●「条件範囲1」を指定します。

  1. 関数の引数ダイアログの「条件範囲1」をクリックしてカーソルを挿入します。
  2. 入出庫管理の「商品名」、セル範囲「C5:C13」を範囲指定します。
  3. 関数ダイアログの「条件範囲1」に指定した引数「テーブル13[商品名]」が入力されます。

 

●「条件2」と「条件範囲2」を指定します。

  1. 関数の引数ダイアログの「条件2」をクリックしてカーソルを挿入します。
  2. 「入庫」と入力します。
  3. 関数の引数ダイアログの「条件範囲2」をクリックしてカーソルを挿入します。
  4. 入出庫記録の「入出庫」、セル範囲「D5:D13」を範囲指定します。
  5. 関数ダイアログの「条件範囲2」に指定した引数「テーブル13[入出庫]」が入力されます。
  6. 条件1（日清カップヌードル）× 条件2（入庫） = 50 の計算結果が表示されます。
  7. 「OK」ボタンを押します。

 

●累計入庫の列すべてに自動的に入力した数式が反映されました。

  • テーブル機能が設定されているので、関数式を1回入力するだけで在庫管理表の累計入庫の数式はすべて入力されます。
  • テーブル機能のメリットのひとつです。

SUMIFS関数を使って累計出庫を入力する

累計出庫を上記同様SUMIFS関数を使って集計します。ここでは、直接SUMIFS関数を入力する方法をご紹介します。

■SUMIFS関数を使って入出庫記録から累計出庫を集計する。

  1. セルD5に「=sum」と入力します。
  2. 「sum」ではじまる関数が表示されます。
  3. 「SUMIFS」を選択します。
  4. 「Tab」キーを押します。

 

■セルに「=SUMIFS(」と入力されました。

  • SUMIFS関数の構文は「=SUMIFS(合計対象範囲,条件範囲1,条件1,条件範囲2,条件2…）」です。
  • 構文の各引数を直接指定していきます。

 

●合計対象範囲,条件範囲1,条件1,条件範囲2,条件2,を指定します。

  • 各引数をマウスで指定していきます。
  • 引数は「,」（カンマ）で区切ります。
  • 注）条件1の商品名の指定は在庫管理表のセルB5を指定します。
  • 入力を終えたところで「Enter」キーを押します。

 

●累計出庫の列すべてに自動的に入力した数式が反映されました。

在庫を計算する

●セルE5に累計入庫数-累計出庫数を入力する。

  1. セルE5をアクティブ（Wクリックして入力可能な状態）にします。
  2. セルE5に「=」と入力します。
  3. マウスで「C5」をクリック。
  4. 「-」を入力。
  5. マウスで「D5」 をクリック。
  6. セルE5に「=[@累計入庫]-[@累計出庫]と入力されます。

 

●「Enter」キーを押します。

  • セルE5に入力した計算式がコピーされます。
  • 各商品別の在庫数が計算されます。

在庫率を計算する

●セルF5に「E5/C5」を入力して在庫率を計算します。

  1. セルF5をアクティブにします。
  2. セルF5に「=」と入力します。
  3. マウスで「E5」をクリック。
  4. 「/」を入力。
  5. マウスで「C5」 をクリック。
  6. セルF5に「=[@在庫]/[@累計入庫]と入力されます。

 

●「Enter」キーを押します。

  • セルF5に入力した計算式がコピーされます。
  • 各商品別の在庫率が計算されます。

 

●書式を「％」に小数点1位まで表示します。

  • セルF5~セルF9をドラッグして範囲選択します。
  • リボンの「ホーム」タブをクリック。
  • 「数値」グループの「%」をクリックしてパーセント表示にします。
  • 「小数点以下の表示桁数を増やす」を1回クリックして小数点1位まで表示させます。

データバーを設定する

●より在庫状況を分かりやすくするためにデータバーを設定します。

  1. データバーを設定するセルを範囲指定します。
  2. リボン「ホーム」タブをクリック、「条件付き書式」をクリック。
  3. 「データバー」をクリック。
  4. 「塗りつぶし（グラデーション）」からデータバーを選択して範囲指定したセルにデータバーが設定されました。

IF関数を使って在庫率が30%を割り込むと「要発注」と自動的に判断し表示する

●IF関数を入力します。

  1. セルG5に「=if」を入力します。
  2. IFではじまる関数が表示され、「IF」関数を選択します。
  3. セルG5に「=if」と入力されました。
  4. 「Tab」キーを押します。

 

●数式を入力します。

  • IF 関数の構文は、「=IF(倫理式,真の場合,偽の場合」です。
  • F5が30%以下ならば「要発注」（「”要発注”」ダブルクォーテーションを使います）、そうでなければ「空欄」（「””」ダブルクォーテーションを続けて空欄になります）を返す式とします。
  • 「=IF(F5<=0.3,”要発注”,””」と入力します。（この時点では「）」は入力する必要はありません）

 

●「Enter」キーを押します。

  • IF関数の数式がセル「G5~G9」にコピーされ計算結果が表示されます。
  • 在庫率30%を割り込んだ商品は、「要発注」が表示されます。
  • 入出庫を記録するとリアルで発注のタイミングがわかりますのでとても便利で役立ちます。

今回のまとめ

というわけで、今回は以上です。

今回のテーマは「エクセルを使ってシンプルで機能的な在庫管理表を作る」です。

エクセルのテーブル機能を使って、
入出庫記録シートと在庫管理表を作成。

入出庫管理シートのデータから、
累計入庫数をSUMIFS関数で集計。

在庫の状態がリアルで把握できるようデーターバーの設置も取り入れました。

また、IF関数を利用してタイミングよく、
「要発注」を知らせる機能も取り入れての作成手順。

 

シンプルで機能的な在庫管理表、入出庫記録シートと合わせて作成の手順のご紹介でした。

いかがでしたでしょうか？

最後まで読んでくださりありがとうございます。

少しでもヒントになればうれしく思います。