レッスン-投稿一覧パソコン仕事５倍塾ポータル

投稿/コメントを表示します。

勘定項目ごとに合計額を算出する。SumIf関数がやっていることをマクロで書くと？

受講生さんの投稿

(投稿ID: 2658)

小川先生
お世話になっております。
単純ですが、シートにある勘定科目毎に伝票を集計し、合計を別シートのP/Lに書き出すﾏｸﾛです。
1万行以上と金額が億単位だったので、ﾏｸﾛを動かしたら、ものすごくＰＣのスピードが落ちたので、
何10分まったら終わるのかとおもい途中でやめました。こういう場合はＳＵＭIF関数の方がよいのでしょうか。
ちなみにＰＣのスペックは問題ありません。宜しくお願いします。

Option Explicit
Sub hoge()
    Dim gyo As Long
    Dim goukei As Currency
    Dim kubun As Long
    For kubun = 12 To 226
        goukei = 0
        For gyo = 2 To 11419
            If Worksheets("４月 (2)").Range("C" & kubun).Value = Worksheets("Sheet1 (2)").Range("K" & gyo).Value Then
                goukei = goukei + Worksheets("Sheet1 (2)").Range("AE" & gyo).Value
            End If
                Worksheets("４月 (2)").Range("E" & kubun).Value = goukei
        Next
    Next
    
End Sub

2016/11/06 21:02

ゲストさんのコメント

(コメントID: 4110)

受講生さん:

こんばんは。

これは計算量が多いですね。。なんとかしたくなるのも分かります (^_^;
とはいえ、基礎編レベルの知識ではこれが限界です。

受講生さんも受講済の発展編１で、 WorksheetFunction というオブジェクトを扱っています。
名前のとおり、エクセルのワークシート関数を呼び出してVBAで利用できるというものです。
使いこなせるようになると、同様の計算をするエクセルワークシート関数をご存知でしたら、自分で計算アルゴリズムを編み出さないでも１行で計算をしてくれて便利です。
古典的かつ代表的なワークシート関数でしたらほぼほぼVBAから利用可能です。

以下に、Sum関数、Count関数、CountIf関数を活用する場合のサンプルを示します。試してみてください。

Sub wsfsample()
    Range("N2").Value = WorksheetFunction.Sum(Range("M2:M51"))
    Range("N3").Value = WorksheetFunction.Count(Range("M2:M51"))
    Range("N4").Value = WorksheetFunction.CountIf(Range("F2:F51"), "=渋谷区")
End Sub

上に示したサンプルコードからも分かるとおり、引数でセルを指定する際は、セル番地でなく、セルをオブジェクトとして指定する必要があります。
あと、エクセルのバージョンによっては、 WorksheetFunction の前に Application. をつけなくてはならないかも。
そこだけ注意。

それから、エクセルで開いているファイルのどれかに計算量の多いワークシート関数が大量に含まれている場合も処理が遅くなる傾向があります。
セルの編集がある都度、すべてのワークシート関数が再計算をするからです。
VLookUp関数とかIsError関数とか、そういうたぐいのがぐちゃぐちゃと入れ子になっているセルが大量にあるとかの場合ですね。

その場合、マクロでの処理開始の前に、ワークシート関数の再計算機能を停止させ、処理後に自動で再計算するように再設定します。
以下の要領。

Sub calcsample()
    Application.Calculation = xlCalculationManual
    Range("N2").Value = WorksheetFunction.Sum(Range("M2:M51"))
    Range("N3").Value = WorksheetFunction.Count(Range("M2:M51"))
    Range("N4").Value = WorksheetFunction.CountIf(Range("F2:F51"), "=渋谷区")
    Application.Calculation = xlCalculationAutomatic '[*]
End Sub

[*]について補足すると、エクセルで設定画面から再計算を自分で止めている人も（滅多にないですが）いるかもしれないので、以下のほうがより親切。

Sub calcvsample()
    Dim c As Long
    c = Application.Calculation '[+]
    Application.Calculation = xlCalculationManual
    Range("N2").Value = WorksheetFunction.Sum(Range("M2:M51"))
    Range("N3").Value = WorksheetFunction.Count(Range("M2:M51"))
    Range("N4").Value = WorksheetFunction.CountIf(Range("F2:F51"), "=渋谷区")
    Application.Calculation = c '[*]
End Sub

なぜ変数を Long 型にしたのか？とか [+] でなにをしているのか？とかいったことについては、基礎編レベルを大きく超えるのでここでは説明割愛します。
受講生さんは発展編１受講済のようですので、まずは上記についてテキストにて再学習のうえ、さらにご質問あるようでしたら、発展編１講座のどこかでまたご質問いただければと。

2016/11/07 07:07