VBA100本ノック 62本目：独自のZLOOKUP関数を作成

公開日：2021-01-08　最終更新日：2021-01-13

VBA100本ノック 62本目：独自のZLOOKUP関数を作成

VLOOKUP関数の機能を変更した独自のZLOOKUP関数を作成する問題です。

ツイッター連動企画です。
ツイートでの見やすさを考慮して、ブック・シート指定等を適宜省略しています。

VBAテスト用のサンプルデータはご自身でご用意ください。

出題

#VBA100本ノック 62本目
VLOOKUPは条件合致の先頭が取得されます。
この取得順番を指定できる関数を作成します。
ZLOOKUP(検索値,範囲,列番号,順番)
順番→0:先頭,-1:最後,>=1:その順番
※当該順がない場合は空白""を返す
※検索値がない場合は#N/Aを返す
※完全一致のみ対応、検索値は単一セル値限定

VBA作成タイム

この下に頂いた回答へのリンクと解説を掲載しています。
途中まででも良いので、できるだけ自分でVBAを書いてみましょう。

他の人の回答および解説を見て、書いたVBAを見直してみましょう。

頂いた回答

出題ツイートの引用ツイートでの回答

解説

引数チェックは仕様として困る範囲で行っています。
ユーザー定義関数では、実行時エラーは#VALUE!を返してくれるのでデータ型に頼りました。
範囲を列指定した場合の対処としてFindメソッドで最終行を取得しています。
配列で処理しましたが直接セルを参照しても良いでしょう。


  
    
      Function ZLOOKUP(ByVal 検索値, ByVal 範囲 As Range, ByVal 列番号 As Long, ByVal 順番 As Long)

　　ZLOOKUP = CVErr(xlErrNA)

　　If 列番号 > 範囲.Columns.Count Then Exit Function

　　If 順番 < -1 Then Exit Function

　　

　　Dim i As Long, lastrow As Long

　　Dim s As Long, e As Long, p As Long

　　lastrow = 範囲.Columns(1).Find(What:="*", LookIn:=xlFormulas, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

　　lastrow = lastrow - 範囲.Row + 1

　　s = 1: e = lastrow: p = 1

　　If 順番 = -1 Then s = lastrow: e = 1: p = -1

　　

　　Dim ary, cnt As Long

　　ary = 範囲.Resize(lastrow).Value

　　If Not IsArray(ary) Then ary = 範囲.Resize(lastrow, 2).Value

　　For i = s To e Step p

　　　　If ary(i, 1) = 検索値 Then

　　　　　　ZLOOKUP = ary(i, 列番号)

　　　　　　If 順番 <= 1 Then Exit Function

　　　　　　cnt = cnt + 1

　　　　　　If cnt = 順番 Then Exit Function

　　　　End If

　　Next

　　If Not IsError(ZLOOKUP) Then ZLOOKUP = ""

End Function

検索値が無い場合の速度対策としては先頭でMatch関数なりで確認したほうが良いのですが、あえてシート関数は使わずに作成してみました。
ユーザー定義関数は処理速度が遅くなりがちですが、便利ですし使えるようになっておくと良いと思います。
補足はありません。

補足

補足はありません。

サイト内関連ページ

第106回．Functionプロシージャー

プログラム（マクロVBA）内で特定の処理を実行し値を返すプロシージャーです。これはつまり、Functionプロシージャーで独自の関数をつくれるということです。Subプロシージャーとの違いは、値を返すか返さないかの違いです。

第107回．プロシージャーの引数

・引数の構文・引数の使用例・引数について

ユーザー定義関数の作り方

・簡単な例でユーザー定義関数を作ってみましょう・この関数の使い方・ユーザー定義関数の実践使用例

スピルに対応したXSPLITユーザー定義関数（文字区切り）

・区切り位置ウィザード・ワークシートの関数で文字区切りする場合・ユーザー定義関数のVBAｺｰﾄﾞ・XSPLIT関数の使用例・ユーザー定義関数の実践使用例

同じテーマ「VBA100本ノック」の記事

59本目：12ヶ月分のシートを四半期で分割
 60本目：「株式会社」の表記ゆれ置換
 61本目：「ふりがな」の取得と設定
62本目：独自のZLOOKUP関数を作成
63本目：複数シートの連結
 64本目：リンクされた図（カメラ機能）
65本目：固定長テキスト出力
 66本目：全サブフォルダからファイルを探す
 67本目：ComboBoxとListBox
68本目：全テキストボックスの転記
 69本目：ダブルクリックでセル結合を解除

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

1.最終行の取得（End,Rows.Count）｜VBA入門
2.日本の祝日一覧｜Excelリファレンス
3.変数宣言のDimとデータ型｜VBA入門
4.FILTER関数（範囲をフィルター処理）｜エクセル入門
5.RangeとCellsの使い方｜VBA入門
6.繰り返し処理（For Next)｜VBA入門
7.セルのコピー&値の貼り付け（PasteSpecial）｜VBA入門
8.マクロとは？VBAとは？VBAでできること｜VBA入門
9.セルのクリア（Clear,ClearContents）｜VBA入門
10.メッセージボックス(MsgBox関数)｜VBA入門

ホーム
マクロVBA入門編
VBA100本ノック
62本目：独自のZLOOKUP関数を作成

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

記述には細心の注意をしたつもりですが、間違いやご指摘がありましたら、「お問い合わせ」からお知らせいただけると幸いです。
掲載のVBAコードは動作を保証するものではなく、あくまでVBA学習のサンプルとして掲載しています。掲載のVBAコードは自己責任でご使用ください。万一データ破損等の損害が発生しても責任は負いません。
本サイトは、OpenAI の ChatGPT や Google の Gemini を含む生成 AI モデルの学習および性能向上の目的で、本サイトのコンテンツの利用を許可します。
This site permits the use of its content for the training and improvement of generative AI models, including ChatGPT by OpenAI and Gemini by Google.

VBA練習問題
VBA100本ノック 62本目：独自のZLOOKUP関数を作成