店舗オペレーション

【無料テンプレート付き】エクセルで売上管理をする方法は?集計から予測までを行う

売上管理票の無料テンプレー付き|エクセル初心者でも簡単に売上管理表を作る方法

店舗DXを始めるにしても予算が伴わないケースも多いかと思います。まずはエクセルで売上管理を行い、日々の業務を簡易化することから始めてみましょう。

Excelの解説についてはMicrosoft Excel2019を使用しています。
※他バージョンでは動作が違う場合がありますのでご注意ください。

売上管理の基礎

売上管理とは何をしたら良いのでしょう?まず、会社の年間業績を損益計算書にまとめます。その中で収益、費用、利益の3つの要素を記載します。

収益ー費用=利益

この金額が分かるようにエクセルで管理を行えばよいのです。

では一般的にどのような項目が管理されているか見ていきましょう。

管理に必要な項目例一覧

No取引を行った際に重複しない番号を振ります。
販売日取引を行った日付です。
取引先名取引を行った相手です。
商品番号取引した商品の固有番号です。バーコードの番号のように商品ごとに重複しない固有の番号を振ります。
商品名商品番号に対応した商品名です。
販売価格商品の価格です。割引を行う場合もありますので、商品ごとに一定ではない場合もあります。
個数商品を何個売ったかの個数です。
原価商品の仕入れ値です。
売上商品の販売価格×個数です。
粗利売上ー原価の値です。
その他消費税など、管理に必要な場合は追加します。

Noや商品番号は必ず重複しない固有の番号を使用します。のちに集計を正しく行うために必要です。

売上管理の土台が整ったら、KPIの設定も忘れずに行いましょう。KPIの設定については「データドリブンな店舗運営を実現する実践メソッド」もございます。合わせてご覧ください。

資料ダウンロード

店舗が抱える課題POS

データに基づいた店舗運営を目指して、POSデータだけでは取れない数値も取得しましょう。

マスターデータを作成する

同じ相手に同じものを売るときなど、商品名や取引先は何度も同じものを使用します。その都度手入力を行うのは無駄も多く、入力者によって全角・半角・略称などの違いが出てしまいます。

そのため、マスターを用意し、マスターに記載された項目を選択するというような手法を取ります。
例にある項目程度でしたらマスターは2つになります。

取引先マスター

取引先番号、取引先名、郵便番号、住所、電話番号など

取引先マスター
取引先マスター

項目を入力したらテーブルの形式にしておきます。

取引先マスターテーブル作成
取引先マスターテーブル作成

「先頭行をテーブルの見出しとして使用する」というチェック項目があるので、こちらをチェックしてテーブルを作成します。

取引先マスターテーブル名称
取引先マスターテーブル名称

テーブルを作成するとリボンに「テーブルデザイン」が表示されるようになります。「テーブル3」のようなデフォルト名ではどのテーブルなのか分かりにくくなりがちなので、テーブル名を変更しておきましょう。

商品マスター

商品番号、商品名、原価、販売価格など

仕入れたものを販売する場合には仕入れ先名、仕入れ先住所なども追加します。

商品マスター
商品マスター

取引先マスターと同様にテーブルにし、テーブル名を変更しておきます。

売上管理シート

それでは取引先マスターと商品マスターを使用して売上管理シートを作成しましょう。

売上管理
売上管理

それぞれの項目をセットしていきます。

取引先番号や商品番号のような重複しない番号は取引先マスターや商品マスターから値を取得し、リスト選択が出来るように設定します。

「入力規則」を選択します。

売上管理入力規則
売上管理入力規則

Excelを使った売上管理シート作成方法

「入力値の種類」で「リスト」を選択し、「元の値」に取引先マスターの取引先番号など、使用したい範囲を選択します。実際にエクセルで売上管理シートを作成する場合には知っておいた方が集計が早くなるような便利な関数がたくさんあります。

先ほど作成した売上管理シートから集計をしてみましょう。

便利な機能:SUMIF

SUMIFは条件に合った値のみ集計出来る関数です。

取引先ごとの粗利や顧客ごとの売上高など、一部の集計を行いたいときに使用します。

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

  • 範囲には、条件になる範囲を選択します。
  • 検索条件は、範囲で指定した範囲の中でどの値になればよいかの値を設定します。
  • 合計範囲は、金額などを合計する範囲を選択します。
sumif
SUMIF

便利な機能:VLookup

VLookupは表の中から指定した値を取り出してくる関数です。しかし、文章だけでは分かりにくいですね。
売上管理シートで取引先番号を取引先マスターからリストで選択できるようにしましたね。

VLookupでは取引先番号の横に取引先名を入れるという使い方ができます。

=VLookup(検索値、範囲、列番号、検索の型)

  • 検索値には、取引先番号を設定します。
  • 範囲には、取引先マスター(テーブル)を指定します。テーブル名を事前に設定しておけばそのテーブルを指定するだけです。
  • 列番号には、範囲(取引先マスター)の中で何列目か、です。
  • 検索の型には、2種類あります。
    • 「近似一致」のTRUE(1)
    • 「完全一致」のFALSE(0)

今回は取引先番号と完全に一致する値が欲しいのでFALSEにします。

vlookup
vlookup

これで取引先番号を選択するだけで、自動的に取引先名を表示してくれるようになります。入力の手間や入力ミスを防ぐことができます。

便利な機能:ピボットテーブル

SUMIFの解説の時に、取引先ごとの利益を計算しました。

作成する際に取引先番号を取引先マスターからコピーペーストしたり、取引先名をVLookupでもう一度取得したりしています。

集計するたびにコピーペーストして表を作り直して、とするのは手間がかかります。そんな時にピボットテーブルが活躍します。

まずは画像をご覧ください。

ピボット作成
ピボット作成

慣れたらこんな表が30秒で出来てしまいます。

ピボット商品ごと
ピボット商品ごと

データの元になる表にカーソルがある状態で「挿入」→「ピボットテーブル」を選択します。

ピボット範囲選択
ピボット範囲選択

テーブルの範囲も自動で選択してくれます。ほとんど手を付けることはありません。このまま「OK」を押します。

ピボット初期
ピボット初期

右側に「ピボットテーブルのフィールド」が表示されます。ここを設定していくと左側に表が自動で作成されます。

ピボット商品ごと

先ほど作成していたピボットテーブルはこのように設定しています。

  • 列に販売日
  • 行に商品名
  • 値に売上

上のフィールドから下の列などにドラッグで移動させます。間違えた場合やフィールドを変更したい場合は下から上のフィールドにドラッグして戻します。

ピボット取引先ごと
ピボット取引先ごと

今度は取引先ごとに集計したピボットテーブルを作成しました。商品ごとでは列にあった販売日を行にしてみると、また違った印象になります。日付ごとの集計を行にすると日付ごとでも月ごとでも見ることができます。

ピボットテーブルのメリットは自動で集計表を作成してくれることですが、全角・半角、略称などの違いも全て別の項目として扱われるデメリットがあります。このデメリットを発生させないためにも、マスターテーブルを作成しておくことは不可欠になります。

無料の売上管理表のテンプレートのダウンロードはこちらから↓↓

売上管理.xlsx
売上管理.xlsxの無料ダウンロード

売上管理で重要なこと

売上管理をやろうと思えば、まずは簡単に始めてみることができることが分かったと思います。
しかし、気を付けなければならない重要なこともあります。

セキュリティがしっかりしているか?

事務担当が一人で入力するような小さな会社などではセキュリティへの理解も不十分なケースも見られます。
社員であれば誰でも簡単に取引先との情報が見れる環境になっている場合もあります。情報流出は近年ニュースでも取り上げられることは珍しくありません。

そこに会社の規模は関係ありません。また、せっかく事務担当が日々入力したデータを誰かがうっかり消してしまった。そんなこともありうるかもしれません。

パスワードロックやウィルス対策ソフトなど、出来る対策をしておきましょう。

ミスを最小限化する工夫ができているか?

人間はミスをする生き物です。ミスを0にすることは出来ません。しかしミスを減らすことはできます。

ミスを減らす工夫として、すでに解説したマスターからのリスト選択や、VLookupで名称の自動取得もその1つです。声を出して読み上げて確認や、複数人でのチェックなどアナログ的な方法もあります。

ミスをしたことに気づかない、ミスを取り返すのが非常に手間がかかる、そんなことがないように少し手間でもミスを減らす工夫をしましょう。

管理の期間を設定しているか?

売上管理シートで比較的簡単に売上を管理できることが分かりました。

しかし、これはどのくらいの期間使用するのでしょう?
パソコンにも処理限界があります。個々のパソコンで性能は違いますが、何年も、何十年も同じファイルに継ぎ足していけば、ファイルサイズは膨大になり、ファイルを開くだけでも長時間かかるようになってしまいます。

集計して見たい年度以外の年度もたくさん入っていれば不要な年度は邪魔なだけで、ピボットテーブル作成などの処理に時間がかかることも。帳簿などでも年度単位に分けたりするように、管理の期間を設定しましょう。

これは見直しのチャンスでもあります。
切り替えのタイミングで、エクセルから別のシステム導入を検討したり、エクセルのままでも不足項目はないか、逆に不要項目はないか。

より快適な作業が出来るよう、改善を検討することも大切です。

まとめ

「エクセル初心者でもOK!無料で売上管理表を作る方法を紹介」はいかがでしたでしょうか。
取引先や商品をマスターテーブルで管理しする。
日々の売上データを入力していく。
ピボットテーブルを使って簡単に集計する。

まずは一度試してみてください。こんな簡単に出来るのかと感じられると思います。