Excel (VBA)

Excel VBAに関するフォーラムです。
  • 解決済みのトピックにはコメントできません。
このトピックは解決済みです。
質問

 
(Windows 11 Pro : Excel 2021)
ロジックの考え方
投稿日時: 22/06/22 08:55:04
投稿者: Nubo

シート(W1)
    |[A] | |[C] |[D]
 [1]|Code_2 |単価|個数|金額
 [2]|B002_20210321| 60| 3| 180
 [3]|D012_20210415| 65| 6| 390
 [4]|A001_20220105| 40| 2| 80
 [5]|C005_20210912| 50| 8| 400
 [6]|D012_20210525| 67| 3| 201
 [7]|A001_20220515| 41| 4| 164
 [8]|C005_20220515| 52| 4| 208
 
[b]シート(W2)

    |[A] | |[C]
 [1]|Code|商品名 |合計
 [2]|A001|Pen(LONG) | 244
 [3]|B002|Pen(Short)| 180
 [4]|C005|Note(A4_S)| 608
 [5]|D012|Note(B5_S)| 591
 
[b]やりたい事は、

W2のcode(A列)を検索値として
W1のCode_2(A列)は検索範囲を検索
 
W1の金額(D列)の検索値の合計を
W2の合計(C列)に書き出す
 
大まかなロジックと考えていることは以下です。
    A001をW1のA列で1行ずつ検索(Find関数?)して
    存在すればoffsetで金額を配列に保存
    最終行まで続ける
    最終行まで行けば、そこで配列にキープしている値(金額)を合計して
    W2のA001と同じ行のC列に書き出す(offsetを利用)
 
    同じ処理を続けてB002,C005,D012でも金額を求める
 
大まかすぎて具体的になっていませんが
使用すべき関数を含めてアドバイスあればお願いします。
(ロジックの具体なダメだしなど参考になりそうな事があればウエルカムです。)
 
 
全面的なコードの作成依頼では有りません。
 
マクロ初心者なので後で見直したときに判るように
分かりやすいコードを心がけて作成したいと思っての質問です。ro

回答
投稿日時: 22/06/22 11:28:19
投稿者: sk

引用:
W2のcode(A列)を検索値として
W1のCode_2(A列)は検索範囲を検索
  
W1の金額(D列)の検索値の合計を
W2の合計(C列)に書き出す

本当に上記の通りでよいなら、無理に VBA を使わなくても、
ワークシート[W2]の C 列の各セルに SUMIF 関数を用いた数式を
設定すれば解決する問題でしょう。
 
引用:
A001をW1のA列で1行ずつ検索(Find関数?)して

まず、ここでの文字列比較方式を明記するようにして下さい。
 
サンプルデータから類推した限りでは、例えば次のような
前方一致方式での文字列比較となるはず。
 
(ワークシート[W2]の C2 セルの数式)
------------------------------------------------------------
 
=SUMIF('W1'!$A$2:$A$8,A2&"_*",'W1'!$D$2:$D$8)
 
------------------------------------------------------------
 
C3:C5 にも同様の数式を設定すれば、求めている結果は得られるでしょう。
 
引用:
大まかすぎて具体的になっていませんが
使用すべき関数を含めてアドバイスあればお願いします。

(そもそもマクロを使う必要があるのか、という点は置くとして)
上記と同様の操作をマクロによって実行する形であれば、
少なくともループ処理や配列を使用する必要はありません。

投稿日時: 22/06/22 13:07:38
投稿者: Nubo

アドバイス感謝します。
 
>無理に VBA を使わなくても、
 
ワークシート[W2]の C2 セルに以下の数式を記載して下方にコピペ
=SUMIF('W1'!$A$2:$A$8,A2&"_*",'W1'!$D$2:$D$8)
 
確かにマクロを使わなくてもワークシート関数だけで
合計を出すことは出来ますが
マクロの勉強(ロジックの遂行)を目的としています。
 
そこのところをご理解下さい。
 
>(そもそもマクロを使う必要があるのか、という点は置くとして)
>上記と同様の操作をマクロによって実行する形であれば、
>少なくともループ処理や配列を使用する必要はありません。
 
A001の一行だけなら下記で処理できます。
でもループ無しでは他のB002等が処理できないと思いますが ?
(ループで処理した場合のコードも追加しました。)
 
Sub test()
 
Dim ws1 As Worksheet, ws2 As Worksheet
 
Set ws1 = Sheets("W1")
Set ws2 = Sheets("W2")
 
ws2.Range("C2") = WorksheetFunction.SumIf(ws1.Range("a2:a8"), ws2.Range("A2") & "_*", ws1.Range("d2:d8"))
 
End Sub
'--------------------------------
ループで処理した場合
 
Sub test2()
 
Dim ws1 As Worksheet, ws2 As Worksheet
Dim i As Long
Dim LRows1 As Long, LRows2 As Long
 
Set ws1 = Sheets("W1")
Set ws2 = Sheets("W2")
 
LRows1 = ws2.Cells(Rows.Count, 1).End(xlUp).Row
LRows2 = ws1.Cells(Rows.Count, 1).End(xlUp).Row
 
For i = 2 To LRows1
    ws2.Cells(i, "C") = WorksheetFunction.SumIf(Range(ws1.Cells(2, "A"), ws1.Cells(LRows2, "A")), ws2.Cells(i, "A") & "_*", Range(ws1.Cells(2, "D"), ws1.Cells(LRows2, "D")))
     
Next
End Sub
 
 

回答
投稿日時: 22/06/22 13:50:53
投稿者: sk

引用:
A001の一行だけなら下記で処理できます。
でもループ無しでは他のB002等が処理できないと思いますが ?

範囲内の全てのセルの数式をまとめて設定すれば可能です。
 
(標準モジュール)
------------------------------------------------------------
Sub Test1()
 
    Dim wsSource As Worksheet
     
    '参照元シートの参照
    Set wsSource = ThisWorkbook.Worksheets("W1")
     
    '参照元シートのメンバーの参照、操作
    With wsSource
         
        Dim lngSourceFirstRow As Long
        Dim lngSourceLastRow As Long
         
        '最初のデータ行の行番号を取得
        lngSourceFirstRow = 2
        '最後のデータ行の行番号を取得
        lngSourceLastRow = .Cells(.Rows.Count, 1).End(xlUp).Row
         
        'データ行がなければ終了
        If lngSourceLastRow < lngSourceFirstRow Then
            Set wsSource = Nothing
            Exit Sub
        End If
     
        Dim rngEvaluatedRange As Range
        Dim rngSumRange As Range
         
        '検索条件の評価対象となるセル範囲の参照
        Set rngEvaluatedRange = .Range(.Cells(lngSourceFirstRow, "A"), _
                                       .Cells(lngSourceLastRow, "A"))
         
        '金額の合計範囲となるセル範囲の参照
        Set rngSumRange = .Range(.Cells(lngSourceFirstRow, "D"), _
                                 .Cells(lngSourceLastRow, "D"))
     
    End With
     
    Dim wsDestination As Worksheet
     
    Set wsDestination = ThisWorkbook.Worksheets("W2")
 
    '出力先シートのメンバーの参照、操作
    With wsDestination
         
        Dim lngDestinationFirstRow As Long
        Dim lngDestinationLastRow As Long
         
        '最初のデータ行の行番号を取得
        lngDestinationFirstRow = 2
        '最後のデータ行の行番号を取得
        lngDestinationLastRow = .Cells(.Rows.Count, 1).End(xlUp).Row
         
        'データ行がなければ終了
        If lngDestinationLastRow < lngDestinationFirstRow Then
            Set rngSumRange = Nothing
            Set rngEvaluatedRange = Nothing
            Set wsDestination = Nothing
            Set wsSource = Nothing
            Exit Sub
        End If
         
        Dim rngResultRange As Range
         
        '集計結果の出力先となるセル範囲の参照
        Set rngResultRange = .Range(.Cells(lngDestinationFirstRow, "C"), _
                                    .Cells(lngDestinationLastRow, "C"))
         
    End With
     
    'ここから SUMIF 関数を用いた数式を生成する処理
     
    Dim strEvaluatedRange As String
    Dim strCriteria As String
    Dim strSumRange As String
    Dim strFormula As String
     
    '第1引数に渡すのは、評価範囲のセル番地(絶対参照)
    With rngEvaluatedRange
        strEvaluatedRange = "'" & .Worksheet.Name & "'!" & _
                            .Address(RowAbsolute:=True, _
                                     ColumnAbsolute:=True)
    End With
     
    '第2引数に渡すのは、「『A 列の値と "_" を連結した文字列』と
    '前方一致する」という検索条件(相対参照)
    strCriteria = "A" & lngDestinationFirstRow & "&""_*"""
     
    '第3引数に渡すのは、合計範囲のセル番地(絶対参照)
    With rngSumRange
        strSumRange = "'" & .Worksheet.Name & "'!" & _
                      .Address(RowAbsolute:=True, _
                               ColumnAbsolute:=True)
    End With
 
    '全ての引数を組み込んだ SUMIF 関数を用いた数式を生成
    strFormula = "=SUMIF(" & strEvaluatedRange & "," & _
                             strCriteria & "," & _
                             strSumRange & ")"
     
    '生成した数式をイミディエイトウィンドウに出力する(デバッグ用)
    Debug.Print strFormula
        
    Application.ScreenUpdating = False
     
    '出力先セル範囲のメンバーの操作
    With rngResultRange
        '生成した数式を全てのセルにセットする
        .Formula = strFormula
        '数式の計算結果をそのまま代入し、定数セルに変換する
        .Value = .Value
    End With
 
    Application.ScreenUpdating = True
 
    Set rngResultRange = Nothing
    Set rngSumRange = Nothing
    Set rngEvaluatedRange = Nothing
    Set wsDestination = Nothing
    Set wsSource = Nothing
 
End Sub
------------------------------------------------------------
 
引用:
ループで処理した場合のコードも追加しました。

[W2]のデータ行の件数がそれほど多くなければ、
そういう方式でもよいと思います。

投稿日時: 22/06/22 17:10:10
投稿者: Nubo

skさん、ループ無しでのコード有難うございます。
 
ループしなくても記載されたような方法も有るんですね。
「ループ無し」の事例としてkeepさせていただきます。
 
>[W2]のデータ行の件数がそれほど多くなければ、
>そういう方式でもよいと思います。
 
ループで処理する場合、処理速度が遅くなるので
このコメントが付いたとの理解でよろしいでしょうか ?

回答
投稿日時: 22/06/22 18:21:48
投稿者: sk

引用:
ループで処理する場合、処理速度が遅くなるので
このコメントが付いたとの理解でよろしいでしょうか ?

ループする回数が極端に多ければ(各セルへのアクセスが
頻繁に発生すれば)、そうなる可能性は高くなるでしょうけど、
 
引用:
マクロの勉強(ロジックの遂行)を目的としています。

ということなのであれば、今のところあまり気にすることでもないかと。
 
処理の目的や内容によっては、数式やワークシート関数で解決するのが困難で、
別の検索手段やループ処理、動的配列などを用いた方が適しているような
ケースもあるでしょうし。
 
引用:
マクロ初心者なので後で見直したときに判るように
分かりやすいコードを心がけて作成したいと思っての質問です。

あとはまあ、「処理効率の良さ」と「可読性の高さ」が両立するか、
そういうコーディングを実践できるか否かの問題。
(名前の付け方やコメントの書き方などをひっくるめての話なら
『リーダブルコード』でも読まれた方がよいと思いますが)

投稿日時: 22/06/23 04:54:11
投稿者: Nubo

skさん、アドバイス感謝します。
 
中々、マクロ初心者から抜け出せていませんが、頑張ります。
 
 
『リーダブルコード』の事初めて知りました。
少し勉強してみます。