プログラムの概要
今回のVBAプログラムで特徴的なのは複数のシートを動的に切り替えて集計する仕組みです。
主な集計計算はエクセルが本来持っている表計算機能を利用しますから特に難しいところはありません。
シートのコレクション化
シートオブジェクトをコレクション化して変数で連続使用できるようにします。
手順として（、下図のプログラムでハイライトしている行のコードに注目してください。
2行目，コレクション変数を宣言する（モジュールの先頭に記述）
２4行目，コレクション変数にシートオブジェクトを追加する（6個の集計シートを格納）
17行目，コレクションを使用する（ここでは変数に格納した各シートを連続して印刷）
'ジェネラルに宣言したコレクション変数
Dim myCollection As New Collection
'印刷フォームで印刷ボタンを押したときに開始されるプロシージャの一部分--------------
kakunin = MsgBox("■ 印刷を開始します" & Chr(13) & Chr(13) _
& "プリンタの電源とA4コピー紙を確認してください" & Chr(13) & Chr(13) _
& "okを押すとチェックした全てのシートが印刷されます", vbOKCancel)
'--------------------------------------------------------------------
Call korekusyonn
Application.ScreenUpdating = False
If kakunin = 1 Then
For i = 6 To 1 Step -1
If myChekuCaption(i).Value = True Then
FormPrint.Hide
'myCollection(i).PrintPreview
myCollection(i).PrintOut
End If
Next i
'------------------------------------------
’上のプロシージャから呼び出されるサブプロシージャ
Private Sub korekusyonn()
With myCollection
.Add Item:=Sheet4 '冷蔵庫１
.Add Item:=Sheet8 '冷凍庫１
.Add Item:=Sheet10 '厨房１
.Add Item:=Sheet12 'グロッサリ庫１
.Add Item:=Sheet15 'サプライ庫
.Add Item:=Sheet16 'その他１
End With
本システムで作成したVBAプログラムではこの様なコレクションを多数利用してオブジェクトを変数化して連続した動的処理を実行しています。
棚卸し
給食部門の運営状況の把握において棚卸しは欠かすことのできない重要な構成要素の一つです。
利用者様に料理を提供するうえで、仕入れと売り上げの関係だけで損益を把握はできません。それは仕入れたものを１００％使い切ることはなく必ず在庫が発生するからです。
料理を作って利用者様に提供するとき仕入れた食材の実際の使用量を把握するには在庫量を把握しないと正確なコストを計算できません。
料理レシピのコスト計算
フードコスト（F/C％）＝ 仕入れ食材料価格 × 使用数量 ÷ 販売価格 × １００
運営コスト計算
NET. F/C％ ＝（総食材仕入れ金額 ー 棚卸し在庫金額） ÷ 給食部門売上金額 × １００
料理レシピのコスト計算は目安でしかありません。計画段階でのコスト計算が正しかったかどうかを把握するにはある一定期間の運営コストがどの位だったかを知る必要があります。
棚卸しはその運営を計る手段として大変重要な構成要素なのです。しかしその作業は大変手間のかかる大仕事です多大な時間と人件費を必要とします。筆者が以前勤めていた会社では毎週末ごとに棚卸しを実施し週単位で事業所運営の評価をやっていましたね。
さて、ここではそんな大変な集計作業をエクセルを使って少しでも事務作業の軽量化ができるよう工夫してみました。
インベントリーブックの作成
まずダウンロードしたファイル「InventoryBook」を開きます
下図のようなフォームが開くので年度と月度の設定をします
一番下のボタンをクリックすると年度と月度をファイル名にしたエクセルのファイルが作成されます。
PC画面はそのままで、ファイル名が（例えば）棚卸2023_8 に代わります
作成フォルダーは最初の「InventoryBook」と同じフォルダーになります。
集計作業を中断するときはそのまま保存して終了できます
次回作業を再開するときは下図のようなエクセルファイルを開いて続きを編集できます
集計は、冷蔵庫１、冷凍庫１、厨房１、グロッサリ庫１、サプライ庫、その他１の6つのシートに分けてあります。これらのシート名は設定シートで変更することができます。
実際の各収納棚に合わせて事業所に合わせて各シートの中で棚分けして使用してください。
それぞれのシートの適当な行の品名列のセルでダブルクリックすると食材を検索するフォームが開くので検索窓に適当な語句の一部を入れると一覧に表示するのでその中から適当な食材をクリックします。食材の情報が行に挿入されるので数量列に在庫量を入れると自動集計します。
単価はかなり古い情報なので書き直しが必要です。
各シートで入力が終わったら集計ページを開きます
各入力シートの集計結果を確認して右上のボタンをクリックすると印刷用のフォームが開きます。
印刷したいページ（シート）をチェックして印刷を終了します。
ブックを印刷して集計完了したらそのブックは別な場所に保存してください。
本来なら集計完了したファイルは再編集できないようにするべきですが、そこまではしていません。
不適切な書き換えや再編集ができないようにしたい場合はご相談ください。
ダウンロード
下のカードからダウンロードしてください
コメント