| |
▼T.K さん：
T.K さん、全文引用は紙面がもったいないから、やめましょう。
>▼kanabun さん：
>ありがとうございます。ここまでの説明でちょっとずつ仕組みが分かりました。
>細かい部分がまだ分からないですが、続きの解説楽しみにしています。
Dictionaryの概略が分かっていただけたら、
ではコード【72606】の説明のつづきです。
[Sheet1]
2 chr1 1048371 1049797 ER_1_Carrol
3 chr1 1054645 1055778 ER_2_Carrol
4 chr1 1285749 1286349 ER_3_Carrol
5 chr1 243097903 243098610 ER_396_Carrol
6 chr1 243121865 243122466 ER_397_Carrol
7 chr1 243604797 243605398 ER_398_Carrol
8 chr2 250722 251323 ER_399_Carrol
9 chr2 1650212 1650812 ER_400_Carrol
10 chr2 1660654 1661254 ER_401_Carrol
11 chr3 58431890 58432547 ER_756_Carrol
12 chr3 61588283 61588993 ER_757_Carrol
13 chr3 61595330 61595989 ER_758_Carrol
14 chr3 61605662 61606263 ER_759_Carrol
15 chr3 61649269 61649870 ER_760_Carrol
16 chr3 61768126 61769266 ER_761_Carrol
17 chr3 62137944 62138544 ER_762_Carrol
まず
> v = Intersect(r, r.Offset(1)).Value
を実行することにより、
[Sheet1]の2行目から17行目までが配列として変数vに代入されます。
つぎに
'------------------------------------------------------- (code)
> For i = 1 To UBound(v)
> If Not dic.Exists(v(i, 1)) Then
> Set dic(v(i, 1)) = _
> CreateObject("Scripting.Dictionary")
> End If
> dic(v(i, 1))(v(i, 4)) = Array(v(i, 2), v(i, 3))
> Next
'-----------------------------------------------------------
でやっていることは、昨日ちょっと触れたように、
日本語にすると、
'----------------------------------------------------- (日本語)
If 「dic にA列のclass名が Key登録されていなかったら」
→ dic のなかに子供の辞書を作成する
End If
dic(A列のclass名)のなかの 子供辞書の(D列ID)のKeyラベル
をもつ容器に 配列(B列の数値, C列の数値) を容れる、
'-----------------------------------------------------------
というようなことの繰り返しです。
一番最初のデータ i = 1（表の2行目のデータ）
> 2 chr1 1048371 1049797 ER_1_Carrol
に即していえば、
上のコードは、
'----------------------------------------------------- (code)
For i = 1 のとき
> If Not dic.Exists("chr1") Then
> Set dic("chr1") = CreateObject("Scripting.Dictionary")
> End If
> dic("chr1")("ER_1_Carrol") = Array(1048371, 1049797)
'-----------------------------------------------------------
をやっているということです。
For i = 1 を実行した後
↓親dic
┏━━━━━━━━━━━━━━━━━━━━━━━
┃ ↓子のDictionary
[chr1]┃ ┌─────────────
┃[ER_1_Carrol]│{1048371, 1049797}
┃ └─────────────
┃
┗━━━━━━━━━━━━━━━━━━━━━━━
という構造になります。（今度は図を横にしました）
つぎに、For i = 2 を実行した後は
↓親dic
┏━━━━━━━━━━━━━━━━━━━━━━━
┃ ┌─────────────
┃[ER_1_Carrol]│{1048371, 1049797}
┃ └─────────────
[chr1]┃ ┌─────────────
┃[ER_2_Carrol]│{1054645, 1055778}
┃ └─────────────
┃
┗━━━━━━━━━━━━━━━━━━━━━━━
となります。
以下同様にして、17行目(i = 16) まで繰り返したあとは dicは
こういう構造になります。（↓図は 最後のほうだけ示します）
[chr3]┃ └─────────────
┃ ┌─────────────
┃[ER_760_Carrol] │{61649269, 61649870}
┃ └─────────────
┃ ┌─────────────
┃[ER_761_Carrol] │{61768126, 61769266}
┃ └─────────────
┃ ┌─────────────
┃[ER_762_Carrol] │{62137944, 62138544}
┃ └─────────────
┗━━━━━━━━━━━━━━━━━━━━━━━
dic内には 3つのKey ("chr1","chr2","chr3") があり、
各Keyは それぞれ別の(独自の)子Dictionary をもつ --- このような
構成になります。
以上で、あたりチェックのSheet1側の範囲表データがDictionary
に登録されました。
これで、たとえば、"chr3" class の ID "ER_760_Carrol" の範囲は
dic("chr3")("ER_760_Carrol")
に配列として格納されていますから、
dic("chr3")("ER_760_Carrol")(0) とすれば start値が、
dic("chr3")("ER_760_Carrol")(1) とすれば end値を得ること
ができます。
さて、
> Dim a1, a2
> Dim b1, b2
> Dim vv
以降でやっていることは
> Set r = Worksheets("Sheet2").Cells(1).CurrentRegion
> With Intersect(r, r.Offset(1))
> v = .Resize(, 3).Value
> vv = .Columns(4).Cells.Value
> End With
で、
[Sheet2]
class start end ID
chr1 61649566 61649584
chr2 61649566 61649584
chr3 61649566 61649584
の1行目を除いたのち、
A,B,Cの3列を 変数v に格納し、
さらに4列目のID用に（現在空白セルの）4列目だけを 変数vv に入れ
たあと、
> For i = 1 To UBound(v) 'Sheet2 2行目から
> vv(i, 1) = Empty
たとえば i = 3 のとき、
> chr3 61649566 61649584
v(i,1) v(i,2) v(i,3)
という変数とデータの関係になってますから、
↓辞書dicに "chr3" というKeyが存在したら
> If dic.Exists(v(i, 1)) Then
↑は「真」となり、以下を実行します。
まず、
a1 に 2列目のstart値 61649566を、
a2 に 3列目のend値 61649584 を覚えておき↓、
> a1 = v(i, 2)
> a2 = v(i, 3)
dicのほうから class が "chr3" の子辞書内の
すべてのIDの start値と end値をb1,b2 として
a1,a2範囲との当たり判定をチェックしていきます。
> For Each ID In dic(v(i, 1)).Keys()
> b1 = dic(v(i, 1))(ID)(0)
> b2 = dic(v(i, 1))(ID)(1)
ここのところを iが3 の時の実数例で示せば、
> For Each ID In dic("chr3").Keys()
▼
最初のIDは "ER_756_Carrol"ですから、
> b1 = dic("chr3")("ER_756_Carrol")(0)
> b2 = dic("chr3")("ER_756_Carrol")(1)
は
b1 = 58431890
b2 = 58432547
になります。
これと
a1 = 61649566
a2 = 61649584
とで重なる部分の判定をします。
判定方法は前々回まとめたように
---------------------------------------- 判定の手順
> b1とa1をくらべて、
> ●b1 のほうが小さいときは、a1がb2より小さければ「あたり」
> （言い換えると「b1 <= a1 <b2 の関係にある」とき→あたり）
>
> a1
> b1───────b2
>
> ●a1 のほうが小さいときは、b1がa2より小さければ「あたり」
> （言い換えると「a1 < b1 <a2 の関係にあるとき」→あたり）
>
> a1━━━━━━━a2
> b1
------------------------------------------------------
という考えを用います。
最初のID ="ER_756_Carrol" のばあい、
> b1とa1をくらべて、
とは、b1 = 58431890 と a1 = 61649566 を比べることになります。
この結果、b1のほうが小さいので、次に a1と b2を比べますが、
b2のほうがa1より小さいので、このIDは「ハズレ」となります。
：
：
こんな調子で
> For Each ID In dic("chr3").Keys()
▼
に従って、IDをループしていくと、
▼"chr3" class 内で、ID = "ER_760_Carrol" のときは、
b1 = 61649269
b2 = 61649870
で、このときは
「 b1 <= a1 で、さらに、a1 < b2 の関係が成立」 します。
なので、このときのIDの値を vv(i,1)に代入して、
"chr3" 内のIDのLoop を中断(Exit For)します。
こうして、
> [Sheet2]
> chr1 61649566 61649584
> chr2 61649566 61649584
> chr3 61649566 61649584
のすべての行について、
各行の start値,end値(a1,a2) が
[Sheet1]の同名のclass内のID範囲(b1,b2)と比べて、
重なるIDがあればその値を vv(i) に入れ、
どれとも重ならなければ vv(i)には「ハズレ」を記入します。
最後に メモリ内のvv配列をシートの（2行目,4列目)以降に貼り
付けます.
↓範囲r内の 2行目4列目のセルのこと
> r.Item(2, 4).Resize(UBound(vv)).Value = vv
↑vvの最大要素数で 3 が返る
だいたい、こういう流れです。
|
|
![[未読]](./data/excel/image/read_icon.gif){kind=icon}
