食札管理システム概要
小規模老人保健施設の利用者の給食管理で使用する食札を印刷できるツールです。
サンプルデータが100件ほど入力されていますから、使用感が確認できます。
ダウンロード
ベクター様からダウンロードできます。（10日間の試用期間設定あり）
取り扱い説明書
ダウンロードしたファイルを解凍してください。
ファイルを開くとSheet1が開きます。
ライセンスキーの入力画面が開きます。（ベクター様から購入いただくとライセンスキーが発行されます）。「後から登録」ボタンを押せばライセンスキーなしでも10日間お試し利用できます。
左上にある「メニュー表示」ボタンをクリックします。
データベースを利用するための下図のメニュー画面が開きます
今のところ、3個の機能が設定されています
①は施設利用者のデータベースを操作する機能です。新規登録と作成済みデータの修正用編集メニューがあります。
まず最初は新規登録の説明です。
左側の「新規登録」ボタンをクリックします。
すると、入力画面が表示されますから「編集開始」ボタンをクリックします。
左端の氏名から順に入力していきます。
氏名を入力すると、よみがなが自動的に入力されますから一応読み仮名を確認し間違えがあれば修正してください。
性別はコンボボックスになっていますから、右側の▼をクリックして開いたリストから選択入力します。
生年月日は空欄をクリックすると別ウインドウが開いて年、月、日をそれぞれ選択入力します。年齢はその時自動的に計算されて転記されます。その他順次入力項目を選択入力していきますが、選択食の項目は今のところ何も設定していませんのでスルーしてください。一番最後の登録日の項目は自動的に入力されます。
登録ができたら③の「登録」ボタンをクリックすると画面の情報がSheet１に転記されます。
次は、「編集（修正）」ボタンの説明です。ボタンをクリックすると、①のインデックスボタンが表示されます。
このファイルにはあらかじめ１００件程のサンプルデータが入っていますので、「あ」のボタンをクリックします。
すると、入力してあるデータのよみがな項目のうちから先頭の文字が「あ」行（あいうえお）が含まれるデータをリストした画面が表示されます。
ここでは、リストの上から二行目にある「大村様」をクリックしました。
すでに入力されている大村様の各データを表示しますから、必要な個所を修正します。編集が終わったら登録ボタンを押すとそのまま修正が反映されます。
次に、下図の②登録情報を集計するボタンをクリックします。
Sheet１の情報からその日当日の施設利用者を抜き出してSheet2に書き出してその項目の内容を集計したものが下図のように表示します。栄養士さんが厨房へ指示だしするときのデータとして使用できます。
次に、③の食札カードを印刷するボタンの説明です。
上図③をクリックするとウインドウが下に伸びて、食札を印刷する準備をします。
下図の①でインデックスからデータの絞り込みをします。最初はすべての人が②のようにリストされます。
食札カードを印刷する人をクリックします。
下図のように、ここでは上から４人目までチェックを入れました。
リストボックスの下にある用紙の選択で、ラベル紙かコピー紙かを選択します。
次に、その下にあるカードを印刷するボタンを押します。
下図のように「印刷します」メッセージが表示しますから、OKボタンをクリックします。
すると、少し待つとエクセルの印刷プレビュー画面が表示されます。
プレビュー画面の左端にある印刷アイコンをクリックして印刷を開始してください
データを均一にするための入力フォーム
このシステムでは100名前後までの極小規模な老人保健施設などの給食施設で利用されることを前提に入居者用（給食利用者）名簿データベースを作っています。
スタイルとしては、下図の様なごく一般的なシートに一行に多項目のレコードを積み重ねる形です。
一行にA～Xまで14の項目を持っています。施設によってはもっと多くの項目を設定する必要があるかもしれませんが、その時は少しマクロを変更する必要があるかもしれません。
データを入力する際は入力する人によって形式が変わらないように、データの形式を定型化して均一に入力できるよう工夫しました。
入力する項目は下図の様な別のシートにまとめ、そのデータを引用する形でリストから選択する形式にしました。後から項目の内容を増減できるよう工夫しています。
下図はデータ入力の際、表示される入力フォームの一部です。
氏名読、み仮名等手入力する項目もありますが定型化できるところはコンボボックスから選択入力できるようにしたり、項目を選択したとき別表示されるリストから選択して入力できる部分もあります。同姓同名のチェック機能も付けました。
具体的コードの解説
コンボボックスのリストを設定する
左図の様な、別シートのQ列にある「副食形態」の項目の赤丸囲みしたところのデータを、右図のコンボボックスにセットします
|
1 2 3 4 5 6 7 8 9 10 11 |
Sub comboAddset(konbo As Variant, retu As Integer) '利用形態 Dim r As Integer Dim i As Integer ① r = Sheet4.Cells(4, retu).CurrentRegion.Rows.Count + 1 '一行目の空白もリストに入れたい For i = 1 To r With konbo .AddItem Sheet4.Cells(2 + i, retu) End With Next i End Sub |
comboAddsetの引数にはフォームのコントロールと設定シートのどのデータを使うのかを指定する列位置が指示されます
コレクションの作成
上図の様な入力フォームに張り付けた多くのコントロールを効率よくプログラムで利用するにはコレクションという機能を利用します。
例えば、ここで使用している入力用フォームには、多くのコンボボックスやテキストボックスを使用しています。その多種類のコントロールをコード中に表現するにはいちいちCombobox1、Combobox2、TextBox1、TextBox２…とそれぞれのコントロールの「オブジェクト名」を記述しなければなりませんが、そのフォームで使用する様々なオブジェクトをひとまとめにしてコレクション化すると一個のオブジェクト名 ＋ インデックスで表現できるようになります。これで繰り返し処理などで種々のコントロールをひとまとめにしてインデックスで表すと、インデックスを変数として扱えば効率よく処理できるので大変便利です。
以下はそのコレクションを作成するコードを表します
①でコレクション変数を宣言して
②で具体的にコレクション変数に、コントロールのオブジェクトを登録します。
このコードの場合コレクション「controrunarabi 」に登録されたコントロールの順番に１、２、３とインデックスが割り振られます。これを呼び出して利用するには controrunarabi (1)、controrunarabi (2)、controrunarabi (3) ・・・などとします。
下図で使われている controrunarabi (3) はフォームの「Tyomigana」とオブジェクト名を付けたTextBoxを表しています。
|
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 36 37 38 39 |
'① コントロールのコレクション変数宣言 Public controrunarabi As New Collection '② フォームのコントロールをコレクション定義 Sub fm_korekusyonn() With controrunarabi 'コレクションの削除 For i = 1 To .Count .Remove 1 Next i '③ コレクションの作成 .Add Item:=UserForm22.Lid '(1)ラベル .Add Item:=UserForm22.Tsimei '(2)テキストボックス .Add Item:=UserForm22.Tyomigana '(3)テキストボックス .Add Item:=UserForm22.Cseibetu '(4)コンボボックス .Add Item:=UserForm22.Ldoumei '(5)ラベル .Add Item:=UserForm22.Tseinenngappi '(6)テキストボックス .Add Item:=UserForm22.Tnennrei '(7)テキストボックス .Add Item:=UserForm22.Criyoukeitai '(8)コンボボックス .Add Item:=UserForm22.CTriyouyoubi '(9)コンボボックス .Add Item:=UserForm22.Csenntakusyoku '(10)コンボボックス .Add Item:=UserForm22.Ckyojuukuiki .Add Item:=UserForm22.Cbyoutai .Add Item:=UserForm22.Csyusyoku .Add Item:=UserForm22.Cfukusai .Add Item:=UserForm22.Tkinnsi .Add Item:=UserForm22.Ctokusyukeitai .Add Item:=UserForm22.Csyokkigu .Add Item:=UserForm22.Chayadasi .Add Item:=UserForm22.Tkaisibi .Add Item:=UserForm22.Ckara .Add Item:=UserForm22.Tsyuuryoubi .Add Item:=UserForm22.Cmade .Add Item:=UserForm22.Ctaisyoriyuu .Add Item:=UserForm22.Ttourokubi End With End Sub |
コントロールの利用 For…Next（繰り返し処理）で使う
’————————————————–
For i=1 to 10
controrunarabi (i) = ”テキスト、テストデータ” ＆ i
Next i
・
・
・
・
’————————————————–
等の様に使います。
例えば、コレクションを使用しないで同じ処理をしようとすると、コントロールのオブジェクト名を10個分記述しなければなりません。実際は20個以上のコントロールがありますから、これをたった一行で多くのコントロールを表現できるのですから大変便利です。
下図のプロシージャはこのシステムの実際のコードです。
入力フォームで新規に利用者のデータを登録する処理です。コントロールに入力されたデータをシートの入力できる行を見つけて、各項目列のセルに順次張り付ける処理をしています。
同じ繰り返し処理で使用しますが上の例とは違って For…Earth…In を使っています。
|
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 36 37 38 39 40 41 |
Sub sinnkikakikomi() '新規書き込みの処理 Dim ID As Integer '新しいID番号 Dim gyou As Integer '新規で入力できる行番号 Dim data As Variant Dim i As Integer 'ポイント① 書き込みできる最終行を調べる gyou = Sheet1.Cells(Rows.Count, 1).End(xlUp).Row + 1 'IDの行を整列させて最終行の値に１加算 Application.ScreenUpdating = False IDseiretu Application.ScreenUpdating = True '新しいデータにID番号を付ける。データが一行も無いときはID列は項目名になるので「１」を指定する If Sheet1.Cells(Rows.Count, 1).End(xlUp).Value = "ID" Then ID = 1 Else ID = Sheet1.Cells(Rows.Count, 1).End(xlUp).Value + 1 End If 'MsgBox id '書き込む i = 0 'ポイント② シートの新規行にコレクションのデータを転記する For Each data In controrunarabi i = i + 1 If i = 1 Then Sheet1.Cells(gyou, i).Value = ID ElseIf i = 7 Then Dim Nennrei As Integer 'IDと年齢は数値として登録するので整数値に変換 Nennrei = Val(data) Sheet1.Cells(gyou, i).Value = Nennrei Else Sheet1.Cells(gyou, i).Value = data End If Next data End Sub |
ポイント① 書き込みできる最終行を調べる
このプロシージャでまず、ポイントになるのがデータをどこに書き込むかを知る必要があるということです。エクセルでデータベースを作る時によく使うテクニックですから覚えておきましょう。
普通、データベースは行と列のデータの塊ですが、データとデータの間に空白の行や空白の列がないことが条件です。そして、データ群のデータの書き込まれた最終行以降はすべて空白のセルであることが必要です。
入力済みの既存データがある行が最終行ですから、その一個下のセルが新規行位置です。
それを表現したのが
gyou = Sheet1.Cells(Rows.Count, 1).End(xlUp).Row + 1 です。
赤色文字の部分．．．Cells(Rows.Count, 1) について。
()内のカンマの左側の、Rows.Count とはSheet1のシートの行数のことです。シートの行数はエクセルのバージョンによって違っています。筆者が現在使っているExcel2013で最終行の番号を見てみると、1048576となっています。もちろんこの実数を使ってもいいのでしょうが、汎用的にはRows.Countとするのがいいようです。そしてカンマの右側の１は列のことで、一列目、A列のことです。
つまり、1列目のセルの1048576行目のことです。
そして、 gyou = Sheet1.Cells(Rows.Count, 1).End(xlUp).Row + 1 の
赤文字のところ、．．．End(xlUp).Row + 1 について。
1列目の1048576行目のセルから上方向に向かって最初にデータがあるセル の１行下 を意味します。（シートの最終行のセルを選択して、Ctrl ＋ 上矢印キーを押したときと同じ動作）
何もデータが書き込まれていないシートでセルA10に文字列 ”ABC” を書き込んでおきます。
gyou = Sheet1.Cells(Rows.Count, 1).End(xlUp).Row + 1 を実行すると変数gyouには 11が代入されます。
このgyouを使って Cells(gyou, 1) ＝ ”DEF” などと利用することになります。
Sheet1.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Select
ActiveCell = ”DEF” などでも上と同じことができますね。
エクセルには様々な機能が数多くあります。、何をどのよう動かしたいか考えたら、大抵のことはほぼ全て実現できます。
ポイント② コントロールの利用 For…Earth…In（繰り返し処理）で使う
上で作ったコレクションを使って下のコードを実行してみます。
For Eachで使う赤文字の変数（data）はVariant で宣言しなければならないと書いてありましたが、宣言は省略できるとも書いてありましたので、ここでは宣言を省略しました。
In に指定した コレクション controrunarabi には、フォームに張り付けたコントロールが24個が登録されています。
そして、繰り返すたびにcontrorunarabiに登録された順にオブジェクトが取り出されdataに渡されます。
Dim i As Integer
i=0
For Each data In controrunarabi
i = i + 1
Cells(i, 1) = data
Next data
すなわち、一回目の繰り返しではCells(1, 1)に
コレクションの一番目のオブジェクトLid（ラベル）に書き込まれた値が転記され
二回目の繰り返しではCells(2, 1)に
コレクションの二番目ののオブジェクトTsimei（テキストボックス）に書き込まれた値が転記され
・
・
・
となるわけです。
（For Eachではコレクションから最後のオブジェクトが取り出されると繰り返しが終了しますが、何回繰り返したかを知る必要があったので、別途カンター用の変数 i を宣言して利用しました）
10面カットラベル紙へ印刷
枠線なし印刷の様子
入居者の食札を作る時は、カードに直接印刷するのは難しいので、ラベル紙に印刷させる方法をとりました。ここではラベル紙メーカー「Aone」で出している10面カットラベル紙にサイズを合わせて印刷できるようにしました。10面カット用の用紙なので、人数などによっては、10カット丁度にならず半端で印刷が終わる場合もあると思い、残りのカット用紙が無駄にならないように、次回印刷の時残っているカット用紙の状態を選択して印刷を開始できるように工夫しました。
下図はその選択画面です。
又、カットラベル紙なのでデータに枠線を設定していませんが、あえて枠線をつけて印刷ができるようにもしています。その場合は普通のコピー紙などに枠線付きで印刷して枠線に合わせてハサミでカットして使うなどもできるようにしました。
利用される施設のやり方に合わせて使用してみてください。
まだまだあります。
食札情報をセルに埋め込んで印刷する
編集後記
コロナ禍、未だ続く自粛生活ですが、もともと透析治療中ですから、どこにも出かける予定もありません。鮎斗君は散歩にも連れて行けないのでストレスが溜まっているようだけど何とかやり過ごしています。
毎回、病床で透析の治療を受けながら、プログラミングしているけど、最近はめっきり集中力が持続しなくなってしまった。午前中3時間もすると疲れ切って眠ってしまうので一つのプロシージャを完成させるのに３日もかかってしまうこともあるんです。
今回のデータベースも15日もかかってしまった。まだまだ満足の行くものには程遠いが、まずは使用できる段階までにはなったので公開しました。
追伸
約１年前に買ったキーボード（ ANNE PRO2）の調子がよくない。XPS13にBluetooth接続で使っていたけど、そのBluetooth接続が切れることが度々起きる、Win10のデバイス設定でBluetoothの再接続設定をしても3分と持たずに切れる。ドライバーを入れなおしたり、色々やったけどいっこうに改善しない。中華製とはこんなものなのか？他の5000円程度の安物と比べても結構高かったけど気に入っていたのに残念だ。
（この部分だけで4回切れた。）
コメント