VBA入門
WorksheetFunctionの効率的な使い方とスピル新関数の利用

ExcelマクロVBAの基本と応用、エクセルVBAの初級・初心者向け解説
公開日:2022-10-27 最終更新日:2022-10-27

第143回.WorksheetFunctionの効率的な使い方とスピル新関数の利用


マクロ VBA WorksheetFunction スピル新関数

ワークシート関数には、VBAにはない豊富かつ強力な関数が多数存在します。
ワークシート関数を使う事で、VBAコードを非常に簡潔に記述することが出来る場合が多くあります。


その為にも、基本的なワークシート関数はある程度は使えるようにしておいてください。

エクセル入門
・0からのエクセル入門 ・ショートカットキー ・基本操作 ・表示形式 ・セルの書式設定 ・入力規則 ・数式・関数 ・スピルと新関数 ・LAMBDA以降に追加された関数 ・・・ Excel2021では使用できません ・Microsoft 365 Insider
ここに掲載してある関数は、どのような関数があるかくらいは最低限見ておくことをお勧めします。

ここでは、WorksheetFunctionの効率的な使い方とスピル新関数の利用について解説します。
WorksheetFunctionの基本的な使い方については以下をお読みください。

第87回.WorksheetFunction(ワークシート関数を使う)
・ワークシート関数の使い方 ・WorksheetFunctionで使用できる関数 ・個別の関数の使い方 ・関数の結果(戻り値) ・WorksheetFunctionの使用例. ・検索系の関数での日付の扱い ・WorksheetFunctionのエラー対処 ・最後に


WorksheetFunctionをオブジェクト変数に入れて使用する

WorksheetFunctionはとても文字数が多く、VBA記述の中で横幅をとって読みずらくなってしまう場合があります。
入力自体は、
Ctrl+J → w
ここまで入れれば、

マクロ VBA WorksheetFunction

このように候補表示されますのでそれほど問題はありませんが、
VBAのコードを見た時に、その文字数の多さもあり全体が読みずらくなる場合もあります。
このWorksheetFunctionをオブジェクト変数に入れて使う事でVBAを書きやすくしてみましょう。

まずは、だらだらと書いた状態から少しずつ変更していきます。

Sub sample1()
  Dim i As Long, r As Long
  For i = 2 To ActiveSheet.Cells(ActiveSheet.Rows.Count, 5).End(xlUp).Row
    If WorksheetFunction.CountIf(ActiveSheet.Columns("A"), ActiveSheet.Cells(i, 5).Value) > 0 Then
      r = WorksheetFunction.Match(ActiveSheet.Cells(i, 5).Value, ActiveSheet.Columns("A"), 0)
      ActiveSheet.Cells(i, 6).Value = ActiveSheet.Cells(r, 2).Value
      ActiveSheet.Cells(i, 7).Value = ActiveSheet.Cells(r, 3).Value
    Else
      ActiveSheet.Cells(i, 6).Value = ""
      ActiveSheet.Cells(i, 7).Value = ""
    End If
  Next
End Sub

さすがに読みずらいですね。
ActiveSheetが何度も出てきているので、まずはこのActiveSheetを変数に入れてみましょう。

Sub sample2()
  Dim ws As Worksheet:  Set ws = ActiveSheet
  Dim i As Long, r As Long
  For i = 2 To ws.Cells(ws.Rows.Count, 5).End(xlUp).Row
    If WorksheetFunction.CountIf(ws.Columns("A"), ws.Cells(i, 5).Value) > 0 Then
      r = WorksheetFunction.Match(ws.Cells(i, 5).Value, ws.Columns("A"), 0)
      ws.Cells(i, 6).Value = ws.Cells(r, 2).Value
      ws.Cells(i, 7).Value = ws.Cells(r, 3).Value
    Else
      ws.Cells(i, 6).Value = ""
      ws.Cells(i, 7).Value = ""
    End If
  Next
End Sub

少し見やすくなりました。
では本題のWorksheetFunctionをオブジェクト変数に入れて使います。

Sub sample3()
  Dim ws As Worksheet:  Set ws = ActiveSheet
  Dim wsf As WorksheetFunction: Set wsf = WorksheetFunction
  Dim i As Long, r As Long
  For i = 2 To ws.Cells(ws.Rows.Count, 5).End(xlUp).Row
    If wsf.CountIf(ws.Columns("A"), ws.Cells(i, 5).Value) > 0 Then
      r = wsf.Match(ws.Cells(i, 5).Value, ws.Columns("A"), 0)
      ws.Cells(i, 6).Value = ws.Cells(r, 2).Value
      ws.Cells(i, 7).Value = ws.Cells(r, 3).Value
    Else
      ws.Cells(i, 6).Value = ""
      ws.Cells(i, 7).Value = ""
    End If
  Next
End Sub

コードの見た目もありますが、何よりコードを書くときに楽になると思います。
WorksheetFunctionの中の個別の関数をオブジェクト変数に入れることは出来ません。
WorksheetFunctionをオブジェクト変数に入れて、その中の関数を使うようにします。

シートのwsをWithで定義したりと工夫の余地はいろいろありそうです。

Sub sample4()
  Dim ws As Worksheet:  Set ws = ActiveSheet
  Dim wsf As WorksheetFunction: Set wsf = WorksheetFunction
  Dim i As Long, r As Long
  With ws
    For i = 2 To .Cells(.Rows.Count, 5).End(xlUp).Row
      If wsf.CountIf(.Columns("A"), .Cells(i, 5).Value) > 0 Then
        r = wsf.Match(.Cells(i, 5).Value, .Columns("A"), 0)
        .Cells(i, 6).Value = .Cells(r, 2).Value
        .Cells(i, 7).Value = .Cells(r, 3).Value
      Else
        .Cells(i, 6).Value = ""
        .Cells(i, 7).Value = ""
      End If
    Next
  End With
End Sub

Withを使用すると、インデントが1段下がることもありますし、先頭ピリオドの付け忘れ等の発生もあるので好みが分かれるところだと思います。
今回は、Withについては本題ではないので、この下では使わずに進めます。


Functionの中でWorksheetFunctionを使う事でエラー処理を簡潔にする

先のVBAでは、Match関数で検索値なしの場合の対応としてCountIf関数で件数確認してからMatch関数を使いました。
Match関数のエラー対策としてはOn Error Resume Nextを使う方が一般的かもしれません。

Sub sample11()
  Dim ws As Worksheet:  Set ws = ActiveSheet
  Dim wsf As WorksheetFunction: Set wsf = WorksheetFunction
  Dim i As Long, r As Long
  On Error Resume Next
  For i = 2 To ws.Cells(ws.Rows.Count, 5).End(xlUp).Row
    Err.Clear
    r = wsf.Match(ws.Cells(i, 5).Value, ws.Columns("A"), 0)
    If Err.Number = 0 Then
      ws.Cells(i, 6).Value = ws.Cells(r, 2).Value
      ws.Cells(i, 7).Value = ws.Cells(r, 3).Value
    Else
      ws.Cells(i, 6).Value = ""
      ws.Cells(i, 7).Value = ""
    End If
  Next
End Sub

もちろん、これはこれで良いのですが、VBAを書いているとエラー処理というのは結構面倒なものに感じます。
また、On Error Resume Nextを記述してしまうと、関係のないエラーも素通りしてしまう弊害もあります。
出来ればOn Error Resume Nextは広い範囲で使いたくありません。
そこで、
On Error Resume NextとMatch関数をセットにしてFunctionを作成します。

Sub sample12()
  Dim ws As Worksheet:  Set ws = ActiveSheet
  Dim i As Long, r As Long
  For i = 2 To ws.Cells(ws.Rows.Count, 5).End(xlUp).Row
    r = wsfMatch(ws.Cells(i, 5).Value, ws.Columns("A"))
    If r > 0 Then
      ws.Cells(i, 6).Value = ws.Cells(r, 2).Value
      ws.Cells(i, 7).Value = ws.Cells(r, 3).Value
    Else
      ws.Cells(i, 6).Value = ""
      ws.Cells(i, 7).Value = ""
    End If
  Next
End Sub

Function wsfMatch(ByVal sFind, ByVal sRange) As Long
  On Error Resume Next
  wsfMatch = 0
  wsfMatch = WorksheetFunction.Match(sFind, sRange, 0)
End Function

本体のVBAは大分すっきりしてきました。
VBAを書いていく中で.Match関数をどこで使ったとしても、エラー処理は気にしなくて良くなります。
(検索値がない場合の処理は必要ですが。)

これでも良いのですが、せっかくなのでセル消去部分を一か所にまとめたり、
最終行判定もCurrentRegionに変更してみます。
・最終行取得の基本:手動ではCtrl + ↑、VBAではCells(1, 1).End(xlDown) ・最終列の取得 ・特殊な表の場合 ・CurrentRegion ・SpecialCells(xlCellTypeLastCell) ・UsedRange ・Findメソッド ・サイト内関連ページ

Sub sample13()
  Dim ws As Worksheet:  Set ws = ActiveSheet
  Dim rng As Range: Set rng = ws.Range("E1").CurrentRegion
  Set rng = Intersect(rng.Cells, rng.Offset(1))
  rng.Offset(, 1).ClearContents
  
  Dim r As Long, tRng As Range
  For Each tRng In rng.Columns(1).Cells
    r = wsfMatch(tRng.Value, ws.Columns("A"))
    If r > 0 Then
      tRng.Offset(, 1).Resize(, 2).Value = ws.Cells(r, 2).Resize(, 2).Value
    End If
  Next
End Sub

Function wsfMatch(ByVal sFind, ByVal sRange) As Long
  On Error Resume Next
  wsfMatch = 0
  wsfMatch = WorksheetFunction.Match(sFind, sRange, 0)
End Function

このくらいまで書けるようになれば、後はご自身で読みやすく書きやすいVBAにしていけば良いと思います。


WorksheetFunctionのスピル新関数を利用する

WorksheetFunctionにはスピル新関数が全て入っています。

マクロ VBA WorksheetFunction スピル関数
FILTER関数(範囲をフィルター処理)
・FILTER関数の書式 ・FILTER関数使用例のサンプルデータ ・FILTER関数の基本 ・空白セルを0ではなく空白にする場合 ・複数条件のフィルター ・関数を使ってフィルター ・横(列)でフィルター ・表示する列を選択する ・FILTER関数の結果を他の関数で使う ・スピルによって新しく追加された関数
SORT関数、SORTBY関数(範囲を並べ替え)
・SORT関数の書式 ・SORTBY関数の書式 ・SORT関数、SORTBY関数と、ワークシートの並べ替えの違い ・最も単純な並べ替え ・複数キーでの並べ替え ・列方向(横方向)で並べ替え ・並べ替え範囲(配列)以外の基準で並べ替える ・列全体を範囲指定する場合 ・スピルによって新しく追加された関数
UNIQUE関数(一意の値)
・UNIQUE関数の書式 ・ワークシートでの一意化(ユニーク化) ・UNIQUE関数の使用例 ・UNIQUE関数の応用例 ・スピルによって新しく追加された関数
RANDARRAY関数(ランダム数値)
・RANDARRAY関数の書式 ・RANDARRAY関数の使用例 ・スピルによって新しく追加された関数
SEQUENCE関数(連続数値)
・SEQUENCE関数の書式 ・SEQUENCE関数の使用例 ・SEQUENCE関数を使ったエクセル問題 ・スピルによって新しく追加された関数
XLOOKUP関数(VLOOKUP関数を拡張した新関数)
・XLOOKUP関数の書式 ・従来の関数の代わりとして ・XLOOKUP関数をスピルさせる ・見つからない場合 ・一致モードの使い方 ・検索モードの使い方 ・XLOOKUP関数をネストして戻り列を可変にする ・XLOOKUP関数の戻りセル範囲を別の関数で使う ・XLOOKUP関数が縦横に同時にスピルしないことについて ・スピルと新関数の練習 ・XLOOKUP関数のVBA使用例 ・スピルによって新しく追加された関数
XMATCH関数(MATCH関数を拡張した新関数)
・XMATCH関数の書式 ・従来のMATCH関数との違い ・一致モードの使い方 ・検索モードの使い方 ・XMATCH関数をスピルさせる ・XMATCHの応用例 ・スピルによって新しく追加された関数
以下では、XLOOKUP , SORT , SORTBY について実際のVBAコードで紹介します。
他の関数も同様にVBAで使えますので実際に試してみてください。。

ただし、シート関数には件数制限があるので、この点だけは注意してください。
VBAでシート関数使用時の配列要素数制限
・FILTER関数 ・SORT関数、SORTBY関数 ・UNIQUE関数 ・XLOOKUP関数、HLOOKUP関数、VLOOKUP関数 ・XMATCH関数、MATCH関数 ・TRANSPOSE関数 ・VBAでシート関数使用時の配列要素数制限まとめ
上記ページで見てもらった通り、それなりに大きな件数も扱えますので、
件数制限があることだけ覚えておいて、大量データを扱う時に思い出してもらえれば良いと思います。

XLOOKUP関数

マクロ VBA サンプル画像

上図で、E2:E4のidでA列を検索し、対応するB:C列の値をF2:G4に出力します。

Sub sample21()
  Dim ws As Worksheet:  Set ws = ActiveSheet
  Dim rng As Range:    Set rng = ws.Range("E1").CurrentRegion
  Set rng = Intersect(rng.Cells, rng.Offset(1))
  rng.Offset(, 1).ClearContents
  
  Dim r As Range
  For Each r In rng.Columns(1).Cells
    r.Offset(, 1).Resize(, 2).Value = wsfXlookup(r, ws.Columns("A"), ws.Columns("B:C"))
  Next
End Sub

Function wsfXlookup(ByVal sFind, ByVal sRange, ByVal rRng As Range)
  Dim v, arr
  ReDim arr(1 To rRng.Columns.Count)
  wsfXlookup = WorksheetFunction.XLookup(sFind, sRange, rRng, arr)
End Function

XLOOKUPでは検索値がない場合の引数があるのでエラー処理は必要ないのですが、
逆に戻り範囲を複数列で取得できるため、配列処理が必要になります。
上記では、この配列処理をするためのFunctionを作成して、取得が1列でも複数列でも使えるようにしています。
ただし、もちろん結果を入れるセル範囲の記述は取得列数に応じて変更が必要になります。

SORT関数

マクロ VBA サンプル画像

A:C列をA列で並べ替えて、E:G列に出力します。

Sub sample22()
  Dim ws As Worksheet:  Set ws = ActiveSheet
  Dim rng As Range:    Set rng = ws.Range("A1").CurrentRegion
  Set rng = Intersect(rng.Cells, rng.Offset(1))
  
  Dim rng2 As Range
  Set rng2 = ws.Range("E2").Resize(rng.Rows.Count, rng.Columns.Count)
  
  rng2 = WorksheetFunction.Sort(rng)
End Sub

これは普通に使ってもらえれば良いと思います。
最後の1行は.Valueをあえて書きませんでした。
もちろん書いて良いのですが、
シート関数なのでRangeオブジェクトでも配列でもどっちを入れても良いし、むしろ書かない方が自然な感じさえします。

SORTBY関数

マクロ VBA サンプル画像

マクロ VBA サンプル画像

最初の図のA:C列に都道府県コードを付加し、都道府県コード順で並べ替えてE:H列に出力します。

Sub sample23()
  Dim ws As Worksheet:  Set ws = ActiveSheet
  Dim rng As Range:    Set rng = ws.Range("A1").CurrentRegion
  Set rng = Intersect(rng.Cells, rng.Offset(1))
  Dim arr: arr = rng.Value
  ReDim Preserve arr(1 To UBound(arr, 1), 1 To UBound(arr, 2) + 1)
  
  Dim wsf As WorksheetFunction: Set wsf = WorksheetFunction
  
  Dim ws県 As Worksheet: Set ws県 = Worksheets("都道府県")
  Dim i As Long
  For i = LBound(arr) To UBound(arr)
    arr(i, 4) = wsf.XLookup(arr(i, 3), ws県.Range("A:A"), ws県.Range("B:B"), "")
  Next
  arr = wsf.SortBy(arr, wsf.Index(arr, 0, 4), 1)
  
  ws.Range("E2").Resize(UBound(arr, 1), UBound(arr, 2)) = arr
End Sub

基準配列を作り出すために、あえてINDEX関数を使いました。
この基準配列は上記処理ではForループ内でも作れるのですが、INDEX関数の使用例の紹介も兼ねて使っています。

SORTBY関数のシートでの構文は、
=SORTBY(配列,基準配列,[並べ替え順序],...)
このように、[並べ替え順序]は省略可能となっているのですが、VBAで使う時は「並べ替え順序」は必須となっています。
理由は不明ですが、
1 : 昇順
-1 : 降順
指定はこれだけですので、特に困る事は無いと思います。


サイト内の関連ページ

第87回.WorksheetFunction(ワークシート関数を使う)
・ワークシート関数の使い方 ・WorksheetFunctionで使用できる関数 ・個別の関数の使い方 ・関数の結果(戻り値) ・WorksheetFunctionの使用例. ・検索系の関数での日付の扱い ・WorksheetFunctionのエラー対処 ・最後に
第52回.オブジェクト変数とSetステートメント
・オブジェクト変数 ・個有のオブジェクト型とは ・Setステートメント ・Setステートメントの使用例 ・WithとSetの使い分け方 ・Setステートメントの実践的な使い方 ・Is演算子によるオブジェクトの比較 ・最後に
新関数SORTBYをVBAで利用するラップ関数を作成
新関数はスピルに対応していてとても便利です。新関数はVBAからもWorksheetFunctionで利用できます。シート関数には件数制限があるので使用する場合は注意が必要ですが、その範囲内ならVBAでもとても便利に利用できます。
VBA100本ノック 15本目:シートの並べ替え|VBA練習問題
・出題 ・頂いた回答 ・解説 ・補足 ・サイト内関連ページ
VBA100本ノック 36本目:列の並べ替え|VBA練習問題
・出題 ・頂いた回答 ・解説 ・補足 ・サイト内関連ページ
スピルでVBAの何が変わったか|VBA技術解説
・Rangeオブジェクトに追加されたプロパティ ・セルに数式を設定する場合 ・1次元配列を返すユーザー定義関数 ・2次元配列を返すユーザー定義関数 ・JAG配列を返すユーザー定義関数 ・スピルのVBAでの活用について




同じテーマ「マクロVBA入門」の記事

第130回.テーブル操作の概要(ListObject)
第131回.テーブル操作のVBAコード(ListObject,DataBodyRange)
第142回.テーブル全件処理とデータ最終行(ListObject,DataBodyRange)
第127回.他のブックのマクロを実行(Runメソッド)
第128回.マクロをショートカットで起動(OnKeyメソッド)
第129回.レジストリの操作(SaveSetting,GetSetting,GetAllSettings,DeleteSetting)
第133回.引数の数を可変にできるパラメーター配列(ParamArray)
第134回.Errオブジェクトとユーザー定義エラー
第138回.外部ライブラリ(ActiveXオブジェクト)
第140回.Property {Get|Let|Set} ステートメント
第143回.WorksheetFunctionの効率的な使い方とスピル新関数の利用


新着記事NEW ・・・新着記事一覧を見る

ExcelマクロVBA入門目次|エクセルの神髄(2024-03-20)
VBA10大躓きポイント(初心者が躓きやすいポイント)|VBA技術解説(2024-03-05)
テンキーのスクリーンキーボード作成|ユーザーフォーム入門(2024-02-26)
無効な前方参照か、コンパイルされていない種類への参照です。|エクセル雑感(2024-02-17)
初級脱出10問パック|VBA練習問題(2024-01-24)
累計を求める数式あれこれ|エクセル関数応用(2024-01-22)
複数の文字列を検索して置換するSUBSTITUTE|エクセル入門(2024-01-03)
いくつかの数式の計算中にリソース不足になりました。|エクセル雑感(2023-12-28)
VBAでクリップボードへ文字列を送信・取得する3つの方法|VBA技術解説(2023-12-07)
難しい数式とは何か?|エクセル雑感(2023-12-07)


アクセスランキング ・・・ ランキング一覧を見る

1.最終行の取得(End,Rows.Count)|VBA入門
2.RangeとCellsの使い方|VBA入門
3.セルのコピー&値の貼り付け(PasteSpecial)|VBA入門
4.繰り返し処理(For Next)|VBA入門
5.変数宣言のDimとデータ型|VBA入門
6.ブックを閉じる・保存(Close,Save,SaveAs)|VBA入門
7.並べ替え(Sort)|VBA入門
8.条件分岐(IF)|VBA入門
9.セルのクリア(Clear,ClearContents)|VBA入門
10.マクロとは?VBAとは?VBAでできること|VBA入門




このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。


記述には細心の注意をしたつもりですが、
間違いやご指摘がありましたら、「お問い合わせ」からお知らせいただけると幸いです。
掲載のVBAコードは動作を保証するものではなく、あくまでVBA学習のサンプルとして掲載しています。
掲載のVBAコードは自己責任でご使用ください。万一データ破損等の損害が発生しても責任は負いません。



このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。
本文下部へ