現状関数で求められる値をVBA化できません？

関数（I2）
=SUMPRODUCT(($A$2:$A$5>=$F$2)*($A$2:$A$5<=$H$2)*($B$2:$E$5=I1))
セルI2の値を求める記述を下記のようにしてみましたが動きません？

Dim kei As Long

kei = WorksheetFunction.SumProduct(Range("A2:A5" >= "F2") * Range("A2:A5" >= "H2") * Range("B2:G5" = "I1"))

Range("L2") = kei '表示する

また、右の表セルI2からK5までの時間別、品目別の個数を求めるにはどのように記述したらよろしいでしょうか？

ご教示いただけると助かります。
よろしくお願いいたします。

質問者からの補足コメント

• お考えいただきありがとうございます。
  
  １.A列の行は最大300件ほどになります。
  ２.A列の時刻は9:00～17:00の間で不定期です。
  ３.F列、H列は固定ですが行は9：00～17；00までの9行になります。
  ４,品物は全部で13品目
  アップル、バナナ、メロン、オレンジ、なし、スイカ、ぶどう、もも、マンゴ-、みかん、いちじく、あけび、イチゴになります。
  
  よろしくお願いいたします。

  No.1の回答に寄せられた補足コメントです。 補足日時：2021/03/04 23:52

• お考えいただきありがとうございます。
  
  4.品目は固定数ｏｒ最大品目数で、Ｉ１より右に事前に記載されてます。
  
  よろしくお願いいたします。

    補足日時：2021/03/05 07:24

No.8 回答者： めぐみん_ 回答日時： 2021/03/05 13:17

No.7です。

事前にＦ～Ｈ列には値が書き込まれていると言うのが前提です。

この回答へのお礼

詳細なご説明ありがとうございました。
勉強になります。

お礼日時：2021/03/05 17:37

No.7 回答者： めぐみん_ 回答日時： 2021/03/05 13:16

ご意見頂きました内容を基にしますと、

Dim r As Range

Set r = Cells(Rows.Count, "A").End(xlUp)

With Intersect(Range("F2", Cells(Rows.Count, "F").End(xlUp)).EntireRow, Range("I1", Cells(1, Columns.Count).End(xlToLeft)).EntireColumn)

.FormulaLocal = "=SUMPRODUCT((HOUR($A$2:" & r.Address & ")=HOUR($F2))*($B$2:" & r.Offset(, 4).Address & "=I$1))"
.Value = .Value

End With

Set r = Nothing

このような感じで出来ました。

この回答へのお礼

詳細なご説明ありがとうございました。
勉強になります。

お礼日時：2021/03/05 17:38

No.6 回答者： めぐみん_ 回答日時： 2021/03/05 11:21

数式でも『HOUR関数』は使えるので、

＞=SUMPRODUCT(($A$2:$A$5>=$F$2)*($A$2:$A$5<=$H$2)*($B$2:$E$5=I1))

最初の数式は

=SUMPRODUCT((HOUR($A$2:$A$5)=HOUR($F$2))*($B$2:$E$5=I1))

とかは無理なのかな？
後は相対参照・行数の変更等で修正し書き入れたい範囲は決まっているので、No.4様のご意見を参考に一気に数式を入れて値に変換すれば早いのかも。

No.5 ベストアンサー 回答者： tatsumaru77 回答日時： 2021/03/05 10:38

以下のマクロを標準モジュールに登録してください。

Option Explicit

Public Sub 時間別品目別個数算出()
Dim dicT As Object
Dim sh As Worksheet
Dim maxrow As Long
Dim maxcol As Long
Dim wrow As Long
Dim wcol As Long
Dim grow As Long
Dim gcol As Long
Dim key As String
Dim hh As Long
Set dicT = CreateObject("Scripting.Dictionary") ' 連想配列の定義
Set sh = ActiveSheet
maxrow = sh.Cells(Rows.Count, 1).End(xlUp).Row 'A列最終行を求める
maxcol = sh.Cells(1, Columns.Count).End(xlToLeft).Column '1行目の最終列を求める
sh.Range(sh.Cells(2, 9), sh.Cells(10, maxcol)).Value = "" '個数クリア
'品目記憶
For wcol = 9 To maxcol
dicT(sh.Cells(1, wcol).Value) = wcol
Next
'全行分繰り返す
For wrow = 2 To maxrow
hh = Hour(sh.Cells(wrow, "A").Value)
If hh < 9 Or hh > 17 Then
MsgBox ("時刻不正:" & sh.Cells(wrow, "A").Text)
Exit Sub
End If
'発注１～３分繰り返す
For wcol = 2 To 4
key = sh.Cells(wrow, wcol).Value
If key <> "" Then
If dicT.exists(key) = False Then
MsgBox ("品目不正:" & key)
Exit Sub
End If
'該当品目へ１加算
grow = hh - 7
gcol = dicT(key)
sh.Cells(grow, gcol).Value = sh.Cells(grow, gcol).Value + 1
End If
Next
Next
MsgBox ("完了")
End Sub

この回答へのお礼

詳細なご説明ありがとうございました。
コメントをもとに解読し勉強させていただきます。
無事意図した動きを構築でき大変助かりました！

お礼日時：2021/03/05 17:40

No.4 回答者： fujillin 回答日時： 2021/03/05 08:52

こんにちは

No2様のご指摘をよく考えた方がよろしいと思います。

＞Range("A2:A5" >= "F2")
だけでも、VBAとして意味不明ということになっていると思われます。

別法になりますが、VBA化できれば良いのであれば、計算する対象範囲をrngとして
　rng.FormulaLocal = 通常の関数式
　rng.Value = rng.Value
のような方法で、エクセルに計算してもらうのが簡単です。

この回答へのお礼

詳細なご説明ありがとうございました。
勉強になります。

お礼日時：2021/03/05 17:41

No.3 回答者： めぐみん_ 回答日時： 2021/03/05 08:24

No.2です。

あとはスマホになるのでＥｘｃｅｌでの作業は出来なくなります。
よってベテランさんに『お・ま・か・せ』になってしまいますが、初級レベルなジジィが見る限りでも既に情報は揃ってますし問題ないかなと。
３０分はかからないのでは？

この回答へのお礼

詳細なご説明ありがとうございました。
勉強になります。

お礼日時：2021/03/05 17:41

No.2 回答者： めぐみん_ 回答日時： 2021/03/05 06:44

＞４,品物は全部で13品目

は固定数ｏｒ最大品目数としても、Ｉ１より右に事前に記載されてますか？
それとも集計する際に入力されている項目を取得し並べる必要があるのでしょうか？

Ｆ～Ｈ列についても。

ＰＳ．
＞Range("A2:A5" >= "F2"
片方はRangeオブジェクトを用いているのに、相方は文字列"F2"って違うのかな？と思いました。（未検証ですが）
数式とごっちゃになってませんかね？

SumProduct(Range("A2:A5") >= Range("F2").Value)

なのかなぁ～と感じます。（初級者なので憶測ではありますが）

この回答へのお礼

詳細なご説明ありがとうございました。
勉強になります。

お礼日時：2021/03/05 17:41

No.1 回答者： tatsumaru77 回答日時： 2021/03/04 23:02

１．A列の行が増えることはないのですか？

（データは２～５行固定で良いのですか）
２．A列の時刻は変わらない前提で良いですか。
（もし、変わる場合、9時より前の時刻、13:00以降の時刻もありますか）
３．F列、H列の時刻は固定であることが前提で良いですか。
（範囲もF2：H5固定であり、行が追加されることはない）
４．品物は、アップル、バナナ、メロンの３つ固定でよいですか。
（オレンジなどが追加されることはない。）

この回答へのお礼

詳細なご説明ありがとうございました。
勉強になります。

お礼日時：2021/03/05 17:41