こんにちはゲストさん。会員登録(無料)して質問・回答してみよう!

解決済みの質問

エクセル 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 しなくてはなりません。
併せて教えてください。
よろしくお願いします。

投稿日時 - 2010-05-27 03:07:43

QNo.5924816

すぐに回答ほしいです

質問者が選んだベストアンサー

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

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

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

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

投稿日時 - 2010-06-09 11:01:55

お礼

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

投稿日時 - 2010-06-09 23:15:40

ANo.7

このQ&Aは役に立ちましたか?

11人が「このQ&Aが役に立った」と投票しています

回答(7)

ANo.6

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

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

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

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

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

投稿日時 - 2010-06-07 18:59:36

補足

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

H列 =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,""))

I列 =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,""))

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

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

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

M列 =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,""))

N列 =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,""))

O列 =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


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

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

Excel2000使用です。

よろしくお願いします。

投稿日時 - 2010-06-08 00:55:21

ANo.5

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

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

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

投稿日時 - 2010-06-01 11:12:01

補足

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

Dim sNumber As String

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

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

投稿日時 - 2010-06-06 23:36:38

ANo.4

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

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

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

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

投稿日時 - 2010-05-30 06:31:30

補足

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

=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田中 となおし、作動したところ、三ヶ月分の作業時間(R列)の合計が、集計シートのすべての注番に表示され、もう一度ためすと

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

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

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

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

投稿日時 - 2010-06-01 01:34:36

ANo.3

ANo.1です。
あぁ、「作業時間」だからてっきりシリアル値だと思っていましたが実数なんですか。
それでしたら
Dim dRtn  As Date

Dim dRtn  As Double
に変えたうえで、B8:AY20のセル書式も標準に戻してください。
恐らくこれで大丈夫だと思います。

投稿日時 - 2010-05-29 00:30:06

補足

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

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

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

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

投稿日時 - 2010-05-30 02:20:44

ANo.2

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

投稿日時 - 2010-05-28 10:05:24

補足

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

投稿日時 - 2010-05-28 18:52:28

ANo.1

補足願います。

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

投稿日時 - 2010-05-27 10:26:06

補足

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

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

投稿日時 - 2010-05-27 22:21:44

あなたにオススメの質問