1. Home » 
  2. エクセル練習問題：目次 » 
  3. 条件付きの合計の計算2(VBA)

エクセル練習問題：条件付きの合計の計算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

スポンサードリンク

Home｜エクセル練習問題：目次｜条件付きの合計の計算2(VBA)

PageViewCounter
Counter
Since2006/2/27