レッスン-投稿一覧パソコン仕事５倍塾ポータル

投稿/コメントを表示します。

morimotoさんの投稿

(投稿ID: 4625) 添付ファイルのダウンロード権限がありません

小川塾長：
現在発展編1学習中です。この講義で研究していることがありまして、
商品ごと→年ごとに並び替えをし、H列に商品ごと、年ごと、合計額、
件数を出力し、さらに商品ごとの合計額と件数をsum関数を使って出力しする。そして1行開けて次の商品についても同様に
商品ごとの合計額と件数をsum関数を使って出力したい。

非常に難しかったのが、sum関数の範囲を指定するところでした。
範囲の始点をsrow, 終点をerowとして
srow→商品の出力の初めだからsrow=tate
erow→商品が変わり出力される手前だからerow=tate-1
,,,だろうとF8の押下を何度もトライしました。
特にsrow=tateと書く位置、erow=tate-1と書く位置を決めるところが
なかなかすぐに定まらず、F8を頼りに何度も何度も書く位置を探し出し
ようやくできた感じです。

そこでお伺いしたいのが、このようなsum関数を使うため範囲を指定するときに、何かコツのようなものがないものか？
あるいは、まったく別の方法があるのか、アドレスいただきたく
よろしくお願いします。仕事でこのような資料を作成する場面があり
この機会に何か習得できたらいいなと思います。

2020/02/06 09:25

morimotoさんのコメント

(コメントID: 6402)

続き：コードを記載します。よろしくお願いします。
Sub kenkyu()
'20200102研究
'keyを使っての方針
'課題→商品ごとにSUM関数で合計を出せるか '20200205

Worksheets("練習Sheet1").Activate

'(1)----商品→年ごとに並び替えする
Dim mgyo As Integer
mgyo = Range("a" & Rows.Count).End(xlUp).Row
'(1)----商品→年ごとに並び替えする
Range("a" & 2 & ":" & "f" & mgyo).Sort _
key1:=Range("e2"), order1:=xlAscending, _
key2:=Range("b2"), order2:=xlAscending, _
Header:=xlYes

'(2)-----商品リスト、年を出力

Dim gyo As Integer
Dim key As String '商品&年のkey
Dim key1 As String '商品key
Dim tate As Integer '出力最初のの最初位置
Dim cnt As Integer '件数
Dim shohin As String
Dim toshi As String
Dim goukei As Long
Dim srow As Integer 'sum関数の初値
Dim erow As Integer 'sum関数の終値

tate = 3
cnt = 0
key = ""
key1 = ""

srow = tate '-----ここの位置

For gyo = 2 To mgyo

shohin = Range("e" & gyo).Value
toshi = Range("b" & gyo).Value
goukei = Range("F" & gyo).Value

If key <> shohin & toshi Then
key = shohin & toshi
cnt = 1

If key1 <> shohin Then
key1 = shohin

If tate > 3 Then '1発目(tate=3)のときは転記位置がおかしいから *1　　　　　　　　　　　'へ飛んで転記
'#合計記載
erow = tate - 1 '-----ここの位置

Range("H" & tate).Value = Range("H" & erow).Value & "の合計"
Range("J" & tate).Value = "=sum(J" & srow & ": J" & erow & ")"
Range("K" & tate).Value = "=sum(K" & srow & ": K" & erow & ")"
Range("H" & erow & ":" & "k" & erow).Borders. _
(xlEdgeBottom).LineStyle = xlContinuous
tate = tate + 2
srow = tate '-----ここの位置
End If
End If

'↓*1
Range("H" & tate).Value = shohin
Range("I" & tate).Value = toshi
Range("K" & tate).Value = cnt
Range("J" & tate).Value = goukei

tate = tate + 1

Else
'keyに変更がない場合(shohin&toshiが不変のとき）
cnt = cnt + 1
Range("K" & tate - 1).Value = cnt

goukei = Range("J" & tate - 1).Value
goukei = goukei + Range("F" & gyo).Value
Range("J" & tate - 1).Value = goukei

End If

Next gyo

erow = tate - 1 '-----ここの位置

Range("H" & tate).Value = Range("H" & erow).Value & "の合計"
Range("J" & tate).Value = "=sum(J" & srow & ": J" & erow & ")"
Range("K" & tate).Value = "=sum(K" & srow & ": K" & erow & ")"
Range("H" & erow & ":" & "k" & erow).Borders. _
(xlEdgeBottom).LineStyle = xlContinuous

tate = tate + 2
srow = tate

End Sub

2020/02/06 09:33

小川慶一さんのコメント

(コメントID: 6403)

morimotoさん:

morimotoさん:

> そこでお伺いしたいのが、このようなsum関数を使うため範囲を指定するときに、何かコツのようなものがないものか？
> あるいは、まったく別の方法があるのか、アドレスいただきたく

「sum関数を使う」というより抽象的に言うと、「途中集計の範囲を決める」ですね。
その範囲が決まれば、基礎編レベルの合計の計算を実装するか？sum関数を使うか？は自由です。

「途中集計の範囲を決める」ことに困難を感じているならば、そこだけに集中していったんマクロを書き上げることです。

ということで、順を追って実装してみました。
こんな感じ↓ですかね。

ShohinYearStartEnd1 はご理解いただけるものと思います。理解できなければ or 理解できても自力で実装できなければ、基礎編演習不足ですので基礎編演習を徹底してやり直してください。

構造的には「伝票作成マクロ」のアレンジですが、morimotoさんの現在のスキルを考慮して、罫線を引く部分以外は基礎編でお渡しした道具だけで実装しています。

Option Explicit

Sub Shokika()
    Dim cMax As Long
    cMax = Range("H" & Rows.Count).End(xlUp).Row
    If cMax > 2 Then
        Range("H3:M" & cMax).Clear
    End If
End Sub
'商品ごと、年ごとに開始行、終了行だけを調べる
Sub ShohinYearStartEnd1()
    Shokika
    
    '並べ替えを実施
    Range("A1").CurrentRegion.Sort _
        key1:=Range("E1"), order1:=xlAscending, _
        key2:=Range("B1"), order2:=xlAscending, _
        Header:=xlYes
    
    '開始行、終了行を調べる
    Dim cYear As Long
    Dim cBgn As Long 'その商品の最初の行
    Dim cMax As Long
    Dim cFm As Long
    Dim cTo As Long
    cTo = 2
    cMax = Range("A" & Rows.Count).End(xlUp).Row
    For cFm = 2 To cMax
        If Range("B" & cFm).Value <> cYear Then
            If cFm > 2 Then
                Range("H" & cTo).Value = Range("E" & cFm - 1).Value
                Range("I" & cTo).Value = cYear
                Range("J" & cTo).Value = cBgn
                Range("K" & cTo).Value = cFm - 1
            End If
            cYear = Range("B" & cFm).Value
            cBgn = cFm
            cTo = cTo + 1
        End If
    Next
    
    Range("H" & cTo).Value = Range("E" & cFm - 1).Value
    Range("I" & cTo).Value = cYear
    Range("J" & cTo).Value = cBgn
    Range("K" & cTo).Value = cFm
End Sub

'商品が変わったら2行空ける
Sub ShohinYearStartEnd2()
    Shokika
    
    Range("A1").CurrentRegion.Sort _
        key1:=Range("E1"), order1:=xlAscending, _
        key2:=Range("B1"), order2:=xlAscending, _
        Header:=xlYes
    
    Dim cYear As Long
    Dim cBgn As Long
    Dim cMax As Long
    Dim cFm As Long
    Dim cTo As Long
    cTo = 2
    cMax = Range("A" & Rows.Count).End(xlUp).Row
    For cFm = 2 To cMax
        If Range("B" & cFm).Value <> cYear Then
            If cFm > 2 Then
                Range("H" & cTo).Value = Range("E" & cFm - 1).Value
                Range("I" & cTo).Value = cYear
                Range("J" & cTo).Value = cBgn
                Range("K" & cTo).Value = cFm - 1
                '以下の3行を追加
                If Range("E" & cFm - 1).Value <> Range("E" & cFm).Value Then
                    cTo = cTo + 2
                End If
            End If
            cYear = Range("B" & cFm).Value
            cBgn = cFm
            cTo = cTo + 1
        End If
    Next
    Range("H" & cTo).Value = Range("E" & cFm - 1).Value
    Range("I" & cTo).Value = cYear
    Range("J" & cTo).Value = cBgn
    Range("K" & cTo).Value = cFm
End Sub

'集計行を挿入
Sub ShohinYearStartEnd3()
    Shokika
    
    Range("A1").CurrentRegion.Sort _
        key1:=Range("E1"), order1:=xlAscending, _
        key2:=Range("B1"), order2:=xlAscending, _
        Header:=xlYes
    
    Dim cYear As Long
    Dim cBgn As Long
    Dim cMax As Long
    Dim cFm As Long
    Dim cTo As Long
    cTo = 2
    cMax = Range("A" & Rows.Count).End(xlUp).Row
    For cFm = 2 To cMax
        If Range("B" & cFm).Value <> cYear Then
            If cFm > 2 Then
                Range("H" & cTo).Value = Range("E" & cFm - 1).Value
                Range("I" & cTo).Value = cYear
                Range("J" & cTo).Value = cBgn
                Range("K" & cTo).Value = cFm - 1
                If Range("E" & cFm - 1).Value <> Range("E" & cFm).Value Then
                    cTo = cTo + 1
                    Range("H" & cTo).Value = Range("E" & cFm - 1).Value & "の合計" '追加
                    cTo = cTo + 1
                End If
            End If
            cYear = Range("B" & cFm).Value
            cBgn = cFm
            cTo = cTo + 1
        End If
    Next
    Range("H" & cTo).Value = Range("E" & cFm - 1).Value
    Range("I" & cTo).Value = cYear
    Range("J" & cTo).Value = cBgn
    Range("K" & cTo).Value = cFm - 1
    If Range("E" & cFm - 1).Value <> Range("E" & cFm).Value Then
        cTo = cTo + 1
        Range("H" & cTo).Value = Range("E" & cFm - 1).Value & "の合計" '追加
        cTo = cTo + 1
    End If
End Sub

'商品ごと、年ごとの合計を計算
Sub ShohinYearStartEnd4()
    Shokika
    
    Range("A1").CurrentRegion.Sort _
        key1:=Range("E1"), order1:=xlAscending, _
        key2:=Range("B1"), order2:=xlAscending, _
        Header:=xlYes
    
    Dim cYear As Long
    Dim cBgn As Long
    Dim cMax As Long
    Dim cFm As Long
    Dim cTo As Long
    Dim cGokei As Long '追加
    Dim cCnt As Long '追加
    cTo = 2
    cMax = Range("A" & Rows.Count).End(xlUp).Row
    For cFm = 2 To cMax
        If Range("B" & cFm).Value <> cYear Then
            If cFm > 2 Then
                Range("H" & cTo).Value = Range("E" & cFm - 1).Value
                Range("I" & cTo).Value = cYear
                Range("J" & cTo).Value = cBgn
                Range("K" & cTo).Value = cFm - 1
                cGokei = 0
                '↓追加から
                For cCnt = cBgn To cFm - 1
                    cGokei = cGokei + Range("F" & cCnt).Value
                Next
                Range("L" & cTo).Value = cGokei
                Range("M" & cTo).Value = cFm - cBgn
                '↑追加ここまで
                If Range("E" & cFm - 1).Value <> Range("E" & cFm).Value Then
                    cTo = cTo + 1
                    Range("H" & cTo).Value = Range("E" & cFm - 1).Value & "の合計"
                    cTo = cTo + 1
                End If
            End If
            cYear = Range("B" & cFm).Value
            cBgn = cFm
            cTo = cTo + 1
        End If
    Next
    Range("H" & cTo).Value = Range("E" & cFm - 1).Value
    Range("I" & cTo).Value = cYear
    Range("J" & cTo).Value = cBgn
    Range("K" & cTo).Value = cFm - 1
    cGokei = 0
    '↓追加から
    For cCnt = cBgn To cFm - 1
        cGokei = cGokei + Range("F" & cCnt).Value
    Next
    Range("L" & cTo).Value = cGokei
    Range("M" & cTo).Value = cFm - cBgn
    '↑追加ここまで
    If Range("E" & cFm - 1).Value <> Range("E" & cFm).Value Then
        cTo = cTo + 1
        Range("H" & cTo).Value = Range("E" & cFm - 1).Value & "の合計"
        cTo = cTo + 1
    End If
End Sub

'その商品だけのサマリー
Sub ShohinYearStartEnd5()
    Shokika
    
    Range("A1").CurrentRegion.Sort _
        key1:=Range("E1"), order1:=xlAscending, _
        key2:=Range("B1"), order2:=xlAscending, _
        Header:=xlYes
    
    Dim cYear As Long
    Dim cBgn As Long
    Dim cMax As Long
    Dim cFm As Long
    Dim cTo As Long
    Dim cGokei As Long
    Dim cSokei As Long '追加 総計
    Dim cSoken As Long '追加 総件数
    Dim cShoBgn As Long '追加 当該商品出力開始行
    Dim cCnt As Long
    cTo = 2
    cShoBgn = 3 '追加
    cMax = Range("A" & Rows.Count).End(xlUp).Row
    For cFm = 2 To cMax
        If Range("B" & cFm).Value <> cYear Then
            If cFm > 2 Then
                Range("H" & cTo).Value = Range("E" & cFm - 1).Value
                Range("I" & cTo).Value = cYear
                Range("J" & cTo).Value = cBgn
                Range("K" & cTo).Value = cFm - 1
                cGokei = 0
                For cCnt = cBgn To cFm - 1
                    cGokei = cGokei + Range("F" & cCnt).Value
                Next
                Range("L" & cTo).Value = cGokei
                Range("M" & cTo).Value = cFm - cBgn
                If Range("E" & cFm - 1).Value <> Range("E" & cFm).Value Then
                    cTo = cTo + 1
                    Range("H" & cTo).Value = Range("E" & cFm - 1).Value & "の合計"
                    cSokei = 0
                    cSoken = 0
                    '↓追加ここから
                    For cCnt = cShoBgn To cTo - 1
                        cSokei = cSokei + Range("L" & cCnt).Value
                        cSoken = cSoken + Range("M" & cCnt).Value
                    Next
                     Range("L" & cTo).Value = cSokei
                     Range("M" & cTo).Value = cSoken
                    '↑追加ここまで
                    cTo = cTo + 1
                    cShoBgn = cTo '追加
                End If
            End If
            cYear = Range("B" & cFm).Value
            cBgn = cFm
            cTo = cTo + 1
        End If
    Next
    Range("H" & cTo).Value = Range("E" & cFm - 1).Value
    Range("I" & cTo).Value = cYear
    Range("J" & cTo).Value = cBgn
    Range("K" & cTo).Value = cFm - 1
    cGokei = 0
    For cCnt = cBgn To cFm - 1
        cGokei = cGokei + Range("F" & cCnt).Value
    Next
    Range("L" & cTo).Value = cGokei
    Range("M" & cTo).Value = cFm - cBgn
    If Range("E" & cFm - 1).Value <> Range("E" & cFm).Value Then
        cTo = cTo + 1
        Range("H" & cTo).Value = Range("E" & cFm - 1).Value & "の合計"
        cSokei = 0
        cSoken = 0
        '↓追加ここから
        For cCnt = cShoBgn To cTo - 1
            cSokei = cSokei + Range("L" & cCnt).Value
            cSoken = cSoken + Range("M" & cCnt).Value
        Next
         Range("L" & cTo).Value = cSokei
         Range("M" & cTo).Value = cSoken
        '↑追加ここまで
        cTo = cTo + 1
        cShoBgn = cTo '追加
    End If
End Sub

'罫線 & J列K列は解説の便宜のために出力していたがそもそも出力不要なので省略
Sub ShohinYearStartEnd6()
    Shokika
    
    Range("A1").CurrentRegion.Sort _
        key1:=Range("E1"), order1:=xlAscending, _
        key2:=Range("B1"), order2:=xlAscending, _
        Header:=xlYes
    
    Dim cYear As Long
    Dim cBgn As Long
    Dim cMax As Long
    Dim cFm As Long
    Dim cTo As Long
    Dim cGokei As Long
    Dim cSokei As Long
    Dim cSoken As Long
    Dim cShoBgn As Long
    Dim cCnt As Long
    cTo = 2
    cShoBgn = 3
    cMax = Range("A" & Rows.Count).End(xlUp).Row
    For cFm = 2 To cMax
        If Range("B" & cFm).Value <> cYear Then
            If cFm > 2 Then
                Range("H" & cTo).Value = Range("E" & cFm - 1).Value
                Range("I" & cTo).Value = cYear
'                Range("J" & cTo).Value = cBgn
'                Range("K" & cTo).Value = cFm - 1
                cGokei = 0
                For cCnt = cBgn To cFm - 1
                    cGokei = cGokei + Range("F" & cCnt).Value
                Next
                Range("L" & cTo).Value = cGokei
                Range("M" & cTo).Value = cFm - cBgn
                If Range("E" & cFm - 1).Value <> Range("E" & cFm).Value Then
                    cTo = cTo + 1
                    Range("H" & cTo & ":M" & cTo).Borders(xlEdgeTop).LineStyle = xlContinuous '追加
                    Range("H" & cTo).Value = Range("E" & cFm - 1).Value & "の合計"
                    cSokei = 0
                    cSoken = 0
                    For cCnt = cShoBgn To cTo - 1
                        cSokei = cSokei + Range("L" & cCnt).Value
                        cSoken = cSoken + Range("M" & cCnt).Value
                    Next
                     Range("L" & cTo).Value = cSokei
                     Range("M" & cTo).Value = cSoken
                    cTo = cTo + 1
                    cShoBgn = cTo
                End If
            End If
            cYear = Range("B" & cFm).Value
            cBgn = cFm
            cTo = cTo + 1
        End If
    Next
    Range("H" & cTo).Value = Range("E" & cFm - 1).Value
    Range("I" & cTo).Value = cYear
'    Range("J" & cTo).Value = cBgn
'    Range("K" & cTo).Value = cFm - 1
    cGokei = 0
    For cCnt = cBgn To cFm - 1
        cGokei = cGokei + Range("F" & cCnt).Value
    Next
    Range("L" & cTo).Value = cGokei
    Range("M" & cTo).Value = cFm - cBgn
    If Range("E" & cFm - 1).Value <> Range("E" & cFm).Value Then
        cTo = cTo + 1
        Range("H" & cTo & ":M" & cTo).Borders(xlEdgeTop).LineStyle = xlContinuous '追加
        Range("H" & cTo).Value = Range("E" & cFm - 1).Value & "の合計"
        cSokei = 0
        cSoken = 0
        For cCnt = cShoBgn To cTo - 1
            cSokei = cSokei + Range("L" & cCnt).Value
            cSoken = cSoken + Range("M" & cCnt).Value
        Next
         Range("L" & cTo).Value = cSokei
         Range("M" & cTo).Value = cSoken
        cTo = cTo + 1
        cShoBgn = cTo
    End If
End Sub

> 小川塾長：
> 現在発展編1学習中です。この講義で研究していることがありまして、
> 商品ごと→年ごとに並び替えをし、H列に商品ごと、年ごと、合計額、
> 件数を出力し、さらに商品ごとの合計額と件数をsum関数を使って出力しする。そして1行開けて次の商品についても同様に
> 商品ごとの合計額と件数をsum関数を使って出力したい。
>
> 非常に難しかったのが、sum関数の範囲を指定するところでした。
> 範囲の始点をsrow, 終点をerowとして
> srow→商品の出力の初めだからsrow=tate
> erow→商品が変わり出力される手前だからerow=tate-1
> ,,,だろうとF8の押下を何度もトライしました。
> 特にsrow=tateと書く位置、erow=tate-1と書く位置を決めるところが
> なかなかすぐに定まらず、F8を頼りに何度も何度も書く位置を探し出し
> ようやくできた感じです。
>
> そこでお伺いしたいのが、このようなsum関数を使うため範囲を指定するときに、何かコツのようなものがないものか？
> あるいは、まったく別の方法があるのか、アドレスいただきたく
> よろしくお願いします。仕事でこのような資料を作成する場面があり
> この機会に何か習得できたらいいなと思います。
>

2020/02/06 18:16

小川慶一さんのコメント

(コメントID: 6404)

morimotoさん:

当初いただいたマクロの添削も示します。
以下でコメントを参照してください。

Option Explicit

Sub kenkyu_rewrite()
    '[*]sub ... end sub の中身は一段右へ
    
    Worksheets("練習Sheet1").Activate
    
    '(1)----商品→年ごとに並び替えする
    Dim mgyo As Integer
    mgyo = Range("a" & Rows.Count).End(xlUp).Row '[*]よけいなインデントを入れない
    Range("a" & 2 & ":" & "f" & mgyo).Sort _
         key1:=Range("e2"), order1:=xlAscending, _
         key2:=Range("b2"), order2:=xlAscending, _
         Header:=xlYes
    '[*]並べ替えはよく書けています
    
    '(2)-----商品リスト、年を出力 [*]こういうコメントのつけかたは良いです。やろうとしていることが分かりやすい。
    '[*]以下の変数へのコメントつけも秀逸
    Dim gyo As Integer
    Dim key As String   '商品&年のkey
    Dim key1 As String  '商品key
    Dim tate As Integer '出力最初のの最初位置
    Dim cnt As Integer  '件数
    Dim shohin As String
    Dim toshi As String
    Dim goukei As Long
    Dim srow As Integer 'sum関数の初値
    Dim erow As Integer 'sum関数の終値
    
    tate = 3
    cnt = 0
    key = ""
    key1 = ""
    
    srow = tate '-----ここの位置
    
    For gyo = 2 To mgyo
        '[*]for ... nextの中身は一段右へ。構造の中は常にひとつ右です。
        '[*]空白を2行入れない。「違う話になる」と示すのであれば、空白を入れるのではなくコメントを1行で記述する
        shohin = Range("e" & gyo).Value
        toshi = Range("b" & gyo).Value
        goukei = Range("F" & gyo).Value
        
        If key <> shohin & toshi Then
            key = shohin & toshi
            cnt = 1
            
            If key1 <> shohin Then
               key1 = shohin
                   If tate > 3 Then   '1発目(tate=3)のときは転記位置がおかしいから *1へ飛んで転記
                      '#合計記載
                      erow = tate - 1 '-----ここの位置
            
                      Range("H" & tate).Value = Range("H" & erow).Value & "の合計"
                      Range("J" & tate).Value = "=sum(J" & srow & ": J" & erow & ")"
                      Range("K" & tate).Value = "=sum(K" & srow & ": K" & erow & ")"
                      Range("H" & erow & ":" & "k" & erow).Borders(xlEdgeBottom).LineStyle = xlContinuous
                      
                      tate = tate + 2
                      srow = tate  '-----ここの位置
                   End If
             End If
               
            '↓*1
            Range("H" & tate).Value = shohin
            Range("I" & tate).Value = toshi
            Range("K" & tate).Value = cnt
            Range("J" & tate).Value = goukei
            
            tate = tate + 1
        Else
            'keyに変更がない場合
            cnt = cnt + 1
            Range("K" & tate - 1).Value = cnt
           
            goukei = Range("J" & tate - 1).Value
            goukei = goukei + Range("f" & gyo).Value
            Range("j" & tate - 1).Value = goukei
        End If
    Next gyo
   
     erow = tate - 1  '-----ここの位置
    
     Range("H" & tate).Value = Range("H" & erow).Value & "の合計"
     Range("J" & tate).Value = "=sum(J" & srow & ": J" & erow & ")"
     Range("K" & tate).Value = "=sum(K" & srow & ": K" & erow & ")"
     Range("H" & erow & ":" & "k" & erow).Borders(xlEdgeBottom).LineStyle = xlContinuous
          
     tate = tate + 2
     srow = tate
End Sub

2020/02/06 18:19