エクセル VBA ワークシート関数をマクロで使う？

質問

エクセル　VBA　ワークシート関数をマクロで使う？
まず、人名・月ごとの、その日にどの注番の仕事を何時間したかという表があります。
シート名は　年.月氏名　で　セルF4～F63に注番、R4～R63に作業時間です。

次に、注番ごとにかかった作業時間や経費を求める表を別シートに作りました。
B4～AY4 に注番、A8～A20に氏名
B8～AY20に　注番、氏名ごとの3ヶ月（今月から先先月）の作業合計時間をマクロで作りたいのです。
最初は、A8～A20にシート名を入力し、セルB8の例ですが、計算式
=IF($A$8="","",IF(B4="","",SUMIF(INDIRECT($A$8&"!$F$4:$F$63"),B4,INDIRECT($A$8&"!$R$4:$R$63"))))
としていたのですが、メモリ不足などの問題もあり、上記のようにマクロで作成したいと思いますが、初心者なので、構文が浮かびません。

Sub 作業時間計算()
'
Dim シート名 As String
Dim 氏名 As String
Dim i As Integer
Dim j As Integer

シート名 = Year(Date) & "." & Month(Date) & 氏名
氏名 = ActiveSheet.Range("A", i).Value

For i = 8 To 20
For j = 2 To 51

Cells(8, j).Value =

Next j
Next i

End Sub

また、3ヶ月分を合算するのは、シート名の　Month(Date)　を　-1、-2　とすればよいのかと思うのですが、今月が、1月や2月のとき、Year(Date)　も　-1　しなくてはなりません。
併せて教えてください。
よろしくお願いします。

回答No.7

mt2008 · ベストアンサー

IF文で空白文字列にしているんですね。
本当の空白の場合、Excelでは0として扱いますが、空白文字列はあくまでも文字列です。そのまま演算しようとすると「型が違います」と怒られます。

コードを以下の様に変えて見てください。
この式なら、文字列を0として扱ってくれるます。ただし、文字列で数値を入れていた場合も0として扱います（まぁ、提示された式を見る限りその心配は無いと思いますが）。

sFormula = "SUMPRODUCT(('" & シート名 & "'!F4:F63=""" & sNumber & """)*('" & シート名 & "'!R4:R63))"
↓
sFormula = "SUMPRODUCT(('" & シート名 & "'!F4:F63=""" & sNumber & """)*1,('" & シート名 & "'!R4:R63))"

お礼

できました！！！！ありがとうございました。
もう、超感激です（涙）
今まで丁寧にご教示いただきまして、本当にありがとうございました！！！
m(_　_)mm(_　_)mm(_　_)mm(_　_)mm(_　_)m

回答No.6

mt2008

数値じゃないと駄目な理由は、数値として値が一致するものだけを合計しているからです。
注番はアルファベット混じりの文字列なんですね。
それでは、次のように変更してください。文字列の比較をします。

sFormula = "SUMPRODUCT(('" & シート名 & "'!F4:F63=" & sNumber & ")*('" & シート名 & "'!R4:R63))"
↓
sFormula = "SUMPRODUCT(('" & シート名 & "'!F4:F63=""" & sNumber & """)*('" & シート名 & "'!R4:R63))"

変数sNumberを文字列として宣言していて誤解を招いたみたいですが、これは単なる癖みたいなもので、セルに入っているものをとりあえず突っ込むにはintやlongよりもvariantやstringのほうが使い勝手が良いのでやっています。

また、集計はIF文を使った演算結果であっても問題は無いはずです。
どんな式を使っていますか？可能ならその式を提示してください。

補足

ありがとうございます。長くなりますがよろしくお願いします。

Ｈ列　=IF(D4="","",IF(OR(AND(C4<$AF$6,D4<=$AF$6),
　　　　　　　　　　　AND(C4>=$AF$7,D4<=$AG$6),
　　　　　　　　　　　AND(C4>=$AG$7,D4<=$AH$6),
　　　　　　　　　　　AND(C4>=$AH$7,D4<=$AI$7),
　　　　　　　　　　　AND(C4>=$AJ$6,D4>$AJ$6,D4<=$AJ$7)),D4-C4,""))

Ｉ列　=IF(D4="","",IF(OR(AND(C4<=$AF$6,D4<=$AG$6,D4>$AF$6),
　　　　　　　　　AND(C4>=$AF$7,C4<=$AG$7,D4>$AI$7,D4<=$AJ$7)),D4-C4-"1：00"*1,""))

Ｊ列　=IF(D4="","",IF(AND(C4<=$AF$6,D4>=$AG$7,D4<=$AH$6),D4-C4-"1:15"*1,""))

Ｋ列　=IF(D4="","",IF(AND(C4<=$AF$6,D4<=$AI$7,D4>=$AH$7),D4-C4-"1:45"*1,""))

Ｌ列　=IF(D4="","",IF(AND(C4<$AF$6,D4>$AI$7,D4<=$AJ$7),D4-C4-"2:00"*1,""))

Ｍ列　=IF(D4="","",IF(OR(AND(C4>=$AF$7,C4<=$AG$6,D4>=$AG$7,D4<=$AH$6),
　　　　　　　　AND(C4>=$AH$7,C4<=$AI$7,D4>=$AJ$6,D4<=$AJ$7)),D4-C4-"0:15"*1,""))

Ｎ列　=IF(D4="","",IF(OR(AND(C4>=$AF$7,C4<=$AG$6,D4>=$AH$7,D4<=$AI$7),
　　　　　　　　　AND(C4>=$AG$7,C4<$AH$6,D4>=$AJ$6)),D4-C4-"0:45"*1,""))

Ｏ列　=IF(D4="","",IF(AND(C4>=$AG$7,C4<=$AH$6,D4>=$AH$7,D4<=$AI$7),D4-C4-"0:30"*1,
　　　　　　　　　　IF(AND($AI$6<=D4,D4<=$AI$7,$AE$7<=C4,C4<$AH$7,E4="C-50"),D4-C4,
　　　　　　　　　　IF(AND($AH$6<D4,D4<=$AH$7,$AG$7<=C4,C4<$AE$7),D4-C4-(D4-$AE$7),
　　 　　　　　　　IF(AND($AI$6<=D4,D4<=$AI$7,$AH$6<=C4,C4<$AH$7),D4-C4-($AH$7-C4),"")))))

Q列　=IF(D4="","",SUM(H4:P4)*24)
R列　=IF(Q4="","",IF(Q4<0.01,0,Q4))

　　　　　　　　　　　AD AE　　　　AF AH　　　　　AI 　AJ　　　　　AK
　　　　　定時　　休憩　　　休憩　　休憩　　　普通残業　深夜残業　　早朝勤務
6　　開始時間　　8:00　　　12:00　　　15:00　　17:00　　　17:30　　　　22:15　　　　　3:00
7　　終了時間　　17:00　　13:00　　　15:15　　　17:30　　　22:00　　　　27:00　　　　8:00

という計算式が入っています。Ｒ列は、Q列のままだとなぜかたまに小数点のすごい数字が出るので、こんな式にしてあります。

前回も述べたように、このままでマクロを動作させると、型が一致しません　とエラーがでます。
試しに　「形式を選択して貼り付け」　「値」　で、貼り付けてみましたが、同じ結果です。
また、「セルの書式設定」で標準や、数列に変更しても同じ結果で、セルのデータをすべて消去し、新しく数字を入力した場合のみ、正しく計算されました。

Excel2000使用です。

よろしくお願いします。

回答No.5

mt2008

1度は正しく動いた……って事ですかね？

No.2で書いたように、このコードは「手抜き」版なので、集計用シートがアクティブじゃ無いと正しい動きをしないとか、注番に数字以外の文字列が入ると駄目とか色々制限があります。
…と、言い訳をしておいて。

VBE画面で[F8]キーを押して1ステップづつマクロを実行する「ステップ実行」を行い、エラーになる行での変数　dRtn　と　sFormula　の値を確認してください。

補足

何度もすみません。一週間がかりでやっと解決しました。
解決というか、何故動かないかわかりました。
実は、注番にアルファベットが入っているからなのです。

Dim sNumber As String

と、文字列と宣言しているので、深く考えていなかったのですが、どうして数字じゃないとだめなのですか？
駄目な理由と、アルファベットを使う方法をご教示いただけるとうれしいです。

あと、R行には、休憩時間を引くために、IF関数が使われています。
ただの数字や、ためしにSUM関数をいれてみたら、正常に計算されましたが、IF関数が入っていると、「型が一致しません」とエラーがでます。
IF関数だと計算されない理由と、計算させる方法があれば、併せてご教示よろしくおねがいします。
m（_　_）mお手数かけます・・・

回答No.4

mt2008

う～ん、あとは注番が各シートは文字列で「001」と入力されているのに、集計シートでは「1」とだけ入力してセル書式で「001」にしている等で統一がとれていないとか……。

集計用シートの適当なセルに以下の式を入れるとどうなります？
＃シート名は存在する物に変えてください

=SUMPRODUCT(('2010.05田中'!F4:F63=" & Range("B4")& ")*('2010.05田中'!R4:R63))

あと、こちらで確認した時のイメージも添付します。
こちらで何か勘違いしている箇所があれば捕捉してください。

補足

何度もありがとうございます。
集計用のシートのセルに

=SUMPRODUCT(('2010.05田中'!F4:F63=" & Range("B4")& ")*('2010.05田中'!R4:R63))

を入力すると、B4のところが反転してエラーになりました。

=SUMPRODUCT(('2010.05田中'!$F$4:$F$63=B4)*1,'2010.05田中'!$R$4:$R$63)

と入力すると、正しく計算されました。

イメージの添付ありがとうございました。
おかげで、私の間違いが、1箇所発見できまして、シート名が、2010.5田中となっていました。
それを、　2010.05田中　となおし、作動したところ、三ヶ月分の作業時間(Ｒ列)の合計が、集計シートのすべての注番に表示され、もう一度ためすと

dRtn = dRtn + Evaluate(sFormula) '三か月分を合算

のところで、型が一致しません。というエラーになりました。
その後何度ためしても、同じエラーがでます。

添付していただいたイメージは、完璧です。わたしの表とまったく同じです。
ちなみに、添付していただいたイメージのように、別ブックに簡易的な表を作り試してみましたが、
最初のように、0　が表示されます。
なぜなのか、わからない状態です。(T T)

もしお分かりでしたら、ご指摘おねがいします。m(_　_)m

回答No.3

mt2008

ANo.1です。
あぁ、「作業時間」だからてっきりシリアル値だと思っていましたが実数なんですか。
それでしたら
Dim dRtn　　As Date
↓
Dim dRtn　　As Double
に変えたうえで、B8:AY20のセル書式も標準に戻してください。
恐らくこれで大丈夫だと思います。

補足

回答ありがとうございます。試してみましたが、今度は　0　と表示されました。
なんとか自力でやってみようと、いろいろ調べてみて

sFormula = "SUMPRODUCT(('" & シート名 & "'!F4:F63=" & 工番 & ")*1,('" & シート名 & "'!R4:R63))"

としてみたり、3ヶ月合算のところを消してみたりしましたが、やっぱり、　0　だったり、動作しなかったりしました。

F4からの列と、B4からの行の注番の表記も半角全角の間違いはありません。
他に原因は考えられますか？
よろしくお願いします。

回答No.2

mt2008

ANo.1です。
補足確認しました。
ちょっと手抜きですがこんな感じに作って見ました。
ワークシート関数のSUMPRODUCTを、今月を含む3か月分のシートで行った合計を表示させています。
試して見てください。
なお、シート名は「YYYY.MM氏名（例：2010.05田中）」の、様になっていると仮定しています。違うようでしたら変更してください。
また、B8:AY20の書式はユーザ定義で「[h]:mm」にして置いてください。

Sub Sample()
　　Dim シート名 As String
　　Dim 氏名　　As String
　　Dim i　　 As Long
　　Dim j　　 As Long
　　Dim k　　 As Long
　　Dim m 　　As Long
　　Dim sNumber As String
　　Dim dRtn　　As Date
　　Dim sFormula　　As String

　　For i = 8 To 20
　　　　If ActiveSheet.Range("A" & i) = "" Then Exit For
　　　　氏名 = ActiveSheet.Range("A" & i).Value

　　　　For j = 2 To 51
　　　　　　If Cells(4, j) = "" Then Exit For
　　　　　　sNumber = Cells(4, j) '確認する注番

　　　　　　dRtn = 0
　　　　　　'三ヶ月分のシートをチェック
　　　　　　For m = 0 To -2 Step -1
　　　　　　　　シート名 = Format(DateAdd("M", m, Now()), "YYYY.MM") & 氏名
　　　　　　　　'シート名が存在するか確認
　　　　　　　　For k = 1 To ThisWorkbook.Sheets.Count
　　　　　　　　　　If シート名 = ThisWorkbook.Sheets.Item(k).Name Then
　　　　　　　　　　　　sFormula = "SUMPRODUCT(('" & シート名 & "'!F4:F63=" & sNumber & ")*('" & シート名 & "'!R4:R63))"
　　　　　　　　　　　　dRtn = dRtn + Evaluate(sFormula) '三か月分を合算
　　　　　　　　　　　　Exit For
　　　　　　　　　　End If
　　　　　　　　Next k
　　　　　　Next m
　　　　　　Cells(i, j) = dRtn
　　　　Next j
　　Next i
End Sub

補足

ありがとうございます。やってみました。
でも、すべて、　0：00　と表示されました。ひょっとして、R4～R63に入力されている時間が、シリアル値じゃなくて、実数なのが関係しているのでしょうか？
SUMPRODUCT　など、初めての関数だったので、本で調べてみましたが、0：00　と表示される原因は理解不能です。
ご指導お願いします。

回答No.1

mt2008

補足願います。

1.注番は毎月固定ですか？番号が増えたり減ったりしますか？
2.人名・月毎のシートでは注番が60個、まとめるシートでは50個になっています。
　注番の数は最大何個ですか？
3.まとめるシート注番は予め入力してある物と考えて良いのですか？それともシートから抜き出すのですか？

補足

補足します。
1.注番は、毎月変わります。売上を上げた月に、その注番の合計作業時間や経費を集計したいからです。
　（注番によって、作業員が作業した時間が、数時間だったり、複数人で2～3ヶ月だったりするので、　3か月分を足したいのです。）
2.人名・月毎のシートでは、60個になっているのは、もし一日に同じ注番の作業をしていれば、一ヶ月　の作業日数分の行で足りるのですが、一日に掛け持ちで、違う注番の作業をしたりするので、多め　に60行になっているのです。（説明不足でごめんなさい。ちなみにB列に日付、C列に作業開始時間、D列に　作業終了時間というふうになっています。）
　まとめるシートでは、50個になっているのは、売上を上げた月に集計するので、客先ごとに列を割　　り当てたので、空白列も含み50個になっています。
3.まとめるシートに注番は、4行目に予め入力してあります。

これで大丈夫でしょうか？
よろしくお願いします。