「記録」だけで終わらせない！在庫の“見える化”がカギ

前回 は、Googleフォームを使って誰でも簡単に入出庫データを登録できる仕組みと、スプレッドシートで在庫数を自動集計する方法をご紹介しました。
スプレッドシートが ”リアルタイム在庫一覧表” になる実感を得ていただけたと思います。
今回は、その記録データをさらに活用し、複数の切り口で集計・分析して、見える化する方法に踏み込んでいきます。

入出庫履歴をFILTER関数で絞り込み

「今月の出庫だけ」「特定商品だけ確認したい」といったニーズには、FILTER関数を使った履歴表示がおすすめです。

前回 のフォームで登録すると、スプレッドシートでは次のようなデータになります。

実例：今月の出庫数を集計する

新しいシート「今月の集計シート」を作り、「商品」と「今月の出庫数」列を用意します。
そして、「今月の出庫数」列に以下の数式を入力します。

=SUM(
    FILTER(
        'フォームの回答'!E:E,                         // ←【合計範囲】
        'フォームの回答'!D:D=A2,                      // ←【条件式1】　A列に商品名を入力
        'フォームの回答'!F:F="出庫",                  // ←【条件式2】
        MONTH('フォームの回答'!C:C)=MONTH(TODAY()),   // ←【条件式3】
        YEAR('フォームの回答'!C:C)=YEAR(TODAY())      // ←【条件式4】
    )
)

各パーツの意味

1. 【合計範囲】 'フォームの回答'!E:E
   ここでは、フォーム回答の E 列（数量）すべてが合計対象になります。
2. 【条件式1】'フォームの回答'!D:D = A2
   フォーム回答の D 列（商品名）を見て、今月の集計シート A2 セルの値「角形ハンガー A-45」に該当する行だけに絞ります。
3. 【条件式2】 'フォームの回答'!F:F＝“出庫”
   フォーム回答の F 列（入庫or出庫）を見て、「出庫」とマークされた回答だけに絞ります。
4. 【条件式3】 'フォームの回答'!C:C＝MONTH(TODAY())、【条件式4】 'フォームの回答'!C:C＝YEAR(TODAY())
   フォーム回答の C 列（日付）を見て、同じ年の今月分だけに絞ります。

この記述で商品ごとの今月の出庫数を動的に計算できます。

QUERY関数＋IMPORTRANGE関数 で複数店舗を一括集計

複数の店舗を運営している場合、各店舗のデータを一括で可視化できます。

A店舗とB店舗の入出庫の記録は別ファイル（スプレッドシート）で管理していると想定して、それらの情報を合計してみましょう。主に使用する関数は次の3つです。

この組み合わせをマスターすれば、スプレッドシートをまるでミニデータベースのように使いこなせるようになります。

新しいシート「複数店舗の集計シート」を追加し、各店舗の入出庫データを記録しているスプレッドシートのURLおよびシート名を、以下のように入力します。

A店舗の情報
　Ｂ２セル＝A店舗の入出庫履歴スプレッドシートのURL（例：https://docs.google.com/spreadsheets/d/ファイルのID/edit）
　Ｃ２セル＝A店舗の入出庫履歴スプレッドシートのシート名（例：フォームの回答シート）
B店舗の情報
　Ｂ３セル＝B店舗の入出庫履歴スプレッドシートのURL
　Ｃ３セル＝B店舗の入出庫履歴スプレッドシートのシート名

そして、以下の数式をＡ６セルに入力します。
この数式は、A店舗とB店舗の入出庫データを取り込んで、商品別の在庫数を自動集計するものです。

=QUERY(
  //QUERY関数［第1引数］： data — 対象範囲または配列
  {
    // > A店舗の入出庫履歴
    IMPORTRANGE($B$2, $C$2&"!C:G"),
    ARRAYFORMULA(IF(IMPORTRANGE($B$2, $C$2&"!F:F")="入庫", VALUE(IMPORTRANGE($B$2, $C$2&"!E:E")), 0)), 
    ARRAYFORMULA(IF(IMPORTRANGE($B$2, $C$2&"!F:F")="出庫", VALUE(IMPORTRANGE($B$2, $C$2&"!E:E")), 0)); 

    // > B店舗の入出庫履歴
    IMPORTRANGE($B$3, $C$3&"!C:G"),
    ARRAYFORMULA(IF(IMPORTRANGE($B$3, $C$3&"!F:F")="入庫", VALUE(IMPORTRANGE($B$3, $C$3&"!E:E")), 0)),
    ARRAYFORMULA(IF(IMPORTRANGE($B$3, $C$3&"!F:F")="出庫", VALUE(IMPORTRANGE($B$3, $C$3&"!E:E")), 0))
  },

　//QUERY関数［第2引数］： query_string — SQL ライクな命令
  "SELECT Col2, SUM(Col6), SUM(Col7), SUM(Col6)-SUM(Col7)
   WHERE  Col3 is not null
   GROUP BY Col2
   LABEL SUM(Col6) '入庫合計', SUM(Col7) '出庫合計', SUM(Col6)-SUM(Col7) '在庫数'",

  //QUERY関数［第3引数］： ヘッダー行数（省略可）
  1
)

これにより、A店舗・B店舗の合計在庫数を商品別に一括集計できます。

// > A店舗の入出庫履歴
//↓① 店舗Aの入出庫履歴シート（Ｂ２セルに入力したスプレッドシートの Ｃ２セルに入力したシート）の
//      Ｃ列からＧ列までの情報すべて（日付、商品名、数量、入庫or出庫、担当者 の 5列分）
IMPORTRANGE($B$2, $C$2&"!C:G"),

//↓② 店舗Aの入出庫履歴シートのＦ列（入庫or出庫）が「入庫」の場合E列（数量）の値を入庫数とする
ARRAYFORMULA(IF(IMPORTRANGE($B$2, $C$2&"!F:F")="入庫", VALUE(IMPORTRANGE($B$2, $C$2&"!E:E")), 0)),

//↓③ 店舗Aの入出庫履歴シートのＦ列（入庫or出庫）が「出庫」の場合E列（数量）の値を出庫数とする
ARRAYFORMULA(IF(IMPORTRANGE($B$2, $C$2&"!F:F")="出庫", VALUE(IMPORTRANGE($B$2, $C$2&"!E:E")), 0));

//↓① SELECT句で、取得したい情報を指定します。
//     QUERY関数の第1引数で指定したデータの１列目が Col1、２列目が Col2…　というように指定します。
//     今回の例では、Col1＝日付、Col2＝商品名…、Col6＝入庫数、Col7＝出庫数　となります。
//     また、在庫数は 入庫数－出庫数 として計算します。
SELECT Col2, SUM(Col6), SUM(Col7), SUM(Col6)-SUM(Col7)

//↓② WHERE句で、取得対象の条件を設定します。
//　　数量（Col3）に数値が入力されているものだけを対象とします。
WHERE  Col3 is not null

//↓③ GROUP句で、集計対象の情報を指定します。
//　　商品（Col2）で集計します。
GROUP BY Col2

//↓④ LABEL句で、SELECT句で指定した情報の見出し名を指定します。
//　　指定していない場合は、QUERY関数の第3引数が「1」であるため、最初の1行を「データの見出し」として使います。
LABEL SUM(Col6) '入庫合計', SUM(Col7) '出庫合計', SUM(Col6)-SUM(Col7) '在庫数'

期間を指定して集計する

QUERYの抽出条件を工夫すれば、さまざまな値の集計が可能です。たとえば、指定した期間の出庫数の合計を集計してみましょう。

期間の指定を追加します。Ｆ２セルに集計開始日、Ｆ３セルに集計終了日を入力します。

そして、Ａ６セルの数式は、WHERE句で、期間指定する条件を追加します。

=QUERY(
  //QUERY関数［第1引数］： data — 対象範囲または配列
  {
    // > A店舗の入出庫履歴
    IMPORTRANGE($B$2, $C$2&"!C:G"),
    ARRAYFORMULA(IF(IMPORTRANGE($B$2, $C$2&"!F:F")="出庫", VALUE(IMPORTRANGE($B$2, $C$2&"!E:E")), 0));

    // > B店舗の入出庫履歴
    IMPORTRANGE($B$3, $C$3&"!C:G"),
    ARRAYFORMULA(IF(IMPORTRANGE($B$3, $C$3&"!F:F")="出庫", VALUE(IMPORTRANGE($B$3, $C$3&"!E:E")), 0))
  },

  //QUERY関数［第2引数］： query_string — SQL ライクな命令
  "SELECT Col2, SUM(Col6) 
   WHERE  Col3 is not null

     AND Col1 >= date '"&TEXT($F$2,"yyyy-MM-dd")&"'
     AND Col1 <= date '"&TEXT($F$3,"yyyy-MM-dd")&"'

   GROUP BY Col2
   LABEL SUM(Col6) '出庫合計'",

  //QUERY関数［第3引数］： ヘッダー行数（省略可）
  1
)

これで、A店舗とB店舗の商品別の指定した期間の出庫数を集計できます。

ピボットテーブルで直感的に集計

大量データでも、簡単に集計できるのがピボットテーブルの魅力です。
今回は、月ごと・商品ごとの出庫数の合計を例に紹介します。

この操作で、月別商品別の出庫数が簡単に集計できます。

グラフで視覚的に把握

作成したピボットテーブルを元に、グラフを作成しましょう。

1. ピボットテーブルの中のセルをクリック → 「挿入」→「グラフ」を選択
2. グラフの種類は「折れ線」または「縦棒」の積み上げタイプなどを選択
   今回は「縦棒」を選択しました

このようにグラフを活用すれば、『どの商品がいつよく出ているか』を直感的に把握でき、売上の偏りや在庫補充のタイミングも一目瞭然になります。

まとめ