Excel VBA質問箱 IV

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

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


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

【13168】合計の算出法について ハルコ 04/4/25(日) 17:07 質問[未読]
【13171】Re:合計の算出法について Hirofumi 04/4/25(日) 21:11 回答[未読]
【13200】Re:合計の算出法について ハルコ 04/4/27(火) 15:45 質問[未読]
【13209】Re:合計の算出法について Hirofumi 04/4/27(火) 19:38 回答[未読]
【13252】Re:合計の算出法について ハルコ 04/4/28(水) 18:32 質問[未読]
【13256】Re:合計の算出法について Hirofumi 04/4/28(水) 23:58 回答[未読]
【13264】Re:合計の算出法について ハルコ 04/4/29(木) 14:37 質問[未読]
【13265】Re:合計の算出法について Hirofumi 04/4/29(木) 16:11 回答[未読]
【13266】Re:合計の算出法について ハルコ 04/4/29(木) 19:53 質問[未読]
【13273】Re:合計の算出法について Hirofumi 04/4/29(木) 21:16 回答[未読]
【13274】Re:合計の算出法について ハルコ 04/4/29(木) 22:03 お礼[未読]

【13168】合計の算出法について
質問  ハルコ  - 04/4/25(日) 17:07 -

引用なし
パスワード
   みなさん、こんばんわ。私は、まだ3ヶ月の初心者です。
仕事上、毎日送付されてくる表(エクセル:以下の表です)を
合計など、加工してくれるものを作っています。大体のところまで
できたのですが、どうしても分からないところがあります。
その表は、毎日、日別のデータが大量に送付されてくるのですが、
その日別のデータの合計を出すため、今まで手作業でやっていたのですが
かなりの時間を要し、簡単に日別の合計を出す方法を考えています。
もちろん、日別のデータの数や行数は、その日ごとに違います。

以下の表を参考にしますと、具体的には、日付がB列に表示され、これもエクセルの表を取得する日によって表示されるセルの場所は異なります。

レイアウトについては、日付の下の行から、その日付分の勘定項目(H列)、そして、それの金額(D列)が表示されており、これらが、各日付毎に分かれています。

合計の対象は、D列の各日別の金額を集計し、4月1日分ですとD5へ、4月2日ですとD10へそれぞれ表示させ、D11にそれらの総合計を計算させたいのです。さらに、わがまま言うと、それら日別の件数も併せて集計し、4月1日分はC5へ、4月2日分はC10へ計算させたいです。

変数等を使い、かなり試行錯誤したのですが、うまくいきません。
どなたか、考え方でも構いませんので、お教えていただけないでしょうか?

A_B_C_D_E_F_G_H_I_J_K_L_M_N_O_P_Q_R_S_T_U_V_W__  1_20040401______________________________
2____100_____KKKK_______________________
3____100_____LLLL_______________________
4____100_____MMMM________________________
5____________________________________
6_20040402_______________________________
7____200_____FFFF_______________________
8____200_____SSSS________________________
9____200_____QQQQ______________________  10____________________________________


--------------------------------------------------------------------------------

【13171】Re:合計の算出法について
回答  Hirofumi E-MAIL  - 04/4/25(日) 21:11 -

引用なし
パスワード
   >日付がB列に表示され、これもエクセルの表を取得する日によって表示されるセルの場所は異なります。

此れが気に成りますが?
善く理解していなくて、上手くいか無いかも?
取り合えず叩き台として

集計対象はActiveSheetとしています
以下を標準モジュールに記述してください

Option Explicit

Public Sub AddUp()

  Dim i As Long
  Dim j As Long
  Dim lngListTop As Long
  Dim lngListEnd As Long
  Dim vntData As Variant
  Dim vntSum(1) As Variant
  Dim vntSubSum(1) As Variant
  
  With ActiveSheet
    '仮に先頭行を設定
    lngListTop = 1
    'もし、先頭行B列が""なら
    If .Cells(lngListTop, "B").Value = "" Then
      '先頭行を取得
      lngListTop = .Cells(lngListTop, "B").End(xlDown).Row
    End If
    '最終行を取得
    lngListEnd = .Cells(65536, "D").End(xlUp).Row + 1
    '先頭行から最終行まで繰り返し
    For i = lngListTop To lngListEnd
      '現在行のB、C、D列の値を取得
      vntData = .Cells(i, "B").Resize(, 3).Value
      'もし、B列が""でD列が""なら(日付の終り)
      If vntData(1, 1) = "" And vntData(1, 3) = "" Then
        '小計を出力
        .Cells(i, "C").Resize(, 2).Value = vntSubSum
        '小計を計に加算
        For j = 0 To 1
          vntSum(j) = vntSum(j) + vntSubSum(j)
        Next j
      Else
        'もし、B列が""で無いなら(日付が有る場合)
        If vntData(1, 1) <> "" Then
          '小計をクリア
          For j = 0 To 1
            vntSubSum(j) = 0
          Next j
        Else
          '小計にD列値、及びカウントを加算
          vntSubSum(0) = vntSubSum(0) + 1
          vntSubSum(1) = vntSubSum(1) + vntData(1, 3)
        End If
      End If
    Next i
    '計を出力
    .Cells(i, "C").Resize(, 2).Value = vntSum
  End With
  
  Beep
  MsgBox "処理完了"
    
End Sub

【13200】Re:合計の算出法について
質問  ハルコ  - 04/4/27(火) 15:45 -

引用なし
パスワード
   ▼Hirofumi さん、ありがとうございます。
 ここまでご丁寧に回答がいただけて、大変、参考になります。
 これを活用させていただきます。
 
 それと、また少し教えていただきたいのですが。
 いただいた内容で、分からないところがあり、これから勉強しますが、
 以下の部分(2つです)について、その内容の意図と言いますか、意味を教えていただ けますでしょうか?

 度々の、質問で大変申し訳ありませんが、宜しくお願い致します。 


>    'もし、先頭行B列が""なら
>    If .Cells(lngListTop, "B").Value = "" Then
>      '先頭行を取得
>      lngListTop = .Cells(lngListTop, "B").End(xlDown).Row
>    End If


>      'もし、B列が""でD列が""なら(日付の終り)
>      If vntData(1, 1) = "" And vntData(1, 3) = "" Then
>        

【13209】Re:合計の算出法について
回答  Hirofumi E-MAIL  - 04/4/27(火) 19:38 -

引用なし
パスワード
   >以下の部分(2つです)について、その内容の意図と言いますか、
>意味を教えていただ けますでしょうか?

    'もし、先頭行B列が""なら
    If .Cells(lngListTop, "B").Value = "" Then
      '先頭行を取得
      lngListTop = .Cells(lngListTop, "B").End(xlDown).Row
    End If

これは、
>日付がB列に表示され、これもエクセルの表を取得する日によって表示されるセルの場所は異なります。
との事なので、Listの先頭、要は最初にに日付が出てくる行位置を探しています
ただし、日付かどうかは判断していませんB列のセルが""かどうかで判断しています


      'もし、B列が""でD列が""なら(日付の終り)
      If vntData(1, 1) = "" And vntData(1, 3) = "" Then
        '小計を出力
        .Cells(i, "C").Resize(, 2).Value = vntSubSum
        '小計を計に加算
        For j = 0 To 1
          vntSum(j) = vntSum(j) + vntSubSum(j)
        Next j

の意味は、セルを直接参照すると遅く成るので、
このコードでは一度セルの値を1行づつ配列に読みこんでいます
其の時、vntData(1, 1)がB列の値で、vntData(1, 3)がD列の値と成ります
同一行で、B列が""で、D列が""の位置は、1日付のデータの終りを表しています
よって、そこが出てきたら、小計を出力しています

【13252】Re:合計の算出法について
質問  ハルコ  - 04/4/28(水) 18:32 -

引用なし
パスワード
   ▼Hirofumi さん,こんにちは。
ご回答ありがとうございました。ご教授の内容をもとに、私のファイルへ当てはめ、少し、加工しましたが、うまくいきません。以下の様な、内容ではまずいのでしょうか?ちなみに、以前、ご紹介しましたエクセルの表を若干、加工した表を使います。申し訳ありませんが、またのご教授をお願い致します。

・1〜3行は文字列(**で入力しておきます。)が、4〜5行は空白、6行目は 項目名(B〜K列、入力は省略してます。)です。
・前回ご紹介したエクセルの表の日付(20040402など)を、その対象となるデータ のB列へ貼り付けています。(後で、オートフィルタをかけるためです。)
・A列7行目以降は、項目名になります。
・集計する列は、F〜H列の数値であり、小計をF〜H列の10行目と16行目に、合 計をF〜H列の17行目に表示させ、なおかつ、件数の小計もE列10行目と16行目 に、合計をE列16行目に集計させます。
・前述で説明しましたとおり、サンプル表は、1〜3行目の文字列、B列の日付、 F〜H列の数値だけ入力しています。

A_B_C_D_E_F_G_H_I_J_K_L_M_N_O_P_Q_R_S_T_U_V_W__  1***______________________________
2***_______________________________
3***________________________________
4_________________________________
5____________________________________
6___________________________________
7_20040401__100_10_1000________________________
8_20040401__200_20_2000________________________
9_20040401__300_30_3000____________________    10____________________________________11____________________________________12_20040402__100_10_1000________________________13_20040402__200_20_2000________________________14_20040402__300_30_3000________________________15_20040402__400_40_4000________________________16________________________________17_____________________________________________


Option Explicit

Public Sub AddUp()

  Dim i As Long
  Dim j As Long
  Dim lngListTop As Long
  Dim lngListEnd As Long
  Dim vntData As Variant
  Dim vntSum(1) As Variant
  Dim vntSubSum(1) As Variant
 
  With ActiveSheet
    '仮に先頭行を設定
    lngListTop = 4
    'もし、先頭行B列が""なら
    If .Cells(lngListTop, "A").Value = "" Then
      '先頭行を取得
      lngListTop = .Cells(lngListTop, "A").End(xlDown).Row
    End If
    '最終行を取得
    lngListEnd = .Cells(65536, "E").End(xlUp).Row + 1
    '先頭行から最終行まで繰り返し
    For i = lngListTop To lngListEnd
      '現在行のA、F、G、H列の値を取得
      vntData = .Cells(i, "A").Resize(, 7).Value
      'もし、A列が""でF列が""なら(日付の終り)
      If vntData(1, 1) = "" Then
        '小計を出力
        .Cells(i, "F").Resize(, 3).Value = vntSubSum
        '小計を計に加算
        For j = 0 To 1
          vntSum(j) = vntSum(j) + vntSubSum(j)
        Next j
      Else
        'もし、A列が""で無いなら(日付が有る場合)
        If vntData(1, 1) <> "" Then
          '小計をクリア
          For j = 0 To 1
            vntSubSum(j) = 0
          Next j
        Else
          '小計にF列値、G列値、H列値及びカウントを加算
          vntSubSum(0) = vntSubSum(0) + 1
          vntSubSum(1) = vntSubSum(1) + vntData(1, 5)
          vntSubSum(2) = vntSubSum(2) + vntData(1, 6)
          vntSubSum(3) = vntSubSum(3) + vntData(1, 7)
        End If
      End If
    Next i
    '計を出力
    .Cells(i, "E").Resize(, 4).Value = vntSum
  End With
 
  Beep
  MsgBox "処理完了"
  
End Sub

【13256】Re:合計の算出法について
回答  Hirofumi E-MAIL  - 04/4/28(水) 23:58 -

引用なし
パスワード
   最初の質問の表とデータのイメージが大幅の変更されている為
前のコードでは使い物に成りません

確認します
データの先頭(日付の始まる先頭行)は必ず7行目から始まるとします
一つの日付の終りは、2行の空の行が入るとします
F〜H列にデータが有る場合は、必ずB列に日付がはいるとします

この形でコードを作ると以下の様に成ります

Option Explicit

Public Sub AddUp2()

  Dim i As Long
  Dim j As Long
  Dim lngListTop As Long
  Dim lngListEnd As Long
  Dim vntData As Variant
  Dim vntSum(3) As Variant
  Dim vntSubSum(3) As Variant
  Dim blnCalc As Boolean
  
  With ActiveSheet
    '集計開始の先頭行を設定
    lngListTop = 7
    '最終行を取得
    lngListEnd = .Cells(65536, "B").End(xlUp).Row + 1
    '先頭行から最終行まで繰り返し
    For i = lngListTop To lngListEnd
      '現在行のB〜H列の値を取得
      vntData = .Cells(i, "B").Resize(, 7).Value
      'もし、B列が""なら(日付の終り)
      If vntData(1, 1) = "" Then
        If blnCalc Then
          '小計を出力
          .Cells(i, "E").Resize(, 4).Value = vntSubSum
          '小計を計に加算、小計をクリア
          For j = 0 To 3
            vntSum(j) = vntSum(j) + vntSubSum(j)
            vntSubSum(j) = 0
          Next j
          '集計終了
          blnCalc = False
        End If
      Else
        'もし、B列が""で無いなら(日付が有る場合)
        If vntData(1, 1) <> "" Then
          '集計開始
          blnCalc = True
          '小計にF〜H列値、及びカウントを加算
          vntSubSum(0) = vntSubSum(0) + 1
          For j = 1 To 3
            vntSubSum(j) = vntSubSum(j) + vntData(1, 4 + j)
          Next j
        End If
      End If
    Next i
    '計を出力
    .Cells(i, "E").Resize(, 4).Value = vntSum
  End With
  
  Beep
  MsgBox "処理完了"
    
End Sub

【13264】Re:合計の算出法について
質問  ハルコ  - 04/4/29(木) 14:37 -

引用なし
パスワード
   ありがとうございました。
試してみましたが、以下の部分でエラーが出てしまいます。
エラー内容は「型が一致しません」です。
どうしてなんでしょうか?

>         For j = 0 To 3
>           vntSum(j) = vntSum(j) + vntSubSum(j)  ←ここです
>           vntSubSum(j) = 0
>         Next j

【13265】Re:合計の算出法について
回答  Hirofumi E-MAIL  - 04/4/29(木) 16:11 -

引用なし
パスワード
   >試してみましたが、以下の部分でエラーが出てしまいます。
>エラー内容は「型が一致しません」です。
>どうしてなんでしょうか?
>
>>         For j = 0 To 3
>>           vntSum(j) = vntSum(j) + vntSubSum(j)  ←ここです
>>           vntSubSum(j) = 0
>>         Next j

変ですね、其の部分で「型が一致しません」のエラーは考え難いのですが?
以下の部分では、エラーが出る可能性は有ります

          '小計にF〜H列値、及びカウントを加算
          vntSubSum(0) = vntSubSum(0) + 1
          For j = 1 To 3
            vntSubSum(j) = vntSubSum(j) + vntData(1, 4 + j) ←この部分
          Next j

これが出る理由は、F〜H列の金額部分に数値では無く文字列が入っている場合に起きます
対策は、金額部分に文字列を入れない様にするか
そのセルを無視する為、以下の様にします

          '小計にF〜H列値、及びカウントを加算
          vntSubSum(0) = vntSubSum(0) + 1
          For j = 1 To 3
            vntSubSum(j) = vntSubSum(j) _
                    + Val(vntData(1, 4 + j))
          Next j

【13266】Re:合計の算出法について
質問  ハルコ  - 04/4/29(木) 19:53 -

引用なし
パスワード
   ▼Hirofumi さん、こんばんは。
 実は、思い当たる部分あります。前回のエクセルの表を使った場においては、グループ1.(20040401に対応するデータの部分)とグループ2.(20040402に対応するデータの部分)のそれぞれの小計・件数を算出し、なおかつそれらの合計を出すというものでした。仕事でその表を入手した時、既に、グループ1.のF・H列は各セルに金額が出ているのですが、G列は全てのセルに金額が出ない場合もあります。(出ない場合は、全て空白になります。)又、グループ2.でも、F列は金額が全てのセル出るのですが、G・H列は出る場合もあり、出ない場合もあります。(出ない場合は、0ではなく空白になります。)ちなみに、F〜H列の関係は、F+G=Hで、つまり、H列のセルは、F・G列のセルの合計となります。ところが、その合計が出ていません。これが起因しているのでしょうか?

【13273】Re:合計の算出法について
回答  Hirofumi E-MAIL  - 04/4/29(木) 21:16 -

引用なし
パスワード
   ▼ハルコ さん:
>▼Hirofumi さん、こんばんは。
> 実は、思い当たる部分あります。前回のエクセルの表を使った場においては、グループ1.(20040401に対応するデータの部分)とグループ2.(20040402に対応するデータの部分)のそれぞれの小計・件数を算出し、なおかつそれらの合計を出すというものでした。仕事でその表を入手した時、既に、グループ1.のF・H列は各セルに金額が出ているのですが、G列は全てのセルに金額が出ない場合もあります。(出ない場合は、全て空白になります。)又、グループ2.でも、F列は金額が全てのセル出るのですが、G・H列は出る場合もあり、出ない場合もあります。(出ない場合は、0ではなく空白になります。)ちなみに、F〜H列の関係は、F+G=Hで、つまり、H列のセルは、F・G列のセルの合計となります。ところが、その合計が出ていません。これが起因しているのでしょうか?

まず、エラーの出ている場所は、どの部分ですか?
私の指摘した部分ですか?
ただ、セルが本当の空白なら、試して見ましたが0として計算しています
また、私の指摘した部分なら、前回の回答の様にコードを修正して試して見ましたか?

【13274】Re:合計の算出法について
お礼  ハルコ  - 04/4/29(木) 22:03 -

引用なし
パスワード
   ▼Hirofumi さん、こんばんは。
 ちょっと焦ってしまい、間違って入力しておりました。
 Hirofumiさんから、ご指示がありました内容を
 確実に入力したところ、うまくいきました。大変失礼しました。
 色々と、度重なるご教授、ありがとうございました。
 大変勉強になりました。今後とも、精進させていただきます。

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