【サンプル有】EC の店舗別の売上を Google スプレッドシートで集計｜EC × Google スプレッドシート #2

できること

2022.6.16

前回：直近の EC 売上を分析｜EC × Google スプレッドシート #1
次回：店舗・ブランド別の売上を Google スプレッドシートで集計｜EC × Google スプレッドシート #3

この記事では、EC 店舗の受注データから、売上を店舗別で集計するシートを作成していきます。サンプルのシートは、どなたでもダウンロード可能です。サンプルを見ながら、集計の仕組みや関数を丁寧に解説していくので、ぜひチャレンジしてみてください。

今回は、以下のような集計表を解説します。チャネル（店舗）ごとの売上の、前月・前年比をひと目で確認できるので、売上が変化した要因を推測しやすいというメリットがあります。

サンプルを以下からダウンロードし、さっそく始めていきましょう。
サンプルを入手

集計に使う元データについて

必要な項目は以下のとおりです。各モールや受注管理システムの管理画面から、受注データをダウンロードしてください。

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

TēPs（テープス）を使えば、楽天市場や Amazon、Yahoo!ショッピングなど、さまざまな店舗の受注データを自動で取得し、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」と表示されます。

一方、C2 の条件式を

A2 > 10

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

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

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

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

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

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

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

になります。

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)

EDATE 関数

=EDATE(日付, 数字)

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

VLOOKUP 関数

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

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

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

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

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

「検索範囲」に参照したい値のある列まで含めることに注意してください。
この場合は、店舗コードの値がある列は「店舗マスタ」の A 列だけですが、「店舗マスタ」の C 列の値を参照したいので、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 関数で集計しています。

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

以下の 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)

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

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

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

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

おわりに

今回の集計表は、店舗別の売上の比較だけでなく、前月や前年のデータとの比較も可能だという点が、大きなメリットです。そのため、日々の売上データを Google スプレッドシートに蓄積し、来月、来年も過去のデータと比較できるようにしておくことで、より深い分析が実現できます。

TēPs（テープス）を使えば、楽天市場や Yahoo!ショッピング、Amazon や Shopify など、プラットフォームの異なる店舗の受注データを自動で取得し、Google スプレッドシートに定期的に書きだすことが可能です。放置していても、最新の販売状況をひと目で確認できるようになるので、すばやく効率よく売上を分析できるようになります。