Excel VBA質問箱 IV

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

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

  新規投稿 ┃ツリー表示 ┃一覧表示 ┃トピック表示 ┃検索 ┃設定 ┃過去ログ ┃ホーム  
2509 / 13646 ﾂﾘｰ ←次へ　｜　前へ→
▼ 【67431】SUMPRODUCT関数の活用について Yaesu 10/12/2(木) 21:58 質問[未読]
【67432】Re:SUMPRODUCT関数の活用について SK63 10/12/2(木) 22:28 発言[未読]
【67433】Re:SUMPRODUCT関数の活用について SK63 10/12/2(木) 22:46 発言[未読]
【67445】Re:SUMPRODUCT関数の活用について Yaesu 10/12/4(土) 12:15 質問[未読]
【67447】Re:SUMPRODUCT関数の活用について SK63 10/12/5(日) 8:10 回答[未読]
【67461】Re:SUMPRODUCT関数の活用について Yaesu 10/12/6(月) 21:50 質問[未読]
【67463】Re:SUMPRODUCT関数の活用について Yaesu 10/12/6(月) 22:25 質問[未読]
【67480】Re:SUMPRODUCT関数の活用について SK63 10/12/8(水) 18:28 回答[未読]
【67530】Re:SUMPRODUCT関数の活用について Yaesu 10/12/10(金) 23:03 質問[未読]
【67531】Re:SUMPRODUCT関数の活用について SK63 10/12/11(土) 1:10 発言[未読]
【67532】Re:SUMPRODUCT関数の活用について SK63 10/12/11(土) 1:47 発言[未読]
【67639】Re:SUMPRODUCT関数の活用について Yaesu 10/12/20(月) 19:16 お礼[未読]
【67431】SUMPRODUCT関数の活用について
質問  Yaesu  - 10/12/2(木) 21:58 -
引用なし
パスワード
   こんばんは。。
　
　以下のデータを基に計算式を手入力して、各班の積算合計をやっています。

　その作業負担を軽減させるため、マクロを使えば、効果があるのではない
かと思って、自動記録方法で、一応マクロを実行すれば、正しく計算してく
れますが、横方向の「受注１班」から「受注合計」までの項目数が日々変わ
ることと縦方向も変わります。

　この可変の要素を絡むと、マクロの構成にはどのように対応すればいい
でしょうか？ぜひご伝授ください。
　
*************************************************************************
　　A　　　　　B　　　 C　　　　 D　　　　E　　　　F　　　　 　 H
　 製品コード 製品名 受注単価　受注１班 受注２班 受注３班 ・・　受注合計
　Ａ００１　　ああ　 　10　　　　1　　　　 3　　　　　　　　　　 4
　Ａ００２　　いい　 　20　　　　1 　　　　1　　　 1　　　　　　 3
　Ａ００３　　うう　　 30　　　　
　Ａ００４　　ええ　　 40　　　　1　　　　 1　　　 1　　　　　　 3
　・
　・
　　　　　　　　　　　　　　　　70　　　　 90　　 60　　　　　　220
*************************************************************************

---------------------------マクロコード----------------------------------
Sub Macro1()
　　
　　Range("D7").Select
　　ActiveCell.FormulaR1C1 = _
　　　　"=SUMPRODUCT(VALUE(R[-5]C[-1]:R[-2]C[-1]),VALUE(R[-5]C:R[-2]C))"
　　
　　Range("E7").Select
　　ActiveCell.FormulaR1C1 = _
　　　　"=SUMPRODUCT(VALUE(R[-5]C[-2]:R[-2]C[-2]),VALUE(R[-5]C:R[-2]C))"
　　
　　Range("F7").Select
　　ActiveCell.FormulaR1C1 = _
　　　　"=SUMPRODUCT(VALUE(R[-5]C[-3]:R[-2]C[-3]),VALUE(R[-5]C:R[-2]C))"
　　
　　Range("H7").Select
　　ActiveCell.FormulaR1C1 = _
　　　　"=SUMPRODUCT(VALUE(R[-5]C[-5]:R[-2]C[-5]),VALUE(R[-5]C:R[-2]C))"
　　
　　Range("I7").Select

End Sub
--------------------------------------------------------------------------


【67432】Re:SUMPRODUCT関数の活用について
発言  SK63  - 10/12/2(木) 22:28 -
引用なし
パスワード
   ▼Yaesu さん：

Dim RC As String
RC = ActiveSheet.Cells.SpecialCells(xlLastCell).Address
とするとRCの中に"$G$9"のように最終行がはいりますので、
これを応用してはいかがですか？
RC=ActiveSheet.Range("$A$65536").End(xlUp).Address
ではA列の最終行は得られます。
【67433】Re:SUMPRODUCT関数の活用について
発言  SK63  - 10/12/2(木) 22:46 -
引用なし
パスワード
   ▼SK63 さん：
>▼Yaesu さん：
歯抜け等の問題があるのでこちらのほうが良いかもです。
cは列、Rは行が帰ります。

Dim c As Integer
Dim R As Integer

For c = 1 To 100
If Cells(1, c) = "受注合計" Then
　 Exit For
End If
Next c

R = Range("$A$65536").End(xlUp).Row
【67445】Re:SUMPRODUCT関数の活用について
質問  Yaesu  - 10/12/4(土) 12:15 -
引用なし
パスワード
   SK63さん
ご回答をいただき、ありがとうございます。
希望の通り、データの有効範囲を特定することはできました。
その次、データ範囲を集計させるため、教科書になっている「SUMPRODUCT積算
関数」を利用して、計算をしたいです。

質問：集計に必要な変数がわかりますが、計算関数に変数を渡す方法と
　　　VBAコードの組み立て方法は分りません。

///////////////////////////////////////////////////////////////////////
集計範囲：変数Retu1=9（Ｉ）列目〜変数Retu2=12（Ｌ）列目
集計開始行：4行目＝＝＞固定
集計終了行：変数Gyou=6

計算結果：I7=SUMPRODUCT(VALUE(B4:B6),VALUE(I4:I6)) --
　　　　　J7=SUMPRODUCT(VALUE(B4:B6),VALUE(J4:J6))　|　集計範囲変数指定
　　　　　K7=SUMPRODUCT(VALUE(B4:B6),VALUE(K4:K6))　|
　　　　　L7=SUMPRODUCT(VALUE(B4:B6),VALUE(L4:L6)) --
　　　　　　　　　　　　-----------　
　　　　　　　　　　　　固定B4〜
　　　　　　　　　　　　終了変数Gyouまで
　　　　　　　　　　　　　　　　　　　　　　　　　集計変数Retu1〜
　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　Retu2まで
　　　　　　　　　　　　　　　　　　　　　　　　　-------------------
　A　　 B　　 C　　 D　　 E　　 F　　 G　　 H　　 I　　 J　　 K　　 L　　M
1
2
3
4
【67447】Re:SUMPRODUCT関数の活用について
回答  SK63  - 10/12/5(日) 8:10 -
引用なし
パスワード
   他にも方法はありますが、、、、
理解しやすそうに困難でいかがですか。
私もｴｸｾﾙsumproductがあるとは、、、、、

dO~lOOPでまわしてありますが、"受注合計"が変更されると
無限ループになりますので、do unTIL　Cells(1, c) = ""　
のほうが安全ですね。多分列の終わりのに"=SUM(~)"で横計も
出したいはずですね。

Sub t()

Dim c As Integer, r As Integer
Dim ads As String, ade As String
r = Cells(65536, 1).End(xlUp).Row
c = 3

Do

If Cells(1, c) = "受注合計" Then
　　Exit Do
Else
ade = ActiveSheet.Cells(r, c).Address
ads = ActiveSheet.Cells(4, c).Address
Cells(r + 1, c) = "=SUMPRODUCT(VALUE($B4:$B" & r & _
　　　　　　 "),VALUE(" & ads & ":" & ade & "))"
　c = c + 1
End If

Loop

End Sub
【67461】Re:SUMPRODUCT関数の活用について
質問  Yaesu  - 10/12/6(月) 21:50 -
引用なし
パスワード
   SK63 さん

　こんばんは。
　紹介してくれたコードは、難しいですね、読んでも理解できない。
可能であれば各変数の意味を書いてくれれば助かります。
お願いします！
【67463】Re:SUMPRODUCT関数の活用について
質問  Yaesu  - 10/12/6(月) 22:25 -
引用なし
パスワード
   ************変数の説明**************

retu2＝9（I列目）

retu1＝12（L列目）

データの開始行：4行目

データの終了行：10行目　　de=10

計算結果：11行目　　　　　ke=11
-------------------------------------------------------------------------
計算方法

i=9のとき
　セル(11,9)=I10に「SUMPRODUCT(VALUE(B4:B10),VALUE(I4:I10))
i=10のとき
　セル(11,10)=J10に「SUMPRODUCT(VALUE(B4:B10),VALUE(J4:J10))
・
・
i=12のとき
　セル(11,12)=L10に「SUMPRODUCT(VALUE(B4:B10),VALUE(L4:L10))

のように計算式を該当セルに代入したいので、以下のコードを作成しました。
どこに間違っているか、わからなくて、教えてください。

-----------------------------------------------------------------------
For i = retu2 To retu1
Worksheets("sheet1").Cells(ke, i).Formula = "=SUMPRODUCT(VALUE(B4:B" & de & "),VALUE(" & Na(i) & " 4:" & Na(i) & de & "))"
Next i
・
・
【67480】Re:SUMPRODUCT関数の活用について
回答  SK63  - 10/12/8(水) 18:28 -
引用なし
パスワード
   ▼Yaesu さん：

" 4:"ここは、"4:"出ないですか？？
Dim c As Integer, r As Integer
cは列の終わり、rは行の終わり
Dim ads As String, ade As String
adsはスタートの列のアドレス、、、A,B,Cみたいな
aseは終わりです。

F8で動作させるてマウスを変数の上にするかウオッチ式を設定すると見れます。


>************変数の説明**************
>
>retu2＝9（I列目）
>
>retu1＝12（L列目）
>
>データの開始行：4行目
>
>データの終了行：10行目　　de=10
>
>計算結果：11行目　　　　　ke=11
>-------------------------------------------------------------------------
>計算方法
>
>i=9のとき
>　セル(11,9)=I10に「SUMPRODUCT(VALUE(B4:B10),VALUE(I4:I10))
>i=10のとき
>　セル(11,10)=J10に「SUMPRODUCT(VALUE(B4:B10),VALUE(J4:J10))
>・
>・
>i=12のとき
>　セル(11,12)=L10に「SUMPRODUCT(VALUE(B4:B10),VALUE(L4:L10))
>
>のように計算式を該当セルに代入したいので、以下のコードを作成しました。
>どこに間違っているか、わからなくて、教えてください。
>
>-----------------------------------------------------------------------
>For i = retu2 To retu1
>Worksheets("sheet1").Cells(ke, i).Formula = "=SUMPRODUCT(VALUE(B4:B" & de & "),VALUE(" & Na(i) & " 4:" & Na(i) & de & "))"
>Next i
>・
>・
【67530】Re:SUMPRODUCT関数の活用について
質問  Yaesu  - 10/12/10(金) 23:03 -
引用なし
パスワード
   SK63さん

返事遅くなりました。確認の結果を報告します。

実行すると、合計行の列番号はIVまでになっています。

たとえば、セルIV12の計算式=SUMPRODUCT(VALUE($B4:$B11),VALUE($IV$4:$IV$11))
操作の間違いでしょうか？
教えてください。
【67531】Re:SUMPRODUCT関数の活用について
発言  SK63  - 10/12/11(土) 1:10 -
引用なし
パスワード
   ▼Yaesu さん：
>SK63さん
少し話が見えてきてないのですが、

For i = retu2 To retu1
Worksheets("sheet1").Cells(ke, i).Formula = "=SUMPRODUCT(VALUE(B4:B" & de & "),VALUE(" & Na(i) & " 4:" & Na(i) & de & "))"
Next i

を実行すると

=SUMPRODUCT(VALUE($B4:$B11),VALUE($IV$4:$IV$11))
になるのですか？？
どの様なコードでretu1,retu2等の値を出しているのでしょうか？

また、どの様な結果を求めているのでしょうか？
こちらからは、何が知りたいかが理解できないのですが、、、、
全体のコードがあれば解りやすいですが、

たとえば、セルIV12の計算式=SUMPRODUCT(VALUE($B4:$B11),VALUE($IV$4:$IV$11))
操作の間違いでしょうか？

エラーではないですし、、、
【67532】Re:SUMPRODUCT関数の活用について
発言  SK63  - 10/12/11(土) 1:47 -
引用なし
パスワード
   ▼SK63 さん：
" 4:"ここは、"4:"出ないですか？？
でエラーは直ったと思いますが？？どうでしょうか？
テストでしたのコードを実行してみると問題ないのですが
配列Na(i)がおかしいのでは。

ade = ActiveSheet.Cells(de, i).Address
ads = ActiveSheet.Cells(4, i).Address
初買わない理由があるのですか？
ヘルプの引用です。

Set mc = Worksheets("Sheet1").Cells(1, 1)
MsgBox mc.Address()　　　　　　　　　　　　　　　' $A$1
MsgBox mc.Address(RowAbsolute:=False)　　　　　　' $A1
MsgBox mc.Address(ReferenceStyle:=xlR1C1)　　　　' R1C1
MsgBox mc.Address(false,false)　　　　　　　　　 'A1


retu2 = 9
retu1 = 12
de = 10
ke = 11

Dim na(20) As String
na(9) = "I"
na(10) = "J"
na(11) = "K"
na(12) = "L"


For i = retu2 To retu1

ade = ActiveSheet.Cells(de, i).Address
ads = ActiveSheet.Cells(4, i).Address

Worksheets("sheet1").Cells(ke, i) = "=SUMPRODUCT(VALUE(B4:B" & de & "),VALUE(" & ads & ":" & ade & "))"
Next i
【67639】Re:SUMPRODUCT関数の活用について
お礼  Yaesu  - 10/12/20(月) 19:16 -
引用なし
パスワード
   　ActiveSheet.Cells(de, i).Addressを使用することは、スゴクヒントに
なりました。

本当にありがとうございました。
  新規投稿 ┃ツリー表示 ┃一覧表示 ┃トピック表示 ┃検索 ┃設定 ┃過去ログ ┃ホーム  
2509 / 13646 ﾂﾘｰ ←次へ　｜　前へ→
ページ：  ┃  記事番号：
2610219
(SS)C-BOARD v3.8 is Free