[[20130726143343]]　『小計・合計の間に中計を挿入』（ねむ）　ページの最後に飛ぶ

『小計・合計の間に中計を挿入』（ねむ）

[[20130628101456]]

 上記で質問した者です。
 月毎の売上予測データを、いくつかの条件を指定して抽出し、リストを作成しています。

 作成されるリストは下記のようなレイアウトです

 　　　　A　　　　B　　　　　　　　　　　　 H　　　　 I　　　　J　　　　K　　　　L　　　　　　　M　　　　N　　　　　O　　　　　P　　　 ・・・
 1　　7月度売上予定表
 2　　自社集計（A～B列結合）
 3 　　出荷日　　納品日　　 ・・・・・　　受注数　　出荷数　　区分　　担当　　　分類　　　　　定価　　　経費　　　掛率　　　売上額　　 ・・・
 4　 2013/7/2　2013/7/3　　 ・・・・・　　　13　　　　13　　　製品　　田中　　製品-AA　　　1,000,000　　　　　　　22.00%　　200,000　  ・・・
 5　 2013/7/12 2013/7/13　　・・・・・　　　10　　　　10　　　製品　　鈴木　　製品-AA　　　　250,000　　15,000　　20.00%　　 53,000　  ・・・
 　　～～～
 11　2013/7/25 2013/7/26　　・・・・・　　　 5　　　　 5　　　製品　　鈴木　　製品-AA　　　1,500,000　　　　　　　25.50%　　385,500　　・・・
 12　　製品-AA小計（A～B列結合）　　　　　　60　　　　60　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　1,352,500　　・・・
 13　2013/7/3　2013/7/3　　 ・・・・・　　　25　　　　25　　　製品　　田中　　製品-BB　　　　　　　　　　　　　　　　　　　 620,000　　・・・
 14　2013/7/10 2013/7/11　　・・・・・　　　 3　　　　 3　　　製品　　田中　　製品-BB　　　　300,000　　 6,000　　21.00%　　 64,260　　・・・
 15　　製品-BB小計（同）　　　　　　　　　　47　　　　47　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　800,000　　・・・
 　　～～～
 30　　製品-DD小計（同)　　　　　　　　　　150　　　 150　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　2,500,000　　・・・
 31　2013/7/2　2013/7/3　　 ・・・・・　　　15　　　　15　　　商品　　鈴木　　商品-AA　　　1,300,200　　 7,200　　22.00%　　287,628　　・・・
 32　2013/7/12 2013/7/16　　・・・・・　　　12　　　　12　　　商品　　田中　　商品-AA　　　　540,000　　12,000　　21.20%　　117,024　　・・・
 　　～～～
 40　　商品-AA小計（同）　　　　　　　　　 110　　　 110　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　1,900,000　　・・・
 　（中略）
 50　　商品-ZZ小計（同）　　　　　　　　　　50　　　　50　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　300,000　　・・・
 51　自社合計（A～B列結合）　　　　　　　　550　　　 550　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　8,970,000　　・・・
 52　OEM集計（A～B列結合）
 53　2013/7/25 2013/7/26　　　　　　　　　　80　　　　80　　　製品　　OEM　　OEM-製品-AA　 　　　　　　　　　　　　　　　12,520,500　　・・・
 54　2013/7/26 2013/7/29　　　　　　　　　　11　　　　11　　　製品　　OEM　　OEM-製品-AA　 　　　　　　　　　　　　　　　　 850,000　　・・・
 55　　OEM-製品-AA小計（A～B列結合）　　　　91　　　　91　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　 13,370,500　　・・・
 56　2013/7/18 2013/7/19　　　　　　　　　　 8　　　　 8　　　製品　　OEM　　OEM-製品-PP　　　　　　　　　　　　　　　　　1,120,000　　・・・
 57　　OEM-製品-PP小計（A～B列結合）　　　　 8　　　　 8　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　1,120,000　　・・・
 58　2013/7/16 2013/7/17　　　　　　　　　　14　　　　14　　　商品　　OEM　　OEM-商品-LL　　　　　　　　　　　　　　　　　　250,000　　・・・
 59　2013/7/22 2013/7/23　　　　　　　　　　13　　　　13　　　商品　　OEM　　OEM-商品-LL　　　　　　　　　　　　　　　　　　120,000　　・・・
 60　　OEM-製品-PP小計（A～B列結合）　　　　27　　　　27　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　370,000　　・・・
 61　OEM合計（A～B列結合） 　　　　　　　　126　　　 126　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　 14,860,500　　・・・
 62　　総合計（A～C列結合）　　　　　　　　676　　　 676　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　 23,830,500　　・・・

 上の方に「自社」の売上リストがあり、「製品」と「商品」に分かれています。
 「製品」・「商品」の中にも「製品○○」「製品××」といった小分類があります。
 必ず「製品」→「商品」の順に並ぶようになっています。

 下の方は「OEM」というものの売上リストで、「製品」「商品」に分かれているのは同じですが
 L列の「分類」欄に「OEM-xxx」と、先頭に「OEM」がつくようになっています。
 （「担当」も「OEM」だけです）

 「自社」「OEM」それぞれの分類ごとに小計を出し、そこから「自社の製品・商品の合計」
 「OEMの製品・商品の合計」を出します。
 そして最後に「自社」と「OEM」の全てのデータを足したものを「総合計」として出します。

 今回やりたいのは、「自社」「OEM」それぞれ、「製品の合計」と「商品の合計」を途中に挿入したいです。

 上記の例だと、30行目の次に「自社」の「製品」の受注数、出荷数、売上額の小計行を挿入し、
 50行目の次（「自社合計」の上）に「自社」の商品」の小計行を挿入したいのですが
 （下のOEMの項目も同様）
 どのようにして挿入すればよいかで行き詰っています。

 「製品○○」「商品○○」の最後が何か、というのはその月によって違います。

 前の質問にも書きましたが、今の集計用のデータは下記のようになっています
 （「自社」と「OEM」とで分かれています。「自社」だけを抽出することもあるため）

 ★自社の集計

 Private Sub 集計()
    Dim GYO1 As Long
    Dim GYO2 As Long
    Dim GYO As Long
    Dim strFORMULA As String
    Dim kei As Variant
    Dim z As Long

    ' 4行目から処理開始(見出しなし)
    GYO = 4

    Application.ScreenUpdating = False

    Do While lsh.Cells(GYO, 1).Value <> ""
        ' 小計ｸﾞﾙｰﾌﾟの先頭行→GYO1
        GYO1 = GYO
        GYO = GYO + 1
        ' 次の行から同じｸﾞﾙｰﾌﾟでない行を見つける
        Do While lsh.Cells(GYO, 12).Value = lsh.Cells(GYO1, 12).Value
            GYO = GYO + 1
        Loop
        ' 同じｸﾞﾙｰﾌﾟの最終行→GYO2
        GYO2 = GYO - 1

        ' 小計行を挿入
        Rows(GYO).Insert
        lsh.Range("A" & GYO & ":B" & GYO).Merge
        lsh.Cells(GYO, 1).Value = lsh.Cells(GYO - 1, 12).Text & "小計"
        lsh.Cells(GYO, 1).Font.Bold = True
        lsh.Cells(GYO, 1).HorizontalAlignment = xlRight

        '受注数合計
         lsh.Cells(GYO, 8).Formula = _
        Application.ConvertFormula("=SUBTOTAL(9,R" & GYO1 & "C8:R" & GYO2 & "C8)", xlR1C1, xlA1, 4)

        lsh.Cells(GYO, 8).Font.Bold = True
        '出荷数合計
         lsh.Cells(GYO, 9).Formula = _
        Application.ConvertFormula("=SUBTOTAL(9,R" & GYO1 & "C9:R" & GYO2 & "C9)", xlR1C1, xlA1, 4)

        lsh.Cells(GYO, 9).Font.Bold = True
        '売上額合計
         lsh.Cells(GYO, 16).Formula = _
        Application.ConvertFormula("=SUBTOTAL(9,R" & GYO1 & "C16:R" & GYO2 & "C16)", xlR1C1, xlA1, 4)

        lsh.Cells(GYO, 16).Font.Bold = True

        GYO = GYO + 1
    Loop
    ' 総合計

        z = lsh.Range("C" & lsh.Rows.Count).End(xlUp).Row

        lsh.Range("A" & GYO & ":B" & GYO).Merge

        lsh.Cells(GYO, 1).Value = "自社合計"
        lsh.Cells(GYO, 1).Font.Bold = True

        lsh.Cells(GYO, 8).Formula = "=SUMIF(A4:A" & GYO - 1 & ",""*小計"", H4:H" & GYO - 1 & ")"
        lsh.Cells(GYO, 8).Font.Bold = True

        lsh.Cells(GYO, 9).Formula = "=SUMIF(A4:A" & GYO - 1 & ",""*小計"", I4:I" & GYO - 1 & ")"
        lsh.Cells(GYO, 9).Font.Bold = True

        lsh.Cells(GYO, 16).Formula = "=SUMIF(A4:A" & GYO - 1 & ",""*小計"", P4:P" & GYO - 1 & ")"
        lsh.Cells(GYO, 16).Font.Bold = True

     Application.ScreenUpdating = True

 End Sub

 ★OEMの集計

 Private Sub 集計_OEM()
    Dim GYO1 As Long
    Dim GYO2 As Long
    Dim GYO As Long
    Dim strFORMULA As String
    Dim kei As Variant
    Dim z As Long
    Dim i As Long

　　'OEM行の先頭

    GYO = TakSr

    Application.ScreenUpdating = False

    z = lsh.Range("C" & lsh.Rows.Count).End(xlUp).Row

    Do While lsh.Cells(GYO, 1).Value <> ""
        ' 小計ｸﾞﾙｰﾌﾟの先頭行→GYO1
        GYO1 = GYO
        GYO = GYO + 1
        ' 次の行から同じｸﾞﾙｰﾌﾟでない行を見つける
        Do While lsh.Cells(GYO, 12).Value = lsh.Cells(GYO1, 12).Value
            GYO = GYO + 1
        Loop
        ' 同じｸﾞﾙｰﾌﾟの最終行→GYO2
        GYO2 = GYO - 1

        ' 小計行を挿入
        Rows(GYO).Insert
        lsh.Range("A" & GYO & ":B" & GYO).Merge

            lsh.Cells(GYO, 1).Value = lsh.Cells(GYO - 1, 12).Text & "小計"
            lsh.Cells(GYO, 1).Font.Bold = True
            lsh.Cells(GYO, 1).HorizontalAlignment = xlRight

            lsh.Cells(GYO, 8).Formula = _
            Application.ConvertFormula("=SUBTOTAL(9,R" & GYO1 & "C8:R" & GYO2 & "C8)", xlR1C1, xlA1, 4)

            lsh.Cells(GYO, 8).Font.Bold = True

            lsh.Cells(GYO, 9).Formula = _
            Application.ConvertFormula("=SUBTOTAL(9,R" & GYO1 & "C9:R" & GYO2 & "C9)", xlR1C1, xlA1, 4)

            lsh.Cells(GYO, 9).Font.Bold = True
            lsh.Cells(GYO, 16).Formula = _
            Application.ConvertFormula("=SUBTOTAL(9,R" & GYO1 & "C16:R" & GYO2 & "C16)", xlR1C1, xlA1, 4)

            lsh.Cells(GYO, 16).Font.Bold = True

            GYO = GYO + 1

    Loop
    ' 総合計

        z = lsh.Range("C" & lsh.Rows.Count).End(xlUp).Row

        lsh.Range("A" & GYO & ":B" & GYO).Merge

        lsh.Cells(GYO, 1).Value = "OEM合計"
        lsh.Cells(GYO, 1).Font.Bold = True

        lsh.Cells(GYO, 8).Formula = "=SUMIF(A" & TakSr & ":A" & GYO - 1 & ",""*小計"", H" & TakSr & ":H" & GYO - 1 & ")"
        lsh.Cells(GYO, 8).Font.Bold = True

        lsh.Cells(GYO, 9).Formula = "=SUMIF(A" & TakSr & ":A" & GYO - 1 & ",""*小計"", I" & TakSr & ":I" & GYO - 1 & ")"
        lsh.Cells(GYO, 9).Font.Bold = True

        lsh.Cells(GYO, 16).Formula = "=SUMIF(A" & TakSr & ":A" & GYO - 1 & ",""*小計"", P" & TakSr & ":P" & GYO - 1 & ")"
        lsh.Cells(GYO, 16).Font.Bold = True

        lsh.Cells(GYO + 1, 1).Value = "総合計"
        lsh.Cells(GYO + 1, 1).Font.Bold = True
        lsh.Cells(GYO + 1, 1).Font.Size = 14
        lsh.Range("A" & GYO + 1 & ":C" & GYO + 1).Merge

        lsh.Cells(GYO + 1, 8).Formula = "=SUMIF(A4:A" & GYO & ",""*合計"", H4:H" & GYO & ")"
        lsh.Cells(GYO + 1, 8).Font.Bold = True
        lsh.Cells(GYO + 1, 8).Font.Size = 14

        lsh.Cells(GYO + 1, 9).Formula = "=SUMIF(A4:A" & GYO & ",""*合計"", I4:I" & GYO & ")"
        lsh.Cells(GYO + 1, 9).Font.Bold = True
        lsh.Cells(GYO + 1, 8).Font.Size = 14

        lsh.Cells(GYO + 1, 16).Formula = "=SUMIF(A4:A" & GYO & ",""*合計"", P4:P" & GYO & ")"
        lsh.Cells(GYO + 1, 16).Font.Bold = True
        lsh.Cells(GYO + 1, 16).Font.Size = 14

     Application.ScreenUpdating = True

 End Sub

 「自社」だけの時は「総合計」は必要ないのですが、「OEM」が入る時は「総合計」がいるので、
 「集計_OEM」プロシージャで総合計を計算しています。

 （モジュールレベルで宣言している変数もありますのでぬけがあると思います）

 ごちゃごちゃしててすみません、「自社」「OEM」それぞれの「製品」「商品」の集計行を挿入するにはどうしたらいいでしょうか？
 不足部分は補いますのでよろしくお願いします。

 Excel2010です。

 考えてみたので、検討してもらって良いですか？

 まず、集計行を挿入する場所ですが
 現在のコードの中で候補としてあげられるのは
 小計行を挿入した後の行ですよね。

 ですから、小計の式を入れた後で、
 　J列の　上のデータと下のデータを比べて
 　一致してない時に　集計行を挿入する
 ってのは、どうですか？

 A～B列結合 の所に　何と入れるかが問題ですが
 ○○小 計 の様に間にスペースを一つ入れておくと
       ~
 今回入れる集計行のSUMIFにも、総合計時のSUMIFにも
 引っかからずに済むのではないかと思います。

 式を一つで済ませようと思ったら
 =SUMIF(A4:A10,"*小計",H4:H10)-SUMIF(A4:A10,"*小 計",H4:H10)
 って感じにして、下側の集計に上側の集計が混ざらない様にできるんじゃないかと思います。

 もう一つ変数を増やして
 ＞'OEM行の先頭 
 ＞GYO = TakSr
 の様に、きっちりやっても良いと思いますが。
 （きっちり出来ているのか　載せて下さっているコードからだけじゃわからないですが）

 前スレに関連の事ですが、SUBTOTALの式も列が変わるわけじゃないんで
 こちらも A1 方式で書く様にされてはどうですか？

 それから、行の挿入や削除を行うのであれば「$」がついていても　連動して移動しますよね？
 それ以外の操作があるのかもしれませんが。

 絶対参照と相対参照を使い分けると
 式を一度に入力も出来る様になると思います。

 '------
Sub 三つの列に()
    Dim StrR As Long, LstR As Long, MyR As Long

    StrR = 4: LstR = 9: MyR = 10

    Range("H" & MyR & ",I" & MyR & ",P" & MyR).Formula = _
        "=SUMIF($A" & StrR & ":$A" & LstR & ",""*小計"",H" & StrR & ":H" & LstR & ")"
End Sub
 '------

 (HANA)

 (HANA)様ありがとうございます。

 ＞ 　J列の　上のデータと下のデータを比べて
 ＞ 　一致してない時に　集計行を挿入する

 なるほど、その発想はありませんでした！

 ＞ こちらも A1 方式で書く様にされてはどうですか？

 すみません、私もA1方式の方がわかりやすいのですが、参考にしたサイトのをそのまま流用したらA1方式に変換する方法がわからなくて…

 ＞ それから、行の挿入や削除を行うのであれば「$」がついていても　連動して移動しますよね？

 えええええ！そうだったんですか！今更知りました…（今やってみてびっくりしました）

 こんな基本的なことも知らなかった自分が恥ずかしいです…（；；）

 VBAやる前に勉強することがたくさんありそうです…

 （ねむ）

 ＞私もA1方式の方がわかりやすいのですが、参考にしたサイトのをそのまま流用したら
 じゃあ、数式の方から考えてみますか？
 Cells(GYO, 8). ってのは、8列目なのでH列ですよね。
 どのブックでも良いので H列のどこかのセルに入っている　SUBTOTALの式を一つ
 こちらに載せてみてもらえますか？

 (HANA)

 (HANA)様たびたびありがとうございます

 H列
 =SUBTOTAL(9,H16:H19)

 このように入っています。

 絶対参照の時は
 =SUBTOTAL(9,$H$18:$H$22)

 このようになっていました

 （ねむ）

 まず、
 ＞=SUBTOTAL(9,H16:H19)
 でしたら、 GYO1=１６　GYO2=１９　の時の式だって事になりますが
 セルに書かれた式をみて、それぞれの変数がいくつだったか　わかりますか？

 それから、
 セルに式を入れる所がいくつかありますが
 R1C1形式で書かれている所と、A1形式で書かれている所を見比べてみて下さい。
        lsh.Cells(GYO, 8).FormulaR1C1 = "=SUBTOTAL(9,R" & GYO1 & "C8:R" & GYO2 & "C8)"
        lsh.Cells(GYO + 1, 8).Formula = "=SUMIF(A4:A" & GYO & ",""*合計"", H4:H" & GYO & ")"

 左側は「R1C1」ってのが　ついているのと　ついていないのの違いがあります。
 右側は、どちらも数式が""でかこって書かれています。
 　また、R1C1の方は R16C8:R19C8 って感じでセルが指定してあって
                   "=SUBTOTAL(9,R16C8:R19C8)"
         ついてない方は、GYO=１９だった場合、A4:A19 って感じでセルが指定してあります。
                   "=SUMIF(A4:A19,""*合計"", H4:H19)"
 各変数の値を仮に決めたら、コードを見て　セルに入る数式がわかりますか？

 そして、
 セルに　数式を文字として表示させる場合を考えてみて下さい。
 A1セルに16　A2セルに19 が入っていて　A3セルに =SUBTOTAL(9,H16:H19) と表示させたければ
 A3セルには
="=SUBTOTAL(9,H"&A1&":H"&A2&")"
 の式を入れますね？

 VBAでもセルに式を入れる時は基本的には同じ感じで
 A1→GYO1　A2→GYO2 にそれぞれ値が入っているので
 lsh.Cells(GYO + 1, 8).Formula = "=SUBTOTAL(9,H" & GYO1 & ":H" & GYO2 & ")"
 こんな感じになります。                           ~~A1~~        ~~A2~~

 絶対参照にしたいときは、絶対参照にしたい位置に「$」を追加して下さい。

 どうでしょう。変更できそうですか？

 (HANA)

 (HANA)様ありがとうございます。

 丁寧に説明していただいて助かります。

 一つずつ置き換えながら考えたら何とかできそうです。

 今までR1C1方式自体をあまり使ったことがないので、A1方式と同じように数式を入れたら絶対参照になったりして分からないことばかりでした…

 自分で理解しながらコードを書いてみます。
 また分からなかったら質問するかもしれませんがよろしくお願いしますm(__)m

 （ねむ）

コメント返信：

[ 一覧(最新更新順) ]

[[20130726143343]] 『小計・合計の間に中計を挿入』（ねむ） ページの最後に飛ぶ

[[20130726143343]]　『小計・合計の間に中計を挿入』（ねむ）　ページの最後に飛ぶ