[[20150827184149]]　『マクロのSUMPRODUCT』（Choi）　ページの最後に飛ぶ

『マクロのSUMPRODUCT』（Choi）

マクロで「SUMPRODUCT」を使用できると知り試してみましたがうまくいきません。
下記関数式とマクロでの結果が違うのはどこがオカシイのでしょうか？
（どちらもエラー表示は出てません）
関数式）
G2=SUMPRODUCT(($A$2:$A$100=A2)*1,($E$2:$E$100=1)*1,$D$2:$D$100)
マクロ）
Sub Test_0827()
Dim i As Long
Dim cntR As Long
Dim TT As Long, Fa As String, Fe As Long

cntR = Cells(Rows.Count, 1).End(xlUp).Row 　
For i = 2 To cntR
Fa = Cells(i, 1)
Fe = 1
TT = Evaluate("SUMPRODUCT((A2:A" & cntR & "=""" & Fa & """)*1, (E2:E" & cntR & _
"=""" & Fe & """)*1, (D2:D" & cntR & "))")
Cells(i, 7) = TT
Next i
End Sub

返信は明日以降になりそうですが、よろしくお願いします。

＜使用 Excel：Excel2007、使用 OS：WindowsXP ＞

 A列の各セルに、どんな値が入っているのかわかりませんが、Fa = Cells(i, 1) 。
 この Fa を式の中で使っていますね。
 その他にも、作り上げた、Evaluateに与える文字列が、意図したものになっているかどうかを
 目で見て確認されてはいかがでしょう。

 TT = Evaluate(・・・) の上に

 MsgBox "SUMPRODUCT((A2:A" & cntR & "=""" & Fa & """)*1, (E2:E" & cntR & _ 
 "=""" & Fe & """)*1, (D2:D" & cntR & "))"

 こんなコードを追加して、表示される式の文字列が正しいかどうか、確認されてはいかがですか。
 かなり、予期したものと違っていると思われます。

（β） 2015/08/27(木) 19:08

 ＞TT = Evaluate("SUMPRODUCT((A2:A" & cntR & "=""" & Fa & """)*1, (E2:E" & cntR & "=""" & Fe & """)*1, (D2:D" & cntR & "))") 
 　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　↑　　　　↑

 　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　Feは文字じゃないので、""　が余分です。

  (D2:D" & cntR & "))") 
  ↑　　　　　　　↑　
  ついでですけど、この括弧は無益です。
　害も無いですが、計算の順序に何の影響も与えないので不要です。

  　　　　　　　 G2=SUMPRODUCT(($A$2:$A$100=A2)*1,($E$2:$E$100=1)*1,$D$2:$D$100)                
　　　　　　　　　　　　　　   ~~~~~~~~~~~~~↑~￣~~~~~~~~~~~~~~~~~
真偽値同士の掛け算でいいので 　($A$2:$A$100=A2)*($E$2:$E$100=1),

（半平太） 2015/08/27(木) 19:22

β様、半平太様　ありがとうございます。
下記で関数式結果との一致をみたのですが、今一つ？？なもので．．

TT = Evaluate("SUMPRODUCT((A2:A" & cntR & "=""" & Fa & """)*(E2:E" & cntR & _
"=" & Fe & "), D2:D" & cntR & ")")

↑の「MsgBox」の結果は、「SUMPRODUCT((A2:A="")*(E2:E),D2:D)」　です。

1.これで正解なのでしょうか？　
　「MsgBox」が表示している「""」は何でしょう？
　なぜ全記述を表示しないのでしょうか？
2.「& "=""" & Fa & """)」の「""」は空白を意味していると思いますが、
　>Feは文字じゃないので、""　が余分．．< のことを含めて「""」の使い方と
　いうかルールを教えていただけないでしょうか？

“生半可”な知識で無謀なことをしていると自覚はしていますが、
どうかよろしくお願いします。
（Choi） 2015/08/28(金) 10:30

  ＞“生半可”な知識で無謀なことをしていると自覚はしていますが、

  「無謀」は大げさです　(^_^;)

 ＞　>Feは文字じゃないので、""　が余分．．< のことを含めて「""」の使い方と 
 ＞　いうかルールを教えていただけないでしょうか？

 文字列は""で囲みますよね？

 では（"）自体を文字列に一つ入れたい場合はどうするかと云うと→""""となります。

 両サイドの""が文字列を表し、中は二つの""で、一つの"を表します。

  よって例えば =A1="B"　と云う文字列を作る為には下の様に書く必要があります。
    　　　　　　↓
       MsgBox "=A1=""B"""

 当然、MsgBox "=A1=""1"""　と書いたら、=A1="1" になります。
 その１は文字ですよね。

 数値の1としてチェックする式にしたいなら、余分な""は除去して
 MsgBox "=A1=1"　としなければなりません。

 重要：外側に配置されている"  "　と文字列の中にある""を峻別してください。

 で、上記アドバイス通りに書くとすれば、下記になります。

  Sub Test_0827()
      Dim i As Long
      Dim cntR As Long
      Dim TT As Long, Fa As String, Fe As Long

      cntR = Cells(Rows.Count, 1).End(xlUp).Row

      For i = 2 To cntR
          Fa = Cells(i, 1)
          Fe = 1
          TT = Evaluate("SUMPRODUCT((A2:A" & cntR & "=""" & Fa & """)*(E2:E" & cntR & _
          "=" & Fe & "), (D2:D" & cntR & "))")
          Cells(i, 7) = TT
       Next i
   End Sub

  でも、やろうとしていることが、上の数式の結果値をG列の各行に埋め込みたいのでしたら 
  セルにその数式を埋め込んでから、Valueに置き換えればいいんじゃないですか？

  検証もやり易いです（値化の前でStopすれば数式がセル内に見えますので）

  Sub Test_0828()
       Const strFml  As String = _
            "=SUMPRODUCT(($A$2:$A$LastRow=A2)*($E$2:$E$LastRow=1),$D$2:$D$LastRow)"
       Dim Fml As String
       Dim LastRow As Long

       LastRow = Cells(Rows.Count, 1).End(xlUp).Row
       Fml = Replace(strFml, "LastRow", LastRow)

       With Range("G2:G" & LastRow)
          .FormulaLocal = Fml   '数式を入力
          .Value = .Value  　　 '値に変換
       End With
   End Sub

（半平太） 2015/08/28(金) 13:52

半平太様、　ありがとうございます。

何回も読み返してようやくわかった気が．．．

 >（"）自体を文字列に一つ入れたい場合はどうするかと云うと→""""となりま す。
 >両サイドの""が文字列を表し、中は二つの""で、一つの"を表します

↑の場合の「""」は「空白」ではなく先頭の「"」は後ろの「"」自体を表す“おまじない”で
「& "=""" &」の「=」の後の「""」は「空白」を表す．．ですかねぇ．．

実は初めて一からマクロを作りまして、右も左もわかってません。
「SUMPRODUCT」を使った処理がこの先あるのですが、提示いただいたマクロを参考にがんばってみたいと思います。

これからもよろしくお願いします。
（Choi） 2015/08/28(金) 15:23

コメント返信：

[ 一覧(最新更新順) ]

[[20150827184149]] 『マクロのSUMPRODUCT』（Choi） ページの最後に飛ぶ

[[20150827184149]]　『マクロのSUMPRODUCT』（Choi）　ページの最後に飛ぶ