Excel2003を使用しています。
A列 B列 C列 D列 E列 F列
1 9/1 20000
2 9/2 1000
3 9/10 5000
:
:
10 9月計 ※1 ※2
11
12
13 10/1 10000
14 10/10 2000
15 10/14 4000
:
:
20 10月計 ※3 ※4
上記のデータでいうと、
※1(E10セル) =SUM(E1:E9)
※2(F10セル) =SUM(F1:F9)
※3(E20セル) =SUM(E13:E19)
※4(F20セル) =SUM(F13:F19)
という内容の数式を入力するマクロを作成したく、数式を入力したいセルをアクティブにして、ボタン等に登録したマクロを実行すると数式が入力されるようにしたいと考えていますが、その際、集計範囲の最下行は、アクティブセルの1行上までになりますが、最上行をどのようにして取得すればよいかがわかりません。
(実際のデータは、もっと行数も入力されているデータ数も多いです)
また、他にもこんな方法があるとか、こうしたほうが簡単とかあれば、教えていただけると勉強にもなりますので助かります。
うまく説明できなくて申し訳ありませんが、よろしくお願いします。
No.3ベストアンサー
- 回答日時:
簡単に式を代入したいだけでしたらこんなのは如何でしょう。
ただし、B列の日付がシリアル値で、集計したい行のC列には必ず「n月計」と入っている場合限定ですが……。E10に =SUMPRODUCT((MONTH(B$1:B9)=VALUE(LEFT(C10,LEN(C10)-2)))*(E$1:E9))
F10に =SUMPRODUCT((MONTH(B$1:B9)=VALUE(LEFT(C10,LEN(C10)-2)))*(F$1:F9))
と、入れてE10:F10をコピーして他の集計したいセル(例:E20)等に貼り付け。
「n月計」の月に一致した合計値を1行目からアクティブセルの一つ上の行までを対象に求めています。
アドバイスありがとうございます。
>簡単に式を代入したいだけでしたら
セルの位置に関係なく、その月の計を算出できる数式を入力できればいいので、教えていただいた数式をアクティブセルにマクロで入力するようにコードを書いてみたところ、うまくいきました。
ありがとうございました。
No.5
- 回答日時:
エラー400、大当たりだったようで嬉しい限りです。
。(^o^)/~(表のレイアウト)
----------------------------------------------------------
A列 B列 C列 D列 E列 F列
1●1行目は見出し●
2 9/2 1000
3 9/10 5000
:
:
10 9月計 ※1 ※2
11●空白行
12●空白行
13 10/1 10000
14 10/10 2000
15 10/14 4000
:
:
20 10月計 ※3 ※4
21●空白行
22●空白行
23 11/1 10000
24 11/15 2000
---------以下同様ーーーーーー
------------------------------------------------------------------
(前提)
見出し: 1行目
データ: 2行目~~
日_付: B列
月計をセットする行の「C列」には必ず「X月計」の文字あり
次の2行は、空白行
続いて、翌月データが始まる
この繰り返し
重要=>「X月計」のあとは必ず2行空白行があること
(質問のデータではそう取れる)
'--------------------------------------------------
Sub Test()
Dim R As Long
Dim StartRow As Long
Dim EndRow As Long
StartRow = 2
For R = 2 To Cells(Rows.Count, "C").End(xlUp).Row
If Cells(R, "C").Value Like "*月計" Then
EndRow = R - 1
Cells(R, "E").Value = "=SUM(E" & StartRow & ":E" & EndRow & ")"
Cells(R, "F").Value = "=SUM(F" & StartRow & ":F" & EndRow & ")"
StartRow = R + 3
End If
Next R
End Sub
'--------------------------------------------------
また、当方なら予め「X月計」を入れておかないで、それも式のセットの時にC列に入れてやりますが。。
(その場合の条件)
月と月の変わり目には必ず、「空白行」が3行あること
'----------------------------------------------------
Sub Test555()
Dim R As Long
Dim StartRow As Long
Dim EndRow As Long
StartRow = 2
For R = 2 To Cells(Rows.Count, "B").End(xlUp).Row + 1
If Cells(R, "B").Value = "" Then
EndRow = R - 1
Cells(R, "C").Value = Month(Cells(EndRow, "B").Value) & "月計"
Cells(R, "E").Value = "=SUM(E" & StartRow & ":E" & EndRow & ")"
Cells(R, "F").Value = "=SUM(F" & StartRow & ":F" & EndRow & ")"
StartRow = R + 3
R = R + 2
End If
Next R
End Sub
'----------------------------------------------
テストデータでお試しください。
以上。
回答ありがとうございます。
今回の質問にも目を通していただき、嬉しい限りです♪
まず、質問内容のTestマクロを試してみたところ、うまくいきました。
データ開始行等、実際使用する表と多少異なる部分があるのですが、そのへんはこちらで対処できました。
次に、Test555マクロですが、数式と一緒に「*月計」も入れてしまえば、なお良いですよね。
ただ、実際使用する表では、「*月計」の次行に「累計」もあります。また、1番目の「*月計」の次行には「累計」はなく、2番目の「*月計」以降、次行に「累計」があるので、それらの条件を追加すれば、数式をセットするのと同時に「*月計」も入力できそうですね。
実際、自分でそこまでできるかどうかわかりませんが、ぜひ試してみたいと思います。
ありがとうございました。
No.4
- 回答日時:
今の方法で行くと
合計する「数式を設定」セルをActiveにして、このマクロを実行する
Sub 数式設定()
Dim mR As Long
Dim wR As Long
Dim fR As Long
'
fR = 1
With ActiveSheet
mR = ActiveCell.Row - 1
For wR = mR To 1 Step -1
If Left(.Cells(wR, "E").Formula, 1) = "=" Then
fR = wR + 1
Exit For
End If
Next
.Cells(ActiveCell.Row, "E") = "=SUM(E" & fR & ":E" & mR & ")"
.Cells(ActiveCell.Row, "E").Offset(0, 1) = "=SUM(F" & fR & ":F" & mR & ")"
End With
End Sub
アドバイスありがとうございます。
記載していただいたマクロを試してみたところ、うまくいきました。
今回の質問そのままの数式が入力されますね。
想像していたものより、シンプルなコードで少々驚きました。
ありがとうございました。
No.2
- 回答日時:
例の表を見ますと、式を入力したい行のC列には必ず「~月計」と入力されてますよね?
例えば、アクティブセル行のC列の値を一旦取得し、集計したい月を割り出した後、B行でその月範囲内の行を特定し、その範囲行内のE列とF列の集計をする、という手順でマクロを組んだらどうでしょうか?
あと特にSUM関数の計算式で入力する必要性がないなら、マクロ上で合計値を計算して値のみを入力してしまっても良いと思います。
アドバイスありがとうございます。
>例の表を見ますと、式を入力したい行のC列には必ず「~月計」と入力されてますよね?
そのとおりです。数式を入力したい行のC列には必ず「~月計」と入力されていますので、それも集計範囲取得に利用できないかと思い、質問内の表に記載しました。
こんなふうにすればできるのではないかというアイデアは浮かぶものの、それを実際にコードとして記述することがまだまだの状態ですが、他の方からもアドバイスをいただいていますので、頑張ってみます。
ありがとうございました。
No.1
- 回答日時:
>他にもこんな方法があるとか、こうしたほうが簡単とかあれば、教えていただけると
一般的には
A列 B列 C列 D列 E列 F列
1 9/1 20000
2 9/2 1000
3 9/10 5000
:
13 10/1 10000
14 10/10 2000
15 10/14 4000
:
別シートに
A列 B列 C列 D列 E列 F列
1 9月計 ※1 ※2
2 10月計 ※3 ※4
の方が見やすいと思いますが。
この回答への補足
アドバイスありがとうございます。
>他にもこんな方法があるとか、こうしたほうが簡単とかあれば、教えていただけると
“データを集計する数式をマクロで入力する方法”に関して、他にもこんな方法があるとかがあれば、教えていただけると…というつもりで、質問文に書かせていただいたので、データが見やすくなる方法とかいうことではないのです。
今回、質問に関係ないセルのデータは特に記載せず、空白にしていますが、実際には入力されているセルもありますし、同様のシートが30数枚ありますので、集計する数式をマクロで入力できればと思い、質問させていただきました。わかりづらかったようで、申し訳ありません。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- Excel(エクセル) 表示形式、文字列セル(列)に数式を入力するには マクロ 1 2022/09/18 10:53
- Excel(エクセル) Excel2007での条件付き書式について 6 2023/05/02 10:56
- Visual Basic(VBA) 最終列の右へSUM関数を作成するため下記コードを実行しましたが、最終列「10月28日」が上書きされて 3 2022/12/05 20:32
- Visual Basic(VBA) Excel VBA マクロ ある列の最終行迄を参照し、別の列の空白セルに値を入力したいです 2 2023/03/05 02:44
- Excel(エクセル) マクロだと数式が表示される 2 2022/09/10 14:48
- Excel(エクセル) 条件付き書式の色付きセルのカウント方法について 2 2022/10/21 14:51
- Excel(エクセル) 【Excel】指定した文字列に該当する行を重複しないようにリスト 3 2022/03/30 12:27
- Excel(エクセル) エクセルで、 A1セルに「A」という値、 B1セルに「B」という値が入っています。 どちらも表示形式 5 2023/02/22 23:05
- Excel(エクセル) 関数EXACT(文字列,文字列)とexcelVBA 3 2022/04/14 15:07
- Excel(エクセル) エクセルの数式を等間隔にオートフィルできるやり方を教えていただきたいです。 実際の作業↓ A3セルに 7 2023/06/05 19:04
関連するカテゴリからQ&Aを探す
おすすめ情報
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
エクセル初心者です 関数の入れ...
-
【関数】先頭だけにある、半角...
-
エクセル 白黒印刷で白線を印刷...
-
Excelのチェックボックスの使い...
-
【関数】適切な文字数の数字を...
-
Excelのpivotについて質問です
-
Excel ピボットテーブルで日付...
-
LOOKUP関数を使えばいいのでし...
-
エクセル関数を教えてください
-
エクセルのセルに同じ大きさの...
-
UNIQUE関数が使えないバージョ...
-
excelの不要な行の削除ができな...
-
エクセルで「-0.0」と表示さ...
-
時間によってファイル名が変わ...
-
WPS OFFICEでの縦書きについて
-
エクセルの関数について教えて...
-
Aというブックの1というシート...
-
【マクロ】シート名を取得する...
-
VBA Private Sub Worksheet_Cha...
-
VBA、Excelのworkbook.open に...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
Excel 2019 のピボットテーブル...
-
[関数得意な方]教えて下さい・...
-
Excelにてある膨大なデータを管...
-
[関数について]わかる方教えて...
-
Excel初心者です。 詳しい方、...
-
excelの不要な行の削除ができな...
-
エクセル関数に詳しい方教えて...
-
INDIRECTを使わず excelで複数...
-
[オートフィルタ]で抽出された...
-
エクセルの神よ、ご回答を! エ...
-
エクセル関数に詳しい方、教え...
-
各ページの1番上の表示について
-
Excelで写真のような表を作った...
-
エクセルで不等号記号(≠)が上に...
-
数学 Tan(θ)-1/Cos(θ)について...
-
Excel 2019 は、SPILL機能があ...
-
Excelで全角を半角にしたいので...
-
条件付き書式を教えてください
-
Excel フィルターを掛けた状態...
-
[オートフィルタ]の適用範囲の...
おすすめ情報