『EvaluateとVLOOKUP』（純丸）
下記のようなシートで、
Range("J1").Value = Evaluate("VLOOKUP(I1,$A$1:$B$17,2,FALSE)") は、Jセルに「三井住友」と入ります。
J1:J3セルに同時に入力するため上記を応用して、
Range("J1:J3").Value = Evaluate("VLOOKUP(I1:I3,$A$1:$B$17,2,FALSE)")としてみましたが、
セル３つとも「三井住友」となってしまいました。
Evaluate + VLOOKUPでこのような使い方は出来ないのでしょうか？
A B ・・・・ I J 1 金融機関CD 金融機関名 9 三井住友 2 1 みずほ 17 3 5 三菱ＵＦＪ 35 4 9 三井住友 5 10 りそな 6 17 埼玉りそな 7 33 ＰａｙＰａｙ 8 34 セブン 9 35 ソニー 10 36 楽天 11 38 住信ＳＢＩネット 12 39 ａｕじぶん 13 40 イオン 14 41 大和ネクスト 15 42 ローソン 16 43 みんなの 17 44 ＵＩ
尚、現状では
With Range("J1:J3")
.FormulaR1C1 = "=VLOOKUP(RC[-1],R1C1:R17C2,2,FALSE)"
.Value = .Value
End With
のようにしており、マクロ自体は回っています。
＜ 使用 Excel：Excel2021、使用 OS：Windows11 ＞
Evaluate("VLOOKUP(I1:I3,$A$1:$B$17,2,FALSE)")が単一の値を返します。
これは内部ロジックなので、私ども一般ユーザーは結果を受け入れるしかないように思います。
バグかもしれませんが、MS社は仕様というかもしれません（説得力ないですが）。
ちなみに、こういう場合は既に提示された方法のほか、
WorksheetFunction(またはApplication.)を使う方法もありますね。
Range("J1:J3").Value = Application.VLookup([I1:I3], [$A$1:$B$17], 2, False)
（xyz） 2026/01/16(金) 10:22:52
【Copilot検索結果】
R1C1形式の利点
相対参照の明確化: R1C1形式では、相対位置を数値で表すため、
数式の仕組みがより論理的に理解しやすくなります。 VBAやマクロでの利用: R1C1形式は、VBA （Visual Basic for Applications）での自動化処理において特に便利です。 行・列を数字で指定することで、プログラムの可読性が向上し、バグを防ぎやすくなります。
【検証結果】
R1C1形式の場合、相対参照として計算されているようです(たぶん)
＞.FormulaR1C1 = "=VLOOKUP(RC[-1],R1C1:R17C2,2,FALSE)"
「Rの部分は変更なし」、「Cは-1列(つまり1つ左側の列)」という指定なので…
【結論】
A1形式の場合、検索値の行の部分が絶対参照になってしまうので、
行の部分を変更してあげればよいと思われます。
Sub Sample()
Const LastRow As Long = 3
Dim rng As Range
Set rng = Range("J1")
For i = 1 To LastRow
rng.Value = Evaluate("VLOOKUP(I" & i & ":I3,A1:B17,2,FALSE)")
Set rng = rng.Offset(1, 0)
DoEvents
Next i
Set rng = Nothing
End Sub
（匿名） 2026/01/16(金) 10:31:27
xyz様の手段の方がよさそうです。
（匿名） 2026/01/16(金) 10:38:25
純丸さん Evaluate メソッドは配列に対応できますが、使用関数には制限があります。 この場合、純丸さんの数式を直接セルに埋め込んで計算させる手法が最良でしょう。
もし計算結果を直接配列に格納する必要があるのであればApplicationオブジェクトを使用します。
Dim x
With Application
x = .IfError(.VLookup([i1:i3], [a:b], 2, False), "")
[j1:j3] = x
End With
（jindon） 2026/01/16(金) 11:13:07
私が過去に立てたスレです。 純丸さんも投稿されていました。 参考になれば （jindon） 2026/01/16(金) 11:31:22
急遽外に出てしまいまして、返信が遅くなってすみません。 追って、再度返信させていただきます。 （純丸） 2026/01/16(金) 19:24:08
xyzさん ありがとうございます。まぁ仕様なのかなとは思ってました。 もともとEvaluateを使いたかったのは 〇 直接数式をいれない 〇 セルをループしない という希望なので、ご提示いただいたWorksheetFunctionを使うのがよさそうです。 ※ ついでに言うと実はデータが30万行くらいあります(汗)
匿名さん ありがとうございます。今回は「セルをループしない」のが１つの希望でした。
jindonさん ご提示の過去スレは何回も参考にさせていただきました。 「数式を入れない・ループしない」方法として配列に格納する方法のご提示、ありがとうございます。 確かに「セルをループする位なら配列にする」のは、過去jindonさんが何回もご提示されていたと思います。 思い出せなかったのは不覚でした。
皆さん、ご回答ありがとうございました。 （純丸） 2026/01/17(土) 16:33:55
R1C1形式ではなくA1形式で記述したいという趣旨なら下記のような書き方もありますね。
With Range("J1:J3")
.Formula = "=VLOOKUP(I1,A$1:B$17,2,FALSE)"
.Value = .Value
End With
J1:J3を選択した状態でJ1に =VLOOKUP(I1,A$1:B$17,2,FALSE) と入力して Ctrl+Enter と同じ動作になります。
と投稿したら質問主さんと衝突しました。
「数式を入れない」という趣旨なので希望とは異なりますが せっかくなので投稿しておきます。
（hatena） 2026/01/17(土) 16:48:31
丁寧な回答ありがとうございました。当掲示板を昔から利用されていた大ベテランさんでしたか。
繰返しになってしまいますが、若干の説明を追加します。 なぜVLOOKUPの時に誤った結果を返すのかは一般ユーザーには不明なんですね。 例えば、SUMIFの第二引数を配列にしたときには、正しく配列を返すわけです。 二つは同じようなことだと思うんですよ。引数を配列に自然な拡張をしたという意味で。 しかし、結果は大きく異なる。なぜか、ということですね。
Evaluateというのは、特定の環境を作成して、そこでワークシート関数などを実行する仕組みかと 思いますが、なぜ「VLOOKUPの時に誤った結果を返すのか」は、内部でどのような環境が作られ、 どんな計算が行われているかに関し、ソースコードを閲覧できる知識ある内部の人にしか分からないと思います。
どうしてもということであれば、マイクロソフト社にお尋ねになってはいかがでしょう。
（xyz） 2026/01/17(土) 16:52:29
ちなみに、 ループが一番遅いのは当然として、 Applicationsを使う方法より、 Formula(またはFormulaR1C1 R1C1)に式を設定してValueで値化する方が 私の環境では高速でした。
（hatena） 2026/01/17(土) 18:26:57
xyzさん
Evaluate("SUMIF(・・ は私の環境でも正しい結果が返されていたので、VLOOKUPがダメなのが疑問だった次第です。
まぁこのような「これって仕様？」なんてことはエクセルにはよくある事と思ってあきらめてます。
（純丸） 2026/01/17(土) 20:00:37
hatenaさん R1C1形式とかA1形式とかにこだわっているわけではなく、あくまで「数式を入れない」のが希望でした。
しかし、 ＞ Applicationsを使う方法より、 ＞ Formula(またはFormulaR1C1 R1C1)に式を設定してValueで値化する方が ＞ 私の環境では高速でした。
↑これの方が気になります。直接データが入るようにした方が速いと思い込んでました。 ちょっと検証が必要ですね。 ありがとうございました。 （純丸） 2026/01/17(土) 20:09:43
私の環境で RANDARRAY関数等を使ってランダムな30万行のデータを作成して、 100行の銀行名を抜き出すコードで検証した結果を置いておきます。
Sub TestFormula()
Dim st As Single
st = Timer
With Range("J1:J100")
.Formula = "=VLOOKUP(I1,A$1:B$329816,2,FALSE)"
.Value = .Value
End With
Debug.Print Format(Timer - st, "0.000s")
End Sub
Sub TestApplication()
Dim st As Single
st = Timer
Range("J1:J100").Value = Application.VLookup([I1:I100], [$A$1:$B$329816], 2, False)
Debug.Print Format(Timer - st, "0.000s")
End Sub
交互に5回検証しました
TestFormula 0.063s 0.070s 0.070s 0.078s 0.070s
TestApplication 0.406s 0.406s 0.406s 0.398s 0.406s
（hatena） 2026/01/17(土) 20:34:00
Excel2021や365ならXLOOKUPでスピルさせるのが最速かも？ ということで上記と同じデータで検証してみました。
Sub TestFormulaXLOOKUP()
Dim st As Single
st = Timer
With Range("J1:J200")
.Cells(1).Formula2 = "=XLOOKUP(I1:I200,A2:A329816,B2:B329816,"""",0)"
.Value = .Value
End With
Debug.Print Format(Timer - st, "0.000s")
End Sub
検証結果 0.211s 0.211s 0.203s 0.211s 0.211s
※ミスがあったので更新しました
（hatena） 2026/01/17(土) 21:16:43
hatenaさんの処理速度に興味をそそられたので、Excel2021で自分もやってみました。 予想では、自分もExcel2021や365なら後発のXLOOKUPでスピルさせるのが最速と思っていましたが 結果はVLOOKUPでスピルさせるのが最速でした。 最も実務上は影響のない差ですので自分なら後発のXLOOKUPを使いますが... Application.VLookupやWorksheetFunction.VLookupは圧倒的に遅いですが、可読性の面では良いと思います。
＜測定結果＞ 1回目--------------------------------------------- Application.VLookup 3.171875 VLOOKUP Formula 0.33203125 VLOOKUPスピル Formula2 0.26953125 XLOOKUPスピル Formula2 0.3359375 2回目--------------------------------------------- Application.VLookup 3.1640625 VLOOKUP Formula 0.34765625 VLOOKUPスピル Formula2 0.265625 XLOOKUPスピル Formula2 0.3203125 3回目--------------------------------------------- Application.VLookup 3.15625 VLOOKUP Formula 0.3359375 VLOOKUPスピル Formula2 0.26953125 XLOOKUPスピル Formula2 0.3359375 4回目--------------------------------------------- Application.VLookup 3.15625 VLOOKUP Formula 0.328125 VLOOKUPスピル Formula2 0.265625 XLOOKUPスピル Formula2 0.33203125 5回目--------------------------------------------- Application.VLookup 3.15625 VLOOKUP Formula 0.34765625 VLOOKUPスピル Formula2 0.2578125 XLOOKUPスピル Formula2 0.33984375
＜テストコード＞
Private Sub CommandButton1_Click()
Cells.Clear
Dim n&: n = 1000
[A1].Formula2 = "=SEQUENCE(300000)"
[B1].Formula2 = "=""Item-""&SEQUENCE(300000)"
[D1].Formula2 = "=RANDARRAY(" & n & ",,1,300000,TRUE)"
[A1:B300000].Value = [A1:B300000].Value
[D1].Resize(n).Value = [D1].Resize(n).Value
Dim t#, i&
For i = 1 To 5
[E:H].Clear
Debug.Print i & "回目---------------------------------------------"
'Application.VLookup
t = Timer
[E1].Resize(n).Formula = Application.VLookup([D1].Resize(n), [A:B], 2, False)
[E1].Resize(n).Value = [E1].Resize(n).Value
Debug.Print "Application.VLookup", Timer - t
'VLOOKUP
t = Timer
[F1].Resize(n).Formula = "=VLOOKUP(D1,A:B,2,FALSE)"
[F1].Resize(n).Value = [F1].Resize(n).Value
Debug.Print "VLOOKUP Formula", Timer - t
'VLOOKUPスピル
t = Timer
[G1].Formula2 = "=VLOOKUP(D1:D" & n & ",A:B,2,FALSE)"
[G1].Resize(n).Value = [G1].Resize(n).Value
Debug.Print "VLOOKUPスピル Formula2", Timer - t
'XLOOKUPスピル
t = Timer
[H1].Formula2 = "=XLOOKUP(D1:D" & n & ",A:A,B:B)"
[H1].Resize(n).Value = [H1].Resize(n).Value
Debug.Print "XLOOKUPスピル Formula2", Timer - t
Next
End Sub
（まる2021） 2026/01/19(月) 12:00:04
hatenaさん、まる2021さん、検証ありかとうございます。 スピルは記事などで知ってる程度でFormula2プロパティも知りませんでした。 古い知識で何とかこなしてる状況で、今回は勉強になりました。
検証の結果はいずれにしても人間が体感する差ではありませんが、 それでも少しでも速いコードを求めてしまうのがプログラミングの性(さが)でしょうか。
いろいろとありがとうございました。 （純丸） 2026/01/19(月) 12:40:13
すみません。今、見返したら変な事してました。
'Application.VLookup
t = Timer
[E1].Resize(n).Formula = Application.VLookup([D1].Resize(n), [A:B], 2, False)
[E1].Resize(n).Value = [E1].Resize(n).Value
Debug.Print "Application.VLookup", Timer - t
以下に訂正
'Application.VLookup
t = Timer
[E1].Resize(n).Value = Application.VLookup([D1].Resize(n), [A:B], 2, False)
Debug.Print "Application.VLookup", Timer - t
（まる2021） 2026/01/19(月) 15:42:41
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.