NO.2280663で質問した者です。
http://oshiete1.goo.ne.jp/kotaeru.php3?q=2280663
2.のsassakunさんの回答で思っていた通りの結果が得られました。
この例では、データ列は、B列だけですが、
これを発展させ、複数列(M列まで)ある表にそれぞれの列で
マクロで空白行にコード毎の計と最後に合計を入れる方法を教えてください。
配列変数の使い方がよくわかりません。
No.7ベストアンサー
- 回答日時:
#06です。
大サービスでヒントをさし上げますからがんばって下さい。マクロの勉強をするつもりがないなら、この先も「既製服に体を合わせる」しかないでしょうw。数値をカンマ編集するには
対象.NumberFormatLocal = "#,##0"
を追加すればよいです。なお、それだけだと以前集計が入ったセルの書式が残りますから、それがいやなら
.ClearContents
は
.Clear
にしたほうがよいでしょう
集計セルの位置を変える方法は小計挿入、合計挿入でそれぞれ1回ずつ使用しているFOR分の意味を考えるとすぐに分かると思います。
このマクロはあるセルを基準として別のセルを指定するOffsetを多用していますが、その意味が分かれば難しいアルゴリズムではありませんよ。がんばって読んでくださいね。
No.6
- 回答日時:
#01です。
補足ありがとうございました。たぶんこのマクロでいけるでしょう。次回ご質問されるときは、曖昧さがないように最初から具体的な例を提示してくださいね。お互いに二度手間、三度手間になってしまいますから…
Option Explicit
Sub Macro2()
Dim Target, Rng, OfRng, StRng As Range, Adr As String
Dim LastC, LastR, idxC, idxR As Long
On Error Resume Next
Application.ScreenUpdating = False
'初期化、最終セル位置確認
LastR = Range("a65536").End(xlUp).Row + 1
LastC = Range("IV1").End(xlToLeft).Column
Set Target = Nothing
Set Target = ActiveSheet.Cells.SpecialCells(xlCellTypeFormulas, 23)
If Target.Cells.Count > 0 Then
Target.ClearContents
End If
For idxR = 2 To LastR
If Cells(idxR, 1) = "計" Or Cells(idxR, 1) = "合計" Then
Cells(idxR, 1).ClearContents
End If
Next idxR
LastR = Range("a65536").End(xlUp).Row + 1
'小計挿入
For idxR = LastR To 2 Step -1
Set Rng = Cells(idxR, 1)
With Rng
.Value = "計"
Set OfRng = .Offset(-1, 0)
Set StRng = OfRng.End(xlUp)
For idxC = 2 To LastC
If OfRng.Offset(-1, 0) <> "" Then
Adr = Range(StRng.Offset(0, idxC - 1), _
OfRng.Offset(0, idxC - 1)).Address
Rng.Offset(0, idxC - 1).Formula = "=subtotal(9," & Adr & ")"
Else
Rng.Offset(0, idxC - 1).Formula = "=subtotal(9," & _
OfRng.Offset(0, idxC - 1).Address & ")"
End If
Next idxC
idxR = .End(xlUp).Row
End With
Next idxR
'合計挿入
LastR = Range("A65536").End(xlUp).Row + 1
Cells(LastR, 1) = "合計"
For idxC = 2 To LastC
Cells(LastR, idxC).Formula = "=subtotal(9," & Cells(2, idxC).Address & ":" _
& Cells(LastR - 1, idxC).Address & ")"
Next idxC
Application.ScreenUpdating = True
End Sub
返事がおそくなりました。丁寧なご回答ありがとうございました。うまくできました!!
ここで要望が3点あります。
1.例では、2桁の数字を入れていました。これを千円単位の数字に置き換えて実行しますと、空白セルの下の計と合計欄がカンマ区切りになりません。計、合計すべてカンマ区切りにする。
2.例では、コード番号がA列でした。A,B列に他の項目が入り、コード番号がC列、データがD列からJ列までのデータにする。
3.注釈を「小計挿入」のように付けていただいていますが、他の行でも、もう少し詳しい注釈をつけて下さい。
No.5
- 回答日時:
質問です。
なぜ前回の質問は閉じてしまったのでしょうか?
提供されたコードに配列は使われていませんが、なぜ唐突に「配列変数の使い方がよくわかりません。」という発言が出てくるのでしょうか?
提供されたコードの使用に際しては、以下ご注意を。
・変数は型を宣言してから使う事
・Excel2007以降誰も使わなくなる事がわかっているようなコーディングをしない事
なお、「複数列(M列まで)ある表にそれぞれの列でマクロで空白行にコード毎の計と最後に合計を入れる」事がなぜ難しいのか?(大した違いはない筈なのに)
もしかすると、提供されたコードを全く理解しないまま使おうとされているのではないか?
という疑問にも、よろしければお答え下さい。
ご回答ありがとうございます。
具体例は、NO.1の補足をご参照下さい。
前回の質問では、1列の計ができたので、解決したと思い、質問を閉じました。
その後、複数列の疑問が生じ新たに質問し直した訳です。
複数列の場合は、配列変数を使うのかなと思ったからです。
No.4
- 回答日時:
前回書いた、Wendy02です。
前回の件で、そのまま締めてしまったようですが、前回のマクロで、SpecialCellsで、空白とデータを分けて、その空白セルに Subtotal関数を入れるように設計しました。しかし、実際には、SpecialCellsで、空白が取れないデータは、もともと、本来の空白がなかったとしか思えません。
私も他の方も書いたように、「集計」を使うほうが楽だと思います。それを記録マクロにしてもよいのではないでしょうか?
なぜ、マクロでなければならないのか理由が分かりませんし、また、データの空白値やデータの並び自体に信頼がおけないデータの場合は、最初、空白セルを追い出して、ベタの生データから、データ分類をしたほうが安全のようです。
私は、変数に加算方式で足し算するような方法は、極力さけるのは、大量になると、それはものすごく遅いことになるからです。
>配列変数の使い方がよくわかりません。
なぜ、配列変数が出てくるのでしょうか?
ご回答ありがとうございます。
具体例は、NO.1の補足をご参照下さい。
前回の質問では、1列のみだったので、
今回のように複数列の場合は、配列変数を使うのかなと
思ったからです。
No.3
- 回答日時:
#01です。
補足回答が待てなかったのでB列~任意の列全てに金額があると見なして小計と合計を入れるサンプルマクロを作ってみました。列毎に空白セルはバラバラの行にあっても構いません。また空白行が2行以上連続してもよいです。なお上記仕様だと「小計」「合計」は入れるところがなくなりますので小計:青文字、合計:赤文字にしました。
もし「1行目に金額」とある行だけが集計対象」のような条件がある場合はif文を追加すればいけると思います。修正してみてください。
Option Explicit
Sub Macro1()
Dim Target, Rng, Ofrng As Range, Adr As String
Dim LastC, LastR, idxC, idxR As Long
On Error Resume Next
Application.ScreenUpdating = False
'初期化、最終セル位置確認
Set Target = ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell)
Range(Cells(1, 1), Target).Font.ColorIndex = 0
LastR = Target.Row + 1
LastC = Target.Column
Set Target = Nothing
Set Target = ActiveSheet.Cells.SpecialCells(xlCellTypeFormulas, 23)
If Target.Cells.Count > 0 Then
Target.ClearContents
End If
'小計挿入
For idxC = 2 To LastC
For idxR = LastR To 2 Step -1
Set Rng = Cells(idxR, idxC)
With Rng
Set Ofrng = .Offset(-1, 0)
Adr = Ofrng.Address
If Ofrng <> "" Then
If IsNumeric(Ofrng) Then
If Ofrng.Offset(-1, 0) <> "" Then
Adr = Range(Ofrng, Ofrng.End(xlUp)).Address
idxR = Range(Ofrng, Ofrng.End(xlUp)).Row
Else
Adr = Range(Ofrng).Address
End If
Rng.Formula = "=subtotal(9," & Adr & ")"
Rng.Font.ColorIndex = 5
End If
End If
End With
Next idxR
Next idxC
'合計挿入
For idxC = 2 To LastC
Set Target = Cells(65536, idxC).End(xlUp)
Adr = Target.Address
Set Target = Target.Offset(1, 0)
Target.Formula = "=subtotal(9," & Cells(1, idxC).Address & ":" & Adr & ")"
Target.Font.ColorIndex = 3
Next idxC
Application.ScreenUpdating = True
End Sub
ご回答ありがとうございます。
具体例は、NO.1の補足をご参照下さい。
データに空白があった場合、そこで集計されてしまいます。空白データを0に置き換えると、うまく、計欄に合計数字が出ます。
空白データを自動的に0に置き換える方法はありますか?
No.2
- 回答日時:
No.1さんに加えて、
前の質問のコードで思ったとおりの結果が得られたということは、
●一行目には「見出し行」はない
●一回実行後、データの削除修正追加があった場合は、いちいち、”計”、"合計"の欄をクリアーしてから再計算
これ、非常に使いにくいのでは?
普通は一行目ではなくても「見出し行」があり、データはその次の行から入力され、
また、一回実行後、データの削除修正追加があっても、いちいち、"計","合計"の欄をクリアーしないでそのまま再計算するのが普通だと思うがどうか。
No.1
- 回答日時:
補足要求です
前回の質問はA列がコード、B列が金額でB列の小計と合計を求めるものでした。
1)M列までデータがある場合、集計するべき列であることはどのようにすれば分かるのでしょうか。(B~Mは全て集計対象ですか?)
2)また各列のデータで空白行は全て一緒ですか?
(例えばB列はB4が空白なら、C4~M4も同じですか? それとも列毎に空白の行は変化しますか?)
3)「計」という文字は今回も入れるのですか?
その場合どの位置に入れれば良いですか?
上記の条件を教えていただかないと、適当にしかマクロを組めません
この回答への補足
返事が遅くなり、申し訳ありません。
1)B~Mは全て集計対象です。
2)列毎に空白の行は変化します。
3)「計」という文字は今回も入れます。
B列のcodeの変わり目です。
(下記で1行目は列、1列目は行数を表しています)
実行前
------A------B------C-----D
1--code--data1--data2--data3
2-----1-----10-----40-----20
3-----1-----20------------30
4-----1-----50-----20-----50
5
6-----2-----40-----10
7-----2-----60-----20-----90
8-----2-----30------------80
9
10----3-----50-----40-----70
11----3-----10------------50
12----3-----30-----60
実行後
------A------B------C-----D
1--code--data1--data2--data3
2-----1-----10-----40-----20
3-----1-----20------------30
4-----1-----50-----20-----50
5----計-----80-----60----100
6-----2-----40-----10
7-----2-----60-----20-----90
8-----2-----30------------80
9----計----130-----30----170
10----3-----50-----40-----70
11----3-----10------------50
12----3-----30-----60
13---計-----90----100----120
14-合計----300----190----390
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- Visual Basic(VBA) Excel VBA マクロ ある列の最終行迄を参照し、別の列の空白セルに値を入力したいです 2 2023/03/05 02:44
- Excel(エクセル) PowerQueryに詳しい方教えてください(Office365) 1 2022/07/24 21:11
- Visual Basic(VBA) エクセルVBAについて 2 2023/01/31 16:21
- Excel(エクセル) マクロだと数式が表示される 2 2022/09/10 14:48
- Excel(エクセル) 特定文字(数字)で行挿入、挿入された行で合計したい 2 2023/03/13 14:30
- Excel(エクセル) capeofdragonと申します Excel2016を使っておりまして 半角又は全角の任意文字列が 2 2022/10/31 13:51
- Excel(エクセル) エクセルの表でダブりを解消する方法を、教えてください。 5 2023/04/12 12:11
- Excel(エクセル) Excel 文字列を結合するときに重複をなくしたい 関数・VBA 2 2022/12/12 10:40
- その他(プログラミング・Web制作) テキストエディタで複数行にわたる文字列の行頭に番号を振る方法 4 2023/03/11 12:57
- Visual Basic(VBA) マクロで最終行を取得したい 4 2023/05/28 12:14
関連するカテゴリからQ&Aを探す
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
Excel2017 フィルタ昇順並びがA...
-
エクセルの時刻のカウントが出...
-
エクセルで行の高さ及び列幅の...
-
急ぎ!色のついたセルを非表示...
-
【Excel VBA】指定した行の最大...
-
VBA 複数行の検索及び抽出
-
オートフィルタ後のデータから...
-
EXCELマクロを使い、空白行では...
-
列と行の名前(重複あり)が交...
-
エクセル関数について
-
複数列の条件抽出したCOUNT
-
エクセル関数のSUMPRODUCTにつ...
-
VBA 配列で型がエラーになります。
-
Excelで並び替え後にア行...
-
文字列を比較し、相違するフォ...
-
EXCELで日付を比べ3か月以内の...
-
excel / ピポッド 日数を出したい
-
プルダウンに【なし、平均、デ...
-
エクセル VBA 行間隔を飛ばした...
-
EXCELでデータ並べ替えにの時、...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
Excel2017 フィルタ昇順並びがA...
-
Excelで並び替え後にア行...
-
エクセルで行の高さ及び列幅の...
-
オートフィルタ後のデータから...
-
急ぎ!色のついたセルを非表示...
-
エクセルの時刻のカウントが出...
-
【Excel VBA】指定した行の最大...
-
基準日以前のデータを範囲を指...
-
平均変化率の信頼区間
-
エクセル VBA 行間隔を飛ばした...
-
文字列を比較し、相違するフォ...
-
プルダウンに【なし、平均、デ...
-
EXCEL 最終行のデータを他のセ...
-
excel / ピポッド 日数を出したい
-
EXCELで日付を比べ3か月以内の...
-
VBA 複数行の検索及び抽出
-
エクセル関数について
-
複数回答のクロス集計の方法
-
マクロで行の高さを設定したい
-
エクセル関数のSUMPRODUCTにつ...
おすすめ情報