
https://excel-ubara.com/excelvba4/EXCEL226.html
によるとMatch関数の扱えるシート範囲は限られるようです。
自分ではVBAで要素が1からの連番である1次元配列に対して試してみましたが、
検索範囲を配列全体に設定し、検索値"34465"ではエラー、"34464"では検索できました。
この限界は上記リンク先と異なる結果ですが、参照情報が間違っているのでしょうか。
こちらではVBA上で配列を作成し、リンク先ではセルから取得している点が異なるものの、
Excel 2010までの制約(65535要素)よりも小さい34464になっているのはなにかMatch自体の仕様以外の要素がネックとなっているのでしょうか。
尚、当方はExcel2016です。
以下は試したときのコードです。
Public Sub test()
Dim a(1 To 100000) As Long, i As Long, b As Long
For i = 1 To 100000
a(i) = i
Next i
b = WorksheetFunction.Match(34465, a, 0)
End Sub
ご教授くださると幸いです。
No.1ベストアンサー
- 回答日時:
こんにちは。
> もはや配列での上限はワークシートでのそれよりも仕様上厳しい、どうしても配列でMatchを使いたければ、Matchをループさせて検索を連続させるしかない、
自己完結されてしまうと、返事のつけようがないのですが、それは、この際、放っておきます。なぜなら、解決方法は、ワークシート関数だけとは限らないからです。(例:Dictinaryオブジェクト)
https://excel-ubara.com/excelvba4/EXCEL226.html
》 Excel2013以降では、この制限はなくなっていることが確認できました。
こちらは、Excel 2016(32bit) ですが、エラーは出ますね。(ここのサイトはあまり信用しないほうが良いでしょう)
'//
Public Sub test_A()
Dim a(100000) As Long, i As Long, b As Long, j As Long
For i = 0 To 100000
a(i) = i
Next i
For j = 34400 To 65536
On Error Resume Next
b = WorksheetFunction.Match(j, a(), 0)
DoEvents
If Err.Number <> 0 Then
MsgBox b
Exit Sub
End If
Next
On Error GoTo 0
End Sub
MsgBox ->34465 エラー値
'// 予め、A列に数値が入っています。
Public Sub test_B()
Dim a(1 To 100000) As Long, i As Long, b As Long, j As Long
For i = 1 To 100000
a(i) = Cells(i, 1).Value
Next i
For j = 34400 To 65536
On Error Resume Next
b = WorksheetFunction.Match(j, a(), 0)
DoEvents
If Err.Number <> 0 Then
MsgBox b
Exit Sub
End If
Next
On Error GoTo 0
End Sub
MsgBox ->34464 エラー値
ご返答ありがとうございます。
ご教授くださった方法と同様のことを試していたのですが、Match使わずに配列全体を1行ずつ調べていった方が断然速かったです。なぜだ
UTF8のテキスト(xml)を変数に読み込んだあと、行ごとにSplit、ループでMatchの上限行数ごとに配列化、それをさらに配列にまとめたもの(2段階配列)をつくり、Matchの検索範囲に
親配列(i) ←親配列(i)(1)~親配列(i)(34464)
を指定、ワイルドカード含む検索、見つからなければ
i = i + 1
https://www.banana-juice.com/VBA/Performance/Ran …
によれば配列内を検索する際に、For nextはワークシート関数よりもFindよりもFor eachよりも遅いことになっていましたが、
いま手元で試すと、一次元配列上を検索対象とすると逆転してループの方がMatchより速いです。
(ワークシート上を対象とすると断然For nextよりMatchが速いです)
リンク先とはExcelのバージョンなどが異なるのかもしれませんが、いずれにしろ、初めに確認すればよかった。
実はワイルドカード検索をしてその行数を取得する関係で、DictionaryもCollectionもFilter関数もFindメソッドも使えませんでした。
尚、行ごとに配列定数にしてワークシート上のMatchに渡すとメモリ不足、Matchの34464制限撤廃のためにxmlをワークシートに読み込むと読み込みが遅すぎる(UTF8だからか)‥‥。同じ理由でフィルターやスライサーも断念。
(レコードセットならワークシートを経由せずにフィルターをかけられるか‥‥)
No.2
- 回答日時:
こちらの当面の目的は、「〇〇○○の神髄」とやらの内容の間違いを確認することでしたから、その目的は果たしたと思っていましたが、検索については、ADOやら、サーチ・アルゴリズムなどがありますが、ワイルドカードやそれに準じたものを使うということなら、固定したリストをソートするか、使う前に、ソートを必要とするかをしなくてはならないのだと思います。
回答者の中で、二分木法をつかう人もいましたが、それが該当するか分かりません。それはやってみないとなんとも言えませんが。
ご返答ありがとうございます。
おっしゃる通り当初の質問の範囲から逸脱してしまったようでした。
No.1の時点で十分なご回答を頂いておりました。重ねてお礼申し上げます。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
このQ&Aを見た人はこんなQ&Aも見ています
-
プロが教えるわが家の防犯対策術!
ホームセキュリティのプロが、家庭の防犯対策を真剣に考える 2組のご夫婦へ実際の防犯対策術をご紹介!どうすれば家と家族を守れるのかを教えます!
-
複数の条件に合う行番号を取得するには
その他(Microsoft Office)
-
matchプロパティを取得できません…と出ます。
PowerPoint(パワーポイント)
-
ApplicationとWorksheetFunctionの違い
Visual Basic(VBA)
-
-
4
Application.Matchで特定行の検索
Visual Basic(VBA)
-
5
VBAでのMATCH関数の使用
Visual Basic(VBA)
-
6
vbaでworksheetfunctionでの複数関数がエラーになります
Visual Basic(VBA)
-
7
VBA 変数名に変数を使用したい。
Visual Basic(VBA)
-
8
ユーザーフォームを表示中にシートの操作をさせるには
Excel(エクセル)
-
9
Excelのセルの色指定をVBAから配列を用いて効率的に行う方法はあり
Visual Basic(VBA)
-
10
Application.Matchについて
Excel(エクセル)
-
11
或るプロシージャの呼び出し元判定
その他(Microsoft Office)
-
12
excel match で日付が見つからない
PowerPoint(パワーポイント)
-
13
特定のPCだけ動作しないVBAマクロがあります。その理由は?
Visual Basic(VBA)
-
14
VBAで保存しないで閉じると空のBookが残る
Excel(エクセル)
-
15
エクセルVBAのメニューバー&リボンを再表示させる方法について
Excel(エクセル)
関連するカテゴリからQ&Aを探す
おすすめ情報
このQ&Aを見た人がよく見るQ&A
人気Q&Aランキング
-
4
特定のセル範囲で4文字以上入力...
-
5
2つ以上の変数を比較して最大数...
-
6
subの配列引数をoptionalで使う...
-
7
2次元配列について、教えてくだ...
-
8
配列を任意の数値で埋める方法
-
9
Dim は何の略ですか?
-
10
配列変数の添字が範囲外ですと...
-
11
COBOL 2次元配列について
-
12
2次元動的配列の第一引数のみを...
-
13
VB6 配列を初期化したい
-
14
VBA 1次元配列を2次元に追加する
-
15
サイコロを二つ振って出た数a,b...
-
16
《エクセル2000》A列・B列の商...
-
17
ArrayListの初期値に二次元配列...
-
18
linest関数に配列を渡す
-
19
ListViewで、非表示列って作れ...
-
20
delphiで配列を、コピーするには。
おすすめ情報
公式facebook
公式twitter
ワークシート関数はすべてVBAから呼び出しています。
VlookupもMatchと同じく検索値"34465"ではエラー、"34464"では検索できました。
配列の中身を検索した場合の話です。
配列をワークシートに書き込んでからMatchを使うと100,000まで、Vlookpuでは1,000,000以上に対応する様でした。
もはや配列での上限はワークシートでのそれよりも仕様上厳しい、どうしても配列でMatchを使いたければ、Matchをループさせて検索を連続させるしかない、と言ったところでしょうか。