Excel VBA 配列の分割について

Question

Excel VBAでコーディングしていますが
行き詰っているのでお助け下さい。

(1)二次元配列に格納されている値の中から
特定の値が格納されている位置をループを使わず
取得したいのですがその方法が分かりません。
<例>
　Dim x(2,2) As Valiant
　　x(0,0) = "あああ"
　　x(0,1) = "いいい"
　　x(0,2) = "ううう"
　　x(1,1) = "えええ"
　　　　　・
　　　　　・ 
　　　　　・
この配列から"えええ"が格納されている位置をループを使わず
取得する方法を教えてください。　⇒　1, 1


(2)二次元配列の指定した列（？）を一次元配列に
格納する方法も重ねて教えてください。

以上、よろしくお願いします。

ruru123456789 · Accepted Answer

(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

ham_kamo · Answer

あまりスマートなやり方ではないかもしれなないですが、(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

ham_kamo · Answer

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

Wendy02 · Answer

こんばんは。

>この配列から"えええ"が格納されている位置をループを使わず取得する方法を教えてください。　⇒　1, 1

純粋に、プログラムで解決するなら、ループしかありません。

>本来であればOracleやせめてAccessで処理をしたいのですが
>仕様上Excelでの処理しか出来ないもので悩んでいます。

元のデータは何かにもよりますね。Excelからでも、ADO は、使えるはずです。

100×20,000　なら、一旦、Excelに書き出して、AdvancedFilter という方法もあります。

KenKen_SP · Answer

(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 から始まる。

Excel VBA 配列の分割について

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

No.1です。

こんばんは。

(1) について。

(1)について

似たような質問が見つかりました

このQ&Aを見た人はこんなQ&Aも見ています

関連するカテゴリからQ&Aを探す

このQ&Aを見た人がよく見るQ&A

デイリーランキングこのカテゴリの人気デイリーQ&Aランキング

マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング