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


スポンサードリンク

問題    topへ

解答例    topへ

問題1の解答例    topへ


B C D E F G
2 販売先 商品名 販売額   販売先 販売額合計
3 井上商事 りんご 52,000 上田青果 245,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 test00()
      Cells(3, 7).Value = _
          Application.WorksheetFunction.SumIf(Range("B3:B10"), Range("F3"), Range("D3:D10"))
      Cells(3, 7).NumberFormatLocal = "#,##0"
    End Sub
  2. For〜Next で条件に一致する行の値を合計する例です。
    Sub test01()
      Dim goukei As Double
      Dim i As Long
        For i = 3 To 10
          If Cells(i, 2).Value = Cells(3, 6).Value Then
            goukei = goukei + Cells(i, 4).Value
          End If
        Next i
        Cells(3, 7).Value = goukei
        Cells(3, 7).NumberFormatLocal = "#,##0"
    End Sub
  3. 元データをいったん配列に読み込んでから、For〜Next で条件に一致する行の値を合計する例です。
    上記Bの例より処理速度が早くなります。
    Sub test01a()
      Dim goukei As Double
      Dim i As Long
      Dim myVal, myKey As String
        ' ---元データを配列に格納
        myVal = Range("B3:D10").Value
        myKey = Cells(3, 6).Value
        For i = 1 To UBound(myVal, 1)
          If myVal(i, 1) = myKey Then
            goukei = goukei + myVal(i, 3)
          End If
        Next i
        Cells(6, 10).Value = goukei
        Cells(6, 10).NumberFormatLocal = "#,##0"
    End Sub
  4. Dictionaryオブジェクトを利用した例です。
    For〜Nextではデータ数が多くなると処理に時間がかかりますので、Dictionaryオブジェクトを利用すると短時間での処理ができます。
    Sub test02()
      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, 1) = Empty Then
            If Not myDic.exists(myVal(i, 1)) Then
              '---新たなkeyの時はkeyとitemを追加します
              myDic.Add myVal(i, 1), myVal(i, 3)
            Else
              '---すでに存在しているkeyの時はitemを加算します
              myDic(myVal(i, 1)) = myDic(myVal(i, 1)) + myVal(i, 3)
            End If
          End If
        Next
        ' ---一致データの合計値を書き出す
        Cells(3, 7).Value = myDic(Range("F3").Value)
        Cells(3, 7).NumberFormatLocal = "#,##0"

        Set myDic = Nothing
    End Sub

問題2の解答例    topへ


B C D E F G H
2 販売先 商品名 販売額   販売先 商品名 販売額合計
3 井上商事 りんご 52,000 上田青果 りんご 145,000
4 上田青果 りんご 65,000
5 井上商事 みかん 78,000
6 上田青果 みかん 43,000
7 井上商事 りんご 45,000
8 上田青果 バナナ 57,000
9 井上商事 バナナ 49,000
10 上田青果 りんご 80,000
  1. ワークシート関数SUMIFを利用した例です。(Excel2007以降で使用可能です)
    Sub test10()
      Cells(3, 8).Value = Application.WorksheetFunction. _
        SumIfs(Range("D3:D10"), Range("B3:B10"), Range("F3"), Range("C3:C10"), Range("G3"))
      Cells(3, 8).NumberFormatLocal = "#,##0"
    End Sub
  2. For〜Next で条件に一致する行の値を合計する例です。
    Sub test11a()
      Dim goukei As Double
      Dim i As Long
      Dim myVal, myKey As String
        myVal = Range("B3:D10").Value
        myKey = Cells(3, 6).Value & "_" & Cells(3, 7).Value

        For i = 1 To UBound(myVal, 1)
          If myVal(i, 1) & "_" & myVal(i, 2) = myKey Then
            goukei = goukei + myVal(i, 3)
          End If
        Next i

        Cells(6, 12).Value = goukei
        Cells(6, 12).NumberFormatLocal = "#,##0"
    End Sub
  3. 元データをいったん配列に読み込んでから、For〜Next で条件に一致する行の値を合計する例です。
    上記Bの例より処理速度が早くなります。
    Sub test11()
      Dim goukei As Double
      Dim i As Long

        For i = 3 To 10
          If Cells(i, 2).Value = Cells(3, 6).Value And Cells(i, 3).Value = Cells(3, 7).Value Then
            goukei = goukei + Cells(i, 4).Value
          End If
        Next i

        Cells(3, 8).Value = goukei
        Cells(3, 8).NumberFormatLocal = "#,##0"
    End Sub
  4. Dictionaryオブジェクトを利用した例です。
    Sub test12()
      Dim myDic As Object, myKey, myItem
      Dim myVal, myVal2, myVal3
      Dim i As Long
        Set myDic = CreateObject("Scripting.Dictionary")
        ' ---元データを配列に格納
        myVal = Range("B3:D10").Value
        ' ---myDicへデータを格納
        For i = 1 To UBound(myVal, 1)
          myVal2 = myVal(i, 1) & "_" & myVal(i, 2)
          If Not myVal2 = "_" Then
            If Not myDic.exists(myVal2) Then
              myDic.Add myVal2, myVal(i, 3)
            Else
              myDic(myVal2) = myDic(myVal2) + myVal(i, 3)
            End If
          End If
        Next
        ' ---Key,Itemの書き出し
        Cells(3, 8).Value = myDic(Range("F3").Value & "_" & Range("G3").Value)
        Cells(3, 8).NumberFormatLocal = "#,##0"

        Set myDic = Nothing
    End Sub

スポンサードリンク



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