BLOG

売上管理のエクセル表は利益が残る客を見るために作る|列と集計の手順

売上管理のエクセル表は、合計を出すためではなく、どの客とどの商品で利益が残っているかを見て、誰に力を入れるかを決めるために作ることをおすすめします。1件の取引を1行に入れて原価と粗利の列を置けば、売上の順位と粗利の順位を並べ、取引を増やす客と条件を見直す客を分けられます。

会社の事務所で、社長がノートパソコンに映したエクセルの売上管理表を見ていて、画面には客ごとの売上と粗利、2つの順位を並べたピボットテーブルが表示されている様子。

「売上はエクセルに入れて合計も合っているから、見やすいテンプレートに替えれば経営に使えるはず」。月末に合計が会計の数字と合えば表の役目は済んだことになるため、表の形を整えれば足りると考えがちです。ところが、合計からは、どの客との取引を増やし、どの客との条件を見直すかは決められません。売上管理の表は、利益が残る客と商品を見て、力を入れる先を決めるために作ります。

本記事では、集計の自動化から社内のシステム開発までを引き受ける立場で、社長が自分で答える5つの問いから、9つの列とピボットテーブルでの集計まで、順を追ってお伝えします。

この記事でわかること
  • 売上の順位と粗利の順位で入れ替わった客が、取引を増やすか条件を見直す相手の候補になる
  • 売上管理表には9つの列を置き、1件の取引を1行に入れる
  • ピボットテーブルを使えば、関数を書かずに客ごとの売上と粗利、2つの順位を出せる
  • 入力する人と日を決めてファイルを1つにすると、集計の前に表を写す作業がなくなる

先月の売上はすぐ答えられるか

先月の売上の合計は、売上をエクセルに入れている社長なら、表を開けばすぐに答えられます。ところが、合計だけでは次の手が決まりません。先月より売上が減ったと分かっても、減ったのがどの客の分か、どの商品の分かが分からなければ、誰に連絡するかも、何の値段を見直すかも決められないからです。

経営者経営者

合計が会計の数字と合っていれば、売上の表としては十分ではないのですか。

Writers-hub編集部Writers-hub
編集部

合計を会計と合わせると入れ忘れに気づけるので、続ける意味はあります。ただ、合計が合っても、どの客との取引を増やせばよいかは分かりません。客と商品を1件ずつ入れておけば、同じ表から内訳も出せます。

今の表が合計を出すための表かどうかは、次の3つで分かります。1つでも当てはまれば、客ごとの内訳は今の表のままでは出せないか、出すたびに表を写し直すことになります。

  • 1日分や1か月分の売上を合計して、1行に入れている
  • 客の名前の列がないか、同じ客の名前の書き方がそろっていない
  • 月ごとにシートかファイルを分けていて、1年分を並べて見られない

関連記事:生成AIのビジネス活用完全ガイド|企業の活用事例と導入ポイントを徹底解説

先月の売上はどの客とどの商品から来たか

先月の売上がどの客とどの商品から来たかは、1件の取引を1行に入れた表なら、客の名前と商品の列で集計して答えられます。1日分の合計を1行に入れた表には合計の中身が残っていないため、請求書や納品書を見返すしかありません。

1日分の合計を1行に入れた売上の表と、1件の取引を1行に入れた売上の表を比べた図

客の内訳では、まず売上の上位10社を書き出し、10社の売上が全体の何割になるかを出します。割合が高いほど、上位の客との取引が減ったときに、会社全体の売上が大きく動きます。

商品の内訳も、売上の多い順に並べて確認します。売上の多い客と商品が分かっても、利益が残っているかはまだ分かりません。売上の金額からは、仕入れや材料に払った費用が引かれていないからです。

関連記事:見積もりの自動化はエクセルでどこまでできる?単価表から作る手順

利益が残っている客の見分け方

利益が残っている客は、客ごとの粗利を出し、売上の順位と粗利の順位を並べると見分けられます。粗利は、売上から原価を引いた金額です。原価に入れるのは、その取引のために払った仕入れや材料、外注の費用です。

売上の順位と粗利の順位の比べ方

売上の順位と粗利の順位では、同じ客が同じ順位に並ぶとは限りません。たとえば、売上300万円で粗利率(売上に占める粗利の割合)が10%の客と、売上120万円で粗利率が40%の客を比べます。粗利は30万円と48万円になり、売上の少ない客のほうが、粗利の額は多くなります。

順位の入れ替わりは、値引きの大きさや、原価の高い商品の割合などで起きます。どれが原因かは業種でも客でも違うため、入れ替わった客の取引を何件か開き、単価と原価を見比べます。

客ごとの売上の順位と粗利の順位を左右に並べ、順位が入れ替わる客を線で結んだ図

2つの順位の違いから、力を入れる客の候補は次のように分けられます。

💡

粗利の順位が売上の順位より上の客は、取引を増やす相手の候補になります。売上の順位だけが上の客は、値引きや取引の条件を見直す相手の候補です。

粗利に表れない手間の確かめ方

粗利に表れない手間は、客ごとの取引の件数と、担当への聞き取りで確かめます。見積もりや請求、問い合わせへの返事にかかるこの表では社員の時間を原価の列に入れないからです。

取引の件数は、売上管理表の行の数を客ごとに数えれば出ます。売上が同じでも、件数の多い客ほど、見積もりや請求の回数が増えます。

件数に表れない手間は、担当に「この客で時間がかかっている作業は何か」と聞きます。やり直しの依頼や支払いの遅れのように、表に残らない手間は担当が知っています。

関連記事:中小企業のシステム開発は頼む前に決める|最初に作る範囲と直す人

問いの答えから決まる売上管理表の列と入れ方

売上管理表の列と入れ方は、客ごとと商品ごとの粗利を正しく出せるかで決まります。

売上管理表に置く9つの列

売上管理表に置く列は、客、商品、担当のどれでも売上と粗利を集計できるようにする次の9つです。

列

入れる内容

答えに使う問い

日付

売上を計上している基準の日(納品日・検収日など)にそろえる

先月の売上、来年も続く客

客の名前

決めた名前の一覧から選ぶ

売上の上位10社、客ごとの粗利

商品

商品やサービスの名前

商品ごとの売上と粗利

数量

取引した個数や回数

売上と原価の計算

単価

値引きした後の1個の値段

売上の計算

売上

数量×単価の数式

先月の売上、売上の上位10社

原価

取引のために払った仕入れや外注の費用

客ごとの粗利

粗利

売上−原価の数式

客ごとの粗利、手間のかかる客

担当

取引を受け持った社員の名前

手間のかかる客

無料のテンプレートを使う場合も、9つの列がそろっているかで選び、足りない列は足します。客の住所や連絡先は売上管理表に入れず、顧客管理の表に分けます。顧客管理の表の作り方は、顧客管理をエクセルで続ける手順|誰が何をいつ入れるかを先に決めるにまとめています。

粗利を正しく出すための入れ方

粗利を正しく出すために、単価には値引きした後の値段を入れます。値引き前の値段を入れると、値引きの大きい客ほど、粗利が実際より多く出ます。

税込と税抜の混在は、とりわけ見落とされがちです。売上と原価は、どちらも会計で使っている税込か税抜のどちらかにそろえて入れます。売上を税込で、原価を税抜で入れると、売上にかかる消費税の分だけ粗利が多く出ます。

客の名前と商品は、[データ]タブの[データの入力規則]で[リスト]を選び、決めた名前から選ぶ形にします。「株式会社〇〇」と「(株)〇〇」が混ざると、集計のときに別の客として数えられるためです。

自分で答える問いの一覧と売上管理表の作り方

社長が自分で答える問いは5つあり、9つの列を持つ売上管理表とピボットテーブルがあれば、5つとも同じ表を元に考えられます。

自分で答える5つの問い

社長が自分で答える5つの問いと、それぞれの答えの出し方は次のとおりです。

問い

答えの出し方

先月の売上はいくらか

日付を月ごとにまとめ、売上を合計する

売上の上位10社はどこか

客の名前ごとに売上を合計し、多い順に並べる

客ごとの粗利はいくらか

客の名前ごとに粗利を合計し、粗利の順位を付ける

手間のかかる客はどこか

客の名前ごとに取引の件数を数え、担当に聞く

来年も続く客はどこか

客の名前ごとに今年と去年の売上を並べる

来年も続く客は、表の数字だけでは決まりません。去年より売上が減った客は、担当に今後の取引の予定を聞いて確かめます。

ピボットテーブルで客ごとの売上と粗利を出す手順

ピボットテーブルは、表の行を、選んだ列ごとに自動で集計するエクセルの機能です。関数を書かずに、客ごとの売上と粗利、2つの順位まで出せます。

1
STEP
表のテーブル化

売上管理表の中のセルを1つ選び、[挿入]タブの[テーブル]を選びます。テーブルにしておくと、行を足した後もピボットテーブルを更新するだけで、足した行が集計に入ります。

2
STEP
ピボットテーブルの作成

テーブルの中のセルを選び、[挿入]タブの[ピボットテーブル]を選びます。置く場所に[新しいワークシート]を選び、[OK]を押します。

3
STEP
行に客の名前、値に売上と粗利

[ピボットテーブル フィールド]の一覧で、「客の名前」を[行]へ、「売上」と「粗利」を[値]へドラッグします。

4
STEP
売上と粗利の順位付け

「売上」と「粗利」をもう一度[値]へドラッグします。足した列で右クリックし、[値の表示の種類]から[降順での順位]を選ぶと、多い順に1から番号が付きます。基準にするフィールドを聞かれたら「客の名前」を選びます。

5
STEP
粗利の多い順への並べ替え

粗利の合計の列で右クリックし、[並べ替え]から[降順]を選びます。

(参考:ピボットテーブルを作成してワークシート データを分析する(Microsoft サポート))

値に置いた売上が「個数」で集計されたら、売上の列が数値ではなく文字として扱われています。数字だけに直してから、ピボットテーブルの中で右クリックし、[更新]を選びます。

取引の件数は、「客の名前」を[値]へドラッグすると、客ごとの行の数として出ます。商品ごとに見るときは、[行]の「客の名前」を「商品」に替えます。月ごとに見るときは、ピボットテーブルの日付を右クリックして[グループ化]を選び、[基準]で[月]を選びます。

ピボットテーブルのフィールドの一覧で、客の名前を行に、売上と粗利と2つの順位を値に置いた様子と、できあがった客ごとの集計表を並べた図

入力する人と入力する日の決め方

売上管理表に入力する人と日は、請求書を作る人が、請求書を出した日に入れると決めると、入れ忘れが減ります。請求書の金額を見ながら、同じ数字を入れられるからです。請求書を出さない取引が多い店なら、レジを締めた人が締めた日に入れるように、毎日必ずする作業の直後に置きます。

ファイルは1つにし、担当ごとや月ごとのコピーを作りません。ファイルが分かれていると、集計の前に毎回1つの表へ写す作業が要り、写すたびに漏れや二重の入力が起きます。何人かで入れるなら、OneDriveかSharePoint Onlineに置き、編集できる人を決めて、全員が同じファイルを開く形にします。

今ある月ごとのファイルは、1回だけ新しい表へまとめます。まとめた後は、古いファイルに書き足さないと決めます。

店や担当ごとのファイルから、毎月の売上を写していませんか

業務効率化・自動化では、データの転記のような定型業務の自動化から、社内のシステム開発までを引き受けます。今使っている売上の表と、写す元のファイルの画面を1〜2点見せていただければ、どこを自動にできるかを一緒に確かめられます。料金は個別にお見積もりします。

売上の集計や転記に手が回らない場合

売上の集計や転記に手が回らない場合も、売上管理の表を作る目的は、利益が残る客を見て、力を入れる先を決めることです。問いの答えが出れば、表に何を入れるかは自分で決められます。

複数のファイルからの転記や毎月の集計に時間がかかっている場合は、私たち合同会社Writers-hubが、集計の自動化から社内のシステム開発まで引き受けます(料金は個別にお見積もりします)。

エクセルを続けるか、別の仕組みに移るかを、作業の時間とミスの数で決める方法は、エクセル管理の限界を感じたら|時間とミスを数えて4つの方法から選ぶにまとめています。

毎月の集計に追われて、客ごとの粗利を見る時間が取れていないのでは

毎月の売上の集計を自動にすると、社長は担当の集計を待たずに、客ごとの粗利と2つの順位を見られます。業務効率化・自動化では、定型業務のAI自動化から社内システムの開発までを引き受けます。料金は個別にお見積もりします。

この記事を書いた人

米山拓真

米山拓真

合同会社Writers-hub 代表社員

滋賀県立大学工学研究科の修士課程を修了後、大手制作会社の編集部を経て2019年にWebライターとして独立。2020年に記事制作を手順化した「ハブ式SEOライティングメソッド」を開発し、これまでに200人以上のライターを育成しました。2022年3月に合同会社Writers-hubを設立し、会社として累計8,000記事以上のSEO記事制作に携わっています。2024年にAIライティングシステム「一気通貫Pro」、2025年から書き手の文体を再現するAI編集者「Edico」を開発。このブログでは、クライアント案件と自社サイトで実際に試したことを書いています。

読んで終わりにしないために

記事の内容を自社に当てはめるとどうなるか、オンライン30分で一緒に整理します。費用はかかりません。