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で質問しましょう!
似たような質問が見つかりました
- Visual Basic(VBA) VBAでのMATCH関数 3 2022/10/17 19:06
- Visual Basic(VBA) Vbaで数式をポーランド記法に変換するコードを作って実行しようとするとフリーズします。 1 2022/05/24 17:53
- Visual Basic(VBA) ExcelVBAで、index、match関数を使用して、指定範囲に出力したい 3 2022/10/18 21:53
- Visual Basic(VBA) 改行ごとに行を追加し、数量を分割 4 2023/07/11 16:39
- Visual Basic(VBA) マクロVBA 1シートをまとめる 閉じ方 初心者 SOS! 1 2022/06/17 14:54
- Visual Basic(VBA) VBAで質問ですが、皆さんはどの様に導き出しているのでしょうか? 6 2022/05/03 21:53
- Excel(エクセル) 日付で矢印マクロ 4 2023/07/25 16:47
- Visual Basic(VBA) Sheet1のA列にコードB列にメアド、Sheet2のB列にコード一覧とD列にメアド一覧があり、Sh 3 2022/10/19 11:57
- Visual Basic(VBA) 複数のcsvファイルをExcelに一括変換したい 2 2023/03/03 12:44
- Visual Basic(VBA) いつもお世話になっております、VBAで教えて頂きたいのですが 2 2022/05/05 22:20
このQ&Aを見た人はこんなQ&Aも見ています
-
見学に行くとしたら【天国】と【地獄】どっち?
みなさんは、一度だけ見学に行けるとしたら【天国】と【地獄】どちらに行きたいですか? 理由も聞きたいです。
-
人生最悪の忘れ物
今までの人生での「最悪の忘れ物」を教えてください。 私の「最悪の忘れ物」は「財布」です。
-
治せない「クセ」を教えてください
なくて七癖という言葉どおり、人によっていろいろなクセがありますよね。 あなたには治せないクセがありますか?
-
何歳が一番楽しかった?
自分の人生を振り返ったとき、何歳のころが一番楽しかったですか? 子供の頃でしょうか、それとも大人になってからでしょうか。
-
我がまちの「給食」自慢を聞かせてっ!
富山県の給食には「ベニズワイガニ」が出る、、、なんて話を聞いたことがあります。 日本全国「え、給食にそれ出るの!?」な驚きメニューがまだまだあるはず!
-
複数の条件に合う行番号を取得するには
その他(Microsoft Office)
-
Application.Matchで特定行の検索
Visual Basic(VBA)
-
vbaでworksheetfunctionでの複数関数がエラーになります
Visual Basic(VBA)
-
-
4
VBA シートのボタン名を変更したい
Visual Basic(VBA)
-
5
ユーザーフォームをホイールでスクロールする方法(Excel2000VBA)
Excel(エクセル)
-
6
VBAでEmpty値って何ですか?
Excel(エクセル)
-
7
VBA エラー回避を教えてください。ループ内のMatchで値がないと止まってしまいます。
Excel(エクセル)
-
8
Excel VBA 複数条件にマッチしたものを指定したセルに配置したい
Excel(エクセル)
-
9
Excel VBA のdebug(F8キー) がうまく動作しません(超初心者です)
Excel(エクセル)
-
10
VBAにて読み込みが出来ない環境依存文字 ㉖ の文字コードを教えて下さい。
Excel(エクセル)
-
11
VBAでのMATCH関数
Visual Basic(VBA)
関連するカテゴリからQ&Aを探す
おすすめ情報
- ・「みんな教えて! 選手権!!」開催のお知らせ
- ・漫画をレンタルでお得に読める!
- ・【選手権お題その2】この漫画の2コマ目を考えてください
- ・2024年に成し遂げたこと
- ・3分あったら何をしますか?
- ・何歳が一番楽しかった?
- ・治せない「クセ」を教えてください
- ・【大喜利】看板の文字を埋めてください
- ・【大喜利】【投稿~12/17】 ありそうだけど絶対に無いことわざ
- ・【選手権お題その1】これってもしかして自分だけかもしれないな…と思うあるあるを教えてください
- ・【穴埋めお題】恐竜の新説
- ・我がまちの「給食」自慢を聞かせてっ!
- ・冬の健康法を教えて!
- ・一番好きな「クリスマスソング」は?
- ・集合写真、どこに映る?
- ・自分の通っていた小学校のあるある
- ・フォントについて教えてください!
- ・これが怖いの自分だけ?というものありますか?
- ・スマホに会話を聞かれているな!?と思ったことありますか?
- ・それもChatGPT!?と驚いた使用方法を教えてください
- ・見学に行くとしたら【天国】と【地獄】どっち?
- ・これまでで一番「情けなかったとき」はいつですか?
- ・この人頭いいなと思ったエピソード
- ・あなたの「必」の書き順を教えてください
- ・10代と話して驚いたこと
- ・14歳の自分に衝撃の事実を告げてください
- ・人生最悪の忘れ物
- ・あなたの習慣について教えてください!!
- ・都道府県穴埋めゲーム
このQ&Aを見た人がよく見るQ&A
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
配列数式の解除
-
特定のセル範囲で4文字以上入力...
-
for each の現在の配列ポインタ...
-
2つ以上の変数を比較して最大数...
-
2次元動的配列の第一引数のみを...
-
ListViewで、非表示列って作れ...
-
VB6 配列を初期化したい
-
Excel VBA配列をFunctionに渡す
-
Excel-VBAの配列「Public Const...
-
VBのFunctionで、配列を引数...
-
行列の積の計算プログラムを教...
-
Dim は何の略ですか?
-
《エクセル2000》A列・B列の商...
-
配列変数の添字が範囲外ですと...
-
MATLABにて場合分け関数を定義...
-
subの配列引数をoptionalで使う...
-
verilogで配列の任意の8bitを取...
-
エクセルで最小値から0を除く方法
-
配列を任意の数値で埋める方法
-
FORTRANのCOMMON文
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
配列数式の解除
-
2つ以上の変数を比較して最大数...
-
特定のセル範囲で4文字以上入力...
-
配列変数の添字が範囲外ですと...
-
ListViewで、非表示列って作れ...
-
VBA 1次元配列を2次元に追加する
-
VB6 配列を初期化したい
-
subの配列引数をoptionalで使う...
-
配列を任意の数値で埋める方法
-
for each の現在の配列ポインタ...
-
エクセルで最小値から0を除く方法
-
2次元動的配列の第一引数のみを...
-
VBのFunctionで、配列を引数...
-
Excel-VBAの配列「Public Const...
-
《エクセル2000》A列・B列の商...
-
linest関数に配列を渡す
-
VBAで近似曲線の係数取得
-
配列内の内容を全て表示する方法
-
Excel VBA配列をFunctionに渡す
-
AES暗号にて、AES_set_encrypt_...
おすすめ情報
ワークシート関数はすべてVBAから呼び出しています。
VlookupもMatchと同じく検索値"34465"ではエラー、"34464"では検索できました。
配列の中身を検索した場合の話です。
配列をワークシートに書き込んでからMatchを使うと100,000まで、Vlookpuでは1,000,000以上に対応する様でした。
もはや配列での上限はワークシートでのそれよりも仕様上厳しい、どうしても配列でMatchを使いたければ、Matchをループさせて検索を連続させるしかない、と言ったところでしょうか。