Excel VBA質問箱 IV

当質問箱は、有志のボランティア精神のおかげで成り立っています。
問題が解決したら、必ずお礼をしましょうね。
本サイトの基本方針をまとめました。こちら をご一読ください。

投稿種別の選択が必要です。ご注意ください。
迷惑投稿防止のため、URLの入力を制限しています。ご了承ください。


12573 / 13646 ツリー ←次へ | 前へ→

【9820】こんな合計の出し方って・・・ 翡翠 03/12/17(水) 17:48 質問
【9842】Re:こんな合計の出し方って・・・ Jaka 03/12/18(木) 13:59 発言
【9844】Re:こんな合計の出し方って・・・ Kein 03/12/18(木) 14:24 回答
【9847】Re:こんな合計の出し方って・・・ 翡翠 03/12/18(木) 17:05 お礼

【9820】こんな合計の出し方って・・・
質問  翡翠  - 03/12/17(水) 17:48 -

引用なし
パスワード
   ダウンロードしたデータの小計金額と合計金額の値をSUM関数に置き換えたいのですが、うまくできないので、ご教授願います。

                 商品1    商品2
北海道        旭川    5,360     32,130
北海道        札幌    19,190     77,750
---------------------------------------------
北海道計             24,550     109,880
---------------------------------------------
東北        仙台    1,710     33,870
東北        山形    3,580     23,300
東北        秋田    2,270     27,750
---------------------------------------------
東北計             7,560     84,920
=============================================
地区合計(あ)        32,110     194,800
=============================================
デパート       Cデパート    14,050     67,150
デパート       Dデパート    28,980     77,800
---------------------------------------------
デパート計(い)        43,030     144,950
---------------------------------------------
コンビニ       コンビニE    23,240     13,590
コンビニ       コンビニF    16,580     20,400
---------------------------------------------
コンビニ計(う)        39,820     33,990
=============================================
合計(あ+い+う)        114,960     373,740
=============================================

ダウンロードしたデータには小計金額と合計金額が値で既にあるので、EXCEL集計機能では不具合が生じます・・・

2箇所の合計欄には =SUM(C4,C11,C19) のような数式に置き換えるのを目標にしています。

**************************************************************************
Sub TEST()
  
  Dim MaxRow As Long
  Dim i As Long
  Dim j As Integer
  Dim k As Long
 
  MaxRow = Range("A65536").End(xlUp).Row
  
  LastColumn = Range("C4").End(xlToRight).Column
  
  For i = 4 To MaxRow
    If Right(Cells(i, 1).Value, 1) = "計" Then

      For j = 3 To LastColumn
        Cells(i, j).FormulaR1C1 = "=SUM(R[-1]C:R[-" & k & "]C)"
      Next
      
      k = 0
      
    ElseIf Right(Cells(i, 1).Value, 1) = "合計" Then

      For j = 3 To LastColumn
        Cells(i, j).FormulaR1C1 = "=SUM(???)"
      Next
      
      k = 0

    Else
      k = k + 1
    End If

  Next

End Sub
**************************************************************************

"計"の部分はきちんとSUM関数に置き換えられます。条件分岐するつもりで「ElseIf節」を使ってみましたが、上記「???」部分が妙らしく循環エラーになってしまいます。
「???」部分は、=SUM(C4,C11,C19) のような結果を複数列にわたって反映させるR1C1の書き方がわからないので、本投稿にあたり「???」で代替しています。

【9842】Re:こんな合計の出し方って・・・
発言  Jaka  - 03/12/18(木) 13:59 -

引用なし
パスワード
   >R1C1の書き方がわからないので

こう言うのでも調べられますし

pp = Range("C4,C11,C19").Address(, , xlR1C1)
Range("b15").Formula = "=sum(" & pp & ")"

これでも良いと思います。

a1 = 4: a2 = 11: a3 = 19
aaa = "C" & a1 & ",C" & a2 & ",C" & a3
Range("b16").Formula = "=sum(" & aaa & ")"

【9844】Re:こんな合計の出し方って・・・
回答  Kein  - 03/12/18(木) 14:24 -

引用なし
パスワード
   必ずしもご希望どうりというわけではないですが・・

Sub TEST_集計()
  Dim i As Long
  Dim C As Range
  Dim Ad As String
 
  Application.ScreenUpdating = False
  For i = Range("A65536").End(xlUp).Row To 2 Step -1
   With Cells(i, 1)
     Select Case True
      Case .Value Like "*計*"
        Rows(i).Delete xlShiftUp
      Case .Value Like "*-*"
        Rows(i).Delete xlShiftUp
      Case .Value Like "*=*"
        Rows(i).Delete xlShiftUp
     End Select
   End With
  Next i
  Range("A1").CurrentRegion.Subtotal 1, xlSum, Array(3, 4), True
  For Each C In Columns(3).SpecialCells(3, 1)
   Ad = Ad & C.Address(0, 0) & ","
   If C.Offset(, -2).Value Like "東北*" Then
     Ad = Left$(Ad, Len(Ad) - 1)
     C.Offset(1).EntireRow.Insert xlShiftDown
     C.Offset(1).Resize(, 2).Formula = "=SUM(" & Ad & ")"
     C.Offset(1, -2).Value = "地区合計"
     Ad = C.Offset(1).Address(0, 0) & ","
   ElseIf C.Offset(, -2).Value = "総計" Then
     Ad = Left$(Ad, InStrRev(Ad, ",", Len(Ad) - 1) - 1)
     C.Resize(, 2).Formula = "=SUM(" & Ad & ")"
     C.Offset(, -2).Value = "地区・デパート・コンビニ合計"
   End If
  Next
  With Range("A1").CurrentRegion
   .ClearOutline
   .EntireColumn.AutoFit
  End With
  Application.ScreenUpdating = True
End Sub

地区の合計を出すところは、UPされている表のとおり"東北の1行下"という
条件にしています。他の地区名にするなら、Like "東北*" を修正して下さい。
あと、見やすいようにアウトラインはクリアしてありますが、集計の処理が
されていることには変わりがないので、データ全体を消すときは、まず
シート全体のセルを選択し、必ず「データ」「集計」で「すべて削除」のボタン
を押してからクリアして下さい。

【9847】Re:こんな合計の出し方って・・・
お礼  翡翠  - 03/12/18(木) 17:05 -

引用なし
パスワード
   Jaka さん:
Kein さん:

回答、ありがとうございました。

記録マクロに少し手を加えるところから始め、その場しのぎで今までやってきたので
基礎も何もあったものではなく、苦悩することもしばしばです (T-T)
いただいたアドバイス、コードを参考に鋭意研究?に励みたいと思います。

12573 / 13646 ツリー ←次へ | 前へ→
ページ:  ┃  記事番号:
2610219
(SS)C-BOARD v3.8 is Free