在庫管理の新しいカタチ
記録を見える化!在庫管理に活かそう

第3回:自動集計と在庫一覧をスプレッドシートで可視化!記録を“見える化”して活かす仕組み

※本稿は連載の第3回目です[最初から読む

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

前回 は、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:CMONTH(TODAY())【条件式4】 'フォームの回答'!C:CYEAR(TODAY())
    フォーム回答の C 列(日付)を見て、同じ年の今月分だけに絞ります。

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

FILTER関数を使った集計結果イメージ

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

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

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

関数名概要
QUERYSQLライクにデータを抽出・集計・整形
IMPORTRANGE他シートからデータを取り込む
ARRAYFORMULA複数セルで一括計算
範囲(例:列全体や行全体)を指定して、一度の入力で複数のセルに結果を生成

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

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

A店舗の情報
 B2セル=A店舗の入出庫履歴スプレッドシートのURL(例:https://docs.google.com/spreadsheets/d/ファイルのID/edit)
 C2セル=A店舗の入出庫履歴スプレッドシートのシート名(例:フォームの回答シート)
B店舗の情報
 B3セル=B店舗の入出庫履歴スプレッドシートのURL
 C3セル=B店舗の入出庫履歴スプレッドシートのシート名

そして、以下の数式をA6セルに入力します。
この数式は、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店舗の合計在庫数を商品別に一括集計できます。

複数店舗の商品別在庫数集計
数式の意味

QUERY関数[第1引数]:data-対象範囲または配列
ここでは、A店舗とB店舗の入出庫履歴のデータを指定します。

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

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

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

各データ取得関数の末尾の「,」または「;」で、次に指定するデータをどのように結合するかが決まります。
   , :横に結合→列として追加
   ; :縦に結合→行として追加
①,②,③ で、店舗Aの入出庫履歴シートの日付、商品名、数量、入庫or出庫、担当者、入庫数、出庫数 のデータが取得でき、その後に、店舗Bの同様のデータを追加したデータになります。

QUERY関数[第2引数]:query_string-SQL ライクな命令
そして、第1引数で指定したデータに対して、SQLライクに集計していきます。

//↓① SELECT句で、取得したい情報を指定します。
//     QUERY関数の第1引数で指定したデータの1列目が Col1、2列目が 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関数[第3引数]:headers-ヘッダ行数(省略可)
本例では、入出庫情報のスプレッドシートにヘッダ行が1行あるため「1」とします。

期間を指定して集計する

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

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

QUERY関数の集計で使う条件入力ボックスを追加したイメージ

そして、A6セルの数式は、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. 表示された小窓で「作成」ボタンをクリック
    初期値は”新しいシート”のため、シートが追加されます
  1. ピボット テーブル エディタにて、条件を指定
    ・行 「追加」 をクリックし、「日付」を選択
    ・列 「追加」 をクリックし、「商品」を選択
    ・値「追加」をクリックし、「数量」を選択
    ・フィルタ「追加」をクリックし、「入庫 or 出庫」を選択 → ”出庫”のみチェック
  2. 日付での集計になっているので、年月での集計にする
    ピボットテーブル出力後、日付列を右クリック→「ピボット日付グループを作成」→「年-月」を選択

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

月ごと商品ごとの出庫数合計

グラフで視覚的に把握

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

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

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

まとめ

  • FILTER/SUM/QUERY/IMPORTRANGE/ARRAYFORMULA などの関数で、
    商品・店舗・期間別の集計が自在になります
  • ピボットテーブルとグラフ を併用することで、データを視覚化し、洞察が深まります
  • 見える化により、売れ筋分析と補充判断を効率化し、業務負担を削減できます

次回の予告

次回は、通知機能を追加して「在庫が少なくなったらアラートを出す」などの仕組みをご紹介します。
Google Apps Scriptで在庫管理に“自動化の魔法”をかけていきましょう!

フォーム設計から関数構築・可視化まで、当社がワンストップで完全サポートします。導入支援・設定代行・社員研修・運用体制構築など、お悩みごとがあればぜひお気軽にご相談ください!

在庫管理の新しいカタチ の記事一覧

この記事は役に立ちましたか?

参考になりましたら、下のボタンで教えてください。

コメント

この記事へのコメントはありません。

PAGE TOP
ログイン