過剰在庫(不動在庫、余剰在庫)はまずは返品するのが基本ですが、包装変更品や、冷所保管品、うっかり開封してしまったものなど、返品ができず困りますよね。
それら過剰在庫をグループ内の薬局で使用している店舗に商品移動して使ってもらえば無駄がなくなり、在庫も減るのでとても合理的です。
では、皆さんの薬局では、他店舗に過剰品目をもらってもらうのにどのような作業をしていますか？
グループで採用している、いわゆる「本部システム」「在庫管理システム」によってその機能は様々ですが、多くのシステムでは、【他店舗照会】みたいな画面で薬品名を入力して、他店の在庫状況（現在の在庫数、最終処方出庫日、最終入庫日）などが一覧表示されて、その画面を見て当該過剰品目の移動先の目星をつけて、引き取りを依頼する。という流れが多いのではないでしょうか。
ただ、この照会方法では１）薬品名数文字を入力して「検索」して、２）検索文字に該当する医薬品候補が表示されて、３）その中から当該医薬品を選択確定して、やっと４）在庫状況が表示される、という画面遷移のシステムが多いのではないでしょうか。
これを1品目ずつやっていると、あほらしいほどの時間が必要です。
「あほらしい」と思える人は優秀です。もっと楽な方法を探すことにつながるからです。当ブログのようなサイトに辿り着く大事な「動機」です。
では、その面倒くさい作業をどのように楽にするかを紹介していきましょう。
過剰品をもらってもらうためには、その品目を
- 採用している
- 定期的に処方出庫している
ことを確認できれば頼みやすいし、当てが外れてやり直し、というのを避けられます。
当薬局で採用している在庫管理システムは、医薬品卸のアルフレッサから提供されている「JustockEX Ⅲ」というWebサービスです。（ちょっと前ならASPとかSaaSとか言われていたサービスです）
特長としては、グループ薬局の在庫状況などを照会できることです。
残念なのは、サービスにアクセスできる端末を特定の1台のみに限定されていることですね。
今回はJustockEXから出力できる帳票(データ)で、他店の薬品の使用状況を確認するためのデータ集計方法を紹介します。タイトルにもありますが、超簡単です。
また、その他の在庫管理システムや本部システムでも同じような内容の帳票データが出力できるはずですので、基本的には同じような考え方で大丈夫です。
ポイントは、薬品ごとの①月間の処方量、②月末の在庫数がデータに含まれていればOKです。
各店データを帳票出力する
JustockEXから帳票出力＞「採用薬在庫一覧」の画面で、グループ薬局の名称と出力対象年月を変更しながら各店舗12～15か月分くらいを出力していきましょう。
１：①の薬局選択欄でグループ薬局を順に選択
２：②の出力対象月で年、月を選択
３：医薬品区分欄は空白のまま
４：医薬品状態欄は「全て」
５：出力対象欄は「全て」
６：YJ/JAN表示選択欄はどちらでもいいですが今回は「YJ」で例示しました
７：左下の「明細表示」ボタンを押す
８：次にポップアップされる画面で左下の「データ出力」を押し、任意のフォルダに名前を付けて保存します
店舗、対象年月を変更しながら「明細表示」＞「データ出力」を選択し、PCの任意の場所にデータを保存します。ファイル名は分かりやすく「〇〇店yyyymm」みたいな感じで構いません。
例えば12か月4店舗分なら48回作業を繰り返します。
最初は面倒くさいですが、次からは4店舗1か月分ずつなので大した作業にはなりません。ということで初回頑張って作業しましょう。
データを集計する
PCに保存した48ヶのファイルを順に開き、1つの集計用のExcelファイルに、下に下にと転記(コピー＆ペースト)していきます。
この時に、ちょっとしたExcelテクニックがあります。
移したい元データのファイルを開いたときにセルは一番左上の「A1」セルが選択されています。1行目はタイトル行となっていますので、データだけをコピーしたい場合は、「Enter」キーを1回押すか、「↓」キーを押して「A2」セルを選択して、
「Ctrl」＋「Shift」＋「↓」で最下行まで選択、さらに「Ctrl」＋「Shift」＋「→」で最右行を選択します。
※決してマウスを使ってA2セルから右下のセルまでドラッグしようとしてはいけません！！
データ行がすべて選択できたら「Ctrl」＋「C」でコピーします。
※マウス右クリック＞コピーを選択、でも構いませんが、何度も同じ行を繰り返すのでキーボード操作に慣れた方が絶対早くなります
基になるデータには「年月」を示す項目列がないので、集計用のファイルに２列を追加してA列に「年」、B列に「月」を入力するようにしておきます。
集計用ファイルを選択し、C列の最下行にデータを転記(ペースト)し、A列、B列には当該「年」「月」を全ての行に入力します。これがとても重要です。
コピーを終えたファイルを閉じて、次のファイルを開き同じ作業を繰り返していきます。
1店舗平均1500品目だとすると、
1500品目 ｘ 4店舗 ｘ 12か月 ＝ 7万2千行 のExcelファイルとなります。
Excel2003までは1シートで扱える行数は約5万6千行まででしたが、Excel2007以降は一気に約100万行を扱えるようになりましたので、全然余裕です。
こうして48のファイルを全て一つの集計用Excelシートに貼り付けたら、ひとまず適当な名前を付けて保存しておきましょう。
不要データを削除
このデータの中には在庫も０、処方も0、というデータがあります。この不要なデータを削除すればデータ行数は3～5割くらい減ることもあります。
データ量が少なければ、オートフィルタをかけて、「在庫数」列のフィルタを「０」、「処方量」列のフィルタを「０」に絞り込んで、タイトル行以外をすべて選択した状態で「削除」すればOKです。(データ量が多いと、Excelに負荷がかかり時間が必要になりますが、ひたすら待てば処理が終わるでしょう)
データが多い場合の処理を短時間で終わらせるためには、「在庫数」列と「処方量」列の並べ替えをしてから、上記作業をする方法や、作業列を追加して数式を入力して「在庫数」「処方量」ともに0の行を判定させて、並べ替えをしたうえでフィルタで絞込をして削除する方法など、いろいろあります。
今回は不要データ削除については紹介を割愛します。
ピボットテーブルで活用する
データ上のどこかのセルを一つ選択した状態で、「挿入」タブからテーブルにしておきます。
「先頭行をタイトル行とする」のチェックはONにします
さらにテーブル上のセルを一つ選択して、「挿入」タブからピボットテーブルを挿入します。
この時設定の画面で「テーブル/範囲」のところに「テーブル1」など上で作ったテーブルの名前になっていることを確認してください。これで次月以降データを追加した際にも自動で基データとして認識されます。
新しいシートが挿入され、ピボットテーブルが設置されます。
シートの右側に表示されるピボットテーブルのフィールドを次のように操作します。
上の項目一覧からドラッグ＆ドロップで項目を配置していきます。
「列」フィールド：年、月の順で配置
「行」フィールド：医薬品名、単位名(剤形単位)、包装倍率、調剤薬局名の順で配置
「値」フィールド：処方量、在庫数の順で配置（合計／ となっていることを確認)
次に「Σ値」という項目が列フィールドに配置されていたら、行フィールドの一番下に配置しましょう。
※データが多いと値フィールドに項目を配置してからしばらく処理に時間を要する場合がありますが、じっと待ちましょう。
自店舗にマーキングする
一覧を見やすくするために、自店舗のデータにマーキングをしておきます。
自店舗名が表示されている行の先頭辺りをマウスでポイントして、マウスポインタが「→」に変わる場所でクリックします。すると自店舗のデータについて全て選択されるので、「ホーム」タブの「フォント」から適当な色で塗りつぶしを選択します。
店舗の表示順を変更しておくとさらに見やすくなりますね。
全店で取り扱いのある品目までスクロールして、店舗名を選択し、セルの枠をポイントするとポインタが十字矢印に変わったらドラッグ＆ドロップして店舗名の順を変えていきます。
在庫数の表示を色文字にする
次に見やすさを確保するために、「処方量」の行と「在庫数」の行を区別するための色付けをします。
先ほどと同様に、在庫数が表示されている行の先頭辺りをポイントして、ポインタが「→」に変わったところでクリックします。
全ての「在庫数」の行が選択されているのを確認して、「ホーム」タブの「フォント」から文字の色を区別しやすい色に変えます。
全店在庫状況の閲覧方法
ここまででようやく在庫状況把握の準備が整いました。
それでは、閲覧方法です。
１：ピボットテーブルの左側にある「行ラベル」のフィルタボタンをクリック
２：「フィールドの選択」欄で「医薬品名称」を選択
３：検索窓に照会したい医薬品の名称(部分一致)を入力
４：「OK」をクリックまたは「Enter」キーを押す
これだけで、各店舗の当該医薬品の月別処方量と在庫数の推移が一目瞭然になりました。
例）「リクシアナ」で検索
検索結果は次のようになります。
ご覧のように「リクシアナ」と入力するだけで「リクシアナ」という文字が含まれる医薬品全てがいっぺんに表示されます。
処方量と在庫数の推移についても、手に取るように把握できます！
これなら、どの店舗に「もらって依頼」をすれば引き受けてくれそうかはばっちり分かります。
さらに、医療材料や自費医薬品（薬価基準未収載）についても、各店で独自マスタとして登録された品名で検索ができます。
※現状JustockEXでは、自費医薬品や医療材料の他店状況は簡単には照会できません
この後は、月が変わった時に、前月分の在庫一覧データを全店分抽出し、集計ファイルに転記するだけでデータが追加されていきます。
（ピボットテーブルに基データが増えたことを読み込みさせる必要がある場合は、「ピボットテーブルツール」リボン＞「分析」タブ＞「データ」項にある「更新」ボタンを押してください）
在庫データの応用
Excelはとっても優秀なので、これだけのデータ活用ではもったいないところです。
データがこれだけあれば、加工次第でいろいろできます。
- 各店過剰在庫状況・過剰在庫金額の把握
- 各店過剰在庫の品目特定と移動先の指定
- 一般名コード別の使用量・在庫数の状況把握
- 向覚麻毒などの規制品目の把握（内部統制に利用）
- 帳合ごとの医薬品の動向把握
- 店舗数が多い場合、「エリア」列を追加することもできる
などでしょうか。VBA（マクロ）を扱えて、やりたいことがはっきり決まればプログラムを書いてボタンなどを配置し画面レイアウトを構成していけば、「在庫照会アプリ」のようにすることもできますよ。その話はまたいつか…
