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も見ています
-
これ何て呼びますか Part2
あなたのお住いの地域で、これ、何て呼びますか?
-
フォロワー20万人のアカウントであなたのあるあるを披露してみませんか?
あなたが普段思っている「これまだ誰も言ってなかったけど共感されるだろうな」というあるあるを教えてください
-
映画のエンドロール観る派?観ない派?
映画が終わった後、すぐに席を立って帰る方もちらほら見かけます。皆さんはエンドロールの最後まで観ていきますか?
-
海外旅行から帰ってきたら、まず何を食べる?
帰国して1番食べたくなるもの、食べたくなるだろうなと思うもの、皆さんはありますか?
-
天使と悪魔選手権
悪魔がこんなささやきをしていたら、天使のあなたはなんと言って止めますか?
-
複数の条件に合う行番号を取得するには
その他(Microsoft Office)
-
Application.Matchで特定行の検索
Visual Basic(VBA)
-
vbaでworksheetfunctionでの複数関数がエラーになります
Visual Basic(VBA)
-
-
4
VBA シートのボタン名を変更したい
Visual Basic(VBA)
-
5
ユーザーフォームをホイールでスクロールする方法(Excel2000VBA)
Excel(エクセル)
-
6
VBAでのMATCH関数
Visual Basic(VBA)
-
7
vba 時間の引き算 例えば 15:00から16:15の間の時間は1時間15分ですが、vbaのコード
Visual Basic(VBA)
関連するカテゴリからQ&Aを探す
おすすめ情報
- ・漫画をレンタルでお得に読める!
- ・【お題】絵本のタイトル
- ・【大喜利】世界最古のコンビニについて知ってる事を教えてください【投稿~10/10(木)】
- ・メモのコツを教えてください!
- ・CDの保有枚数を教えてください
- ・ホテルを選ぶとき、これだけは譲れない条件TOP3は?
- ・家・車以外で、人生で一番奮発した買い物
- ・人生最悪の忘れ物
- ・【コナン30周年】嘘でしょ!?と思った○○周年を教えて【ハルヒ20周年】
- ・ハマっている「お菓子」を教えて!
- ・最近、いつ泣きましたか?
- ・夏が終わったと感じる瞬間って、どんな時?
- ・10秒目をつむったら…
- ・人生のプチ美学を教えてください!!
- ・あなたの習慣について教えてください!!
- ・牛、豚、鶏、どれか一つ食べられなくなるとしたら?
- ・都道府県穴埋めゲーム
このQ&Aを見た人がよく見るQ&A
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
配列数式の解除
-
2つ以上の変数を比較して最大数...
-
特定のセル範囲で4文字以上入力...
-
VBA 1次元配列を2次元に追加する
-
プログラミングの問題で、二分...
-
subの配列引数をoptionalで使う...
-
VB6 配列を初期化したい
-
ListViewで、非表示列って作れ...
-
シェルスクリプト中で、ヒアド...
-
Dim は何の略ですか?
-
行列の積の計算プログラムを教...
-
配列変数の添字が範囲外ですと...
-
for each の現在の配列ポインタ...
-
VBAで2次元配列を使ってセル検...
-
Excel・Word リサーチ機能を無...
-
エクセルで特定の列が0表示の場...
-
特定のPCだけ動作しないVBAマク...
-
教えて下さい
-
UserForm1.Showでエラーになり...
-
メッセージボックスのOKボタ...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
配列数式の解除
-
2つ以上の変数を比較して最大数...
-
特定のセル範囲で4文字以上入力...
-
VB6 配列を初期化したい
-
subの配列引数をoptionalで使う...
-
VBA 1次元配列を2次元に追加する
-
ListViewで、非表示列って作れ...
-
for each の現在の配列ポインタ...
-
配列変数の添字が範囲外ですと...
-
エクセルで最小値から0を除く方法
-
2次元動的配列の第一引数のみを...
-
VBのFunctionで、配列を引数...
-
配列を任意の数値で埋める方法
-
《エクセル2000》A列・B列の商...
-
Excel VBA配列をFunctionに渡す
-
VBA Match関数の限界
-
AES暗号にて、AES_set_encrypt_...
-
VBAで近似曲線の係数取得
-
Array配列の末尾に追加したい。
-
配列内の内容を全て表示する方法
おすすめ情報
ワークシート関数はすべてVBAから呼び出しています。
VlookupもMatchと同じく検索値"34465"ではエラー、"34464"では検索できました。
配列の中身を検索した場合の話です。
配列をワークシートに書き込んでからMatchを使うと100,000まで、Vlookpuでは1,000,000以上に対応する様でした。
もはや配列での上限はワークシートでのそれよりも仕様上厳しい、どうしても配列でMatchを使いたければ、Matchをループさせて検索を連続させるしかない、と言ったところでしょうか。