ExcelマクロVBAでSUMIFS関数(複数条件に合致した数値を合計)を作成

エクセルで条件に合致する数値を累計する場合、Excel関数では「=SUMIFS($D$2:$D$31,$B$2:$B$31,$F2,$C$2:$C$31,G$1)」と記載することで、合計値を算出できます。

このようにエクセル関数SUMIFで合計値の算出は可能なのですが、VBAを使う場合はどのようにプログラムを作ればいいのでしょうか。

本記事では、VBAでSUMIFS関数を作成してみます。

VBA作成前の準備

ExcelでVBAを使うためにはいくつかの準備が必要です。

具体的には以下です。

以下で説明します。

1. ExcelでVBAを使うための事前準備

Excelで、以下の2つの準備をします。

保存ファイルの拡張子変更、Excelの基本設定変更の2つです。

2つともで難しい作業はなく、それぞれ1分もあれば設定変更可能です。

上記に関しては、以下の記事で解説をしています。

もしVBAを使うための準備段階に不安がある人は上記をご覧ください。

この内容は以下の動画で紹介しています。

入門エクセルマクロの使い方｜マクロ作成から実行までを徹底解説

文字や画像だけで分かりづらい人は上記の動画をご覧ください。

Excel VBAで複数条件に合致する数値を合計

今回はB列、C列、D列のデータを取得してG列、H列、I列の表を埋める作業をVBAで行います。

それぞれの色で突合(マッチング)させて、条件に合致する行のD列の値を合計していきます。

合計値をG2~I11に出力させます。

今回は、この処理をVBAプログラムで行います。

VBA入りのエクセルファイルをダウンロード

紹介しているVBAプログラムをそのまま使いたい人は、以下のページのフォームからダウンロードできます。

上記のページでは下記10コのエクセル関数について、VBAプログラムで作成した事例を紹介しています。

興味がある人はご覧ください。

それでは、以下で本ページで解説するVBAプログラムについて詳細を説明します。

VBAのプログラムソース解説

今回紹介するプログラムの概要は以下です。

以下で詳しく説明していきます。

プログラム0｜変数宣言の指定

「Option Explicit」とは、変数宣言を強制するためのものです。

これを入れておくと、変数を定義していない場合、エラーが出ます。

つまり、「Option Explicit」を入力しておくことで、たとえば「Dim i」をあらかじめ入力しないと、「i」という変数を使えません。

もし「Option Explicit」を入力しているのに、「Dim i」を書かずに「i = 1」と書くと、エラーメッセージが表示されます。

実は、この機能はあくまでオプションです。「Option Explicit」を入力しなくても、プログラムは動きます。

しかし、これを入れておくことで、変数の誤記によるエラーを防止することができます。

結果的に、プログラム作成速度が上がるので、「Option Explicit」を入力することを習慣化することをオススメします。

プログラム1｜プログラム開始

「Sub Excel_SumIfs()」のプログラムを開始することを意味します。

VBAではプロシージャという単位でプログラムを呼び出します。

プロシージャの構文は下記となっています。

「Sub」で始まり「End Sub」で終わります。

「Sub XXXX」の「XXXX」の部分がプロシージャ名です。

このプロシージャ名はあらゆる文字（アルファベット、ひらがな、漢字、数字など）が使用可能です。

ただし、プロシージャ名の先頭は数字を入れるとエラーとなります。

あとで見たときに、「何のプログラムだったのか？」とならないようにするためです。

なお、「()」の中には何も記入されていません。これは引数なしという意味です。

初心者の内は、引数ということが分からなくてもVBAプログラムを書くことは可能です。

興味があれば、「VBA 引数」で検索して調べてみてください。

プログラム2｜対象シートを設定

変数wsをWorksheet(ワークシート)型で設定します。

変数wsにExcelのシート「Sheet6」を設定します。

なおここでシートを指定しておかないと、今開いているExcelのシートに処理が実行されます。

たとえばSheet1が表示されていれば、Sheet1で処理が実行されてしまいます。

そうすると想定と異なる処理が行われてしまうため、注意が必要です。

プログラム3｜最終行の取得

cmaxとをLong(整数)型で定義します。

そして「cmax = ws.Range(“A65536”).End(xlUp).Row」とすることで、A列の最終行を取得することができます。

「cmax = ws.Range(“A65536”).End(xlUp).Row」は、wsのセルA65536, A65535, A65534,・・・, A31と上のセルをチェックしていき、値が入っている最初のセルを取得するという意味です。

セルA65536から数えて、セルA21が値が入っている最初のセルなので、「cmax1 = 31」となります。

「Debug.Print()」で検証すると、以下の通りです。

このようにDebug.Printで検証する習慣をつけておくと、プログラム作成が早くなります。ぜひ習慣化しておくことをお勧めします。

ここで取得したcmaxの値は後半のプログラムで活用します。

プログラム4｜変数設定

kokyaku, tantoをString(文字列)型で設定します。B列とC列の値と突合(マッチング)させるときの変数として使用します。

kingaku,i,j,kをLong(整数)型で設定します。kingakuは、D列の金額を合計するときの変数として使用します。またi,j,kは繰り返し処理を実行するときの数値カウンターとして使用します。

プログラム5｜F列の値を取得

「For i = 2 to cmax」で「i =2,3,4,・・・, 31(cmax)」というようにiに1ずつ加算しながら、ForとNextの間の処理を繰り返し実行させます。

For Next構文は使用頻度が高いので、使えるようになると威力を発揮します。For Next文は以下で事例を交えながら解説しています。

「kokyaku = ws.Range(“F” & i).Value」で、Fi(i=2,3,4,・・・,21)の値を変数kokyakuとして取得します。

ここで取得したkokyaku(F列)はプログラム9でB列の値をマッチングさせます。

プログラム6｜変数Kokyakuが空欄かどうか確認

i=2,3,4,・・・,cmax(31)」とiの値が大きくなると、F2,F3,F4,・・・,F31と対象セルも変わります。

しかしF12~F31は空欄であり、空欄は件数をカウントする必要がありません。

そこで、「If Not kokyaku = “” Then」で空欄かどうかをチェックします。そして空欄でないときのみ、プログラム7以降の処理を実行させるようにします。つまり、F2~F11でのみプログラム7以降が処理されるようにしています。

「If Not Kokyaku = “” Then」は、「もし変数「Kokyaku」が””(空欄)でないならば」という意味です。

If文に「Not」,「””」を組み合わせています。少々ややこしく感じるかもしれませんが、覚えておくと非常に役立ちます。If文は以下で事例を交えながら解説しています。

プログラム7｜担当者(G1, H1, I1)を取得

「For j = 0 to 2」で「j = 0,1,2」とjに1ずつ加算しながら、ForとNextの間の処理を繰り返し実行させます。

プログラム5で使用した「i」と混同しないように気を付けます。もしプログラム7でも「i」を使ってしまうと、正しく処理されません。

「kingaku」はプログラム4で定義した変数ですが、これをを0(数字)として設定します。この変数「kingaku」は条件に合致した金額(D列)を合計するためのものです。

このタイミングでゼロリセットさせることで、正しい合計値を計算できます。ゼロリセットするタイミングを間違えると、正しい金額が計算されないので、注意が必要です。

「tanto = ws.Range(“G1”).Offset(0, j).Value」で、offsetを使うことで、「j」の値に応じて、セルG1(j=0)、H1(j=1)、I1(j=2)の値を変数tantoとして取得します。

offset関数についてイメージが付かない人もいるかもしれませんので、以下で要素に分けて説明します。

このように「Offset(行, 列)」とForNextを組み合わせることで、行と列について繰り返し処理を実行できます。

列を繰り返し処理する方法

複数列を繰り返し処理を実行する方法としてoffsetを紹介しました。offsetを紹介した理由は概念的に理解しやすいからです。

たとえば「Range(“A1”).Offset(i,j)」はセルA1を起点にしてi行、j列移動したセルとなり、イメージがしやすいのです。

またエクセル関数でもoffsetは使用されており、理解しておくと自動化の幅が広がることも理由の一つです。（VBAとエクセル関数のoffsetは引数が異なりますが）

さて列を繰り返し処理するとき、他の方法もあります。たとえばCellsを使う方法です。

しかし複数の方法をすべて理解する方法はありません。なぜなら網羅的に覚えたところで、処理内容は同じだからです。offsetとcellsの2つの方法を覚えても、処理したいプロセスは同じです。

そのためoffsetを覚えてしまえば、cellsを覚えることはコストでしかありません。

当然、他人が作成したプログラムでCellsなどを見つけることはありますが、プログラムを読んで繰り返し処理を行っていることを理解できれば十分です。

つまり覚えるコストを考慮し、offsetを理解すればそれで十分です。

プログラム8｜B列とF列をマッチング（突合）

「For k = 2 to cmax」で「k =2,3,4,・・・, cmax」のようにjに1ずつ加算しながら、ForとNextの間の処理を繰り返し実行させます。

プログラム5で使用した「i」、プログラム7で使用した「j」と混同しないように気を付けます。もしここで「k」ではなく「i」や「j」を使ってしまうと、正しく処理されません。

「If ws.Range(“B” & k).Value = kokyaku Then」で、「取引先名称」(B列)と変数「kokyaku」（F列）が一致しているかどうか調べます。

一致していれば、プログラム9を実行します。

プログラム9｜C列と担当者をマッチング（突合）

「If ws.Range(“C” & k).Value = tanto Then」で、担当者（C列）と変数「tanto」（G1,H1,I1）が一致しているかどうか調べます。

もし一致していれば、「kingaku = kingaku + ws.Range(“D” & j).Value」でD列の取引金額を加算します。

数値を加算していく場合は、「A = A + B」と記載します。VBAに限らず、プログラミングでは「右辺を先に計算して、左辺を更新する」という考え方があります。つまり「右辺(A + B)を先に計算して、左辺(A)を更新する」のです。

AとBは数値ではないとエラーがでるので、AとBが数値であることを事前に確認しておく必要があります。

「End If」と「Next」を記載漏れでエラーが出ることがよくありますので、注意が必要です。

プログラム10｜G列～I列に出力

「ws.Range(“G” & i).Offset(0, j).Value = kingaku」でG列~I列に取引金額の累計を出力します。kingakuはプログラム9で計算した値です。

ここでは「i」を使います。「j」や「k」ではありません。ForNextを3つ使っているため、数値カウンターを取り間違えてしまうことがよくありますので注意が必要です。

また「End If」や「Next」を記載漏れで、エラーが出ることがよくありますので注意が必要です。

プログラム11｜プログラム終了

プログラム終了させる記載です。「End Sub」を読み込むと、プログラムが終了します。

プログラムの解説はここまでです。

Excel VBAについて詳しく理解したいなら

VBAを活用すると、仕事を効率化できる幅を広げることができます。

たとえば私が実際にVBAを活用して効率化してきた作業は以下の記事で紹介しています。

興味がある人は以下の記事もご覧ください。

動画でも解説しています。

エクセルマクロVBAで出来ることを15の事例で紹介｜日常業務をラクにするヒントを見つけよう！

(音声が小さいので、ボリュームを上げてご覧いただければと思います)

VBAの勉強方法

私はプログラミング初心者からVBAを勉強を始めて少しずつレベルアップしていきました。

少しずつレベルアップしながら、難しい内容に挑戦していくと効率的に学ぶことができます。

上記のリンクでは、VBA勉強に役立つ内容を紹介しています。

興味がある人はご覧ください。