[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『VBA SUM関数と変数を使用して合計する方法』（楓）
お世話になります。
|[D] |[E]
[4] |店舗合計|
[5] |純売上高|総売上高
[6] | |
[7] | |
省略
[36]| |
通常通り関数で記載すると
D6=SUM(D44,D82,D120,D158,AF44,AF82,AF120,AF158・・・)
D7=SUM(D45,D83,D121,D159,AF45,AF83,AF121,AF159・・・)
E6=SUM(E44,E82,E120,E158,AG44,AG82,AG120,AG158・・・)
E7=SUM(E45,E83,E121,E159,AG45,AG83,AG121,AG159・・・)
になり、
ＶＢＡ上で記載する場合は
Cells(6, 4) = WorksheetFunction.Sum(Cells(44, 4), Cells(82, 4), Cells(120, 4), Cells(158, 4), Cells(44, 32), Cells(82, 32), Cells(120, 32), Cells(158, 32)・・・)
になると思うのですが、
合計する数が多く、今後増えたりもするため、変数やFor～nextで行いたいです。
宜しくお願い致します。
＜ 使用 Excel：Excel2007、使用 OS：Windows7 ＞
こんにちわ。 D列とAF列やE列とAG列は同じ行数になるんですか？ 今後増えていくとの事ですが、最終行の取得はD列で行えば他の列も同じになりますか？
下記は単純に足してるだけですけど。
Sub test()
Dim buf(1, 1) As Double
Dim i As Long
For i = 44 To Cells(Rows.Count, "D").End(xlUp).Row Step 38
buf(0, 0) = buf(0, 0) + Cells(i, "D").Value + Cells(i, "AF").Value
buf(1, 0) = buf(1, 0) + Cells(i + 1, "D").Value + Cells(i + 1, "AF").Value
buf(0, 1) = buf(0, 1) + Cells(i, "E").Value + Cells(i, "AG").Value
buf(1, 1) = buf(1, 1) + Cells(i + 1, "E").Value + Cells(i + 1, "AG").Value
Next i
Range("D6:E7").Value = buf
End Sub
（sy） 2016/04/23(土) 15:33
＞D列とAF列やE列とAG列は同じ行数になるんですか？
行数・列幅は全て同じになります。
＞最終行の取得はD列で行えば他の列も同じになりますか？
絶対とは言えませんが、基本的にはそうなるかと思います。
上記は例で省略して書いてましたが、
現在、１９の表があり
列は[B～AA] [AD～BC] [BF～CE] [CH～DG] [DJ～EI]
行は[44～74] [82～112] [120～150] [158～188]
で表ができています。（最後のDJ158～EI188は現在空欄）
列を増やす予定は現在ないですが、絶対にないとも言えません。
上記列の数のうち16項目はSUM関数を使用し、2項目は平均、その他は最初の表から取得したり、実際に合計された数値から計算したりします。
分かりずらく申し訳ありませんが
宜しくお願い致します。
（楓） 2016/04/23(土) 16:25
まだ今一分かりにくいですねぇ。
B列～AA列の26列は別々の式になると言う事ですけど、平均も合計と同じ範囲で求めるのですか？ 合計16列と平均2列だけ計算出来れば、残りは必要ないと言う事で良いんですか？ どの列がどの演算の集計対象かを提示して頂かないと、回答する側もどうする事も出来ません。
44～74行と言う事は31行分が別々の計算対象になるんですか？ なら結果も6行目から36行目まで表示すると言う事ですか？
（sy） 2016/04/23(土) 17:43
まだ要件が良く分かって無いので仮で、B列～Q列までの16列が合計対象として、B6～Q36のセルに合計結果を表示するコードです。
Sub test()
Dim i As Long, j As Long, x As Long, y As Long
Dim Rng(30, 15) As Range
Dim buf(30, 15) As Double
For y = 0 To 30
For x = 0 To 15
Set Rng(y, x) = Cells(y + 44, x + 2)
Next x
Next y
For i = 44 To Cells(Rows.Count, "D").End(xlUp).Row Step 38
For j = 2 To Cells(44, Columns.Count).End(xlToLeft).Column Step 28
For y = 0 To 30
For x = 0 To 15
Set Rng(y, x) = Union(Rng(y, x), Cells(i + y, j + x))
Next x
Next y
Next j
Next i
For y = 0 To 30
For x = 0 To 15
buf(y, x) = WorksheetFunction.Sum(Rng(y, x))
Next x
Next y
Range("B6:Q36").Value = buf
End Sub
（sy） 2016/04/23(土) 18:43
よく理解できてないかも知れませんが... こういうことかな？
Sub test()
Dim rng As Range, i As Long
Set rng = Range("d44,d82,d120,d158")
For i = 1 To 3
Set rng = Union(rng, rng.Offset(, i * 28))
Next
[d6:e7] = "=sum(" & rng.Address(0, 0) & ")"
End Sub
（seiya） 2016/04/23(土) 23:25
＞平均も合計と同じ範囲で求めるのですか？
範囲は全て同じになります。
＞合計16列と平均2列だけ計算出来れば、残りは必要ないと言う事で良いんですか？
はい！他の部分は今回の質問としては不必要です。
合計はD列～K列、N列・O列・Q列・S列～U列・W列・Y列
平均はL列・AA列が対象になっています。
合計する列が不規則になっているため、
表内はD～AA列までは数値ですので一旦D～AA列までそれぞれ合計してから平均や別の部分の表示でも問題ありません。
＞44～74行と言う事は31行分が別々の計算対象になるんですか？
＞なら結果も6行目から36行目まで表示すると言う事ですか？
はい、31行分別々の計算対象となり、それを6～36行目に表示となります。
上記コードありがとうございます。
今からとなり申し訳ありませんが、確認させていただきます。
seiyaさん
ありがとうございます。
一旦確認させていただきます。
（楓） 2016/04/25(月) 15:27
こんばんわ。
私の提示した方法だと、記述も複雑で、飛び飛びのセルに貼付や、集計方法が変わるのに臨機応変に対応できません。 かなり複雑になってしまうので忘れて下さい。 seiyaさんの方法だと、飛び飛びでも集計方法が変わっても簡単な記述で柔軟に対応できるので、この方が良いです。 どうも私はスムーズに最適解を一発で思い浮かばないのですいません。
Sub test2()
Dim i As Long, j As Long
Dim Rng As Range
'合計対象の範囲の一番左上のセルを変数に代入
Set Rng = Range("D44")
'最終行・最終列を取得して38行・28列毎に変数に結合しながら代入
For i = 44 To Range("B" & Rows.Count).End(xlUp).Row Step 38
For j = 4 To Cells(44, Columns.Count).End(xlToLeft).Column Step 28
Set Rng = Union(Rng, Cells(i, j))
Next j
Next i
'合計・平均の結果セルに数式として貼付
Range("D6:L36,N6:N36,Q6:Q36,S6:U36,W6:W36,Y6:Y36,AA6:AA36").Formula = "=SUM(" & Rng.Address(0, 0) & ")"
'平均のセルは置換
Range("L6:L36,AA6:AA36").Replace What:="SUM", Replacement:="AVERAGE", LookAt:=xlPart
End Sub
（sy） 2016/04/25(月) 20:51
最初の内容確認しました。
修正を加えて、D～AA列迄合計で表示させるなどまではできましたが、平均を出す分にはどうしてもプロパティエラーで出す事ができませんでした。（WorksheetFunction.Averageは使った事がなく、置き換えるだけでいけると思ってました）
再提示ありがとうございます。
問題なく希望通りの動作しました！
分かりずらい説明等でご迷惑おかけしましたが、ありがとうございました。
これでまたひとつ進めれそうです。
seiyaさんもありがとうございました！
（楓） 2016/04/26(火) 09:27
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.