エクセル関数をマクロで作る！10事例でVBAプログラムを解説

このページではエクセル関数をVBAを使ったら、「どのようなプログラムとなるのか？」について紹介していきます。

実際に、以下のエクセル関数をVBAプログラムによって作成します。

VBAでエクセル関数を作ってみる

・SUM関数
・IF関数
・COUNTIF関数
・COUNTIFS関数
・SUMIF関数
・SUMIFS関数
・VLOOKUP関数
・SUMPRODUCT関数

このページでは、「VBA×エクセル関数」について実務に近い事例を交えながら紹介していきます。

なお本記事で紹介しているVBAプログラムをそのまま使いたい人のために、ダウンロードフォームを準備しています。

必要な人は本ページのダウンロードフォームから「VBAプログラムが含まれたエクセル」をダウンロードしてご利用ください。

1 なぜ「VBA×エクセル関数」を紹介するのか？
- 1.1 メリット｜VBA初心者にとって良い教材となる
- 1.2 デメリット｜仕事で効率化できるほどのインパクトはない
2 VBA×エクセル関数｜事前準備
- 2.1 1. ExcelでVBAを使うための事前準備
3 Excel関数×VBA｜関数を事例
4 Excel VBAについて詳しく理解したいなら
- 4.1 VBAの勉強方法
5 VBAを自分で書けるようになる
- 5.1 まずは無料でマクロを勉強してみる

なぜ「VBA×エクセル関数」を紹介するのか？

メリット｜VBA初心者にとって良い教材となる

VBA初心者にとって、エクセル関数を題材にしたVBAプログラムは学ぶのに適しています。

その理由はエクセル関数はイメージしやすいからです。

エクセル関数をVBAで作ってみるのは、練習問題にちょうどいい

VBA初心者がやってしまいがちなミスとして、いきなり難易度の高いことをやろうとしてしまうことがあります。

英語初心者がいきなりネイティブスピーカーと話をしてもチンプンカンプンになってしまうのと同様に、VBAもいきなる業務を効率化するプログラムを作ろうとしても「???」となってしまうのでうｓ。

そのため練習問題としてVBAに慣れる必要があります。

その練習問題として、エクセル関数をVBAで作ることが適しているのです。

エクセル関数は動作がイメージしやすい

エクセル関数を普段から使っている人であれば、エクセル関数がどのように動作するかイメージするのは簡単です。

たとえばエクセル関数のIFは「=IF(B2=”X”,”該当”,”非該当”)」というように入力します。これでセルB2が「X」だったら「該当」を出力し、そうではないなら「非該当」を出力します。

このように動作の結果が見えているため、過程をVBAプログラムで作ることに専念できるのです。

解答が分かっている設問を解いていくので、躓く可能性が低く、レベルアップしやすいというわけです。

一方で、デメリットもあります。

デメリット｜仕事で効率化できるほどのインパクトはない

エクセル関数をVBAを作成しても、実際に仕事を効率化できるレベルのものにはなりません。VBAを作るよりエクセル関数で処理を作ったほうが、早くて簡単です。

たとえば、エクセル関数「=IF(B2=”X”,”該当”,”非該当”)」をVBAで作ってみます。

エクセル関数の場合

=IF(B2="X","該当","非該当")

1	=IF(B2="X","該当","非該当")

エクセルVBAの場合

If ws.Range("B2").Value = "X" Then
    ws.Range("C2").Value = "該当"
Else            
    ws.Range("C2").Value = "非該当"        
End If

If ws.Range("B2").Value = "X" Then

ws.Range("C2").Value = "該当"

Else

ws.Range("C2").Value = "非該当"

End If

上記のとおり同じ処理を行うのにも関わらず、エクセル関数は1行で済みます。

IFだけではなくsumif,vlookupなどもエクセル関数の方が短い記載で済んでしまいます。

だからこそ、エクセル関数をVBAで作れるようになったとしても、大幅な効率化を得られないのです。

実際、sumifやvlookupのようなシンプルな処理を効率化するのであれば、エクセル関数を使えばよいです。

より大きな効率化をしたい場合は、仕事に応用できるようにVBAを高いレベルで使いこなせるようになる必要があります。

そのため、このページで紹介している事例は基礎固めの練習問題として考えていただくのが良いです。

VBA×エクセル関数｜事前準備

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

具体的には以下です。

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

以下で説明します。

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

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

1. Excelファイルを「.xlsm」として保存（「.xlsx」で保存しない）

2. 開発タブを追加

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

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

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

エクセルマクロ使い方｜編集方法をVBA作成から保存のやり方まで解説

これからエクセルマクロを勉強しようとする人にとって、マクロの編集は一つのカベです。マクロというこれまで触ったことのないものを操作できるようになるのは決して簡単なことではありません。しかし順序良く学んでいけば、難しいも […]

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

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

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

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

Excel関数×VBA｜関数を事例

この記事では以下の事例について紹介していきます。

VBAでエクセル関数を作ってみる

事例1｜SUM関数
事例2｜IF関数
事例3｜COUNTIF関数
事例4｜COUNTIFS関数
事例5｜SUMIF関数
事例6｜SUMIFS関数
事例7｜VLOOKUP関数
事例8｜SUMPRODUCT関数
事例9｜SUMIFS関数(日付の比較)
事例10｜COUNTIF関数(ワイルドカード)

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

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

登録したメールアドレスへ「VBAプログラムが含まれたエクセル」を送信します。

本プログラムの内容をそのまま使用可能です。ぜひお仕事にお役立てください。

読み込んでいます…

それでは、以下で各事例について説明します。

事例1｜SUM関数をVBAで作る

セルC2~セルC21の数値の合計をセルG2に出力

SUM関数は合計値を計算する関数で上記画像の例でいえば「=sum(C2:C21)」とすれば、合計値を出力できます。

VBAを使ってプログラムを作ると以下です。

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

'プログラム1｜プログラム開始
Sub Excel_Sum()

    'プログラム2｜最終行の取得
    Dim Cmax As Long
    Cmax = Range("A65536").End(xlUp).Row
    
    'プログラム3｜変数設定
    Dim Goukei As Long
    Dim Kingaku As Long
    Dim i As Long
    
    'プログラム4｜C列の金額を計算
    For i = 2 To Cmax
        Kingaku = Range("C" & i).Value
        Goukei = Goukei + Kingaku
    Next
    
    'プログラム5｜G2に合計を出力
    Range("G2").Value = Goukei
    
    'プログラム6｜ファイル保存
    ThisWorkbook.SaveAs Filename:=ThisWorkbook.Path & "\Sample1.xlsm"

'プログラム7｜プログラム終了
End Sub

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

Option Explicit

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

Sub Excel_Sum()

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

Dim Cmax As Long

Cmax = Range("A65536").End(xlUp).Row

'プログラム3｜変数設定

Dim Goukei As Long

Dim Kingaku As Long

Dim i As Long

'プログラム4｜C列の金額を計算

For i = 2 To Cmax

Kingaku = Range("C" & i).Value

Goukei = Goukei + Kingaku

'プログラム5｜G2に合計を出力

Range("G2").Value = Goukei

'プログラム6｜ファイル保存

ThisWorkbook.SaveAs Filename:=ThisWorkbook.Path & "\Sample1.xlsm"

'プログラム7｜プログラム終了

End Sub

上記のプログラムは以下のぺージで詳細を解説しています。

ExcelマクロVBAでSUM関数を作成(最終行まで自動計算)

Excel関数でいえば、「=sum(C2:C21)」とすれば、合計値を出力することができます。しかしVBAを使う場合、どのようにプログラムを作ればいいのでしょうか。本記事では、そのプログラム作成について詳しく紹介して […]

事例2｜IF関数をVBAで作る

セルB2~B21の値に応じて、セルC2~セルC21に「該当」、「非該当」を入力

「該当」ならば、赤く太字にする

上記の画像のようなことをしたいとき、Excel関数で「=IF(B2=”愛知販売”,”該当”,”非該当”)」と入力します。また文字を赤くするために、条件付き書式を活用します。

このようなIf関数や条件付き書式をVBAを使ってプログラムを作ると以下です。

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

'プログラム1｜プログラム開始
Sub Excel_If()

    'プログラム2｜対象シートを設定
    Dim ws As Worksheet
    Set ws = Worksheets("Sheet2")
    
    'プログラム3｜最終行の取得
    Dim cmax As Long
    cmax = ws.Range("A65536").End(xlUp).Row
    
    'プログラム4｜検索ワードの設定
    Dim Keyword As String
    Keyword = "愛知販売"

    'プログラム5｜変数定義
    Dim i As Long
    
    'プログラム6｜B列の値を確認
    For i = 2 To cmax        
        If Keyword = ws.Range("B" & i).Value Then
            
            'プログラム7｜C列に入力(プログラム6がTrueの場合)
            ws.Range("C" & i).Value = "該当"
            
            'プログラム8｜C列のフォント変更(プログラム6がTrueの場合)
            With ws.Range("C" & i).Font
                .Size = 11
                .Bold = True
                .Color = vbRed
            End With
        
        'プログラム9｜C列に入力(プログラム6がFalseの場合)
        Else            
            ws.Range("C" & i).Value = "非該当"        
        End If    
    Next

'プログラム10｜プログラムの終了
End Sub

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

Option Explicit

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

Sub Excel_If()

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

Dim ws As Worksheet

Set ws = Worksheets("Sheet2")

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

Dim cmax As Long

cmax = ws.Range("A65536").End(xlUp).Row

'プログラム4｜検索ワードの設定

Dim Keyword As String

Keyword = "愛知販売"

'プログラム5｜変数定義

Dim i As Long

'プログラム6｜B列の値を確認

For i = 2 To cmax

If Keyword = ws.Range("B" & i).Value Then

'プログラム7｜C列に入力(プログラム6がTrueの場合)

ws.Range("C" & i).Value = "該当"

'プログラム8｜C列のフォント変更(プログラム6がTrueの場合)

With ws.Range("C" & i).Font

.Size = 11

.Bold = True

.Color = vbRed

End With

'プログラム9｜C列に入力(プログラム6がFalseの場合)

Else

ws.Range("C" & i).Value = "非該当"

End If

'プログラム10｜プログラムの終了

End Sub

上記のプログラムは以下のぺージで詳細を解説しています。

ExcelマクロVBAでIF関数を作成(条件付き書式のように文字列を赤くする)

上記の画像のようなことをしたいとき、Excel関数で「=IF(B2=”愛知販売”,”該当”,”非該当”)」と入力します。また文字を赤くするには、条 […]

事例3｜COUNTIF関数をVBAで作る

セルB2~B21の値の件数をカウント

セルG2~G11に件数を出力

上記の画像のようなことをしたいとき、Excel関数で「=COUNTIF($B$2:$B$21,F2)」と入力します。

このようなCOUNTIF関数をVBAプログラムで表現すると以下のようになります。

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

'プログラム1｜プログラム開始
Sub Excel_Countif()

    'プログラム2｜対象シートを設定
    Dim ws As Worksheet
    Set ws = Worksheets("Sheet3")
    
    'プログラム3｜最終行の取得
    Dim cmax As Long
    cmax = Range("A65536").End(xlUp).Row
    
    'プログラム4｜変数設定
    Dim kokyaku As String, torihiki As String
    Dim Kensu As Long, i As Long, j As Long
    
    'プログラム5｜B列の値を確認
    For i = 2 To cmax
        Kensu = 0
        kokyaku = ws.Range("F" & i).Value
        
        'プログラム6｜変数Kokyakuが空欄かどうか確認
        If Not kokyaku = "" Then
            
            'プログラム7｜B列に入力(プログラム6がTrueの場合)
            For j = 2 To cmax
                torihiki = ws.Range("B" & j).Value
                
                'プログラム8｜B列(Torihiki)とF列(Kokyaku)をカウントアップ
                If torihiki = kokyaku Then
                    Kensu = Kensu + 1
                End If
                
            Next
            
            'プログラム9｜G列に出力
            ws.Range("G" & i).Value = Kensu
        End If
    Next

'プログラム10｜プログラムの終了
End Sub

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

Option Explicit

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

Sub Excel_Countif()

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

Dim ws As Worksheet

Set ws = Worksheets("Sheet3")

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

Dim cmax As Long

cmax = Range("A65536").End(xlUp).Row

'プログラム4｜変数設定

Dim kokyaku As String, torihiki As String

Dim Kensu As Long, i As Long, j As Long

'プログラム5｜B列の値を確認

For i = 2 To cmax

Kensu = 0

kokyaku = ws.Range("F" & i).Value

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

If Not kokyaku = "" Then

'プログラム7｜B列に入力(プログラム6がTrueの場合)

For j = 2 To cmax

torihiki = ws.Range("B" & j).Value

'プログラム8｜B列(Torihiki)とF列(Kokyaku)をカウントアップ

If torihiki = kokyaku Then

Kensu = Kensu + 1

End If

'プログラム9｜G列に出力

ws.Range("G" & i).Value = Kensu

End If

'プログラム10｜プログラムの終了

End Sub

上記のプログラムは以下のぺージで詳細を解説しています。

ExcelマクロVBAでCountIf関数を作成(件数をカウントする)

上記の画像のようなことをしたいとき、Excel関数で「=COUNTIF($B$2:$B$21,F2)」と入力します。このようにExcelでCOUNTIF関数を使えば、件数のカウントを行うことができます。しかしVBAを […]

事例4｜COUNTIFS関数をVBAで作る

・セルB2~B31、セルC2~C31の値を取得

・セルG2~G11に合致する件数を出力

2条件で件数をカウントしたいとき、Excel関数で「=COUNTIFS($B$2:$B$31,$F2,$C$2:$C$31,G$1)」と入力します。

このようなCOUNTIFS関数をVBAプログラムで表現すると以下のようになります。

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

'プログラム1｜プログラム開始
Sub Excel_Countifs()

    'プログラム2｜対象シートを設定
    Dim ws As Worksheet
    Set ws = Worksheets("Sheet4")
    
    'プログラム3｜最終行の取得
    Dim cmax As Long
    cmax = ws.Range("A65536").End(xlUp).Row
    
    'プログラム4｜変数設定
    Dim Kensu As Long
    Dim kokyaku As String, tanto As String
    Dim i As Long, j As Long, k As Long
    
    'プログラム5｜F列の取得
    For i = 2 To cmax
        kokyaku = ws.Range("F" & i).Value
        
        'プログラム6｜変数Kokyakuが空欄かどうか確認
        If Not kokyaku = "" Then
            
            'プログラム7｜担当者(G1, H1, I1)を取得
            For j = 0 To 2
                Kensu = 0
                tanto = ws.Range("G1").Offset(0, j).Value
                
                'プログラム8｜B列とC列の値を確認
                For k = 2 To cmax
                    
                    'プログラム9｜B列とF列をマッチング（突合）
                    If ws.Range("B" & k).Value = kokyaku Then
                        
                        'プログラム10｜C列と担当者をマッチング（突合）
                        If ws.Range("C" & k).Value = tanto Then
                            Kensu = Kensu + 1
                        End If
                    End If
                Next
            
                'プログラム11｜G2～I11に出力
                ws.Range("G" & i).Offset(0, j).Value = Kensu
            Next
        End If
    Next

'プログラム12｜プログラムの終了
End Sub

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

Option Explicit

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

Sub Excel_Countifs()

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

Dim ws As Worksheet

Set ws = Worksheets("Sheet4")

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

Dim cmax As Long

cmax = ws.Range("A65536").End(xlUp).Row

'プログラム4｜変数設定

Dim Kensu As Long

Dim kokyaku As String, tanto As String

Dim i As Long, j As Long, k As Long

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

For i = 2 To cmax

kokyaku = ws.Range("F" & i).Value

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

If Not kokyaku = "" Then

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

For j = 0 To 2

Kensu = 0

tanto = ws.Range("G1").Offset(0, j).Value

'プログラム8｜B列とC列の値を確認

For k = 2 To cmax

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

If ws.Range("B" & k).Value = kokyaku Then

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

If ws.Range("C" & k).Value = tanto Then

Kensu = Kensu + 1

End If

'プログラム11｜G2～I11に出力

ws.Range("G" & i).Offset(0, j).Value = Kensu

End If

'プログラム12｜プログラムの終了

End Sub

上記のプログラムは以下のぺージで詳細を解説しています。

ExcelマクロVBAでCountIfs関数を作成(複数条件に合致する件数をカウント)

2条件で件数をカウントしたいとき、Excel関数で「=COUNTIFS($B$2:$B$31,$F2,$C$2:$C$31,G$1)」と入力します。このようにExcelでCOUNTIFS関数を使えば、複数条件に合致する […]

事例5｜SUMIF関数をVBAで作る

・B2~B21の取引先名称に合致したら、C2~C21の金額を取得し、合計値を算出

・セルG2~G11に合計値を出力

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

このようなSUMIF関数についてVBAを使ってプログラムを作ると以下です。

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

'プログラム1｜プログラム開始
Sub Excel_SumIf()

    'プログラム2｜対象シートを設定
    Dim ws As Worksheet
    Set ws = Worksheets("Sheet5")
    
    'プログラム3｜最終行の取得
    Dim cmax As Long
    cmax = ws.Range("A65536").End(xlUp).Row
    
    'プログラム4｜変数設定
    Dim kokyaku As String, torihiki As String
    Dim kingaku As Long, i As Long, j As Long
    
    'プログラム5｜F列の値を取得
    For i = 2 To cmax
        kingaku = 0
        kokyaku = ws.Range("F" & i).Value
        
        'プログラム6｜変数Kokyakuが空欄かどうか確認
        If Not kokyaku = "" Then
            
            'プログラム7｜B列の値を取得
            For j = 2 To cmax
                torihiki = ws.Range("B" & j).Value
                
                'プログラム8｜B列の値とF列の値を比較してC列を加算
                If torihiki = kokyaku Then
                    kingaku = kingaku + ws.Range("C" & j).Value
                End If
            Next
            
            'プログラム9｜G列に出力
            ws.Range("G" & i).Value = kingaku
        End If
    Next

'プログラム10｜プログラムの終了
End Sub

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

Option Explicit

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

Sub Excel_SumIf()

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

Dim ws As Worksheet

Set ws = Worksheets("Sheet5")

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

Dim cmax As Long

cmax = ws.Range("A65536").End(xlUp).Row

'プログラム4｜変数設定

Dim kokyaku As String, torihiki As String

Dim kingaku As Long, i As Long, j As Long

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

For i = 2 To cmax

kingaku = 0

kokyaku = ws.Range("F" & i).Value

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

If Not kokyaku = "" Then

'プログラム7｜B列の値を取得

For j = 2 To cmax

torihiki = ws.Range("B" & j).Value

'プログラム8｜B列の値とF列の値を比較してC列を加算

If torihiki = kokyaku Then

kingaku = kingaku + ws.Range("C" & j).Value

End If

'プログラム9｜G列に出力

ws.Range("G" & i).Value = kingaku

End If

'プログラム10｜プログラムの終了

End Sub

上記のプログラムは以下のぺージで詳細を解説しています。

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

エクセルで条件に合致する数値を累計する場合、Excel関数では「=SUMIF($B$2:$B$21,F2,$C$2:$C$21)」と記載することで、合計値を算出できます。このようにエクセル関数SUMIFで合計値の算出は […]

事例6｜SUMIFS関数をVBAで作る

・取引先名称(B2~B31)とF2~F11、担当者(C2~C31)とG1,H1,I1をそれぞれ突合し、D列の合計値を算出

・合計値をG列~I列に出力します。

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

このようなSUMIF関数についてVBAを使ってプログラムを作ると以下です。

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

'プログラム1｜プログラム開始
Sub Excel_SumIfs()

    'プログラム2｜対象シートを設定
    Dim ws As Worksheet
    Set ws = Worksheets("Sheet6")
    
    'プログラム3｜最終行の取得
    Dim cmax As Long
    cmax = ws.Range("A65536").End(xlUp).Row
    
    'プログラム4｜変数設定
    Dim kokyaku As String, tanto As String
    Dim kingaku As Long, i As Long, j As Long, k As Long
    
    'プログラム5｜F列の取得
    For i = 2 To cmax
        kokyaku = ws.Range("F" & i).Value
        
        'プログラム6｜変数Kokyakuが空欄かどうか確認
        If Not kokyaku = "" Then
            
            'プログラム7｜担当者(G1, H1, I1)を取得
            For j = 0 To 2
                kingaku = 0
                tanto = ws.Range("G1").Offset(0, j).Value
                
                'プログラム8｜B列とF列をマッチング（突合）
                For k = 2 To cmax
                    If ws.Range("B" & k).Value = kokyaku Then
                        
                        'プログラム9｜C列と担当者をマッチング（突合）
                        If ws.Range("C" & k).Value = tanto Then
                            kingaku = kingaku + ws.Range("D" & k).Value
                        End If
                    End If
                Next
            
                'プログラム10｜G列～I列に出力
                ws.Range("G" & i).Offset(0, j).Value = kingaku
            Next
        End If
    Next

'プログラム11｜プログラム終了
End Sub

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

Option Explicit

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

Sub Excel_SumIfs()

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

Dim ws As Worksheet

Set ws = Worksheets("Sheet6")

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

Dim cmax As Long

cmax = ws.Range("A65536").End(xlUp).Row

'プログラム4｜変数設定

Dim kokyaku As String, tanto As String

Dim kingaku As Long, i As Long, j As Long, k As Long

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

For i = 2 To cmax

kokyaku = ws.Range("F" & i).Value

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

If Not kokyaku = "" Then

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

For j = 0 To 2

kingaku = 0

tanto = ws.Range("G1").Offset(0, j).Value

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

For k = 2 To cmax

If ws.Range("B" & k).Value = kokyaku Then

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

If ws.Range("C" & k).Value = tanto Then

kingaku = kingaku + ws.Range("D" & k).Value

End If

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

ws.Range("G" & i).Offset(0, j).Value = kingaku

End If

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

End Sub

上記のプログラムは以下のぺージで詳細を解説しています。

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

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

事例7｜VLOOKUP関数をVBAで作る

・「Sheet7_1のB列」と「Sheet7_2のA列」をマッチング

・「Sheet7_1のE列とF列」に「Sheet7_2のB列とC列」のデータを出力

マスターデータから情報を取得したいとき、Vlookup関数を使うのが一般的です。

たとえば、上記の画像で取り扱っている事例では、Excel関数「=VLOOKUP($B2,Sheet7_2!$A$2:$C$8,COLUMN()-3,FALSE)」と入力して処理を行うことが可能です。

このようなVLOOKUP関数についてVBAを使ってプログラムを作ると以下です。

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

'プログラム1｜プログラム開始
Sub Excel_Vlookup()

    'プログラム2｜対象シートを設定
    Dim Ws1 As Worksheet, Ws2 As Worksheet
    Set Ws1 = Worksheets("Sheet7_1")
    Set Ws2 = Worksheets("Sheet7_2")
    
    'プログラム3｜最終行の取得
    Dim cmax1 As Long, cmax2 As Long
    cmax1 = Ws1.Range("A65536").End(xlUp).Row
    cmax2 = Ws2.Range("A65536").End(xlUp).Row
    
    'プログラム4｜変数設定
    Dim Product_code As String, Master_code As String, Product_name As String
    Dim i As Long, j As Long, Product_price As Long
    
    'プログラム5｜Ws1のB列を取得
    For i = 2 To cmax1
        Product_code = Ws1.Range("B" & i).Value
                   
        'プログラム6｜Ws2のA列を取得
        For j = 2 To cmax2
            Master_code = Ws2.Range("A" & j).Value
            
            'プログラム7｜Ws1のB列とWs2のA列をマッチング
            If Product_code = Master_code Then
                
                'プログラム8｜Ws2のB列とC列を取得
                Product_name = Ws2.Range("B" & j).Value
                Product_price = Ws2.Range("C" & j).Value
                Exit For
            End If
        Next
        
        'プログラム9｜Ws1のE列とF列に出力
        Ws1.Range("E" & i).Value = Product_name
        Ws1.Range("F" & i).Value = Product_price
    Next

'プログラム10｜プログラム終了
End Sub

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

Option Explicit

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

Sub Excel_Vlookup()

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

Dim Ws1 As Worksheet, Ws2 As Worksheet

Set Ws1 = Worksheets("Sheet7_1")

Set Ws2 = Worksheets("Sheet7_2")

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

Dim cmax1 As Long, cmax2 As Long

cmax1 = Ws1.Range("A65536").End(xlUp).Row

cmax2 = Ws2.Range("A65536").End(xlUp).Row

'プログラム4｜変数設定

Dim Product_code As String, Master_code As String, Product_name As String

Dim i As Long, j As Long, Product_price As Long

'プログラム5｜Ws1のB列を取得

For i = 2 To cmax1

Product_code = Ws1.Range("B" & i).Value

'プログラム6｜Ws2のA列を取得

For j = 2 To cmax2

Master_code = Ws2.Range("A" & j).Value

'プログラム7｜Ws1のB列とWs2のA列をマッチング

If Product_code = Master_code Then

'プログラム8｜Ws2のB列とC列を取得

Product_name = Ws2.Range("B" & j).Value

Product_price = Ws2.Range("C" & j).Value

Exit For

End If

'プログラム9｜Ws1のE列とF列に出力

Ws1.Range("E" & i).Value = Product_name

Ws1.Range("F" & i).Value = Product_price

'プログラム10｜プログラム終了

End Sub

上記のプログラムは以下のぺージで詳細を解説しています。

エクセルマクロVBAでVlookup関数の代わりに別シートのデータ参照(最終行まで)

エクセルで別シートからデータを参照したい、Vlookup関数を使うことで対応できます。たとえば「=VLOOKUP($B2,Sheet7_2!$A$2:$C$8,COLUMN()-3,FALSE)」というように、VLOO […]

事例8｜SUMPRODUCT関数をVBAで作る

1. セルH3の製品名とD列で一致するデータを取得

2. 一致したデータの数量と単価から金額を取得

3. 合計金額を算出して、セルI3に出力

条件に合ったデータの計算をおこなうとき、SUMPRODUCT関数を使うと便利です。（実際にはIF関数とSUM関数を組み合わせても対応可能です）

たとえば、上記の画像の事例では「=SUMPRODUCT((D2:D21=$H$3)*(E2:E21),(F2:F21))」で、Excel関数によって処理を行うことが可能です。

このようなSUMPRODUCT関数についてVBAを使ってプログラムを作ると以下です。

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

'プログラム1｜プログラム開始
Sub Excel_SumProduct()

    'プログラム2｜対象シートを設定
    Dim ws As Worksheet
    Set ws = Worksheets("Sheet8")

    'プログラム3｜最終行の取得
    Dim cmax As Long
    cmax = ws.Range("A65536").End(xlUp).Row
    
    'プログラム4｜変数設定
    Dim Goukei As Long, kingaku As Long, i As Long
    
    'プログラム5｜製品名を取得
    Dim Seihin As String
    Seihin = ws.Range("H3").Value
    
    'プログラム6｜D列の製品名を取得
    For i = 2 To cmax
        If Seihin = ws.Range("D" & i).Value Then
            
            'プログラム7｜数量×単価を算出
            kingaku = ws.Range("E" & i).Value * ws.Range("F" & i).Value
        
            'プログラム8｜金額を加算
            Goukei = Goukei + kingaku
        End If
    Next
    
    'プログラム9｜セルI3に合計値を出力
    ws.Range("I3").Value = Goukei
    
'プログラム10｜プログラムの終了
End Sub

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

Option Explicit

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

Sub Excel_SumProduct()

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

Dim ws As Worksheet

Set ws = Worksheets("Sheet8")

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

Dim cmax As Long

cmax = ws.Range("A65536").End(xlUp).Row

'プログラム4｜変数設定

Dim Goukei As Long, kingaku As Long, i As Long

'プログラム5｜製品名を取得

Dim Seihin As String

Seihin = ws.Range("H3").Value

'プログラム6｜D列の製品名を取得

For i = 2 To cmax

If Seihin = ws.Range("D" & i).Value Then

'プログラム7｜数量×単価を算出

kingaku = ws.Range("E" & i).Value * ws.Range("F" & i).Value

'プログラム8｜金額を加算

Goukei = Goukei + kingaku

End If

'プログラム9｜セルI3に合計値を出力

ws.Range("I3").Value = Goukei

'プログラム10｜プログラムの終了

End Sub

上記のプログラムは以下のぺージで詳細を解説しています。

ExcelマクロVBAでSumproduct関数を作成(条件に合うデータを自動計算)

条件に合ったデータの計算をおこなうとき、SUMPRODUCT関数を使うと便利です。（実際にはIF関数とSUM関数を組み合わせても対応可能です）たとえば、本記事でサンプルでは、「=SUMPRODUCT((D2:D21=$ […]

事例9｜SUMIFS関数×日付をVBAで作る

・セルC2~C21の日付、G2~G21の金額を取得

・セルJ3~J13に毎日の金額を出力

エクセルで日付条件に合致する数値を累計する場合、Excel関数では「=SUMIFS($G$2:$G$21,$C$2:$C$21,”>=”&I3,$C$2:$C$21,”<"&I4)」のように記載することで、日付条件を絞り込みます。このようなSUMIFS関数と日付の事例をVBAでプログラムを作ると以下のようになります。

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

'プログラム1｜プログラム開始
Sub Excel_DateHikaku()

    'プログラム2｜対象シートを設定
    Dim ws As Worksheet
    Set ws = Worksheets("Sheet9")
    
    'プログラム3｜最終行の取得
    Dim cmax1 As Long, cmax2 As Long
    cmax1 = ws.Range("I65536").End(xlUp).Row
    cmax2 = ws.Range("A65536").End(xlUp).Row
    
    'プログラム4｜変数設定
    Dim hiduke_start As Date, hiduke_end As Date, hiduke As Date
    Dim kingaku As Long
    Dim i As Long, j As Long
    
    'プログラム5｜I列の日付を取得
    For i = 3 To cmax1
        hiduke_start = ws.Range("I" & i).Value
        hiduke_end = DateAdd("d", 1, hiduke_start)
            
        'プログラム6｜ゼロリセット
        kingaku = 0
    
        'プログラム7｜日付を比較して金額を加算
        For j = 2 To cmax2
            hiduke = ws.Range("C" & j).Value
            If hiduke >= hiduke_start Then
                If hiduke < hiduke_end Then
                    kingaku = kingaku + ws.Range("G" & j).Value
                End If
            End If
        Next
        
        'プログラム8｜J列に出力
        ws.Range("J" & i).Value = kingaku
    Next

'プログラム9｜プログラムの終了
End Sub

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

Option Explicit

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

Sub Excel_DateHikaku()

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

Dim ws As Worksheet

Set ws = Worksheets("Sheet9")

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

Dim cmax1 As Long, cmax2 As Long

cmax1 = ws.Range("I65536").End(xlUp).Row

cmax2 = ws.Range("A65536").End(xlUp).Row

'プログラム4｜変数設定

Dim hiduke_start As Date, hiduke_end As Date, hiduke As Date

Dim kingaku As Long

Dim i As Long, j As Long

'プログラム5｜I列の日付を取得

For i = 3 To cmax1

hiduke_start = ws.Range("I" & i).Value

hiduke_end = DateAdd("d", 1, hiduke_start)

'プログラム6｜ゼロリセット

kingaku = 0

'プログラム7｜日付を比較して金額を加算

For j = 2 To cmax2

hiduke = ws.Range("C" & j).Value

If hiduke >= hiduke_start Then

If hiduke < hiduke_end Then

kingaku = kingaku + ws.Range("G" & j).Value

End If

'プログラム8｜J列に出力

ws.Range("J" & i).Value = kingaku

'プログラム9｜プログラムの終了

End Sub

上記のプログラムは以下のぺージで詳細を解説しています。 [nlink url="https://fastclassinfo.com/entry/excelvba_date/"]

事例10｜COUNTIF関数×ワイルドカードをVBAで作る(Instrを使用)

・セルB2~B21に「ある文字列」が入っていれば、セルC2~セルC21に「該当」

・セルF2に合計件数を出力

ある文字列が含まれているセルの数をカウントする場合、Excel関数では「=COUNTIF(B2:B21,”*愛*”)」と記載します。

COUNTIF関数とワイルドカード(*)を組み合わせるのです。

このような事例についてVBAを使ってプログラムを作ると以下です。

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

'プログラム1｜プログラム開始
Sub Excel_Instr()

    'プログラム2｜対象シートを設定
    Dim ws As Worksheet
    Set ws = Worksheets("Sheet10")
    
    'プログラム3｜最終行の取得
    Dim cmax As Long
    cmax = ws.Range("A65536").End(xlUp).Row
    
    'プログラム4｜検索ワードの設定
    Dim Keyword As String
    Keyword = "愛"

    'プログラム5｜変数定義
    Dim i As Long, Kensu As Long
    Dim torihiki As String
    
    'プログラム6｜B列の値を取得
    For i = 2 To cmax
        torihiki = ws.Range("B" & i).Value
        
        'プログラム7｜B列に検索ワードが含まれるかチェック
        If InStr(torihiki, Keyword) > 0 Then
            ws.Range("C" & i).Value = "該当"
            
            'プログラム8｜件数をカウント
            Kensu = Kensu + 1
        End If
    Next

    'プログラム9｜F2に出力
    ws.Range("F2").Value = Kensu

'プログラム10｜プログラムの終了
End Sub

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

Option Explicit

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

Sub Excel_Instr()

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

Dim ws As Worksheet

Set ws = Worksheets("Sheet10")

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

Dim cmax As Long

cmax = ws.Range("A65536").End(xlUp).Row

'プログラム4｜検索ワードの設定

Dim Keyword As String

Keyword = "愛"

'プログラム5｜変数定義

Dim i As Long, Kensu As Long

Dim torihiki As String

'プログラム6｜B列の値を取得

For i = 2 To cmax

torihiki = ws.Range("B" & i).Value

'プログラム7｜B列に検索ワードが含まれるかチェック

If InStr(torihiki, Keyword) > 0 Then

ws.Range("C" & i).Value = "該当"

'プログラム8｜件数をカウント

Kensu = Kensu + 1

End If

'プログラム9｜F2に出力

ws.Range("F2").Value = Kensu

'プログラム10｜プログラムの終了

End Sub

上記のプログラムは以下のぺージで詳細を解説しています。

ExcelマクロVBAである特定の文字列が含まれる件数をカウント(Instr関数)

ある文字列が含まれているセルの数をカウントする場合、Excel関数では「=COUNTIF(B2:B21,”*愛*”)」と記載します。 COUNTIF関数とワイルドカード(*)を組み合わせるのです。 […]

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

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

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

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

エクセルマクロVBA「できること」は何？100事例で自動化を解説

エクセルマクロVBAを使えば面倒な仕事を自動化して、作業をエクセルに処理させることができます。しかし、エクセルマクロと聞いてもそもそも何か分からない人もいたり、どのようなことができるのか知らなかったりする人は多いです。 […]

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

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

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

VBAの勉強方法

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

成長の過程は以下で紹介しています。

→私のVBA習得の道のり（10ヶ月でVBA取得するまで）

学習の過程では、意識すべきポイントがあります。

特に独学の場合だと、勉強を優先してしまい、肝心な実践を疎かにしがちです。

私の経験では、実践から逆算した勉強が必要だと考えています。

目指すべきは会社でお金をもらいながら勉強することです。

要はVBAを仕事の一つとして上司に認めてもらうのです。

そうすればわざわざ高いお金を払って勉強をする必要がなくなります。

しかも作業を自動化して、会社やチームに貢献しつつ、自らのスキルアップできます。

そのために必要な考え方を以下で紹介しています。

→VBAを作成するときに覚えておきたい18のコツ

とはいえ、プログラミング初心者でVBAについて知識ゼロの人もいるはずです。

いきなり会社でVBAで使うことさえ、とてつもなくハードルが高く見えてしまうものです。

その場合は、VBAの基本について学ぶ必要があります。

たとえば車の運転も慣れてしまえば、たいしたことではありません。

しかし教習所で運転の基本を学び、免許を取得することで、公道で運転できるようになります。

VBAも同じです。VBAに免許はありませんが、まずは基本を学ばないことには会社で使えるレベルにはなりません。

実際に私もプログラミング初心者のときは、動画を見たり書籍を読んだりして勉強しました。

今はオンラインの教材で無料で学習できるものも多いです。

ExcelマクロVBAのオンライン通信講座おすすめ5選｜評判や内容

「ExcelマクロVBAやAccessVBAを学びたいから、オンラインの通信講座を試そう！」　こう考えて、ウェブで調べてみると、いくつも種類があり、どれを選べばいいのか判断に迷います。しかし、初心者には有料のオンライン […]

上記のリンクでは、私の経験から勉強にオススメの教材を紹介しています。

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

VBAを自分で書けるようになる

さて、本記事で紹介したマクロを利用すれば、作業の自動化が可能になります。

しかしデメリットもあります。それはカスタムできないことです。

なぜなら、色々な要望が増えるからです。

この動画を見たとき、「もっと○○ができるのでは？」や「ここはなんとかならないのか」と感じる人は少なくないはずです。

例えば、「他の条件を付け加えたい」や「日付毎に条件を変えたい」といった要望が出るかもしれません。

このような要望を満たすには、マクロを勉強して自力でマクロを編集できるようになる必要があります。

もし、自力でマクロを編集できるようになれば、今より仕事の効率はグッと上がります。

実際、私も自力でマクロを書けるようになってからは、仕事の生産性が一気に上がりました。

他の人が30分～1時間かけて行う仕事が、ボタン一つで終わらせることができるようになったのです。

その結果、周囲からの信頼も増し、仕事で高い評価を得られるようになりました。

ただ、要望に応えるようになるためには、マクロを学ぶ必要があります。

まずは無料でマクロを勉強してみる

ウェブや書籍で勉強すれば、マクロを習得できると考えている人は少なくありません。

しかし、仕事で使えるマクロを習得したいなら、仕事で使える部分に特化した教材で学ぶことをお勧めします。

なぜなら、ウェブや書籍には仕事に関係しない部分まで提供していることが多いからです。

例えば、マクロ初心者なのに配列を学ぼうとする人がいます。実は配列なしでも仕事で使えるマクロを書くことは可能です。

しかし、マクロ初心者ほど「全ての知識が必要だ」と考えて、無駄な学習に時間を使ってしまうのです。詳しくは、こちらの記事で紹介しています。

エクセルマクロVBAのプログラムやコードは全てを覚える必要はない

エクセルマクロVBAを勉強している人の中には、自分の知らないプログラムやコードを必死で理解しようとする人がいます。しかし、仕事で使えるエクセルマクロを習得するためには、プログラムやコードをすべて覚える必要はありません。 […]

エクセルマクロVBAを独学で習得できるか？自学の限界と挫折する理由

エクセルマクロ習得には継続的な学習が必須です。とくにマクロ初心者が独学で習得するには、少なくとも数ヶ月は地道に学習を続ける必要があります。ただ忙しい社会人の場合、毎日30分の勉強時間でさえ確保するのが難しい人もいます。 […]

そこで、私がお勧めするのは仕事に直結するマクロ教材です。とくにお勧めするのは、こちらの無料オンライン動画です。

なぜなら、仕事に直結する部分に絞って、エクセルマクロを学ぶことができるからです。

マクロの作り方・考え方から解説しているので、教材をしっかり学べばここで紹介したマクロをゼロから書けるようになります。

マクロ初心者が、仕事に直結したいマクロを学ぶなら、まずはこちらの無料オンライン動画を試すのがいいです。

興味がある人は、まずは無料でエクセルマクロの勉強を始めてみてください。

もっと学びたいと感じたら、さらに深く勉強をしてみることをお勧めします。

エクセル関数をマクロで作る！10事例でVBAプログラムを解説

なぜ「VBA×エクセル関数」を紹介するのか？

メリット｜VBA初心者にとって良い教材となる

エクセル関数は動作がイメージしやすい

デメリット｜仕事で効率化できるほどのインパクトはない

エクセル関数の場合

エクセルVBAの場合

VBA×エクセル関数｜事前準備

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

Excel関数×VBA｜関数を事例

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

事例1｜SUM関数をVBAで作る

事例2｜IF関数をVBAで作る

事例3｜COUNTIF関数をVBAで作る

事例4｜COUNTIFS関数をVBAで作る

事例5｜SUMIF関数をVBAで作る

事例6｜SUMIFS関数をVBAで作る

事例7｜VLOOKUP関数をVBAで作る

事例8｜SUMPRODUCT関数をVBAで作る

事例9｜SUMIFS関数×日付をVBAで作る

事例10｜COUNTIF関数×ワイルドカードをVBAで作る(Instrを使用)

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

VBAの勉強方法

VBAを自分で書けるようになる

まずは無料でマクロを勉強してみる

人気記事

サイト運営情報

パソコンスキル

仕事術

なぜ「VBA×エクセル関数」を紹介するのか？

メリット｜VBA初心者にとって良い教材となる

エクセル関数は動作がイメージしやすい

デメリット｜仕事で効率化できるほどのインパクトはない

エクセル関数の場合

エクセルVBAの場合

VBA×エクセル関数｜事前準備

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

Excel関数×VBA｜関数を事例

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

事例1｜SUM関数をVBAで作る

事例2｜IF関数をVBAで作る

事例3｜COUNTIF関数をVBAで作る

事例4｜COUNTIFS関数をVBAで作る

事例5｜SUMIF関数をVBAで作る

事例6｜SUMIFS関数をVBAで作る

事例7｜VLOOKUP関数をVBAで作る

事例8｜SUMPRODUCT関数をVBAで作る

事例9｜SUMIFS関数×日付をVBAで作る

事例10｜COUNTIF関数×ワイルドカードをVBAで作る(Instrを使用)

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

VBAの勉強方法

VBAを自分で書けるようになる

まずは無料でマクロを勉強してみる

Footer

人気記事

サイト運営情報

パソコンスキル

仕事術