Excel VBA 配列の分割について

質問

Excel VBAでコーディングしていますが
行き詰っているのでお助け下さい。

(1)二次元配列に格納されている値の中から
特定の値が格納されている位置をループを使わず
取得したいのですがその方法が分かりません。
<例>
　Dim x(2,2) As Valiant
　　x(0,0) = "あああ"
　　x(0,1) = "いいい"
　　x(0,2) = "ううう"
　　x(1,1) = "えええ"
　　　　　・
　　　　　・
　　　　　・
この配列から"えええ"が格納されている位置をループを使わず
取得する方法を教えてください。　⇒　1, 1

(2)二次元配列の指定した列（？）を一次元配列に
格納する方法も重ねて教えてください。

以上、よろしくお願いします。

回答No.5

ruru123456789 · ベストアンサー

(1)について
最低限、列のループは必要ですが、下記のコードを試して下さい。

Sub Test()

'配列セット
Dim x(2, 4) As Variant

x(0, 0) = "A": x(0, 1) = "B": x(0, 2) = "C": x(0, 3) = "D": x(0, 4) = "E"
x(1, 0) = "F": x(1, 1) = "G": x(1, 2) = "H": x(1, 3) = "I": x(1, 4) = "J"
x(2, 0) = "K": x(2, 1) = "L": x(2, 2) = "M": x(2, 3) = "N": x(2, 4) = "A"

'列をループして検索
Dim i As Integer
Dim m As Long
Dim c As New Collection

On Error Resume Next
For i = 1 To UBound(x, 2) + 1
Err = 0
m = Application.Match("A", Application.Index(x, 0, i), False)
If Err = 0 Then c.Add i & "," & m
Next
On Error GoTo 0

'結果表示
For i = 1 To c.Count
If MsgBox("列,行=" & c(i), vbInformation + vbOKCancel, i & "/" & c.Count) = vbCancel Then Exit For
Next

End Sub

お礼

お礼が遅くなり申し訳ありません。

上記の方法を利用したところ
思う通りのパフォーマンスを実現出来ました。

アドバイスありがとうございます。

回答No.4

KenKen_SP

(1) について。

> 二次元配列に格納されている値の中から特定の値が格納されている
> 位置をループを使わず取得したい

無理ですね...
現状のコードは？ もともとの配列はどのように生成しているのですか？
配列のソースがセルなら Find が使えますし...データソースによっては
改善策があるかも。

(2) について。

> 配列が100×20,000と大きな配列なので...

20,000項目のデータが100レコードとは考えにくいので、
100項目のデータが20,000レコードと考えてよろしいですか？

ワークシート関数を VBA で利用します。

書式） Application.Index(配列, RowIndex, ColumnIndex)

Sub Sample()

　　Dim Buf(20000, 100) As Variant
　　Dim Array2D　　 As Variant
　　Dim Array1D　　 As Variant

　　' // サンプルデータをセット
　　Buf(20000, 100) = "TEST"
　　' // Index 関数で100列目を切り抜く
　　' // ※ 1オリジンで考え、ColumnIndex には101を指定
　　Array2D = Application.Index(Buf, 0, 101)
　　' // Index 関数により１列のみの２次元配列となる
　　MsgBox Array2D(20001, 1)
　　' // これをさらに Transpose 関数で１次元配列化
　　Array1D = Application.Transpose(Array2D)
　　MsgBox Array1D(20001)

End Sub

ただし、Index 関数の制約で Excel2000 の場合、要素数が5461個
までの配列にしか使えません。Excel2002 以降ではこの制限が大幅
に改善されましたが...

なお、Index 関数で切り抜かれる配列はソース配列が 0 オリジンで
あっても必ず 1 オリジンになります。これは Index 関数がワーク
シート関数であるが故です。

※ RowIndex も ColumnIndex も 1 から始まる。

お礼

お礼が遅くなり申し訳ありません。

教えて頂いた方法で
二次元配列を一次元配列に格納することが出来ました。
ありがとうございます。

回答No.3

Wendy02

こんばんは。

>この配列から"えええ"が格納されている位置をループを使わず取得する方法を教えてください。　⇒　1, 1

純粋に、プログラムで解決するなら、ループしかありません。

>本来であればOracleやせめてAccessで処理をしたいのですが
>仕様上Excelでの処理しか出来ないもので悩んでいます。

元のデータは何かにもよりますね。Excelからでも、ADO は、使えるはずです。

100×20,000　なら、一旦、Excelに書き出して、AdvancedFilter という方法もあります。

お礼

お礼が遅くなり申し訳ありません。

>>Excelからでも、ADO は、使えるはずです。
SOX法対応としてDBへの接続は禁止されておりまして。。。

やはり一度Excelに書き出すべきでしょうか。

回答No.2

ham_kamo

No.1です。

回答してから気づいたのですが、Excel2003まででは、列の最大値は256で、行の最大値は65536なので、それを超える大きさの配列ではNo.1のやり方ではできません。（Excel2007では範囲は拡張されてますが）

ループを使いたくない、ということはけっこう大きな配列なのですかね…。

お礼

ご回答ありがとうございます。

当方の環境はExcel2000なのですが、
ご推測の通り配列が100×20,000と大きな配列なので
パフォーマンスの観点からセルの利用・ループは避けたいと
考えています。

こう言った背景から、(2)の質問のようにMatch関数を
上手く使う方法もあるのかな？とも思った次第です。

本来であればOracleやせめてAccessで処理をしたいのですが
仕様上Excelでの処理しか出来ないもので悩んでいます。

引き続きご教授願えれば幸いです。

回答No.1

ham_kamo

あまりスマートなやり方ではないかもしれなないですが、(1)だけ。

配列をいったんセルに格納して、Findメソッドで検索してみてはいかがでしょうか。

以下はサンプルです。dummyという名前のシートを作って、マクロを実行してみてください。dummyシートが目障りなら非表示にしておけばいいでしょう。

Sub Test()
　Dim TmpRng As Range, R As Range
　Dim x(2, 2) As Variant
　Dim i As Integer, j As Integer
　Dim SearchStr As String

　x(0, 0) = "あああ"
　x(0, 1) = "いいい"
　x(0, 2) = "ううう"
　x(1, 0) = "えええ"
　x(1, 1) = "おおお"
　x(1, 2) = "かかか"
　x(2, 0) = "ききき"
　x(2, 1) = "くくく"
　x(2, 2) = "けけけ"

　SearchStr = InputBox("検索する文字を入力してください")

　Set TmpRng = Worksheets("dummy").Cells(1, 1).Resize(3, 3) '配列を格納するセル
　TmpRng.ClearContents '念のため格納先のセルをクリア
　TmpRng.Value = x 'セルに配列を代入

　Set R = TmpRng.Find(What:=SearchStr, LookAt:=xlWhole) 'セル範囲から値を検索
　If Not R Is Nothing Then
　　i = R.Row - 1
　　j = R.Column - 1
　　MsgBox ("x(" & i & "," & j & ") = " & SearchStr)
　Else
　　MsgBox ("配列xに「" & SearchStr & "」はありません")
　End If
End Sub