効率の良い配列格納方法

質問

excelvbaで配列を勉強しています。
配列を使うと効率よく簡単に書けると聞きましたので、
過去に書いたプログラムを配列を使い直そうと思っています。
そこで質問ですが、

先着２０名様、というプログラムですが、
この２０名を作るのに、配列を作ろうと思います。
（実プログラムは２０人なので、あっという間に終わってしまいますが勉強のためです。）

シートに応募者のデータが、１行を使って入ってきます。
その中からある条件に合致する行（応募者）を取り出してゆくというモノです。
応募者の順は当然にして、到着順です。
応募者データの中身は、応募者氏名、住所、生年月日、、、、と項目が多いです。

やりたいことは、
この応募者データの全ての項目ではなく、在る特定の項目のみを配列に２０人分取り込むということです。
一つずつ取り込めばよいのですが、（今は１個ずつ取り込んでいます。）
取り込む項目も１０個と多いので、
何とか効率の良い取り込み方を教えて欲しいのです。

具体的には、
データはaから始まり、以下の通りです。
a応募者氏名
b応募者現住所
c応募者生年月日
d現住所の在住期間
e出身大学
f一番長く居住した都道府県
g引っ越し回数
h質問への応え１
i質問への応え２
j質問への応え３
k質問への応え４
l質問への応え５
m質問への応え６
nＸＸＸ氏名
oｘｘｘ住所
pＸＸＸ生年月日
q応募者とＸＸＸの続柄
rアピールしたい内容

上記項目の中から、配列に格納する項目は、
a～c,h～m,r
です。

説明が分からない部分は聞いていただけると助かります。
宜しくお願いします。

回答No.4

kagakusuki · ベストアンサー

>配列bbbのように１個に格納したいのです。

　残念ながら、私は「連続していない」複数のセル範囲のデータを、For～Nextも使わずに、1個の配列変数に"直接的に"格納する術を存じ上げません。（方法が無いと断言している訳では御座いません）
　そこで、一時的に新しいワークシートを作成して、そこに「セル範囲が連続する様に」データのコピーを取り、そのコピーしたデータを配列変数に取り込むという方法では如何でしょうか？

Sub Macro ()

Dim シート枚数 As Integer
Dim 最終行 As Long
最終行 = Application.WorksheetFunction.Match(9E+99, Sheets("Sheet1").Columns("C:C"))
Dim 配列A() As Variant
ReDim 配列A(1 To 最終行 - 1, 1 To 10)

Sheets.Add After:=Sheets(Sheets.Count)

Sheets(Sheets.Count).Range("A2:C" & 最終行).Value = Sheets("Sheet1").Range("A2:C" & 最終行).Value
Sheets(Sheets.Count).Range("D2:I" & 最終行).Value = Sheets("Sheet1").Range("H2:M" & 最終行).Value
Sheets(Sheets.Count).Range("J2:J" & 最終行).Value = Sheets("Sheet1").Range("R2:R" & 最終行).Value
配列A() = Sheets(Sheets.Count).Range("A2:J" & 最終行).Value

Application.DisplayAlerts = False
Sheets(Sheets.Count).Delete
Application.DisplayAlerts = True

End Sub

　因みに、上記のマクロは元データがSheet1に存在していて、Sheet1の1行目に「応募者氏名」～「アピールしたい内容」といった項目名が入力されている場合を想定したものです。
　Sheet1の1行目の項目名は、配列変数に格納する必要がないものとしているため、配列変数に格納する事できる行数の限界の設定を

1 To 最終行 - 1

としております。
　もし、Sheet1の項目名が、例えば3行目に入力されている場合には、配列変数に格納する事できる行数の限界の設定は

1 To 最終行 - 3

となります。

お礼

何回も何回もしつこくて済みません。
また、それにお応えいただき、大変感謝しております。

＞私は「連続していない」複数のセル範囲のデータを、For～Nextも使わずに、1個の配列変数に"直接的に"格納する術
やはり無いのですね、了解しました。

これに対応する方法もご教示いただき、有り難うございました。

教えていただいたマクロはよく分かります。
また、先に教えていただいた雛形を作っておき、オートフィルする方法は
昨日一日掛かって理解できました。

今回はつまらない質問事から、素晴らしい考え方、コーディングを得ました。
世の中凄い人が居るな、というのが実感です。
お時間を取らせて申し訳在りませんでした。
深謝です。
有り難うございました。

回答No.3

kagakusuki

>実は、質問の項目毎の格納で、a～c、およびh～mは連続したセルです。
>このため、今はfor nextでループさせていますが、一発で出来ないか、ということでした。

　ですから、

Dim 配列A As Variant
Dim 配列B As Variant
Dim 配列C As Variant
配列A = Range("A2:C21")
配列B = Range("H2:M21")
配列C = Range("R2:R21")

とした場合において、例えば 配列B(3,4)の値はK4セルの値となり、実際、

Range("T1").Value = 配列B(3,4)

としますと、T1セルにはK4セルと同じ値が表示されるのですが、それでは駄目なのでしょうか？

お礼

今帰ってきました。
またまた、返事を頂き有り難うございます。
まだ、朝お礼したとおり、内容を全て読み切っていませんが、
最後にした質問が、言葉足りずで申し訳在りません。

教えていただいている内容は理解できています。
ただ、配列がＡ，Ｂ，Ｃと３個在りますが、
配列bbbのように１個に格納したいのです。

それとも、（私が分かっていないだけで）
配列Ａ，Ｂ，Ｃはまた配列bbbにまとめられるのでしょうか。

とにかく最終的には、配列は１個にまとめたいのです。
→書いていませんが、私の書いたその後のプログラムでは、
１個の配列としてコーディングしていますので。

何回もしつこくて済みません。
ここだけ何とかクリアできればと思います。
宜しくお願いします。

回答No.2

kagakusuki

ただ単にデータを配列変数に格納するだけでしたら、

Dim 配列A As Variant
Dim 配列B As Variant
Dim 配列C As Variant
配列A = Range("A2:C21")
配列B = Range("H2:M21")
配列C = Range("R2:R21")

の様な形式になると思いますが、配列変数に格納しただけでは、セル範囲にデータが存在している場合と大して状況は変わりませんから、どの行のデータを取捨選択するのかを決める段階で、結局、For～Nextを使い、1行ずつ判定を行う作業を繰り返す羽目になるのではないかと思います。

　もし、配列変数を使用する事には拘らず、兎に角、一括して処理を行うだけでも良いというのでしたら、適当な作業列に、判定を行う働きをするワークシート関数を、一時的に一括して入力し、そのワークシート関数の結果を基準とするオートフィルターで、行を取捨選択する様なマクロは如何でしょうか？（For～Nextの繰り返し回数が多過ぎるよりは高速化が可能です）

>その中からある条件に合致する行（応募者）を取り出してゆく

という箇所で述べられている「ある条件」が、どの様なものなのかが不明なため、例えとして、仮に
「生年月日」が1994/3/31以前」で、「質問への応え１」が「はい」となっていて、「質問への応え２」が「いいえ」ではなく、且つ、「質問への応え３」～「質問への応え６」の中に「はい」が2つ以上ある行の中で、先着の20行を取り出すものとします。
　又、応募者の元データが入力されているシートがSheet1であり、その1行目には「応募者氏名」～「アピールしたい内容」といった各項目名が入力されていて、実際の応募者のデータは2行目以下に入力されているものとします。
　又、条件を満たしている応募者のデータを取り出した結果は、Sheet2のA1～R21（1行目は項目名の表示に使用する）に表示するものとします。
　又、Sheet1のIV列を作業列として使用するものとします。

　その場合のマクロの一例は、次の様なものとなります。

Sub 条件による抽出()

Dim lr As Long
lr = Application.WorksheetFunction.Match(9E+99, Sheets("Sheet1").Columns("C:C"), 1)
Sheets("Sheet2").Range("A1:S" & lr + 1).Clear
With Sheets("Sheet1").Range("IV1:IV" & lr)
.Offset(1).FormulaR1C1 = "=AND(COUNTIF(R1C:R[-1]C,TRUE)<20,RC3<""1994/4/1""+0,AND(RC8=""はい"",RC9<>""いいえ"",COUNTIF(RC10:RC13,""はい"")>1))"
.AutoFilter
.AutoFilter Field:=1, Criteria1:="TRUE"
Sheets("Sheet1").Range("A1:R" & lr).Copy
Sheets("Sheet2").Range("A1").PasteSpecial Paste:=xlPasteValues
Sheets("Sheet2").Range("A1").PasteSpecial Paste:=xlPasteFormats
Application.CutCopyMode = False
.AutoFilter
.ClearContents
End With

End Sub

　尚、当然の事ながら、行を取り出すのか否かの基準は、あくまで仮のものですので、5行目の

"=AND(COUNTIF(R1C:R[-1]C,TRUE)<20,RC3<""1994/4/1""+0,AND(RC8=""はい"",RC9<>""いいえ"",COUNTIF(RC10:RC13,""はい"")>1))"

の部分は、実際の条件に合わせて適時修正して下さい。

お礼

早速有り難うございました。
また、事細かに例まで作っていただき、感謝です。
正月から仕事で、先ほど帰ってきました、お礼が遅くなりました。
細かくは見ておらず、またすぐ出掛けなければならず、
あとでゆっくり見させていただきますが、要旨は理解できました。

配列という概念が効率／速さ共に、、、と聞いていたのですが、
オートフィルターという概念もあるのですね。
一つ雛形を作り、それを全データーに当てはめてゆく、ということでしょうか。

今回作っていただいた例題はサンプル集に保管し、今後の宝にします。
その前にじっくり内容の理解が必要ですが。

また、「在る条件」で端折ってしまったことで、
多大なるお手間をお掛けし、まことに申し訳在りませんでした。
「ある条件」とは、応募者データ限りで満足させられず、社内の基準との比較等があります。
これについても条件の設定を考えれば済むことでしょうが。

お礼と感想は以上ですが、
（十分解決は出来たのですが）
これだけよく分かっておられて、さらにご親切な方がいらっしゃったので、
本当に聞きたかったことを言わせていただきます。
実は、質問の項目毎の格納で、
a～c、およびh～mは連続したセルです。
このため、今はfor nextでループさせていますが、一発で出来ないか、
ということでした。
（こんな命令はないと思いますが）
　配列bbb(n,1(3)) = range("ax:cx").value
　配列bbb(n,4(6)) = range("hx:mx").value
の様な。

とにかく、後日じっくり勉強させていただき、お礼を述べさせていただきます。
とりあえず、見たということでお礼させていただきました。
大変有り難うございました。

回答No.1

corokorocoro

これを変数に登録と言うことなら配列もだけど構造体(VBAではユーザー定義型と言うみたいだけど)も理解する必要がある。