エクセル練習問題:条件付きの合計の計算2(VBA)


スポンサードリンク

問題    topへ

解答例    topへ

問題3の解答例    topへ


B C D E F
2 販売先 商品名 販売額   りんごとみかんの販売額合計
3 井上商事 りんご 52,000 363,000
4 上田青果 りんご 65,000
5 井上商事 みかん 78,000
6 上田青果 みかん 43,000
7 井上商事 りんご 45,000
8 上田青果 バナナ 57,000
9 井上商事 バナナ 49,000
10 上田青果 りんご 80,000
  1. ワークシート関数SUMIFを利用した例です。
    Sub test30()
      Cells(3, 6).Value = _
        Application.WorksheetFunction.SumIf(Range("C3:C10"), "りんご", Range("D3:D10")) _
        + Application.WorksheetFunction.SumIf(Range("C3:C10"), "みかん", Range("D3:D10"))

        Cells(3, 6).NumberFormatLocal = "#,##0"
    End Sub
  2. For〜Next で条件に一致する行の値を合計する例です。
    Sub test31()
      Dim goukei As Double
      Dim i As Long
        For i = 3 To 10
          If Cells(i, 3).Value = "りんご" Or Cells(i, 3).Value = "みかん" Then
            goukei = goukei + Cells(i, 4).Value
          End If
        Next i

        Cells(3, 6).Value = goukei
        Cells(3, 6).NumberFormatLocal = "#,##0"

    End Sub
  3. 元データをいったん配列に読み込んでから、For〜Next で条件に一致する行の値を合計する例です。
    上記Bの例より処理速度が早くなります。
    Sub test32()
      Dim goukei As Double
      Dim i As Long
      Dim myVal, myKey1 As String, myKey2 As String
        ' ---元データを配列に格納
        myVal = Range("B3:D10").Value
        myKey1 = "りんご"
        myKey2 = "みかん"

        For i = 1 To UBound(myVal, 1)
          If myVal(i, 2) = myKey1 Or myVal(i, 2) = myKey2 Then
            goukei = goukei + myVal(i, 3)
          End If
        Next i
        Cells(3, 6).Value = goukei
        Cells(3, 6).NumberFormatLocal = "#,##0"

    End Sub
  4. Dictionaryオブジェクトを利用した例です。
    For〜Nextではデータ数が多くなると処理に時間がかかりますので、Dictionaryオブジェクトを利用すると短時間での処理ができます。
    Sub test33()
      Dim myDic As Object, myKey, myItem
      Dim myVal
      Dim i As Long
        Set myDic = CreateObject("Scripting.Dictionary")
        ' ---元データを配列に格納
        myVal = Range("B3:D10").Value
        ' ---myDicへデータを格納
        For i = 1 To UBound(myVal, 1)
          If Not myVal(i, 2) = Empty Then
            If Not myDic.exists(myVal(i, 2)) Then
              '---新たなkeyの時はkeyとitemを追加します
              myDic.Add myVal(i, 2), myVal(i, 3)
            Else
              '---すでに存在しているkeyの時はitemを加算します
              myDic(myVal(i, 2)) = myDic(myVal(i, 2)) + myVal(i, 3)
            End If
          End If
        Next
        ' ---結果の書き出し
        Cells(3, 6).Value = myDic("りんご") + myDic("みかん")
        Cells(3, 6).NumberFormatLocal = "#,##0"

        Set myDic = Nothing
    End Sub

問題4の解答例    topへ


B C D E F
2 年月日 商品名 販売額 5月の総販売額
3 2011/4/2 りんご 52,000 145,000
4 2011/4/3 りんご 65,000
5 2011/4/4 みかん 78,000
6 2011/5/7 みかん 43,000
7 2011/5/8 りんご 45,000
8 2011/5/9 バナナ 57,000
9 2011/6/1 バナナ 49,000
10 2011/6/2 りんご 80,000
  1. 2011年5月という条件は「2011/5/1以上 かつ 2011/6/1未満」と考えるとSUMIFS関数が使えます。
    ワークシート関数SUMIFSを利用した例です。(Excel2007以降で使用可能です)
    Sub test40()
      Cells(3, 6).Value = Application.WorksheetFunction. _
        SumIfs(Range("D3:D10"), Range("B3:B10"), ">=2011/5/1", Range("B3:B10"), "<2011/6/1")
      Cells(3, 6).NumberFormatLocal = "#,##0"
    End Sub
  2. For〜Next で条件に一致する行の値を合計する例です。
    Sub test41()
      Dim goukei As Double
      Dim i As Long
        For i = 3 To 10
          If Cells(i, 2).Value >= DateSerial(2011, 5, 1) And Cells(i, 2).Value < DateSerial(2011, 6, 1) Then
            goukei = goukei + Cells(i, 4).Value
          End If
        Next i

        Cells(3, 6).Value = goukei
        Cells(3, 6).NumberFormatLocal = "#,##0"

    End Sub
  3. 元データをいったん配列に読み込んでから、For〜Next で条件に一致する行の値を合計する例です。
    上記Bの例より処理速度が早くなります。
    Sub test42()

      Dim goukei As Double
      Dim i As Long
      Dim myVal, myKey1 As Date, myKey2 As Date
        ' ---元データを配列に格納
        myVal = Range("B3:D10").Value
        myKey1 = "2011/5/1"
        myKey2 = "2011/6/1"

        For i = 1 To UBound(myVal, 1)
          If myVal(i, 1) >= myKey1 And myVal(i, 1) < myKey2 Then
            goukei = goukei + myVal(i, 3)
          End If
        Next i

        Cells(3, 6).Value = goukei
        Cells(3, 6).NumberFormatLocal = "#,##0"

    End Sub
  4. Dictionaryオブジェクトを利用した例です。
    • B列の日付から年と月を取り出して、 年_月 といった形式に変更してデータを処理しています。
    Sub test43()
      Dim myDic As Object
      Dim myVal
      Dim i As Long
      Dim myKey As String

      Set myDic = CreateObject("Scripting.Dictionary")
      ' ---元データを配列に格納
      myVal = Range("B3:D10").Value
      ' ---myDicへデータを格納
      For i = 1 To UBound(myVal, 1)
        myKey = Year(myVal(i, 1)) & "_" & Month(myVal(i, 1))
        If Not myKey = Empty Then
          If Not myDic.exists(myKey) Then
            '---新たなkeyの時はkeyとitemを追加します
            myDic.Add myKey, myVal(i, 3)
          Else
            '---すでに存在しているkeyの時はitemを加算します
            myDic(myKey) = myDic(myKey) + myVal(i, 3)
          End If
        End If
      Next
      ' ---結果の書き出し
      Cells(3, 6).Value = myDic("2011_5")
      Cells(3, 6).NumberFormatLocal = "#,##0"

      Set myDic = Nothing
    End Sub

スポンサードリンク



よねさんのExcelとWordの使い方エクセル練習問題:目次|条件付きの合計の計算2(VBA)