Excel VBA業務効率化

あなたのExcel定型業務をVBAで効率化、省力化をお手伝いします。

詳細はこちら

エクセルVBAの仕事術 For…Next繰り返し処理のやり方 小規模飲食関係事業所の棚卸し集計作業の軽量化 事業所運営の収益管理の要。

エクセルVBA
この記事は約23分で読めます。

初めに

棚卸しの適切な定義や目的はここでは説明しませんので他のサイトで検索してみてください。

 

どんな商売でもお店等で物を売って利益を得ることでその運営、経営が成り立ちます。

事業所運営の形は様々ですが、ここでは具体的に筆者が以前勤めていた給食サービス会社での経験から少しお話します。

 

事業所運営の例

 

上図で、料理を作ってお客様に提供して代金を頂く場合を考えます。

①と②の販売計画では8万5000円の利益を見込んでいましたが③④⑤を見てみると結果は8万2000円の赤字だったことがわかります。(ここでの設定は運営の様子をりかいするための極端な仮の数字を設定しています)

ここで重要なのが④の棚卸し作業なのです。ま、仮の数字なので運営のどこに問題があるのかはわかりません。実際の運営ではもっと綿密な計画を立てるのでこんなことにはならないでしょうが、棚卸の作業が重要なのは同じことです。

 

実際の現場では、計画したレシピをもとに販売計画のことをよく知らない調理師やパート作業員が料理を作って提供するのでしょうから、例えばそのレシピに書いて無い調味料を使ったり指定された分量より多くの材料を使ったり、またはあまりおいしい料理ではなかった、などが問題で計画通りに売り上げが上がらなかったとしたら・・・・・・・。

 

棚卸しを実施することで運営を検証し、修正して適正な方向へ導くことができるのです。もし、棚卸をしなければ全くコントロールすることはできません。

 

普通は月末の一回棚卸をしてその月の運営結果を知りますが、筆者の勤めていた会社では月4回の棚卸をやっていました。毎週末棚卸をして、週単位で運営を測っていました。その週のフォードコストは適正か、計画通りの利益が確保できたかを知り次週の運営に生かす、結果その月々の敷いてはその年の適正(計画通り)な運営ができるのです。

 

実際の現場では棚卸しの作業は大変なボリュームがあり、集計作業も手作業であれば大変です。

一日の営業は終了してから棚をメモに取って棚卸帳に移し書くと訳2時間、電卓で計算し集計をするのに2時間。それだけで数時間かかりますから、たいていは次の日に持ち越すこともたびたびです。

 

そんな煩雑な事務作業を少しでも軽くしようと、エクセルで作ったのがこの「InventoryBook」です。最初は表を作って計算式で計算する簡単なものでしたが、その中でも定型的な反復作業をVBAで自動化できるようになりました。当時は毎週棚卸でしたから月締めのスタイルを入れるととても複雑な処理もしていました。結果は飛躍的に事務作業が楽になったのです。

 

「InventoryBook」は月一回用にシンプルに作り直しています。使った食材itemリストは当時20年前のものですから当然、食材名や規格、価格等、現在のものとは違いますから、使う方の責任で変更して使っていただければ幸いです。

 

現在ではデジタル化も進んで手書きで集計する会社なんてないでしょうが、極小規模な事業所とか個人経営の商店とかまだコンピュータが導入されていない様々な所でお役に立てると思います。

VBAの初心者でも理解しやすいようわかりやすくなるよう細かく解説するよう心がけています。マクロのコードもロックしないで公開しますから、ご自分でもっと使いやすく改造することも可能なので挑戦してみてください。

 

すべてのシートやセルのロックはしていないのでセルや列を削除することも可能ですが、そうするとマクロに影響を与えますので改造は慎重に(フィルのバックアップをとるなど)行ってください。

 

 

InventoryBookの使い方

ダウンロードしたファイルを開きます

 

ファイルを開くとまず最初に下図の②のようなメッセージが出ます。これはマクロを含んだファイルに表示されるようです。ここは「編集を有効にする」ボタンを押します。①は開いたファイル名を表していますから確認だけしておきます。

 

 

少し2,3秒待って次に下図のようなメッセージウインドウが出るので「コンテンツの有効化」ボタンを押してください。これをやらないとマクロが機能しません

少し待つと、下図のダイアログが開きます。

ここでは、棚卸の期間をファイル名として名前を付けて保存します。

ダイアログの中方付近にある年度と月度は自動的に設定されますから、そのままでよければ一番下の緑色のボタンを押します。

 

 

—————————————————

 

上図で「棚卸期間とファイル名を確認しました」の緑色ボタンを押すと、下図のようにファイル名が変わることを確認しておきます。つまり最初に開いた「InventoryBook」からそのまま「棚卸2020_12月」に変わったということです。(ファイル「InventoryBook」は編集用のファイル「棚卸…(年月度)」を作成するためだけに使用されます。)

 

最初のファイル”nventoryBook”を開いたときと同じフォルダー(場所)に編集用のファイルが自動的に作成されます。

「・・・保存しました。引き続き・・・」のメッセージダイアログがでるので、そのまま「OK」ボタンを押します

 

 

 

 

これで棚卸を作成する画面が表示されます。

 

このファイルには現在12個のデータ入力用シートと設定用と食材item用、集計用、表紙用の各シートがあります12個のデータ入力用シートにはそれぞれ名前が設定されていますが、設定シートで自由に変更することができます。

 

右側中央付近(J列15行目)にあるボタン群グループは各シートへ移動(開く)するためのボタンです。(通常エクセルの一番下にあるシートタブをクリックしてももちろん移動できます。)

 

(J列4行目)にある集計表はそのシートの50行分のデータ入力行の分類ごとの集計をしています。

 

(A列5行目)から始まる50行のデータ入力行の使い方としては、品名に対して数量を入力すると設定した単価をかけて金額を計算しています。(このシートではあらかじめサンプルデータを何行かセットしています。数量を入力すれば金額が自動的に入力されます。)

 

 

 

棚卸の品名等各項目の設定方法

 

例えば、セルC10をポイントして「コロッケ」と入力してエンターキーを押します。

すると中央付近に「食材リスト検索」と名前の付いたダイアログボックスが表示されるのでもう一度エンターキーを押すと検索ボタンがフォーカスされます。そこでそのままエンターキーを押すと、食材itemシートの中から「コロッケ」が含まれる食材のリストが選択表示されます。

 

 

もりろん、検索ボタンをマウスでクリックしても同じ結果になります

 

 

 

ここでは、リストの中央付近にある「野菜コロッケ」をマウスでクリックしました。

結果、各項目がセットされました。

 

サンプルのデータ行は、このようにして入力しています。

 

 

食材itemにない材料を手入力で入力することもできますが、データを直接シートに入力するより食材itemシートに書き込んだほうが効率的です。

 

ここでは、最初のPage1シートに「冷蔵庫1」と名付けています。50行のデータ行の使い方は自由です。例えば、冷蔵庫1の上下左右の4つの扉ごとに区分があると仮定して棚を取っています。面倒なら一つの冷蔵庫として全体をひとまとめにしても勿論かまいません。自由に使いやすいように使ってください。

 

自由にとは言っても、設定されている表組の行を追加したり、必要ないからと行を削除したりしないで下さい。又、現在12個のデータ入力用シートがありますがシートを削除しないで下さい。マクロが正常に機能しなくなります。

 

 

集計と印刷

 

棚卸(数量の入力)をした段階で随時集計用のシートにデータが反映されていますので、各シートで入力が終わって「集計へ」ボタンを押すと各ページのデータが集計されているので、あとは印刷するだけです

 

右上にある黒い「棚卸の集計を完了して印刷する」ボタンをクリックします。

 

右側の「印刷ページ(シート)の選択」グループにある各チェックボックスは各シートの名前を表しています。チェックを付けたシートだけ印刷されます

 

各シートの内、シート内の合計金額が0以上のシートだけ自動的にチェックされますが、0の場合はチェックが付きません。
(合計が0でも印刷はしておきたいというときはクリックしてチェックをつけてください)

 

 

「棚卸期間の確認」グループでは年月度の確認だけしておきます。ここの表示は最初に設定したファイル名の年月度が自動的に表示されます。

棚卸の期間を確認しました」の水色ボタンをクリックします。

 

「印刷ページ(シート)の選択」グループの中に「印刷します」の緑ボタンが表示されますからこれをクリックすると確認のメッセージが出るのでOKボタンで印刷が始まります。

 

 

 

印刷が終わるとファイルが閉じます。

 

印刷が終わると、データ(数量)が入力された年月度ファイルの状態(各ページの数量以外の項目データ)が最初のファイル「InventoryBook」に反映されます。
入力された「数量」の列データは削除されます。

 

 

 

棚卸集計表ダウンロード

 

 

 

プログラムの実用例

 

 

食材を手入力で記入する場合一行一行のデータを入力しますが、毎月の作業で、毎回同じ食材を記入するのはひどく難雑で非効率です。手書き帳票と同じです。ですので、ここは検索して希望した食材をリストから選択できるようにしました。

 

セルに希望する食材名の一部を入力したら、フォームを開いてリストを表示するようにしました。

フォームにはコンボボックスが一つ張り付けています。

 

コンボボックスの図はこんな感じです。(使い方例でも説明しています)

 

 

プログラムの流れ

 

  1. セルに入力したい食材名の文字列の一部を入力してエンターキーを押す。
  2. 食材名をリスト表示するための検索用フォームが開く。
  3. フォームには食材リストを表示するためのコンボボックスと検索ボタンが一つずつ張り付けてある。
  4. フォームが開くと同時にその文字列はコンボボックスに転記されている。
  5. 検索ボタンをクリックする。
    Sheet2の食材itemデータの中からその文字列が含まれる食材名のデータを選び出しコンボボックスにリストする。
  6. コンボボックスのリストから希望する食材名をクリックする。
  7. 食材名の行の1列目にある食材コードを使って食材itemデータから目的のデータを取得する。
  8. 最初にセルに文字列を入力したときの行にその食材のデータを書き込む。

 

 

繰り返し処理の使いどころ

下図のプロシージャListInput()は、上の流れの5.検索ボタンをクリックしたときに呼び出されます。For…Next、繰り返し処理を使って食材をリスト表示する処理をしています。

 

 

For…Next の使い方

行番号18For i = 1 To r と 行番号30Next i の間に書かれているコードを指定された回数(r)だけ繰り返します。

コンピュータのCPUはプログラムを行番号2の文から一行ずつ順番にその文を評価して処理していきます。
行番号18でForを開始した後も更に一行ずつ処理を進め30行目まで来ると、次は18行目に戻ります。そしてまたそれ以降の行を順次処理します。
また30行目までくるとまた18行目に戻るを繰り返します。これが繰り返し処理の動きです。1回でも1万回でも10万回でも指定した回数を繰り返します。繰り返しの処理中で何回繰り返したかを知る必要がある場合は、ここで指定している変数 iを参照します。

 

ここではSheet2の食材データベースには1900行程のデータがあります。このプロシージャの繰り返しではその約1900回繰り返しますが、そのスピードは一瞬で終了します。検索ボタンを押すとほぼ同時にその処理が終了して一瞬でリストが表示されます。コンピュータのすごさが実感できる一瞬です。

 

繰り返し処理の方法にはForのほかにもありますがここではForだけ説明します。

そして、Forでは繰り返す回数を指定する必要があります。回数の指定方法は次のようにします。

 

For i = 1 To r 

 

上の変数 i にはCPUが行番号18を見にくるたびに1からrまで順に代入されます。

 

行番号12  r = .Range(“A2”).CurrentRegion.Rows.Count
Sheet2のA2セルから始まるデータベース領域の全行数を変数rに取得すると読みます。

 

itemシートの表組
下図のitemシートのデータベースは約1400行の食材と500行の営業用消耗品で訳1900行程度あります。変数rには表のデータ行数が入ることになります。一行目の項目名は以降の説明をするために仮に入れてありますが、実際は空白になっています

 

For…Next の中の処理

 

では、繰り返しの処理の中で実際にその仕事内容では何を繰り返しているのか?

 

行番号12の With Sheet2 で指定しているシート2(食材itemデータ)のデータベースです。

次の二行が繰り返す内容です。

  • 行番号19、foodId = .Cells(i, 1)
  • 行番号20、foodName = .Cells(i, 2)

”=”の右側の文 ”.cells(i, 1)” で、先頭のピリオド11行目の「With」でSheet2を指定しているのでシート2であることを省略する形で表しています。

 

また、()内の(i, 1)の左側の” i ”は For i = 1 To r で指定している変数iのことですから、繰り返すたびに1から順に2,3,4、…と順次入れ替わります。右側の1は列を表しています。

 

つまり、シート2の食材データベースの i 行目1 列目(食材コードの列)と読みます。

シートのセル i 行目の 1 列目の値ということですから、行の位置を変えながら参照しているのです。

しつこいようですが、(筆者はここのところを理解するのにずいぶん時間がかかったんです。)

.cells(i, 1)” で繰り返しデータベースのデータを繰り返しながら参照しているのです。

 

セルを表す方法
Rnge(列と行を表す記号)…例① Rnge(”A1”)…A列の1行目を表す
Cells(行,列)     …例② Cells(, 1)…行目の1列目を表す
同じセル位置(シートの一行目、一列目のセル)を表しています。プログラムのなかでForなどの繰り返し処理の中で順次行や列の位置を入れ替えて処理するときにはRangeでもできますが、Cellsのほうがあつかいやすいです。

 

あいまい検索

 

行番号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

 

両端の*印はワイルドカードと言って「なんでもいい」ということです。
str = CmbTxt  の場合
コンボボックスに入力された文字列Aが ”コロッケ”だったとすると別の文字列B ”メンチコロッケ100g” と比較するとき、AとBはイコールではないと判断します。

しかし、

str = ”*” & CmbTxt & ”*” の場合は A=B が成り立ちます。
前後にどんな文字がくっついていても。コロッケが含まれてさえいればokなんです。

InStr()関数でもできます。
’ーーーーーーーーーーーーーーーーーーーーーーーーーーーーーー
foodName  = ”いろんなコロッケあります”
str = ”コロッケ”
If InStr(foodName , str) > 0 Then……True

 

上記の場合、InStr(foodName , str)は5を返すので0以上なのでTrueになります。
Likeを使ったあいまい検索より簡単に、含まれるか判断できますね。こっちのほうが簡単でした。

 

例えば、コンボボックスに「コロッケ」と入力したとすると、食材itemシートのデータベースの中から、食材名に「コロッケ」が含まれているか調べるために利用されます。

 

行番号22行目の if foodName Like str Then でそれを調べているのです。

条件検索に使う比較演算子

Likeは=や<>等と同じように使える、文字列をあいまい比較するときの演算子です。
先の例では変数foodName に「コロッケ」が含まれていればになると評価します。

 

 

つまり、下図のようにFor とNextの間で Ifの条件を判断し真であれば、If とEnd Ifの間の処理文を実行します(コンボボックスにリストを追加する処理を実行する)

 

   For …食材itemのデータを一行目から最終行まで以下の条件判断を繰り返す
     If 

処理文…真であればリストに追加する処理を実行     End If
   Next

 

そして、食材データベースの一行目から順に食材名にコロッケが含まれる行を探し続けます。その行を見つけたらその時初めてそのデータを格納する処理を 行番号24,25,26を実行します。

 

そして食材コードと食材名をコンボボックスのリストに追加します。

 

行番号24 ComboBox1.AddItem は以下の2行をまとめてリストにするための一文です

行番号25 Combobox1.List(c, 0)= foodId

行番号26 Combobox1.List(c, 1)= foodName

 

List(c, 0)は、コンボボックスのListプロパティーでカッコ内のカンマを挟んだ左右はそれぞれ行と列を意味します。(いわゆる二次元配列のような形です)

プロパティーとは
あるオブジェクト(ここではCompuBox)の性格や振る舞いを定義したものです
下図の左側中央付近にあるプロパティーと書かれている部分です。結構沢山あって値を設定したり参照したり様々な処理に利用されます。

 

cはカウンター用の変数で行番号17行目で0を代入され27行目で1づつカウントアップされています

Forに入って一回目のcは0になりますから、それぞれ、0行目の0列目にfoodId、0行目の1列目にfoodNameを代入します。

 

ですから、条件判断で”コロッケ”が含まれるfoodNameが見つかったらリストの1行目から食材コードと食材名を追加していきます。

イメージは下図のようです

配列の添え字は0から始まります。つまり、Forに入って1回目の処理ではcに0が入ってくるので配列の一行目の一列目となります。ちょっとややこしいですがプログラミングの世界ではどの言語でも同じような仕様になっていますので覚えておいてください。
上図の行と列はそのままコンボボックスのリストに形を変えますので、結果としてはリストの一行目にコロッケが含まれた最初の食材Idと食材名がセットされ、順次リストに追加されていきます。
コンボボックスではプロパティーのリストの列は初期値で1列に設定されているので、そのままではうまく表示できません、ColomCountプロパティーを2にするようにします。
又、リストをクリックしたときにその値を返す列位置は、boundclomプロパティーで1(1列目)を指定しておきます
上図(コンボボックスの図はこんな感じです。)の中では、
冷)野菜コロッケ 60g×10ケの行をクリックしているので、コンボボックスのValueプロパティーには一列目の食材コード(3212413438)が帰ってきます。
つまり、このコンボボックスのリストがクリックされたときに起動するプロシージャにこの食材コードを利用して、食材の項目データを拾い出してシートに書出しする処理プログラムをを書きます。コンボボックスの説明はこの辺で終わりにします。
最後にこのサブプロシージャ、Private Sub ListInput(CmbTxt As String) は、セルに希望する食材名の一部を入力してエンターキーを押したときにWorksheet_Changeプロシージャが起動されて検索フォームが表示されます。フォームが開き、コンボボックスの横にある「検索ボタン」をクリックしたときに入力された文字列を引き数にして呼び出されてリストの表示処理が実行されます。

二回目の繰り返し処理の実際

上記のこれまでの処理の流れで一回目の繰り返し処理でコンボボックスに食材名をリスト表示させ、そのリストから希望する食材を選択したとき、その食材のデータを使って今度は二度目の繰り返し処理をしてその食材の他のデータを取り出すという方式にしています。

一回目の繰り返し処理だけで済むのであれば効率的ですが、そうするにはコンボボックスのリストデータにすべての列データを保持しなければならないので、その列数が少なければ問題ないのだけど、60列を超えるデータベースを扱う場合もあったので、その時の経験から二つに分けた処理をしました。

後になってみてみると、今回の場合は列数も5列程度なので多少処理が複雑でも、一回の繰り返し処理で済ませた方がよかったかなと思っています。次回バージョンアップの時はそうしてみます。

 

ここからは上で説明した食材コードを利用して食材itemのデータを特定して諸データを取得する処理を説明します。
具体的には食材itemデータを検索して食材コードに完全一致する行のデータを取得してそのデータをシートに張り付けます。

 

前提条件

このプロシージャはコンボボックスに一覧表示したリストから任意の行をクリックしたときに起動します。そもそも、このコンボボックスはシートのセルに検索する文字列を書き込んでエンターキーを押したときに開く検索フォームの上に張り付いています。

 

そして、このフォームが開くとき、その文字列を書き込んだセル位置(Target)をフォームのTagプロパティーに格納しています。(具体的にはセル位置を示すTarget.Adressです)

これ以降で説明するプロシージャの中で、見つけたデータをシートに転記するとき、どのセルに書き込むかの手掛かりにするためです。

 

条件判断はこんな風に使いました

 

ここで使用している食材itemデータをFor…Nextで繰り返す処理は前回とほぼ同じなので説明は省きます。

 

今回のは条件判断がちょっと違ってあいまいではなく、完全一致するデータを見つけだします。

 

If .Cells(i, 1) = foodId Then では検索するセルの値が変数foodIdと一致(同じ)なら真を返します。

 

.Cells(i, 1)の先頭の ”.”(ピリオド)は、行番号7のWithで宣言したSheet2を意味します。

 

つまり、シート2のi行目の1列目のセルの値を意味し、順次行を検索しながら移動して一致する行が見つかった時点で繰り返しを中止します。

 

食材コードはユニークな値なので見つかった時点でそれ以降のデータを探す必要がないのでここで繰り返しを中止できます。

行番号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)

つまり、下図のような場合では、C10 のセルを起点としているので

 

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です

 

以上今回のプログラムの説明は終わりにします。

 

コメント

タイトルとURLをコピーしました