エクセルを使った売上集計表の作り方を解説!

SuitUP 売上集計表

売上管理は、現場のマネージャーや経営者には欠かせない業務です。しかし、売上が計上されるには、商品や店舗、担当者や取引先などのさまざまな情報や経緯があります。

そのため、なかには「売上を分析するために見やすい集計表を作りたい」「売上実績の情報をうまく整理できない」と悩んでいる方もいるでしょう。

売上の詳細情報を区分してまとめた売上集計表があると、効率よく売上管理ができます。特に、導入する負担の少ないエクセルなら、売上集計表を作成しやすいでしょう。

エクセルでの売上集計表の作り方をご紹介します。

目次

売上集計表とは

売上集計表とは、店舗・担当者・商品・日付・地域・取引先などに区分して、それぞれの売上合計や取引件数をまとめた表です。売上管理表や利益管理表と言われることもありますが、売上集計表は条件ごとの売上を比較できる点が特徴です。

営業の実績をデータ化するので、売上や利益の予測を立てたり、具体的な目標や対策を検討したりする営業計画の際に役立つ資料になるでしょう。

売上集計表に必要な項目の要素は、以下の5点です。

  • いつ:売上が計上された日付け
  • どこで:担当者や店舗、店舗のあるエリアや支店など
  • だれが:取引先の名前や連絡先などの情報
  • なにを:商品やサービスの情報
  • どのくらい:単価、数量、売上金額、原価、粗利などの数値

売上計上日がわかれば、月次データとしてまとめたり、季節や天候が売上に影響していないかを分析したりできます。また、売上の多い店舗や担当者が明確になり、各取引先の傾向をつかめれば、営業戦略を立てやすいでしょう。さらに、商品やサービスの売れ筋がわかると、今後の展開を検討しやすいです。

ぜひ、集計する項目や条件を工夫して、事業に合った売上集計表を作成しましょう。

エクセルで売上集計表を作る方法

エクセルで売上集計表を作る場合、以下の機能と関数を使用します。

  • 名前の定義
  • SUMIF関数
  • SUMIFS関数
  • COUNTIF関数
  • COUNTIFS関数
  • DSUM関数

エクセルはカスタマイズをすれば、より便利になりますが、複雑になるとユーザーによっては扱いにくいこともあります。そのため、社内で共有する可能性がある売上集計表は、なるべく簡単な方法で作りましょう。

実際のエクセル画面を参考にしながら、売上集計表の作り方をご説明します。

まずは売上管理表を用意する

ここでの「売上管理表」は、売上を単純に記録した一覧表です。エクセル上で必要な項目リストを作り、それぞれのデータを入力していきます。

まず、必要な項目を検討しましょう。項目の例は、以下が考えられます。

  • 取引番号
  • 取引日
  • 店舗
  • 担当者
  • 取引先番号
  • 取引先名
  • 商品番号
  • 商品名
  • 原価
  • 販売価格
  • 個数
  • 売上金額
  • 粗利
  • 売上目標
  • 総合計

項目リストができたら、売上を記録した資料から必要な情報を抽出して、入力していきましょう。入力が完了したら、売上集計に用いる売上管理表の完成です。

また、取引先や商品については、マスター情報を用意し、売上管理表と紐づけておくと便利です。

取引先マスターの例は、以下が挙げられます。

  • 取引先番号
  • 取引先名
  • 所在地
  • 電話番号
  • 担当者名
  • 支払い条件
  • 備考

商品マスターの例は、以下が考えられます。

  • 商品番号
  • 商品名
  • カテゴリ
  • 仕入れ先
  • 仕入れ価格
  • 販売価格
  • 在庫数
  • 備考

マスターデータを紐づける手順は、以下の通りです。

STEP
VLOOKUP関数を登録

VLOOKUP関数には、以下の引数を入力してください。

=VLOOKUP(検索値,範囲,列番号,検索方法)

検索値:該当する取引先番号のセル
範囲:取引先マスター
列番号:取引先マスター上での取引先名の列番号
検索方法:FAULSE(完全一致検索)

STEP
項目リストを設定

取引先番号に有効な番号のみしか入力できないように設定します。

取引先番号の列を選択し、「データの入力規則」で「リスト」を選択し、「ソース」に有効な番号をコンマで区切りながら入力します。

STEP
マスターデータの紐づけが完了

取引先番号を入力すれば、取引先名が自動で表示されるようになります。商品番号と商品名も同様にマスターデータを紐づけておきましょう。

シートに名前をつける

売上集計表として条件ごとの集計を始める前に、作成した売上管理表に名前を定義しましょう。手順は以下の通りです。

STEP
売上管理表を選択

作成した売上管理表の全体を選択します。

STEP
名前を登録

シートの左上にある名前ボックスに名称を入力します。「売上9月」などデータの種類がわかる名前にしましょう。名前の先頭が数字だと設定できないので注意してください。

STEP
名前の定義が完了

名前の定義ができたら、Ctrl+F3で名前マネジャーを呼び出し、定義した名前を確認しましょう。

表や列、行などに名前を定義しておくと、さまざまな条件で集計表を作成する際に便利です。区分して集計する「店舗名」の列や「商品名」の列などに名前を定義しておきましょう。

名前の定義が完了したら、売上金額を集計していきましょう。

店舗ごとの売上金額を集計する方法

SUMIF関数を使って売り上げを集計していきます。手順は以下の通りです。

STEP
店舗ごとの売上集計表のフォーマットを作成

店舗名と売上金額の表を作成します。

STEP
SUMIF関数を登録

該当のセルを選択し、SUMIF関数を登録します。SUMIF関数は、指定した範囲の中から条件に合うものだけを合計する関数です。そのため、以下の登録を行いましょう。

=SUMIF(範囲,検索条件,合計範囲)

範囲:どこから探すか(今回は売上管理表の店舗の列)
検索条件:何を探すか(今回は売上集計表の店舗名のセルからA2=「新宿」と一致するものを探す)
合計範囲:何を合計するか(今回は売上管理表の売上金額の列)

このとき、売上管理表の店舗や売上金額の列も、あらかじめ名前の定義をしていれば、範囲選択が簡単になります。表の一番上の店舗で関数を登録したら、他の店舗にもコピーします。このとき、指定した範囲は変わらないので、絶対参照($でアルファベットを挟む、例:$D$2:$D$15など)を使用しましょう。

STEP
店舗ごとの売上金額を集計

各店舗に関数の登録ができたら、店舗ごとの売上金額の集計が自動計算されます。

店舗ごとの商品区分別売上金額を集計する方法

店舗ごとで、さらに商品別の売上金額を集計していきましょう。複数の条件を指定するので、SUMIFS関数を使います。手順は以下の通りです。

STEP
店舗ごとの商品別売上集計表のフォーマットを作成

店舗名と商品名を入力した表を作成します。

STEP
SUMIFS関数を登録

SUMIFS関数は、指定したの範囲の中から複数の条件に合うものだけを合計する関数です。そのため、以下の登録を行いましょう。

=SUMIFS(合計対象範囲,条件範囲1,条件1,条件範囲2,条件2)

合計対象範囲:何を合計するか(今回は売上管理表の売上金額の列)
条件範囲1:1つ目の条件をどこから探すか(今回は売上管理表の店舗の列)
条件1:1つ目の条件として何を探すか(下の画像ではA3=「新宿」と一致したものを探す)
条件範囲2:2つ目の条件をどこから探すか(今回は売上管理表の商品名の列)
条件2:1つ目の条件として何を探すか(下の画像ではB2=「商品A」と一致したものを探す)

このとき、指定範囲の行数が一致していないと、計算されないので注意してください。

一番上の新宿店で関数登録ができたら、他の店舗にもコピーしていきましょう。

STEP
店舗ごとの商品区分別売上金額を集計

表内の全てのセルに関数登録ができたら、集計表の完成です。

注意点は、SUMIF関数(条件,合計範囲)とSUMIFS関数(合計範囲,条件1,条件2,,,)で登録する引数の順序が逆になっていることです。気を付けましょう。

店舗ごとの取引件数を集計する方法

売上金額ではなく取引件数を数える集計表も作成できます。店舗ごとに取引が成立した件数を集計していきましょう。COUNTIF関数を使用します。手順は以下の通りです。

STEP
店舗ごとの取引件数の集計表のフォーマットを作成

店舗名と取引件数の表を作成します。

STEP
COUNTIF関数を登録

COUNTIF関数は、条件と一致するデータを数える関数です。以下のように登録しましょう。

=COUNTIF(範囲,条件)

範囲:どこから探すか(今回は売上管理表の店舗名の列)
条件:なにを探すか(下の画像は売上集計表のA2=「新宿」と一致するものを探す)


各店舗にコピーできたら完成です。

STEP
店舗ごとの取引件数を集計

COUNTIF関数はデータの数を数えるだけなので、数値を合計するSUMIF関数とは異なります。

店舗ごとの商品区分別取引件数を集計する方法

店舗ごとで、さらに商品ごとの取引件数を集計していきましょう。条件が複数になるので、COUNTIFS関数を使います。手順は以下の通りです。

STEP
店舗ごとの商品別取引件数の集計表のフォーマットを作成

店舗名と商品名の表を作成します。

STEP
COUNTIFS関数を登録

COUNTIFS関数は、複数の条件と一致するデータの数を数える関数です。以下の引数を入力しましょう。

=COUNTIFS(検索条件範囲1,検索条件1,検索条件範囲2,検索条件2)

検索条件範囲1:1つ目の条件をどこから探すか(今回は売上管理表の店舗名の列)
検索条件1:1つ目の条件として何を探すか(下の画像では売り上げ集計表のA3=「新宿」と一致するものを探す)
検索条件範囲2:2つ目の条件をどこから探すか(今回は売上管理表の商品名の列)
検索条件2:2つ目の条件として何を探すか(下の画像では売り上げ集計表のB2=「商品A」と一致するものを探す)

1店舗目に登録ができたら、他の店舗にも関数をコピーしましょう。絶対参照や列のみ絶対参照、行のみ絶対参照を忘れずに活用してください。

STEP
店舗ごとの商品区分別取引件数を集計

全てのセルに関数が登録できたら、自動計算されます。

複数の条件を指定して、合計売上金額を集計する方法

最後に、自由度の高い集計表を作成します。複数の条件を自由に指定できる集計表です。使用する関数は、SUMIFS関数もしくはDSUM関数です。2パターンの集計方法をご紹介します。

各条件を1つずつ設定する場合

各条件を設定する場合は、SUMIFS関数を用い、手順は以下のようになります。

STEP
複数の条件を指定できる売上集計表のフォーマットを作成

条件として指定したい項目(契約日、店舗、取引先名など)と、集計したい項目(金額)の表を作成します。

STEP
SUMIFS関数を登録

「店舗ごとの商品別売上集計表」の作成と同様にSUMIFS関数を用いて、条件をさらに増やして登録します。条件として必要のない項目には、「*」を入力しましょう。

=SUMIFS(合計対象範囲,条件範囲1,条件1,条件範囲2,条件2,条件範囲3,条件3,,,)

STEP
複数の条件を指定して、合計売上金額を集計

条件(集計表で指定したい項目)とその範囲(売上管理表(元データ)内の指定したい項目の範囲)をそれぞれ設定する必要があるため、ずれると集計結果が正しく算出されません。

集計表全体を条件として設定する場合

各条件を1つずつ設定せずに集計表全体を条件とする場合は、DSUM関数を用い、手順は以下のようになります。

STEP
複数の条件を指定できる売上集計表のフォーマットを作成

条件となる項目と、集計したい項目(金額)の表を作成します。

STEP
DSUM関数を登録

DSUM関数は、データベースとフィールドを使用して条件を指定し、合計を算出します。以下のように登録をしましょう。

=DSUM(データベース,フィールド,条件)

データベース:どこから条件に該当するものを探すのか(今回は売上管理表)
フィールド:何を合計するのか(今回は売上金額)
条件:集計表(入力された項目が条件になる)

条件として集計表全体を指定しているので、自由に項目の増減や変更が可能です。

STEP
複数の条件を指定して、合計売上金額を集計

指定したい条件にのみデータを入力すれば、計算されます。必要ない項目に「*」を入力する必要はありません。

上記例のように、横方向に並べた表の場合はAND検索になり、全ての条件が一致するデータの合計を計算します。反対に、縦方向に並べた表の場合はOR検索になるので、いずれかの条件に当てはまるデータの合計が算出されます。

よかったらシェアしてね!
  • URLをコピーしました!
  • URLをコピーしました!

この記事を書いた人

目次