スポンサードリンク | |
B | C | D | E | F | |
2 | 販売先 | 商品名 | 販売額 | りんごとみかんの販売額合計 | |
3 | 井上商事 | りんご | 52,000 | ||
4 | 上田青果 | りんご | 65,000 | ||
5 | 井上商事 | みかん | 78,000 | ||
6 | 上田青果 | みかん | 43,000 | ||
7 | 井上商事 | りんご | 45,000 | ||
8 | 上田青果 | バナナ | 57,000 | ||
9 | 井上商事 | バナナ | 49,000 | ||
10 | 上田青果 | りんご | 80,000 |
B | C | D | E | F | |
2 | 年月日 | 商品名 | 販売額 | 5月の総販売額 | |
3 | 2011/4/2 | りんご | 52,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 |
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 |
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 |
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 |
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 |
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 |
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 |
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 |
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 |
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 |
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 |
スポンサードリンク
PageViewCounter
Since2006/2/27