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

公開日：2022-10-27　最終更新日：2022-10-27

第143回.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を書きやすくしてみましょう。

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


  
    
      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にはスピル新関数が全て入っています。

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関数

上図で、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関数

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関数

最初の図の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（ワークシート関数を使う）

第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の効率的な使い方とスピル新関数の利用

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

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

ホーム
マクロVBA入門編
マクロVBA入門
WorksheetFunctionの効率的な使い方とスピル新関数の利用

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

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

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

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

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

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

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

XLOOKUP関数

SORT関数

SORTBY関数

サイト内の関連ページ

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

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

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

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

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

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

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

XLOOKUP関数

SORT関数

SORTBY関数

サイト内の関連ページ

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

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

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

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