Excelで在庫エイジングレポートを作成する4つのステップ【初心者向け完全ガイド】

Excelで在庫エイジングレポートを作成する具体的な手順をお探しの方に、この記事はきっと役立ちます。在庫エイジングレポートとは、各商品が在庫として保管されてから売り切れるまでの期間を示すものです。この期間を分析することで、商品を「回転の速い在庫」「回転の遅い在庫」「滞留在庫」などに簡単に分類できます。

それでは、本編を見ていきましょう。

ワークブックのダウンロード

Excelで在庫エイジングレポートを作成する4つのステップ

在庫エイジングレポートの作成には、基本となる表の骨組みを作り、数式で必要な値を計算し、最後にデータセットをピボットテーブルへ変換してレポートを見やすく整えるといった工程が必要です。以下では、これらの手順を4つのステップに分けて詳しく解説します。

ここでは Microsoft Office 365 を使用していますが、お使いのバージョンに合わせて操作してください。

ステップ1:基本となるアウトラインの作成

まず、在庫エイジングレポートとその元データの基本的なアウトラインを作成します。

➤ 下の図のような在庫エイジング用の基本レポートを、「Inventory」シートに作成します。

この表には、製品ID、製品名、単価、数量、有効期限の列があり、それぞれご自身の在庫データを入力できます。

さらに計算を行うために、合計金額、残日数、状態という3つの列を追加します。

次に、「Category」シートに、商品の状態を分析するためのもうひとつのアウトラインを作成します。

➤ 残日数に応じて在庫の状態・鮮度を判定できるよう、カテゴリの一覧表を作成します。この範囲には「limit」という名前を付けておきます。

ステップ2:数式を使って在庫エイジングレポートを作成する

➤ 商品ごとの合計金額を計算するために、セル E4 に次の数式を入力します。

=C4*D4

ここで、C4 は商品「Apple」の単価、D4 はその数量です。

➤ Enter キーを押した後、フィルハンドルを下方向へドラッグします。

これで、「合計金額」列にすべての商品の合計額が表示されます。

➤ 次に、本日の日付(19-05-22)から商品の期限までの残り日数を計算します。

=IF((F4-TODAY())<0,0,F4-TODAY())

ここで、F4 は商品の有効期限であり、TODAY() は本日の日付(19-05-22)を返します。

両者の差がマイナスになった場合、IF関数は 0 を返します。差がプラスの場合は、その差が残日数として表示されます。

➤ Enter キーを押して、フィルハンドルを下へドラッグします。

これで、本日時点での各商品の残日数が求められます。

➤ 続いて、次の数式を使い、「Category」シートの値を参照して残日数に基づく商品の状態を判定します。

=VLOOKUP(G4, limit,2, TRUE)

ここで、G4 は検索値、limit は検索範囲として事前に名前を付けた範囲、2 は列番号、TRUE は近似一致を指定する引数です。

➤ Enter キーを押して、フィルハンドルを下へドラッグします。

これで、「状態」列に各在庫の状態が表示されます。

ステップ3:ピボットテーブルを作成して在庫エイジングレポートを仕上げる

このステップでは、データを見やすく整理し、在庫の経過状況を明確に示すためにピボットテーブルを作成します。

➤ 「挿入」タブ >> 「ピボットテーブル」を選択します。

すると、「テーブルまたは範囲からピボットテーブル」ダイアログボックスが開きます。

➤ 「Inventory」シートから対象の表の範囲を選択し、「OK」をクリックします。

新しいシートが開き、「ピボットテーブル」と「ピボットテーブル フィールド」の2つの領域が表示されます。

➤ 製品ID と 製品名 を「行」エリアへ、数量 と 合計金額 を「値」エリアへ、状態 を「列」エリアへドラッグします。

「値」エリア内のフィールド名を短くしたい場合は、次のようにカスタマイズできます。

➤ 「数量の合計」フィールドのドロップダウン矢印をクリックし、「値のフィールド設定」を選択します。

「値のフィールド設定」ダイアログボックスが開いたら、

➤ 「ユーザー設定の名前」ボックスに任意の名前(例:Q)を入力して「OK」をクリックします。

➤ 同様に、「合計金額の合計」も見やすくするために P に変更します。これで「値」エリアに新しいフィールド名が2つ表示されます。

下の画像は、商品の状態を数量・価格のヘッダーとして整理した、データ範囲のピボットテーブルです。

ステップ4:ピボットテーブルを装飾する

最後のステップは、ピボットテーブルをより見栄えよく仕上げるための装飾です。

この表では合計値が不要なため、簡単に削除できます。

➤ 「ピボットテーブル分析」タブ >> 「オプション」ドロップダウン >> 「オプション」を選択します。

「ピボットテーブル オプション」ダイアログボックスが開いたら、

➤ 「集計とフィルター」タブを選択し、「総計」のチェックボックスをオフにします。

➤ 最後に「OK」をクリックします。

これで、行と列から合計値が削除されました。

➤ さらに、「デザイン」タブから好みのテーマを選択すれば、表のデザインも変更できます。

以上で、在庫エイジングレポートの完成です。

まとめ

この記事では、Excelで在庫エイジングレポートを作成するための手順を解説しました。皆さんの業務に役立てば幸いです。ご質問やご意見があれば、ぜひコメント欄でお聞かせください。

関連記事

• Excelで収支レポートを作成する方法(具体例付き)
• Excel VBAでPDF形式のレポートを生成する3つのテクニック
• Excelで営業用MISレポートを作成する手順
• ExcelデータからPDFレポートを作成する4つの方法
• ExcelでMISレポートを作成する実例ガイド