日付ごとの集計マクロ

Question

日付と入荷予定数が記載された一覧表のSHEET(SHEET名：集計表)があります。
(商品名もありますが、特にマクロ起動の際は使用しません)
それとは別に管理表のSHEET(SHEET名：管理表)があり、それには横軸方向に日付があり、その下にその日付で入荷予定の総数を記入する予定です。
集計表の日付、入荷数を抽出し、管理表に各日の総数を記入するようなマクロを組みたいです。
日々入荷予定数が変わるので、日々管理表への総数変更(更新)をしたいため、マクロで自動化したいです。

もし難しい構文になりそうでしたら、簡単でもいいので説明を記載していただけると非常に助かります。
よろしくおねがいします。

tatsu99 · Accepted Answer

No6です。
以下のマクロを管理表.xlsmに登録してください。
    Const folder_name As String = "d:\goo\excel"     '集計表.xlsmの格納ホルダ
が　集計表.xlsmの格納ホルダです。あなたの環境にあわせて適切に設定してください。
集計表.xlsmは必ず、クローズした状態で、マクロを実行してください。
--------------------------------------------------
Option Explicit
Public Sub 総数更新()
    Const folder_name As String = "d:\goo\excel"     '集計表.xlsmの格納ホルダ
    Const book_name1 As String = "集計表.xlsm"
    Const book_name2 As String = "管理表.xlsm"
    Dim sh1 As Worksheet
    Dim sh2 As Worksheet
    Dim maxrow1 As Long
    Dim maxcol2 As Long
    Dim row1 As Long
    Dim col2 As Long
    Dim diff As Long
    Dim date0 As Date
    Set sh2 = Worksheets("管理表")
    maxcol2 = sh2.Cells(2, Columns.Count).End(xlToLeft).Column   '2行目の最終列取得
    '総数クリア
    sh2.Range("B3:B" & maxcol2).Value = ""
    date0 = sh2.Cells(2, "B").Value
    If Day(date0) <> 1 Then
        MsgBox ("B2の日付が1日でない")
        Exit Sub
    End If
    Application.ScreenUpdating = False
    Workbooks.Open (folder_name & "\" & book_name1)
    Workbooks(book_name1).Activate
    Set sh1 = Worksheets("集計表")
    maxrow1 = sh1.Cells(Rows.Count, "C").End(xlUp).Row   'C列の最大行取得
    For row1 = 2 To maxrow1
        diff = sh1.Cells(row1, "C").Value - date0
        If diff >= 0 Then
            ThisWorkbook.Worksheets("管理表").Cells(3, 2 + diff).Value = ThisWorkbook.Worksheets("管理表").Cells(3, 2 + diff).Value + sh1.Cells(row1, "D").Value
        End If
    Next
    Workbooks(book_name1).Close
    Application.ScreenUpdating = True
    MsgBox ("更新完了")
End Sub

めぐみん_ · Answer

NO.7です。

＞＞マクロ実行前は閉じている前提が希望です

って、閉じたままでデータを取りたいのではなく、
実行の際にブックを開きたいと言うことだった（？）のですね。
勘違いしてました。

てっきり『共有ネットワーク上にある別ＰＣ又はドライブのブック』なので、
開くと入力する担当者側が操作できなくなると危惧してのことかと、
深読みしすぎました・・・たぶん。

tom04 · Answer

こんばんは！

横からお邪魔します。
「集計表.xlsm」と「管理表.xlsm」は同じフォルダ内に保存されているという前提です。
VBAでもそのままワークシート関数が使えますので、SUMIF関数で対応してみました。

管理表Bookの標準モジュールにしてください。

Sub Sample1()
    Dim j As Long, fN As String
    Dim wB As Workbook, wS As Worksheet
        fN = ThisWorkbook.Path & "\" & "集計表.xlsm"
        Workbooks.Open fN
        Set wB = ActiveWorkbook
        Set wS = wB.Worksheets("集計表")
            With ThisWorkbook.Worksheets("管理表")
                For j = 2 To .Cells(2, Columns.Count).End(xlToLeft).Column
                    .Cells(3, j) = WorksheetFunction.SumIf(wS.Range("C:C"), .Cells(2, j), wS.Range("D:D"))
                Next j
            End With
            '//開いたブック「集計表.xlsm」を保存せずに閉じる//
            Application.DisplayAlerts = False
                wB.Close
            Application.DisplayAlerts = True
            MsgBox "完了"
End Sub

こんなんではどうでしょうか？m(_ _)m

めぐみん_ · Answer

取り敢えず。

Sub try()
　Dim ws1 As Worksheet
　Dim ws2 As Worksheet
　Dim r1 As Range, r2 As Range
　Dim r As Range, cnt As Integer

Set ws1 = Workbooks("集計表.xlsm").Worksheets("集計表")
　Set ws2 = ThisWorkbook.Worksheets("管理表")

With ws1
　　Set r1 = .Range("C2", .Cells(Rows.Count, 3).End(xlUp))
　End With

With ws2
　　Set r2 = .Range("B2", .Cells(2, Columns.Count).End(xlToLeft))
　　For Each r In r2
　　　cnt = WorksheetFunction.SumIf(ws1.Range(r1.Address(0, 1)), "=" & r.Value, ws1.Range(r1.Offset(, 1).Address(0, 1)))
　　　r.Offset(1).Value = cnt
　　Next
　End With

Set r1 = Nothing
　Set r2 = Nothing
　Set ws1 = Nothing
　Set ws2 = Nothing
End Sub

出来上がっては見たものの（多分いけるかな？）、Ｎｏ．６の

＞マクロ実行前は閉じている前提が希望です。

は完全に作り直しですので、検証の出来ない私は退散するしかないですね。

ググって見るとわかりますけど、ブックをとじた状態でデータを出すのがどれだけ大変なのか
色々見つかると思いますよ。
簡単に言えば『閉じている本の中身を読む』のと同じですからね。

ただ最近の物であれば上手くいけばブックに接続をし、データベースもどきでデータを抜き出せるかも
しれませんが、あいにく私にはそれば出来る環境ではないですし。

完成すると良いですね。

tatsu99 · Answer

>説明不足でした。集計表と管理表は別のエクセルになります。
>この形でのマクロは可能でしょうか。。。？
以下の補足をお願いします。
１）集計表のあるブックのブック名（拡張子も含めて記述してください）
２）管理表のあるブックのブック名（拡張子も含めて記述してください）
３）マクロを集計表のあるブックに割り当てるのか、管理表のあるブックのブックに割り当てるかの指定
（マクロを割り当てられたブックの拡張子は.xlsmになります。他方は、xlsm又はxlsxになります）
４）マクロ実行時、両ブックは既に開かれている前提でマクロを実行しますがよろしいでしょうか。

めぐみん_ · Answer

集計表の入荷日が月を跨ごうが年を跨ごうが、管理表の日付が何日から始まろうがそこは問題にはならないのですが、
なにぶんExcelが古い物でブック指定が違っているんですよね。

同一ブックであればまだ良かったのですが。。。

tatsu99 · Answer

No1です。
以下のマクロを標準モジュールへ登録してください。
Option Explicit
Public Sub 総数更新()
    Dim sh1 As Worksheet
    Dim sh2 As Worksheet
    Dim maxrow1 As Long
    Dim maxcol2 As Long
    Dim row1 As Long
    Dim col2 As Long
    Dim diff As Long
    Dim date0 As Date
    Set sh1 = Worksheets("集計表")
    Set sh2 = Worksheets("管理表")
    maxrow1 = sh1.Cells(Rows.Count, "C").End(xlUp).Row   'C列の最大行取得
    maxcol2 = sh2.Cells(2, Columns.Count).End(xlToLeft).Column   '2行目の最終列取得
    date0 = sh2.Cells(2, "B").Value
    If Day(date0) <> 1 Then
        MsgBox ("B2の日付が1日でない")
        Exit Sub
    End If
    '総数クリア
    sh2.Range("B3:B" & maxcol2).Value = ""
    For row1 = 2 To maxrow1
        diff = sh1.Cells(row1, "C").Value - date0
        If diff >= 0 Then
            sh2.Cells(3, 2 + diff).Value = sh2.Cells(3, 2 + diff).Value + sh1.Cells(row1, "D").Value
        End If
    Next
    MsgBox ("更新完了")
End Sub

めぐみん_ · Answer

No.2です。

もしかして『集計表』と『管理表』は別のExcelブックになっていて、月毎に『集計表』ブックを変えたいから？

めぐみん_ · Answer

SUMIF関数
http://www.eurus.dti.ne.jp/~yoneyama/Excel/kansu/sumif.htm

でいけるんじゃないですか？

＞日々入荷予定数が変わるので、日々管理表への総数変更(更新)をしたいため

なら、数式が手っ取り早いですよ。
管理表の日付が変更されたとしても、セルを参照するのですから数式を変える必要はないと思いますし。

まぁ、担当者が『間違えて数式消しちゃうから』と言うなら別ですけど。

tatsu99 · Answer

前提として
１）集計表の入荷日は、同じ月だけであると考えて良いですか。（例では11月）
11月30日と12月1日のような月がまたがるデータはないと考えてよいですか。
２）管理表のB2は、集計表と同じ月の1日（ついたち）が設定されている（今回は11月1日）と考えて良いですか。

日付ごとの集計マクロ

No6です。

NO.7です。

こんばんは！

取り敢えず。

>説明不足でした。

集計表の入荷日が月を跨ごうが年を跨ごうが、管理表の日付が何日から始まろうがそこは問題にはならないのですが、

No1です。

No.2です。

SUMIF関数

前提として

このQ&Aを見た人はこんなQ&Aも見ています

関連するカテゴリからQ&Aを探す

このQ&Aを見た人がよく見るQ&A

デイリーランキングこのカテゴリの人気デイリーQ&Aランキング

マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング