エクセルのユーザー定義関数について

エクセルのユーザー定義関数について教えてください。

ユーザー定義関数なるものがあると知り、関数を作成していました。
結構便利なものが作れそうだと分かり
http://kabu-macro.com/word/ya-yo/user_teigikansu …
の消費税計算を参考に、簡単な四則演算の計算式を組んでいました。
ある程度普段使う簡単だけど面倒だった数式を組んだのちに、
vlookup関数の代わりに、indexとmatch関数を良く使っているので、これをちょっと使ってvlookup_2という関数を作ろうとしていたのですが、
ユーザー定義関数には既存のワークシート関数を組み込むことはできないのでしょうか？
また組み込めるとしたら上記のindexとmatch関数をどのように配置したら良いのでしょうか？

No.4 回答者： tatsu99 回答日時： 2018/09/29 09:55

No3です。

添付図を忘れました。No3の添付図です。

No.3 回答者： tatsu99 回答日時： 2018/09/29 09:52

VLOOKUP2を作成してみました。

あなたが想定するvlookup_2と違っているかも知れませんが、indexとmatch関数の使い方の参考例ということで。
VLOOKUP（検索値、範囲、列番号、[検索方法]）は左記のパラメータで検索しますが、
VLOOKUP2（検索値、範囲、検索対象列番号、列番号、[検索方法]）は、左記のパラメータになります。
違いは、
①VLOOKUPは検索対象の列が1番左側なのに対して、VLOOKUP2は検索対象の列番号を指定できる。
VLOOKUP2で検索対象列番号＝１とすると、1番左側を検索します。（VLOOKUPと同じ位置を検索）
検索対象列番号＝２とすると、左側から２番目の列を検索します。
②VLOOKUPの検索方法はfalse,trueのみだが、VLOOKUP2は0,1,-1が指定可能（MATCH関数の検索方法に準拠）

添付図が使用例です。
G1へ
=VLOOKUP2(F1,$A$2:$D$5,2,1,0)
と入力し、オートフィルで下へコピー

H1へ
=VLOOKUP2(F1,$A$2:$D$5,2,1,-1)
入力し、オートフィルで下へコピー
しています.

G列の検索方法は0
H列の検索方法は-1
である以外は、同じ式を指定しています。

G2は
=VLOOKUP2(F2,$A$2:$D$5,2,1,0)
となりますが、350がB列にないので、#N/Aとなります。
H2は
=VLOOKUP2(F2,$A$2:$D$5,2,1,-1)
となりますが、350がB列にない場合、東京を表示します。

以下、VLOOKUP2のマクロです。
------------------------------------
Public Function VLOOKUP2(sval, srng, scol, dcol, Optional how = 0)
'sval:検索値
'srng:検索範囲
'scol:検索列
'dcol:表示列
'how:検索方法
Dim ix As Variant
'Application.Volatile
On Error GoTo err_exit
ix = WorksheetFunction.Match(sval, srng.Columns(scol), how)
VLOOKUP2 = WorksheetFunction.Index(srng.Columns(dcol), ix)
Exit Function
err_exit:
VLOOKUP2 = CVErr(xlErrNA)
End Function
--------------------------------------
もし、検索エラーが発生した場合、#N/Aでなく、空白にしたいなら、
err_exit:
VLOOKUP2 = ""
としてください。

上記でわかりずらいのが、srng.Columns(scol)の箇所かと思います。
srngはRangeとして、A2:D2が与えられていますが、matchで検索するのは、B2:B4になります。
このB列のセルの集合がsrng.Columnsで取得できます。
srng.Columns(1)=>A2:A4
srng.Columns(2)=>B2:B4
srng.Columns(3)=>C2:C4
srng.Columns(4)=>D2:D4
scolは2が指定されているので、srng.Columns(2)となり、B2:B4となります。
蛇足ですが
srng.Rows(1)=>A2:D2
srng.Rows(2)=>A3:D3
srng.Rows(3)=>A4:D4
srng.Rows(4)=>A5:D5となります。
この辺のことは、
https://excel-ubara.com/EXCEL/EXCEL906.html
を参照ください。



上記は、WorksheetFunctionを使った例ですが、Applicationを使って、VLOOKUP2を実装した例です。
名前は、VLOOKUP2Aとしています。
--------------------------------------------
Public Function VLOOKUP2A(sval, srng, scol, dcol, Optional how = 0)
'sval:検索値
'srng:検索範囲
'scol:検索列
'dcol:表示列
'how:検索方法
Dim ix As Variant
ix = Application.Match(sval, srng.Columns(scol), how)
VLOOKUP2A = Application.Index(srng.Columns(dcol), ix)
' If IsError(VLOOKUP2A) = True Then
' VLOOKUP2A = ""
' End If
End Function
---------------------------------------------
ApplicationとWorksheetFunctionの違いは、No2の方が述べられていますのでそれを参考に作りました。
もし、検索エラーが発生した時、空白を表示したいなら、コメントをとって
If IsError(VLOOKUP2A) = True Then
VLOOKUP2A = ""
End If
としてください。

この回答へのお礼

ありがとうございます。
イメージぴったりです。vlookupはいちいち再計算になってしまうので、index,matchのほうが数が多くなると楽なんですよね。
Applicationの方も組んでみたいなと思っておりましたので、良い参考資料になります。例題として勉強させていただきます。

お礼日時：2018/09/29 10:36

No.2 回答者： WindFaller 回答日時： 2018/09/29 00:14

こんばんは。

>ユーザー定義関数には既存のワークシート関数を組み込むことはできないのでしょうか？
そうやって、ワークシート関数を使う人もいるでしょうけれども、私は、全くと言ってよいほど、ワークシート関数をユーザー定義関数の中で使うことはありません。

私がずっと何年も使い続けているユーザー定義関数のひとつとして、Holidays というものがあります。年とIndex (1～)を指定すれば、休日を計算で出してくれるというものです。他は、もうほぼ組み込み関数になってしまいました。Join, Unicode など。

>組み込めるとしたら上記のindexとmatch関数をどのように配置したら良いのでしょうか？
どのような数式なのか分かりませんが、こちらは、あえて名付け、逆INDEX関数(InvINDX)

'//
Function inv_INDEX(Rng As Range, srch As Variant, colNum1 As Long, colNum2 As Long)
'rng:データ範囲,srch:検索する語,colNum1:検索する列番号,colNum2:出力する列
With Application
inv_INDEX = .Index(Rng, .Match(srch, Rng.Columns(colNum1), 0), colNum2)
End With
End Function
'//
WorksheetFunction.～を使わないのは、エラーが、VBAとしての全体のエラーになってしまうからで、Application.～の場合は、エラーは、エラー値を吐き出して、VBAとしててはエラーにはなっていないからです。(言葉の説明では理解しにくいでしょう)

WorksheetFunction としては関数がない場合は、Evaluate　を利用します。

なお、ユーザー定義関数だからと、頻繁にVolatileメソッドを使う人がいますが、無意味にCalculateイベントが走ってしまい、あまり多用するのは関心しません。

ワークシート関数の数式
=INDEX($A$2:$C$11,MATCH(6,$C$2:$C$11,0),1)

ユーザー定義関数
=inv_INDEX($A$2:$C$11,6,3,1) '3列目から、6を探し、その該当する1列目は、"F"

使った表

この回答へのお礼

ワークシート関数を組みこむとエラーが出たときに全体エラーになるですか。
エラーを返してくれる分、Application.の場合の方が便利なのかなと思いました。
こちらも勉強してみます。ありがとうございます。

お礼日時：2018/09/29 10:31

No.1 ベストアンサー 回答者： fujillin 回答日時： 2018/09/28 16:01

こんにちは

ユーザ定義関数は（マクロと同様）VBAという言語で記述しています。
ワークシートで利用する関数も（ほとんどのものは）利用することが可能です。

利用法は、例えばINDEX関数であれば
　WorksheetFunction.Index( ～～～ ）
のように、WorksheetFunctionオブジェクトのメソッドとして呼び出せばよいです。
引数の内容はシートの関数とほとんど同じですが、一応、事前に確認なさった方がよろしいかと。
https://docs.microsoft.com/ja-jp/office/vba/api/ …

なお、VBAから利用できるワークシート関数の一覧が以下にありますので、ご参考までに。
https://msdn.microsoft.com/ja-jp/library/office/ …
https://docs.microsoft.com/ja-jp/office/vba/api/ …

この回答へのお礼

お返事遅れて申し訳ありません。
ご返信いただいてから外勤に出ていまして先ほど実行してみました。
教えていただいた構文がわからなくて何で動かないの？になっておりました。
おかげ様でワークシート関数が使えるようになりましたのでまだ未完成ですが、望みに近くなってきました。

お礼日時：2018/09/29 10:26