Excel VBA質問箱 IV

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

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


11980 / 13646 ツリー ←次へ | 前へ→

【9083】sumproductがうまくいきません2 猛虎襲来 03/11/18(火) 0:58 質問
【9084】Re:sumproductがうまくいきません2 ichinose 03/11/18(火) 2:06 回答
【9119】Re:Re:sumproductがうまくいきません2 猛虎襲来 03/11/18(火) 22:16 質問
【9120】Re:Re:sumproductがうまくいきません2 ichinose 03/11/18(火) 23:04 回答
【9122】できました!! 猛虎襲来 03/11/19(水) 0:22 お礼
【12976】SumProductがうまくいきません auxo 04/4/20(火) 17:27 質問
【12978】Re:SumProductがうまくいきません ichinose 04/4/20(火) 17:48 発言
【12985】Re:SumProductがうまくいきません auxo 04/4/20(火) 18:48 質問
【12989】【解決しました】SumProductがうまくいき... auxo 04/4/20(火) 19:06 お礼

【9083】sumproductがうまくいきません2
質問  猛虎襲来 E-MAIL  - 03/11/18(火) 0:58 -

引用なし
パスワード
   エクセル97です。
“データ”というワークシートのA列に星座、B列に血液型、というデータが入っています。
こういう場合に、「てんびん座かつA型」の数を、別のシート”人数”のセルB2に表示させようとしたいのです。
それで、ワークシート関数のsumproduct関数を使って、以下のように作ったのですが、「型が一致しません」というメッセージがでます。
どうしたらよいでしょう?
Sub 人数表示()
  Dim seiza As Range
  Dim ti As Range
  Dim nm As Integer
  Set seiza = worksheets(“データ”).range(“b2:b102”)
Set ti = worksheets(“データ”).range(“A2:A102”)
  nm = Application.WorksheetFunction.SumProduct((seiza= "てんびん") * (ti = "A"))
  sheets(“人数”).range(“b2”) = nm
End Sub
よろしくお願いします。

【9084】Re:sumproductがうまくいきません2
回答  ichinose  - 03/11/18(火) 2:06 -

引用なし
パスワード
   ▼猛虎襲来 さん:
こんばんは。

>エクセル97です。
Excel2000で確認しましたが・・・・。


>こういう場合に、「てんびん座かつA型」の数を、別のシート”人数”のセルB2に表示させようとしたいのです。

>それで、ワークシート関数のsumproduct関数を使って、以下のように作ったのですが、「型が一致しません」というメッセージがでます。
>どうしたらよいでしょう?
>  nm = Application.WorksheetFunction.SumProduct((seiza= "てんびん") * (ti = "A"))
↑のコードでは、
seiza="てんびん"
が評価されませんね、つまり、配列が生成されません。

それで、
nm=WorksheetFunction.SumProduct(([b2:b102 = "てんびん"]), [a2:a102 = "A"])
としたら、配列として評価はされますが、配列の中身がTrueとFalseのためか
0しか返りません。
Helpに、「数値以外の配列要素は、0 であると見なされます。」とありました。

で、以下のコードだと答えを出してくれました。
'==================================================================
Sub 人数表示()
  Dim seiza As String
  Dim ti As String
  Dim nm As Integer
  seiza = Range("b2:b102").Address
  ti = Range("A2:A102").Address
  nm = Application.Evaluate("=SUMPRODUCT((" & seiza & "=""てんびん"")*(" & ti & "=""A""))")
  MsgBox nm
End Sub

「てんびん座かつA型」のichinoseでした。

【9119】Re:Re:sumproductがうまくいきません2
質問  猛虎襲来 E-MAIL  - 03/11/18(火) 22:16 -

引用なし
パスワード
   「てんびん座かつA型」のichinoseさん、ありがとうございました。
早速実行してみると、ちゃんとでました。
しかし、今度はその数値を他のワークシートで表示させようとするとなぜかできません。
何か、初歩的なミスのような気がするのですが…
そのコードは、
Sub 人数記入()
  Dim ti As String
  Dim seiza As String
  Dim nm As Integer
  ti = Worksheets("データ").Range("a2:a152").Address
  seiza = Worksheets("データ").Range("b2:b152").Address
  nm = Application.Evaluate("=SUMPRODUCT((" & ti & "=""A"")*(" & seiza & "=""てんびん""))")
  worksheets(“人数”).range(“b2”) = nm
End Sub
実際にb2に表示させる前に、MsgBoxにnmを表示させると、0になっています。
どうしてでしょう?
よろしくお願いします。

【9120】Re:Re:sumproductがうまくいきません2
回答  ichinose  - 03/11/18(火) 23:04 -

引用なし
パスワード
   ▼猛虎襲来 さん:
こんばんは。


>しかし、今度はその数値を他のワークシートで表示させようとするとなぜかできません。
>何か、初歩的なミスのような気がするのですが…
>そのコードは、

>Sub 人数記入()
>  Dim ti As String
>  Dim seiza As String
>  Dim nm As Integer
  ti = "データ!" & Range("a2:a152").Address
  seiza = "データ!" & Range("b2:b152").Address
'としてみて下さい。
>  nm = Application.Evaluate("=SUMPRODUCT((" & ti & "=""A"")*(" & seiza & "=""てんびん""))")
>  worksheets(“人数”).range(“b2”) = nm
>End Sub

尚、もし仮にブックが二つ以上開いていて、シート名「データ」の存在しないブックが
アクティブだったりすると、又正しい値が入ってきません。
その場合はブック名も指定しなければなりません。

Evaluateメソッドの()の中の文字列は、数式ですから、
セルに実際に同様の数式を入力して確認しながらコードを記述されると
よいですよ(と言ってもtiやseizaまでそのまま入力しないでね)。

確認してみて下さい。

【9122】できました!!
お礼  猛虎襲来 E-MAIL  - 03/11/19(水) 0:22 -

引用なし
パスワード
   ▼ichinose さん:
おかげさまで、ばっちりできました。
ありがとうございました。

【12976】SumProductがうまくいきません
質問  auxo  - 04/4/20(火) 17:27 -

引用なし
パスワード
   同じようなことで困っているのでこちらの続きで失礼いたします。

Excel2002使用です。

同じブック内の資料シート中C列に果物の名前の一覧があり、
DATAシート中A列には上記集計対象と同じ果物の名前があり、
H列にはA列の名前と対になるお店の名前が入っています。

資料シートの一覧で順番にDATAシート上から

「果物の名前 且 お店の名前のないもの」の件数

を集計したく、SUMPRODUCTを使って集計しようとしました。

ichinoseさんがご回答されていることを参考に作成してみたのですが、
「型が一致しません」となってしまいます。

Sub Test()
  Dim WS_Data As Worksheet
  Dim WS_In As Worksheet
  Dim TRange As Range '集計対象範囲
  Dim SfRange As String  '集計範囲1
  Dim By2Range As String '集計範囲2
  Dim SFB As String '集計名
  Dim Bcnt As Integer
  
  Set WS_Data = ThisWorkbook.Worksheets("DATA")
  Set WS_In = ThisWorkbook.Worksheets("資料")
  Set TRange = WS_In.Range(.Cells(2, 3), .Cells(.Rows.Count, 3).End(xlUp)) '資料範囲
  
  With WS_Data
    '集計範囲1
    SfRange = .Name & "!" & .Range(.Cells(3, 1), .Cells(.Rows.Count, 1).End(xlUp)).Address
    '集計範囲2
    By2Range = .Name & "!" & .Range(.Cells(3, 8), .Cells(.Rows.Count, 8).End(xlUp)).Address
    
    '集計
    For Each FERange In TRange
      SFB = FERange.Value
      Bcnt = Application.Evaluate( _
        "=SUMPRODUCT((" & SfRange & "=" & SFB & ")*(" & By2Range & "<>""""))")
        
      MsgBox Bcnt
    Next
    
  End With
End Sub

ウォッチ式で確認したところ、以下のようになっていました。
-----------------------------------
SfRange="DATA!$A$2:$A$105"
By2Range="DATA!$H$2:$H$105"
SFB="りんご"
-----------------------------------

ウォッチ式の内容を代入して試したところ、エラーは出ないのですが、
Bcntは0となってしまいました。

代入するSfRangeとBy2Rangeを以下のように""を外してみたところ、
正しい計算結果が得られました。
-----------------------------------
SfRange=DATA!$A$2:$A$105
By2Range=DATA!$H$2:$H$105
-----------------------------------

範囲については日々変わっていくので固定することが出来ません。
どうしたらいいかわかりますでしょうか。

よろしくお願いいたします。

【12978】Re:SumProductがうまくいきません
発言  ichinose  - 04/4/20(火) 17:48 -

引用なし
パスワード
   ▼auxo さん:
こんにちは。

>Sub Test()
>  Dim WS_Data As Worksheet
>  Dim WS_In As Worksheet
>  Dim TRange As Range '集計対象範囲
>  Dim SfRange As String  '集計範囲1
>  Dim By2Range As String '集計範囲2
>  Dim SFB As String '集計名
>  Dim Bcnt As Integer
>  
>  Set WS_Data = ThisWorkbook.Worksheets("DATA")
>  Set WS_In = ThisWorkbook.Worksheets("資料")
>  Set TRange = WS_In.Range(.Cells(2, 3), .Cells(.Rows.Count, 3).End(xlUp)) '資料範囲
>  
>  With WS_Data
>    '集計範囲1
>    SfRange = .Name & "!" & .Range(.Cells(3, 1), .Cells(.Rows.Count, 1).End(xlUp)).Address
>    '集計範囲2
>    By2Range = .Name & "!" & .Range(.Cells(3, 8), .Cells(.Rows.Count, 8).End(xlUp)).Address
>    
>    '集計
>    For Each FERange In TRange
>      SFB = FERange.Value
>      Bcnt = Application.Evaluate( _
>        "=SUMPRODUCT((" & SfRange & "=" & SFB & ")*(" & By2Range & "<>""""))")

'evaluate()の中ってセルに入れる数式と一緒ですよね?

'debug.print "=SUMPRODUCT((" & SfRange & "=" & SFB & ")*(" & By2Range & "<>""""))"

'なんてして確認してみて下さい。見せていただいたコードだと

'変数SFBには、「""りんご""」と文字列としてダブルコーテーションが入っていなければなりませんが・・・。

'もしくは、

'"=SUMPRODUCT((" & SfRange & "=""" & SFB & """)*(" & By2Range & "<>""""))"
'とするか・・・。


>        
>      MsgBox Bcnt
>    Next
>    
>  End With
>End Sub

実際に確認したわけではありません。Excel2002もHD取り替えれば環境はあるんですが、
現在、バックアップ中なもので・・・。
とりあえず、上記を確認してみて下さい。

【12985】Re:SumProductがうまくいきません
質問  auxo  - 04/4/20(火) 18:48 -

引用なし
パスワード
   ichinose さんお世話になります。

>'evaluate()の中ってセルに入れる数式と一緒ですよね?

Bcnt = Application.Evaluate( _
"=SUMPRODUCT((DATA!$A$2:$A$105=""りんご"")*(DATA!$H$2:$H$105<>""""))")

↑や、ワークシートのセルに以下の関数を入れてもうまくいきます。
※"りんご"を参照形式にしても大丈夫でした。
=SUMPRODUCT((DATA!$A$2:$A$105="りんご")*(DATA!$H$2:$H$105<>""))

Debug.Printを試した結果をご連絡いたします。
◆"=SUMPRODUCT((" & SfRange & "=" & SFB & ")*(" & By2Range & "<>""""))"
は「型が一致しません」

◆"=SUMPRODUCT((" & SfRange & "=""りんご"")*(" & By2Range & "<>""""))"
Bcnt=3 →(OK)
Debug.Print結果
=SUMPRODUCT((DATA!$A$2:$A$105="りんご")*(DATA!$H$2:$H$105<>""))

◆"=SUMPRODUCT((" & SfRange & "=" & """SFB""" & ")*(" & By2Range & "<>""""))"
Bcnt=0 →(NG)
Debug.Print結果
"=SUMPRODUCT((DATA!$A$2:$A$105="SFB")*(DATA!$H$2:$H$105<>""))"

できればExcel2000でもExcel2002でも動くようにしたいのですが・・・

よろしくお願いいたします。

【12989】【解決しました】SumProductがうまくいき...
お礼  auxo  - 04/4/20(火) 19:06 -

引用なし
パスワード
   すみません。自己解決しました。

"=SUMPRODUCT((" & SfRange & "=" & """" & SFB & """" & ")*(" & By2Range & "<>""""))"

にすると上手くいきました。
Excel2000でも試してみましたが大丈夫のようです。

Debug.Printは知らなかったので大変勉強になりました。
メカラウロコでした。

ありがとうございました。

また、何かありましたら宜しくお願いいたします。

11980 / 13646 ツリー ←次へ | 前へ→
ページ:  ┃  記事番号:
2610219
(SS)C-BOARD v3.8 is Free