【ExcelVBA】SUM関数を選択範囲の縦横に挿入する方法は？位置決めも可！

Excelの操作でSUM関数を挿入する機会は多いかと思います。

そんな時に大変だと思うことは

・SUM関数を一個づつ設定するのは面倒…
・SUM関数で合計範囲が漏れていた…
・縦横両方にSUM関数を入れたいが方法がわからない…

ということはないでしょうか。

今回はそんなときに役立つ、
一瞬でSUM関数を選択範囲の縦横に挿入する方法と、あるとうれしい位置決め機能を
ご紹介します！

標準機能で縦横にSUM関数を挿入する方法はオートSUM！

ご存知の方も多いでしょうが、縦横にSUM関数を挿入する方法はExcelの標準機能で実現が可能です。

その機能とは、

「オートSUM」

です。

使用方法はカンタンで、

合計したいセルとSUM関数を挿入したいセルを範囲選択し、

数式タブをクリックし、オートSUMボタンをクリックするだけです。

はい、できましたね。

なお、合計したいセルのみの範囲選択ですと、

最下行のみにSUM関数は挿入されます。

ただ、この「オートSUM」ですが、選択範囲右列のみにまとめてSUM関数を挿入することはできません。

また、SUM関数の挿入位置を選択範囲の次の次の列や行にしたりすることはできません。

標準機能ですので、若干柔軟性が無いわけです。

そこで登場するのがVBAです。

ExcelVBAでSUM関数を選択範囲の縦横に挿入したり、位置を調整するには？

SUM関数を挿入したい位置は表によってさまざまかと思いますが、

条件によっては「オートSUM」が利用できない場合があります。

例えば、合計値の位置がデータの位置と隣接していない場合です。

こんな場合ですね。

その場合には手動で入れる必要がありますが、大量にあるとうんざりしますよね。

そこで今回の目玉である、SUM関数の挿入位置を指定できるようにしました。

さらに、SUM関数の挿入範囲を
「行だけ」、「列だけ」、「行列」の
3パターンの選択をできるようにしました。

タカヒロ

VBEから実行をかけると、別ブックのデータも同じくSUM関数挿入処理が可能ですので便利です！

サンプルデータを準備する

SUM関数を挿入するためのデータを準備します。

今回は「オートSUM」同様、選択範囲分挿入されますので、2行、2列以上のデータを準備します。

できたサンプルデータは上記と同様の内容となります。

VBAを実装する

続いてExcelのVisual Basic EditorへVBAを実装します。

Sub SUM関数挿入()

    '引数の数値はSUM関数挿入の位置を表します。1の場合は選択範囲の次のセル、2は次の次のセルといった具合です。
    Call 選択範囲の最終列右にSUM関数を挿入する(2)
    
    Call 選択範囲の最終行下にSUM関数を挿入する(2)

End Sub




Sub 選択範囲の最終列右にSUM関数を挿入する(intSumPosCol As Long)

    Dim intRow As Long
    Dim intCol As Long
    Dim intMaxRow As Long
    Dim intMaxCol As Long
    Dim strStartCell As String
    Dim strEndCell As String
    Dim strSumPosCell As String
    
    intRow = Selection(1).Row '行数
    intCol = Selection(1).Column '列数
    intMaxRow = Selection(Selection.Count).Row '最終行
    intMaxCol = Selection(Selection.Count).Column '最終列
    intSumPosCol = intSumPosCol + intMaxCol '位置数に最終行数を加える。
    
    'アドレス値に変換※相対参照とする。
    strStartCell = Cells(intRow, intCol).Address(RowAbsolute:=False, ColumnAbsolute:=False)
    strEndCell = Cells(intRow, intMaxCol).Address(RowAbsolute:=False, ColumnAbsolute:=False)
    strSumPosCell = Cells(intRow, intSumPosCol).Address(RowAbsolute:=False, ColumnAbsolute:=False)
    
    With Range(strSumPosCell)
        .Formula = "=SUM(" & strStartCell & ":" & strEndCell & " )"
        '1行以下はエラーとなるため回避
        If intMaxRow - intRow + 1 > 1 Then
            .AutoFill Destination:=.Resize(intMaxRow - intRow + 1, 1)
        End If
    End With

End Sub



Sub 選択範囲の最終行下にSUM関数を挿入する(intSumPosRow As Long)

    Dim intRow As Long
    Dim intCol As Long
    Dim intMaxRow As Long
    Dim intMaxCol As Long
    Dim strStartCell As String
    Dim strEndCell As String
    Dim strSumPosCell As String
    
    intRow = Selection(1).Row '行数
    intCol = Selection(1).Column '列数
    intMaxRow = Selection(Selection.Count).Row '最終行
    intMaxCol = Selection(Selection.Count).Column '最終列
    intSumPosRow = intSumPosRow + intMaxRow '位置数に最終行数を加える。
    
    'アドレス値に変換※相対参照とする。
    strStartCell = Cells(intRow, intCol).Address(RowAbsolute:=False, ColumnAbsolute:=False)
    strEndCell = Cells(intMaxRow, intCol).Address(RowAbsolute:=False, ColumnAbsolute:=False)
    strSumPosCell = Cells(intSumPosRow, intCol).Address(RowAbsolute:=False, ColumnAbsolute:=False)
    
    With Range(strSumPosCell)
        .Formula = "=SUM(" & strStartCell & ":" & strEndCell & " )"
        '1列以下はエラーとなるため回避
        If intMaxCol - intCol + 1 > 1 Then
            .AutoFill Destination:=.Resize(1, intMaxCol - intCol + 1)
        End If
    End With

End Sub

実装手順は以下の通りです。

Excel側にVBAを実装します。

①Excelを新規に開き、開発タブをクリックし、VisualBasicをクリックします。
もしくはショートカットキーAlt＋F11でもOKです。

②標準モジュールを追加します。
左ペインのVBAProjectを右クリックし、挿入、標準モジュールを選択します。

③右ペインのウインドウに上記のVBAを入力します。

こちらで完了です。

VBAを実行する

では早速VBAの実行をしてみましょう。

①合計したいデータがあるセルを範囲選択します。

②開発タブのVBAをクリックし

「SUM関数挿入」を選択し、

「実行」をクリックします。

行列の指定位置にSUM関数が挿入されましたね！

別パターン①：SUM関数挿入位置を変える

SUM関数挿入位置を変えることが可能です。

選択範囲の次の次の次のセルにする場合は各プロシージャの引数の値を変更します。

サンプルでは各プロシージャの引数を「2」から「3」に変更しました。
Call 選択範囲の最終列右にSUM関数を挿入する(3)
Call 選択範囲の最終行下にSUM関数を挿入する(3)

実行すると、

はい、SUM関数の挿入位置が変わりましたね。

タカヒロ

欄外挿入で美しくないですがご了承ください…

別パターン②：SUM関数挿入を行または列のみにする

「オートSUM」ではできなかったSUM関数挿入を列のみにすることですが、

この場合、「選択範囲の最終列右にSUM関数を挿入する」をコメントアウトすることによって可能になります。
Call 選択範囲の最終列右にSUM関数を挿入する(2)
’Call 選択範囲の最終行下にSUM関数を挿入する(2)

はい、できましたね。

別パターン③：別ブックにSUM関数挿入をする

VBEから実行すると、別ブックの表データに対してSUM関数を挿入することが可能になります。

別ブックを開き、表データを範囲選択し、VBEを実行します。

はい、できましたね。

VBAの説明

VBAの内容を説明します。

今回は3つのプロシージャを利用しています。

一つがメインの「SUM関数挿入」プロシージャで、ここから各サブプロシージャを呼び出しています。

残りの2つは
「選択範囲の最終列右にSUM関数を挿入する」
「選択範囲の最終行下にSUM関数を挿入する」
で、第一引数にはSUM関数挿入位置を指定します。

次に「選択範囲の最終列右にSUM関数を挿入する」プロシージャについて説明します。

以下では選択範囲の座標を取得しています。
intRow = Selection(1).Row ‘行数
intCol = Selection(1).Column ‘列数
intMaxRow = Selection(Selection.Count).Row ‘最終行
intMaxCol = Selection(Selection.Count).Column ‘最終列
intSumPosRow = intSumPosRow + intMaxRow ‘位置数に最終行数を加える。

以下では座標からA1などのアドレス値に変換しています。指定パラメータは相対参照となるよう設定をしています。
strStartCell = Cells(intRow, intCol).Address(RowAbsolute:=False, ColumnAbsolute:=False)
strEndCell = Cells(intMaxRow, intCol).Address(RowAbsolute:=False, ColumnAbsolute:=False)
strSumPosCell = Cells(intSumPosRow, intCol).Address(RowAbsolute:=False, ColumnAbsolute:=False)

以下はSUM関数を挿入する処理です。
With Range(strSumPosCell)
.Formula = “=SUM(” & strStartCell & “:” & strEndCell & ” )”
‘1列以下はエラーとなるため回避
If intMaxCol – intCol + 1 > 1 Then
.AutoFill Destination:=.Resize(1, intMaxCol – intCol + 1)
End If
End With

ちなみにエラー回避の箇所ですが、数式をコピーするAutoFill関数は1個だけの場合はエラー1004「RangeクラスのAutoFillメソッドが失敗しました。」となりますので、2個以上の場合に処理をするようにしています。