お弁当注文システムを作る ~その2~

出力の効率化

「お弁当注文システムを作る ~その1~」の続きとなります。その1では、スマートフォンでお弁当の注文ができるように、注文フォームを作成し、注文フォームが7時から9時まで注文を受け付けるようにタイマーを設定しました。

4. 毎朝9時に注文を取りまとめ発注書を作成する

お弁当の種類と数の集計や発注書の作成は、毎日のことなので、これらの作業をExcelマクロで自動化していきます。

プログラムのフローとしては、①Excelファイルに、日々の注文内容を取り込む、②取り込んだデータをもとにDBを作成する、③DBから「発注書」を出力するの3工程となります。

(1)パワークエリを活用してExcelファイルに注文内容を取り込む

今回は、Excelのパワークエリを活用して、外部データの取り込みを行っていきます。パワークエリはExcel2016から標準で装備されていますが、それ以前のバージョンでもインストールして利用することができます。

①Excelマクロファイル(お弁当注文システム)を作成する

Excelファイルを新規作成します。新規作成したファイルの「ファイル」から「名前を付けて保存」を選択し、保存場所として「デスクトップ」を指定します。

「ファイル名」に「お弁当注文システム」という名前を付け、「ファイルの種類」に「excelマクロ有効ブック」を選択して、「保存」ボタンをクリックします。

②スプレッドシート(お弁当の注文フォーム(回答))のURLを取得する

googleドライブからスプレッドシート(お弁当の注文フォーム(回答))を開き、右上の「共有」ボタンをクリックし、「リンクを取得」で「リンクを知っている全員に変更」をクリックした後、「リンクをコピー」をクリックします。(※スプレッドシート(お弁当の注文フォーム(回答))は、お弁当注文システムを作る~その1~参照)

③コピーしたリンクをメモ帳に貼り付けて、アドレスの最後の部分「edit?usp=sharing」を「export?format=xlsx」に変更します。

https://docs.google.com/spreadsheets/d/**************************************/edit?usp=sharing

https://docs.google.com/spreadsheets/d/**************************************/export?format=xlsx

④Excelファイル(お弁当注文システム)を開き、「データ」(Excel2013はpower query)タブをクリックして、「WEBから」を選択します。

⑤「WEBから」というダイヤログボックスの「URL」欄に③の変更後のURLを貼り付け、「ok」ボタンをクリックします。

⑥「ナビゲータ」の左下「フォームの回答1」をクリックすると、右側にプレビューが表示されるので確認して、「読み込み」ボタンをクリックします。

⑦読み込みできました。読み込みをしたシートの名前を「inport」に変更します。

⑧ここまでの読み込み動作のマクロを作成します。

ⅰ「開発」タグから「Visual Basic」を選択します。

ⅱ「Visual Basic」の「挿入」タグから「標準モジュール」を選択して標準モジュールを作成します。

ⅲ 作成した標準モジュールに下記コードを入力します。

Sub inport() 
  ActiveWorkbook.Connections("クエリ - フォームの回答 1").Refresh 
End sub

フォームの回答1」は適宜クエリ名を入れてください。inportシートのクエリ内のセルをクリックすると右側に「クエリ」名が表示されます。

ⅳ 新しいシートを作成し、シート名を「メニュー」に変更して、「開発」タグから「挿入」をクリックし、フォームコントロールの「ボタン」を選択するとカーソルが十字になるので、シートの上でドラッグして適当なサイズのボタンを作成します。

ⅴ 「マクロの登録」ダイヤログの「マクロ名」欄の「inport」を選択して「OK」をクリックします。

ⅵ 作成されたボタンの上で右クリックして、「テキストの編集」からボタンの表示名を「inport」に変更します。

ⅶ ボタンをクリックすると更新されます(inportシートにスプレッドシートのデータが取り込まれる)。

(2) 取り込んだデータをもとにDBを作成する

①DBを作成する

 取り込んだデータをそのまデータベースとして利用しても良いのですが、日を追うごとにデータ量が増えて行くため、更新(取り込み)に時間がかかってしまいます。

 そこで、スプレッドシート(お弁当の注文フォーム(回答))のデータは、日々リセットし、当日の分のデータだけを読み込むようにしたいので、少し面倒ですがinportシートは取り込みデータの仮置き場として利用し、データベース用シートは他に用意します。

ⅰ 「新しいシート」を作成しシート名を「DB」に変更します。

ⅱ 「DB」シートの1行目のに項目の見出しを設けます。見出し名は(A1)「区分」、(B1)「年月日時分秒」、(C1)「管理№」、(D1)「社員№」、(E1)「氏名」、(F1)「お弁当」、(G1)「数量」とします。

ⅲ 「開発」タグの「visual Basic」を選択し、「標準モジュール」内に下記マクロコードを入力(コピペ)します。

Sub DBrenew()
'注文DBの更新
    Dim n As Integer, m As Integer    
    Dim e As Date, x As Integer, y As String, z As Integer, v As String   'e=年月日時分秒,x=社員番号,y=氏名,z=数量,v=弁当
    m = Worksheets("inport").Cells(Rows.Count, 1).End(xlUp).Row
    For n = 2 To m
        e = Worksheets("inport").Cells(n, 1)  '年月日時分秒
        x = Worksheets("inport").Cells(n, 2)  '社員番号
        y = Worksheets("inport").Cells(n, 3)  '氏名
        v = Worksheets("inport").Cells(n, 4)  '弁当
        z = Worksheets("inport").Cells(n, 5)  '数量
        
        Dim foundcell As Range, firstcell As Range, targetcell As Range
        
        Set foundcell = Worksheets("DB").Range("b:b").Find(e)
        If Not foundcell Is Nothing Then        
            Set firstcell = foundcell        
            If foundcell.Offset(0, 2) = x Then
                GoTo 100
            Else
                Do
                    Set foundcell = Worksheets("DB").Range("b:b").FindNext(foundcell)
                        If foundcell.Address = firstcell.Address Then
                            Exit Do
                        End If                    
                        If foundcell.Offset(0, 2) = x Then
                            GoTo 100
                        End If
                Loop
             End If
        End If
  
        Dim o As Integer
        o = Worksheets("DB").Cells(Rows.Count, 2).End(xlUp).Row + 1
        Worksheets("DB").Cells(o, 1) = 1  '区分
        Worksheets("DB").Cells(o, 2) = e  '年月日時分秒
        Worksheets("DB").Cells(o, 3) = o  '管理№
        Worksheets("DB").Cells(o, 4) = x  '社員番号
        Worksheets("DB").Cells(o, 5) = y  '氏名
        Worksheets("DB").Cells(o, 6) = v  'お弁当
        Worksheets("DB").Cells(o, 7) = z  '数量            
100
    Next n
End Sub

ⅳ 「メニュー」シートの「開発」タグから「挿入」、フォームコントロールの「ボタン」を選択し、「メニュー」シート上にボタンを作成します。

ⅴ 「マクロの登録」ダイヤログでは、マクロ名で「DBrenew」を選択します。

ⅵ ボタン上で右クリックして、ボタンの表示名を「DBrenew」に変更します。

ⅶ 「DBrenew」ボタンをクリックすると「DBシート」にinportされたデータが追記されていきます。

②本日の注文者リストを作成する

本日の注文状況を確認するため、DBから本日の注文者を抽出してリスト化します。また、注文に間違いがある場合は、ここで注文をキャンセルできるようにします。

ⅰ 新しいシートを追加して、シート名を「orderlist」とします。

ⅱ 「orderlist」シートの2行目に項目の見出しを設けます。見出し名は(A2)「№」、(B2)「管理№」、(C2)「氏名」、(D2)「お弁当」、(E2)「数量」とし、セル(A1)~セル(E1)をセル結合します。

ⅲ 「開発」タグの「visual Basic」を選択し、「標準モジュール」内に下記マクロコードを入力(コピペ)します。

Sub orderlist()
    Worksheets("orderlist").Activate
    Dim a As Long
    a = Worksheets("orderlist").Cells(Rows.Count, 1).End(xlUp).Row
    If a <> 2 Then
        Worksheets("orderlist").Range("a3:e" & a).Select
        Selection.ClearContents
        Selection.Borders.LineStyle = xlLineStyleNone
    End If

    Dim d As Date     ' d =今日の日付
    d = Date
    Dim n As Integer, m As Integer
    Dim e As Date, f As Date
    Dim p As Integer
    m = Worksheets("DB").Cells(Rows.Count, 2).End(xlUp).Row
    For n = 2 To m
        If Worksheets("DB").Cells(n, 1) <> 0 Then
            e = Worksheets("DB").Cells(n, 2)
            f = DateSerial(Year(e), Month(e), Day(e))
            If f = d Then
                Dim o As Integer
                o = Worksheets("orderlist").Cells(Rows.Count, 1).End(xlUp).Row + 1
                p = p + 1
                Worksheets("orderlist").Cells(o, 1) = p        '№
                Worksheets("orderlist").Cells(o, 2) = Worksheets("DB").Cells(n, 3)  '管理№
                Worksheets("orderlist").Cells(o, 3) = Worksheets("DB").Cells(n, 5)  '氏名
                Worksheets("orderlist").Cells(o, 4) = Worksheets("DB").Cells(n, 6)  'お弁当
                Worksheets("orderlist").Cells(o, 5) = Worksheets("DB").Cells(n, 7)  '数量
            End If
        End If
    Next n
    
    Worksheets("orderlist").Cells(1, 1) = Month(d) & "月" & Day(d) & "日 お弁当注文者リスト"

    a = Worksheets("orderlist").Cells(Rows.Count, 1).End(xlUp).Row
    Worksheets("orderlist").Range("a2:e" & a).Borders.LineStyle = xlContinuous
End Sub

ⅳ 「メニュー」シートの「開発」タグから「挿入」、フォームコントロールの「ボタン」を選択し、「メニュー」シート上にボタンを作成します。

ⅴ 「マクロの登録」ダイヤログでは、マクロ名で「orderlist」を選択します。

ⅵ ボタン上で右クリックして、ボタンの表示名を「orderlist」に変更します。

ⅶ 「orderlist」ボタンをクリックすると「orderlistシート」に本日の注文者リストが出力されます。

③注文をキャンセルする

本日の注文者リストで確認しているときに、注文の間違いがあった場合、ここで注文をキャンセルできるのが便利です。今回は、キャンセルしたい注文のセルの上で右クリックして注文をキャンセルできるようにします。

ⅰ 右クリックメニューの中に「注文のキャンセル」を追加するには、「orderlistシートモジュール」に下記マクロコードを入力(コピペ)します。「orderlistシートモジュール」は、エクスプローラーの「orderlistシート」アイコンをダブルクリックして開きます。

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
    Dim cmdBar1 As CommandBarControl
    For Each cmdBar1 In Application.CommandBars("cell").Controls
        If cmdBar1.Caption = "注文のキャンセル" Then
            cmdBar1.Delete
        End If
    Next    
    With Application.CommandBars("cell").Controls.Add
        .Caption = "注文のキャンセル"
        .OnAction = "ordercancel"
    End With
End Sub


Private Sub Worksheet_Deactivate()
    Dim cmdBar1 As CommandBarControl
    For Each cmdBar1 In Application.CommandBars("cell").Controls
        If cmdBar1.Caption = "注文のキャンセル" Then
            cmdBar1.Delete
        End If
    Next
End Sub

ⅱ 次は注文のキャンセルがクリックされたときに、注文キャンセルを実行するマクロコードです。標準モジュールに次のマクロコードを記入(コピペ)します。

Sub ordercancel()
  Dim n as integer,t As String,o as long
    n = ActiveCell.Row
    t = Worksheets("orderlist").Cells(n, 3)  ‘オーダーをキャンセルする者の氏名
    o = Worksheets("orderlist").Cells(n, 2)  ‘キャンセルするオーダーの管理№
    Set rcd = Worksheets("DB").Range("c:c").Find(o, lookat:=xlWhole)
    If Worksheets("orderlist").Cells(n, 1).Value = "" Then
        MsgBox "キャンセルする行を指定してください"
    Else
        rtn = MsgBox(t & "さんのオーダーをキャンセルしますか", vbYesNo + vbQuestion + vbDefaultButton2, "確認")
        Select Case rtn  '押されたボタンの確認
            Case vbYes
                rcd.Offset(0, -2) = 0
                Call Module1.orderlist
            Case vbNo
        End Select
    End If    
End Sub

ⅲ orderlistシートに表示された注文者リストのキャンセルしたい行にマウスポインタを合わせて右クリックすると、右クリックメニューの一番したに「注文のキャンセル」が出ていますので、こちらをクリックすると、注文がキャンセルされます。

ⅳ 注文者リストから「にこにこ太郎」の注文が削除されました。

(3) DBから「発注書」を出力する

 DBから日々の発注書を作成・出力する手順は以下の通りです。

①発注書の書式を作成する

ⅰ 新しいシートを作成し、シート名を「発注書」に変更します。

ⅱ 「発注書」のフォーマットを作成します。

    ※ここでは行・列の番号・記号を間違えないようにしてください。

②マスターシートにお弁当のメニューと価格を登録する

ⅰ 新しいシートを作成し、シート名を「マスター」に変更します。

ⅱ マスターシートのセルA1に「メニュー」、セルB1に「価格」という見出しを入力し、メニュー表を作成します。

  ※ここで入力するメニュー名は注文フォームのメニュー名と完全に一致するようにしてください。

③発注書を出力するマクロコードを書く

ⅰ 「開発」タグの「visual Basic」を選択し、「標準モジュール」内に下記マクロコードを入力(コピペ)します。

Sub 発注書()
    Worksheets("発注書").Activate
        Set rcd = Worksheets("発注書").Range("b:b").Find("計")
        r = rcd.Row - 1
        If r <> 10 Then
            Worksheets("発注書").Range("b11:b" & r).EntireRow.Delete
        End If

    Dim d As Date     ' d =今日の日付
    d = Date
    Worksheets("発注書").Cells(1, 5) = d

    Dim n As Integer, m As Integer, x As Integer, e As Date, f As Date    
    m = Worksheets("DB").Cells(Rows.Count, 1).End(xlUp).Row
    x = 0
    For n = 2 To m
        e = Worksheets("DB").Cells(n, 2)
        f = DateSerial(Year(e), Month(e), Day(e))
        If f = d Then
            If Worksheets("DB").Cells(n, 1) = 1 Then
                v = Worksheets("DB").Cells(n, 6)
                Set rcd2 = Worksheets("発注書").Range("b:b").Find(v)
                    If rcd2 Is Nothing Then
                        Set rcd = Worksheets("発注書").Range("b:b").Find("計")
                        rcd.EntireRow.Insert
                        rcd.Offset(-1, 0) = v
                        rcd.Offset(-1, 1) = Worksheets("DB").Cells(n, 7)
                        Set rcd3 = Worksheets("マスター").Range("a:a").Find(v)
                        If rcd3 Is Nothing Then
                            MsgBox "マスターにメニューを登録してください"
                            Exit Sub
                        End If
                        rcd.Offset(-1, 2) = rcd.Offset(-1, 1) * rcd3.Offset(0, 1)
                    Else
                        rcd2.Offset(0, 1) = rcd2.Offset(0, 1) + Worksheets("DB").Cells(n, 7)
                        Set rcd3 = Worksheets("マスター").Range("a:a").Find(v)
                        rcd2.Offset(0, 2) = rcd2.Offset(0, 1) * rcd3.Offset(0, 1)
                    End If
            End If
        End If
    Next n
    
    Set rcd = Worksheets("発注書").Range("b:b").Find("計")
    r = rcd.Row - 1
    For n = 11 To r
        su = su + Worksheets("発注書").Cells(n, 3)
        ki = ki + Worksheets("発注書").Cells(n, 4)
    Next n
    Worksheets("発注書").Cells(r + 1, 3) = su
    Worksheets("発注書").Cells(r + 1, 4) = ki
End Sub

ⅱ 「メニュー」シートの「開発」タグから「挿入」、フォームコントロールの「ボタン」を選択し、「メニュー」シート上にボタンを作成します。

ⅲ 「マクロの登録」ダイヤログでは、マクロ名で「発注書」を選択します。

ⅳ ボタン上で右クリックして、ボタンの表示名を「発注書」に変更します。

ⅴ 「発注書」ボタンをクリックすると本日の発注書が出力されます。

次回、「その3」では月次の締め「支払確認書」と「給与天引きリスト」を作成します。

コメント

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