【Excel VBA】SUM関数の使い方！合計値のみ挿入も！

けど、そんな中で悩むことは、

ですよね。

今回はそんなお悩みを解決する

についてまとめます！

完成イメージ

VBAで合計を扱うときは、「合計値を数値として受け取る」のか「SUMの数式を入れる」のかで書き方が変わります。
今回は
・WorksheetFunction.Sum で合計値を取得してセルに入れる方法（合計値のみ挿入）
をゴールにします。
あわせて、複数列をまとめて合計する方法や、文字列・エラー値が混ざった列、見えている行だけの合計まで扱えるようにします。

上の画像が完成形です。合計値のみ・数式・表示中のみの3種類の合計が並び、文字列やエラー値が混ざった列も正しく合計できています。

なお、毎回手で数式を入れるのが手間な方向けにVBAで合計行を一括挿入する方法と、うまくいかないときの対処法（トラブルシュート）もあわせてご紹介します。

STEP1. 元データを用意する

まずは合計したい売上表を用意します。
1行目が見出し、2行目以降がデータで、今回は10件です。
D列（金額）は =B2*C2、F列（税込金額）は =ROUND(D2*1.1,0) の数式にしてあります。
F列の6行目はエラー値 #N/A、E列（納品数）には文字列の 「未定」 を混ぜてあります。
こうしておくと、あとで「混ざっていても正しく合計する方法」まで確認できます。

下の表をコピーして、Excelのシートに貼り付けるとそのまま試せます。

※ 数式で求める列は空にしています。すでに値が入っている列はそのまま残して試せます。

STEP2. WorksheetFunction.Sumで合計値を取得する

VBAで合計だけを取得したいときは WorksheetFunction.Sum を使います。
ワークシートのSUM関数をそのまま呼び出せるので、戻り値は合計値（数値）です。
まずは合計値をそのままD13セルに入れてみましょう。

Sub SumValue()
    Dim total As Double
    total = WorksheetFunction.Sum(Range("D2:D11"))
    Range("D13").Value = total
    MsgBox "合計は " & Format(total, "#,##0") & " 円です"
End Sub

マクロを実行すると、D13セルに 987,400 が入りました。
このとき数式バーには、数式ではなく数値そのものが表示されています。
あとから数量や単価を書き換えても、この数値は自動では変わりません。
逆にいえば、結果の数値だけを残したいときはこの形が便利です。
カンタンですね。

STEP3. 数式として入れたいときは「.Formula」

「あとで数量や単価を変えたら、合計も自動で更新されてほしい」というときは、数式として入れます。
Rangeオブジェクトの .Formula に、SUMの数式を文字列で代入します。

Sub SumFormula()
    Range("D14").Formula = "=SUM(D2:D11)"
End Sub

実行すると、D14セルは =SUM(D2:D11) という数式になりました。
数式バーにも =SUM(D2:D11) と表示されています。
数式なので、金額を直すと合計もその場で計算し直されます。
同じSUMでも、.Value は値、.Formula は数式が入る、と覚えておきましょう。

STEP4. 「合計値のみ」を挿入する（数式を残さない）

数式が入っているセルを、計算結果の値だけに置き換えたいときは .Value = .Value を使います。
同じセルに自分の値を入れ直すことで、数式が計算結果に置き換わります。

Sub ValueOnly()
    With Range("D14")
        .Value = .Value
    End With
End Sub

実行すると、D14セルから数式が消え、987,400 という値だけが残りました。
数式バーにも数値だけが表示されています。
別のブックや他の担当者に渡すとき、数式のままだと参照がずれてエラーになることがあります。
そのため渡す前は値に変換しておくのがおすすめです。
これで「合計値のみ挿入」もできるようになりました。

タカヒロ

WorksheetFunction.Sum を使えば、そもそも最初から値で入るので変換は不要です。
「数式で入れてしまったあとから値にしたい」ときに .Value = .Value を使ってくださいね。

4つの入れ方の比較

同じSUMでも、入り方が違うと使いどころも変わります。
違いを表にまとめました。

STEP5. 複数の列をまとめて合計する

合計したい列が複数あるときも、同じ考え方でまとめて合計できます。
E13セル（納品数）とF13セル（税込金額）に数式を入れてみましょう。

Sub MultiSum()
    Range("E13").Formula = "=SUM(E2:E11)"
    Range("F13").Formula = "=SUM(F2:F11)"
End Sub

納品数の合計は 51 になりました。
E列には文字列の「未定」が2つ混ざっていますが、SUM関数は数値以外を無視するので、数値だけが合計されています。
ところが、F13セルは #N/A になってしまいました。
F列にエラー値が混ざっているためです。
次のステップで、このエラーの直し方を確認しましょう。

STEP6. エラー値が混ざった列を合計する

エラー値が1つでも混ざっていると、SUMは合計できずに同じエラーを返してしまいます。
こういうときは AGGREGATE関数 を使います。
AGGREGATEは、第2引数で「エラー値を無視する」を選べる集計関数です。
F13セルの数式を次のように変えてみましょう。

Sub SumIgnoreError()
    Range("F13").Formula = "=AGGREGATE(9,6,F2:F11)"
End Sub

F13セルが 955,020 に変わり、エラー値の行を除いて合計できました。
下のF14セルは、あえて =SUM(F2:F11) のまま残したものです。
同じ範囲でも、SUMは #N/A、AGGREGATEは数値になる違いが見られます。

VBAでSumIf（条件付き合計）を使う

「ノートPCだけ合計したい」など、条件に合う行だけを合計したいときは WorksheetFunction.SumIf が使えます。

Sub SumIfSample()
    Dim total As Double
    total = Application.WorksheetFunction.SumIf( _
        Range("A2:A11"), "ノートPC", Range("D2:D11"))
    Range("D13").Value = total
End Sub

第1引数に条件の範囲、第2引数に条件、第3引数に合計したい範囲を指定します。
条件を変えるだけで、商品別・担当者別の集計が一瞬で作れます。
カンタンですね。

STEP7. 見えているセルだけ合計する

オートフィルターで絞り込んだあと、見えている行だけを合計したいときは SUBTOTAL関数 を使います。
D15セルに =SUBTOTAL(9,D2:D11) と入力します。
ここでは「数量が10以上」の商品だけに絞り込んでみました。
SUMなら全件の 987,400 のままですが、SUBTOTALは表示中の行だけを合計して 143,600 になりました。
絞り込みを切り替えると合計も自動で追従します。
VBAで同じことをするときは SpecialCells(xlCellTypeVisible) を使います。

Sub VisibleSum()
    Dim total As Double
    total = WorksheetFunction.Sum( _
        Range("D2:D11").SpecialCells(xlCellTypeVisible))
    Range("D15").Value = total
End Sub

SpecialCells(xlCellTypeVisible) は「見えているセルだけ」を表すので、絞り込み中の合計にぴったりです。

うまくいかないときの対処法（トラブルシュート）

実際に試すと、思ったように動かないこともあるかもしれません。
よくあるつまずきと対処法をまとめました。

関数の説明

今回使う3つの関数の概要です。
どれも集計の基本になる関数なので、違いを押さえておきましょう。

SUM関数（サム）

=SUM(D2:D11)

SUBTOTAL関数（サブトータル）

=SUBTOTAL(9, D2:D11)

AGGREGATE関数（アグリゲート）

=AGGREGATE(9, 6, F2:F11)

完成

「合計（値のみ）」「合計（数式）」「合計（表示中のみ）」の3つの合計が並びました。
絞り込みを解除しているので、いまは3つとも 987,400 でそろっています。
ここで数量が10以上に絞り込むと、「合計（表示中のみ）」だけが 143,600 に切り替わります。
文字列の「未定」が混ざったE列も、エラー値が混ざったF列も、正しく合計できています。
思ったとおりに動きましたか？
「値で入れる」「数式で入れる」「表示中だけ合計する」を使い分けられると、集計作業がぐっとラクになります。

さいごに

いかがでしょうか。

今回は、

についてまとめました。

まずは今回のサンプル表で、ぜひ一度試してみてください。
合計の「値」と「数式」を使い分けて、文字列やエラー値が混ざっても落ち着いて対処できるようになりましょう。
また、他にも便利なExcelの時短テクニックがありますので、よろしければご参照頂ければと思います。

ぜひ試してみてください。