行の自動カウントと複数の関数式をマクロで実行したい

質問

EXCELの初心者です。今後の事も考えて、
マクロで右側のセルの値($J$4～$V$4から下方へ数万行)が入っている行数を自動カウントさせて、複数の関数もマクロの中に記載して、
『値形式』の値を張り付けと、自動で左側の各項目に集計した数値が表示されるようにしたいのですが、マクロに全く慣れておらず、マクロを書く為に使用するsheetの種類も分かっていません。

行数を自動カウントし、複数の関数もマクロの中に記載して集計を
自動計算するマクロの書き方をどなたか教えて頂けないでしょうか。

値の集計はマクロは使っておらず、
右側のセル($J$4～$V$4から下方へ数万行)に『値形式』で値が張り付けて、左側のセルに項目毎の合計を下記のような複数(48行)の計算式を入れ込んむ事で求めています。

＜$B$11のセルに入れている関数式＞
=SUMPRODUCT((($Q$4:$Q$10000=B$10)*($S$4:$S$10000<>"自主運用"))/COUNTIFS($Q$4:$Q$10000,$Q$4:$Q$10000&"",$S$4:$S$10000,$S$4:$S$10000&"",$T$4:$T$10000,$T$4:$T$10000&""))

＜$B$15のセルに入れている関数式＞
=SUMPRODUCT((($Q$4:$Q$10000=B$14)*($S$4:$S$10000<>"自主運用"))/COUNTIFS($Q$4:$Q$10000,$Q$4:$Q$10000&"",$S$4:$S$10000,$S$4:$S$10000&"",$O$4:$O$10000,$O$4:$O$10000&""))

＜$B$21のセルに入れている関数式＞
=SUMPRODUCT((($Q$4:$Q$10000=$B$20)*($S$4:$S$10000=$B$19))/COUNTIFS($Q$4:$Q$10000,$Q$4:$Q$10000&"",$S$4:$S$10000,$S$4:$S$10000&"",$T$4:$T$10000,$T$4:$T$10000&""))

＜$B$24のセルに入れている関数式＞
=SUMPRODUCT((($Q$4:$Q$10000=$B$23)*($S$4:$S$10000=$B$22))/COUNTIFS($Q$4:$Q$10000,$Q$4:$Q$10000&"",$S$4:$S$10000,$S$4:$S$10000&"",$T$4:$T$10000,$T$4:$T$10000&""))
2月分の集計時は、右側の値の行は9753行でしたが、
3月分の集計時は、右側の値の行は10648行となり10000行を超えていた為、上記の関数式では正しい集計を出す事が出来ませんでした。
今回は置換を利用して、以下のように関数中の$10000→$20000へ変更しました。
$B$11
=SUMPRODUCT((($Q$4:$Q$20000=B$10)*($S$4:$S$20000<>"自主運用"))/COUNTIFS($Q$4:$Q$20000,$Q$4:$Q$20000&"",$S$4:$S$20000,$S$4:$S$20000&"",$T$4:$T$20000,$T$4:$T$20000&""))

$B$15
=SUMPRODUCT((($Q$4:$Q$20000=B$14)*($S$4:$S$20000<>"自主運用"))/COUNTIFS($Q$4:$Q$20000,$Q$4:$Q$20000&"",$S$4:$S$20000,$S$4:$S$20000&"",$O$4:$O$20000,$O$4:$O$20000&""))

説明が上手く表現出来ず申し訳ありませんが、どうぞ宜しくお願い致します。

回答No.4

kkkkkm · ベストアンサー

> 『値形式』で値が張り付けて

これがどこから貼り付けるのか不明ですのでコードに記載してませんが

> 行数を自動カウントし、複数の関数もマクロの中に記載して集計を
> 自動計算する

もとの計算式の1000の所をその時のJ列の最終行にしてそれぞれのセルに式を入れ直します。

> 下記のような複数(48行)

48行というのがどこなのか不明なので質問に記載された部分だけです。

コードは式のあるシートのシートモジュールでもいいですし標準モジュールでも可です。

Sub Test()
Dim LastRow As Long

With Sheets("Sheet1") 'Sheet1は実際のシート名を記載してください
'Ｊ列の最終行を最後のデータがある行とします
LastRow = .Cells(Rows.Count, "J").End(xlUp).Row

.Range("B11").Formula = "=SUMPRODUCT((($Q$4:$Q$" & LastRow & "=B$10)*($S$4:$S$" & LastRow & "<>""自主運用""))/COUNTIFS($Q$4:$Q$" & LastRow & ",$Q$4:$Q$" & LastRow & "&"""",$S$4:$S$" & LastRow & ",$S$4:$S$" & LastRow & "&"""",$T$4:$T$" & LastRow & ",$T$4:$T$" & LastRow & "&""""))"
.Range("B15").Formula = "=SUMPRODUCT((($Q$4:$Q$" & LastRow & "=B$14)*($S$4:$S$" & LastRow & "<>""自主運用""))/COUNTIFS($Q$4:$Q$" & LastRow & ",$Q$4:$Q$" & LastRow & "&"""",$S$4:$S$" & LastRow & ",$S$4:$S$" & LastRow & "&"""",$O$4:$O$" & LastRow & ",$O$4:$O$" & LastRow & "&""""))"
.Range("B21").Formula = "=SUMPRODUCT((($Q$4:$Q$" & LastRow & "=$B$20)*($S$4:$S$" & LastRow & "=$B$19))/COUNTIFS($Q$4:$Q$" & LastRow & ",$Q$4:$Q$" & LastRow & "&"""",$S$4:$S$" & LastRow & ",$S$4:$S$" & LastRow & "&"""",$T$4:$T$" & LastRow & ",$T$4:$T$" & LastRow & "&""""))"
.Range("B24").Formula = "=SUMPRODUCT((($Q$4:$Q$" & LastRow & "=$B$23)*($S$4:$S$" & LastRow & "=$B$22))/COUNTIFS($Q$4:$Q$" & LastRow & ",$Q$4:$Q$" & LastRow & "&"""",$S$4:$S$" & LastRow & ",$S$4:$S$" & LastRow & "&"""",$T$4:$T$" & LastRow & ",$T$4:$T$" & LastRow & "&""""))"
End With

End Sub

補足

早速のご教示ありがとうございました。
皆様からのご教示やご指摘を頂く中で、説明が上手く出来ず、ご教示をくださった皆様には、大変ご迷惑をお掛けしました。文章だけの説明には限界がある事がわかりましたので、Excelのダミーデータを作成しました。
この後、このダミーデータを添付して、再度やり方をお聞きする事は可能でしょうか？
OKWAVEでの規則など、分からなくて申し訳ありません。
皆様から色々と熱心にご教示頂いていますので、
この件はなんとか解決して行きたいと思っております。

回答No.7

kkkkkm

> 『=B11』の値をセルB5へ表示させる

.Range("B5").Value = .Range("B11").Value

> 『=SUM(B5:G5)』の値をセルH5へ表示させる

.Range("H5").Value = WorksheetFunction.Sum(.Range("B5:G5"))

上記の順番で実行させてください。

No.4のコードの
End With
より前に記載してください。

ただ、VBAで代入しなくても
そのまま
B5に
=B11
H5に
=SUM(B5:G5)
としておいてもいいのではと思うのですが。

お礼

更に、早速のご教示ありがとうございました。
ご教示頂いた通りに実行し、正しく値が入りました。
ありがとうございました。感謝です！
これからも、よろしくお願いいたします。

回答No.6

kkkkkm

> この後、このダミーデータを添付して、再度やり方をお聞きする事は可能でしょうか？

エクセルファイルそのものは添付できないと思いますよ。画像なら添付できますが、お礼や補足に画像を添付することはできないと思いますし、画像を見せられても、たとえば値貼り付けをどこからどこへするのかは文章で説明しないと画像では分かりません。

また、数式を画像で見せられるよりは文字で記載してもらったほうがそのままコピペできるので回答する方としては楽です。
あと、ちゃんと具体的に書かないと伝わりません。
たとえば、私が「5列右に寄せてください」とだけ記載してあなたはそれがどこなのかわかりますか？

現在の数式で正しい結果が出ているのですから、VBAで数式の10000(最終行)の部分だけ毎回書き替えるようにすればいいのではないですか。それがNo.4のやり方です。

とりあえずNo.4のコードを実行して
$B$11、$B$15、$B$21、$B$24
の式の最終行の部分がちゃんと変化するかどうか見てみてそれでよければ他の部分を足せばいいだけです。

お礼

つたない説明にもかかわらず、私がしたい事を明確にそして端的にご教示くださり、大変感謝もうしあげます。
『その他にやりたい事は、例えば、『=SUM(B5:G5)』の値をセルH5へ表示させる、『=B11』の値をセルB5へ表示させる為のVBAのマクロ』
上記についてご教示をお願いしましたが、以下のようにマクロに追記する事で、正しく表示出来ました。
.Range("H5").Formula = "=SUM(B5:G5)"
.Range("B5").Formula = "=B11"

質問の仕方についも、皆様から色々とご指摘をいただき、本当にありがたい事です。
そして、VBAの奥の深さを実感させて頂いた事も感謝致します。
今後ともよろしくお願いいたします

補足

VBAマクロへの記載及び、詳細なご指摘本当にありがとうございます。

回答No.5

kkkkkm

No.4訂正です

もとの計算式の1000の所を
は
1000じゃなくて10000の所をでした。

補足

ご教示頂いた通りに、VBAのsheet1へVBAのマクロを入力し、実行した所、データの最終行を自動認識して、カウントした正しい値を出す事が出来ました。ありがとうございました。
その他にやりたい事は、例えば、『=SUM(B5:G5)』の値をセルH5へ表示させる、『=B11』の値をセルB5へ表示させる為のVBAのマクロを教えて頂きたいです。これをご教示頂けたら、EXCEL上に記載がある48カ所の関数について、VBAのマクロへ頑張って記載し、結果をお知らせ致します。

回答No.3

imogasi

#2です。質問者以外の方も見ているだろうから、説明を追加します。
初心者には、ややこしく、ついでに学習は、しない方がよいと思う。
余り重点を置いて、VBAでワークシート関数を使おうと思わなかったので、
今までの小生の不勉強を感じるので、説明の不十分な点は後免。
（ワークシート関数は今まで通り使い、VBAでやったほうが便利な場合はVBAでやる、でよいのでは。何でもVBAでやる、はそれなりに勉強・経験が要ると思う。
でも、VBAでやったほうが便利な場合を見つけるのは、VBAの勉強次第です。自明とか・定形（ケースが決まっている、の）ではない。
（１）VBAで関数を実行し、セルにその値を入れる方法。
（２）VBAで関数をセルに入れる（埋め込む）方法
（３）その他　浅学にして思いつかない
などがある。
参考例。
Sub test01()
Set wf = Application.WorksheetFunction
MsgBox wf.Sum(Range("a1:C5"))
End Sub
'ーーー
Sub test02()
Set wf = Application.WorksheetFunction
For i = 2 To 5
Cells(i, "A") = wf.Sum(Range("B" & i & ":C" & i))
Next i
End Sub
’ーーーー
'http://officetanaka.net/excel/vba/db/db04.htm
'計算式を埋め込む
Sub Sample04()
'Call Sample03_2
With Worksheets("Sheet1").Cells(Rows.Count, 1).End(xlUp)
.Offset(1, 0) = "合計"
.Offset(1, 2) = "=SUM(" & Range(Range("C2"), Cells(Rows.Count, 3).End(xlUp)).Address & ")"
End With
End Sub
ーーー
（１）関数名では、VBAではそのままでは使えないものがある。
　　　別にVBAの関数で存在する場合など。
（２）コード表現が長くなるので、WFなどを使ってはどうか。我流です。
　　Set wf = Application.WorksheetFunction
（３）( )内の番地の表現について
　　　VBAで、集合関数では、式の複写を使うよりも、対象範囲の番地の記述を変化させて、繰り返し実行をするのではと思う。
セルの番地の式複写による変化は使わないはず。
　　　セルに式を埋め込むときは、番地は、自己責任で考えなければならない。
　（４）VBAで式の複写の機能を使う場合は、新たな別の機能を使わないといけない。
https://www.relief.jp/docs/excel-vba-formula-copy.html
VBAで数式のコピー時に正しく相対参照させる
のような知識が要る。
（５）その時のデータで実行するだけで、関係データが変わっても、結果が連動・自動で変わるわけでなく、再実行する必要がある。
Sub test03()
Range("C6").Formula = "=SUM(c2:C4)"
End Sub
のようにセルに式を、埋め込んだ場合はデータ変動に、連動する。

回答No.2

imogasi

式だけ書いてあって、何がしたいか、読み解けません。複雑で、（小生は）読み解く気がしない。
＝＝＝
（１）エクセル（ワークシート）関数（VBAでも、関数の考えはあるが）では、どの関数を使うか、どう組み合わせるかに、苦労するところを、「IF　Ｔｈｅｎ」文のネストなどを使って考えやすくすると、思います（本件は複数条件をかけて、条件に合致する行の、ある列を加算する、をやっているのでしょうが）それを、．別のコードで計算することが多い。ＳＡMＰＲＯＤＵＣＴ関数で、本来の機能のやりたいこと（積の和）も、ＶＢＡでは、この関数を使ってやらないかもしれない、でしょう。先輩プログラマーも、Javaなどの経験者が多く、エクセルの関数を深くきわめてないかもしれない。
このＳＡMＰＲＯＤＵＣＴ関数を使うことも、質問者が自分で考え付いたものではなく、何か本やWEBに書いてあったのではないですか。それぐらいこの使い方(複数条件に使う方法）は、技巧的な方法です。今は素直にSUMIFS関数もある。
（２）＞EXCELの初心者です。今後の事も考えて、
ＶＢＡでやるときに、ワークシート関数に拘るのは、ＶＢＡの技量の発展を妨げる
と思う。
ですからその時になったら、虚心に、誰かの指導を受けて、VBAを（VBAに向いた方法を）勉強してください。質問者の場合特に指導を受けることが、これが必要と思います。
ちなみに
（３）VBAでは、現状のデータ行数を捕まえるのに
’--データ最下行番号
Sub test01()
lrow = Range("A100000").End(xlUp).Row
MsgBox lrow
End Sub
’ーーCurrentRegionのRangeの行数
Sub test02()
lrow = Range("A2").CurrentRegion.Rows.Count
MsgBox lrow
End Sub
などを使います。ほかにも方法はあるが。
＝＝＝＝
また、VBAでエクセルワークシート関数を使う方法は、
基本的には　ｓ＝Apｐlication.WorksheetFunction.Sumproduct（・・・）でVBA実行でも答えが出るでしょう。ただし
https://excel-ubara.com/excelvba1/EXCELVBA387.html
を見よ。
＞WorksheetFunctionで使用できる関数
の、ような注意事項ありますが。
セル範囲は、VBAでは、Rangeという考えで、考えるようになる。

回答No.1

Mathmi

VBAに限らずプログラムの基本は、手作業で行う事の明確化です。
どういう結果が欲しくて、その為には何を、どのような手順で行っていけばいいのか考えれば、後はコードに落とし込むだけになります。

申し訳ありませんが、関数のcountifsで何を計上しているのか分かりませんでした。
どのような条件で行数を計上したいのか仰っていただければコードが書けると思いますので、宜しくお願いします。
(三つの列の内一つでも空白があるとカウントしない、一つでも値が入っていればカウントする、Q列がB10と同じでS列が自主運用でも空白でもなくT列が空白でない、等)
また、J～V列まで値が入力されているとの事ですが、関数をみるとO,Q,S,T列しか参照していませんが、これは使用しないという事で良いでしょうか？

＞マクロを書く為に使用するsheetの種類
Visual Basic Editorは開けますよね？
右クリック→挿入→標準モジュールを選択すると、左側のツリーに[標準モジュール]及びその下に[Module 1]が追加されたと思います。自分はいつもそのModuleに書いています。