商品在庫画面の棚卸集計表出力 [VBA在庫管理システムVer01#30]

前回、商品在庫画面の発注リストを例に、帳票出力部分を作成しました。

この記事では、棚卸集計表の出力を解説します。

VBA在庫管理システムVer01

帳票出力の準備

出力帳票の作成

レポートブック（在庫管理REPORT.xlsx）に棚卸集計表用のシートを作成します。

帳票はヘッダー部のみ作成します。標題はVBAで書き込みますが、サンプルとして入力しています。

書式は、左に在庫情報、実棚数以降は記入用として作成します。

出力ボタンの作成

商品在庫画面に棚卸集計表ボタンを追加します。

ツールボックスでコマンドボタンを選択し、フッター部でドラッグしてコントロールを追加し、プロパティウィンドウで詳細設定します。

※プロパティ設定後のユーザーフォーム

　

標準モジュールの作成

標準モジュールを追加し、オブジェクト名を棚卸集計表とします。

帳票を出力するプロシージャはユーザーフォームの動作とは異なりますので、標準モジュールに作成していきます。

棚卸集計表の出力

変数の設定

商品マスタデータの取り込み用変数の設定をしていきます。
VBEで標準モジュール 棚卸集計表を選択し、宣言セクションに列挙型変数をPrivateで宣言します。

Option Explicit

'wsItemMasterの項目列設定
Private Enum wsItemMasterColumns
    M商品ID = 1
    M商品名称
    M登録日
    M更新日
    M発注先
    M発注単位
    M梱包数
    M最大在庫
    M発注点
    M棚位置
    M棚卸日
    M棚卸数
    M出庫済み
    M出庫予定
    M入庫済み
    M入庫予定
    M現在在庫
    M有効在庫
    M発注Flag
    M削除
End Enum

@1.列挙型変数の宣言

標準モジュール 棚卸集計表では、商品マスタシートの全ての列を列挙型変数で宣言します。

棚卸集計表

帳票を出力するプロシージャをサブルーチンとして、標準モジュール 棚卸集計表に作成します。

Sub ShowItemInventoryReport()

'インプットボックスを設定し、棚卸日を取得
Dim myMsg As String, myTitle As String, 棚卸日 As String
    myMsg = "棚卸日を入力してください" & vbCr & "(例:2023/01/01）"
    myTitle = "棚卸日入力"
    棚卸日 = Application.InputBox(prompt:=myMsg, Title:=myTitle, Default:=Format(Date, "yyyy/mm/dd"), Type:=2)

'インプットボックスの入力値を判定
If 棚卸日 = "False" Then
    Exit Sub
End If

    Application.ScreenUpdating = False


'棚卸集計データの更新---------------------------------------------------------------------------------------------------

'作業ブックを開く
Workbooks.Open データ位置 & "在庫管理DATA.xlsx"
    
'オブジェクト変数の取得
Dim wsItemMaster As Worksheet, wsItemInOut As Worksheet, wsStockExtraction As Worksheet
Set wsItemMaster = Workbooks("在庫管理DATA.xlsx").Sheets("M_商品")
Set wsItemInOut = Workbooks("在庫管理DATA.xlsx").Sheets("T_入出庫")
Set wsStockExtraction = Workbooks("在庫管理DATA.xlsx").Sheets("受払抽出")

'在庫更新
Dim Buf() As Variant
Dim i As Long

'最終行の取得
Dim wsItemMasterRow As Long
wsItemMasterRow = wsItemMaster.Cells(Rows.Count, 1).End(xlUp).Row

'変数の設定
ReDim Buf(wsItemMasterRow - 1, 4) As Variant

With wsStockExtraction
    For i = 0 To wsItemMasterRow - 2
    
        '抽出条件の設定
        .Activate
        .Cells(2, 1).Value = wsItemMaster.Cells(i + 2, 1).Value
        .Cells(2, 2).Value = ">" & wsItemMaster.Cells(i + 2, 11).Value
        
        '受払抽出
        wsItemInOut.Columns("A:H").AdvancedFilter _
         Action:=xlFilterCopy, _
         CriteriaRange:=.Range("A1:C2"), _
         CopyToRange:=.Range("F1:I1")
        
        '出庫済み数
        Buf(i, 0) = WorksheetFunction.SumIfs(Range("I:I"), .Range("H:H"), "　出庫", .Range("G:G"), "<" & 棚卸日)
        
         '出庫予定数
        Buf(i, 1) = WorksheetFunction.SumIfs(Range("I:I"), .Range("H:H"), "　出庫", .Range("G:G"), ">=" & 棚卸日)
    
        '入庫済み数
        Buf(i, 2) = WorksheetFunction.SumIfs(Range("I:I"), .Range("H:H"), "入庫", .Range("G:G"), "<=" & 棚卸日)
    
         '出庫予定数
        Buf(i, 3) = WorksheetFunction.SumIfs(Range("I:I"), .Range("H:H"), "入庫", .Range("G:G"), ">" & 棚卸日)
    
         '現在在庫
        Buf(i, 4) = wsItemMaster.Cells(i + 2, wsItemMasterColumns.M棚卸数) - Buf(i, 0) + Buf(i, 2)
     
    Next
End With

'製品マスタへの書き込み
wsItemMaster.Range("M2").Resize(UBound(Buf, 1) + 1, UBound(Buf, 2) + 1).Value = Buf


'棚卸集計データの取得---------------------------------------------------------------------------------------------------

'棚卸集計データの取り込み
Dim aryInventory() As Variant
Dim j As Long

'商品名称/棚位置を昇順で並べ替え
wsItemMaster.Range("A1").Sort key1:=wsItemMaster.Range("B1"), order1:=xlAscending, Header:=xlYes
wsItemMaster.Range("A1").Sort key1:=wsItemMaster.Range("J1"), order1:=xlAscending, Header:=xlYes

'発注リストがある時のみデータ取り込み
If wsItemMasterRow >= 2 Then

    '変数の設定
    ReDim aryInventory(wsItemMasterRow - 2, 6) As Variant
    j = 0
    
    '変数への取り込み
    For i = 2 To wsItemMasterRow
        If wsItemMaster.Cells(i, wsItemMasterColumns.M削除) = 0 Then
            aryInventory(j, 0) = wsItemMaster.Cells(i, wsItemMasterColumns.M商品ID)
            aryInventory(j, 1) = wsItemMaster.Cells(i, wsItemMasterColumns.M商品名称)
            aryInventory(j, 2) = wsItemMaster.Cells(i, wsItemMasterColumns.M棚位置)
            aryInventory(j, 3) = wsItemMaster.Cells(i, wsItemMasterColumns.M棚卸数)
            aryInventory(j, 4) = "+" & wsItemMaster.Cells(i, wsItemMasterColumns.M入庫済み)
            aryInventory(j, 5) = "－" & wsItemMaster.Cells(i, wsItemMasterColumns.M出庫済み)
            aryInventory(j, 6) = wsItemMaster.Cells(i, wsItemMasterColumns.M現在在庫)
            j = j + 1
        End If
    Next

End If

'作業ブックを閉じる
Workbooks("在庫管理DATA.xlsx").Close savechanges:=False


'棚卸集計データの書込---------------------------------------------------------------------------------------------------
    
'作業ブックを開く
Workbooks.Open データ位置 & "在庫管理REPORT.xlsx"
    
'オブジェクト変数の取得
Dim wsInventoryList As Worksheet
Set wsInventoryList = Workbooks("在庫管理REPORT.xlsx").Sheets("棚卸集計表")

'最終行の取得
Dim wsInventoryListRow As Long
wsInventoryListRow = wsInventoryList.Cells(Rows.Count, 1).End(xlUp).Row

'棚卸集計表のリセット
If wsInventoryListRow > 4 Then
    wsInventoryList.Rows("5:" & wsInventoryListRow).Delete shift:=xlUp
End If

'棚卸集計表への書き込み
If wsItemMasterRow >= 2 Then
    wsInventoryList.Cells(5, 1).Resize(UBound(aryInventory, 1) + 1, UBound(aryInventory, 2) + 1).Value = aryInventory
End If

'棚卸日の書き込み
wsInventoryList.Cells(3, 8).Value = Format(棚卸日, "yyyy/mm/dd")

'最終行の再取得
wsInventoryListRow = wsInventoryList.Cells(Rows.Count, 1).End(xlUp).Row

'罫線の設定
With wsInventoryList.Range("D4:I" & wsInventoryListRow).Borders(xlInsideVertical)
    .LineStyle = xlDot
    .Weight = xlHairline
End With
With wsInventoryList.Range("D4:D" & wsInventoryListRow).Borders(xlEdgeLeft)
    .LineStyle = xlContinuous
    .Weight = xlThin
End With
With wsInventoryList.Range("A4:I" & wsInventoryListRow).Borders(xlInsideHorizontal)
    .LineStyle = xlDot
    .Weight = xlHairline
End With

'ブックの最大化
ActiveWindow.WindowState = xlMaximized

'表題の作成
wsInventoryList.Cells(2, 1).Value = "● " & Format(棚卸日, "yyyy/mm/dd") & "　棚卸集計表"

'印刷範囲の設定
wsInventoryList.PageSetup.PrintArea = wsInventoryList.Range("A1:I" & wsInventoryListRow).Address

'印刷プレビューの表示
Application.CommandBars.ExecuteMso "PrintPreviewAndPrint"

    Application.ScreenUpdating = True

'出力フォームの表示
frmItemInventoryReport.show

End Sub

@1.棚卸日の取得
インプットボックスの機能を利用し、ユーザーに棚卸日を入力して貰います。

@2.入力値の判定
インプットボックスのキャンセルが押された場合は、プロシージャーを抜けます。

A.データの更新：在庫更新のコードを利用し、更新データを商品マスタシートへ書き込みます。更新は５個のデータのみとします。

@3. 作業ブックを開く
Application.ScreenUpdatingをFalsに設定しエクセルの描画を止め、データブックを開き、各シートと、M_商品シートの最終行を変数に格納します。

@4.配列変数の設定
１レコードにつき、５個のデータが格納できるようにします。

@5.在庫データの取得
商品マスタデータを１行づつループしてデータを取得します。
① フィルターオプション機能で、商品IDを基準に、前回棚卸日より後の入出庫履歴を抽出します。
② 抽出データにSumIfs関数を利用して、出庫済み数・出庫予定数・入庫済み数・入庫予定数を集計し、配列変数に取り込みます
③ 棚卸数を基準に、②で取得したデータを利用して、現在在庫数を計算し、配列変数に取り込みます。

@6. 製品マスタへの書き込み
製品マスタシートへの書き込みは、書き込みの起点となるセル位置("M2")から、UBound関数を使用した Resizeでセル範囲を拡張させて指定し、配列データを一括で書き込みます。

B.データの取得：商品マスタシートからデータを配列変数に取得します。棚位置基準で並べ替えて取り込みます。

@7.シートの並び替え
商品名称と棚位置を昇順に並び替え、リストを成形します。

@8.配列変数への取り込み
データが有るときのみ、配列変数 aryInventory に取り込みます。
配列変数の要素数を確定させたあと、For〜Next構文でデータを取り込みます。
データは論理削除の構造なので、削除フラグが立っているものは取り込みません。

@9.ブックの立ち下げ
シートを操作してますので、初期化するため、ブックは保存せずに閉じます。

C.データの書込：配列変数を棚卸集計表シートへ書き込みます。

@10.作業ブックを開く
レポートブックを開き、棚卸集計表シートを変数に格納します。
このとき、作業シートに前回のデータが残っているので、削除用に最終行も取得します。

@11.棚卸集計表のリセット
棚卸集計表の前回データを削除します。

@12.棚卸集計表への書き込み
データが有るときのみ、作業シートにデータを書き込みます。
データを取り込んだ配列変数 aryInventory をResizeで棚卸集計表に書き込みます。
また、H3セルに棚卸日を入力します。

@13.書式設定
罫線は、線の種類と太さを設定しながら引きます。
標題を設定し、棚卸集計表が完成です。

@14.印刷設定
印刷範囲を指定し、プレビュー画面を表示させます。
また、Application.ScreenUpdatingもTrue設定に戻します。

@15.終了フラグ
出力フォームを表示して、処理は終了です。

出力フォームの作成

前回の帳票出力の出力フォーム作成を参考にして、帳票出力完了後の終了フラグ用に、新たにユーザーフォーム frmItemInventoryReportを作成します。
　
※VBA実行時の出力画面

棚卸集計表ボタンの設定

棚卸集計表ボタンクリック時の動作を作成します。サブルーチンを実行させます。

Private Sub btnItemInventoryReport_Click()

'棚卸集計表の出力
Call ShowItemInventoryReport

End Sub

@1.サブルーチンの実行
棚卸集計表出力のプロシージャを実行します。

棚卸集計表の出力

棚卸集計表ボタンをクリックし、動作を確認します。

画面上では、商品在庫画面の上に、棚卸集計表。更にその上に、出力フォームが表示されている状態です。

棚卸集計表は、実棚の計量の作業支援用に、棚位置基準で並べています。また、入出庫履歴を表示することにより、計量忘れや不動在庫が確認できるようにしています。

出力時は、出力フォームがアクティブな状態なので、印刷するには、エクセルシートをクリックしアクティブにしてから、印刷ボタンをクリックします。２回、クリックが必要になります。

商品在庫画面の棚卸集計表出力部分ができました。
次回は商品在庫画面の入力支援を作成し、完成させます。
www.minizaiko.com