スマレジExcelドライバで発注残を計算する方法

by 浦邊信太郎 | 2020年12月21日

こんにちは。CData Software Japanエンジニアの浦邊です。

スマレジには入出荷や在庫データを一覧できる機能があり、ある程度の販売分析や在庫分析はスマレジの画面上で行うことができます。 しかし、より実用的な分析を行うには、発注残や受注残、理論在庫など、入出荷や在庫などの一次データから算出されるデータが必要で、これを計算するにはExcelなどのツールとの連携が欠かせません。

本記事ではスマレジの発注データをAPI経由でExcelに読み込み、商品ごと、また店舗ごとの発注残数量を計算する方法を紹介します。

Excelスマレジへの接続にはCData Softwareの「CData Excel Add-In for Smaregi」を使います。 ドライバの入手、インストール方法、その他基本的な使い方については以下の記事を参考にしてください。

https://www.cdata.com/jp/blog/2020-02-11-152211

データ読み込み方法

以下ではCData Excel Add-In for Smaregiでの基本的なデータ読み込み方法を説明します。

単一テーブルの読み込み

単一のテーブルを読み込む場合、[CData テーブル選択]で目的のテーブルを選択し、[OK]をクリックします。

読み込むフィールドを絞り込む場合は[カラム]をチェックします。

読み込むレコードの条件を設定する時は[フィルタ]を設定します。

結合されたテーブルの読み込み

複数のテーブルを結合して読み込む場合はテーブル結合のSQLクエリを記述します。 [SQLクエリ]をクリックしてSQLクエリ入力欄を表示してください。

二つのテーブルを結合する基本的なSQL文は以下の通りです。

SELECT * from [テーブル1] join [テーブル2] on [テーブル1].[キー] = [テーブル2].[キー]

テーブル1とテーブル2の結合キーが同じ名前の場合は以下のようにusing句を使用できます。

SELECT * from [テーブル1] join [テーブル2] using ([キー])

以下はShinpments(出荷)テーブルとShipmmentDetails(出荷明細)を結合する例です。

発注関連テーブルの読み込み

スマレジの発注に関するテーブルの関連を以下に示します。

商品ごと、仕入先ごとの発注残を算出するためには、StorageInfoテーブルから個々の発注のステータス(入荷完了かどうか)を、StorageInfoDeliveryProductsテーブルから発注の商品や仕入先を取得する必要があります。 そのため、今回はStorageInfoとStorageInfoDeliveryProductsとを、発注のId(StorageId)をキーとして結合します。

StorageInfoとStorageInfoDeliveryProductsとを結合するために、SQLクエリーとして以下のSQL文を設定します。

select * from StorageInfoDeliveryProducts join StorageInfo using (StorageInfoId)

このクエリーでデータを読み込むと、以下のように結合されたデータが取得できます。

発注残の計算

続いて商品ごと、店舗ごとの発注残を算出します。 算出にはExcelのピボットテーブル機能を使います。

ピボットテーブルの使い方については以下のMicrosoftのサポート情報をご覧ください。

https://support.microsoft.com/ja-jp/office/%E3%83%94%E3%83%9C%E3%83%83%E3%83%88%E3%83%86%E3%83%BC%E3%83%96%E3%83%AB%E3%82%92%E4%BD%9C%E6%88%90%E3%81%97%E3%81%A6%E3%83%AF%E3%83%BC%E3%82%AF%E3%82%B7%E3%83%BC%E3%83%88-%E3%83%87%E3%83%BC%E3%82%BF%E3%82%92%E5%88%86%E6%9E%90%E3%81%99%E3%82%8B-a9a84538-bfe9-40a9-a8e9-f99134456576

メニュー[挿入]から[ピボットテーブル]をクリックし、[ピボットテーブルの作成]ダイアログを開いてください。 [テーブルの範囲]に読み込んだ全データの範囲、配置場所に[新規ワークシート]を選択し[OK]をクリックしてください。

以下のように新規シートに「ピボットテーブルのフィールド」が表示されます。

発注残レコードの絞り込み

読み込んだ発注データから入荷済みの発注を除外します。 発注が入荷済みかどうかを判定するには、StorageInforテーブルのStatus(状態)を参照します。 ヘルプのStorageInforテーブルの項に、以下の通りStatusの値の意味が書いてあります。

• 2：商品分配済
• 3：入荷検品中
• 4：入荷完了
• 5：仮発注商品分配済

今回は、状態が「5：仮発注商品分配済」の発注を発注残とみなします。 フィールドの「Status」を右クリックし、「5」の以外チェックを外します。

これで仮発注以外の発注が計算から除外されます。

商品ごと発注残数量の表示

フィールドから「ProductName」を「行」に、「Quantity」を「値」にドラッグしてください。 シートに商品名ごとの発注残数量が表示されます。

商品ごと・店舗ごとの発注残数量の表示

商品名ごとの発注残数量が表示された状態で、フィールドから「StoreId」を「列」にドラッグしてください。 ピボットテーブルの列が店舗Idごとに分けられ、店舗ごとの発注残数量が表示されます。

期間の指定

対象データの期間を指定します。 ピボットテーブルでの期間のフィルタリングでは「タイムライン」という機能を利用すると直感的な操作で期間を指定できます。 フィールドの「OrderdDate」の上で右クリックし、コンテキストメニューの「タイムラインとして追加」を選択します。

画面にタイムラインのウィンドウが表示されます。

このタイムラインでデータの期間を調整することができます。 例えば2018年1月～3月までのデータのみを表示する場合は以下のように期間を選択します。

データの更新

[CData]タブの[ワークシートのリフレッシュ]をクリックするとスマレジの最新のデータが反映されます。

最新のデータでピボットテーブルを更新するには、ピボットテーブルの任意の場所で右クリックし「更新」をクリックしてください。

おわりに

本記事ではExcelを使ってスマレジの発注データから発注残データを計算する方法を紹介しました。 発注残の計算に必要なデータは二つの発注関連テーブルにまたがっているため、テーブルを結合させて読み込む必要があります。 しかし、初回のデータ読み込み時にテーブル結合のクエリーを作成してしまえば、日々の運用ではリフレッシュ操作のみで最新のデータにアクセスできます。

CData Excel Add-In for Smaregi

今回スマレジとのデータ連携にはCData Excel Add-In for Smaregiを使いました。 CData Excel Add-In for Smaregiは30日間の評価版を無料で利用できます。 スマレジとクラウドサービスとのデータ連携をご検討の方はぜひお試しください。

関連コンテンツ