初めに
棚卸しの適切な定義や目的はここでは説明しませんので他のサイトで検索してみてください。
どんな商売でもお店等で物を売って利益を得ることでその運営、経営が成り立ちます。
事業所運営の形は様々ですが、ここでは具体的に筆者が以前勤めていた給食サービス会社での経験から少しお話します。
事業所運営の例
上図で、料理を作ってお客様に提供して代金を頂く場合を考えます。
①と②の販売計画では８万5000円の利益を見込んでいましたが③④⑤を見てみると結果は8万2000円の赤字だったことがわかります。（ここでの設定は運営の様子をりかいするための極端な仮の数字を設定しています）
ここで重要なのが④の棚卸し作業なのです。ま、仮の数字なので運営のどこに問題があるのかはわかりません。実際の運営ではもっと綿密な計画を立てるのでこんなことにはならないでしょうが、棚卸の作業が重要なのは同じことです。
実際の現場では、計画したレシピをもとに販売計画のことをよく知らない調理師やパート作業員が料理を作って提供するのでしょうから、例えばそのレシピに書いて無い調味料を使ったり指定された分量より多くの材料を使ったり、またはあまりおいしい料理ではなかった、などが問題で計画通りに売り上げが上がらなかったとしたら・・・・・・・。
棚卸しを実施することで運営を検証し、修正して適正な方向へ導くことができるのです。もし、棚卸をしなければ全くコントロールすることはできません。
普通は月末の一回棚卸をしてその月の運営結果を知りますが、筆者の勤めていた会社では月4回の棚卸をやっていました。毎週末棚卸をして、週単位で運営を測っていました。その週のフォードコストは適正か、計画通りの利益が確保できたかを知り次週の運営に生かす、結果その月々の敷いてはその年の適正（計画通り）な運営ができるのです。
実際の現場では棚卸しの作業は大変なボリュームがあり、集計作業も手作業であれば大変です。
一日の営業は終了してから棚をメモに取って棚卸帳に移し書くと訳2時間、電卓で計算し集計をするのに2時間。それだけで数時間かかりますから、たいていは次の日に持ち越すこともたびたびです。
そんな煩雑な事務作業を少しでも軽くしようと、エクセルで作ったのがこの「InventoryBook」です。最初は表を作って計算式で計算する簡単なものでしたが、その中でも定型的な反復作業をVBAで自動化できるようになりました。当時は毎週棚卸でしたから月締めのスタイルを入れるととても複雑な処理もしていました。結果は飛躍的に事務作業が楽になったのです。
「InventoryBook」は月一回用にシンプルに作り直しています。使った食材itemリストは当時20年前のものですから当然、食材名や規格、価格等、現在のものとは違いますから、使う方の責任で変更して使っていただければ幸いです。
現在ではデジタル化も進んで手書きで集計する会社なんてないでしょうが、極小規模な事業所とか個人経営の商店とかまだコンピュータが導入されていない様々な所でお役に立てると思います。
VBAの初心者でも理解しやすいようわかりやすくなるよう細かく解説するよう心がけています。マクロのコードもロックしないで公開しますから、ご自分でもっと使いやすく改造することも可能なので挑戦してみてください。
InventoryBookの使い方
ダウンロードしたファイルを開きます
ファイルを開くとまず最初に下図の②のようなメッセージが出ます。これはマクロを含んだファイルに表示されるようです。ここは「編集を有効にする」ボタンを押します。①は開いたファイル名を表していますから確認だけしておきます。
少し２，３秒待って次に下図のようなメッセージウインドウが出るので「コンテンツの有効化」ボタンを押してください。これをやらないとマクロが機能しません
少し待つと、下図のダイアログが開きます。
ここでは、棚卸の期間をファイル名として名前を付けて保存します。
ダイアログの中方付近にある年度と月度は自動的に設定されますから、そのままでよければ一番下の緑色のボタンを押します。
—————————————————
上図で「棚卸期間とファイル名を確認しました」の緑色ボタンを押すと、下図のようにファイル名が変わることを確認しておきます。つまり最初に開いた「InventoryBook」からそのまま「棚卸2020_12月」に変わったということです。（ファイル「InventoryBook」は編集用のファイル「棚卸…（年月度）」を作成するためだけに使用されます。）
「・・・保存しました。引き続き・・・」のメッセージダイアログがでるので、そのまま「OK」ボタンを押します
これで棚卸を作成する画面が表示されます。
このファイルには現在12個のデータ入力用シートと設定用と食材item用、集計用、表紙用の各シートがあります12個のデータ入力用シートにはそれぞれ名前が設定されていますが、設定シートで自由に変更することができます。
右側中央付近（Ｊ列１５行目）にあるボタン群グループは各シートへ移動（開く）するためのボタンです。(通常エクセルの一番下にあるシートタブをクリックしてももちろん移動できます。)
（Ｊ列4行目）にある集計表はそのシートの50行分のデータ入力行の分類ごとの集計をしています。
（A列5行目）から始まる50行のデータ入力行の使い方としては、品名に対して数量を入力すると設定した単価をかけて金額を計算しています。（このシートではあらかじめサンプルデータを何行かセットしています。数量を入力すれば金額が自動的に入力されます。）
棚卸の品名等各項目の設定方法
例えば、セルC10をポイントして「コロッケ」と入力してエンターキーを押します。
すると中央付近に「食材リスト検索」と名前の付いたダイアログボックスが表示されるのでもう一度エンターキーを押すと検索ボタンがフォーカスされます。そこでそのままエンターキーを押すと、食材itemシートの中から「コロッケ」が含まれる食材のリストが選択表示されます。
もりろん、検索ボタンをマウスでクリックしても同じ結果になります
ここでは、リストの中央付近にある「野菜コロッケ」をマウスでクリックしました。
結果、各項目がセットされました。
サンプルのデータ行は、このようにして入力しています。
ここでは、最初のPage１シートに「冷蔵庫１」と名付けています。50行のデータ行の使い方は自由です。例えば、冷蔵庫１の上下左右の4つの扉ごとに区分があると仮定して棚を取っています。面倒なら一つの冷蔵庫として全体をひとまとめにしても勿論かまいません。自由に使いやすいように使ってください。
集計と印刷
棚卸（数量の入力）をした段階で随時集計用のシートにデータが反映されていますので、各シートで入力が終わって「集計へ」ボタンを押すと各ページのデータが集計されているので、あとは印刷するだけです
右上にある黒い「棚卸の集計を完了して印刷する」ボタンをクリックします。
右側の「印刷ページ（シート）の選択」グループにある各チェックボックスは各シートの名前を表しています。チェックを付けたシートだけ印刷されます。
「棚卸期間の確認」グループでは年月度の確認だけしておきます。ここの表示は最初に設定したファイル名の年月度が自動的に表示されます。
「棚卸の期間を確認しました」の水色ボタンをクリックします。
「印刷ページ（シート）の選択」グループの中に「印刷します」の緑ボタンが表示されますからこれをクリックすると確認のメッセージが出るのでOKボタンで印刷が始まります。
印刷が終わるとファイルが閉じます。
棚卸集計表ダウンロード
プログラムの実用例
食材を手入力で記入する場合一行一行のデータを入力しますが、毎月の作業で、毎回同じ食材を記入するのはひどく難雑で非効率です。手書き帳票と同じです。ですので、ここは検索して希望した食材をリストから選択できるようにしました。
セルに希望する食材名の一部を入力したら、フォームを開いてリストを表示するようにしました。
フォームにはコンボボックスが一つ張り付けています。
コンボボックスの図はこんな感じです。（使い方例でも説明しています）
プログラムの流れ
- セルに入力したい食材名の文字列の一部を入力してエンターキーを押す。
- 食材名をリスト表示するための検索用フォームが開く。
- フォームには食材リストを表示するためのコンボボックスと検索ボタンが一つずつ張り付けてある。
- フォームが開くと同時にその文字列はコンボボックスに転記されている。
- 検索ボタンをクリックする。
Sheet2の食材itemデータの中からその文字列が含まれる食材名のデータを選び出しコンボボックスにリストする。 - コンボボックスのリストから希望する食材名をクリックする。
- 食材名の行の１列目にある食材コードを使って食材itemデータから目的のデータを取得する。
- 最初にセルに文字列を入力したときの行にその食材のデータを書き込む。
繰り返し処理の使いどころ
下図のプロシージャListInput()は、上の流れの５．検索ボタンをクリックしたときに呼び出されます。For…Next、繰り返し処理を使って食材をリスト表示する処理をしています。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 |
'食材をあいまい検索してコンボボックスにリスト表示 Private Sub ListInput(CmbTxt As String) Dim i As Integer Dim str As String Dim foodName As String Dim foodId As String Dim r As Long ComboBox1.Clear With Sheet2 r = .Range("A2").CurrentRegion.Rows.Count str = "*" & CmbTxt & "*" 'コンボボックスに入力した文字をあいまい検索する文字列を生成 '----------コンボボックスにリストを設定--------- c = 0 For i = 1 To r foodId = .Cells(i, 1) foodName = .Cells(i, 2) If foodName Like str Then 'データを格納 ComboBox1.AddItem ComboBox1.List(c, 0) = foodId '１列目に食品番号をセット ComboBox1.List(c, 1) = foodName '２列目に食品名をセット c = c + 1 End If Next i End With End Sub |
For…Next の使い方
行番号18、For i = 1 To r と 行番号30、Next i の間に書かれているコードを指定された回数（ｒ）だけ繰り返します。
コンピュータのCPUはプログラムを行番号２の文から一行ずつ順番にその文を評価して処理していきます。
行番号18でForを開始した後も更に一行ずつ処理を進め30行目まで来ると、次は18行目に戻ります。そしてまたそれ以降の行を順次処理します。
また30行目までくるとまた18行目に戻るを繰り返します。これが繰り返し処理の動きです。1回でも1万回でも10万回でも指定した回数を繰り返します。繰り返しの処理中で何回繰り返したかを知る必要がある場合は、ここで指定している変数 iを参照します。
ここではSheet2の食材データベースには1900行程のデータがあります。このプロシージャの繰り返しではその約1900回繰り返しますが、そのスピードは一瞬で終了します。検索ボタンを押すとほぼ同時にその処理が終了して一瞬でリストが表示されます。コンピュータのすごさが実感できる一瞬です。
繰り返し処理の方法にはForのほかにもありますがここではForだけ説明します。
そして、Forでは繰り返す回数を指定する必要があります。回数の指定方法は次のようにします。
For i = 1 To r
上の変数 i にはCPUが行番号18を見にくるたびに１からｒまで順に代入されます。
行番号12 r = .Range(“A2”).CurrentRegion.Rows.Count
Sheet2のA2セルから始まるデータベース領域の全行数を変数ｒに取得すると読みます。
下図のitemシートのデータベースは約1400行の食材と500行の営業用消耗品で訳1900行程度あります。変数ｒには表のデータ行数が入ることになります。一行目の項目名は以降の説明をするために仮に入れてありますが、実際は空白になっています
For…Next の中の処理
では、繰り返しの処理の中で実際にその仕事内容では何を繰り返しているのか？
行番号12の With Sheet2 で指定しているシート２（食材itemデータ）のデータベースです。
次の二行が繰り返す内容です。
- 行番号19、foodId = .Cells(i, 1）
- 行番号20、foodName = .Cells(i, 2)
”＝”の右側の文 ”.cells(i, 1)” で、先頭のピリオドは11行目の「With」でSheet2を指定しているのでシート２であることを省略する形で表しています。
また、()内の(i, 1）の左側の” i ”は For i = 1 To r で指定している変数iのことですから、繰り返すたびに１から順に２，３，4、…と順次入れ替わります。右側の１は列を表しています。
つまり、シート２の食材データベースの i 行目の1 列目（食材コードの列）と読みます。
シートのセル i 行目の 1 列目の値ということですから、行の位置を変えながら参照しているのです。
しつこいようですが、（筆者はここのところを理解するのにずいぶん時間がかかったんです。）
”.cells(i, 1)” で繰り返しデータベースのデータを繰り返しながら参照しているのです。
あいまい検索
行番号13では 繰り返しに入る前に このプロシージャに渡された引数を使って変数strに検索用の文字列を生成しています。
例えば、CmbTxt にはコンボボックスに”コロッケ”と入力されていたとすると
str ＝ ”＊” & CmbTxt & ”＊”
そして、一回目の繰り返しでは
- foodId には ””（空白）
- foodName には ””（空白）が入ります。
一行目の項目名は以降の説明をするために仮に入れてありますが、実際は空白になっています
行番号22 のIf条件判断でfoodName には ”コロッケ”が含まれていないので比較はFalseなので何もしないでIfを抜けて一回目の繰り返しを終わります。
そして二回目の繰り返しで
- foodId には ”1117911QQQ” が入り
- foodName には ”S）牛小間” が入ります。
ここでもfoodName には ”コロッケ”が含まれていないので比較はFalseなので順次繰り返しを ”コロッケ”が含まれるfoodName がみつかるまで続けます
行番号22 If foodName Like str Then
例えば、コンボボックスに「コロッケ」と入力したとすると、食材itemシートのデータベースの中から、食材名に「コロッケ」が含まれているか調べるために利用されます。
行番号22行目の if foodName Like str Then でそれを調べているのです。
つまり、下図のようにFor とNextの間で Ifの条件を判断し真であれば、If とEnd Ifの間の処理文を実行します（コンボボックスにリストを追加する処理を実行する）
そして、食材データベースの一行目から順に食材名にコロッケが含まれる行を探し続けます。その行を見つけたらその時初めてそのデータを格納する処理を 行番号24，25，26を実行します。
そして食材コードと食材名をコンボボックスのリストに追加します。
行番号24 ComboBox1.AddItem は以下の2行をまとめてリストにするための一文です
行番号25 Combobox1.List（ｃ, 0）= foodId
行番号26 Combobox1.List（ｃ, 1）= foodName
List（ｃ, 0）は、コンボボックスのListプロパティーでカッコ内のカンマを挟んだ左右はそれぞれ行と列を意味します。（いわゆる二次元配列のような形です）
あるオブジェクト（ここではCompuBox）の性格や振る舞いを定義したものです
下図の左側中央付近にあるプロパティーと書かれている部分です。結構沢山あって値を設定したり参照したり様々な処理に利用されます。
cはカウンター用の変数で行番号17行目で０を代入され27行目で1づつカウントアップされています
Forに入って一回目のｃは０になりますから、それぞれ、0行目の0列目にfoodId、0行目の1列目にfoodNameを代入します。
ですから、条件判断で”コロッケ”が含まれるfoodNameが見つかったらリストの1行目から食材コードと食材名を追加していきます。
イメージは下図のようです
二回目の繰り返し処理の実際
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 |
'このフォームを起動させたターゲットのアドレス（タグに収納）に値を転記する Private Sub ComboBox1_Click() Dim foodId As String Dim r As Long Dim i As Integer With Sheet2 r = .Range("A2").CurrentRegion.Rows.Count For i = 1 To r foodId = ComboBox1.Value If .Cells(i, 1) = foodId Then Application.EnableEvents = False '------------------------------------ 'データをシートに転記 ActiveSheet.Range(Me.Tag).Offset(0, -1) = Left(.Cells(i, 1), 1) '分類コードを取得 ActiveSheet.Range(Me.Tag) = .Cells(i, 2).Text '食材品名 ActiveSheet.Range(Me.Tag).Offset(0, 1) = .Cells(i, 3).Text 'サプライやー ActiveSheet.Range(Me.Tag).Offset(0, 3) = .Cells(i, 4).Text '単位 ActiveSheet.Range(Me.Tag).Offset(0, 4) = .Cells(i, 5).Text '価格 ActiveSheet.Range(Me.Tag).Offset(0, 2).Select '------------------------------------ Application.EnableEvents = True '--------- Exit For '--------- End If Next i Unload Me End With End Sub |
前提条件
このプロシージャはコンボボックスに一覧表示したリストから任意の行をクリックしたときに起動します。そもそも、このコンボボックスはシートのセルに検索する文字列を書き込んでエンターキーを押したときに開く検索フォームの上に張り付いています。
そして、このフォームが開くとき、その文字列を書き込んだセル位置（Target）をフォームのTagプロパティーに格納しています。（具体的にはセル位置を示すTarget.Adressです）
これ以降で説明するプロシージャの中で、見つけたデータをシートに転記するとき、どのセルに書き込むかの手掛かりにするためです。
条件判断はこんな風に使いました
ここで使用している食材itemデータをFor…Nextで繰り返す処理は前回とほぼ同じなので説明は省きます。
今回のは条件判断がちょっと違ってあいまいではなく、完全一致するデータを見つけだします。
If .Cells(i, 1) = foodId Then では検索するセルの値が変数foodIdと一致（同じ）なら真を返します。
.Cells(i, 1)の先頭の ”.”（ピリオド）は、行番号7のWithで宣言したSheet2を意味します。
つまり、シート２のi行目の１列目のセルの値を意味し、順次行を検索しながら移動して一致する行が見つかった時点で繰り返しを中止します。
食材コードはユニークな値なので見つかった時点でそれ以降のデータを探す必要がないのでここで繰り返しを中止できます。
行番号25 Exit For で繰り返しを抜けて、31行目以降に処理を映します
条件判断でfoodidと一致するセルを見つけたら転記作業をします
‘データをシートに転記
行番号15 ActiveSheet.Range(Me.Tag).Offset(0, -1) = Left(.Cells(i, 1), 1) ‘分類コードを取得
行番号16 ActiveSheet.Range(Me.Tag) = .Cells(i, 2).Text ‘食材品名
行番号17 ActiveSheet.Range(Me.Tag).Offset(0, 1) = .Cells(i, 3).Text ‘サプライやー
行番号18 ActiveSheet.Range(Me.Tag).Offset(0, 3) = .Cells(i, 4).Text ‘単位
行番号19 ActiveSheet.Range(Me.Tag).Offset(0, 4) = .Cells(i, 5).Text ‘価格
行番号20 ActiveSheet.Range(Me.Tag).Offset(0, 2).Select
※Me.Tagに前提条件で説明したセルのアドレスが入っています。
Offset(0, -1)は上で説明したセル位置を起点としたオフセット位置を示します。
補足説明 Offsetとは
Rangeで表すと
Range(“C3”).Offset(0, 0)………Range(“C3”)
Range(“C3”).Offset(0, -1)………Range(“B3”)
Range(“C3”).Offset(0, 1)………Range(“D3”)
Range(“C3”).Offset(1, 0)………Range(“C4”)
Range(“C3”).Offset(-1 0)………Range(“C2”)
Cellsで表すと
Cells(3, 3).Offset(0, 0)………Cells(3, 3)
Cells(3, 3).Offset(0, -1)………Cells(3, 2)
Cells(3, 3).Offset(0, 1)………Cells(3, 4)
Cells(3, 3).Offset(1, 0)………Cells(4, 3)
Cells(3, 3).Offset(-1 0)………Cells(2, 3)
Offset(0, -1) に 分類コード
セル位置そのものに 食材名 （ActiveSheet.Range(Me.Tag).Offset(0, 0) としてもOKです）
Offset(0, 1) に メーカー名
Offset(0, 3) に 単位（規格）
Offset(0, 4) に 単価
と指定すると下図のようにデータが張り付きます。
注意事項
プログラムでコードを実行中にセルに値を書き込むとシートのセルのチェンジイベントプロシージャが起動します。
コードの実行中にチェンジイベントが起こると、そのチェンジイベントプロシージャに書かれたコードの実行に処理が飛んで、予期しない動作をしてしまう可能性があります。
これを防ぐためにチェンジイベントが起こらないようにするため
行番号12で Application.EnableEvents = False と書いておきます
これで、エクセル本体のイベントが停止します。
このままにしておくと以降のマクロが停止したまま動かなくなるので、書き込み処理が終わったら必ず元に戻しておきます
行番号22で Application.EnableEvents =True です。これでOKです
以上今回のプログラムの説明は終わりにします。
コメント