エクセルで仕入れ管理する方法を解説！

仕入れ業務の効率化に悩んでいませんか？エクセルを使えば、面倒な仕入れ管理を自動化し、時間と手間を大幅に削減できます。この記事では、仕入れに関する様々なデータを一元管理する方法や、便利な関数を使った集計・分析の仕方をわかりやすく解説します。

仕入れ管理は2種類ある！

仕入れ管理は、大きく分けて以下の2種類に分けられます。

• 在庫を管理する仕入れ管理
  これは、企業が販売するために仕入れた商品を、適切な数量で在庫として管理することを指します。
• お金を管理する仕入れ管理
  仕入れにかかる費用を管理し、企業の資金繰りを安定させることを目的とした仕入れ管理です。

在庫を管理する仕入れ管理

「在庫を管理する仕入れ管理」は、具体的には在庫管理表のことで、企業の事業活動において最も基本的な仕入れ管理の一つです。商品を適切な数量で在庫として保有することで、顧客への安定的な供給を確保し、売上を最大化することを目的としています。

お金を管理する仕入れ管理

こちらは、仕入れにかかる費用を管理し、企業の資金繰りを安定させることを目的とした仕入れ管理です。具体的には仕入れ台帳で管理します。

エクセルで在庫管理表を作成する方法

エクセルで在庫管理表を作成すると、商品の在庫状況を簡単に把握でき、業務効率化に繋がります。最もよく使われる2つのタイプ、「単票タイプ」と「在庫移動表タイプ」について、具体的な作成方法を解説します。

単票タイプは、1つの商品ごとに記録するシンプルなタイプです。商品名、入出庫日、数量などを記入することで、個々の商品の入出庫履歴を詳細に追跡できます。

在庫移動表タイプは、複数の商品を一括で管理するタイプです。商品名、在庫数、入庫数、出庫数を表形式でまとめ、全体的な在庫状況を把握するのに適しています。

どちらのタイプも、エクセルの機能を活用することでより高度な管理が可能になります。

単票タイプの作り方

エクセルを使って、商品の在庫を効率的に管理できる表を作成してみましょう。まずはシンプルな「単票タイプ」の在庫管理表の作成方法を解説します。

在庫移動表タイプの作り方

次に、複数の商品を管理する「在庫移動表タイプ」の在庫管理表の作成方法を解説します。

エクセルで仕入れ台帳を作成する方法

次にエクセルでお金を管理するための仕入れ台帳を作成する方法について説明します。

仕入れ台帳に必ずしも必要な項目はありませんが、一般的に以下の項目を含めることが多いです。以下のような列を設定します。

商品マスターを作る

エクセルで仕入れ管理を効率的に行うために、商品マスターを作成することが重要です。商品マスターとは、取り扱う全商品の情報を一元管理するデータベースです。これを作成することで、仕入れデータ入力の際に商品情報を簡単に参照でき、入力ミスを減らすとともに、仕入れ管理の分析精度を高めることができます。

商品マスターを作成する手順は以下の通りです。

1. 新しいシートを作成し、「商品マスター」と名付けます。

2.以下のような項目を列に設定します。

3.各列にデータを入力していきます。

4.データの入力が完了したら、テーブル形式に変換します。これにより、データの並べ替えやフィルタリングが容易になります。

データ範囲を選択する: 顧客データが入力された範囲を全て選択します（例：A1から最後のセルまで）。

テーブルを挿入する: 「挿入」タブをクリックし、「テーブル」を選択します。選択した範囲が自動的にテーブルとして設定されます。

ここで「列1」となっているところに項目名を入力してください。

5.必要に応じて、条件付き書式を設定し、在庫数が少ない商品や利益率の高い商品などを視覚的に強調します。

仕入れ台帳とマスターを紐づける

エクセルで仕入れ管理を効率的に行うためには、仕入れ台帳と先ほど作成した商品マスターを紐づけることが重要です。この紐づけにより、仕入れデータ入力の手間を大幅に削減し、入力ミスを防ぐことができます。

紐づけの基本的な手順は以下の通りです。

仕入れ台帳に「商品コード」の列を追加し、各売上に対する取引コードと商品コードを登録する。

仕入れ台帳に計算式を入力する

1. 数量×単価で金額が自動計算されるように入力します。

　 F2のセルに「＝D2*E2」を入力します。

F2のセルを選択しマウスで下にドラッグすればF列に同じ数式が入力されます。

2.VLOOKUP関数を使用して、コードに基づいて商品のマスターデータから情報を取得します。例えば、仕入先名を取得する場合は次のような関数を使用します。=VLOOKUP(I2,商品マスター!$A$2:$H$10,7,FALSE)

3.同様に、商品名や単価などの情報も商品マスターから取得します。例えば、商品名を取得する場合は次のような関数を使用します。=VLOOKUP(I2,商品マスター!$A$1:$H$10,2,FALSE)

4.同様の数式を各項目に入力すれば仕入れ台帳の完成です。商品コードを入力すれば必要項目が自動入力されるようになります。

仕入れ管理で便利なエクセル機能

仕入れ管理で便利なエクセルの機能を３つ紹介します。

SUMIF機能

SUMIF関数 は、Excelで特定の条件に合うセルだけを合計したいときに使う便利な関数です。例えば、「商品名が「Tシャツ」というセルだけ、数量を合計する」といった計算を簡単に実行できます。

範囲: 条件を判定するセル範囲を指定します。

条件: 合計対象を決める条件を指定します。

合計範囲: 条件に合うセルに対応する、合計したいセル範囲を指定します。

フィルター機能

Excelのフィルター機能は、大量のデータの中から、特定の条件に合うデータだけを抽出表示させるための機能です。例えば、仕入れ台帳から特定の商品だけを表示させたりといったことができます。

1.「データー」→「フィルター」を選択する

2.表示させたい項目を選択する。（例：商品名からTシャツのみを表示）

ピボットテーブル

Excelのピボットテーブル機能は、大量の売上データを効率的に集計・分析し、多角的な視点から仕入れ状況を把握するための強力なツールです。

商品、仕入先など、様々な切り口でデータの集計が容易に行えます。これにより、仕入トレンドを詳細に分析し、潜在的な課題を早期に発見することができます。

ピボットテーブルの作成は、売上データを選択し、「挿入」タブから「ピボットテーブル」を選ぶことで開始できます。その後、行、列、値といったフィールドをドラッグ＆ドロップするだけで、柔軟に集計結果をカスタマイズできます。

・商品ごとの仕入れ数量を表示させたいとき

　「商品名」を「行」へ、「数量」を「値」にドラッグ＆ドロップする。

・仕入先ごとの商品別仕入金額を表示させたいとき

「仕入先」を「行」へ、「商品名」を「列」へ、金額を「値」にドラッグ＆ドロップする。

・備考ごと（今回の例ではサイズごと）の在庫数量を表示させたいとき

「数量」を「値」へ、「備考」を「行」へドラッグ＆ドロップする。

エクセルで仕入れ管理をするときのポイント

エクセルで仕入れ管理するときのポイントを２つ説明します。

入力ミスを減らす

Excelは、仕入れ管理を効率化するための強力なツールです。しかし、単に表を作成するだけでなく、入力ミスを防ぎ、データの整合性を保つための工夫が必要です。今回紹介したように、商品マスターを作成し、VLOOKUP関数で紐づければ、入力ミスを減らしデーターの整合性を保つことができます。

データを活用する

Excelで作成した仕入れデータは、様々な角度から分析することができます。仕入れデータは、単に記録するだけでなく、次のビジネスチャンスにつなげるための重要な情報源です。データを収集するだけでなく、次のビジネスチャンスにつなげましょう。