スポンサードリンク | |
B | C | D | E | |
2 | 商品 | 上半期 | 下半期 | 合計 |
3 | りんご | 5,800 | 2,790 | 8,590 |
4 | みかん | 4,580 | 4,680 | 9,260 |
5 | バナナ | 7,540 | 7,720 | 15,260 |
6 | 合計 | 17,920 | 15,190 | 33,110 |
Sub test_01() 'セルに数式を入力します Range("E3:E6").FormulaLocal = "=SUM(C3:D3)" Range("C6:D6").FormulaLocal = "=SUM(C3:C5)" '数式を値に変えます Range("E3:E6").Value = Range("E3:E6").Value Range("C6:D6").Value = Range("C6:D6").Value '表示形式を設定します Range("E3:E6").NumberFormatLocal = "#,##0" Range("C6:D6").NumberFormatLocal = "#,##0" End Sub |
Sub test_02() Dim i As Long 'E3:E5セルをワークシート関数を使って計算します For i = 3 To 5 Cells(i, 5).Value = Application.WorksheetFunction.Sum(Range(Cells(i, 3), Cells(i, 4))) Next i 'C6:E6セルをワークシート関数を使って先に計算します For i = 3 To 5 Cells(6, i).Value = Application.WorksheetFunction.Sum(Range(Cells(3, i), Cells(5, i))) Next i '表示形式を設定します Range("E3:E6").NumberFormatLocal = "#,##0" Range("C6:D6").NumberFormatLocal = "#,##0" End Sub |
Sub test_03() Dim i As Long, j As Long Dim goukei As Double 'E3〜E5へC列〜D列の足し算の結果を入力します For i = 3 To 5 Cells(i, 5).Value = Cells(i, 3).Value + Cells(i, 4).Value Cells(i, 5).NumberFormatLocal = "#,##0" Next i 'C6〜E6に足し算の結果を入力します For i = 3 To 5 goukei = 0 '3行目〜5行目の足し算をします For j = 3 To 5 goukei = goukei + Cells(j, i).Value Next j Cells(6, i).Value = goukei Cells(6, i).NumberFormatLocal = "#,##0" Next i End Sub |
Sub test_04() Dim i As Long, j As Long Dim x1 As Variant, x2 As Variant Dim ans1 As Variant, ans2 As Variant x1 = Range("C3:D5").Value ReDim ans1(1 To UBound(x1), 1 To 1) For i = LBound(x1) To UBound(x1) ans1(i, 1) = x1(i, 1) + x1(i, 2) Next i With Range("E3").Resize(UBound(x1)) .Value = ans1 .NumberFormatLocal = "#,##0" End With ' x2 = Range("C3:E5").Value ReDim ans2(1 To 1, 1 To UBound(x2, 2)) For i = LBound(x2, 2) To UBound(x2, 2) For j = LBound(x2) To UBound(x2) ans2(1, i) = ans2(1, i) + x2(j, i) Next j Next i With Range("C6").Resize(1, UBound(x2, 2)) .Value = ans2 .NumberFormatLocal = "#,##0" End With End Sub |
B | C | D | E | |
2 | 商品 | 上半期 | 下半期 | 平均 |
3 | りんご | 5,800 | 2,790 | 4,295 |
4 | みかん | 4,580 | 4,680 | 4,630 |
5 | バナナ | 7,540 | 7,720 | 7,630 |
6 | 平均 | 5,973 | 5,063 | 5,518 |
Sub test_11() 'セルに数式を入力します Range("E3:E6").FormulaLocal = "=AVERAGE(C3:D3)" Range("C6:D6").FormulaLocal = "=AVERAGE(C3:C5)" '数式を値に変えます Range("E3:E6").Value = Range("E3:E6").Value Range("C6:D6").Value = Range("C6:D6").Value '表示形式を設定します Range("E3:E6").NumberFormatLocal = "#,##0" Range("C6:D6").NumberFormatLocal = "#,##0" End Sub |
Sub test_12() Dim i As Long 'C6:D6セルをワークシート関数を使って先に計算します For i = 3 To 4 Cells(6, i).Value = Application.WorksheetFunction.Average(Range(Cells(3, i), Cells(5, i))) Next i 'E3:E6セルをワークシート関数を使って次に計算します For i = 3 To 6 Cells(i, 5).Value = Application.WorksheetFunction.Average(Range(Cells(i, 3), Cells(i, 4))) Next i '表示形式を設定します Range("E3:E6").NumberFormatLocal = "#,##0" Range("C6:D6").NumberFormatLocal = "#,##0" End Sub |
Sub test_13() Dim i As Long, j As Long Dim goukei As Double For i = 3 To 5 Cells(i, 5).Value = (Cells(i, 3).Value + Cells(i, 4).Value) / 2 Cells(i, 5).NumberFormatLocal = "#,##0" Next i For i = 3 To 5 goukei = 0 For j = 3 To 5 goukei = goukei + Cells(j, i).Value Next j Cells(6, i).Value = goukei / 3 Cells(6, i).NumberFormatLocal = "#,##0" Next i End Sub |
Sub test_14() Dim i As Long, j As Long Dim x1 As Variant, x2 As Variant Dim ans1 As Variant, ans2 As Variant x1 = Range("C3:D5").Value ReDim ans1(1 To UBound(x1), 1 To 1) For i = LBound(x1) To UBound(x1) ans1(i, 1) = (x1(i, 1) + x1(i, 2)) / 2 Next i With Range("E3").Resize(UBound(x1)) .Value = ans1 .NumberFormatLocal = "#,##0" End With ' x2 = Range("C3:E5").Value ReDim ans2(1 To 1, 1 To UBound(x2, 2)) For i = LBound(x2, 2) To UBound(x2, 2) For j = LBound(x2) To UBound(x2) ans2(1, i) = ans2(1, i) + x2(j, i) Next j ans2(1, i) = ans2(1, i) / 3 Next i With Range("C6").Resize(1, UBound(x2, 2)) .Value = ans2 .NumberFormatLocal = "#,##0" End With End Sub |
スポンサードリンク
PageViewCounter
Since2006/2/27