エクセル資源のスマートシートによる有効活用 ２

第二部　

はじめに

第一部では備品管理エクセルのシートを例に取り上げました。スマートシートにアプロードして、いくつかの比較的簡単な設定をすることにより、クラウド環境でのスマートな備品管理ができることを説明いたしました。

第二部では、更に スマートシート のモバイルアプリや高度なロジック機能を使います。備品が保管されている現場でバーコードやメニューを使って備品を選択し、入出庫の記録を行い、ダッシュボード等で現在数を表示するなどによって更に業務を効率化する方法について説明いたします。

やりたいこと　

備品の簡単な在庫管理を行うにあたって、やりたいことをリストアップします。

■　メニューから選択して、その場で出入庫処理※

スマホやタブレットで、メニューにより備品を選択するようにして、備品名をいちいち入力しなくても良くします。

スマートシートのシートにリアルタイムな在庫数が反映されます。紙やExcel台帳への記載漏れやミスを防いで、在庫管理の精度を高めれるようにします。

※　備品管理に出入庫の用語は大袈裟かもしれませんが、備品を補充することを入庫、備品を持ち出して使うことを出庫と考え、在庫管理一般の用語にあわせています。

■　バーコード・QRコードで 入庫・出庫処理を効率化

モバイルアプリはバーコードやQRコードを読むことが出来るので、備品のバーコードを読み取って入出庫数を入力する方式にすることが出来ます。

集計作業も自動で行い、作業後の面倒な作業を無くすようにします。

■　スマートシートのシート上で出入庫の履歴を管理

誰が、いつ、何個、備品を購入・使用・補充したかを記録。備品の活用状況の分析も可能となるようにします。

具体的には出入庫のたびに、記録をとっていき、その記録から現在数が求められるようにします。

■　発注点を下回ったらメールでお知らせ

指定された在庫数を下回った場合に担当者にアラートメールを配信して在庫切れ・発注漏れを防止することが出来るようにします。

出来上がりのイメージ

フォームの入力結果が反映されるのに１分程度かかります。

スマートシートによる具体的実現方法

■　スマートシートのシート上で出入庫の履歴を管理

メニューやバーコードでの入力の前に、どのようなシート等の構成にするか考えます。

ここでは入出庫の記録を管理する「簡単な備品の在庫管理 入出庫記録」シートに出入庫の記録をつけます。
そして、前回作ったガント等の表示できるシートに関数で現在数を自動入力するようにします。
なお、「簡単な備品の在庫管理表」（備品管理表から名前を変更しました。）

①スマホで JAN CODE を読み取り入出庫数を登録

②JAN CODE を元に備品名等を取得、③備品名をキーとして入出庫数を集計

シート間の数式（クロスシート計算式）

「簡単な備品の在庫管理表」 から別のシートの「簡単な備品の在庫管理 入出庫記録」のデータを集計するのには、シート間の数式を使います。

クロスシート計算式とは？
　シートをまたぐ数式は、別のシートのデータを参照して計算を行ったり、特定のセルの情報を検索したりします。計算で代表的なのがSUMIF(), COUNTIF()で、検索で代表できなのがVLOOKUPです。

SUMIF()関数

シート間の数式で使えるこの関数の構文は

SUMIF( 範囲, 条件, [ 合計範囲 ])
　範囲 — 評価するセルのグループ。　
　条件 — 加算する数値を定義する条件 。
　合計範囲 —[任意] 加算するセルのグループ。
で、範囲の設定で別のシートを指定できます。

具体的には
=SUMIF({goods_name}, 備品名@row, {updated_qty})
と式が入っています。

最初の評価するセルの範囲として {goods_name} が指定されていますが、これは別のシートである 「簡単な備品の在庫管理 入出庫記録」 の以下の範囲です。
なお、 {goods_name} は範囲を設定するときに分かりやすいように付けた任意の名前です。

式の{goods_name}, 備品名@ の部分の意味は以下のとおりです。

　 「簡単な備品の在庫管理表」 の備品名の列のデータと上記の範囲 {goods_name} のデータを比べて、一定しているものを集計対象にする

これにより備品名毎の集計を行います。

次に 合計範囲 として、 {updated_qty} の範囲が指定されていますが、これは２つ隣の数の列です。

以上により「元のシートの備品名を元に入出庫のシートの備品名が一致しているものの数の合計」を求めます。

これにより、各備品ごとの現在数が求められます。

■　メニューから選択して、その場で出入庫処理※

最初のメニューで備品の分類として、事務用品、販促品、PC関連等を選択。
事務用品を選択すると、次のメニューで事務用品のリストが選択できます。
販促品を選択すると、次のメニューで販促品のリストが選択できます。

階層メニュー (Dependent Drop Down List)

このような方法を階層メニュー（英語ではDependent Drop Down List とか Conditiona List とか）といって Appsheet をスマートシートにつなげて利用するメリットの一つとして別の記事で紹介しました。

Appsheetの階層メニューはドロップダウンリスト用の表を別に作る方法で動態的なリスト内容の変更が可能で使いやすいです。

利用者数に比例するAppsheetの料金体系

しかし、Appsheet の料金体系は利用者数×5ドル又は１０ドル（月）で、バーコードのスキャンには１０ドルのアカウントが必要です。
また、後に説明するように誰が入力したかを正確に記録するには、アノニマス（無記名）のフォームは不適当です。

例えば、筆者が以前属していた日本郵便のような組織の場合、利用する可能性のあるユーザーが何十万人もいるので利用者数に利用料金が比例するタイプのサービスは現実的には利用が困難です。

そこで、スマートシートの機能だけで階層メニュー層相当のことを行うようにしてみます。

フォームの条件付きロジックにより対応するメニューを有する列を表示させる

まず、フォームで最初に事務用品、販促品等の分類を選択します。
次に、その選択に基づいて、例えば、販促品が選択された場合は、販促品のカロリーメイト、カカオ８８等がドロップダウンリストとして設定されている列を表示するようにします。

IF関数により一つの列に集約する

以上のようにすると、備品名が別々の列に入力されるので、IF関数により一つの列に集約するようにします。

具体的には
=IF(menu@row = “販促品”, 販促品menu@row, IF(menu@row = “事務用品”, 事務用品menu@row))
という式が入っています。

式は、ある行で、menuの列に販促品が入っていたら、menu備品名の列に販促品menuの列の該当する行の備品名を入れる、という意味です。

ここでは販促品と事務用品を条件とするIF文が書いてありますが、必要に応じPC、消耗品等の分類を追加します。
上記のフォームのロジックも同様です。

■　バーコード・QRコードで 入庫・出庫処理を効率化

スマートシートのモバイルアプリ

Smartsheet モバイル アプリを使えば、どこにいても Smartsheet にアクセスできるので、備品の在庫のある場所で、入力などの作業ができます。

フォームの入力には、 Smartsheet モバイル アプリ でなくてもウェブのフォームとして行うことはできます。
しかし、スマートフォンからバーコードのスキャンをおこなうには Smartsheet モバイル アプリ が必要です。

なお、このほかに、 Smartsheet モバイル アプリ によって写真をその場で撮って添付したり、GPS機能により緯度経度を入力することも可能です。

モバイルアプリの起動方法（フォームのQRコードから）

フォームの管理から共有を選択しフォームのURLを取得します。

このURLからもフォームを開くことが出来ますが、このURLをQRコードにしてスマホから簡単にアクセスできるようにします。上のダッシュボードに貼り付けたQRコードを拡大したものが下のQRコードです。

上のQRコードを読み込むとフォームのURLが取得できるので、以下のようにサンプルのカロリーメイトまたはコクヨノートのJAN CODEを読み取り、入出庫登録することにより現在数が更新されるか確認します。
（ダッシュボード上で更新されるには約１分ほどかかります。）

シート間の数式

「簡単な備品の在庫管理 入出庫記録」のJANコードから別のシートの「備品マスター」備品名等を取得するには、シート間の数式を使います。

VLOOKUP()関数

シート間の数式で使えるこの関数の構文は

VLOOKUP( 検索する値, 検索するセル範囲, 返される値の列の位置, 検索方法)
　検索する値: 最初の列にある必要があります。
　検索するセル範囲: 左端の列と検索結果としての戻り値の両方が含まれます。
　返り値の列の位置: 検索するセル範囲の左端の列 1 は 1 として検索する値のある列が何番目か指定します。
　検索方法: 完全一致 (false) または近似一致 (true) のどちらで検索するかを指定します。

で、範囲の設定で別のシートを指定できます。

具体的には
=IFERROR(VLOOKUP([JAN CODE]@row, {bar_code_to_goods_name_2}, 2, false), “”)
と式が入っています。

検索するセル範囲 として {bar_code_to_goods_name_2} が指定されていますが、これは別のシートである 「備品マスター」 の以下の範囲です。
なお、 {bar_code_to_goods_name_2} は範囲を設定するときに分かりやすいように付けた任意の名前で、_2は返り値が２番目にあることを示す自分用のメモです。

式はbar_codeの列が元のシートのコードと同一のものを探して、それに対応する２列目、goods_nameすなわち備品名を返す、という意味です。

■　スマートシートのシート上で出入庫の履歴を管理

上のダッシュボードにQRコードを掲示したフォームでは、Smartsheetへのログインを求めていません。読者の皆さんが気軽に試したいただけるようにするためです。

しかし、誰が 出入庫 の登録をしたかを記録するためには、 Smartsheetへのログイン を求めるようにします。

同じノートの出庫と入庫をログインありとログインなしでやったのが下の図です。

ログインが要求されていない状況では、作成者はアノニマス（無記名的）なweb-form@smartsheet.comとなっていますが、ログインを要求した後は、jmiyazaki@cloudsmart.jpと私が入力したことが記録されています。

この表の作成日時と作成者は「システム生成列」という列タイプで、SmartsheetのSystemが自動的に取得します。

これにより誰が、いつ、記録したかが誤魔化しのない形で記録することできます。

何個、入出庫の別なども、セルの履歴を見ると、ログインしている場合は、誰が何時その編集を行ったかが記録されます。

■　発注点を下回ったらメールでお知らせ

一定の条件（トリガー）が満たされた場合に、何かのアクション（例、メールでお知らせ）を自動で行うようにするにはワークフローを用います。

ワークフロー

ワークフローを作成するには上のメニューから自動化をクリックします。

右の図のように、トリガーとして「行が追加または変更された場合」を選び、次の場合として、「使用率」が何らかの値に変更され、条件として「使用率が１００％以上」の場合に、セルに含まれる連絡先に送信、を選び「発注担当」にアラートを送信する、とワークフローを設定します。

なお、送信文を以下のように編集すると{{}}で囲まれたセルの情報を代入して、カスタマイズしたメッセージを送ることが出来ます。

これにより、指定された在庫数を下回った場合（使用率100％超）に担当者にアラートメールを配信して在庫切れ・発注漏れを防止することが出来るようになります。

まとめ

今回は前回に比べて、
１　シートが「備品ごとの現在数等を表示するシート」、「入出庫を記録するシート」、「備品名とJAN CODE等の関係を示す備品マスタ」と三種類に分かれていたこと
２　その３つのシート間で、数式による集計や値の参照、フォームのロジック、ワークフロー等、ロジックを使うものが多かったこと、
から少し分かりにくかったかもしれません。（というか、読み返してみて、これで一発で理解できる人は相当頭のいい人です。）

Youtubeの方も用意したいと思いますので、よく分からなかった方は、アップされた時点で、この記事を参考資料として活用いただければ幸いです。

• 学んだこと
  • シート間の数式
    • 現在数の集計用　→　SUMIF()
    • バーコードから備品名を取ってくる →　VLOOKUP()
  • フォームのロジック
    • 依存ドロップダウンリスト用
  • モバイルアプリのバーコード等入力
  • IF式による列をまたいだ値のまとめ
  • 簡単なワークフロー（送付文での{{セル値}}の活用）

残課題（次回以降）

難しそうなので、今回は取上げませんでしたが、棚卸で帳簿上の数と実際の在庫数を調整するフローについては、次回以降に挑戦したいと思います。