商品ごと、年ごとの販売額合計をピボットテーブルのように出力する(その1)

この講座は有料講座です。
講座の購入後ご覧になれます。

ログインアカウントの新規作成

解説

商品ごと、年ごとの販売額合計をピボットテーブルのように出力します。
まずは、商品、年が行タイトルになって、1列で合計額を出力する形式。以下の要領で出力します。

商品A, 2013年, xx,xxx円
商品A, 2014年, xx,xxx円
商品A, 2015年, xx,xxx円
商品A, 2016年, xx,xxx円
商品B, 2013年, xx,xxx円
商品B, 2014年, xx,xxx円
商品B, 2015年, xx,xxx円
商品B, 2016年, xx,xxx円
商品C, 2013年, xx,xxx円
商品C, 2014年, xx,xxx円
商品C, 2015年, xx,xxx円
商品C, 2016年, xx,xxx円

やり方は何通りか考えられますが、基礎編レベルの知識で解ける方法をひとつ紹介します。
既出の「商品ごとの合計額の計算」ができるなら、難しくないです。
ただし、注意点がふたつあります。詳しくは動画で。

この教材についての過去の質問・感想

11792 : 小川慶一の回答 (2020-02-07 23:46:01)

morimotoさん:

しっかり手を動かしてくださいね。
この順序でスラスラ実装していけるようになるまでです。

> 小川塾長:
> さっそく返答いただきありがとうございます。6つに細分化までしていただき工程を追いかけやすく助かります。まだざっと目を通したところですがこれから精読していきます。sum範囲と考えず、商品がかわる行をみつけると考えればできそうな気がしました。九九のごとく何度もガチガチととF8でやってみてつかみたいと思います。


11789 : morimotoさんのコメント (2020-02-06 23:19:35)

小川塾長:
さっそく返答いただきありがとうございます。6つに細分化までしていただき工程を追いかけやすく助かります。まだざっと目を通したところですがこれから精読していきます。sum範囲と考えず、商品がかわる行をみつけると考えればできそうな気がしました。九九のごとく何度もガチガチととF8でやってみてつかみたいと思います。


11787 : 小川慶一の回答 (2020-02-06 09:19:31)

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


11786 : 小川慶一の回答 (2020-02-06 09:16:47)

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


11785 : morimotoさんのコメント (2020-02-06 00:33:13)

続き:コードを記載します。よろしくお願いします。
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


11784 : morimotoさんのコメント (2020-02-06 00:25:19)

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

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

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


10643 : ガラパゴスタディー平田の回答 (2018-10-10 15:43:32)

受講生 さん:
いつもご利用いただき有難うございます。
また、この度はご不便をおかけし申し訳ございません。

特定の動画で音声が聞き取りにくいという事でしたが、
原因としては、該当動画がモノラル設定になっているということが考えられます。
再生される際にヘッドフォンもしくはスピーカーの片側からのみ再生され
音が小さく聞こえている可能性がございます。

調査を行いますので、宜しければどの動画が音が小さくなるのか、教えて頂けませんでしょうか。
お手数をおかけいたしますが、よろしくお願いいたします。

> 動画再生時、音量が小さくて聞き取りにくい状態にあります。
> 「よくある質問の回答」に記載されていることを実行しましたが、解決しませんでした。
> 特定の動画で、このような現象が起こります。
>


10634 : 受講生さんのコメント (2018-10-10 03:53:15)

動画再生時、音量が小さくて聞き取りにくい状態にあります。
「よくある質問の回答」に記載されていることを実行しましたが、解決しませんでした。
特定の動画で、このような現象が起こります。


10185 : 小川慶一の回答 (2018-07-01 10:45:26)

わかやまさん:

コメント投稿ありがとうございます。
なるほど、よさそうですね。


10176 : わかやまさんのコメント (2018-06-29 16:09:13)

小川様

いつもありがとうございます。
特に深く考えたわけではなかったのですが、以下のマクロができました。

Sub kotae1()

Columns("A:F").Select
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("E2:E231") _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("B2:B231") _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Sheet1").Sort
.SetRange Range("A1:F231")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
'ここから自装したマクロ
Dim moto
Dim saki
Dim syoukei
saki = 3
For moto = 2 To 231
syoukei = syoukei + Workbooks("コピーpivot_type1.xlsm").Worksheets("Sheet1").Range("F" & moto).Value
If Workbooks("コピーpivot_type1.xlsm").Worksheets("Sheet1").Range("B" & moto).Value <> Workbooks("コピーpivot_type1.xlsm").Worksheets("Sheet1").Range("B" & moto + 1).Value Then
Workbooks("コピーpivot_type1.xlsm").Worksheets("Sheet1").Range("H" & saki).Value = Workbooks("コピーpivot_type1.xlsm").Worksheets("Sheet1").Range("E" & moto).Value
Workbooks("コピーpivot_type1.xlsm").Worksheets("Sheet1").Range("I" & saki).Value = Workbooks("コピーpivot_type1.xlsm").Worksheets("Sheet1").Range("B" & moto).Value
Workbooks("コピーpivot_type1.xlsm").Worksheets("Sheet1").Range("J" & saki).Value = syoukei
saki = saki + 1
syoukei = 0
End If
Next

ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("A2:A231") _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Sheet1").Sort
.SetRange Range("A1:F231")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With



End Sub

はじめとおわりの操作がないので、すごくシンプルだと思います。
これでもよいでしょうか?


9260 : 小川慶一の回答 (2017-12-22 06:59:30)

三橋さん:

おはようございます。

> 小川さんのマクロの方が圧倒的にスピードが速いですね。
> 私のマクロは時間がかかる。遅い。

お、そうですね。よく気づかれましたね。
メモリに格納した値を参照するのとセルを見つけてそこにある値を拾ってくるのとでは、コストが違います。

> この課題にはだいぶ時間がかかってしまいましたが、納得できました。

こういう経験を通じて、コンピュータの動きにだんだん詳しくなっていくものです。
長い目で見れば、必要な時間だったかとも思います。

ひきつづきお楽しみください☆


9256 : 三橋さんのコメント (2017-12-21 07:43:10)

小川様

汎用性と書いたのはいろんな事例に使える「型」なのかどうか、
ということを伺いたかったのですが、
何度か試してみてわかりました。
小川さんのマクロの方が圧倒的にスピードが速いですね。
私のマクロは時間がかかる。遅い。
違いがよくわかりました。

この課題にはだいぶ時間がかかってしまいましたが、
納得できました。
ありがとうございました。


9253 : 小川慶一の回答 (2017-12-20 09:44:30)

三橋さん:

おもしろいご質問、ありがとうございます。

汎用性という意味では、三橋さんの回答例のものでも十分と思います。

> ②shohinなど分かりやすい変数を使っているため、理解しやすい。


ここは一長一短です。
変数に何が入っているのか?をロジックを追ってさらっと理解できない人には、かえって読んでいて負担ですね。
一方、それが理解できる場合は、都度セルの値を見に行くよりも高速で処理できるという利点があります。

僕も、「変数に値を入れてそれと比較」だと解説がしんどい(もっと重要な他のことの理解に受講生の脳内リソースを集中させて欲しい)ときには、前者で行くことがあります。

もしまだ以下の記事を読まれたことがないようでしたら、読んでみて、それからまた思ったことをお知らせいただけますか。

[質問] 同じ機能を実現するマクロの書き方が複数ある場合、どの書き方が良いか分からなくて迷います。
http://www.exvba.com/blog/?p=4378


僕としては、複数の書き方を状況に応じて使い分けられるようになれば良いかなと思います。

以下の件も、たとえば、テストのときには有効だったりします。あるいは、マクロを知らない人にデモとして見せるときなどは。
https://online.pc5bai.com/Movie/index/27/150/?id=c9251


9252 : 三橋さんのコメント (2017-12-20 09:01:57)

小川様

お世話になります。
この課題に対して自分なりにマクロを書いてみたのですが(ソート後のマクロ部分)、一応正しいアウトプットは得られたものの、どの程度汎用性があるのか不安です。このマクロの課題を指摘していただけないでしょうか?

①小川さんのマクロの方がすっきりしている。
②shohinなど分かりやすい変数を使っているため、理解しやすい。
③今後マクロを学んでいく上で、小川さんのマクロ(の考え方)の方がたぶん都合がいい(だろう)。
ぐらいは何となくわかるのですが…。
まだマクロに対して根本的な理解が浅いためよろしくお願いいたします。

下記ががそのマクロとなります。

Sub mondai3()

Dim cHida As Long
Dim cMigi As Long
Dim cLast As Long
Dim cGokei As Long

cLast = Range("A" & Rows.Count).End(xlUp).Row
cMigi = 3

For cHida = 2 To cLast
If Range("E" & cHida).Value <> Range("E" & cHida - 1).Value Then
Range("H" & cMigi).Value = Range("E" & cHida).Value
Range("I" & cMigi).Value = Range("B" & cHida).Value
Range("J" & cMigi).Value = 0
cMigi = cMigi + 1

ElseIf Range("B" & cHida).Value <> Range("B" & cHida - 1).Value Then
Range("H" & cMigi).Value = Range("E" & cHida).Value
Range("I" & cMigi).Value = Range("B" & cHida).Value
Range("J" & cMigi).Value = 0
Debug.Print cGokei
cMigi = cMigi + 1
End If
cGokei = Range("J" & cMigi - 1).Value + Range("F" & cHida).Value
Range("J" & cMigi - 1) = cGokei
Debug.Print cGokei
Next
Range("H" & cMigi).Value = Range("E" & cHida).Value
Range("I" & cMigi).Value = Range("B" & cHida).Value
End Sub



9234 : 小川慶一の回答 (2017-12-16 20:43:58)

受講生 さん:

VBAに習熟すればするほど、表側の機能を必要としなくなりますね。
ひきつづきよろしくお願いいたします。


9233 : 受講生さんのコメント (2017-12-16 17:59:11)

動画の解説に従えば、2003以前でも4項目以上の並べ替えが可能であることが分りました。また、ピボットテーブル(SUMIF関数)を自力で実装する手順が分りました。


3日がかりのその仕事、3分で終わらせる方法教えます。ガラパゴスタディーオンライン講座 ユーザー登録

本講座の動画一覧

  1. 【動画1】 住所情報を都道府県から市区町村レベルで細かく分割する
    【動画1】 住所情報を都道府県から市区町村レベルで細かく分割する 未習得
  2. 【動画2】 保険料額表から標準報酬額ごとの保険料負担額を算出する
    【動画2】 保険料額表から標準報酬額ごとの保険料負担額を算出する 未習得
  3. 【動画3】 セル内の文字列から区切り文字を見つけ、その出現回数+1回だけデータ転記する
    【動画3】 セル内の文字列から区切り文字を見つけ、その出現回数+1回だけデータ転記する 未習得
  4. 【動画4】 セル内の文字列から区切り文字を見つけ、その区切り文字の間にある文字列を切り出す
    【動画4】 セル内の文字列から区切り文字を見つけ、その区切り文字の間にある文字列を切り出す 未習得
  5. 【動画5】 セル内の文字列から区切り文字を見つけ、その区切り文字の間にある文字列を反映しつつデータ転記する
    【動画5】 セル内の文字列から区切り文字を見つけ、その区切り文字の間にある文字列を反映しつつデータ転記する 未習得
  6. 【動画6】 VLookUp関数、Index関数、Match関数より便利なマクロ-該当する行が見つからない場合の処理をアレンジ
    【動画6】 VLookUp関数、Index関数、Match関数より便利なマクロ-該当する行が見つからない場合の処理をアレンジ 未習得
  7. 【動画7】 VLookUp関数、Index関数、Match関数より便利なマクロ-複数シート間、複数ファイル間でのマッチング
    【動画7】 VLookUp関数、Index関数、Match関数より便利なマクロ-複数シート間、複数ファイル間でのマッチング 未習得
  8. 【動画8】 転記先シートを生成してデータを流し込む。
    【動画8】 転記先シートを生成してデータを流し込む。 未習得
  9. 【動画9】 転記先ファイルを生成してデータを流し込む。
    【動画9】 転記先ファイルを生成してデータを流し込む。 未習得
  10. 【動画10】 条件に一致するデータだけのリストを、条件に一致しないデータを削除する方法で作成する。
    【動画10】 条件に一致するデータだけのリストを、条件に一致しないデータを削除する方法で作成する。 未習得
  11. 【動画11】 条件に一致するデータだけのリストを、条件に一致しないデータを削除する方法で同一ファイル内に連続的に作成する。
    【動画11】 条件に一致するデータだけのリストを、条件に一致しないデータを削除する方法で同一ファイル内に連続的に作成する。 未習得
  12. 【動画12】 条件に一致するデータだけのリストを、条件に一致しないデータを削除する方法で複数の別ファイルとして連続的に作成する。
    【動画12】 条件に一致するデータだけのリストを、条件に一致しないデータを削除する方法で複数の別ファイルとして連続的に作成する。 未習得
  13. 【動画13】 ファイル配布→配布先でのデータ記入→記入済データを手元資料に統合
    【動画13】 ファイル配布→配布先でのデータ記入→記入済データを手元資料に統合 未習得
  14. 【動画14】 データ記入用ファイルを配布する-その1
    【動画14】 データ記入用ファイルを配布する-その1 未習得
  15. 【動画15】 データ記入用ファイルを配布する-その2
    【動画15】 データ記入用ファイルを配布する-その2 未習得
  16. 【動画16】 各部署からファイルを回収し、記入済データを手元資料に統合その1
    【動画16】 各部署からファイルを回収し、記入済データを手元資料に統合その1 未習得
  17. 【動画17】 各部署からファイルを回収し、記入済データを手元資料に統合その2
    【動画17】 各部署からファイルを回収し、記入済データを手元資料に統合その2 未習得
  18. 【動画18】 商品ごと、年ごとの販売額合計をピボットテーブルのように出力する(その1)
    【動画18】 商品ごと、年ごとの販売額合計をピボットテーブルのように出力する(その1) 未習得
  19. 【動画19】 商品ごと、年ごとの販売額合計をピボットテーブルのように出力する(その2-1)
    【動画19】 商品ごと、年ごとの販売額合計をピボットテーブルのように出力する(その2-1) 未習得
  20. 【動画20】 商品ごと、年ごとの販売額合計をピボットテーブルのように出力する(その2-2)
    【動画20】 商品ごと、年ごとの販売額合計をピボットテーブルのように出力する(その2-2) 未習得

塾長 小川慶一

メニュー

コメント紹介

もっと見る

ページの先頭へ