Loading...

店舗別の売上を集計|EC × Google スプレッドシート #2

ブログ

2022.6.16

Google スプレッドシートはさまざまな関数とピボットテーブルを上手に活用することで、EC の運営効率を高められるツールです。
一方で、「それぞれの機能をどのように組み合わせればいいのかわからない」という声も多くあります。
この「EC × Google スプレッドシート」シリーズの記事では、関数やピボットテーブルの使い方と、それらを組み合わせた EC 運営に実用的なシートをご紹介します。

前回の記事でお知らせしたとおり、今回の記事は店舗別の売上を集計するシートのつくり方を、サンプルの Google スプレッドシートとともに解説します。

今回のシートには、以下のようなメリットがあります。
・店舗ごとに前月比、前年比を比較することで、売上が変化した要因を推測しやすい
・日別・時間帯別の売上情報を見ることで、イベント時の売上の波を把握しやすい

集計する元データ

元データに必要な項目

今回は以下の項目を集計します。集計したいデータは、各モールや受注管理システムの管理画面などからダウンロードできます。

  • 受注番号
  • 受注日
  • 受注時間
  • 店舗コード
  • 受注キャンセル区分
  • 総合計

こちらにサンプルの Google スプレッドシートがございますので、サンプルと見比べながら本文を読み進めてください。

サンプルを入手

※サンプルでは集計の元データを「売上情報_raw」というシートに格納しています。上述した 6 つ以外の項目も含まれていますが、それらのデータは今回は使用しません。

使用する関数

  • TEXT:セルの値を指定の書式に変換する
  • IF:条件分岐をする
  • YEAR:指定した日付の「年」を表示する
  • MONTH:指定した日付の「月」を表示する
  • DAY:指定した日付の「日」を表示する
  • QUOTIENT:割り算の商を計算する
  • WEEKDAY:日付に対応する曜日を表示する
  • EDATE:指定した日付の 〇ヶ月後を計算する
  • VLOOKUP:キーとなる値をもとに、別の値を探す

TEXT 関数

=TEXT(変換対象のセル, “書式” )

セルの値を指定の書式に変換します。
「年月日」を「年」に変換するには、以下のようにします。

=TEXT(変換対象のセル, “yyyy” )

「月」の場合はこちらです。

=TEXT(変換対象のセル, “mm” )

他にも書式のパターンがあるので、気になる方は公式のリファレンスを参照してください。https://support.google.com/docs/answer/3094139?hl=ja

IF 関数

=IF(条件式, True の場合の処理, False の場合の処理)

「条件式」とは、True または False の値を返す式のことです。

こちらの関数を例に解説します。

=IF(任意の条件式, “True”, “False”)

この関数は、かっこ内の最初の条件式が True を返す場合は「True」、そうでないなら「False」と表示します。
A2 に 5 という値を入れたとき、B2 の IF 関数の条件式を

A2 < 10

とすると、5 は 10 より小さいため、B2 には「True」と表示されます。

条件式が True を返す場合、「True」と表示する

一方、C2 の条件式を

A2 > 10

とすると、A2 はこの条件式を満たさないので、C2 には「False」の値が入ります。

条件式が False を返す場合、「False」と表示する

このように IF 関数は、条件式の返す値が True のときと False のときで続く処理を分岐させることができます。

もう少し具体的な例を見ていきましょう。
A 列には年月日の値が入っており、B 列に TEXT 関数を使って「年」の値を入れたいとします。
2 行目では「年」の値がうまく抽出されていますが、3 行目では A 列に値がないので誤った値が表示されてしまっています。

A3 が空なので誤った値が表示されている

もし A 列の値が空白である場合には B 列に何も表示したくないので、

「もし A 列の値が空白ならば B 列を空白に、そうでないなら TEXT 関数で「年」の値を記入する」

と処理を分岐させます。
これを実際の関数に表すと、

=IF(A3=””, “”, TEXT(A3, “yyyy”))

になります。

A3 が空白であるかどうかで処理を分岐させた

YEAR 関数 / MONTH 関数 / DAY 関数

=YEAR(日付型の値)

先ほど TEXT 関数で日付型の値から「年」の値を抽出しましたが、YEAR、MONTH、DAY 関数によって、それぞれの「年」「月」「日」の値を抽出することができます。
※代表でYEAR 関数をご紹介しますが、MONTH と DAY も使い方は同じです。

TEXT 関数と比較したとき、関数に入力する値が 1 つで完結し、後で見返したときに何をやっているのかわかりやすいというメリットがあります。

QUOTIENT 関数

=QUOTIENT(割られる数, 割る数)

「クオシェント」と読み、割り算の商の値を計算することができます。


単純ですが、さまざまな計算に活用でき、この記事では日付の値がその月の何週目かを計算するのに使用します。

日付から第何週かを割り出す

WEEKDAY 関数

=WEEKDAY(日付, 種類)

日付がその週の何日目にあたるかを計算する関数です。
種類には 1 ~ 3 の数字を入力します。入力した数字によって結果が変化します。

1:1(日曜)~ 7(土曜)
2:1(月曜)~ 7(日曜)
3:0(日曜)~ 6(日曜)

種類の数字によって、曜日に対応する数字が変化する

この記事では、任意の日付と同じ週の、月曜日の日付を算出するのに使用しています。

=日付-WEEKDAY(日付,3)

日付とWEEKDAY 関数の値の差によって、その週の月曜日の日付を算出

EDATE 関数

=EDATE(日付, 数字)

指定した日付の 〇ヶ月後を計算します。
数字の値を -1 とすれば 1 ヶ月前、-12 とすれば 1 年前の値を出すことができます。

VLOOKUP 関数

=VLOOKUP(検索する値, 検索する範囲, 列指定, False)

キーとなる値を指定の範囲内で検索して、値が一致した範囲内のセルの右隣の値を表示します。
たとえば、A 列に店舗コードが書かれた「元データ」というシート(画像 1)があり、この店舗コードの右隣の B 列に、店舗コードに対応するチャネルの値を入れたいとします。

画像 1:店舗コードが記載された「元データ」シート

まずは、店舗コードに対応するチャネルが書かれたシート「店舗マスタ」(画像 2)を用意します。

画像 2:店舗コードに対応する店舗名とチャネルが記載された「店舗マスタ」シート

検索する値を「元データ」の店舗コードの “1”、検索範囲を「店舗マスタ」の A ~ C 列とすると、検索範囲内で値が 1 であるセルの右隣のセルの値を参照することができます。
ここで表示したいのは 3 列目のチャネルの値なので、列指定を 3 とします。
実際の関数に当てはめたのが以下です。

=VLOOKUP(A2,’店舗マスタ’!A:C,3,False)

「検索範囲」に参照したい値のある列まで含めることに注意してください。
この場合は、店舗コードの値がある列は「店舗マスタ」の A 列だけですが、「店舗マスタ」の C 列の値を参照したいので、C 列まで検索範囲に含めないとエラーになります。

「検索範囲」に「店舗マスタ」シートの A ~ C 列を含める

関数の最後の値は基本的に「False」とします。詳細な理由については、公式のリファレンスをご覧ください。
https://support.google.com/docs/answer/3093318

サンプルの解説

サンプルは、ここまでご紹介した関数を使って組まれています。今回はその中の、「レポート」というシートの最下部にある月初から集計日までの店舗別の売上集計表の仕組みを解説します。

「レポート」シートの赤枠の表の仕組みを解説

① 表題と集計期間

表題ですが、テキストを直接入力するのではなく、指定した集計日に合わせて表記が変わるようになっています。

使っているのは TEXT 関数で、参照している値は「集計日付の指定」というシートの C2 のセルです。このセルには「集計したい日付」が入力されており、その値を TEXT 関数を使って文字列型に変更しています。

集計期間も「集計日付の指定」シートの C2 を参照しています。

次に、これらの表題と集計期間で参照している「集計日付の指定」シートについて解説します。

② 「集計日付の指定」シート

B 列の文言に対応する値が C 列に入るよう、関数が組まれています。それぞれの関数をご紹介します。

集計したい日付:=TODAY()-1
デフォルトでは前日の日付が入るようになっていますが、日付を手入力することもできます。

当年:=TEXT(C2,”yyyy”)

当月:=TEXT(C2,”mm”)

前年:=TEXT(C11,”yyyy”)
先に「前年の日付」を入力する必要があります。

前月:=TEXT(C12,”mm”)
先に「前月の初日」を入力する必要があります。

当月の初日:=DATE(year(C2),month(C2),1)
上ではご紹介していませんが、DATE 関数を使うとそれぞれの「年」「月」「日」のデータを合わせて日時型のデータにすることができます。
=DATE(年,月,日)

前年の当月・初日:=DATE(year(C11),month(C11),1)
上に同じで、DATE 関数を使用して日時型のデータに変換しています。

前年の日付:=EDATE(C2,-12)
現在の日付から – 12 ヶ月して、前年の同日の日付を算出しています。

前月の初日:=EDATE(C9,-1)
当月の初日から – 1 ヶ月しています。

前月の前日:=EDATE(C2,-1)
前日の日付から – 1 ヶ月しています。

③ 縦軸と横軸

縦軸にはチャネル(販売経路)の名前が入力されていますが、こちらは「店舗マスタ」というシートを参照しています。

縦軸「チャネル」

店舗マスタには店舗コードの一覧と、コードに対応する店舗名、チャネルが入力されています。

「店舗マスタ」シート

もしレポートのチャネルを増やす場合は、こちらの店舗マスタにチャネルの情報を追加し、それを参照するようにしてください。

④ 予算

予算は、「予算入力シート」を参照し、前回の記事でご紹介した SUMIFS 関数で集計しています。

SUMIFS 関数で予算の値を算出している

まず、「予算入力シート」の内容からご紹介します。
予算入力シートの項目は以下の画像のとおりで、日別・チャネル別の予算が記載されています。

「予算入力シート」の内容

以下の 4 項目は「日付」の値をもとに関数で算出されています。

  • 受注週(1 日はじまり)
  • 受注週(月曜はじまり)

「レポート」シートに戻ります。

SUMIFS 関数では、予算入力シートの「日付」「予算」「チャネル」と、集計日付の指定シートの「集計したい日付」「当月の初日」の値を参照しています。

=SUMIFS(‘予算入力シート’!$G:$G,’予算入力シート’!$A:$A,”<=”&’集計日付の指定’!$C$2,’予算入力シート’!$A:$A,”>=”&’集計日付の指定’!$C$9,’予算入力シート’!$F:$F,$C31)

この式を分解すると、以下のような意味になります。

  • 以下の条件を満たす、すべての G 列(予算)のセルの値の合計を算出
  • 条件①:予算入力シートの A 列(日付)が集計日付の指定シートの C2(集計日)以下
  • 条件②:予算入力シートの A 列(日付)が集計日付の指定シートの C9(当月の初日)以上
  • 条件③:予算入力シートの F 列(チャネル)の値が現在の左隣のセルの値と等しい

以上によって、集計日時を 2022 年 5 月 27 日としたとき、5/1 ~ 5/27 までの各チャネルの予算の合計が算出されます。

⑤ 実績

「実績」では実際の売上情報を参照して集計しています。この売上情報は、「売上情報」というシートに格納されています。

売上情報シートは、元データが格納されている「売上情報_raw」に、関数で以下の 5 つの項目を追加したものです。

  • 受注年
  • 受注月
  • 受注週(1 日はじまり)
  • 受注週(月曜はじまり)
  • チャネル
関数で項目を追加した「売上情報」シート

「レポート」シートの実績に戻ります。

例として、チャネルが Amazon の実績が表示されている E31 のセルの関数を見てみます。

=SUMIFS(‘売上情報’!$H:$H,’売上情報’!$B:$B,”<=”&’集計日付の指定’!$C$2,’売上情報’!$B:$B,”>=”&’集計日付の指定’!$C$9,’売上情報’!$K:$K,$C31)

SUMIFS 関数によって、5/1 ~ 5/27 までの Amazon での売上の合計が、「実績」として算出されているのがわかります。

⑥ 粗利額

SUMIFS 関数によって、5/1 ~ 5/27 までに各チャネルで売れた商品の原価の合計を算出し、その値をそれぞれのチャネルの実績から引いて、粗利額を算出しています。

=E31-SUMIFS(‘売上情報’!$I:$I,’売上情報’!$B:$B,”<=”&’集計日付の指定’!$C$2,’売上情報’!$B:$B,”>=”&’集計日付の指定’!$C$9,’売上情報’!$K:$K,$C31)

⑦ 前月・前年

前月の例として、J31 のセルの関数を見てみましょう。

=SUMIFS(‘売上情報’!$H:$H,’売上情報’!$B:$B,”<=”&’集計日付の指定’!$C$13,’売上情報’!$B:$B,”>=”&’集計日付の指定’!$C$12,’売上情報’!$K:$K,$C31)

「前月」の J31 の式

⑤ の「実績」の関数と比較すると、集計期間の指定が「前月の初日から集計日の 1 ヶ月前」になっていることがわかります。

前年の場合は、集計期間が「前年の同じ月の初日から前年の集計日と同日まで」となります。

サンプルをご活用ください

ご提供しているサンプルの「レポート」シートには、当日のチャネル別の売上データや週次実績、サマリなどの表が含まれています。また、「指定した期間の比較」や「時間帯別売上」のシートもありますので、ぜひ参考にしてください。

今回のシートの注意点

ここまでで解説してきた集計表ですが、店舗別の売上の比較だけでなく、前月や前年のデータと比較して分析ができるのは大きなメリットです。そのため、定期的に売上データを書き出して元データとして蓄積し、来月、来年も過去のデータと比較できるようにしておくことが重要です。

売上データは各モールやツールの管理画面からダウンロードできますが、そのデータを Google スプレッドシートに毎回貼り付けるのは手間がかかります。また、貼り付けのミスなどのヒューマンエラーが発生する可能性があります。

このような最新の売上データを Google スプレッドシートに書き出す作業は、EC 特化のノーコードツール TēPs(テープス)を使うことで、自動化することができます。

TēPs は Amazon ベンダーセントラル、Amazon セラーセントラル、楽天市場 RMS、ネクストエンジンの受注情報を、最短 15 分間隔で取得し、Google スプレッドシートに書き出すことが可能です。

ネクストエンジンの受注伝票データを TēPs で 15 分ごとに書き出す様子

こちらのサンプルの「売上情報_raw」のシートに、TēPs で売上情報を自動で書き出すように設定しておけば、店舗別の売上の最新情報をいつでも見ることができるようになります。

TēPs は 14 日間の無料トライアルができますので、ご興味がございましたらお気軽にお問い合わせください。

おわりに

いかがでしたでしょうか。

この記事が役に立ったという方は、ぜひ SNS でシェアしてください!

今回は「店舗別の売上を集計するシート」を取りあげましたが、次回は「店舗別・ブランド別の売上を集計するシート」を、ピボットテーブルと使い方とともにお送りする予定です。

関連記事

     ホーム / ブログ / 店舗別の売上を集計|EC × Google スプレッドシート #2