診療所の薬品管理の方、「在庫の数量の確認」「受払い」「注文」など、時間を取られていませんか？特に「注文」においては、薬品に対応するメーカーまで把握しないとならないため、非常に面倒ですよね。
わたしの勤務している診療所でも、「薬品に対応する業者が書かれた早見表」を見ながらメールによる注文や、手書きによる注文履歴表の作成など行っていました。
今回は、なんとか「自動化」によって効率化できないかと、Excelの機能の一つである「マクロ」という機能を用いて、誰でも短時間で発注できるファイルを作成しましたので、ぜひ参考にしていただければと思います。
必要なものは、「パソコン（タブレットが望ましい）」と「Excelソフト」のみです。
「マクロ」と聞くとかなり抵抗があると思いますが、意外とどなたでも簡便なシステムは作成できます。「Mac」と「Windows」で少しExcelの画面が異なりますのでわかりづらい部分は両方の画面を写真に載せて説明したい思います。
完成予定のシートです。1枚目はプルダウンのリストから薬品を選択すると、業者までが自動で表示されます。「注文登録」ボタンを押すと、2枚目の「注文リスト」へ入力内容を転送します。
2枚目のシートでは、業者を指定して「指定注文書」ボタンを押すと、転送された薬品リストの中から、指定した業者が取り扱う薬品のみを抽出し、3枚目のシート「注文依頼書」のひな形へ自動で転記します。
3枚目の「注文依頼書」は新規のExcelファイルとして指定のフォルダに自動で作成されます。作成された「注文依頼書」をFAXもしくは、メールに添付し注文完了となります。今回はここまでを作成する手順を書きたいと思います。
始める前の準備
「マクロ」を使用するにあたり、まず始めに「開発」というタブを表示させることと、「マクロ」の設定をする必要があります。
Windowsの場合
- 画面の左上「ファイル」から、左端のメニューの一番下にあるオプションを選択
- オプションメニューにある「リボンのユーザー設定」より、メインタブの「開発」をチェックしOKをする
- オプションメニューにある「トラストセンター」より、「マクロの設定」を選択し、「警告を表示して全てのマクロを無効にする」をチェックする
Macの場合
- 「環境設定」から、「リボンとツールバー」を選択
- メインタブの下の方にある「開発」にチェックをする
- 「環境設定」の「セキュリティ」を選択し、「警告を表示してすべてのマクロを無効にする」にチェックする
これで「マクロ」を使用する準備が整いました。
手順の概要
大まかなステップは以下の通りとなります。
- 注文登録画面の作成準備
- プルダウンリストの作成（分類・薬品名・規格・備考）
- VLOOKUP関数のリスト作成（薬品と業者を対応）
- 数量変更ボタンの設置
- 注文履歴表の作成準備
- 表の作成（業者名・品名・規格・数量・備考・日付）
- プルダウンリストの作成（業者名）
- 注文リスト印刷ボタンの設置（マクロ）
- クリアボタンの設置（マクロ）
- 注文依頼書（ひな形）の作成
- 「注文登録」のマクロ作成
- 「指定注文書」のマクロ作成
- 注文依頼書のひな形へ選択転記
- 注文依頼書の新規Excelファイルの作成
それぞれのリストの作成に時間を費やしてしまいますが、マクロの「コード」作成には時間はかかりません。では始めましょう。
注文登録画面の作成準備
「分類」「品名」「規格」が３段階のプルダウンリストとなるように作成します。リストを作成後、グループの名前を設定しておきます。
「数式」のタブのメニュー内の、名前の定義を選択し、グループ全体の範囲を指定して、一番上をグループ名と設定します。
例えば、抗生物質・ホルモン剤・その他の注射・注入薬…を「分類」というグループ名で定義しておきます。同じように、アンピシリン・エクセネル・セファゾリン…を「抗生物質」、オバホルモン・コンサルタン・プロナルゴン…を「ホルモン剤」とグループに名前を設定します。（注）10％バイトリルなど「カタカナ」と「％」などの記号は組み合わせて名前を付けることが出来ないようです。この場合は、「規格」の場所に「％」を記載します。
さらに、規格についても同様に名前を設定します。例えば、バイトリルと規格の範囲を全て指定し、「バイトリル」という名前を設定します。
リストの作成と名前の定義が完了したら、次に空白のセルがプルダウンになるように設定します。
空白セル（写真はB５）を選択し、Excelのリボンの「データ」の中にある「データの入力規則」を選択します。入力される値の場所が初期では「全ての値」となっていますが、「リスト」に変更します。また、すぐ下のデータの範囲は、リストの範囲もしくはグループ名に設定します。例えば、=D2:D8 もしくは=分類と設定します。（名前を定義していれば=分類）
次に、分類で選択した項目に応じた「品名」のみを選択できるように設定します。空白セル（写真はC5）を選択し、再度「データの入力規則」を選択します。値を「リスト」に変更後、選択範囲を=INDIRECT(B5）に設定します。つまり、B5に入力された値に応じて、（C5）に表示されるリストが設定されます。
規格についても同様です。空白セル（写真はD5）を選択し、「データの入力規則」から「リスト」、選択範囲を=INDIRECT(C5)に設定。これで、３段階のプルダウンリストの完成です。
次にVLOOKUP関数を用いて「薬品名」に対応した「業者名」が自動で表示される設定を行います。
まず始めに、品名に対応した業者名のリストを作成します。
次に、業者名を表示させたい空白セル（写真はF5）を選択します。次にVLOOKUP(参照するセル（ここでは品名のセル「C5」),作成したリストの範囲（別のシートに作成した場合は「シート名！」を頭につける）,列番号(ここでは業者名の記載された2列目の２を入力)、検索方法(ここではFALSE)）
写真では =VLOOKUP(C5,業者リスト!C2:D249,2,FALSE)となりました。
注）リストの範囲内では、参照するセル（検索値）の列が必ず一番左に来るように入力しなければなりません。一番左の「分類」の列は必要ありません。
数量増減のスピンボタンの設置
「開発」から「スピンボタン」を選択します。数量を入力するセルに設置し（後で場所や大きさは微調整できます）、右クリックで書式設定を開きます。ボタンの条件設定において、「リンクするセル」を登録します（写真はE5）。最後に右クリック後、ボタンの大きさを微調整して完了です。
注文履歴表の作成準備
注文リストで入力された内容を転記するための表を作成します。
項目の並び順は自由ですが、今回は「業者名」「品名」「規格」「数量」「備考」「日付」の順で作成しました。また、業者名をプルダウンリストから選択できるように、同シート内に業者リストを作成しています。これで、注文履歴表のひな形は完成となります。
「マクロの記録」をうまく利用する
注文履歴表をボタン一つで印刷する「マクロ」と注文履歴表を一括クリアする「マクロ」の二つを設定します。
開発の機能の中に、「マクロの記憶」という便利な機能があります。録画機能のようなもので、ボタンを押してから、停止するまでの作業内容を「マクロ」の作業として記録してくれます。今回はこの機能を使用します。
「マクロの記憶」を押すと、これから記録する「マクロ」名前を付ける表示が出ます。ここでは「注文リスト印刷」と名前をつけておきましょう。OKを押すと記録がスタートします。印刷範囲を選択し、メニューから印刷を押すところまで実行して下さい。これで「マクロ」の完成です。実行後、開発メニューから「ボタン」を設置します。設置すると登録された「マクロ」一覧が表示されますので、「注文リスト印刷」を選択します。
これでボタンをクリックすると、選択した範囲がボタン一つで印刷されます。同じ手順で、「注文リストクリア」（表の入力範囲を選択して削除を記録させる）のマクロを作成し、ボタンを設置します。
配置や大きさは右クリックで後から微調整できます。
注文依頼書の作成
次に、実際にFaxもしくはメールで注文する際の「注文依頼書」のひな形を作成します。ひな形の形式は自由ですが、データを転記する必要があるため、Excelのシートで作成する必要があります。
「注文登録」のマクロ作成
ここまでが発注システムの外枠となります。ここからが、システムの内部（自動転記、ファイル作成）の作業となります。
まず始めに、「注文登録」ボタンを押すと、注文履歴表へ自動で転記するマクロを作成します。英語の文章に似ていますので理解しやすいかと思います。開発メニューを開くと、左側に「Visual Basic」があります。開いた後、新規モジュールの挿入を選択すると、下の画面が表示されます。
次に、写真のようにコードを入力して下さい。
写真のコードの内容を説明したいと思います。
一番上の「Sub」 から一番下の「End Sub」 までが一つのマクロの作業ですよという決まり事です。この間に実行して欲しい内容を入力します。Subの横にマクロの名前を付けておきます。
次に「Sheets(“〇〇”).Range(××).Value = Sheets(“△△”).Range(□□).Value」とは
シート（△△）のセル（□□）の内容を、シート（〇〇）のセル（××）に転記するという意味となります。今回の場合は、注文登録の入力内容をそれぞれ、注文履歴表の対応した場所へ転記するという意味となります。最後のDateの部分は日付を入力するという意味となります。
If Sheets(〇〇).Range(××).Value = “” Then
これはシート(〇〇)のセル(××)の内容が空白ならば次の作業を実行するという意味となります。
では、空白でなかった場合はどうすれば良いでしょうか？
すでに入力されている場合は、下に続けて入力したいですね。そこで最初の部分のコードが必要となります。
Dim i （Dimensionという意味で、これは「i」が特別な意味を持ちますと宣言しています）
For i = 3 to Sheets(“注文累積”).Range(“B10000”).End(xlUp).Row + 1（10000行目からCtl ＋上を押したときに対象となるセルから１行下がったセルを意味します）
これは、iという変数の条件が、３からスタートして、入力済のセルのすぐ下の空白の行まで変化することを表しています。これにより、「i」行目のセルにそれぞれの項目が転記されることになります。
空白でなかった場合、「Next」により変数「i」の値が＋１され、再び最初から実行されます。
「Next」が実行されないように「Exit For」を用います。これは入力が一回でも実行した場合、作業を終了するという意味です。これを表示しないと、繰り返しが終わるまでひたすら同じ内容の入力が続いてしまいます。
MagBoxはメッセージボックスを表示し、” ” の中に表示したい文字列を入力します。
コードの入力を終えたら、ボタンを設置し、前回と同様に「注文登録」のマクロを選択して完了です。
「指定注文書」のマクロ作成
最後に「指定注文書」のマクロを作成します。まずSub〜End Subまで記入して下さい。コードの内容を説明したいと思います。
Dim i と Dim cnt は前回同様の変数の宣言となります。
Sheets(“注文書”).Copy After : =Sheets(Sheets.Count)
Sheets(Sheets.Count).Name = Sheets(“注文累積積”).Range(“I3”).Value
このコードは、ひな形をコピーして、新しいシート（シート名は指定された業者の名前）を作成するという意味です。After移行を入力しないと、新しいbookを作成してしまいます。
Sheets(Sheets.Count).Range(“H3”).Value = Date
Sheets(Sheets.Count).Range(“B5”).Value = Sheets(“注文累積”).Range(“I3”).Value
これらは、ひな形の「H3」に注文の日付、「H5」に業者名を転記するというコードです。
If Sheets(“注文累積”).Range(“B” & i).Value = Sheets(“注文累積”).Range(“I3”).Value Then
このコードは、注文履歴表の業者名と指定された業者が一致した場合に、以下の作業を実行するという意味です。これにより、指定された業者の薬品等のみが「ひな形」へ転記されます。
cnt = cnt+1 Next
これにより、変数に１を加えて最初に作業に戻ることになります。
一致する業者が無かった場合は次の作業に進みます。
Sheets(Sheets.Count).Copy
Application.DisplayAlerts = False
このコードにより転記されたシートを新しいBookで作成します。また、下のコードは「保存しますか」などの警告表示を省略してくれます。
ActiveWorkbook.SaveAs “「保存先のフォルダの場所」¥「フォルダ名」¥ ” & Format(Date, “yyyymmdd”) & ActiveSheet.Name & “.xlsx”
先程のコピーして作成された新しいBookを、指定したフォルダに保存します。作成先のフォルダの場所は、「windows」の場合、プロパティの中に記載されています。「Mac」の場合は「Finder」からフォルダを検索し、右クリックでメニューを表示させた後、「optionキー」を押します。すると、メニューが切り替わり「パスをコピー」という項目が現れます。それぞれ、そのままコピーして貼り付けた方が良いでしょう。またフォルダ名は￥で名前を囲みます。
ActiveWorkbook.Close
Sheets(Sheets.Count).Delete
Sheets(“注文累積”).Select
新規で作成したブックを閉じ、ひな形の横にコピーされたシートを削除します。また、注文履歴表の画面に戻るところまでが一連の作業で登録されています。
あとは、作成された注文書をFaxもしくはメールで送信して完了となります。
タブレットなどで作成すれば、在庫を確認しながら「注文登録」ができます。さらに印刷機ともBluetoothで接続することができればFaxまで容易にできるようになります。
コードに対して抵抗があると思いますが、「英語」の感覚と似ていますので、何となく入力しているうちに理解できるようになります。わたしも関数などはほとんど記憶もしておらず、使いこなせていませんが、ぜひチャレンジしてみてはいかがでしょうか？
分かりづらい部分や不明な点がありましたら、フォームからメールをいただければと思います。
「AI」に負けないために…
コメント