image

VLOOKUP、COUNTIF、SUMIFなどの関数は、ワークシート関数と呼ばれます。通常、Excelで事前定義されており、ワークシートですぐに使用できる関数は、ワークシート関数です。 VBAでこれらの関数の背後にあるコードを変更したり表示したりすることはできません。

一方、ユーザー定義関数およびMsgBoxやInputBoxなどのVBAに固有の関数はVBA関数です。

私たちは皆、VBAでVBA関数を使用する方法を知っています。しかし、VBAでVLOOKUPを使用したい場合はどうでしょうか。どうすればいいですか？この記事では、まさにそれを探求します。

VBAでのワークシート関数の使用

image

ワークシート関数にアクセスするには、Applicationクラスを使用します。ほとんどすべてのワークシート関数は、Application.Worksheet関数クラスにリストされています。そして、ドット演算子を使用して、それらすべてにアクセスできます。

任意のサブに、Application.Worksheet関数を記述します。そして、関数の名前を書き始めます。 VBAのインテリセンスには、使用可能な関数の名前が表示されます。関数名を選択すると、Excelの他の関数と同様に、変数が要求されます。ただし、変数をVBAで理解できる形式で渡す必要があります。たとえば、範囲A1：A10を渡す場合は、Range（ “A1：A10″）のような範囲オブジェクトとして渡す必要があります。

それでは、いくつかのワークシート関数を使用して、それをよりよく理解しましょう。

VBAでVLOOKUP関数を使用する方法

image

VBAでVLOOKUP関数を使用する方法を示すために、ここにサンプルデータがあります。 VBAを使用して、指定されたログインIDの名前と都市をメッセージボックスに表示する必要があります。データは範囲A1：K26に分散されます。

ALT + F11を押してVBEを開き、モジュールを挿入します。

以下のコードを参照してください。

Sub WsFuncitons()



Dim loginID As String

Dim name, city As String



loginID = "AHKJ_1-3357042451"



'Using VLOOKUP function to get name of given id in table

name = Application.WorksheetFunction.VLookup(loginID, Range("A1:K26"), 2, 0)



'Using VLOOKUP function to get city of given id in table

city = Application.WorksheetFunction.VLookup(loginID, Range("A1:K26"), 4, 0)



MsgBox ("Name: " & name & vbLf & "City: " & city)



End Sub

このコードを実行すると、この結果が得られます。

image

VBAが結果をメッセージボックスに出力する速度を確認できます。それでは、コードを調べてみましょう。

どのように機能しますか？

1.

Dim loginID As String Dim name、city As String最初に、VLOOKUP関数によって返される結果を格納するために、文字列型の2つの変数を宣言しました。 VLOOKUPによって返される結果が文字列値になると確信しているため、文字列型変数を使用しました。ワークシート関数が数値、日付、範囲などのタイプの値を返すことが予想される場合は、その種類の変数を使用して結果を格納します。ワークシート関数によって返される値の種類がわからない場合は、バリアント型変数を使用してください。

2.

loginID = “AHKJ_1-3357042451”

次に、loginID変数を使用してルックアップ値を格納しました。ここでは、ハードコードされた値を使用しました。参照も使用できます。例えば。

Range（ “A2″）。Valueを使用して、範囲A2からルックアップ値を動的に更新できます。

3.

name = Application.WorksheetFunction.VLookup（loginID、Range（ “A1：K26″）、2、0）

ここでは、VLOOKUP関数を使用して取得します。関数を右揃えにして括弧を開くと、必要な引数が表示されますが、Excelに表示されるほど説明的ではありません。自分で見て。

image

どの変数をどのように使用する必要があるかを覚えておく必要があります。いつでもワークシートに戻って、記述変数の詳細を確認できます。

ここで、ルックアップ値はArg1です。 Arg1の場合、loginIDを使用します。ルックアップテーブルはArg2です。 Arg2には、Range（ “A1：K26″）を使用しました。 ExcelのようにA2：K26を直接使用しなかったことに注意してください。列のインデックスはArg3です。

Arg3の場合、名前が2番目の列にあるため、2を使用しました。ルックアップタイプはArg4です。 Arg4として0を使用しました。

city = Application.WorksheetFunction.VLookup（loginID、Range（ “A1：K26″）、4、0）

同様に、都市名を取得します。

4.

MsgBox（ “名前：”＆名前＆vbLf＆ “都市：”＆都市）

最後に、Messageboxを使用して名前と都市を印刷します。

VBAでワークシート関数を使用する理由ワークシート関数は膨大な計算能力を備えており、ワークシート関数の能力を無視するのは賢明ではありません。たとえば、データセットの標準偏差が必要で、そのためのコード全体を記述したい場合、数時間かかることがあります。ただし、VBAでワークシート関数STDEV.Pを使用して、一度に計算を行う方法を知っている場合。
Sub GetStdDev()

std = Application.WorksheetFunction.StDev_P(Range("A1:K26"))

End Sub

image

複数のワークシート関数の使用VBA

いくつかの値を取得するためにインデックス一致を使用する必要があるとしましょう。では、VBAで数式をどのように記述しますか。これは私があなたが書くと思うものです：

Sub IndMtch()

Val = Application.WorksheetFunction.Index(result_range, _

Application.WorksheetFunction.Match(lookup_value, _

lookup_range, match_type))

End Sub

これは間違いではありませんが、長いです。複数の関数を使用する正しい方法は、Withブロックを使用することです。以下の例を参照してください：

Sub IndMtch()

With Application.WorksheetFunction

Val = .Index(result_range, .Match(lookup_value, lookup_range, match_type))

val2 = .VLookup(arg1, arg2, arg3)

val4 = .StDev_P(numbers)

End With

End Sub

image

ご覧のとおり、Withブロックを使用して、Application.WorksheetFunctionのプロパティと関数を使用することをVBAに通知しました。だから私はどこでもそれを定義する必要はありません。ドット演算子を使用して、INDEX、MATCH、VLOOKUP、およびSTDEV.P関数にアクセスしました。 End Withステートメントを使用すると、完全修飾関数名を使用せずに関数にアクセスすることはできません。

したがって、VBAで複数のワークシート関数を使用する必要がある場合は、ブロックとともに使用してください。

すべてのワークシート関数がApplication.WorksheetFunctionから使用できるわけではありません。一部のワークシート関数は、VBAで直接使用できます。 Application.WorksheetFunctionオブジェクトを使用する必要はありません。

たとえば、文字列、左、右、中央、トリム、オフセットなどの文字数を取得するために使用されるLen（）のような関数。これらの関数はVBAで直接使用できます。これが例です。

Sub GetLen()

Strng = "Hello"

Debug.Print (Len(strng))

End Sub

image

ここでは、Application.WorksheetFunctionオブジェクトを使用せずにLEN関数を使用しました。

同様に、left、right、mid、charなどの他の関数を使用できます。

Sub GetLen()



Strng = "Hello"

Debug.Print (Len(strng))

Debug.Print (left(strng,2))

Debug.Print (right(strng,1))

Debug.Print (Mid(strng, 3, 2))

End Sub

When you run the above sub, it will return:

5

He

o

ll

ExcelのVBAでVLOOKUPのようなワークシート関数を使用する方法に関するこの記事が説明的であることを願っています。 VBAの数式と関連するExcelの数式に関するその他の記事はこちらからご覧ください。私たちのブログが気に入ったら、Facebookで友達と共有してください。また、TwitterやFacebookでフォローすることもできます。皆様からのご意見をお待ちしております。私たちの仕事を改善、補完、革新し、より良いものにする方法をお知らせください。 [email protected]までご連絡ください。

関連記事：

link：/ excel-macros-and-vba-what-is-csng-function-in-excel-vba [Excel VBAのCSng関数とは]：SCng関数は、任意のデータ型をに変換するVBA関数です。単一精度の浮動小数点数（「それが数値である場合」）。私は主にCSng関数を使用して、テキスト形式の数値を実際の数値に変換します。

link：/ vba-how-to-get-text-number-in-reverse-through-vba-in-microsoft-excel [MicrosoftExcelのVBAを介してテキストと番号を逆に取得する方法]：番号を逆にし、テキストは、VBAでループとmid関数を使用します。 1234は4321に変換され、「you」は「uoy」に変換されます。これがスニペットです。

link：/ formating-in-vba-format-table-depending-on-number-formats-using-vba-in-microsoft-excel [MicrosoftExcelのVBAを使用してカスタム番号形式でデータをフォーマットする]：番号を変更するにはExcelの特定の列の形式は、このVBAスニペットを使用します。ワンクリックで指定フォーマットから指定フォーマットまでの数値フォーマットをカバーします。

link：/ Tips-using-worksheet-change-event-to-run-macro-when-any-change-is-made [ワークシート変更イベントを使用して変更が加えられたときにマクロを実行する]：マクロを実行するにはシートが更新されるたびに、VBAのワークシートイベントが使用されます。

link：/ events-in-vba-run-macro-if-any-change-made-on-sheet-range [指定された範囲のシートに変更が加えられた場合にマクロを実行する]：値が次の場合にマクロコードを実行します指定された範囲の変更では、このVBAコードを使用します。指定された範囲で行われた変更を検出し、イベントを発生させます。

link：/ events-in-vba-simplest-vba-code-to-highlight-current-row-and-column-using [現在の行と列を強調表示する最も簡単なVBAコード]：この小さなVBAスニペットを使用して強調表示しますシートの現在の行と列。

人気のある記事：

link：/ Tips-if-condition-in-excel [ExcelでIF関数を使用する方法]：ExcelのIFステートメントは、条件をチェックし、条件がTRUEの場合は特定の値を返し、FALSEの場合は別の特定の値を返します。 。

link：/ forms-and-functions-introduction-of-vlookup-function [ExcelでVLOOKUP関数を使用する方法]：これは、さまざまな範囲から値を検索するために使用される、Excelで最も使用され人気のある関数の1つです。とシート。

link：/ excel-formula-and-function-excel-sumif-function [ExcelでSUMIF関数を使用する方法]：これはもう1つのダッシュボードの必須関数です。これは、特定の条件で値を合計するのに役立ちます。

link：/ Tips-countif-in-microsoft-excel [ExcelでCOUNTIF関数を使用する方法]：この驚くべき関数を使用して条件付きの値をカウントします。特定の値をカウントするためにデータをフィルタリングする必要はありません。ダッシュボードを準備するには、Countif関数が不可欠です。